Applying the DSTv20 update for the Oracle Database (Doc ID 1552819.1)
[oracle@pxebssdl1 ~]$ cd 16799735
[oracle@pxebssdl1 ~]$ sqlplus / as sysdba
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
14
[oracle@pxebssdl1 16799735]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oinstall 4583 May 29 2013 README.txt
-rw-r--r-- 1 oracle oinstall 14574 May 29 2013 README.html
drwxr-xr-x 3 oracle oinstall 4096 May 29 2013 files
drwxr-xr-x 4 oracle oinstall 4096 May 29 2013 etc
[oracle@pxebssdl1 16799735]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/oracle/PROD/db/tech_st/11.2.0.3
Central Inventory : /home/oracle/oraInventory
from : /u01/oracle/PROD/db/tech_st/11.2.0.3//oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.3.0
Log file location : /u01/oracle/PROD/db/tech_st/11.2.0.3/cfgtoollogs/opatch/16799735_Jul_28_2014_08_11_00/apply2014-07-28_08-11-00AM_1.log
Applying interim patch '16799735' to OH '/u01/oracle/PROD/db/tech_st/11.2.0.3'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Backing up files...
Patching component oracle.oracore.rsf, 11.2.0.3.0...
Verifying the update...
Patch 16799735 successfully applied
Log file location: /u01/oracle/PROD/db/tech_st/11.2.0.3/cfgtoollogs/opatch/16799735_Jul_28_2014_08_11_00/apply2014-07-28_08-11-00AM_1.log
OPatch succeeded.
Step 1:
[oracle@pxebssdl1 ~]$ cd 16799735/files/oracore/zoneinfo/big
[oracle@pxebssdl1 big]$ ls -ltr
total 1116
-rwxr-xr-x 1 oracle oinstall 343167 May 29 2013 timezone_20.dat
-rwxr-xr-x 1 oracle oinstall 789973 May 29 2013 timezlrg_20.dat
[oracle@pxebssdl1 big]$ cp -p * /u01/oracle/PROD/db/tech_st/11.2.0.3/oracore/zoneinfo/big/
[oracle@pxebssdl1 ~]$ cd 16799735/files/oracore/zoneinfo/little
[oracle@pxebssdl1 little]$ ls -ltr
total 1116
-rwxr-xr-x 1 oracle oinstall 343167 May 29 2013 timezone_20.dat
-rwxr-xr-x 1 oracle oinstall 789973 May 29 2013 timezlrg_20.dat
[oracle@pxebssdl1 little]$ cp -p * /u01/oracle/PROD/db/tech_st/11.2.0.3/oracore/zoneinfo/little
Step 2 :
++++++++++++++
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)
+++++++++++++++
[oracle@pxebssdl1 DBMS_DST_scriptsV1.8]$ ls -ltr
total 60
-rw-r--r-- 1 oracle oinstall 4784 Mar 17 18:33 countTSTZdata.sql
-rw-r--r-- 1 oracle oinstall 30772 Jul 10 15:02 upg_tzv_check.sql
-rw-r--r-- 1 oracle oinstall 16931 Jul 10 15:03 upg_tzv_apply.sql
[oracle@pxebssdl1 DBMS_DST_scriptsV1.8]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 28 08:31:22 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @countTSTZdata.sql
Estimating amount of TSTZ data.
This might take some time....
.
For SYS tables first...
Note: empty tables are not listed.
Owner.Tablename.Columnname - count star of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - Count * is : 4
SYS.AQ$_ALERT_QT_S.DELETION_TIME - Count * is : 4
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - Count * is : 4
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - Count * is : 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - Count * is : 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - Count * is : 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - Count * is : 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - Count * is : 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.CREATION_TIME - Count * is : 1
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.DELETION_TIME - Count * is : 1
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - Count * is : 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - Count * is : 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - Count * is : 3
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - Count * is : 3
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - Count * is : 3
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - Count * is : 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - Count * is : 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - Count * is : 7
SYS.KET$_CLIENT_TASKS.CURR_WIN_START - Count * is : 2
SYS.KET$_CLIENT_TASKS.LG_DATE - Count * is : 2
SYS.KET$_CLIENT_TASKS.LT_DATE - Count * is : 2
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - Count * is : 19
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - Count * is : 19
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - Count * is : 114
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - Count * is : 11
SYS.SCHEDULER$_JOB.END_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - Count * is : 14
SYS.SCHEDULER$_JOB.LAST_END_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.LAST_START_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.START_DATE - Count * is : 14
SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - Count * is : 46
SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - Count * is : 46
SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - Count * is : 46
SYS.SCHEDULER$_SCHEDULE.END_DATE - Count * is : 3
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - Count * is : 3
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.END_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - Count * is : 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - Count * is : 3
SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - Count * is : 3
SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - Count * is : 3
SYS.WRI$_ALERT_HISTORY.CREATION_TIME - Count * is : 42
SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - Count * is : 42
SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - Count * is : 2
SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - Count * is : 2
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - Count * is : 29840
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - Count * is : 29840
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - Count * is : 27006
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - Count * is : 27006
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - Count * is : 2131
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - Count * is : 2131
SYS.WRI$_OPTSTAT_OPR.END_TIME - Count * is : 355
SYS.WRI$_OPTSTAT_OPR.SPARE6 - Count * is : 355
SYS.WRI$_OPTSTAT_OPR.START_TIME - Count * is : 355
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - Count * is : 1989
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - Count * is : 1989
Total count * of SYS TSTZ columns ROWS is : 123612
There are in total 124 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.Tablename.Columnname - count star of that column
APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S.CREATION_TIME - Count * is : 2
APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S.DELETION_TIME - Count * is : 2
APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S.MODIFICATION_TIME - Count * is : 2
APPLSYS.AQ$_FND_CP_TM_AQTBL_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_AQTBL_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_AQTBL_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_RET_AQTBL_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_RET_AQTBL_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_RET_AQTBL_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_CONTROL_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_CONTROL_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_CONTROL_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_ERROR_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_ERROR_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_ERROR_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_DEFERRED_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_DEFERRED_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_DEFERRED_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_ERROR_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_ERROR_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_ERROR_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_JMS_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_JMS_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_REPLAY_IN_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_IN_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_IN_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_OUT_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_OUT_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_OUT_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_WS_JMS_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_SAMPLE_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_SAMPLE_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_SAMPLE_S.MODIFICATION_TIME - Count * is : 3
APPS.AQ$_WF_BPEL_QTAB_S.CREATION_TIME - Count * is : 1
APPS.AQ$_WF_BPEL_QTAB_S.DELETION_TIME - Count * is : 1
APPS.AQ$_WF_BPEL_QTAB_S.MODIFICATION_TIME - Count * is : 1
AR.AQ$_AR_REV_REC_QT_S.CREATION_TIME - Count * is : 3
AR.AQ$_AR_REV_REC_QT_S.DELETION_TIME - Count * is : 3
AR.AQ$_AR_REV_REC_QT_S.MODIFICATION_TIME - Count * is : 3
AR.AQ$_HZ_JMS_OUT_S.CREATION_TIME - Count * is : 3
AR.AQ$_HZ_JMS_OUT_S.DELETION_TIME - Count * is : 3
AR.AQ$_HZ_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
ASO.AQ$_ASO_ORDER_FEEDBACK_T_S.CREATION_TIME - Count * is : 3
ASO.AQ$_ASO_ORDER_FEEDBACK_T_S.DELETION_TIME - Count * is : 3
ASO.AQ$_ASO_ORDER_FEEDBACK_T_S.MODIFICATION_TIME - Count * is : 3
CS.AQ$_CS_SERVICE_REQUEST_IQT_S.CREATION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_IQT_S.DELETION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_IQT_S.MODIFICATION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_OQT_S.CREATION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_OQT_S.DELETION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_OQT_S.MODIFICATION_TIME - Count * is : 1
OKC.AQ$_OKC_AQ_EV_TAB_S.CREATION_TIME - Count * is : 6
OKC.AQ$_OKC_AQ_EV_TAB_S.DELETION_TIME - Count * is : 6
OKC.AQ$_OKC_AQ_EV_TAB_S.MODIFICATION_TIME - Count * is : 6
PO.AQ$_PO_OTM_OUT_S.CREATION_TIME - Count * is : 3
PO.AQ$_PO_OTM_OUT_S.DELETION_TIME - Count * is : 3
PO.AQ$_PO_OTM_OUT_S.MODIFICATION_TIME - Count * is : 3
XNP.AQ$_XNP_OUT_MSG_QTAB_S.CREATION_TIME - Count * is : 3
XNP.AQ$_XNP_OUT_MSG_QTAB_S.DELETION_TIME - Count * is : 3
XNP.AQ$_XNP_OUT_MSG_QTAB_S.MODIFICATION_TIME - Count * is : 3
Total count * of non-SYS TSTZ columns ROWS is : 243
There are in total 125 non-SYS TSTZ columns.
Total Minutes elapsed : 2
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 11.2.0.3 .
INFO: Database RDBMS DST version is DSTv10 .
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 DSTv20 .
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 DSTv20 .
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 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 465568952 bytes
Database Buffers 587202560 bytes
Redo Buffers 13930496 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 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 465568952 bytes
Database Buffers 587202560 bytes
Redo Buffers 13930496 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: "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_S"
Number of failures: 0
Table list: "OKC"."AQ$_OKC_AQ_EV_TAB_S"
Number of failures: 0
Table list: "AR"."AQ$_AR_REV_REC_QT_S"
Number of failures: 0
Table list: "XNP"."AQ$_XNP_OUT_MSG_QTAB_S"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_OQT_S"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_IQT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_ERROR_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_CONTROL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_DEFERRED_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_ERROR_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_SAMPLE_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_AQTBL_S"
Number of failures: 0
Table list: "AR"."AQ$_HZ_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_IPC_AQTBL_S"
Number of failures: 0
Table list: "PO"."AQ$_PO_OTM_OUT_S"
Number of failures: 0
Table list: "APPS"."AQ$_WF_BPEL_QTAB_S"
Number of failures: 0
Table list: "AR"."AQ$_AR_REV_REC_QT_L"
Number of failures: 0
Table list: "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_L"
Number of failures: 0
Table list: "PO"."AQ$_PO_OTM_OUT_L"
Number of failures: 0
Table list: "XNP"."AQ$_XNP_OUT_MSG_QTAB_L"
Number of failures: 0
Table list: "APPS"."AQ$_WF_BPEL_QTAB_L"
Number of failures: 0
Table list: "OKC"."AQ$_OKC_AQ_EV_TAB_L"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_OQT_L"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_IQT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_ERROR_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_CONTROL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_DEFERRED_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_ERROR_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_SAMPLE_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_IPC_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_AQTBL_L"
Number of failures: 0
Table list: "AR"."AQ$_HZ_JMS_OUT_L"
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 DSTv20 .
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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@pxebssdl1 DBMS_DST_scriptsV1.8]$ cd
[oracle@pxebssdl1 ~]$
[oracle@pxebssdl1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 28 08:44:16 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
20
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@pxebssdl1 ~]$
[oracle@pxebssdl1 ~]$ cd 16799735
[oracle@pxebssdl1 ~]$ sqlplus / as sysdba
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
14
[oracle@pxebssdl1 16799735]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oinstall 4583 May 29 2013 README.txt
-rw-r--r-- 1 oracle oinstall 14574 May 29 2013 README.html
drwxr-xr-x 3 oracle oinstall 4096 May 29 2013 files
drwxr-xr-x 4 oracle oinstall 4096 May 29 2013 etc
[oracle@pxebssdl1 16799735]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/oracle/PROD/db/tech_st/11.2.0.3
Central Inventory : /home/oracle/oraInventory
from : /u01/oracle/PROD/db/tech_st/11.2.0.3//oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.3.0
Log file location : /u01/oracle/PROD/db/tech_st/11.2.0.3/cfgtoollogs/opatch/16799735_Jul_28_2014_08_11_00/apply2014-07-28_08-11-00AM_1.log
Applying interim patch '16799735' to OH '/u01/oracle/PROD/db/tech_st/11.2.0.3'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Backing up files...
Patching component oracle.oracore.rsf, 11.2.0.3.0...
Verifying the update...
Patch 16799735 successfully applied
Log file location: /u01/oracle/PROD/db/tech_st/11.2.0.3/cfgtoollogs/opatch/16799735_Jul_28_2014_08_11_00/apply2014-07-28_08-11-00AM_1.log
OPatch succeeded.
Step 1:
[oracle@pxebssdl1 ~]$ cd 16799735/files/oracore/zoneinfo/big
[oracle@pxebssdl1 big]$ ls -ltr
total 1116
-rwxr-xr-x 1 oracle oinstall 343167 May 29 2013 timezone_20.dat
-rwxr-xr-x 1 oracle oinstall 789973 May 29 2013 timezlrg_20.dat
[oracle@pxebssdl1 big]$ cp -p * /u01/oracle/PROD/db/tech_st/11.2.0.3/oracore/zoneinfo/big/
[oracle@pxebssdl1 ~]$ cd 16799735/files/oracore/zoneinfo/little
[oracle@pxebssdl1 little]$ ls -ltr
total 1116
-rwxr-xr-x 1 oracle oinstall 343167 May 29 2013 timezone_20.dat
-rwxr-xr-x 1 oracle oinstall 789973 May 29 2013 timezlrg_20.dat
[oracle@pxebssdl1 little]$ cp -p * /u01/oracle/PROD/db/tech_st/11.2.0.3/oracore/zoneinfo/little
Step 2 :
++++++++++++++
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)
+++++++++++++++
[oracle@pxebssdl1 DBMS_DST_scriptsV1.8]$ ls -ltr
total 60
-rw-r--r-- 1 oracle oinstall 4784 Mar 17 18:33 countTSTZdata.sql
-rw-r--r-- 1 oracle oinstall 30772 Jul 10 15:02 upg_tzv_check.sql
-rw-r--r-- 1 oracle oinstall 16931 Jul 10 15:03 upg_tzv_apply.sql
[oracle@pxebssdl1 DBMS_DST_scriptsV1.8]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 28 08:31:22 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @countTSTZdata.sql
Estimating amount of TSTZ data.
This might take some time....
.
For SYS tables first...
Note: empty tables are not listed.
Owner.Tablename.Columnname - count star of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - Count * is : 4
SYS.AQ$_ALERT_QT_S.DELETION_TIME - Count * is : 4
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - Count * is : 4
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - Count * is : 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - Count * is : 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - Count * is : 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - Count * is : 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - Count * is : 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.CREATION_TIME - Count * is : 1
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.DELETION_TIME - Count * is : 1
SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - Count * is : 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - Count * is : 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - Count * is : 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - Count * is : 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - Count * is : 3
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - Count * is : 3
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - Count * is : 3
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - Count * is : 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - Count * is : 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - Count * is : 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - Count * is : 7
SYS.KET$_CLIENT_TASKS.CURR_WIN_START - Count * is : 2
SYS.KET$_CLIENT_TASKS.LG_DATE - Count * is : 2
SYS.KET$_CLIENT_TASKS.LT_DATE - Count * is : 2
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - Count * is : 19
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - Count * is : 19
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - Count * is : 114
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - Count * is : 11
SYS.SCHEDULER$_JOB.END_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - Count * is : 14
SYS.SCHEDULER$_JOB.LAST_END_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.LAST_START_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - Count * is : 14
SYS.SCHEDULER$_JOB.START_DATE - Count * is : 14
SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - Count * is : 46
SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - Count * is : 46
SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - Count * is : 46
SYS.SCHEDULER$_SCHEDULE.END_DATE - Count * is : 3
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - Count * is : 3
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.END_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - Count * is : 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW.START_DATE - Count * is : 9
SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - Count * is : 3
SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - Count * is : 3
SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - Count * is : 3
SYS.WRI$_ALERT_HISTORY.CREATION_TIME - Count * is : 42
SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - Count * is : 42
SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - Count * is : 2
SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - Count * is : 2
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - Count * is : 29840
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - Count * is : 29840
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - Count * is : 27006
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - Count * is : 27006
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - Count * is : 2131
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - Count * is : 2131
SYS.WRI$_OPTSTAT_OPR.END_TIME - Count * is : 355
SYS.WRI$_OPTSTAT_OPR.SPARE6 - Count * is : 355
SYS.WRI$_OPTSTAT_OPR.START_TIME - Count * is : 355
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - Count * is : 1989
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - Count * is : 1989
Total count * of SYS TSTZ columns ROWS is : 123612
There are in total 124 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.Tablename.Columnname - count star of that column
APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S.CREATION_TIME - Count * is : 2
APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S.DELETION_TIME - Count * is : 2
APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_S.MODIFICATION_TIME - Count * is : 2
APPLSYS.AQ$_FND_CP_TM_AQTBL_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_AQTBL_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_AQTBL_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_RET_AQTBL_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_RET_AQTBL_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_FND_CP_TM_RET_AQTBL_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_CONTROL_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_CONTROL_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_CONTROL_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_DEFERRED_TABLE_M_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_ERROR_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_ERROR_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_ERROR_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_DEFERRED_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_DEFERRED_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_DEFERRED_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_ERROR_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_ERROR_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JAVA_ERROR_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_JMS_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_JMS_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_NOTIFICATION_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_REPLAY_IN_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_IN_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_IN_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_OUT_S.CREATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_OUT_S.DELETION_TIME - Count * is : 1
APPLSYS.AQ$_WF_REPLAY_OUT_S.MODIFICATION_TIME - Count * is : 1
APPLSYS.AQ$_WF_WS_JMS_IN_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_IN_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_IN_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_OUT_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_OUT_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_SAMPLE_S.CREATION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_SAMPLE_S.DELETION_TIME - Count * is : 3
APPLSYS.AQ$_WF_WS_SAMPLE_S.MODIFICATION_TIME - Count * is : 3
APPS.AQ$_WF_BPEL_QTAB_S.CREATION_TIME - Count * is : 1
APPS.AQ$_WF_BPEL_QTAB_S.DELETION_TIME - Count * is : 1
APPS.AQ$_WF_BPEL_QTAB_S.MODIFICATION_TIME - Count * is : 1
AR.AQ$_AR_REV_REC_QT_S.CREATION_TIME - Count * is : 3
AR.AQ$_AR_REV_REC_QT_S.DELETION_TIME - Count * is : 3
AR.AQ$_AR_REV_REC_QT_S.MODIFICATION_TIME - Count * is : 3
AR.AQ$_HZ_JMS_OUT_S.CREATION_TIME - Count * is : 3
AR.AQ$_HZ_JMS_OUT_S.DELETION_TIME - Count * is : 3
AR.AQ$_HZ_JMS_OUT_S.MODIFICATION_TIME - Count * is : 3
ASO.AQ$_ASO_ORDER_FEEDBACK_T_S.CREATION_TIME - Count * is : 3
ASO.AQ$_ASO_ORDER_FEEDBACK_T_S.DELETION_TIME - Count * is : 3
ASO.AQ$_ASO_ORDER_FEEDBACK_T_S.MODIFICATION_TIME - Count * is : 3
CS.AQ$_CS_SERVICE_REQUEST_IQT_S.CREATION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_IQT_S.DELETION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_IQT_S.MODIFICATION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_OQT_S.CREATION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_OQT_S.DELETION_TIME - Count * is : 1
CS.AQ$_CS_SERVICE_REQUEST_OQT_S.MODIFICATION_TIME - Count * is : 1
OKC.AQ$_OKC_AQ_EV_TAB_S.CREATION_TIME - Count * is : 6
OKC.AQ$_OKC_AQ_EV_TAB_S.DELETION_TIME - Count * is : 6
OKC.AQ$_OKC_AQ_EV_TAB_S.MODIFICATION_TIME - Count * is : 6
PO.AQ$_PO_OTM_OUT_S.CREATION_TIME - Count * is : 3
PO.AQ$_PO_OTM_OUT_S.DELETION_TIME - Count * is : 3
PO.AQ$_PO_OTM_OUT_S.MODIFICATION_TIME - Count * is : 3
XNP.AQ$_XNP_OUT_MSG_QTAB_S.CREATION_TIME - Count * is : 3
XNP.AQ$_XNP_OUT_MSG_QTAB_S.DELETION_TIME - Count * is : 3
XNP.AQ$_XNP_OUT_MSG_QTAB_S.MODIFICATION_TIME - Count * is : 3
Total count * of non-SYS TSTZ columns ROWS is : 243
There are in total 125 non-SYS TSTZ columns.
Total Minutes elapsed : 2
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 11.2.0.3 .
INFO: Database RDBMS DST version is DSTv10 .
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 DSTv20 .
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 DSTv20 .
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 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 465568952 bytes
Database Buffers 587202560 bytes
Redo Buffers 13930496 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 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 465568952 bytes
Database Buffers 587202560 bytes
Redo Buffers 13930496 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: "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_S"
Number of failures: 0
Table list: "OKC"."AQ$_OKC_AQ_EV_TAB_S"
Number of failures: 0
Table list: "AR"."AQ$_AR_REV_REC_QT_S"
Number of failures: 0
Table list: "XNP"."AQ$_XNP_OUT_MSG_QTAB_S"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_OQT_S"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_IQT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_ERROR_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_CONTROL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_IN_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_DEFERRED_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_ERROR_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_SAMPLE_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_AQTBL_S"
Number of failures: 0
Table list: "AR"."AQ$_HZ_JMS_OUT_S"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_IPC_AQTBL_S"
Number of failures: 0
Table list: "PO"."AQ$_PO_OTM_OUT_S"
Number of failures: 0
Table list: "APPS"."AQ$_WF_BPEL_QTAB_S"
Number of failures: 0
Table list: "AR"."AQ$_AR_REV_REC_QT_L"
Number of failures: 0
Table list: "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_L"
Number of failures: 0
Table list: "PO"."AQ$_PO_OTM_OUT_L"
Number of failures: 0
Table list: "XNP"."AQ$_XNP_OUT_MSG_QTAB_L"
Number of failures: 0
Table list: "APPS"."AQ$_WF_BPEL_QTAB_L"
Number of failures: 0
Table list: "OKC"."AQ$_OKC_AQ_EV_TAB_L"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_OQT_L"
Number of failures: 0
Table list: "CS"."AQ$_CS_SERVICE_REQUEST_IQT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_DEFERRED_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_ERROR_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_REPLAY_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_CONTROL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_NOTIFICATION_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_IN_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_DEFERRED_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JAVA_ERROR_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_JMS_JMS_OUT_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_WF_WS_SAMPLE_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_IPC_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_RET_AQTBL_L"
Number of failures: 0
Table list: "APPLSYS"."AQ$_FND_CP_TM_AQTBL_L"
Number of failures: 0
Table list: "AR"."AQ$_HZ_JMS_OUT_L"
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 DSTv20 .
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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@pxebssdl1 DBMS_DST_scriptsV1.8]$ cd
[oracle@pxebssdl1 ~]$
[oracle@pxebssdl1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 28 08:44:16 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
20
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@pxebssdl1 ~]$
