目的：可传输表空间可以实现迅速将数据迁移/分发的目的，但需要将源库表空间设置为readonly状态，这个条件使该方法无法在生产库联机使用。
不过，可传输表空间支持使用备份生成可传输表空间，从而在不需要修改表空间状态的前提下，实现表空间传递。
该方法使用与表空间时间点恢复（TPITR）类似的方法实现，具体说明可以参考《Database Backup and Recovery Advanced User's Guide》
14 Creating Transportable Tablespace Sets from Backup with RMAN
1，更改为READONLY方式
1.1 源库创建表空间，测试表
SQL> conn / as sysdba
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' size 10m;
Tablespace created.
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 10m;
Tablespace created.
SQL> conn system/oracle1
Connected.
SQL> create table tt_table1 tablespace tbs1 as select * from scott.dept;
Table created.
SQL> create table tt_table2 tablespace tbs2 as select * from scott.emp;
Table created.
SQL> create index pk_tt_table1 on tt_table1(deptno) tablespace tbs1
Index created.
SQL> alter table tt_table1
2 add constraint pk_tt_table1
3 primary key(deptno)
4 using index;
Table altered.
SQL> alter table tt_table2
2 add constraint fk_tt_table2_r_tab1
3 foreign key(deptno) references tt_table1(deptno);
1.2源库检查自包含性
SQL> conn / as sysdba
SQL> begin
2 dbms_tts.transport_set_check('TBS1',true,true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Constraint FK_TT_TABLE2_R_TAB1 between table SYSTEM.TT_TABLE1 in tablespace TBS1
and table SYSTEM.TT_TABLE2 in tablespace TBS2
SQL> begin
2 dbms_tts.transport_set_check('TBS2',true,true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Constraint FK_TT_TABLE2_R_TAB1 between table SYSTEM.TT_TABLE1 in tablespace TBS1
and table SYSTEM.TT_TABLE2 in tablespace TBS2
SQL> begin
2 dbms_tts.transport_set_check('TBS1,TBS2',true,true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
1.3 源库修改表空间为read only
SQL> alter tablespace tbs1 read only;
Tablespace altered.
SQL> alter tablespace tbs2 read only;
Tablespace altered.
1.4源库导出
[oracle@rac1 ~]$ exp \'/ as sysdba\' transport_tablespace=y tablespaces=tbs1,tbs2 file=tt_tbs.dmp
Export: Release 10.2.0.3.0 - Production on Mon Jul 9 19:24:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TBS1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TT_TABLE1
EXP-00091: Exporting questionable statistics.
For tablespace TBS2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TT_TABLE2
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully with warnings.
1.5 复制数据文件到目标库
[oracle@rac1 ~]$ cp /u01/app/oracle/oradata/orcl/tbs1.dbf /u01/app/oracle/oradata/emrep/tbs1.dbf
[oracle@rac1 ~]$ cp /u01/app/oracle/oradata/orcl/tbs2.dbf /u01/app/oracle/oradata/emrep/tbs2.dbf
1.6 源库更改表空间状态为read write
conn / as sysdba
SQL> alter tablespace tbs1 read write;
Tablespace altered.
SQL> alter tablespace tbs2 read write;
Tablespace altered.
1.7 目标库导入
[oracle@rac1 ~]$ export ORACLE_SID=emrep
[oracle@rac1 ~]$ imp \'/ as sysdba\' transport_tablespace=y datafiles='/u01/app/oracle/oradata/emrep/tbs1.dbf','/u01/app/oracle/oradata/emrep/tbs2.dbf' tablespaces=tbs1,tbs2 file=tt_tbs.dmp
Import: Release 10.2.0.3.0 - Production on Mon Jul 9 19:33:33 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. . importing table "TT_TABLE1"
. . importing table "TT_TABLE2"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
1.8 目标库验证
[oracle@rac1 ~]$ sqlplus system/oracle1
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 9 19:34:01 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> desc tt_table1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select * from tt_table1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select constraint_name,constraint_type from user_constraints where constraint_name='FK_TT_TABLE2_R_TAB1'
CONSTRAINT_NAME C
------------------------------ -
FK_TT_TABLE2_R_TAB1 R
2，使用备份生成可传输表空间
2.1 源库备份
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jul 9 19:38:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1314428069)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/orcl/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/orcl/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default
RMAN> backup database plus archivelog;
2.2 源库更新（表空间处于read write状态）
SQL> update system.tt_table1 set loc='xxx';
4 rows updated.
SQL> commit;
Commit complete.
SQL> update system.tt_table1 set dname='xxx';
4 rows updated.
2.3 源库准备目录，用于放置生成的数据文件
[oracle@rac1 oracle]$ pwd
/u01/app/oracle
[oracle@rac1 oracle]$ ls -lrt
drwxr-xr-x 2 oracle oinstall 4096 Jul 9 19:47 ttbs
drwxr-xr-x 2 oracle oinstall 4096 Jul 9 19:49 ttbs_auxdest
2.4 使用备份生成传输表空间
[oracle@rac1 ~]$ rman target /
RMAN> transport tablespace tbs1,tbs2
2> tablespace destination '/u01/app/oracle/ttbs'
3> auxiliary destination '/u01/app/oracle/ttbs_auxdest'
4> ;
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time
List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
Creating automatic instance, with SID='vodh'
initialization parameters used for automatic instance:
db_name=ORCL
compatible=10.2.0.3.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ORCL_vodh
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/ttbs_auxdest
control_files=/u01/app/oracle/ttbs_auxdest/cntrl_tspitr_ORCL_vodh.f
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 1260888 bytes
Variable Size 146801320 bytes
Database Buffers 50331648 bytes
Redo Buffers 7127040 bytes
Automatic instance created
contents of Memory Script.:
{
# set the until clause
set until scn 767171;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 09-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl/c-1314428069-20120709-05
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/orcl/c-1314428069-20120709-05 tag=TAG20120709T192642
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/ttbs_auxdest/cntrl_tspitr_ORCL_vodh.f
Finished restore at 09-JUL-12
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script.:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until scn 767171;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination filename for restore
set newname for clone datafile 3 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 7 to
"/u01/app/oracle/ttbs/tbs1.dbf";
# set a destination filename for restore
set newname for datafile 8 to
"/u01/app/oracle/ttbs/tbs2.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 3, 7, 8;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 3 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 7 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 8 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TBS1", "TBS2", "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 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
renamed temporary file 1 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 09-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00003 to /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_sysaux_%u_.dbf
restoring datafile 00007 to /u01/app/oracle/ttbs/tbs1.dbf
restoring datafile 00008 to /u01/app/oracle/ttbs/tbs2.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl/1tnfm9s3_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/orcl/1tnfm9s3_1_1 tag=TAG20120709T193947
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:11
Finished restore at 09-JUL-12
datafile 1 switched to datafile copy
input datafile copy recid=21 stamp=788212459 filename=/u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_system_7zokh9hc_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=22 stamp=788212459 filename=/u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_undotbs1_7zokh9m3_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=23 stamp=788212459 filename=/u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_sysaux_7zokh9jh_.dbf
datafile 7 switched to datafile copy
input datafile copy recid=24 stamp=788212459 filename=/u01/app/oracle/ttbs/tbs1.dbf
datafile 8 switched to datafile copy
input datafile copy recid=25 stamp=788212459 filename=/u01/app/oracle/ttbs/tbs2.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 7 online
sql statement: alter database datafile 8 online
Starting recover at 09-JUL-12
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 30 is already on disk as file /u01/app/oracle/archive/orcl/1_30_787328709.dbf
archive log thread 1 sequence 31 is already on disk as file /u01/app/oracle/archive/orcl/1_31_787328709.dbf
archive log filename=/u01/app/oracle/archive/orcl/1_30_787328709.dbf thread=1 sequence=30
archive log filename=/u01/app/oracle/archive/orcl/1_31_787328709.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-JUL-12
database opened
contents of Memory Script.:
{
#mark read only the tablespace that will be exported
sql clone "alter tablespace TBS1 read only";
#mark read only the tablespace that will be exported
sql clone "alter tablespace TBS2 read only";
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/u01/app/oracle/ttbs''";
# export the tablespaces in the recovery set
host 'expdp userid=\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oraclevodh\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=vodh^'\)\)\(CONNECT_DATA=\(SID=vodh\)\)\) as sysdba\" transport_tablespaces=
TBS1,
TBS2 dumpfile=
dmpfile.dmp directory=
STREAMS_DIROBJ_DPDIR logfile=
explog.log';
}
executing Memory Script
sql statement: alter tablespace TBS1 read only
sql statement: alter tablespace TBS2 read only
sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/u01/app/oracle/ttbs''
Export: Release 10.2.0.3.0 - Production on Monday, 09 July, 2012 19:54:56
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": userid="/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle)(ARGV0=oraclevodh)(ARGS=\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\))(ENVS=ORACLE_SID=vodh))(CONNECT_DATA=(SID=vodh))) AS SYSDBA" transport_tablespaces= TBS1, TBS2 dumpfile=dmpfile.dmp directory=STREAMS_DIROBJ_DPDIR logfile=explog.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/ttbs/dmpfile.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 19:55:25
host command complete
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script. for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/u01/app/oracle/ttbs/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/u01/app/oracle/ttbs';
/* 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 := 'tbs1.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
tbs_files( 2).file_name := 'tbs2.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 objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u01/app/oracle/ttbs_auxdest/cntrl_tspitr_ORCL_vodh.f deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_system_7zokh9hc_.dbf deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_undotbs1_7zokh9m3_.dbf deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_sysaux_7zokh9jh_.dbf deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/datafile/o1_mf_temp_7zokn7pq_.tmp deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog/o1_mf_1_7zokmjx5_.log deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog/o1_mf_2_7zokmr11_.log deleted
auxiliary instance file /u01/app/oracle/ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog/o1_mf_3_7zokmzcm_.log deleted
2.5 验证生成的文件
[oracle@rac1 oracle]$ ps -ef|grep pmon
oracle 5539 1 0 18:44 ? 00:00:00 ora_pmon_orcl
oracle 8179 1 0 19:29 ? 00:00:00 ora_pmon_emrep
oracle 12635 1 0 19:51 ? 00:00:00 ora_pmon_vodh
oracle 12686 5959 1 19:52 pts/2 00:00:00 grep pmon
--在rman生成文件过程中可以看到辅助实例的pmon进程：ora_pmon_vodh
[oracle@rac1 oracle]$ ls -lrt ttbs
total 20644
-rw-r----- 1 oracle oinstall 10493952 Jul 9 19:54 tbs2.dbf
-rw-r----- 1 oracle oinstall 10493952 Jul 9 19:54 tbs1.dbf
-rw-r--r-- 1 oracle oinstall 1426 Jul 9 19:55 explog.log
-rw-r----- 1 oracle oinstall 106496 Jul 9 19:55 dmpfile.dmp
-rw-r--r-- 1 oracle oinstall 2191 Jul 9 19:55 impscrpt.sql
[oracle@rac1 oracle]$ ls -lrt ttbs_auxdest
total 7008
drwxr-x--- 4 oracle oinstall 4096 Jul 9 19:52 TSPITR_ORCL_VODH
-rw-r----- 1 oracle oinstall 7159808 Jul 9 19:55 cntrl_tspitr_ORCL_vodh.f
[oracle@rac1 oracle]$ ls -lrt ./ttbs_auxdest/TSPITR_ORCL_VODH
total 8
drwxr-x--- 2 oracle oinstall 4096 Jul 9 19:55 onlinelog
drwxr-x--- 2 oracle oinstall 4096 Jul 9 19:55 datafile
[oracle@rac1 oracle]$ ls -lrt ./ttbs_auxdest/TSPITR_ORCL_VODH/datafile
total 0
[oracle@rac1 oracle]$ ls -lrt ./ttbs_auxdest/TSPITR_ORCL_VODH/onlinelog
total 0
[oracle@rac1 oracle]$ cd ttbs
[oracle@rac1 ttbs]$ more *.sql
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'dmpfile.dmp' transport_datafil
es= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script. for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/u01/app/oracle/ttbs/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/u01/app/oracle/ttbs';
/* 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 := 'tbs1.dbf';
tbs_files( 1).directory_object := 'STREAMS$DIROBJ$1';
tbs_files( 2).file_name := 'tbs2.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 objects
DROP DIRECTORY STREAMS$DIROBJ$1;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
2.6 目标库导入
SQL> conn / as sysdba
SQL> create directory ttbsdir as '/u01/app/oracle/ttbs';
Directory created.
[oracle@rac1 ~]$ impdp \'/ as sysdba\' directory=ttbsdir dumpfile='dmpfile.dmp' transport_datafiles= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
Import: Release 10.2.0.3.0 - Production on Monday, 09 July, 2012 20:19:18
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": '/******** AS SYSDBA' directory=ttbsdir dumpfile=dmpfile.dmp transport_datafiles= /u01/app/oracle/ttbs/tbs1.dbf, /u01/app/oracle/ttbs/tbs2.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 20:19:23
2.7目标库验证导入的数据
SQL> select * from system.tt_table1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-735038/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-735038/
474
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
