Oracle 11g升级到19c
一、环境说明
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.直接升级路线
2.间接升级路线
升级前准备
一、源端环境需求和检查
在升级之前，确保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]
参数说明：
如果你定义了ORACLE_BASE,日志会被创建在$ORACLE_BASE/cfgtoollogs/
$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