32位oracle数据迁移到64位oracle,将Oracle数据库从32位平台迁移到64位

3: 查看当前的数据库状态

SQL> select count(*) from dba_objects where status ='INVALID';

COUNT(*)

----------

0

SQL> select comp_name,status from dba_registry;

COMP_NAME    STATUS

---------------------------------------- ----------------------

Oracle Database Catalog Views  VALID

Oracle Database Packages and Types  VALID

Oracle Workspace Manager  VALID

JServer JAVA Virtual Machine  VALID

Oracle XDK    VALID

Oracle Database Java Packages  VALID

Oracle Expression Filter  VALID

Oracle Data Mining    VALID

Oracle Text    VALID

Oracle XML Database    VALID

Oracle Rules Manager    VALID

COMP_NAME    STATUS

---------------------------------------- ----------------------

Oracle interMedia    VALID

OLAP Analytic Workspace   VALID

Oracle OLAP API     VALID

OLAP Catalog    VALID

Spatial     VALID

Oracle Enterprise Manager  VALID

17 rows selected.

4：拷贝参数文件，控制文件，数据文件，日志文件到64位数据库

5：在64位系统上创建相应目录，如udmp  adump等，要和32位系统一致

6：如果数据库的数据文件目录发生变化，则需要新建控制文件，(最好保持源库和目标库的一致，减少工作量和错误发生率)

7：将数据库中的存储过程等置为失效,UTLIRP脚本会将存储过程等置为失效

--如果迁移的是oracle9i的数据库，执行如下命令：

SQL> STARTUP MIGRATE

--如果是Oracle10g或11g，执行如下命令：

SQL> STARTUP UPGRADE

SQL> SPOOL EASY.log;

SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

SQL> SPOOL OFF;

8: 重新启动数据库，

9: 如果数据库有OLAP组件，则删除OLAP，以sys用户执行如下脚本

SQL> conn / as sysdba

----> Remove OLAP Catalog

SQL> @?/olap/admin/catnoamd.sql

----> Remove OLAP API

SQL> @?/olap/admin/olapidrp.plb

SQL> @?/olap/admin/catnoxoq.sql

----> Deinstall APS - OLAP AW component

SQL> @?/olap/admin/catnoaps.sql

SQL> @?/olap/admin/cwm2drop.sql  ---只需要在11g中执行。在10g中，catnoamd.sql 脚本中已经包含了该脚本，所以不用执行

You could getsome invalid objects under SYS and PUBLIC owner, and they are the old duplicate OLAPSYS objects copied under these schemas when Olap has been installedpreviously.

Generally, theOlap objects are named with context like %OLAP%, %AWM%, or other Olap word keys intheir "object_name" field, however, if it needs a help to recognizethem, then please contact Oracle  Support and create a Service Request toget assistance for this question.

Note that catnoadm.sql could fail from 10.1.0.5 to 11.1.0.7release.

Due to the factthat it refers to three scripts which don't get shipped until 11.2 this scriptwill fail.

Besides that itwill error on 7 non-existing synonyms to drop.

Prior 11.2, execute these three dropsynonym statements:

SQL> drop public synonym OlapFactView;

SQL> drop public synonym OlapDimView;

SQL> drop public synonym DBMS_ODM;

10：编译失效对象

SQL> @?/rdbms/admin/utlrp.sql

此时会有错误，这是因为olap没有安装，下面安装olap，然后重新编译

11：添加OLAP

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

SQL> @?/rdbms/admin/utlrp.sql    --这次会非常快

12.  验证无效对象和组件状态

SQL> l

1* select count(*) from dba_objects where status<>'VALID'

SQL> col comp_name for a40

SQL> select comp_name,status from dba_registry;

COMP_NAME    STATUS

---------------------------------------- ----------------------

Oracle Database Catalog Views  VALID

Oracle Database Packages and Types  VALID

Oracle Workspace Manager  VALID

JServer JAVA Virtual Machine  VALID

Oracle XDK    VALID

Oracle Database Java Packages  VALID

Oracle Expression Filter  VALID

Oracle Data Mining    VALID

Oracle Text    VALID

Oracle XML Database    VALID

Oracle Rules Manager    VALID

COMP_NAME    STATUS

---------------------------------------- ----------------------

Oracle interMedia    VALID

OLAP Analytic Workspace   VALID

Oracle OLAP API     VALID

OLAP Catalog    VALID

Spatial     VALID

Oracle Enterprise Manager  VALID

11：重新编译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

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

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

/

It may be necessary to recompilethe Java objects with ncomp:i.e.  % ncomp-user scott/tiger Hello.class

迁移过程中，最好不要有ddl操作，以免造成死锁