Transport_Tablespace-RMAN

將 192.168.3.199 資料庫下， chenjc 使用者下的 t1 表，匯入到 192.168.3.198 資料庫下， chenjc 使用者下；

一 檢視作業系統版本，資料庫版本

192.168.3.199

[oracle@ogg1 ~]$ cat /etc/issue

Oracle Linux Server release 6.3

SQL> select * from v$version where rownum<=2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

192.168.3.198

[oracle@ogg2 orcl]$ cat /etc/issue

Oracle Linux Server release 6.3

SQL> select * from v$version where rownum<=2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

二 建立測試表空間，測試使用者，測試表

192.168.3.199

SQL> create tablespace chenjc datafile '/u01/app/oracle/oradata/orcl/chenjc01.dbf' size 30m autoextend on;

Tablespace created.

SQL> create user chenjc identified by chenjc default tablespace chenjc;

User created.

SQL> grant connect,resource,dba to chenjc;

Grant succeeded.

SQL> conn chenjc/chenjc

Connected.

SQL> create table t1 as select level id,sysdate as t_date from dual connect by level<=100000;

Table created.

三 檢查準備遷移的表空間是否自包含

SQL> conn /as sysdba

Connected.

SQL> execute dbms_tts.transport_set_check(ts_list=>'CHENJC',incl_constraints=>TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

/* 無返回記錄，說明符合傳輸表空間條件*/

四 建立備份檔案

RMAN> backup database plus archivelog delete input;

五 透過 RMAN 工具匯出所要傳輸表空間的原資料以及指令碼

RMAN> transport tablespace chenjc tablespace destination '/home/oracle' auxiliary destination '/home/oracle';

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='bEyE'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=bEyE_tspitr_ORCL

compatible=11.2.0.3.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/home/oracle

log_archive_dest_1='location=/home/oracle'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2227744 bytes

Variable Size 100663776 bytes

Database Buffers 184549376 bytes

Redo Buffers 4837376 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:

{

# set requested point in time

set until scn 327263;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 03-AUG-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_ncnnf_TAG20150803T131502_bvxy776x_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_ncnnf_TAG20150803T131502_bvxy776x_.bkp tag=TAG20150803T131502

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/home/oracle/ORCL/controlfile/o1_mf_bvxzhyb7_.ctl

Finished restore at 03-AUG-15

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until scn 327263;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 6 to

"/home/oracle/chenjc01.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2, 6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 03-AUG-15

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/chenjc01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_nnndf_TAG20150803T131502_bvxy6ptl_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_nnndf_TAG20150803T131502_bvxy6ptl_.bkp tag=TAG20150803T131502

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 03-AUG-15

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=886772243 file name=/home/oracle/ORCL/datafile/o1_mf_system_bvxzj4hj_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=886772243 file name=/home/oracle/ORCL/datafile/o1_mf_undotbs1_bvxzj4hn_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=886772243 file name=/home/oracle/ORCL/datafile/o1_mf_sysaux_bvxzj4j3_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=9 STAMP=886772243 file name=/home/oracle/chenjc01.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 327263;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 6 online";

# recover and open resetlogs

recover clone database tablespace "CHENJC", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 6 online

Starting recover at 03-AUG-15

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=71

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_annnn_TAG20150803T131520_bvxy78t9_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_08_03/o1_mf_annnn_TAG20150803T131520_bvxy78t9_.bkp tag=TAG20150803T131520

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/home/oracle/1_71_885838407.dbf thread=1 sequence=71

channel clone_default: deleting archived log(s)

archived log file name=/home/oracle/1_71_885838407.dbf RECID=8 STAMP=886772244

media recovery complete, elapsed time: 00:00:00

Finished recover at 03-AUG-15

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace CHENJC read only';

# create directory for datapump export

sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''

/home/oracle''";

}

executing Memory Script

sql statement: alter tablespace CHENJC read only

sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/home/oracle''

Performing export of metadata...

EXPDP> Starting "SYS"."TSPITR_EXP_bEyE":

EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

EXPDP> Master table "SYS"."TSPITR_EXP_bEyE" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_bEyE is:

EXPDP> /home/oracle/dmpfile.dmp

EXPDP> ******************************************************************************

EXPDP> Datafiles required for transportable tablespace CHENJC:

EXPDP> /home/oracle/chenjc01.dbf

EXPDP> Job "SYS"."TSPITR_EXP_bEyE" successfully completed at 13:38:11

Export completed

/*

The following command may be used to import the tablespaces.

Substitute values for and .

impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /home/oracle/chenjc01.dbf

*/

--------------------------------------------------------------

-- Start of sample PL/SQL script for importing the tablespaces

--------------------------------------------------------------

-- creating directory objects

CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/home/oracle/';

CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/home/oracle';

/* 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 := 'chenjc01.dbf';

tbs_files( 1).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

database closed

database dismounted

Oracle instance shut down

Automatic instance removed

auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_temp_bvxzjrfs_.tmp deleted

auxiliary instance file /home/oracle/ORCL/onlinelog/o1_mf_3_bvxzjq44_.log deleted

auxiliary instance file /home/oracle/ORCL/onlinelog/o1_mf_2_bvxzjpoq_.log deleted

auxiliary instance file /home/oracle/ORCL/onlinelog/o1_mf_1_bvxzjp71_.log deleted

auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_sysaux_bvxzj4j3_.dbf deleted

auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_undotbs1_bvxzj4hn_.dbf deleted

auxiliary instance file /home/oracle/ORCL/datafile/o1_mf_system_bvxzj4hj_.dbf deleted

auxiliary instance file /home/oracle/ORCL/controlfile/o1_mf_bvxzhyb7_.ctl deleted

[oracle@ogg1 ~]$ ll -rth

......

drwxr-x--- 5 oracle oinstall 4.0K Aug 3 13:37 ORCL

-rw-r----- 1 oracle oinstall 31M Aug 3 13:37 chenjc01.dbf

-rw-r----- 1 oracle oinstall 88K Aug 3 13:38 dmpfile.dmp

-rw-r--r-- 1 oracle oinstall 2.0K Aug 3 13:38 impscrpt.sql

/*

模擬平臺轉換（同一平臺傳輸不需要這步）

SQL> col platform_name for a35

SQL> select * from v$transportable_platform order by platform_id;

RMAN>convert tablespace "TESTSPACE" to platform 'Microsoft Windows IA (32-bit)' format 'd:\TESTSPACE01.DBF' --這個是轉換的目標地址

*/

六 將資料庫檔案和匯出的表空間原檔案複製到 192.168.3.198 伺服器

[oracle@ogg1 ~]$ scp chenjc01.dbf dmpfile.dmp impscrpt.sql 192.168.3.198:/home/oracle/

七 目標資料庫建立使用者，指定表空間 ( 目標資料庫不能有和將要傳輸表空間同名的表空間 )

192.168.3.198

SQL> drop tablespace chenjc including contents and datafiles;

Tablespace dropped.

SQL> drop user chenjc cascade;

User dropped.

SQL> create user chenjc identified by chenjc default tablespace users;

User created.

SQL> grant connect,resource,dba to chenjc;

Grant succeeded.

八 透過 @impscrpt.sql 指令碼匯入表空間

SQL> @impscrpt.sql

Directory created.

PL/SQL procedure successfully completed.

Directory dropped.

SQL> select name from v$dbfile;

NAME

--------------------------------------------------------------------------------

。。。。。。

/home/oracle/chenjc01.dbf

6 rows selected.

九 修改使用者預設表空間

SQL> alter user chenjc default tablespace chenjc;

User altered.

十檢視

SQL> conn chenjc/chenjc

SQL> select id,to_char(t_date,'yyyy-mm-dd hh24:mi:ss') from t1 where rownum<=3;

ID TO_CHAR(T_DATE,'YYY

---------- -------------------

1 2015-08-03 09:27:01

2 2015-08-03 09:27:01

3 2015-08-03 09:27:01

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 傳輸表空間-Rman

Oracle 傳輸表空間-Rman

相關文章