Oracle Incomplete数据库恢复: 使用restore point

本文主要是介绍Oracle Incomplete数据库恢复: 使用restore point，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

环境: Oracle数据库12.2.0.1，CDB=orclcdb，PDB=orclpdb1

You can’t perform an incomplete database recovery on a subset of your database’s online data files

首先全备数据库：

RMAN> backup database plus archivelog;Starting backup at 25-APR-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=23 STAMP=1006429277
input archived log thread=1 sequence=7 RECID=24 STAMP=1006530361
input archived log thread=1 sequence=8 RECID=25 STAMP=1006530370
input archived log thread=1 sequence=9 RECID=26 STAMP=1006531303
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_annnn_TAG20190425T160144_gd2tc8t7_.bkp tag=TAG20190425T160144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-19Starting backup at 25-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcbc7_.bkp tag=TAG20190425T160146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp tag=TAG20190425T160146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/85ED1CD129746EA8E0530100007FAF27/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcwz4_.bkp tag=TAG20190425T160146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-APR-19Starting backup at 25-APR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=27 STAMP=1006531328
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_annnn_TAG20190425T160208_gd2td0ho_.bkp tag=TAG20190425T160208 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-19Starting Control File and SPFILE Autobackup at 25-APR-19
piece handle=/u01/fra/ORCLCDB/autobackup/2019_04_25/o1_mf_s_1006531329_gd2td290_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-19

然后创建恢复点：

RMAN> create restore point MY_RP;Statement processedRMAN> select current_scn from v$database;CURRENT_SCN
-----------2470073RMAN> select name, scn from v$restore_point;NAME
--------------------------------------------------------------------------------SCN
----------MY_RP2470052

插入一条新数据：

SQL> alter session set container=orclpdb1;Session altered.SQL> select * from foo;FOO
----------12SQL> insert into foo values(3);1 row created.SQL> commit;Commit complete.

进行恢复：

RMAN> shutdown immediate;database closed
database dismounted
Oracle instance shut downRMAN> startup mount;connected to target database (not started)
Oracle instance started
database mountedTotal System Global Area    1207959552 bytesFixed Size                     8792152 bytes
Variable Size                436209576 bytes
Database Buffers             754974720 bytes
Redo Buffers                   7983104 bytesRMAN> restore database until restore point MY_RP;Starting restore at 25-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISKskipping datafile 5; already restored to file /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
skipping datafile 6; already restored to file /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcbc7_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcbc7_.bkp tag=TAG20190425T160146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp tag=TAG20190425T160146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-APR-19RMAN> recover database until restore point MY_RP;Starting recover at 25-APR-19
using channel ORA_DISK_1starting media recovery
media recovery complete, elapsed time: 00:00:01Finished recover at 25-APR-19RMAN> alter database open resetlogs;Statement processedRMAN> alter session set container=orclpdb1;RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 04/25/2019 16:10:50
RMAN-06815: cannot change the container in RMAN session.

验证时间点恢复成功：

SQL> alter session set container=orclpdb1;Session altered.SQL> select * from foo;FOO
----------12

再做一次，这次在PDB中建立restore point:

SQL> show con_name;CON_NAME
------------------------------
ORCLPDB1
SQL> create restore point MY_RP;Restore point created.SQL> insert into foo values(3);1 row created.SQL> select * from foo;FOO
----------312SQL> select current_scn from v$database;CURRENT_SCN
-----------2471694SQL> select name, scn from v$restore_point;NAME
--------------------------------------------------------------------------------SCN
----------
MY_RP2470052MY_RP2471498

目前为止，我们有两个restore point了：

select * from v$restore_point;

以下为输出：
开始恢复，注意是直接连接到PDB：

[oracle@oracle-12201-vagrant ~]$ rman target sys/Abcdef_123456@orclpdb1Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 25 16:27:08 2019Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCLCDB:ORCLPDB1 (DBID=2352397633)RMAN> shutdown immediate;using target database control file instead of recovery catalog
database closedRMAN> startup mount;RMAN> restore database until restore point MY_RP;Starting restore at 25-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISKchannel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp tag=TAG20190425T160146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 25-APR-19RMAN> recover database until restore point MY_RP;Starting recover at 25-APR-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/25/2019 16:28:48
RMAN-07536: command not allowed when connected to a Pluggable Database

restore成功了，但recover失败。因为不允许在PDB中执行。
只好重新连接到根容器，此时recover成功：

[oracle@oracle-12201-vagrant ~]$ rlwrap rman target /Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 25 16:32:22 2019Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCLCDB (DBID=2776037359)RMAN> recover pluggable database orclpdb1 until restore point MY_RP;Starting recover at 25-APR-19
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISKstarting media recoveryarchived log for thread 1 with sequence 10 is already on disk as file /u01/fra/ORCLCDB/archivelog/2019_04_25/o1_mf_1_10_gd2td039_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/fra/ORCLCDB/archivelog/2019_04_25/o1_mf_1_11_gd2tvbq3_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/fra/ORCLCDB/archivelog/2019_04_25/o1_mf_1_1_gd2w5nsx_.arc
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-APR-19

然后打开PDB：

RMAN> alter pluggable database orclpdb1 open resetlogs;Statement processed

验证恢复成功：

SQL> alter session set container=orclpdb1;Session altered.SQL> select * from foo;FOO
----------12

新插入的数据3缺省不见了。

实验结论为：

1. 可以为每一个数据库，包括CDB和PDB创建各自的restore point
2. 可以为单独的数据库进行incomplete恢复

这篇关于Oracle Incomplete数据库恢复: 使用restore point的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---