Redologs and Archivelogs

Redologs:

The most crucial structure for recovery operations is the redo log, which consists 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 an associated redo log 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.each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

The redo log of a database consists of two or more redo log files. The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if the database is in ARCHIVELOG mode).

LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again.  As bellow

Active (Current) and Inactive Redo Log Files

Oracle Database uses only one redo log files 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 its contents. 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.

Views

v$log (can find size, status, member…etc)

      v$logfile

Commands:

To see the names of all of the member of a group

SQL>SELECT * FROM v$logfile;

Adding redolog group:

sql> ALTER DATABASE ADD LOGFILE('/u01/app/oracle/oradata/redo3a.log','/u01/app/oracle/oradata/redo3b.log') SIZE 5M;

Here the group number will be taken automatically as next number to previous number

Adding redo log file to a group

sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO GROUP 2;

Droping redo log member from a group

sql> ALTERDATABASEDROPLOGFILE MEMBER '/u03/oradata/mydb/redo03.log';

If you want to drop logfile group

sql> ALTER DATABASE DROP LOGFILE GROUP 6;

 Relocating A redo log member(file):

You may want to relocate your existing redo log group and change the path of redo log files.

Shutdown database

SQL>shut immediate

Backup your existing control file and redo log file at operating system level.

 Move your redo log member to a new location. You may also change the file name if you want.

$ mv /u02/oradata/mydb/redo02.log /u03/oradata/mydb/redo03.log

Step 4: Startup in mount mode

sql> startup mount;

Step 5: Path of redo log files is kept in control file. You have to update that record by command below:

sql> ALTERDATABASERENAME FILE '/u02/oradata/mydb/redo02.log' TO '/u03/oradata/mydb/redo03.log';

Control file is modified and the path of redo log member is updated. Now you can open your database.

sql> alter database open;

Log Switches and Log Sequence Numbers:

A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

We we need switch manually whenever it is required we can use alter system command.

Sql> alter system switch logfile;

To findout current redolog you can visit v$log view. 

Archived Redo Log:

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.

When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn will write the redo data to archivelog files.

You can use archived redo logs to:

·       Recover a database

·       Update a standby database

·       Get information about the history of a database using the LogMiner utility

 

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.

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 an archived log, 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 continuously applying the original archived redo logs to the standby.

Changing the Database to Archiving Mode

The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG:

Shut down the database instance.

Sql>SHUTDOWN IMMEDIATE

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.

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

Sql>ALTER DATABASE ARCHIVELOG;

Sql>ALTER DATABASE OPEN;

Check the log mode.

Sql>SELECT log_mode FROM v$database;

Changing the Database to noarchiving Mode:

Sql>SELECT log_mode FROM v$database;

Sql>SHUTDOWN IMMEDIATE;

Sql>STARTUP MOUNT;

Sql>ALTER DATABASE NOARCHIVELOG;

Sql>ALTER DATABASE OPEN;

Sql>SELECT log_mode FROM v$database;

Specifying the Archive Destination

Before you can archive redo logs, you must determine the destination to which you will archive and familiarize yourself with the various destination states. 

You can choose whether to archive redo logs to a single destination or multiplex them. If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations.

Ex:

ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;

Or we can modify directly in the pfile if we are using pfile, database base bounce is required. As bellow

Open the file

$vi initorcl.ora

log_archive_dest=’location=/u01/oradata/MYSID/archive’

save and exit

If there is no settings to this log_archive_dest, By default it will store in Flash recovery area i.e db_recovery_file_dest

To know current log sequence and location and status you can use following.

Sql>archive log list;

Or visit v$archived_log.