Oracle 12c full transportable export & import

传输数据库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这种方法要比传输表空间方便很多。

发表评论

电子邮件地址不会被公开。