Sunday , October 22 2017
Home / Oracle DBA / MULTIPLEXING REDOLOG FILES

MULTIPLEXING REDOLOG FILES

MULTIPLEXING REDOLOG FILES

  1. Redo log files are mainly used for recovering a database and also to ensure data commit.
  1. If a redo log file is lost, it will lead to data loss. To avoid this, we can maintain multiplexed copies of redo log files in different locations. These copies are together called as redo log group and individual files are called redo log members.
  1. Oracle recommends to maintain a min of 2 redo log groups with min of 2 members in each group.
  1. LGWR will write into members of same group parallely only if ASYNC I/O is enabled at OS level.
  1. Redo log files will have 3 states – CURRENT, ACTIVE and INACTIVE. Always these states will be changed in cyclic order.
  1. We cannot have different sizes for members in the same group, whereas we can have different sizes for different groups, but not recommended to implement.
  2. Default size of redo log member is 100mb in 9i and 50mb in 10g.
  1. In 8i, LOG_CHECKPOINT_INTERVAL parameter setting will specify the time at which checkpoint should occur where as from 9i the same can be achieved using FAST_START_MTTR_TARGET.

MULTIPLEXING REDOLOG FILES

COMMANDS
# To check redolog file members
SQL> select member from v$logfile;
# To check redolog group info,status and size
SQL> select group#,members,status,sum(bytes/1024/1024) from v$log
         group by group#,members,status;
# To add a redolog file group
SQL> alter database add logfile group 4 (‘/u02/prod/redo04a.log’,’/u02/prod/redo04b.log’) size  
         50m;
# To add a redolog member
SQL> alter database add logfile member ‘/u02/prod/redo01b.log’ to group 1;
# To drop a redolog group
SQL> alter database drop logfile group 4;
# To drop a redolog member
SQL> alter database drop logfile member ‘/u02/prod/redo01b.log’;
Note: Even after we drop logfile group or member, still file will exists at OS level.
Note: We cannot drop a member or a group which is in CURRENT status.
# Resuing a member
SQL> alter database add logfile member ‘/u02/prod/redo04a.log’ reuse to group 4;
# Steps to rename (or) relocate a redolog member
SQL> shutdown immediate;
SQL> ! cp /u02/prod/redo01.log /u02/prod/redo01a.log (If relocating, use the source and destination paths)
SQL> startup mount
SQL> alter database rename file ‘/u02/prod/redo01.log’ to ‘/u02/prod/redo01a.log’;
The above command will make server process to update the controlfile with new file name
SQL> alter database open;
Note: We cannot resize a redolog member, instead we need to create new group with required size and drop the old group.
# Handling corrupted redolog file
SQL> alter database clear logfile member ‘/u02/prod/redo01a.log’;
Or
SQL> alter database clear unarchived logfile member ‘/u02/prod/redo01a.log’;
MULTIPLEXING OF CONTROL FILES
  1. Control file contains crucial database information and loss of this file will lead to loss of important data about database. So it is recommended to have multiplexed copies of files in different locations.
  1. If control file is lost in 9i, database may go for force shutdown, where as database will continue to run, if it is 10g version.
COMMANDS
# Steps to multiplex control file using spfile
SQL> show parameter spfile
SQL> show parameter control_files
SQL> alter system set control_files=’/u02/prod/control01.ctl’,’/u02/prod/control02.ctl’,’/u02/prod/control03.ctl’,’/u02/prod/control04.ctl’ scope=spfile;
Here we have added 4th control file. This addition can also be done in different location when implementing OFA
SQL> shutdown immediate
SQL> ! cp /u02/prod/control01.ctl /u02/prod/control04.ctl
SQL> startup
# Steps to multiplex controlfile using pfile
SQL> shutdown immediate
[oracle@pc1 ~] $ cd $ORACLE_HOME/dbs
[oracle@pc1 ~] $ vi initprod.ora
Edit control_files parameter and add new path to it and save the file
[oracle@pc1 ~] cp /u02/prod/control01.ctl /u02/prod/control04.ctl
[oracle@pc1 ~] sqlplus “/ as sysdba”
SQL> startup
Note: we can create a maximum of 8 copies of controlfiles.
Read More:
SELECT STATEMENT PROCESSING
ORACLE 11g DATABASE ARCHITECTURE
SHARED SERVER ARCHITECTURE

Comments

comments

Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *