Changing the Database Archiving Mode In Oracle

Changing the Database Archiving Mode In Oracle: In this article we will discuss how Changing the Database Archiving Mode In Oracle.

There are 2 types of logging mode in oracle database:

Archivelog mode :

In this mode, after the online redo logs are filled , it will move to archive location.

Noarchivelog mode :

In this mode, filled online redo logs wont be archives, instead they will be overwritten.

How to Enable archiving mode :

Step-1:

Check the database archive log mode status.

SQL> select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      NOARCHIVELOG
 

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disbled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513

Step-2:

Enter the following command to set the first archived log file destination:

SQL>
SQL> alter system set log_archive_dest_1='LOCATION=/u01/archive/PROD';

database altered.

Note: The directory must exist.

Step-3:

Shutdown the Database:

SQL> 
SQL> shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

Startup the instance and mount the database (do not open the database). To enable archiving, the database must be mounted but not open.

SQL> startup mount
ORACLE instance started.

Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes
Database mounted.

Step-4:

Change the database archiving mode and then open the database for normal operations.

SQL>
SQL>
SQL> alter database archivelog;
 
database altered.
 
SQL>
SQL>
SQL>
SQL> alter database open;
 
database altered.
 
SQL>

Check and verify archive log mode status.

SQL>
SQL> select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      ARCHIVELOG
 
SQL>
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513

Step-5:

Shutdown the database.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step-6:

Back up the database: Because changing the archiving mode updates the control file, it is recommended that you create a new backup.

Step-7:

Start up the database normally.

SQL> startup
ORACLE instance started.

Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes
Database mounted.
Database opened.

How to Disable archiving mode :

Step-1:

Check the database archive log mode status. And make sure db is running in spfile.

SQL>
SQL> select name, log_mode from v$database;

NAME      LOG_MODE
--------- ------------
PROD      ARCHIVELOG

SQL>
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513

Step-2:

Shutdown and Startup the database in mount stage (do not open the database). To disable archiving, the database must be mounted but not open.

SQL> 
SQL> shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup mount
ORACLE instance started.

Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size             905970240 bytes
Database Buffers         5502926848 bytes
Redo Buffers                4530176 bytes
Database mounted.

Step-3:

Change the database archiving mode and then open the database for normal operations.

SQL>
SQL>
SQL> alter database noarchivelog;
 
database altered.
 
SQL>
SQL>
SQL>
SQL> alter database open;
 
database altered.
 
SQL>

Step-4:

Check and verify archive log mode status.

SQL>
SQL> select name, log_mode from v$database;
 
NAME      LOG_MODE
--------- -----------
PROD      NOARCHIVELOG
 
SQL>
SQL>
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disbled
Archive destination            /u01/archive/PROD
Oldest online log sequence     506
Next log sequence to archive   513
Current log sequence           513

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.