Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle Upgradation from 8i to 10

Posted on 2008-10-20
17
516 Views
Last Modified: 2013-12-18
Hi
Enviroment
OS- Linux
Oracle version : 10.1
we are up grading old oracle database from 8i to 10 i , but while up gradation we are facing issue . we executed following command
>startup upgrade

Now database is up in upgrade mode but if we again shut it down with following command
>shutdown immediate
It giving "ORA-00904: "DROP_SEGMENTS": invalid identifier" and we could not able to shut it down
What is the problem can any body suggest some solution.


0
Comment
Question by:techprocess
  • 8
  • 8
17 Comments
 
LVL 4

Expert Comment

by:hqassap
ID: 22766231
Connect to the SQL session as SYS and issue the following:
SQL> desc sys.mon_mods$

If the drop_segments is missing then we are getting ORA-904.If this is the case,we can do one of these:

Add the drop_segments column using:
SQL> alter table sys.mon_mods$ add ( drop_segments number default 0 );

Then run $ORACLE_HOME/rdbms/admin/catalog.sql again.
0
 
LVL 3

Author Comment

by:techprocess
ID: 22766755
i tried this solution also but did not helped. it is giving me "table/ view dose not exist" even if we were able to describe the table using desc sys.mon_mods$ table.
0
 
LVL 4

Expert Comment

by:hqassap
ID: 22767149
Are you logged in as SYSDBA?

Did you found the drop_segments  column in sys.mon_mods$  table when you describe it?

Did you ran the  $ORACLE_HOME/rdbms/admin/catupgrd.sql after you start the database with upgrade option?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 3

Author Comment

by:techprocess
ID: 22773450

Yes i logged in as sysdba and found "drop_segments" no such column in sys.mon_mods$ when i described it.
$ORACLE_HOME/rdbms/admin/catupgrd.sql , this sql not present in my oracle  location. So what sould i do now.

0
 
LVL 4

Expert Comment

by:hqassap
ID: 22773762
To upgrade from 9i to 10g, the DBUA should run $ORACLE_HOME/rdbms/admin/catupgrd.sql

Did you run the DBUA?
You need to run it from an x-windows session though.
0
 
LVL 14

Expert Comment

by:cjl7
ID: 22773763
Have you got a service call started with Oracle, that might be a good idea? ;)


Did you export the schema and the data before starting the upgrade?  One thing you could try is to do a clean install and import the data into the newly installed database.
0
 
LVL 3

Author Comment

by:techprocess
ID: 22773837
>Did you run the DBUA?

Yes, first we tried with the upgrade assistance and selected old database name 'seek01' . we provided sysdba password to process further but could not  able to  complete the process due to some error occurred and up gradation skipped. We started DBUA again , this time we got two database on wizard , one that old one with renamed like 'seek01_absolute and other seek01.
 The seek01 was the oracle 8i old database name.  


I have exported database before we start upgrade, so if incase we will not able to upgrade old dartabase then we have to create new database, that is last option.


0
 
LVL 4

Expert Comment

by:hqassap
ID: 22773908
It is better to find out why that script didn't install than copy it from another system.
I attached the catupgrd.txt file rename it to catupgrd.sql, then run it and then do shutdown and startup again. This may solve your problem.

You don't know what else may be missing from the home because this sql script will call too many other scripts to re-create the catalogs.

If some other files also are missed, run the install again just to be safe.



catupgrd.txt
0
 
LVL 3

Author Comment

by:techprocess
ID: 22773963
I ran your script

It is giving me following error

SELECT TO_NUMBER('MUST_BE_10_2') FROM v$instance
                 *
ERROR at line 1:
ORA-01722: invalid number

I have attached log file
catupgrd.log
0
 
LVL 4

Expert Comment

by:hqassap
ID: 22774128
As sysdba, do the following:

SQL> startup force;
SQL> Shutdown immediate;
SQL> Startup upgrade,
SQL> @ catupgrd.sql

If still the error exist, run the install again.
0
 
LVL 3

Author Comment

by:techprocess
ID: 22774237
u mean install oracle 10 .1 again?
0
 
LVL 4

Expert Comment

by:hqassap
ID: 22774278
yse, but before that, I think you are running on the wrong instance!

Read this Metalink note:
738342.1

https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=738342.1
0
 
LVL 3

Author Comment

by:techprocess
ID: 22774344

I went through the link u provided, .let me clear one thing, we have Oracle8i as old oracle and i am upgrading to oracle 10.1. The document showing the catupgrd script to upgrade from 9.2.0.x to 10.2.0.x.

What about the old database that we migrated, any way to recover the old databse.
0
 
LVL 3

Author Comment

by:techprocess
ID: 22774363

I went through the link u provided, .let me clear one thing, we have Oracle8i as old oracle and i am upgrading to oracle 10.1. The document showing the catupgrd script to upgrade from 9.2.0.x to 10.2.0.x.

What about the old database that we migrated, any way to recover the old database.
0
 
LVL 4

Expert Comment

by:hqassap
ID: 22776334
Can you check how many oracle instance you have in windows services?
0
 
LVL 3

Author Comment

by:techprocess
ID: 22783260

I have  installed Oracle10.1 on Linux. Now we decided we should drop the database that unsuccessfully migrated and create new one to handle this situation. What is your thought on this.
0
 
LVL 4

Accepted Solution

by:
hqassap earned 500 total points
ID: 22802455
I agree with you, it is better to reinstall Oracle in new Oracle Home and then remove the old one after moving the data.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

At the beginning of the year, the IT world was taken hostage by the shareholders of LogMeIn. Their free product, which had been free for ten years, all of the sudden became a "pay" product. Now, I am the first person who will say that software maker…
Remote Desktop Shadowing often has a lot of benefits. When helping end users determine problems, it is much easier to see what is going on, what is being slecected and what is being clicked on. While the industry has many products to help with this,…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question