使用XTTS迁移数据库
源库：11.2.0.4 + Windows目标库：12.2.0.1 + Redhat 7.6-- 创建测试用的表空间create tablespace tbs_data datafile 'D:\ORADATA\TESTOGG\TBS_DATA01.DBF' size 100M ;create tablespace idx_data datafile 'D:\ORADATA\TES...
·
源库：11.2.0.4 + Windows
目标库：12.2.0.1 + Redhat 7.6
-- 创建测试用的表空间
create tablespace tbs_data datafile 'D:\ORADATA\TESTOGG\TBS_DATA01.DBF' size 100M ;
create tablespace idx_data datafile 'D:\ORADATA\TESTOGG\IDX_DATA01.DBF' size 100M ;
create table t1_tbs (id number,name varchar2(6)) tablespace tbs_data;
insert into t1_tbs values(1,'a');
insert into t1_tbs values(2,'b');
commit;
create index idx_t1_tbs on t1_tbs(id) tablespace idx_data;-- 检查平台版本
SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM
WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%'
or UPPER(PLATFORM_NAME) LIKE '%MICROSOFT%';
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
-- 创建文件夹，
create directory DUMP as 'c\dump';
grant read,write,execute on directory DUMP to public;-- 检查是否有自包含
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_data',true,true); -- 比较严格的
select * from sys.transport_set_violations;
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_data',true); -- 不太严格的
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_data,idx_data',true);
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_data',true,true);
PL/SQL 过程已成功完成。
SQL> select * from sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: 索引 SYS.IDX_T1_TBS (在表空间 IDX_DATA 中) 指向表 SYS.T1_TBS (在表空间 TBS_DATA 中)。
SQL>
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_data,idx_data',true);
PL/SQL 过程已成功完成。
SQL> select * from sys.transport_set_violations;
未选定行
SQL>-- 设置表空间为只读表空间
alter tablespace tbs_data read only;
alter tablespace idx_data read only;
SQL> alter tablespace tbs_data read only;
表空间已更改。
SQL> alter tablespace idx_data read only;
表空间已更改。
SQL>
-- expdp导出要传输表空间的元数据
expdp '/as sysdba' dumpfile = trans_tbs_%U.dmp directory = DUMP transport_tablespaces=tbs_data,idx_data logfile=trans.log parallel =2
ORA-39047: TRANSPORTABLE 类型的作业不能使用多个执行流。 -- 不能用并行，去掉并行 OK .
PS C:\Users\Administrator> expdp "'/ as sysdba'" dumpfile = trans_tbs.dmp directory = DUMP transport_tablespaces=tbs_dat
a,idx_data logfile=trans.log
Export: Release 11.2.0.4.0 - Production on 星期二 3月 17 11:51:05 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=trans_tbs.dmp directory=DUMP transport_tablesp
aces=tbs_data idx_data logfile=trans.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
C:\DUMP\TRANS_TBS.DMP
******************************************************************************
可传输表空间 IDX_DATA 所需的数据文件:
D:\ORADATA\TESTOGG\IDX_DATA01.DBF
可传输表空间 TBS_DATA 所需的数据文件:
D:\ORADATA\TESTOGG\TBS_DATA01.DBF
作业 "SYS"."SYS_EXPORT_TRANSPORTABLE_01" 已于 星期二 3月 17 11:51:59 2020 elapsed 0 00:00:53 成功完成
PS C:\Users\Administrator>-- 将导出的元数据和数据库文件，传输到目标库（略）
-- 在目标库导入表空间
impdp / as sysdba directory=DUMP dumpfile=TRANS_TBS.DMP transport_datafiles='/u01/app/oracle/oradata/test/IDX_DATA01.DBF','/u01/app/oracle/oradata/test/TBS_DATA01.DBF' logfile=imp.log
[oracle@wls10306-01 test]$ impdp "'/ as sysdba'" directory=DUMP dumpfile=TRANS_TBS.DMP transport_datafiles='/u01/app/oracle/oradata/test/IDX_DATA01.DBF','/u01/app/oracle/oradata/test/TBS_DATA01.DBF' logfile=imp.log
Import: Release 12.2.0.1.0 - Production on Tue Mar 17 13:47:31 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
import done in AL32UTF8 character set and UTF8 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DUMP dumpfile=TRANS_TBS.DMP transport_datafiles=/u01/app/oracle/oradata/test/IDX_DATA01.DBF,/u01/app/oracle/oradata/test/TBS_DATA01.DBF logfile=imp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Mar 17 13:48:17 2020 elapsed 0 00:00:26
[oracle@wls10306-01 test]$
-- 将表空间read write
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
IDX_DATA READ ONLY
TBS_DATA READ ONLY
8 rows selected.
SQL> alter tablespace IDX_DATA read write;
Tablespace altered.
SQL> alter tablespace TBS_DATA read write;
Tablespace altered.
SQL> select * from t1_tbs;
ID NAME
---------- ------
1 a
2 b
SQL> END （使用增量备份的方法，下次有机会再测试）
-- 几个补充
从10gr2开始，可以传输包含XMLTypes的表空间，从11gR1开始，必须使用数据泵导出导入包含XMLTypes的表空间的元数据（而不是使用exp/imp）.在这个测试案例中使用的就是数据泵。所以可以不查询该语句。
select distinct p.tablespace_name from dba_tablespaces p,
dba_xml_tables x, dba_users u, all_all_tables t where
t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
and x.owner=u.username-- 在源端转换
RMAN> CONVERT TABLESPACE sales_1,sales_2
2> TO PLATFORM 'Microsoft Windows IA (32-bit)'
3> FORMAT '/tmp/%U';
--在目标端转换
RMAN> CONVERT DATAFILE
2>'C:\Temp\sales_101.dbf',
3>'C:\Temp\sales_201.dbf'
4>TO PLATFORM="Microsoft Windows IA (32-bit)"
5>FROM PLATFORM="Solaris[tm] OE (32-bit)"
6>DB_FILE_NAME_CONVERT=
7>'C:\Temp\', 'C:\app\orauser\oradata\orawin\'
8> PARALLELISM=4;-- 或者使用下面的方法转换，在目标端
Convert datafile '/home/oracle/scripts/transport_tbs01.dbf'
from platform 'Solaris[tm] OE (32-bit)'
format '/u01/app/oracle/oradata/PROD1/transport_tbs01.dbf'-- 关于ORA-39047的告警
Parallel Import of Object Metadata in 12.2 and Above - Effect of the TRANSPORTABLE and NETWORK_LINK Parameters (Doc ID 2452220.1)
Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Doc ID 1166564.1)
参考文档：
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN01101
END
-- 2020-03-18 add
-- 能否将该dump文件，生成sql文件，测试结果是不可以
[oracle@wls10306-01 dump]$ impdp "'/ as sysdba'" directory=DUMP dumpfile=TRANS_TBS.DMP logfile=imp_sql.log sqlfile=imp_sql.sql
Import: Release 12.2.0.1.0 - Production on Wed Mar 18 15:56:02 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39002: invalid operation
ORA-39061: import mode FULL conflicts with export mode TRANSPORTABLE
[oracle@wls10306-01 dump]$ [oracle@wls10306-01 dump]$ more imp_sql.log
;;;
Import: Release 12.2.0.1.0 - Production on Wed Mar 18 15:56:02 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
ORA-39061: import mode FULL conflicts with export mode TRANSPORTABLE
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Mar 18 15:56:29 2020 elapsed 0 00:00:12
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Mar 18 15:56:29 2020 elapsed 0 00:00:12
[oracle@wls10306-01 dump]$ oerr ora 39061
39061, 00000, "import mode %s conflicts with export mode %s"
// *Cause: The mode used for import cannot be used with a dump file set of
// specified mode. Transportable jobs are not compatible with other
// modes.
// *Action: Perform the import using a mode compatible with the export.
[oracle@wls10306-01 dump]$
[oracle@wls10306-01 dump]$
END
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)