首页 > 代码库 > TTS备份实验

TTS备份实验

  1. 查看系统版本支持的系统
     1 SQL> select * from v$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME; 2  3 PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT 4 ----------- ------------------------------------ -------------- 5           6 AIX-Based Systems (64-bit)           Big 6          16 Apple Mac OS                         Big 7          21 Apple Mac OS (x86-64)                Little 8          19 HP IA Open VMS                       Little 9          15 HP Open VMS                          Little10           5 HP Tru64 UNIX                        Little11           3 HP-UX (64-bit)                       Big12           4 HP-UX IA (64-bit)                    Big13          18 IBM Power Based Linux                Big14           9 IBM zSeries Based Linux              Big15          10 Linux IA (32-bit)                    Little16 17 PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT18 ----------- ------------------------------------ --------------19          11 Linux IA (64-bit)                    Little20          13 Linux x86 64-bit                     Little21           7 Microsoft Windows IA (32-bit)        Little22           8 Microsoft Windows IA (64-bit)        Little23          12 Microsoft Windows x86 64-bit         Little24          17 Solaris Operating System (x86)       Little25          20 Solaris Operating System (x86-64)    Little26           1 Solaris[tm] OE (32-bit)              Big27           2 Solaris[tm] OE (64-bit)              Big28 29 20 rows selected.

    我源的机器是 Linux x86 64-bit ,参考官方文档,如果两个平台不同,则表空间需要转化。我这边是从 linux 到windows

         特别需要注意一些限制,系统表空间,SYS用户对象等。

    2. 查询表空间是否为自包含  

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(TTS_TPS, TRUE);PL/SQL procedure successfully completed.

      查询视图

SQL> select * from TRANSPORT_SET_VIOLATIONS;no rows selected

     没有记录就是符合条件,没有字包含。

   3.Generate a Transportable Tablespace Set  修改表空间为只读 

SQL> alter tablespace tts_tps read only;Tablespace altered.

  4. 创建目录

SQL> create directory  dir_cyf as /tmp/dir_cyf;Directory created.SQL> grant read,write on directory dir_cyf to public;Grant succeeded.

   5.数据导出

[oracle@evancao dir_cyf]$ expdp  system/a123456  dumpfile=aaaa.dmp directory=dir_cyf        transport_tablespaces=TTS_TPS logfile=exp_cyf.logExport: Release 11.2.0.3.0 - Production on Thu Dec 25 00:51:35 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=aaaa.dmp directory=dir_cyf Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 320 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SYNONYM/SYNONYMProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTIONProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/VIEW/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTIONProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          6.328 KB       2 rows. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.882 KB      28 rows. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.835 KB      19 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.484 KB       3 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           6.289 KB       2 rows. . exported "SYSTEM"."DEF$_AQCALL"                          0 KB       0 rows. . exported "SYSTEM"."DEF$_AQERROR"                         0 KB       0 rows. . exported "SYSTEM"."DEF$_CALLDEST"                        0 KB       0 rows. . exported "SYSTEM"."DEF$_DEFAULTDEST"                     0 KB       0 rows. . exported "SYSTEM"."DEF$_DESTINATION"                     0 KB       0 rows. . exported "SYSTEM"."DEF$_ERROR"                           0 KB       0 rows. . exported "SYSTEM"."DEF$_LOB"                             0 KB       0 rows. . exported "SYSTEM"."DEF$_ORIGIN"                          0 KB       0 rows. . exported "SYSTEM"."DEF$_PROPAGATOR"                      0 KB       0 rows. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"             0 KB       0 rows. . exported "SYSTEM"."MVIEW$_ADV_INDEX"                     0 KB       0 rows. . exported "SYSTEM"."MVIEW$_ADV_PARTITION"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_CONFLICT"                     0 KB       0 rows. . exported "SYSTEM"."REPCAT$_DDL"                          0 KB       0 rows. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"                   0 KB       0 rows. . exported "SYSTEM"."REPCAT$_EXTENSION"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_FLAVORS"                      0 KB       0 rows. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_GENERATED"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"            0 KB       0 rows. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"                  0 KB       0 rows. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"             0 KB       0 rows. . exported "SYSTEM"."REPCAT$_PRIORITY"                     0 KB       0 rows. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"            0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPCAT"                       0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPCATLOG"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPOBJECT"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPPROP"                      0 KB       0 rows. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RESOLUTION"                   0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 rows. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"                0 KB       0 rows. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:  /tmp/dir_cyf/aaaa.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 00:52:42

 6.rman导出数据:

 

RMAN TARGET /RMAN> CONVERT TABLESPACE tts_tps2> TO PLATFORM Microsoft Windows x86 64-bit3>  FORMAT /tmp/dir_cyf/aaaa01.dbf;Starting conversion at source at 25-DEC-14using channel ORA_DISK_1channel ORA_DISK_1: starting datafile conversioninput datafile file number=00009 name=/app/oracle/oradata/orcl/tts01.dbfconverted datafile=/tmp/dir_cyf/aaaa01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16Finished conversion at source at 25-DEC-14

7.拷贝dmp 数据定义文件和dbf 数据文件到目标库服务器

8.在目标库中,执行转换

CONVERT DATAFILE D:\tts01.dbf TO PLATFORM="Microsoft Windows x86 64-bit" FROM PLATFORM="Linux x86 64-bit"  format D:\tts02.dbf;

9.执行导

impdp tts/tts dumpfile=aaaa01.dmp directory=c_dir      transport_datafiles= d:\TTS02.DBF   logfile=tts_import.log

 

TTS备份实验