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.