Oracle DST 18 Update

Upgrade to Oracle DST  version  18 bundled with RDBMS version 12.1.0.2

• Current DST Version
• Prepare DST Upgrade
• Run DST Upgrade
• Verify DST Upgrade
• DST References

The DST (Daylight Savings Time) version deployed with Oracle 12 is "18" . The DST typically used in Oracle 11.2 is "14" upgrade to version 18 of DST

Current DST Version

As Oracle

sqlplus /nolog <<EndSQL

connect / as sysdba

column property_name format a30

column property_value format a14

select property_name,property_value from database_properties where property_name like '%DST%' order by property_name;

exit

EndSQL

Example Output:

Property_name                     Property_value

DST_PRIMARY_IT_VERSION            14

DST_SECONDARY_IT_VERSION          0

DST_UPGRADE_STATE                 NONE

Note The value of DST_PRIMARY_IT_VERSION of not "18", then consider the upgrade to 18

Prepare DST Upgrade

As Oracle

sqlplus /nolog <<EndSQL

connect / as sysdba

alter session set "_with_subquery"=materialize;

exec DBMS_DST.BEGIN_PREPARE(180;

truncate table sys.dst$trigger_table;

truncate table sys.dst$affected_tables;

truncate table sys.dst$error_table;

exec DBMS_DST.END+PREPARE;

alter system set cluster_database =false scope=spfile sid='*';---Only for RAC Specific

exit

EndSQL

For Single Instance/HA , The following error can be ignored.

ORA-02095: Specified initialization parameter  cannot to modified

RUN DST Upgrade

As Oracle

. oraenv

srvctl stop database -d <DBName>

sqlplus /nolog <<EndSQL

connect / as sysdba

startup upgrade

purge dba_recyclebin;

EXEC DBMS_DST.BEGIN_UPGRADE(18);

alter system set cluster_database =true scope=spfile sid='*';---Only for RAC Specific

exit

EndSQL

For Single Instance/HA , The following error can be ignored.

ORA-02095: Specified initialization parameter  cannot to modified

. oraenv

srvctl start database -d <DBName>

sqlplus /nolog <<EndSQL

connect / as sysdba

alter session set "_with_subquery"=materialize;

set serveroutput on

VAR numfail number

BEGIN

  DBMS_DST.UPGRADE_DATABASE(:numfail,

                             parallel=>TRUE,

                             log_errors=>TRUE,

                             log_errors_table=>'SYS.DST\$ERROR_TABLE',

                             log_trigger_table=>'SYS.DST\$TRIGGER_TABLR',

                             error_on_overlap_time=>FALSE,

                             error_on_nonexisting_time=>FALSE);

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

END;

/

exit

EndSQL

sqlplus /nolog <<EndSQL

connect / as sysdba

alter session set "_with_subquery"=materialize;

set serveroutput on

VAR fail number

BEGIN

  DBMS_DST.END_UPGRADE(:fail);

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

END;

/

exit

EndSQL

Verify DST Upgrade

Verify that the DST file being used is version 18

As Oracle

sqlplus /nolog <<EndSQL

connect / as sysdba

column property_name format a30

column property_value format a14

select property_name,property_value from database_properties where property_name like '%DST%' order by property_name;

exit

EndSQL

Example Output:

Property_name                     Property_value

DST_PRIMARY_IT_VERSION            18

DST_SECONDARY_IT_VERSION          0

DST_UPGRADE_STATE                 NONE

3 rows selected

FILENAME                         VERSION           CON_ID

timezlrg_18.dat                  18                0

1 row selected