Solved

Oracle Upgradation from 8i to 10

Posted on 2008-10-20
17
518 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LINUX Field Separators 7 58
SSH in linux 9 72
VNC stopped working when I log off the PC connected via VPN 20 29
SQL Syntax Question 9 30
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,…
Like many organizations, your foray into cloud computing may have started with an ancillary or security service, like email spam and virus protection. For some, the first or second step into the cloud was moving email off-premise. For others, a clou…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

697 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