这次因为要迁移的数据库大小并不大但也不是只有几十G所以选择使用rman将生产数据库从hp-ux迁移到aix上，数据库是10.2.0.4

1.操作环境 1.1 源生产环境 数据库名 CAIWU 实例名 CAIWU DBID 338270341 数据库 HP-UX 10.2.0.4.0 - 64bit Production 数据文件目录 /cwjc_data/CAIWU 文本初始化参数文件:/oracle/product/10.2.0/dbs/init/initCAIWU.ora

1.2 目标生产环境

数据库名 CAIWU

实例名 CAIWU DBID 338270341 数据库 HP-UX 10.2.0.4.0 - 64bit Production 数据文件目录 /yl_oradata/CAIWU 文本初始化参数文件:/oracle/product/10.2.0/db_1/dbs/init/initCAIWU.ora

2 备份

2.1 备份注意事项 1. 迁移库只能在同字节顺序平台之间(即ENDIAN_FORMAT相同)且得是ORACLE所支持的平台，可以在V$TRANSPORTABLE_PLATFORM中查看。 2. 传输前需要将源库置为只读。 3. 生成的2个脚本文件要仔细根据需要修改。

2.2 源库操作

2.2.1 将库只读，并检查是否支持迁移 一．将库只读，并检查是否支持迁移

[rx6600-2:oracle:/cwjc_data]$sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 29 22:34:24 2014Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> shutdown immediateDatabase closed.Database dismounted.SQL> startup mountORACLE instance started.Total System Global Area 3439329280 bytesFixed Size                  2060008 bytesVariable Size            2348810520 bytesDatabase Buffers         1073741824 bytesRedo Buffers               14716928 bytesDatabase mounted.SQL> alter database open read only;Database altered.

2.2.2 检查支持迁移平台

SQL> select * from v$transportable_platform;PLATFORM_ID PLATFORM_NAME                          ENDIAN_FORMAT----------- -------------------------------------- --------------          1 Solaris[tm] OE (32-bit)                Big          2 Solaris[tm] OE (64-bit)                Big          7 Microsoft Windows IA (32-bit)          Little         10 Linux IA (32-bit)                      Little          6 AIX-Based Systems (64-bit)             Big          3 HP-UX (64-bit)                         Big          5 HP Tru64 UNIX                          Little          4 HP-UX IA (64-bit)                      Big         11 Linux IA (64-bit)                      Little         15 HP Open VMS                            Little          8 Microsoft Windows IA (64-bit)          Little          9 IBM zSeries Based Linux                Big         13 Linux x86 64-bit                       Little         16 Apple Mac OS                           Big         12 Microsoft Windows x86 64-bit           Little         17 Solaris Operating System (x86)         Little         18 IBM Power Based Linux                  Big         20 Solaris Operating System (x86-64)      Little         19 HP IA Open VMS                         Little19 rows selected.

2.2.3 利用dbms_tdb 包检查数据库

二．利用dbms_tdb 包检查数据库能否被传输以及列出外部表和DIRECTORY 等无法传输的对象信息， 分别在hp-ux平台数据库通过SQLPLUS执行以下两个存储过程

SQL> set serveroutput onSQL> declare  2   db_ready boolean;  3   begin  4   /* db_ready is ignored, but with SERVEROUTPUT set to ON any  5   * conditions preventing transport will be output to console */  6   db_ready := dbms_tdb.check_db('AIX-Based Systems (64-bit)',  7   dbms_tdb.skip_none);  8   end;  9   /PL/SQL procedure successfully completed.SQL>  declare  2   external boolean;  3   begin  4   /* value of external is ignored, but with SERVEROUTPUT set to ON  5   * dbms_tdb.check_external displays report of external objects  6   * on console */  7   external := dbms_tdb.check_external;  8  end;  9  /The following directories exist in the database:SYS.DUMP_CAIWU, SYS.DATA_PUMP_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR, SYS.ORACLE_OCM_CONFIG_DIRPL/SQL procedure successfully completed.

2.2.4 RMAN ConvertDB

连接target database 查看数据库打开模式

[rx6600-2:oracle:/home/oracle]$sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 29 22:46:30 2014Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select open_mode from v$database;OPEN_MODE----------READ ONLY

利用RMAN转换数据库

RMAN> convert database new database 'CAIWU'2> transport script '/cwjc_data/CAIWURMAN/caiwu.sql'3> to platform 'AIX-Based Systems (64-bit)'4> db_file_name_convert '/cwjc_data/CAIWU/','/cwjc_data/CAIWURMAN/';Starting convert at 29-SEP-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=539 devtype=DISKDirectory SYS.DUMP_CAIWU found in the databaseDirectory SYS.DATA_PUMP_DIR found in the databaseDirectory SYS.WORK_DIR found in the databaseDirectory SYS.ADMIN_DIR found in the databaseDirectory SYS.ORACLE_OCM_CONFIG_DIR found in the databaseUser SYS with SYSDBA and SYSOPER privilege found in password filechannel ORA_DISK_1: starting datafile conversioninput datafile fno=00005 name=/cwjc_data/CAIWU/zwdata01.dbfconverted datafile=/cwjc_data/CAIWURMAN/zwdata01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:06:25channel ORA_DISK_1: starting datafile conversioninput datafile fno=00006 name=/cwjc_data/CAIWU/zwdata02.dbfconverted datafile=/cwjc_data/CAIWURMAN/zwdata02.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:06:25channel ORA_DISK_1: starting datafile conversioninput datafile fno=00001 name=/cwjc_data/CAIWU/system01.dbfconverted datafile=/cwjc_data/CAIWURMAN/system01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:25channel ORA_DISK_1: starting datafile conversioninput datafile fno=00002 name=/cwjc_data/CAIWU/undotbs01.dbfconverted datafile=/cwjc_data/CAIWURMAN/undotbs01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:45channel ORA_DISK_1: starting datafile conversioninput datafile fno=00003 name=/cwjc_data/CAIWU/sysaux01.dbfconverted datafile=/cwjc_data/CAIWURMAN/sysaux01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:45channel ORA_DISK_1: starting datafile conversioninput datafile fno=00004 name=/cwjc_data/CAIWU/users01.dbfconverted datafile=/cwjc_data/CAIWURMAN/users01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55Run SQL script /cwjc_data/CAIWURMAN/caiwu.sql on the target platform to create databaseEdit init.ora file /oracle/product/10.2.0/dbs/init_00pjoiqb_1_0.ora. This PFILE will be used to create the database on the target platformTo recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platformTo change the internal database identifier, use DBNEWID UtilityFinished backup at 29-SEP-14

将转换后的文件传输到目标主机上

[rx6600-2:oracle:/cwjc_data/CAIWURMAN]$ls -lrttotal 53002438-rw-r-----   1 oracle     dba        8589942784 Sep 29 22:57 zwdata01.dbf-rw-r-----   1 oracle     dba        8589942784 Sep 29 23:03 zwdata02.dbf-rw-r-----   1 oracle     dba        4588576768 Sep 29 23:07 system01.dbf-rw-r-----   1 oracle     dba        2147491840 Sep 29 23:08 undotbs01.dbf-rw-r-----   1 oracle     dba        2147491840 Sep 29 23:10 sysaux01.dbf-rw-r-----   1 oracle     dba        1073750016 Sep 29 23:11 users01.dbf-rw-r--r--   1 oracle     dba           2704 Sep 29 23:11 caiwu.sqlftp> get zwdata01.dbf200 PORT command successful.150 Opening BINARY mode data connection for zwdata01.dbf (8589942784 bytes).226 Transfer complete.8589942784 bytes received in 197.5 seconds (4.248e+04 Kbytes/s)local: zwdata01.dbf remote: zwdata01.dbfftp> get zwdata02.dbf200 PORT command successful.150 Opening BINARY mode data connection for zwdata02.dbf (8589942784 bytes).226 Transfer complete.8589942784 bytes received in 202.7 seconds (4.138e+04 Kbytes/s)local: zwdata02.dbf remote: zwdata02.dbfftp> get system01.dbf200 PORT command successful.150 Opening BINARY mode data connection for system01.dbf (4588576768 bytes).226 Transfer complete.4588576768 bytes received in 111.3 seconds (4.027e+04 Kbytes/s)local: system01.dbf remote: system01.dbfftp> get undotbs01.dbf200 PORT command successful.150 Opening BINARY mode data connection for undotbs01.dbf (2147491840 bytes).226 Transfer complete.2147491840 bytes received in 52.55 seconds (3.99e+04 Kbytes/s)local: undotbs01.dbf remote: undotbs01.dbfftp> get sysaux01.dbf200 PORT command successful.150 Opening BINARY mode data connection for sysaux01.dbf (2147491840 bytes).226 Transfer complete.2147491840 bytes received in 51.29 seconds (4.089e+04 Kbytes/s)local: sysaux01.dbf remote: sysaux01.dbfftp> get users01.dbf200 PORT command successful.150 Opening BINARY mode data connection for users01.dbf (1073750016 bytes).226 Transfer complete.1073750016 bytes received in 26.01 seconds (4.032e+04 Kbytes/s)local: users01.dbf remote: users01.dbfftp> get caiwu.sql200 PORT command successful.150 Opening BINARY mode data connection for caiwu.sql (2704 bytes).226 Transfer complete.2704 bytes received in 9e-05 seconds (2.934e+04 Kbytes/s)local: caiwu.sql remote: caiwu.sqlftp> get init_00pjoiqb_1_0.ora200 PORT command successful.150 Opening BINARY mode data connection for init_00pjoiqb_1_0.ora (1556 bytes).226 Transfer complete.1556 bytes received in 8.7e-05 seconds (1.747e+04 Kbytes/s)local: init_00pjoiqb_1_0.ora remote: init_00pjoiqb_1_0.ora[IBMP740-2:oracle:/yl_oradata/CAIWU]$ls -lrttotal 53002408-rw-r--r--    1 oracle   dba      8589942784 Sep 29 23:02 zwdata01.dbf-rw-r--r--    1 oracle   dba      8589942784 Sep 29 23:06 zwdata02.dbf-rw-r--r--    1 oracle   dba      4588576768 Sep 29 23:08 system01.dbf-rw-r--r--    1 oracle   dba      2147491840 Sep 29 23:09 undotbs01.dbf-rw-r--r--    1 oracle   dba      2147491840 Sep 29 23:11 sysaux01.dbf-rw-r--r--    1 oracle   dba      1073750016 Sep 29 23:11 users01.dbf-rw-r--r--    1 oracle   dba            2704 Sep 29 23:12 caiwu.sql-rw-r--r--    1 oracle   dba            1556 Sep 29 23:12 init_00pjoiqb_1_0.ora

将init_00pjoiqb_1_0.ora参数文件复制到$ORACLE_HOME/dbs目录下

[IBMP740-2:oracle:/yl_oradata/CAIWU]$ cp init_00pjoiqb_1_0.ora $ORACLE_HOME/dbs

2.2.5 在目标生产机器上修改pfile参数文件并将参数文件init_00pjoiqb_1_0.ora重命名为initCAIWU.ora

IBMP740-2:oracle:/oracle/product/10.2.0/db_1/dbs]$mv init_00pjoiqb_1_0.ora initCAIWU.ora

修改前参数文件内容如下:

[IBMP740-2:oracle:/oracle/product/10.2.0/db_1/dbs]$vi initCAIWU.ora"initCAIWU.ora" 68 lines, 1556 characters   __shared_pool_size       = 2164260864  __large_pool_size        = 16777216  __java_pool_size         = 16777216  __streams_pool_size      = 150994944  __db_cache_size          = 1073741824# Please change the values of the following parameters:  control_files            ="/yl_oradata/CAIWU/control01.ctl","/yl_oradata/CAIWU/control02.ctl","/yl_oradata/CAIWU/control03.ctl"  db_recovery_file_dest    = "/oracle/flash_recovery_area"  db_recovery_file_dest_size= 2147483648  background_dump_dest     = "/oracle/admin/CAIWU/bdump"  user_dump_dest           = "/oracle/admin/CAIWU/udump"  core_dump_dest           = "/oracle/admin/CAIWU/cdump"  audit_file_dest          = "/oracle/admin/CAIWU/adump"  db_name                  = "CAIWU"# Please review the values of the following parameters:  remote_login_passwordfile= "EXCLUSIVE"  db_domain                = ""  dispatchers              = "(PROTOCOL=TCP) (SERVICE=CAIWUXDB)"# The values of the following parameters are from source database:  processes                = 500  sessions                 = 555  sga_target               = 3439329280  db_block_size            = 8192  compatible               = "10.2.0.3.0"# log_archive_dest_1       = "LOCATION=/yl_oradata/arch/CAIWU/"  log_archive_format       = "%t_%s_%r.dbf"  db_file_multiblock_read_count= 16  undo_management          = "AUTO"  undo_tablespace          = "UNDOTBS1"  job_queue_processes      = 10  open_cursors             = 300  pga_aggregate_target     = 858783744

修改后参数文件内容如下:

[IBMP740-2:oracle:/oracle/product/10.2.0/db_1/dbs]$cat initCAIWU.ora    # Please change the values of the following parameters:  control_files            ="/yl_oradata/CAIWU/control01.ctl","/yl_oradata/CAIWU/control02.ctl","/yl_oradata/CAIWU/control03.ctl"   db_recovery_file_dest    = "/oracle/flash_recovery_area"  db_recovery_file_dest_size= 2147483648  background_dump_dest     = "/oracle/admin/CAIWU/bdump"  user_dump_dest           = "/oracle/admin/CAIWU/udump"  core_dump_dest           = "/oracle/admin/CAIWU/cdump"  audit_file_dest          = "/oracle/admin/CAIWU/adump"  db_name                  = "CAIWU"# Please review the values of the following parameters:  remote_login_passwordfile= "EXCLUSIVE"  db_domain                = ""  dispatchers              = "(PROTOCOL=TCP) (SERVICE=CAIWUXDB)"# The values of the following parameters are from source database:  processes                = 500  sessions                 = 555  sga_target               = 3439329280  db_block_size            = 8192  compatible               = "10.2.0.3.0"# log_archive_dest_1       = "LOCATION=/yl_oradata/arch/CAIWU/"  log_archive_format       = "%t_%s_%r.dbf"  db_file_multiblock_read_count= 16  undo_management          = "AUTO"  undo_tablespace          = "UNDOTBS1"  job_queue_processes      = 10  open_cursors             = 300  pga_aggregate_target     = 858783744

2.2.6 修改重建控制文件的脚本caiwu.sql

修改后的caiwu.sql内容如下:

[IBMP740-2:oracle:/yl_oradata/CAIWU]$cat caiwu.sql-- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNT PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora'CREATE CONTROLFILE REUSE SET DATABASE "CAIWU" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/yl_oradata/CAIWU/redo01.log'  SIZE 512M,  GROUP 2 '/yl_oradata/CAIWU/redo02.log'  SIZE 512M,  GROUP 3 '/yl_oradata/CAIWU/redo03.log'  SIZE 512MDATAFILE  '/yl_oradata/CAIWU/system01.dbf',  '/yl_oradata/CAIWU/undotbs01.dbf',  '/yl_oradata/CAIWU/sysaux01.dbf',  '/yl_oradata/CAIWU/users01.dbf',  '/yl_oradata/CAIWU/zwdata01.dbf',  '/yl_oradata/CAIWU/zwdata02.dbf'CHARACTER SET ZHS16GBK;-- Database can now be opened zeroing the online logs.ALTER DATABASE OPEN RESETLOGS;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE '/yl_oradata/CAIWU/temp01.dbf'     SIZE 2048M AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;-- End of tempfile additions.--set echo offprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt * Your database has been created successfully!prompt * There are many things to think about for the new database. Hereprompt * is a checklist to help you stay on track:prompt * 1. You may want to redefine the location of the directory objects.prompt * 2. You may want to change the internal database identifier (DBID) prompt *    or the global database name for this database. Use the prompt *    NEWDBID Utility (nid).prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SHUTDOWN IMMEDIATE STARTUP UPGRADE PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora'@$ORACLE_HOME/rdbms/admin/utlirp.sql SHUTDOWN IMMEDIATE STARTUP PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora'-- The following step will recompile all PL/SQL modules.-- It may take serveral hours to complete.@$ORACLE_HOME/rdbms/admin/utlrp.sql set feedback 6;

2.3 转换数据库

2.3.1 创建实例CAIWU执行转换脚本

SQL> STARTUP NOMOUNT PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora'ORACLE instance started.Total System Global Area 3439329280 bytesFixed Size                  2087840 bytesVariable Size             671089760 bytesDatabase Buffers         2717908992 bytesRedo Buffers               48242688 bytesSQL> CREATE CONTROLFILE REUSE SET DATABASE "CAIWU" RESETLOGS  ARCHIVELOG  2      MAXLOGFILES 16  3      MAXLOGMEMBERS 3  4      MAXDATAFILES 100  5      MAXINSTANCES 8  6      MAXLOGHISTORY 292  7  LOGFILE  8    GROUP 1 '/yl_oradata/CAIWU/redo01.log'  SIZE 512M,  9    GROUP 2 '/yl_oradata/CAIWU/redo02.log'  SIZE 512M, 10    GROUP 3 '/yl_oradata/CAIWU/redo03.log'  SIZE 512M 11  DATAFILE 12    '/yl_oradata/CAIWU/system01.dbf', 13    '/yl_oradata/CAIWU/undotbs01.dbf', 14    '/yl_oradata/CAIWU/sysaux01.dbf', 15    '/yl_oradata/CAIWU/users01.dbf', 16    '/yl_oradata/CAIWU/zwdata01.dbf', 17    '/yl_oradata/CAIWU/zwdata02.dbf' 18  CHARACTER SET ZHS16GBK 19  ;Control file created.SQL> ALTER DATABASE OPEN RESETLOGS;Database altered.SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/yl_oradata/CAIWU/temp01.dbf'  2       SIZE 2048M AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;Tablespace altered.SQL> SHUTDOWN IMMEDIATE Database closed.Database dismounted.ORACLE instance shut down.STARTUP UPGRADE PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora'SQL>@$ORACLE_HOME/rdbms/admin/utlirp.sql.....SQL> SQL> Rem Continue even if there are SQL errorsSQL> WHENEVER SQLERROR CONTINUE;SQL> SQL> Rem ===========================================================================SQL> Rem END utlip.sqlSQL> Rem ===========================================================================SQL> SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC>   utlirp.sql completed successfully. All PL/SQL objects in theDOC>   database have been invalidated.DOC>DOC>   Shut down and restart the database in normal mode and run utlrp.sql toDOC>   recompile invalid objects.DOC>#######################################################################DOC>#######################################################################DOC># SQL> SHUTDOWN IMMEDIATEDatabase closed.Database dismounted.ORACLE instance shut downSQL> STARTUP PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora'ORACLE instance started.Total System Global Area 3439329280 bytesFixed Size                  2087840 bytesVariable Size             671089760 bytesDatabase Buffers         2717908992 bytesRedo Buffers               48242688 bytesDatabase mounted.Database opened.SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql.....SQL> SQL> SQL> Rem =====================================================================SQL> Rem Run component validation procedureSQL> Rem =====================================================================SQL> SQL> SET serveroutput onSQL> EXECUTE dbms_registry_sys.validate_components;PL/SQL procedure successfully completed.SQL> SET serveroutput offSQL> SQL> SQL> Rem ===========================================================================SQL> Rem END utlrp.sqlSQL> Rem ===========================================================================SQL>

2.3.2 用system用户和密码进行登录来验证

SQL> conn system/ufgovConnected.

可以登录

2.3.3 表空间验证，查询迁移后的表空间及文件

SQL> select tablespace_name,file_id from dba_data_files;TABLESPACE_NAME                   FILE_ID------------------------------ ----------ZWDATA                                  6ZWDATA                                  5USERS                                   4SYSAUX                                  3UNDOTBS1                                2SYSTEM                                  16 rows selected.

2.3.4 对数据库进行DML操作验证

SQL> create table test (i int);Table created.SQL> insert into test values (1);1 row created.SQL> select * from test;         I----------         1SQL> delete from test;1 row deleted.SQL> drop table test;Table dropped.

2.3.5 切换日志测试

SQL> alter system switch logfile;System altered.

小结

1. 确保源库与目标数据库的字符集一致 2. 要确保源库与目标数据库的字节编码相同 3. 数据库迁移后要把无效的数据库对象重新编译，确保正确执行utlrp.sql,utlirp.sql 4. 数据库迁移后，确保应用系统用户可以正确连接，登陆有效 5. 监听要重新配置，检查临时表空间的属主与可用性 6. 迁移完成后要立即进行一次全备份