Tuesday, September 3, 2013

Update the RDBMS DST version in 11203.

In this post I'll complete the rest of the upgrading task by upgrading the RDBMS DST version by 11 to 14.

Please follow my previous post to upgrade the database version from 11201 to 11203.

http://dbarohit.blogspot.in/2013/09/upgrading-oracle-database-11201-to-11203.html

Step 1.

Check the current RDBMS DST version.

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         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

3 rows selected.


PROPERTY_NAME                   VALUE

------------------------------ -------------------------------- 
DST_PRIMARY_TT_VERSION         <the old DST version number>
DST_SECONDARY_TT_VERSION       0 <THIS NEEDS TO BE "0">
DST_UPGRADE_STATE NONE         <THIS NEEDS TO BE "NONE">


SQL> SELECT version FROM v$timezone_file;
VERSION

----------
 11

1 row selected.



Step 2.

Preparing for the DST update.

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_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');
PL/SQL procedure successfully completed.


SQL> EXEC DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.


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       14
DST_UPGRADE_STATE              PREPARE

3 rows selected.

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

PL/SQL procedure successfully completed.


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.

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
3 rows selected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 3.
Upgrading the RDBMS DST.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2037673984 bytes

Fixed Size                  2229824 bytes
Variable Size            1342179776 bytes
Database Buffers          687865856 bytes
Redo Buffers                5398528 bytes
Database mounted.
Database opened.

SQL> set serveroutput on
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
3 rows selected.

SQL> purge dba_recyclebin;
DBA Recyclebin purged.


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> alter session set "_with_subquery"=materialize;
Session altered.


SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.


SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');
PL/SQL procedure successfully completed.


SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);An upgrade window has been successfully started.
PL/SQL procedure successfully completed.


SQL> SELECT * FROM sys.dst$error_table;
no rows selected


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       11
DST_UPGRADE_STATE              UPGRADE
3 rows selected.


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
IX           AQ$_ORDERS_QUEUETABLE_L        YES
IX           AQ$_ORDERS_QUEUETABLE_S        YES
IX           AQ$_STREAMS_QUEUE_TABLE_L      YES
IX           AQ$_STREAMS_QUEUE_TABLE_S      YES
18 rows selected.


SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2037673984 bytes

Fixed Size                  2229824 bytes
Variable Size            1342179776 bytes
Database Buffers          687865856 bytes
Redo Buffers                5398528 bytes
Database mounted.
Database opened.

SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> set serveroutput on

VAR numfail number
BEGINDBMS_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;
/
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: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Failures:0

PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.dst$error_table;
no rows selected


SQL> VAR fail number

BEGINDBMS_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> 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
3 rows selected.

Now cross check the RDBMS DST version.


SQL> SELECT * FROM v$timezone_file;


FILENAME                VERSION

-------------------- ----------
timezlrg_14.dat              14

1 row selected.

Reference.OTN DOC 977512.1: Updating the RDBMS DST version in 11gR2.

No comments:

Post a Comment