Oracle 12C使用备份集执行只读表空间的跨平台传输

使用带用for transport或to platform子句的backup命令来创建备份集来将只读表空间从一个平台传输到另一个平台。当传输只读表空间时,必须要导出这些表空间的元数据。这些元数据会在将表空间附加到目标数据库时需要使用。当跨平台传输表空间时,源平台与目标平台可以使用不同的字节序。可以还原跨平台备份中的所有数据文件或表空间或者它们中的一部分。在还原这些对象之后,可以为被还原的数据文件指定路径与名称。

使用备份集执行跨平台传输表空间的先决条件
.源数据库与目标数据库的spfile参数文件中的compatible参数必须设置为12.0.0或更高版本。

.被传输的表空间必须是自包含。执行dbms_tts.transport_set_check过程来检查依赖性。如果transport_set_violations视图包含指定表空间的相关行记录,那么必须在创建跨平台备份之前解决这种依赖性。

.被传输的表空间必须是只读模式,除非在backup命令中使用了allow inconsistent子句。

下面的例子将Linux平台上的RAC CDB中的jypdb的表空间tts与cs传输到windows平台上的单实例CDB的jypdb数据库中。使用备份集对只读表空间执行跨平台传输的操作如下:
1.检查源数据库与目标数据库的compatible参数是否设置为12.0.0或更高版本
源数据库

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE

目标数据库
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE

2.对源数据库中的要被传输的表空间tts与cs执行自包含检查

SQL> alter session set container=jypdb;

Session altered.

SQL> exec sys.dbms_tts.transport_set_check('TTS,CS',true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

3.检查源平台与目标平台的字节序
源平台

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d  where tp.platform_name=d.platform_name;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little

目标平台

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d  where t

PLATFORM_NAME                                                                       ENDIAN_FORMAT
--------------------------------------------------------------------------------    --------------
Microsoft Windows x86 64-bit                                                        Little

4.将要被传输的表空间(tts,cs)设置为只读状态

SQL> alter tablespace tts read only;

Tablespace altered.

SQL> alter tablespace cs read only;

Tablespace altered.


SQL> select tablespace_name,status from user_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TEST                           ONLINE
TTS                            READ ONLY
CS                             READ ONLY

9 rows selected.

5.给RMAN备份输出文件选择命名方法,使用backup命令的format子句来为备份输出文件选择输出方法。

6.启动RMAN连接到源数据库(整个RAC CDB)并将重启源数据库并以只读方式打开

[oracle@jytest1 ~]$ rman target sys/abcd@jy catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Feb 5 23:22:24 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

7.在backup命令中使用for transport或to platform子句来备份表空间。下面的例子对表空间tts与cs创建跨平台传输备份。备份可以被还原到Microsoft Windows IA (64-bit)平台。Data Pump导出dump文件包含了附加表空间到目标数据库中所需要的元数据。备份文件与dump文件存储在/ora_backup/tts目录中。

RMAN> backup to platform 'Microsoft Windows x86 64-bit' format '/ora_backup/tts/trans_ts.bck' datapump format '/ora_backup/tts/trans_ts_dmp.bck' tablespace JYPDB:'TTS',JYPDB:'CS';

Starting backup at 05-FEB-18
using channel ORA_DISK_1
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces...
   EXPDP> Starting "SYS"."TRANSPORT_EXP_JY_xput":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Master table "SYS"."TRANSPORT_EXP_JY_xput" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TRANSPORT_EXP_JY_xput is:
   EXPDP>   /u01/app/oracle/product/12.2.0/db/dbs/backup_tts_JY_38873.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace CS:
   EXPDP>   +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
   EXPDP> Datafiles required for transportable tablespace TTS:
   EXPDP>   +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
   EXPDP> Job "SYS"."TRANSPORT_EXP_JY_xput" successfully completed at Mon Feb 5 23:57:57 2018 elapsed 0 00:03:08
Export completed

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 05-FEB-18
channel ORA_DISK_1: finished piece 1 at 05-FEB-18
piece handle=/ora_backup/tts/trans_ts.bck tag=TAG20180205T235319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
input Data Pump dump file=/u01/app/oracle/product/12.2.0/db/dbs/backup_tts_JY_38873.dmp
channel ORA_DISK_1: starting piece 1 at 05-FEB-18
channel ORA_DISK_1: finished piece 1 at 05-FEB-18
piece handle=/ora_backup/tts/trans_ts_dmp.bck tag=TAG20180205T235319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-FEB-18
starting full resync of recovery catalog
full resync complete


[oracle@jytest1 tts]$ ls -lrt
total 2472
-rw-r----- 1 oracle asmadmin 2318336 Feb  5 17:57 trans_ts.bck
-rw-r----- 1 oracle asmadmin  212992 Feb  5 17:57 trans_ts_dmp.bck

因为在backup命令中使用了to platform子句,因此将数据文件转换成目标平台字节序的操作在源平台执行。

8.将backup命令生成的备份集与Data Pump导出的dump文件传输到目标平台的D:\app\oracle\oradata\tts目录中

9.使用rman连接到目标数据库(单实例CDB中的JYPDB)

C:\Users\Administrator>rman target sys/abcd@jypdb

恢复管理器: Release 12.2.0.1.0 - Production on 星期一 2月 5 00:22:31 2018

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

已连接到目标数据库: JY:JYPDB (DBID=4203494064)

10.使用有foreign tablespace子句的restore命令来还原从源平台传输过来的备份集

RMAN> restore foreign tablespace TTS,CS to new from backupset 'D:\app\oracle\oradata\tts\trans_ts.bck' dump file from backupset 'D:\app\oracle\oradata\tts\trans_ts_dmp.bck';

从位于 06-2月 -18 的 restore 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=8 设备类型=DISK

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原外部表空间 TTS 中的所有文件
通道 ORA_DISK_1: 正在还原外部表空间 CS 中的所有文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tts\trans_ts.bck
通道 ORA_DISK_1: 将外部文件 22 还原到 D:\APP\ORACLE\ORADATA\JY\5F9AC6865E87549FE053AB828A0ADE94\DATAFILE\O1_MF_TTS_F7K17RGW_.DBF
通道 ORA_DISK_1: 将外部文件 23 还原到 D:\APP\ORACLE\ORADATA\JY\5F9AC6865E87549FE053AB828A0ADE94\DATAFILE\O1_MF_CS_F7K17RHC_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tts\trans_ts.bck
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:02
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在将数据泵转储文件还原到 D:\app\oracle\product\12.2.0\dbhome_1/dbs/backup_tts_JY_38586.dmp
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tts\trans_ts_dmp.bck
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tts\trans_ts_dmp.bck
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:02

正在执行元数据导入...
   IMPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_IMP_JY_pfCp"
   IMPDP> 启动 "SYS"."TSPITR_IMP_JY_pfCp":
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> 作业 "SYS"."TSPITR_IMP_JY_pfCp" 已于 星期二 2月 6 00:24:02 2018 elapsed 0 00:00:33 成功完成
导入完毕

在 05-2月 -18 完成了 restore

11.在目标数据库中查询表tts.t1与cs.t2记录是否与源数据库中一致,如果一致说用表空间tts与cs跨平台传输成功
源数据库

SQL> alter session set container=jypdb;

会话已更改。

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

目标数据库

SQL> alter session set container=jypdb;

会话已更改。

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

到此使用备份集跨平台传输只读表空间操作就完成了。

Oracle 12C Transport a Database to a Different Platform Using Backup Sets

可以从源平台传输整个数据库到有相同字节序的不同平台。当为了传输数据库而创建跨平台备份时,可以在源平台或目标平台上执行对数据库的转换。在目标平台执行对数据库的转换的最大好处就是执行转换的开锁从源平台转 移到了目标平台。
使用备份集执行跨平台数据库传输的先决条件
在为了跨平台传输数据库而创建跨平台备份之前需要满足以下条件:
.源数据库与目标数据库的spfile文件中的compatible参数必须设置为12.0.0或更高的版本。
.源数据库必须是read-only模式打开。
.dbms_tdb.check_db过程必须成功运行。
.源平台与目标平台必须有相同的字节序。

下面的例子将使用备份集执行跨平台传输数据库,源平台为Linux 64位,目标平台为windows 64位其具体操作如下
1.在目标平台上创建数据库实例jy
设置环境变量ORACLE_SID

C:\Users\Administrator>set ORACLE_SID=jy

创建实例

C:\Users\Administrator>oradim -new -sid jy -startmode m

输入 Oracle 服务用户的口令:
实例已创建。

创建Oracle口令文件

C:\Users\Administrator>orapwd file=D:\app\oracle\product\12.2.0\dbhome_1\database\pwdjy.ora password=xxzx$7817600  sysbackup=xxzx$7817600

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = jy)
(ORACLE_HOME =D:\app\oracle\product\12.2.0\dbhome_1)
(GLOBAL_DBNAME=jy)
)
)

在tnsnames.ora文件中增加以下内容

JY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-ROUOJ6ERFO3)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jy)
      (UR = A)
    )
  )

2.利用源数据库的spfile文件来为目标数据库创建spfile文件

SQL> create pfile='$ORACLE_HOME/dbs/inityyl.ora' from spfile;

File created.

将上面生成的inityyl.ora文件复制到目标平台D:\app\oracle\product\12.2.0\dbhome_1\database目录下并将其内容修改成如下所示

*.audit_file_dest='D:\app\oracle\admin\jy\adump'
*.cluster_database=false
*.compatible='12.2.0'
*.control_files='D:\APP\ORACLE\ORADATA\JY\CONTROLFILE\control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='D:\app\oracle\oradata\'
*.db_name='jy'
*.diagnostic_dest='D:\app\oracle\'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jyXDB)'
*.enable_pluggable_database=true
jy2.instance_number=2
jy1.instance_number=1
*.log_archive_dest_1='location=D:\app\oracle\oradata\arch\jy'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1g
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_max_size=2147483648
*.sga_target=2147483648
jy2.thread=2
jy1.thread=1
*.undo_retention=8640
jy1.undo_tablespace='UNDOTBS1'
jy2.undo_tablespace='UNDOTBS2'

3.在目标平台上启动实例jy

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期四 2月 1 01:23:54 2018

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

已连接到空闲例程。


SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 2147483648 bytes
Fixed Size                  8920984 bytes
Variable Size            1392509032 bytes
Database Buffers          738197504 bytes
Redo Buffers                7856128 bytes

创建spfile参数文件并重启实例到nomount状态

SQL> create spfile from pfile='D:\app\oracle\product\12.2.0\dbhome_1\database\initjy.ora';

文件已创建。

SQL> shutdown immediate
ORA-01507: ??????


ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 2147483648 bytes
Fixed Size                  8920984 bytes
Variable Size            1392509032 bytes
Database Buffers          738197504 bytes
Redo Buffers                7856128 bytes
SQL> show parameter spfile

NAME                                 TYPE                           VALUE
------------------------------------ ----------------------         -----------------------------------------------------------
spfile                               string                         D:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\SPFILEJY.ORA

测试tns连接实例jy是否能够成功

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on 星期四 2月 1 18:01:52 2018

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

SQL> conn sys/abcd@jy as sysdba
已连接。

4.使用有sysdba权限的用户通过SQL*Plus连接到源数据库,并查询v$transportable_platform来查询目标平台的名字,因为传输整个数据库源平台与目标平台的字节序应该相同。

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 1 18:06:37 2018

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


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

SQL> set long 200
SQL> set linesize 200
SQL> select platform_name,endian_format from v$transportable_platform;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit)                                                                               Big
Solaris[tm] OE (64-bit)                                                                               Big
Microsoft Windows IA (32-bit)                                                                         Little
Linux IA (32-bit)                                                                                     Little
AIX-Based Systems (64-bit)                                                                            Big
HP-UX (64-bit)                                                                                        Big
HP Tru64 UNIX                                                                                         Little
HP-UX IA (64-bit)                                                                                     Big
Linux IA (64-bit)                                                                                     Little
HP Open VMS                                                                                           Little
Microsoft Windows IA (64-bit)                                                                         Little
IBM zSeries Based Linux                                                                               Big
Linux x86 64-bit                                                                                      Little
Apple Mac OS                                                                                          Big
Microsoft Windows x86 64-bit                                                                          Little
Solaris Operating System (x86)                                                                        Little
IBM Power Based Linux                                                                                 Big
HP IA Open VMS                                                                                        Little
Solaris Operating System (x86-64)                                                                     Little
Apple Mac OS (x86-64)                                                                                 Little

20 rows selected.

我这里源平台为Linux x86 64-bit,目标平台为Microsoft Windows IA (64-bit),两个平台的字节序都是Little

5.给RMAN备份输出文件选择命名方法
使用backup命令的format子句来为备份输出文件选择输出方法。比如使用format子句来为备份输出文件指定存储目录/ora_backup/tdb并且为每个文件生成以transport_为前缀的唯一文件名。FORMAT ‘/ora_backup/tdb/transport_%U’

6.启动RMAN连接到源数据库(整个RAC CDB)并将重启源数据库并以只读方式打开。

[oracle@jytest1 tdb]$ rman target sys/abcd0@jy catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 1 18:37:04 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

RMAN> shutdown immediate

starting full resync of recovery catalog
full resync complete
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database: JY (DBID=979425723)
database is already started
starting full resync of recovery catalog
full resync complete

RMAN> alter database open read only;

starting full resync of recovery catalog
full resync complete
Statement processed

7.在backup命令中使用for transport或to platform子句来备份源数据库。下面的例子对整个数据库创建跨平台传输备份。备份可以被还原到任何与源平台有相同字节序的平台。这里源平台为Linux x86 64-bit,因为使用了 for transport子句,那么对整个数据库的转换操作将在目标平台上执行。跨平台数据库备份被存储在/ora_backup/tdb目录中。

RMAN> backup for transport format '/ora_backup/tdb/db_transport_%U' database;

Starting backup at 01-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/JY/DATAFILE/sysaux.298.962209605
input datafile file number=00009 name=+DATA/JY/DATAFILE/undotbs2.312.962209605
input datafile file number=00004 name=+DATA/JY/DATAFILE/undotbs1.277.962209605
input datafile file number=00001 name=+DATA/JY/DATAFILE/system.317.962209603
input datafile file number=00007 name=+DATA/JY/DATAFILE/users.301.962209605
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7ssq5l1i_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
input datafile file number=00011 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
input datafile file number=00013 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
input datafile file number=00010 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
input datafile file number=00012 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
input datafile file number=00015 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7tsq5l5g_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
input datafile file number=00016 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
input datafile file number=00018 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
input datafile file number=00019 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
input datafile file number=00021 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409
input datafile file number=00020 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7usq5l8p_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
input datafile file number=00005 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
input datafile file number=00008 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7vsq5lbf_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 01-FEB-18



[oracle@jytest1 tdb]$ ls -lrt
total 5532808
-rw-r----- 1 oracle asmadmin 3146178560 Feb  1 13:27 db_transport_7ssq5l1i_1_1
-rw-r----- 1 oracle asmadmin  990789632 Feb  1 13:29 db_transport_7tsq5l5g_1_1
-rw-r----- 1 oracle asmadmin  988348416 Feb  1 13:30 db_transport_7usq5l8p_1_1
-rw-r----- 1 oracle asmadmin  540270592 Feb  1 13:31 db_transport_7vsq5lbf_1_1

备份源数据库的控制文件到跨踪文件中用于在目标平台为目标数据库重新创建控制文件

SQL> alter session set tracefile_identifier='create';

Session altered.

SQL> alter database backup controlfile to trace resetlogs;

Database altered.

[root@jytest1 /]# find / -name *create*.trc
/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_22752_create.trc

[root@jytest1 /]# cat /u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_22752_create.trc
Trace file /u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_22752_create.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /u01/app/oracle/product/12.2.0/db
System name:    Linux
Node name:      jytest1.jydba.net
Release:        3.8.13-55.1.6.el7uek.x86_64
Version:        #2 SMP Wed Feb 11 14:18:22 PST 2015
Machine:        x86_64
Instance name: jy1
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 22752, image: oracle@jytest1.jydba.net (TNS V1-V3)


*** 2018-02-01T23:42:02.696976+08:00 (CDB$ROOT(1))
*** SESSION ID:(6.7558) 2018-02-01T23:42:02.697094+08:00
*** CLIENT ID:() 2018-02-01T23:42:02.697109+08:00
*** SERVICE NAME:(SYS$USERS) 2018-02-01T23:42:02.697120+08:00
*** MODULE NAME:(sqlplus@jytest1.jydba.net (TNS V1-V3)) 2018-02-01T23:42:02.697131+08:00
*** ACTION NAME:() 2018-02-01T23:42:02.697141+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2018-02-01T23:42:02.697150+08:00
*** CONTAINER ID:(1) 2018-02-01T23:42:02.697161+08:00

-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="jy"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?#/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=+TEST/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JY" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/JY/DATAFILE/system.317.962209603',
  '+DATA/JY/DATAFILE/sysaux.298.962209605',
  '+DATA/JY/DATAFILE/undotbs1.277.962209605',
  '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
  '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
  '+DATA/JY/DATAFILE/users.301.962209605',
  '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
  '+DATA/JY/DATAFILE/undotbs2.312.962209605',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'
CHARACTER SET ZHS16GBK
;
-- Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('+TEST/jy/snapcf_jy.f');
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''+test/jy/snapcf_jy.f''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''+test/rman_backup/%F''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+TEST/arch/1_1_934293149.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+TEST/arch/1_1_961976319.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697' SIZE 200M BLOCKSIZE 512 REUSE,
  GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705' SIZE 200M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/TEMPFILE/temp.299.961976339'
     SIZE 150994944  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865'
     SIZE 67108864  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = JYPDB;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.962210519' REUSE;
ALTER SESSION SET CONTAINER = TESTPDB;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/TEMPFILE/temp.258.962469435' REUSE;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- End of tempfile additions.

8.将在源平台上使用rman backup命令生成的备份集与备份的控制文件传输到目标平台的D:\app\oracle\oradata\tdb目录中

9.使用rman连接到目标数据库并确保目标数据库(单实例CDB)处于nomount状态

C:\Users\Administrator>rman target sys/abcd@jy

恢复管理器: Release 12.2.0.1.0 - Production on 星期四 2月 1 19:55:44 2018

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

已连接到目标数据库: JY (未装载)

10.使用有foreign database子句的restore命令来还原从源平台传输过来的备份集

RMAN> restore from platform 'Linux x86 64-bit' foreign database to new from backupset 'D:\app\oracle\oradata\tdb\db_transport_7ssq5l1i_1_1' backupset 'D:\app\oracle\oradata\tdb \db_transport_7tsq5l5g_1_1' backupset 'D:\app\oracle\oradata\tdb\db_transport_7usq5l8p_1_1' backupset 'D:\app\oracle\oradata\tdb\db_transport_7vsq5lbf_1_1';

从位于 02-2月 -18 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7ssq5l1
i_1_1
通道 ORA_DISK_1: 将外部文件 3 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 9 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 4 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 1 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 7 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7ssq5l1i_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:56
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7tsq5l5
g_1_1
通道 ORA_DISK_1: 将外部文件 14 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 11 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 13 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 10 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 12 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 15 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 22 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF
通道 ORA_DISK_1: 将外部文件 23 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7tsq5l5g_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:26
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7usq5l8
p_1_1
通道 ORA_DISK_1: 将外部文件 17 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF
通道 ORA_DISK_1: 将外部文件 16 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF
通道 ORA_DISK_1: 将外部文件 18 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF
通道 ORA_DISK_1: 将外部文件 19 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF
通道 ORA_DISK_1: 将外部文件 21 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF
通道 ORA_DISK_1: 将外部文件 20 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7usq5l8p_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7vsq5lb
f_1_1
通道 ORA_DISK_1: 将外部文件 6 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF
通道 ORA_DISK_1: 将外部文件 5 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF
通道 ORA_DISK_1: 将外部文件 8 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7vsq5lbf_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
在 02-2月 -18 完成了 restore

11.在目标平台上使用备份的控制文件来为目标数据库创建控制文件,将备分的控制文件记录的源数据文件修改成还原后的数据文件名,其创建语句如下

CREATE CONTROLFILE REUSE DATABASE "JY" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo01.log'  SIZE 200M,
  GROUP 2 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo02.log'  SIZE 200M
-- STANDBY LOGFILE
DATAFILE
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF'
CHARACTER SET ZHS16GBK
;

执行上面的创建语句

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo01.log'  SIZE 200M,

  9    GROUP 2 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo02.log'  SIZE 200M
 10  -- STANDBY LOGFILE
 11  DATAFILE
 12    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF',
 13    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF',
 14    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF',
 15    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF',
 16    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF',
 17    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF',
 18    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF',
 19    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF',
 20    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF',
 21    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF',
 22    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF',
 23    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF',
 24    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF',
 25    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF',
 26    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF',
 27    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF',
 28    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF',
 29    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF',
 30    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF',
 31    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF',
 32    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF',
 33    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF'
 34  CHARACTER SET ZHS16GBK
 35  ;

控制文件已创建。


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF

已选择 22 行。


12.以open resetlogs选项打开数据库

SQL> alter database open resetlogs;

数据库已更改。

SQL>  alter pluggable database all open read write;

插接式数据库已变更。

SQL> select name,open_mode from v$pdbs;

NAME                       OPEN_MODE
----------------------     ------------------------
PDB$SEED                   READ ONLY
JYPDB                      READ WRITE
TESTPDB                    READ WRITE

13.给CDB数据库增加临进表空间数据文件

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp01.dbf'     size 100M  reuse autoextend on next 655360  maxsize 200m;

表空间已更改。

SQL> alter session set container = pdb$seed;

会话已更改。

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp02.dbf'   size 100M  reuse autoextend on next 655360  maxsize 200m;

表空间已更改。

SQL> alter session set container = jypdb;

会话已更改。

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp03.dbf'  size 100M reuse;

表空间已更改。

SQL> alter session set container = testpdb;

会话已更改。

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp04.dbf' size 100M reuse;

表空间已更改。

SQL> alter session set container = cdb$root;

会话已更改。

到此使用备份集跨平台传输整个CDB数据库就完成了。

Oracle 12C RMAN transport tablespace from PDB of RAC CDB to remote PDB

这个例子是将Linux 平台上的RAC CDB数据库中PDB(jypdb)数据库中的tts,cs表空间(tts,cs用户的缺省永久表空间)传输到Linux 平台上的单实例CDB数据库中的PDB(jypdb)中
1.确认源数据库与目标数据库所在平台是不是传输表空间所支持的操作系统平台。
确认源数据库操作系统平台:

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d  where tp.platform_name=d.platform_name ;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little

确认目数据库操作系统平台:

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d  where tp.platform_name=d.platform_name ;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little

这里操作系统平台都是64位Linux

2.确认要被传输的表空间是否是自包含表空间(tts,cs):

SQL> exec sys.dbms_tts.transport_set_check('TTS,CS',true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

如果没有行选择,表示该表空间只包含表数据,可以传输

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

3.对源数据库执行备份,在执行备份时一定要加上include current controlfile否在执行

transport tablespace命令时会出现以下错误信息:
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of tranport tablespace command at 01/25/2018 15:24:22
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore


[oracle@jytest1 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@jytest1 ~]$ rman target sys/abcd@jy  catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 25 23:30:46 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

使用RMAN连接到源RAC CDB数据库执行备份操作

RMAN> backup as compressed backupset database format '+test/rman_backup/jy_%u_%d_%t_%s_%p' include current controlfile   plus archivelog format '+test/rman_backup/arch_%d_%T_%U'  delete all input;


Starting backup at 2018-01-25 23:30:58
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1522 instance=jy2 device type=DISK
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=2 sequence=141 RECID=320 STAMP=966188816
input archived log thread=1 sequence=161 RECID=321 STAMP=966188818
input archived log thread=2 sequence=142 RECID=323 STAMP=966188832
input archived log thread=1 sequence=162 RECID=322 STAMP=966188829
input archived log thread=1 sequence=163 RECID=325 STAMP=966188842
input archived log thread=2 sequence=143 RECID=324 STAMP=966188842
input archived log thread=2 sequence=144 RECID=327 STAMP=966189268
input archived log thread=1 sequence=164 RECID=326 STAMP=966189266
input archived log thread=1 sequence=165 RECID=328 STAMP=966189274
input archived log thread=2 sequence=145 RECID=329 STAMP=966189274
input archived log thread=1 sequence=166 RECID=330 STAMP=966192976
input archived log thread=2 sequence=146 RECID=331 STAMP=966192977
input archived log thread=1 sequence=167 RECID=333 STAMP=966193374
input archived log thread=2 sequence=147 RECID=332 STAMP=966193373
input archived log thread=2 sequence=148 RECID=334 STAMP=966193380
input archived log thread=1 sequence=168 RECID=335 STAMP=966193380
input archived log thread=1 sequence=169 RECID=336 STAMP=966207796
input archived log thread=2 sequence=149 RECID=338 STAMP=966208644
input archived log thread=1 sequence=170 RECID=337 STAMP=966208634
input archived log thread=1 sequence=171 RECID=339 STAMP=966208856
input archived log thread=2 sequence=150 RECID=340 STAMP=966208857
input archived log thread=1 sequence=172 RECID=341 STAMP=966209350
input archived log thread=2 sequence=151 RECID=342 STAMP=966209350
input archived log thread=1 sequence=173 RECID=344 STAMP=966209356
input archived log thread=2 sequence=152 RECID=343 STAMP=966209356
input archived log thread=2 sequence=153 RECID=346 STAMP=966246980
input archived log thread=1 sequence=174 RECID=345 STAMP=966246243
input archived log thread=1 sequence=175 RECID=347 STAMP=966271688
input archived log thread=2 sequence=154 RECID=349 STAMP=966282445
input archived log thread=1 sequence=176 RECID=348 STAMP=966282440
input archived log thread=1 sequence=177 RECID=351 STAMP=966282933
input archived log thread=2 sequence=155 RECID=350 STAMP=966282933
input archived log thread=2 sequence=156 RECID=354 STAMP=966296899
input archived log thread=1 sequence=178 RECID=352 STAMP=966296887
input archived log thread=1 sequence=179 RECID=353 STAMP=966296890
input archived log thread=2 sequence=157 RECID=355 STAMP=966331920
input archived log thread=1 sequence=180 RECID=356 STAMP=966340837
input archived log thread=2 sequence=158 RECID=359 STAMP=966382267
input archived log thread=1 sequence=181 RECID=357 STAMP=966382003
input archived log thread=1 sequence=182 RECID=358 STAMP=966382267
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:31:12
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:33:17
piece handle=+TEST/rman_backup/arch_jy_20180125_7ispjklv_1_1 tag=TAG20180125T233110 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+TEST/arch/2_141_961976319.dbf RECID=320 STAMP=966188816
archived log file name=+TEST/arch/1_161_961976319.dbf RECID=321 STAMP=966188818
archived log file name=+TEST/arch/2_142_961976319.dbf RECID=323 STAMP=966188832
archived log file name=+TEST/arch/1_162_961976319.dbf RECID=322 STAMP=966188829
archived log file name=+TEST/arch/1_163_961976319.dbf RECID=325 STAMP=966188842
archived log file name=+TEST/arch/2_143_961976319.dbf RECID=324 STAMP=966188842
archived log file name=+TEST/arch/2_144_961976319.dbf RECID=327 STAMP=966189268
archived log file name=+TEST/arch/1_164_961976319.dbf RECID=326 STAMP=966189266
archived log file name=+TEST/arch/1_165_961976319.dbf RECID=328 STAMP=966189274
archived log file name=+TEST/arch/2_145_961976319.dbf RECID=329 STAMP=966189274
archived log file name=+TEST/arch/1_166_961976319.dbf RECID=330 STAMP=966192976
archived log file name=+TEST/arch/2_146_961976319.dbf RECID=331 STAMP=966192977
archived log file name=+TEST/arch/1_167_961976319.dbf RECID=333 STAMP=966193374
archived log file name=+TEST/arch/2_147_961976319.dbf RECID=332 STAMP=966193373
archived log file name=+TEST/arch/2_148_961976319.dbf RECID=334 STAMP=966193380
archived log file name=+TEST/arch/1_168_961976319.dbf RECID=335 STAMP=966193380
archived log file name=+TEST/arch/1_169_961976319.dbf RECID=336 STAMP=966207796
archived log file name=+TEST/arch/2_149_961976319.dbf RECID=338 STAMP=966208644
archived log file name=+TEST/arch/1_170_961976319.dbf RECID=337 STAMP=966208634
archived log file name=+TEST/arch/1_171_961976319.dbf RECID=339 STAMP=966208856
archived log file name=+TEST/arch/2_150_961976319.dbf RECID=340 STAMP=966208857
archived log file name=+TEST/arch/1_172_961976319.dbf RECID=341 STAMP=966209350
archived log file name=+TEST/arch/2_151_961976319.dbf RECID=342 STAMP=966209350
archived log file name=+TEST/arch/1_173_961976319.dbf RECID=344 STAMP=966209356
archived log file name=+TEST/arch/2_152_961976319.dbf RECID=343 STAMP=966209356
archived log file name=+TEST/arch/2_153_961976319.dbf RECID=346 STAMP=966246980
archived log file name=+TEST/arch/1_174_961976319.dbf RECID=345 STAMP=966246243
archived log file name=+TEST/arch/1_175_961976319.dbf RECID=347 STAMP=966271688
archived log file name=+TEST/arch/2_154_961976319.dbf RECID=349 STAMP=966282445
archived log file name=+TEST/arch/1_176_961976319.dbf RECID=348 STAMP=966282440
archived log file name=+TEST/arch/1_177_961976319.dbf RECID=351 STAMP=966282933
archived log file name=+TEST/arch/2_155_961976319.dbf RECID=350 STAMP=966282933
archived log file name=+TEST/arch/2_156_961976319.dbf RECID=354 STAMP=966296899
archived log file name=+TEST/arch/1_178_961976319.dbf RECID=352 STAMP=966296887
archived log file name=+TEST/arch/1_179_961976319.dbf RECID=353 STAMP=966296890
archived log file name=+TEST/arch/2_157_961976319.dbf RECID=355 STAMP=966331920
archived log file name=+TEST/arch/1_180_961976319.dbf RECID=356 STAMP=966340837
archived log file name=+TEST/arch/2_158_961976319.dbf RECID=359 STAMP=966382267
archived log file name=+TEST/arch/1_181_961976319.dbf RECID=357 STAMP=966382003
archived log file name=+TEST/arch/1_182_961976319.dbf RECID=358 STAMP=966382267
Finished backup at 2018-01-25 23:33:25

Starting backup at 2018-01-25 23:33:25
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=00003 name=+DATA/JY/DATAFILE/sysaux.298.962209605
input datafile file number=00009 name=+DATA/JY/DATAFILE/undotbs2.312.962209605
input datafile file number=00004 name=+DATA/JY/DATAFILE/undotbs1.277.962209605
input datafile file number=00001 name=+DATA/JY/DATAFILE/system.317.962209603
input datafile file number=00007 name=+DATA/JY/DATAFILE/users.301.962209605
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:33:28
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:35:43
piece handle=+TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
input datafile file number=00011 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
input datafile file number=00013 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
input datafile file number=00010 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
input datafile file number=00012 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
input datafile file number=00015 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:35:43
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:36:48
piece handle=+TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
input datafile file number=00016 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
input datafile file number=00018 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
input datafile file number=00019 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
input datafile file number=00021 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409
input datafile file number=00020 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:36:49
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:37:44
piece handle=+TEST/rman_backup/jy_7lspjl0h_jy_966382609_245_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
input datafile file number=00005 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
input datafile file number=00008 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:37:44
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:38:29
piece handle=+TEST/rman_backup/jy_7mspjl28_jy_966382664_246_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:38:33
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:38:34
piece handle=+TEST/rman_backup/jy_7nspjl3l_jy_966382709_247_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018-01-25 23:38:34

Starting backup at 2018-01-25 23:38:34
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=2 sequence=159 RECID=361 STAMP=966382716
input archived log thread=1 sequence=183 RECID=360 STAMP=966382716
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:38:39
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:38:40
piece handle=+TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1 tag=TAG20180125T233838 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+TEST/arch/2_159_961976319.dbf RECID=361 STAMP=966382716
archived log file name=+TEST/arch/1_183_961976319.dbf RECID=360 STAMP=966382716
Finished backup at 2018-01-25 23:38:41

Starting Control File and SPFILE Autobackup at 2018-01-25 23:38:42
piece handle=+TEST/rman_backup/c-979425723-20180125-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2018-01-25 23:38:50

RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7004    696.52M    DISK        00:01:56     2018-01-25 23:33:07
        BP Key: 7005   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233110
        Piece Name: +TEST/rman_backup/arch_jy_20180125_7ispjklv_1_1

  List of Archived Logs in backup set 7004
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    161     15489267   2018-01-23 03:00:12 15615014   2018-01-23 12:00:12
  1    162     15615014   2018-01-23 12:00:12 15660438   2018-01-23 17:46:58
  1    163     15660438   2018-01-23 17:46:58 15660585   2018-01-23 17:47:21
  1    164     15660585   2018-01-23 17:47:21 15661249   2018-01-23 17:54:26
  1    165     15661249   2018-01-23 17:54:26 15661286   2018-01-23 17:54:34
  1    166     15661286   2018-01-23 17:54:34 15668074   2018-01-23 18:56:16
  1    167     15668074   2018-01-23 18:56:16 15669730   2018-01-23 19:02:53
  1    168     15669730   2018-01-23 19:02:53 15669749   2018-01-23 19:02:59
  1    169     15669749   2018-01-23 19:02:59 15725290   2018-01-23 23:03:04
  1    170     15725290   2018-01-23 23:03:04 15748445   2018-01-23 23:17:11
  1    171     15748445   2018-01-23 23:17:11 15749326   2018-01-23 23:20:56
  1    172     15749326   2018-01-23 23:20:56 15754717   2018-01-23 23:29:08
  1    173     15754717   2018-01-23 23:29:08 15754744   2018-01-23 23:29:16
  1    174     15754744   2018-01-23 23:29:16 15913424   2018-01-24 09:43:49
  1    175     15913424   2018-01-24 09:43:49 15970367   2018-01-24 16:47:52
  1    176     15970367   2018-01-24 16:47:52 15998139   2018-01-24 19:47:17
  1    177     15998139   2018-01-24 19:47:17 15999227   2018-01-24 19:55:33
  1    178     15999227   2018-01-24 19:55:33 16159305   2018-01-24 23:48:02
  1    179     16159305   2018-01-24 23:48:02 16159307   2018-01-24 23:48:02
  1    180     16160440   2018-01-24 23:54:33 16282809   2018-01-25 12:00:25
  1    181     16282809   2018-01-25 12:00:25 16387407   2018-01-25 23:26:30
  1    182     16387407   2018-01-25 23:26:30 16389194   2018-01-25 23:31:04
  2    141     15416434   2018-01-22 23:13:31 15539078   2018-01-23 07:00:05
  2    142     15539078   2018-01-23 07:00:05 15660442   2018-01-23 17:47:00
  2    143     15660442   2018-01-23 17:47:00 15660582   2018-01-23 17:47:21
  2    144     15660582   2018-01-23 17:47:21 15661253   2018-01-23 17:54:28
  2    145     15661253   2018-01-23 17:54:28 15661290   2018-01-23 17:54:34
  2    146     15661290   2018-01-23 17:54:34 15668077   2018-01-23 18:56:16
  2    147     15668077   2018-01-23 18:56:16 15669727   2018-01-23 19:02:53
  2    148     15669727   2018-01-23 19:02:53 15669752   2018-01-23 19:02:59
  2    149     15669752   2018-01-23 19:02:59 15748449   2018-01-23 23:17:13
  2    150     15748449   2018-01-23 23:17:13 15749330   2018-01-23 23:20:56
  2    151     15749330   2018-01-23 23:20:56 15754721   2018-01-23 23:29:09
  2    152     15754721   2018-01-23 23:29:09 15754741   2018-01-23 23:29:15
  2    153     15754741   2018-01-23 23:29:15 15915073   2018-01-24 09:56:08
  2    154     15915073   2018-01-24 09:56:08 15998143   2018-01-24 19:47:20
  2    155     15998143   2018-01-24 19:47:20 15999223   2018-01-24 19:55:33
  2    156     15999223   2018-01-24 19:55:33 16159321   2018-01-24 23:48:04
  2    157     16159321   2018-01-24 23:48:04 16262173   2018-01-25 09:31:46
  2    158     16262173   2018-01-25 09:31:46 16389189   2018-01-25 23:31:00

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7055    Full    596.08M    DISK        00:02:06     2018-01-25 23:35:33
        BP Key: 7100   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1
  List of Datafiles in backup set 7055
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/system.317.962209603
  3       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/sysaux.298.962209605
  4       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/undotbs1.277.962209605
  7       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/users.301.962209605
  9       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/undotbs2.312.962209605

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7056    Full    207.16M    DISK        00:00:55     2018-01-25 23:36:38
        BP Key: 7101   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1
  List of Datafiles in backup set 7056
  Container ID: 3, PDB Name: JYPDB
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
  11      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
  12      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
  13      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
  14      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
  15      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
  22      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
  23      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7057    Full    252.70M    DISK        00:00:53     2018-01-25 23:37:42
        BP Key: 7102   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7lspjl0h_jy_966382609_245_1
  List of Datafiles in backup set 7057
  Container ID: 4, PDB Name: TESTPDB
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  16      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
  17      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
  18      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
  19      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
  20      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
  21      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7058    Full    166.49M    DISK        00:00:36     2018-01-25 23:38:20
        BP Key: 7103   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7mspjl28_jy_966382664_246_1
  List of Datafiles in backup set 7058
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1449535    2017-12-05 23:46:58              NO    +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
  6       Full 1449535    2017-12-05 23:46:58              NO    +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
  8       Full 1449535    2017-12-05 23:46:58              NO    +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7059    Full    4.09M      DISK        00:00:04     2018-01-25 23:38:33
        BP Key: 7104   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7nspjl3l_jy_966382709_247_1
  Control File Included: Ckp SCN: 16392014     Ckp time: 2018-01-25 23:38:29

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7134    5.64M      DISK        00:00:01     2018-01-25 23:38:39
        BP Key: 7136   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233838
        Piece Name: +TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1

  List of Archived Logs in backup set 7134
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    183     16389194   2018-01-25 23:31:04 16392023   2018-01-25 23:38:35
  2    159     16389189   2018-01-25 23:31:00 16392045   2018-01-25 23:38:36

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7145    Full    19.06M     DISK        00:00:04     2018-01-25 23:38:47
        BP Key: 7148   Status: AVAILABLE  Compressed: NO  Tag: TAG20180125T233843
        Piece Name: +TEST/rman_backup/c-979425723-20180125-01
  SPFILE Included: Modification time: 2018-01-25 22:01:06
  SPFILE db_unique_name: JY
  Control File Included: Ckp SCN: 16392079     Ckp time: 2018-01-25 23:38:43

4.创建一个数据库目录对象用来执行Data Pump导出(/ora_backup/tts/dump),一个辅助目录(/ora_backup/tts/auxi),一个传输表空间目录(/ora_backup/tts/tbs)

[oracle@jytest1 tts]$ mkdir dump
[oracle@jytest1 tts]$ mkdir tbs
[oracle@jytest1 tts]$ mkdir auxi

SQL> conn sys/abcd@jy as sysdba
Connected.
SQL> create or replace directory test_dump as '/ora_backup/tts/dump';

Directory created.

SQL>
SQL> grant read,write on directory test_dump to public;

Grant succeeded.

5.使用RMAN连接到源RAC CDB数据库执行传输表空间操作

[oracle@jytest1 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@jytest1 ~]$ rman target sys/abcd@jy  catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jan 26 00:32:53 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

RMAN> transport tablespace JYPDB:"TTS",JYPDB:"CS" tablespace destination '/ora_backup/tts/tbs' auxiliary destination '/ora_backup/tts/auxi'  datapump directory test_dump  dump file 'tts.dmp'  import script 'importtts.sql'  export log 'ttsexport.log';


Creating automatic instance, with SID='jBqz'

initialization parameters used for automatic instance:
db_name=JY
db_unique_name=jBqz_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=/ora_backup/tts/auxi
log_archive_dest_1='location=/ora_backup/tts/auxi'
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
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  scn 16412368;
# 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-26 00:34:32
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-20180125-01
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/c-979425723-20180125-01 tag=TAG20180125T233843
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:38
output file name=/ora_backup/tts/auxi/JY/controlfile/o1_mf_f6n1rxq2_.ctl
Finished restore at 2018-01-26 00:35:11

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  scn 16412368;
# 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;
set newname for datafile  22 to
 "/ora_backup/tts/tbs/o1_mf_tts_%u_.dbf";
set newname for datafile  23 to
 "/ora_backup/tts/tbs/o1_mf_cs_%u_.dbf";
# 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, 22, 23;

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

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /ora_backup/tts/auxi/JY/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2018-01-26 00:35:28
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_backup/tts/auxi/JY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_backup/tts/auxi/JY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1 tag=TAG20180125T233325
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /ora_backup/tts/tbs/o1_mf_tts_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to /ora_backup/tts/tbs/o1_mf_cs_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1 tag=TAG20180125T233325
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2018-01-26 00:39:58

datafile 10 switched to datafile copy
input datafile copy RECID=14 STAMP=966386398 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_f6n20mok_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_system_f6n1tjl2_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=16 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_f6n20mpv_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs1_f6n1tjjq_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=18 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_f6n20mn5_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=966386400 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs2_f6n1tjhj_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=966386400 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_sysaux_f6n1tjfj_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=21 STAMP=966386400 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_f6n20mlb_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=22 STAMP=966386400 file name=/ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=23 STAMP=966386401 file name=/ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 16412368;
# 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";
sql clone 'JYPDB' "alter database datafile
 22 online";
sql clone 'JYPDB' "alter database datafile
 23 online";
# recover and open resetlogs
recover clone database tablespace  "JYPDB":"TTS", "JYPDB":"CS", "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  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

sql statement: alter database datafile  22 online

sql statement: alter database datafile  23 online

Starting recover at 2018-01-26 00:40:04
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 184 is already on disk as file +TEST/arch/1_184_961976319.dbf
archived log for thread 2 with sequence 160 is already on disk as file +TEST/arch/2_160_961976319.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=159
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=183
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1 tag=TAG20180125T233838
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/ora_backup/tts/auxi/2_159_961976319.dbf thread=2 sequence=159
archived log file name=/ora_backup/tts/auxi/1_183_961976319.dbf thread=1 sequence=183
channel clone_default: deleting archived log(s)
archived log file name=/ora_backup/tts/auxi/1_183_961976319.dbf RECID=363 STAMP=966386408
archived log file name=+TEST/arch/1_184_961976319.dbf thread=1 sequence=184
channel clone_default: deleting archived log(s)
archived log file name=/ora_backup/tts/auxi/2_159_961976319.dbf RECID=362 STAMP=966386408
archived log file name=+TEST/arch/2_160_961976319.dbf thread=2 sequence=160
media recovery complete, elapsed time: 00:01:36
Finished recover at 2018-01-26 00:41:44

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:
{
# make read only the tablespace that will be exported
sql clone 'JYPDB' 'alter tablespace
 "TTS" read only';
sql clone 'JYPDB' 'alter tablespace
 "CS" read only';
}
executing Memory Script

sql statement: alter tablespace  "TTS" read only

sql statement: alter tablespace  "CS" read only

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jBqz_ufoz":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.02.00.00.00',newblock)
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 10846
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.02.00.00.00',newblock)
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 10846
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

   EXPDP> Master table "SYS"."TSPITR_EXP_jBqz_ufoz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jBqz_ufoz is:
   EXPDP>   /ora_backup/tts/dump/tts.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace CS:
   EXPDP>   /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf
   EXPDP> Datafiles required for transportable tablespace TTS:
   EXPDP>   /ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jBqz_ufoz" completed with 2 error(s) at Fri Jan 26 00:45:41 2018 elapsed 0 00:02:14
Export completed

Not performing table import after point-in-time recovery
/*
   The following command may be used to import the tablespaces.
   Substitute values for  and .

   impdp  directory= dumpfile= 'tts.dmp' transport_datafiles= /ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf, /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf
*/

--
--
--
--
CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/ora_backup/tts/tbs/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
--
  tbs_files     dbms_streams_tablespace_adm.file_set;
  cvt_files     dbms_streams_tablespace_adm.file_set;

--
  dump_file     dbms_streams_tablespace_adm.file;
  dp_job_name   VARCHAR2(30) := NULL;

--
  ts_names       dbms_streams_tablespace_adm.tablespace_set;
BEGIN
--
  dump_file.file_name :=  'tts.dmp';
  dump_file.directory_object := 'test_dump';

--
  tbs_files( 1).file_name :=  'o1_mf_tts_f6n20mr5_.dbf';
  tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';

  tbs_files( 2).file_name :=  'o1_mf_cs_f6n20mso_.dbf';
  tbs_files( 2).directory_object :=  'STREAMS$DIROBJ$1';

--
  dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);

--
  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
      dbms_output.put_line('imported tablespace '|| ts_names(i));
    END LOOP;
  END IF;
END;
/

--
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_temp_f6n28lj8_.tmp deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_temp_f6n28clb_.tmp deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_4_f6n2699f_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_3_f6n2698j_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_2_f6n2697p_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_1_f6n26971_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_f6n20mlb_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_sysaux_f6n1tjfj_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs2_f6n1tjhj_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_f6n20mn5_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs1_f6n1tjjq_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_f6n20mpv_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_system_f6n1tjl2_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_f6n20mok_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/controlfile/o1_mf_f6n1rxq2_.ctl deleted

上面的传输表空间操作完成后生成了传输表空间的数据文件/ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf,/ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf和Data Pump导出文件dmpfile/ora_backup/tts/dump/tts.dmp,由于我将目标主机的/ora_backup/tts/目录通过nfs挂载成源主机上的/ora_backup/tts/目录,所以这里不需要将这些文件复制就可以在目标主机上访问这些文件。

5.在目标主机上创建相关用户及Data Pump目录(这个目录应该在PDB数据库(jypdb)中创建)并将表空间附加到目标数据库中

SQL> alter session set container=jypdb;

Session altered.

SQL> create or replace directory test_dump as '/ora_backup/tts/dump';

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.


SQL> create user tts identified by "tts";

User created.

SQL> grant dba,connect,resource to tts;

Grant succeeded.

SQL> create user cs identified by "cs";

User created.

SQL> grant dba,connect,resource to cs;

Grant succeeded.

使用system用户连接到PDB数据库(jypdb)执行下面的导入操作

[oracle@shard1 admin]$ impdp system/abcd@jypdb directory=test_dump dumpfile= 'tts.dmp' transport_datafiles= /ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf, /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf

Import: Release 12.2.0.1.0 - Production on Thu Jan 25 19:03: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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@jypdb directory=test_dump dumpfile=tts.dmp transport_datafiles=/ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf, /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.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/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Jan 25 19:04:06 2018 elapsed 0 00:00:29

6.检查数据来验证传输表空间是否成功

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

与表空间传输之前的状态一致,最后还需要导入用户tts,cs下的其它元数据这里不再详细介绍具体的操作了。

Oracle 12C Duplicating a Database to a Remote Host by Using Backup-based Duplication

不连接目标数据库和恢复目录使用备份来将RAC CDB复制成单实例CDB数据库操作如下
1.检查源RAC CDB(jy)的参数文件,数据文件,联机日志文件,控制文件,归档日志文件的存储目录

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------
spfile                               string      +DATA/JY/PARAMETERFILE/spfile.303.961976713

RMAN> report schema;

Report of database schema for database with db_unique_name JY

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    840      SYSTEM               YES     +DATA/JY/DATAFILE/system.317.962209603
3    2340     SYSAUX               NO      +DATA/JY/DATAFILE/sysaux.298.962209605
4    1015     UNDOTBS1             YES     +DATA/JY/DATAFILE/undotbs1.277.962209605
5    250      PDB$SEED:SYSTEM      NO      +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
6    340      PDB$SEED:SYSAUX      NO      +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
7    5        USERS                NO      +DATA/JY/DATAFILE/users.301.962209605
8    100      PDB$SEED:UNDOTBS1    NO      +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
9    1350     UNDOTBS2             YES     +DATA/JY/DATAFILE/undotbs2.312.962209605
10   270      JYPDB:SYSTEM         YES     +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
11   590      JYPDB:SYSAUX         NO      +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
12   205      JYPDB:UNDOTBS1       YES     +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
13   310      JYPDB:UNDO_2         YES     +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
14   1267     JYPDB:USERS          NO      +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
15   100      JYPDB:TEST           NO      +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
16   260      TESTPDB:SYSTEM       YES     +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
17   550      TESTPDB:SYSAUX       NO      +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
18   245      TESTPDB:UNDOTBS1     YES     +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
19   100      TESTPDB:UNDO_2       YES     +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
20   5        TESTPDB:USERS        NO      +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
21   100      TESTPDB:TEST         NO      +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    144      TEMP                 32767       +DATA/JY/TEMPFILE/temp.299.961976339
2    64       PDB$SEED:TEMP        32767       +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865
3    137      JYPDB:TEMP           32767       +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.962210519
4    135      TESTPDB:TEMP         32767       +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/TEMPFILE/temp.258.962469435

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------------
control_files                        string      +DATA/JY/CONTROLFILE/current.272.961976315


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/JY/ONLINELOG/group_2.302.961976321
+DATA/JY/ONLINELOG/group_1.261.961976319
+DATA/JY/ONLINELOG/group_3.263.961976697
+DATA/JY/ONLINELOG/group_4.262.961976705


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +TEST/arch
Oldest online log sequence     127
Next log sequence to archive   128
Current log sequence           128

2.对源RAC CDB数据库创建RMAN备份,这里将远程主机的目录/ora_backup通过nfs挂载到源主机的/ora_backup目录这样可以避免对备份文件进行复制操作

[oracle@jytest1 ~]$ rman target/ catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 24 19:47:12 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

RMAN> run
2> {
3> backup as compressed backupset database format '/ora_backup/jy_%u_%d_%t_%s_%p'  plus archivelog format '/ora_backup/arch_%d_%T_%U';
4> backup current controlfile format'/ora_backup/%d_%s.ctl';
5> backup spfile format '/ora_backup/spf%d_%s.ora';
6> }


Starting backup at 24-JAN-18
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 instance=jy1 device type=DISK
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=2 sequence=141 RECID=320 STAMP=966188816
input archived log thread=1 sequence=161 RECID=321 STAMP=966188818
input archived log thread=2 sequence=142 RECID=323 STAMP=966188832
input archived log thread=1 sequence=162 RECID=322 STAMP=966188829
input archived log thread=1 sequence=163 RECID=325 STAMP=966188842
input archived log thread=2 sequence=143 RECID=324 STAMP=966188842
input archived log thread=2 sequence=144 RECID=327 STAMP=966189268
input archived log thread=1 sequence=164 RECID=326 STAMP=966189266
input archived log thread=1 sequence=165 RECID=328 STAMP=966189274
input archived log thread=2 sequence=145 RECID=329 STAMP=966189274
input archived log thread=1 sequence=166 RECID=330 STAMP=966192976
input archived log thread=2 sequence=146 RECID=331 STAMP=966192977
input archived log thread=1 sequence=167 RECID=333 STAMP=966193374
input archived log thread=2 sequence=147 RECID=332 STAMP=966193373
input archived log thread=2 sequence=148 RECID=334 STAMP=966193380
input archived log thread=1 sequence=168 RECID=335 STAMP=966193380
input archived log thread=1 sequence=169 RECID=336 STAMP=966207796
input archived log thread=2 sequence=149 RECID=338 STAMP=966208644
input archived log thread=1 sequence=170 RECID=337 STAMP=966208634
input archived log thread=1 sequence=171 RECID=339 STAMP=966208856
input archived log thread=2 sequence=150 RECID=340 STAMP=966208857
input archived log thread=1 sequence=172 RECID=341 STAMP=966209350
input archived log thread=2 sequence=151 RECID=342 STAMP=966209350
input archived log thread=1 sequence=173 RECID=344 STAMP=966209356
input archived log thread=2 sequence=152 RECID=343 STAMP=966209356
input archived log thread=2 sequence=153 RECID=346 STAMP=966246980
input archived log thread=1 sequence=174 RECID=345 STAMP=966246243
input archived log thread=1 sequence=175 RECID=347 STAMP=966271688
input archived log thread=2 sequence=154 RECID=349 STAMP=966282445
input archived log thread=1 sequence=176 RECID=348 STAMP=966282440
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/arch_JY_20180124_78spgj6g_1_1 tag=TAG20180124T194727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 24-JAN-18

Starting backup at 24-JAN-18
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=00003 name=+DATA/JY/DATAFILE/sysaux.298.962209605
input datafile file number=00009 name=+DATA/JY/DATAFILE/undotbs2.312.962209605
input datafile file number=00004 name=+DATA/JY/DATAFILE/undotbs1.277.962209605
input datafile file number=00001 name=+DATA/JY/DATAFILE/system.317.962209603
input datafile file number=00007 name=+DATA/JY/DATAFILE/users.301.962209605
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/jy_79spgj9s_JY_966282556_233_1 tag=TAG20180124T194913 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
input datafile file number=00011 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
input datafile file number=00013 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
input datafile file number=00010 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
input datafile file number=00012 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
input datafile file number=00015 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/jy_7aspgjfb_JY_966282731_234_1 tag=TAG20180124T194913 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
input datafile file number=00016 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
input datafile file number=00018 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
input datafile file number=00019 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
input datafile file number=00021 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409
input datafile file number=00020 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/jy_7bspgjhn_JY_966282807_235_1 tag=TAG20180124T194913 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
input datafile file number=00005 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
input datafile file number=00008 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/jy_7cspgjjo_JY_966282872_236_1 tag=TAG20180124T194913 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/jy_7dspgjlg_JY_966282928_237_1 tag=TAG20180124T194913 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JAN-18

Starting backup at 24-JAN-18
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=177 RECID=351 STAMP=966282933
input archived log thread=2 sequence=155 RECID=350 STAMP=966282933
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/arch_JY_20180124_7espgjln_1_1 tag=TAG20180124T195535 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JAN-18

Starting backup at 24-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/JY_239.ctl tag=TAG20180124T195540 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-JAN-18

Starting backup at 24-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-JAN-18
channel ORA_DISK_1: finished piece 1 at 24-JAN-18
piece handle=/ora_backup/spfJY_240.ora tag=TAG20180124T195549 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JAN-18

在目标主机的/ora_backup目录中查看生成的RMAN备份文件
[root@shard1 ora_backup]# ls -lrt
total 1646052
-rw-r-----. 1 oracle 1006 473020928 Jan 24 13:45 arch_JY_20180124_78spgj6g_1_1
-rw-r-----. 1 oracle 1006 615038976 Jan 24 13:48 jy_79spgj9s_JY_966282556_233_1
-rw-r-----. 1 oracle 1006 213581824 Jan 24 13:49 jy_7aspgjfb_JY_966282731_234_1
-rw-r-----. 1 oracle 1006 190742528 Jan 24 13:50 jy_7bspgjhn_JY_966282807_235_1
-rw-r-----. 1 oracle 1006 171524096 Jan 24 13:51 jy_7cspgjjo_JY_966282872_236_1
-rw-r-----. 1 oracle 1006   1163264 Jan 24 13:51 jy_7dspgjlg_JY_966282928_237_1
-rw-r-----. 1 oracle 1006    392192 Jan 24 13:51 arch_JY_20180124_7espgjln_1_1
-rw-r-----. 1 oracle 1006  19972096 Jan 24 13:51 JY_239.ctl
-rw-r-----. 1 oracle 1006    114688 Jan 24 13:51 spfJY_240.ora

3.在目标主机上创建存储目标数据库(dupjy)相关数据库文件的目录,目标主机上的/u01/app/oracle/oradata/dupjy目录用来存储数据库的数据文件,控制文件,联机重做日志文件),/u01/app/oracle/arch/dupjy目录用来存储数据库的归档重做日志文件。

4.将源数据库的密码文件复制到目标主机的相应目录中并修改为目标数据库(dupjy)对应的文件名

[grid@jytest1 ~]$ srvctl config database -db jy
Database unique name: jy
Database name: jy
Oracle home: /u01/app/oracle/product/12.2.0/db
Oracle user: oracle
Spfile: +DATA/JY/PARAMETERFILE/spfile.303.961976713
Password file: +DATA/JY/PASSWORD/pwdjy.274.961976109
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: jy1,jy2
Configured nodes: jytest1,jytest2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

[grid@jytest1 ~]$ asmcmd cp  +DATA/JY/PASSWORD/pwdjy.274.961976109 /home/grid/pwddupjy
copying +DATA/JY/PASSWORD/pwdjy.274.961976109 -> /home/grid/pwddupjy


[oracle@shard1 dbs]# scp grid@10.138.130.171:/home/grid/pwddupjy /u01/app/oracle/product/12.2.0/db/dbs/
The authenticity of host '10.138.130.171 (10.138.130.171)' can't be established.
ECDSA key fingerprint is 7a:62:58:8b:77:98:52:94:d6:d5:0c:c4:6c:87:a6:7f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.171' (ECDSA) to the list of known hosts.
grid@10.138.130.171's password:
pwddupjy

[oracle@shard1 dbs]$ mv pwddupjy orapwdupjy

5.使用源数据库的spfile文件来创建目标数据库要使用的spfile文件
在源数据库中执行下面的命令来创建pfile参数文件

SQL> create pfile from spfile;

File created.

[root@shard1 ~]# scp oracle@10.138.130.171:/u01/app/oracle/product/12.2.0/db/dbs/initjy1.ora /u01/app/oracle/product/12.2.0/db/dbs/
grid@10.138.130.171's password:
initjy1.ora

             100% 1731     1.7KB/s   00:00
[root@shard1 ~]#


[oracle@shard1 dbs]$ cat initdupjy.ora
*.audit_file_dest='/u01/app/oracle/admin/dupjy/adump'
*.cluster_database=false
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/dupjy/control01.ctl'
*.db_block_size=8192
*.db_name='dupjy'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=1g
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_max_size=2147483648
*.sga_target=2147483648
db_file_name_convert=

('+DATA/JY/DATAFILE/','/u01/app/oracle/oradata/dupjy/','+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/','/u01/app/oracle/oradata/dupjy/','+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATA

FILE/','/u01/app/oracle/oradata/dupjy/','+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/','/u01/app/oracle/oradata/dupjy/')
log_file_name_convert=('+DATA/JY/ONLINELOG/','/u01/app/oracle/oradata/dupjy/')



[oracle@shard1 dbs]$ export ORACLE_SID=dupjy
[oracle@shard1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 19:06:00 2018

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/db/dbs/initdupjy.ora'
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size             570428704 bytes
Database Buffers         1560281088 bytes
Redo Buffers                7979008 bytes

SQL> create spfile from pfile='/u01/app/oracle/product/12.2.0/db/dbs/initdupjy.ora';

File created.

6.使用spfile参数文件来启动目标数据库实例(辅助实例dupjy)

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size             570428704 bytes
Database Buffers         1560281088 bytes
Redo Buffers                7979008 bytes


SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- -----------------------------------------------------
spfile                               string                 /u01/app/oracle/product/12.2.0/db/dbs/spfiledupjy.ora

7.给目标数据库配置静态监听

[oracle@shard1 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shard1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dupjy)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
      (GLOBAL_DBNAME=dupjy)
    )
  )


[oracle@shard1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-JAN-2018 20:30:34

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

Starting /u01/app/oracle/product/12.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shard1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                18-JAN-2018 20:30:34
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "dupjy" has 1 instance(s).
  Instance "dupjy", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



8.在目标主机上配置tns,用来通过网络服务名来连接源数据库与目标数据库

[oracle@shard1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.171)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jy)
    )
  )

DUPJY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.180)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dupjy)
    )
  )

9.在目标主机上测试是否可以通过使用网络服务名来连接源数据库与目标数据库

[oracle@shard1 admin]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 21:02:53 2018

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

SQL> conn / as sysdba
Connected.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size             570428704 bytes
Database Buffers         1560281088 bytes
Redo Buffers                7979008 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@shard1 admin]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 21:04:51 2018

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

SQL> conn sys/abcd@dupjy as sysdba
Connected.
SQL> conn sys/abcd@jy as sysdba
Connected.

10.在源主机上配置目标数据库的tns名

[oracle@jytest1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


JY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jytest-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jy)
    )
  )



DUPJY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.180)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dupjy)
      (UR = A)
    )
  )

11.在源主机上测试能否可以使用网络服务名来连接源数据库与目标数据库

[oracle@jytest1 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 19 03:16:59 2018

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

SQL> conn sys/abcd@jy as sysdba
Connected.
SQL> conn sys/abcd@dupjy as sysdba
Connected.

12.使用备份来复制RAC CDB为单实例CDB(dupjy),执行duplicate命令,如果没有配置自动通道,那么至少手动分配一个辅助实例。如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件,且pfile参数文件必须存储在运行RMAN执行复制的主机上。复制命令没有指定数据库名。使用database关键字而不指定数据库名,duplicate将从备份中获得数据库名和DBID。如果在backup location目录中一个数据库有多个备份将会显示错误。使用backup子句来识别没有目标数据库和恢复目录的复制类型。until time选项用来指定恢复目标时间,它是唯一能与backup location子句一起使用的选项。因为副本数据库和原数据库文件的文件名相同所以要指定nofilenamecheck选项:

[oracle@shard1 ora_backup]$ rman auxiliary sys/abcd@dupjy

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 24 17:13:28 2018

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

connected to auxiliary database: JY (not mounted)

RMAN> run
2> {
3> set newname for database to '/u01/app/oracle/oradata/dupjy/%b';
4> duplicate database 'JY' to 'dupjy'
5> logfile group 1 ('/u01/app/oracle/oradata/dupjy/group_1.261.961976319') size 200M reuse,
6> group 2 ('/u01/app/oracle/oradata/dupjy/group_2.302.961976321')  size 200M reuse,
7> group 3 ('/u01/app/oracle/oradata/dupjy/group_3.263.961976697')  size 200M reuse,
8> group 4 ('/u01/app/oracle/oradata/dupjy/group_4.262.961976705')  size 200M reuse
9> backup location '/ora_backup';
10> }

executing command: SET NEWNAME

Starting Duplicate Db at 24-JAN-18

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''JY'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/ora_backup/JY_239.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''JY'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     8794848 bytes
Variable Size               1056967968 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   7979008 bytes

Starting restore at 24-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2269 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/dupjy/control01.ctl
Finished restore at 24-JAN-18

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2269 device type=DISK

contents of Memory Script:
{
   set until scn  15999223;
   sql clone 'alter database flashback off';
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/dupjy/system.317.962209603";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dupjy/sysaux.298.962209605";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dupjy/undotbs1.277.962209605";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/dupjy/system.256.962209675";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dupjy/sysaux.270.962209675";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/dupjy/users.301.962209605";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/dupjy/undotbs1.296.962209675";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/dupjy/undotbs2.312.962209605";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/dupjy/system.271.962209649";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/dupjy/sysaux.316.962209649";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/dupjy/undotbs1.264.962209649";
   set newname for datafile  13 to
 "/u01/app/oracle/oradata/dupjy/undo_2.268.962209649";
   set newname for datafile  14 to
 "/u01/app/oracle/oradata/dupjy/users.278.962209649";
   set newname for datafile  15 to
 "/u01/app/oracle/oradata/dupjy/test.275.962210609";
   set newname for datafile  16 to
 "/u01/app/oracle/oradata/dupjy/system.260.962469409";
   set newname for datafile  17 to
 "/u01/app/oracle/oradata/dupjy/sysaux.259.962469409";
   set newname for datafile  18 to
 "/u01/app/oracle/oradata/dupjy/undotbs1.265.962469409";
   set newname for datafile  19 to
 "/u01/app/oracle/oradata/dupjy/undo_2.266.962469409";
   set newname for datafile  20 to
 "/u01/app/oracle/oradata/dupjy/users.267.962469409";
   set newname for datafile  21 to
 "/u01/app/oracle/oradata/dupjy/test.269.962469409";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database flashback off

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

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

Starting restore at 24-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 /u01/app/oracle/oradata/dupjy/system.317.962209603
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dupjy/sysaux.298.962209605
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dupjy/undotbs1.277.962209605
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/dupjy/users.301.962209605
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/dupjy/undotbs2.312.962209605
channel ORA_AUX_DISK_1: reading from backup piece /ora_backup/jy_79spgj9s_JY_966282556_233_1
channel ORA_AUX_DISK_1: piece handle=/ora_backup/jy_79spgj9s_JY_966282556_233_1 tag=TAG20180124T194913
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
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 /u01/app/oracle/oradata/dupjy/system.271.962209649
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/dupjy/sysaux.316.962209649
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/dupjy/undotbs1.264.962209649
channel ORA_AUX_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/dupjy/undo_2.268.962209649
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/dupjy/users.278.962209649
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/dupjy/test.275.962210609
channel ORA_AUX_DISK_1: reading from backup piece /ora_backup/jy_7aspgjfb_JY_966282731_234_1
channel ORA_AUX_DISK_1: piece handle=/ora_backup/jy_7aspgjfb_JY_966282731_234_1 tag=TAG20180124T194913
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00: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 00016 to /u01/app/oracle/oradata/dupjy/system.260.962469409
channel ORA_AUX_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/dupjy/sysaux.259.962469409
channel ORA_AUX_DISK_1: restoring datafile 00018 to /u01/app/oracle/oradata/dupjy/undotbs1.265.962469409
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/dupjy/undo_2.266.962469409
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u01/app/oracle/oradata/dupjy/users.267.962469409
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/oradata/dupjy/test.269.962469409
channel ORA_AUX_DISK_1: reading from backup piece /ora_backup/jy_7bspgjhn_JY_966282807_235_1
channel ORA_AUX_DISK_1: piece handle=/ora_backup/jy_7bspgjhn_JY_966282807_235_1 tag=TAG20180124T194913
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
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 00005 to /u01/app/oracle/oradata/dupjy/system.256.962209675
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/dupjy/sysaux.270.962209675
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/dupjy/undotbs1.296.962209675
channel ORA_AUX_DISK_1: reading from backup piece /ora_backup/jy_7cspgjjo_JY_966282872_236_1
channel ORA_AUX_DISK_1: piece handle=/ora_backup/jy_7cspgjjo_JY_966282872_236_1 tag=TAG20180124T194913
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 24-JAN-18

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=21 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/system.317.962209603
datafile 3 switched to datafile copy
input datafile copy RECID=22 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/sysaux.298.962209605
datafile 4 switched to datafile copy
input datafile copy RECID=23 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/undotbs1.277.962209605
datafile 5 switched to datafile copy
input datafile copy RECID=24 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/system.256.962209675
datafile 6 switched to datafile copy
input datafile copy RECID=25 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/sysaux.270.962209675
datafile 7 switched to datafile copy
input datafile copy RECID=26 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/users.301.962209605
datafile 8 switched to datafile copy
input datafile copy RECID=27 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/undotbs1.296.962209675
datafile 9 switched to datafile copy
input datafile copy RECID=28 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/undotbs2.312.962209605
datafile 10 switched to datafile copy
input datafile copy RECID=29 STAMP=966273552 file name=/u01/app/oracle/oradata/dupjy/system.271.962209649
datafile 11 switched to datafile copy
input datafile copy RECID=30 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/sysaux.316.962209649
datafile 12 switched to datafile copy
input datafile copy RECID=31 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/undotbs1.264.962209649
datafile 13 switched to datafile copy
input datafile copy RECID=32 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/undo_2.268.962209649
datafile 14 switched to datafile copy
input datafile copy RECID=33 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/users.278.962209649
datafile 15 switched to datafile copy
input datafile copy RECID=34 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/test.275.962210609
datafile 16 switched to datafile copy
input datafile copy RECID=35 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/system.260.962469409
datafile 17 switched to datafile copy
input datafile copy RECID=36 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/sysaux.259.962469409
datafile 18 switched to datafile copy
input datafile copy RECID=37 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/undotbs1.265.962469409
datafile 19 switched to datafile copy
input datafile copy RECID=38 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/undo_2.266.962469409
datafile 20 switched to datafile copy
input datafile copy RECID=39 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/users.267.962469409
datafile 21 switched to datafile copy
input datafile copy RECID=40 STAMP=966273553 file name=/u01/app/oracle/oradata/dupjy/test.269.962469409

contents of Memory Script:
{
   set until scn  15999223;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 24-JAN-18
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=177
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=155
channel ORA_AUX_DISK_1: reading from backup piece /ora_backup/arch_JY_20180124_7espgjln_1_1
channel ORA_AUX_DISK_1: piece handle=/ora_backup/arch_JY_20180124_7espgjln_1_1 tag=TAG20180124T195535
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/oradata/arch_dupjy/1_177_961976319.dbf thread=1 sequence=177
archived log file name=/u01/app/oracle/oradata/arch_dupjy/2_155_961976319.dbf thread=2 sequence=155
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/arch_dupjy/1_177_961976319.dbf RECID=1 STAMP=966273556
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/arch_dupjy/2_155_961976319.dbf RECID=2 STAMP=966273556
media recovery complete, elapsed time: 00:00:02
Finished recover at 24-JAN-18
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     8794848 bytes
Variable Size               1056967968 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   7979008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''DUPJY'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''DUPJY'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     8794848 bytes
Variable Size               1056967968 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   7979008 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPJY" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/dupjy/group_1.261.961976319' ) SIZE 200 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/dupjy/group_2.302.961976321' ) SIZE 200 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/dupjy/group_3.263.961976697' ) SIZE 200 M  REUSE,
  GROUP   4 ( '/u01/app/oracle/oradata/dupjy/group_4.262.961976705' ) SIZE 200 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/dupjy/system.317.962209603',
  '/u01/app/oracle/oradata/dupjy/system.256.962209675',
  '/u01/app/oracle/oradata/dupjy/system.271.962209649',
  '/u01/app/oracle/oradata/dupjy/system.260.962469409'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/dupjy/temp.299.961976339";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/dupjy/temp.297.962209865";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/dupjy/temp.276.962210519";
   set newname for tempfile  4 to
 "/u01/app/oracle/oradata/dupjy/temp.258.962469435";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dupjy/sysaux.298.962209605",
 "/u01/app/oracle/oradata/dupjy/undotbs1.277.962209605",
 "/u01/app/oracle/oradata/dupjy/sysaux.270.962209675",
 "/u01/app/oracle/oradata/dupjy/users.301.962209605",
 "/u01/app/oracle/oradata/dupjy/undotbs1.296.962209675",
 "/u01/app/oracle/oradata/dupjy/undotbs2.312.962209605",
 "/u01/app/oracle/oradata/dupjy/sysaux.316.962209649",
 "/u01/app/oracle/oradata/dupjy/undotbs1.264.962209649",
 "/u01/app/oracle/oradata/dupjy/undo_2.268.962209649",
 "/u01/app/oracle/oradata/dupjy/users.278.962209649",
 "/u01/app/oracle/oradata/dupjy/test.275.962210609",
 "/u01/app/oracle/oradata/dupjy/sysaux.259.962469409",
 "/u01/app/oracle/oradata/dupjy/undotbs1.265.962469409",
 "/u01/app/oracle/oradata/dupjy/undo_2.266.962469409",
 "/u01/app/oracle/oradata/dupjy/users.267.962469409",
 "/u01/app/oracle/oradata/dupjy/test.269.962469409";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dupjy/temp.299.961976339 in control file
renamed tempfile 2 to /u01/app/oracle/oradata/dupjy/temp.297.962209865 in control file
renamed tempfile 3 to /u01/app/oracle/oradata/dupjy/temp.276.962210519 in control file
renamed tempfile 4 to /u01/app/oracle/oradata/dupjy/temp.258.962469435 in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/sysaux.298.962209605 RECID=1 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undotbs1.277.962209605 RECID=2 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/sysaux.270.962209675 RECID=3 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/users.301.962209605 RECID=4 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undotbs1.296.962209675 RECID=5 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undotbs2.312.962209605 RECID=6 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/sysaux.316.962209649 RECID=7 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undotbs1.264.962209649 RECID=8 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undo_2.268.962209649 RECID=9 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/users.278.962209649 RECID=10 STAMP=966273582
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/test.275.962210609 RECID=11 STAMP=966273583
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/sysaux.259.962469409 RECID=12 STAMP=966273583
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undotbs1.265.962469409 RECID=13 STAMP=966273583
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/undo_2.266.962469409 RECID=14 STAMP=966273583
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/users.267.962469409 RECID=15 STAMP=966273583
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dupjy/test.269.962469409 RECID=16 STAMP=966273583

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/sysaux.298.962209605
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/undotbs1.277.962209605
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/sysaux.270.962209675
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/users.301.962209605
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/undotbs1.296.962209675
datafile 9 switched to datafile copy
input datafile copy RECID=6 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/undotbs2.312.962209605
datafile 11 switched to datafile copy
input datafile copy RECID=7 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/sysaux.316.962209649
datafile 12 switched to datafile copy
input datafile copy RECID=8 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/undotbs1.264.962209649
datafile 13 switched to datafile copy
input datafile copy RECID=9 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/undo_2.268.962209649
datafile 14 switched to datafile copy
input datafile copy RECID=10 STAMP=966273582 file name=/u01/app/oracle/oradata/dupjy/users.278.962209649
datafile 15 switched to datafile copy
input datafile copy RECID=11 STAMP=966273583 file name=/u01/app/oracle/oradata/dupjy/test.275.962210609
datafile 17 switched to datafile copy
input datafile copy RECID=12 STAMP=966273583 file name=/u01/app/oracle/oradata/dupjy/sysaux.259.962469409
datafile 18 switched to datafile copy
input datafile copy RECID=13 STAMP=966273583 file name=/u01/app/oracle/oradata/dupjy/undotbs1.265.962469409
datafile 19 switched to datafile copy
input datafile copy RECID=14 STAMP=966273583 file name=/u01/app/oracle/oradata/dupjy/undo_2.266.962469409
datafile 20 switched to datafile copy
input datafile copy RECID=15 STAMP=966273583 file name=/u01/app/oracle/oradata/dupjy/users.267.962469409
datafile 21 switched to datafile copy
input datafile copy RECID=16 STAMP=966273583 file name=/u01/app/oracle/oradata/dupjy/test.269.962469409

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Finished Duplicate Db at 24-JAN-18

13.检查源RAC CDB(jy)是否被成功复制成单实例CDB(dupjy)


SQL> alter session set container=testpdb;

Session altered.

SQL> select count(*) from jy.t1;

  COUNT(*)
----------
        39

SQL> select table_name,tablespace_name from dba_tables where owner='JY';

TABLE_NAME                                                                                                                       TABLESPACE_NAME
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
T1

从上面的输出结果可以看到已经将源RAC CDB数据库复制成为单实例CDB数据库就已经操作完成了。