Oracle 传输表空间-Rman

最新推荐文章于 2024-08-19 13:32:03 发布

ckth47350

最新推荐文章于 2024-08-19 13:32:03 发布

阅读量121

点赞数

文章标签：数据库操作系统

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1760405/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1760405/

ckth47350

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 传输表空间-Rman

Transport_Tablespace-RMAN 将192.168.3.199数据库下，chenjc用户下的t1表，导入到192.168.3.198数据库下，chenjc用户下； ...
复制链接

扫一扫