Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-1654 during alter database open

Posted on 2009-05-20
10
Medium Priority
?
1,009 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
ID: 24434680
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
ID: 24434741
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
ID: 24435000
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:DallasDAF
ID: 24435080
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
ID: 24435256
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
 
LVL 48

Expert Comment

by:schwertner
ID: 24435494
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
ID: 24435658
@schwertner:
The OS is Solaris10
0
 

Author Comment

by:DallasDAF
ID: 24436050
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 2000 total points
ID: 24436179
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 48

Expert Comment

by:schwertner
ID: 24438529
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

916 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