这里将介绍如何从Redhat Linux 平台上将表空间cdzj,ldjc使用跨平台增量备份的传输表空间方法来迁移到Aix平台。在执行完初始化阶段后执行以下三个阶段来执行数据的迁移操作：
准备阶段
在准备阶段，要传输的表空间的数据文件会被传输到目标系统并被转换为目标系统所使用的字节序。在准备阶段应用程序是完全可以访问源数据的。准备阶段使用RMAN备份或dbms_file_transfer，后面将会详细介绍如何选择准备阶段所要使用的方法。
执行跨平台增量备份传输表空间的条件与建议
执行跨平台增量备份传输表空间的条件
在执行跨平台增量备份传输表空间之前需要检查是否满足以下条件:
.传输表空间的限制
.其它必须满足的条件
当前版本不支持windows
源数据库的compatible参数必须设置为10.2.0或更高版本
源数据库的compatible参数必须不能比目标数据库的compatible参数的值大
源数据库必须启用了archivelog模式
尽管首选目标系统是Linux操作系统（可以是64位的Oracle Linux或者是已经认证的Redhat Linux)，但这个过程也可以在其它Unix操作系统中执行。然而，对于任何non-Linux操作系统来说数据库的版本必须是11.2.0.4
源数据库的版本必须小于或等于目标数据库的版本
RMAN的缺省设备类型应该被配置为DISK
源数据库的RMAN不能对DEVICE TYPE DISK使用COMPRESSED配置。如果使用COMPRESSED配置，执行操作时可能会返回:ORA-19994: cross-platform backup of compressed backups different endianess.
源数据库中将要被迁移的表空间必须是online，并且不能包含脱机数据文件。表空间必须为read write状态。表空间为read only那么可以使用正常的XTTS方法进行迁移。
.所有步骤中执行操作的用户必须是Oracle用户并且它是OSDBA组的成员。操作系统审计被用来连接源数据库与目标数据库。
.如果准备阶段方法选择dbms_file_transfer，那么目标数据库版本必须至少为11.2.0.4。
.如果准备阶段方法选择RMAN，那么在源系统与目标系统中都要创建预备区域
.对于备库或快照备库是不支持这种操作的
.如果目标数据库版本是11.2.0.3或更低的版本，那么在目标系统中需要有一个11.2.0.4的数据库home目录来运行11.2.0.4的实例来执行增量备份的转换操作。如果目标数据库版本是11.2.0.3或更低版本，那么需要一个版本为11.2.0.4的单独的增量转换Home目录与实例。如果ASM使用11.2.0.4的转换Home，那么ASM的版本也必须是11.2.0.4，否则会触发ORA-15295(ORA-15295: ASM instance software version 11.2.0.3.0 less than client version 11.2.0.4.0)。
已知问题
1.如果源数据库包含使用键压缩的嵌套的IOTs表，那么在目标数据库home中必须安装修复Bug 14835322的补丁
2.如果想在创建增量备份时对源数据库使用块改变跟踪功能，那么需要在源数据库home中安装修复Bug 16850197的补丁
3.如果前滚阶段(xttdriver.pl -r)失败并显示以下错误信息，那么验证RMAN的DEVICE TYPE DISK不能使用COMPRESSED配置
Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: RestoreSetPiece
DECLARE
*
ERROR at line 1:
ORA-19624: operation failed, retry possible
ORA-19870: error while restoring backup piece
/dbfs_direct/FS1/xtts/incrementals/xtts_incr_backup
ORA-19608: /dbfs_direct/FS1/xtts/incrementals/xtts_incr_backup is not a backup
piece
ORA-19837: invalid blocksize 0 in backup piece header
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 2338
ORA-06512: at line 40
[oracle@sjjh ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 15 17:10:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select a.NAME,b.NAME from v$tablespace a,v$datafile b where a.TS#=b.TS# and a.NAME in('CDZJ','LDJC');
NAME NAME
------------------------------ --------------------------------------------------------------------------------
LDJC /home/app/oracle/product/11.2.0/dbs/ldjc01
CDZJ /home/app/oracle/product/11.2.0/dbs/cdzj01
SQL> create directory sourcedir as '/home/app/oracle/product/11.2.0/dbs';
Directory created.
IBMP740-2:/oracle11$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 16 11:23:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle11/oradata/jycs/jycs/system01.dbf
/oracle11/oradata/jycs/jycs/sysaux01.dbf
/oracle11/oradata/jycs/jycs/undotbs01.dbf
/oracle11/oradata/jycs/jycs/users01.dbf
/oracle11/oradata/jycs/jycs/example01.dbf
SQL> create directory destdir as '/oracle11/oradata/jycs/jycs';
Directory created.
[oracle@sjjh ~]$ cd /home/xtts_script
[oracle@sjjh xtts_script]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -p
============================================================
trace file is /home/xtts_script/prepare_Aug16_Wed_15_50_19_497//Aug16_Wed_15_50_19_497_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Starting prepare phase
--------------------------------------------------------------------
Prepare source for Tablespaces:
'CDZJ' /oracle11/xtts
xttpreparesrc.sql for 'CDZJ' started at Wed Aug 16 15:50:19 2017
xttpreparesrc.sql for ended at Wed Aug 16 15:50:19 2017
Prepare source for Tablespaces:
'LDJC' /oracle11/xtts
xttpreparesrc.sql for 'LDJC' started at Wed Aug 16 15:51:08 2017
xttpreparesrc.sql for ended at Wed Aug 16 15:51:08 2017
Prepare source for Tablespaces:
'''' /oracle11/xtts
xttpreparesrc.sql for '''' started at Wed Aug 16 15:52:05 2017
xttpreparesrc.sql for ended at Wed Aug 16 15:52:05 2017
Prepare source for Tablespaces:
'''' /oracle11/xtts
xttpreparesrc.sql for '''' started at Wed Aug 16 15:52:06 2017
xttpreparesrc.sql for ended at Wed Aug 16 15:52:06 2017
Prepare source for Tablespaces:
'''' /oracle11/xtts
xttpreparesrc.sql for '''' started at Wed Aug 16 15:52:07 2017
xttpreparesrc.sql for ended at Wed Aug 16 15:52:07 2017
--------------------------------------------------------------------
Done with prepare phase
--------------------------------------------------------------------
--------------------------------------------------------------------
Find list of datafiles in system
--------------------------------------------------------------------
--------------------------------------------------------------------
Done finding list of datafiles in system
--------------------------------------------------------------------
IBMP740-2:/oracle11/xtts$ftp 10.138.130.101
Connected to 10.138.130.101.
220 (vsFTPd 2.2.2)
Name (10.138.130.101:root): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> cd /home/dfcopydir
250 Directory successfully changed.
ftp> ls -lrt
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
-rw-r----- 1 501 501 2147491840 Aug 16 07:50 CDZJ_7.tf
-rw-r----- 1 501 501 5368717312 Aug 16 07:52 LDJC_6.tf
226 Directory send OK.
ftp> lcd /oracle11/xtts
Local directory now /oracle11/xtts
ftp> bin
200 Switching to Binary mode.
ftp> get LDJC_6.tf
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for LDJC_6.tf (5368717312 bytes).
226 Transfer complete.
5368717312 bytes received in 45.68 seconds (1.148e+05 Kbytes/s)
local: LDJC_6.tf remote: LDJC_6.tf
ftp> get CDZJ_7.tf
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for CDZJ_7.tf (2147491840 bytes).
226 Transfer complete.
2147491840 bytes received in 18.52 seconds (1.132e+05 Kbytes/s)
local: CDZJ_7.tf remote: CDZJ_7.tf
IBMP740-2:/oracle11/xtts$ls -lrt
total 14680112
-rw-r--r-- 1 oracle11 oinstall 5368717312 Aug 16 16:53 LDJC_6.tf
-rw-r--r-- 1 oracle11 oinstall 2147491840 Aug 16 16:54 CDZJ_7.tf
IBMP740-2:/oracle11/xtts$ftp 10.138.130.101
Connected to 10.138.130.101.
220 (vsFTPd 2.2.2)
Name (10.138.130.101:root): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> cd /home/backup
250 Directory successfully changed.
ftp> ls -lrt
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
-rw-r----- 1 501 501 122880 Aug 16 08:39 30sc2f2u_1_1
-rw-r----- 1 501 501 786432 Aug 16 08:40 31sc2f31_1_1
226 Directory send OK.
ftp> lcd /oracle11/xtts
Local directory now /oracle11/xtts
ftp> bin
200 Switching to Binary mode.
ftp> get 30sc2f2u_1_1
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for 30sc2f2u_1_1 (122880 bytes).
226 Transfer complete.
122880 bytes received in 0.001414 seconds (8.487e+04 Kbytes/s)
local: 30sc2f2u_1_1 remote: 30sc2f2u_1_1
ftp> get 31sc2f31_1_1
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for 31sc2f31_1_1 (786432 bytes).
226 Transfer complete.
786432 bytes received in 0.007007 seconds (1.096e+05 Kbytes/s)
local: 31sc2f31_1_1 remote: 31sc2f31_1_1
IBMP740-2:/oracle11/xtts$ls -lrt
total 1776
-rw-r--r-- 1 oracle11 oinstall 122880 Aug 16 17:37 30sc2f2u_1_1
-rw-r--r-- 1 oracle11 oinstall 786432 Aug 16 17:37 31sc2f31_1_1
Error:
------
The incremental backup was not taken as a datafile has been added to the tablespace:
Please Do the following:
--------------------------
1. Copy fixnewdf.txt from source to destination temp dir
2. Copy backups:
from to the in destination
3. On Destination, run $ORACLE_HOME/perl/bin/perl xttdriver.pl --fixnewdf
4. Re-execute the incremental backup in source:
$ORACLE_HOME/perl/bin/perl xttdriver.pl --bkpincr
NOTE: Before running incremental backup, delete FAILED in source temp dir or
run xttdriver.pl with -L option:
$ORACLE_HOME/perl/bin/perl xttdriver.pl -L --bkpincr
These instructions must be followed exactly as listed. The next incremental backup will include the new datafile.
SQL> create public database link ttslink
2 connect to system identified by "xxzx7817600"
3 using '(DESCRIPTION =
4 (ADDRESS_LIST =
5 (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.101)(PORT = 1521))
6 )
7 (CONNECT_DATA =
8 (SERVER = DEDICATED)
9 (SERVICE_NAME =sjjh)
10 )
11 )';
Database link created.
创建dblink后验证是否可以能过dblink访问源数据库
SQL> select * from dual@ttslink;
D
-
X
在目标数据库中创建用户方案LDJC,CDZJ
SQL> create user ldjc identified by "ldjc";
User created.
SQL> grant dba,connect,resource to ldjc;
Grant succeeded.
SQL> create user cdzj identified by "cdzj";
User created.
SQL> grant dba,connect,resource to cdzj;
Grant succeeded.
IBMP740-2:/oracle11/xtts_script$impdp system/abcd directory=dump_dir logfile=tts_imp.log network_link=ttslink transport_full_check=no transport_tablespaces=CDZJ,LDJC transport_datafiles='/oracle11/oradata/jycs/jycs/CDZJ_7.dbf','/oracle11/oradata/jycs/jycs/LDJC_6.dbf'
Import: Release 11.2.0.4.0 - Production on Wed Aug 16 19:49:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dump_dir logfile=tts_imp.log network_link=ttslink transport_full_check=no transport_tablespaces=CDZJ,LDJC transport_datafiles=/oracle11/oradata/jycs/jycs/CDZJ_7.dbf,/oracle11/oradata/jycs/jycs/LDJC_6.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Aug 16 19:50:19 2017 elapsed 0 00:01:17
IBMP740-2:/home/oracle11$impdp system/abcd directory=dump_dir logfile=ysj.log schemas=ldjc,cdzj content=metadata_only exclude=table,index network_link=ttslink
Import: Release 11.2.0.4.0 - Production on Fri Aug 18 08:40:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=dump_dir logfile=ysj.log schemas=ldjc,cdzj content=metadata_only exclude=table,index network_link=ttslink
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LDJC" already exists
ORA-31684: Object type USER:"CDZJ" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"LDJC"."DB03" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AA10" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AA12" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB01" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB01H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB01_LOG" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB01_USER" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB01_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB01_WEB_NSLOG" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB02" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB02H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."AB02_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB01" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB01H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB01_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB02" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB02H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB02_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB03" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB03H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB03_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB04" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB04H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB04_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB05" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB05H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BB05_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BC02" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BC02H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BC02_LOG" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BD01_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BD02_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BD03_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BF01" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BF01H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ01" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ01H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ01_1" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ02" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ02H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ02_1" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ03" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ03H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ04" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ04H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ04_1" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ05" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ05H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ05_1" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ06" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ06H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ07" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ07H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ07_1" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ08" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ08H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ09" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ09H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ09_TEMP" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ10" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ10H" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ10_LOG" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."BZ11_WEB" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."OAE300_LOG" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."TEMP_AAB002" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."V_JCXT_AJGL_JABP" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."JCXT_AJGL_ZWNR" created with compilation warnings
ORA-39082: Object type VIEW:"LDJC"."V_JCXT_AJGL_LABP" created with compilation warnings
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"LDJC"."QUEST_SOO_PKG" created with compilation warnings
ORA-39082: Object type PACKAGE_BODY:"LDJC"."QUEST_SOO_SQLTRACE" created with compilation warnings
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 72 error(s) at Fri Aug 18 08:40:22 2017 elapsed 0 00:00:20
SQL> select * from ldjc.jy_test;
USER_ID
----------
1
2
3
SQL> select * from cdzj.jy_test;
USER_ID
----------
1
2
3
在元数据导入后，可以将源数据库中的表空间ldjc,cdzj修改为read write状态
SQL> alter tablespace ldjc read write;
Tablespace altered.
SQL> alter tablespace cdzj read write;
Tablespace altered.
dfcopydir:源系统中用来存储xttdriver.pl -p操作所生成的数据文件副本目录。这个目录要有足够的空间来存储所有被传输表空间的数据文件副本。这个目录可以是目标系统上通过NFS-mounted文件系统所挂载到源系统中的一个目录，在这种情况下，目标系统中的stageondest参数也引用这个相同的NFS目录。可以参考See Note 359515.1 for mount option guidelines。 这个参数只有使用RMAN备份生成数据文件副本时才使用，例如dfcopydir=/stage_source
stageondest:目标系统中存储从源系统中手动传输过来的数据文件副本。这个目录要有足够的空间来存储数据文件副本。这个目录同时也是用来存储从源系统传输过来的增量备份文件的目录。在目标系统上执行xttdriver.pl -c转换数据文件与执行xttdriver.pl -r前滚数据文件时会从这个目录中读取数据文件副本与增量备份文件。这个目标也可以是一个DBFS-mounted文件系统。个目录可以是源系统上通过NFS-mounted文件系统所挂载到目标系统中的一个目录，在这种情况下，源系统中的backupformat参数与dfcopydir参数就会引用这个相同的NFS目录。可以参考See Note 359515.1 for mount option guidelines。例如stageondest=/stage_dest
RMAN> run
2> {
3> recover table SH.SALES:SALES_Q3_1998, SH.SALES:SALES_Q4_1998 of pluggable database jypdb
4> until time '2018-01-11 01:03:47'
5> auxiliary destination '/run/recover'
6> datapump destination '/run/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table SH.SALES:SALES_Q3_1998:JY.SALES_Q3_1998,SH.SALES:SALES_Q4_1998:JY.SALES_Q4_1998
9> remap tablespace 'USERS':'TEST';
10> }
Starting recover at 2018-01-11 01:46:51
using channel ORA_DISK_1
Creating automatic instance, with SID='lCEx'
initialization parameters used for automatic instance:
db_name=JY
db_unique_name=lCEx_pitr_jypdb_JY
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2048M
processes=200
db_create_file_dest=/run/recover
log_archive_dest_1='location=/run/recover'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance JY
Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 553651488 bytes
Database Buffers 1577058304 bytes
Redo Buffers 7979008 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 01:03:47";
# 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';
# resync catalog
resync catalog;
}
executing Memory Script
executing command: SET until clause
Starting restore at 2018-01-11 01:47:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 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 +TEST/rman_backup/c-979425723-20180111-00
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/c-979425723-20180111-00 tag=TAG20180111T004839
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/run/recover/JY/controlfile/o1_mf_f5dnfy2z_.ctl
Finished restore at 2018-01-11 01:47:43
sql statement: alter database mount clone database
sql statement: alter system archive log current
starting full resync of recovery catalog
full resync complete
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 01:03:47";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 10 to new;
set newname for clone datafile 1 to new;
set newname for clone datafile 12 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 13 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 11 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 10, 1, 12, 4, 13, 9, 3, 11;
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
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /run/recover/JY/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2018-01-11 01:47:55
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 /run/recover/JY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /run/recover/JY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /run/recover/JY/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /run/recover/JY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_19soc7b4_1_1_965090660
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_19soc7b4_1_1_965090660 tag=TAG20180111T004418
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
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 00010 to /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_1asoc7ee_1_1_965090766
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_1asoc7ee_1_1_965090766 tag=TAG20180111T004418
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2018-01-11 01:50:35
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=965094635 file name=/run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_f5dnkync_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=965094635 file name=/run/recover/JY/datafile/o1_mf_system_f5dngcjj_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=965094635 file name=/run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_f5dnkynd_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=965094635 file name=/run/recover/JY/datafile/o1_mf_undotbs1_f5dngcjk_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=16 STAMP=965094635 file name=/run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_f5dnkynd_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=17 STAMP=965094635 file name=/run/recover/JY/datafile/o1_mf_undotbs2_f5dngcjl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=965094635 file name=/run/recover/JY/datafile/o1_mf_sysaux_f5dngcjh_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=19 STAMP=965094635 file name=/run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_f5dnkynb_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 01:03:47";
# online the datafiles restored or switched
sql clone 'JYPDB' "alter database datafile
10 online";
sql clone "alter database datafile 1 online";
sql clone 'JYPDB' "alter database datafile
12 online";
sql clone "alter database datafile 4 online";
sql clone 'JYPDB' "alter database datafile
13 online";
sql clone "alter database datafile 9 online";
sql clone "alter database datafile 3 online";
sql clone 'JYPDB' "alter database datafile
11 online";
# recover and open database read only
recover clone database tablespace "JYPDB":"SYSTEM", "SYSTEM", "JYPDB":"UNDOTBS1", "UNDOTBS1", "JYPDB":"UNDO_2", "UNDOTBS2", "SYSAUX", "JYPDB":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 10 online
sql statement: alter database datafile 1 online
sql statement: alter database datafile 12 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 11 online
Starting recover at 2018-01-11 01:50:36
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file +TEST/arch/1_96_961976319.dbf
archived log for thread 1 with sequence 97 is already on disk as file +TEST/arch/1_97_961976319.dbf
archived log for thread 2 with sequence 71 is already on disk as file +TEST/arch/2_71_961976319.dbf
archived log for thread 2 with sequence 72 is already on disk as file +TEST/arch/2_72_961976319.dbf
archived log file name=+TEST/arch/1_96_961976319.dbf thread=1 sequence=96
archived log file name=+TEST/arch/2_71_961976319.dbf thread=2 sequence=71
archived log file name=+TEST/arch/1_97_961976319.dbf thread=1 sequence=97
archived log file name=+TEST/arch/2_72_961976319.dbf thread=2 sequence=72
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018-01-11 01:50:39
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database JYPDB open read only';
}
executing Memory Script
sql statement: alter pluggable database JYPDB 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 =
''/run/recover/JY/controlfile/o1_mf_f5dnfy2z_.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 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 553651488 bytes
Database Buffers 1577058304 bytes
Redo Buffers 7979008 bytes
sql statement: alter system set control_files = ''/run/recover/JY/controlfile/o1_mf_f5dnfy2z_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 553651488 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 "2018-01-11 01:03:47";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 14 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2018-01-11 01:51:49
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 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 00014 to /run/recover/LCEX_PITR_JYPDB_JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_1asoc7ee_1_1_965090766
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_1asoc7ee_1_1_965090766 tag=TAG20180111T004418
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:49
Finished restore at 2018-01-11 01:52:39
datafile 14 switched to datafile copy
input datafile copy RECID=21 STAMP=965094759 file name=/run/recover/LCEX_PITR_JYPDB_JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_users_f5dnot2n_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 01:03:47";
# online the datafiles restored or switched
sql clone 'JYPDB' "alter database datafile
14 online";
# recover and open resetlogs
recover clone database tablespace "JYPDB":"USERS", "JYPDB":"SYSTEM", "SYSTEM", "JYPDB":"UNDOTBS1", "UNDOTBS1", "JYPDB":"UNDO_2", "UNDOTBS2", "SYSAUX", "JYPDB":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 14 online
Starting recover at 2018-01-11 01:52:39
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file +TEST/arch/1_96_961976319.dbf
archived log for thread 1 with sequence 97 is already on disk as file +TEST/arch/1_97_961976319.dbf
archived log for thread 2 with sequence 71 is already on disk as file +TEST/arch/2_71_961976319.dbf
archived log for thread 2 with sequence 72 is already on disk as file +TEST/arch/2_72_961976319.dbf
archived log file name=+TEST/arch/1_96_961976319.dbf thread=1 sequence=96
archived log file name=+TEST/arch/2_71_961976319.dbf thread=2 sequence=71
archived log file name=+TEST/arch/1_97_961976319.dbf thread=1 sequence=97
archived log file name=+TEST/arch/2_72_961976319.dbf thread=2 sequence=72
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018-01-11 01:52:42
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database JYPDB open';
}
executing Memory Script
sql statement: alter pluggable database JYPDB open
contents of Memory Script:
{
# create directory for datapump import
sql 'JYPDB' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/run/dump''";
# create directory for datapump export
sql clone 'JYPDB' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/run/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/run/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/run/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_lCEx_rDzp":
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> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
EXPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
EXPDP> . . exported "SH"."SALES":"SALES_Q3_1998" 1.634 MB 50515 rows
EXPDP> . . exported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows
EXPDP> Master table "SYS"."TSPITR_EXP_lCEx_rDzp" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_lCEx_rDzp is:
EXPDP> /run/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_lCEx_rDzp" successfully completed at Thu Jan 11 01:53:35 2018 elapsed 0 00:00:41
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_lCEx_pFDE" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_lCEx_pFDE":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."SALES_Q3_1998" 1.634 MB 50515 rows
IMPDP> . . imported "JY"."SALES_Q4_1998" 1.581 MB 48874 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
IMPDP> ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
GRANT SELECT ON "JY"."SALES" TO "BI"
IMPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
IMPDP> ORA-39083: Object type COMMENT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON TABLE "JY"."SALES" IS 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys'
ORA-39083: Object type COMMENT:"PROD_ID" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."PROD_ID" IS 'FK to the products dimension table'
ORA-39083: Object type COMMENT:"CUST_ID" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."CUST_ID" IS 'FK to the customers dimension table'
ORA-39083: Object type COMMENT:"TIME_ID" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."TIME_ID" IS 'FK to the times dimension table'
ORA-39083: Object type COMMENT:"CHANNEL_ID" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."CHANNEL_ID" IS 'FK to the channels dimension table'
ORA-39083: Object type COMMENT:"PROMO_ID" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."PROMO_ID" IS 'promotion identifier, without FK constraint (intentionally) to show outer join optimization'
ORA-39083: Object type COMMENT:"QUANTITY_SOLD" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."QUANTITY_SOLD" IS 'product quantity sold with the transaction'
ORA-39083: Object type COMMENT:"AMOUNT_SOLD" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON COLUMN "JY"."SALES"."AMOUNT_SOLD" IS 'invoiced amount to the customer'
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_lCEx_pFDE" completed with 9 error(s) at Thu Jan 11 01:55:38 2018 elapsed 0 00:01:38
import completed with errors; Oracle Data Pump dump file t_emp_recvr.dmp is retained
Removing automatic instance
Automatic instance removed
auxiliary instance file /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_temp_f5dnmkjx_.tmp deleted
auxiliary instance file /run/recover/JY/datafile/o1_mf_temp_f5dnmj7f_.tmp deleted
auxiliary instance file /run/recover/LCEX_PITR_JYPDB_JY/onlinelog/o1_mf_4_f5dnqbjt_.log deleted
auxiliary instance file /run/recover/LCEX_PITR_JYPDB_JY/onlinelog/o1_mf_3_f5dnqbhs_.log deleted
auxiliary instance file /run/recover/LCEX_PITR_JYPDB_JY/onlinelog/o1_mf_2_f5dnqbh8_.log deleted
auxiliary instance file /run/recover/LCEX_PITR_JYPDB_JY/onlinelog/o1_mf_1_f5dnqbgj_.log deleted
auxiliary instance file /run/recover/LCEX_PITR_JYPDB_JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_users_f5dnot2n_.dbf deleted
auxiliary instance file /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_f5dnkynb_.dbf deleted
auxiliary instance file /run/recover/JY/datafile/o1_mf_sysaux_f5dngcjh_.dbf deleted
auxiliary instance file /run/recover/JY/datafile/o1_mf_undotbs2_f5dngcjl_.dbf deleted
auxiliary instance file /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_f5dnkynd_.dbf deleted
auxiliary instance file /run/recover/JY/datafile/o1_mf_undotbs1_f5dngcjk_.dbf deleted
auxiliary instance file /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_f5dnkynd_.dbf deleted
auxiliary instance file /run/recover/JY/datafile/o1_mf_system_f5dngcjj_.dbf deleted
auxiliary instance file /run/recover/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_f5dnkync_.dbf deleted
auxiliary instance file /run/recover/JY/controlfile/o1_mf_f5dnfy2z_.ctl deleted
Finished recover at 2018-01-11 01:55:46
starting full resync of recovery catalog
full resync complete
RMAN> run
{
2> 3> recover table SH.SALES:SALES_Q3_1998, SH.SALES:SALES_Q4_1998
4> until time '2018-01-11 03:11:06'
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table SH.SALES:SALES_Q3_1998:JY.SALES_Q3_1998,SH.SALES:SALES_Q4_1998:JY.SALES_Q4_1998
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 2018-01-11 03:21:12
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 UNDOTBS1
Creating automatic instance, with SID='xDyx'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=xDyx_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 03:11:06";
# 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 2018-01-11 03:22:20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-02
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-02 tag=TAG20180110T200959
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5dszl5f_.ctl
Finished restore at 2018-01-11 03:22:27
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 "2018-01-11 03:11:06";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 2018-01-11 03:22:34
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2018-01-11 03:23:20
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965100201 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5dszvs1_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965100201 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5dszvt5_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965100201 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5dszvsm_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 03:11:06";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 2018-01-11 03:23:22
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbf
archived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbf
archived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbf
archived log for thread 1 with sequence 30 is already on disk as file +DATA/arch/orcl/1_30_964992135.dbf
archived log for thread 1 with sequence 31 is already on disk as file +DATA/arch/orcl/1_31_964992135.dbf
archived log for thread 1 with sequence 32 is already on disk as file +DATA/arch/orcl/1_32_964992135.dbf
archived log for thread 1 with sequence 33 is already on disk as file +DATA/arch/orcl/1_33_964992135.dbf
archived log for thread 1 with sequence 34 is already on disk as file +DATA/arch/orcl/1_34_964992135.dbf
archived log for thread 1 with sequence 35 is already on disk as file +DATA/arch/orcl/1_35_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26
archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27
archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28
archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29
archived log file name=+DATA/arch/orcl/1_30_964992135.dbf thread=1 sequence=30
archived log file name=+DATA/arch/orcl/1_31_964992135.dbf thread=1 sequence=31
archived log file name=+DATA/arch/orcl/1_32_964992135.dbf thread=1 sequence=32
archived log file name=+DATA/arch/orcl/1_33_964992135.dbf thread=1 sequence=33
archived log file name=+DATA/arch/orcl/1_34_964992135.dbf thread=1 sequence=34
archived log file name=+DATA/arch/orcl/1_35_964992135.dbf thread=1 sequence=35
media recovery complete, elapsed time: 00:00:51
Finished recover at 2018-01-11 03:24:16
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5dszl5f_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5dszl5f_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 03:11:06";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2018-01-11 03:25:26
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/XDYX_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2018-01-11 03:26:22
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965100383 file name=/ora_xtts/recover/XDYX_PITR_ORCL/datafile/o1_mf_users_f5dt57w8_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-11 03:11:06";
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 2018-01-11 03:26:23
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbf
archived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbf
archived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbf
archived log for thread 1 with sequence 30 is already on disk as file +DATA/arch/orcl/1_30_964992135.dbf
archived log for thread 1 with sequence 31 is already on disk as file +DATA/arch/orcl/1_31_964992135.dbf
archived log for thread 1 with sequence 32 is already on disk as file +DATA/arch/orcl/1_32_964992135.dbf
archived log for thread 1 with sequence 33 is already on disk as file +DATA/arch/orcl/1_33_964992135.dbf
archived log for thread 1 with sequence 34 is already on disk as file +DATA/arch/orcl/1_34_964992135.dbf
archived log for thread 1 with sequence 35 is already on disk as file +DATA/arch/orcl/1_35_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26
archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27
archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28
archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29
archived log file name=+DATA/arch/orcl/1_30_964992135.dbf thread=1 sequence=30
archived log file name=+DATA/arch/orcl/1_31_964992135.dbf thread=1 sequence=31
archived log file name=+DATA/arch/orcl/1_32_964992135.dbf thread=1 sequence=32
archived log file name=+DATA/arch/orcl/1_33_964992135.dbf thread=1 sequence=33
archived log file name=+DATA/arch/orcl/1_34_964992135.dbf thread=1 sequence=34
archived log file name=+DATA/arch/orcl/1_35_964992135.dbf thread=1 sequence=35
media recovery complete, elapsed time: 00:00:08
Finished recover at 2018-01-11 03:26:34
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_xDyx_nniA":
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> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
EXPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
EXPDP> . . exported "SH"."SALES":"SALES_Q3_1998" 1.634 MB 50515 rows
EXPDP> . . exported "SH"."SALES":"SALES_Q4_1998" 1.581 MB 48874 rows
EXPDP> Master table "SYS"."TSPITR_EXP_xDyx_nniA" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_xDyx_nniA is:
EXPDP> /ora_xtts/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_xDyx_nniA" successfully completed at Thu Jan 11 03:27:33 2018 elapsed 0 00:00:39
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_xDyx_xeDs" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_xDyx_xeDs":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."SALES_Q3_1998" 1.634 MB 50515 rows
IMPDP> . . imported "JY"."SALES_Q4_1998" 1.581 MB 48874 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_xDyx_xeDs" completed with 9 error(s) at Thu Jan 11 03:28:18 2018 elapsed 0 00:00:36
import completed with errors; Oracle Data Pump dump file t_emp_recvr.dmp is retained
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5dt3146_.tmp deleted
auxiliary instance file /ora_xtts/recover/XDYX_PITR_ORCL/onlinelog/o1_mf_3_f5dt7brh_.log deleted
auxiliary instance file /ora_xtts/recover/XDYX_PITR_ORCL/onlinelog/o1_mf_2_f5dt7bqr_.log deleted
auxiliary instance file /ora_xtts/recover/XDYX_PITR_ORCL/onlinelog/o1_mf_1_f5dt7bq5_.log deleted
auxiliary instance file /ora_xtts/recover/XDYX_PITR_ORCL/datafile/o1_mf_users_f5dt57w8_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5dszvsm_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5dszvt5_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5dszvs1_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5dszl5f_.ctl deleted
Finished recover at 2018-01-11 03:28:22
RMAN> run
2> {
3> recover table hr.t1,cs.t1
4> until time '2018-01-10 22:55:39'
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table 'HR'.'T1':'JY'.'T1_1','CS'.'T1':'JY'.'T1_2'
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 2018-01-10 23:26:43
current log archived
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 UNDOTBS1
Creating automatic instance, with SID='fvtm'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=fvtm_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 22:55:39";
# 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 2018-01-10 23:27:59
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-02
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-02 tag=TAG20180110T200959
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5dd84jp_.ctl
Finished restore at 2018-01-10 23:28:05
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 "2018-01-10 22:55:39";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 2018-01-10 23:28:15
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2018-01-10 23:29:02
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965086142 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5dd8k64_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965086142 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5dd8k8j_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965086142 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5dd8k7d_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 22:55:39";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 2018-01-10 23:29:03
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbf
archived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbf
archived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbf
archived log for thread 1 with sequence 30 is already on disk as file +DATA/arch/orcl/1_30_964992135.dbf
archived log for thread 1 with sequence 31 is already on disk as file +DATA/arch/orcl/1_31_964992135.dbf
archived log for thread 1 with sequence 32 is already on disk as file +DATA/arch/orcl/1_32_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26
archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27
archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28
archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29
archived log file name=+DATA/arch/orcl/1_30_964992135.dbf thread=1 sequence=30
archived log file name=+DATA/arch/orcl/1_31_964992135.dbf thread=1 sequence=31
archived log file name=+DATA/arch/orcl/1_32_964992135.dbf thread=1 sequence=32
media recovery complete, elapsed time: 00:00:34
Finished recover at 2018-01-10 23:29:40
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5dd84jp_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5dd84jp_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 22:55:39";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2018-01-10 23:30:43
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/FVTM_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2018-01-10 23:31:50
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965086310 file name=/ora_xtts/recover/FVTM_PITR_ORCL/datafile/o1_mf_users_f5ddf560_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 22:55:39";
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 2018-01-10 23:31:50
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbf
archived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbf
archived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbf
archived log for thread 1 with sequence 30 is already on disk as file +DATA/arch/orcl/1_30_964992135.dbf
archived log for thread 1 with sequence 31 is already on disk as file +DATA/arch/orcl/1_31_964992135.dbf
archived log for thread 1 with sequence 32 is already on disk as file +DATA/arch/orcl/1_32_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26
archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27
archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28
archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29
archived log file name=+DATA/arch/orcl/1_30_964992135.dbf thread=1 sequence=30
archived log file name=+DATA/arch/orcl/1_31_964992135.dbf thread=1 sequence=31
archived log file name=+DATA/arch/orcl/1_32_964992135.dbf thread=1 sequence=32
media recovery complete, elapsed time: 00:00:05
Finished recover at 2018-01-10 23:31:58
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_fvtm_ikCg":
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 "CS"."T1" 39.66 KB 8 rows
EXPDP> . . exported "HR"."T1" 39.66 KB 8 rows
EXPDP> Master table "SYS"."TSPITR_EXP_fvtm_ikCg" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_fvtm_ikCg is:
EXPDP> /ora_xtts/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_fvtm_ikCg" successfully completed at Wed Jan 10 23:32:53 2018 elapsed 0 00:00:34
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_fvtm_Dybh" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_fvtm_Dybh":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."T1_2" 39.66 KB 8 rows
IMPDP> . . imported "JY"."T1_1" 39.66 KB 8 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_fvtm_Dybh" successfully completed at Wed Jan 10 23:33:32 2018 elapsed 0 00:00:30
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5ddc523_.tmp deleted
auxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/onlinelog/o1_mf_3_f5ddhgrn_.log deleted
auxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/onlinelog/o1_mf_2_f5ddhgqw_.log deleted
auxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/onlinelog/o1_mf_1_f5ddhgq8_.log deleted
auxiliary instance file /ora_xtts/recover/FVTM_PITR_ORCL/datafile/o1_mf_users_f5ddf560_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5dd8k7d_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5dd8k8j_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5dd8k64_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5dd84jp_.ctl deleted
auxiliary instance file t_emp_recvr.dmp deleted
Finished recover at 2018-01-10 23:33:36
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- --------
28 INACTIVE
29 CURRENT
27 INACTIVE
3 rows selected.
进行日志切换，记录truncate操作之前的logseq号为30，并执行truncate操作
SQL> alter system switch logfile;
System altered.
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
28 INACTIVE
29 ACTIVE
30 CURRENT
SQL> truncate table t_emp;
Table truncated.
进行日志切换，记录truncate操作之后的logseq号为31
SQL> alter system switch logfile;
System altered.
SQL> select sequence#, status from v$log;
SEQUENCE# STATUS
---------- ----------------
31 CURRENT
29 ACTIVE
30 ACTIVE
3.启动RMAN并使用有sysbacup或sysdba权限的用户连接到目标数据库
[oracle@jytest3 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@jytest3 ~]$ rman target/
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 10 02:22:13 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1492772871)
RMAN> run
2> {
3> recover table hr.t_emp
4> until sequence 30
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table 'HR'.'T_EMP':'JY'.'T_EMP_NEW'
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 2018-01-10 22:32:35
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 UNDOTBS1
Creating automatic instance, with SID='zdjl'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=zdjl_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until logseq 30 thread 1;
# 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 2018-01-10 22:33:40
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-02
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-02 tag=TAG20180110T200959
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctl
Finished restore at 2018-01-10 22:33:46
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 logseq 30 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 2018-01-10 22:33:55
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2018-01-10 22:34:41
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965082881 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d92n67_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965082881 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d92n7d_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965082881 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d92n6t_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 30 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 2018-01-10 22:34:43
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbf
archived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbf
archived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26
archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27
archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28
archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29
media recovery complete, elapsed time: 00:00:24
Finished recover at 2018-01-10 22:35:09
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until logseq 30 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2018-01-10 22:36:13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/ZDJL_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2018-01-10 22:37:09
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965083029 file name=/ora_xtts/recover/ZDJL_PITR_ORCL/datafile/o1_mf_users_f5d96ycb_.dbf
contents of Memory Script:
{
# set requested point in time
set until logseq 30 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 2018-01-10 22:37:10
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log for thread 1 with sequence 26 is already on disk as file +DATA/arch/orcl/1_26_964992135.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA/arch/orcl/1_27_964992135.dbf
archived log for thread 1 with sequence 28 is already on disk as file +DATA/arch/orcl/1_28_964992135.dbf
archived log for thread 1 with sequence 29 is already on disk as file +DATA/arch/orcl/1_29_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
archived log file name=+DATA/arch/orcl/1_26_964992135.dbf thread=1 sequence=26
archived log file name=+DATA/arch/orcl/1_27_964992135.dbf thread=1 sequence=27
archived log file name=+DATA/arch/orcl/1_28_964992135.dbf thread=1 sequence=28
archived log file name=+DATA/arch/orcl/1_29_964992135.dbf thread=1 sequence=29
media recovery complete, elapsed time: 00:00:06
Finished recover at 2018-01-10 22:37:18
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_zdjl_ctic":
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 "HR"."T_EMP" 17.08 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_zdjl_ctic" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_zdjl_ctic is:
EXPDP> /ora_xtts/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_zdjl_ctic" successfully completed at Wed Jan 10 22:38:12 2018 elapsed 0 00:00:33
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_zdjl_nBfi" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_zdjl_nBfi":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."T_EMP_NEW" 17.08 KB 107 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_zdjl_nBfi" successfully completed at Wed Jan 10 22:38:51 2018 elapsed 0 00:00:32
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5d94xrx_.tmp deleted
auxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/onlinelog/o1_mf_3_f5d98yqq_.log deleted
auxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/onlinelog/o1_mf_2_f5d98yq4_.log deleted
auxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/onlinelog/o1_mf_1_f5d98ypg_.log deleted
auxiliary instance file /ora_xtts/recover/ZDJL_PITR_ORCL/datafile/o1_mf_users_f5d96ycb_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d92n6t_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d92n7d_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d92n67_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5d929nw_.ctl deleted
auxiliary instance file t_emp_recvr.dmp deleted
Finished recover at 2018-01-10 22:38:56
RMAN> run
2> {
3> recover table hr.t_emp
4> until time '2018-01-10 20:10:01'
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table 'HR'.'T_EMP':'JY'.'T_EMP_NEW'
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 2018-01-10 22:07:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 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='BxcB'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=BxcB_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 20:10:01";
# 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 2018-01-10 22:09:04
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-02
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-02 tag=TAG20180110T200959
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctl
Finished restore at 2018-01-10 22:09:10
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 "2018-01-10 20:10:01";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 2018-01-10 22:09:18
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2018-01-10 22:10:04
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965081404 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d7nhmp_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965081404 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d7nhq5_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965081405 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d7nhot_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 20:10:01";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 2018-01-10 22:10:06
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018-01-10 22:10:09
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 20:10:01";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2018-01-10 22:11:13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/BXCB_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2018-01-10 22:12:09
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965081529 file name=/ora_xtts/recover/BXCB_PITR_ORCL/datafile/o1_mf_users_f5d7r2q4_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "2018-01-10 20:10:01";
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 2018-01-10 22:12:10
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018-01-10 22:12:14
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_BxcB_tfoq":
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 "HR"."T_EMP" 17.08 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_BxcB_tfoq" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_BxcB_tfoq is:
EXPDP> /ora_xtts/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_BxcB_tfoq" successfully completed at Wed Jan 10 22:13:08 2018 elapsed 0 00:00:33
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_BxcB_Enlv" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_BxcB_Enlv":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."T_EMP_NEW" 17.08 KB 107 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_BxcB_Enlv" successfully completed at Wed Jan 10 22:13:48 2018 elapsed 0 00:00:33
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5d7p27f_.tmp deleted
auxiliary instance file /ora_xtts/recover/BXCB_PITR_ORCL/onlinelog/o1_mf_3_f5d7syo5_.log deleted
auxiliary instance file /ora_xtts/recover/BXCB_PITR_ORCL/onlinelog/o1_mf_2_f5d7syng_.log deleted
auxiliary instance file /ora_xtts/recover/BXCB_PITR_ORCL/onlinelog/o1_mf_1_f5d7symr_.log deleted
auxiliary instance file /ora_xtts/recover/BXCB_PITR_ORCL/datafile/o1_mf_users_f5d7r2q4_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d7nhot_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d7nhq5_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d7nhmp_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5d7n52r_.ctl deleted
auxiliary instance file t_emp_recvr.dmp deleted
Finished recover at 2018-01-10 22:13:53
[oracle@jytest3 ~]$ rman target/
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 10 02:22:13 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1492772871)
RMAN> run
2> {
3> recover table hr.t_emp
4> until scn 399411
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp.dmp'
8> remap table 'hr'.'t_emp':'t_emp_recvr'
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 10-JAN-18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/10/2018 19:46:50
RMAN-05063: Cannot recover specified tables
RMAN-05075: Remapped object "hr"."t_emp" is not being recovered
错误信息说hr.t_emp表不能被恢复，其实是表名要使用大写
RMAN> run
2> {
3> recover table hr.t_emp
4> until scn 399411
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table 'HR'.'T_EMP':'JY'.'T_EMP_RECVR'
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 10-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 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='goBv'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=goBv_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# 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 10-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-01
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-01 tag=TAG20180110T024913
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5czvqq3_.ctl
Finished restore at 10-JAN-18
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 399411;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 10-JAN-18
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705 tag=TAG20180110T024824
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 10-JAN-18
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965073457 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5czw3w1_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965073457 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5czw3x5_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965073457 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5czw3wm_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 10-JAN-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file +DATA/arch/orcl/1_19_964992135.dbf
archived log for thread 1 with sequence 20 is already on disk as file +DATA/arch/orcl/1_20_964992135.dbf
archived log file name=+DATA/arch/orcl/1_19_964992135.dbf thread=1 sequence=19
archived log file name=+DATA/arch/orcl/1_20_964992135.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-JAN-18
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5czvqq3_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5czvqq3_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 10-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/GOBV_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705 tag=TAG20180110T024824
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 10-JAN-18
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965073585 file name=/ora_xtts/recover/GOBV_PITR_ORCL/datafile/o1_mf_users_f5czzt3r_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 10-JAN-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file +DATA/arch/orcl/1_19_964992135.dbf
archived log for thread 1 with sequence 20 is already on disk as file +DATA/arch/orcl/1_20_964992135.dbf
archived log file name=+DATA/arch/orcl/1_19_964992135.dbf thread=1 sequence=19
archived log file name=+DATA/arch/orcl/1_20_964992135.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-JAN-18
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_goBv_vFCd":
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 "HR"."T_EMP" 17.08 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_goBv_vFCd" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_goBv_vFCd is:
EXPDP> /ora_xtts/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_goBv_vFCd" successfully completed at Wed Jan 10 20:00:44 2018 elapsed 0 00:00:33
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_goBv_apfb" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_goBv_apfb":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."T_EMP_RECVR" 17.08 KB 107 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_goBv_apfb" successfully completed at Wed Jan 10 20:01:32 2018 elapsed 0 00:00:40
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5czxpvw_.tmp deleted
auxiliary instance file /ora_xtts/recover/GOBV_PITR_ORCL/onlinelog/o1_mf_3_f5d01ojq_.log deleted
auxiliary instance file /ora_xtts/recover/GOBV_PITR_ORCL/onlinelog/o1_mf_2_f5d01oj5_.log deleted
auxiliary instance file /ora_xtts/recover/GOBV_PITR_ORCL/onlinelog/o1_mf_1_f5d01ohd_.log deleted
auxiliary instance file /ora_xtts/recover/GOBV_PITR_ORCL/datafile/o1_mf_users_f5czzt3r_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5czw3wm_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5czw3x5_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5czw3w1_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5czvqq3_.ctl deleted
auxiliary instance file t_emp_recvr.dmp deleted
Finished recover at 10-JAN-18
[oracle@jytest3 ~]$ rman target/
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 10 02:22:13 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1492772871)
RMAN> run
2> {
3> recover table hr.t_emp
4> until scn 399411
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp.dmp'
8> notableimport;
9> }
Starting recover at 10-JAN-18
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 UNDOTBS1
Creating automatic instance, with SID='fcsj'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=fcsj_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# 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 10-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-01
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-01 tag=TAG20180110T024913
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctl
Finished restore at 10-JAN-18
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 399411;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 10-JAN-18
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705 tag=TAG20180110T024824
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 10-JAN-18
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965013098 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5b4xwvo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965013098 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5b4xwx2_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965013098 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5b4xwwj_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 10-JAN-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file +DATA/arch/orcl/1_19_964992135.dbf
archived log for thread 1 with sequence 20 is already on disk as file +DATA/arch/orcl/1_20_964992135.dbf
archived log file name=+DATA/arch/orcl/1_19_964992135.dbf thread=1 sequence=19
archived log file name=+DATA/arch/orcl/1_20_964992135.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-JAN-18
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 10-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/FCSJ_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705 tag=TAG20180110T024824
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 10-JAN-18
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965013242 file name=/ora_xtts/recover/FCSJ_PITR_ORCL/datafile/o1_mf_users_f5b51h8p_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 10-JAN-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file +DATA/arch/orcl/1_19_964992135.dbf
archived log for thread 1 with sequence 20 is already on disk as file +DATA/arch/orcl/1_20_964992135.dbf
archived log file name=+DATA/arch/orcl/1_19_964992135.dbf thread=1 sequence=19
archived log file name=+DATA/arch/orcl/1_20_964992135.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-JAN-18
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_fcsj_pkfh":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
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> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
EXPDP> . . exported "HR"."T_EMP" 17.08 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_fcsj_pkfh" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_fcsj_pkfh is:
EXPDP> /ora_xtts/dump/t_emp.dmp
EXPDP> Job "SYS"."TSPITR_EXP_fcsj_pkfh" successfully completed at Wed Jan 10 03:15:08 2018 elapsed 0 00:00:32
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5b4zhf9_.tmp deleted
auxiliary instance file /ora_xtts/recover/FCSJ_PITR_ORCL/onlinelog/o1_mf_3_f5b53yp4_.log deleted
auxiliary instance file /ora_xtts/recover/FCSJ_PITR_ORCL/onlinelog/o1_mf_2_f5b53yol_.log deleted
auxiliary instance file /ora_xtts/recover/FCSJ_PITR_ORCL/onlinelog/o1_mf_1_f5b53ynw_.log deleted
auxiliary instance file /ora_xtts/recover/FCSJ_PITR_ORCL/datafile/o1_mf_users_f5b51h8p_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5b4xwwj_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5b4xwx2_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5b4xwvo_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5b4xmpo_.ctl deleted
Finished recover at 10-JAN-18
RMAN> run
2> {
3> recover table hr.t_emp
4> until scn 399411
5> auxiliary destination '/ora_xtts/recover'
6> datapump destination '/ora_xtts/dump'
7> dump file 't_emp_recvr.dmp'
8> remap table 'HR'.'T_EMP':'JY'.'T_EMP_NEW'
9> remap tablespace 'USERS':'USERTBS';
10> }
Starting recover at 10-JAN-18
current log archived
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 UNDOTBS1
Creating automatic instance, with SID='jzdF'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=jzdF_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1='location=/ora_xtts/recover'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# 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 10-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 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 +DATA/backup/c-1492772871-20180110-02
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/c-1492772871-20180110-02 tag=TAG20180110T200959
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.ctl
Finished restore at 10-JAN-18
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 399411;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 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, 3, 2;
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 /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_%u_.tmp in control file
Starting restore at 10-JAN-18
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 /ora_xtts/recover/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 10-JAN-18
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=965075765 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d248q8_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=965075766 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d248r7_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=965075766 file name=/ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d248qv_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 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 3 online
sql statement: alter database datafile 2 online
Starting recover at 10-JAN-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-JAN-18
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 =
''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.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 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter system set control_files = ''/ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 10-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 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 00004 to /ora_xtts/recover/JZDF_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/backup/orcl_1492772871_26sobn77_1_1_965074151
channel ORA_AUX_DISK_1: piece handle=+DATA/backup/orcl_1492772871_26sobn77_1_1_965074151 tag=TAG20180110T200911
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 10-JAN-18
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=965075892 file name=/ora_xtts/recover/JZDF_PITR_ORCL/datafile/o1_mf_users_f5d27wvd_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 399411;
# online the datafiles restored or switched
sql clone "alter database datafile 4 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 4 online
Starting recover at 10-JAN-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 24 is already on disk as file +DATA/arch/orcl/1_24_964992135.dbf
archived log for thread 1 with sequence 25 is already on disk as file +DATA/arch/orcl/1_25_964992135.dbf
archived log file name=+DATA/arch/orcl/1_24_964992135.dbf thread=1 sequence=24
archived log file name=+DATA/arch/orcl/1_25_964992135.dbf thread=1 sequence=25
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-JAN-18
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/ora_xtts/dump''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/ora_xtts/dump''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_jzdF_fxiC":
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 "HR"."T_EMP" 17.08 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_jzdF_fxiC" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_jzdF_fxiC is:
EXPDP> /ora_xtts/dump/t_emp_recvr.dmp
EXPDP> Job "SYS"."TSPITR_EXP_jzdF_fxiC" successfully completed at Wed Jan 10 20:39:09 2018 elapsed 0 00:00:32
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_jzdF_BDce" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_jzdF_BDce":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "JY"."T_EMP_NEW" 17.08 KB 107 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_jzdF_BDce" successfully completed at Wed Jan 10 20:39:47 2018 elapsed 0 00:00:31
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_tempts1_f5d25tp8_.tmp deleted
auxiliary instance file /ora_xtts/recover/JZDF_PITR_ORCL/onlinelog/o1_mf_3_f5d29sj0_.log deleted
auxiliary instance file /ora_xtts/recover/JZDF_PITR_ORCL/onlinelog/o1_mf_2_f5d29shf_.log deleted
auxiliary instance file /ora_xtts/recover/JZDF_PITR_ORCL/onlinelog/o1_mf_1_f5d29sgs_.log deleted
auxiliary instance file /ora_xtts/recover/JZDF_PITR_ORCL/datafile/o1_mf_users_f5d27wvd_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_sysaux_f5d248qv_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_undotbs1_f5d248r7_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/datafile/o1_mf_system_f5d248q8_.dbf deleted
auxiliary instance file /ora_xtts/recover/ORCL/controlfile/o1_mf_f5d2402v_.ctl deleted
auxiliary instance file t_emp_recvr.dmp deleted
Finished recover at 10-JAN-18
5.通过t_emp.dmp文件将表t_emp中的数据导入
[oracle@jytest3 dump]$ impdp hr/hr@orcl dumpfile=dump_dir:t_emp.dmp
Import: Release 12.2.0.1.0 - Production on Wed Jan 10 03:23:34 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@orcl dumpfile=dump_dir:t_emp.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."T_EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Jan 10 03:23:51 2018 elapsed 0 00:00:10
[oracle@jytest3 dump]$ impdp hr/hr@orcl dumpfile=dump_dir:t_emp.dmp table_exists_action=truncate
Import: Release 12.2.0.1.0 - Production on Wed Jan 10 03:29:10 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@orcl dumpfile=dump_dir:t_emp.dmp table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "HR"."T_EMP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."T_EMP" 17.08 KB 107 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Wed Jan 10 03:29:30 2018 elapsed 0 00:00:18
6.验证表t_emp中的记录，可以看到已经成功恢复到被删了记录之前的状态。
SQL> select count(*) from t_emp;
COUNT(*)
----------
107
RMAN> backup as compressed backupset database format '/acfs1/rman_back/jyrac_%u_%d_%t_%s_%p' plus archivelog format '/acfs1/rman_back/arch_%d_%T_%U';
Starting backup at 29-DEC-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=497 RECID=1044 STAMP=964006170
input archived log thread=2 sequence=540 RECID=1045 STAMP=964006171
channel ORA_DISK_1: starting piece 1 at 29-DEC-17
channel ORA_DISK_1: finished piece 1 at 29-DEC-17
piece handle=/acfs1/rman_back/arch_JYRAC_20171229_0isnb48r_1_1 tag=TAG20171229T112931 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-DEC-17
Starting backup at 29-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATADG/jyrac/datafile/ldjc01
input datafile file number=00002 name=+DATADG/jyrac/datafile/sysaux.258.930413055
input datafile file number=00011 name=+DATADG/jyrac/datafile/cdzj01
input datafile file number=00001 name=+DATADG/jyrac/datafile/system.259.930413057
input datafile file number=00003 name=+DATADG/jyrac/datafile/undotbs1.262.930413057
input datafile file number=00008 name=+DATADG/jyrac/datafile/sales_test_01.dbf
input datafile file number=00005 name=+DATADG/jyrac/datafile/example.260.930413057
input datafile file number=00006 name=+DATADG/jyrac/datafile/undotbs2.261.930413057
input datafile file number=00007 name=+DATADG/jyrac/datafile/test01.dbf
input datafile file number=00009 name=+DATADG/jyrac/datafile/emp_test_01.dbf
input datafile file number=00010 name=+DATADG/jyrac/datafile/orders_test_01.dbf
input datafile file number=00004 name=+DATADG/jyrac/datafile/users.263.930413057
channel ORA_DISK_1: starting piece 1 at 29-DEC-17
channel ORA_DISK_1: finished piece 1 at 29-DEC-17
piece handle=/acfs1/rman_back/jyrac_0jsnb48u_JYRAC_964006174_19_1 tag=TAG20171229T112933 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:09:06
Finished backup at 29-DEC-17
Starting backup at 29-DEC-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=498 RECID=1046 STAMP=964006723
input archived log thread=2 sequence=541 RECID=1047 STAMP=964006720
channel ORA_DISK_1: starting piece 1 at 29-DEC-17
channel ORA_DISK_1: finished piece 1 at 29-DEC-17
piece handle=/acfs1/rman_back/arch_JYRAC_20171229_0ksnb4q5_1_1 tag=TAG20171229T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-DEC-17
Starting Control File and SPFILE Autobackup at 29-DEC-17
piece handle=/acfs1/rman_back/c-2655496871-20171229-00 comment=NONE
Finished Control File and SPFILE Autobackup at 29-DEC-17
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9 24.50K DISK 00:00:01 29-DEC-17
BP Key: 9 Status: AVAILABLE Compressed: YES Tag: TAG20171229T112931
Piece Name: /acfs1/rman_back/arch_JYRAC_20171229_0isnb48r_1_1
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 497 14690299647719 29-DEC-17 14690299647943 29-DEC-17
2 540 14690299647723 29-DEC-17 14690299647960 29-DEC-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 1.82G DISK 00:09:05 29-DEC-17
BP Key: 10 Status: AVAILABLE Compressed: YES Tag: TAG20171229T112933
Piece Name: /acfs1/rman_back/jyrac_0jsnb48u_JYRAC_964006174_19_1
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/system.259.930413057
2 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/sysaux.258.930413055
3 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/undotbs1.262.930413057
4 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/users.263.930413057
5 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/example.260.930413057
6 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/undotbs2.261.930413057
7 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/test01.dbf
8 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/sales_test_01.dbf
9 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/emp_test_01.dbf
10 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/orders_test_01.dbf
11 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/cdzj01
12 Full 14690299647966 29-DEC-17 +DATADG/jyrac/datafile/ldjc01
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 92.50K DISK 00:00:01 29-DEC-17
BP Key: 11 Status: AVAILABLE Compressed: YES Tag: TAG20171229T113845
Piece Name: /acfs1/rman_back/arch_JYRAC_20171229_0ksnb4q5_1_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 498 14690299647943 29-DEC-17 14690299648779 29-DEC-17
2 541 14690299647960 29-DEC-17 14690299648994 29-DEC-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 17.89M DISK 00:00:08 29-DEC-17
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20171229T113847
Piece Name: /acfs1/rman_back/c-2655496871-20171229-00
SPFILE Included: Modification time: 29-DEC-17
SPFILE db_unique_name: JYRAC
Control File Included: Ckp SCN: 14690299650469 Ckp time: 29-DEC-17
[grid@jyrac1 rman_back]$ ls -lrt
total 1929424
-rw-r----- 1 oracle asmadmin 25600 Dec 29 11:29 arch_JYRAC_20171229_0isnb48r_1_1
-rw-r----- 1 oracle asmadmin 1951571968 Dec 29 11:38 jyrac_0jsnb48u_JYRAC_964006174_19_1
-rw-r----- 1 oracle asmadmin 95232 Dec 29 11:38 arch_JYRAC_20171229_0ksnb4q5_1_1
-rw-r----- 1 oracle asmadmin 18776064 Dec 29 11:38 c-2655496871-20171229-00
3.将步骤2生成的RMAN备份文件拷贝到目标主机相应目录(/ora_xtts/rman)中
[oracle@jytest3 rman]$ scp oracle@10.138.130.151:/acfs1/rman_back/* /ora_xtts/rman/
The authenticity of host '10.138.130.151 (10.138.130.151)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.151' (RSA) to the list of known hosts.
oracle@10.138.130.151's password:
arch_JYRAC_20171229_0isnb48r_1_1
100% 25KB 25.0KB/s 00:00
arch_JYRAC_20171229_0ksnb4q5_1_1
100% 93KB 93.0KB/s 00:00
c-2655496871-20171229-00
100% 18MB 17.9MB/s 00:01
jyrac_0jsnb48u_JYRAC_964006174_19_1
100% 1861MB 38.0MB/s 00:49
[oracle@jytest3 rman]$ ls -lrt
total 1924300
-rw-r-----. 1 oracle oinstall 25600 Dec 30 01:48 arch_JYRAC_20171229_0isnb48r_1_1
-rw-r-----. 1 oracle oinstall 95232 Dec 30 01:48 arch_JYRAC_20171229_0ksnb4q5_1_1
-rw-r-----. 1 oracle oinstall 18776064 Dec 30 01:48 c-2655496871-20171229-00
-rw-r-----. 1 oracle oinstall 1951571968 Dec 30 01:49 jyrac_0jsnb48u_JYRAC_964006174_19_1
4.在目标主机上，设置ORACLE_SID为源数据库的SID(jyrac1为单实例数据库)
[oracle@jytest3 ~]$ export ORACLE_SID=jyrac1
5.在目标主机上启动RMAN并连接到目标数据库
[oracle@jytest3 ~]$ rman target/ nocatalog
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Dec 30 02:06:59 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
6.设置DBID，该值为源数据库的DBID
RMAN> set DBID 2655496871
executing command: SET DBID
7.启动目标数据库到nomount状态
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0/db/dbs/initjyrac1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1073741824 bytes
Fixed Size 8628936 bytes
Variable Size 293602616 bytes
Database Buffers 763363328 bytes
Redo Buffers 8146944 bytes
RMAN> run
2> {
3> set controlfile autobackup format for device type disk to '/ora_xtts/rman/%F';
4> restore spfile to pfile '/u01/app/oracle/product/12.2.0/db/dbs/initjyrac1.ora' from autobackup;
5> shutdown abort;
6> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 30-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20171230
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20171229
channel ORA_DISK_1: AUTOBACKUP found: /ora_xtts/rman/c-2655496871-20171229-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /ora_xtts/rman/c-2655496871-20171229-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 30-DEC-17
Oracle instance shut down
RMAN> startup force nomount pfile='/u01/app/oracle/product/12.2.0/db/dbs/initjyrac1.ora'
Oracle instance started
Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 637536552 bytes
Database Buffers 1862270976 bytes
Redo Buffers 8151040 bytes
RMAN> run
2> {
3> set controlfile autobackup format for device type disk to '/ora_xtts/rman/%F';
4> restore controlfile from autobackup;
5> alter database mount;
6> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 30-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20171230
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20171229
channel ORA_DISK_1: AUTOBACKUP found: /ora_xtts/rman/c-2655496871-20171229-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /ora_xtts/rman/c-2655496871-20171229-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/JYRAC/CONTROLFILE/current.329.964063767
Finished restore at 30-DEC-17
Statement processed
released channel: ORA_DISK_1
RMAN> catalog start with '/ora_xtts/rman/';
searching for all files that match the pattern /ora_xtts/rman/
List of Files Unknown to the Database
=====================================
File Name: /ora_xtts/rman/arch_JYRAC_20171229_0isnb48r_1_1
File Name: /ora_xtts/rman/arch_JYRAC_20171229_0ksnb4q5_1_1
File Name: /ora_xtts/rman/c-2655496871-20171229-00
File Name: /ora_xtts/rman/jyrac_0jsnb48u_JYRAC_964006174_19_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /ora_xtts/rman/arch_JYRAC_20171229_0isnb48r_1_1
File Name: /ora_xtts/rman/arch_JYRAC_20171229_0ksnb4q5_1_1
File Name: /ora_xtts/rman/c-2655496871-20171229-00
File Name: /ora_xtts/rman/jyrac_0jsnb48u_JYRAC_964006174_19_1
RMAN> run
2> {
3> set newname for database to '+data/jyrac/datafile/%U';
4> alter database rename file '+DATADG/jyrac/onlinelog/group_1.264.930413221' to '+data/jyrac/onlinelog/redo1.log';
5> alter database rename file '+DATADG/jyrac/onlinelog/group_2.265.930413225' to '+data/jyrac/onlinelog/redo2.log';
6> alter database rename file '+DATADG/jyrac/onlinelog/group_3.266.930413227' to '+data/jyrac/onlinelog/redo3.log';
7> alter database rename file '+DATADG/jyrac/onlinelog/group_4.267.930413231' to '+data/jyrac/onlinelog/redo4.log';
8> set until scn 14690299648200;
9> restore database;
10> switch datafile all;
11> recover database;
12> }
executing command: SET NEWNAME
Statement processed
Statement processed
Statement processed
Statement processed
executing command: SET until clause
Starting restore at 30-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=93 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +data/jyrac/datafile/data_D-JYRAC_TS-SYSTEM_FNO-1
channel ORA_DISK_1: restoring datafile 00002 to +data/jyrac/datafile/data_D-JYRAC_TS-SYSAUX_FNO-2
channel ORA_DISK_1: restoring datafile 00003 to +data/jyrac/datafile/data_D-JYRAC_TS-UNDOTBS1_FNO-3
channel ORA_DISK_1: restoring datafile 00004 to +data/jyrac/datafile/data_D-JYRAC_TS-USERS_FNO-4
channel ORA_DISK_1: restoring datafile 00005 to +data/jyrac/datafile/data_D-JYRAC_TS-EXAMPLE_FNO-5
channel ORA_DISK_1: restoring datafile 00006 to +data/jyrac/datafile/data_D-JYRAC_TS-UNDOTBS2_FNO-6
channel ORA_DISK_1: restoring datafile 00007 to +data/jyrac/datafile/data_D-JYRAC_TS-TEST_FNO-7
channel ORA_DISK_1: restoring datafile 00008 to +data/jyrac/datafile/data_D-JYRAC_TS-SALES_TEST_FNO-8
channel ORA_DISK_1: restoring datafile 00009 to +data/jyrac/datafile/data_D-JYRAC_TS-EMP_TEST_FNO-9
channel ORA_DISK_1: restoring datafile 00010 to +data/jyrac/datafile/data_D-JYRAC_TS-ORDERS_TEST_FNO-10
channel ORA_DISK_1: restoring datafile 00011 to +data/jyrac/datafile/data_D-JYRAC_TS-CDZJ_FNO-11
channel ORA_DISK_1: restoring datafile 00012 to +data/jyrac/datafile/data_D-JYRAC_TS-LDJC_FNO-12
channel ORA_DISK_1: reading from backup piece /acfs1/rman_back/jyrac_0jsnb48u_JYRAC_964006174_19_1
channel ORA_DISK_1: errors found reading piece handle=/acfs1/rman_back/jyrac_0jsnb48u_JYRAC_964006174_19_1
channel ORA_DISK_1: failover to piece handle=/ora_xtts/rman/jyrac_0jsnb48u_JYRAC_964006174_19_1 tag=TAG20171229T112933
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:09:55
Finished restore at 30-DEC-17
datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=964064400 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-system_fno-1
datafile 2 switched to datafile copy
input datafile copy RECID=15 STAMP=964064401 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-sysaux_fno-2
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=964064401 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-undotbs1_fno-3
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=964064401 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-users_fno-4
datafile 5 switched to datafile copy
input datafile copy RECID=18 STAMP=964064402 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-example_fno-5
datafile 6 switched to datafile copy
input datafile copy RECID=19 STAMP=964064402 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-undotbs2_fno-6
datafile 7 switched to datafile copy
input datafile copy RECID=20 STAMP=964064402 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-test_fno-7
datafile 8 switched to datafile copy
input datafile copy RECID=21 STAMP=964064403 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-sales_test_fno-8
datafile 9 switched to datafile copy
input datafile copy RECID=22 STAMP=964064403 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-emp_test_fno-9
datafile 10 switched to datafile copy
input datafile copy RECID=23 STAMP=964064403 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-orders_test_fno-10
datafile 11 switched to datafile copy
input datafile copy RECID=24 STAMP=964064404 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-cdzj_fno-11
datafile 12 switched to datafile copy
input datafile copy RECID=25 STAMP=964064404 file name=+DATA/jyrac/datafile/data_d-jyrac_ts-ldjc_fno-12
Starting recover at 30-DEC-17
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=498
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=541
channel ORA_DISK_1: reading from backup piece /acfs1/rman_back/arch_JYRAC_20171229_0ksnb4q5_1_1
channel ORA_DISK_1: errors found reading piece handle=/acfs1/rman_back/arch_JYRAC_20171229_0ksnb4q5_1_1
channel ORA_DISK_1: failover to piece handle=/ora_xtts/rman/arch_JYRAC_20171229_0ksnb4q5_1_1 tag=TAG20171229T113845
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/arch/jyrac/1_498_930413221.dbf thread=1 sequence=498
archived log file name=+DATA/arch/jyrac/2_541_930413221.dbf thread=2 sequence=541
media recovery complete, elapsed time: 00:00:02
Finished recover at 30-DEC-17
14.使用resetlogs与upgrade选项来打开数据库
RMAN> alter database open resetlogs upgrade;
Statement processed
RMAN-06900: warning: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: warning: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-04023: Object SYS.STANDARD could not be validated or authorized
错误信息是由于数据库相关plsql包作为升级处理需要被重新验证。
15.退出RMAN
RMAN> exit
Recovery Manager complete.
16.对还原后的目标数据库升级
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 637536552 bytes
Database Buffers 1862270976 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> ERROR
DOC>
DOC>
DOC> As of 12.2, customers must use the parallel upgrade utility, catctl.pl,
DOC> to invoke catupgrd.sql when upgrading the database dictionary.
DOC> Running catupgrd.sql directly from SQL*Plus is no longer supported.
DOC>
DOC> For Example:
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> catctl
DOC>
DOC> or
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
DOC>
DOC> Refer to the Oracle Database Upgrade Guide for more information.
DOC>
DOC>
DOC>######################################################################
DOC>######################################################################
DOC>#
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
12cr2升级方法与之前不同，要执行如下命令:
[oracle@jytest3 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@jytest3 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Argument list for [catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]
/u01/app/oracle/product/12.2.0/db/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/db]
/u01/app/oracle/product/12.2.0/db/bin/orabasehome = [/u01/app/oracle/product/12.2.0/db]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0/db]
Analyzing file /u01/app/oracle/product/12.2.0/db/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20171230035616]
catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20171230035616/catupgrd_catcon_11460.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20171230035616/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20171230035616/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 6
Database Name = jyrac
DataBase Version = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db/cfgtoollogs/jyrac/upgrade20171230035617/catupgrd_catcon_11460.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db/cfgtoollogs/jyrac/upgrade20171230035617/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db/cfgtoollogs/jyrac/upgrade20171230035617/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/12.2.0/db/cfgtoollogs/jyrac/upgrade20171230035617]
Parallel SQL Process Count = 4
Components in [jyrac]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS WK]
....省略....
[oracle@jytest1 ~]$ rman target/ catalog rco/xxzx7817600@jypdb_173
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Dec 20 16:53:26 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: JY (DBID=979425723)
connected to recovery catalog database
4.将要执行恢复的PDB关闭，其它的PDB与CDB仍然处于open状态
RMAN> alter pluggable database jypdb close immediate;
starting full resync of recovery catalog
full resync complete
Statement processed
starting full resync of recovery catalog
full resync complete
RMAN> run
2> {
3> set until scn 6255735;
4> restore pluggable database jypdb;
5> recover pluggable database jypdb;
6> }
executing command: SET until clause
Starting restore at 2017-12-20 17:00:38
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
channel ORA_DISK_1: restoring datafile 00011 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
channel ORA_DISK_1: restoring datafile 00012 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
channel ORA_DISK_1: restoring datafile 00013 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
channel ORA_DISK_1: restoring datafile 00014 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
channel ORA_DISK_1: restoring datafile 00015 to +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
channel ORA_DISK_1: reading from backup piece +TEST/rman_backup/jy_979425723_962563516_11slv3ds_1_1
channel ORA_DISK_1: piece handle=+TEST/rman_backup/jy_979425723_962563516_11slv3ds_1_1 tag=TAG20171212T184328
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2017-12-20 17:01:15
Starting recover at 2017-12-20 17:01:16
current log archived
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 38 is already on disk as file +TEST/arch/1_38_961976319.dbf
archived log for thread 1 with sequence 39 is already on disk as file +TEST/arch/1_39_961976319.dbf
archived log for thread 1 with sequence 40 is already on disk as file +TEST/arch/1_40_961976319.dbf
archived log for thread 1 with sequence 41 is already on disk as file +TEST/arch/1_41_961976319.dbf
archived log for thread 1 with sequence 42 is already on disk as file +TEST/arch/1_42_961976319.dbf
archived log for thread 1 with sequence 43 is already on disk as file +TEST/arch/1_43_961976319.dbf
archived log for thread 1 with sequence 44 is already on disk as file +TEST/arch/1_44_961976319.dbf
archived log for thread 1 with sequence 45 is already on disk as file +TEST/arch/1_45_961976319.dbf
archived log for thread 1 with sequence 46 is already on disk as file +TEST/arch/1_46_961976319.dbf
archived log for thread 1 with sequence 47 is already on disk as file +TEST/arch/1_47_961976319.dbf
archived log for thread 1 with sequence 48 is already on disk as file +TEST/arch/1_48_961976319.dbf
archived log for thread 1 with sequence 49 is already on disk as file +TEST/arch/1_49_961976319.dbf
archived log for thread 1 with sequence 50 is already on disk as file +TEST/arch/1_50_961976319.dbf
archived log for thread 1 with sequence 51 is already on disk as file +TEST/arch/1_51_961976319.dbf
archived log for thread 1 with sequence 52 is already on disk as file +TEST/arch/1_52_961976319.dbf
archived log for thread 1 with sequence 53 is already on disk as file +TEST/arch/1_53_961976319.dbf
archived log for thread 1 with sequence 54 is already on disk as file +TEST/arch/1_54_961976319.dbf
archived log for thread 1 with sequence 55 is already on disk as file +TEST/arch/1_55_961976319.dbf
archived log for thread 1 with sequence 56 is already on disk as file +TEST/arch/1_56_961976319.dbf
archived log for thread 1 with sequence 57 is already on disk as file +TEST/arch/1_57_961976319.dbf
archived log for thread 2 with sequence 32 is already on disk as file +TEST/arch/2_32_961976319.dbf
archived log for thread 2 with sequence 33 is already on disk as file +TEST/arch/2_33_961976319.dbf
archived log for thread 2 with sequence 34 is already on disk as file +TEST/arch/2_34_961976319.dbf
archived log for thread 2 with sequence 35 is already on disk as file +TEST/arch/2_35_961976319.dbf
archived log for thread 2 with sequence 36 is already on disk as file +TEST/arch/2_36_961976319.dbf
archived log for thread 2 with sequence 37 is already on disk as file +TEST/arch/2_37_961976319.dbf
archived log for thread 2 with sequence 38 is already on disk as file +TEST/arch/2_38_961976319.dbf
archived log for thread 2 with sequence 39 is already on disk as file +TEST/arch/2_39_961976319.dbf
archived log for thread 2 with sequence 40 is already on disk as file +TEST/arch/2_40_961976319.dbf
archived log for thread 2 with sequence 41 is already on disk as file +TEST/arch/2_41_961976319.dbf
archived log for thread 2 with sequence 42 is already on disk as file +TEST/arch/2_42_961976319.dbf
archived log for thread 2 with sequence 43 is already on disk as file +TEST/arch/2_43_961976319.dbf
archived log for thread 2 with sequence 44 is already on disk as file +TEST/arch/2_44_961976319.dbf
archived log for thread 2 with sequence 45 is already on disk as file +TEST/arch/2_45_961976319.dbf
archived log for thread 2 with sequence 46 is already on disk as file +TEST/arch/2_46_961976319.dbf
archived log for thread 2 with sequence 47 is already on disk as file +TEST/arch/2_47_961976319.dbf
archived log for thread 2 with sequence 48 is already on disk as file +TEST/arch/2_48_961976319.dbf
archived log for thread 2 with sequence 49 is already on disk as file +TEST/arch/2_49_961976319.dbf
archived log for thread 2 with sequence 50 is already on disk as file +TEST/arch/2_50_961976319.dbf
archived log for thread 2 with sequence 51 is already on disk as file +TEST/arch/2_51_961976319.dbf
archived log for thread 2 with sequence 52 is already on disk as file +DATA/JY/ONLINELOG/group_4.262.961976705
archived log for thread 2 with sequence 53 is already on disk as file +DATA/JY/ONLINELOG/group_3.263.961976697
media recovery complete, elapsed time: 00:04:03
Finished recover at 2017-12-20 17:05:30
starting full resync of recovery catalog
full resync complete
6. 以读写方式打开PDB，放弃目标SCN之后的所有改变,执行以下命令
RMAN> alter pluggable database jypdb open resetlogs;
Statement processed
starting full resync of recovery catalog
full resync complete
SQL> conn jy/jy@jypdb
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
39