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

[Brian Crabtree]

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

[Volker Borowski]

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

[Indira Aramandla · Accepted solution]

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.

[Volker Borowski]

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

[Steven E. Protter]

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

[Volker Borowski]

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

[Martin Johnson]

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