redolog

Today  we discuss about Archive mode and No archive mode  before that we have to  remain what is redo log file ?  what is the use ? how the logwr process works on database? just refer our previous post Oracle Database 11g Architecture redo log file and  LGWR process.

NOARCHIVE MODE:

  - When  we create an oracle database by default it's in NOARCHIVE MODE

  - In below these scenarios redo log writes through LGWR  from redo log buffer

      *Every 3 sec

      *When there is full 1MB reached

      *One third full

      *Before DBWR writes

      *At commit

  - When the database is NO ARCHIVE MODE the redo log files overwrites one member into  another  member these function is called as"CYCLE FASHION".

  - In these mode you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup

  - It holds the information about  recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery

ARCHIVE  MODE:

  - We can maintain 1 t0 31 designation in LOG_ARCHIVE_DEST_N

  - While you take backup the database  ARCHIVE LOG MODE is must be enable , which  means  guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

 - It has a no cycle fashion  

redolog transformations

in above picture we can see various levels of redo log files are

  *Current - currently LGWR writing log file from redolog buffer

  *Inactive - No longer  required for that instance recovery i.e reuse log file which contain information are flushed

   *Active -    Redo log files that are required for instance recovery i.e information are written into archive logs

  *Unused - Newly created log file  

WORK FLOW OF REDO LOGS:

  - Once the Current redo log file is full or manually  switched it always search unused redo log file to write Information from  redo log buffer. if is not available then it  will goes to inactive redo log file into current

  - Now the current redo log file will turns into  "Active  redo log file" which means that  file information are written into archive log through ARCn

  - Once that written process is over an archive log then it turns into "Inactive redo log file "

CREATING GROUP AND MEMBER IN REDO LOG :

  - Each database consists of two or more redo log files

  - Here group is logical and member is physical ADDING GROUP:

     Alter database add logfile group 2 ('/u01/app/oracle/product/11.2.0.2/oradata/dbname/redo01.log') size 40m;

  - Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES. Do not skip redo log file group numbers (that is, do not number your groups 10, 20, 30, and so on), or you will consume unnecessary space in the control files of the database.  

ADDING MEMBER:

  - Each groups have at least two members

    alter database add logfile member '/u01/app/oracle/product/11.2.0.3/oradata/dbname/redo02.log' to  group 2;

  - We can add new member file to  an existing group like above to view the  group and  member in redo log file:  

    VIEW            DESC
    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

The following query returns the control file information about the redo log for a database.  

    SELECT * FROM V$LOG;

    GROUP# THREAD#   SEQ   BYTES  MEMBERS  ARC STATUS     FIRST_CHANGE# FIRST_TIME
    ------ ------- ----- -------  -------  --- ---------  ------------- ---------
     1       1 10605 1048576        1  YES ACTIVE          11515628 16-APR-15
     2       1 10606 1048576        1  NO  CURRENT         11517595 16-APR-15

To see the names of all of the member of a group, use a query similar to the following:

     SELECT * FROM V$LOGFILE;

     GROUP#   STATUS  MEMBER
     ------  -------  ----------------------------------
     2           u01/app/oracle/product/11.2.0.3/oradata/dbname/REDO01.LOG
     2           u01/app/oracle/product/11.2.0.3/oradata/dbname/REDO02.LOG

  - If status is blank for a member, then the file is in use.

DROP GROUP AND MEMBER IN REDO LOG FILE:

  - To drop a  group  in redo log file

    alter database drop logfile group 2;

  - To drop a  member  in redo log file  

    ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/product/11.2.0.3/oradata/dbname/redolog2.log';

  Note: we can't drop a member file or group when it is a "Current" or an "Active "status,only can do on " Inactive " and "Unused" status  

FORCING LOG SWITCH:

  - A log switch occurs when LGWR stops writing to one redo log group and starts writing to another

  - By default it happens automatically  when  the current redo log group fills - Sometimes we can manually to force switch a  log file to make the currently active group inactive and available for redo log maintenance operations

   alter system switch logfile;

CLEARING REDO LOGFILE:

  - When  a redo log file is corrupted or damaged while the database is open, at the same time it stop database activity because archiving cannot continue

  - At these critical situation reinitialize the file without shutting down the database.

   ALTER DATABASE CLEAR LOGFILE GROUP 2;

Note: you can't clear the redo log group when it is in current group and there are only two log groups

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

   ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Note :If you clear an unarchived redo log file, you should make another backup of the database because if you clear  a log file i.e needed for a recovery backup,then you can no longer recover from that backup.