合 12c RMAN新特性之recover table（表级别恢复）

发布日期 2022年11月7日 · 已更新 2022年11月18日

0 340 1

Tags： Oracle 表级别恢复 recover table

👉 本文共约20133个字，系统预计阅读时间或需76分钟。

12c中的RECOVER TABLE
Oracle 12c 新特性：RMAN Recover Table 详解
1. Recover Table 和 Table Partitions概述
2 PDB操作示例
2.1 准备测试环境
2.2 RMAN 备份CDB
2.3 恢复数据
3 注意事项
3.1 说明1：PDB操作恢复的数据还在原PDB中生成
3.2 说明2: RMAN-05112: table "LOTUS"."CNDBA" already exists
3.3 说明3：ORA-01516: nonexistent log file 问题
3.4 说明4：non-CDB与PDB的区别
Oracle 12C 新特性之恢复表
参考

12c中的RECOVER TABLE

在RMAN中提供了表级别恢复（RECOVER TABLE）。在Oracle 12c中，在发生drop或truncate的情况下，可以从RMAN备份种将一个特定的表或分区恢复到某个时间点、SCN或归档序列号，并且可以有下面的选择：

l 使用REMAP选项将表恢复为一个新表或者分区中，也可以恢复到其他用户中。

l 只生成一个需要被恢复表的expdp格式的dump文件，选择后期再进行恢复。

Oracle 12c的Recover Table新特性是利用创建辅助临时实例加数据泵工具来实现的。通常在进行Recover Table之前应该准备好两个目录（AUXILIARY DESTINATION和DATAPUMP DESTINATION），AUXILIARY DESTINATION用来临时存放辅助实例的数据文件，DATAPUMP DESTINATION用来临时存放数据泵导出的文件。

只要之前创建了RMAN备份，那么就可以根据指定的的时间来进行表级和表分区级的恢复操作，而且不影响其他的数据库对象。RMAN的表级和表分区级恢复可以使用在如下场景：

①　在恢复小表或数据库中的某几张表时，但发现使用Restore Database或Tablespace的代价很高而且效率很低。也可以使用TSPITR（表空间基于时间点的恢复）的方法，但该方法效率很低，因为需要移动表空间中的所有对象。

②　恢复有逻辑损坏或者被删除的表。

③　Flashback Table不可用，例如Undo数据已经被覆盖的情况。

④　DDL操作后需要恢复数据。Flashback Table不支持表结构发生改变后的回退，例如TRUNCATE TABLE。

RMAN从备份中自动处理恢复表或者表分区时会执行如下步骤：

1.判断哪些备份包含需要恢复的表或表分区，然后根据指定的时间来进行恢复。

2.判断目标主机上是否有足够的空间来创建auxiliary instance，该实例用于处理表或分区的恢复。如果需要的空间不足，那么RMAN会报错并退出恢复操作。

3.创建auxiliary database，并根据指定的时间来恢复指定的表或表分区到auxiliary database中。辅助数据库的数据文件位置可以在命令中指定。

4.创建包含恢复表或表分区的数据泵文件（expdp dump file）。数据泵的名称和位置也可以在命令中指定。

5.（可选操作）将上一步生产的数据泵文件导入到目标实例中。当然也可以选择不导入，如果选择不导入就必须使用impdp手工导入。

6.（可选操作）在目标数据库中rename恢复表或表分区。

关于RECOVER TABLE需要注意的几个问题：

l 目标数据库必须被置于读写模式。

l 目标数据库必须被置于归档模式。

l 如果要恢复表或者分区，你必须拥有这些表或者分区存在后的时间的备份。

l 想要恢复单个表分区，COMPATIBLE初始化参数所在的目标库必须设置为11.1.0或以上。

l SYS用户下的表或分区无法恢复。

l 存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复。

l Standby数据库上的表或表分区不能进行恢复。

l 在使用REMAP的情况下，有NOT NULL 约束的表不能进行恢复。

l 确保对于辅助数据库在文件系统下有足够的可用空间，同时对数据泵文件也有同样保证。

l 必须要存在一份完整的数据库备份，至少要有SYSTEM、UNDO、SYSAUX和表所在表空间相关的备份。表误操作可以在数据库备份之前也可以在数据库备份之后。如果恢复的表在PDB中，那么需要备份Root Container的SYSTEM，SYSAUX、UNDO和PDB的SYSTEM、SYSAUX以及包含了要恢复的表的表空间。

l 在存在CDB的情况下，在执行RECOVER TABLE时必须使用sys用户登录，而不能使用“rman target /”进行登录。

在执行“RECOVER TABLE”命令时，可以根据需要在以下三种级别指定时间：

（1）SCN号

（2）Sequence number（日志序列号）

（3）Time：根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间，也可以用SYSDATE，比如"SYSDATE-30"、"to_date('2018-04-09:13:51:48','yyyy-mm-dd hh24:mi:ss')"

“RECOVER TABLE”命令的一般格式为：

RMAN> connect target "username/password as SYSBACKUP"; 

RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…' 

AUXILIARY DESTINATION '/u01/tablerecovery' 

DATAPUMP DESTINATION '/u01/dpump' 

DUMP FILE 'tablename.dmp' 

NOTABLEIMPORT -- this option avoids importing the table automatically.(此选项避免自动导入表) 

REMAP TABLE 'username.tablename': 'username.new_table_name'; -- can rename table with this option.(此选项可以对表重命名)

RMAN> connect target "username/password as SYSBACKUP";

RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'

AUXILIARY DESTINATION '/u01/tablerecovery'

DATAPUMP DESTINATION '/u01/dpump'

DUMP FILE 'tablename.dmp'

NOTABLEIMPORT -- this option avoids importing the table automatically.(此选项避免自动导入表)

REMAP TABLE 'username.tablename': 'username.new_table_name'; -- can rename table with this option.(此选项可以对表重命名)

示例1：在PDB中恢复表HR.PDB_EMP,恢复后的表命名为EMP_RECVR

RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB

UNTIL TIME 'SYSDATE-4'

AUXILIARY DESTINATION '/tmp/backups'

REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';

RECOVER TABLE DB12C.T

UNTIL SCN 1932621

AUXILIARY DESTINATION '/tmp/oracle/recover'

REMAP TABLE 'DB12C'.'T':'T_HISTORY_20130717';

RECOVER TABLE LHR.TEST_RT

UNTIL TIME "to_date('2018-04-09:13:51:48','yyyy-mm-dd hh24:mi:ss')"

AUXILIARY DESTINATION '/tmp'

REMAP TABLE 'LHR'.'TEST_RT':'TEST_RT_LHR';

RECOVER TABLE HR.DEPARTMENTS, SH.CHANNELS

UNTIL TIME 'SYSDATE – 1'

AUXILIARY DESTINATION '/tmp/auxdest'

REMAP TABLE hr.departments:example.new_departments, sh.channels:example.new_channels;

RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB

UNTIL TIME 'SYSDATE-4'

AUXILIARY DESTINATION '/tmp/backups'

REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';

RECOVER TABLE DB12C.T

UNTIL SCN 1932621

AUXILIARY DESTINATION '/tmp/oracle/recover'

REMAP TABLE 'DB12C'.'T':'T_HISTORY_20130717';

RECOVER TABLE LHR.TEST_RT

UNTIL TIME "to_date('2018-04-09:13:51:48','yyyy-mm-dd hh24:mi:ss')"

AUXILIARY DESTINATION '/tmp'

REMAP TABLE 'LHR'.'TEST_RT':'TEST_RT_LHR';

RECOVER TABLE HR.DEPARTMENTS, SH.CHANNELS

UNTIL TIME 'SYSDATE – 1'

AUXILIARY DESTINATION '/tmp/auxdest'

REMAP TABLE hr.departments:example.new_departments, sh.channels:example.new_channels;

示例2：从RMAN备份中恢复表SCOTT.EMP,SCOTT.DEPT,并以数据泵格式导出emp_dept_exp_dump.dat，并不进行表的导入

RECOVER TABLE SCOTT.EMP, SCOTT.DEPT

  UNTIL TIME 'SYSDATE-1'

  AUXILIARY DESTINATION '/tmp/oracle/recover'

  DATAPUMP DESTINATION '/tmp/recover/dumpfiles'

  DUMP FILE 'emp_dept_exp_dump.dat'

  NOTABLEIMPORT;

RECOVER TABLE SCOTT.EMP, SCOTT.DEPT

UNTIL TIME 'SYSDATE-1'

AUXILIARY DESTINATION '/tmp/oracle/recover'

DATAPUMP DESTINATION '/tmp/recover/dumpfiles'

DUMP FILE 'emp_dept_exp_dump.dat'

NOTABLEIMPORT;

示例3：恢复表的两个分区，恢复后表分区重新命名并且放置于SALES_PRE_2000_TS表空间

RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999

  UNTIL SEQUENCE 354

  AUXILIARY DESTINATION '/tmp/oracle/recover'

  REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',

       'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999'

  REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';

RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999

UNTIL SEQUENCE 354

AUXILIARY DESTINATION '/tmp/oracle/recover'

REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',

'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999'

REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';

Oracle 12c 新特性：RMAN Recover Table 详解

1. Recover Table 和 Table Partitions概述

Oracle 从12c开始支持在RMAN 中进行表级的恢复操作，即对单表进行recover。官方文档链接如下：

http://docs.oracle.com/database/122/BRADV/rman-recovering-tables-partitions.htm

RMAN的表级和表分区级恢复可以使用在如下场景：

\1. 恢复小表时。也可以使用TSPITR 的方法，但该方法效率很低,因为需要移动表空间中的所有对象。

\2. 恢复有逻辑损坏或者被删除的表。

\3. Flashback Table 不可用，比如undo 数据已经被覆盖。

\4. DDL 操作后需要恢复数据。Flashback Table 不支持表结构发生改变后的回退。比如truncate table。

只要之前创建了RMAN 备份，就可以根据指定的的时间来进行表级和表分区级的恢复操作，而且不影响其他的数据库对象。

可以根据在以下三种级别指定时间：

（1） SCN

（2） Sequence number

（3） Time：根据 NLS_LANG 和 NLS_DATE_FORMAT 环境变量中的格式来指定时间，也可以用SYSDATE，比如 SYSDATE-30.

为了恢复表或者表分区，需要备份undo，SYSTEM,SYSAUX和包含表或者表分区的表空间。

如果恢复的表在PDB中，那么需要备份如下内容：

\1. Root的SYSTEM，SYSAUX 和undo 表空间，SEED，以及包含表的PDB。

\2. 包含表或分区的表空间

当然恢复表或者表分区也有限制条件，以下情况不能使用：

\1. SYS 用户的表或表分区不能进行恢复

\2. SYSTEM 和SYSAUX 表空间中的表或表分区不能进行恢复。

\3. Standby 数据库上的表或表分区不能进行恢复。

\4. 在使用REMAP 的情况下，有NOT NULL 约束的表不能进行恢复。

RMAN从备份中自动处理恢复表或者表分区时会执行如下步骤：

1.判断哪些备份包含需要恢复的表或表分区，然后根据指定的时间来进行恢复。

2.判断目标主机上是否有足够的空间来创建auxiliary instance，该实例用于处理表或分区的恢复。如果需要的空间不足，那么RMAN 会报错并退出恢复操作。

3.创建auxiliary database，并根据指定的时间来恢复指定的表或表分区到auxiliary database中。辅助数据库的数据文件位置可以在命令中指定。

4.创建包含恢复表或表分区的数据泵文件（expdp dump file）。数据泵的名称和位置也可以在命令中指定。

\5. （可选操作）将上一步生产的数据泵文件导入到目标实例中。当然也可以选择不导入，如果选择不导入就必须使用impdp 手工导入。

\6. （可选操作）在目标数据库中rename 恢复表或表分区。

2 PDB操作示例

2.1 准备测试环境

因为特性不支持系统用户和表，所以需要先创建独立的用户和表空间：

oracle@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 16 23:24:44 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

 CON_ID CON_NAME             OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED          READ ONLY NO
     3 DAVE              READ WRITE NO
SQL> alter session set container=DAVE;
Session altered.

SQL> create tablespace lotus datafile '/dave/app/oracle/oradata/cndba/dave/lotus01.dbf' size 20m;
Tablespace created.

SQL> create user lotus identified by lotus default tablespace lotus;
User created.

SQL> grant resource,connect,dba to lotus;
Grant succeeded.

--创建测试表：
SQL> conn lotus/lotus@dave
Connected.
SQL> create table cndba as select * from dba_objects;

Table created.

SQL> select count(1) from cndba;

 COUNT(1)
----------
 72636

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

oracle@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 16 23:24:44 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 DAVE READ WRITE NO

SQL> alter session set container=DAVE;

Session altered.

SQL> create tablespace lotus datafile '/dave/app/oracle/oradata/cndba/dave/lotus01.dbf' size 20m;

Tablespace created.

SQL> create user lotus identified by lotus default tablespace lotus;

User created.

SQL> grant resource,connect,dba to lotus;

Grant succeeded.

--创建测试表：

SQL> conn lotus/lotus@dave

Connected.

SQL> create table cndba as select * from dba_objects;

Table created.

SQL> select count(1) from cndba;

COUNT(1)

----------

72636

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

2.2 RMAN 备份CDB

--使用如下命令备份CDB的组建：ROOT,SEED,PDBS：

RMAN> backup database plus archivelog;

具体过程省略，第一小节已有说明，在PDB 中必须备份ROOT,SEED,和所有的PDBS.

2.3 恢复数据

现在drop cndba 表，然后执行恢复操作：

SQL> conn lotus/lotus@dave
Connected.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2017-04-17 10:20:49

SQL> drop table cndba;

SQL> conn lotus/lotus@dave

Connected.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2017-04-17 10:20:49

SQL> drop table cndba;

注意这里只能是drop，truncate不能recover。如果是truncate，那么就不能执行导入操作。

注意在链接RMAN 时需要连接到root，并且具有SYSDBA 或 SYSBACKUP 权限。

可以使用rman target sys 或 rman target /(The connection is established as the SYS user with SYSDBA privilege)

[oracle@www.cndba.cn cndba]$ mkdir -p /tmp/oracle/recover
[oracle@www.cndba.cn cndba]$ mkdir -p /tmp/recover/dumpfiles

1 2	[oracle@www.cndba.cn cndba]$ mkdir -p /tmp/oracle/recover [oracle@www.cndba.cn cndba]$ mkdir -p /tmp/recover/dumpfiles

恢复过程:还原system,undo,sysaux表空间,然后read only数据库,然后重启数据库还原表所在表空间,然后expdp导出表,根据需要决定是否导入表到原PDB数据库中，最后删除辅助数据库。整个过程对原PDB没有影响。

恢复命令如下：

recover table lotus.cndba of pluggable database dave
 until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')"
 auxiliary destination '/tmp/oracle/recover'
datapump destination '/tmp/recover/dumpfiles'
 dump file 'www.cndba.cn.dat';

recover table lotus.cndba of pluggable database dave

until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')"

auxiliary destination '/tmp/oracle/recover'

datapump destination '/tmp/recover/dumpfiles'

dump file 'www.cndba.cn.dat';

大致过程：

Creating automatic instance, with SID='ajrg' -----这里开始创建auxiliary instance

Starting restore at 17-APR-17 -----先restore database

Starting recover at 17-APR-17 ----再recover

# create directory for datapump import -----使用数据泵导出相关数据

Performing import of tables... -------向target database导入数据

Removing automatic instance

Automatic instance removed ------删除环境

完整log：

RMAN> recover table lotus.cndba of pluggable database dave
 until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')"
 auxiliary destination '/tmp/oracle/recover'
datapump destination '/tmp/recover/dumpfiles'
 dump file 'www.cndba.cn.dat';
2> 3> 4> 5> 
Starting recover at 17-APR-17
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace DAVE:SYSTEM
Tablespace UNDOTBS1
Tablespace DAVE:UNDOTBS1

Creating automatic instance, with SID='ajrg'

initialization parameters used for automatic instance:
db_name=CNDBA
db_unique_name=ajrg_pitr_dave_CNDBA
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/dave/app/oracle
_system_trig_enabled=FALSE
sga_target=2000M
processes=200
db_create_file_dest=/tmp/oracle/recover
log_archive_dest_1='location=/tmp/oracle/recover'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CNDBA

Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 8794696 bytes
Variable Size 503319992 bytes
Database Buffers 1577058304 bytes
Redo Buffers 7979008 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log 
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 17-APR-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=244 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/autobackup/2017_04_17/o1_mf_s_941537994_dh89lcs6_.bkp
channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/autobackup/2017_04_17/o1_mf_s_941537994_dh89lcs6_.bkp tag=TAG20170417T101954
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl
Finished restore at 17-APR-17

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 17-APR-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89jsk0_.bkp
channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89jsk0_.bkp tag=TAG20170417T101904
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp
channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp tag=TAG20170417T101904
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 17-APR-17

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/datafile/o1_mf_system_dh8brn6g_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_system_dh8bs39k_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/datafile/o1_mf_undotbs1_dh8brn8q_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_undotbs1_dh8bs3bb_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/datafile/o1_mf_sysaux_dh8brn7p_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_sysaux_dh8bs37y_.dbf

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'DAVE' "alter database datafile 
 9 online";
sql clone "alter database datafile 4 online";
sql clone 'DAVE' "alter database datafile 
 11 online";
sql clone "alter database datafile 3 online";
sql clone 'DAVE' "alter database datafile 
 10 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "DAVE":"SYSTEM", "UNDOTBS1", "DAVE":"UNDOTBS1", "SYSAUX", "DAVE":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 9 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

Starting recover at 17-APR-17
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc
archived log for thread 1 with sequence 16 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc
archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc thread=1 sequence=15
archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:04
Finished recover at 17-APR-17

sql statement: alter database open read only

contents of Memory Script:
{
sql clone 'alter pluggable database DAVE open read only';
}
executing Memory Script

sql statement: alter pluggable database DAVE open read only

contents of Memory Script:
{
 sql clone "create spfile from memory";
 shutdown clone immediate;
 startup clone nomount;
 sql clone "alter system set control_files = 
 ''/tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl'' comment=
 ''RMAN set'' scope=spfile";
 shutdown clone immediate;
 startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 8794696 bytes
Variable Size 503319992 bytes
Database Buffers 1577058304 bytes
Redo Buffers 7979008 bytes

sql statement: alter system set control_files = ''/tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 8794696 bytes
Variable Size 503319992 bytes
Database Buffers 1577058304 bytes
Redo Buffers 7979008 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 13 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 13;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 17-APR-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=244 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_lotus_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp
channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp tag=TAG20170417T101904
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-APR-17

datafile 13 switched to datafile copy
input datafile copy RECID=17 STAMP=941539307 file name=/tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_lotus_dh8bvbof_.dbf

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone 'DAVE' "alter database datafile 
 13 online";
# recover and open resetlogs
recover clone database tablespace "DAVE":"LOTUS", "SYSTEM", "DAVE":"SYSTEM", "UNDOTBS1", "DAVE":"UNDOTBS1", "SYSAUX", "DAVE":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 13 online

Starting recover at 17-APR-17
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc
archived log for thread 1 with sequence 16 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc
archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc thread=1 sequence=15
archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc thread=1 sequence=16
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-APR-17

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database DAVE open';
}
executing Memory Script

sql statement: alter pluggable database DAVE open

contents of Memory Script:
{
# create directory for datapump import
sql 'DAVE' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/tmp/recover/dumpfiles''";
# create directory for datapump export
sql clone 'DAVE' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/tmp/recover/dumpfiles''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover/dumpfiles''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover/dumpfiles''

Performing export of tables...
 EXPDP> Starting "SYS"."TSPITR_EXP_ajrg_izmu": 
 EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
 EXPDP> . . exported "LOTUS"."CNDBA" 9.613 MB 72623 rows
 EXPDP> Master table "SYS"."TSPITR_EXP_ajrg_izmu" successfully loaded/unloaded
 EXPDP> ******************************************************************************
 EXPDP> Dump file set for SYS.TSPITR_EXP_ajrg_izmu is:
 EXPDP> /tmp/recover/dumpfiles/www.cndba.cn.dat
 EXPDP> Job "SYS"."TSPITR_EXP_ajrg_izmu" successfully completed at Mon Apr 17 10:42:28 2017 elapsed 0 00:00:15
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
 IMPDP> Master table "SYS"."TSPITR_IMP_ajrg_onBg" successfully loaded/unloaded
 IMPDP> Starting "SYS"."TSPITR_IMP_ajrg_onBg": 
 IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
 IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 IMPDP> . . imported "LOTUS"."CNDBA" 9.613 MB 72623 rows
 IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
 IMPDP> Job "SYS"."TSPITR_IMP_ajrg_onBg" successfully completed at Mon Apr 17 10:42:46 2017 elapsed 0 00:00:13
Import completed

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_temp_dh8bsmnd_.tmp deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_temp_dh8bslnq_.tmp deleted
auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/onlinelog/o1_mf_3_dh8bvhz2_.log deleted
auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/onlinelog/o1_mf_2_dh8bvhyt_.log deleted
auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/onlinelog/o1_mf_1_dh8bvhyk_.log deleted
auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_lotus_dh8bvbof_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_sysaux_dh8bs37y_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_sysaux_dh8brn7p_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_undotbs1_dh8bs3bb_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_undotbs1_dh8brn8q_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_system_dh8bs39k_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_system_dh8brn6g_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl deleted
auxiliary instance file www.cndba.cn.dat deleted
Finished recover at 17-APR-17

RMAN>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

RMAN> recover table lotus.cndba of pluggable database dave

until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')"

auxiliary destination '/tmp/oracle/recover'

datapump destination '/tmp/recover/dumpfiles'

dump file 'www.cndba.cn.dat';

2> 3> 4> 5>

Starting recover at 17-APR-17

using channel ORA_DISK_1

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace DAVE:SYSTEM

Tablespace UNDOTBS1

Tablespace DAVE:UNDOTBS1

Creating automatic instance, with SID='ajrg'

initialization parameters used for automatic instance:

db_name=CNDBA

db_unique_name=ajrg_pitr_dave_CNDBA

compatible=12.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/dave/app/oracle

_system_trig_enabled=FALSE

sga_target=2000M

processes=200

db_create_file_dest=/tmp/oracle/recover

log_archive_dest_1='location=/tmp/oracle/recover'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance CNDBA

Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 8794696 bytes

Variable Size 503319992 bytes

Database Buffers 1577058304 bytes

Redo Buffers 7979008 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 17-APR-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=244 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/autobackup/2017_04_17/o1_mf_s_941537994_dh89lcs6_.bkp

channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/autobackup/2017_04_17/o1_mf_s_941537994_dh89lcs6_.bkp tag=TAG20170417T101954

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl

Finished restore at 17-APR-17

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 9 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 11 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 10 to new;

set newname for clone tempfile 1 to new;

set newname for clone tempfile 3 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 9, 4, 11, 3, 10;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 3 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 17-APR-17

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/oracle/recover/CNDBA/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89jsk0_.bkp

channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89jsk0_.bkp tag=TAG20170417T101904

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00009 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00011 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00010 to /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp

channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp tag=TAG20170417T101904

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 17-APR-17

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/datafile/o1_mf_system_dh8brn6g_.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=11 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_system_dh8bs39k_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/datafile/o1_mf_undotbs1_dh8brn8q_.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=13 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_undotbs1_dh8bs3bb_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=14 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/datafile/o1_mf_sysaux_dh8brn7p_.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=15 STAMP=941539242 file name=/tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_sysaux_dh8bs37y_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone 'DAVE' "alter database datafile

9 online";

sql clone "alter database datafile 4 online";

sql clone 'DAVE' "alter database datafile

11 online";

sql clone "alter database datafile 3 online";

sql clone 'DAVE' "alter database datafile

10 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "DAVE":"SYSTEM", "UNDOTBS1", "DAVE":"UNDOTBS1", "SYSAUX", "DAVE":"SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 9 online

sql statement: alter database datafile 4 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

Starting recover at 17-APR-17

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc

archived log for thread 1 with sequence 16 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc

archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc thread=1 sequence=15

archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc thread=1 sequence=16

media recovery complete, elapsed time: 00:00:04

Finished recover at 17-APR-17

sql statement: alter database open read only

contents of Memory Script:

{

sql clone 'alter pluggable database DAVE open read only';

}

executing Memory Script

sql statement: alter pluggable database DAVE open read only

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 8794696 bytes

Variable Size 503319992 bytes

Database Buffers 1577058304 bytes

Redo Buffers 7979008 bytes

sql statement: alter system set control_files = ''/tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2097152000 bytes

Fixed Size 8794696 bytes

Variable Size 503319992 bytes

Database Buffers 1577058304 bytes

Redo Buffers 7979008 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 13 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 13;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 17-APR-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=244 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00013 to /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_lotus_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp

channel ORA_AUX_DISK_1: piece handle=/dave/app/oracle/recovery_area/CNDBA/4C0212A2559073AFE05515D212634C11/backupset/2017_04_17/o1_mf_nnndf_TAG20170417T101904_dh89k8lc_.bkp tag=TAG20170417T101904

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 17-APR-17

datafile 13 switched to datafile copy

input datafile copy RECID=17 STAMP=941539307 file name=/tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_lotus_dh8bvbof_.dbf

contents of Memory Script:

{

# set requested point in time

set until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')";

# online the datafiles restored or switched

sql clone 'DAVE' "alter database datafile

13 online";

# recover and open resetlogs

recover clone database tablespace "DAVE":"LOTUS", "SYSTEM", "DAVE":"SYSTEM", "UNDOTBS1", "DAVE":"UNDOTBS1", "SYSAUX", "DAVE":"SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 13 online

Starting recover at 17-APR-17

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc

archived log for thread 1 with sequence 16 is already on disk as file /dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc

archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_15_dh89l7vk_.arc thread=1 sequence=15

archived log file name=/dave/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_16_dh89or10_.arc thread=1 sequence=16

media recovery complete, elapsed time: 00:00:00

Finished recover at 17-APR-17

database opened

contents of Memory Script:

{

sql clone 'alter pluggable database DAVE open';

}

executing Memory Script

sql statement: alter pluggable database DAVE open

contents of Memory Script:

{

# create directory for datapump import

sql 'DAVE' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/tmp/recover/dumpfiles''";

# create directory for datapump export

sql clone 'DAVE' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/tmp/recover/dumpfiles''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover/dumpfiles''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_ajrg_izmu":

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> . . exported "LOTUS"."CNDBA" 9.613 MB 72623 rows

EXPDP> Master table "SYS"."TSPITR_EXP_ajrg_izmu" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_ajrg_izmu is:

EXPDP> /tmp/recover/dumpfiles/www.cndba.cn.dat

EXPDP> Job "SYS"."TSPITR_EXP_ajrg_izmu" successfully completed at Mon Apr 17 10:42:28 2017 elapsed 0 00:00:15

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_ajrg_onBg" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_ajrg_onBg":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "LOTUS"."CNDBA" 9.613 MB 72623 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_ajrg_onBg" successfully completed at Mon Apr 17 10:42:46 2017 elapsed 0 00:00:13

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_temp_dh8bsmnd_.tmp deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_temp_dh8bslnq_.tmp deleted

auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/onlinelog/o1_mf_3_dh8bvhz2_.log deleted

auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/onlinelog/o1_mf_2_dh8bvhyt_.log deleted

auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/onlinelog/o1_mf_1_dh8bvhyk_.log deleted

auxiliary instance file /tmp/oracle/recover/AJRG_PITR_DAVE_CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_lotus_dh8bvbof_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_sysaux_dh8bs37y_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_sysaux_dh8brn7p_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_undotbs1_dh8bs3bb_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_undotbs1_dh8brn8q_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/4C0212A2559073AFE05515D212634C11/datafile/o1_mf_system_dh8bs39k_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/datafile/o1_mf_system_dh8brn6g_.dbf deleted

auxiliary instance file /tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh8brc7q_.ctl deleted

auxiliary instance file www.cndba.cn.dat deleted

Finished recover at 17-APR-17

RMAN>

3 注意事项

3.1 说明1：PDB操作恢复的数据还在原PDB中生成

上面的示例是在PDB中演示的。操作对象是单表，实际上对分区表中的单个分区也可以进行类似操作。

SQL> show con_name

CON_NAME
------------------------------
DAVE
SQL> select count(1) from cndba;

 COUNT(1)
----------
 72623

恢复的表还在原PDB中，中间产生的辅助数据库会在操作结束后删除。

SQL> show con_name

CON_NAME

------------------------------

DAVE

SQL> select count(1) from cndba;

COUNT(1)

----------

72623

恢复的表还在原PDB中，中间产生的辅助数据库会在操作结束后删除。

3.2 说明2: RMAN-05112: table "LOTUS"."CNDBA" already exists

在PDB测试中 , 最开始的操作的时候，是对cndba表进行truncate 操作的。但是执行recover时报错。提示表已经存在，尝试remap，并未成功。后来drop 掉表后成功recovoer。

当然也使用导出，添加notableimport参数不执行导入操作，然后在手工处理。

RMAN> recover table lotus.cndba of pluggable database dave
 until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')"
 auxiliary destination '/tmp/oracle/recover'
datapump destination '/tmp/recover/dumpfiles'
remap table 'lotus'.' cndba':'cndba_2> 3> 4> 5> new';
 dump file 'www.cndba.cn.dat';

Starting recover at 17-APR-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/17/2017 10:33:20
RMAN-05063: Cannot recover specified tables
RMAN-05112: table "LOTUS"."CNDBA" already exists

RMAN> recover table lotus.cndba of pluggable database dave

until time "to_date('2017-04-17 10:20:49','yyyy-mm-dd hh24:mi:ss')"

auxiliary destination '/tmp/oracle/recover'

datapump destination '/tmp/recover/dumpfiles'

remap table 'lotus'.' cndba':'cndba_2> 3> 4> 5> new';

dump file 'www.cndba.cn.dat';

Starting recover at 17-APR-17

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/17/2017 10:33:20

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "LOTUS"."CNDBA" already exists

3.3 说明3：ORA-01516: nonexistent log file 问题

第一次模拟的时候，recover 时报了数据文件不存在的问题。实际上这个数据文件是我自己创建的。后来换了个环境，重新备份后正常。推测之前其他的rman 备份导致的异常。

auxiliary instance file /tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh79n58l_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/17/2017 01:16:25
RMAN-03015: error occurred in stored script Memory Script
RMAN-20505: create datafile during recovery
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 17 offline
ORA-01516: nonexistent log file, data file, or temporary file "17" in the current container
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_19_dh79h697_.arc'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 17: '/u01/app/oracle/oradata/cndba/dave/lotus01.dbf'

auxiliary instance file /tmp/oracle/recover/CNDBA/controlfile/o1_mf_dh79n58l_.ctl deleted

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/17/2017 01:16:25

RMAN-03015: error occurred in stored script Memory Script

RMAN-20505: create datafile during recovery

RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 17 offline

ORA-01516: nonexistent log file, data file, or temporary file "17" in the current container

RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/recovery_area/CNDBA/archivelog/2017_04_17/o1_mf_1_19_dh79h697_.arc'

ORA-00283: recovery session canceled due to errors

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

ORA-01110: data file 17: '/u01/app/oracle/oradata/cndba/dave/lotus01.dbf'

3.4 说明4：non-CDB与PDB的区别

官网文档里还有另外一种写法，就是不带PDB 名称的。在CDB架构中测试的时候，会一直报RMAN-05057的错误：

recover table "c##lotus2".cndba 
 until time "to_date('2017-04-17 11:03:49','yyyy-mm-dd hh24:mi:ss')"
 auxiliary destination '/tmp/oracle/recover'
datapump destination '/tmp/recover/dumpfiles'
 dump file 'www.cndba.cn.dat';

但尝试恢复一直报表不存在的错误：
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/17/2017 11:35:55
RMAN-05063: Cannot recover specified tables
RMAN-05057: Table c##lotus2.CNDBA not found

recover table "c##lotus2".cndba

until time "to_date('2017-04-17 11:03:49','yyyy-mm-dd hh24:mi:ss')"

auxiliary destination '/tmp/oracle/recover'

datapump destination '/tmp/recover/dumpfiles'

dump file 'www.cndba.cn.dat';

但尝试恢复一直报表不存在的错误：

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/17/2017 11:35:55

RMAN-05063: Cannot recover specified tables

RMAN-05057: Table c##lotus2.CNDBA not found

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： Oracle 表级别恢复 recover table

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 12c RMAN新特性之recover table（表级别恢复）

12c中的RECOVER TABLE

Oracle 12c 新特性：RMAN Recover Table 详解

1. Recover Table 和 Table Partitions概述

2 PDB操作示例

2.1 准备测试环境

2.2 RMAN 备份CDB

2.3 恢复数据

3 注意事项

3.1 说明1：PDB操作恢复的数据还在原PDB中生成

3.2 说明2: RMAN-05112: table "LOTUS"."CNDBA" already exists

3.3 说明3：ORA-01516: nonexistent log file 问题

3.4 说明4：non-CDB与PDB的区别

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 12c RMAN新特性之recover table（表级别恢复）

12c中的RECOVER TABLE

Oracle 12c 新特性：RMAN Recover Table 详解

1. Recover Table 和 Table Partitions概述

2 PDB操作示例

2.1 准备测试环境

2.2 RMAN 备份CDB

2.3 恢复数据

3 注意事项

3.1 说明1：PDB操作恢复的数据还在原PDB中生成

3.2 说明2: RMAN-05112: table "LOTUS"."CNDBA" already exists

3.3 说明3：ORA-01516: nonexistent log file 问题

3.4 说明4：non-CDB与PDB的区别

相关文章

您可能还喜欢...

使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c

Oracle的内存列存储介绍

Oracle rac安装报错 multiple user has uid 0

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复