How to Convert a 32-bit Database to 64-bit Database on Linux （remove olap）

http://dreamsanqin.blog.51cto.com/845412/1329010

一、rman方法迁移

32位服务器需要如下工作

1、在32位服务器上查看OLAP组件是否使用。

A、查看实例是否支持OLAP组件。

select VALUE from v$OPTION where PARAMETER = 'OLAP';

B、查看OLAP组件是否备使用过。

select name "FEATURE", first_usage_date "FROM", last_usage_date "TO" from DBA_FEATURE_USAGE_STATISTICS where name like '%OLAP%'

C、查看当前定义的Analytic Workspace (AW)，如果查询返回Analytic Workspace (AW)为sys用户所属，那么该实例未使用OLAP。

col owner format a10

col aw_name format a20

select owner, aw_name from dba_aws;

select aw_name, aw_number from all_aws;

D、查看OLAP当前状态。

select comp_id, comp_name, version, status from dba_registry where comp_name like '%OLAP%';

或

col comp_name for a40

SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;

2、查看dbid

select dbid from v$database;

3、查看授权用户

select * from v$pwfile_users

4、拷贝RMAN备份及控制文件备份。

5、查看日志scn号

select current_scn from v$database

select sequence#,first_change#,first_time,next_change#,next_time,archived,deleted from v$archived_log

64位服务器需要如下工作

1、拷贝有用的备份到64位机器（数据库备份、控制文件）。

2、拷贝pfile文件从32服务器上到对应位置。

3、启动64服务器数据库实例。

4、连接rman配置

rman连接数据库并设置dbid

$ rman target / nocatalog

RMAN> set dbid=<dbid from 32 server>;

还原控制文件及数据库

RMAN> restore controlfile from 'xxxxxx';

RMAN> alter database mount;

RMAN> restore database;

恢复数据库

Identify the archivelog till you want to do the recovery and check whether you have all the backups.

RMAN> recover database until scn xxxx;

RMAN> exit

5、升级数据库

启动sqlplus执行如下操作

windows平台：

SQL> SPOOL mig32-64.log;

SQL> alter database open resetlogs migrate;

SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql;

SQL> SPOOL OFF;

SQL> shutdown immediate;

SQL> startup;

SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;

linux平台：

SQL> SPOOL mig32-64.log;

SQL> alter database open resetlogs migrate;

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

SQL> SPOOL OFF;

Now you must restart the database and explicitly invoke utlrp.sql to recompile invalid objects.

SQL> shutdown immediate;

SQL> startup;

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

常见报错

Note 1 :If you are changing wordsize for Oracle release10.1.x/10.2.x/11.1/11.2 32-Bit to 64-bit with OLAP enabled for DB then you may likely to see the error

On Console:

<>ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []<>

On alert.log:

ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] <>

Or

ORA-07445: exception encountered: core dump [__intel_new_memcpy()+2132] [SIGSEGV]

(on 11g)

Or ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] < style="font-family: arial unicode ms;"> ( Run the Following query to check OLAP component installed bit version of Oracle release 9.2.0.4 and you want to switch to the 64-bit version of Oracle release 9.2.0.4, then you must complete thi

6、报错解决方法

A、移除olap组件

linux平台：

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

windows平台：

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoamd.sql

----> Remove OLAP API

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\olapidrp.plb

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoxoq.sql

----> Deinstall APS - OLAP AW component

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoaps.sql

B、查看无效对象

select owner, object_name, object_type, status from dba_objects where status='INVALID';

或

col owner for a8

col object_name for a40

col object_type for a15

select owner, object_name, object_type from dba_objects where status <> 'VALID';

C、删除OLAP相关无效的组件

spool oldrop.sql

select 'drop public synonym ' || synonym_name || ';'from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';

spool off

7、再次编译无效对象

linux平台：

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

windows平台：

SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;

8、更新java对象

SQL> CREATE OR REPLACE JAVA SYSTEM;

/

SQL> shutdown immediate;

SQL> startup

二、冷备方法迁移

1、在32位服务器上查看OLAP组件是否使用。

A、查看实例是否支持OLAP组件。

select VALUE from v$OPTION where PARAMETER = 'OLAP';

B、查看OLAP组件是否备使用过。

select name "FEATURE", first_usage_date "FROM", last_usage_date "TO" from DBA_FEATURE_USAGE_STATISTICS where name like '%OLAP%'

C、查看当前定义的Analytic Workspace (AW)，如果查询返回Analytic Workspace (AW)为sys用户所属，那么该实例未使用OLAP。

col owner format a10

col aw_name format a20

select owner, aw_name from dba_aws;

select aw_name, aw_number from all_aws;

D、查看OLAP当前状态。

select comp_id, comp_name, version, status from dba_registry where comp_name like '%OLAP%';

或

col comp_name for a40

SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;

2、shutdown 32位数据库，进行冷备份全库,紧接着是安装64位ORACLE。

注意：32位于64位数据库版本相同。

3、Oracle程序和数据库分别安装完成后，将TNS和实例服务器停掉，重命名数据库文件目录。

4、将32位服务器上的数据文件COPY到64位服务器的数据文件位置。

######################### 64位服务器需要如下工作 ################################

5、执行升级命令、启动数据库

linux平台:

SQL> STARTUP UPGRADE

Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database:

SQL> SPOOL mig32-64.log;

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

SQL> SPOOL OFF;

SQL> shutdown immediate;

SQL> startup;

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql;

Windows平台:

SQL> SPOOL mig32-64.log;

SQL>Startup upgrade

SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlirp.sql;

SQL> SPOOL OFF;

SQL> shutdown immediate;

SQL> startup;

SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;

常见错误

Note 1 :If you are changing wordsize for Oracle release10.1.x/10.2.x/11.1/11.2 32-Bit to 64-bit with OLAP enabled for DB then you may likely to see the error

On Console:

<>ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []<>

On alert.log:

ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] <>

Or

ORA-07445: exception encountered: core dump [__intel_new_memcpy()+2132] [SIGSEGV]

(on 11g)

Or ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] < style="font-family: arial unicode ms;"> ( Run the Following query to check OLAP component installed bit version of Oracle release 9.2.0.4 and you want to switch to the 64-bit version of Oracle release 9.2.0.4, then you must complete thi

6、移除olap组件

A、删除olap组件

linux平台：

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

windows平台：

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoamd.sql

----> Remove OLAP API

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\olapidrp.plb

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoxoq.sql

----> Deinstall APS - OLAP AW component

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catnoaps.sql

B、查看无效对象

select owner, object_name, object_type, status from dba_objects where status='INVALID';

col owner for a8

col object_name for a40

col object_type for a15

select owner, object_name, object_type from dba_objects where status <> 'VALID';

C、删除OLAP相关无效的组件

spool oldrop.sql

select 'drop public synonym ' || synonym_name || ';'from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';

spool off

7、再次编译无效对象

linux平台：

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

windows平台：

SQL>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql;

8、更新java对象

SQL> CREATE OR REPLACE JAVA SYSTEM;

/

9、再次查看数据库组件状态及无效对象。

col comp_name for a40

SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;

或

select owner, object_name, object_type, status from dba_objects where status='INVALID';