UPDATING THE RDBMS DST VERSION IN 11GR2 USING DBMS_DST

Reason: there is one BUG with Time Zone(TZ or DST_PRIMARY_TT_VERSION) and Oracle Streams technology. Well between two databases should be the same TZ version. 

In my case on one DB there was TZ 11 and on another 14 and there was ORA-600. Oracle server versions and editions are the same 11.2.0.2 x64 on RHEL 5. Information applies to any platform.

So how to update TZ from 11 to 14:

My ENV

USERNAME              HOST_NAME           VERSION

--------------                       -----------------          ------------

SYS                             lv-oracle-test1             11.2.0.2.0

SQL >

  SELECT   PROPERTY_NAME, SUBSTR (property_value, 1, 30) VALUE
    FROM   DATABASE_PROPERTIES
   WHERE   PROPERTY_NAME LIKE 'DST_%'
ORDER BY   PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL>  exec DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       14

DST_UPGRADE_STATE              PREPARE

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL>

Table truncated.

SQL>

Table truncated.

set serveroutput on

BEGIN

DBMS_DST.FIND_AFFECTED_TABLES

(affected_tables => 'sys.dst$affected_tables',

log_errors => TRUE,

log_errors_table => 'sys.dst$error_table');

END;

SQL>   2    3    4    5    6    7  /

PL/SQL procedure successfully completed.

SQL> SQL> SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;

A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL>

SQL>

SQL> shutdown immediate

startup upgrade;

set serveroutput on

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

Database closed.

Database dismounted.

purge dba_recyclebin;

ORACLE instance shut down.

SQL> ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2233088 bytes

Variable Size             784338176 bytes

Database Buffers          251658240 bytes

Redo Buffers                5656576 bytes

Database mounted.

Database opened.

SQL> SQL>   2    3    4

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         11

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL>

DBA Recyclebin purged.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL>

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       11

DST_UPGRADE_STATE              UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG

------------------------------ ------------------------------ ---

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES

SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES

SYSMAN                         MGMT_PROV_ASSIGNMENT           YES

SYSMAN                         MGMT_PROV_BOOTSERVER           YES

SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES

SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

SYSMAN                         MGMT_PROV_IP_RANGE             YES

SYSMAN                         MGMT_PROV_NET_CONFIG           YES

SYSMAN                         MGMT_PROV_OPERATION            YES

SYSMAN                         MGMT_PROV_RPM_REP              YES

SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

STREAMADMIN                    AQ$_DST_BOOK_ENTRIES_T_L       YES

STREAMADMIN                    AQ$_DST_BOOK_ENTRIES_T_S       YES

STREAMADMIN                    AQ$_DST_MSGLOG_MESSAGES_T_L    YES

STREAMADMIN                    AQ$_DST_MSGLOG_MESSAGES_T_S    YES

18 rows selected.

SQL>

SQL>

shutdown immediate

startup

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> ORACLE instance started.

Total System Global Area 1043886080 bytes

Fixed Size                  2233088 bytes

Variable Size             784338176 bytes

Database Buffers          251658240 bytes

Redo Buffers                5656576 bytes

Database mounted.

Database opened.

SQL> SQL> SQL>

SQL>

SQL>

SQL>

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> set serveroutput on

VAR numfail number

log_errors_table => 'SYS.DST$ERROR_TABLE',

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;

SQL> SQL>   2    3    4    5    6    7    8    9   10   11  /

Table list: "SYSMAN"."MGMT_PROV_SUITE_INST_MEMBERS"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_STAGING_DIRS"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_RPM_REP"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_OPERATION"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_NET_CONFIG"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_IP_RANGE"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_DEFAULT_IMAGE"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_CLUSTER_NODES"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_BOOTSERVER"

Number of failures: 0

Table list: "SYSMAN"."MGMT_PROV_ASSIGNMENT"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_L"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"

Number of failures: 0

Table list: "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_L"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_MSGLOG_MESSAGES_T_S"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_MSGLOG_MESSAGES_T_L"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_BOOK_ENTRIES_T_S"

Number of failures: 0

Table list: "STREAMADMIN"."AQ$_DST_BOOK_ENTRIES_T_L"

Number of failures: 0

Failures:0

PL/SQL procedure successfully completed.

VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

  /

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

SQL>

SQL>

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION

-------------------- ----------

timezlrg_14.dat              14

more [ID 977512.1]