troubleshooting Question

Oracle refuses to load db

Avatar of Bob_Zumbrunnen
Bob_Zumbrunnen asked on
Oracle Database
37 Comments4 Solutions6194 ViewsLast Modified:
While removing rows from a 19.2M-row table in Oracle 8.1.6 yesterday, in batches of about 300k rows at a time, the system suddenly froze and has been unresponsive since.  We've tried many different things we've found online and sometimes we'll get past one error message only to get to another.  I don't expect this to be an easy one, so 500 points.  Plus, it's for a very large website with about a million hits per day, so urgency it's quite urgent; I'm sure I'm getting cussed out a million times a day.

I expect this to be multi-step.  If you can get me past one error message, I'm likely to get another.

And there is no cold backup.

Current error situation:

SQL> alter database open resetlogs;
alter database open resetlogs
ERROR at line 1:
ORA-01245: offline file 21 will be lost if RESETLOGS is done
ORA-01110: data file 21: '/u07/oradata/msg/big_rbs01.dbf'

SQL> recover database until cancel;
ORA-00279: change 6898137434 generated at 03/25/2004 11:49:04 needed for thread
ORA-00289: suggestion : /export/oracle/product/8.1.6/dbs/arch1_1.dbf
ORA-00280: change 6898137434 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

There's no such thing as arch1_1.dbf on this system, so what happens when I hit <RET> is predictable:

ORA-00308: cannot open archived log
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oradata/msg/system01.dbf'

And let me backtrack just a bit to be sure I'm covering as many questions as possible right upfront.

oracle@oramsg<msg>$ sqlplus internal

SQL*Plus: Release - Production on Thu Mar 25 12:39:44 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production

SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  634404848 bytes
Fixed Size                    69616 bytes
Variable Size             141750272 bytes
Database Buffers          491520000 bytes
Redo Buffers                1064960 bytes
Database mounted.

Looks peachy, eh?

SQL> select * from msg_user where userid=4591117;
select * from msg_user where userid=4591117
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Okay, not peachy.

And if I try to log in as the correct user instead of internal:

oracle@oramsg<msg>$ sqlplus message@msg

SQL*Plus: Release - Production on Thu Mar 25 12:42:19 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Enter password:
ORA-01033: ORACLE initialization or shutdown in progress

Enter user-name:

This is running on a Sun/Solaris box and if we try to use the W2K Enterprise Manager, we get the same 01033 error.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 4 Answers and 37 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 37 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros