使用until time或者SCN来执行transport tablespace
如果在执行传输表空间操作时，对transport tablespace命令指定了目标时间，那么RMAN将在辅助实例中使用备份将表空间还原到目标时间之前并执行按时间点恢复将辅助数据库恢复到指定的目标时间点。（对于按时间点恢复所需要的备份和重做日志必须可以使用）
恢复的目标时间可以使用SCN（包括当前incarnation或ancestors incarnation)，日志序列号，还原点或时间表达式。
使用SCN来执行transport tablespace
1.确认源数据库与目标数据库所在平台是不是传输表空间所支持的操作系统平台。确认源数据库操作系统平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d 2 where tp.platform_name=d.platform_name ;PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
确认目数据库操作系统平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d 2 where tp.platform_name=d.platform_name ;PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
这里操作系统平台都是Linux
2.确认要被传输的表空间是否是自包含表空间(TSPITR,TEST):
SQL> exec sys.dbms_tts.transport_set_check('TSPITR',true);PL/SQL procedure successfully completed.SQL> exec sys.dbms_tts.transport_set_check('TEST',true);PL/SQL procedure successfully completed.SQL> select * from sys.transport_set_violations;no rows selected
如果没有行选择，表示该表空间只包含表数据，可以传输。
修改表tspitr与test中的记录,在修改之前记录当前的SCN，在执行传输表空间时指定这个SCN
SQL> conn tspitr/tspitrConnected.SQL> select current_scn from v$database;CURRENT_SCN----------- 1167731SQL> select to_char(scn_to_timestamp(1167731),'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SCN_TO_TIME-------------------2015-03-27 16:48:29SQL> insert into tspitr select * from tspitr;50641 rows created.SQL> commit;Commit complete.SQL> select count(*) from tspitr COUNT(*)---------- 101282SQL> conn test/testConnected.SQL> select count(*) from test; COUNT(*)---------- 50680SQL> delete from test;50680 rows deleted.SQL> commit;Commit complete.SQL> select count(*) from test; COUNT(*)---------- 0
在SCN=1167731之后，表tspitr中的记录数为101282，表test的记录数为0,SCN=1167731之前，表tspitr中的记录数为50641，表test的记录数为50680
3.对源数据库执行备份，在执行备份时一定要加上include current controlfile否则执行transport tablespace命令时会出现以下错误信息：
Automatic instance removedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of tranport tablespace command at 03/26/2015 20:24:22RMAN-03015: error occurred in stored script Memory ScriptRMAN-06026: some targets not found - aborting restoreRMAN-06024: no backup or copy of the control file found to restore[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Thu Mar 26 20:01:33 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: TEST (DBID=2168949517)connected to recovery catalog databaseRMAN> backup as backupset database include current controlfile plus archivelog;Starting backup at 2015-03-26 20:32:19current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=68 recid=75 stamp=875286167input archive log thread=1 sequence=69 recid=76 stamp=875291999input archive log thread=1 sequence=70 recid=77 stamp=875302397input archive log thread=1 sequence=71 recid=78 stamp=875308491input archive log thread=1 sequence=72 recid=79 stamp=875350203input archive log thread=1 sequence=73 recid=80 stamp=875351397input archive log thread=1 sequence=74 recid=81 stamp=875390545input archive log thread=1 sequence=75 recid=82 stamp=875390643input archive log thread=1 sequence=76 recid=83 stamp=875391627input archive log thread=1 sequence=77 recid=84 stamp=875391661input archive log thread=1 sequence=78 recid=85 stamp=875391764input archive log thread=1 sequence=79 recid=86 stamp=875392340channel ORA_DISK_1: starting piece 1 at 2015-03-26 20:32:22channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:32:29piece handle=/u02/ora_test875392341_991 tag=TAG20150326T203220 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:08Finished backup at 2015-03-26 20:32:29Starting backup at 2015-03-26 20:32:29using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbfinput datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbfchannel ORA_DISK_1: starting piece 1 at 2015-03-26 20:32:30channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:33:45piece handle=/u02/ora_test875392350_1001 tag=TAG20150326T203229 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:15channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetchannel ORA_DISK_1: starting piece 1 at 2015-03-26 20:33:46channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:33:47piece handle=/u02/ora_test875392425_1011 tag=TAG20150326T203229 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 2015-03-26 20:33:47Starting backup at 2015-03-26 20:33:47current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=80 recid=87 stamp=875392427channel ORA_DISK_1: starting piece 1 at 2015-03-26 20:33:49channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:33:50piece handle=/u02/ora_test875392428_1021 tag=TAG20150326T203348 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 2015-03-26 20:33:50Starting Control File and SPFILE Autobackup at 2015-03-26 20:33:50piece handle=/u02/c-2168949517-20150326-07 comment=NONEFinished Control File and SPFILE Autobackup at 2015-03-26 20:33:54RMAN> list backup;List of Backup Sets===================BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ -------------------6565 56.14M DISK 00:00:07 2015-03-26 20:32:28 BP Key: 6566 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203220 Piece Name: /u02/ora_test875392341_991 List of Archived Logs in backup set 6565 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 68 1040304 2015-03-25 09:33:41 1064641 2015-03-25 15:02:45 1 69 1064641 2015-03-25 15:02:45 1067420 2015-03-25 16:39:58 1 70 1067420 2015-03-25 16:39:58 1090668 2015-03-25 19:33:17 1 71 1090668 2015-03-25 19:33:17 1093434 2015-03-25 21:14:51 1 72 1093434 2015-03-25 21:14:51 1125870 2015-03-26 08:50:00 1 73 1125870 2015-03-26 08:50:00 1126817 2015-03-26 09:09:56 1 74 1126817 2015-03-26 09:09:56 1144051 2015-03-26 20:02:21 1 75 1144051 2015-03-26 20:02:21 1144106 2015-03-26 20:04:02 1 76 1144106 2015-03-26 20:04:02 1144862 2015-03-26 20:20:27 1 77 1144862 2015-03-26 20:20:27 1144893 2015-03-26 20:21:00 1 78 1144893 2015-03-26 20:21:00 1144980 2015-03-26 20:22:44 1 79 1144980 2015-03-26 20:22:44 1145259 2015-03-26 20:32:19BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ -------------------6607 Full 640.39M DISK 00:01:08 2015-03-26 20:33:38 BP Key: 6621 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203229 Piece Name: /u02/ora_test875392350_1001 List of Datafiles in backup set 6607 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/system01.dbf 2 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/users01.dbf 5 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/example01.dbf 6 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/tspitr01.dbf 7 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/test01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ -------------------6608 Full 7.02M DISK 00:00:01 2015-03-26 20:33:46 BP Key: 6622 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203229 Piece Name: /u02/ora_test875392425_1011 Control File Included: Ckp SCN: 1145304 Ckp time: 2015-03-26 20:33:45BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ -------------------6658 2.50K DISK 00:00:01 2015-03-26 20:33:49 BP Key: 6660 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203348 Piece Name: /u02/ora_test875392428_1021 List of Archived Logs in backup set 6658 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 80 1145259 2015-03-26 20:32:19 1145307 2015-03-26 20:33:47BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ -------------------6689 Full 7.05M DISK 00:00:01 2015-03-26 20:33:52 BP Key: 6691 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203351 Piece Name: /u02/c-2168949517-20150326-07 Control File Included: Ckp SCN: 1145329 Ckp time: 2015-03-26 20:33:51 SPFILE Included: Modification time: 2015-03-26 19:31:13
4.在这里指定了auxiliary destination子句让RMAN使用缺省值来管理辅助实例。只指定必须的选项。Oracle建议transport tablespace命令使用辅助目录秋简化辅助实例文件的管理。下面来执transport tablespace命令来传输tspitr,test两个表空间:
[oracle@oracle11g ~]$ export NLS_DATE_FORMATE='yyyy-mm-dd hh24:mi:ss';[oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Fri Mar 27 17:09:07 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: TEST (DBID=2168949517)connected to recovery catalog databaseRMAN> transport tablespace "TSPITR","TEST" tablespace destination '/u02/transport' auxiliary destination '/u02/transport' until scn 1167731;Creating automatic instance, with SID='kayd'initialization parameters used for automatic instance:db_name=TESTcompatible=10.2.0.5.0db_block_size=8192db_files=200db_unique_name=tspitr_TEST_kaydsga_target=180Mprocesses=50#No auxiliary parameter file useddb_create_file_dest=/u02/transportcontrol_files=/u02/transport/cntrl_tspitr_TEST_kayd.fstarting up automatic instance TESTOracle instance startedTotal System Global Area 188743680 bytesFixed Size 1272720 bytesVariable Size 62915696 bytesDatabase Buffers 121634816 bytesRedo Buffers 2920448 bytesAutomatic instance createdcontents of Memory Script:{# set the until clauseset until scn 1167731;# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online log for tspitr to a resent until timesql 'alter system archive log current';# avoid unnecessary autobackups for structural changes during TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';# resync catalog after controlfile restoreresync catalog;}executing Memory Scriptexecuting command: SET until clauseStarting restore at 27-MAR-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=47 devtype=DISKchannel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /u02/c-2168949517-20150326-07channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/c-2168949517-20150326-07 tag=TAG20150326T203351channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01output filename=/u02/transport/cntrl_tspitr_TEST_kayd.fFinished restore at 27-MAR-15sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;starting full resync of recovery catalogfull resync completecontents of Memory Script:{# generated tablespace point-in-time recovery script# set the until clauseset until scn 1167731;# set an omf destination filename for restoreset newname for clone datafile 1 to new;# set an omf destination filename for restoreset newname for clone datafile 2 to new;# set an omf destination filename for restoreset newname for clone datafile 3 to new;# set an omf destination tempfileset newname for clone tempfile 1 to new;# set a destination filename for restoreset newname for datafile 6 to "/u02/transport/tspitr01.dbf";# set a destination filename for restoreset newname for datafile 7 to "/u02/transport/test01.dbf";# rename all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 3, 6, 7;switch clone datafile all;#online the datafiles restored or flippedsql clone "alter database datafile 1 online";#online the datafiles restored or flippedsql clone "alter database datafile 2 online";#online the datafiles restored or flippedsql clone "alter database datafile 3 online";#online the datafiles restored or flippedsql clone "alter database datafile 6 online";#online the datafiles restored or flippedsql clone "alter database datafile 7 online";# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace "TSPITR", "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;alter clone database open resetlogs;# PLUG HERE the creation of a temporary tablespace if export fails due to lack# of temporary space.# For example in Unix these two lines would do that:#sql clone "create tablespace aux_tspitr_tmp# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed temporary file 1 to /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 27-MAR-15using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_system_%u_.dbfrestoring datafile 00002 to /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_undotbs1_%u_.dbfrestoring datafile 00003 to /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_sysaux_%u_.dbfrestoring datafile 00006 to /u02/transport/tspitr01.dbfrestoring datafile 00007 to /u02/transport/test01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875392350_1001channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/ora_test875392350_1001 tag=TAG20150326T203229channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05Finished restore at 27-MAR-15datafile 1 switched to datafile copyinput datafile copy recid=22 stamp=875466695 filename=/u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_system_bkb7lb6k_.dbfdatafile 2 switched to datafile copyinput datafile copy recid=23 stamp=875466695 filename=/u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_undotbs1_bkb7lbfr_.dbfdatafile 3 switched to datafile copyinput datafile copy recid=24 stamp=875466695 filename=/u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_sysaux_bkb7lb6y_.dbfdatafile 6 switched to datafile copyinput datafile copy recid=25 stamp=875466695 filename=/u02/transport/tspitr01.dbfdatafile 7 switched to datafile copyinput datafile copy recid=26 stamp=875466695 filename=/u02/transport/test01.dbfsql statement: alter database datafile 1 onlinesql statement: alter database datafile 2 onlinesql statement: alter database datafile 3 onlinesql statement: alter database datafile 6 onlinesql statement: alter database datafile 7 onlineStarting recover at 27-MAR-15using channel ORA_AUX_DISK_1starting media recoveryarchive log thread 1 sequence 80 is already on disk as file /u02/1_80_870806981.dbfarchive log thread 1 sequence 81 is already on disk as file /u02/1_81_870806981.dbfarchive log thread 1 sequence 82 is already on disk as file /u02/1_82_870806981.dbfarchive log thread 1 sequence 83 is already on disk as file /u02/1_83_870806981.dbfarchive log filename=/u02/1_80_870806981.dbf thread=1 sequence=80archive log filename=/u02/1_81_870806981.dbf thread=1 sequence=81archive log filename=/u02/1_82_870806981.dbf thread=1 sequence=82archive log filename=/u02/1_83_870806981.dbf thread=1 sequence=83media recovery complete, elapsed time: 00:00:08Finished recover at 27-MAR-15database openedcontents of Memory Script:{#mark read only the tablespace that will be exportedsql clone "alter tablespace TSPITR read only";#mark read only the tablespace that will be exportedsql clone "alter tablespace TEST read only";# create directory for datapump exportsql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''/u02/transport''";# export the tablespaces in the recovery sethost 'expdp userid=\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclekayd\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=kayd^'\)\)\(CONNECT_DATA=\(SID=kayd\)\)\) as sysdba\" transport_tablespaces= TSPITR, TEST dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log';}executing Memory Scriptsql statement: alter tablespace TSPITR read onlysql statement: alter tablespace TEST read onlysql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/u02/transport''Export: Release 10.2.0.5.0 - Production on Friday, 27 March, 2015 17:12:19Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle)(ARGV0=oraclekayd)(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=kayd))(CONNECT_DATA=(SID=kayd))) AS SYSDBA" transport_tablespaces= TSPITR, TEST dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: /u02/transport/dmpfile.dmpJob "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:13:05host command complete/* The following command may be used to import the tablespaces. Substitute values for and . impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /u02/transport/tspitr01.dbf, /u02/transport/test01.dbf*/---------------------------------------------------------------- Start of sample PL/SQL script for importing the tablespaces---------------------------------------------------------------- creating directory objectsCREATE DIRECTORY STREAMS$DIROBJ$1 AS '/u02/transport/';CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/u02/transport';/* PL/SQL Script to import the exported tablespaces */DECLARE -- the datafiles tbs_files dbms_streams_tablespace_adm.file_set; cvt_files dbms_streams_tablespace_adm.file_set; -- the dumpfile to import dump_file dbms_streams_tablespace_adm.file; dp_job_name VARCHAR2(30) := NULL; -- names of tablespaces that were imported ts_names dbms_streams_tablespace_adm.tablespace_set;BEGIN -- dump file name and location dump_file.file_name := 'dmpfile.dmp'; dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR'; -- forming list of datafiles for import tbs_files( 1).file_name := 'tspitr01.dbf'; tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1'; tbs_files( 2).file_name := 'test01.dbf'; tbs_files( 2).directory_object := 'STREAMS$DIROBJ$1'; -- import tablespaces 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); -- output names of imported tablespaces 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;/-- dropping directory objectsDROP DIRECTORY STREAMS$DIROBJ$1;DROP DIRECTORY STREAMS$DIROBJ$DPDIR;---------------------------------------------------------------- End of sample PL/SQL script--------------------------------------------------------------Removing automatic instanceshutting down automatic instanceOracle instance shut downAutomatic instance removedauxiliary instance file /u02/transport/cntrl_tspitr_TEST_kayd.f deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_system_bkb7lb6k_.dbf deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_undotbs1_bkb7lbfr_.dbf deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_sysaux_bkb7lb6y_.dbf deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_temp_bkb7q2ql_.tmp deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/onlinelog/o1_mf_1_bkb7pnmt_.log deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/onlinelog/o1_mf_2_bkb7pqk4_.log deletedauxiliary instance file /u02/transport/TSPITR_TEST_KAYD/onlinelog/o1_mf_3_bkb7ptbb_.log deleted
5.将步骤4生成的传输表空间的数据文件tspitr01.dbf,test01.dbf和Data Pump导出文件dmpfile拷贝到目标主机上的/u02目录中
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/tspitr01.dbf /u02oracle@192.168.56.2's password:tspitr01.dbf 100% 100MB 7.1MB/s 00:14[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/test01.dbf /u02oracle@192.168.56.2's password:test01.dbf 100% 10MB 10.0MB/s 00:00[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/dmpfile.dmp /u02oracle@192.168.56.2's password:dmpfile.dmp 100% 96KB 96.0KB/s 00:00[oracle@jingyong1 u02]$ ls -lrttotal 112876-rw-r----- 1 oracle oinstall 104865792 Mar 27 17:54 tspitr01.dbf-rw-r----- 1 oracle oinstall 10493952 Mar 27 18:22 test01.dbf-rw-r----- 1 oracle oinstall 98304 Mar 27 18:22 dmpfile.dmp
6.在目录主机上创建相关用户及Data Pump目录并将表空间附加到目标数据库中
SQL> create user tspitr identified by "tspitr";User created.SQL> grant dba,connect,resource to tspitr;Grant succeeded.SQL> create user test identified by "test";User created.SQL> grant dba,connect,resource to test;Grant succeeded.SQL> create directory mytest as '/u02';Directory created.SQL> grant read,write on directory mytest to public;Grant succeeded.
最后就可以执行导入过程了
[oracle@jingyong1 ~]$ export ORACLE_SID=jy[oracle@jingyong1 ~]$ export NLS_DATE_FORMATE='yyyy-mm-dd hh24:mi:ss'[oracle@jingyong1 ~]$ impdp test/test directory=mytest dumpfile=dmpfile.dmp logfile=testtransport.log transport_datafiles= /u02/tspitr01.dbf, /u02/test01.dbfImport: Release 10.2.0.5.0 - Production on Friday, 27 March, 2015 18:25:25Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "TEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "TEST"."SYS_IMPORT_TRANSPORTABLE_01": test/******** directory=mytest dumpfile=dmpfile.dmp logfile=testtransport.log transport_datafiles= /u02/tspitr01.dbf, /u02/test01.dbfProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "TEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:25:49
进入数据库中检查一下
SQL> select count(*) from tspitr.tspitr; COUNT(*)---------- 50641SQL> select count(*) from test.test; COUNT(*)---------- 50680
与SCN=1167731之前的状态一致。
下面来演示使用until time执行传输表空间
1.确认源数据库与目标数据库所在平台是不是传输表空间所支持的操作系统平台。
确认源数据库操作系统平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d 2 where tp.platform_name=d.platform_name ;PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
确认目数据库操作系统平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d 2 where tp.platform_name=d.platform_name ;PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
这里操作系统平台都是Linux
2.确认要被传输的表空间是否是自包含表空间(TSPITR,TEST):
SQL> exec sys.dbms_tts.transport_set_check('TSPITR',true);PL/SQL procedure successfully completed.SQL> exec sys.dbms_tts.transport_set_check('TEST',true);PL/SQL procedure successfully completed.SQL> select * from sys.transport_set_violations;no rows selected
如果没有行选择，表示该表空间只包含表数据，可以传输。
修改表tspitr与test中的记录
在修改之前记录当前的系统时间，在执行传输表空间时指定这个系统时间。
SQL> conn tspitr/tspitrConnected.SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SCN_TO_TIME-------------------2015-03-27 16:48:29SQL> insert into tspitr select * from tspitr;50641 rows created.SQL> commit;Commit complete.SQL> select count(*) from tspitr COUNT(*)---------- 101282SQL> conn test/testConnected.SQL> select count(*) from test; COUNT(*)---------- 50680SQL> delete from test;50680 rows deleted.SQL> commit;Commit complete.SQL> select count(*) from test; COUNT(*)---------- 0
在2015-03-27 16:48:29之后，表tspitr中的记录数为101282，表test的记录数为0,2015-03-27 16:48:29之前，表tspitr中的记录数为50641，表test的记录数为50680
3.对源数据库执行备份，在执行备份时一定要加上include current controlfile否则执行transport tablespace命令时会出现以下错误信息：
Automatic instance removedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of tranport tablespace command at 03/26/2015 20:24:22RMAN-03015: error occurred in stored script Memory ScriptRMAN-06026: some targets not found - aborting restoreRMAN-06024: no backup or copy of the control file found to restore
[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Thu Mar 26 20:01:33 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: TEST (DBID=2168949517)connected to recovery catalog databaseRMAN> backup as backupset database include current controlfile plus archivelog;Starting backup at 2015-03-26 20:32:19current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=68 recid=75 stamp=875286167input archive log thread=1 sequence=69 recid=76 stamp=875291999input archive log thread=1 sequence=70 recid=77 stamp=875302397input archive log thread=1 sequence=71 recid=78 stamp=875308491input archive log thread=1 sequence=72 recid=79 stamp=875350203input archive log thread=1 sequence=73 recid=80 stamp=875351397input archive log thread=1 sequence=74 recid=81 stamp=875390545input archive log thread=1 sequence=75 recid=82 stamp=875390643input archive log thread=1 sequence=76 recid=83 stamp=875391627input archive log thread=1 sequence=77 recid=84 stamp=875391661input archive log thread=1 sequence=78 recid=85 stamp=875391764input archive log thread=1 sequence=79 recid=86 stamp=875392340channel ORA_DISK_1: starting piece 1 at 2015-03-26 20:32:22channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:32:29piece handle=/u02/ora_test875392341_991 tag=TAG20150326T203220 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:08Finished backup at 2015-03-26 20:32:29Starting backup at 2015-03-26 20:32:29using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbfinput datafile fno=00007 name=/u01/app/oracle/oradata/test/test01.dbfchannel ORA_DISK_1: starting piece 1 at 2015-03-26 20:32:30channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:33:45piece handle=/u02/ora_test875392350_1001 tag=TAG20150326T203229 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:15channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetchannel ORA_DISK_1: starting piece 1 at 2015-03-26 20:33:46channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:33:47piece handle=/u02/ora_test875392425_1011 tag=TAG20150326T203229 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 2015-03-26 20:33:47Starting backup at 2015-03-26 20:33:47current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=80 recid=87 stamp=875392427channel ORA_DISK_1: starting piece 1 at 2015-03-26 20:33:49channel ORA_DISK_1: finished piece 1 at 2015-03-26 20:33:50piece handle=/u02/ora_test875392428_1021 tag=TAG20150326T203348 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 2015-03-26 20:33:50Starting Control File and SPFILE Autobackup at 2015-03-26 20:33:50piece handle=/u02/c-2168949517-20150326-07 comment=NONEFinished Control File and SPFILE Autobackup at 2015-03-26 20:33:54RMAN> list backup;List of Backup Sets===================BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ -------------------6565 56.14M DISK 00:00:07 2015-03-26 20:32:28 BP Key: 6566 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203220 Piece Name: /u02/ora_test875392341_991 List of Archived Logs in backup set 6565 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 68 1040304 2015-03-25 09:33:41 1064641 2015-03-25 15:02:45 1 69 1064641 2015-03-25 15:02:45 1067420 2015-03-25 16:39:58 1 70 1067420 2015-03-25 16:39:58 1090668 2015-03-25 19:33:17 1 71 1090668 2015-03-25 19:33:17 1093434 2015-03-25 21:14:51 1 72 1093434 2015-03-25 21:14:51 1125870 2015-03-26 08:50:00 1 73 1125870 2015-03-26 08:50:00 1126817 2015-03-26 09:09:56 1 74 1126817 2015-03-26 09:09:56 1144051 2015-03-26 20:02:21 1 75 1144051 2015-03-26 20:02:21 1144106 2015-03-26 20:04:02 1 76 1144106 2015-03-26 20:04:02 1144862 2015-03-26 20:20:27 1 77 1144862 2015-03-26 20:20:27 1144893 2015-03-26 20:21:00 1 78 1144893 2015-03-26 20:21:00 1144980 2015-03-26 20:22:44 1 79 1144980 2015-03-26 20:22:44 1145259 2015-03-26 20:32:19BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ -------------------6607 Full 640.39M DISK 00:01:08 2015-03-26 20:33:38 BP Key: 6621 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203229 Piece Name: /u02/ora_test875392350_1001 List of Datafiles in backup set 6607 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/system01.dbf 2 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/users01.dbf 5 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/example01.dbf 6 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/tspitr01.dbf 7 Full 1145279 2015-03-26 20:32:30 /u01/app/oracle/oradata/test/test01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ -------------------6608 Full 7.02M DISK 00:00:01 2015-03-26 20:33:46 BP Key: 6622 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203229 Piece Name: /u02/ora_test875392425_1011 Control File Included: Ckp SCN: 1145304 Ckp time: 2015-03-26 20:33:45BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ -------------------6658 2.50K DISK 00:00:01 2015-03-26 20:33:49 BP Key: 6660 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203348 Piece Name: /u02/ora_test875392428_1021 List of Archived Logs in backup set 6658 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 80 1145259 2015-03-26 20:32:19 1145307 2015-03-26 20:33:47BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ -------------------6689 Full 7.05M DISK 00:00:01 2015-03-26 20:33:52 BP Key: 6691 Status: AVAILABLE Compressed: NO Tag: TAG20150326T203351 Piece Name: /u02/c-2168949517-20150326-07 Control File Included: Ckp SCN: 1145329 Ckp time: 2015-03-26 20:33:51 SPFILE Included: Modification time: 2015-03-26 19:31:13
4.在这里指定了auxiliary destination子句让RMAN使用缺省值来管理辅助实例。只指定必须的选项。Oracle建议transport tablespace命令使用辅助目录秋简化辅助实例文件的管理。下面来执transport tablespace命令来传输tspitr,test两个表空间:
RMAN> transport tablespace "TSPITR","TEST" tablespace destination '/u02/transport' auxiliary destination '/u02/transport' until time "to_date('2015-03-27 16:48:29','yyyy-mm-dd hh24:mi:ss')";Creating automatic instance, with SID='wkgj'initialization parameters used for automatic instance:db_name=TESTcompatible=10.2.0.5.0db_block_size=8192db_files=200db_unique_name=tspitr_TEST_wkgjsga_target=180Mprocesses=50#No auxiliary parameter file useddb_create_file_dest=/u02/transportcontrol_files=/u02/transport/cntrl_tspitr_TEST_wkgj.fstarting up automatic instance TESTOracle instance startedTotal System Global Area 188743680 bytesFixed Size 1272720 bytesVariable Size 62915696 bytesDatabase Buffers 121634816 bytesRedo Buffers 2920448 bytesAutomatic instance createdcontents of Memory Script:{# set the until clauseset until time "to_date('2015-03-27 16:48:29','yyyy-mm-dd hh24:mi:ss')";# restore the controlfilerestore clone controlfile;# mount the controlfilesql clone 'alter database mount clone database';# archive current online log for tspitr to a resent until timesql 'alter system archive log current';# avoid unnecessary autobackups for structural changes during TSPITRsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';# resync catalog after controlfile restoreresync catalog;}executing Memory Scriptexecuting command: SET until clauseStarting restore at 27-MAR-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=47 devtype=DISKchannel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: reading from backup piece /u02/c-2168949517-20150326-07channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/c-2168949517-20150326-07 tag=TAG20150326T203351channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03output filename=/u02/transport/cntrl_tspitr_TEST_wkgj.fFinished restore at 27-MAR-15sql statement: alter database mount clone databasesql statement: alter system archive log currentsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;starting full resync of recovery catalogfull resync completecontents of Memory Script:{# generated tablespace point-in-time recovery script# set the until clauseset until time "to_date('2015-03-27 16:48:29','yyyy-mm-dd hh24:mi:ss')";# set an omf destination filename for restoreset newname for clone datafile 1 to new;# set an omf destination filename for restoreset newname for clone datafile 2 to new;# set an omf destination filename for restoreset newname for clone datafile 3 to new;# set an omf destination tempfileset newname for clone tempfile 1 to new;# set a destination filename for restoreset newname for datafile 6 to "/u02/transport/tspitr01.dbf";# set a destination filename for restoreset newname for datafile 7 to "/u02/transport/test01.dbf";# rename all tempfilesswitch clone tempfile all;# restore the tablespaces in the recovery set plus the auxilliary tablespacesrestore clone datafile 1, 2, 3, 6, 7;switch clone datafile all;#online the datafiles restored or flippedsql clone "alter database datafile 1 online";#online the datafiles restored or flippedsql clone "alter database datafile 2 online";#online the datafiles restored or flippedsql clone "alter database datafile 3 online";#online the datafiles restored or flippedsql clone "alter database datafile 6 online";#online the datafiles restored or flippedsql clone "alter database datafile 7 online";# make the controlfile point at the restored datafiles, then recover themrecover clone database tablespace "TSPITR", "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;alter clone database open resetlogs;# PLUG HERE the creation of a temporary tablespace if export fails due to lack# of temporary space.# For example in Unix these two lines would do that:#sql clone "create tablespace aux_tspitr_tmp# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed temporary file 1 to /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_temp_%u_.tmp in control fileStarting restore at 27-MAR-15using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_system_%u_.dbfrestoring datafile 00002 to /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_undotbs1_%u_.dbfrestoring datafile 00003 to /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_sysaux_%u_.dbfrestoring datafile 00006 to /u02/transport/tspitr01.dbfrestoring datafile 00007 to /u02/transport/test01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875392350_1001channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/ora_test875392350_1001 tag=TAG20150326T203229channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:16Finished restore at 27-MAR-15datafile 1 switched to datafile copyinput datafile copy recid=22 stamp=875478089 filename=/u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_system_bkblqy42_.dbfdatafile 2 switched to datafile copyinput datafile copy recid=23 stamp=875478089 filename=/u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_undotbs1_bkblqy5h_.dbfdatafile 3 switched to datafile copyinput datafile copy recid=24 stamp=875478089 filename=/u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_sysaux_bkblqy4h_.dbfdatafile 6 switched to datafile copyinput datafile copy recid=25 stamp=875478089 filename=/u02/transport/tspitr01.dbfdatafile 7 switched to datafile copyinput datafile copy recid=26 stamp=875478089 filename=/u02/transport/test01.dbfsql statement: alter database datafile 1 onlinesql statement: alter database datafile 2 onlinesql statement: alter database datafile 3 onlinesql statement: alter database datafile 6 onlinesql statement: alter database datafile 7 onlineStarting recover at 27-MAR-15using channel ORA_AUX_DISK_1starting media recoveryarchive log thread 1 sequence 80 is already on disk as file /u02/1_80_870806981.dbfarchive log thread 1 sequence 81 is already on disk as file /u02/1_81_870806981.dbfarchive log thread 1 sequence 82 is already on disk as file /u02/1_82_870806981.dbfarchive log thread 1 sequence 83 is already on disk as file /u02/1_83_870806981.dbfarchive log filename=/u02/1_80_870806981.dbf thread=1 sequence=80archive log filename=/u02/1_81_870806981.dbf thread=1 sequence=81archive log filename=/u02/1_82_870806981.dbf thread=1 sequence=82archive log filename=/u02/1_83_870806981.dbf thread=1 sequence=83media recovery complete, elapsed time: 00:00:07Finished recover at 27-MAR-15database openedcontents of Memory Script:{#mark read only the tablespace that will be exportedsql clone "alter tablespace TSPITR read only";#mark read only the tablespace that will be exportedsql clone "alter tablespace TEST read only";# create directory for datapump exportsql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''/u02/transport''";# export the tablespaces in the recovery sethost 'expdp userid=\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclewkgj\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=wkgj^'\)\)\(CONNECT_DATA=\(SID=wkgj\)\)\) as sysdba\" transport_tablespaces= TSPITR, TEST dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log';}executing Memory Scriptsql statement: alter tablespace TSPITR read onlysql statement: alter tablespace TEST read onlysql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/u02/transport''Export: Release 10.2.0.5.0 - Production on Friday, 27 March, 2015 20:22:11Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle)(ARGV0=oraclewkgj)(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=wkgj))(CONNECT_DATA=(SID=wkgj))) AS SYSDBA" transport_tablespaces= TSPITR, TEST dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: /u02/transport/dmpfile.dmpJob "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:23:07host command complete/* The following command may be used to import the tablespaces. Substitute values for and . impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /u02/transport/tspitr01.dbf, /u02/transport/test01.dbf*/---------------------------------------------------------------- Start of sample PL/SQL script for importing the tablespaces---------------------------------------------------------------- creating directory objectsCREATE DIRECTORY STREAMS$DIROBJ$1 AS '/u02/transport/';CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/u02/transport';/* PL/SQL Script to import the exported tablespaces */DECLARE -- the datafiles tbs_files dbms_streams_tablespace_adm.file_set; cvt_files dbms_streams_tablespace_adm.file_set; -- the dumpfile to import dump_file dbms_streams_tablespace_adm.file; dp_job_name VARCHAR2(30) := NULL; -- names of tablespaces that were imported ts_names dbms_streams_tablespace_adm.tablespace_set;BEGIN -- dump file name and location dump_file.file_name := 'dmpfile.dmp'; dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR'; -- forming list of datafiles for import tbs_files( 1).file_name := 'tspitr01.dbf'; tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1'; tbs_files( 2).file_name := 'test01.dbf'; tbs_files( 2).directory_object := 'STREAMS$DIROBJ$1'; -- import tablespaces 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); -- output names of imported tablespaces 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;/-- dropping directory objectsDROP DIRECTORY STREAMS$DIROBJ$1;DROP DIRECTORY STREAMS$DIROBJ$DPDIR;---------------------------------------------------------------- End of sample PL/SQL script--------------------------------------------------------------Removing automatic instanceshutting down automatic instanceOracle instance shut downAutomatic instance removedauxiliary instance file /u02/transport/cntrl_tspitr_TEST_wkgj.f deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_system_bkblqy42_.dbf deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_undotbs1_bkblqy5h_.dbf deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_sysaux_bkblqy4h_.dbf deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/datafile/o1_mf_temp_bkblv2ot_.tmp deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/onlinelog/o1_mf_1_bkbltlnc_.log deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/onlinelog/o1_mf_2_bkbltp71_.log deletedauxiliary instance file /u02/transport/TSPITR_TEST_WKGJ/onlinelog/o1_mf_3_bkbltt75_.log deleted
5.将步骤4生成的传输表空间的数据文件tspitr01.dbf,test01.dbf和Data Pump导出文件dmpfile拷贝到目标主机上的/u02目录中
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/tspitr01.dbf /u02oracle@192.168.56.2's password:tspitr01.dbf 100% 100MB 7.1MB/s 00:14[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/test01.dbf /u02oracle@192.168.56.2's password:test01.dbf 100% 10MB 10.0MB/s 00:00[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/dmpfile.dmp /u02oracle@192.168.56.2's password:dmpfile.dmp 100% 96KB 96.0KB/s 00:00[oracle@jingyong1 u02]$ ls -lrttotal 112876-rw-r----- 1 oracle oinstall 104865792 Mar 27 17:54 tspitr01.dbf-rw-r----- 1 oracle oinstall 10493952 Mar 27 18:22 test01.dbf-rw-r----- 1 oracle oinstall 98304 Mar 27 18:22 dmpfile.dmp
6.在目录主机上创建相关用户及Data Pump目录并将表空间附加到目标数据库中
SQL> create user tspitr identified by "tspitr";User created.SQL> grant dba,connect,resource to tspitr;Grant succeeded.SQL> create user test identified by "test";User created.SQL> grant dba,connect,resource to test;Grant succeeded.SQL> create directory mytest as '/u02';Directory created.SQL> grant read,write on directory mytest to public;Grant succeeded.
最后就可以执行导入过程了
[oracle@jingyong1 ~]$ export ORACLE_SID=jy[oracle@jingyong1 ~]$ export NLS_DATE_FORMATE='yyyy-mm-dd hh24:mi:ss'[oracle@jingyong1 ~]$ impdp test/test directory=mytest dumpfile=dmpfile.dmp logfile=testtransport.log transport_datafiles= /u02/tspitr01.dbf, /u02/test01.dbfImport: Release 10.2.0.5.0 - Production on Friday, 27 March, 2015 20:28:39Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "TEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "TEST"."SYS_IMPORT_TRANSPORTABLE_01": test/******** directory=mytest dumpfile=dmpfile.dmp logfile=testtransport.log transport_datafiles= /u02/tspitr01.dbf, /u02/test01.dbfProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "TEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 20:28:55进入数据库中检查一下SQL> select count(*) from tspitr.tspitr; COUNT(*)---------- 50641SQL> select count(*) from test.test; COUNT(*)---------- 50680
与2015-03-27 16:48:29之前的状态一致。下面演示使用还原点来执行传输表空间1.确认源数据库与目标数据库所在平台是不是传输表空间所支持的操作系统平台。确认源数据库操作系统平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d 2 where tp.platform_name=d.platform_name ;PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
确认目数据库操作系统平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d 2 where tp.platform_name=d.platform_name ;PLATFORM_NAME ENDIAN_FORMAT----------------------------------------------------------------------------------------------------- --------------Linux IA (32-bit) Little
这里操作系统平台都是Linux
2.确认要被传输的表空间是否是自包含表空间(TSPITR,TEST):
SQL> exec sys.dbms_tts.transport_set_check('TSPITR',true);PL/SQL procedure successfully completed.SQL> exec sys.dbms_tts.transport_set_check('TEST',true);PL/SQL procedure successfully completed.SQL> select * from sys.transport_set_violations;no rows selected
如果没有行选择，表示该表空间只包含表数据，可以传输。
修改表tspitr与test中的记录,在修改之前创建一个还原点，在执行传输表空间时指定这个还原点
SQL> select count(*) from test; COUNT(*)---------- 0SQL> select count(*) from tspitr.tspitr; COUNT(*)---------- 101282SQL> create restore point before_update;Restore point created.SQL> insert into test select * from dba_objects;50683 rows created.SQL> commit;Commit complete.SQL> delete from tspitr.tspitr;101282 rows deleted.SQL> commit;Commit complete.SQL> select count(*) from test; COUNT(*)---------- 50683SQL> select count(*) from tspitr.tspitr; COUNT(*)---------- 0
在创建还原点before_update之后，表tspitr中的记录数为0，表test的记录数为50683,before_update之前，表tspitr中的记录数为101282，表test的记录数为0。
3.对源数据库执行备份，在执行备份时一定要加上include current controlfile否则执行transport tablespace命令时会出现以下错误信息：
Automatic instance removedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of tranport tablespace command at 03/26/2015 20:24:22RMAN-03015: error occurred in stored script Memory ScriptRMAN-06026: some targets not found - aborting restoreRMAN-06024: no backup or copy of the control file found to restore
[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Thu