这次因为要迁移的数据库大小并不大但也不是只有几十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 sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 29 22:34:24 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. SQL> startup mount ORACLE instance started. Total System Global Area 3439329280 bytes Fixed Size 2060008 bytes Variable Size 2348810520 bytes Database Buffers 1073741824 bytes Redo Buffers 14716928 bytes Database 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 Little
19 rows selected.
2.2.3 利用dbms_tdb 包检查数据库
二．利用dbms_tdb 包检查数据库能否被传输以及列出外部表和DIRECTORY 等无法传输的对象信息，
分别在hp-ux平台数据库通过SQLPLUS执行以下两个存储过程
SQL> set serveroutput on
SQL> 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_DIR
PL/SQL procedure successfully completed.
2.2.4 RMAN ConvertDB
连接target database 查看数据库打开模式
[rx6600-2:oracle:/home/oracle]$sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Sep 29 22:46:30 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> 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-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=539 devtype=DISK Directory SYS.DUMP_CAIWU found in the database Directory SYS.DATA_PUMP_DIR found in the database Directory SYS.WORK_DIR found in the database Directory SYS.ADMIN_DIR found in the database Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database User SYS with SYSDBA and SYSOPER privilege found in password file channel ORA_DISK_1: starting datafile conversion input datafile fno=00005 name=/cwjc_data/CAIWU/zwdata01.dbf converted datafile=/cwjc_data/CAIWURMAN/zwdata01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:06:25 channel ORA_DISK_1: starting datafile conversion input datafile fno=00006 name=/cwjc_data/CAIWU/zwdata02.dbf converted datafile=/cwjc_data/CAIWURMAN/zwdata02.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:06:25 channel ORA_DISK_1: starting datafile conversion input datafile fno=00001 name=/cwjc_data/CAIWU/system01.dbf converted datafile=/cwjc_data/CAIWURMAN/system01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:25 channel ORA_DISK_1: starting datafile conversion input datafile fno=00002 name=/cwjc_data/CAIWU/undotbs01.dbf converted datafile=/cwjc_data/CAIWURMAN/undotbs01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting datafile conversion input datafile fno=00003 name=/cwjc_data/CAIWU/sysaux01.dbf converted datafile=/cwjc_data/CAIWURMAN/sysaux01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting datafile conversion input datafile fno=00004 name=/cwjc_data/CAIWU/users01.dbf converted datafile=/cwjc_data/CAIWURMAN/users01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55 Run SQL script /cwjc_data/CAIWURMAN/caiwu.sql on the target platform to create database Edit 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 platform To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform To change the internal database identifier, use DBNEWID Utility Finished backup at 29-SEP-14
将转换后的文件传输到目标主机上
[rx6600-2:oracle:/cwjc_data/CAIWURMAN]$ls -lrt total 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.sql ftp> get zwdata01.dbf 200 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.dbf ftp> get zwdata02.dbf 200 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.dbf ftp> get system01.dbf 200 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.dbf ftp> get undotbs01.dbf 200 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.dbf ftp> get sysaux01.dbf 200 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.dbf ftp> get users01.dbf 200 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.dbf ftp> get caiwu.sql 200 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.sql ftp> get init_00pjoiqb_1_0.ora 200 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 -lrt total 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 PERFORMANCE
STARTUP 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 292
LOGFILE
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 512M
DATAFILE
'/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 off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * 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 bytes Fixed Size 2087840 bytes Variable Size 671089760 bytes Database Buffers 2717908992 bytes Redo Buffers 48242688 bytes SQL> 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 errors SQL> WHENEVER SQLERROR CONTINUE; SQL> SQL> Rem =========================================================================== SQL> Rem END utlip.sql SQL> Rem =========================================================================== SQL> SQL> DOC DOC>####################################################################### DOC>####################################################################### DOC> utlirp.sql completed successfully. All PL/SQL objects in the DOC> database have been invalidated. DOC> DOC> Shut down and restart the database in normal mode and run utlrp.sql to DOC> recompile invalid objects. DOC>####################################################################### DOC>####################################################################### DOC># SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down SQL> STARTUP PFILE='/oracle/product/10.2.0/db_1/dbs/initCAIWU.ora' ORACLE instance started. Total System Global Area 3439329280 bytes Fixed Size 2087840 bytes Variable Size 671089760 bytes Database Buffers 2717908992 bytes Redo Buffers 48242688 bytes Database mounted. Database opened. SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql ..... SQL> SQL> SQL> Rem ===================================================================== SQL> Rem Run component validation procedure SQL> Rem ===================================================================== SQL> SQL> SET serveroutput on SQL> EXECUTE dbms_registry_sys.validate_components; PL/SQL procedure successfully completed. SQL> SET serveroutput off SQL> SQL> SQL> Rem =========================================================================== SQL> Rem END utlrp.sql SQL> Rem =========================================================================== SQL>
2.3.2 用system用户和密码进行登录来验证
SQL> conn system/ufgov Connected.
可以登录
2.3.3 表空间验证，查询迁移后的表空间及文件
SQL> select tablespace_name,file_id from dba_data_files; TABLESPACE_NAME FILE_ID ------------------------------ ---------- ZWDATA 6 ZWDATA 5 USERS 4 SYSAUX 3 UNDOTBS1 2 SYSTEM 1 6 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
----------
1
SQL> 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. 迁移完成后要立即进行一次全备份