oracle 12c 使用RMAN的傳輸表空間功能在PDB之間遷移資料

oracle 12c 使用RMAN的傳輸表空間功能在PDB之間遷移資料記錄


1.備份PDB資料庫ORA12CPD的USERS表空間,如下：

oracle@lzstix0itest12:~> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 6 15:17:23 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Oracle Database Vault and Real Application Testing options

SQL> alter session set container=ora12cpd;
Session altered.

SQL> alter tablespace users read only; 
Tablespace altered.

oracle@lzstix0itest12:/tmp> rman target / 

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 6 15:16:51 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA12C (DBID=290586312)
RMAN> backup for transport format '/tmp/tts_readonly.bck' tablespace ORA12CPD:USERS datapump format '/tmp/tts_dump.bck';

Starting backup at 06-JUN-16
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_ORA12C_ahld":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TRANSPORT_EXP_ORA12C_ahld" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TRANSPORT_EXP_ORA12C_ahld is:
   EXPDP>   /u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_ORA12C_54560.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace USERS:
   EXPDP>   /u01/app/oradata/ora12c/ora12cpd/users01.dbf
   EXPDP> Job "SYS"."TRANSPORT_EXP_ORA12C_ahld" successfully completed at Mon Jun 6 15:18:24 2016 elapsed 0 00:00:20
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=00020 name=/u01/app/oradata/ora12c/ora12cpd/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-JUN-16
channel ORA_DISK_1: finished piece 1 at 06-JUN-16
piece handle=/tmp/tts_readonly.bck tag=TAG20160606T151757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
input Data Pump dump file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_ORA12C_54560.dmp
channel ORA_DISK_1: starting piece 1 at 06-JUN-16
channel ORA_DISK_1: finished piece 1 at 06-JUN-16
piece handle=/tmp/tts_dump.bck tag=TAG20160606T151757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-JUN-16


RMAN> exit


備份完成之後,可以看到/tmp目錄下的兩個檔案，一個是後設資料，一個是透過資料泵匯出的資料檔案。
SQL> !ls -l /tmp/tts*
-rw-r----- 1 oracle oinstall    184320 Jun  6 15:18 /tmp/tts_dump.bck
-rw-r----- 1 oracle oinstall  39510016 Jun  6 15:18 /tmp/tts_readonly.bck


匯入至PDB資料庫ORA12CPC資料庫
oracle@lzstix0itest12:/tmp> rman target sys/oracle@lzstix0itest12:11521/ora12cpc
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 6 15:42:08 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA12C (DBID=290586312)

RMAN> RESTORE FOREIGN TABLESPACE USERS FORMAT '/u01/app/oradata/ora12c/ora12cpc/users01.dbf' FROM BACKUPSET '/tmp/tts_readonly.bck' DUMP FILE DATAPUMP DESTINATION '/u01/app/oradata/ora12c/ora12cpc' FROM BACKUPSET '/tmp/tts_dump.bck'; 

Starting restore at 06-JUN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=428 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring all files in foreign tablespace USERS
channel ORA_DISK_1: reading from backup piece /tmp/tts_readonly.bck
channel ORA_DISK_1: restoring foreign file 20 to /u01/app/oradata/ora12c/ora12cpc/users01.dbf
channel ORA_DISK_1: foreign piece handle=/tmp/tts_readonly.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring Data Pump dump file to /u01/app/oradata/ora12c/ora12cpc/backup_tts_ORA12C_49028.dmp
channel ORA_DISK_1: reading from backup piece /tmp/tts_dump.bck
channel ORA_DISK_1: foreign piece handle=/tmp/tts_dump.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_ORA12C_khoD" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_ORA12C_khoD":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_ORA12C_khoD" successfully completed at Mon Jun 6 15:42:18 2016 elapsed 0 00:00:01
Import completed

Finished restore at 06-JUN-16


在ORA12CPC資料庫中將表空間USERS去除只讀
SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
SALES                          ONLINE
USERS                          READ ONLY


SQL> alter tablespace users read write;


Tablespace altered.


SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
SALES                          ONLINE
USERS                          ONLINE


SQL> select count(1) from tt;


  COUNT(1)
----------
    273171


SQL> insert into tt select * from dba_objects;


91123 rows created.


SQL> commit;


Commit complete.


SQL> 
SQL> 
SQL> select count(1) from tt;


  COUNT(1)
----------
    364294