1、同一台linux系统的两个实例测试，源ccod，目标gc，版本10.2.0.1.0
源库 ccod
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ccod
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS
SYSAUX
TEMP
7777
CCOD
PERF
FY_REC_DATA
FY_RST_DATA
9 rows selected.
目的库 gc
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
gc
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
迁移表空间为ccod，TTS 操作的目标库上不能包含重名的表空间，如果有可以先对tablespace 进行rename。
查看源库 ccod 用户下的对象
SQL> conn ccod/ccod
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CCOD_SYS_MENU TABLE
CCOD_SYS_MENU_ROLE TABLE
CCOD_SYS_ROLE TABLE
CCOD_SYS_USER TABLE
CCOD_SYS_USER_ROLE TABLE
CFG_ALTER_INFO TABLE
CFG_NODE TABLE
CFG_SMS_LOG TABLE
DCMS_DOMAIN_MANAGER TABLE
DCMS_LOGIN_USER TABLE
DCMS_MANAGEMENT TABLE
DCMS_MENU TABLE
DCMS_USER_MENU TABLE
EC_CLUSTER_AREA TABLE
EC_CLUSTER_INFO TABLE
EC_CLUSTER_RELA TABLE
EC_EXCEL_INFO TABLE
EC_EXCEL_RELA TABLE
EC_RI_CONFIG TABLE
EC_VGPROXY_INFO TABLE
EC_VGPROXY_TAC TABLE
EC_VGPT_RELA TABLE
GLS_ACCOUNT_NUMBER TABLE
GLS_C_E_RELATION TABLE
GLS_DATA_CLEAN TABLE
GLS_DB_AGENT_RELATE TABLE
GLS_DB_ENT_RELATE TABLE
GLS_DB_SG_RELATE TABLE
GLS_DB_SOURCE TABLE
GLS_ENTERPRISE_INFO TABLE
GLS_ILDTLICENSE TABLE
GLS_INT_COUNTRY_PHONE TABLE
GLS_INT_PROVINCE_PHONE TABLE
GLS_LOG_FILTER_COLUMN TABLE
GLS_LOG_FILTER_OBJ TABLE
GLS_MONITOR_CONFIG TABLE
GLS_OPERATION_LOG TABLE
GLS_OPERATION_LOG_DETAIL TABLE
GLS_PARAMETER TABLE
GLS_RESOURCE_NUM TABLE
GLS_SERVICE_ROUTE TABLE
GLS_SERVICE_UNIT TABLE
GLS_SS_CONFIG TABLE
GLS_VIRTUAL_ENT TABLE
GLS_VIRTUAL_ENT_RELATION TABLE
OCX_UPDATE_LOG TABLE
PROXY_AGENT_INFO TABLE
47 rows selected.
SQL>
2、字符集检查
TTS的两端必须使用兼容的database character、national character sets
源库 ccod
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> select * from database_properties where property_name like 'NLS_NCHAR_CHARACTERSET';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- --------------------
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
目的库 gc
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> select * from database_properties where property_name like 'NLS_NCHAR_CHARACTERSET';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- --------------------
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
3、源系统和目标系统是不同的endianness
--TTS仅支持在endian 格式一致的情况，那么必须先转换，让两端一致，本次测试为同一台机器。
检查方法：
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
17 rows selected.
4、检查表空间是否为自包含
--TTS 仅支持对象都在要传输的表空间里，表空间里的对象不能与其他表空间有逻辑上或者物理上的依赖关系。
使用TRANSPORT_SET_CHECK过程来检查表空间是否自包含，调用该过程需要EXECUTE_CATALOG_ROLE角色。
源库 ccod
SQL> desc dbms_tts
PROCEDURE DOWNGRADE
FUNCTION ISSELFCONTAINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN
FULL_CHECK BOOLEAN IN
PROCEDURE KCP_CKCMP
FUNCTION TRANSPORT_CHAR_SET_CHECK RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
ERR_MSG VARCHAR2 OUT
PROCEDURE TRANSPORT_CHAR_SET_CHECK_MSG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
PROCEDURE TRANSPORT_SET_CHECK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN DEFAULT
FULL_CHECK BOOLEAN IN DEFAULT
SQL> exec dbms_tts.transport_set_check('ccod',true);
PL/SQL procedure successfully completed.
检查自包含的结果可以通过TRANSPORT_SET_VIOLATIONS视图查看，如果为空，就是自包含，如果不是自包含的，会列出这些对象。
SQL> select * from transport_set_violations;
no rows selected
5、将被传输的表空间设为只读后，exp导出
SQL> alter tablespace ccod read only;
Tablespace altered.
SQL> exit
[oracle@databak ~]$ exp userid=\"/ as sysdba\" TRANSPORT_TABLESPACE=y TABLESPACES=ccod file=ccod.dmp
Export: Release 10.2.0.1.0 - Production on Thu Nov 22 14:17:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace CCOD ...
. exporting cluster definitions
. exporting table definitions
. . exporting table CCOD_SYS_MENU
. . exporting table CCOD_SYS_MENU_ROLE
. . exporting table CCOD_SYS_ROLE
. . exporting table CCOD_SYS_USER
. . exporting table CCOD_SYS_USER_ROLE
. . exporting table CFG_ALTER_INFO
. . exporting table CFG_NODE
. . exporting table CFG_SMS_LOG
. . exporting table DCMS_DOMAIN_MANAGER
. . exporting table DCMS_LOGIN_USER
. . exporting table DCMS_MANAGEMENT
. . exporting table DCMS_MENU
. . exporting table DCMS_USER_MENU
. . exporting table EC_CLUSTER_AREA
. . exporting table EC_CLUSTER_INFO
. . exporting table EC_CLUSTER_RELA
. . exporting table EC_EXCEL_INFO
. . exporting table EC_EXCEL_RELA
. . exporting table EC_RI_CONFIG
. . exporting table EC_VGPROXY_INFO
. . exporting table EC_VGPROXY_TAC
. . exporting table EC_VGPT_RELA
. . exporting table GLS_ACCOUNT_NUMBER
. . exporting table GLS_C_E_RELATION
. . exporting table GLS_DATA_CLEAN
. . exporting table GLS_DB_AGENT_RELATE
. . exporting table GLS_DB_ENT_RELATE
. . exporting table GLS_DB_SG_RELATE
. . exporting table GLS_DB_SOURCE
. . exporting table GLS_ENTERPRISE_INFO
. . exporting table GLS_ILDTLICENSE
. . exporting table GLS_INT_COUNTRY_PHONE
. . exporting table GLS_INT_PROVINCE_PHONE
. . exporting table GLS_LOG_FILTER_COLUMN
. . exporting table GLS_LOG_FILTER_OBJ
. . exporting table GLS_MONITOR_CONFIG
. . exporting table GLS_OPERATION_LOG
. . exporting table GLS_OPERATION_LOG_DETAIL
. . exporting table GLS_PARAMETER
. . exporting table GLS_RESOURCE_NUM
. . exporting table GLS_SERVICE_ROUTE
. . exporting table GLS_SERVICE_UNIT
. . exporting table GLS_SS_CONFIG
. . exporting table GLS_VIRTUAL_ENT
. . exporting table GLS_VIRTUAL_ENT_RELATION
. . exporting table OCX_UPDATE_LOG
. . exporting table PROXY_AGENT_INFO
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
6、将导出的dmp文件导入目的库gc中
[oracle@databak ~]$ export ORACLE_SID=gc
[oracle@databak ~]$ cp /data/oracle10g/oradata/ccod/ccod.dbf /data/oracle10g/oradata/gc/
[oracle@databak ~]$ imp userid=\"/ as sysdba\" file=ccod.dmp TRANSPORT_TABLESPACE=y TABLESPACES=ccod TTS_OWNERS=ccod DATAFILES=/data/oracle10g/oradata/gc/ccod.dbf fromuser=ccod touser=ccod
Import: Release 10.2.0.1.0 - Production on Thu Nov 22 14:27:23 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00017: following statement failed with ORACLE error 29343:
"BEGIN sys.dbms_plugts.checkUser('CCOD'); END;"
IMP-00003: ORACLE error 29343 encountered
ORA-29343: user CCOD (mapped from user CCOD) does not exist in the database
ORA-06512: at "SYS.DBMS_PLUGTS", line 1895
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
由报错知，未在目标库gc建ccod用户导致
[oracle@databak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 22 14:27:33 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user ccod identified by ccod;
User created.
SQL> grant dba,resource to ccod;
Grant succeeded.
SQL> exit
[oracle@databak ~]$ imp userid=\"/ as sysdba\" file=ccod.dmp TRANSPORT_TABLESPACE=y TABLESPACES=ccod TTS_OWNERS=ccod DATAFILES=/data/oracle10g/oradata/gc/ccod.dbf fromuser=ccod touser=ccod
Import: Release 10.2.0.1.0 - Production on Thu Nov 22 14:27:58 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CCOD's objects into CCOD
. . importing table "CCOD_SYS_MENU"
. . importing table "CCOD_SYS_MENU_ROLE"
. . importing table "CCOD_SYS_ROLE"
. . importing table "CCOD_SYS_USER"
. . importing table "CCOD_SYS_USER_ROLE"
. . importing table "CFG_ALTER_INFO"
. . importing table "CFG_NODE"
. . importing table "CFG_SMS_LOG"
. . importing table "DCMS_DOMAIN_MANAGER"
. . importing table "DCMS_LOGIN_USER"
. . importing table "DCMS_MANAGEMENT"
. . importing table "DCMS_MENU"
. . importing table "DCMS_USER_MENU"
. . importing table "EC_CLUSTER_AREA"
. . importing table "EC_CLUSTER_INFO"
. . importing table "EC_CLUSTER_RELA"
. . importing table "EC_EXCEL_INFO"
. . importing table "EC_EXCEL_RELA"
. . importing table "EC_RI_CONFIG"
. . importing table "EC_VGPROXY_INFO"
. . importing table "EC_VGPROXY_TAC"
. . importing table "EC_VGPT_RELA"
. . importing table "GLS_ACCOUNT_NUMBER"
. . importing table "GLS_C_E_RELATION"
. . importing table "GLS_DATA_CLEAN"
. . importing table "GLS_DB_AGENT_RELATE"
. . importing table "GLS_DB_ENT_RELATE"
. . importing table "GLS_DB_SG_RELATE"
. . importing table "GLS_DB_SOURCE"
. . importing table "GLS_ENTERPRISE_INFO"
. . importing table "GLS_ILDTLICENSE"
. . importing table "GLS_INT_COUNTRY_PHONE"
. . importing table "GLS_INT_PROVINCE_PHONE"
. . importing table "GLS_LOG_FILTER_COLUMN"
. . importing table "GLS_LOG_FILTER_OBJ"
. . importing table "GLS_MONITOR_CONFIG"
. . importing table "GLS_OPERATION_LOG"
. . importing table "GLS_OPERATION_LOG_DETAIL"
. . importing table "GLS_PARAMETER"
. . importing table "GLS_RESOURCE_NUM"
. . importing table "GLS_SERVICE_ROUTE"
. . importing table "GLS_SERVICE_UNIT"
. . importing table "GLS_SS_CONFIG"
. . importing table "GLS_VIRTUAL_ENT"
. . importing table "GLS_VIRTUAL_ENT_RELATION"
. . importing table "OCX_UPDATE_LOG"
. . importing table "PROXY_AGENT_INFO"
Import terminated successfully without warnings.
7、在目标库gc中，将表空间置为读写模式
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
CCOD READ ONLY
6 rows selected.
SQL> alter tablespace ccod read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
CCOD ONLINE
6 rows selected.
SQL> conn ccod/ccod
Connected.
SQL> /
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GLS_ENTERPRISE_INFO TABLE
GLS_ILDTLICENSE TABLE
GLS_INT_COUNTRY_PHONE TABLE
GLS_INT_PROVINCE_PHONE TABLE
GLS_LOG_FILTER_COLUMN TABLE
GLS_LOG_FILTER_OBJ TABLE
GLS_MONITOR_CONFIG TABLE
GLS_OPERATION_LOG TABLE
GLS_OPERATION_LOG_DETAIL TABLE
GLS_PARAMETER TABLE
GLS_RESOURCE_NUM TABLE
GLS_SERVICE_ROUTE TABLE
GLS_SERVICE_UNIT TABLE
GLS_SS_CONFIG TABLE
GLS_VIRTUAL_ENT TABLE
GLS_VIRTUAL_ENT_RELATION TABLE
OCX_UPDATE_LOG TABLE
PROXY_AGENT_INFO TABLE
CCOD_SYS_MENU TABLE
CCOD_SYS_MENU_ROLE TABLE
CCOD_SYS_ROLE TABLE
CCOD_SYS_USER TABLE
CCOD_SYS_USER_ROLE TABLE
CFG_ALTER_INFO TABLE
CFG_NODE TABLE
CFG_SMS_LOG TABLE
DCMS_DOMAIN_MANAGER TABLE
DCMS_LOGIN_USER TABLE
DCMS_MANAGEMENT TABLE
DCMS_MENU TABLE
DCMS_USER_MENU TABLE
EC_CLUSTER_AREA TABLE
EC_CLUSTER_INFO TABLE
EC_CLUSTER_RELA TABLE
EC_EXCEL_INFO TABLE
EC_EXCEL_RELA TABLE
EC_RI_CONFIG TABLE
EC_VGPROXY_INFO TABLE
EC_VGPROXY_TAC TABLE
EC_VGPT_RELA TABLE
GLS_ACCOUNT_NUMBER TABLE
GLS_C_E_RELATION TABLE
GLS_DATA_CLEAN TABLE
GLS_DB_AGENT_RELATE TABLE
GLS_DB_ENT_RELATE TABLE
GLS_DB_SG_RELATE TABLE
GLS_DB_SOURCE TABLE
47 rows selected.
祝好运！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20553601/viewspace-749741/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
FY_Recover_Data
请登录后发表评论
登录
全部评论
