传输数据库full transportable export/import
可以使用full transportable export/import功能将整个数据库从一个数据库实例复制到另一个数据库实例。可以使用Data Pump来生成一个导出dump文件,如果需要将这个dump文件传输到目标数据库,然后导入dump文件。另外也可以使用Data Pump跨网络来复制数据库。
数据库中要被传输的表空间可以是字典管理或本地管理表空间。源数据库中的表空间的块大小不必与目标数据库中标准块大小相同。
这种传输数据库的方法要求直到完成导出dump文件之前所要传输的用户创建的表空间必须设置为只读状态。如果不能满足这个条件那么可以使用备份功能来完成传输表空间。
full transportable export/import的限制
full transportable export/import有以下限制:
.对于不同字节编码的平台不能传输加密表空间,对于相同字节编码的平台为了传输加密表空间,在执行导出dump文件时需要设置encryption_pwd_prompt导出参数设置为yes,或者使用encryption_password导出参数。在导入dump文件时,使用与导出时相同的参数设置。
.当跨网络传输数据库时,如果在管理表空间(比如system或sysaux表空间)中存在包含long或long raw列的表,那么是不支持传输的。
.full transportable export/import可以使用传统的Data Pump导出/导入来导出与导入存储在管理表空间中用户创建的数据库对象,比如直接路径或外部表。管理表空间不是用户创建而是由数据库提供,比如sytem与sysaux表空间。
.full transportable export/import不能传输同时存储在管理表空间(比如system与sysaux)与用户创建表空间中的数据库对象。例如,一个分区表可能会同时存储在管理表空间与用户表空间中。如果有这样的对象,那么在传输之前应该重新定义这些对象,因此它们将整个存储在管理表空间或者用户表空间中。如果对象不能重定义,那么可以使用传统的Data Pump导出/导入。.当跨网络传输数据库时,当存储在管理表空间(比如system与sysaux)中的表它的审计跟踪住处本身存储在用户表空间中就不能启用审计。
使用导出dump文件来传输数据库
使用导出dump文件方式来传输数据库必须执行以下步骤:
1.在源数据库上,将每个用户表空间设置为只读状态。在执行导出操作时要确保设置参数transportable=always与full=y。如果源数据库的版本是11.2.0.3或11G之后的版本,那么还必须设置version=12或更高版本号。导出的dump文件包含了存储在用户表空间中对象的元数据与存储在管理表空间(比如system与sysaux)中用户创建对象的元数据与实际数据。
2.将导出的dump文件传输到目标数据库
3.将所有用户表空间的所有数据文件传输到目标数据库,如果源平台与目标平台不同,那么需要检查字节编码,可以通过查询v$transportable_platform视图进行查看。如果源平台与目标平台的字节编码不一样,那么使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会将源数据文件自动转换为目标平台的字节编码方式。
.使用rman的convert命令将源数据文件转换为目标平台的字节编码方式
4.可选操作,将源数据库中的将被传输的表空间设置为读写状态
5.在目标数据库中导入数据,当导入完成后,用户表空间将会设置为读写状态。
下面的例子将把jyrac数据库(11.2.0.4)传输到jypdb数据库(12.2的PDB),源数据库jyrac中用户表空间为test,users,example,源平台与目标平台的字节编码相同。具体操作如下:
1.将表空间test设置为只读状态
SQL> alter tablespace test read only; Tablespace altered. SQL> alter tablespace users read only; Tablespace altered. SQL> alter tablespace example read only; Tablespace altered. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS READ ONLY UNDOTBS2 ONLINE EXAMPLE READ ONLY TEST READ ONLY 8 rows selected.
2.使用Data Pump导出工具执行full transportable export操作
SQL> create or replace directory tts_dump as '/tts'; Directory created SQL> grant execute,read,write on directory tts_dump to public; Grant succeeded SQL> host expdp tts/tts@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log Export: Release 11.2.0.4.0 - Production on Fri May 26 17:41:33 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "TTS"."SYS_EXPORT_FULL_01": tts/********@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 47.43 MB Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/CONTEXT Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088402C00006$$". ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088405C00002$$". Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW Processing object type DATABASE_EXPORT/SCHEMA/JOB Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION Processing object type DATABASE_EXPORT/END_PLUGTS_BLK Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.976 KB 38 rows . . exported "SYS"."AUD$" 473.3 KB 2931 rows . . exported "SYS"."DAM_CONFIG_PARAM$" 6.367 KB 10 rows . . exported "WMSYS"."WM$ENV_VARS" 5.921 KB 3 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 rows Master table "TTS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TTS.SYS_EXPORT_FULL_01 is: /tts/exp_test.dmp ****************************************************************************** Datafiles required for transportable tablespace EXAMPLE: +DATADG/jyrac/datafile/example.260.930413057 Datafiles required for transportable tablespace TEST: +DATADG/jyrac/datafile/test01.dbf Datafiles required for transportable tablespace USERS: +DATADG/jyrac/datafile/users.263.930413057 Job "TTS"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Fri May 26 17:47:08 2017 elapsed 0 00:05:31
在执行导出时必须指定transportable=always,它用来判断是否使用传输选项。full参数用来指定将导出整个数据库。dumpfile参数指定dump文件名。directory参数指定目录,它可以指向操作系统或ASM磁盘组。在执行导出前必须先创建目录,并授予读写权限。在non-CDB中,会自动创建目录对象DATA_PUMP_DIR,并且会自动授予DBA角色可以对其执行读写访问。因此sys与system用户就可以对目录执行读写操作。然而在PDB中不会自动创建目录DATA_PUMP_DIR。因此在导入PDB时,需要先创建目录。logfile参数用来指定导出操作日志文件。为了对数据库版本为11.2.0.3或以后的11G版本执行full transportable导出,必须使用version参数,并且必须指定为12或更高版本。
full transportable导入操作只有在Oracle 12c中支持,因此目标数据库必须为12c
3.将导出的dump文件传输到目标平台的所选定的目录中,该目录可以被目标数据库所访问在目标数据库中创建目录tts_dump(存储dump文件),tts_datafile(存储数据文件)
SQL> create or replace directory tts_dump as '/tts'; Directory created. SQL> grant execute,read,write on directory tts_dump to public; Grant succeeded. SQL> create or replace directory tts_datafile as '+test/jycs/datafile'; Directory created. SQL> grant execute,read,write on directory tts_datafile to public; Grant succeeded.
在目标数据库中执行以下命令来传输dump文件
[oracle@jytest1 tts]$ scp -r oracle@10.138.130.152:/tts/exp_test.dmp /tts/ The authenticity of host '10.138.130.152 (10.138.130.152)' can't be established. RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.138.130.152' (RSA) to the list of known hosts. oracle@10.138.130.152's password: exp_test.dmp 100% 59MB 29.5MB/s 00:02 [oracle@jytest1 tts]$
4.从源平台将所有用户表空间传的相关数据文件输到目标平台的tts_datafile文件,通过dbms_file_transfer.put_file过程来实现。
创建源数据库连接目标数据库的数据链路
SQL> create database link jycs_link 2 connect to system identified by "xxzx7817600" 3 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.175)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = jycs)))'; Database link created SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATADG/jyrac/datafile/users.263.930413057 USERS +DATADG/jyrac/datafile/undotbs1.262.930413057 UNDOTBS1 +DATADG/jyrac/datafile/sysaux.258.930413055 SYSAUX +DATADG/jyrac/datafile/system.259.930413057 SYSTEM +DATADG/jyrac/datafile/example.260.930413057 EXAMPLE +DATADG/jyrac/datafile/undotbs2.261.930413057 UNDOTBS2 +DATADG/jyrac/datafile/test01.dbf TEST 7 rows selected
需要传输的数据文件为test01.dbf,example.260.930413057与users.263.930413057
SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf',destination_database => 'jypdb_link'); PL/SQL procedure successfully completed SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf',destination_database => 'jypdb_link'); PL/SQL procedure successfully completed SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf',destination_database => 'jypdb_link'); PL/SQL procedure successfully completed
在目标数据库的ASM磁盘组可以看到相关的数据文件
ASMCMD [+test/jycs/datafile] > ls -lt Type Redund Striped Time Sys Name DATAFILE MIRROR COARSE MAY 26 18:00:00 N users01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.281.945022371 DATAFILE MIRROR COARSE MAY 26 18:00:00 N test01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.279.945022099 DATAFILE MIRROR COARSE MAY 26 18:00:00 N example01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.280.945022161 DATAFILE MIRROR COARSE MAY 26 18:00:00 Y FILE_TRANSFER.281.945022371 DATAFILE MIRROR COARSE MAY 26 18:00:00 Y FILE_TRANSFER.280.945022161 DATAFILE MIRROR COARSE MAY 26 18:00:00 Y FILE_TRANSFER.279.945022099 DATAFILE MIRROR COARSE MAY 26 06:00:00 Y SYSAUX.260.942323941 DATAFILE MIRROR COARSE MAY 20 22:00:00 Y UNDOTBS1.259.942323977 DATAFILE MIRROR COARSE MAY 11 12:00:00 Y SYSTEM.269.942323889 DATAFILE MIRROR COARSE MAY 11 00:00:00 Y UNDOTBS2.266.942324411 DATAFILE MIRROR COARSE MAY 02 11:00:00 Y USERS.258.942323981
5.可选操作,将源数据库中的所有用户表空间设置为读写模式
SQL> alter tablespace users read write; Tablespace altered. SQL> alter tablespace test read write; Tablespace altered. SQL> alter tablespace example read write; Tablespace altered. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE UNDOTBS2 ONLINE EXAMPLE ONLINE TEST ONLINE 8 rows selected.
5.在目标数据库上执行数据库导入
[oracle@jytest1 admin]$ impdp jy/jy@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' logfile=import.log Import: Release 12.2.0.1.0 - Production on Fri May 26 20:18:03 2017 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 "JY"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "JY"."SYS_IMPORT_TRANSPORTABLE_01": jy/********@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf logfile=import.log Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK Processing object type DATABASE_EXPORT/TABLESPACE ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists ORA-31684: Object type TABLESPACE:"TEMP" already exists Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER ORA-31685: Object type USER:"SYS" failed due to insufficient privileges. Failing sql is: ALTER USER "SYS" IDENTIFIED BY VALUES 'S:0C82FC9FD1570D45359355071D58A402378ABB404B83306BEA34DD19216F;D50A6384B1C2A4CF' TEMPORARY TABLESPACE "TEMP" ..... Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA . . imported "SYS"."KU$_EXPORT_USER_MAP" 5.976 KB 38 rows Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA . . imported "SYS"."AMGT$DP$AUD$" 473.3 KB 2931 rows . . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.367 KB 10 rows . . imported "WMSYS"."E$ENV_VARS" 5.921 KB 3 rows . . imported "WMSYS"."E$EVENTS_INFO" 5.75 KB 12 rows . . imported "WMSYS"."E$HINT_TABLE" 9.25 KB 72 rows . . imported "WMSYS"."E$NEXTVER_TABLE" 6.265 KB 1 rows . . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE" 5.875 KB 1 rows . . imported "WMSYS"."E$WORKSPACES_TABLE" 14.51 KB 1 rows . . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE" 6.851 KB 8 rows . . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows . . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows . . imported "SYS"."NET$_ACL" 0 KB 0 rows . . imported "SYS"."WALLET$_ACL" 0 KB 0 rows . . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES" 0 KB 0 rows . . imported "WMSYS"."E$CONSTRAINTS_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$CONS_COLUMNS" 0 KB 0 rows . . imported "WMSYS"."E$INSTEADOF_TRIGS_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$LOCKROWS_INFO" 0 KB 0 rows . . imported "WMSYS"."E$MODIFIED_TABLES" 0 KB 0 rows . . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$NESTED_COLUMNS_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$RIC_LOCKING_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$RIC_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$RIC_TRIGGERS_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS" 0 KB 0 rows . . imported "WMSYS"."E$UDTRIG_INFO" 0 KB 0 rows . . imported "WMSYS"."E$VERSION_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$VT_ERRORS_TABLE" 0 KB 0 rows . . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE" 0 KB 0 rows Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA ORA-31693: Table data object "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-22303: type "SYS"."JDM_STR_VALS" not found ORA-21700: object does not exist or is marked for delete . . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.859 KB 2 rows . . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows . . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows . . imported "WMSYS"."E$EXP_MAP" 0 KB 0 rows Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE ORA-39082: Object type PACKAGE BODY:"SYS"."WWV_DBMS_SQL" created with compilation warnings ...... ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_EXPR" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_FORM" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_STANDARD" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_XLIFF" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_META_CLEANUP" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_ACC_LOAD" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRMMENU_LOAD_XML" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_LOAD_XML" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_OLB_LOAD_XML" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UPDATE_APX_APP" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UTILITIES" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_RPT_LOAD_XML" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CALENDAR2" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CHART2" created with compilation warnings ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_REPORT3" created with compilation warnings ORA-39082: Object type TRIGGER:"APEX_030200"."WWV_BIU_FLOW_SESSIONS" created with compilation warnings Job "JY"."SYS_IMPORT_TRANSPORTABLE_01" completed with 536 error(s) at Fri May 26 20:45:45 2017 elapsed 0 00:27:38
检查表空间及其状态
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- ------------------------------ +DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015 SYSTEM +DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015 SYSAUX +DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015 UNDOTBS1 +DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063 UNDO_2 +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf USERS +DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf TESTTB +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf EXAMPLE +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf TEST +DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs2.278.945029905 UNDOTBS2 9 rows selected SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE UNDO_2 ONLINE USERS ONLINE TESTTB ONLINE TEMP2 ONLINE TEMP3 ONLINE EXAMPLE ONLINE TEST ONLINE UNDOTBS2 ONLINE 12 rows selected
对于要传输整个数据库来说,使用full transportable export /import这种方法要比传输表空间方便很多。