Solved

Oracle Upgradation from 8i to 10

Posted on 2008-10-20
17
510 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to have a cron job run until a condition is met 12 55
Unable to RDP to windows 10 pro machine 15 75
mcrypt_create_iv() is deprecated 4 74
database upgrade 8 41
Remote Desktop Connections allow you to control remote host machines via the magic of the Internet and RDP (Remote Desktop Protocol). For the purposes of this article we will assume you are connecting from your home PC or laptop to a remote offic…
Fine Tune your automatic Updates for Ubuntu / Debian
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…

920 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

15 Experts available now in Live!

Get 1:1 Help Now