Home > Troubleshooting > Issues with upgrade to 11g

Issues with upgrade to 11g

I was doing  manual upgrade from version 10.2.0.3 to 11.1.0.7 .Really  did not expect any big issues since I did it many times.So after doing all pre-upgrade steps ( or I taught I did all of them)

I started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2160112 bytes
Variable Size            1946159632 bytes
Database Buffers         4429185024 bytes
Redo Buffers               36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The first time this script is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
DOC>
DOC>   If this script is being re-run after correcting some problem, then
DOC>   expect the following error which is not automatically suppressed:
DOC>
DOC>   ORA-00001: unique constraint (<constraint_name>) violated

DOC>#
   FROM registry$database
        *
ERROR at line 2:
ORA-00942: table or view does not exist

Upgrade failed! Since I hit same error in the past I knew where is the problem and expected to fix it quickly.

This  error is related to timezone file  which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied.

Query to check timezone file  is:

SQL> select * from v$timezone_file;

FILENAME        VERSION
———— ———-
timezlrg.dat          4

so I pointed database to old oracle home and started it:
-sh-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Aug 11 11:12:41 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2082376 bytes
Variable Size            1577060792 bytes
Database Buffers         4848615424 bytes
Redo Buffers               14692352 bytes
ORA-00201: control file version 11.0.0.0.0 incompatible with ORACLE version
10.2.0.3.0
ORA-00202: control file:
‘/oracle/demo/control/copy_1/control01.ctl’  

Database run into different issue.Database files and controlfile did not match.

I tried to do partial restore but it did not work and I end up with full database restore from backup.

After restoring database I  run query to check timezone:

SQL> select * from v$timezone_file;

FILENAME        VERSION
———— ———-
timezlrg.dat          4

So I had correct version.I remember applying patch before upgrade.I got lucky because patch existed for version 10.2.0.3.

If there is no patch for your Oracle versions than patch can be download for similar version and  applied manually.

Instructions are below:

1.                 Download the identified patch.

2.                 Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the “files/oracore/zoneinfo” directory of the uncompressed patch (or from the relevant .jar file of a   patchset). If there is also a readme.txt in this location then make a note of this as well.

3.                 Backup your existing files in $ORACLE_HOME/oracore/zoneinfo – THIS CAN BE VITAL, DO NOT SKIP.

note:
Before going on with step 4, make sure the current files are not in use.
On Windows the files will simply refuse to be removed when the are in use.
On Unix replacing the files whilst they are in use can cause the files to become corrupt. Use the fuser command before replacing the files to make sure they are not in use.

4.                 Copy the 2 .dat files and possibly the readme.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory.

5.                 Restart the database (in case of installation on a database), or restart the client applications (in case of client install). Note that the database did not need to be down before the time zone files were applied, but it does need to be restarted afterwards.

Take note that opatch will not have any knowledge that you have applied this patch when you use these manual steps, so make sure that you document this for yourself.
The time zone files will never be in conflict with any other patches, future time zone files patches will always supersede the older time zone patches, so the fact that optach does not know about this will not cause any patching problem
So why my upgrade failed with ORA-00942: table or view does not exist?
Pre-upgrade  documents is requiring to  run utlu111i.sql.So just copy utlu111i.sql from Oracle 11g version that you are upgrading to  (file is  located under rdbs/admin directory ) and run it into database still running under old oracle home.I did run it but it never finished successfully and I ignore it hopping that everything will be fine.And that’s the part that made whole upgrade to fail.Running the utlu111i.sql updates utlu111i.sqlwith the proper information about the time zone file version and catupgrd.sql will check registry$database to find the current version of timezone file.

After restoring database I run utlu111i.sql for a second time  but when script failed with RA-07445 error  I stop  upgrade.
I did some tracing and research and even opened service request.It turns out that I run into uknown bug and Oracle support confirmed that there is no patch for a bug and it might take some time to get a fix for it.So at this point I decided not to use catupgrd.sql but export/import to do upgrade.

About these ads
Categories: Troubleshooting
  1. August 14, 2009 at 6:09 am

    Thanks to share this experience.

  2. odenysenko
    August 14, 2009 at 2:03 pm

    Hi.

    Just one question:
    Why after facing
    “ORA-00201: control file version 11.0.0.0.0 incompatible with ORACLE version 10.2.0.3.0″
    You didn’t recreated controlfile from trace of from scratch ?

    The other issues I see is parameter COMPATIBLE was rised too early.

    Oleksandr

  3. August 14, 2009 at 4:11 pm

    Who said I did not try :) I already said that I tried partial restore.
    Database will start perfectly fine under 11g Oracle home in startup mode but once you shutdown and start under 10g home then you get this error.I run into this error few times.One time it was complaining that db blocksize parameter did not match ( It was always 8k so far I know but it was asking for 16k ) .

    What is happening here is that datafiles headers got updated with running upgrade script.Although script fails there are still updated.You can easily test this on your test environment.

    Regards,

    Miladin

  4. Sam
    October 15, 2009 at 6:06 am

    if you have your audit settings turned on you get this error
    disable sys auditing and the audit_trial setting and it should work

  1. August 16, 2009 at 6:43 pm

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

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: