The database recovery case in the case of the backup information in the Control File

xiaoxiao2021-03-06 19

The on-site person reports that the user (DROP user) is misused, and the incomplete recovery of the operation document is used as the operation document, but the error message is displayed:

RMAN-03002: Failure During Compiration of Command

RMAN-03013: Command Type: Restore

RMAN-03002: Failure During Compiration of Command

RMAN-03013: Command Type: IRESTORE

RMAN-06026: Some Targets Not Found - Aborting Restore

RMAN-06023: No Backup or Copy of DataFile 13 Found to Restore

Rman-06023: No Backup or Copy of DataFile 12 Found to Restore

Rman-06023: No Backup or Copy of DataFile 11 Found to Restore

RMAN-06023: No Backup or copy of datafile 10 found to restore

Rman-06023: No Backup or Copy of DataFile 9 Found to Restore

Rman-06023: No Backup or Copy of DataFile 8 Found to Restore

Rman-06023: No Backup or Copy of DataFile 7 Found to Restore

Rman-06023: No Backup or Copy of DataFile 6 Found to Restore

Rman-06023: No Backup or Copy of DataFile 5 Found to Restore

Rman-06023: No Backup or Copy of DataFile 4 Found to Restore

Rman-06023: No Backup or Copy of DataFile 3 Found to Restore

Rman-06023: No Backup or Copy of DataFile 2 Found to Restore

Data file backup available in RESTORE DATABASE? So the List backup, the results, found that there is no real-free version of the 0-level backup.

First explain the backup strategy used by the customer:

AT 5:00 / Every: Friday CMD / C E: /oracle/oradata.bak/rman/b_db_inc0.bat

AT 5:00 / Every: Saturday cmd / c e: /oracle/oradata.bak/rman/b_db_inc2.bat

AT 5:00 / Every: Sunday CMD / C E: /oracle/oradata.bak/rman/b_db_inc2.bat

AT 5:00 / Every: Monday CMD / C E: /oracle/oradata.bak/rman/b_db_inc2.bat

AT 5:00 / Every: Tuesday CMD / C E: /oracle/oradata.bak/rman/b_db_inc1.bat

AT 5:00 / Every: Wednesday cmd / c e: /oracle/oradata.bak/rman/b_db_inc2.bat

AT 5:00 / Every: Thursday cmd / c e: /oracle/oradata.bak/rman/b_db_inc2.bat

AT 6:00 / EVERY: FRIDAY CMD / C E: /Oracle/oradata.bak/rman/b_del_archive.bat Every Friday, 5 o'clock in the morning of the 0th, 2nd-level backups on Saturday to Monday, Tuesday, Tuesday, Wednesday and Thursday made a 2-level backup.

View the files generated in the backup path:

2004-12-31 05:04 2,147,475,968 db0_qfiidb_159_1_546325203

2004-12-31 05:08 2,147,475,968 db0_qfiidb_159_2_546325203

2004-12-31 05:09 425, 230, 848 dB0_QFIIDB_159_3_546325203

2005-01-01 05:04 149, 479, 936 dB2_QFIIDB_160_1_546411603

2005-01-02 05:04 179, 380, 736 dB2_QFIIDB_161_1_546498003

2005-01-03 05:04 179, 945, 984 dB2_QFIIDB_162_1_546584403

2005-01-04 05:04 298, 500, 608 db1_qfiidb_163_1_546670803

2005-01-05 05:04 281, 788, 928 dB2_QFIIDB_164_1_546757203

2005-01-06 05:04 116, 072, 960 dB2_QFIIDB_165_1_546843603

2005-01-08 05:04 300, 474, 880 dB2_QFIIDB_167_1_547016403

2005-01-09 05:04 3,596,800 db2_qfiidb_168_1_547102803

2005-01-10 05:04 3,662,336 db2_qfiidb_169_1_547189205

2005-01-11 05:04 519,537,152 db1_qfiidb_170_1_547275603

2005-01-12 05:04 112, 280, 064 dB2_QFIIDB_171_1_547362003

2005-01-13 05:04 94,863,872 db2_qfiidb_172_1_547448404

2005-01-15 05:04 199,836,160 db2_qfiidb_174_1_547621203

2005-01-16 05:04 72, 442, 368 dB2_QFIIDB_175_1_547707603

2005-01-17 05:04 10,600,960 db2_qfiidb_176_1_547794003

can be seen

2004

year

month

day

Indeed 0-level backup, but

2005

year

month

Seduce

day

with

month

day

The 0-level backup has not been generated. You can see the RMAN backup log file discovers that these two days of 0-level backup reported the error of "insufficient disk space", that is, the idle disk space is not enough to make a 0-level backup, so this The two-day backup is not successful. And it happens that this project has not used Catalog, but only the database's control file is used to store backup information. The database's control_file_record_keep_time is the default 7 days, so it is clear that the backup information in the file has exceeded the recorded save period and is overwritten by subsequent backup information.

The problem then simplifies how if the control file does not contain the available backup information (even if the control file is completely corrupted), how do you restore the RMAN backup database? This requires the DBMS_BACKUP_RESTORE package that does not introduce in the Oracle online document.

About the installation and introduction of the package and some usage methods can see the three of the DBA Work memo written by Fenng: RMAN backup, unused solutions to control file loss.

This article is a recovery example in a real environment, and some stored procedures not mentioned in the Fenng in the article.

1. Close the database

SQL> Shutdown Immediate;

2. Start databases to Nomount status

SQL> Startup Nomount;

3. RESTORE 0-level backup file

Declare

DEVTYPE VARCHAR2 (256);

Done Boolean;

Begin

DevType: = sys.dbms_backup_restore.deviceAllocate (Type => ', Ident =>' T1 ');

Sys.dbms_backup_restore.restoreSetDataFile;

Sys.dbms_backup_restore.restoreDataFileTo (DFNumber => 01, Toname => 'E: /oracle/oradata/qfiidb/system01.dbf');

Sys.dbms_backup_restore.restoreDataFileto (DFNumber => 02, Toname => 'E: /oracle/oradata/qfiidb/rbs01.dbf');

Sys.dbms_backup_restore.restoredaDataFileTo (DFNumber => 03, Toname => 'E: /Oracle/oradata/qfiidb/users01.dbf');

Sys.dbms_backup_restore.restoredAtaFileTo (DFNumber => 04, Toname => 'E: /oracle/oradata/qfiidb/stk_ts01.dbf');

Sys.dbms_backup_restore.restoredataFileTo (DFNumber => 05, Toname => 'E: /oracle/oradata/qfiidb/tools01.dbf');

Sys.dbms_backup_restore.restoredAtaFileTo (DFNumber => 06, Toname => 'E: /oracle/oradata/qfiidb/stk_inx_ts01.dbf');

Sys.dbms_backup_restore.restoreDataFileTo (DFNumber => 07, Toname => 'E: /oracle/oradata/qfiidb/stk_his_ts01.dbf');

sys.dbms_backup_restore.restoredAtaFileTo (DFNumber => 08, Toname => 'E: /oAcle/oradata/qfiidb/stk_his_ind_ts01.dbf');

sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 09, toname => 'E: /ORACLE/ORADATA/QFIIDB/STK_TS02.DBF'); sys.dbms_backup_restore.restoreDatafileTo (dfnumber => 10, toname => 'E: / ORACLE / ORADATA / QFIIDB / STK_INX_TS02.DBF ');

Sys.dbms_backup_restore.restoredAtaFileTo (DFNumber => 11, Toname => 'E: /oracle/oradata/qfiidb/stk_his_ts02.dbf');

Sys.dbms_backup_restore.restoreDataFileto (DFNumber => 12, Toname => 'E: /Oracle/oradata/qfiidb/stk_his_ind_ts02.dbf');

Sys.dbms_backup_restore.restoredAtaFileto (DFNumber => 13, Toname => 'E: /oracle/oradata/qfiidb/stk_ts03.dbf');

Sys.dbms_backup_restore.restorebackuppie (DONE => DONE, HANDE => 'E: /oracle/oradata.bak/db0_qfiidb_159_1_546325203', params => null);

Sys.dbms_backup_restore.restorebackuppiece (DONE => DONE, HANDE => 'E: /oracle/oradata.bak/db0_qfiidb_159_2_546325203', params => null);

Sys.dbms_backup_restore.restorebackuppie (DONE => DONE, HANDE => 'E: /oracle/oradata.bak/db0_qfiidb_159_3_546325203', params => null);

Sys.dbms_backup_restore.devicedeallocate;

END;

4. RESTORE Level 1 Backup File

Declare

DEVTYPE VARCHAR2 (256);

Done Boolean;

Begin

DevType: = sys.dbms_backup_restore.deviceAllocate (Type => ', Ident =>' T1 ');

Sys.dbms_backup_restore.ApplySetDataFile;

Sys.dbms_backup_restore.ApplyDataFileTo (DFNumber => 01, Toname => 'E: /oracle/oradata/qfiidb/system01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 02, Toname => 'E: /oracle/oradata/qfiidb/rbs01.dbf');

sys.dbms_backup_restore.applyDatafileTo (dfnumber => 03, toname => 'E: /ORACLE/ORADATA/QFIIDB/USERS01.DBF'); sys.dbms_backup_restore.applyDatafileTo (dfnumber => 04, toname => 'E: / ORACLE / ORADATA / QFIIDB / STK_TS01.DBF ');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 05, Toname => 'E: /oracle/oradata/qfiidb/tools01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 06, Toname => 'E: /oracle/oradata/qfiidb/stk_inx_ts01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 07, Toname => 'E: /oracle/oradata/qfiidb/stk_his_ts01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 08, Toname => 'E: /oracle/oradata/qfiidb/stk_his_ind_ts01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 09, Toname => 'E: /oracle/oradata/qfiidb/stk_ts02.dbf');

Sys.dbms_backup_restore.ApplyDataFileTo (DFNumber => 10, Toname => 'E: /oracle/oradata/qfiidb/stk_inx_ts02.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 11, Toname => 'E: /oracle/oradata/qfiidb/stk_his_ts02.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 12, Toname => 'E: /oracle/oradata/qfiidb/stk_his_ind_ts02.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNUMBER => 13, Toname => 'E: /oracle/oradata/qfiidb/stk_ts03.dbf');

Sys.dbms_backup_restore.Applybackuppiece (DONE => DONE, HANDE => 'E: /oracle/oradata.bak/db1_qfiidb_170_1_547275603', params => null);

Sys.dbms_backup_restore.devicedeallocate;

END;

5. RESTORE first level 2 backup file

Declare

DEVTYPE VARCHAR2 (256);

Done Boolean;

Begin

DevType: = sys.dbms_backup_restore.deviceAllocate (type => '', Ident => 'T1'); sys.dbms_backup_restore.ApplySetDataFile;

Sys.dbms_backup_restore.ApplyDataFileTo (DFNumber => 01, Toname => 'E: /oracle/oradata/qfiidb/system01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 02, Toname => 'E: /oracle/oradata/qfiidb/rbs01.dbf');

Sys.dbms_backup_restore.ApplyDataFileTo (DFNumber => 03, Toname => 'E: /oracle/oradata/qfiidb/users01.dbf');

Sys.dbms_backup_restore.applydatafileto (DFNumber => 04, Toname => 'E: /Oracle/oradata/qfiidb/stk_ts01.dbf');