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_rbs0
1.dbf'
SQL> recover database until cancel;
ORA-00279: change 6898137434 generated at 03/25/2004 11:49:04 needed for thread
1
ORA-00289: suggestion : /export/oracle/product/8.1
.6/dbs/arc
h1_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
'/export/oracle/product/8.
1.6/dbs/ar
ch1_1.dbf'
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 8.1.6.0.0 - Production on Thu Mar 25 12:39:44 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 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.
SQL>
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 8.1.6.0.0 - Production on Thu Mar 25 12:42:19 2004
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter password:
ERROR:
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.
Start Free Trial
