一聚教程网：一个值得你收藏的教程网站

ps教程| flash教程| 路由器设置| Jsp教程| 加入收藏

PHP教程 Css教程 操作系统 数据库 安卓下载

首页 游戏下载 软件下载 专题 资讯教程 php教程 asp.net教程 css教程 网页制作 网页特效 手机开发 办公数码
+ -
当前位置：一聚教程网 > 数据库 > Oracle教程

推荐专题

最新下载

热门教程

oracle数据库如何从32位平台迁移到64位？

时间：2022-06-29 09:55:40 编辑：袖梨 来源：一聚教程网

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位的机器了，如果有同学是按照些步骤操作的，祝迁移成功

相关文章

热门栏目

一聚教程网| 关于我们| 联系我们| 广告合作| 友情链接| 版权声明

copyRight@2007-2022 www.111CN.NET AII Right Reserved

网站内容来自网络整理或网友投稿如有侵权行为请邮件:[email protected] 我们24小时内处理