欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

使用RMAN简单迁移表空间

程序员文章站 2022-05-16 23:24:34
...

确认环境: 源库: 系统:RedHat企业版5.4 数据库版本:Oracle 10.2.0.1.0 IP:10.37.100.100 目标库: 系统:RedHat企业版5.4

确认环境:

源库:

系统:RedHat企业版5.4

数据库版本:Oracle 10.2.0.1.0

IP:10.37.100.100

目标库:

系统:RedHat企业版5.4

数据库版本:ORACLE 10.2.0.3.0

IP:10.37.100.101

需要传输的表空间:TTS_TEST

检查一下环境:

源库上查看版本支持情况:

SQL> select db.name,db.platform_name,tp.endian_format

2 from v$transportable_platform tp,v$database db

3 where tp.platform_name=db.platform_name

4 /

NAME PLATFORM_NAME ENDIAN_FORMAT

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

ORCL Linux IA (32-bit) Little

查看数据库版本:

SQL> select version from v$instance;

VERSION

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

10.2.0.1.0

备库上查看版本情况:

SQL> select db.name,db.platform_name,tp.endian_format

2 from v$transportable_platform tp,v$database db

3 where tp.platform_name=db.platform_name

4 /

NAME PLATFORM_NAME ENDIAN_FORMAT

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

ORCL Linux IA (32-bit) Little

源库上需要有整库和所有归档日志的备份

RMAN> backup database plus archivelog format '/u01/rman_backup/all_database_bak_%T_%t.bak';

RMAN> transport tablespace tts_test

2> tablespace destination '/u01/tts_dir/td'

3> auxiliary destination '/u01/tts_dir/ad';

此时传输表空间所需的数据文件,日志文件,,传输集及导入脚本均已生成在tablepsace desnation目录下,而auxilibary desnation路径下的临时文件已经被删除

[oracle@localhost td]$ cd /u01/tts_dir/td/

[oracle@localhost td]$ ls

dmpfile.dmp explog.log impscrpt.sql tts01.dbf tts02.dbf

[oracle@localhost td]$ cd /u01/tts_dir/ad/

[oracle@localhost ad]$ ls

TSPITR_ORCL_MWWE

[oracle@localhost ad]$ cd TSPITR_ORCL_MWWE/

[oracle@localhost TSPITR_ORCL_MWWE]$ ls

datafile onlinelog

[oracle@localhost TSPITR_ORCL_MWWE]$ cd datafile/

[oracle@localhost datafile]$ ls

[oracle@localhost datafile]$ cd ../onlinelog/

[oracle@localhost onlinelog]$ ls

将tablepsace desnation路径下生成的所有文件copy到目标库:

[oracle@localhost ~]$ scp 10.37.100.100:/u01/tts_dir/td/* /u01/tts_dir/

oracle@10.37.100.100's password:

dmpfile.dmp 100% 76KB 76.0KB/s 00:00

explog.log 100% 1226 1.2KB/s 00:00

impscrpt.sql 100% 2175 2.1KB/s 00:00

tts01.dbf 100% 20MB 20.0MB/s 00:01

tts02.dbf 100% 20MB 20.0MB/s 00:01

SQL> @/u01/tts_dir/impscrpt.sql

Directory created.

Directory created.

DECLARE

*

ERROR at line 1:

ORA-39002: invalid operation

ORA-06512: at "SYS.DBMS_STREAMS_TABLESPACE_ADM", line 2006

ORA-06512: at line 20

Directory dropped.

Directory dropped.

报错的原因是目标库的传输集路径跟源库的不一样,可以将传输集复制到相同的路径下,或者去修改一下impscrpt.sql中的路径:

[oracle@localhost tts_dir]$ vi impscrpt.sql

将一下两个路径修改成目标库下的传输集路径即可:

CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/u01/tts_dir/';

CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/u01/tts_dir/';

再次执行脚本导入:

SQL> @/u01/tts_dir/impscrpt.sql

Directory created.

Directory created.

PL/SQL procedure successfully completed.

Directory dropped.

Directory dropped.

也可以直接使用inpdp命令导入(这种导入可以使用remap_schma参数来修改表空间的所属主)

[oracle@localhost tts_dir]$ impdp system/oracle dumpfile=dmpfile.dmp directory=tts_dir nologf

ile=y transport_datafiles=/u01/tts_dir/tts01.dbf,/u01/tts_dir/tts02.dbf remap_schema=tts:xtt;

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=dmpfile.dmp directory=tts_dir nologfile=y transport_datafiles=/u01/tts_dir/tts01.dbf,/u01/tts_dir/tts02.dbf remap_schema=tts:xtt

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:24:37

查看一下结果:

SQL> conn xtt/oracle

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TEST TABLE

DG_TEST TABLE

STD_TABLE_1 TABLE

STD_TABLE TABLE