Changing Archive Log Destination

In this article you will learn how to change the destination for archived redo log files. Sometime the location where archive redo log is full and you can not access the database.there are two way to this:

1.Temporarily Changing the Destination Using SQL*Plus

If you are automatically archiving, you can use the following command to override the destination specified by the LOG_ARCHIVE_DEST. This command does not change the value in the initialization parameter file. This change is only valid until you restart the instance.

>sqlplus / as sysdba

see current location

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287

To change the location

sql>ARCHIVE LOG START '/oracle2/arch';

To Verify your changes:

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle2/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287


Permanently Changing the Destination Using SQL*Plus


To permanently change the destination, you must change the initialization parameter. You can change it dynamically with the ALTER SYSTEM command as shown below:

Note: LOG_ARCHIVE_DEST has been deprecated in favor of LOG_ARCHIVE_DEST_n for Enterprise Edition users. If you do not have Enterprise Edition or you have not specified any LOG_ARCHIVE_DEST_n parameters, LOG_ARCHIVE_DEST is valid.


> sqlplus / as sysdba

Issue the ALTER SYSTEM command to update the value of the LOG_ARCHIVE_DEST_n parameter in memory and in your SPFILE:

sql> ALTER SYSTEM SET log_archive_dest ='/oradata2/arch' scope=both;

To Verify your changes:

sql> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle2/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287

IN ORACLE 10g

To see archive log status
>sqlplus / as sysdba

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 132
Next log sequence to archive 134
Current log sequence 134

To see the physical archive location

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/oracle/oradata/flash_r
ecovery_area/
db_recovery_file_dest_size big integer 2G


To change the size of archive log

SQL> alter system SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='orca';

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/oracle/oradata/flash_r
ecovery_area/
db_recovery_file_dest_size big integer 10G
SQL>

To change the Physical Location:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='/backup/oracle/flash_recovery_area/' SCOPE=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 2020448 bytes
Variable Size 218106784 bytes
Database Buffers 1023410176 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/oracle/flash_recovery_
area/
db_recovery_file_dest_size big integer 10G