............/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 |
Database Technologies > Oracle >