Oracle Database Interview Questions : Redo Logs and Archiving

What is the 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 create Redo Log Groups?

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause. The following statement adds a new group of redo logs to the database:

ALTER DATABASE
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 100M;

You can also specify the number that identifies the group using the GROUP clause, e.g.:

ALTER DATABASE 
ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')
SIZE 100M BLOCKSIZE 512;

When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'
TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

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;

How to clear a Redo Log file?

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database. Example:

ALTER DATABASE CLEAR LOGFILE GROUP [grp#];

This statement overcomes two situations where dropping redo logs is not possible:

• If there are only two log groups
• The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE [grp#];

This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived.

What are the Redo Log Data Dictionary Views?

The following views provide information on redo logs.

What is the Archived 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 the difference between NOARCHIVELOG and ARCHIVELOG 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.

How to turn on and off archivelog mode?

Refer the below post to get a detailed idea on how to turn on and off archiving in oracle database.

How to Perform Manual Archiving?

You can configure your database for manual archiving only. To operate your database in manual archiving mode, follow the procedure below:

1. Shut down the database instance.

SQL> SHUTDOWN

An open database must first be closed and any associated instances shut down before you can switch the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.

2. Back up the database. Before making any major change to a database, always back up the database to protect against any problems. This will be your final backup of the database in NOARCHIVELOG mode and can be used if something goes wrong during the change to ARCHIVELOG mode.

3. Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archived redo log files.

4. Start a new instance and mount, but do not open, the database.

SQL> STARTUP MOUNT

To enable or disable archiving, the database must be mounted but not open.

5. Change the database archiving mode. Then open the database for normal operations.

SQL> ALTER DATABASE ARCHIVELOG MANUAL;
SQL> ALTER DATABASE OPEN;

6. Shut down the database.

SQL> SHUTDOWN IMMEDIATE

7.Back up the database. Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode.

When you operate your database in manual ARCHIVELOG mode, you must archive inactive groups of filled redo log files or your database operation can be temporarily suspended. To archive a filled redo log group manually, connect with administrator privileges. Ensure that the database is either mounted or open. Use the ALTER SYSTEM statement with the ARCHIVE LOG clause to manually archive filled redo log files. The following statement archives all unarchived log files:

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

How to Adjust the Number of Archiver Processes ?

The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the number of ARCn processes that the database initially starts. The default is four processes. The following statement configures the database to start six ARCn processes upon startup:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=6;

The statement has an immediate effect on the currently running instance.

How to Set Initialization Parameters for Archive Destinations

You can choose to archive redo logs to a single destination or to multiple destinations. Destinations can be
1. local – within the local file system or an Oracle Automatic Storage Management (Oracle ASM) disk group or
2. remote – For e.g. on a standby database.

To archive to only a single destination, specify that destination using the LOG_ARCHIVE_DEST initialization parameter. To archive to multiple destinations, you can choose to archive to two or more locations using the LOG_ARCHIVE_DEST_n initialization parameters, or to archive only to a primary and secondary destination using the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST initialization parameters.

What are possible Archive Destination Status values?

Each archive destination has the following variable characteristics that determine its status:

• Valid/Invalid: indicates whether the disk location or service name information is specified and valid.
• Enabled/Disabled: indicates the availability state of the location and whether the database can use the destination.
• Active/Inactive: indicates whether there was a problem accessing the destination.

How to View Information About the Archived Redo Log?

You can display information about the archived redo log using dynamic performance views or the ARCHIVE LOG LIST command.