oracle資料庫如何從32位平台遷移到64位？

1：備份oracle資料庫

2：備份控制檔案到trace（以備需要建立控制檔案）

代碼如下

複製代碼

SQL> alter database backup controlfile to trace; Database altered. SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /u01/app/admin/easy/udump/easy_ora_18830.trc SQL> host cat /u01/app/admin/easy/udump/easy_ora_18830.trc ...... -- --     Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "EASY" NORESETLOGS  NOARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '/oradata/easy/redo01.log'  SIZE 11200K,   GROUP 2 '/oradata/easy/redo02.log'  SIZE 11200K -- STANDBY LOGFILE DATAFILE   '/oradata/easy/system01.dbf',   '/oradata/easy/undotbs01.dbf',   '/oradata/easy/sysaux01.dbf',   '/oradata/easy/users01.dbf' CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/easy/temp01.dbf'      SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; -- End of tempfile additions. -- --     Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "EASY" RESETLOGS  NOARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '/oradata/easy/redo01.log'  SIZE 11200K,   GROUP 2 '/oradata/easy/redo02.log'  SIZE 11200K -- STANDBY LOGFILE DATAFILE   '/oradata/easy/system01.dbf',   '/oradata/easy/undotbs01.dbf',   '/oradata/easy/sysaux01.dbf',   '/oradata/easy/users01.dbf' CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/easy/temp01.dbf'      SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; -- End of tempfile additions. --

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操作，以免造成死結。

通過以上11步，資料庫就能成功遷移到64位的機器了，如果有同學是按照些步驟操作的，祝遷移成功