11.2.0.3’ten 11.2.0.4’e upgrade ederken aşağıdaki hatayı verdi çünkü 11.2.0.3 db’de timezone versiyonu 28 idi (yaz saati patch geçişi sonrası) 11.2.0.4 yeni Oracle home’unda ise timezone versiyonu default kurulumda 14 olduğundan dolayı.
11.2.0.3’de timezone versiyonunu 28’den 14’e downgrade etmeme izin vermiyordu;
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 28
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> exec DBMS_DST.BEGIN_PREPARE(14);
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;
*
ERROR at line 1:
ORA-56921: invalid time zone version
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1360
ORA-06512: at line 1
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> BEGIN
2 DBMS_DST.FIND_AFFECTED_TABLES
3 (affected_tables => 'sys.dst$affected_tables',
4 log_errors => TRUE,
5 log_errors_table => 'sys.dst$error_table');
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-56924: prepare window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1491
ORA-06512: at line 2
SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected
SQL> EXEC DBMS_DST.END_PREPARE;
BEGIN DBMS_DST.END_PREPARE; END;
*
ERROR at line 1:
ORA-56924: prepare window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1421
ORA-06512: at line 1
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
BEGIN DBMS_DST.BEGIN_UPGRADE(14); END;
*
ERROR at line 1:
ORA-56921: invalid time zone version
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1178
ORA-06512: at line 1
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> exec DBMS_DST.BEGIN_PREPARE(14)
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;
*
ERROR at line 1:
ORA-56921: invalid time zone version
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1360
ORA-06512: at line 1
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
BEGIN DBMS_DST.BEGIN_UPGRADE(14); END;
*
ERROR at line 1:
ORA-56921: invalid time zone version
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1178
ORA-06512: at line 1
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
BEGIN
*
ERROR at line 1:
ORA-56928: upgrade window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1034
ORA-06512: at line 2
SQL> VAR fail number
SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:fail);
3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-56928: upgrade window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1265
ORA-06512: at line 2
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
28
- Oracle’ın aşağıdaki Doc ID’sinde DST timezone versiyonunu daha aşağı bir değere downgarde edemediğimizi belirtiyor:
E.3) Can I “downgrade” to a lower DST version?
No, that not possible, please do NOT rollback dst patches who are used by the database(s) of the home in question.
RDBMS DST patches can be rolledback using Opatch but you cannot “downgrade” the DST version using DBMS_DST.
When the DST version specified when using DBMS_DST is lower than the current used DBMS_DST will fail with ORA-56921: invalid time zone version
If you have for example an DSTv23 database ( DST_PRIMARY_TT_VERSION = 23) and you rollback the DSTv23 patch using Opatch then the database will open but will NOT function properly.
In the alert.log the message “Daylight Saving Time feature is disabled” will appear, ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi init] may be seen (see note 362036.1)
Seen all DST version are cumulative there is also no real reason to “downgrade” a database DST version.
Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)
- 11.2.0.4 Oracle Home’una da yaz saati değişikliğinde geçtiğim 24701840 patch’ini uyguladıktan sonra her iki Oracle Home’un timezone versiyonu 28 olarak eşitlenmiş oldu ve dbau – Upgrade’de aldığım hatayı geçmiş oldum.
testdbserver01@oracle export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_2 testdbserver01@oracle $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 11.2.0.3.15 Copyright (c) 2017, Oracle Corporation. All rights reserved. Oracle Home : /oracle/app/oracle/product/11.2.0/dbhome_2 Central Inventory : /oracle/app/oraInventory from : /oracle/app/oracle/product/11.2.0/dbhome_2/oraInst.loc OPatch version : 11.2.0.3.15 OUI version : 11.2.0.4.0 Log file location : /oracle/app/oracle/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2017-02-16_09-30-33AM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 24701840 Do you want to proceed? [y|n] Y User Responded with: Y 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... Applying interim patch '24701840' to OH '/oracle/app/oracle/product/11.2.0/dbhome_2' ApplySession: Optional component(s) [ oracle.oracore.rsf.core, 11.2.0.4.0 ] not present in the Oracle Home or a higher version is found. Patching component oracle.oracore.rsf, 11.2.0.4.0... Patching in all-node mode. Updating nodes 'fbdbadb02t' Apply-related files are: FP = "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/copy_files.txt" DP = "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/copy_dirs.txt" MP = "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/make_cmds.txt" RC = "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/remote_cmds.txt" Instantiating the file "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/copy_files.txt" with actual path. Propagating files to remote nodes... Instantiating the file "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/app/oracle/product/11.2.0/dbhome_2/.patch_storage/NApply/2017-02-16_09-30-33AM/rac/copy_dirs.txt" with actual path. Propagating directories to remote nodes... Patch 24701840 successfully applied. Log file location: /oracle/app/oracle/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2017-02-16_09-30-33AM_1.log OPatch succeeded.
Orhan Eripek's Oracle Blog 