May 28, 2012

ORA-00904: "TZ_VERSION": invalid identifier during upgrade, since utlu112i.sql didn't run

We all know that we had to run an Pre-Upgrade Information Toolscript (example: "utlu112i.sql" if target is 11.2.0.X) before an upgrade.
More then doing a requirement checking such as tablespace free size,  the script will also do some modification on dictionary tables.

So if you forget or failed to run utlu112i.sql before upgrade, then when you run catupgrd.sql during upgrade, you will encounter some errors such as:
TO_NUMBER(value$) != (SELECT tz_version from registry$database))*
ERROR at line 6:
ORA-00904: "TZ_VERSION": invalid identifier

Once you got this error, it is often too later for you to realize that you forgot to run pre-upgrade script.
And since your Database currently is already in upgrade mode, so you can't re-run the pre-check script anymore.
 
So what should we do now?  Do we had to run the pre-check script in souce DB and re-start from begining?
 
Infact we can fix the problem in below way: 
1. forget to run utlu112i.sql for 11.2.0.2, to fix, execute below sqls:
vecs1>select STATUS from v$instance;
STATUS
------------
OPEN MIGRATE
vecs1>ALTER TABLE registry$database ADD (tz_version NUMBER);
Table altered.
vecs1>ALTER PACKAGE dbms_registry COMPILE BODY;
ERROR at line 1:
ORA-04023: Object SYS.DBMS_STANDARD could not be validated or authorized
vecs1>ALTER VIEW dba_registry_database COMPILE;
View altered.
vecs1>ALTER PUBLIC SYNONYM DBA_REGISTRY_DATABASE COMPILE;
Synonym altered.

2. forget to run utlu112i.sql for 11.2.0.3, to fix, need to execute one more sql:
ALTER TABLE registry$database ADD (tz_version NUMBER);
UPDATE registry$database set tz_version = 14;    --- one extra SQL for 11.2.0.3
ALTER PACKAGE dbms_registry COMPILE BODY;
ALTER VIEW dba_registry_database COMPILE;
ALTER PUBLIC SYNONYM DBA_REGISTRY_DATABASE COMPILE;

And then, re-run the catupgrd.sql  script, you won't encounter the "TZ_VERSION: invalid identifier " error anymore.

From where i get above SQLs?  I get them from the pre-upgrade script itself.
For example,  you can find below in 11.2.0.3's utlu112i.sql file:
EXECUTE IMMEDIATE
'ALTER TABLE registry$database ADD (tz_version NUMBER)';
EXECUTE IMMEDIATE
'UPDATE registry$database set tz_version = :1'
USING db_tz_version;
EXECUTE IMMEDIATE
'ALTER PACKAGE dbms_registry COMPILE BODY';
EXECUTE IMMEDIATE
'ALTER VIEW dba_registry_database COMPILE';
EXECUTE IMMEDIATE
'ALTER PUBLIC SYNONYM DBA_REGISTRY_DATABASE COMPILE';

For other versions upgrade which you forgot to run the pre-check script, you also can go to view the utluxxxi.sql file and find the corresponding part,  and execute them in target upgrade mode DB, and then re-run catupgrd.sql can be succeed.

4 Comments:

joelhunn said...

Kevin...Ohhhhhhh how you have saved my a$$. After the many hours of backup, scping files over, restoring database to a new, 11g only server...THEN I find out that I needed to run the script before ever building the backup. I was going to be 2 days past my deadline - you saved me a whole day! And as far as I can tell, yours is the only workaround on the net ("go back to the original home and run the script" is NOT a reasonable answer in my situation. Thank you, thank you, thank you!!

Anonymous said...

I'll second that - saved me a lot of pain after stupidly missing the pre upgrade script. Cheers

Demystifying DBA said...

I cannot really comprehend why Oracle would make this dramatic shift in the upgrading process. I have to do regular test upgrades on different client sites and in most cases the backups that I have to use are sometimes days old depending on the size of the DB. Problem is I cannot run the utlu112i.sql on the live DB as I will not have access to it till the actual live upgrade. So essentially, I was having to setup a similiar VM environment (what a pain as many customers were on different versions, patch levels) - apply the utlu112i then move to 11g to do the upgrade. Tried this process and worked flawlessly.

Unbelievable - thank you.

But I do have a few queries:
Is this recommended? Can there be any long term issues encountered in the future due to bypassing utlu112i?
What is the relevance of "tz_version = 14" - what exactly is the significance of 14 ?

If you can throw some light please?

Many thanks for the process.

Naresh Prusty said...

Awesome, it really helpful and i have followed the same.

Post a Comment