1.We were going to open DB after shutdown immediate but it went hang.In alert log , we could only notice following message for 40 minutes.

2.Troubleshooting using following queries:-

export NLS_DATE_FORMAT=’dd-mon-yyyy hh24:mi:ss’

set feedback on

set heading on

set echo on

set pagesize 100

set linesize 150

col checkpoint_change# format 999999999999999999999

set echo on

set linesize 400

set pagesize 20000

col name format a75

col MEMBER format a100

col file_name format a100

col PLATFORM_NAME format a30

col TABLESPACE_NAME format a30

set numformat 999999999999999

col hxfil format 9999

col fhsta format 9999

col fhscn format 999999999999999999999999

col fhtmn format a30

col fhrba_Seq format 9999999999999

col FHBCP_THR format 9999

col fhthr format 999

col fhrba_seq format 99999999

col fhscn format 999999999999999999

set linesize 175

set pagesize 50

alter session set nls_date_format = ‘DD-MON-RRRR HH24:MI:SS’;

Spool recover.lst

select name,platform_name,open_mode,controlfile_type,log_mode,flashback_on,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$database;

select name, status from v$datafile;

select name, recover, fuzzy, checkpoint_change#,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$datafile_header;

select * from v$backup;

select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

select GROUP#,member from v$logfile;

select * from v$recover_file;

select * from v$recovery_log;

select hxfil FILE#, fhsta STATUS, fhscn SCN, fhrba_Seq SEQUENCE, fhtnm TABLESPACE, FHBCP_THR Thread, fhafs AFUZZY from x$kcvfh order by hxfil;

— Min PITR ABSSCN is minimun SCN needed to open

select min(FHSCN) “LOW FILEHDR SCN”, max(FHSCN) “MAX FILEHDR SCN”, max(FHAFS) “Min PITR ABSSCN” from X$KCVFH;

— Min max SEQUENCE on file headers

SELECT fhthr thread, fhrba_seq sequence, fhscn scn, fhsta status, count(*) FROM x$kcvfh group by fhthr,fhrba_seq,fhscn,fhsta;

select min(FHRBA_SEQ) “LOW FILEHDR SEQUENCE”, max(FHRBA_SEQ) “MAX FILEHDR SEQUENCE” from X$KCVFH;

select HXFIL File_num,substr(HXFNM,1,140) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH;

SELECT fhthr thread, fhrba_seq sequence, fhscn scn, fhsta status, count(*) FROM x$kcvfh group by fhthr,fhrba_seq,fhscn,fhsta;

3. Please execute following command to check recovery.

RMAN>backup validate check logical database;

SQL>alter system dump logfile ‘/oracle/data/san_oradata01/eocmdb/redofiles/log10b.rdo’ validate;

RMAN>recover database test;

4.Re-creating control file and run recover from RMAN

connect target /;
alter session set tracefile_identifier = “10046_trace_file”;
alter session set events ‘10046 trace name context forever, level 3’;
set echo on;
debug on;
run {
allocate channel d1 type disk;
recover database;
}
debug off;
exit

But this was stuck with following:-

5.Now we need to validate archive log 39597 for consistency.

RMAN> validate archivelog sequence 39597 thread 1;

6.We need to do following if recover stuck

a)Open one more session and shutdown abort the instance

b)Startup mount

c)Try with no parallel recovery

Now, you are applying the redolog named /oracle/data/san_oradata01/eocmdb/redofiles/log13b.log

with sequence 39598

Which is the current redo log:

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
—————- —————- —————- —————- — —————- —————-
8 1 39593 1 YES INACTIVE 12444097046518
9 1 39595 1 YES INACTIVE 12444097257724
10 1 39596 1 YES ACTIVE 12444097354077
15 1 39592 1 YES INACTIVE 12444096949452
12 1 39594 1 YES INACTIVE 12444097140637
13 1 39598 1 NO CURRENT 12444621699598 >>>>>>>>>>>>>>>>>>
14 1 39591 1 YES INACTIVE 12444095658402
11 1 39597 1 YES ACTIVE 12444621392892

SQL> recover database noparallel using backup controlfile;

If it ask for log sequence 39598, then provide the full path as below and then press <Enter>
/oracle/data/san_oradata01/eocmdb/redofiles/log13b.log  (This is current redolog file)

If it says… “Media Recovery complete” then open the db with resetlogs:

SQL> alter database open resetlogs;

Note:-Following views are very useful to monitor instance recovery progress.

Reference:-Monitoring Restore/Recovery Progress (Doc ID 1335910.1)

 Session Waits:-

Are there any sessions in wait and what is it waiting for?

Run the following queries at least 3 times, at 5 minute intervals to see progress/change.