Post upgrade : Update Timezone Version

i’d been doing a lot of database updgrade now from 10.2.0.4/11.1.0.7 to 11.2.0.4.1 but i never updated the timezone versions.
so i decided to it now.

below step are done after doing the upgrade.

1. Start the database in upgrade mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             620758936 bytes
Database Buffers         1509949440 bytes
Redo Buffers                4923392 bytes
Database mounted.
Database opened.

2. Using dbms_dst package start the upgrade.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

PL/SQL procedure successfully completed.

3. Verify if the DST_UPGRADE_STATE is in upgrade.

SQL>  SELECT version FROM v$timezone_file;

   VERSION
----------
         4

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4

PROPERTY_NAME                                      VALUE
-------------------------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION                             14
DST_SECONDARY_TT_VERSION                           4
DST_UPGRADE_STATE                                  UPGRADE

4. Restart the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             620758936 bytes
Database Buffers         1509949440 bytes
Redo Buffers                4923392 bytes
Database mounted.
Database opened.
SQL>

5. Perform the upate.

SQL> alter session set "_with_subquery"=materialize;

Session altered.

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  /
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: "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"."MGMT_CONFIG_ACTIVITIES"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL>   2    3    4    5
An upgrade window has been successfully ended.
Failures:0

PL/SQL procedure successfully completed.

SQL>

6. Check if its updated.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;  2    3    4


PROPERTY_NAME                                      VALUE
-------------------------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION                             14
DST_SECONDARY_TT_VERSION                           0
DST_UPGRADE_STATE                                  NONE

SQL>

SQL> SELECT * FROM v$timezone_file;

FILENAME                                                        VERSION
------------------------------------------------------------ ----------
timezlrg_14.dat                                                      14

SQL>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s