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

eric0435發表於2018-02-01

這個例子是將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下的其它後設資料這裡不再詳細介紹具體的操作了。

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

相關文章