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;
}
No comments:
Post a Comment