Wednesday, August 8, 2012

Archivelog destination change on fly

Sometimes you need to change your archivelog destination. You might need bigger disk etc.
Here is how you can do that.

After you have added new disk into OS (remember that with RAC you need to add same disk into all nodes) and in Oracle ASM ( if you use ASM) you need to do following:


1. Check which archivelog destination you are using now:

 Go into sqlplus (as sysdba):

(This will show you which parameter or parameters you have used with archivelog destination.)
(There can be several destinations with some configurations but usually only one.)
(And if there is now destination paths in these parameters then you have used "Flash Recovery Area" for archivelog destination.) 
SQL> show parameter log_archive_dest

Check also this (you might use same disk for flash recovery area or your archivelog destination might be in the flash recovery area). :
(This will show flash revovery area and flash recovery size.)
SQL> show parameter db_recovery

You can also use following command to see archivelog destination. But if you have multiple destinations this will show only one at the time:
SQL> archive log list

2. Change archivelog destination to new disk on fly:

If you are using some log_archive_dest parameter then you change it this way from sqlplus (as sysdba):
SQL> alter system set log_archive_dest_1 = 'LOCATION=/data/oradata/test/arch' scope=both;
If you are using same old disk for flash recovery are too change this also:
SQL> alter system set db_recovery_file_dest = ‘/data/oradata/test/flash’ scope=both;
If you are using flash recovery area for archive log destination you need to do following:
If you use ASM then add new bigger disk to ASM diskgroup where your flash recovery area is (check "db_recovery_file_dest" parameter (look above)) and start use it with changing db_recovery_file_dest_size parameter to bigger:
SQL>  alter system set db_recovery_file_dest_size=<new_size> scope=both;

And if you are not using ASM then you need to change "db_recovery_file_dest" parameter to point into new disk (like with "log_archive_dest" parameters):
SQL>  alter system set db_recovery_file_dest='<path>' scope=both;

3. Check that new destination is working as it should:

Check that everything is working ok:
SQL>  archive log list
SQL>  alter system archive log current;

If this does not give you errors you can remove old disk from OS (and from ASM if you are using it.).
 

No comments:

Post a Comment