暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

【Oracle升级】Oracle脚本升级11g to 19c non-CDB

原创 恩强Boy 2020-12-24
2128

一、环境说明

source DB version: 11.2.0.4(单机)
target DB version: 19c non-CDB
OS: Redhat Linux 7.6
11g_ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
19c_ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
19c数据库软件已经完成安装

二、升级路线

想要升级到19c,必须按照以下路线进行升级

1.直接升级路线

image.png

2.间接升级路线

image.png

升级前准备

一、源端环境需求和检查

  • 在升级之前,确保Oracle提供的所有数据库组件、对象在源数据库中都是有效的;
  • 在升级或降级之前,Oracle强烈建议将数据库版本打上最新的补丁(PSU/RU);
  • 源库时区应小于或等于目标库时区版本;
  • 确保在升级前,源库有一个有效的备份或者创建有效还原点。
  • 升级前禁用在DDL语句之前/之后执行的自定义触发器,升级后再启用;
  • 在升级前,检查数据库服务器升级/降级的兼容性;
  • 在升级期间开启归档。Oracle建议打开归档日志,用于DBU在升级过程创建和更新日志;
  • 如果是Oracle RAC,如果您使用DBUA升级数据库,则必须将cluster_database参数设置为TRUE;如果您使用脚本升级,必须将cluster_database参数设置为false,等待完成升级后再设置为true;
  • 在升级之前确保运行预升级工具;
  • 检查并遵循预升级日志中给出的建议;
  • 在升级前源库应停止物化视图;
  • 禁用jobs/crontab定时任务;
  • 升级19c时,源库的COMPATIBLE参数最小值为”11.2.0”,确保已经将COMPATIBLE设置为11.2.0或更高;

二、目标端环境需求和检查

  • 确保你的操作系统/平台格式满足19c的要求
  • 下载并在新的ORACLE_HOME中安装Oracle 19c
  • 在MOS上下载并安装最新的RU或RUR
  • 环境变量确保已经设置了ORACLE_HOME,PATH,LD_LIBRARY_PATH等信息指向19c

三、预升级

执行以下命令:
(命令格式如下)

$ $11g_ORACLE_HOME/jdk/bin/java -jar $19c_ORACLE_HOME/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir] 

参数说明:

[FILE|TERMINAL]: 指定生成文件是到文件还是终端,默认为FILE
[TEXT|XML]: 指定日志应用格式,默认为TEXT
[DIR]: 日志在<output_dir>下创建。如果不指定输出目录dir选项,那么将会在以下默认目录之一创建日志:
如果你定义了ORACLE_BASE,日志会被创建在$ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/,否则会创建在
$ORACLE_HOME/cfgtoollogs/db_name/preupgrade/

例:
源端ORACLE_HOME:/u01/app/oracle/product/11.2.0/db_1
目标端ORACLE_HOME:/u01/app/oracle/product/19.0.0/dbhome_1
执行以下命令

$ export ORACLE_SID=orcl
$ echo ORACLE_BASE=/u01/app/oracle
$ echo ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT

输出总结如下:

==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2020-12-16T11:16:39

根据上面的输出,在升级时候要遵守preupgrade.log里面的建议。整个升级过程都在严格遵守preupgrade.log的建议。
日志中并指出,在升级前执行preupgrade_fixups.sql脚本,在升级后执行postupgrade_fixups.sql脚本。
preupgrade.log报告如下,需要根据报告的建议进行修改

1.更新初始化参数

更新初始化参数以满足最小值

常见的需要更改的参数有memory_target、processes

SQL> alter system set processes=300 scope=spfile;
SQL> shutdown immediate;
SQL> startup;

2.移除EM DB Control

从19c的ORACLE_HOME中复制$ORACLE_HOME/rdbms/admin/emremove.sql脚本到11g的ORACLE_HOME相应位置。

$ cd /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/
$ cp emremove.sql $ORACLE_HOME/rdbms/admin/

第一步:如果配置了em,需要停止em

$ emctl stop dbconsole

第二步:使用sys用户执行以下命令

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @emremove.sql

执行完成后,必须手动删除以下两个目录:

ORACLE_HOME/HOSTNAME_SID
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID

如果没有设置echo和serveroutput命令,我们将无法跟踪脚本的进度。从Oracle 12C开始,本地的EM控件不再存在。在升级期间,知识库将从数据库中删除。可以在升级之前执行此步骤,以减少停机的时间。

3.移除OLAP目录

通过运行11g的以下脚本来移除OLAP目录组件(AMD)

$ORACLE_HOME/olap/admin/catnoamd.sql

从Oracle 12c开始,OLAP目录组件(AMD)被删除。如果存在,将在数据库升级期间标记为option off。Oracle建议在数据库升级之前删除OLAP目录,可以在升级之前执行此操作,以减少停机时间。

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @catnoamd.sql

4.忽略APEX升级

5.收集数据字典统计信息

执行以下存储过程:

SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;

数据字典统计信息可以帮助Oracle优化器找到有效的SQL执行计划。Oracle建议在升级数据库之前的24小时内完成收集字典统计信息。

6.包含在AUTOFIXUP中

7.扩展列出的表空间,或者设置为自动扩展

                                           Min Size
Tablespace                        Size     For Upgrade
----------                     ----------  -----------
SYSAUX                             760 MB       982 MB
SYSTEM                             780 MB      1206 MB
UNDOTBS1                           110 MB       446 MB

8.删除EXF和RUL

运行以下脚本:

$ORACLE_HOME/rdbms/admin/catnoexf.sql

从Oracle 12c版本开始,表达过滤器(EXF)和数据库规则管理器(RUL)特性被取消,并在升级的过程中被删除。可以在升级之前手动执行此步骤,以减少停机时间。

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @catnoexf.sql

9.检查归档日志目录是否还有4950M空间可用,用于升级过程产生的归档日志。

10.升级RMAN恢复目录

如果您使用的恢复目录模式的版本比RMAN客户端的所需的版本更低,需要手动升级RMAN恢复目录。

11.执行修复脚本preupgrade_fixups.sql

SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

四、检查无效的对象、组件

(检查数据库注册信息)

SQL> set pagesize500 
SQL> set linesize 100 
SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry  order by comp_name; 
COMP_NAME                                STATUS      VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine             VALID       11.2.0.4.0
OLAP Analytic Workspace                  VALID       11.2.0.4.0
OLAP Catalog                             VALID       11.2.0.4.0
OWB                                      VALID       11.2.0.4.0
Oracle Application Express               VALID       3.2.1.00.1
Oracle Database Catalog Views          	 VALID       11.2.0.4.0
Oracle Database Java Packages            VALID       11.2.0.4.0
Oracle Database Packages and Types       VALID       11.2.0.4.0
Oracle Enterprise Manager                VALID       11.2.0.4.0
Oracle Expression Filter                 VALID       11.2.0.4.0
Oracle Multimedia                        VALID       11.2.0.4.0
Oracle OLAP API                          VALID       11.2.0.4.0
Oracle Rules Manager                     VALID       11.2.0.4.0
Oracle Text                              VALID       11.2.0.4.0
Oracle Workspace Manager                 VALID       11.2.0.4.0
Oracle XDK                               VALID       11.2.0.4.0
Oracle XML Database                      VALID       11.2.0.4.0
Spatial                                  VALID       11.2.0.4.0

(检查无效对象)

SQL> select substr(object_name,1,40) object_name,substr(owner,1,15),owner,object_type from dba_objects where status='INVALID' order by owner, object_type;

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; 

或者执行下面存储过程,也可以检查无效的对象

SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_PREUP.INVALID_OBJECTS;

如果发现无效的对象和数据库组件,需要对无效对象进行编译,执行以下SQL:

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

五、确保在升级前物化视图刷新完成

在Oracle升级前,必须等待所有物化视图完成刷新。可以执行此存储过程进行查询,以确定是否所有的物化视图是否刷新完成。

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 

六、在升级前确保没有进行备份和恢复任务

执行以下SQL

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
SQL> SELECT * FROM v$recover_file;

七、在升级前清理回收站

SQL> PURGE DBA_RECYCLEBIN; 

也可以通过DBUA清理回收站

八、检查password_version

$ sqlplus / as sysdba
SQL> select username,password_versions from dba_users;

执行结果如果有10g版本,建议参考oracle文档来修改10g版本,否则升级后所有的用户将会被锁定。

升级数据库

1.关闭db和监听

$ lsnrctl stop
$ sqlplus / as sysdba
SQL> shutdown immediate

2.复制监听文件

将配置文件从11g ORACLE_HOME复制到19c ORACLE_HOME目录下

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ cp *.ora /u01/app/oracle/product/19.0.0/dbhome_1/network/admin

3.传输密码文件和参数文件到19c ORACLE_HOME目录下

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
$ cp spfileorcl.ora orapworcl /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

4.修改环境变量

将ORACLE_HOME指向19c目录

5.检查监听文件路径,修改成19c路径,开启监听

$ lsnrctl start
$ lsnrctl status

6.升级模式起库

$ sqlplus / as sysdba
SQL> startup upgrade

7.运行升级脚本

(后者是前者的简写)
#Regular upgrade command.

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

#Shorthand command.
$ORACLE_HOME/bin/dbupgrade

输出日志如下:

Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20201223165934]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20201223165934/catupgrd_catcon_28030.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201223165934/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20201223165934/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 4
Database Name         = orcl
DataBase Version      = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20201223165944/catupgrd_catcon_28030.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20201223165944/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20201223165944/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20201223165944]

Parallel SQL Process Count            = 4
Components in [orcl]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]

------------------------------------------------------
Phases [0-107]         Start Time:[2020_12_23 16:59:57]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [orcl] Files:1    Time: 372s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [orcl] Files:5    Time: 186s
Restart  Phase #:2    [orcl] Files:1    Time: 3s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [orcl] Files:19   Time: 59s
Restart  Phase #:4    [orcl] Files:1    Time: 2s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [orcl] Files:7    Time: 51s
*****************   Catproc Start   ****************
Serial   Phase #:6    [orcl] Files:1    Time: 31s
*****************   Catproc Types   ****************
Serial   Phase #:7    [orcl] Files:2    Time: 32s
Restart  Phase #:8    [orcl] Files:1    Time: 2s
****************   Catproc Tables   ****************
Parallel Phase #:9    [orcl] Files:67   Time: 89s
Restart  Phase #:10   [orcl] Files:1    Time: 3s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [orcl] Files:1    Time: 284s
Restart  Phase #:12   [orcl] Files:1    Time: 5s
**************   Catproc Procedures   **************
Parallel Phase #:13   [orcl] Files:94   Time: 34s
Restart  Phase #:14   [orcl] Files:1    Time: 4s
Parallel Phase #:15   [orcl] Files:120  Time: 60s
Restart  Phase #:16   [orcl] Files:1    Time: 3s
Serial   Phase #:17   [orcl] Files:22   Time: 9s
Restart  Phase #:18   [orcl] Files:1    Time: 2s
*****************   Catproc Views   ****************
Parallel Phase #:19   [orcl] Files:32   Time: 66s
Restart  Phase #:20   [orcl] Files:1    Time: 3s
Serial   Phase #:21   [orcl] Files:3    Time: 28s
Restart  Phase #:22   [orcl] Files:1    Time: 4s
Parallel Phase #:23   [orcl] Files:25   Time: 181s
Restart  Phase #:24   [orcl] Files:1    Time: 3s
Parallel Phase #:25   [orcl] Files:12   Time: 87s
Restart  Phase #:26   [orcl] Files:1    Time: 3s
Serial   Phase #:27   [orcl] Files:1    Time: 0s
Serial   Phase #:28   [orcl] Files:3    Time: 11s
Serial   Phase #:29   [orcl] Files:1    Time: 0s
Restart  Phase #:30   [orcl] Files:1    Time: 3s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [orcl] Files:1    Time: 2s
Restart  Phase #:32   [orcl] Files:1    Time: 3s
Serial   Phase #:34   [orcl] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [orcl] Files:293  Time: 58s
Serial   Phase #:36   [orcl] Files:1    Time: 0s
Restart  Phase #:37   [orcl] Files:1    Time: 1s
Serial   Phase #:38   [orcl] Files:6    Time: 12s
Restart  Phase #:39   [orcl] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [orcl] Files:3    Time: 83s
Restart  Phase #:41   [orcl] Files:1    Time: 2s
******************   Catproc SQL   *****************
Parallel Phase #:42   [orcl] Files:13   Time: 88s
Restart  Phase #:43   [orcl] Files:1    Time: 2s
Parallel Phase #:44   [orcl] Files:11   Time: 11s
Restart  Phase #:45   [orcl] Files:1    Time: 3s
Parallel Phase #:46   [orcl] Files:3    Time: 3s
Restart  Phase #:47   [orcl] Files:1    Time: 4s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [orcl] Files:1    Time: 17s
Restart  Phase #:49   [orcl] Files:1    Time: 3s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [orcl] Files:1    Time: 70s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [orcl] Files:1    Time: 2s
Restart  Phase #:52   [orcl] Files:1    Time: 4s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [orcl] Files:2    Time: 667s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [orcl] Files:1    Time: 3s
Serial   Phase #:56   [orcl] Files:3    Time: 53s
Serial   Phase #:57   [orcl] Files:3    Time: 11s
Parallel Phase #:58   [orcl] Files:10   Time: 7s
Parallel Phase #:59   [orcl] Files:25   Time: 15s
Serial   Phase #:60   [orcl] Files:4    Time: 20s
Serial   Phase #:61   [orcl] Files:1    Time: 0s
Serial   Phase #:62   [orcl] Files:32   Time: 11s
Serial   Phase #:63   [orcl] Files:1    Time: 0s
Parallel Phase #:64   [orcl] Files:6    Time: 6s
Serial   Phase #:65   [orcl] Files:2    Time: 21s
Serial   Phase #:66   [orcl] Files:3    Time: 75s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [orcl] Files:1    Time: 4s
Serial   Phase #:69   [orcl] Files:1    Time: 4s
Parallel Phase #:70   [orcl] Files:2    Time: 116s
Restart  Phase #:71   [orcl] Files:1    Time: 3s
Parallel Phase #:72   [orcl] Files:2    Time: 4s
Serial   Phase #:73   [orcl] Files:2    Time: 5s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [orcl] Files:1    Time: 2s
Serial   Phase #:76   [orcl] Files:1    Time: 49s
Serial   Phase #:77   [orcl] Files:2    Time: 7s
Restart  Phase #:78   [orcl] Files:1    Time: 2s
Serial   Phase #:79   [orcl] Files:1    Time: 42s
Restart  Phase #:80   [orcl] Files:1    Time: 2s
Parallel Phase #:81   [orcl] Files:3    Time: 152s
Restart  Phase #:82   [orcl] Files:1    Time: 3s
Serial   Phase #:83   [orcl] Files:1    Time: 8s
Restart  Phase #:84   [orcl] Files:1    Time: 2s
Serial   Phase #:85   [orcl] Files:1    Time: 14s
Restart  Phase #:86   [orcl] Files:1    Time: 3s
Parallel Phase #:87   [orcl] Files:4    Time: 147s
Restart  Phase #:88   [orcl] Files:1    Time: 3s
Serial   Phase #:89   [orcl] Files:1    Time: 5s
Restart  Phase #:90   [orcl] Files:1    Time: 3s
Serial   Phase #:91   [orcl] Files:2    Time: 11s
Restart  Phase #:92   [orcl] Files:1    Time: 2s
Serial   Phase #:93   [orcl] Files:1    Time: 3s
Restart  Phase #:94   [orcl] Files:1    Time: 3s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [orcl] Files:1    Time: 37s
Restart  Phase #:96   [orcl] Files:1    Time: 2s
***********   Final Component scripts    ***********
Serial   Phase #:97   [orcl] Files:1    Time: 5s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [orcl] Files:1    Time: 380s
*******************   Migration   ******************
Serial   Phase #:99   [orcl] Files:1    Time: 101s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [orcl] Files:1    Time: 2s
Serial   Phase #:101  [orcl] Files:1    Time: 0s
Serial   Phase #:102  [orcl] Files:1    Time: 104s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [orcl] Files:1    Time: 26s
****************   Summary report   ****************
Serial   Phase #:104  [orcl] Files:1    Time: 2s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [orcl] Files:1    Time: 2s
Serial   Phase #:106  [orcl] Files:1    Time: 0s
Serial   Phase #:107  [orcl] Files:1     Time: 71s

------------------------------------------------------
Phases [0-107]         End Time:[2020_12_23 18:10:06]
------------------------------------------------------

Grand Total Time: 4211s 

 LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20201223165944/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl/upgrade20201223165944/upg_summary.log

Grand Total Upgrade Time:    [0d:1h:10m:11s]

时间一共花费1小时10分钟11秒,升级的速度跟存储密切相关。
升级完成后,数据库是自动关闭的,此时需要手动起库

$ sqlplus / as sysdba
SQL> startup;

升级后操作

升级后操作还是要参考preupgrade.log日志,部分要手工操作,其他的执行生成的postupgrade_fixups.sql脚本即可。

1.升级时区文件

源端库使用的时区文件版本为14,目标库19c使用的时区版本为32。
执行以下脚本:

$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
sqlplus / as sysdba <<EOF

-- Check current settings.
SELECT * FROM v$timezone_file;

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;	

-- Begin upgrade to the latest version.
SET SERVEROUTPUT ON
DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;
  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/

SHUTDOWN IMMEDIATE;
STARTUP;

-- Do the upgrade.
SET SERVEROUTPUT ON
DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/
-- Check new settings.
SELECT * FROM v$timezone_file;

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

exit;
EOF

2.忽略

3.收集数据字典统计信息

执行以下存储过程

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

4.收集固定对象统计信息

执行以下存储过程

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

5.执行preupgrade.log日志中postupgrade_fixups.sql脚本

SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

6.清理11g数据库软件

$ /u01/app/oracle/product/11.2.0/db_1/deinstall
$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /home/oracle/oraInventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################### CHECK OPERATION START #########################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/db_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /home/oracle/oraInventory
Checking for sufficient temp space availability on node(s) : 'primary'

## [END] Install check configuration ##

Network Configuration check config START

Network de-configuration trace file location: /home/oracle/oraInventory/logs/netdc_check2020-12-24_11-18-19-AM.log

Specify all Single Instance listeners that are to be de-configured [LISTENER]:

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /home/oracle/oraInventory/logs/databasedc_check2020-12-24_11-18-25-AM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [orcl]: 

###### For Database 'orcl' ######

Single Instance Database
The diagnostic destination location of the database: /u01/app/oracle/diag/rdbms//diag/rdbms/orcl
Storage type used by the Database: 

The details of database(s) orcl have been discovered automatically. Do you still want to modify the details of orcl database(s)? [n]: y

###### For Database 'orcl' ######

Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database) [1]: 
Specify the diagnostic destination location of the database [/u01/app/oracle/diag/rdbms//diag/rdbms/orcl]: 
Specify the storage type used by the Database ASM|FS []: FS

Specify the list of directories if any database files exist on a shared file system. If 'orcl' subdirectory is found, then it will be deleted. Otherwise, the specified directory will be deleted. Alternatively, you can specify list of database files with full path [ ]: 

Specify the fast recovery area location, if it is configured on the file system. If 'orcl' subdirectory is found, then it will be deleted. [/u01/app/oracle/fast_recovery_area/ORCL]: 

Specify the database spfile location [/u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora]: 

Database Check Configuration END

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /home/oracle/oraInventory/logs/emcadc_check2020-12-24_11-19-07-AM.log 

Checking configuration for database orcl
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /home/oracle/oraInventory/logs//ocm_check9984.log
Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/product/11.2.0/db_1
Inventory Location where the Oracle home registered is: /home/oracle/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER
The following databases were selected for de-configuration : orcl
Database unique name : orcl
Storage used : FS
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/home/oracle/oraInventory/logs/deinstall_deconfig2020-12-24_11-18-18-AM.out'
Any error messages from this session will be written to: '/home/oracle/oraInventory/logs/deinstall_deconfig2020-12-24_11-18-18-AM.err'

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /home/oracle/oraInventory/logs/emcadc_clean2020-12-24_11-19-07-AM.log 

Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /home/oracle/oraInventory/logs/databasedc_clean2020-12-24_11-19-14-AM.log
Database Clean Configuration START orcl
This operation may take few minutes.
Database Clean Configuration END orcl

Network Configuration clean config START

Network de-configuration trace file location: /home/oracle/oraInventory/logs/netdc_clean2020-12-24_11-19-27-AM.log

De-configuring Single Instance listener(s): LISTENER

De-configuring listener: LISTENER
    Stopping listener: LISTENER
    Listener stopped successfully.
    Deleting listener: LISTENER
    Listener deleted successfully.
Listener de-configured successfully.

De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /home/oracle/oraInventory/logs//ocm_clean9984.log
Oracle Configuration Manager clean END
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/11.2.0/db_1' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1'.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2020-12-24_11-18-06AM' on node 'primary'

## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : orcl
Following Single Instance listener(s) were de-configured successfully: LISTENER
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u01/app/oracle/product/11.2.0/db_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/11.2.0/db_1' on the local node.
Oracle Universal Installer cleanup was successful.

Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

7.删除11g残留文件

$ rm -rf /u01/app/oracle/product/11.2.0

8.检查环境变量

---- end -----

最后修改时间:2020-12-28 17:09:40
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论