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.
- 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
- 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
- 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
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/184.108.40.206/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.
- Each groups have at least two members
alter database add logfile member '/u01/app/oracle/product/220.127.116.11/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/18.104.22.168/oradata/dbname/REDO01.LOG 2 u01/app/oracle/product/22.214.171.124/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/126.96.36.199/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.