Oracle Convert a 32bit Database to 64bit Database（32位转到 64位）说明

Oracle数据分32位和64位，对应不同的平台，关于查看DB 是32还是64位，我之前的Blog 有说明，参考：

查看Oracle 是32位还是64位的方法

http://blog.csdn.net/tianlesoftware/article/details/6818709

我们这里说的32位和64位就是wordsize。在某些情况下，比如数据迁移时，需要从32位的Oracle 迁移到64位的oracle上，这时候就需要修改wordsize，使他们保持一致。

这里我们假设迁移平台的ENDIAN 格式相同，不同的操作系统对应的ENDIAN也是不一样的。这里不做过多的说明，这部分内容属于跨操作系统迁移部分，会单独进行说明。

MOS 上有几篇关于修改wordsize的文章：

Changing between 32-bit and 64-bit WordSizes [ID 62290.1]

How To Change Oracle 11g Wordsize from32-bit to 64-bit. [ID 548978.1]

How to Convert a 32-bit Database to 64-bitDatabase on Linux? [ID 341880.1]

这里我们看一下[341880.1]。

Use thefollowing procedure to change wordsize of an Oracle9i Release 2 (9.2) orOracle10g (10.1 & 10.2) or 11g single instance database from Linux x86 toLinux x86-64 in same release:

--以下的操作过程支持linux下的Oracle 9iR2，10g和11g 单实例32到64的转换操作。注意这里数据库的版本要一致，仅wordsize 不一样。

1. Perform cold backups of theexisting Oracle9i or Oracle10g (10.1.x & 10.2.x) or 11g database on theLinux x86 system to protect against any failures during the wordsize change.

--冷备份整个DB，以防修改wordsize失败。

2. NOTE:This steps is required ifthe target system has different directory structure for database files.Otherwise this step can be skipped.

--这一步操作仅当Target system 的目录结构和Source 不一样，如果一样，这一步可以跳过。

To help withcreating the control file after the wordsize change, enter the following SQLcommand:

SQL> ALTERDATABASE BACKUP CONTROLFILE TO TRACE;

--将控制文件dump 到trace file，已方便我们后面的修改。

Oracle 控制文件

http://blog.csdn.net/tianlesoftware/article/details/4974440

This command saves the control file information to a trace file in the UDUMP directory (Typeshow parameter USER_DUMP_DEST in SQLPLUS as sys/system user). The control fileinformation is similar to the following:

CREATECONTROLFILE REUSE DATABASE "SAMPLE" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 112
LOGFILE
GROUP1 '/ia32lnx_path/oracle/dbs/t_log1.f' SIZE 25M,
GROUP2 '/ia32lnx_path/oracle/dbs/t_log2.f' SIZE 25M
DATAFILE '/ia32lnx_path/oracle/dbs/t_DB1.F'
CHARACTER SET WE8DEC;

3. In a new Oracle home, installthe 64-Bit release of Oracle9i Release 2 (9.2.x) , Oracle10.1.x, Oracle 10.2.x,Oracle 11.1.x, 11.2.x software for Linux x86-64. It is recommended to usethe same version on as on the 32-Bit box.

--在新的系统上安装64位的Oracle 软件，建议使用的版本和之前的32位oracle 保持一致。

4. Perform a clean databaseshutdown for Oracle-32 Bit database

--clean shutdown 32位的Oracle。

当clean shutdown 时，checkpoint会进行，并且此时datafile的stop scn和控制文件里的start scn会相同。等到open数据库时，Oracle检查datafile header中的start scn和存于control file中的datafile的scn是否相同，如果相同，接着检查start scn和stop scn是否相同，如果仍然相同，数据库就会正常开启，否则就需要recovery。

等到数据库开启后，储存在control file中的stop scn就会恢复为NULL值，此时表示datafile是open在正常模式下了。

http://blog.csdn.net/tianlesoftware/article/details/6286330

5. Copy the database files fromLinux-x86 to the Linux x86-64 system.

--复制所有的datafiles 从32位系统到64位系统上。

6. Copy your existing Oracleinitialization parameter file (initsid.ora) to the new Oracle home.

--复制初始化参数（pfile）到64位系统上对应的目录，默认是$ORACLE_HOME/dbs

7. Edit the parameter file topoint to the controlfiles if the location has change from the source machine.

--编辑pfile 文件，修改控制文件的保存位置。

8. Change any Oracle home pathreferences to use the new Oracle home path on the Linux x86-64 system. Likeuser_dump_dest , background_dump_dest , core_dump_dest etc

--修改pfile里的其他参数，比如user_dump_dest,background_dump_dest,core_dump_dest 等。

9. NOTE:This steps is required ifthe target system has different directory structure for database files whichcauses controlfile recreation Otherwise this step can be skipped.

--这一步是可选的操作，如果在64位系统上我们的文件目录结构和32位系统上的相同，那么可以跳过这个步骤。

Start up the database using SQL commandssimilar to the following

Example:
SQL> STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "SAMPLE" NORESETLOGS
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 112
LOGFILE
GROUP 1 '/lnx_x86-64_path/oracle/dbs/t_log1.f' size 25M
GROUP 2 '/lnx_x86-64_path/oracle/dbs/t_log2.f' size 25M
DATAFILE '/lnx_x86-64_path/oracle/dbs/t_db1.f'
CHARACTER SET WE8DEC
ALTER DATABASE OPEN

Note: In the previous example, the path values will vary depending on yoursystem.

--以上步骤就是重建控制文件，在控制文件里，我们修改了datafile 等参数的位置，所以如果目录相同，可以不用重建。

10. To change the word size of your release, enter the followingcommand:

--使用如下命令修改wordsize

If you are migrating an Oracle9i 9.2.0.xdatabase, run STARTUP MIGRATE:

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

SQL> STARTUP MIGRATE

If you are migrating an Oracle10g or 11gdatabase, run STARTUP UPGRADE:

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

SQL> STARTUP UPGRADE

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

--按照64位Oracle的格式，重新编译已经存在的PL/SQL 模块：

SQL> SPOOL mig32-64.log;
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
SQL> SPOOL OFF;

Check the spoolfile and verify that the packages and procedures compiled successfully. Correctany problems you find in this file.

--检查spool file，验证所有对象是否编译成功。

Handling instructions for JVM during upgrade

When migrating adatabase from 32 to 64bit (or vice versa) additional actions are required forjava. In theory the format of javashared data objects (SRO) is not compatible between 32 and 64 bit and so theseobjects need to be dropped and regenerated. In practice it may be the case prior to release 11 such objects couldinteroperate but if so this would only be by chance and should not be reliedon.

--当我们从32位迁移到64位时，或者从64到32位，需要对Java进行操作。理论上Java SRO的格式在32位和64位上不兼容，所以所有的对象需要先droped，在重新生成。但实际上，在Oracle 11g之前这些SRO 是可以相互操作的，但这也是偶然情况，所以还是需要重建这些Java SRO对象。

The steps to dothe regeneration are as follows. Theymay take several minutes to complete. They must be done connected as SYS.

--重新生成Java SRO的脚本如下，需要使用SYS 用户来执行，执行这个脚本需要一定的时间。

begin
update obj$ set status=5 whereobj#=(select obj# from obj$,javasnm$
    where owner#=0 and type#=29 andshort(+)=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 whereo.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 max number of iterations
      select count(*) into iterationsfrom obj$ where type#=56;
      exit when iterations=0 oriterations >= previous_iterations;
      previous_iterations := iterations;
      loop_count := 0;
      open C1;
      loop
        begin
          fetch C1 into ddl_statement;
          exit when C1%NOTFOUND orloop_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 fromjava$policy$shared$table');
update obj$ set status=1 whereobj#=(select obj# from obj$,javasnm$
    where owner#=0 and type#=29 andshort(+)=name and
   nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
commit;
end;
/

create or replace java system
/

Oracle recommends that you run the utlrp.sql script after running the utlirp.sqlscript. The utlrp.sql script recompiles all PL/SQL modules that might be in aninvalid state, including packages, procedures, and types. This is optional butOracle recommends that you do it during installation and not at a later date.To run the utlrp.sql script, enter the following command:

--执行utlrp.sql 脚本重新编译可能无效的对象，这步建议执行，脚本如下：

SQL> shutdown immediate;
SQL> startup (use appropriate command; outlined above, depending on yourversion)
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

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

--注意，如果在启用OLAP的情况，从32位转到64位时还可能遇到如下错误：

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] <>

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

(on 11g)

ORA-07445: exception encountered: core dump[_intel_fast_memcpy.A()+10] [SIGSEGV] < style="font-family: arialunicode ms;"> ( Run the Following query to check OLAP componentinstalled

SELECT COMP_NAME, STATUS FROM DBA_REGISTRY WHERE COMP_NAME LIKE ‘%OLAP%’)

Please refer the document to solve thisissue

--如果遇到这种问题，参考如下文档进行解决：

Note:352306.1 Upgrading OLAP from 32 to 64 bits
Note:332351.1 How To Remove or To Reinstall the OLAP Option

Note: At thispoint, the natively compiled Java objects in the new[wordsize changed] databaseare invalid. Refer to the section "Steps to Update Natively Compiled JavaObjects in the Database" for the procedure to revalidate these objects.

--注意，如果此时Java 对象还是无效的，参考如下方法来revalidate 这些对象：

Steps to Update NativelyCompiled Java Objects in the Database

To use nativelycompiled Java objects for Linux x86-64, use the following procedure to updatecompiled Java objects in the database:

（1） Connect to the database as userSYS or SYSTEM as SYSDBA [ You may get error ORA-1031 while running followingcommand if you are not connected as SYSDBA]
（2）Enter the following command:
（3）SQL> CREATE OR REPLACE JAVASYSTEM;

（4）It may be necessary to recompilethe Java objects with ncomp:

i.e. % ncomp-user scott/tiger Hello.class

12. Perform a clean shutdown of the database.

--clean shutdown DB

13. Perform a complete backup of the new 64-bit Oracle9i Release 2 (9.2)or Oracle10gr1 /10gr2/11gR1 database

--对64位的DB 进行备份，迁移操作结束。

小结：

这篇Blog里将的方法属于冷copy，即将整个datafiles拷贝到新的环境上，然后修改相关的信息，重建控制文件等操作。这只是32位到64位转换的一种方法，还有2种方法可以实现这种操作：

（1）使用RMAN Revoer，参考：

RMAN Restoring A32 bit Database to 64 bit - An Example [ID 467676.1]

（2）使用Expdp 和Impdp，参考：

How to UseExport and Import when Transferring Data Across Platforms or Across 32-bit and64-bit Servers [ID 277650.1]

具体会另篇演示这几种方法。

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

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823

DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940

Oracle Convert a 32bit Database to 64bit Database（32位 转到 64位）说明

Handling instructions for JVM during upgrade

小结：

Oracle Convert a 32bit Database to 64bit Database（32位转到 64位）说明