readwrite狀態下實現可傳輸表空間
目的：可傳輸表空間可以實現迅速將資料遷移/分發的目的，但需要將源庫表空間設定為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=\") 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="))) 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
impdp
*/
--------------------------------------------------------------
-- 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
impdp
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用RMAN實現可傳輸的表空間
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（下）Oracle
- 用可傳輸表空間實現Oracle跨平臺移植Oracle
- 基於可傳輸表空間的表空間遷移
- Oracle可傳輸表空間測試Oracle
- 5.7 mysql的可傳輸表空間MySql
- oracle可傳輸表空間TTS小結OracleTTS
- 跨平臺表空間傳輸的實現
- 使用Oracle可傳輸表空間的特性複製資料(7)實戰RMAN備份傳輸表空間Oracle
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（上）Oracle
- 聊聊Oracle可傳輸表空間（Transportable Tablespace）（中）Oracle
- MySQL 傳輸表空間MySql
- Oracle 表空間傳輸Oracle
- oracle表空間傳輸Oracle
- Oracle傳輸表空間Oracle
- MySQL表空間傳輸MySql
- 【實驗】利用可傳輸表空間技術實現資料的高效遷移
- 關於oracle可傳輸表空間的總結Oracle
- Oracle 10g的可傳輸表空間操作Oracle 10g
- 實戰RMAN備份傳輸表空間
- mysql之 表空間傳輸MySql
- 傳輸表空間操作-OracleOracle
- Oracle傳輸表空間（TTS）OracleTTS
- Oracle 傳輸表空間-RmanOracle
- 總結-表空間傳輸
- 跨平臺表空間遷移(傳輸表空間)
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移
- Oracle傳輸表空間學習Oracle
- Oracle 傳輸表空間-EXPDP/IMPDPOracle
- Oracle 傳輸表空間-EXP/IMPOracle
- 傳輸表空間自包含理解
- Oracle表空間傳輸詳解Oracle
- RMAN跨平臺可傳輸表空間和資料庫資料庫
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移[轉]
- 使用Oracle可傳輸表空間的特性複製資料(3)跨平臺的傳輸實踐Oracle
- oracle 傳輸表空間一例Oracle
- 表空間傳輸讀書筆記筆記
- 利用可傳輸表空間跨平臺遷移 -RMAN CONVERT