............/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