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ı.

tz

 

 

 

 

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.