Showing posts with label ORA-00904. Show all posts
Showing posts with label ORA-00904. Show all posts

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