Warning: session_start(): open(/tmp/sess_u4i2a43jisli50po9pqsedgfc5, O_RDWR) failed: No space left on device (28) in /razi/eraazi/darbari/mercy/index.php on line 15
EraAzi | how to change archivelog destination in oracle how to change archivelog destination in oracle
 

If your present archivelog mountpoint is FULL or for any other reason, you want to change the archivelog destination in the database, then follow the below steps.

NOTE - > It can be done ,when the database up are running. No downtime required. 

FOR STANDALONE DATABASE:

1. Find the current archivelog location:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /exatree05/ORCLSIT2/Arch
Oldest online log sequence     25626
Next log sequence to archive   25628
Current log sequence           25628


SQL>  select destination,STATUS from v$archive_dest where statuS='VALID';

DESTINATION                    			    STATUS
----------------------------------                ---------
/exatree05/ORCLSIT2/Arch                             VALID

create the new archive location:

mkdir -p /exatree06/arch

change the destination:

SQL> alter system set log_archive_dest_1='LOCATION=/exatree06/arch' scope=both;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /exatree06/arch
Oldest online log sequence     25626
Next log sequence to archive   25628
Current log sequence           25628

SQL>  select destination,STATUS from v$archive_dest where statuS='VALID';

DESTINATION                    			    STATUS
----------------------------------                ---------
/exatree06/arch                                    VALID

Check whether archives are getting generated at new location:

SQL> alter system switch logfile;

System altered.

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

$ cd /exatree06/arch
$ ls -ltr
total 11768
-rw-r----- 1 oracle oinstall 12049920 Mar 22 15:17 1_25628_929882536.dbf

For Real application cluster(RAC):

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

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=+CRMARCH

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.

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
------------------ ---------
+CRMARCH           VALID

archivelog DATABASEHOW TO