Oracle: How to tell DB about manually freed space in FRA

If a database is configured to use a Flash Recovery Area (FRA), all archive-logs and RMAN-Backups go there. The FRA’s disk-space is limited to a user-defined value.
Usually a DBA’s RMAN-Backupjob cleans up all obsolete files in the FRA. This can easily be done by issuing a “DELETE OBSOLETE” in RMAN. Related to the defined RETENTION POLICY this command would delete all files no longer needed. If no manual deletes of files in the FRA on OS-Level are done, the database correctly keeps track of the remaining space in there.

SQL> SELECT name,
  ROUND(space_limit/1024/1024/1024,0) limit_gb,
  ROUND(space_used /1024/1024/1024,0) used_gb,
  ROUND(space_used /space_limit,4)*100 AS pct_used
FROM V$RECOVERY_FILE_DEST;

NAME                                    LIMIT_GB    USED_GB   PCT_USED
------------------------------------- ---------- ---------- ----------
/usr/people/oracle/fast_recovery_area         50         43      85.09

SQL>

This query gives a more detailed overview about the storage-distribution:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      63.52                         0             379
BACKUP PIECE                      21.56                         0               4
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL>

If a database is configured in archive-log-mode and to use a FRA, but without a RMAN-Based backup with cleanup, the FRA will continually be filled with archive-logs until no FRA-Space is left. This would bring the database to a halt. All active sessions are on hold showing the wait-events “log file switch (archiving needed)” or “free buffer waits”.

Now if we, in “panic”, delete a bunch of old archive-logs in the FRA using OS-Commands (like “rm”) to free space, the database would not notice this. Even a “CROSSCHECK ARCHIVELOG ALL” would not release the DB from it’s hold-state. It would just mark the deleted files as “EXPIRED” in the RMAN-Catalog. To make the database to recognize the delete, we must “delete” the files using RMAN once more:

RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

In general there is no need to manually delete files in the FRA on OS-Level, as we could have achieved our goal completely from within RMAN. This command for example would delete all archive-logs older than seven days – in the RMAN-Catalog and on disk:

RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';

The above procedure, of course, can also be used to handle the (manual) delete of backup-sets.

More info about the RMAN’s DELETE-Command can be found here:

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28273/rcmsynta016.htm#i81315

But there is one thing left: If we delete our archive-logs in the FRA with RMAN, only the files are deleted. And as Oracle creates every day a new directory for that days archive-logs named “YYYY-MM-DD” , we would still have a huge set of empty directories left. To clean up that too we could execute this (on Linux) after our RMAN-Backup/Cleanup has run:

find /apps/oracle/fast_recovery_area/DEMODB/ -mindepth 1 -empty -type d -exec rm -r {} \;

This command would pass all empty directories found under the given path to “rm” and hence delete them.

Edit 2012-05-02:
I got a note that, according to Oracle-Support, all empty directories in the FRA older than seven days would get deleted automatically after backup of control-file. I verified this successfully. But mind that “older than seven days” here means “remained unchanged since seven days”. In plain: If you delete all files from your archive-log-folder named “2012_05_02”, this folder would get deleted on 2012-05-09 (seven days later).

See MOS-Document: “Flash Recovery Area Housekeeping of empty directories” [ID 305970.1]

Advertisements

Bad filenames after “ZIPing” files from Linux to Windows

I need to copy the whole images-directory of our Mediawiki from a Linux-Box to a Windows-Machine. But no matter if I tar or zip the files, the filenames with non-ASCII-characters (ü,ö,ä,…) are messed up after unziped on Windows. All those non-ASCII-chars are shown as squares or other obscure characters under Windows. Not just that they look ugly this way, the Mediawiki won’t find these files anymore as their names changed referred to the entry in the wiki’s database.

Again it smells like an encoding-problem. How nice it would be, if the whole IT-world would just use unicode.

Our Linux is a RedHat 5 where the command “locale” shows this:

LANG=C
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_PAPER="C"
LC_NAME="C"
LC_ADDRESS="C"
LC_TELEPHONE="C"
LC_MEASUREMENT="C"
LC_IDENTIFICATION="C"
LC_ALL=

Don’t know what charset this “C” is meant to be. Edit 2012-10-18: “C” seems to mean charset “ANSI-C”. I thought all Linuxes use UTF-8 per default but at least for ours this seems not to be true. We found two solutions:

1.) Use WinSCP to copy all files over from Linux to Windows. This way the filenames get converted to Window’s own charset WIN1252.

2.) Change the charset of the Linux-console explicitly to UTF-8 prior to ZIP the files. Under Bash I do this:

export LANG=de_DE.UTF-8

Afterwards I zip the files using 7zip (in 7z-Format!). When I unzip them under Windows with 7zip too, all is fine. Using the normal ZIP-command to compress under Linux still messed up my filenames.

Mediawiki 1.16: Bad performance with IE 6.0

Checking out the latest Mediawiki version 1.16, we encountered a really bad performance while accessing the wiki with “Internet Explorer 6.0”. We had an average response-time of 3 seconds. On some older machines even more then 20 seconds. With Firefox 3.6 all was fine: instant response.

It turned out, that the cause of this performance problem was in the new default-skin “Vector”. In the file “/skins/vector.php” is a “public function initPage” with this snippet:

$out->addScript(
	'<!--[if lt IE 7]><style type="text/css">body{behavior:url("' .
		$wgStylePath .
		'/vector/csshover.htc")}</style><![endif]-->'
);

This adds a version-check: “[if lt IE 7]”. So for clients with IE lower than version 7 this function loads the given file “/vector/csshover.htc” which supplies some functionality that IE 6.0 and lower lacks. After we commented out this part of code the performance was well in IE 6.0 too. Until now we saw no cutbacks done by this, so we left it disabled.

Edit 2011-07-06:
Ok, it should have been obvious (“csshover.htc”): There is a lack of functionality after disabling it. In IE6 the hover-effect, e.g. on the arrow-tab right of the “version-history”-tab, doesn’t work. So the corresponding menu with “delete”, “move”, etc. will not show up.

Here is a hack to resolve the problem:

In file skins/Vector.php (~line 39) replace this

$out->addScript(
	'<!--[if lt IE 7]><style type="text/css">body{behavior:url("' .
		$wgStylePath .
		'/vector/csshover.htc")}</style><![endif]-->'
);

with this

$out->addScript(
	'<!--[if lt IE 7]><script type="text/javascript">
		jQuery(function(){
			jQuery("#p-cactions").mouseenter(function(){
				jQuery("#p-cactions div.menu").css("display","block");
			});
			jQuery("#p-cactions").mouseleave(function(){
				jQuery("#p-cactions div.menu").css("display","none");
			});
		});
	</script><![endif]-->'
);