1  说明

利用数据泵进行数据迁移已经不新鲜了，在数据库升级中也经常使用。很多步骤都是大同小异，这个实验就是讲oracle11g R2数据库迁移到12cR2的PDB中。

 

相关参考文档：

​​http://docs.oracle.com/database/121/UPGRD/preup.htm#UPGRD12371​​

​​http://docs.oracle.com/database/121/UPGRD/expimp.htm#UPGRD12565​​

2  前提条件

安装好12c数据库软件并创建数据库(dbca).

略...

参考：12c R2安装​​http://www.cndba.cn/Expect-le/article/1746​​

 

通过数据泵进行迁移有两种方法：

1.  Expdp/impdp

2.  Impdp dblink  语法：IMPDP import_user NETWORK_LINK=db_link FULL=Y;

好处：节省源库的存储空间，占带宽。

3  开始

3.1   环境说明

源库：

 

SQL> select * from v$version where rownum=1;


BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

 

目标库:

 

BANNER CON_ID

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production   0

 

3.2   源库创建一个测试表

为了验证数据是否正确导入

 

SQL> select count(*) from scott.emp;

 

  COUNT(*)

----------

14

 

3.3   源库全库导出

Data Pump can be used to migrate all or any portion of a database between different releases of the database software. The Data Pump Export VERSION parameter is typically used to do this. This will generate a Data Pump dump file set compatible with the specified version.

 

The default value for VERSION is COMPATIBLE, indicating that exported database object definitions will be compatible with the release specified for the COMPATIBLE initialization parameter.

 

In an upgrade situation, when the target release of a Data Pump-based migration is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release. An exception is when an entire Oracle Database 11g (release 11.2.0.3 or higher) is exported in preparation for importing into Oracle Database 12c Release 1 (12.1.0.1) or later. In this case, explicitly specify VERSION=12 in conjunction with FULL=YES in order to include a complete set of Oracle internal component metadata.

 

In a downgrade situation, when the target release of a Data Pump-based migration is lower than the source, the VERSION parameter should be explicitly specified to be the same version as the target. An exception is when the target release version is the same as the value of the COMPATIBLE initialization parameter on the source system; then VERSION does not need to be specified. In general however, Data Pump import cannot read dump file sets created by an Oracle release that is newer than the current release unless the VERSION parameter is explicitly specified.

 

从上面可以看出11.2.0.3及以上导入到12.1.0.1及以上，需要在导出时加version=12

 

[[email protected] ~]$ expdp system/oracle directory=lei_dir full=y dumpfile=dbfull.dmp logfile=dbfull.log version=12

 

导出完成！

3.4   拷贝导出文件到目标库(如果同一台机器上，略)

 

[[email protected] ~]$ scp /u01/backup/dbfull.dmp 192.168.1.110:/u01/backup/

 

3.5   目标库导入

--检查导入文件

 

[[email protected] backup]$ ll dbfull.dmp

-rw-r----- 1 oracle oinstall 140853248 Jul 28 02:56 dbfull.dmp

 

--执行导入，导入LEI1中

 

SQL> alter session set container=lei1;

SQL> create directory cndba as '/u01/backup';


Directory created.

 

注意：导入的时候，虽然会自动创建表空间和相关数据文件，但是要确保源库的数据文件所在的目录，目标库也要存在相应的目录，要不就会报错。

 

如：

 

ORA-39083: Object type TABLESPACE:"IUYJ" failed to create with error:

ORA-01119: error in creating database file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/ytruty'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

 

 

[[email protected] ~]$ impdp [email protected]:1521/LEI1 DIRECTORY=cndba full=y logfile=impfull.log dumpfile=dbfull.dmp

Import: Release 12.2.0.1.0 - Production on Fri Jul 28 15:00:10 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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

Warning: possible data loss in character set conversions

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  [email protected]:1521/LEI1 DIRECTORY=cndba full=y logfile=impfull.log dumpfile=dbfull.dmp

Processing object type DATABASE_EXPORT/TABLESPACE

ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"TEMP" already exists

ORA-31684: Object type TABLESPACE:"USERS" already exists

略.............

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SYSTEM"."SYS_EXPORT_FULL_01"               25.58 MB   25939 rows

. . imported "LEO"."SYS_EXPORT_FULL_01"                  21.29 MB   21429 rows

. . imported "SQLTXPLAIN"."SQLI$_FILE"                   11.77 MB     148 rows

. . imported "LEO"."LEO"                                 8.376 MB   86371 rows

. . imported "SQLTXPLAIN"."TRCA$_FILE"                   5.270 MB      10 rows

. . imported "APEX_030200"."WWV_FLOW_PAGE_PLUGS"         3.834 MB    7416 rows

. . imported "APEX_030200"."WWV_FLOW_STEP_ITEMS"         3.505 MB    9671 rows

. . imported "APEX_030200"."WWV_FLOW_DICTIONARY$"        2.909 MB   70601 rows

. . imported "APEX_030200"."WWV_FLOW_STEP_PROCESSING"    1.248 MB    2238 rows

. . imported "APEX_030200"."WWV_FLOW_REGION_REPORT_COLUMN"  1.146 MB    7903 rows

. . imported "APEX_030200"."WWV_FLOW_STEP_ITEM_HELP"     1003. KB    6335 rows

. . imported "SYSMAN"."MGMT_METRICS"                     3.233 MB   12635 rows

. . imported "APEX_030200"."WWV_FLOW_STEPS"              570.5 KB    1754 rows

. . imported "LEI"."TEST"                                701.6 KB    2810 rows

. . imported "SQLTXPLAIN"."SQLT$_LOG"                    643.1 KB    3373 rows

. . imported "SQLTXPLAIN"."TRCA$_IND_COLUMNS$"           622.2 KB    8134 rows

. . imported "APEX_030200"."WWV_FLOW_LIST_ITEMS"         590.3 KB    3048 rows

........

 

ORA-39082: Object type FUNCTION:"SYSMAN"."ENCRYPTBYTES" created with compilation warnings

​​ORA-39082: Object type FUNCTION:"SYSMAN"."DECRYPTBYTES" created with compilation warnings​​​​ORA-39082: Object type FUNCTION:"SYSMAN"."ENCRYPT" created with compilation warnings​​​​ORA-39082: Object type FUNCTION:"SYSMAN"."DECRYPT" created with compilation warnings​​​​ORA-39082: Object type PROCEDURE:"APEX_030200"."F" created with compilation warnings​​​​ORA-39082: Object type PROCEDURE:"APEX_030200"."APEX_ADMIN" created with compilation warnings​​​​ORA-39082: Object type PROCEDURE:"APEX_030200"."HTMLDB_ADMIN" created with compilation warnings​​​​ORA-39082: Object type PROCEDURE:"SCOTT"."TEST_JOBPROCE" created with compilation warnings​​​​ORA-39082: Object type VIEW:"SYSMAN"."AQ$MGMT_NOTIFY_QTABLE" created with compilation warnings​​​​ORA-39082: Object type VIEW:"SYSMAN"."AQ$MGMT_LOADER_QTABLE" created with compilation warnings​​​​ORA-39082: Object type TRIGGER:"APEX_030200"."WWV_BIU_FLOW_SESSIONS" created with compilation warnings​​​​ORA-39082: Object type TRIGGER:"SYSMAN"."MGMT_CREDS_INS_UPD" created with compilation warnings​​​​--如果有无效对象，运行?/rdbms/admin/utlrp.sql重新编译，切换到相应的PDB下运行。非SYS,SYSTEM的无效对象，需要手动进行处理。​​
 
3.6   简单验证一下
 
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
14
 
表的数据量一致。
从目前来看，已经顺利的将11g迁移到12c R2中。