Solved

ORA-1654 during alter database open

Posted on 2009-05-20
10
982 Views
Last Modified: 2012-05-07
Hi,
I have the RMAN backup of a 9.2.0.5 database, from a remote server.

I restored it. Applied archive logs.Then there was "alter database open resetlogs" which forced disconnection because the dbms on my solaris server is 10g.

Now I understand I need to upgrade my DB, and for that I need a STARTUP UPGRADE.

But my DB wont open - in any mode.
The error I get on my prompt is:
ORA-01092: ORACLE instance terminated. Disconnection forced

And my alert log and trc file say:
ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_FIXED_OBJ$_OBJ# by 2 in tablespace SYSTEM

Now I can see that I need to add space to SYSTEM tablespace, but how do I do that if my DB wont open :(

Any inputs are appreciated.
Thanks.
0
Comment
Question by:DallasDAF
  • 5
  • 3
  • 2
10 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Have you tried, in this order:

startup nomount;
alter database mount;

--Do the datafile manipulation there (extend or add system datafile)

shutdown
startup upgrade;




0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Err sorry I don't know what I was thinking, you cannot extend the datafile while in mount mode. :(
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Since you have a backup of the database, try opening in restricted mode _without_ upgrade option. If that works, turn on autoextend  on system tablespace, or manually extend it. Then shutdown abort, and restart with upgrade option

startup restrict;

alter database datafile '/oracle/oradata/ORA1/system.dbf' autoextend on;
-- or resize

shutdown abort

startup upgrade;

0
 

Author Comment

by:DallasDAF
Comment Utility
startup does not go through, not even in the restricted mode. The alertlog shows the same ORA-00604 and 01654 error.
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
I would suggest installing 9.2.0.5 binaries (in a different home or different server) so you can properly open the database, then do maintennance such as setting autoextend on, then try the upgrade again.

There are some bugs relating to 9i -> 10g that actually require opening in 9i prior to running the upgrade, or at least Oracle's solution does not include the option to address those bugs using 10g binaries, so I would bet your case falls into that category.
0
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.

 
LVL 47

Expert Comment

by:schwertner
Comment Utility
This error does not necessarily indicate whether or not you have enough space in
the tablespace, it merely indicates that Oracle could not find a large enough
area of free contiguous space in which to fit the next extent.

So you have to try to cleanup (or defragment) the disk.
You do not reveal the OS you use, so try to investigate the tools available there for cleanup.
0
 

Author Comment

by:DallasDAF
Comment Utility
@schwertner:
The OS is Solaris10
0
 

Author Comment

by:DallasDAF
Comment Utility
My final goal is to move ALL data from this 9i DB to another database. So  basically, I just want to be able to access the data in the DB. Once the data is moved I'll be deleting this DB.

I've already started the 9i download from oracle.

@schwertner:
I checked, there is enough space on the server. What do you suggest?
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
Comment Utility
It is not a server space issue, or else you wold have seen an additional ORA error in your alert log indicating file system full. Oracle could not extend the tablespace due to it not being an auto-extend, I say that simply due to the lack of any additional error code. This is the most common cause.

All of the resolutions for intra-database space problems require the database be open (coalesce, extend, etc.) so your problem is simply opening the database, so adding filesystem space will not help, nor will any other "alter system" or "alter database" most likely.

Even opening in read only might get it open, but won't help, because you can't fix the problem in read only state. I recommend opening it with 9i and fixing the underlying issue or just export the data directly with 9i export and move on to a fresh empty database in 10g.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
A workaround is to go to the original database and to set SYSTEM tablespace autoextendable.
But it is set so by default (at least in 10g)
After that use the backup to clone the DB.
Given the upgraded Oracle home the only thing you have to do is to upgrade the Catalog
(catupgrd.sql script) plus all prereqiusites and UTLRP.SQL after that.
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 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

10 Experts available now in Live!

Get 1:1 Help Now