手工升级到Oracle 12C

这篇具有很好参考价值的文章主要介绍了手工升级到Oracle 12C。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、升级路线

  • 10.2.0.5,11.1.0.7,11.2.0.2以上版本可以直接升级到12c。
  • 10.2.0.5以前的版本和11.2.0.1版需要先升级到中间版本,再升级到12c。

二、环境说明

  • 操作系统:Red Hat 8 Linux 64位
  • 源数据库版本:Oracle 11.2.0.3
  • 目标数据库版本:Oracle 12.1.0.2

三、升级步骤简述

  • 备份源数据库(RMan)
  • 执行Pre-Upgrade Information Tool(preupgrd.sql)
  • 准备新版本的Oracle Home
  • 关闭数据库实例
  • 使用12c启动数据库,进入升级模式
  • 执行并行升级实用程序(catctl.pl)
  • 执行Post-Upgrade Status Tool(utlu121s.sql)
  • 执行附加脚本(catuppst.sql和utlrp.sql)
  • 完成升级阶段

四、升级步骤

4.1、备份数据库

4.2、执行Pre-Upgrade Information Tool

4.2.1、运行预升级工具脚本

使用目标版本数据库(Oracle 12C)目录/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/下的preupgrd.sql脚本。在源数据库的SQL*Plus内执行。

1 sqlplus /nolog
2 
3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 14 15:43:31 2023
4 
5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
6 
7 SQL> conn / as sysdba
8 Connected.
9 SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql

此工具是用来分析源数据库存在的问题,并生成用来生成解决问题的脚本。

Loading Pre-Upgrade Package...


***************************************************************************
Executing Pre-Upgrade Checks in RCAT...
***************************************************************************


      ************************************************************

                   ====>> ERRORS FOUND for RCAT <<====

 The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
                    prior to attempting your upgrade.
            Failure to do so will result in a failed upgrade.


 1) Check Tag:    PURGE_RECYCLEBIN
    Check Summary: Check that recycle bin is empty prior to upgrade
    Fixup Summary:
     "The recycle bin will be purged."

            You MUST resolve the above error prior to upgrade

      ************************************************************

      ************************************************************

               ====>> PRE-UPGRADE RESULTS for RCAT <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
 /u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
 /u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
 /u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql

      ************************************************************

***************************************************************************
Pre-Upgrade Checks in RCAT Completed.
***************************************************************************

***************************************************************************
***************************************************************************

以上结果提示,必须先清空源数据的回收站。

建议关注预升级检查记录:/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade.log。

4.2.2、升级前脚本

升级前在源环境运行升级前修复脚本:/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql,此脚本用来修复在源数据发现的问题。

1 SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2023-09-14 15:51:38  Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container RCAT

**********************************************************************
Check Tag:     EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary:   Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database

     In Oracle Database 12c, Database Control is removed during
     the upgrade. To save time during the Upgrade, this action
     can be done prior to upgrading using the following steps after
     copying rdbms/admin/emremove.sql from the new Oracle home
   - Stop EM Database Control:
    $> emctl stop dbconsole

   - Connect to the Database using the SYS account AS SYSDBA:

   SET ECHO ON;
   SET SERVEROUTPUT ON;
   @emremove.sql
     Without the set echo and serveroutput commands you will not 
     be able to follow the progress of the script.
**********************************************************************


**********************************************************************
Check Tag:     AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the 
     ORACLE_HOME/olap/admin/catnoamd.sql script before or 
     after the upgrade.
**********************************************************************


**********************************************************************
Check Tag:     PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fix Summary:   The recycle bin will be purged.
**********************************************************************
Fixup Succeeded
**********************************************************************


**********************************************************************
Check Tag:     APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
     manually upgraded prior to database upgrade

     APEX is currently at version 3.2.1.00.10 and will need to be
     upgraded to APEX version 4.2.5 in the new release.
     Note 1: To reduce database upgrade time, APEX can be manually
             upgraded outside of and prior to database upgrade.
     Note 2: See MOS Note 1088970.1 for information on APEX
             installation upgrades.
**********************************************************************


**********************************************************************
                      [Pre-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ********* Dictionary Statistics *********
                        *****************************************

Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

 1 fixup routine was successful.
 3 fixup routines returned INFORMATIONAL text that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************

提出的建议如下:

  • 发现了Enterprise Manager,在升级过程中会删除EM库,为了减少升级时间,可以先删除。已自动处理。
  • 发现了OLAP Catalog组件,在12C中不再支持OLAP Catalog组件,可以在升级前或升级后使用catnoamd.sql脚本删除,这里就不删除了,升级后再说。

  • 回收站是空的,不需要处理。如果回收站不是空的,将自动清空回收站。
  • 发现了APEX,APEX版本升级会占用比较多的时间,oracle建议可以手工升级,参考: 1088970.1,这里也不管了,一起升级。

  • 建议在数据库升级的24小时之前执行EXECUTE dbms_stats.gather_dictionary_stats;,收集数据字典统计信息。
1 SQL> EXECUTE dbms_stats.gather_dictionary_stats;
 4.2.3、升级后脚本

升级后在新环境运行升级后修复脚本:/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql,此脚本用来修复数据库升级后的问题。

4.3、准备12c Oracle Home

编辑/etc/oratab文件,将RCAT的环境变量指向Oracle 12C的Oracle Home。

1 [oracle@orasrv admin]$ vim /etc/oratab
rcat:/u01/app/oracle/product/12.1.0/dbhome_1:Y

4.4、关闭数据库实例

1 SQL> shutdown immediate

4.5、使用12c启动数据库,进入升级模式

1 SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 14 16:36:59 2023
2 
3 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
4 
5 SQL> conn / as sysdba
6 Connected to an idle instance.
7 SQL> startup upgrade
8 ORA-01078: failure in processing system parameters
9 LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initrcat.ora'

以上提示说明需要参数文件initrcat.ora,我们将11g数据库的参数文件复制到12c的相应目录

1 [oracle@orasrv ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilercat.ora /u01/app/oracle/product/12.1.0/dbhome_1/dbs/

再次启动

 1 SQL> startup upgrade

4.6、执行并行升级实用程序

1 SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC>                                 NOTE
DOC>
DOC>    The catupgrd.sql is being deprecated in the 12.1 release of the
DOC>    Oracle Database.  Customers are encouraged to use catctl.pl as
DOC>    the replacement for catupgrd.sql when upgrading the database dictionary.
DOC>
DOC>                    cd $ORACLE_HOME/rdbms/admin
DOC>                    $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
DOC>
DOC>    Refer to the Oracle Database Upgrade Guide for more information.
DOC>
DOC>    This database upgrade procedure must be called with the following
DOC>    argument when invoking from the SQL prompt:
DOC>
DOC>                    @catupgrd.sql PARALLEL=NO
DOC>
DOC>######################################################################
DOC>######################################################################
DOC>#
old   2: WHERE  UPPER('&&1') = 'PARALLEL=NO' OR
new   2: WHERE  UPPER('') = 'PARALLEL=NO' OR
old   3:        UPPER('&&1') = 'PARALLEL=YES'
new   3:        UPPER('') = 'PARALLEL=YES'
SELECT (to_number(count(*)))/(to_number(count(*))) FROM DUAL
                            *
ERROR at line 1:
ORA-01476: divisor is equal to zero
Disconnected
from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

在12c中,使用升级脚本catctl.pl代替了catupgrd.sql,升级脚本的执行方式也发生了变化。根据以上提示可知,在Linux环境下需按以下方式执行: 

[oracle@orasrv ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@orasrv admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 catupgrd.sql

 -n后参数2代表并行度为2,可按CPU核数进行设置。接下来就进入到漫长的升级过程: 

Argument list for [catctl.pl]
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Input Directory       d = 0
Phase Logging Table   t = 0
Log Dir               l = 0
Script                s = 0
Serial Run            S = 0
Upgrade Mode active   M = 0
Start Phase           p = 0
End Phase             P = 0
Log Id                i = 0
Run in                c = 0
Do not run in         C = 0
Echo OFF              e = 1
No Post Upgrade       x = 0
Reverse Order         r = 0
Open Mode Normal      o = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = /u01/app/oracle

Analyzing file catupgrd.sql
Log files in /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_4741.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus        = 2
SQL Process Count     = 2

------------------------------------------------------
Phases [0-73]
Serial   Phase #: 0 Files: 1     Time: 99s   
Serial   Phase #: 1 Files: 5     Time: 23s   
Restart  Phase #: 2 Files: 1     Time: 0s    
Parallel Phase #: 3 Files: 18    Time: 27s   
Restart  Phase #: 4 Files: 1     Time: 0s    
Serial   Phase #: 5 Files: 5     Time: 32s   
Serial   Phase #: 6 Files: 1     Time: 26s   
Serial   Phase #: 7 Files: 4     Time: 5s    
Restart  Phase #: 8 Files: 1     Time: 0s    
Parallel Phase #: 9 Files: 62    Time: 63s   
Restart  Phase #:10 Files: 1     Time: 0s    
Serial   Phase #:11 Files: 1     Time: 43s   
Restart  Phase #:12 Files: 1     Time: 0s    
Parallel Phase #:13 Files: 91    Time: 49s   
Restart  Phase #:14 Files: 1     Time: 0s    
Parallel Phase #:15 Files: 111   Time: 64s   
Restart  Phase #:16 Files: 1     Time: 0s    
Serial   Phase #:17 Files: 3     Time: 40s   
Restart  Phase #:18 Files: 1     Time: 0s    
Parallel Phase #:19 Files: 32    Time: 56s   
Restart  Phase #:20 Files: 1     Time: 0s    
Serial   Phase #:21 Files: 3     Time: 43s   
Restart  Phase #:22 Files: 1     Time: 0s    
Parallel Phase #:23 Files: 23    Time: 105s  
Restart  Phase #:24 Files: 1     Time: 0s    
Parallel Phase #:25 Files: 11    Time: 71s   
Restart  Phase #:26 Files: 1     Time: 0s    
Serial   Phase #:27 Files: 1     Time: 20s   
Restart  Phase #:28 Files: 1     Time: 0s    
Serial   Phase #:30 Files: 1     Time: 0s    
Serial   Phase #:31 Files: 257   Time: 51s   
Serial   Phase #:32 Files: 1     Time: 0s    
Restart  Phase #:33 Files: 1     Time: 0s    
Serial   Phase #:34 Files: 1     Time: 43s   
Restart  Phase #:35 Files: 1     Time: 0s    
Restart  Phase #:36 Files: 1     Time: 17s   
Serial   Phase #:37 Files: 4     Time: 74s   
Restart  Phase #:38 Files: 1     Time: 0s    
Parallel Phase #:39 Files: 13    Time: 55s   
Restart  Phase #:40 Files: 1     Time: 0s    
Parallel Phase #:41 Files: 10    Time: 46s   
Restart  Phase #:42 Files: 1     Time: 0s    
Serial   Phase #:43 Files: 1     Time: 44s   
Restart  Phase #:44 Files: 1     Time: 0s    
Serial   Phase #:45 Files: 1     Time: 38s   
Serial   Phase #:46 Files: 1     Time: 0s    
Restart  Phase #:47 Files: 1     Time: 0s    
Serial   Phase #:48 Files: 1     Time: 115s  
Restart  Phase #:49 Files: 1     Time: 0s    
Serial   Phase #:50 Files: 1     Time: 69s   
Restart  Phase #:51 Files: 1     Time: 0s    
Serial   Phase #:52 Files: 1     Time: 51s   
Restart  Phase #:53 Files: 1     Time: 0s    
Serial   Phase #:54 Files: 1     Time: 147s  
Restart  Phase #:55 Files: 1     Time: 0s    
Serial   Phase #:56 Files: 1     Time: 81s   
Restart  Phase #:57 Files: 1     Time: 0s    
Serial   Phase #:58 Files: 1     Time: 124s  
Restart  Phase #:59 Files: 1     Time: 0s    
Serial   Phase #:60 Files: 1     Time: 200s  
Restart  Phase #:61 Files: 1     Time: 0s    
Serial   Phase #:62 Files: 1     Time: 1016s 
Restart  Phase #:63 Files: 1     Time: 0s    
Serial   Phase #:64 Files: 1     Time: 32s   
Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err
returned from sqlpatch
    Time: 75s   
Serial   Phase #:66 Files: 1     Time: 36s   
Serial   Phase #:68 Files: 1     Time: 0s    
Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err
returned from sqlpatch
    Time: 259s  
Serial   Phase #:70 Files: 1     Time: 149s  
Serial   Phase #:71 Files: 1     Time: 0s    
Serial   Phase #:72 Files: 1     Time: 0s    
Serial   Phase #:73 Files: 1     Time: 90s    

Grand Total Time: 3586s 

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/rcat/upgrade/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:59m:46s]

升级完成后,数据库实例自动关闭,需要重新启动数据库到open状态

SQL> startup

继续执行4.2.3所述升级后修复脚本

1 SQL> @/u01/app/oracle/cfgtoollogs/rcat/preupgrade/postupgrade_fixups.sql 
Post Upgrade Fixup Script Generated on 2023-09-14 15:51:38  Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...

**********************************************************************
Check Tag:     OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use

     Database is using a time zone file older than version 18.
     After the upgrade, it is recommended that DBMS_DST package
     be used to upgrade the 12.1.0.2.0 database time zone version
     to the latest version which comes with the new release.
     Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************


**********************************************************************
Check Tag:     NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT be upgraded
Fix Summary:   This fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.  
If you want to upgrade those other components, you must do so manually.
**********************************************************************


**********************************************************************
                     [Post-Upgrade Recommendations]
**********************************************************************

                        *****************************************
                        ******** Fixed Object Statistics ********
                        *****************************************

Please create stats on fixed objects two weeks
after the upgrade using the command:
   EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^


           **************************************************
                ************* Fixup Summary ************

 2 fixup routines generated INFORMATIONAL messages that should be reviewed.

*************** Post Upgrade Fixup Script Complete ********************

PL/SQL procedure successfully completed.

提出的建议如下:

  • 使用旧的Timezone文件版本,数据库升级完成后进行Timezone版本升级。
  • 确认没有升级的现有组件,忽略。
  • 在数据库升级后的两周内运行EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;收集对象的统计信息。
1 SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

4.7、执行Post-Upgrade Status Tool

升级完成后可以执行Post-Upgrade Status Tool($ORACLE_HOME/rdbms/admin/utlu121s.sql),查看升级的概况信息。

1 SQL> @?/rdbms/admin/utlu121s.sql
Oracle Database 12.1 Post-Upgrade Status Tool           09-15-2023 10:47:44

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.1.0.2.0  00:18:54
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:01:34
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:00:52
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:30
OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00
Oracle OLAP API                           VALID      12.1.0.2.0  00:00:11
Oracle XDK                                VALID      12.1.0.2.0  00:00:48
Oracle Text                               VALID      12.1.0.2.0  00:00:54
Oracle XML Database                       VALID      12.1.0.2.0  00:01:12
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:08
Oracle Multimedia                         VALID      12.1.0.2.0  00:01:45
Spatial                                UPGRADED      12.1.0.2.0  00:03:02
Oracle Application Express                VALID     4.2.5.00.08  00:15:53
Final Actions                                                    00:01:56
Post Upgrade                                                     00:02:20

除了OLAP Catalog组件外,其它组件都升级到希望的版本。

因不再被12c支持,可以移除OLAP Catalog组件。

1 SQL> @?/olap/admin/catnoamd.sql

4.8、执行附加脚本

执行catuppst.sql脚本完成接下来的升级操作,它不要求数据库处于升级模式。

1 SQL> @?/rdbms/admin/catuppst.sql

执行utlrp.sql脚本重新编译PL/SQL和Java代码。

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

等待执行完成。

4.9、完成升级阶段

4.9.1、检查环境变量
  • ORACLE_BASE
  • ORACLE_HOME
  • PATH,LD_LIBRARY_PATH and SHLIB_PATH
  • 更新oratab文件
4.9.2、检查初始化参数文件

确认初始化参数修改正确。

4.9.3、检查口令文件

可以使用orapwd命令重建口令文件。

4.9.4、COMPATIBLE参数

COMPATIBLE参数控制数据库兼容性级别,如果数据库不会再降级到以前的版本,可以设置此参数。

1 SQL> alter system set compatible='12.1.0.2.0' scope=spfile;

重启数据库实例。

4.9.5、升级TIMEZONE文件版本

 查看数据库当前timezone 版本:

 1 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
2    FROM DATABASE_PROPERTIES 3 WHERE PROPERTY_NAME LIKE 'DST_%' 4 ORDER BY PROPERTY_NAME; 5 PROPERTY_NAME           VALUE 6 ---------------------------  --------------------------- 7 DST_PRIMARY_TT_VERSION     11
8
DST_SECONDARY_TT_VERSION    0 9 DST_UPGRADE_STATE        NONE

1 SQL> SELECT version FROM v$timezone_file;
2 
3    VERSION
4 ----------
5         11

注意:11.2.0.1.0timezone最高支持到11,在12.1.0.2中最高支持到18。

准备升级timezone到18

1 SQL> set serveroutput on
2 SQL> exec DBMS_DST.BEGIN_PREPARE(18);

 查看升级准备信息

 1 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
 2   2  FROM DATABASE_PROPERTIES
 3   3  WHERE PROPERTY_NAME LIKE 'DST_%'
 4   4  ORDER BY PROPERTY_NAME;
 5 
 6 PROPERTY_NAME            VALUE
 7 -----------------------------  --------------------------
 8 DST_PRIMARY_TT_VERSION         11
 9 DST_SECONDARY_TT_VERSION        18
10 DST_UPGRADE_STATE           PREPARE

准备升级工作

 1 SQL> BEGIN
 2   2    DBMS_DST.FIND_AFFECTED_TABLES
 3   3    (affected_tables => 'sys.dst$affected_tables',
 4   4    log_errors => TRUE,
 5   5    log_errors_table => 'sys.dst$error_table');
 6   6    END;
 7   7  /
 8 
 9 PL/SQL procedure successfully completed.
10 
11 SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
12 
13 Table truncated.
14 
15 SQL> TRUNCATE TABLE sys.dst$affected_tables;
16 
17 Table truncated.
18 
19 SQL> TRUNCATE TABLE sys.dst$error_table;
20 
21 Table truncated.

结束升级准备

1 SQL> EXEC DBMS_DST.END_PREPARE;
2 
3 PL/SQL procedure successfully completed.

升级过程

 1 SQL> shutdown immediate;
 2 Database closed.
 3 Database dismounted.
 4 ORACLE instance shut down.
 5 
 6 SQL> startup upgrade;
 7 ORACLE instance started.
 8 
 9 Total System Global Area 1610612736 bytes
10 Fixed Size                  2924928 bytes
11 Variable Size             671092352 bytes
12 Database Buffers          922746880 bytes
13 Redo Buffers               13848576 bytes
14 Database mounted.
15 Database opened.
16 
17 SQL> set serveroutput on
18 SQL> purge dba_recyclebin;
19 
20 DBA Recyclebin purged.
21 
22 SQL> alter session set "_with_subquery"=materialize;
23 
24 Session altered.
25 
26 SQL> EXEC DBMS_DST.BEGIN_UPGRADE(18);      
27 An upgrade window has been successfully started.
28 
29 PL/SQL procedure successfully completed.
30 
31 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
32   2  FROM DATABASE_PROPERTIES
33   3  WHERE PROPERTY_NAME LIKE 'DST_%'
34   4  ORDER BY PROPERTY_NAME;
35 
36 PROPERTY_NAME                               VALUE
37 --------------------------------------    --------------------------------------
38 DST_PRIMARY_TT_VERSION                    18
39 DST_SECONDARY_TT_VERSION                 11
40 DST_UPGRADE_STATE                          UPGRADE
 1 SQL> shutdown immediate;
 2 Database closed.
 3 Database dismounted.
 4 ORACLE instance shut down.
 5 SQL>          
 6 SQL> startup
 7 ORACLE instance started.
 8 
 9 Total System Global Area 1610612736 bytes
10 Fixed Size                  2924928 bytes
11 Variable Size             671092352 bytes
12 Database Buffers          922746880 bytes
13 Redo Buffers               13848576 bytes
14 Database mounted.
15 Database opened.
16 SQL> alter session set "_with_subquery"=materialize;
17 
18 Session altered.
19 执行timezone升级过程:
20 SQL> set serveroutput on
21 SQL> VAR numfail number
22 SQL> BEGIN
23   2      DBMS_DST.UPGRADE_DATABASE(:numfail,
24   3      parallel => TRUE,
25   4      log_errors => TRUE,
26   5      log_errors_table => 'SYS.DST$ERROR_TABLE',
27   6      log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
28   7      error_on_overlap_time => FALSE,
29   8      error_on_nonexisting_time => FALSE);
30   9      DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
31  10     END;
32  11     /
33 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
34 Number of failures: 0
35 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
36 Number of failures: 0
37 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
38 Number of failures: 0
39 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
40 Number of failures: 0
41 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
42 Number of failures: 0
43 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
44 Number of failures: 0
45 Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
46 Number of failures: 0
47 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
48 Number of failures: 0
49 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
50 Number of failures: 0
51 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
52 Number of failures: 0
53 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
54 Number of failures: 0
55 Failures:0
56 
57 PL/SQL procedure successfully completed.
58 结束升级,校验升级信息:
59 SQL> VAR fail number
60 SQL> BEGIN
61   2    DBMS_DST.END_UPGRADE(:fail);
62   3    DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
63   4    END;
64   5    /
65 An upgrade window has been successfully ended.
66 Failures:0
67 
68 PL/SQL procedure successfully completed.

确认升级成功

 1 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value 
 2   2  FROM DATABASE_PROPERTIES
 3   3  WHERE PROPERTY_NAME LIKE 'DST_%'
 4   4  ORDER BY PROPERTY_NAME;
 5 
 6 PROPERTY_NAME              VALUE
 7 -------------------------  ----------------------------
 8 DST_PRIMARY_TT_VERSION     18
 9 DST_SECONDARY_TT_VERSION   0
10 DST_UPGRADE_STATE          NONE
1 SQL> SELECT * FROM v$timezone_file;
2 
3 FILENAME                VERSION     CON_ID
4 -------------------- ---------- ----------
5 timezlrg_18.dat              18          0

 4.9.6、升级RMAN Recovery Catalog

如果当前库上有RMAN Catalog,可以使用UPGRADE CATALOG进行升级。

参考:https://docs.oracle.com/database/121/BRADV/rcmcatdb.htm#BRADV188

4.9.7、为Oracle XML DB配置FTP和HTTP端口以及HTTP身份验证

Oracle Database 12c DBCA不为Oracle XML DB配置端口,因此您必须手动配置它们。您还应该为HTTP配置摘要身份验证,以利用改进的安全功能。

4.9.8、启用Database Vault

Register Database Vault by using the DVSYS.DBMS_MACADM.ENABLE_DV procedure.文章来源地址https://www.toymoban.com/news/detail-709784.html

4.9.9、打开统一审计

4.9.10、重新注册到云控制台

到了这里,关于手工升级到Oracle 12C的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【Oracle】Linux——Centos7安装Oracle12c

    官方网站:https://www.oracle.com 历史版本下载地址:https://edelivery.oracle.com/ (需要登录) 如果官方下载有问题,使用百度网盘:链接: https://pan.baidu.com/s/101U3P3KYUQ5p_zsAP1aCfw?pwd=6666 提取码: 6666 添加oinstall、dba 组,创建oracle用户,设置oracle用户密码(练习的话,为了方便记忆,建议不

    2024年03月20日
    浏览(59)
  • Docker 安装oracle12c容器并创建新用户

    下载镜像 启动镜像 8080和22端口没有映射出来,有需要自己 正常日志 启动报错日志 原因 容器没有操作主机文件夹权限 主机内执行 进入容器内并以dba登录Oracle 创建表空间及用户和赋权

    2024年02月08日
    浏览(39)
  • Windows下 Oracle 12c 安装保姆级图文详解

    Windows下 Oracle 12c 安装步骤如下: 1、将压缩包“winx64_12c_database_1of2.zip“和“winx64_12c_database_2of2.zip”解压到同一目录“database”目录。 2、双击“database”目录下的“setup.exe\\\",软件会加载并初步校验系统是否可以达到了数据库安装的最低配置,如果达到要求,就会直接加载程序并

    2024年02月10日
    浏览(40)
  • 4. Oracle12c数据库在Linux系统安装步骤

    1.Oracle11gR2数据库安装 https://edu.csdn.net/course/detail/27750 2.Oracle 12c 数据库安装 https://edu.csdn.net/course/detail/35882 ​ 3.Oracle 19c 数据库一键安装 https://edu.csdn.net/course/detail/39198 4.Oracle 19c RAC For Linux安装部署 (视频讲解) https://edu.csdn.net/course/detail/35792 5.oracle 19c 数据库基础和日常管理

    2024年02月15日
    浏览(45)
  • docker快速部署oracle19c、oracle12c,测试环境问题复现demo快速搭建笔记

    (复制sql,替换表名执行完毕后,再修改自己想要的字段即可) (复制sql,替换自己的表名) 一个oracle表示一个实例,一个实例可以配置多个服务,独立维护的oracle服务 一个服务内可以有多个表空间,默认表空间就有很多,比如常见的SYSTEM、TEMP、USERS 常见的默认角色: 1、

    2024年02月04日
    浏览(60)
  • oracle 12c 容器数据库公共用户去访问pdb数据库

    在oracle 12c之前数据库和数据库实例之间的关系是多对一或者一对一的关系,在oracle 12c 中引进了多租户的概念,实现了数据库实例可以对应多个数据库。cdb代表的是容器数据库,pdb代表的是可插拔数据库,实际上就是传统的数据库.而且现在也衍生了两种用户,一种是cbd用户,

    2024年02月01日
    浏览(44)
  • 好文分享 | 记一次Oracle12c数据库SQL短暂缓慢问题分析

    本文为墨天轮社区作者 张sir 原创作品,记录了日常运维Oracle数据库过程中遇到的一个慢SQL问题的解决、优化过程,文章内容全面具体、分析到位,且含有经验总结,分享给各位。 这次出问题的数据库比较特殊,承接的系统交易要求很高,SQL基本都是短平快,响应时间基本不

    2024年02月05日
    浏览(53)
  • oracle 12c和plsql的详细安装和配置过程(超级详细,小白也能懂)

    oracle 12c和plsql的详细安装和配置过程 Oracle 12c 的压缩包连接如下: 链接:https://pan.baidu.com/s/1xTvjnXsKysmRb18-QUXRkA 提取码:4a9j 1解压,打开文件双击\\\"setup.exe\\\" 2去掉勾选,下一步 3点击”是” 4点击”下一步” 5点击”下一步” 6选择”单实例数据库安装”,点击”下一步” 7选择”高级安

    2023年04月10日
    浏览(44)
  • 【SQL开发实战技巧】系列(四十九):Oracle12C常用新特性☞表分区部分索引(Partial Indexes)

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2024年02月05日
    浏览(40)
  • Oracle 19C 单机环境升级RU(19.3升级至19.12)

    📢📢📢📣📣📣 哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】!😜😜😜 中国DBA联盟(ACDU)成员,目前服务于工业互联网 擅长主流Oracle、MySQL、PG、高斯及GP 运维开发,备份恢复,安装迁移,性能优化、

    2024年02月07日
    浏览(87)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包