1. 编程号首页

【传输表空间】exp/imp方式传输表空间

编程小号 • • 未分类

【传输表空间】exp/imp方式传输表空间1、同一台linux系统的两个实例测试，源ccod，目标gc，版本10.2.0.1.0源库ccodSQLselectinstance_namefromv$instance;INSTANCE_NAME-…_ec_cluster_rela是什么表

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.


祝好运！
001.gif

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

转载于:http://blog.itpub.net/20553601/viewspace-749741/

今天的文章【传输表空间】exp/imp方式传输表空间分享到此就结束了，感谢您的阅读。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/65438.html

赞 (0)
编程小号编程小号
0

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注