Text box

Please note that the problems that i face may not be the same that exist on your environment so please test before applying the same steps i followed to solve the problem .

Sunday 20 May 2012

Applying patch 12949905 to upgrade the time zone to version 17 on RDBMS 11g Release 2-Linux 64Bit


1-Unzip the patch file then CD to the patch directory then issue opatch apply command as shown below:

[oracle@salesincdbstag1 12949905]$ /app/11_2/oracle/product/11.2.0/dbhome_1/OPat                                                             ch/opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /app/11_2/oracle/product/11.2.0/dbhome_1
Central Inventory : /app/11_2/grid/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /app/11_2/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/                                                             opatch2012-04-24_12-42-47PM.log

Applying interim patch '12949905' to OH '/app/11_2/oracle/product/11.2.0/dbhome_                                                             1'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...

Patching component oracle.oracore.rsf, 11.2.0.3.0...
Patch 12949905 successfully applied
Log file location: /app/11_2/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/o                                                             patch2012-04-24_12-42-47PM.log

OPatch succeeded.

2- Check current RDBMS DST version and "DST UPGRADE STATUS":


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> SELECT version FROM v$timezone_file;

   VERSION
----------
        14

Now Prepare the database for patch apply:

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> exec DBMS_DST.BEGIN_PREPARE(17);

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;  2    3    4

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       17
DST_UPGRADE_STATE              PREPARE

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


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>   2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> SELECT * FROM sys.dst$affected_tables;

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
 ROW_COUNT ERROR_COUNT
---------- -----------
SYS                            SCHEDULER$_WINDOW
NEXT_START_DATE
         4           0


SQL> SELECT * FROM sys.dst$error_table;

no rows selected

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

no rows selected


SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.



Now We will do the real change:

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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1954160640 bytes
Fixed Size                  2229624 bytes
Variable Size            1241516680 bytes
Database Buffers          704643072 bytes
Redo Buffers                5771264 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;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

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

SQL>
SQL>
SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(17);
An upgrade window has been successfully started.

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;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         17
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UP                                                             GRADE_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

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES
SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES

14 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UP                                                             GRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_IP_RANGE             YES
SYSMAN                         MGMT_PROV_NET_CONFIG           YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES
SYSMAN                         MGMT_PROV_BOOTSERVER           YES
SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES
SYSMAN                         MGMT_PROV_ASSIGNMENT           YES
SYSMAN                         MGMT_PROV_OPERATION            YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

14 rows selected.

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UP                                                             GRADE_IN_PROGRESS='YES';

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_IP_RANGE             YES
SYSMAN                         MGMT_PROV_NET_CONFIG           YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_L       YES
SYSMAN                         AQ$_MGMT_LOADER_QTABLE_S       YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_L       YES
SYSMAN                         MGMT_PROV_BOOTSERVER           YES
SYSMAN                         MGMT_PROV_SUITE_INST_MEMBERS   YES
SYSMAN                         AQ$_MGMT_NOTIFY_QTABLE_S       YES
SYSMAN                         MGMT_PROV_ASSIGNMENT           YES
SYSMAN                         MGMT_PROV_OPERATION            YES
SYSMAN                         MGMT_PROV_STAGING_DIRS         YES

OWNER                          TABLE_NAME                     UPG
------------------------------ ------------------------------ ---
SYSMAN                         MGMT_PROV_CLUSTER_NODES        YES
SYSMAN                         MGMT_PROV_RPM_REP              YES
SYSMAN                         MGMT_PROV_DEFAULT_IMAGE        YES

14 rows selected.
SQL> desc sysman.MGMT_PROV_NET_CONFIG;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NET_CONFIG_GUID                           NOT NULL RAW(16)
 LAST_MODIFIED_TIME                                 TIMESTAMP(6) WITH TIME ZONE
 NET_CONFIG_NAME                                    VARCHAR2(255)
 NET_DOMAIN_NAME                                    VARCHAR2(255)
 NET_SUBNET_MASK                                    VARCHAR2(255)
 NET_GATEWAY_ADDRS                                  VARCHAR2(512)
 NET_DNS_ADDRS                                      VARCHAR2(512)

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

Total System Global Area 1954160640 bytes
Fixed Size                  2229624 bytes
Variable Size            1241516680 bytes
Database Buffers          704643072 bytes
Redo Buffers                5771264 bytes



Database mounted.
Database opened.
SQL> SQL> SQL> SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
Session altered.

SQL>

Session altered.

SQL>
SQL> set serveroutput on
VAR numfail number
SQL> 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"."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
Failures:0

PL/SQL procedure successfully completed.
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

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;
  2    3    4
PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         17
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE              UPGRADE

SQL>
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_17.dat              17

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

SQL>
SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_17.dat              17

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        14

SQL> select version FROM v$timezone_file
  2  ;

   VERSION
----------
        17

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        17

SQL> commit;

Commit complete.



End Of the steps
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)