解决一个oracle迁移从64bit到32bit的问题

这两天做一个迁移的测试,源是64位10.2.0.5,目标是32位的10.2.0.5,之前没有注意到64位到32位的区别,结果出了一些问题,现把解决过程记录一下.

先是迁移:

先在源端上面做一个rman的全备,把备份集copy到目标端,路径要和源端一致,备份脚本参考如下:

run{
sql'alter system archive log current';
crosscheck backup;
crosscheck archivelog all;
backup as backupset incremental level=0 database format '/rman/full_%d_%T_%u.bak';
backup archivelog all format '/rman/al_%d_%T_%u.bak' delete all input;
delete noprompt expired backup;
delete noprompt obsolete;
delete noprompt archivelog until time 'sysdate-8' all;
backup current controlfile;
sql'alter database backup controlfile to trace';
sql'alter system archive log current';
}

接着在目标端进行操作,需要先创建相关的目录,如oradata,xxdump的目录等等,把源端的pfile copy到目标端,改一下不合适的参数(如sga,pga大小等),然后开始恢复,恢复步骤:

1.以nomount状态启动

2.恢复控制文件,rman中restore controlfile from 'xxxxx';

3.把数据库状态改为mount,alter database mount;

4.restore database

5.recover database

6.以resetlogs方式打开数据库(因为没有用一致的redo),alter database open;

然后检查alert日志,发现开始不停的刷报错,报错类似如下:

Errors in file
/u01/app/oracle/admin/xxx/bdump/xxx_cjq0_26746.trc 
ORA-00604: error occurred at recursive SQL level 1
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56319]

查了下mos,恰好有个这种问题的文章:ORA-06553: PLS-801: internal error [56319], ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], [], ORA-00604 when using different bit datafiles than Oracle executable [ID 1436552.1]

产生的原因是:The database on the new location was built using datafiles from a different word-size database. For example, you used the datafiles from a 64-bit database for your new 32-bit database OR you used the datafiles from a 32-bit database for your new 64-bit database. Either of these scenarios results in PL/SQL objects that are inconsistent with the Oracle executable.

它的解决步骤如下:

1.Shutdown the database and restart in STARTUP UPGRADE mode.
2.Under the <ORACLE_HOME>\RDBMS\Admin folder, run utlip.sql to invalidate all PL/SQL objects.
3.Shutdown the database and restart in normal mode.
4.Under the <ORACLE_HOME>\RDBMS\Admin folder, run utlrp.sql to recompile all invalid PL/SQL objects.
5.Retest and the errors should be resolved.

根据它的解决步骤,能解决大部分问题,在这个过程需要反复好几次@utlrp,直到最后,会有几十个对象无法重新编译,用select object_name,object_type,owner from dba_objects where status <>'VALID'检查名称和类型,发现这个是因为这些个对象是olap中使用到的,只需要重建olap

删除 
@?/olap/admin/catnoamd.sql 
@?/olap/admin/olapidrp.plb

重建
@?/olap/admin/olap SYSAUX TEMP

新建好olap的对象后,再执行@utlrp,对无效对象进行编译,直到 SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6) 为0.

recompile之后再重启,检查alert日志,就一切正常了.