ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用传输表空间跨平台迁移数据

用传输表空间跨平台迁移数据

原创 Linux操作系统 作者:space6212 时间:2019-04-21 16:06:05 0 删除 编辑

最近做了一次跨平台的数据迁移,迁移要求是把部分用户的数据迁移到另一个数据库中。
源数据库环境是Solaris 10(x86_64) + ORACLE 10203 RAC + ASM
目标数据库环境是Linux AS 4(x86) + ORACLE 10203 + 文件系统

我在这次迁移用的是传输表空间技术。


1、检查需要传输的用户对应的表空间

SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='NDMAIN';

TABLESPACE_NAME
------------------------------
NDMAIN

SQL> SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='ZHEJIANG';

TABLESPACE_NAME
------------------------------
ZHEJIANG

2、查询是否满足传输表空间的条件
1)查询是否支持平台转换
--查询源库的平台信息
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (64-bit) Big

--查询目标库的平台信息
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux IA (32-bit) Little

--从v$transportable_platform视图查询以上两个平台的备份集是否可以转换
SQL> select * from v$transportable_platform where PLATFORM_NAME in ('Solaris[tm] OE (64-bit)','Linux IA (32-bit)');

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
2 Solaris[tm] OE (64-bit) Big
10 Linux IA (32-bit) Little
有两个记录,表示这两种平台下的备份集可以互相转换称对应的平台备份集。

2)查看表空间集是否是子包含
如果某个表空间集引用了其他表空间的对象(如外键引用的表在其他表空间、表空间包含物化视图等),则这个表空间不是自包含的;否则就是自包含。
只有自包含的表空间集才可以用表空间传输技术。

oracle提供dbms_tts.transport_set_check过程来帮助我们判断某个表空间集是否是自包含的:
SQL> execute dbms_tts.transport_set_check('NDMAIN,ZHEJIANG',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

如果查询TRANSPORT_SET_VIOLATIONS有记录返回,则表示表空间集不是自包含的,无法对这些表空间使用表空间传输技术;反之,则表示表空间集是自包含的,可以对这些表空间使用表空间传输技术。

注意:
1)使用dbms_tts.transport_set_check时,要把需要表空间传输的表空间列表都写上,用都好分割,不要每一个表空间执行一次dbms_tts.transport_set_check,这样可能会引起误解:
例如:
A表在表空间T1上,
B表在表空间T2上,并且B表上有一个外键引用A表。
如果要迁移A、B两个表空间,如果分别执行:
execute dbms_tts.transport_set_check('T1',TRUE);
execute dbms_tts.transport_set_check('T2',TRUE);
则在检查T2时TRANSPORT_SET_VIOLATIONS肯定会有记录,因为B表引用了A表,两个表不在一个表空间上。
但实际上,A、B两个表的表空间都在我们要迁移的范围内,所以迁移后数据的完整性也是有保证的,这个问题就在于我们把T1、T2分别检验造成了误解。
正确的方法应该是:
execute dbms_tts.transport_set_check('T1,T2',TRUE);
2)TRANSPORT_SET_VIOLATIONS是一个临时表,必须在执行dbms_tts.transport_set_check同一个会话中查询这个表。


3、在源库导出数据
1)创建directory
SQL> create directory tran as '/backup/dmp';

Directory created.

2)把对应表空间置于只读模式
SQL> alter tablespace ndmain read only;

Tablespace altered.

SQL> alter tablespace zhejiang read only;

Tablespace altered.

3)用数据泵导出
指定了transport_tablespaces后,数据泵导出的只是一些结构方面的信息,所以导出文件会比较小。
bash-3.00$ expdp system/test directory=tran transport_tablespaces=ndmain,zhejiang

Export: Release 10.2.0.3.0 - 64bit Production on 星期二, 23 10月, 2007 1:21:36

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=tran transport_tablespaces=ndmain,zhejiang
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
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/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/backup/dmp/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:24:55


4、转换数据文件格式
由于源数据库和目标数据库所在平台不同,所以要把要传输的表空间对应的数据文件转换为目标平台的格式。
10g下rman提供这一功能,你可以选择在源数据库或者目标数据库进行转换:
1)在源数据库转换:conver tablespace ... to platform ....
2)在目标数据库转换:conver datafile ... from fplatform ....

在这里选择第一种方式:
bash-3.00$ export ORACLE_SID=prerac1
bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on 星期二 10月 23 01:26:01 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PRETRADE (DBID=3201410814)

RMAN> run{
2> allocate channel c1 device type disk connect
'sys/testrac@pre1';
3> convert tablespace ndmain,zhejiang to platform 'Linux IA (32-bit)' Format '/backup/dmp/%U';
4> release channel c1;
5> }

--这段脚本的意思是把ndmain、zhejiang这两个表空间对应的数据文件转换成Linux IA (32-bit),并把转换后的数据文件放在/backup/dmp下

allocated channel: c1
channel c1: sid=316 instance=prerac1 devtype=DISK

Starting backup at 23-10月-07
channel c1: starting datafile conversion
input datafile fno=00014 name=+DATA/datafile/ndmain1.ora
converted datafile=/backup/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-14_64iv64lb
channel c1: datafile conversion complete, elapsed time: 00:05:06
......
channel c1: starting datafile conversion
input datafile fno=00038 name=+DATA/datafile/zhejiang18.ora
converted datafile=/backup/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-38_6oiv6bac
channel c1: datafile conversion complete, elapsed time: 00:05:45
Finished backup at 23-10月-07

released channel: c1

5、传送文件到目标数据库
[oracle@tdata dmp]$ sftp 172.0.2.1
Password:
sftp> cd /backup
sftp> ls
dmp lost+found soft
sftp> cd dmp
sftp> mget *

6、在目标数据库创建对应的用户及授权

在导入数据时,对应的用户必须已经存在,并有合适的权限,否则会报错。
[oracle@tdata dmp]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 10月 23 09:32:00 2007

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 and Data Mining options

SQL> create user ndmain identified by testndmain202;

User created.

SQL> create user zhejiang identified by testzj202;

User created.

SQL> create user ZHEJIANG_OPERATOR identified by testzjop202;

User created.

SQL> create user ZHEJIANG_KHD identified by testzjkhd202;

User created.

SQL> grant connect,resource to ndmain;

Grant succeeded.

SQL> grant connect,resource to zhejiang;

Grant succeeded.

SQL> grant connect,resource to ZHEJIANG_OPERATOR;

Grant succeeded.

SQL> grant connect,resource to ZHEJIANG_KHD;

Grant succeeded.

SQL> grant create materialized view to zhejiang;

Grant succeeded.

SQL> grant create materialized view to ndmain;

Grant succeeded.

SQL> grant create synonym to zhejiang;

Grant succeeded.

SQL> grant create synonym to ndmain;

Grant succeeded.

SQL> grant create materialized view to ZHEJIANG_KHD;

Grant succeeded.

SQL> grant create materialized view to ZHEJIANG_OPERATOR;

Grant succeeded.

SQL> grant create synonym to ZHEJIANG_KHD;

Grant succeeded.

SQL> grant create synonym to ZHEJIANG_OPERATOR;

Grant succeeded.

--创建directory
SQL> create directory tran as '/oradata/dmp';

Directory created.


7、在目标库转换文件路径和名称
在目标数据库以rman执行:
run{
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-14_64iv64lb' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-14_64iv64lb' , '/oradata/oradata/testzj/ndmain1.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-15_65iv64ut' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-15_65iv64ut' , '/oradata/oradata/testzj/ndmain2.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-16_66iv658p' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-NDMAIN_FNO-16_66iv658p' , '/oradata/oradata/testzj/ndmain3.dbf';
......
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-36_6miv6ald' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-36_6miv6ald' , '/oradata/oradata/testzj/zhejiang16.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-37_6niv6b06' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-37_6niv6b06' , '/oradata/oradata/testzj/zhejiang17.dbf';
convert datafile '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-38_6oiv6bac' db_file_name_convert '/oradata/dmp/data_D-PRETRADE_I-3201410814_TS-ZHEJIANG_FNO-38_6oiv6bac' , '/oradata/oradata/testzj/zhejiang18.dbf';
}
--这段话的意思是把源数据库拷贝过来的文件复制并重命名。

8、把数据导入到目标库中

[oracle@tdata dmp]$ impdp system/test dumpfile=expdat.dmp directory=tran exclude=transportable_export/materialized_view,transportable_export/table_statistics,transportable_export/grant/owner_grant/object_grant transport_datafiles='/oradata/oradata/testzj/ndmain1.dbf','/oradata/oradata/testzj/ndmain2.dbf','/oradata/oradata/testzj/ndmain3.dbf','/oradata/oradata/testzj/zhejiang1.dbf','/oradata/oradata/testzj/zhejiang2.dbf','/oradata/oradata/testzj/zhejiang3.dbf','/oradata/oradata/testzj/zhejiang4.dbf','/oradata/oradata/testzj/zhejiang5.dbf','/oradata/oradata/testzj/zhejiang6.dbf','/oradata/oradata/testzj/zhejiang7.dbf','/oradata/oradata/testzj/zhejiang8.dbf','/oradata/oradata/testzj/zhejiang9.dbf','/oradata/oradata/testzj/zhejiang10.dbf','/oradata/oradata/testzj/zhejiang11.dbf','/oradata/oradata/testzj/zhejiang12.dbf','/oradata/oradata/testzj/zhejiang13.dbf','/oradata/oradata/testzj/zhejiang14.dbf','/oradata/oradata/testzj/zhejiang15.dbf','/oradata/oradata/testzj/zhejiang16.dbf','/oradata/oradata/testzj/zhejiang17.dbf','/oradata/oradata/testzj/zhejiang18.dbf'

Import: Release 10.2.0.3.0 - Production on 星期二, 23 10月, 2007 17:08:14

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 and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=tran exclude=transportable_export/materialized_view,transportable_export/table_statistics,transportable_export/grant/owner_grant/object_grant transport_datafiles=/oradata/oradata/testzj/ndmain1.dbf,/oradata/oradata/testzj/ndmain2.dbf,/oradata/oradata/testzj/ndmain3.dbf,/oradata/oradata/testzj/zhejiang1.dbf,/oradata/oradata/testzj/zhejiang2.dbf,/oradata/oradata/testzj/zhejiang3.dbf,/oradata/oradata/testzj/zhejiang4.dbf,/oradata/oradata/testzj/zhejiang5.dbf,/oradata/oradata/testzj/zhejiang6.dbf,/oradata/oradata/testzj/zhejiang7.dbf,/oradata/oradata/testzj/zhejiang8.dbf,/oradata/oradata/testzj/zhejiang9.dbf,/oradata/oradata/testzj/zhejiang10.dbf,/oradata/oradata/testzj/zhejiang11.dbf,/oradata/oradata/testzj/zhejiang12.dbf,/oradata/oradata/testzj/zhejiang13.dbf,/oradata/oradata/testzj/zhejiang14.dbf,/oradata/oradata/testzj/zhejiang15.dbf,/oradata/oradata/testzj/zhejiang16.dbf,/oradata/oradata/testzj/zhejiang17.dbf,/oradata/oradata/testzj/zhejiang18.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/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:10:00


在导入时有三个部分对象排除出去:
transportable_export/materialized_view:因为是测试环境,不需要同步数据,所以没有必要创建物化视图
transportable_export/grant/owner_grant/object_grant:因为新环境有很多用户不存在,保留授权会导致一大堆的报错信息,故排除
transportable_export/table_statistics:不导入表统计信息是为了避免bug:
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 12, column 17:
PL/SQL: ORA-00917: missing comma
ORA-06550: line 4, column 127:
PL/SQL: SQL Statement ignored


注意:用传输表空间的方法,只是把传输过来的表空间的数据都导过来,用户的很多对象如序列、视图等是不会导过来的,你需要手工补上这些对象。

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

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    118
  • 访问量
    86822