Change Archivelog Destination (FRA)

by

Scenario: If current Archivelog mountpoint is full then we want to change the Archivelog destination in the database.

It can be done in up are running condition of DB . No downtime required. 

For Real application cluster(RAC):


In RAC, the archive destination is pointed to an ASM diskgroup.
1
2
3
4
5
SQL> show parameter log_archive_dest_1
 
NAME                                       TYPE                   VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                    string             LOCATION=+PROD_DG_FLASH
Change the archive destination:

Before pointing the path to new ASM DG, make sure that Diskgroup exists.

-- Use sid='*', so that parameter will be changed across all the instance pfiles.
1
2
3
4
5
6
7
8
9
10
alter system set log_archive_dest_1='LOCATION=+FRA' scope=both sid='*'
 
System altered.
 
 
SQL> select destination,STATUS from v$archive_dest where statuS='VALID';
 
DESTINATION STATUS
------------------ ---------
+FRA VALID
FOR STANDALONE DATABASE:
1
2
3
4
5
6
7
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/FRA/Arch
Oldest online log sequence 98674
Next log sequence to archive 98676
Current log sequence 98676
1
2
3
4
5
SQL> select destination,status from v$archive_dest where status='VALID';
 
DESTINATION                        STATUS
---------------------------------- ---------
/opt/app/oracle/FRA/Arch             VALID
Create the new archive location:
1
mkdir -p /newFRA/arch
Change the destination:
1
2
3
SQL> alter system set log_archive_dest_1='LOCATION=/newFRA/arch' scope=both;
 
System altered.
1
2
3
4
5
6
7
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /newFRA/arch
Oldest online log sequence 98674
Next log sequence to archive 98676
Current log sequence 98676
1
2
3
4
5
SQL> select destination,status from v$archive_dest where status='VALID';
 
DESTINATION                       STATUS
---------------------------------- ---------
/newFRA/arch                       VALID
Check whether archives are getting generated at new location:
1
2
3
4
5
6
7
8
9
SQL> alter system switch logfile;
 
System altered.
 
 
$ cd /newFRA/arch
$ ls -ltr
total 1231
-rw-r----- 1 oracle oinstall 12049920 Aug 16 10:34 1_98675_754673927.dbf