天萃荷净
rman从多份备份中还原操作,运维DBA工程师反映数据库在进行恢复时报错ORA-01157 ORA-01110,分析原因为11号数据文件需要recover
1、数据恢复ORA错误
RMAN> shutdown immediate
database dismounted
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/18/ 14:40:40
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/opt/oracle/oradata/test/oracleplus03.dbf'
RMAN> restore datafile 11;
Starting restore at -11-18 14:41:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring datafile 00011
input datafile copy recid=13 stamp=767543949 filename=/tmp/11.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/test/oracleplus03.dbf
ORA-19505: failed to identify file "/tmp/11.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is datafile-copy 13 (/tmp/11.dbf)
ORA-19601: output file is datafile 11 (/opt/oracle/oradata/test/oracleplus03.dbf)
failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/test/oracleplus03.dbf
channel ORA_DISK_1: reading from backup piece /tmp/test_full_01mrkqdh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG1114T124433
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at -11-18 14:41:08
虽然整个过程datafile 11恢复成功了,但是在恢复过程中,先是去读取/tmp/11.dbf的copy文件,读取这个文件失败,然后继续使用/tmp/test_full_01mrkqdh_1_1来恢复数据文件,为什么会出现这样的情况呢?
2、ORA-01157 ORA-01110原因分析
RMAN> list copy of datafile 11;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - ------------------- ---------- ------------------- ----
13 11 A -11-18 14:39:09 11517136 -11-14 12:44:33 /tmp/11.dbf
--确实存在datafile 11的copy文件/tmp/11.dbf,并且有效
--checkpoint time为:-11-14 12:44:33,创建时间为:-11-18 14:39:09
RMAN> list backup of datafile 11;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 9.32G DISK 00:04:24 -11-14 12:48:57
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG1114T124433
Piece Name: /tmp/test_full_01mrkqdh_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
11 Full 11517136 -11-14 12:44:33 /opt/oracle/oradata/test/oracleplus03.dbf
--backupset中也有datafile 11
--checkpoint time为:-11-14 12:44:33,创建时间为:-11-14 12:48:57
--通过比较这两个关于datafile 11的备份时间,rman自动选择了创建时间比较新的备份恢复
RMAN> crosscheck copy of datafile 11;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
validation failed for datafile copy
datafile copy filename=/tmp/11.dbf recid=13 stamp=767543949
Crosschecked 1 objects
RMAN> list copy of datafile 11;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - ------------------- ---------- ------------------- ----
13 11 X -11-18 14:39:09 11517136 -11-14 12:44:33 /tmp/11.dbf
--通过检测发现/tmp/11.dbf是无效的,所以rman继续使用backupset进行恢复datafile 11
在有些时候,当rman对中关于一个对象的备份有多个(备份时间不一致),rman会从最新备份的开始还原,如果第一个失败,使用下一个备份,但是在有些时候可能第一个失败后,rman不自动使用下一个,这个时候的处理思路是:使用crosscheck backup/copy检测无效的备份,删除掉,继续执行
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01157 ORA-01110 数据启动报错RMAN恢复数据库思路