Redo Log File Management

What is Redo Log ? 

Redo Logs consist of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has associated online redo logs to protect the database in case of an instance failure.

Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

The Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived the file. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

How to Force Log Switches?

A log switch occurs when LGWR stops writing to one redo log group and starts writing to another. By default, a log switch occurs automatically when the current redo log file group fills.

You can force a log switch to make the currently active group inactive and available for redo log maintenance operations. For example, you want to drop the currently active group but are not able to do so until the group is inactive. You may also want to force a log switch if the currently active group must be archived at a specific time before the members of the group are completely filled. This option is useful in configurations with large redo log files that take a long time to fill.

To force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause. The following statement forces a log switch:

alter system switch logfile; 

What are the Redo Log Data Dictionary Views ? 

v$log                                                    Displays the redo log file information from the control file

v$logfile                                          Identifies redo log groups and members and member status

v$log_history                              Contains log history information

Unused: files which are not used at any   point of time.

Current: Oracle Database uses only one redo log file at a time to store redo information written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Active: Redo log files those are required for instance recovery is called active redo log files.

Inactive: Redo log files that are no longer required for instance recovery are called inactive redo log files.

What is the Archive Redo Log ? 

The Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group.

What is difference between Archivelog and Noarchivelog mode ? 

The choice whether or not to enable the archiving of filled groups of redo log files depends on the availability and reliability requirements of the application running on the database. If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode.

Running a Database in NOARCHIVELOG Mode : 

– When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived.
– NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery.
– In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode.

Running a Database in 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 until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.
– The archiving of filled groups has these advantages:

• A database backup, together with 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 archived logs, you can use a backup taken while the database is open and in normal system use.
• You can keep a standby database current with its original database by continually applying the original archived redo logs to the standby.

[cognos@rac1 u02]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012

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

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /backup/orcl/

Oldest online log sequence     1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

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

ORCL      NOARCHIVELOG

Currently the ORCL database is in NOARCHIVELOG mode

To change the Oracle database in ARCHIVELOG mode. PFB below mentioned steps:-

1. If needed set the archive log destination where you want to save your archive logs whether to a single 

location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. 

If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile;

System altered.

Note -- To change this parameter while database is open, your database has to run with SPFILE, 

if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE

2. After this you need to shut down your database and start again in MOUNT mode

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/archive

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

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

ORCL      ARCHIVELOG

Database changed to ARCHIVELOG mode.

Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database 

immediately because in recovery scenarios you can recover your database from the last backup taken in this mode.

#############################################################################

To change the Oracle database in NOARCHIVELOG mode. PFB below mentioned steps:-

1.Shutdown your running database.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Start your database in MOUNT mode.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u02/archive

Oldest online log sequence     1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

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

ORCL      NOARCHIVELOG

Database changed to NOARCHIVELOG mode.

I hope this article helped you.

Daily Archive Log Generation :

SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Hourly Archive Log Generation :

select trunc(COMPLETION_TIME,'HH') Hour,thread# ,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;