Sunday, August 17, 2008

Archived redo log destination filling up

What can you do while the archived redo log destination gets filling up? One of the clients I worked before has set up a cron job to monitor the space and automatically move the old archived redo logs to the second location if certain threshold reaches. The script also does the cleaup for those acrhived logs older than three days ago. The client adopted NetApp snapshot for backup solution.

The current client I am working for uses different strategy. They also monitor the space, however instead of moving the arhived log, they change the archived redo log destination dynamically when the threshold reaches. In addtion, there is a rman job runing every 10-15 min to backup archived redo logs to tape and cleanup the logs.

When I try to test the syntax of changing archived redo log destination dynamically, I get confused by Oracle online documentation. After searching internet and test a little while, I have learned that the 'location' keyword is necessary and no space inside the quotation mark is allowed. While Oracle documention shows the space there. Examples are shown as follows:


- missing 'location' keyword

sys@DB10G> alter system set log_archive_dest_1='C:\temp';
alter system set log_archive_dest_1='C:\temp'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


- correct syntax

sys@DB10G> alter system set log_archive_dest_1='location=C:\temp';

System altered.

- space can not appear inside the quotation mark

sys@DB10G> alter system set log_archive_dest_1='location = C:\temp';
alter system set log_archive_dest_1='location = C:\temp'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


sys@DB10G> alter system set log_archive_dest_1='location = C:\';
alter system set log_archive_dest_1='location = C:\'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


- This is correct

sys@DB10G> alter system set log_archive_dest_1='location=C:\';

System altered.

No comments: