oracle64位导入oracle32位,ORACLE 从32位到64位的一次迁移

1 环境说明

A 机：192.168.1.184

操作系统：LINUX 32位

数据库：ORACLE 10G 32位

B机：192.168.1.183

操作系统：LINUX 64位

数据库：ORACLE 10G 64位

2 A机备份

备份脚本：

Run{

Allocate channel a1 type disk;

Allocate channel a2 type disk;

Sql’alter system switch logfile’

Backup full format=’/home/oracle/rman/FULL_%d_%T_%s’ database plus archivelog format=’/home/oracle/rman/arch_%d_%T_%s’;

Backup format=’/home/oracle/rman/ctl_%U’ current controlfile;

Release channel a2;

Release channel a1;

}

3 A机生成参数文件

Create pfile=’/home/oracle/rman/initdevdb.ora’ from spfile;

4 将备从文件和参数文件传至B机

Cd /home/oracle/rman

Scp * oracle@192.168.1.183:/home/oracle/rman

5 在B机对参数文件做相应的修改后启动到NOMOUNT状态

Export ORACLE_SID=devdb

Sqlplus / as sysdba

Startup nomount pfile=’/home/oracle/rman/initdevdb.ora’;

6 介质恢复

6.1 恢复控制文件

Rman target /

restore controlfile from ‘/home/oracle/rman/ctl_0hn2cdp1_1_1′;

6.2 启动到MOUNT 状态

Alter database mount;

6.3 介质恢复

Restore database;

Recover database;

在RECOVER DATABASE 时会报一个日志序列的错误：

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/08/2012 01:47:31

RMAN-06054: media recovery requesting unknown log: thread 1 seq 5 lowscn 504551

解决办法：

run{

set until sequence 5;

recover database;

}

6.4 以resetlogs 方式打开

alter database open resetlogs;

database opened

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows

ORACLE error from target database:

ORA-06553: PLS-801: internal error [56319]

因为数据库从32位到64位，会有很多无效对像出现，所以会出现以上错误。

解决办法：

在Target 端重新编译64位的PL/SQL模块

6.4.1 先重新启动到更新模式。

startup upgrade pfile=’/home/oracle/rman/initdevdb.ora’;

6.4.2 执行脚本：@$ORACLE_HOME/rdbms/admin/utlirp.sql

6.4.3 查询无效对象：

select count(*) from all_objects where status=’INVALID’;

COUNT(*)

―――-

6548

由以上可以看出无效对象。

6.4.4 创建SPFILE 并重新启动：

create spfile from pfile=’/home/oracle/rman/initdevdb.ora’;

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 264241152 bytes

Fixed Size 2020024 bytes

Variable Size 100666696 bytes

Database Buffers 155189248 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.

6.4.5 执行编译脚本

@?/rdbms/admin/utlrp.sql;

在执行以下时会报03113错误。

DECLARE

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

6 /

ERROR at line 1:

ORA-03113: end-of-file on communication channel

6.4.6 再次查询无效对象

select count(*) from all_objects where status=’INVALID’;

COUNT(*)

―――-

261

从以上数字可以看到比头一次查的要少很多了。

先放着，处理完Java在编译一次

重建 javashared data objects (SRO)

执行以下脚本：

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

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;

/

重建命令：create or replace java system

/

6.4.7 再次重新编译无效对象

@?/rdbms/admin/utlrp.sql;

在执行脚本的过程中，session异常中断，在alert log和 trace里都有：ORA-7445的错误：

ORA-07445: exception encountered: core dump[__intel_new_memcpy()+2164] [SIGSEGV] [ADDR:0x7F2F0CBBD2BF] [PC:0x47ED7D4][Address not mapped to object] []

―� Current SQL Statement for thissession (sql_id=2y0pxmcj6k00t) ―�

ALTER VIEW”OLAPSYS”.”ALL$OLAP2_AW_CATALOGS” COMPILE

导致这个问题，是因为我们启动了OLAP，在32位转到64位时就会遇到这种错误。

7 处理OLAP问题

7.1 删除组件

@?/olap/admin/catnoamd.sql

@?/olap/admin/olapidrp.plb

@?/olap/admin/catnoxoq.sql

@?/olap/admin/catnoaps.sql

@?/olap/admin/cwm2drop.sql ―此脚本在11G的版本中用，在10G是不需要的。

@?/rdbms/admin/utlrp.sql

drop public synonym OlapFactView;

drop public synonym OlapDimView;

drop public synonym DBMS_ODM;

关闭数据库

7.2 添加OLAP组件

利用DBCA工具。

因为恢复的实例在DBCA中是看不到的，所以要做一些修改，修改如下：

devdb:/home/oracle/oracle/product/10.2.0/db_1:N

然后在图形化下启动DBCA， 选择 configure database option――�next――�next―――-选择OLAP(SYSAUX) ―-next ――finish

待重建完毕后，可再次查看无效对象：

select count(*) from all_objects where status=’INVALID’;

COUNT(*)―――-1此处的一个无效对像并不影响数据库的完整功能。 迁移完毕。