Changing the Archive Log Destination in Oracle

Changing the Archive Log Destination in Oracle

by

Changing the Archive Log Destination in Oracle: During this article we’ll see the way to Changing the Archive Log Destination in Oracle Database.

If your present mount point for archivelog destination is going to FULL or for the other reason, you wish to alter the archivelog destination in the database, then follow the below steps.

NOTE –> It may be done, when the database is up and running. No downtime required.

FOR STANDALONE DATABASE:

Firstly, Find the current archivelog location:

SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/ORCL/Archive
Oldest online log sequence     25626
Next log sequence to archive   25628
Current log sequence           25628
 
 
SQL>
SQL>
SQL>  select destination,STATUS from v$archive_dest where statuS='VALID';
 
DESTINATION                    			    STATUS
----------------------------------                        ---------
/u01/ORCL/Archive                                    VALID

Secondly, create the new archive location:

mkdir -p /u02/ORCL/Arch

Thirdly, change the destination:

SQL>
SQL>
SQL> alter system set log_archive_dest_1='LOCATION=/u02/ORCL/Arch' scope=both;
 
System altered.

SQL>
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/ORCL/Arch
Oldest online log sequence     25626
Next log sequence to archive   25628
Current log sequence           25628
 
SQL>
SQL>
SQL>  select destination,STATUS from v$archive_dest where statuS='VALID';
 
DESTINATION                    			    STATUS
----------------------------------                ---------
/u02/ORCL/Arch                                    VALID

Lastly, Check whether archives are getting generated at new location:

SQL> alter system switch logfile;
 
System altered.

SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 

[oracle@orakldba ~]$ cd /u02/ORCL/Arch
[oracle@orakldba ~]$
[oracle@orakldba ~]$ ls -ltr
total 11
-rw-r----- 1 oracle oinstall 12049920 Aug 19 15:17 1_25628_929882536.dbf

For Real application cluster(RAC):

In RAC, the archivelog destination is pointed to an ASM diskgroup.

SQL>
SQL> show parameter log_archive_dest_1
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=+CRMARCH

Change the archive destination:

In this case, you need to use sid=’*’, so that parameter will be changed across all the instance pfiles.

SQL> alter system set log_archive_dest_1='LOCATION=+FRA' scope=both sid='*'
 
System altered.
 
SQL>
SQL>
SQL>
SQL> select destination,STATUS from v$archive_dest where statuS='VALID';
 
DESTINATION        STATUS
------------------ ---------
+CRMARCH           VALID

SQL>
SQL>

Note:

Most vital, To permanently change the destination, you must need to change the initialization parameter. You’ll change it dynamically with the alter system command as shown below.

alter system set log_archive_dest_1='LOCATION=<New archive destination>';

Then check and confirm your location is changed or not.

archive log list;

LOG_ARCHIVE_DEST has been censured for LOG_ARCHIVE_DEST_n for Enterprise Edition clients. On the off chance that you don’t have Enterprise Edition or you have not determined any LOG_ARCHIVE_DEST_n boundaries, LOG_ARCHIVE_DEST is legitimate.

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.

Leave a Comment