Shulou(Shulou.com)06/01 Report--

RMAN recovery cases-incomplete recovery error cases

RMAN recovery case:

Incomplete recovery, due to an error in the previous data file backup, resulting in recovery failure.

[oracle@rh7] $rman target / Recovery Manager: Release 11.2.0.1.0-Production on Sat Sep 20 15:54:25 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: TEST1 (DBID=1195893416) RMAN > run {2 > shutdown immediate;3 > startup mount;4 > set until time '2014-09-20 15-14 49-49-36 sql'alter system switch logfile' 5 > restore database;6 > recover database; 7 > alter database open resetlogs;8 > sql'alter system switch logfile' 9 >} using target database control file instead of recovery catalogdatabase closeddatabase dismountedOracle instance shut downconnected to target database (not started) Oracle instance starteddatabase mountedTotal System Global Area 175767552 bytesFixed Size 1335276 bytesVariable Size 142606356 bytesDatabase Buffers 29360128 bytesRedo Buffers 2465792 bytesexecuting command: SET until clauseStarting restore at 20-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKflashing back control file to SCN 9601534channel ORA_DISK_1: starting datafile Backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/test1/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/test1/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to / u01/app/oracle/oradata/test1/perfs.dbfchannel ORA_DISK_1: restoring datafile 00007 to / u01/app/oracle/oradata/test1/undotbs2.dbfchannel ORA_DISK_1 : restoring datafile 00010 to / u01/app/oracle/oradata/test1/index01.dbfchannel ORA_DISK_1: reading from backup piece / dsk1/backup/test1/TEST1_240.bakchannel ORA_DISK_1: piece handle=/dsk1/backup/test1/TEST1_240.bak tag=TAG20140920T152720channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:35channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00013 to / u01/app/oracle/oradata/test1/tbs_16.dbfchannel ORA_DISK_1: reading from backup piece / dsk1/backup/test1/TEST1_241.bakchannel ORA_DISK_1: piece handle=/dsk1/backup/test1/TEST1_241.bak tag=TAG20140920T152720channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:01channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/test1/system01.dbfchannel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/test1/test1.dbfchannel ORA_DISK_1: restoring datafile 00006 to / u01/app/oracle/oradata/test1/dict1.dbfchannel ORA_DISK_1: reading from backup piece / dsk1/ Backup/test1/TEST1_239.bakchannel ORA_DISK_1: piece handle=/dsk1/backup/test1/TEST1_239.bak tag=TAG20140920T152720channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:45Finished restore at 20-SEP-14Starting recover at 20-SEP-14using channel ORA_DISK_1starting media recovery

Recover failed.

Media recovery failed

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 09/20/2014 15:59:01

RMAN-20505: create datafile during recovery

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

Start until time 'SEP 20 2014 15 49 36'

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile (s) added to control file by media recovery

ORA-01110: data file 8:'/ u01 * app *

View backup information:

RMAN > list backup of database List of Backup Sets=BS Key Type LV Size Device Type Elapsed Time Completion Time--229 Full 158.89M DISK 00:01:18 20-SEP-14 BP Key: 229 Status: AVAILABLE Compressed: NO Tag: TAG20140920T152720 Piece Name: / dsk1/backup/test1/TEST1_240.bak List of Datafiles in backup set 229 File LV Type Ckp SCN Ckp Time Name-2 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/sysaux01.dbf 4 Full 9601534 20-SEP -14 / u01/app/oracle/oradata/test1/users01.dbf 5 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/perfs.dbf 7 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/undotbs2.dbf 10 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/index01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time-- -- 230 Full 1.06m DISK 00:00:01 20-SEP-14 BP Key: 230 Status: AVAILABLE Compressed: NO Tag: TAG20140920T152720 Piece Name: / dsk1/backup/test1/TEST1_241.bak List of Datafiles in backup set 230File LV Type Ckp SCN Ckp Time Name-13 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/tbs_16.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time-- -231 Full 609.73M DISK 00:01:32 20-SEP-14 BP Key: 231 Status: AVAILABLE Compressed: NO Tag: TAG20140920T152720 Piece Name: / dsk1/backup/test1/TEST1_239.bak List of Datafiles in backup set 231 File LV Type Ckp SCN Ckp Time Name-1 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/system01.dbf 3 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/test1.dbf 6 Full 9601534 20-SEP-14 / u01/app/oracle/oradata/test1/dict1.dbf 8 Full 9601534 20-SEP-14 / u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008

Found in the backup:

'/ u01aphort _ oracle_ produce _ 11.2.0 _

Solution:

Set the datafile offline,open database:

16:02:43 SYS@ test1 > alter database datafile 8 offline;Database altered.

Recover database skips this tablespace:

RMAN > recover database skip tablespace tbs2

Starting recover at 20-SEP-14using channel ORA_DISK_1Executing: alter database datafile 8 offlinestarting media recoverymedia recovery complete, elapsed time: 00:00:30Finished recover at 20-SEP-14

View the alarm log:

Alter database datafile 8 offlineCompleted: alter database datafile 8 offlinealter database recover datafile list clearCompleted: alter database recover datafile list clearalter database recover datafile list 1,2,3,4,5,6,7,10, 13Completed: alter database recover datafile list 1,2,3,4,5,6,7,10, 13alter database recover if needed startMedia Recovery StartSerial Media Recovery startedRecovery of Online Redo Log: Thread 1 Group 5 Seq 15 Reading mem 0 Mem# 0: / dsk1/oradata/test1/redo05a.logSat Sep 20 16:06:51 2014Media Recovery Complete (test1) Completed: alter database recover if needed start

Open the database to delete this tablespace:

16:04:42 SYS@ test1 > conn / as sysdba;Connected.16:10:10 SYS@ test1 > alter database open;Database altered.Elapsed: 000015 21.6316 10 SYS@ test1 > select name from v$datafile NAME---/u01/app/oracle/oradata/test1/system01.dbf/u01/app/oracle/oradata/test1/sysaux01.dbf/u01/app/oracle/oradata/test1/test1.dbf/u01/app/oracle/oradata/test1/users01.dbf/u01/app/oracle/oradata/test1/perfs. Dbf/u01/app/oracle/oradata/test1/dict1.dbf/u01/app/oracle/oradata/test1/undotbs2.dbf/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008/u01/app/oracle/oradata/test1/index01.dbf/u01/app/oracle/oradata/test1/tbs_16.dbf10 rows selected.16:11:10 SYS@ test1 > drop tablespace tbs2 including contents and datafiles Tablespace dropped.16:11:32 SYS@ test1 > select name from v$datafile NAME---/u01/app/oracle/oradata/test1/system01.dbf/u01/app/oracle/oradata/test1/sysaux01.dbf/u01/app/oracle/oradata/test1/test1.dbf/u01/app/oracle/oradata/test1/users01.dbf/u01/app/oracle/oradata/test1/perfs. Dbf/u01/app/oracle/oradata/test1/dict1.dbf/u01/app/oracle/oradata/test1/undotbs2.dbf/u01/app/oracle/oradata/test1/index01.dbf/u01/app/oracle/oradata/test1/tbs_16.dbf9 rows selected.

Do a complete re-preparation of the database and do data recovery again!

There was another error in the recovery process:

RMAN > run {2 > shutdown immediate;3 > startup mount;4 > set until time '2014-09-20 16 purl 19 purse 30 devils 5 > restore database;6 > recover database;7 > alter database open resetlogs 8 >} database closeddatabase dismountedOracle instance shut downconnected to target database (not started) Oracle instance starteddatabase mountedTotal System Global Area 175767552 bytesFixed Size 1335276 bytesVariable Size 142606356 bytesDatabase Buffers 29360128 bytesRedo Buffers 2465792 bytesexecuting command: SET until clauseStarting restore at 20-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKflashing back control file to SCN 9603237RMAN-00571: = = RMAN-00569: = ERROR MESSAGE STACK FOLLOWS = RMAN-00571: = RMAN-03002: failure of restore command at 09plash 20max 2014 16:21:46RMAN-06085: must use SET NEWNAME command to restore datafile / u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008

View backup information:

RMAN > list backup List of Backup Sets=BS Key Type LV Size Device Type Elapsed Time Completion Time- 233 Full 164.45M DISK 00:01:04 20-SEP-14 BP Key: 233 Status: AVAILABLE Compressed: NO Tag: TAG20140920T161544 Piece Name: / dsk1/backup/test1/TEST1_244.bak List of Datafiles in backup set 233File LV Type Ckp SCN Ckp Time Name-2 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/sysaux01.dbf 5 Full 9603220 20-SEP -14 / u01/app/oracle/oradata/test1/perfs.dbf 7 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/undotbs2.dbf 10 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/index01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time-- -- 234 Full 1.06m DISK 00:00:02 20-SEP-14 BP Key: 234 Status: AVAILABLE Compressed: NO Tag: TAG20140920T161544 Piece Name: / dsk1/backup/test1/TEST1_245.bak List of Datafiles in backup set 234 File LV Type Ckp SCN Ckp Time Name -13 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/tbs_16.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time-- -235 Full 613.10M DISK 00:01:28 20-SEP-14 BP Key: 235 Status: AVAILABLE Compressed: NO Tag: TAG20140920T161544 Piece Name: / dsk1/backup/test1/TEST1_243.bak List of Datafiles in backup set 235 File LV Type Ckp SCN Ckp Time Name- -1 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/system01.dbf 3 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/test1.dbf 4 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/users01.dbf 6 Full 9603220 20-SEP-14 / u01/app/oracle/oradata/test1/dict1.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time- 236 Full 11.33m DISK 00:00:05 20-SEP-14 BP Key: 236 Status: AVAILABLE Compressed : NO Tag: TAG20140920T161714 Piece Name: / dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkp SPFILE Included: Modification time: 03-SEP-14 SPFILE db_unique_name: TEST1 Control File Included: Ckp SCN: 9603237 Ckp time: 20-SEP-14

Restore controlfile:

RMAN > list backup of controlfile List of Backup Sets=BS Key Type LV Size Device Type Elapsed Time Completion Time--236 Full 11.33M DISK 00:00:05 20-SEP-14 BP Key: 236 Status: AVAILABLE Compressed: NO Tag: TAG20140920T161714 Piece Name: / dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkp Control File Included: Ckp SCN: 9603237 Ckp time: 20-SEP-14 RMAN > shutdown immediate Database dismountedOracle instance shut downRMAN > startup nomount;connected to target database (not started) Oracle instance startedTotal System Global Area 175767552 bytesFixed Size 1335276 bytesVariable Size 142606356 bytesDatabase Buffers 29360128 bytesRedo Buffers 2465792 bytesRMAN > restore controlfile from autobackup Starting restore at 20-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=19 device type=DISKrecovery area destination: / dsk4/backupdatabase name (or database unique name) used for search: TEST1channel ORA_DISK_1: AUTOBACKUP / dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkp found in the recovery areachannel ORA_DISK_1: looking for AUTOBACKUP on day: 20140920channel ORA_DISK_1: restoring control file from AUTOBACKUP / dsk4/backup/TEST1/autobackup/2014_09_ 20/o1_mf_s_858788041_b1tg0gds_.bkpchannel ORA_DISK_1: control file restore from AUTOBACKUP completeoutput file name=/u01/app/oracle/oradata/test1/control01.ctloutput file name=/dsk1/oradata/test1/control02.ctloutput file name=/dsk2/oradata/test1/control03.ctlFinished restore at 20-SEP-14

Redo the restore:

RMAN > run {2 > shutdown immediate;3 > startup mount;4 > set until time '2014-09-20 16 purl 19 purse 30 devils 5 > restore database;6 > recover database;7 > alter database open resetlogs 8 >} Oracle instance shut downconnected to target database (not started) Oracle instance starteddatabase mountedTotal System Global Area 175767552 bytesFixed Size 1335276 bytesVariable Size 142606356 bytesDatabase Buffers 29360128 bytesRedo Buffers 2465792 bytesexecuting command: SET until clauseStarting restore at 20-SEP-14Starting implicit crosscheck backup at 20-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 20-SEP-14Starting implicit crosscheck copy At 20-SEP-14using channel ORA_DISK_1Finished implicit crosscheck copy at 20-SEP-14searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files===File Name: / dsk4/backup/TEST1/autobackup/2014_09_20/o1_mf_s_858788041_b1tg0gds_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858262943_b1bf156d_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s _ 858259416_b1b9lv2j_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858261087_b1bc76lg_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2014_09_14/o1_mf_s_858260002_b1bb58sq_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831135549_97ycp30c_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s _ 831134795_97yc0vk0_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831136532_97ydnsbl_.bkpFile Name: / dsk4/backup/TEST1/autobackup/2013_11_10/o1_mf_s_831135962_97yd2z3r_.bkpusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/test1 / sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00005 to / u01/app/oracle/oradata/test1/perfs.dbfchannel ORA_DISK_1: restoring datafile 00007 to / u01/app/oracle/oradata/test1/undotbs2.dbfchannel ORA_DISK_1: restoring datafile 00010 to / u01/app/oracle/oradata/test1/index01.dbfchannel ORA_DISK_1: reading from backup piece / dsk1/backup/test1/TEST1_244.bakchannel ORA_DISK_1: piece handle=/dsk1/backup/test1/TEST1_244.bak tag=TAG20140920T161544channel ORA _ DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:25channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00013 to / u01/app/oracle/oradata/test1/tbs_16.dbfchannel ORA_DISK_1: reading from backup piece / dsk1/backup/test1/TEST1_245.bakchannel ORA_DISK_1: piece handle=/dsk1/backup/test1/TEST1_245.bak tag=TAG20140920T161544channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:01channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/test1/system01.dbfchannel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/test1/test1.dbfchannel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/test1/users01.dbfchannel ORA_DISK_1: restoring datafile 00006 to / u01 / App/oracle/oradata/test1/dict1.dbfchannel ORA_DISK_1: reading from backup piece / dsk1/backup/test1/TEST1_243.bakchannel ORA_DISK_1: piece handle=/dsk1/backup/test1/TEST1_243.bak tag=TAG20140920T161544channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:55Finished restore at 20-SEP-14Starting recover at 20-SEP-14using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 17 is already on disk as file / dsk1/oradata/test1/redo04a.logarchived log for thread 1 with sequence 18 is already on disk as file / dsk1/oradata/test1/redo05a.logarchived log file name=/dsk1/oradata/test1/redo04a.log thread=1 sequence=17archived log file name=/dsk1/oradata/test1/redo05a.log thread=1 sequence=18media recovery complete Elapsed time: 00:00:08Finished recover at 20-SEP-14Starting recover at 20-SEP-14using channel ORA_DISK_1starting media recoveryarchived log for thread 1 with sequence 17 is already on disk as file / dsk1/oradata/test1/redo04a.logarchived log for thread 1 with sequence 18 is already on disk as file / dsk1/oradata/test1/redo05a.logarchived log file name=/dsk1/oradata/test1/redo04a.log thread=1 sequence=17archived log file name=/dsk1/oradata/test1/redo05a.log thread=1 sequence=18media recovery complete, elapsed time: 00:00:08Finished recover at 20-SEP-14database opened

@ at this point, it has not fully recovered!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.