Oracle Database 11.2.0.4升级到 12.2.0.1
参考MOS ：
手动升级到 Non-CDB Oracle Database 12c Release 2（12.2）的完整核对清单 (Doc ID 2297983.1)
Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)
Scripts to update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)
"CATPROC.SQL" EXECUTION HANGS IF DATABASE HAS JOBS RUNNING (Doc ID 1077852.6)
Oracle 12.2.0.1 Installation Fails With "PRVG-0449 : Proper soft limit for maximum stack size was not found on node "node1" [Expected >= "10240" ; Found = "8192"]" (Doc ID 2287806.1)java
OS :radhat7.6
RDBMS: 11.2.0.4 ---> 12.2.0.1node
步骤：
1 安装12.2.0.1的Sofeware，安装目录/u01/app/oracle/product/12.2.0/dbhome_1。其中11.2.0.4的目录是/u01/app/oracle/product/11.2.0/dbhome_1
2 执行dbupgdiag.sql(能够从 note 556610.1 下载这个脚本).主要是确认是否有失效对象或组件。
3 清空回收站
4 检查物化视图是否刷新完毕 。
5 收集统计信息 ，目的是为了减小停机时间。
6 检查时区设置 。
7 检查是否存在备份 。
8 升级前先解决Outstanding分布式事务 。
9 Preupgrade 检查 。
10 进行升级
11 升级后步骤(环境变量，oratab文件更新，升级catalog，post-upgrade fixup脚本等等)linux
详细步骤
1 安装software，略。在安装的时候，提示错误，参考上面的MOS Doc ID 2287806.1解决。sql
2 执行dbupgdiag.sql数据库
sql> alter session set nls_language='American'; sql> @dbupgdiag.sql sql> exit
--- 上面步骤过程 -session
[oracle@wls10306-02 12.2.0]$ cd dbhome_1/rdbms/admin/
[oracle@wls10306-02 admin]$ ll dbup*
-rw-r--r-- 1 oracle oinstall 24633 Jan 16 16:14 dbupgdiag.sql
[oracle@wls10306-02 admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 16 16:14:52 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter session set nls_language='American';
Session altered.
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /tmp
16_Jan_2020_0415 .log
test_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 01-16-2020 16:15:40
===============
Hostname
===============
wls10306-02
===============
Database Name
===============
TEST
===============
Database Uptime
===============
16:08 16-JAN-20
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
=============
Compatibility
=============
Compatibility is set as 11.2.0.4.0
================
Archive Log Mode
================
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 9
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/test/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
================
Cluster Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================
OWNER TABLESPACE_NAME
------------ ------------------------------
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
*
ERROR at line 1:
ORA-00942: table or view does not exist
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
0
==========================================
Oracle Label Security is installed or not
==========================================
Oracle Label Security is NOT installed at database level
================
Number of AQ Records in Message Queue Tables
================
SYS - ALERT_QT - 11
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - KUPC$DATAPUMP_QUETAB_1 - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
SYSMAN - MGMT_LOADER_QTABLE - 0
SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0
SYSMAN - MGMT_NOTIFY_QTABLE - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0
SYSMAN - MGMT_TASK_QTABLE - 28
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
14
================
Local Listener
================
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
MGMT_VIEW TEMP SYSTEM
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
DBSNMP TEMP SYSAUX
SYSMAN TEMP SYSAUX
WEBLOGIC TEMP USERS
APPS TEMP USERS
OUTLN TEMP SYSTEM
FLOWS_FILES TEMP SYSAUX
MDSYS TEMP SYSAUX
ORDSYS TEMP SYSAUX
EXFSYS TEMP SYSAUX
WMSYS TEMP SYSAUX
APPQOSSYS TEMP SYSAUX
APEX_030200 TEMP SYSAUX
OWBSYS_AUDIT TEMP SYSAUX
ORDDATA TEMP SYSAUX
CTXSYS TEMP SYSAUX
ANONYMOUS TEMP SYSAUX
XDB TEMP SYSAUX
ORDPLUGINS TEMP SYSAUX
OWBSYS TEMP SYSAUX
SI_INFORMTN_SCHEMA TEMP SYSAUX
OLAPSYS TEMP SYSAUX
SCOTT TEMP USERS
ORACLE_OCM TEMP USERS
XS$NULL TEMP USERS
BI TEMP USERS
PM TEMP USERS
MDDATA TEMP USERS
IX TEMP USERS
SH TEMP USERS
DIP TEMP USERS
OE TEMP USERS
APEX_PUBLIC_USER TEMP USERS
HR TEMP USERS
SPATIAL_CSW_ADMIN_USR TEMP USERS
SPATIAL_WFS_ADMIN_USR TEMP USERS
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD OLAP Catalog VALID 11.2.0.4.0
APEX Oracle Application Express VALID 3.2.1.00.12
APS OLAP Analytic Workspace VALID 11.2.0.4.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.4.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.4.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.4.0
CONTEXT Oracle Text VALID 11.2.0.4.0
EM Oracle Enterprise Manager VALID 11.2.0.4.0
EXF Oracle Expression Filter VALID 11.2.0.4.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.4.0
ORDIM Oracle Multimedia VALID 11.2.0.4.0
OWB OWB VALID 11.2.0.4.0
OWM Oracle Workspace Manager VALID 11.2.0.4.0
RUL Oracle Rules Manager VALID 11.2.0.4.0
SDO Spatial VALID 11.2.0.4.0
XDB Oracle XML Database VALID 11.2.0.4.0
XML Oracle XDK VALID 11.2.0.4.0
XOQ Oracle OLAP API VALID 11.2.0.4.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
-------- -----------------------------------
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ....
COUNT(1)
----------
0
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ....
DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE DBMS_REPCAT_AUTH
DOC>PACKAGE BODY DBMS_REPCAT_AUTH
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#
Querying for password protected roles ....
Password protected Role Assigned by default to user
------------------------------ ------------------------------
OWB$CLIENT OWBSYS
================
JVM Verification
================
================================================
Checking Existence of Java-Based Users and Roles
================================================
DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
There are 6 JAVA related roles
Roles
ROLE
------------------------------
JAVA_DEPLOY
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
=========================================
List of Invalid Java Objects owned by SYS
=========================================
There are no SYS owned invalid JAVA objects
DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
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 ***
Upload db_upg_diag_test_16_Jan_2020_0415.log from "/tmp" directory
SQL>
3 清空回收站 ，略 。
4 检查物化视图是否刷新完毕。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; no rows selected SQL>
5 收集统计信息app
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. SQL>
6 检查时区设置less
SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_14.dat 14 0 SQL> SQL> col property_name for a30 SQL> SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; 2 3 4 PROPERTY_NAME VALUE ------------------------------ ------------------------------------------------------------------------------------------------------------------------ DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL>
7 检查没有文件处于backup mode分布式
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; no rows selected SQL>
8 检查升级前是否有分布式事务，若是有，执行purge.
SQL> Select * from dba_2pc_pending;
SQL> select local_tran_id FROM dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;
9 检查Preupgrade，在源库执行，会生成一些建议之类的等等。按照建议执行脚本便可，本次测试没有执行这个
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir
10 进行升级
用12c的软件路径启动数据库，启动到upgrade模式 ，在linux命令行下执行dbupgrade命令，该命令调用catctl.pl文件和catupgrd.sql脚本。
CONNECT / AS SYSDBA SQL> startup upgrade; SQL> exit ./dbupgrade
-- 或者执行下面的命令
cd $ORACLE_HOME/rdbms/admin catctl or cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
-- 在dbupgrade里面有一段以下，能够看出，执行那个perl命令和执行dbupgrade命令效果是同样的，我这里直接执行dbupgrade命令了。
if [[ $# -gt 0 ]]
then
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib $ORACLE_HOME/rdbms/admin/catctl.pl "$@" $ORACLE_HOME/rdbms/admin/catupgrd.sql
else
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib $ORACLE_HOME/rdbms/admin/catctl.pl $ORACLE_HOME/rdbms/admin/catupgrd.sql
fi
exit $?
-- 执行过程 ，其中23步这里卡了大约4小时，缘由是job_queue_processes参数。设置为0后，马上经过。（可能和以前没有进行preupgrade脚本执行有关）（以前怀疑是这个脚本运行的时候，以4个并-行来运行致使的，测试机只有1个cpu，如今看应该不是并-行引发的。）
[oracle@wls10306-02 bin]$ ls dbup*
dbupgrade
[oracle@wls10306-02 bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/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]
Unable to Create [/tmp/cfgtoollogs/upgrade20200116165036]
Defaulting to [/tmp]
/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/dbhome_1]
/u01/app/oracle/product/12.2.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0/dbhome_1]
Analyzing file /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp]
catcon: ALL catcon-related output will be written to [/tmp/catupgrd_catcon_16733.lst]
catcon: See [/tmp/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = test
DataBase Version = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200116165037/catupgrd_catcon_16733.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200116165037/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200116165037/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200116165037]
cannot remove directory for /tmp/.X11-unix: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/.XIM-unix: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/.Test-unix: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/.font-unix: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/.ICE-unix: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/hsperfdata_root: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_6977-3879638603: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7058-2856323751: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot unlink file for /tmp/cfgtoollogs/upgrade20200116163737/catupgrd_trace.log: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot restore permissions to 0100644 for /tmp/cfgtoollogs/upgrade20200116163737/catupgrd_trace.log: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot unlink file for /tmp/cfgtoollogs/upgrade20200116163737/catupgrd0.log: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot restore permissions to 0100644 for /tmp/cfgtoollogs/upgrade20200116163737/catupgrd0.log: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/cfgtoollogs/upgrade20200116163737: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/cfgtoollogs: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/wlstTemproot: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp/.oracle: 1 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_9576-3126016563: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_6997-3853881822: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7076-2822900872: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7016-2864909219: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7060-2856454816: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7007-3879114322: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7027-3854537185: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot chdir to child for /tmp/vmware-root_7045-3887961966: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
cannot remove directory for /tmp: 13 at /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/catctl.pl line 3024.
Parallel SQL Process Count = 4
Components in [test]
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:[2020_01_16 16:50:38]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [test] Files:1 Time: 181s
*************** Catalog Core SQL ***************
Serial Phase #:1 [test] Files:5 Time: 81s
Restart Phase #:2 [test] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [test] Files:19 Time: 51s
Restart Phase #:4 [test] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [test] Files:6 Time: 33s
***************** Catproc Start ****************
Serial Phase #:6 [test] Files:1 Time: 24s
***************** Catproc Types ****************
Serial Phase #:7 [test] Files:2 Time: 25s
Restart Phase #:8 [test] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [test] Files:69 Time: 113s
Restart Phase #:10 [test] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [test] Files:1 Time: 75s
Restart Phase #:12 [test] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [test] Files:97 Time: 38s
Restart Phase #:14 [test] Files:1 Time: 0s
Parallel Phase #:15 [test] Files:118 Time: 40s
Restart Phase #:16 [test] Files:1 Time: 1s
Serial Phase #:17 [test] Files:13 Time: 4s
Restart Phase #:18 [test] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [test] Files:33 Time: 95s
Restart Phase #:20 [test] Files:1 Time: 1s
Serial Phase #:21 [test] Files:3 Time: 15s
Restart Phase #:22 [test] Files:1 Time: 0s
Parallel Phase #:23 [test] Files:24 Time: 13912s
Restart Phase #:24 [test] Files:1 Time: 0s
Parallel Phase #:25 [test] Files:11 Time: 102s
Restart Phase #:26 [test] Files:1 Time: 0s
Serial Phase #:27 [test] Files:1 Time: 0s
Serial Phase #:28 [test] Files:3 Time: 6s
Serial Phase #:29 [test] Files:1 Time: 0s
Restart Phase #:30 [test] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [test] Files:1 Time: 1s
Restart Phase #:32 [test] Files:1 Time: 0s
Serial Phase #:34 [test] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [test] Files:283 Time: 137s
Serial Phase #:36 [test] Files:1 Time: 0s
Restart Phase #:37 [test] Files:1 Time: 0s
Serial Phase #:38 [test] Files:1 Time: 7s
Restart Phase #:39 [test] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [test] Files:3 Time: 80s
Restart Phase #:41 [test] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [test] Files:13 Time: 119s
Restart Phase #:43 [test] Files:1 Time: 0s
Parallel Phase #:44 [test] Files:12 Time: 31s
Restart Phase #:45 [test] Files:1 Time: 0s
Parallel Phase #:46 [test] Files:2 Time: 1s
Restart Phase #:47 [test] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [test] Files:1 Time: 8s
Restart Phase #:49 [test] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [test] Files:1 Time: 31s
************ Upgrade Component Start ***********
Serial Phase #:51 [test] Files:1 Time: 1s
Restart Phase #:52 [test] Files:1 Time: 0s
**************** Upgrading Java ****************
Serial Phase #:53 [test] Files:1 Time: 332s
Restart Phase #:54 [test] Files:1 Time: 1s
***************** Upgrading XDK ****************
Serial Phase #:55 [test] Files:1 Time: 58s
Restart Phase #:56 [test] Files:1 Time: 0s
********* Upgrading APS,OLS,DV,CONTEXT *********
Serial Phase #:57 [test] Files:1 Time: 77s
***************** Upgrading XDB ****************
Restart Phase #:58 [test] Files:1 Time: 0s
Serial Phase #:60 [test] Files:3 Time: 33s
Serial Phase #:61 [test] Files:3 Time: 9s
Parallel Phase #:62 [test] Files:9 Time: 4s
Parallel Phase #:63 [test] Files:24 Time: 6s
Serial Phase #:64 [test] Files:4 Time: 7s
Serial Phase #:65 [test] Files:1 Time: 0s
Serial Phase #:66 [test] Files:30 Time: 4s
Serial Phase #:67 [test] Files:1 Time: 0s
Parallel Phase #:68 [test] Files:6 Time: 4s
Serial Phase #:69 [test] Files:2 Time: 20s
Serial Phase #:70 [test] Files:3 Time: 101s
Restart Phase #:71 [test] Files:1 Time: 0s
********* Upgrading CATJAVA,OWM,MGW,RAC ********
Serial Phase #:72 [test] Files:1 Time: 109s
**************** Upgrading ORDIM ***************
Restart Phase #:73 [test] Files:1 Time: 0s
Serial Phase #:75 [test] Files:1 Time: 1s
Parallel Phase #:76 [test] Files:2 Time: 99s
Serial Phase #:77 [test] Files:1 Time: 86s
Restart Phase #:78 [test] Files:1 Time: 1s
Parallel Phase #:79 [test] Files:2 Time: 14s
Serial Phase #:80 [test] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:81 [test] Files:1 Time: 0s
Serial Phase #:83 [test] Files:1 Time: 47s
Serial Phase #:84 [test] Files:1 Time: 2s
Restart Phase #:85 [test] Files:1 Time: 0s
Serial Phase #:86 [test] Files:1 Time: 32s
Restart Phase #:87 [test] Files:1 Time: 1s
Parallel Phase #:88 [test] Files:3 Time: 184s
Restart Phase #:89 [test] Files:1 Time: 0s
Serial Phase #:90 [test] Files:1 Time: 6s
Restart Phase #:91 [test] Files:1 Time: 0s
Serial Phase #:92 [test] Files:1 Time: 3s
Restart Phase #:93 [test] Files:1 Time: 0s
Parallel Phase #:94 [test] Files:4 Time: 146s
Restart Phase #:95 [test] Files:1 Time: 0s
Serial Phase #:96 [test] Files:1 Time: 1s
Restart Phase #:97 [test] Files:1 Time: 0s
Serial Phase #:98 [test] Files:2 Time: 62s
Restart Phase #:99 [test] Files:1 Time: 0s
Serial Phase #:100 [test] Files:1 Time: 1s
Restart Phase #:101 [test] Files:1 Time: 0s
*********** Upgrading Misc. ODM, OLAP **********
Serial Phase #:102 [test] Files:1 Time: 36s
**************** Upgrading APEX ****************
Restart Phase #:103 [test] Files:1 Time: 0s
Serial Phase #:104 [test] Files:1 Time: 792s
Restart Phase #:105 [test] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:106 [test] Files:1 Time: 0s
************* Final Upgrade scripts ************
Serial Phase #:107 [test] Files:1 Time: 144s
********** End PDB Application Upgrade *********
Serial Phase #:108 [test] Files:1 Time: 1s
******************* Migration ******************
Serial Phase #:109 [test] Files:1 Time: 51s
Serial Phase #:110 [test] Files:1 Time: 0s
Serial Phase #:111 [test] Files:1 Time: 92s
***************** Post Upgrade *****************
Serial Phase #:112 [test] Files:1 Time: 775s
**************** Summary report ****************
Serial Phase #:113 [test] Files:1 Time: 1s
Serial Phase #:114 [test] Files:1 Time: 0s
Serial Phase #:115 [test] Files:1 Time: 24s
------------------------------------------------------
Phases [0-115] End Time:[2020_01_16 22:00:27]
------------------------------------------------------
Grand Total Time: 18602s
LOG FILES: (/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200116165037/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200116165037/upg_summary.log
Grand Total Upgrade Time: [0d:5h:10m:2s]
[oracle@wls10306-02 bin]$
[END] 2020/1/17 8:18:03
-- 检查组件，已经都是12.2.0.1的了 。可是下面提示timezone版本仍是比较低的。
SQL> @?/rdbms/admin/utlu122s.sql Oracle Database 12.2 Post-Upgrade Status Tool 01-17-2020 08:22:55 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.2.0.1.0 04:13:32 JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:05:31 Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:01:27 OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:19 OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00 Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:19 Oracle XDK UPGRADED 12.2.0.1.0 00:00:57 Oracle Text UPGRADED 12.2.0.1.0 00:00:56 Oracle XML Database UPGRADED 12.2.0.1.0 00:03:06 Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:20 Oracle Multimedia UPGRADED 12.2.0.1.0 00:03:20 Spatial UPGRADED 12.2.0.1.0 00:08:02 Oracle Application Express UPGRADED 5.0.4.00.12 00:13:10 Final Actions 00:03:15 Post Upgrade 00:12:52 Total Upgrade Time: 05:07:42 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/dbhome_1/cfgtoollogs/test/upgrade20200116165037/upg_summary.log 08:22:56 SQL> 08:22:56 SQL>
-- 检查组件状态
set line 200 col COMP_ID format a10 col COMP_NAME format a35 select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry order by modified; COMP_ID COMP_NAME VERSION STATUS ---------- ----------------------------------- ---------------------------------------- -------------------------------------------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED CATPROC Oracle Database Packages and T 12.2.0.1.0 UPGRADED JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED XML Oracle XDK 12.2.0.1.0 UPGRADED APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED AMD OLAP Catalog 11.2.0.4.0 OPTION OFF CONTEXT Oracle Text 12.2.0.1.0 UPGRADED XDB Oracle XML Database 12.2.0.1.0 UPGRADED CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED COMP_ID COMP_NAME VERSION STATUS ---------- ----------------------------------- ---------------------------------------- -------------------------------------------- SDO Spatial 12.2.0.1.0 UPGRADED XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED APEX Oracle Application Express 5.0.4.00.1 UPGRADED 14 rows selected.
-- 检查兼容性 ，由于以前使用的是11.2.0.4的spfile，里面的兼容性是11.2.0.4.0 。须要修改下 。
SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0 noncdb_compatible boolean FALSE SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.2.0.1.0 noncdb_compatible boolean FALSE SQL>
11 升级后步骤，升级timezone，其余的略。
-- 升级timezone
Download the DBMS_DST_scriptsV1.9.zip file and unzip, it contains 4 files: upg_tzv_check.sql and upg_tzv_apply.sql , countstatsTSTZ.sql and countstarTSTZ.sql .
Copy the 4 files to your database server, the location of the scripts can be any directory on the server.
[oracle@wls10306-02 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 7213 Mar 17 2018 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@wls10306-02 DBMS_DST_scriptsV1.9]$
执行countstatsTSTZ.sql 、upg_tzv_check.sql、upg_tzv_apply.sql
countstatsTSTZ.sql -- list the stats num_row of all tables that have a TSTZ column (= processed by DBMS_DST ) and have actual data according to the stats.
upg_tzv_check.sql -- it will detect the highest installed DST patch automatically and needs no downtime, this can be executed on a live production database but it WILL purge the dba_recyclebin.
upg_tzv_apply.sql -- 这个应该是应用timezone ，真正的安装timezone
SQL> @/home/oracle/DBMS_DST_scriptsV1.9/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 16/01/2020 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 5 16/01/2020 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 5 16/01/2020 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 5 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 24/08/2013 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1 16/01/2020 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1 16/01/2020 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1 16/01/2020 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1 24/08/2013 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1 24/08/2013 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1 24/08/2013 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1 24/08/2013 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1 24/08/2013 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1 24/08/2013 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7 24/08/2013 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7 16/01/2020 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 19 16/01/2020 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 19 16/01/2020 - SYS.RADM_FPTM$.TSWTZ_COL - 1 16/01/2020 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 85 24/08/2013 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11 16/01/2020 - SYS.SCHEDULER$_JOB.END_DATE - 14 16/01/2020 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 14 16/01/2020 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 14 16/01/2020 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 14 16/01/2020 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 14 16/01/2020 - SYS.SCHEDULER$_JOB.START_DATE - 14 16/01/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 85 16/01/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 85 16/01/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 85 24/08/2013 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 3 24/08/2013 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 3 16/01/2020 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9 16/01/2020 - SYS.SCHEDULER$_WINDOW.END_DATE - 9 16/01/2020 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9 16/01/2020 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9 16/01/2020 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9 16/01/2020 - SYS.SCHEDULER$_WINDOW.START_DATE - 9 16/01/2020 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 6 16/01/2020 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 6 16/01/2020 - SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - 1 16/01/2020 - SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - 1 16/01/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 1126 16/01/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 1126 16/01/2020 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 224 16/01/2020 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 224 16/01/2020 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 224 16/01/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 735 16/01/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 735 Total numrow of SYS TSTZ columns is : 5000 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 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_L.DEQUEUE_TIME - 2 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - 4 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - 4 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - 4 24/08/2013 - IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - 1 24/08/2013 - IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - 1 24/08/2013 - IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - 1 Total numrow of non-SYS TSTZ columns is : 17 There are in total 32 non-SYS TSTZ columns. Total Minutes elapsed : 0 SQL>
SQL> @/home/oracle/DBMS_DST_scriptsV1.9/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>
--第三个脚本执行错误了，后面有解决方法。
SQL> @/home/oracle/DBMS_DST_scriptsV1.9/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 830472192 bytes Fixed Size 8626144 bytes Variable Size 511705120 bytes Database Buffers 306184192 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. SP2-1540: Oracle Database cannot startup in an Edition session. ERROR: ORA-01034: ORACLE not available Process ID: 72111 Session ID: 32 Serial number: 36090 Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
-- 再次检查timezone，变成了26 。
SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_26.dat 26 0 SQL> SQL> SELECT property_name, SUBSTR(property_value, 1, 30) value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name; 2 3 4 PROPERTY_NAME ------------------------------ VALUE ------------------------------------------------------------------------------------------------------------------------ DST_PRIMARY_TT_VERSION 26 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE UPGRADE
-- 正常状况下，上面的查询DST_SECONDARY_TT_VERSION应该是0，DST_UPGRADE_STATE应该是None 。根据MOS 1509653.1 进行处理。
CONN / as sysdba
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> set serveroutput on
SQL> VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE)SQL> 2 3 4 5 6 7 8 ;
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END; 9 10
11 /
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
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
Failures:0
PL/SQL procedure successfully completed.
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;SQL> 2 3 4
5 /
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
-- 处理完毕后，再次查看时区，正常了。
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 2 3 4 PROPERTY_NAME ---------------------------------------- VALUE ------------------------------------------------------------------------------------------------------------------------ DST_PRIMARY_TT_VERSION 26 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL>
11.2.0.4 ---> 12.2.0.1 到此结束。
END
---- 2010-01-17 下午，在另外一个机器上作升级，仍是11.2.0.4升级到12.2.0.1
-- 产生预检查修复脚-本，也能够经过MOS 1577379.1 884522.1下载 （在上午作第一次升-级的时候，跳过这个步骤了，此次升-级作一下。其实就是产生一些预升-级的脚-本，跑一些，可是这些脚本好些仍是须要手-工执-行的，而后产生post脚本，检查状态。其实这个预升-级的脚-本不作也能够。作下post脚-本查看下结果，有须要修改的修改下。就能够了）
/u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /u01/
++++++++++++++++++++++++++++
[oracle@wls10306-01 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /u01/
Preupgrade generated files:
/u01/preupgrade.log
/u01/preupgrade_fixups.sql
/u01/postupgrade_fixups.sql
[oracle@wls10306-01 ~]$
[oracle@wls10306-01 ~]$
[oracle@wls10306-01 u01]$ ll
total 508
drwxrwxr-x. 4 oracle oinstall 40 Jan 6 15:31 app
drwxr-xr-x 7 oracle oinstall 136 Aug 27 2013 database
-rw-r--r-- 1 oracle oinstall 5373 Jan 17 13:52 dbms_registry_basic.sql
-rw-r--r-- 1 oracle oinstall 12693 Jan 17 13:52 dbms_registry_extended.sql
drwxr-xr-x 3 oracle oinstall 21 Jan 17 13:52 oracle
-rw-r--r-- 1 oracle oinstall 8129 Jan 17 13:53 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 7027 Jan 17 13:52 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 9196 Jan 17 13:53 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 10685 Jan 17 13:53 preupgrade.log
-rw-r--r-- 1 oracle oinstall 60144 Jan 17 13:52 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 390244 Jan 17 13:52 preupgrade_package.sql
drwxr-xr-x 3 oracle oinstall 24 Jan 17 13:52 upgrade
[oracle@wls10306-01 u01]$
-- 执行preupgrade_fixups.sql 。大部分失败了，须要手-工执-行。
SQL> @/u01/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2020-01-17 13:53:04
For Source Database: TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
em_present Failed Manual fixup recommended.
amd_exists Failed Manual fixup recommended.
dictionary_stats Passed None
trgowner_no_admndbtrg Failed Manual fixup recommended.
mv_refresh Failed Manual fixup recommended.
apex_upgrade_msg Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
SQL> SQL>
-- 删除em
从12c的ORACLE_HOME中copy emremove.sql 到11.2.0.4的oracle home
emctl stop dbconsole
cp /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/
SQL> @?/rdbms/admin/emremove.sql
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
This script will drop the Oracle Enterprise Manager related schemas and objects.
This script might take few minutes to complete; it has 6 phases to complete the process.
The script may take longer if you have SYSMAN and related sessions are active
from Oracle Enterprise Manager(OEM) application.
Recommendations:
You are recommended to shutdown DB Control application immediately before running this
OEM repository removal script.
To shutdown DB Control application, you need to run: emctl stop dbconsole
Steps to be performed manually (after this script is run):
Please note that you need to remove the DB Control Configuration Files
manually to remove DB Control completly; remove the following
directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>
If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4,
then the following directory also needs to be removed from the file system.
<ORACLE_HOME>/<hostname_sid>.upgrade
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade
On Microsoft platforms, also delete the DB Console service, generally with name
OracleDBConsole<sid>
Starting phase 1 : Dropping AQ related objests, EM jobs and all Oracle Enterprise Manager related schemas; except SYSMAN ...
dropping AQ related objests from SYSMAN ...
saved job_queue_process=1000, set to 0, now removing Oracle EM jobs ...
finding users who needs to be dropped ...
found user name: MGMT_VIEW
found [sql%notfound]: no session found; or already killed.
Dropping user : "MGMT_VIEW"...
Finished phase 1
Starting phase 2 : Dropping SYSMAN schema ...
found [sql%notfound]: SYSMAN related sessions are already killed; no session found
dropping user : MGMT_VIEW...
SYSMAN dropped
Finished phase 3
Starting phase 4 : Dropping Oracle Enterprise Manager related MGMT_USER role ...
Finished phase 4
Starting phase 5 : Dropping Oracle Enterprise Manager related public synonyms ...
Dropping synonym : ECM_UTIL ...
Dropping synonym : EMD_MNTR ...
Dropping synonym : MGMT$ALERT_ANNOTATIONS ...
Dropping synonym : MGMT$ALERT_CURRENT ...
Dropping synonym : MGMT$ALERT_HISTORY ...
Dropping synonym : MGMT$ALERT_NOTIF_LOG ...
Dropping synonym : MGMT$APPLIED_PATCHES ...
Dropping synonym : MGMT$APPLIED_PATCHSETS ...
Dropping synonym : MGMT$APPL_PATCH_AND_PATCHSET ...
Dropping synonym : MGMT$AUDIT_LOG ...
Dropping synonym : MGMT$AVAILABILITY_CURRENT ...
Dropping synonym : MGMT$AVAILABILITY_HISTORY ...
Dropping synonym : MGMT$BLACKOUTS ...
Dropping synonym : MGMT$BLACKOUT_HISTORY ...
Dropping synonym : MGMT$CLUSTER_INTERCONNECTS ...
Dropping synonym : MGMT$CPF_ADVISORY_INFO ...
Dropping synonym : MGMT$CPF_HOMES_INFO ...
Dropping synonym : MGMT$CPF_PATCH_DATA ...
Dropping synonym : MGMT$CPF_PATCH_INFO ...
Dropping synonym : MGMT$CSA_CLIENTS ...
Dropping synonym : MGMT$CSA_CLIENT_RULE_VIOLS ...
Dropping synonym : MGMT$CSA_COLLECTIONS ...
Dropping synonym : MGMT$CSA_FAILED ...
Dropping synonym : MGMT$CSA_HOST_COOKIES ...
Dropping synonym : MGMT$CSA_HOST_CPUS ...
Dropping synonym : MGMT$CSA_HOST_CUSTOM ...
Dropping synonym : MGMT$CSA_HOST_IOCARDS ...
Dropping synonym : MGMT$CSA_HOST_NICS ...
Dropping synonym : MGMT$CSA_HOST_OS_COMPONENTS ...
Dropping synonym : MGMT$CSA_HOST_OS_FILESYSTEMS ...
Dropping synonym : MGMT$CSA_HOST_OS_PROPERTIES ...
Dropping synonym : MGMT$CSA_HOST_RULES ...
Dropping synonym : MGMT$CSA_HOST_SW ...
Dropping synonym : MGMT$CSM_DOMAIN_DAILY ...
Dropping synonym : MGMT$CSM_DOMAIN_DIST_DAILY ...
Dropping synonym : MGMT$CSM_DOMAIN_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_DOMAIN_HOURLY ...
Dropping synonym : MGMT$CSM_IP_DAILY ...
Dropping synonym : MGMT$CSM_IP_DIST_DAILY ...
Dropping synonym : MGMT$CSM_IP_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_IP_HOURLY ...
Dropping synonym : MGMT$CSM_METRIC_DETAILS ...
Dropping synonym : MGMT$CSM_MT_DSR_DAILY ...
Dropping synonym : MGMT$CSM_MT_DSR_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_DSR_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_DSR_HOURLY ...
Dropping synonym : MGMT$CSM_MT_IP_DAILY ...
Dropping synonym : MGMT$CSM_MT_IP_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_IP_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_IP_HOURLY ...
Dropping synonym : MGMT$CSM_MT_METRIC_DETAILS ...
Dropping synonym : MGMT$CSM_MT_URL_DAILY ...
Dropping synonym : MGMT$CSM_MT_URL_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_URL_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_URL_HOURLY ...
Dropping synonym : MGMT$CSM_REGION ...
Dropping synonym : MGMT$CSM_REGION_DAILY ...
Dropping synonym : MGMT$CSM_REGION_DIST_DAILY ...
Dropping synonym : MGMT$CSM_REGION_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_REGION_HOURLY ...
Dropping synonym : MGMT$CSM_SUBNET_DAILY ...
Dropping synonym : MGMT$CSM_SUBNET_DIST_DAILY ...
Dropping synonym : MGMT$CSM_SUBNET_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_SUBNET_HOURLY ...
Dropping synonym : MGMT$CSM_URL_DAILY ...
Dropping synonym : MGMT$CSM_URL_DIST_DAILY ...
Dropping synonym : MGMT$CSM_URL_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_URL_HOURLY ...
Dropping synonym : MGMT$CSM_WATCHLIST ...
Dropping synonym : MGMT$CS_CONFIG_STANDARDS ...
Dropping synonym : MGMT$CS_EVAL_SUMMARY_RULE ...
Dropping synonym : MGMT$CS_EVAL_SUMMARY_STANDARD ...
Dropping synonym : MGMT$DB_CONTROLFILES ...
Dropping synonym : MGMT$DB_CONTROLFILES_ALL ...
Dropping synonym : MGMT$DB_DATAFILES ...
Dropping synonym : MGMT$DB_DATAFILES_ALL ...
Dropping synonym : MGMT$DB_DBNINSTANCEINFO ...
Dropping synonym : MGMT$DB_DBNINSTANCEINFO_ALL ...
Dropping synonym : MGMT$DB_FEATUREUSAGE ...
Dropping synonym : MGMT$DB_INIT_PARAMS ...
Dropping synonym : MGMT$DB_INIT_PARAMS_ALL ...
Dropping synonym : MGMT$DB_LICENSE ...
Dropping synonym : MGMT$DB_LICENSE_ALL ...
Dropping synonym : MGMT$DB_OPTIONS ...
Dropping synonym : MGMT$DB_OPTIONS_ALL ...
Dropping synonym : MGMT$DB_REDOLOGS ...
Dropping synonym : MGMT$DB_REDOLOGS_ALL ...
Dropping synonym : MGMT$DB_ROLLBACK_SEGS ...
Dropping synonym : MGMT$DB_ROLLBACK_SEGS_ALL ...
Dropping synonym : MGMT$DB_SGA ...
Dropping synonym : MGMT$DB_SGA_ALL ...
Dropping synonym : MGMT$DB_TABLESPACES ...
Dropping synonym : MGMT$DB_TABLESPACES_ALL ...
Dropping synonym : MGMT$DELTA_COMPONENTS ...
Dropping synonym : MGMT$DELTA_COMPONENT_DETAILS ...
Dropping synonym : MGMT$DELTA_FS_MOUNT ...
Dropping synonym : MGMT$DELTA_HARDWARE ...
Dropping synonym : MGMT$DELTA_HOST_CONFIG ...
Dropping synonym : MGMT$DELTA_INIT ...
Dropping synonym : MGMT$DELTA_ONEOFF_PATCHES ...
Dropping synonym : MGMT$DELTA_ORACLE_HOME ...
Dropping synonym : MGMT$DELTA_OS_COMPONENTS ...
Dropping synonym : MGMT$DELTA_OS_COMP_DETAILS ...
Dropping synonym : MGMT$DELTA_OS_KERNEL_PARAMS ...
Dropping synonym : MGMT$DELTA_PATCHSETS ...
Dropping synonym : MGMT$DELTA_PATCHSET_DETAILS ...
Dropping synonym : MGMT$DELTA_VENDOR_SW ...
Dropping synonym : MGMT$DELTA_VIEW ...
Dropping synonym : MGMT$DELTA_VIEW_DETAILS ...
Dropping synonym : MGMT$E2E_1DAY ...
Dropping synonym : MGMT$E2E_HOURLY ...
Dropping synonym : MGMT$E2E_RAW ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY1 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY2 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY3 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY4 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY5 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY6 ...
Dropping synonym : MGMT$ECM_CURRENT_SNAPSHOTS ...
Dropping synonym : MGMT$ECM_VISIBLE_SNAPSHOTS ...
Dropping synonym : MGMT$EM_HOMES_PLATFORM ...
Dropping synonym : MGMT$ESA_ALL_PRIVS_REPORT ...
Dropping synonym : MGMT$ESA_ANY_DICT_REPORT ...
Dropping synonym : MGMT$ESA_ANY_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_AUDIT_SYSTEM_REPORT ...
Dropping synonym : MGMT$ESA_BECOME_USER_REPORT ...
Dropping synonym : MGMT$ESA_CATALOG_REPORT ...
Dropping synonym : MGMT$ESA_CONN_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_CREATE_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_DBA_GROUP_REPORT ...
Dropping synonym : MGMT$ESA_DBA_ROLE_REPORT ...
Dropping synonym : MGMT$ESA_DIRECT_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_EXMPT_ACCESS_REPORT ...
Dropping synonym : MGMT$ESA_KEY_OBJECTS_REPORT ...
Dropping synonym : MGMT$ESA_OH_OWNERSHIP_REPORT ...
Dropping synonym : MGMT$ESA_OH_PERMISSION_REPORT ...
Dropping synonym : MGMT$ESA_POWER_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_PUB_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_SYS_PUB_PKG_REPORT ...
Dropping synonym : MGMT$ESA_TABSP_OWNERS_REPORT ...
Dropping synonym : MGMT$ESA_TRC_AUD_PERM_REPORT ...
Dropping synonym : MGMT$ESA_TRC_AUD_PERM_REP_NT ...
Dropping synonym : MGMT$ESA_WITH_ADMIN_REPORT ...
Dropping synonym : MGMT$ESA_WITH_GRANT_REPORT ...
Dropping synonym : MGMT$GROUP_DERIVED_MEMBERSHIPS ...
Dropping synonym : MGMT$GROUP_FLAT_MEMBERSHIPS ...
Dropping synonym : MGMT$GROUP_MEMBERS ...
Dropping synonym : MGMT$GRP_METRICS_DAILY ...
Dropping synonym : MGMT$GRP_METRICS_HOURLY ...
Dropping synonym : MGMT$GRP_METRICS_RAW ...
Dropping synonym : MGMT$HA_BACKUP ...
Dropping synonym : MGMT$HA_FILES ...
Dropping synonym : MGMT$HA_FILES_ALL ...
Dropping synonym : MGMT$HA_INFO ...
Dropping synonym : MGMT$HA_INFO_ALL ...
Dropping synonym : MGMT$HA_INIT_PARAMS ...
Dropping synonym : MGMT$HA_INIT_PARAMS_ALL ...
Dropping synonym : MGMT$HA_MTTR ...
Dropping synonym : MGMT$HA_RMAN_CONFIG ...
Dropping synonym : MGMT$HA_RMAN_CONFIG_ALL ...
Dropping synonym : MGMT$HOMES_AFFECTED ...
Dropping synonym : MGMT$HOSTPATCH_GROUPS ...
Dropping synonym : MGMT$HOSTPATCH_GRP_COMPL_HIST ...
Dropping synonym : MGMT$HOSTPATCH_HOSTS ...
Dropping synonym : MGMT$HOSTPATCH_HOST_COMPL ...
Dropping synonym : MGMT$HW_NIC ...
Dropping synonym : MGMT$INTERFACE_STATS ...
Dropping synonym : MGMT$JOBS ...
Dropping synonym : MGMT$JOB_ANNOTATIONS ...
Dropping synonym : MGMT$JOB_EXECUTION_HISTORY ...
Dropping synonym : MGMT$JOB_NOTIFICATION_LOG ...
Dropping synonym : MGMT$JOB_STEP_HISTORY ...
Dropping synonym : MGMT$JOB_TARGETS ...
Dropping synonym : MGMT$MESSAGES ...
Dropping synonym : MGMT$METRIC_CATEGORIES ...
Dropping synonym : MGMT$METRIC_COLLECTION ...
Dropping synonym : MGMT$METRIC_CURRENT ...
Dropping synonym : MGMT$METRIC_DAILY ...
Dropping synonym : MGMT$METRIC_DETAILS ...
Dropping synonym : MGMT$METRIC_ERROR_CURRENT ...
Dropping synonym : MGMT$METRIC_ERROR_HISTORY ...
Dropping synonym : MGMT$METRIC_HOURLY ...
Dropping synonym : MGMT$MISSING_TARGETS ...
Dropping synonym : MGMT$MISSING_TARGETS_IN_GROUPS ...
Dropping synonym : MGMT$ORACLE_SW_ENT_INSTALL ...
Dropping synonym : MGMT$ORACLE_SW_ENT_TARGETS ...
Dropping synonym : MGMT$ORACLE_SW_GRP_INSTALL ...
Dropping synonym : MGMT$ORACLE_SW_GRP_TARGETS ...
Dropping synonym : MGMT$OS_COMPONENTS ...
Dropping synonym : MGMT$OS_FS_MOUNT ...
Dropping synonym : MGMT$OS_HW_SUMMARY ...
Dropping synonym : MGMT$OS_KERNEL_PARAMS ...
Dropping synonym : MGMT$OS_PATCHES ...
Dropping synonym : MGMT$OS_PROPERTIES ...
Dropping synonym : MGMT$OS_SUMMARY ...
Dropping synonym : MGMT$PATCH_ADVISORIES ...
Dropping synonym : MGMT$POLICIES ...
Dropping synonym : MGMT$POLICY_PARAMETERS ...
Dropping synonym : MGMT$POLICY_VIOLATION_CONTEXT ...
Dropping synonym : MGMT$POLICY_VIOLATION_CTXT ...
Dropping synonym : MGMT$POLICY_VIOLATION_CURRENT ...
Dropping synonym : MGMT$POLICY_VIOLATION_HISTORY ...
Dropping synonym : MGMT$POLICY_VIOL_ANNOTATIONS ...
Dropping synonym : MGMT$POLICY_VIOL_NOTIF_LOG ...
Dropping synonym : MGMT$RACDB_INTERCONNECTS ...
Dropping synonym : MGMT$SOFTWARE_COMPONENTS ...
Dropping synonym : MGMT$SOFTWARE_COMPONENT_ONEOFF ...
Dropping synonym : MGMT$SOFTWARE_COMP_PATCHSET ...
Dropping synonym : MGMT$SOFTWARE_DEPENDENCIES ...
Dropping synonym : MGMT$SOFTWARE_HOMES ...
Dropping synonym : MGMT$SOFTWARE_HOME_PROPERTIES ...
Dropping synonym : MGMT$SOFTWARE_ONEOFF_PATCHES ...
Dropping synonym : MGMT$SOFTWARE_OTHERS ...
Dropping synonym : MGMT$SOFTWARE_PATCHES_IN_HOMES ...
Dropping synonym : MGMT$SOFTWARE_PATCHSETS ...
Dropping synonym : MGMT$STEPS ...
Dropping synonym : MGMT$STEP_GROUPS ...
Dropping synonym : MGMT$STEP_METRICS_DAILY ...
Dropping synonym : MGMT$STEP_METRICS_HOURLY ...
Dropping synonym : MGMT$STEP_METRICS_RAW ...
Dropping synonym : MGMT$STORAGE_REPORT_DATA ...
Dropping synonym : MGMT$STORAGE_REPORT_DISK ...
Dropping synonym : MGMT$STORAGE_REPORT_ISSUES ...
Dropping synonym : MGMT$STORAGE_REPORT_KEYS ...
Dropping synonym : MGMT$STORAGE_REPORT_LOCALFS ...
Dropping synonym : MGMT$STORAGE_REPORT_NFS ...
Dropping synonym : MGMT$STORAGE_REPORT_PATHS ...
Dropping synonym : MGMT$STORAGE_REPORT_VOLUME ...
Dropping synonym : MGMT$TARGET ...
Dropping synonym : MGMT$TARGET_ASSOCIATIONS ...
Dropping synonym : MGMT$TARGET_COMPONENTS ...
Dropping synonym : MGMT$TARGET_COMPOSITE ...
Dropping synonym : MGMT$TARGET_FLAT_MEMBERS ...
Dropping synonym : MGMT$TARGET_MEMBERS ...
Dropping synonym : MGMT$TARGET_METRIC_COLLECTIONS ...
Dropping synonym : MGMT$TARGET_METRIC_SETTINGS ...
Dropping synonym : MGMT$TARGET_POLICIES ...
Dropping synonym : MGMT$TARGET_POLICY_EVAL_SUMM ...
Dropping synonym : MGMT$TARGET_POLICY_SETTINGS ...
Dropping synonym : MGMT$TARGET_PROPERTIES ...
Dropping synonym : MGMT$TARGET_TYPE ...
Dropping synonym : MGMT$TARGET_TYPE_DEF ...
Dropping synonym : MGMT$TARGET_TYPE_PROPERTIES ...
Dropping synonym : MGMT$TEMPLATES ...
Dropping synonym : MGMT$TEMPLATE_METRICCOLLECTION ...
Dropping synonym : MGMT$TEMPLATE_METRIC_SETTINGS ...
Dropping synonym : MGMT$TEMPLATE_POLICY_SETTINGS ...
Dropping synonym : MGMT$TXN_PERF_DAY ...
Dropping synonym : MGMT$TXN_PERF_HOUR ...
Dropping synonym : MGMT$TXN_PERF_RAW ...
Dropping synonym : MGMT_ADMIN ...
Dropping synonym : MGMT_AVAILABILITY ...
Dropping synonym : MGMT_TARGET_BLACKOUTS ...
Dropping synonym : MGMT_COLLECTION_PROPERTIES ...
Dropping synonym : MGMT_CREDENTIAL ...
Dropping synonym : MGMT_CURRENT_AVAILABILITY ...
Dropping synonym : MGMT_CURRENT_METRICS ...
Dropping synonym : MGMT_CURRENT_METRIC_ERRORS ...
Dropping synonym : MGMT_CURRENT_SEVERITY ...
Dropping synonym : MGMT_DELTA ...
Dropping synonym : MGMT_DELTA_ENTRY ...
Dropping synonym : MGMT_DELTA_ENTRY_VALUES ...
Dropping synonym : MGMT_DELTA_IDS ...
Dropping synonym : MGMT_DELTA_ID_VALUES ...
Dropping synonym : MGMT_DELTA_VALUE ...
Dropping synonym : MGMT_DELTA_VALUES ...
Dropping synonym : MGMT_GLOBAL ...
Dropping synonym : MGMT_GUID_ARRAY ...
Dropping synonym : MGMT_GUID_OBJ ...
Dropping synonym : MGMT_IP_TGT_GUID_ARRAY ...
Dropping synonym : MGMT_JOB ...
Dropping synonym : MGMT_JOBS ...
Dropping synonym : MGMT_JOB_EXECPLAN ...
Dropping synonym : MGMT_JOB_EXECUTION ...
Dropping synonym : MGMT_JOB_EXEC_SUMMARY ...
Dropping synonym : MGMT_JOB_OUTPUT ...
Dropping synonym : MGMT_JOB_PARAMETER ...
Dropping synonym : MGMT_JOB_SCHEDULE ...
Dropping synonym : MGMT_JOB_TARGET ...
Dropping synonym : MGMT_LOG ...
Dropping synonym : MGMT_LONG_TEXT ...
Dropping synonym : MGMT_MESSAGES ...
Dropping synonym : MGMT_METRICS ...
Dropping synonym : MGMT_METRICS_1DAY ...
Dropping synonym : MGMT_METRICS_1HOUR ...
Dropping synonym : MGMT_METRICS_COMPOSITE_KEYS ...
Dropping synonym : MGMT_METRICS_RAW ...
Dropping synonym : MGMT_METRIC_COLLECTIONS ...
Dropping synonym : MGMT_METRIC_ERRORS ...
Dropping synonym : MGMT_METRIC_THRESHOLDS ...
Dropping synonym : MGMT_NAME_VALUE ...
Dropping synonym : MGMT_NAME_VALUES ...
Dropping synonym : MGMT_PAF$APPLICATIONS ...
Dropping synonym : MGMT_PAF$INSTANCES ...
Dropping synonym : MGMT_PAF$PROCEDURES ...
Dropping synonym : MGMT_PAF$STATES ...
Dropping synonym : MGMT_PAF_JOBS ...
Dropping synonym : MGMT_PAF_PROCS_LATEST ...
Dropping synonym : MGMT_PREFERENCES ...
Dropping synonym : MGMT_SEVERITY ...
Dropping synonym : MGMT_SEVERITY_ARRAY ...
Dropping synonym : MGMT_SEVERITY_OBJ ...
Dropping synonym : MGMT_STRING_METRIC_HISTORY ...
Dropping synonym : MGMT_TARGET ...
Dropping synonym : MGMT_TARGETS ...
Dropping synonym : MGMT_TARGET_MEMBERSHIPS ...
Dropping synonym : MGMT_TARGET_PROPERTIES ...
Dropping synonym : MGMT_TYPE_PROPERTIES ...
Dropping synonym : MGMT_USER ...
Dropping synonym : MGMT_VIEW_UTIL ...
Dropping synonym : SETEMVIEWUSERCONTEXT ...
Dropping synonym : SMP_EMD_AVAIL_OBJ ...
Dropping synonym : SMP_EMD_DELETE_REC_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_NVPAIR ...
Dropping synonym : SMP_EMD_NVPAIR_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_TARGET_OBJ ...
Dropping synonym : SMP_EMD_TARGET_OBJ_ARRAY ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Process DBSNMP user
User DBSNMP is locked
Done processing DBSNMP user
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this script and then delete the DB Control configuration files
PL/SQL procedure successfully completed.
SQL>
-- 删除olap Remove OLAP Catalog by running the 11.2.0.4.0， 大量的synonym,view,type,role等drop。
SQL> @?/olap/admin/catnoamd.sql Synonym dropped. View dropped. Type dropped. View dropped. Type dropped. PL/SQL procedure successfully completed. Role dropped. PL/SQL procedure successfully completed. 1 row deleted. SQL>
-- Gather stale data dictionary statistics prior to database upgrade in off-peak time using
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL>
--授予ADMINISTER DATABASE TRIGGER权限，查询没有值，不授予
SQL> SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE='DATABASE' AND
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE='ADMINISTER DATABASE TRIGGER') 2 3 4 ;
no rows selected
SQL>
--刷新物化视图 ，略
--手动升-级apex，根据1088970.1 。目的是为了减小升-级时间。这里不升-级。
开始升-级 ,job_queue_process=0 后， 23步没有卡，很快就结束了。耗时大约90分钟（虚拟机电脑配置差，2G内存）。
./dbupgrade
[oracle@wls10306-01 bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/12.2.0/dbhome_1//rdbms/admin/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/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/dbhome_1/]
/u01/app/oracle/product/12.2.0/dbhome_1//bin/orabasehome = [/u01/app/oracle/product/12.2.0/dbhome_1/]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0/dbhome_1/]
Analyzing file /u01/app/oracle/product/12.2.0/dbhome_1//rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20200117143709]
catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200117143709/catupgrd_catcon_17337.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20200117143709/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20200117143709/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = test
DataBase Version = 11.2.0.4.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200117143711/catupgrd_catcon_17337.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200117143711/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200117143711/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200117143711]
Parallel SQL Process Count = 4
Components in [test]
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-115] Start Time:[2020_01_17 14:37:12]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [test] Files:1 Time: 233s
*************** Catalog Core SQL ***************
Serial Phase #:1 [test] Files:5 Time: 72s
Restart Phase #:2 [test] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [test] Files:19 Time: 47s
Restart Phase #:4 [test] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [test] Files:6 Time: 32s
***************** Catproc Start ****************
Serial Phase #:6 [test] Files:1 Time: 28s
***************** Catproc Types ****************
Serial Phase #:7 [test] Files:2 Time: 24s
Restart Phase #:8 [test] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [test] Files:69 Time: 74s
Restart Phase #:10 [test] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [test] Files:1 Time: 54s
Restart Phase #:12 [test] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [test] Files:97 Time: 38s
Restart Phase #:14 [test] Files:1 Time: 1s
Parallel Phase #:15 [test] Files:118 Time: 34s
Restart Phase #:16 [test] Files:1 Time: 0s
Serial Phase #:17 [test] Files:13 Time: 4s
Restart Phase #:18 [test] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [test] Files:33 Time: 60s
Restart Phase #:20 [test] Files:1 Time: 0s
Serial Phase #:21 [test] Files:3 Time: 13s
Restart Phase #:22 [test] Files:1 Time: 1s
Parallel Phase #:23 [test] Files:24 Time: 261s
Restart Phase #:24 [test] Files:1 Time: 0s
Parallel Phase #:25 [test] Files:11 Time: 122s
Restart Phase #:26 [test] Files:1 Time: 0s
Serial Phase #:27 [test] Files:1 Time: 0s
Serial Phase #:28 [test] Files:3 Time: 5s
Serial Phase #:29 [test] Files:1 Time: 0s
Restart Phase #:30 [test] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [test] Files:1 Time: 1s
Restart Phase #:32 [test] Files:1 Time: 0s
Serial Phase #:34 [test] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [test] Files:283 Time: 136s
Serial Phase #:36 [test] Files:1 Time: 0s
Restart Phase #:37 [test] Files:1 Time: 0s
Serial Phase #:38 [test] Files:1 Time: 8s
Restart Phase #:39 [test] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [test] Files:3 Time: 77s
Restart Phase #:41 [test] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [test] Files:13 Time: 124s
Restart Phase #:43 [test] Files:1 Time: 1s
Parallel Phase #:44 [test] Files:12 Time: 42s
Restart Phase #:45 [test] Files:1 Time: 1s
Parallel Phase #:46 [test] Files:2 Time: 2s
Restart Phase #:47 [test] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [test] Files:1 Time: 12s
Restart Phase #:49 [test] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [test] Files:1 Time: 38s
************ Upgrade Component Start ***********
Serial Phase #:51 [test] Files:1 Time: 0s
Restart Phase #:52 [test] Files:1 Time: 1s
**************** Upgrading Java ****************
Serial Phase #:53 [test] Files:1 Time: 678s
Restart Phase #:54 [test] Files:1 Time: 1s
***************** Upgrading XDK ****************
Serial Phase #:55 [test] Files:1 Time: 83s
Restart Phase #:56 [test] Files:1 Time: 1s
********* Upgrading APS,OLS,DV,CONTEXT *********
Serial Phase #:57 [test] Files:1 Time: 100s
***************** Upgrading XDB ****************
Restart Phase #:58 [test] Files:1 Time: 1s
Serial Phase #:60 [test] Files:3 Time: 43s
Serial Phase #:61 [test] Files:3 Time: 15s
Parallel Phase #:62 [test] Files:9 Time: 6s
Parallel Phase #:63 [test] Files:24 Time: 10s
Serial Phase #:64 [test] Files:4 Time: 15s
Serial Phase #:65 [test] Files:1 Time: 0s
Serial Phase #:66 [test] Files:30 Time: 6s
Serial Phase #:67 [test] Files:1 Time: 0s
Parallel Phase #:68 [test] Files:6 Time: 6s
Serial Phase #:69 [test] Files:2 Time: 39s
Serial Phase #:70 [test] Files:3 Time: 161s
Restart Phase #:71 [test] Files:1 Time: 1s
********* Upgrading CATJAVA,OWM,MGW,RAC ********
Serial Phase #:72 [test] Files:1 Time: 188s
**************** Upgrading ORDIM ***************
Restart Phase #:73 [test] Files:1 Time: 1s
Serial Phase #:75 [test] Files:1 Time: 1s
Parallel Phase #:76 [test] Files:2 Time: 160s
Serial Phase #:77 [test] Files:1 Time: 126s
Restart Phase #:78 [test] Files:1 Time: 1s
Parallel Phase #:79 [test] Files:2 Time: 21s
Serial Phase #:80 [test] Files:2 Time: 2s
***************** Upgrading SDO ****************
Restart Phase #:81 [test] Files:1 Time: 0s
Serial Phase #:83 [test] Files:1 Time: 60s
Serial Phase #:84 [test] Files:1 Time: 2s
Restart Phase #:85 [test] Files:1 Time: 1s
Serial Phase #:86 [test] Files:1 Time: 43s
Restart Phase #:87 [test] Files:1 Time: 1s
Parallel Phase #:88 [test] Files:3 Time: 257s
Restart Phase #:89 [test] Files:1 Time: 1s
Serial Phase #:90 [test] Files:1 Time: 9s
Restart Phase #:91 [test] Files:1 Time: 0s
Serial Phase #:92 [test] Files:1 Time: 4s
Restart Phase #:93 [test] Files:1 Time: 1s
Parallel Phase #:94 [test] Files:4 Time: 218s
Restart Phase #:95 [test] Files:1 Time: 0s
Serial Phase #:96 [test] Files:1 Time: 1s
Restart Phase #:97 [test] Files:1 Time: 0s
Serial Phase #:98 [test] Files:2 Time: 85s
Restart Phase #:99 [test] Files:1 Time: 0s
Serial Phase #:100 [test] Files:1 Time: 1s
Restart Phase #:101 [test] Files:1 Time: 1s
*********** Upgrading Misc. ODM, OLAP **********
Serial Phase #:102 [test] Files:1 Time: 41s
**************** Upgrading APEX ****************
Restart Phase #:103 [test] Files:1 Time: 1s
Serial Phase #:104 [test] Files:1 Time: 1044s
Restart Phase #:105 [test] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:106 [test] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:107 [test] Files:1 Time: 148s
********** End PDB Application Upgrade *********
Serial Phase #:108 [test] Files:1 Time: 1s
******************* Migration ******************
Serial Phase #:109 [test] Files:1 Time: 59s
Serial Phase #:110 [test] Files:1 Time: 0s
Serial Phase #:111 [test] Files:1 Time: 50s
***************** Post Upgrade *****************
Serial Phase #:112 [test] Files:1 Time: 169s
**************** Summary report ****************
Serial Phase #:113 [test] Files:1 Time: 2s
Serial Phase #:114 [test] Files:1 Time: 0s
Serial Phase #:115 [test] Files:1 Time: 19s
------------------------------------------------------
Phases [0-115] End Time:[2020_01_17 16:07:49]
------------------------------------------------------
Grand Total Time: 5449s
LOG FILES: (/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200117143711/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/test/upgrade20200117143711/upg_summary.log
Grand Total Upgrade Time: [0d:1h:30m:49s]
[oracle@wls10306-01 bin]$
SQL> @?/rdbms/admin/utlu122s.sql Oracle Database 12.2 Post-Upgrade Status Tool 01-17-2020 16:09:04 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.2.0.1.0 00:25:42 JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:11:16 Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:02:33 OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:20 Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:21 Oracle XDK UPGRADED 12.2.0.1.0 00:01:22 Oracle Text UPGRADED 12.2.0.1.0 00:01:18 Oracle XML Database UPGRADED 12.2.0.1.0 00:05:00 Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:33 Oracle Multimedia UPGRADED 12.2.0.1.0 00:05:10 Spatial UPGRADED 12.2.0.1.0 00:11:21 Oracle Application Express UPGRADED 5.0.4.00.12 00:17:22 Final Actions 00:03:28 Post Upgrade 00:02:48 Total Upgrade Time: 01:29:17 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/dbhome_1/cfgtoollogs/test/upgrade20200117143711/upg_summary.log 16:09:05 SQL> 16:09:05 SQL>
-- 运行post 脚-本
[oracle@wls10306-01 u01]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 17 16:12:16 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/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: 1
Generated on: 2020-01-17 13:53:06
For Source Database: TEST
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
depend_usr_tables Failed Manual fixup recommended.
old_time_zones_exist Failed Manual fixup recommended.
post_dictionary Passed None
fixed_objects Passed None
upg_by_std_upgrd Passed None
PL/SQL procedure successfully completed.
Session altered.
SQL>
-- 升级time-zone。此次执行三个脚-本，没有出错。
[oracle@wls10306-01 bin]$ sqlplus /nolog SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 17 16:17:51 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> conn / as sysdba Connected. SQL> @/home/oracle/DBMS_DST_scriptsV1.9/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 17/01/2020 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 5 17/01/2020 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 5 17/01/2020 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 5 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 24/08/2013 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1 17/01/2020 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.CREATION_TIME - 1 17/01/2020 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.DELETION_TIME - 1 17/01/2020 - SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.MODIFICATION_TIME - 1 17/01/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1 17/01/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1 17/01/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1 17/01/2020 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3 17/01/2020 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3 17/01/2020 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3 24/08/2013 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1 24/08/2013 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1 17/01/2020 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1 17/01/2020 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1 17/01/2020 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1 17/01/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4 17/01/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4 17/01/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4 17/01/2020 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1 17/01/2020 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1 17/01/2020 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1 17/01/2020 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1 17/01/2020 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1 17/01/2020 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7 17/01/2020 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7 17/01/2020 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 39 17/01/2020 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 39 17/01/2020 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 360 17/01/2020 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 35 17/01/2020 - SYS.RADM_FPTM$.TSWTZ_COL - 1 17/01/2020 - SYS.REG$.NTFN_GROUPING_START_TIME - 2 17/01/2020 - SYS.REG$.REG_TIME - 2 17/01/2020 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 32 17/01/2020 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11 17/01/2020 - SYS.SCHEDULER$_JOB.END_DATE - 26 17/01/2020 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 26 17/01/2020 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 26 17/01/2020 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 26 17/01/2020 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 26 17/01/2020 - SYS.SCHEDULER$_JOB.START_DATE - 26 17/01/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 18 17/01/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 18 17/01/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 18 17/01/2020 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4 17/01/2020 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4 17/01/2020 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9 17/01/2020 - SYS.SCHEDULER$_WINDOW.END_DATE - 9 17/01/2020 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9 17/01/2020 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9 17/01/2020 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9 17/01/2020 - SYS.SCHEDULER$_WINDOW.START_DATE - 9 17/01/2020 - SYS.TAB_STATS$.SPARE6 - 1122 17/01/2020 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 53 17/01/2020 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 53 17/01/2020 - SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - 1 17/01/2020 - SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - 1 17/01/2020 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 33185 17/01/2020 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 33185 17/01/2020 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 58022 17/01/2020 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 58022 17/01/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 4124 17/01/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 4124 17/01/2020 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 200 17/01/2020 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 200 17/01/2020 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 200 17/01/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 6409 17/01/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 6409 17/01/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 6409 17/01/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 3836 17/01/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 3836 17/01/2020 - SYS.XS$PRIN.END_DATE - 15 17/01/2020 - SYS.XS$PRIN.START_DATE - 15 Total numrow of SYS TSTZ columns is : 220296 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/01/2020 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1 17/01/2020 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1 17/01/2020 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_L.DEQUEUE_TIME - 2 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - 4 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - 4 24/08/2013 - IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - 4 24/08/2013 - IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - 1 24/08/2013 - IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - 1 24/08/2013 - IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - 1 17/01/2020 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1 17/01/2020 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1 17/01/2020 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1 17/01/2020 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1 17/01/2020 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1 Total numrow of non-SYS TSTZ columns is : 25 There are in total 32 non-SYS TSTZ columns. Total Minutes elapsed : 0 SQL> @/home/oracle/DBMS_DST_scriptsV1.9/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> @/home/oracle/DBMS_DST_scriptsV1.9/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 830472192 bytes Fixed Size 8626144 bytes Variable Size 511705120 bytes Database Buffers 306184192 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 830472192 bytes Fixed Size 8626144 bytes Variable Size 511705120 bytes Database Buffers 306184192 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: "IX"."AQ$_ORDERS_QUEUETABLE_L" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S" Number of failures: 0 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>
-- 查看time-zone ，正常了 。
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 2 3 4 PROPERTY_NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- DST_PRIMARY_TT_VERSION 26 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE 3 rows selected. SQL>
END