【Oracle升級】Oracle指令碼升級11g to 19c non-CDB
一、 環境說明
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]
引數說明：
[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 報告如下，需要根據報告的建議進行修改
==============
BEFORE UPGRADE
==============
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 指令碼即可。
=============
AFTER UPGRADE
=============
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 -----
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31529886/viewspace-2744976/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【資料庫升級】Oracle指令碼升級12c CDB to 19c CDB資料庫Oracle指令碼
- Oracle：Oracle RAC 11.2.0.4 升級為 19cOracle
- oracle 12c non-cdb升級成cdb模式Oracle模式
- Oracle 18C升級到19COracle
- 生產庫升級:oracle 9.2.0.1升級oracle 9.2.0.8Oracle
- ORACLE10G升級11GOracle
- ORACLE 11g 升級補丁（Patch）Oracle
- ssh升級指令碼指令碼
- 靜默升級oracle 11g （從11.2.0.1升級到11.2.0.4）Oracle
- Oracle 19c - 手動升級到 Non-CDB 19c 的完整核對清單 (Doc ID 2577572.1)Oracle
- Windows升級到oracle 11g的異機物理升級文件（冷備）WindowsOracle
- Oracle 11g升級到12COracle
- oracle升級步驟Oracle
- oracle 10 rac 升級 10.2.0.1升級到10.2.0.5Oracle
- 【UP_ORACLE】Oracle 19c之從19.3升級到19.9Oracle
- php5.3升級指令碼PHP指令碼
- 讀ORACLE升級筆記Oracle筆記
- ORACLE 10G 升級Oracle 10g
- Oracle 資料庫升級Oracle資料庫
- Oracle Database 10.2.0.5.0 升級OracleDatabase
- oracle客戶端升級Oracle客戶端
- 升級 upgrade ORACLE DBOracle
- 升級Oracle10.2.0.1Oracle
- oracle 升級到 11.2.0.2Oracle
- oracle資料庫升級11.2.0.3升級到11.2.0.4Oracle資料庫
- 使用DBUA升級 Oracle 11.2.0.4到Oracle 19C的問題記錄Oracle
- Oracle 11g升級PSU詳細步驟Oracle
- A Oracle Data Guard Broker 升級和降級Oracle
- Oracle 19c - 手動升級 Oracle 12.x, 18c CDB 到 Oracle 19c (19.x)Oracle
- 12. Oracle版本、補丁及升級——12.3. 升級Oracle
- 9.2.0.4 升級到10.2.0.5升級後 Oracle Ultra Search 元件NO SCRIPTOracle元件
- 指令碼 - Oracle10g PSU後升級資料字典（RAC）指令碼Oracle
- Oracle從Windows 11.2.0.1升級並遷移到Linux 19cOracleWindowsLinux
- Oracle 12c升級指南Oracle
- oracle 10g rac升級Oracle 10g
- ORACLE APPLICATION EXPRESS 5.0 升級OracleAPPExpress
- ORACLE RAC 的滾動升級Oracle
- oracle 9208升級方法Oracle