Add Redo Log Group And Redo Log File Memeber Multiplexing and Recovery

Redo Log Group Add and Redo Log Files Multiplexing and Recovery

Redo Log file Status

Inactive:-    Means Group is not currently access(it is no longer being written to) and checkpoint is also occured. it is no longer required for instance recovery.

Active:-       Means Group is not Currently Access(it is no longer being written to) but checkpoint is not occured(means data is not saved). it is still required for instance recovery.

Current :-    LGWR Process Currently Writing Data to this group.

Unused:-     Group is just now created. not used yet all.

When an checkpoint occurs at that time redo log file status change from active to inactive.

Redo Log File Group Add

SQL> Alter database add logfile group <group_number>;                       

-- it will create two member one in +data disk and another in +flash Disk.

Redo Log File Multiplexing

SQL> Alter database add logfile member '+flash' to group 4;        

  -- it will add one member for logfile to group 4 in flash disk.

Redo Log File Recovery

Steps

  1)   Check the Status of the Logfile Group

 SQL > Select * from v$log;

  2)   If logfile group status is inactive then recover deleted log member by using following query.

SQL> Alter system clear logfile group 2;

And if log group status is not inactive then make it inactive by using following query and then recovery log member by using query.

Switch logfile and occur checkpoint

SQL> Alter system switch logfile;

SQL > Alter system checkpoint;

Check group status and if it is now inactive then recover log member by using following query

SQL> Select * from v$log;

SQL> Alter system clear logfile group 2;

OR

SQL> Alter database drop logfile member 'D:\APP\XP\ORADATA\ORCL1\redo1b.log';

SQL> Alter database add logfile member 'D:\APP\XP\ORADATA\ORCL1\redo1b.log' reuse to group 1;

Drop Redo Log File Member 

SQL> Alter database drop logfile member 'C:\app\Mitesh\flash_recovery_area\orcl\ONLINELOG\redo03a.log';

Drop Redo Log Group 

SQL> Alter database drop logfile group 3;

Log file Group Recovery in case of Archived and not archived

IF Log file is Archived

SQL> Alter database clear logfile group 3;

IF Log file is not Archived

SQL> Alter database clear unarchived logfile group 3;

IF Log file is not Archived and needed for data file.

SQL> Alter database clear unarchived logfile group 3 unrecoverable Datafile;

Recovery of Current Redo Log Group (when Current redo log whole group is lost)

RUN

{

shutdown immediate;

startup mount;

set until sequence enter current seq. no. thread 1;

ALLOCATE CHANNEL c1 TYPE Disk;

Restore DATABASE;

recover database;

ALTER DATABASE OPEN RESETLOGS;

}