LOSS ALL CONTROLFILE AND AND ADD DATAFILE NO MORE BACKUP AVAILABLE DATABASE ARCHIVELOG MODE
The same scenario but here we have added a new datafile, created a new user after full backup of database along with controlfile and before
deletion of controlfile.
--Database Status is up and running--
Record the count of below tables before taking cold backup
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from emp;
COUNT(*)
----------
14
Step 1: Shut the database with "shut immediate" and take cold backup with some os utility like cp etc.
Step 2: Database is Open and made available to users for daily activities
col name format a50
select name,status from v$datafile;
NAME
--------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
Step 3: Add the datafile as shown below
SQL>alter tablespace users add datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend on;
Tablespace altered.
Step 4: Created a user at database level and grant dba role to user as shown below:
SQL> create user abc identified by abc default tablespace users;
User created.
SQL> grant dba to abc;
Grant succeeded.
Connect to newly created user and create tables in it as shown in below steps
SQL> conn abc
Enter password:
Connected.
SQL> create table emp as select * from scott.emp;
Table created.
Note down the count records of newly created table.
SQL> select count(*) from emp;
COUNT(*)
----------
14
After performing above step some user accidentally deleted all the three controlfile at OS level
rm *.ctl
But Users are still unaware of it and normal operations are being carried out as follows:
SQL> create table dept as select * from scott.dept;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL>commit;
SQL> alter system switch logfile;
Step 5: We can’t cleanly shut the database with “Shut immediate” so need to shut abort as follows,copy the controlfile from backup to its desired location
and mount the database.
SQL>shut abort;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1266608 bytes
Variable Size 100666448 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 499603 generated at 02/03/2014 14:05:03 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_2_838562540.dbf
ORA-00280: change 499603 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 502109 generated at 02/16/2014 03:13:32 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_3_838562540.dbf
ORA-00280: change 502109 for thread 1 is in sequence #3
ORA-00278: log file '/oradb/archive/1_2_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502243 generated at 02/16/2014 03:16:05 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_4_838562540.dbf
ORA-00280: change 502243 for thread 1 is in sequence #4
ORA-00278: log file '/oradb/archive/1_3_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502267 generated at 02/16/2014 03:16:12 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_5_838562540.dbf
ORA-00280: change 502267 for thread 1 is in sequence #5
ORA-00278: log file '/oradb/archive/1_4_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502280 generated at 02/16/2014 03:16:15 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 502280 for thread 1 is in sequence #6
ORA-00278: log file '/oradb/archive/1_5_838562540.dbf' no longer needed for
this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradb/app/oracle/oradata/orcl/users02.dbf'
ORA-01112: media recovery not started
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.
SQL> alter database rename file '/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/oradb/app/oracle/oradata/orcl/users02.dbf';
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506474 generated at 02/16/2014 05:08:37 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 506474 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
ORA-00278: log file '/oradb/archive/1_6_838562540.dbf' no longer needed for
this recovery
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> select name,status from v$datafile;
NAME STATUS
----------------------------------------------------- -------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf SYSTEM
/oradb/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/example01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users02.dbf RECOVER
6 rows selected.
SQL> alter database datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' online;
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SOLUTION:- FOLLOW RESOLVED THE ISSUE
------------------------------------------------------------------------------------------------------------
Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.
SQL> select name, controlfile_type from v$database ;
NAME CONTROL
------------ ----------------
ORCL BACKUP
SQL> select status,resetlogs_change#,resetlogs_time,checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time,
checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ;
STATUS RESETLOGS_CHANGE# RESETLOGS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
---------- ----------------------------- ---------------- ------------------------------ -------------------- ----------
ONLINE 464631 03-FEB-14 506996 16-FEB-2014 05:24:09 6
SQL> -- Check for datafile status, and fuzziness ( It means the datafile more recovery to apply the logs on )
SQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;
STATUS ERROR FUZ COUNT(*)
---------- --------------------------------------------------------- ------- --------------
ONLINE YES 6
NOTE:-
IF MIN Checkpoint & MAX Checkpoint is different performing for more clarification check datafile
header checkpoint are same or not if not then performing datafile recovery
SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;
MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
506996 506996
SQL> select substr(L.GROUP#,1,6) GROUP#,substr(L.THREAD#,1,7) THREAD#,substr(L.SEQUENCE#,1,10) SEQUENCE#,
substr(L.MEMBERS,1,7) MEMBERS,substr(L.ARCHIVED,1,8) ARCHIVED ,substr(L.STATUS,1,10) STATUS,
substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE# ,substr(LF.member,1,60) REDO_LOGFILE
from GV$LOG L,
GV$LOGFILE LF
where L.GROUP# = LF.GROUP# ;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE# REDO_LOGFILE
------ ------- ---------- ------- --- ---------- ---------------- ------------------------------------------------------------
1 1 1 1 YES INACTIVE 464631 /oradb/app/oracle/oradata/orcl/redo01.log
3 1 0 1 YES UNUSED 0 /oradb/app/oracle/oradata/orcl/redo03.log
2 1 2 1 NO CURRENT 495046 /oradb/app/oracle/oradata/orcl/redo02.log
SQL> select min(FHSCN) "LOW FILEHDR SCN"
, max(FHSCN) "MAX FILEHDR SCN"
, max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Comment: see CURRENT redo logfile....
In the above output/sample we see, redo (archivelogs) was applied and datafile 6 was rolled forward but still needs more recovery and redo to be applied.
Database Recovery did not complete, because the online 'current' redolog is not applied automatically with a 'BACKUP' controlfile.
As we use a Backup Controlfile we must 'manually' apply the online 'current' redolog '/oradb/app/oracle/oradata/orcl/redo02.log' having SEQ# 7 ( SCN 464631) !
Additional Note:
As this is recover with a Backup Controlfile, or controlfile create from Tracefile (sql> alter database backup to trace;) ,
query of v$log/v$logfile will possibly not give the correct information, about which logfile contains which sequence number (seq#).
# Options to find the Online log to be used
a: Check the Alert.log file for the last sequences used with 'Online Redolog files'
b: If Alert.log is lost you may simply try all online redolog files, if the wrong logfile is chosen nothing will be applied,
but you will see in in the output message which sequence is in that online redolog file.
Then simply try the next online redolog file until you get 'media recovery complete' message.
c: You may also dump the file log file headers for Online redolog file(s)
Example:
--------
sql> alter system dump logfile '/oradb/app/oracle/oradata/orcl/redo02.log' scn min 1 scn max 1 ;
-- This will write a tracefile with the header dump to your 'trace' (11g) [ or udump (<=10g) ] directory
-- Check the tracefile for similar entry like...
~~~
..
thread: 1 nab: 0x3 seq: 0x00000005 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x31fb72ec scn: 0x0000.000716f7 (464631)
..
Low scn: 0x.....
Next scn: 0x.....
..
~~~
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 5; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo02.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01,log
ORA-00308: cannot open archived log '/oradb/app/oracle/oradata/orcl/redo01,log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 464631 03-FEB-14
3 1 0 52428800 1 YES UNUSED 0
2 1 2 52428800 1 NO CURRENT 495046 03-FEB-14
SQL> alter database open resetlogs ;
Note:
If after applying all archive logs and online redo logs the database does not open
please provide the following script output to Oracle support to assist with the recovery.
( Please upload spooled file: recovery_info.txt )
SQL> set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
NOTE:--- DBA use this own risk after that i dnt knw some data missing or not so before using this match all scn and sequence and verify to all
when database not open resetlog and noresetlog then kick start db
_allow_resetlogs_corruption=true set on pfile the up but then up after that take a full backup because after that instance will be chrashed.
------------------------------------------------------------------------------------------------------------
The same scenario but here we have added a new datafile, created a new user after full backup of database along with controlfile and before
deletion of controlfile.
--Database Status is up and running--
Record the count of below tables before taking cold backup
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from emp;
COUNT(*)
----------
14
Step 1: Shut the database with "shut immediate" and take cold backup with some os utility like cp etc.
Step 2: Database is Open and made available to users for daily activities
col name format a50
select name,status from v$datafile;
NAME
--------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
Step 3: Add the datafile as shown below
SQL>alter tablespace users add datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend on;
Tablespace altered.
Step 4: Created a user at database level and grant dba role to user as shown below:
SQL> create user abc identified by abc default tablespace users;
User created.
SQL> grant dba to abc;
Grant succeeded.
Connect to newly created user and create tables in it as shown in below steps
SQL> conn abc
Enter password:
Connected.
SQL> create table emp as select * from scott.emp;
Table created.
Note down the count records of newly created table.
SQL> select count(*) from emp;
COUNT(*)
----------
14
After performing above step some user accidentally deleted all the three controlfile at OS level
rm *.ctl
But Users are still unaware of it and normal operations are being carried out as follows:
SQL> create table dept as select * from scott.dept;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL>commit;
SQL> alter system switch logfile;
Step 5: We can’t cleanly shut the database with “Shut immediate” so need to shut abort as follows,copy the controlfile from backup to its desired location
and mount the database.
SQL>shut abort;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 205520896 bytes
Fixed Size 1266608 bytes
Variable Size 100666448 bytes
Database Buffers 100663296 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 499603 generated at 02/03/2014 14:05:03 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_2_838562540.dbf
ORA-00280: change 499603 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 502109 generated at 02/16/2014 03:13:32 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_3_838562540.dbf
ORA-00280: change 502109 for thread 1 is in sequence #3
ORA-00278: log file '/oradb/archive/1_2_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502243 generated at 02/16/2014 03:16:05 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_4_838562540.dbf
ORA-00280: change 502243 for thread 1 is in sequence #4
ORA-00278: log file '/oradb/archive/1_3_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502267 generated at 02/16/2014 03:16:12 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_5_838562540.dbf
ORA-00280: change 502267 for thread 1 is in sequence #5
ORA-00278: log file '/oradb/archive/1_4_838562540.dbf' no longer needed for
this recovery
ORA-00279: change 502280 generated at 02/16/2014 03:16:15 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 502280 for thread 1 is in sequence #6
ORA-00278: log file '/oradb/archive/1_5_838562540.dbf' no longer needed for
this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradb/app/oracle/oradata/orcl/users02.dbf'
ORA-01112: media recovery not started
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf
/oradb/app/oracle/oradata/orcl/undotbs01.dbf
/oradb/app/oracle/oradata/orcl/sysaux01.dbf
/oradb/app/oracle/oradata/orcl/users01.dbf
/oradb/app/oracle/oradata/orcl/example01.dbf
/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.
SQL> alter database rename file '/oradb/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' to '/oradb/app/oracle/oradata/orcl/users02.dbf';
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506474 generated at 02/16/2014 05:08:37 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_6_838562540.dbf
ORA-00280: change 506474 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
ORA-00278: log file '/oradb/archive/1_6_838562540.dbf' no longer needed for
this recovery
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> select name,status from v$datafile;
NAME STATUS
----------------------------------------------------- -------------------------
/oradb/app/oracle/oradata/orcl/system01.dbf SYSTEM
/oradb/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/example01.dbf ONLINE
/oradb/app/oracle/oradata/orcl/users02.dbf RECOVER
6 rows selected.
SQL> alter database datafile '/oradb/app/oracle/oradata/orcl/users02.dbf' online;
Database altered.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00328: archived log ends at change 501852, need later change 506996
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SOLUTION:- FOLLOW RESOLVED THE ISSUE
------------------------------------------------------------------------------------------------------------
Please see steps below showing you an example getting the errors like ORA-1194, ORA-1547, ORA-1110,
and how to verify status of Controlfile, Datafiles, Logfiles, and finally recover and open database.
SQL> select name, controlfile_type from v$database ;
NAME CONTROL
------------ ----------------
ORCL BACKUP
SQL> select status,resetlogs_change#,resetlogs_time,checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time,
checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time ;
STATUS RESETLOGS_CHANGE# RESETLOGS CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*)
---------- ----------------------------- ---------------- ------------------------------ -------------------- ----------
ONLINE 464631 03-FEB-14 506996 16-FEB-2014 05:24:09 6
SQL> -- Check for datafile status, and fuzziness ( It means the datafile more recovery to apply the logs on )
SQL> select STATUS, ERROR, FUZZY, count(*) from v$datafile_header group by STATUS, ERROR, FUZZY ;
STATUS ERROR FUZ COUNT(*)
---------- --------------------------------------------------------- ------- --------------
ONLINE YES 6
NOTE:-
IF MIN Checkpoint & MAX Checkpoint is different performing for more clarification check datafile
header checkpoint are same or not if not then performing datafile recovery
SQL> select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header ;
MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
506996 506996
SQL> select substr(L.GROUP#,1,6) GROUP#,substr(L.THREAD#,1,7) THREAD#,substr(L.SEQUENCE#,1,10) SEQUENCE#,
substr(L.MEMBERS,1,7) MEMBERS,substr(L.ARCHIVED,1,8) ARCHIVED ,substr(L.STATUS,1,10) STATUS,
substr(L.FIRST_CHANGE#,1,16) FIRST_CHANGE# ,substr(LF.member,1,60) REDO_LOGFILE
from GV$LOG L,
GV$LOGFILE LF
where L.GROUP# = LF.GROUP# ;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE# REDO_LOGFILE
------ ------- ---------- ------- --- ---------- ---------------- ------------------------------------------------------------
1 1 1 1 YES INACTIVE 464631 /oradb/app/oracle/oradata/orcl/redo01.log
3 1 0 1 YES UNUSED 0 /oradb/app/oracle/oradata/orcl/redo03.log
2 1 2 1 NO CURRENT 495046 /oradb/app/oracle/oradata/orcl/redo02.log
SQL> select min(FHSCN) "LOW FILEHDR SCN"
, max(FHSCN) "MAX FILEHDR SCN"
, max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Comment: see CURRENT redo logfile....
In the above output/sample we see, redo (archivelogs) was applied and datafile 6 was rolled forward but still needs more recovery and redo to be applied.
Database Recovery did not complete, because the online 'current' redolog is not applied automatically with a 'BACKUP' controlfile.
As we use a Backup Controlfile we must 'manually' apply the online 'current' redolog '/oradb/app/oracle/oradata/orcl/redo02.log' having SEQ# 7 ( SCN 464631) !
Additional Note:
As this is recover with a Backup Controlfile, or controlfile create from Tracefile (sql> alter database backup to trace;) ,
query of v$log/v$logfile will possibly not give the correct information, about which logfile contains which sequence number (seq#).
# Options to find the Online log to be used
a: Check the Alert.log file for the last sequences used with 'Online Redolog files'
b: If Alert.log is lost you may simply try all online redolog files, if the wrong logfile is chosen nothing will be applied,
but you will see in in the output message which sequence is in that online redolog file.
Then simply try the next online redolog file until you get 'media recovery complete' message.
c: You may also dump the file log file headers for Online redolog file(s)
Example:
--------
sql> alter system dump logfile '/oradb/app/oracle/oradata/orcl/redo02.log' scn min 1 scn max 1 ;
-- This will write a tracefile with the header dump to your 'trace' (11g) [ or udump (<=10g) ] directory
-- Check the tracefile for similar entry like...
~~~
..
thread: 1 nab: 0x3 seq: 0x00000005 hws: 0x2 eot: 0 dis: 0
resetlogs count: 0x31fb72ec scn: 0x0000.000716f7 (464631)
..
Low scn: 0x.....
Next scn: 0x.....
..
~~~
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 5; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo02.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 506996 generated at 02/16/2014 05:24:09 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 506996 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01,log
ORA-00308: cannot open archived log '/oradb/app/oracle/oradata/orcl/redo01,log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradb/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 52428800 1 YES INACTIVE 464631 03-FEB-14
3 1 0 52428800 1 YES UNUSED 0
2 1 2 52428800 1 NO CURRENT 495046 03-FEB-14
SQL> alter database open resetlogs ;
Note:
If after applying all archive logs and online redo logs the database does not open
please provide the following script output to Oracle support to assist with the recovery.
( Please upload spooled file: recovery_info.txt )
SQL> set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit;
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
NOTE:--- DBA use this own risk after that i dnt knw some data missing or not so before using this match all scn and sequence and verify to all
when database not open resetlog and noresetlog then kick start db
_allow_resetlogs_corruption=true set on pfile the up but then up after that take a full backup because after that instance will be chrashed.
------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment