Configuring the Database Archiving Mode in Oracle 12C

Configuring the Database Archiving Mode

Why we need Archiving mode enable?

To back up the database while it is open (database open mode), or to be able to perform complete or point-in-time media recovery, you must enable the archiving of redo log files. To do so, you place the database in ARCHIVELOG mode.

The database can be initially created in ARCHIVELOG mode, but it is configured for NOARCHIVELOG mode by default.

 

How to check Archive log mode enable or not?

SQL> select sequence#, bytes,archived,status,first_time,next_time from v$log;

SQL> archive log list

SQL> select log_mode from v$database;

NOARCHIVELOG Mode

By default, a database is created in NOARCHIVELOG mode. The characteristics of operating a database in NOARCHIVELOG mode are as follows:

Redo log files are used in a circular fashion.

A redo log file can be reused immediately after a checkpoint has taken place.

After redo logs are overwritten, media recovery is only possible to the last full backup.

 

Impacts of NOARCHIVELOG Mode

If a tablespace becomes unavailable because of a failure, you cannot continue to operate the database until the tablespace has been dropped or the entire database has been restored from backups (Cold backup).

All transactions are recorded in the online redo log files. This allows for automatic recovery of transactions in the event of a database failure

If the database is configured for NOARCHIVELOG mode, no redo history is saved to archived log files, and recovery operations are limited and a loss of transaction work may occur. This is the result of the automatic recycling of log files, where older log files needed for recovery are overwritten and only the most recent part of the transaction history is available.

If the online redo log files have been overwritten, you will lose all data since the last full backup.

You can configure a database in ARCHIVELOG mode, so that a history of redo information is maintained in archived files. The archived redo log files can be used for media recovery.

 

Media Recovery Options in Archive log mode

You can restore a backup copy of the damaged files and use archived log files to bring the datafiles up-to-date while the database is online or offline.

You can recover the database to a specific point in time.

You can recover the database to the end of a specified archived log file.

You can recover the database to a specific system change number (SCN).

 

Changing the Archiving Mode

Shut down the database

SQL> shut immediate;

Start the database in Mount state so that you can alter the Archivelog mode of database

SQL> startup mount

Set the database in Archivelog mode by using the ALTER DATABASE command

SQL> alter database archivelog;

Open the database

SQL> alter database open;

 

Check Archive log mode enable or not

SQL> archive log list

 

SQL> select log_mode from v$database;

 

I hope above guide is helpful to all of you and you are able to enable archivelog mode. Share this awesome article to your friends as well. Have a nice day.