代码改变世界

RMAN restore fails with ORA-01180: can not create datafile 1

2018-06-15 10:02  潇湘隐者  阅读(2280)  评论(0编辑  收藏  举报

 

最近在验证、测试备份有效性时,遇到了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:34

ORA-01180: can not create datafile 1

ORA-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_1
channel ORA_DISK_1: sid=910 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u04/backup/backupsets/ora_df977962825_s23801_s1 recid=21806 stamp=977962827
Crosschecked 1 objects
 
RMAN> crosscheck backupset 21804;
 
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 06/15/2018 08:21:59
RMAN-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.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       SCM2     4095319980       PARENT  1          05-OCT-12
2       2       SCM2     4095319980       CURRENT 28333829966 28-MAR-18

 

 

那么,我们检查一下告警日志,如下截图所示,因为catalog归档日志时,把不相关的归档日志catalog了,Oracle从归档日志中自动探测到incarnation信息,并重置incarnation,从而导致恢复报错。

 

 

clip_image001[4]

 

 

参考官方文档 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)