ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle 传输表空间-Rman

Oracle 传输表空间-Rman

原创 Oracle 作者：chenoracle 时间：2015-08-03 14:42:42 0 删除 编辑

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

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

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.

 

 

Directory created.

 

 

PL/SQL procedure successfully completed.

 

 

Directory dropped.

 

 

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"，共同学习，共同成长！！！

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

0
0
上一篇： Oracle 传输表空间-EXPDP/IMPDP
下一篇： Oracle 数据库升级
请登录后发表评论 登录
全部评论
chenoracle
专家姓名：陈举超
Oracle ACE、OCMU 用户组成员、Oracle 11g OCM、微信公众号"IT小Chen"

注册时间：2014-08-05

  • 博文量
    853
  • 访问量
    2727927

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593