Oracle Incomplete Recovery __oracle

Oracle Incomplete recovery means that the recovered data is lost and some data cannot be recovered.

Environment:

Os:rhel 6.0

Db:oracle 10.2.0.1.0

1, first there is a backup of the database here is the full preparation of Rman
[Oracle@rhel ~]$ Rman Target/


Recovery manager:release 10.2.0.1.0-production on Fri APR 27 09:26:26 2012


Copyright (c) 1982, +, Oracle. All rights reserved.


Connected to target Database:orcl10 (dbid=3989340674)


rman> backup Database;
2, create a test table
Sql> CREATE TABLE EMP2 as SELECT * from EMP;
View the current log status
Sql> select * from V$log;


group# thread# sequence# BYTES members ARC STATUS first_change# First_tim
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 1 YES ACTIVE 584685 27-apr-12
2 1 1 52428800 1 YES ACTIVE 584369 27-apr-12
3 1 3 52428800 1 NO current 584693 27-apr-12


3, inserting data, submitting and switching logs
sql> INSERT INTO EMP2 select * from EMP2;


Rows created.


Sql> commit;
sql> alter system switch logfile;
sql> INSERT INTO EMP2 select * from EMP2;


Rows created.


Sql> commit;


Commit complete.
4, Analog breakpoint, lost online Redolog
sql> Shutdown Abort
[Oracle@rhel orcl10]$ RM-RF redo0*
Try to open the database, prompting:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/opt/oracle/product/10.2.0/oradata/orcl10/redo03.log '
Ora-27037:unable to obtain file status
Linux error:2: No such file or directory
Additional Information:3
View the status of the online Redolog log
Sql> select * from V$log;


group# thread# sequence# BYTES members ARC STATUS first_change# First_tim
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 2 52428800 1 YES INACTIVE 584685 27-apr-12
3 1 3 52428800 1 YES ACTIVE 584693 27-apr-12
2 1 4 52428800 1 NO current
Active state indicates that data is not synchronized at restore time
Attempt to recover the database for incomplete recovery
sql> recover database until cancel;
Ora-00279:change 584693 generated at 04/27/2012 09:28:19 for thread 1
Ora-00289:suggestion:/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_3_%u_ . arc
Ora-00280:change 584693 for thread 1 was in sequence #3




Specify log: {<ret>=suggested | AUTO | CANCEL}
Auto
Ora-00279:change 584825 generated at 04/27/2012 09:33:36 for thread 1
Ora-00289:suggestion:/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_4_%u_ . arc
Ora-00280:change 584825 for thread 1 was in sequence #4
Ora-00278:log file '/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_3_ 7so81kbk_.arc ' no longer needed for this recovery




Ora-00308:cannot Open archived log '/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1 _mf_1_4_%u_.arc '
Ora-27037:unable to obtain file status
Linux error:2: No such file or directory
Additional Information:3




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: '/OPT/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10/SYSTEM01.DBF '
The error that follows indicates that there is a data file that is not synchronized
5, save all data files from the most recent backup, dump to the target location after offline files and readonly files
[Oracle@rhel ~]$ Rman Target/


Recovery manager:release 10.2.0.1.0-production on Fri APR 27 09:40:57 2012


Copyright (c) 1982, +, Oracle. All rights reserved.


Connected to target Database:orcl10 (dbid=3989340674, not open)


rman> Restore Database;


6, recover the database
sql> recover database until cancel;
Ora-00279:change 584626 generated at 04/27/2012 09:26:32 for thread 1
Ora-00289:suggestion:/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_1_%u_ . arc
Ora-00280:change 584626 for thread 1 was in sequence #1




Specify log: {<ret>=suggested | AUTO | CANCEL}
Auto
Ora-00279:change 584685 generated at 04/27/2012 09:28:13 for thread 1
Ora-00289:suggestion:/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_2_%u_ . arc
Ora-00280:change 584685 for thread 1 was in sequence #2
Ora-00278:log file '/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_1_ 7so7qh5g_.arc ' no longer needed for this recovery




Ora-00279:change 584693 generated at 04/27/2012 09:28:19 for thread 1
Ora-00289:suggestion:/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_3_%u_ . arc
Ora-00280:change 584693 for thread 1 was in sequence #3
Ora-00278:log file '/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_2_ 7so7qn5h_.arc ' no longer needed for this recovery




Ora-00279:change 584825 generated at 04/27/2012 09:33:36 for thread 1
Ora-00289:suggestion:/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_4_%u_ . arc
Ora-00280:change 584825 for thread 1 was in sequence #4
Ora-00278:log file '/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1_mf_1_3_ 7so81kbk_.arc ' no longer needed for this recovery




Ora-00308:cannot Open archived log '/opt/oracle/product/10.2.0/flash_recovery_area/orcl10/archivelog/2012_04_27/o1 _mf_1_4_%u_.arc '
Ora-27037:unable to obtain file status
Linux error:2: No such file or directory
Additional Information:3
At this time there is no "Ora-01194:file 1 needs more recovery to be consistent" hint, we can open the database.
7, open the database
sql> ALTER DATABASE open resetlogs;


Database altered.
Sql> select * from V$log;


group# thread# sequence# BYTES members ARC STATUS first_change# First_tim
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES unused 0
2 1 1 52428800 1 NO current 584826 27-apr-12
3 1 0 52428800 1 YES unused 0
8, view the data
Sql> Select COUNT (*) from EMP2;


COUNT (*)
----------
28
The data displayed is the data we have before the log is switched, and the log is lost after the log is switched.
9. Incomplete recovery based on point in time
We need to query from the alert log or from the online redolog log to the point at which we need to recover, and transfer the files to the target database.
Perform
Recover database until time ' 2012-04-27 12:46:23 ';
ALTER DATABASE open resetlogs;