【12c】12c RMAN新特性之recover table（表級別恢復）

lhrbest發表於2018-04-09

【12c】12c RMAN新特性之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以及包含了要恢復的表的表空間。


在執行“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.(此選項可以對錶重新命名)

示例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;

 

示例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;

 

示例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';

 




   

https://www.cnblogs.com/andy6/p/6884320.html



http://blog.itpub.net/26753337/viewspace-2107978/




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

1.   Recover Table 和 Table Partitions概述

Oracle 從12c開始支援在RMAN 中進行表級的恢復操作，即對單表進行recover。官方文件連結如下：


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@~]$ 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.


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;

注意這裡只能是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@cndba]$ mkdir -p /tmp/oracle/recover
[oracle@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 '

大致過程：

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 '
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/
 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  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中，中間產生的輔助資料庫會在操作結束後刪除。


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 '
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'


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 '
但嘗試恢復一直報表不存在的錯誤：
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

仔細看了下官網，裡面有提到non-cdb 情況，所以這種不帶pdb名稱的，應該是指這種non-cdb架構的環境。 就是普通的12c 資料庫，非non-cdb 架構。 這裡沒有環境，不再去驗證。

至少有一點可以確認，就是在CDB架構中，對PDB中的表進行操作recover table是沒有問題的。


Oracle 12C 新特性之 恢復表

RMAN的表級和表分割槽級恢復應用場景：

1、You need to recover a very small number of tables to a particular point in time. In this situation, TSPITR is not the most effective solution because it moves all the objects in the tablespace to a specified point in time.
2、You need to recover tables that have been logically corrupted or have been dropped and purged.
3、Flashback Table is not possible because the desired point-in-time is older than available undo.
4、You want to recover data that is lost after a DDL operation modified the structure of tables. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. Flashback Table cannot rewind tables through structural changes such as a truncate table operation.

譯文：
1、您需要將非常少量的表恢復到特定的時間點。在這種情況下，TSPITR 不是最有效的解決方案，因為它將表空間中的所有物件都移動到指定的時間點。
2、您需要恢復已被邏輯損壞或已被刪除和清除的表。
3、Flashback Table 不可用，如undo 資料已經被覆蓋。
4、恢復在DDL操作修改表結構之後丟失的資料。使用Flashback表是不可能的，因為在需要的時間點和當前時間之間的表上執行一個DDL。閃回表不能透過諸如截斷表操作之類的結構更改來倒表。

RMAN的表級和表分割槽級恢復限制：
1、Tables and table partitions belonging to SYS schema cannot be recovered.
2、Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.
3、Tables and table partitions on standby databases cannot be recovered.
4、Tables with named NOT NULL constraints cannot be recovered with the REMAP option.

RMAN的表級和表分割槽級恢復前提：
1、The target database must be in read-write mode.
2、The target database must be in ARCHIVELOG mode.
3、You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.
4、To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.

恢復方法：
1、SCN
2、Time
3、Sequence number

RMAN從備份中自動處理恢復表或者表分割槽時的步驟：

1、Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery.
2、Determines if there is sufficient space on the target host to create the auxiliary instance that will be used during the table or partition recovery process.
If the required space is not available, then RMAN displays an error and exits the recovery operation.
3、Creates an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database.
You can specify the location to which the recovered data files are stored in the auxiliary database.
4、Creates a Data Pump export dump file that contains the recovered tables or table partitions.
You can specify the name and the location of the export dump file used to store the metadata of the recovered tables or table partitions.
5、(Optional) Imports the Data Pump export dump file into the target instance.
You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility.
6、(Optional) Renames the recovered tables or table partitions in the target database.
You can also import recovered objects into a tablespace or schema that is different from the one in which they originally existed.

譯：
1.確定哪些備份包含需要恢復的表或表分割槽，根據指定的時間來進行恢復。
2.確定目標主機上是否有足夠的空間來建立將在表或分割槽恢復過程中使用的輔助例項。 如果需要的空間不足，那麼RMAN會報錯並退出恢復操作。
3.建立一個輔助資料庫並恢復指定的表或表分割槽，並根據指定的時間來恢復指定的表或表分割槽到輔助資料庫中。 可以指定用於儲存已恢復表或表分割槽的後設資料的匯出轉儲檔案的名稱和位置。
4.建立一個資料泵匯出轉儲檔案，其中包含已恢復的表或表分割槽。可以指定用於儲存已恢復表或表分割槽的後設資料的匯出轉儲檔案的名稱和位置。
5. （可選操作）將上一步生產的資料泵檔案匯入到目標例項中。您可以選擇不匯入包含已恢復的表或表分割槽到目標資料庫的匯出轉儲檔案。如果您不匯入匯出轉儲檔案作為恢復過程的一部分，那麼您必須在稍後使用 impdp 手工匯入。
6. （可選操作）在目標資料庫中rename 恢復表或表分割槽。

PDB操作流程：
--  準備測試環境
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> show pdbs
    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED  READ ONLY  NO
3 PDB01  READ WRITE NO
4 PDB02  READ WRITE NO
SQL> alter session set container=pdb01;
Session altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
BBB
6 rows selected.
-- 建立測試使用者
SQL> show con_name
CON_NAME
------------------------------
PDB01
SQL> create user andy identified by andy default tablespace bbb;
User created.
SQL> grant dba to andy;
Grant succeeded.

-- 建立測試表：
SQL> conn andy/andy@10.219.24.16:1521/pdb01 
Connected.
SQL> create table andy(id int);
Table created.
SQL> insert into andy values(1);
1 row created.
SQL> commit;
Commit complete.

SQL> conn / as sysdba
Connected.
SQL>  alter system switch logfile;
System altered.
SQL> /
System altered.
-- RMAN 備份CDB
--使用如下命令備份CDB的組建：ROOT,SEED,PDBS：
[oracle@12c ~]$ rman target /
RMAN> backup database plus archivelog;
Finished Control File and SPFILE Autobackup at 21-MAY-17
說明： 關於 Oracle 12c 多租戶 CDB 與 PDB 備份 請參考 ->http://blog.csdn.net/zhang123456456/article/details/71540927
-- 恢復資料
drop andy purge 表，然後執行恢復操作：
SQL> conn andy/andy@10.219.24.16:1521/pdb01
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    2088202
SQL> drop table andy purge;
Table dropped.
SQL> select * from andy;
ERROR at line 1:
ORA-00942: table or view does not exist
-- 建立輔助目錄
[oracle@12c ~]$ mkdir -p /tmp/oracle/recover
[oracle@12c ~]$ mkdir -p /tmp/recover/dumpfiles
-- 恢復時，cdb 與 pdb 都是Open read writer 狀態。
SQL> show pdbs
    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED  READ ONLY  NO
3 PDB01  READ WRITE NO 
4 PDB02  READ WRITE NO
-- 恢復命令
[oracle@12c ~]$  rman target /
RMAN> 
run{
RECOVER TABLE andy.andy of pluggable database pdb01
UNTIL SCN 2088202
AUXILIARY DESTINATION '/home/oracle/tmp/oracle/recover'
datapump destination '/home/oracle/tmp/recover/dumpfiles';
}

補充：恢復表不支援公共使用者，開始作者使用的是公共使用者做實驗，報錯如下，也沒有很明顯的提示，後換本地使用者沒有這類報錯。
RMAN>recover table c##andy.andy_recover_t of pluggable database pdb01
until scn 2060046
auxiliary destination '/home/oracle/tmp/oracle/recover'
datapump destination  '/home/oracle/tmp/recover/dumpfiles';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "datapump": expecting one of: "advise, allocate, alter, analyze, associate statistics, audit, backup, begin, @, call, catalog, change, comment, commit, configure, connect, convert, copy, create, create catalog, create global, create script, create virtual, crosscheck, declare, delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback, flashback table, grant, grant catalog, grant register, host, import, insert, list, lock, merge, mount, noaudit, open, print, purge, quit, recover, register, release, rename, repair, replace, report, "
RMAN-01007: at line 1 column 1 file: standard input

-- 恢復檢視
SQL> select * from andy;
ID
----------
1   >恢復成功

恢復過程:還原system,undo,sysaux表空間,然後read only資料庫,然後重啟資料庫還原表所在表空間,然後expdp匯出表,根據需要決定是否匯入表到原PDB資料庫中，最後刪除輔助資料庫。 整個過程對原PDB沒有影響。

補充：恢復過程監控
[oracle@12c dumpfiles]$ cd /home/oracle/tmp/oracle/recover
[oracle@12c recover]$ ll
total 8
drwxr-x---. 6 oracle oinstall 4096 May 21 18:26 ANDYCDB
drwxr-x---. 4 oracle oinstall 4096 May 21 18:35 PCAS_PITR_PDB01_ANDYCDB
[oracle@12c recover]$ cd /home/oracle/tmp/recover/dumpfiles
[oracle@12c dumpfiles]$ ll
total 164
-rw-r-----. 1 oracle oinstall 167936 May 21 19:12 tspitr_fgxA_79856.dmp
[root@12c ~]# ps -ef|grep smon
oracle     3838      1  0 17:45 ?        00:00:00 ora_smon_andycdb
oracle     5769      1  0 18:58 ?        00:00:00 ora_smon_fgxA
root       5941   3772  0 19:03 pts/3    00:00:00 grep smon
說明：輔助例項有啟動例項程式fgxA


恢復過程日誌如下：
RMAN> run{
RECOVER TABLE andy.andy of pluggable database pdb01
UNTIL SCN 2088202
AUXILIARY DESTINATION '/home/oracle/tmp/oracle/recover'
datapump destination '/home/oracle/tmp/recover/dumpfiles';
}2> 3> 4> 5> 6> 

Starting recover at 21-MAY-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 PDB01:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB01:UNDOTBS1
Creating automatic instance, with SID='fgxA'
initialization parameters used for automatic instance:
db_name=ANDYCDB
db_unique_name=fgxA_pitr_pdb01_ANDYCDB
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/home/oracle/app/oracle
_system_trig_enabled=FALSE
sga_target=692M
processes=200
db_create_file_dest=/home/oracle/tmp/oracle/recover
log_archive_dest_1='location=/home/oracle/tmp/oracle/recover'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
max_string_size=EXTENDED
#No auxiliary parameter file used
starting up automatic instance ANDYCDB
Oracle instance started
Total System Global Area     725614592 bytes
Fixed Size                     8797008 bytes
Variable Size                205522096 bytes
Database Buffers             507510784 bytes
Redo Buffers                   3784704 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until  scn 2088202;
# 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
Staring restore at 21-MAY-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 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 /home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/c-4182839949-20170521-00
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/c-4182839949-20170521-00 tag=TAG20170521T041813
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/home/oracle/tmp/oracle/recover/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl
Finished restore at 21-MAY-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  scn 2088202;
# 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 /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 21-MAY-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 /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/0ss4p2c8_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/0ss4p2c8_1_1 tag=TAG20170521T041359
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:38
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 /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_undotbs1_%u_.dbf
/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_sysaux_%u_.dbf
2.2.0/dbhome_1/dbs/0ts4p2eu_1_1
1/dbs/0ts4p2eu_1_1 tag=TAG20170521T041359
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 21-MAY-17
datafile 1 switched to datafile copy
ver/ANDYCDB/datafile/o1_mf_system_dl2wqg9o_.dbf
datafile 9 switched to datafile copy
ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_system_dl2wtl6g_.dbf
datafile 4 switched to datafile copy
ver/ANDYCDB/datafile/o1_mf_undotbs1_dl2wqgcc_.dbf
datafile 11 switched to datafile copy
ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_undotbs1_dl2wtlf0_.dbf
datafile 3 switched to datafile copy
ver/ANDYCDB/datafile/o1_mf_sysaux_dl2wqgc0_.dbf
datafile 10 switched to datafile copy
ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_sysaux_dl2wtl06_.db
contents of Memory Script:
{
# set requested point in time
set until  scn 2088202;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'PDB01' "alter database datafile 
 9 online";
sql clone "alter database datafile  4 online";
sql clone 'PDB01' "alter database datafile 
 11 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB01' "alter database datafile 
 10 online";
# recover and open database read only
"UNDOTBS1", "SYSAUX", "PDB01":"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 21-MAY-17
using channel ORA_AUX_DISK_1
starting media recovery
app/oracle/product/12.2.0/dbhome_1/dbs/arch1_20_943753232.dbf
app/oracle/product/12.2.0/dbhome_1/dbs/arch1_21_943753232.dbf
_943753232.dbf thread=1 sequence=20
_943753232.dbf thread=1 sequence=21
media recovery complete, elapsed time: 00:01:32
Finished recover at 21-MAY-17
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database  PDB01 open read only';
}
executing Memory Script
sql statement: alter pluggable database  PDB01 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 = 
nt=
 ''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     725614592 bytes
Fixed Size                     8797008 bytes
Variable Size                205522096 bytes
Database Buffers             507510784 bytes
Redo Buffers                   3784704 bytes
r/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     725614592 bytes
Fixed Size                     8797008 bytes
Variable Size                205522096 bytes
Database Buffers             507510784 bytes
Redo Buffers                   3784704 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until  scn 2088202;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  13 to new;
set newname for datafile  14 to new;
set newname for datafile  15 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  13, 14, 15;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 21-MAY-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 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
f
f
f
2.2.0/dbhome_1/dbs/0ts4p2eu_1_1
1/dbs/0ts4p2eu_1_1 tag=TAG20170521T041359
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 21-MAY-17


datafile 13 switched to datafile copy
x50h8_.dbf
datafile 14 switched to datafile copy
x50l4_.dbf
datafile 15 switched to datafile copy
x500q_.dbf
contents of Memory Script:
{
# set requested point in time
set until  scn 2088202;
# online the datafiles restored or switched
sql clone 'PDB01' "alter database datafile 
 13 online";
sql clone 'PDB01' "alter database datafile 
 14 online";
sql clone 'PDB01' "alter database datafile 
 15 online";
# recover and open resetlogs
TBS1", "PDB01":"UNDOTBS1", "SYSAUX", "PDB01":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile  13 online
sql statement: alter database datafile  14 online
sql statement: alter database datafile  15 online
Starting recover at 21-MAY-17
using channel ORA_AUX_DISK_1
starting media recovery
app/oracle/product/12.2.0/dbhome_1/dbs/arch1_20_943753232.dbf
app/oracle/product/12.2.0/dbhome_1/dbs/arch1_21_943753232.dbf
_943753232.dbf thread=1 sequence=20
_943753232.dbf thread=1 sequence=21
media recovery complete, elapsed time: 00:00:18
Finished recover at 21-MAY-17
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database  PDB01 open';
}
executing Memory Script
sql statement: alter pluggable database  PDB01 open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDB01' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/home/oracle/tmp/recover/dumpfiles''";
# create directory for datapump export
sql clone 'PDB01' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/home/oracle/tmp/recover/dumpfiles''";
}
executing Memory Script
p/recover/dumpfiles''
p/recover/dumpfiles''
Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_fgxA_Fvnl":  
   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/TABLE
 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_fgxA_Fvnl" successfully loaded/unloaded
*****
   EXPDP> Dump file set for SYS.TSPITR_EXP_fgxA_Fvnl is:
   EXPDP>   /home/oracle/tmp/recover/dumpfiles/tspitr_fgxA_79856.dmp
12:59 2017 elapsed 0 00:02:24
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_fgxA_txhb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_fgxA_txhb":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
16:32 2017 elapsed 0 00:01:06
Import completed
Removing automatic instance
Automatic instance removed
31018DB0A1976/datafile/o1_mf_temp_dl2x08jv_.tmp deleted
_dl2wzlwf_.tmp deleted
inelog/o1_mf_3_dl2x6vbp_.log deleted
inelog/o1_mf_2_dl2x6gt1_.log deleted
inelog/o1_mf_1_dl2x6gt1_.log deleted
4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x500q_.dbf deleted
4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x50l4_.dbf deleted
4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x50h8_.dbf deleted
31018DB0A1976/datafile/o1_mf_sysaux_dl2wtl06_.dbf deleted
ux_dl2wqgc0_.dbf deleted
31018DB0A1976/datafile/o1_mf_undotbs1_dl2wtlf0_.dbf deleted
tbs1_dl2wqgcc_.dbf deleted
31018DB0A1976/datafile/o1_mf_system_dl2wtl6g_.dbf deleted
em_dl2wqg9o_.dbf deleted
l2wpytg_.ctl deleted
auxiliary instance file tspitr_fgxA_79856.dmp deleted
Finished recover at 21-MAY-17



oracle@localhost:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Mon Dec 24 01:46:37 2012

Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Current log sequence 33
SQL>
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2263520 bytes
Variable Size 469763616 bytes
Database Buffers 150994944 bytes
Redo Buffers 3305472 bytes
Database mounted.
SQL>
SQL>
SQL>
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

 

SQL> create user c##maclean identified by oracle;

User created.

SQL> grant dba to c##maclean;

Grant succeeded.

SQL>
SQL> conn c##maclean/oracle
Connected.
SQL>
SQL>
SQL>
SQL> create table recoverme as select * from dba_objects;

Table created.

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
———–
2164108

SQL>
SQL> select count(*) from recoverme;

COUNT(*)
———-
89112

SQL>
SQL> delete recoverme where rownum<2000;

1999 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

oracle@localhost:~$ mkdir -p /tmp/oracle/recover
oracle@localhost:~$ mkdir -p /tmp/recover/dumpfiles
RECOVER TABLE “C##MACLEAN”.recoverme
UNTIL SCN 2164108
AUXILIARY DESTINATION ‘/tmp/oracle/recover’
DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
DUMP FILE ‘recover.dat’
NOTABLEIMPORT;

ORA-29283: invalid file operation

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/24/2012 02:47:17
RMAN-06962: Error received during export of metadata
RMAN-06960: EXPDP> ORA-31626: job does not exist
ORA-31633: unable to create master table “SYSBACKUP.TSPITR_EXP_jjFw_trsu”
ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

RECOVER TABLE ‘C##MACLEAN’.’RECOVERME’
UNTIL SCN 2164108
AUXILIARY DESTINATION ‘/tmp/oracle/recover’
REMAP TABLE ‘C##MACLEAN’.’RECOVERME’:’RECOVERME1′;

SQL>
SQL> drop table recoverme;

Table dropped.

SQL> create table recoverme tablespace system as select * from dba_objects;

Table created.

SQL>
SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
———–
2167801

SQL> select count(*) from recoverme;

COUNT(*)
———-
89113

SQL> delete recoverme where rownum<2000;

1999 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from recoverme;

COUNT(*)
———-
87114

RECOVER TABLE “C##MACLEAN”.recoverme
UNTIL SCN 2167801
AUXILIARY DESTINATION ‘/tmp/oracle/recover’
DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
DUMP FILE ‘recover.dat’
NOTABLEIMPORT;

connected to target database: CDB1 (DBID=762218087)

RMAN> RECOVER TABLE “C##MACLEAN”.recoverme
2> UNTIL SCN 2167801
3> AUXILIARY DESTINATION ‘/tmp/oracle/recover’
4> DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
5> DUMP FILE ‘recover.dat’
6> NOTABLEIMPORT;

Starting recover at 24-DEC-12
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
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 UNDOTBS1

Creating automatic instance, with SID=’BxCi’

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=BxCi_pitr_CDB1
compatible=12.0.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
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 CDB1

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2268624 bytes
Variable Size 281018928 bytes
Database Buffers 780140544 bytes
Redo Buffers 5509120 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until scn 2167801;
# 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 24-DEC-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 /u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_ncsnf_TAG20121224T021238_8fhgvdp5_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_ncsnf_TAG20121224T021238_8fhgvdp5_.bkp tag=TAG20121224T021238
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/CDB1/controlfile/o1_mf_8fhlgy7g_.ctl
Finished restore at 24-DEC-12

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 scn 2167801;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
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

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

Starting restore at 24-DEC-12
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/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_nnndf_TAG20121224T021238_8fhgjqd3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_nnndf_TAG20121224T021238_8fhgjqd3_.bkp tag=TAG20121224T021238
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 24-DEC-12

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=802840959 file name=/tmp/oracle/recover/CDB1/datafile/o1_mf_system_8fhlh4w6_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=802840959 file name=/tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_8fhlk5y4_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=802840959 file name=/tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_8fhlh4w8_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 2167801;
# online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 4 online”;
sql clone “alter database datafile 3 online”;
# recover and open database read only
recover clone database tablespace “SYSTEM”, “UNDOTBS1”, “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 4 online

sql statement: alter database datafile 3 online

Starting recover at 24-DEC-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_34_8fhgvg5r_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_35_8fhhpg2f_.arc
archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_36_8fhhr5oz_.arc
archived log for thread 1 with sequence 37 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_37_8fhj8nbh_.arc
archived log for thread 1 with sequence 38 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_38_8fhk741v_.arc
archived log for thread 1 with sequence 39 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_39_8fhlgnor_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_34_8fhgvg5r_.arc thread=1 sequence=34
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_35_8fhhpg2f_.arc thread=1 sequence=35
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_36_8fhhr5oz_.arc thread=1 sequence=36
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_37_8fhj8nbh_.arc thread=1 sequence=37
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_38_8fhk741v_.arc thread=1 sequence=38
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_39_8fhlgnor_.arc thread=1 sequence=39
media recovery complete, elapsed time: 00:00:11
Finished recover at 24-DEC-12

sql statement: alter database open read only

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_8fhlh4w8_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_8fhlk5y4_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_system_8fhlh4w6_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_8fhlh4w8_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_8fhlk5y4_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_system_8fhlh4w6_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/controlfile/o1_mf_8fhlgy7g_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/24/2012 03:23:04
RMAN-05063: Cannot recover specified tables
RMAN-05059: Table C##MACLEAN.RECOVERME resides in tablespace SYSTEM
RMAN-05003: Tablespace Point-in-Time Recovery is not allowed for tablespace SYSTEM
RECOVER TABLE “C##MACLEAN”.recoverme
UNTIL SCN 2164108
AUXILIARY DESTINATION ‘/tmp/oracle/recover’
DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
DUMP FILE ‘recover.dat’
NOTABLEIMPORT;

需要明確使用SYS登入才能成功

oracle@localhost:~$ rman target sys/oracle

Recovery Manager: Release 12.1.0.0.2 – Beta on Mon Dec 24 07:41:36 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDB1 (DBID=762218087)

RMAN> RECOVER TABLE “C##MACLEAN”.recoverme
2> UNTIL SCN 2164108
3> AUXILIARY DESTINATION ‘/tmp/oracle/recover’
4> DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
5> DUMP FILE ‘recover.dat’
6> NOTABLEIMPORT;

Starting recover at 24-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
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 UNDOTBS1

Creating automatic instance, with SID=’npaw’

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=npaw_pitr_CDB1
compatible=12.0.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
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 CDB1

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2268624 bytes
Variable Size 281018928 bytes
Database Buffers 780140544 bytes
Redo Buffers 5509120 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until scn 2164108;
# 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 24-DEC-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 /u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_ncsnf_TAG20121224T021238_8fhgvdp5_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_ncsnf_TAG20121224T021238_8fhgvdp5_.bkp tag=TAG20121224T021238
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/CDB1/controlfile/o1_mf_8fj1t7s0_.ctl
Finished restore at 24-DEC-12

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 scn 2164108;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
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

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

Starting restore at 24-DEC-12
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/CDB1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_nnndf_TAG20121224T021238_8fhgjqd3_.bkp

 

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_nnndf_TAG20121224T021238_8fhgjqd3_.bkp tag=TAG20121224T021238
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 24-DEC-12

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=802856672 file name=/tmp/oracle/recover/CDB1/datafile/o1_mf_system_8fj1thhh_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=802856672 file name=/tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_8fj1w5c5_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=802856672 file name=/tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_8fj1thhj_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 2164108;
# online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 4 online”;
sql clone “alter database datafile 3 online”;
# recover and open database read only
recover clone database tablespace “SYSTEM”, “UNDOTBS1”, “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 4 online

sql statement: alter database datafile 3 online

Starting recover at 24-DEC-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_34_8fhgvg5r_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_35_8fhhpg2f_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_34_8fhgvg5r_.arc thread=1 sequence=34
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_35_8fhhpg2f_.arc thread=1 sequence=35
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-DEC-12

sql statement: alter database 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/CDB1/controlfile/o1_mf_8fj1t7s0_.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 1068937216 bytes

Fixed Size 2268624 bytes
Variable Size 285213232 bytes
Database Buffers 775946240 bytes
Redo Buffers 5509120 bytes

sql statement: alter system set control_files = ”/tmp/oracle/recover/CDB1/controlfile/o1_mf_8fj1t7s0_.ctl” comment= ”RMAN set” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2268624 bytes
Variable Size 285213232 bytes
Database Buffers 775946240 bytes
Redo Buffers 5509120 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until scn 2164108;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 24-DEC-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 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 00006 to /tmp/oracle/recover/NPAW_PITR_CDB1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_nnndf_TAG20121224T021238_8fhgjqd3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2012_12_24/o1_mf_nnndf_TAG20121224T021238_8fhgjqd3_.bkp tag=TAG20121224T021238
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 24-DEC-12

datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=802856763 file name=/tmp/oracle/recover/NPAW_PITR_CDB1/datafile/o1_mf_users_8fj21sqp_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 2164108;
# online the datafiles restored or switched
sql clone “alter database datafile 6 online”;
# recover and open resetlogs
recover clone database tablespace “USERS”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 6 online

Starting recover at 24-DEC-12
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_34_8fhgvg5r_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_35_8fhhpg2f_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_34_8fhgvg5r_.arc thread=1 sequence=34
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2012_12_24/o1_mf_1_35_8fhhpg2f_.arc thread=1 sequence=35
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-DEC-12

database opened

contents of Memory Script:
{
# create directory for datapump import
sql “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/tmp/recover/dumpfiles””;
# create directory for datapump export
sql clone “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_npaw_sAzh”:
EXPDP> Estimate in progress using BLOCKS method…
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 12 MB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported “C##MACLEAN”.”RECOVERME” 9.946 MB 89112 rows
EXPDP> Master table “SYS”.”TSPITR_EXP_npaw_sAzh” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_npaw_sAzh is:
EXPDP> /tmp/recover/dumpfiles/recover.dat
EXPDP> Job “SYS”.”TSPITR_EXP_npaw_sAzh” successfully completed at Mon Dec 24 07:47:08 2012 elapsed 0 00:00:23
Export completed

Not performing table import after point-in-time recovery

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_temp_8fj1z8p6_.tmp deleted
auxiliary instance file /tmp/oracle/recover/NPAW_PITR_CDB1/onlinelog/o1_mf_3_8fj220tk_.log deleted
auxiliary instance file /tmp/oracle/recover/NPAW_PITR_CDB1/onlinelog/o1_mf_2_8fj21zo9_.log deleted
auxiliary instance file /tmp/oracle/recover/NPAW_PITR_CDB1/onlinelog/o1_mf_1_8fj21y5n_.log deleted
auxiliary instance file /tmp/oracle/recover/NPAW_PITR_CDB1/datafile/o1_mf_users_8fj21sqp_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_sysaux_8fj1thhj_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_undotbs1_8fj1w5c5_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/datafile/o1_mf_system_8fj1thhh_.dbf deleted
auxiliary instance file /tmp/oracle/recover/CDB1/controlfile/o1_mf_8fj1t7s0_.ctl deleted
Finished recover at 24-DEC-12

SQL> create directory temp as ‘/tmp/recover/dumpfiles’;

Directory created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@localhost:~$ impdp system/oracle dumpfile=temp:recover.dat

Import: Release 12.1.0.0.2 – Beta on Mon Dec 24 07:51:28 2012

Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 – 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** dumpfile=temp:recover.dat
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “C##MACLEAN”.”RECOVERME” 9.946 MB 89112 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Mon Dec 24 07:51:38 2012 elapsed 0 00:00:09
SQL> select count(*) from “C##MACLEAN”.”RECOVERME” ;

COUNT(*)
———-
89112






About Me

.............................................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub（ http://blog.itpub.net/26736162/abstract/1/ ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人weixin公眾號（ xiaomaimiaolhr ）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址： http://www.cnblogs.com/lhrbest

● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA寶典今日頭條號地址：

.............................................................................................................................................

● QQ群號： 230161599 （滿） 、618766405

● weixin 群：可加我 weixin ，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由

● 於 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成

● 最新修改時間：2018-04-01 06:00 ~ 2018-04-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

.............................................................................................................................................

● 小麥苗的微店 ：

● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用 weixin 客戶端 掃描下面的二維碼來關注小麥苗的 weixin 公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）， 學習最實用的資料庫技術。

小麥苗的微信公眾號 小麥苗的DBA寶典QQ群2 《DBA筆試面寶典》讀者群 小麥苗的微店

   小麥苗的 weixin 公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面試寶典》讀者群       小麥苗的微店

.............................................................................................................................................

【12c】12c RMAN新特性之recover table（表級別恢復）
DBA筆試面試講解群
《DBA寶典》讀者群 歡迎與我聯絡



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2152712/，如需轉載，請註明出處，否則將追究法律責任。

相關文章