Solved

Oracle Upgradation from 8i to 10

Posted on 2008-10-20
17
507 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now