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