11 | 12 | 2017
Latest Articles
Popular Articles

Administration

Upgrade to 11G R2

User Rating:  / 2
PoorBest 

Upgrade to 11G R2

 

update : March 19 2012

This article describes an issue I ran into while I upgraded to 11.2.0.1 and which is related to timezones.

I did not ran into this issue anymore when I upgraded other databases directly to 11.2.0.2 or 11.2.0.3, although I advise to read carefully

Metalink ID 837570.1 Complete Checklist for Manual Upgrades to 11G R2 .

Metalink ID 977512.1 updating the RDBMS DST version 11g Release 2 (11.2.0.1 and up) using DBMS_DST

before you start with the upgrade.


I decided to start with the discovery of Oracle 11G R2. I used to play and test with the new features and that' s what I have in mind this time as well.

Believe it or not but despite my 12 years experience with the Oracle database I never used the DBUA — The Database Upgrade Assistant — so far. The command line approach that' s the way I like it. Note the expression "That' s the way I like it " hasn' t anyhting to do with KC & The Sunshine Band.

 

Oracle' s Metalink has always good guidelines for the manual upgrade. This time it isn' t different.

 

Complete Checklist for Manual Upgrades to 11gR2 ( ID 837570.1 )

somewhere in the middle of that document we can read


"BEFORE upgrading the database, you MUST patch the 11gR2 $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the same version as the one used in the source release database. Apply the patch for each database you will be upgrading. Otherwise, the upgrade script will terminate without upgrading the database.

if the source database is using timezone files higher than version 11 and the Oracle 11gR2 Oracle home  is NOT patched with the same version timezone  before upgrade   then  you will get the following error while trying to upgrade the database .
ERROR at line 1:
ORA-01722: invalid number
"


What is the timezone_file I was using in my 11.1.0.6 database.

 

SQL> select * from v$timezone_file;

FILENAME        VERSION

timezlrg_4.dat              4

 


It is a test database and since I do not care about timestamp with timezone data in my test database, I thought I can afford not to pay attention to this part.  Let's start with the "real" upgrade. But guess what happened ... the upgrade did not start in stead it failed with

 

ERROR at line 1:
ORA-01722: invalid number "

 

So I was forced to apply DSTv11 Patch 8524113 on top of my source Oracle Home 11.1.0.7, whether I had timestamp with local timezone data or not.   I downloaded the patch form Oracle' s Metalink and applied it using the opatch utility. I was surprised that once I applied DSTv11 Patch 8524113 I was not able to use SQL plus anymore. I could not even start the database anymore.

 

$ORACLE_HOME/bin/sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly


It took me some time, but finally I found both explanation and solution for my issue at Oracle' s Metalink


Applying the DSTv11 update for the Oracle Database ( ID 840118.1 )



4b) Patch 7695070 needs to be applied before the RDBMS DSTv11 patch can be installed.

Unlike previous DST patches the DSTv11 patch 8524113 needs to have 2 codefixes in place (Bug 7695070 and bug 7395472) to work.

* All non-windows platforms need to have patch 7695070 applied BEFORE applying the DSTv11 Patch 8524113.



So I applied Patch 7695070 and re-applied the DSTv11 Patch 8524113 both using opatch
afterwards. I was able to continue and progress with the upgrade to 11G R2 using the manual and everyhting ran fine.


At the end as documented we still need to update the DST version using a new package DBMS_DST.


Oracle' s Metalink guided me through that step once again.

 

"Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST " ( ID 977512.1 )


SQL> select * from v$timezone_file;

FILENAME        VERSION

timezlrg_4.dat              4

SQL> show release
release 1102000100
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

 
SQL> startup upgrade; !! yes once again
ORACLE instance started.

Total System Global Area  872742912 bytes
Fixed Size                  2218192 bytes
Variable Size             444598064 bytes
Database Buffers          419430400 bytes
Redo Buffers                6496256 bytes
Database mounted.
Database opened.

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> exec dbms_dst.begin_upgrade(11);

PL/SQL procedure successfully completed.

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

Total System Global Area  872742912 bytes
Fixed Size                  2218192 bytes
Variable Size             444598064 bytes
Database Buffers          419430400 bytes
Redo Buffers                6496256 bytes
Database mounted.
Database opened.
 
SQL> select * from v$timezone_file;

FILENAME        VERSION

timezlrg_11.dat          11

 

I finished with the recompilation of invalid objects using $ORACLE_HOME/rdbms/admin/utlrp.sql.