topic Re: oracle errors: ORA-00604: error occurred at recursive SQL level 1, ORA-00904: invalid column name in Operating System - HP-UX

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

Martin Johnson — Thu, 13 Feb 2003 22:25:03 GMT

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.

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

Julio Yamawaki — Thu, 13 Feb 2003 23:44:31 GMT

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.

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

Brian Crabtree — Fri, 14 Feb 2003 02:29:06 GMT

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

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

Indira Aramandla — Fri, 14 Feb 2003 05:30:23 GMT

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.

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

Indira Aramandla — Fri, 14 Feb 2003 05:37:11 GMT

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

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

Martin Johnson — Fri, 14 Feb 2003 17:38:27 GMT

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.

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

Indira Aramandla — Mon, 17 Feb 2003 04:41:11 GMT

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.

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

Indira Aramandla — Mon, 17 Feb 2003 04:51:34 GMT

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

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

Indira Aramandla — Mon, 17 Feb 2003 04:57:20 GMT

Hi Martin,

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

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

Martin Johnson — Fri, 21 Feb 2003 20:21:17 GMT

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

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

Brian Crabtree — Sat, 22 Feb 2003 04:39:03 GMT

1. I would recommend highly that you use the 'odma' program, rather than the scripts. While a failure can still happen, it is pretty intuitive, and seems to run faster than the scripts did when I used the scripts in the past.

2. Consider how large your database is. You have an additional option for an upgrade, which is performing an import/export. Export you database with the 8.0.6 binaries, destroy your 8.0.6 database, and recreate it with 8.1.7. This can be more costly on time, however it has some added benifits. First, it cleans up any fragmentation you have on your datafiles, and gives you the option of implementing tablespace changes before loading data (ie: LM tablespaces, initial settings, datafile placement). It will also allow you to consolidate your datafiles, if you have a number of small ones, or placed in different areas. Further, it also will allow you to consolidate your objects into a single extent (based on export options), which while not as important as it used to be, can still be good for fragmentation purposes and db reads.

I would not normally recommend doing an export/import for databases over 30g in size. This is your choice however. My normal rule of thumb is that the import will take about 3-4x the export.

Hope this helps,

Brian

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

Volker Borowski — Sat, 22 Feb 2003 17:05:02 GMT

Hi Martin,

first check, that you did not do an Upgrade from 8.0.6 32-bit to 8.1.7 64-bit software.

I think this step is not supported. If you like to accomplish this, you might either migrate to 8.0.6-64-bit before you upgrade, or do it by upgrading to 8.1.7-32-bit and then migrate to 8.1.7-64bit.

Second: Upgrade to the final patchset available ! This saves time and eliminates potential migration errors.
So if your target is 8.1.7, I'd recommend:
- shutdown 8.0.6 DB, listener and rest of oracle.
- Install 8.1.7.0.0 (should be the CD Set) into a new ORACLE_HOME.
- Apply the current patchset 8.1.7.4.0 to this newly installed ORACLE_HOME
- Finally use the migration assistent to migrate the database (But the use of the script you mentioned should work as well). Do NOT(!) migrate the DB to 8.1.7.0.0 and do the patch to 8.1.7.4.0 afterwards!

Good luck this time
(... and may the parachute always work :-)
Volker

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

Indira Aramandla — Mon, 24 Feb 2003 09:09:59 GMT

Hi Mertin,

I think its a good idea to go back to the tapes and restore your database. make sure it is functioning fine after the restore. Then take a complte cold backup of this DBin 8.0.6
Then

1. First install 8.1.7.0 into a dirrerent ORACLE_HOME (different from 8.0.6 HOME).

2. Follow the migration documentation

3. Now you could either do the upgrade manually
OR
use the ODMA of the 8.1.7 version to upgrade the database.

Please refer to the points that are attached for this.

And the other thing is Volker said to apply the patchset and then migrate the database. Well Oracle Patchset documentation does not agree with this. You have to migrate to the base release i.e 8.1.7.0 and after the upgrade is successfull, then apply the patchset level to suite your requirement. The latest one is 8.1.7.4.

Wish you good luck with the migration. Refer attachment.

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

Volker Borowski — Mon, 24 Feb 2003 20:33:58 GMT

Hi,
yes, Indira is right, first migrate to the base release and then apply the patch.

Guess I mixed up with something in the initial installation.... sorry !

No points!
Volker

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

Steven E. Protter — Mon, 24 Feb 2003 21:57:46 GMT

I recommend you look at my 32 bit to 64 bit upgrade thread and follow the instructions closely always having a good backup every step of the way.

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x0060ef70e827d711abdc0090277a778c,00.html

Make sure the Decmeber 2002 Patch set is fully installed.

metalink document id 43507.1 is a complete list of patches you need installed over and above December 2002.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=43507.1

You also want to be current on java configuration, 1.2 or 1.3 with all relavent HP patches at http://www.hp.com/go/java

Now you are in a position to succeed with the migration.

SEP

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

Volker Borowski — Tue, 25 Feb 2003 17:38:55 GMT

Hi,

I just digged up my sources again, (because I was halfway sure I already did it the way I described) but ..., I was comming from 8.1.6.
So this is would indeed not work for you, because of your start-release.

So just for reference, here are the links:

8.1.7
http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=188061.1&p_showHeader=1&p_showHelp=1

9.2.0
http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=214887.1&p_showHeader=1&p_showHelp=1

No points again
Volker

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

Martin Johnson — Tue, 25 Feb 2003 18:08:05 GMT

I don't have access to the oracle metalinks. Our version of oracle is Oracle For Openview, sold by HP. We have to rely on HP for oracle support.

Marty