Oracle 单实例 从32位 迁移到 64位 方法（三）-- 使用导出导入 说明

Oracle 单实例 从32位 迁移到 64位 方法（三）-- 使用导出导入 说明

分类： Oracle 高级知识 2012-02-15 18:25 103人阅读 评论(0) 举报

 

关于将单实例从32位迁移到64位，之前整理了几篇Blog：

OracleConverta 32-bit Database to 64-bit Database（32位 转到 64位）说明

http://blog.csdn.net/tianlesoftware/article/details/7252742

 

Oracle 单实例 从32位 迁移到 64位 方法（一）--直接copydatafiles

http://blog.csdn.net/tianlesoftware/article/details/7258654

 

Oracle 单实例 从32位 迁移到 64位 方法（二）--使用 RMANRestore 示例

http://blog.csdn.net/tianlesoftware/article/details/7261970

 

直接copy datafiles 或者使用RMAN 都会有一个问题，就是需要重建 javashared data objects (SRO) ，而在默认安装的情况下，还会安装OLAP 组件，在迁移时还需要重建OLAP组件，所以给迁移增加了很多的时间。

 

使用导出导入就没有这方面的问题，但是对于数据量比较大的情况，导出导入也需要时间，虽然使用数据泵（expdp/impdp）效率要比exp/imp 要高，但是这个时间也是比较长的。

 

之前整理的有关导出导入的一些Blog，链接如下：

ORACLEEXP/IMP 说明

http://blog.csdn.net/tianlesoftware/article/details/4718366

 

Oracle 10gData Pump Expdp/Impdp 详解

http://blog.csdn.net/tianlesoftware/article/details/4674224

 

Oracleexpdp/impdp 使用示例

http://blog.csdn.net/tianlesoftware/article/details/6260138

 

exp/imp 与expdp/impdp 对比 及使用中的一些优化事项

http://blog.csdn.net/tianlesoftware/article/details/6093973

 

使用导出导入可以解决2个问题：

1.     跨操作系统版本

2.     跨数据库版本

 

所以，在某种程度上讲，这种方法最灵活。

 

关于Export/Import 的兼容性问题参考：

Export/Import DataPump Parameter VERSION -Compatibility of Data Pump Between Different Oracle Versions [Video] [ID 553337.1]

 

这篇不做测试，因为Export/Import 操作比较简单，这里引用MOS 文档上的几个案例，供参考：

How to Use Export and Import whenTransferring Data Across Platforms or Across 32-bit and 64-bit Servers [ID277650.1]

 

Scenario 1: Transfer of a table. 

If one or moretables need to be transferred between databases across platforms, then you canuse the TABLE level export mode. 

Example: 
- source database is 64-bit 8.1.7.4 database on 64-bit Sun Solaris 9 
- target database is 64-bit 10.1.0.5 database on 64-bit Linux Red HatEnterprise Server v3 

- tables scott.emp and hugo.dept need to betransferred 

a. On Sun Solaris, export with the 8.1.7.4 export utility:

> exp system/manager FILE=exp_tabs.dmp LOG=exp_tabs.log \  
RECORDLENGTH=65535 TABLES=scott.emp,hugo.dept

b. Transfer the file in binary mode to the Linux Red HatEnterprise Server. 

c. On Linux Red Hat, import with the 10.1.0.5 import utility:

> imp system/manager FILE=exp_tabs.dmp LOG=imp_tabs.log \  
RECORDLENGTH=65535 FROMUSER=scott,hugo TOUSER=scott,hugo

Notes: 
1. The export and import parameter RECORDLENGTH was used with the maximum value(64 kb). 
2. The users SCOTT and HUGO must exist in the target database prior to theimport operation; otherwise an error is returned. 


Scenario 2: Transfer of a schema. If one or more schema's need to be transferred between databases acrossplatforms, you can use the USER (owner) level export mode. 

Example: 
- source database is 32-bit 9.0.1.4 database on Microsoft Windows 2000 
- target database is 64-bit 9.2.0.8 database on HP-Unix 11i 
- schema's scott and hugo need to be transferred 

a. On Windows 2000, export with the 9.0.1.4 export utility:

-- Windows: type all parameters on one singleline:

D:\> exp system/manager FILE=exp_u.dmp LOG=exp_u.log 
RECORDLENGTH=65535 OWNER=scott,hugo

b. Transfer the file in binary mode to the HP-UX 11iServer. 

c. On HP-Unix, import with the 9.2.0.8 import utility:

> imp system/manager FILE=exp_u.dmp LOG=imp_u.log \  
RECORDLENGTH=65535 FROMUSER=scott,hugo TOUSER=scott,hugo  

Notes: 
1. The user names SCOTT and HUGO must exist in the target database prior to theimport operation; otherwise an error is returned. 
2. In Oracle10g when using export DataPump and import DataPump, you can use theimport DataPump parameter REMAP_SCHEMA which loads all objects from a sourceschema into a target schema, and creates the target schema if it does notexist. 


Scenario 3: Transfer of a tablespace. Beginning with the Oracle10g database, a tablespace can always be transportedto a database with the same or higher compatibility setting, whether the targetdatabase is on the same or a different platform. 

Example: 
- source database is 32-bit 10.1.0.2 database on 32-bit Microsoft Windows2000 
- target database is 64-bit 10.2.0.1 database on AIX 5.2 
- tablespaces USERS and EXAMPLE example need to be transferred 

a. On the Microsoft Windows 2000 platform:

-- check involved datafiles, and check if the transportable set is self 
-- contained: 
SELECT file_name FROM dba_data_files 
 WHERE tablespace_name IN ('USERS','EXAMPLE'); 

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('users,example', TRUE); 

SELECT * FROM transport_set_violations; 

-- make tablespaces read-only, and export the transportable set:  

ALTER TABLESPACE users READ ONLY;  

ALTER TABLESPACE example READ ONLY; 

-- export the transport tablespace set:
-- (Windows: type parameters on one single line)

D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tbsp.dmp 
LOGFILE=exp_tbsp.log TRANSPORT_TABLESPACES=users,example 
TRANSPORT_FULL_CHECK=Y

b. Check platform, and check if the datafiles need to beconverted (difference exists in endian format):

SELECT platform_name FROM v$database;  

SELECT * FROM v$transportable_platform;  

-- Use RMAN if the datafiles need to be converted:   

> rman target /   

RMAN> CONVERT TABLESPACE users,example TO PLATFORM 'AIX-Based Systems   
(64-bit)' FORMAT 'N:\temp\%U';   
RMAN> EXIT;

Otherwise, copy the datafiles to a temporary location,and put the tablespaces back to read write.

c. Transfer the export dumpfile and the copied/converted datafiles in binarymode to the AIX 5.2 Server. 

d. Plug in the tablespaces:

>impdp system/manager DIRECTORY=my_dir2 DUMPFILE=exp_tbsp.dmp \  
LOGFILE=imp_tbsp.log TRANSPORT_DATAFILES=\('/dbdir/users01.dbf', \  
'/dbdir/example01.dbf'\) REMAP_SCHEMA=\(scott:scott2\) \  
TRANSPORT_FULL_CHECK=Y REMAP_SCHEMA=\(hugo:hugo2\) 

e. Change the plugged in tablespaces backto read write. 

Notes: 
1. In this example we assumed that only the users SCOTT and HUGO owned objectsin the tablespaces USERS and EXAMPLE. The users SCOTT2 and HUGO2 become the newowners of the objects. These users should have been created in the targetdatabase prior to the import. 
2. You cannot transport the SYSTEM tablespace. 

--SYSTEM 表空间不能传输
3. You cannot transport objects owned by the user SYS (such as: PL/SQL, Javaclasses, callouts, views, synonyms, users, privileges, dimensions, directories,and sequences). 

--任何SYS 用户的的对象也也不能传输。
4. The source and target database must use the same character set and nationalcharacter set. 
5. In Oracle9i and Oracle8i the source and target database must be on the samehardware platform. 

--这一部分介绍的是使用EXPDP/IMPDP 实现表空间的传输，关于表空间的传输，其实也可以使用RMAN 来实现。 有关这部分问题，近期会整理相关的Blog。

Scenario 4: Transfer of a database. The Export and Import utilities are the only method that Oracle supports formoving an existing Oracle database from one hardware platform to another. Thisincludes moving between UNIX and NT systems. In Oracle10g you can use thetransportable tablespace feature to migrate a database to a different platformby creating a new database on the destination platform and performing atransport of all the user tablespaces. 
Note that you cannot transport the SYSTEM tablespace. Therefore, objects suchas sequences, PL/SQL packages, and other objects that depend on the SYSTEMtablespace are not transported. You must either create these objects manuallyon the destination database, or use Data Pump to transport the objects that arenot moved by transportable tablespace. A full database export and import can beused in all Oracle version to transfer a database across platforms. 

Example: 
- source database is 32-bit 9.2.0.8 database on 32-bit Microsoft Windows2000 
- target database is 64-bit 10.2.0.3 database on 64-bit HP-Unix Itanium11.22 

The following steps provide a general overview of how to move a databasebetween platforms. 

a. Query the views dba_tablespaces, dba_data_files and dba_temp_files. You willneed this information later in the process. 

b. Perform a full export from the source database:

> exp system/manager FULL=y FILE=exp_full.dmp LOG=exp_full.log 

c. Transfer the export dumpfile in binary mode to theHP-Unix 11.22 server. 

d. Create a new database on the target server. 

e. Before importing the dump file, you must first create your tablespaces,using the information obtained in step a (otherwise, the import will create thecorresponding datafiles in the same file structure as at the source database,which may not be compatible with the file structure on the targetsystem). 

f. Perform a full import with the IGNORE parameter enabled:

>imp system/manager FULL=y FILE=exp_full.dmp LOG=imp_full.log IGNORE=y 

Using IGNORE=y instructs Oracle to ignore any creationerrors during the import and permit the import to complete.

--这部分介绍的是全库的导出与导入。

 

更多示例，可以参考：

Oracleexpdp/impdp 使用示例

http://blog.csdn.net/tianlesoftware/article/details/6260138