Solved

ORA-1654 during alter database open

Posted on 2009-05-20
10
989 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

739 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