oracle 传输表空间一例

最新推荐文章于 2024-04-19 17:29:33 发布

北极外套

最新推荐文章于 2024-04-19 17:29:33 发布

阅读量347

点赞数

文档结构图：

1 场景描述

源平台为：rhel6.5 + oracle 11.2.0.3.0 + asm 64位

目标平台：windows xp 32系统 + oracle 11.2.0.1.0

要实现将自定义的应用程序表空间从源平台传递到目标平台

2.1 在源库上创建

windows 平台支持跨平台的表空间传输

源平台和目标平台的Little），不需要进行表空间集转换 4 选择自包含的表空间集（目前要传输app1tbs和idxtbs这2个表空间） 4.1 进行检查 Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set. SQL> execute dbms_tts.transport_set_check('app1tbs,idxtbs',true); PL/SQL procedure successfully completed. 4.2 查看检查结果 SQL> col violations for a70 SQL> select * from transport_set_violations; VIOLATIONS ------------------------------------------------------------------------------------------------------------ ORA-39907: Index scott.IDX_DEPT_DNAME in tablespace IDXTBS points to table scott.APP2_TAB in tablespace APP2TBS. SQL> 结论：在IDX_DEPT_DNAME索引指向了表空间集外的app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查 SQL> execute dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true); PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; no rows selected SQL> 结论：此时这个表空间集已经不在违背自包含的条件，可以确定为一个可传输表空间集 5 产生可传输表空间集 5.1 使自包含的表空间集中的所有表空间变为只读状态 SQL> alter tablespace app1tbs read only; Tablespace altered. SQL> alter tablespace app2tbs read only; Tablespace altered. SQL> alter tablespace idxtbs read only; Tablespace altered. 5.2 使用数据泵导出工具，导出要传输的各个表空间的元数据 5.2.1 确定导出目录 SQL> set line 9999 SQL> col directory_name for a25 SQL> col directory_path for a100 SQL> select directory_name,directory_path from dba_directories; DIRECTORY_NAME DIRECTORY_PATH ------------------------- ---------------------------------------------------------------------------------------------------- REPDIR /oradata06/repdir DIR_ALERT_LHR /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/ DIR_ALERT /u01/app/oracle/diag/rdbms/orclasm/orclasm/trace DIR_ALERT_XML_LHR /u01/app/oracle/diag/rdbms/orclasm/orclasm/alert LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/ MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/ XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/ DATA_PUMP_DIR /u01/app/oracle/admin/orclasm/dpdump/ DIRECTORY_NAME DIRECTORY_PATH ------------------------- ---------------------------------------------------------------------------------------------------- ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state 12 rows selected. 5.2.2 开始导出 [oracle@rhel6_lhr ~]$ env | grep ORACLE ORACLE_UNQNAME=orclasm ORACLE_SID=orclasm ORACLE_HOSTNAME=192.168.59.130 ORACLE_BASE=/u01/app/oracle ORACLE_ALERT=/u01/app/oracle/diag/rdbms/orclasm/orclasm/trace ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 [oracle@rhel6_lhr ~]$ expdp system dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log Export: Release 11.2.0.3.0 - Production on Sun Dec 21 17:48:20 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.logProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEX/INDEXProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /u01/app/oracle/admin/orclasm/dpdump/expdat.dmp******************************************************************************Datafiles required for transportable tablespace APP1TBS: +DATA/orclasm/datafile/app1tbs.274.866911939Datafiles required for transportable tablespace APP2TBS: +DATA/orclasm/datafile/app2tbs.275.866912075Datafiles required for transportable tablespace IDXTBS: +DATA/orclasm/datafile/idxtbs.276.866912133Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:49:47 [oracle@rhel6_lhr ~]$ 查看文件：[root@rhel6_lhr ~]# cd /u01/app/oracle/admin/orclasm/dpdump/[root@rhel6_lhr dpdump]# lltotal 108-rw-r----- 1 oracle asmadmin 106496 Dec 21 17:49 expdat.dmp-rw-r--r-- 1 oracle asmadmin 1499 Dec 21 17:49 tts_export.log[root@rhel6_lhr dpdump]# 告警日志可以看到：Sun Dec 21 17:48:50 2014DM00 started with pid=45, OS id=13188, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01Sun Dec 21 17:48:56 2014DW00 started with pid=46, OS id=13190, wid=1, job SYSTEM.SYS_EXPORT_TRANSPORTABLE_01Sun Dec 21 17:49:15 2014XDB installed.XDB initialized. 5.3 生成数据文件[root@rhel6_lhr ~]# su - grid[grid@rhel6_lhr ~]$ asmcmdASMCMD> cd +DATA/orclasm/datafile/ASMCMD> lsAPP1TBS.274.866911939APP2TBS.275.866912075ENCRYPTED_TS.272.854650889EXAMPLE.265.850260295GOLDENGATE.273.862829891IDXTBS.276.866912133SYSAUX.257.850260145SYSTEM.256.850260145TBS_RC.268.852116523TS_LHR.269.852632495UNDOTBS1.258.851526539UNDOTBS2.267.851204361USERS.259.850260147example.265.850260295_bkASMCMD> cp APP1TBS.274.866911939 /u01/app/oracle/admin/orclasm/dpdumpcopying +DATA/orclasm/datafile/APP1TBS.274.866911939 -> /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939ASMCMD-8016: copy source->'+DATA/orclasm/datafile/APP1TBS.274.866911939' and target->'/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' failedORA-19505: failed to identify file "/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939"ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 13: Permission deniedAdditional information: 1ORA-15120: ASM file name '/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.274.866911939' does not begin with the ASM prefix characterORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)无权限，暂时拷贝到