Oracle 10.2.0.1 rolling upgrade oracle 10.2.0.5 by Logical Standby

最新推荐文章于 2024-09-11 07:16:42 发布

dbatravel

最新推荐文章于 2024-09-11 07:16:42 发布

阅读量913

点赞数

分类专栏： Oracle Upgrade 文章标签： oracle database archive sql manager patch

本文链接：https://blog.csdn.net/dbatravel/article/details/7982127

版权

Oracle Upgrade 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了如何通过逻辑备用库将Oracle数据库从10.2.0.1版本平滑升级到10.2.0.5版本。升级过程包括创建归档日志存储目录，修改数据库参数，如增大java_pool_size，将数据库切换到升级模式，应用日志，以及解决升级过程中遇到的bug。最后，成功切换回主库并确保业务正常运行，标志着升级顺利完成。

摘要由CSDN通过智能技术生成

1、创建物理dg
2、物理dg转为逻辑dg
       a. 在备库上停止管理恢复进程
           sql> alter database recover managed standby database cancel;
       b. 主库上生成逻辑备库所需的数据字典信息
           sql> exec dbms_logstdby.build –-主库执行
       c. 切换物理备库至逻辑备库
           Sql> alter database recover to logical standby lstydb;

d. 主备库修改参数

1.创建存放primary传送过来的归档日志文件

mkdir d:\archive_standby

2. 主添加：

                log_archive_dest_3='location=D:\archive_standby valid_for=(standby_logfiles,standby_role) db_unique_name=primary'
                LOG_ARCHIVE_DEST_STATE_3=enable
           3. 备添加
                log_archive_dest_3='location=D:\archive_standby valid_for=(standby_logfiles,standby_role) db_unique_name=standby'
                LOG_ARCHIVE_DEST_STATE_3=enable

3、升级逻辑dg
          升级软件，安装10.2.0.5

升级数据库

增大java_pool大小

SQL> alter system set java_pool_size='150M' scope=spfile;

启动到升级模式

           SQL> STARTUP UPGRADE

          SQL> SPOOL patch.log

          SQL>@?/rdbms/admin/catupgrd.sql //此脚本执行时间大概需要20分钟左右

          Total Upgrade Time: 00:24:46 //设置JAVA_POOL_SIZE为150M时的升级所用时间

          Total Upgrade Time: 01:06:39 //不设置JAVA_POOL_SIZE为150M时的升级所用时间,所以，大家一定要记得设置这个参数啊！！

SQL>SPOOL OFF

          SQL>shutdown immediate

          SQL> startup

SQL> @?/rdbms/admin/utlrp.sql

    查看组建状态
          SQL> col comp_name format a35
          SQL> col version format a10
          SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

                     COMP_NAME VERSION STATUS
                     ----------------------------------- ---------- ----------------------
                    Oracle Enterprise Manager 10.2.0.5.0 VALID
                    Spatial 10.2.0.5.0 VALID
                    Oracle interMedia 10.2.0.5.0 VALID
                    OLAP Catalog 10.2.0.5.0 VALID
                    Oracle XML Database 10.2.0.5.0 VALID
                    Oracle Text 10.2.0.5.0 VALID
                    Oracle Expression Filter 10.2.0.5.0 VALID
                    Oracle Rule Manager 10.2.0.5.0 VALID
                    Oracle Workspace Manager 10.2.0.5.0 VALID
                    Oracle Data Mining 10.2.0.5.0 VALID
                    Oracle Database Catalog Views 10.2.0.5.0 VALID
                    Oracle Database Packages and Types 10.2.0.5.0 VALID
                    JServer JAVA Virtual Machine 10.2.0.5.0 VALID
                    Oracle XDK 10.2.0.5.0 VALID
                    Oracle Database Java Packages 10.2.0.5.0 VALID
                    OLAP Analytic Workspace 10.2.0.5.0 VALID

Oracle OLAP API 10.2.0.5.0 VALID

应用日志

                  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
4、切换

            1、SELECT SWITCHOVER_STATUS FROM V$DATABASE;    --查看状态
            2、ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;    --主库转换准备
            3、ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;                      --备库转换准备
            4、ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;       --主库转换
            5、ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;                          --备库转换
            6、ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;               --新主库开启应用日志

5、升级主库

按照步骤三执行

在执行catupgrd.sql 遇到bug

          Submitting DBMS jobs.
          BEGIN
          *
          ERROR at line 1:
          ORA-01031: insufficient privileges
          ORA-06512: at "SYS.DBMS_INTERNAL_LOGSTDBY", line 133
          ORA-06512: at "SYS.DBMS_JOB", line 125
          ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 817
          ORA-06512: at "SYSMAN.EMD_MAINTENANCE", line 907
          ORA-06512: at line 2
          解决方法
          1. Identify the file containing the Enterprise Manager patch script:
          SQL> SELECT dbms_registry_sys.patch_script('EM') AS PATCH_FILE FROM DUAL;
          PATCH_FILE
          --------------------------------------------------------------------------------
          ?/sysman/admin/emdrep/sql/empatch.sql

         2. Comment out this line in that PATCH_FILE under the ORACLE_HOME
         where catupgrd.sql will be run.
         To do this, change:
         @&EM_SQL_ROOT/core/latest/admin/admin_submit_dbms_jobs.sql
         To:
         --@&EM_SQL_ROOT/core/latest/admin/admin_submit_dbms_jobs.sql
        重新执行catupgrd.sql

6、切换

安装步骤4 切换过来

7、业务正常运行

升级完成