?
Solved

Oracle Upgradation from 8i to 10

Posted on 2008-10-20
17
Medium Priority
?
525 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
[X]
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
  • 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 1500 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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,…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

770 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