Change Archivelog mode and Destination In Oracle 19c

In this article, we are going to learn how to change archivelog mode and destination in oracle 19c.

Table of Contents

Start Change Archivelog mode and Destination

$ export ORACLE_SID=ocp
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Tue Apr 2 13:04:33 2022
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1778381832 bytes
 Fixed Size            8897544 bytes
 Variable Size          536870912 bytes
 Database Buffers     1224736768 bytes
 Redo Buffers            7876608 bytes
 Database mounted.
 Database opened.

Step 1: Check Archive log mode Status

Check current archive log mode status in oracle 19c.

SQL> archive log list

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4

As we checked our database running in no archive mode.

Step 2: Create the Archive destination

Create a destination for archives.

$ mkdir -p /u01/archives

Step 3: Check the current Archive location

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
-------------------------- 	-----------  ----------------------------------
db_recovery_file_dest 		string       /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size 	big integer  8256M

Step 4: Change the log_archive_dest parameter

Try to change the log_archive_dest parameter value, but this time you will fail.

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/archives' scope=both;
ALTER SYSTEM SET log_archive_dest ='/u01/ARC_BKP' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Before you change the log_archive_dest location you should change the first DB_RERCOVERY_FILE_DEST and then update the log_archive_dest location.

SQL> alter system set DB_RECOVERY_FILE_DEST=”;

System altered.

Step 5: Change Archive Destination

Now change the archive destination, and you will succeed.

SQL> alter system set log_archive_dest='/u01/archives';

System altered.

Step 6: Change database archive log mode.

Here is the step to Change Archivelog mode and Destination.

SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.

Now take bounce the database once and then check archive mode and its destination.

SQL> shut immediate
SQL> startup

SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archives
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8

We have successfully changed Archivelog mode and Destination please write in the comment box.