程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 將oracle數據庫從32位平台遷移到64位

將oracle數據庫從32位平台遷移到64位

編輯:Oracle教程

將oracle數據庫從32位平台遷移到64位


客戶的32位oracle數據庫系統磁盤出現損壞,慶幸的是oracle數據庫完好無損。客戶要求將數據庫遷移到新購的設備上,新設備內存為64G,系統REDHAT 6.2 64位,直接拷貝數據文件肯定是不行的,因為oracle 的存儲過程在32位和64位平台下的wordsize不同,雖然用戶的存儲過程可以在使用時自行完成重新編譯,但oracle的系統存儲過程確需要我們來手工轉換。

總結,遷移過程如下:

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腳本會將存儲過程等置為失效

<span style="white-space:pre">	</span>--如果遷移的是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;
<pre name="code" class="sql">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對象

<pre name="code" class="sql">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操作,以免造成死鎖

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved