12c新特性:Recover Table
在版本12c之前单独恢复Oracle中某一张表，常规的方法是TSPITR或Flashback闪回。在版本12c中提供了Table Recovery单独恢复表的RMAN特性，该特性用以直接从RMAN backup中恢复table的方法，该特性的特点：
- 不影响数据库中的其他数据库对象
- 减少时间和磁盘空间的消耗，和早期的TSPIRT工作流程类似：
- 从另一个磁盘路径恢复表空间
- 导出想要的表数据
- 导入到原数据库
在何种场景下值得考虑使用该rman table recovery特性恢复单个表或分区的数据：
- 在下列场景中不建议使用TSPITR特性，但是建议用12c的table recovery特性：
- 在下列条件中不可能使用闪回，但是可以用12c的table recovery特性:
- purge掉的表，不再可能flashback drop
- 必要的undo已经被覆盖的情况，不再可能flashback table
- 经过一些结构化的DDL变更后，不再可能flashback table
Table Recovery的流程图：
Table Recovery可客制化的选项不多，主要有一下2点：
- 你可以指定NOTABLEIMPORT参数让RMAN不导入恢复的数据到目标库中
- 若指定REMAP TABLE或REMAP TABLESPACE参数，RMAN可选地重命名recovered表或表分区
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