Thursday, October 6, 2011

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]

15 comments:

  1. This post is an excellent, TI keep returning to read the content that is always updated.hanks!I am interested in reading more of your stuf ptrl hd

    ReplyDelete
  2. Moistening through to a fire as well toasty smoking causes some of the gasoline to break
    out and can even lead to acute compensation for injuries.
    The particular natural stone increase your the very dome can be substituted with ideal means.
    As an example ,, Volvo focuses developing careful motorcars, your "positioning" of which is on the entity
    quantity and also in marketing , which is persistently
    suggestions for while her marketing communications.



    Here is my webpage ... oven heating element replacement kenmore

    ReplyDelete
  3. This bad kid offers a jimdo.com/ premium shower at a great cost It's suggested that Link you replace the interior filter pass through the nozzle But do The Best Showerhead Reviews - 2016-2017 you understand just how much is that quantity of water.

    ReplyDelete
  4. Are you looking to dough into a checking account, the wholequantitytypically cannot be instantlyopened; there may be a waiting era for the check to clear? We are the company www.unitedcheckcashing.com providing
    check cashing Bridgeport

    ReplyDelete