Oracle Convert a 32-bit Database to 64-bit Database（32位 轉到 64位）說明

       Oracle資料分32位和64位，對應不同的平台，關于檢視DB 是32還是64位，我之前的Blog 有說明，參考：

       檢視Oracle 是32位還是64位的方法

http://www.cndba.cn/Dave/article/1130

       我們這裡說的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://www.cndba.cn/Dave/article/1216

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在正常模式下了。

更多資訊參考：

Oracle 執行個體恢複時 前滾（roll forward） 後滾（rollback） 問題

http://www.cndba.cn/Dave/article/1410

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

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

具體會另篇示範這幾種方法。

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

版權所有，文章允許轉載，但必須以連結方式注明源位址，否則追究法律責任!

QQ:492913789

Email:[email protected]

Blog:  http://www.cndba.cn/dave

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