本文涉转载自 https://blog.csdn.net/u010692693/article/details/73826193
测试升级步骤正确无误，注意本文中涉及到2个脚本，仅付费用户可以获取.
1. dbupgdiag.zip 为升级诊断脚本，检查数据库是否符合升级标准.
2. DBMS_DST_scriptsV1.9.zip 为时区升级脚本，用于数据库的时区升级.
Complete checklist for Manual Upgrade for Multitenant Architecture Oracle Databases from 12.1.x.x to 12.2..x.x (文档 ID 2173144.1)
Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (文档 ID 2173141.1)
Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (文档 ID 1509653.1)
Upgrading Timezone file on Oracle Releases post 12.2.0.1
From 12.2.0.2 onwards, timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory. Customer can use those files to perform time zone upgrade instead of the ones included in this document. Please refer to Oracle Documentation on how to upgrade a time zone file manually:
Database Globalization Support Guide
Section 4.7.3 Steps to Upgrade Time Zone File and Timestamp with Time Zone Data
http://docs.oracle.com/database/122/NLSPG/datetime-data-types-and-time-zone-support.htm#NLSPG261
1. 查看补丁情况
[oracle@oracle OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-16_20-42-36PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2017-06-16_20-42-36PM.txt
——————————————————————————-
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
——————————————————————————-
OPatch succeeded.
2. 执行预检查脚本
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl11g
[oracle@oracle ~]$ cd /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/
[oracle@oracle admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 16 21:30:55 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /tmp
JAVAVM TESTING
—————
foo
===================================
Oracle Multimedia/InterMedia status
===================================
.
Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.4.0 and status: VALID
.
Checking for installed Database Schemas…
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
ORDDATA user exists.
.
Checking for Prerequisite Components…
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia…(no output if component status is valid)
PL/SQL procedure successfully completed.
*** End of LogFile ***
3. 检查物化视图刷新是否完成
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
2 WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
4. 检查数据文件的状态
SQL> col name format a50
SQL> select name,status from v$datafile;
NAME STATUS
————————————————– ——-
/u01/app/oracle/oradata/orcl11g/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl11g/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl11g/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl11g/users01.dbf ONLINE
4 rows selected.
5. 检查是否存在备份
SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
no rows selected
6. 检查分布式事务
SQL> SELECT * FROM dba_2pc_pending;
no rows selected
如需处理：
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”);
SQL> COMMIT;
7. 清空回收站
SQL> show recyclebin
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
8. 收集数据字典的统计信息
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
9. 拷贝pfile到12c目录
SQL> create pfile from spfile;
File created.
[oracle@oracle admin]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@oracle dbs]$ ll
total 24
-rw-rw—-. 1 oracle oinstall 1544 Jun 16 20:31 hc_orcl11g.dat
-rw-r–r–. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r–r–. 1 oracle oinstall 937 Jun 16 21:49 initorcl11g.ora
-rw-r—–. 1 oracle oinstall 24 Jun 16 20:29 lkORCL11G
-rw-r—–. 1 oracle oinstall 1536 Jun 16 20:30 orapworcl11g
-rw-r—–. 1 oracle oinstall 2560 Jun 16 21:32 spfileorcl11g.ora
[oracle@oracle dbs]$ cp initorcl11g.ora /u01/app/oracle/product/12.2.0/db_1/dbs
10. 停止原11g数据库
[oracle@oracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 16 21:52:29 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
11. 启动12c数据库
[oracle@oracle ~]$ export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1
[oracle@oracle dbs]$ cat initorcl11g.ora
*.audit_file_dest=’/u01/app/oracle/admin/orcl11g/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4.0′
*.control_files=’/u01/app/oracle/oradata/orcl11g/control01.ctl’,’/u01/app/oracle/oradata/orcl11g/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’orcl11g’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)’
*.log_archive_dest_1=’LOCATION=/arch’
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=629145600
*.undo_tablespace=’UNDOTBS1′
[oracle@oracle dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 16 21:58:13 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
12. 运行升级脚本
[oracle@oracle dbs]$ cd ../rdbms/admin/
[oracle@oracle admin]$ ll catctl.pl
-rw-r–r–. 1 oracle oinstall 333980 Dec 19 19:12 catctl.pl
[oracle@oracle admin]$ ll catupgrd.sql
-rw-r–r–. 1 oracle oinstall 7357 Dec 21 2015 catupgrd.sql
默认并行度为4
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
或者使用以前的方式，不开并行
SQL> @catupgrd.sql PARALLEL=NO
执行升级，时间可能需要数十分钟至几小时不等
[oracle@oracle admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Argument list for [catctl.pl]
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: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]
/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/db_1]
/u01/app/oracle/product/12.2.0/db_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0/db_1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0/db_1]
Analyzing file /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20170616220804]
catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20170616220804/catupgrd_catcon_11069.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20170616220804/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20170616220804/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = orcl11g
DataBase Version = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805/catupgrd_catcon_11069.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805]
Parallel SQL Process Count = 4
Components in [orcl11g]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
——————————————————
Phases [0-115] Start Time:[2017_06_16 22:08:05]
——————————————————
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl11g] Files:1 Time: 86s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl11g] Files:5 Time: 44s
Restart Phase #:2 [orcl11g] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl11g] Files:19 Time: 17s
Restart Phase #:4 [orcl11g] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl11g] Files:6 Time: 15s
***************** Catproc Start ****************
Serial Phase #:6 [orcl11g] Files:1 Time: 11s
***************** Catproc Types ****************
Serial Phase #:7 [orcl11g] Files:2 Time: 11s
Restart Phase #:8 [orcl11g] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl11g] Files:69 Time: 43s
Restart Phase #:10 [orcl11g] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl11g] Files:1 Time: 34s
Restart Phase #:12 [orcl11g] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl11g] Files:97 Time: 16s
Restart Phase #:14 [orcl11g] Files:1 Time: 0s
Parallel Phase #:15 [orcl11g] Files:118 Time: 18s
Restart Phase #:16 [orcl11g] Files:1 Time: 0s
Serial Phase #:17 [orcl11g] Files:13 Time: 2s
Restart Phase #:18 [orcl11g] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl11g] Files:33 Time: 33s
Restart Phase #:20 [orcl11g] Files:1 Time: 1s
Serial Phase #:21 [orcl11g] Files:3 Time: 5s
Restart Phase #:22 [orcl11g] Files:1 Time: 0s
Parallel Phase #:23 [orcl11g] Files:24 Time: 116s
Restart Phase #:24 [orcl11g] Files:1 Time: 0s
Parallel Phase #:25 [orcl11g] Files:11 Time: 38s
Restart Phase #:26 [orcl11g] Files:1 Time: 0s
Serial Phase #:27 [orcl11g] Files:1 Time: 0s
Serial Phase #:28 [orcl11g] Files:3 Time: 2s
Serial Phase #:29 [orcl11g] Files:1 Time: 0s
Restart Phase #:30 [orcl11g] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl11g] Files:1 Time: 0s
Restart Phase #:32 [orcl11g] Files:1 Time: 0s
Serial Phase #:34 [orcl11g] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl11g] Files:283 Time: 15s
Serial Phase #:36 [orcl11g] Files:1 Time: 0s
Restart Phase #:37 [orcl11g] Files:1 Time: 0s
Serial Phase #:38 [orcl11g] Files:1 Time: 3s
Restart Phase #:39 [orcl11g] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl11g] Files:3 Time: 39s
Restart Phase #:41 [orcl11g] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl11g] Files:13 Time: 52s
Restart Phase #:43 [orcl11g] Files:1 Time: 0s
Parallel Phase #:44 [orcl11g] Files:12 Time: 16s
Restart Phase #:45 [orcl11g] Files:1 Time: 0s
Parallel Phase #:46 [orcl11g] Files:2 Time: 1s
Restart Phase #:47 [orcl11g] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl11g] Files:1 Time: 5s
Restart Phase #:49 [orcl11g] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl11g] Files:1 Time: 19s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl11g] Files:1 Time: 1s
Restart Phase #:52 [orcl11g] Files:1 Time: 0s
**************** Upgrading Java ****************
Serial Phase #:53 [orcl11g] Files:1 Time: 235s
Restart Phase #:54 [orcl11g] Files:1 Time: 0s
***************** Upgrading XDK ****************
Serial Phase #:55 [orcl11g] Files:1 Time: 28s
Restart Phase #:56 [orcl11g] Files:1 Time: 0s
********* Upgrading APS,OLS,DV,CONTEXT *********
Serial Phase #:57 [orcl11g] Files:1 Time: 52s
***************** Upgrading XDB ****************
Restart Phase #:58 [orcl11g] Files:1 Time: 0s
Serial Phase #:60 [orcl11g] Files:3 Time: 18s
Serial Phase #:61 [orcl11g] Files:3 Time: 4s
Parallel Phase #:62 [orcl11g] Files:9 Time: 2s
Parallel Phase #:63 [orcl11g] Files:24 Time: 4s
Serial Phase #:64 [orcl11g] Files:4 Time: 5s
Serial Phase #:65 [orcl11g] Files:1 Time: 0s
Serial Phase #:66 [orcl11g] Files:30 Time: 3s
Serial Phase #:67 [orcl11g] Files:1 Time: 0s
Parallel Phase #:68 [orcl11g] Files:6 Time: 2s
Serial Phase #:69 [orcl11g] Files:2 Time: 12s
Serial Phase #:70 [orcl11g] Files:3 Time: 51s
Restart Phase #:71 [orcl11g] Files:1 Time: 0s
********* Upgrading CATJAVA,OWM,MGW,RAC ********
Serial Phase #:72 [orcl11g] Files:1 Time: 78s
**************** Upgrading ORDIM ***************
Restart Phase #:73 [orcl11g] Files:1 Time: 0s
Serial Phase #:75 [orcl11g] Files:1 Time: 0s
Parallel Phase #:76 [orcl11g] Files:2 Time: 58s
Serial Phase #:77 [orcl11g] Files:1 Time: 49s
Restart Phase #:78 [orcl11g] Files:1 Time: 0s
Parallel Phase #:79 [orcl11g] Files:2 Time: 10s
Serial Phase #:80 [orcl11g] Files:2 Time: 0s
***************** Upgrading SDO ****************
Restart Phase #:81 [orcl11g] Files:1 Time: 0s
Serial Phase #:83 [orcl11g] Files:1 Time: 37s
Serial Phase #:84 [orcl11g] Files:1 Time: 1s
Restart Phase #:85 [orcl11g] Files:1 Time: 0s
Serial Phase #:86 [orcl11g] Files:1 Time: 17s
Restart Phase #:87 [orcl11g] Files:1 Time: 0s
Parallel Phase #:88 [orcl11g] Files:3 Time: 98s
Restart Phase #:89 [orcl11g] Files:1 Time: 0s
Serial Phase #:90 [orcl11g] Files:1 Time: 3s
Restart Phase #:91 [orcl11g] Files:1 Time: 0s
Serial Phase #:92 [orcl11g] Files:1 Time: 2s
Restart Phase #:93 [orcl11g] Files:1 Time: 0s
Parallel Phase #:94 [orcl11g] Files:4 Time: 65s
Restart Phase #:95 [orcl11g] Files:1 Time: 0s
Serial Phase #:96 [orcl11g] Files:1 Time: 1s
Restart Phase #:97 [orcl11g] Files:1 Time: 0s
Serial Phase #:98 [orcl11g] Files:2 Time: 28s
Restart Phase #:99 [orcl11g] Files:1 Time: 1s
Serial Phase #:100 [orcl11g] Files:1 Time: 0s
Restart Phase #:101 [orcl11g] Files:1 Time: 0s
*********** Upgrading Misc. ODM, OLAP **********
Serial Phase #:102 [orcl11g] Files:1 Time: 21s
**************** Upgrading APEX ****************
Restart Phase #:103 [orcl11g] Files:1 Time: 0s
Serial Phase #:104 [orcl11g] Files:1 Time: 450s
Restart Phase #:105 [orcl11g] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:106 [orcl11g] Files:1 Time: 0s
************* Final Upgrade scripts ************
Serial Phase #:107 [orcl11g] Files:1 Time: 130s
********** End PDB Application Upgrade *********
Serial Phase #:108 [orcl11g] Files:1 Time: 0s
******************* Migration ******************
Serial Phase #:109 [orcl11g] Files:1 Time: 36s
Serial Phase #:110 [orcl11g] Files:1 Time: 0s
Serial Phase #:111 [orcl11g] Files:1 Time: 43s
***************** Post Upgrade *****************
Serial Phase #:112 [orcl11g] Files:1 Time: 439s
**************** Summary report ****************
Serial Phase #:113 [orcl11g] Files:1 Time: 1s
Serial Phase #:114 [orcl11g] Files:1 Time: 0s
Serial Phase #:115 [orcl11g] Files:1 Time: 29s
——————————————————
Phases [0-115] End Time:[2017_06_16 22:52:26]
——————————————————
Grand Total Time: 2665s
LOG FILES: (/u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805/upg_summary.log
Grand Total Upgrade Time: [0d:0h:44m:25s]
13. 重启数据库
[oracle@oracle admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 16 22:54:49 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
14. 补充脚本
[oracle@oracle admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catuppst -d ”’.”’ catuppst.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/catuppst_catcon_16736.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/catuppst*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/catuppst_*.lst] files for spool files, if any
catcon.pl: completed successfully
15.编译对象脚本
[oracle@oracle admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/utlrp_catcon_16773.lst]
catcon: See [/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
16. 预检查修复脚本
How to Download and Run Oracle’s Database Pre-Upgrade Utility (文档 ID 884522.1)
[root@oracle opt]# unzip preupgrade_12201_cbuild_005.zip
Archive: preupgrade_12201_cbuild_005.zip
inflating: dbms_registry_basic.sql
inflating: dbms_registry_extended.sql
inflating: preupgrade_driver.sql
inflating: preupgrade.jar
inflating: preupgrade_messages.properties
inflating: preupgrade_package.sql
[root@oracle opt]# chown oracle:oinstall dbms_registry_* preupgrade*
[root@oracle opt]# cp -rp dbms_registry_* preupgrade* /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/dbms_registry_basic.sql’? y
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/dbms_registry_extended.sql’? y
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade_12201_cbuild_005.zip’? y
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade_driver.sql’? y
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar’? y
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade_messages.properties’? y
cp: overwrite `/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade_package.sql’? y
[oracle@oracle db_1]$ $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/preupgrade.jar
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/postupgrade_fixups.sql
[oracle@oracle db_1]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 17 07:53:25 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> @/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 5
Generated on: 2017-06-17 07:51:12
For Source Database: ORCL11G
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
———- —— ——————
old_time_zones_exist Failed Manual fixup recommended.
post_dictionary Passed None
upg_by_std_upgrd Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
Session altered.
17. 检查脚本
SQL> @?/rdbms/admin/utlu122s.sql
Oracle Database 12.2 Post-Upgrade Status Tool 06-17-2017 07:57:00
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server VALID 12.2.0.1.0 00:10:42
JServer JAVA Virtual Machine VALID 12.2.0.1.0 00:03:53
Oracle Workspace Manager VALID 12.2.0.1.0 00:01:07
OLAP Analytic Workspace VALID 12.2.0.1.0 00:00:19
OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00
Oracle OLAP API VALID 12.2.0.1.0 00:00:13
Oracle XDK VALID 12.2.0.1.0 00:00:27
Oracle Text VALID 12.2.0.1.0 00:00:31
Oracle XML Database VALID 12.2.0.1.0 00:01:40
Oracle Database Java Packages VALID 12.2.0.1.0 00:00:09
Oracle Multimedia VALID 12.2.0.1.0 00:01:57
Spatial VALID 12.2.0.1.0 00:04:11
Oracle Application Express VALID 5.0.4.00.12 00:07:29
Final Actions 00:02:46
Post Upgrade 00:00:01
Post Compile 00:06:36
Total Upgrade Time: 00:42:23
Database time zone version is 14. It is older than current release time
zone version 26. Time zone upgrade is needed using the DBMS_DST package.
Summary Report File = /u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/orcl11g/upgrade20170616220805/upg_summary.log
18. 检查组件的状态
SQL> set line 200
SQL> col COMP_ID format a10
SQL> col COMP_NAME format a35
SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30)
2 comp_name,substr(version,1,10) version,status
3 from dba_registry order by modified;
COMP_ID COMP_NAME VERSION STATUS
———- ———————————– ——————– ———–
AMD OLAP Catalog 11.2.0.4.0 OPTION OFF
CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
CATPROC Oracle Database Packages and T 12.2.0.1.0 VALID
OWM Oracle Workspace Manager 12.2.0.1.0 VALID
CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID
XML Oracle XDK 12.2.0.1.0 VALID
CONTEXT Oracle Text 12.2.0.1.0 VALID
XDB Oracle XML Database 12.2.0.1.0 VALID
ORDIM Oracle Multimedia 12.2.0.1.0 VALID
APS OLAP Analytic Workspace 12.2.0.1.0 VALID
XOQ Oracle OLAP API 12.2.0.1.0 VALID
SDO Spatial 12.2.0.1.0 VALID
APEX Oracle Application Express 5.0.4.00.1 VALID
14 rows selected.
19. 检查OPatch的情况
[oracle@oracle db_1]$ cd OPatch/
[oracle@oracle OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.2.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/opatch/opatch2017-06-17_08-08-09AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/12.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2017-06-17_08-08-09AM.txt
——————————————————————————-
Local Machine Information::
Hostname: oracle
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
——————————————————————————-
OPatch succeeded.
20. 修改ORACLE_HOME，PATH
[oracle@oracle ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
#export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export ORACLE_SID=orcl11g
#export ORACLE_SID=orcl12c
umask 022
21. 检查/etc/oratab
[root@oracle opt]# vi /etc/oratab
#orcl11g:/u01/app/oracle/product/11.2.0/db_1:N
orcl12c:/u01/app/oracle/product/12.2.0/db_1:N
22. 修改支持扩展数据类型
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 11.2.0.4.0
noncdb_compatible boolean FALSE
SQL> show parameter max_string_size
NAME TYPE VALUE
———————————— ———– ——————————
max_string_size string STANDARD
SQL> alter system set compatible=’12.2.0.1.0′ scope=spfile;
System altered.
SQL> alter system set MAX_STRING_SIZE = EXTENDED scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
Process ID: 24368
Session ID: 32 Serial number: 24866
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
SQL> alter system set MAX_STRING_SIZE = EXTENDED;
System altered.
SQL> @?/rdbms/admin/utl32k.sql
SP2-0042: unknown command “aRem” – rest of line ignored.
Session altered.
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an “ORA-01722: invalid number”
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a “SHUTDOWN ABORT” and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an “ORA-01722: invalid number”
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
Session altered.
1988 rows updated.
Commit complete.
System altered.
PL/SQL procedure successfully completed.
Commit complete.
System altered.
Session altered.
Session altered.
Table created.
Table created.
Table created.
Table truncated.
0 rows created.
PL/SQL procedure successfully completed.
STARTTIME
——————————————————————————-
06/17/2017 08:17:22.920635000
PL/SQL procedure successfully completed.
No errors.
PL/SQL procedure successfully completed.
Session altered.
Session altered.
0 rows created.
no rows selected
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an “ORA-01722: invalid number”
DOC> error if we encountered an error while modifying a column to
DOC> account for data type length change as a result of enabling or
DOC> disabling 32k types.
DOC>
DOC> Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.
Package altered.
Package altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 12.2.0.1.0
noncdb_compatible boolean FALSE
SQL> show parameter max_string_size
NAME TYPE VALUE
———————————— ———– ——————————
max_string_size string EXTENDED
SQL>
23. 升级catalog
RMAN客户端版本低的情况下，需要升级
Upgrade catalog
24. 升级timezone
11g本身的timezone是14
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
——————– ———- ———-
timezlrg_14.dat 14 0
SQL> set line 200
SQL> col PROPERTY_NAME format a30
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;
PROPERTY_NAME VALUE
—————————— ————————————————
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
[root@oracle opt]# mkdir timezone
[root@oracle opt]# mv DBMS_DST_scriptsV1.9.zip timezone/
[root@oracle timezone]# unzip DBMS_DST_scriptsV1.9.zip
Archive: DBMS_DST_scriptsV1.9.zip
creating: DBMS_DST_scriptsV1.9/
inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql
[root@oracle opt]# chown oracle:oinstall -R timezone/
[root@oracle DBMS_DST_scriptsV1.9]# ll
total 68
-rw-r–r–. 1 oracle oinstall 6294 Jan 8 2015 countstarTSTZ.sql
-rw-r–r–. 1 oracle oinstall 6909 Jan 8 2015 countstatsTSTZ.sql
-rw-r–r–. 1 oracle oinstall 19502 Aug 22 2014 upg_tzv_apply.sql
-rw-r–r–. 1 oracle oinstall 31010 Aug 22 2014 upg_tzv_check.sql
[oracle@oracle DBMS_DST_scriptsV1.9]$ pwd
/opt/timezone/DBMS_DST_scriptsV1.9
[oracle@oracle DBMS_DST_scriptsV1.9]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 17 09:38:03 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> @countstatsTSTZ.sql
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first…
Note: empty tables are not listed.
Stat date – Owner.Tablename.Columnname – num_rows
24/08/2013 – SYS.AQ$_ALERT_QT_S.CREATION_TIME – 3
24/08/2013 – SYS.AQ$_ALERT_QT_S.DELETION_TIME – 3
24/08/2013 – SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME – 3
24/08/2013 – SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME – 3
24/08/2013 – SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME – 3
24/08/2013 – SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME – 3
17/06/2017 – SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME – 1
17/06/2017 – SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME – 1
17/06/2017 – SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME – 1
17/06/2017 – SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.CREATION_TIME – 1
17/06/2017 – SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.DELETION_TIME – 1
17/06/2017 – SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.MODIFICATION_TIME – 1
17/06/2017 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME – 1
17/06/2017 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME – 1
17/06/2017 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME – 1
17/06/2017 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME – 3
17/06/2017 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME – 3
17/06/2017 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME – 3
17/06/2017 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME – 1
17/06/2017 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME – 1
17/06/2017 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME – 1
17/06/2017 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME – 1
17/06/2017 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME – 1
17/06/2017 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME – 1
17/06/2017 – SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME – 1
17/06/2017 – SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME – 1
17/06/2017 – SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME – 1
17/06/2017 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME – 4
17/06/2017 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME – 4
17/06/2017 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME – 4
17/06/2017 – SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME – 1
17/06/2017 – SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME – 1
17/06/2017 – SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME – 1
17/06/2017 – SYS.KET$_AUTOTASK_STATUS.MW_START_TIME – 1
17/06/2017 – SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME – 1
17/06/2017 – SYS.KET$_CLIENT_CONFIG.FIELD_2 – 7
17/06/2017 – SYS.KET$_CLIENT_CONFIG.LAST_CHANGE – 7
17/06/2017 – SYS.KET$_CLIENT_TASKS.CURR_WIN_START – 3
17/06/2017 – SYS.KET$_CLIENT_TASKS.LG_DATE – 3
17/06/2017 – SYS.KET$_CLIENT_TASKS.LT_DATE – 3
17/06/2017 – SYS.OPTSTAT_HIST_CONTROL$.SPARE6 – 39
17/06/2017 – SYS.OPTSTAT_HIST_CONTROL$.SVAL2 – 39
17/06/2017 – SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP – 5566
17/06/2017 – SYS.OPTSTAT_USER_PREFS$.CHGTIME – 35
17/06/2017 – SYS.RADM_FPTM$.TSWTZ_COL – 1
17/06/2017 – SYS.REG$.NTFN_GROUPING_START_TIME – 2
17/06/2017 – SYS.REG$.REG_TIME – 2
17/06/2017 – SYS.SCHEDULER$_EVENT_LOG.LOG_DATE – 296
17/06/2017 – SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP – 11
17/06/2017 – SYS.SCHEDULER$_JOB.END_DATE – 28
17/06/2017 – SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME – 28
17/06/2017 – SYS.SCHEDULER$_JOB.LAST_END_DATE – 28
17/06/2017 – SYS.SCHEDULER$_JOB.LAST_START_DATE – 28
17/06/2017 – SYS.SCHEDULER$_JOB.NEXT_RUN_DATE – 28
17/06/2017 – SYS.SCHEDULER$_JOB.START_DATE – 28
17/06/2017 – SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE – 190
17/06/2017 – SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE – 190
17/06/2017 – SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE – 190
17/06/2017 – SYS.SCHEDULER$_SCHEDULE.END_DATE – 4
17/06/2017 – SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE – 4
17/06/2017 – SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE – 9
17/06/2017 – SYS.SCHEDULER$_WINDOW.END_DATE – 9
17/06/2017 – SYS.SCHEDULER$_WINDOW.LAST_START_DATE – 9
17/06/2017 – SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME – 9
17/06/2017 – SYS.SCHEDULER$_WINDOW.NEXT_START_DATE – 9
17/06/2017 – SYS.SCHEDULER$_WINDOW.START_DATE – 9
17/06/2017 – SYS.STATS_TARGET$.END_TIME – 1698
17/06/2017 – SYS.STATS_TARGET$.START_TIME – 1698
17/06/2017 – SYS.TAB_STATS$.SPARE6 – 1117
17/06/2017 – SYS.WRI$_ALERT_HISTORY.CREATION_TIME – 23
17/06/2017 – SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED – 23
17/06/2017 – SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME – 42762
17/06/2017 – SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 – 42762
17/06/2017 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME – 56148
17/06/2017 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 – 56148
17/06/2017 – SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME – 3686
17/06/2017 – SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 – 3686
17/06/2017 – SYS.WRI$_OPTSTAT_OPR.END_TIME – 134
17/06/2017 – SYS.WRI$_OPTSTAT_OPR.SPARE6 – 134
17/06/2017 – SYS.WRI$_OPTSTAT_OPR.START_TIME – 134
17/06/2017 – SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME – 7422
17/06/2017 – SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 – 7422
17/06/2017 – SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME – 7422
17/06/2017 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME – 3705
17/06/2017 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 – 3705
17/06/2017 – SYS.XS$PRIN.END_DATE – 15
17/06/2017 – SYS.XS$PRIN.START_DATE – 15
Total numrow of SYS TSTZ columns is : 246735
There are in total 154 non-SYS TSTZ columns.
.
For non-SYS tables …
Note: empty tables are not listed.
Stat date – Owner.Tablename.Columnname – num_rows
17/06/2017 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME – 1
17/06/2017 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME – 1
17/06/2017 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME –
1
17/06/2017 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME – 1
17/06/2017 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME – 1
17/06/2017 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME – 1
17/06/2017 – WMSYS.WM$WORKSPACES_TABLE$.CREATETIME – 1
17/06/2017 – WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE – 1
Total numrow of non-SYS TSTZ columns is : 8
There are in total 24 non-SYS TSTZ columns.
Total Minutes elapsed : 0
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues …
INFO: Database version is 12.2.0.1 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv26 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv26 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen …
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8623832 bytes
Variable Size 234883368 bytes
Database Buffers 381681664 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen …
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_L”
Number of failures: 0
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_S”
Number of failures: 0
Table list: “APEX_050000″.”WWV_FLOW_DEBUG_MESSAGES”
Number of failures: 0
Table list: “APEX_050000”.”WWV_FLOW_DEBUG_MESSAGES2″
Number of failures: 0
Table list: “APEX_050000″.”WWV_FLOW_FEEDBACK”
Number of failures: 0
Table list: “APEX_050000″.”WWV_FLOW_FEEDBACK_FOLLOWUP”
Number of failures: 0
Table list: “APEX_050000″.”WWV_FLOW_WORKSHEET_NOTIFY”
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv26 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
——————– ———- ———-
timezlrg_26.dat 26 0
SQL> set line 200
SQL> col PROPERTY_NAME format a30
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;
PROPERTY_NAME VALUE
—————————— ————————————————
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
———————
