Thursday, July 31, 2014

ORA-00942 During upgrade to 11.2.0.3 or later on Windows

Problem
On Windows platform (32 bit or 64 bit), while upgrading 10.2.0.4 (or earlier version) database to 11.2.0.3 (with bundle patch applied), ORA-00942 error is expected to appear during the process, regardless of whether upgrade is being done manually or using DBUA.
Following is the screen shot of this error which can safely be ignored and let the upgrade process run to the finish.





















After the upgrade finishes, you can query DBA_REGISTRY to confirm that all components are valid.

Reason of the Problem
This is a known issue which appears on Windows platform while upgrading a database to a bundle patch applied; 11.2.0.3 (or greater) home. If you have a look at the log file of upgrade process (oracle_server.log), you will see following error which is related to the database replay.

########################################################
update sys.WRR$_REPLAY_DIVERGENCE set cap_file_id = file_id;
update sys.WRR$_REPLAY_DIVERGENCE set cap_file_id = file_id
*
ERROR at line 1:
ORA-00942: table or view does not exist


commit;

Commit complete.

########################################################

Solution
Once upgrade finishes and DBA_REGISTRY shows all components are VALID, there is nothing much to worry about. Next step is just to manually fix the issue happened during the upgrade. Following scripts are to be run as SYSDBA to recreate the database replay objects and fix this issue.

cd %oracle_home%\rdbms\admin
sqlplus "/ as sysdba"

SQL> @catnowrr.sql
SQL> @catwrr.sql
SQL> exit

No comments:

Popular Posts - All Times