How to turn on and off the ARCHIVELOG mode in oracle?

1. Turning on ARCHIVELOG mode:

We consider a database that is not in ARCHIVELOG mode and also automatic archival is not enabled. To see the status of the database we can use of the following SQL commands:

SQL> select log_mode from v$database;

LOG_MODE
————
NOARCHIVELOG

SQL> show parameter log_archive_start

NAME TYPE VALUE
—————– ——- —–
log_archive_start boolean FALSE
SQL>

Or

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /archivelog
Oldest online log sequence 7193
Current log sequence 7194
SQL>

We can bring this database to ARCHIVELOG mode by using the following SQL commands:

SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database archivelog;
SQL> alter database open;

Now we can see that this database is in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
———-
ARCHIVELOG

SQL>

But automatic archival is disabled yet and DBA must do manual archival by commands like the followings:

SQL> alter system archive log current;
SQL> alter system archive log all;

For enabling automatic archival we can use the following SQL commands:

SQL> alter system archive log start;

Or

SQL> archive log start;

Now we can see that automatic archival is enabled:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 7194
Next log sequence to archive 7195
Current log sequence 7195
SQL>

But this solution is not permanent and automatic archival will be disabled again after restarting the database; so for permanent change we must set parameter ‘log_archive_start’ to ‘TRUE’:

SQL> alter system set log_archive_start=TRUE scope=spfile;

And then restart the database.

Now the database is in ARCHIVELOG mode and also automatic archival is enabled.

Note1: We must take baseline backup right after enabling ARCHIVELOG mode.

Note2: After enabling ARCHIVELOG mode, it is better if we set the following init.ora parameters: log_archive_dest, log_archive_dest_1, log_archive_format.

2. Turning off ARCHIVELOG mode:

To see the status of the database we can use of the following SQL commands:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 7194
Next log sequence to archive 7195
Current log sequence 7195
SQL>

To disabling ARCHIVELOG mode we must run the following commands:

SQL> alter system set log_archive_start=False scope=spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;

SQL> startup mount excluseve;
SQL> alter database noarchivelog;
SQL> alter database open;

Now we can check the status of ARCHIVELOG mode:

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /archivelog
Oldest online log sequence 7194
Current log sequence 7195
SQL>

Note1: After disabling ARCHIVELOG mode, all the un backed up archived log files are unusable and can be deleted.

References:
Managing Archived Redo Logs
What is an archived redo log file?
How to Turn Archiving ON and OFF?
How does one put a database into ARCHIVELOG mode?
Enabling ARCHIVELOG mode!
Configuring the Database Archiving Mode

About these ads