?
Solved

ORA-1654 during alter database open

Posted on 2009-05-20
10
Medium Priority
?
1,002 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
[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
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

719 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