使用数据泵迁移数据库流程
How To Move Or Copy A Database Using DataPump (文档 ID 855268.1)
In this Document
| Goal |
| Solution |
| References |
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 26-Jan-2018***
GOAL
This note explains how to use data pump to copy or move a database.
SOLUTION
The following steps provide a general overview of how to move a database between platforms.
1. On source database query the views dba_tablespaces, dba_data_files. You will need this information later in the process. An easy method to get the ddl for tablespace creation that you can modify for the target directory structure is to use the dbms_metadata.get_ddl procedure.
set long 1000000
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;
2. Perform a full export from the source database:
#> expdp FULL=Y DUMPFILE=full%U.dmp PARALLEL=4 LOGFILE=<LOG_NAME> EXCLUDE=SCHEMA:"in ('SYSMAN')"
See Note 365459.1 Parallel Capabilities of Oracle Data Pump for discussion on using parallelism with data pump.
If you use dbcontrol on source, recommend excluding that schema from export with EXCLUDE=SCHEMA:"in ('SYSMAN')" . This schema and objects can be created when installing dbcontrol.
Invalid Sysman Objects After Upgrading The Db Via Export/Import Note 604129.1 .
Drop the DB Control configuration and repository then recreate using Note 278100.1 .
3. Transfer the export dumpfile in binary mode to the target server.
4. Create a new database on the target server.
You can use the note below to make sure you have desired database features and options installed.
Note 286775.1 How to Perform a Full Database Export Import during Upgrade, Migrate, Copy, or Move of a Database
5. Before importing the dump file, you must first create the user tablespaces, using the information obtained in step 1. Otherwise, the import will try to create the corresponding datafiles in the same file structure as at the source database, which may not be compatible with the file structure on the target system. As an alternative to precreating the tablespaces, you can use the data pump parameter REMAP_DATAFILE to instruct data pump to create the datafile to new directory structure.
REMAP_DATAFILE=source_datafile:target_datafile
The source datafile directory structure can be determined from step 1output using dbms_metadata.get_ddl
If the source database is not available to extract tablespace DDL you can pull the DDL from the export dump file.
Run a full import with additional data pump parameter SQLFILE=yourname.txt. This will not import, but just write DDL for all objects to that file name.
You can pull the tablespace ddl from this file and edit to desired directory path. Then, run ddl to create the user tablespaces prior to import.
6. Perform a full import
#> impdp FULL=Y DUMPFILE=full%U.dmp PARALLEL=4 LOGFILE=<LOG_NAME>
Note that if the database is an Oracle Applications installation, then specific instructions must be followed as per the applicable notes:
Note 362205.1 10g Release 2 Export/Import Process for Oracle Applications Release 11i
Note 454616.1 Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note 557738.1 Export/import notes on Applications 11i Database 11g
A note concerning use of data pump PARALLEL parameter.
On 10.2 data pump may use other nodes for px processes. This cannot be controlled, unless you either shutdown other nodes during export or do not use parallel. This means all db nodes need access to the data pump directory location for the dumpfiles otherwise the px processes will give errors and cause export to fail.
The same is true on 11.2 only more so. Not only can px processes be used on other nodes, but the data pump worker processes as well could be run on other nodes. Again, this requires all db nodes need access to the data pump directory location for the dumpfiles. On 11.2 you can control where parallel would be used if other nodes do not have access to the directory location. Simplest method is use data pump parameter CLUSTER=N. This forces all worker process and px process to be run on the instance where the job was started. A more complex method would be to specify which services would be available for the data pump using data pump parameter SERVICE_NAME. Specify only services that have access to the directory locations.
To improve the time for export and import you can also exclude statistics from being exported with data pump parameter EXCLUDE=STATISTICS. Statistics can then be gathered on the target system. See Note:749227.1 Master Note: Recommendations for Gathering Optimizer Statistics on 11g. When moving to a new database version, new optimizer statistics should be gathered, and data pump parameter EXCLUDE=STATISTICS should be used.
Regarding Character set change
If you will be changing character sets between source and target database, recommend using the csscan tool to determine if there will be any issues with character conversion. This is especially true when moving from a single byte character set to multibyte character set or moving from WE8MSWIN1252 or US7ASCII character sets.
In order to identify any potential issues, run the csscan utility on the source db to list columns that will a problem.
See the Oracle doc Oracle® Database Globalization Support Guide and these notes for information regarding the character set scanner.
Note 227338.1 Character Set Scanner - Frequently Asked Questions
Note 458122.1 Installing and configuring CSSCAN in 8i and 9i
Note 745809.1 Installing and configuring CSSCAN in 10g and 11g
Note 444701.1 Csscan output explained
Basically you will want to run the csscan as follows on the source db.
#> CSSCAN <username>/<password> FULL=y FROMCHAR= TOCHAR= ARRAY=10240 PROCESS=4
where the FROMCHAR will be the source db character set and TOCHAR the target db character set.
Ideally, you want all user application data to be changeless or convertible.
在升级/迁移/复制/移动数据库的时候如何执行全库导出导入 (文档 ID 2227040.1)
适用于:
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
Oracle Database Cloud Exadata Service - 版本 N/A 和更高版本
Oracle Database Backup Service - 版本 N/A 和更高版本
Oracle Database Cloud Service - 版本 N/A 和更高版本
Oracle Database - Personal Edition - 版本 8.1.7.0 到 18.3.0.0.0 [发行版 8.1.7 到 18]
本文档所含信息适用于所有平台
用途
本文描述了怎样在源库执行全库导出以及如何将导出的 dump 文件导入到目标库。
适用范围
本文为那些想用 DataPump 或者 EXPORT/IMPORT 工具来执行在 oracle 版本 X 上做全库导出，并且将数据全库导入到另外一个 oracle 版本 Y 的 Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle10g, Oracle11g 和 Oracle12c 的 DBA 而写。
本文提供的步骤不适用于 EBS 数据库。对于 Oracle E-Business Suite 数据库，有独立的方法，参考如下文档:
Oracle EBS 11i:
Note 230627.1 - 9i Export/Import Process for Oracle Applications Release 11i
Note 331221.1 - 10g Export/Import Process for Oracle Applications Release 11i
Note 362205.1 - 10g Release 2 Export/Import Process for Oracle Applications Release 11i
Note 557738.1 - Export/import notes on Applications 11i Database 11g
Note 1585257.1 - Export/Import Process for EBS 11i on RDBMS 12c
Oracle EBS 12.0 以及 12.1:
Note 454616.1 - Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Note 741818.1 - Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2
Oracle EBS 12.2:
Note 1613716.1 - Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 11.2 (Doc ID 1613716.1)
本文提供的步骤也不适用于 export 和 import 其他某些特殊的 schema，因为它们也有自己特有的方法。参考如下文档:
Note 1439066.1 - Exporting/Importing in Oracle Portal 11g
Note 1359656.1 - OIM 11gR1: Schema Backup and Restoration using Data Pump Client Utility
详细信息
简介
对于如何升级数据库，参考如下联机手册：
- Oracle Database Upgrade Guide, 10g Release 2 (10.2)
- Oracle Database Upgrade Guide, 11g Release 1 (11.1)
- Oracle Database Upgrade Guide, 11g Release 2 (11.2)
- Oracle Database Upgrade Guide, 12c Release 1 (12.1)
关于 Oracle export 和 import 工具如何用于跨平台和跨 32-bit/64-bit 服务器转换数据，schame，tablespace，database 的信息，参考：
Note 277650.1 - How to Use Export and Import when Transferring Data Across Platforms or Across 32-bit and 64-bit Servers
当迁移或者升级数据库的时候，请确保您已经参阅了本文底部的参考文档。
重要提醒！
在您对生产/开发数据库做全库 export/import 之前：
- 对全部步骤至少做一次完整的测试，并且：
- 研究 export/import 过程中抛出的任何错误，修正您的步骤来消除这些错误，并且：
- 了解为什么还有其他错误和警告信息报出来并且知道还需要哪些额外的操作来解决它们
如果需要测试，建议采用最近克隆的源生产库。
第1部分 – 准备目标数据库
1.1. 如果在目标主机上已经安装了其他 oracle 数据库，对这些数据库发起一次 clean shutdown（SHUTDOWN IMMEDIATE），然后对所有这些数据库创建一个全库备份。这样可以确保您在全库导入时遇到任何错误（比如导入的时候搞错了ORACLE_SID），都能从这个备份中恢复数据库。
1.2. 在目标主机上，安装 oracle 软件。确保源主机和目标主机具有相同的 Edition，比如: 如果您的源主机上的是 Oracle RDBMS Enterprise Edition，那么在目标主机上也应当安装 Enterprise Edition。
如果您的源主机安装的是 Oracle RDBMS Standard Edition，那么目标主机安装 Standard 或者 Enterprise Edition 都可以。
SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
关于各 edition 之间的详细差异，参考：
Note 112591.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 8.1
Note 269040.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 9.2
Note 271886.1 - Differences Between Different Editions of Oracle Database 10G Release 1
Note 465465.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2
Note 1084132.1 - Differences Between Enterprise, Standard and Standard One Editions on Oracle 11.2
Note 1628809.1 - Differences Between Enterprise, Standard and Standard One Editions on Oracle 12.1
如果您的源库是 Oracle RDBMS Enterprise Edition 而目标库是 Standard Edition，那么某些在 Enterprise Edition 包含而在 Standard Edition 不包含的特性会在 import 的时候抛出一些错误，这是正常行为，比如以下文档：
Note 1087325.1 - Error ORA-439 When Importing Tables Created With Enabled Deferred Segment Into Oracle 11g Standard Edition
1.3. 在作全库 import 之前，为了消除一些已知而且已经有修复的问题，推荐对 $ORACLE_HOME 打上最新的 patchset，关于最新的 Oracle Server release 的清单，参考：
Note 161818.1 - Oracle Server (RDBMS) Releases Support Status Summary
Note 756671.1 - Oracle Recommended Patches -- Oracle Database
Note 454507.1 - ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts
Note 880782.1 - ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts
Note 1565065.1 - ALERT: Oracle 12c Release 1 (12.1) Support Status and Alerts
第二部分 - 准备源数据库
2.1. 在作全库 export 之前，为了消除一些已知而且已经有修复的问题，推荐对 $ORACLE_HOME 打上最新的 patchset，关于最新的 Oracle Server release 的清单，参考：
Note 161818.1 - Oracle Server (RDBMS) Releases Support Status Summary
Note 756671.1 - Oracle Recommended Patches -- Oracle Database
Note 189908.1 - ALERT: Oracle9i Release 2 (9.2) Support Status and Alerts
Note 263719.1 - ALERT: Oracle 10g Release 1 (10.1) Support Status and Alerts
Note 316900.1 - ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
Note 454507.1 - ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts
Note 880782.1 - ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts
Note 1565065.1 - ALERT: Oracle 12c Release 1 (12.1) Support Status and Alerts
2.2. 对于全库 export/import 来说，我们不会重建目标库的数据字典对象，目标库的数据字典是在目标库创建的时候产生的，并且在 import 的时候已经存在了。这样的情况同样适用于其他 component 和 schema 的数据字典。在源库上，检查哪些 component 安装了。为了找出哪些 component 在使用，可能的方法有：
- 采用如下的查询（针对 Oracle9i 9.2.0. 和以上版本）：
CONNECT / as sysdba
SET lines 90 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12
SELECT comp_id,schema,status,version,comp_name
FROM dba_registry
ORDER BY 1;
COMP_ID SCHEMA STATUS VERSION COMP_NAME
-------- ------------ ------------ ------------ -----------------------------------
AMD OLAPSYS VALID 11.2.0.4.0 OLAP Catalog
APEX APEX_030200 VALID 3.2.1.00.12 Oracle Application Express
APS SYS VALID 11.2.0.4.0 OLAP Analytic Workspace
CATALOG SYS VALID 11.2.0.4.0 Oracle Database Catalog Views
CATJAVA SYS VALID 11.2.0.4.0 Oracle Database Java Packages
CATPROC SYS VALID 11.2.0.4.0 Oracle Database Packages and Types
CONTEXT CTXSYS VALID 11.2.0.4.0 Oracle Text
EM SYSMAN VALID 11.2.0.4.0 Oracle Enterprise Manager
EXF EXFSYS VALID 11.2.0.4.0 Oracle Expression Filter
JAVAVM SYS VALID 11.2.0.4.0 JServer JAVA Virtual Machine
ORDIM ORDSYS VALID 11.2.0.4.0 Oracle Multimedia
OWB OWBSYS VALID 11.2.0.4.0 OWB
OWM WMSYS VALID 11.2.0.4.0 Oracle Workspace Manager
RUL EXFSYS VALID 11.2.0.4.0 Oracle Rules Manager
SDO MDSYS VALID 11.2.0.4.0 Spatial
XDB XDB VALID 11.2.0.4.0 Oracle XML Database
XML SYS VALID 11.2.0.4.0 Oracle XDK
XOQ SYS VALID 11.2.0.4.0 Oracle OLAP API - 采用 Database Configuration Assistant（选择 modify database）
- 查询 DBA_OBJECTS：
CONNECT / as sysdba
SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12
COL owner FORMAT a25
SELECT owner, count(*)
FROM dba_objects
WHERE owner IN ('CTXSYS', 'OLAPSYS', 'MDSYS', 'DMSYS', 'WKSYS', 'LBACSYS',
'ORDSYS', 'XDB', 'EXFSYS', 'OWBSYS', 'WMSYS', 'SYSMAN')
OR owner LIKE 'APEX%'
GROUP BY owner
ORDER BY 1;
OWNER COUNT(*)
------------------------- ------------
APEX_030200 2561
CTXSYS 389
EXFSYS 312
MDSYS 2011
OLAPSYS 721
ORDSYS 2513
OWBSYS 2
SYSMAN 3554
WMSYS 333
XDB 1170
SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE object_type LIKE 'JAVA%'
GROUP BY owner, object_type
ORDER BY 1,2;
OWNER OBJECT_TYPE COUNT(*)
------------------------- ------------------- ------------
EXFSYS JAVA CLASS 47
EXFSYS JAVA RESOURCE 1
MDSYS JAVA CLASS 544
MDSYS JAVA RESOURCE 3
ORDSYS JAVA CLASS 1877
ORDSYS JAVA RESOURCE 72
SYS JAVA CLASS 26500
SYS JAVA DATA 323
SYS JAVA RESOURCE 864
SYS JAVA SOURCE 2
其他可能的数据字典对象：
- Oracle Data Mining - schema: DMSYS
- Oracle Ultra Search - schema: WKSYS
- Oracle Label Security - schema: LBACSYS
- Oracle Warehouse Builder - schema: OWBSYS
对于不同数据库的 component 和 schema 的详情，也可以参考：
Note 472937.1 - Information On Installed Database Components and Schemas
2.3. 在源库上检查数据库的字符集：
SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL value FORMAT a15
SELECT * FROM nls_database_parameters
WHERE parameter LIKE '%SET'
ORDER BY 1;
PARAMETER VALUE
------------------------------ ---------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
2.4. 在源库上，创建一个 spool out 的文件来检查 redo logfile 的详情：
SET lines 140 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL member FORMAT a100
SPOOL redo.out
SELECT group#,bytes,blocksize,members,status
FROM v$log
ORDER BY 1;
SELECT * FROM v$logfile
ORDER BY 1,3;
SPOOL off
2.5. 在源库上，创建一个包含 tablespace/datafile 的清单，和 tablespace 的 DDL 的 spool out 的文件，比如：
SET lines 170 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL file_name FORMAT a100
SPOOL tbsp.out
SELECT tablespace_name, bytes, status, online_status, file_name
FROM dba_data_files
ORDER BY 1,5;
SET lines 100
COL ddl FORMAT a100
SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') "DDL" FROM dual;
-- do this for all tablespaces
SPOOL off
2.6. 在源库上，检查哪些用户可以通过 as sysdba 的方式连接：
SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL file_name FORMAT a100
SELECT * FROM v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
2.7. 在源库的 $ORACLE_HOME/network/admin 下打开 TNSNAMES.ORA 文件，记录（或者复制）那些用于源库本地访问或者用于源库连接到远程数据库 db-link 的 TNS 记录。
2.8. 如果是 Database Vault 环境，将必要的权限授予给实施导出的用户，详情参考：
Note 822048.1 - How To Export / Import Objects In Database Vault Environment
2.9. 如果数据库安装了 Enterprise Manager (EM) 组件(schema SYSMAN - 参见本文上面的步骤 2.2)，那么还需要额外的步骤。详情参考：
Note 1302281.1 - 11g Grid Control: Steps for Migrating the 11g Grid Control Repository from One Database to Another
第三部分 - 创建目标数据库
3.1. 在目标主机，决定目标库的字符集。如果数据库字符集不需要改变，那么采用与源库相同的字符集。参考本文的2.3步骤和如下文档：
Note 77441.1 - Steps to Create a New Database With a Character Set Other Than US7ASCII
如果您需要从单字符集（比如 WE8ISO88859P15）的源库转换到变宽多字节字符集（比如 AL32UTF8）的目标库，那么需要在源库运行字符集扫描工具来检查一些转换过程中可能发生的问题。详情参考：
Note 745809.1 - Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note 444701.1 - Csscan Output Explained
Note 1297961.1 - ORA-01401 / ORA-12899 / ORA-01461 While Importing Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database.
3.2. 对于 Oracle9i 或者更高版本的数据库，需要确定 undo 管理方式：手动或者自动。对于 Oracle9i 或者更高版本，推荐采用 Automatic Undo Managment。参考：
Note 135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
3.3. 对于 Oracle8i 或者更高版本的数据库，需要确定 tablespace 的 space management 管理方式。对于 Oracle9i 或者更高版本，推荐采用 locally managed tablespaces。参考：
Note 105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
3.4. 在目标主机上，创建一个新的包含启动参数的 parameter file。对于 Oracle9i 或者更高版本，需要确定初始化参数存放的方式是旧式的 pfile(init.ora) 或者采用新的 server parameter file(spfile-推荐)。详情参考：
Note 249664.1 - Pfile vs SPfile
3.5. 在目标主机上，通过 Database Configuration Assistant 来创建目标数据库。您可以通过 Database Configuration Assistant(dbca) 选择需要安装哪些数据库 option。选择与源库相同的 component，除非您绝对确认源库的那些component 虽然安装了但是从来没有被使用过（参考本文上面的步骤2.2）。当降级迁移的时候，注意某些在源库的特性和组件可能在低版本的目标数据库不存在或者不兼容。
对于 redo log 文件来说，参见上面的步骤2.4。也可以通过一个已经存在的脚本或者您自己写的脚本来创建数据库。对于这样的情况，确保这个脚本调用了用于添加目标库所需的 option 和 component 的所有其他脚本。
3.5. 当使用老的（Oracle9i 之前）manual undo management 管理方式时，需要在 SYSTEM 表空间创建一个额外的 rollback segment 并且将其 online。详情请参考：
Note 112479.1 - ORA-01552 Error Creating a Rollback Segment in a Locally-Managed Tablespace
3.6. 检查目标库中是否所有的对象都是 valid 的：
SET lines 100 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL status FORMAT a9
COL object_type FORMAT a20;
COL owner.object FORMAT a50
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
ORDER BY 4,2;
如果有 invalid 的对象，编译它们。详情请参考：
Note 213600.1 - How to Compile Invalid Objects in SYS Schema after RDBMS 8.0
3.7. 对于 Oracle9i Release 2 (9.2.0) 和更高的版本，检查数据字典的状态：
SET lines 90 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12
SELECT comp_id,schema,status,version,comp_name
FROM dba_registry
ORDER BY 1;
确保数据字典组件比如 CATALOG 和 CATPRO 具有与 Oracle 可执行文件相同的版本：
SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SELECT * FROM v$version;
3.8. 如果目标库用于组成表空间的数据文件的目录结构与源库不同，那么需要提前在目标数据库创建所有的的表空间（除了 SYSTEM，SYSAUX，UNDO 外）。对于原始 DDL，参见上面的步骤2.5。
3.9. 如果数据库是 Database Vault 的环境，那么对用于执行 import 的用户授予必要的权限，详情参见：
Note 822048.1 - How To Export / Import Objects In Database Vault Environment
3.10. 如果您的源库安装有 Enterprise Manager (EM) (schema SYSMAN - 参考上面的2.2步骤)，那么目标库还需要额外的准备步骤，详情请参考：
Note 1302281.1 - 11g Grid Control: Steps for Migrating the 11g Grid Control Repository from One Database to Another
3.11. 对目标库做一次 clean shutdown，然后通过 RMAN 或者操作系统（datafiles，controlfiles，redolog files）做一次 full backup。如果稍后您需要重新运行 import 的时候，您可以用这个备份来恢复数据库。
SHUTDOWN immediate
-- create a full backup of the complete database
STARTUP
3.12. 检查 $ORACLE_HOME/network/admin 下的 TNSNAMES.ORA 文件，确保用于本地访问和用于从新库上访问其他远程 db-link 的那些 tns alias 已经添加（参见上面的2.7步骤）。
第四部分 - 从源库执行导出
4.1. 对源库做一次 clean shutdown，然后通过 RMAN 或者操作系统（datafiles，controlfiles，redolog files）做一次 full backup。如果稍后您需要重新运行 export 的时候，您可以用这个备份来恢复数据库。
SHUTDOWN immediate
-- create a full backup of the complete database
STARTUP
4.2. 停止源库上的监听，确保没有任何用户和应用能连接到该数据库。
4.3. 创建一个 spool out 文件存放源库中的各 schema 的对象清单。例如：
SET lines 100 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL status FORMAT a9
COL object_type FORMAT a20;
COL owner.object FORMAT a50
SPOOL obj_source.out
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
ORDER BY 4,2;
COL owner FORMAT a30
SELECT owner, object_type, status, count(*)
FROM dba_objects
WHERE object_name NOT LIKE 'BIN$%'
GROUP BY owner, object_type, status
ORDER BY 1,2,3;
SPOOL off
4.4. Owner 为 SYS 的对象不会被导出。创建一个 spool out 文件来存放 Owner 为 SYS 的 trigger 清单，例如：
SET lines 180 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL triggering_event FORMAT a35
SPOOL trig.out
SELECT owner, trigger_name, trigger_type, triggering_event, table_owner, base_object_type, status
FROM dba_triggers
WHERE owner='SYS'
ORDER BY 2;
SET lines 100
COL ddl FORMAT a100
SELECT dbms_metadata.get_ddl('TRIGGER','LOGMNRGGC_TRIGGER','SYS') "DDL" FROM dual;
-- do this for all manually created triggers in the SYS schema
SPOOL off
4.5. Owner 为 SYS 的对象的 grants 不会被导出，创建一个 spool out 文件来存放 SYS 对象被授予各用户的 grants 的清单，例如：
SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL grants FOR a80
SPOOL sysgrants.out
-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- Add below the users and/or roles as appropriate for GRANTEE
-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT 'GRANT ' || privilege || ' ON ' || table_name ||
' TO ' || grantee || ';' "GRANTS"
FROM dba_tab_privs
WHERE owner = 'SYS' AND privilege NOT IN ('READ', 'WRITE')
AND grantee IN ('TC')
ORDER BY 1;
SPOOL OFF
另参见：
Note 1911151.1 - Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database
4.6. 检查 export/import 的兼容性，和 export/import data pump 的兼容性：
- 升级：
- 源库为 Oracle9i 或者更低版本：
- 用原始的 exp 客户端做全库导出。
- 用与源库匹配的 exp 客户端版本。
- 源库为 Oracle 10g 或者更高版本：
- 用 data pump expdp 工具执行全库导出。
- 用任意版本的 data pump expdp（推荐使用与源库版本匹配的 data pump client）。
- 源库为 Oracle9i 或者更低版本：
- 降级：
- 目标库为 Oracle9i 或者更低版本：
- 用原始的 exp 客户端做全库导出。
- 用与目标库匹配的 exp 客户端版本。
- 目标库为 Oracle 10g 或者更高版本：
- 用 data pump expdp 工具执行全库导出。
- 用任意版本的 data pump expdp（推荐使用与源库版本匹配的 data pump client）并且指定 data pump expdp 的参数 VERSION=<value>
- 目标库为 Oracle9i 或者更低版本：
参考：
Note 132904.1 - Compatibility Matrix for Export And Import Between Different Oracle Versions [Video]
Note 345187.1 - Feature Obsolescence - Original Export 10.2
Note 553337.1 - Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video]
例如对于原始的 exp 客户端（在 10.2 后 de-support 了）：
或者 Data Pump (10.1 及以上)：
$ sqplus /nolog
CONNECT / as sysdba
CREATE OR REPLACE DIRECTORY my_dir AS '/u01/expdp';
GRANT read, write ON DIRECTORY my_dir TO system;
接下来：
检查 export 的 log 文件看是否有错误发生。
第五部分 - 向目标库导入
如同步骤2.2中的解释，对于全库 export/import 来说，我们不会重建目标库的数据字典对象。这些 shcema 和他们（默认）的对象已经在目标库创建的时候就存在了。这种情况同样适用于其他字典组件 schema。如果源库的 SYS 和 SYSTEM 对象被人为增加了或者修改了，那么依赖他们的一些对象可能会变成 invalid 的。因此，请一定要检查源库的相关对象。
5.1. 以 binary 模式复制导出的 dump 文件到目标库主机
5.2. （如果4.6采用了 exp）那么以原始的 imp 客户端执行全库导入，（如果4.6采用了 expdp）那么通过 data pump 工具执行。总是采用与目标库版本匹配的 import 客户端。
例如原始的 imp 客户端：
或者 DataPump：
$ sqplus /nolog
CONNECT / as sysdba
CREATE OR REPLACE DIRECTORY my_dir AS '/u01/expdp';
GRANT read, write ON DIRECTORY my_dir TO system;
接着：
注意对于 imp 我们推荐采用默认的 default IGNORE=N，而对于 impdp 推荐采用 TABLE_EXIST_ACTION=skip
5.3. 检测 import 的日志查看是否有错误。比较4.6步骤的 export log 以及5.2步骤的 import log
5.4. 在目标库中创建源库中的那些自定义的 trigger。详情参见上面的4.4步骤。
5.5. 在目标库中创建源库那些被 grant 到其他用户的 SYS 对象的权限。详情参见上面的4.5步骤。
5.6. 在目标库创建一个 spool 文件存放各 schema 下的对象清单。例如：
SET lines 100 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL status FORMAT a9
COL object_type FORMAT a20;
COL owner.object FORMAT a50
SPOOL obj_target.out
COL owner FORMAT a30
SELECT owner, object_type, status, count(*)
FROM dba_objects
WHERE object_name NOT LIKE 'BIN$%'
GROUP BY owner, object_type, status
ORDER BY 1,2,3;
SPOOL off
用这个清单同4.3步骤的清单作比较。
找出清单不一致的原因并且解决它们。
5.7. 在目标库重新编译 invalid 的对象，并且检查是否还有剩余的 invalid 的对象：
SET lines 100 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL status FORMAT a9
COL object_type FORMAT a20;
COL owner.object FORMAT a50
-- recompile all invalid objects:
@?\rdbms\admin\utlrp.sql
@?\rdbms\admin\utlrp.sql
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%'
ORDER BY 4,2;
手工编译在源库 valid（参见上面的4.3步骤）而在目标库 invalid 的对象。
如果对象仍然 invalid，找出原因并解决它们。
5.8. 如果从一个低版本的数据库迁移到目标库（比如从 9.2.0.8 到 11.2.0.3），无论采用的是 data pump 还是传统的 exp/imp，还需要执行一些 post-import 步骤，这些步骤在如下文档中概括了：
- Oracle Database Upgrade Guide, 10g Release 2 (10.2) , Chapter 4: After Upgrading a Database
- Oracle Database Upgrade Guide, 11g Release 1 (11.1) , Chapter 4: After Upgrading to the New Release
- Oracle Database Upgrade Guide, 11g Release 2 (11.2) , Chapter 4: Post-Upgrade Tasks for Oracle Database
- Oracle Database Upgrade Guide, 12c Release 1 (12.1) , Chapter 4: Post-Upgrade Tasks for Oracle Database
5.9. 检查用户和应用能否连接到目标库。
第六部分 - 完成源库中剩下的操作
6.1. Shutdown 旧的源库
SHUTDOWN IMMEDIATE
第七部分 - 完成目标库中剩下的操作
7.1. 对目标库做一次 clean shutdown，然后通过 RMAN 或者操作系统（datafiles，controlfiles，redolog files）对数据库做一次全备份
SHUTDOWN immediate
-- 对全库做 full backup
STARTUP
7.2. 让新的目标库可用。
COL owner FORMAT a30
SELECT owner, object_type, status, count(*)
FROM dba_objects
WHERE object_name NOT LIKE 'BIN$%'
GROUP BY owner, object_type, status
ORDER BY 1,2,3;
参考
NOTE:316900.1 - ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
NOTE:1628809.1 - Differences Between Enterprise, Standard and Standard One Editions on Oracle 12.1
NOTE:189908.1 - ALERT: Oracle9i Release 2 (9.2) Support Status and Alerts
NOTE:1302281.1 - EM 11g: How to Migrate the Enterprise Manager 11g Grid Control Repository from One Database to Another
NOTE:756671.1 - Master Note for Database Proactive Patch Program
NOTE:1613716.1 - Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 11.2
NOTE:822048.1 - How To Export / Import Objects In Database Vault Environment
NOTE:204015.1 - Export/Import Process for Oracle Applications Release 11i Database Instances
NOTE:228516.1 - How to copy (export/import) Portal database schemas of IAS 9.0.2 to another database
NOTE:132904.1 - Compatibility Matrix for Export And Import Between Different Oracle Versions [Video]
NOTE:331221.1 - 10g Export/Import Process for Oracle Applications Release 11i
NOTE:105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
NOTE:1087325.1 - Error ORA-439 When Importing Tables Created With Enabled Deferred Segment Into Oracle 11g Standard Edition
NOTE:741818.1 - Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2
NOTE:135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
NOTE:249664.1 - Pfile vs SPfile
NOTE:269040.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 9.2
NOTE:271886.1 - Differences Between Different Editions of Oracle Database 10G Release 1
NOTE:557738.1 - Export/import notes on Applications 11i Database 11g
NOTE:454507.1 - ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts
NOTE:880782.1 - ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts
NOTE:213600.1 - How to Compile Invalid Objects in SYS Schema after RDBMS 8.0
NOTE:77441.1 - Steps to Create a New Database With a Character Set Other Than US7ASCII
NOTE:277650.1 - How To Use Export And Import When Transferring Data Across Platforms Or Across 32-bit And 64-bit Servers
NOTE:1911151.1 - Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database
NOTE:1565065.1 - ALERT: Oracle 12c Release 1 (12.1) Support Status and Alerts
NOTE:745809.1 - Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
NOTE:230627.1 - 9i Export/Import Process for Oracle Applications Release 11i
NOTE:345187.1 - Feature Obsolescence - Original Export 10.2
NOTE:362205.1 - 10g Release 2 Export/Import Process for Oracle Applications Release 11i
NOTE:1585257.1 - Export/Import Process for EBS 11i on RDBMS 12c
NOTE:444701.1 - Csscan Output Explained
NOTE:1084132.1 - Differences Between Enterprise, Standard and Standard One Editions on Oracle 11.2
NOTE:1297961.1 - ORA-01401 / ORA-12899 / ORA-01461 While Importing Or Loading Data In An AL32UTF8 / UTF8 (Unicode) Or Other Multibyte NLS_CHARACTERSET Database.
NOTE:112479.1 - ORA-01552 Error Creating a Rollback Segment in a Locally-Managed Tablespace
NOTE:161818.1 - Oracle Database (RDBMS) Releases Support Status Summary
NOTE:465460.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 11.1
NOTE:159657.1 - Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)
NOTE:263719.1 - ALERT: Oracle 10g Release 1 (10.1) Support Status and Alerts
NOTE:112591.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 8.1
NOTE:133920.1 - Complete Upgrade Checklist for Manual Upgrades from 8.x to 8.x
NOTE:454616.1 - Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
NOTE:1439066.1 - Exporting/Importing in Oracle Portal 11g
NOTE:472937.1 - Information On Installed Database Components and Schemas
NOTE:553337.1 - Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions
NOTE:465465.1 - Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub、博客园、CSDN和个人微 信公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 （满） 、618766405 ● 微 信群：可加我微 信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成 ● 最新修改时间：2019-07-01 06:00 ~ 2019-07-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客户端 扫描下面的二维码来关注小麦苗的微 信公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。 ........................................................................................................................ |
 |  |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2652256/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2652256/
1766
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
