本文详细记录了从32位Linux系统到64位Linux系统进行Oracle数据库迁移的过程，包括环境配置、文件拷贝、数据库启动升级、重编译PL/SQL对象和处理OLAP相关问题，最终成功完成迁移。
迁移前后环境
souce：
系统：redflag Asialinux 2 32bit 2.6.9-42.7AXsmp
数据库：oracle 32bit 11.2.0.1.0
IP：10.162.2.150
ORACLE_HOME：/opt/oracle/product/11.2.0/dbhome_1
target：
系统：redflag Asialinux 2 64bit 2.6.9-89.5AXSlargesmp
数据库：oracle 64bit 11.2.0.1.0
IP：10.245.1.118
ORACLE_HOME：/opt/oracle/product/11.2.0/dbhome_1
本例不改变souce和target的datafile等文件的存放目录，故不用修改pifle 和 control file 里的路径，如不能达到source和target的control file、datafile等文件存放目录一致。可能需要修改pfile和利用trace重建controlfile。见tianlesoftware写的http://blog.youkuaiyun.com/tianlesoftware/article/details/7258654
一、开始安装oracle数据库（数据库安装之前的操作省略）
[oracle@aqyxn ~]$ cd /opt/app/orainstall/database/
[oracle@aqyxn database]$ ./runInstaller
检查下oracle_home的路径，之后一路“下一步”
/opt/oracle/product/11.2.0/dbhome_1/root.sh
二、在target建立对应目录（实际情况需根据souce建立）
[oracle@aqyxn oracle]$ cd /opt/oracle
[oracle@aqyxn oracle]$ mkdir admin cfgtoollogs diag extapi flash_recovery_area oradata oradiag_oracle oraInventory
[oracle@aqyxn oracle]$ cd admin/
[oracle@aqyxn admin]$ mkdir db
[oracle@aqyxn admin]$ cd db/
[oracle@aqyxn db]$ mkdir adump dpdump pfile
[oracle@aqyxn db]$ cd /opt/oracle/oradata/
[oracle@aqyxn flash_recovery_area]$ mkdir /opt/oracle/oradata/db
[oracle@aqyxn flash_recovery_area]$ cd /opt/oracle/flash_recovery_area
[oracle@aqyxn flash_recovery_area]$ mkdir db
三、拷贝souce文件传到target
1、先要normal停原数据库实例和监听，在source执行scp命令
2、scp oracle文件
[oracle@aqyxn oracle]$ cd /opt/oracle/flash_recovery_area/db/
[oracle@aqyxn db]$ scp control02.ctl oracle@10.245.1.118:/opt/oracle/flash_recovery_area/db/
[oracle@aqyxn oracle]$ cd /opt/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@aqyxn admin]$ ls
[oracle@aqyxn dbs]$ scp spfiledb.ora orapwdb oracle@10.245.1.118:/opt/oracle/product/11.2.0/dbhome_1/dbs/
oracle@10.245.1.118's password:
spfiledb.ora 100% 2560 2.5KB/s 00:00
orapwdb 100% 1536 1.5KB/s 00:00
[oracle@aqyxn oracle]$ cd /opt/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@aqyxn admin]$ ls
listener1104218下午5726.bak samples sqlnet1104218下午5726.bak tnsnames1104218下午5726.bak
listener.ora shrept.lst sqlnet.ora tnsnames.ora
[oracle@aqyxn admin]$ scp * oracle@10.245.1.118:/opt/oracle/product/11.2.0/dbhome_1/network/admin/
oracle@10.245.1.118's password:
listener1104218下午5726.bak 100% 378 0.4KB/s 00:00
listener.ora 100% 363 0.4KB/s 00:00
samples: not a regular file
shrept.lst 100% 187 0.2KB/s 00:00
sqlnet1104218下午5726.bak 100% 215 0.2KB/s 00:00
sqlnet.ora 100% 215 0.2KB/s 00:00
tnsnames1104218下午5726.bak 100% 332 0.3KB/s 00:00
tnsnames.ora 100% 317 0.3KB/s 00:00
[oracle@aqyxn db]$ cd /opt/oracle/oradata/db
[oracle@aqyxn db]$ ls
afiedt.buf aqyxn_data3.dbf data_inx.dbf redo01.log sysaux01.dbf tbs_pdata01.dbf temp.dbf
aqyxn_data1.dbf aqyxn_data4.dbf imp.log redo02.log system01.dbf tbs_pdata02.dbf undotbs01.dbf
aqyxn_data2.dbf control01.ctl nohup.out redo03.log system02.dbf temp02.dbf users01.dbf
[oracle@aqyxn db]$ scp * oracle@10.245.1.118:/opt/oracle/oradata/db
3、scp 其他文件的tar包
/home/oracle/ 下所有文件
crontab
/home/lyl/ 下所有文件
三、开始在target整数据库
因为从32位迁移到64位，有些pl/sql可能不兼容，需要重建，另外也有可能存在一些invalid状态的objects。
1、创建pfile、修改pfile以便启动（需要修改哪些可以试着startup nomount 看哪些参数有冲突）
[oracle@aqyxn dbs]$ sqlplus / as sysdba
SQL> create pfile from spfile;
SQL> quit
[oracle@aqyxn dbs]$ cd /opt/oracle/product/11.2.0/dbhome_1/dbs
[oracle@aqyxn dbs]$ vi initdb.ora
因为startup nomount后发现alert<sid>.log中有以下参数冲突，所以对pfile进行相应的修改
删除 *.log_archive_start=FALSE
*.memory_target=的8409579520 修改为 4204789760
2、重编译pl/sql对象
修改完成后shutdown再startup upgrade，如果直接以normal方式startup，之后运行如下脚本会告诉你需要以upgrade的方式启动。
SQL> STARTUP UPGRADE pfile='/opt/oracle/product/11.2.0/dbhome_1/dbs/initdb.ora';
执行以下命令查看有多少无效的对象
SQL> select count(*) from all_objects where status ='INVALID';
SQL> spool /home/oracle/mig32-64.log
SQL> @?/rdbms/admin/utlirp.sql
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> spool off
执行以下命令，查看有多少无效的对象，会发现有很多，正如上面说的utlirp.sql completed successfully. All PL/SQL objects in the
database have been invalidated.这个脚本将所有pl/sql对象都置为无效。
SQL> select count(*) from all_objects where status ='INVALID';
COUNT(*)
----------
9554
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup
3、这里先重编译java对象
begin
update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$
where owner#=0 and type#=29 and short(+)=name and
nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
declare
cursor C1 is select
'DROP JAVA DATA "' || u.name||'"."' || o.name || '"'
from obj$ o,user$ u where o.type#=56 and u.user#=o.owner#;
ddl_statement varchar2(200);
iterations number;
previous_iterations number;
loop_count number;
my_err number;
begin
previous_iterations := 10000000;
loop
-- To make sure we eventually stop,pick a maxnumber of iterations
select count(*) into iterations from obj$ where type#=56;
exit when iterations=0 or iterations >=previous_iterations;
previous_iterations := iterations;
loop_count := 0;
open C1;
loop
begin
fetch C1 into ddl_statement;
exit when C1%NOTFOUND or loop_count > iterations;
exception when others then
my_err := sqlcode;
if my_err = -1555 then --snapshot too old, re-execute fetch query
exit;
else
raise;
end if;
end;
initjvmaux.exec(ddl_statement);
loop_count := loop_count + 1;
end loop;
close C1;
end loop;
end;
commit;
initjvmaux.drp('delete from java$policy$shared$table');
update obj$ set status=1 where obj#=(select obj# from obj$,javasnm$
where owner#=0 and type#=29 and short(+)=name and
nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
end;
/
SQL> create or replace java system
2 /
SQL> select COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)
----------
8755
开始重编译pl/sql对象。
SQL> @?/rdbms/admin/utlrp.sql
以下命令查看还有多少无效的对象
select COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
另外开一屏，同时监控aler<sid>.log
tail -f /opt/oracle/diag/rdbms/db/db/trace/alert_db.log
Thu Sep 20 17:54:07 2012
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x2B97DBD2BF] [PC:0x3432BB4, __intel_new_memcpy()+2164] [flags: 0x0, count: 1]
Errors in file /opt/oracle/diag/rdbms/db/db/trace/db_j025_26486.trc (incident=4473):
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+2164] [SIGSEGV] [ADDR:0x2B97DBD2BF] [PC:0x3432BB4] [Address not mapped to object] []
见到alert日志中有如上报错，表明pl/sql块已大部分重编译完成，因为olap从32位迁移到64位不兼容，还剩下olap的对象无效。见到以上报错，等几分钟脚本应该会自动中断，如等了几分钟还未中断，可以ctrl + c 停止脚本
查看还有多少无效对象
SQL> select COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)
----------
82
这些无效对象如下显示，都是olap的对象
SQL> col OBJECT_NAME for a40
SQL> set pagesize 999
SQL> select OWNER,OBJECT_NAME,STATUS FROM dba_objects where STATUS='INVALID';
OWNER OBJECT_NAME STATUS
------------------------------ ---------------------------------------- -------
OLAPSYS ALL$OLAP2_AW_CATALOGS INVALID
PUBLIC ALL_OLAP2_AW_CATALOGS INVALID
OLAPSYS ALL$OLAP2_AW_CATALOG_MEASURES INVALID
PUBLIC ALL_OLAP2_AW_CATALOG_MEASURES INVALID
OLAPSYS ALL$OLAP2_AW_PHYS_OBJ INVALID
PUBLIC ALL_OLAP2_AW_PHYS_OBJ INVALID
OLAPSYS ALL$OLAP2_AW_PHYS_OBJ_PROP INVALID
PUBLIC ALL_OLAP2_AW_PHYS_OBJ_PROP INVALID
OLAPSYS ALL$OLAP2_AW_DIMENSIONS INVALID
PUBLIC ALL_OLAP2_AW_DIMENSIONS INVALID
OLAPSYS ALL$OLAP2_AW_ATTRIBUTES INVALID
PUBLIC ALL_OLAP2_AW_ATTRIBUTES INVALID
OLAPSYS ALL$OLAP2_AW_CUBES INVALID
PUBLIC ALL_OLAP2_AW_CUBES INVALID
OLAPSYS ALL$OLAP2_AW_CUBE_DIM_USES INVALID
PUBLIC ALL_OLAP2_AW_CUBE_DIM_USES INVALID
OLAPSYS ALL$AW_DIM_ENABLED_VIEWS INVALID
PUBLIC ALL_AW_DIM_ENABLED_VIEWS INVALID
OLAPSYS ALL$AW_CUBE_ENABLED_VIEWS INVALID
PUBLIC ALL_AW_CUBE_ENABLED_VIEWS INVALID
OLAPSYS ALL$AW_CUBE_ENABLED_HIERCOMBO INVALID
PUBLIC ALL_AW_CUBE_ENABLED_HIERCOMBO INVALID
OLAPSYS ALL$OLAP2_AW_DIM_LEVELS INVALID
PUBLIC ALL_OLAP2_AW_DIM_LEVELS INVALID
OLAPSYS ALL$OLAP2_AW_DIM_HIER_LVL_ORD INVALID
PUBLIC ALL_OLAP2_AW_DIM_HIER_LVL_ORD INVALID
OLAPSYS ALL$OLAP2_AW_CUBE_MEASURES INVALID
PUBLIC ALL_OLAP2_AW_CUBE_MEASURES INVALID
OLAPSYS ALL$OLAP2_AW_CUBE_AGG_SPECS INVALID
PUBLIC ALL_OLAP2_AW_CUBE_AGG_SPECS INVALID
OLAPSYS ALL$OLAP2_AW_CUBE_AGG_MEAS INVALID
PUBLIC ALL_OLAP2_AW_CUBE_AGG_MEAS INVALID
OLAPSYS ALL$OLAP2_AW_CUBE_AGG_LVL INVALID
PUBLIC ALL_OLAP2_AW_CUBE_AGG_LVL INVALID
OLAPSYS ALL$OLAP2_AW_CUBE_AGG_OP INVALID
PUBLIC ALL_OLAP2_AW_CUBE_AGG_OP INVALID
PUBLIC MRV_OLAP1_POP_DIMENSIONS INVALID
PUBLIC MRV_OLAP1_POP_CUBES INVALID
PUBLIC MRV_OLAP2_POP_DIMENSIONS INVALID
PUBLIC MRV_OLAP2_POP_CUBES INVALID
OLAPSYS MRAC_OLAP2_AW_DIMENSIONS_V INVALID
OLAPSYS MRAC_OLAP2_AW_ATTRIBUTES_V INVALID
OLAPSYS MRAC_OLAP2_AW_CUBES_V INVALID
OLAPSYS MRAC_OLAP2_AW_CUBE_MEASURES_V INVALID
PUBLIC MRV_OLAP2_AW_DIMENSIONS INVALID
PUBLIC MRV_OLAP2_AW_ATTRIBUTES INVALID
PUBLIC MRV_OLAP2_AW_CUBES INVALID
PUBLIC MRV_OLAP2_AW_CUBE_MEASURES INVALID
OLAPSYS CWM2_OLAP_MR_SECURITY_INIT INVALID
OLAPSYS CWM2_OLAP_METADATA_REFRESH INVALID
OLAPSYS OLAPFACTVIEW INVALID
OLAPSYS OLAPDIMVIEW INVALID
OLAPSYS ODM$OLAP2UDIM_HIER_LEVEL_USES INVALID
OLAPSYS ODM$OLAP2ULEVEL_KEY_COL_USES INVALID
OLAPSYS ODM$OLAP2UJOIN_KEY_COLUMN_USES INVALID
OLAPSYS ODM$OLAP2UFACT_LEVEL_USES INVALID
OLAPSYS DBMS_ODM INVALID
OLAPSYS OLAP_SYS_AW_ACCESS_DIM_VIEW INVALID
OLAPSYS OLAP_SYS_AW_ACCESS_CUBE_VIEW INVALID
OLAPSYS OLAP_SYS_AW_ENABLE_ACCESS_VIEW INVALID
PUBLIC OLAP_SYS_AW_ACCESS_CUBE_VIEW INVALID
PUBLIC OLAP_SYS_AW_ACCESS_DIM_VIEW INVALID
PUBLIC OLAP_SYS_AW_ENABLE_ACCESS_VIEW INVALID
OLAPSYS CWM2_OLAP_OLAPAPI_ENABLE INVALID
OLAPSYS DBMS_AWM INVALID
OLAPSYS CWM2_OLAP_CUBE INVALID
MDSYS SDO_TOPO_GEOMETRY INVALID
PUBLIC SDO_TOPO_ANYINTERACT INVALID
MDSYS SDO_GEOR INVALID
MDSYS SDO_GEOR_TRIG_INS1 INVALID
MDSYS SDO_GEOR_TRIG_DEL1 INVALID
MDSYS SDO_GEOR_TRIG_UPD1 INVALID
PUBLIC USER_SDO_GEOR_SYSDATA INVALID
MDSYS SDO_GEOR_INT INVALID
MDSYS SDO_NET_PARTITION INVALID
PUBLIC SDO_NET_PARTITION INVALID
MDSYS SDO_NET INVALID
MDSYS MD_NET INVALID
MDSYS PRVT_SAM INVALID
MDSYS SDO_SAM INVALID
MDSYS SDO_OLS INVALID
PUBLIC WWV_FLOW_LDAP INVALID
82 rows selected.
SQL> set linesize 200
SQL> col COMP_ID for a10
SQL> col COMP_NAME for a40
SQL> select comp_id,comp_name , version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- ------------------------------ ----------------------
OWB OWB 11.2.0.1.0 VALID
APEX Oracle Application Express 3.2.1.00.10 VALID
EM Oracle Enterprise Manager 11.2.0.1.0 VALID
AMD OLAP Catalog 11.2.0.1.0 VALID
SDO Spatial 11.2.0.1.0 VALID
ORDIM Oracle Multimedia 11.2.0.1.0 VALID
XDB Oracle XML Database 11.2.0.1.0 VALID
CONTEXT Oracle Text 11.2.0.1.0 VALID
EXF Oracle Expression Filter 11.2.0.1.0 VALID
RUL Oracle Rules Manager 11.2.0.1.0 VALID
OWM Oracle Workspace Manager 11.2.0.1.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.1.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.1.0 VALID
XML Oracle XDK 11.2.0.1.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.1.0 VALID
APS OLAP Analytic Workspace 11.2.0.1.0 VALID
XOQ Oracle OLAP API 11.2.0.1.0 VALID
18 rows selected.
4、以database的方式删除 olap。注意,如果在删除olap后还想重新安装的话，不要使用oracle home的方式删除。
metalink [ID 332351.1]中提到
NOTE: If you removed OLAP from the databse and plan to add it again, do NOT remove it from the ORACLE_HOME and do not set relink the Oracle executable with olap_off.
ORACLE_HOME=your_oracle_home
ORACLE_SID=your_db_sid
PATH=$ORACLE_HOME/bin:$PATH
sqlplus /nolog
SQL> conn / as sysdba
SQL> @?/olap/admin/catnoamd.sql
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/cwm2drop.sql
SQL> select COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)
----------
57
删除完成后，如发现还有无效的对象，可以执行以下命令重编译。
SQL> @?/rdbms/admin/utlrp.sql
SQL> select COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)
----------
0
5、以database的方式安装olap
注意下面的TMP是指临时表空间，因为我source的临时表空间是TMP，所以下面是TMP，如是TEMP则改为TEMP
SQL> @?/olap/admin/olap.sql SYSAUX TMP;
SQL> select COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
COUNT(*)
----------
0
SQL> select comp_id,comp_name , version,status from dba_registry;
到此，迁移完成，查看版本正常。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> exit
四、开启监听，normal方式重启数据库
[oracle@aqyxn ~]$ lsnrctl start
[oracle@aqyxn ~]$ sqlplus / as sysdba
SQL> startup
SQL> alter system register;
SQL> exit
检查listener.ora和tnsname.ora是否配置正确
[oracle@aqyxn ~]$ sqlplus system/oracle@db
备注：对于想反复做测试的情况。可以使用以下命令删除数据库
[oracle@aqyxn ~]$ /opt/oracle/product/11.2.0/dbhome_1/deinstall/deinstall
如想查看olap oracle_home方式reinstall的，另外各个脚本含义的，见tianlesoftware写的http://blog.youkuaiyun.com/tianlesoftware/article/details/7258654
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
