Understanding the Archive Log Mode in Oracle

 

Understanding the Archive Log Mode in Oracle

Donatien MBADI, Database Expert, OCP, OCE

 

 

 

NOTE:

Oracle lets you save filled redo log files to one or more offline destinations to improve the recoverability of your data by having all transactions saved in case of crash, reducing any possibility of data loss. The copy of redo log file containing transactions against your database made to a different location is known as an ARCHIVELOG file and the process of turning redo log files into archived redo log files is called archiving.

 

 

 

1.      WHAT IS REDO LOG?

 

The most crucial structure for recovery operations in Oracle Database is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every database instance of an Oracle database has an associated redo log to protect the database in case of an instance failure.

The changes are written by the server process to the redo log buffer in the SGA (System Global Area). The redo log buffer will then be flushed into the online redo logs in near real-time fashion by the log writer (LGWR) process.

 

The redo log entries are written by the LGWR to a disk when:

 

-       A user issues a COMMIT

-       The log buffer is one third full

-       The amount of unwritten redo entries is 1MB

-       When a database checkpoint takes place

-       Otherwise every three seconds.

 

 

Redo log files must be multiplexed using different disks to ensure that the information stored in them is not lost in the event of a disk failure.

 

The redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group, and each group is identified by a number. The LGWR process writes redo records from the redo log buffer to all members of a redo log group until the file is filled or a log switch operation requested. Then, its switches and writes to the files in the next group. Redo log groups are used in a circular fashion a shown in the following figure:

 

 

The loss of an entire log group is one of the most serious possible media failures you can come across because it can result in loss of data. The loss of a single member within a multiple-member log group is trivial and does not affect database operation, other than causing an alert to be published in the alert log.

 

 

2.      THE NOARCHIVELOG MODE

 

When your database is created by default, it will be created in the NOARCHIVELOG mode. This mode permits any normal database operations but will not provide your database with the capability to perform any point-time recovery operations or online backups for your database.

When the database is in NOARHIVELOG mode, no hot backup is possible. You will only be able to perform full recovery to the point that your backup was made.

 

 

 

 

 

 

 

To make a full backup of your database when in the NOARCHIVELOG mode, you will need to first shutdown your database in consistent mode and then mount the database, run your backup and open the database after the backup completed.

 

If a recovery is required, then you need to restore all files from your last backup and start the database, but you have to understand that all transactions made in the database after your backup will be lost.

 

 

3.      THE ARCHIVELOG MODE

 

 

An archived redo log file is a physical copy of one of the filled members of a redo log group. Remember that redo log files are cyclical files that are overwritten by the Oracle database and are the only archived when the database is in the ARCHIVELOG mode.

 

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR process until the group is archived. A filled group becomes available immediately after a redo log switch occurs.

 

Each redo log file includes all the redo entries and the unique log sequence number of the identical member of the redo log group. To make this point more clear, if you are multiplexing your redo log (minimum two members per group are required), and if your redo log group 1 contains to identical member files such as redolog_1a.log and redolog_1b.log, then the archive process (ARCn) will only archive one of there member files, not both. If redolog_1a.log becomes corrupted, then the ARCn process will be able to archive the identical surviving redo log file redolog_1b.log.

 

When the database is running in the ARCHIVELOG mode, the LGWR process cannot reuse and hence overwrite a given redo log group until it has been archived, this is to ensure the recoverability of your data. The background process ARCn will automate the archiving operation and the database will start multiple archive processes as necessary to ensure that the archiving of filled redo log files does not fall behind. The ARCn process will generate archive copies of your redo log files to one or more archive log destination directories. The number and location of destination directories will depend on your database initialization parameters.

 

The archiving of filled groups has these advantages:

-       A database backup, together with the online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

-       If you keep an archived log, you can use a backup taken while the database is open.

-       You can keep a standby database current with its original database by continuously applying the original archived redo log.

 

You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. The below figure illustrates how the archiver process ARCn writes filled redo log files to the database archived redo log.

 

 

 

You can use archived redo logs to:

-       Recover database

-       Update and keep a standby database in sync with a primary database

-       Get information about the history of a database using the LogMiner utility.

 

 

To use the ARCHIVELOG mode, you will need first set up some configuration parameters and then, all database activity regarding your transactions will be archived to allow your data recoverability and you will need to ensure that your archival destination are always has enough space available. If space runs out, your database will suspend all activities until it becomes able once again to back up your redo log files in the archival destination.

 

 

3.1. Preparing for the ARCHIVELOG mode

 

You can determine if your database is on NOARCHIVELOG or ARCHIVELOG mode by issuing the below query:

 

SQL> select log_mode from v$database;   

 

LOG_MODE

------------

NOARCHIVELOG

 

SQL>

You can also use the below SQL*plus command:

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     23606

Current log sequence           23608

SQL>

 

Before switching your database in ARCHIVELOG mode, you have to choose between generating archive redo logs to a single location or multiplexed them. The most important parameters are:

 

-       LOG_ARCHIVE_DEST_n (n can be a number between 1 and 31): This parameter specifies one or more destinations.

 

SQL> alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelogs';

 

System altered.

 

SQL>

 

You can use the FRA (Called Flash Recovery Area prior to 11gR2, and now called Fast Recovery Area) as one of the archived redo log destination. By default, the FRA destination is specified by the USE_DB_RECOVERY_FILE_DEST initialization parameter, but you can change it.

You have to notice that the DB_RECOVERY_DEST specifies the default location for the Fast Recovery Area which contains multiplexed copies of current control files and online redo logs and as well archived redo logs, flashback logs and RMAN backups.

 

-       LOG_ARCHIVE_FORMAT: This parameter will specify the default filename format when archiving the redo log files. The following variables can be used to format the file:

 

o   %s—log sequence number

o   %S—log sequence number, zero filled

o   %t—thread number

o   %T—thread number, zero filled

o   %a—activation ID

o   %d—database ID

o   %r—resetlogs ID

For example, you can run the following command to define your archive relog format:

 

SQL> alter system set log_archive_format = 'ProdDb_%s_%t_%r.arch' scope = spfile;

 

System altered.

 

SQL>

 

 

3.2. Changing the Database to the ARCHIVELOG mode

 

To switch your database from NOARCHIVELOG to ARCHIVELOG mode, use the following steps:

1-    Shutdown your database using NORMAL, IMMEDIATE or TRANSACTIONAL mode

 

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

2-    Mount your database

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 2432696320 bytes

Fixed Size                  8623592 bytes

Variable Size             620759576 bytes

Database Buffers         1795162112 bytes

Redo Buffers                8151040 bytes

Database mounted.

SQL>

 

3-    Switch your database to use ARCHIVELOG

 

SQL> alter database archivelog;

 

Database altered.

 

SQL>

 

4-    Open your database

 

SQL> alter database open;

 

Database altered.

 

SQL>

 

You can now check the new parameters.

 

SQL> select log_mode from v$database;

 

LOG_MODE

------------

ARCHIVELOG

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/archivelogs

Oldest online log sequence     32315

Next log sequence to archive   32317

Current log sequence           32317

SQL>

3.3. Testing that the archived redo logs are generated

 

 

You can manually test that the redo logs are archived using the following command:

 

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> r

  1* alter system switch logfile

 

System altered.

 

SQL> exit

 

 

[oracle@dladc2-infoem ~]$ cd /u01/app/oracle/archivelogs

[oracle@dladc2-infoem archivelogs]$

[oracle@dladc2-infoem archivelogs]$

[oracle@dladc2-infoem archivelogs]$ ll

total 207712

-rw-r-----. 1 oracle oinstall 201356800 Jan 19 19:26 ProdDb_32316_1_938610491.arch

-rw-r-----. 1 oracle oinstall  11284992 Jan 19 19:35 ProdDb_32317_1_938610491.arch

-rw-r-----. 1 oracle oinstall     45056 Jan 19 19:35 ProdDb_32318_1_938610491.arch

[oracle@dladc2-infoem archivelogs]$

 

 

3.4. Testing hot backup

 

Once your database is on ARCHIVELOG MODE, you can make a backup while the database is open and running.

 

RMAN> backup as compressed backupset database;

 

Starting backup at 19-JAN-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=156 device type=DISK

RMAN-06554: warning: file 1 is in backup mode

RMAN-06554: warning: file 2 is in backup mode

RMAN-06554: warning: file 3 is in backup mode

RMAN-06554: warning: file 4 is in backup mode

RMAN-06554: warning: file 5 is in backup mode

RMAN-06554: warning: file 7 is in backup mode

RMAN-06554: warning: file 8 is in backup mode

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=/u01/app/oracle/oradata/emrep/mgmt.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/emrep/mgmt_ad4j.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/emrep/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/emrep/undotbs01.dbf

…

…