oracle errors: ORA-00604: error occurred at recursive SQL level 1, ORA-00904: invalid column name

[Martin Johnson]

I recently installed oracle 8.1.7 as part of upgrading oracle 8.0.6. When running the update script, u0800060.sql, I got multiple ORA-00604 and ORA-00904 errors. When I tried to do a "shutdown" or "shutdown immediate" I got the 604/904 errors, with oracle failing to shutdown. HP recommended I install the 8.1.7.3 patch, which I did. At this point I could not "startup" oracle.

At this point I discovered there were shared memory segments and semaphores owned by oracle, even though there were no oracle processes running. I removed the memory segments and semaphores. I could now startup oracle, but I still receive the 604/904 errors when trying to shutdown oracle.

I believe the memory segments were part of 8.0.6, used by 8.1.7, which corrupted something. I have de-installed 8.0.6 and 8.1.7, re-installed 8.1.7 and still have the problem. I have installed 8.1.7 on another system with no problems.

Anybody have any ideas what may be corrupted? How do I fix it?

Marty

P.S. HP recommended that I install the latest oracle patch, 8.1.7.4. This did not fix the problem.

[Julio Yamawaki]

Hi,

Before upgrading it is advisable to: Make sure there are no PENDING transactions; Shut down the database cleanly; Take a full cold backup;

If you have a cold backup, try again, if not, I'd like to see the alert file of this database to check it there are any other errors.

[Brian Crabtree]

At this point, there is not much that you can do. You can try to re-run the catalog and catproc scripts, and see if that will resolve the issue, however you should just revert back to an old backup, and redo the migration again.

Also, you might want to consider using odma over the upgrade script. It checks a number of things, and appears to run the import better.

Brian

[Indira Aramandla]

Yes before upgrading it is advisable to Shut down the database cleanly; Take a full cold backup;

If you have a cold backup, then you can restore the database from the full cold backup and then try to upgrade to 8.1.7.

The error you are getting could be of few reasons. One of them may be if your database is configured for InterMedia ie. if it used context indexes. Then when you updrage / migrate you have to drop the CONTEXT index and then re-create the index and preference after successfull migration /ugrade.

The other reason may be your data dictionary has partially upgraded its objects in 8.1.7. If you have a backup go back to the 8.0.6 ORACLE_HOME and restore from backup and then migrate to 8.1.7. Other wise try pointing to 8.0.6 ORACLE_HOME and re-run catproc and catlog scripts to re-build the 8.0.6 dictionary. then try to migrate after successfull backup. Please check you alert log for more detailed errors. If you see other errors like" accessing package DBMS_APPLICATION_INFO", then defenetly the dictionary is corrupt.
Try runing these from 8.0.6 ORACLE_HOME
catalog.sql -- as sys
catproc.sql -- as sys
pubbld.sql -- as system

Then try to shutdown and startup. If success take backup and then try to upgrade.

[Indira Aramandla]

Forgot one more thing.
Oracle Metalink says, "If you upgrade from 8.0.6.X to 8.1.7.X, it is necessary to run the $ORACLE_HOME/rdbms/admin/u0800060.sql script after you start the instance (and open the database) in the 8i environment.
Refer to the following in Metalink
Note: 125822.1 - Description of Errors that may Occur when Upgrading from V8.0 to V8i

[Martin Johnson]

Julio: Where is the alert file?

Indira: I did run the u0800060.sql script. I got multiple 604/904 errors when I ran the script.

I used the Oracle Universl Installer (OUI) to deinstall both 8.0.6 and 8.1.7. Obviously, it doesn't get rid of everything as a reinstall of 8.1.7 continues to have problems.

When I install on a machine that never had oracle installed, it runs fine. The OUI prompts to create a new database (Y/N). When I try to install on the machine that had 8.0.6 I get an extra screen asking me if I want to Upgrade/Migrate existing databases. Three are listed by their SIDs: openview, reporter, orap001. I don't think reporter and orap001 exist any more, but openview is part of the ITO 6.x installation. I believe the openview files may be corrupted.

[Indira Aramandla]

Hi Martin,
Alert log will be in your BDUMP destination directory. It will be something like /oracle/admin/sid/admin/bdump/alert_sid.log
You can check you bdump destination in your init. ora file and see where the alert log is. This log will give more detailed explaination.

Now the other issue that you are getting when you ran u0800060.sql script. Did you make sure you pointed to the correct oracle HOME and SID. The problme here would be you ORACLE_HOME is not setup properly for it to get the correct libraries and executables. Because on the new server with out any prior oracle you have no problems. How about this time you install 8.1.7 into a new ORACLE_HOME. And the OUI prompts to create a new database (Y/N).

Now you can do two things.

1. Once the new sid 8.1.7 has been created then import the 8.0.6 data into 8.1.7 from an export dump file taken previously.

2. If you still feel like upgrading the 8.0.6 using the migration path, then I would suggets you to restore from the backup tapes 8.0.6 database and then follow the migration steps to upgrade to 8.1.7. Remember to point to the correct ORACLE_HOME during this procedure.

I have checked Metalink regarding this error and this is what it says. Please refer to the attachment (Note 125822.1). I hope this helps.

If you feel






When I install on a machine that never had oracle installed, it runs fine. When I try to install on the machine that had 8.0.6 I get an extra screen asking me if I want to Upgrade/Migrate existing databases. Three are listed by their SIDs: openview, reporter, orap001. I don't think reporter and orap001 exist any more, but openview is part of the ITO 6.x installation. I believe the openview files may be corrupted.

[Indira Aramandla]

Hi Martin,
Please ignore the last paragraph, that I pasted by mistake (which is your message).

[Indira Aramandla]

Hi Martin,

Please ignore the last paragraph. I pasted it by mistake (which is your message).

[Martin Johnson]

Update:

I decided to remove 8.0.6 completely as outlined in the 8.1.7 installation guide. I proceeded to do a cold install of 8.1.7. I did get a 205 error (control file not found) when I stried to startup oracle. This was to be expected as I did not create an oracle database.

I then tried to upgrade Network Node Manager (NNM) 6.1 to 6.2. This failed because updates to the solid DB failed.

At this point, I tried to remove NNM to do a cold install of NNM. The GUI version of swremove core dumped while trying to resolve dependencies. The character cell version of swremove sends an out of memory error prior to locking up while trying to resolve dependencies.

I tried to use the remove.nnm script. It removed part of NNM but not the core of NNM. Tried swremove again, same results.

I tried to install NNM 6.2 again. It failed again. NNM appears corrupted. Here are the errors:

NOTE: OV - Installing /opt/OV/bin/pmd and /opt/OV/bin/pmdmgr
* Running "configure" script for fileset "OVPlatform.OVDB-RUN".
NOTE: OV - Replacing /var/opt/OV/share/databases/analysis/default/solid.ini.
Backing it up to /var/opt/OV/share/databases/analysis/default/solid.ini.pre6.20
NOTE: OV - Installing new version of /var/opt/OV/share/databases/analysis/def
ault/solid.lic
Old version in /var/opt/OV/tmp/OVDB-RUN/solid.lic.1
Connecting as user "ovdb" with password
Validate accessability to "tcpip 2690" database
Start embedded database if needed

SOLID process executed an automatic shutdown.

Cannot convert an abnormally closed database. Please use
the old SOLID version to recover the database first.

Version: 03.51.0119
Operating system: HP-UX 11.x MT
Connection to ODBC datasource "tcpip 2690" failed
SQLSTATE = HY000
NATIVE ERROR = 21306
SOLID Communication Error 21306: Server 'tcpip 2690' not found, connection failed
d

ERROR: OV - Unable to start embedded database

At this point I've declared the patient dead. I've requested the backup tapes be returned from the vault.

Before I try this again, any advise? Things to watch out for, etc?

Marty