https://blog.csdn.net/weixin_34029949/article/details/85997154
最近在验证、测试备份有效性时,遇到了“ORA-01180: can not create datafile 1”这个错误,顺便结合metalink的官方文档“RMAN restore fails with ORA-01180: can not create datafile 1 (文档 ID 1265151.1)”里面的内容做一个学习、归纳、总结,顺便加深一下理解。
creating datafile fno=1 name=/u01/oradata/SCM2/system01.dbf RMAN-00571: ================================
RMAN-00569: ===== ERROR MESSAGE STACK FOLLOWS RMAN-00571:==========
RMAN-03002: failure of restore command at 06/14/2018 10:28:34ORA-01180: can not create datafile 1ORA-01110: data file 1: ‘/u01/oradata/SCM2/system01.dbf’ 当然这个数据文件可能是system01.dbf,也可能是其它任何数据文件。但是如果它是FILE_ID为1数据文件,那么就是关键问题,因为FILE_ID为1是系统数据文件。它无法在RMAN还原过程中被创建。而它又必须从备份中还原(restore)。出现这个错误呢,要么就是没有可用的备份(no backups available for use),要么是当前的化身(Incarnation) 未正确设置。
官方文档提供下面命令来判别具体原因.
RMAN> list incarnation of database;
RMAN> list backup of datafile 1;
RMAN> list copy of datafile 1;
RMAN> list backup summary;
1: 检查是否没有可用的备份 如下命令所示,你可以检查是否存在可用的备份 RMAN> list backup of datafile 1;
List of Backup Sets=================== BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------21778 Full 2.47G DISK 00:08:28 03-JUN-18 BP Key: 21778 Status: EXPIRED Compressed: YES Tag: TAG20180603T002006 Piece Name: /u04/backup/backupsets/ora_df977790007_s23771_s1 List of Datafiles in backup set 21778 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 28945050985 03-JUN-18 /u01/oradata/SCM2/system01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------21792 Full 2.47G DISK 00:08:21 04-JUN-18 BP Key: 21792 Status: EXPIRED Compressed: YES Tag: TAG20180604T001936 Piece Name: /u04/backup/backupsets/ora_df977876376_s23786_s1 List of Datafiles in backup set 21792 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 28945603859 04-JUN-18 /u01/oradata/SCM2/system01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------21806 Full 2.47G DISK 00:08:33 05-JUN-18 BP Key: 21806 Status: AVAILABLE Compressed: YES Tag: TAG20180605T002025 Piece Name: /u04/backup/backupsets/ora_df977962825_s23801_s1 List of Datafiles in backup set 21806 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 28946703480 05-JUN-18 /u01/oradata/SCM2/system01.dbf RMAN> 如上所示,虽然有两个备份文件状态是EXPIRED,但是还是存在一个可用的备份文件,如果备份的状态全部是EXPIRED,则对备份集键运行crosscheck 查看它是否存在,例如 RMAN> crosscheck backupset 138; 如果存在,状态将会被更新为AVAILABLE。如果在运行crosscheck后状态仍为EXPIRED,则你需要的备份在物理上不存在。 RMAN> crosscheck backupset 21806; allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=910 devtype=DISKcrosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/u04/backup/backupsets/ora_df977962825_s23801_s1 recid=21806 stamp=977962827Crosschecked 1 objects RMAN> crosscheck backupset 21804; using channel ORA_DISK_1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of crosscheck command at 06/15/2018 08:21:59RMAN-06160: no backup pieces found for backup set key: 21804 RMAN> $ ls /u04/backup/backupsets/ora_df977876376_s23786_s1 ls: /u04/backup/backupsets/ora_df977876376_s23786_s1: No such file or directory$ ls /u04/backup/backupsets/ora_df977790007_s23771_s1 ls: /u04/backup/backupsets/ora_df977790007_s23771_s1: No such file or directory$ ls /u04/backup/backupsets/ora_df977962825_s23801_s1 /u04/backup/backupsets/ora_df977962825_s23801_s1$ 2: 检查数据库当前的Incarnation 官方文档提供的例子如下所示 RMAN> list backup of datafile 1;
List of Backup Sets
BS Key Type LV Size Device Type Elapsed Time Completion Time
138 Full 531.25M DISK 00:00:00 13 FEB 2015 14:31:35
BP Key: 136 Status: AVAILABLE Compressed: NO Tag: TAG20150213T143135
Piece Name: /opt/app/oracle/fra/ORA102/backupset/2015_02_13/o1_mf_nnndf_TAG20150213T143135_bftw0r14_.bkp
List of Datafiles in backup set 138
File LV Type Ckp SCN Ckp Time Name
1 Full 25207062 13 FEB 2015 14:31:35 /opt/app/oracle/oradata/ORA102/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
139 Full 531.32M DISK 00:00:00 13 FEB 2015 14:09:34
BP Key: 137 Status: AVAILABLE Compressed: NO Tag: TAG20150213T140934
Piece Name: /opt/app/oracle/fra/ORA102/backupset/2015_02_13/o1_mf_nnndf_TAG20150213T140934_bfttqhh6_.bkp
List of Datafiles in backup set 139
File LV Type Ckp SCN Ckp Time Name
1 Full 25206825 13 FEB 2015 14:09:34 /opt/app/oracle/oradata/ORA102/system01.dbf RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
1 1 ORA102 400119926 CURRENT 1 19 MAR 2012 15:29:58
2 2 ORA102 400119926 ORPHAN 5766931 04 OCT 2012 15:37:51
3 3 ORA102 400119926 ORPHAN 5768164 16 OCT 2012 15:33:09
…
9 9 ORA102 400119926 ORPHAN 25204629 13 FEB 2015 13:03:55
10 10 ORA102 400119926 ORPHAN 25205038 13 FEB 2015 13:35:57
11 11 ORA102 400119926 ORPHAN 25206695 13 FEB 2015 14:09:07
13 13 ORA102 400119926 ORPHAN 25206882 13 FEB 2015 14:16:24
14 14 ORA102 400119926 ORPHAN 25206882 13 FEB 2015 14:43:32
12 12 ORA102 400119926 ORPHAN 25206883 13 FEB 2015 14:30:54 要还原在2015年2月13日14点09分34秒进行的备份标签 (TAG20150213T140934),你必须在执行还原之前将incarnation重置为11 。
RMAN> reset database to incarnation 11; 个人遇到的例子如下,使用Tag为TAG20180605T002025的备份集还原,照理说当前的Incarnation是正确的 RMAN> list backup of datafile 1; using target database control file instead of recovery catalog List of Backup Sets=================== BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- – ---------- ----------- ------------ ---------------21806 Full 2.47G DISK 00:08:33 05-JUN-18 BP Key: 21806 Status: AVAILABLE Compressed: YES Tag: TAG20180605T002025 Piece Name: /u04/backup/backupsets/ora_df977962825_s23801_s1 List of Datafiles in backup set 21806 File LV Type Ckp SCN Ckp Time Name ---- – ---- ---------- --------- ---- 1 Full 28946703480 05-JUN-18 /u01/oradata/SCM2/system01.dbfRMAN> list incarnation of database; List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- — ---------- ----------1 1 SCM2 4095319980 PARENT 1 05-OCT-122 2 SCM2 4095319980 CURRENT 28333829966 28-MAR-18 那么,我们检查一下告警日志,如下截图所示,因为catalog归档日志时,把不相关的归档日志catalog了,Oracle从归档日志中自动探测到incarnation信息,并重置incarnation,从而导致恢复报错。
参考官方文档 RMAN restore database fails ORA-01180: can not create datafile 1 (文档 ID 1573040.1) When a BACKUP controlfile is used with a Flash Recovery Area defined, an implicit crosscheck of the FRA is done and any files found belonging to the database are catalog’d to the controlfile.Archivelogs created after a resetlogs operation will cause a new incarnation to be registered in the controlfile. The new incarnations meant the database backup needed for restore no longer belonged to the current incarnation.
参考资料: RMAN restore fails with ORA-01180: can not create datafile 1 (文档 ID 1265151.1)
RMAN restore database fails ORA-01180: can not create datafile 1 (文档 ID 1573040.1)