Database Recovery Types

............/Updation in Progress

Database Crashes.

Here we look at various scenarios where Database Files are corrupted / deleted.

Loss of Redo Log Files

Loss of Data Files

Loss of Control Files

Loss of Redo Files.

1) Check the status of the log files.

col member for a30

set lines 102

col status for a10

select a.group#,to_char(b.first_time,'DDMonYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs

from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1;

Log file could be in any of the five status

Unused : Then Redo log has not been written since its creation.

Current : The one current being written to.

Active : Not being written and is required for instance recovery may or may not be archived

Clearing : After issue of "ALTER DATABASE CLEAR LOGFILE " command

The log file is being recreated as empty log.

Clearing_current : I/O error observer in the log file

Inactive : Log no longer required for instance recovery, may or may not be archived.

Invalid : Damaged Logfile

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.log' TO GROUP 2;

=

alLTER DATABASE CLEAR LOGFILE GROUP 3;

=

_allow_resetlogs_corruption=TRUE

==

select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

==

select CONTROLFILE_CHANGE# from v$database;

=

select group#,sequence#,bytes,first_change#,to_char(first_time,'DD-MM-YY hh24:mi:ss') tim,status from v$log;

Recovering after the loss of Online

Redo Log files.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Check the V$log view for the status

of the Log files.

UNUSED : Then Redo log has not been

writeen since its creation.

Current : The one current being written

Active : Not being written and is

required for instance recovery

may or may not be archived

Clearing : After issue of "

ALTER DATABASE CLEAR LOGFILE " command

The log file is being recreated as empty log.

Clearing_current : I/O errror.

Inactive : Log no longer required for

instance recover, may or may not be

archived.

Invalid : Damaged Logfile

======================

To check the status of the redo log files

select a.group#,to_char(b.first_time,'DDMonYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs

from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1;

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.log' TO GROUP 2

=====

Loss of Redo Files.

If redo log are multiplexed, and if a single member of the corrupted group/groups is accesible then the database continues to function as normal , however error messages are logged in the control file.

You could correct the corrupted log member as follows

1) Drop the damaged member

ALTER DATABASE DROP LOGFILE MEMBER '/PATH/TO/CORRUPTE/LOG MEMBER.LOG';

2) Add new member to the group

ALTER DATABASE ADD LOGFILE MEMBER '/PATH/TO/NEW/MEMBER LOCATIN/MEMBER.LGO';

If Online group is lost

1) First case

The group is inactive

ALTER DATABASE CLEAR LOGFILE GROUP 2;

2) Second Case

The group is active

Redo log is active but not current then issue

"ALTER SYSTEM CHECKPOINT", this makes the above log file

inactive and we could follow the the procedure

for inactive log file.

If the commant "ALTER SYSTEM CHECKPOINT"

doesn't work