Cannot open db

Printable View

10-07-2004, 07:50 AM
itwizzkid

Cannot open db

Cannot start db

Hi, I am getting the following message when i try to start my database:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists

Thu Oct 07 02:58:45 2004
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 736

I moved the datafiles to a new location and had this problem:
SQL> alter database rename file 'C:\ORACLE\ORADATA\TRAIN1\UNDOTBS01.DBF' to 'E:\
ORACLE\ORADATA\TRAIN1\UNDOTBS011.DBF';

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\TRAIN1\UNDOTBS011.DBF'
ORA-01251: Unknown File Header Version read for file number 2
ORA-1092 signalled during: ALTER DATABASE OPEN...

I dropped the datafile and now have the problem above. I cannot restore the UNDOTBS011.DBF.
Is there anyway to get around this?

Thanks

Will
:confused: :rolleyes: :confused:
10-07-2004, 08:01 AM
stecal

1) Create new UNDO tablespace, make it the default.
2) Say goodbye to anything you had in the previous UNDO tablespace. Then get rid of it.
3) Make sure you have a good backup & recovery plan/system. What do you think happens to a tablespace when you whack one of its datafiles? You're lucky you didn't do this to a relevant data type of datafile.
4) Read, study, learn. Which Oracle DBA reference book do you own?
10-07-2004, 10:04 AM
itwizzkid

Stecal,

I can't create a new tablespace because (undo tablespace is auto) the database is mounted not opened.:rolleyes:
10-07-2004, 10:29 AM
gandolf989

Quote:

Originally posted by itwizzkid
Stecal,

I can't create a new tablespace because (undo tablespace is auto) the database is mounted not opened.:rolleyes:

change the UNDO_MANAGEMENT parameter to manual in the parameter file. So that you can open the database and recreate your undo tablespace. Then change it back to auto and bounce the database.

You may need to bounce the database after you change the parameter.
10-07-2004, 12:09 PM
itwizzkid

gandolf989 tried this still cannot start the db. Do I need to specify any rollback_segements?
10-07-2004, 12:58 PM
gandolf989

Quote:

Originally posted by itwizzkid
gandolf989 tried this still cannot start the db. Do I need to specify any rollback_segements?

You should be able to start the database without rollback.
Here is an ask tom article that should walk you through everything you need to do to start your database.

Ask Tom
10-08-2004, 05:48 AM
itwizzkid

gandolf989, Thank for your replies. I tried the suggestion in the article. When I open the database this what I get

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

I looked in the alert log and this what I am seeing:

Errors in file c:\oracle\admin\train1\udump\train1_ora_2036.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists
From udump:
** SESSION ID:(9.3) 2004-10-08 02:41:08.000
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 4s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
*** 2004-10-08 02:41:09.000
KCRA: start recovery claims for 0 data blocks
*** 2004-10-08 02:41:09.000
KCRA: buffers claimed = 0/0, eliminated = 0
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists

Any other leads welcomed
will
10-08-2004, 05:52 AM
DaPi

Since you start without an undo, you shouldn't have rollback_segments= in your initSID.ora file.
10-08-2004, 06:27 AM
itwizzkid

Dapi,

I do not have rollback_segments= in initSID.ora file. I still can only mount the db not open.

Thanks
10-08-2004, 10:14 AM
gandolf989

Have you tried recreating the control file? do an alter database backup controlfile to trace; Find the file that was created in you bdump folder and edit it. Take out all of the comments and references to your undo tablespace and then run that against the database as sys. But make sure that you set UNDO_MANAGEMENT = manual;

This should remove the missing tablespace from your database, you should then be able to open the database and recreate the undo tablespace and change UNDO_MANAGEMENT = auto;
10-11-2004, 06:30 AM
itwizzkid

gandolf989,

Thans for your input. I tried recreating the controlefiles. Now when I try to open the database I get:
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'

I don't mind if I can open the database in an inconsistent state.

Any expert opinion welcomed.

Thanks
10-11-2004, 07:06 AM
akhadar

U need to perform a media recovery.
Issue the following statements from SQL.
1.Shutdown immediate.
2.Startup mount
3.SELECT member FROM v$log l, v$logfile f WHERE l.group# = f.group# AND l.status = 'CURRENT';
4.Recover database until cancel;
5.Apply the log member from step 3.
6.Alter database open resetlogs;
Once ur database is succssefully open then take immediate system backup.
10-11-2004, 09:28 AM
itwizzkid

akhadar,

I get no rows selected

SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> SELECT member FROM v$log l, v$logfile f WHERE l.group# = f.group# AND
2 l.status = 'CURRENT';

no rows selected
10-12-2004, 01:11 AM
akhadar

Before procedding further let me know
1.Is ur database in archive log mode?
2.Do u have a recent backup?. If so restore it.
10-12-2004, 04:30 AM
itwizzkid

Hi the database is in noarchive log mode. The only backup I have has the issue with the undo tablespace. This became corrupted during the backup.
10-12-2004, 04:50 AM
akhadar

Really it is bad that if the database is no archive mode u have a few option.

Do as following

1.Mount the database
2. SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;
This will list all the redo log files and their change numbers.
3. SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
If the CHANGE# is GREATER than the minimum FIRST_CHANGE#
of your logs, the datafile can be recovered.
4.Recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF';
5.keep on applying the redo logs it will request to aplly until u will see the message 'Media recovery complete'.
5.Then open database and take a full backup.
10-12-2004, 09:23 AM
itwizzkid

hi got close,

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
2 FROM V$LOG V1, V$LOGFILE V2
3 WHERE V1.GROUP# = V2.GROUP# ;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

SEQUENCE# FIRST_CHANGE#
---------- -------------
1
E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG
128 14471300

2
E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG
129 14491303

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------

SEQUENCE# FIRST_CHANGE#
---------- -------------

3
E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
130 14511306

SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

FILE# CHANGE#
---------- ----------
1 14511307
3 14511307
4 14511307
5 14511307
6 14511307
7 14511307
8 14511307
9 14511307
10 14511307
11 14511307
12 14511307

FILE# CHANGE#
---------- ----------
13 14511307

12 rows selected.

SQL> Recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 14511307 generated at 10/11/2004 06:19:41 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00130.001
ORA-00280: change 14511307 for thread 1 is in sequence #130

Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG
ORA-00310: archived log contains sequence 128; sequence 130 required
ORA-00334: archived log: 'E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG'

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: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 14511307 generated at 10/11/2004 06:19:41 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00130.001
ORA-00280: change 14511307 for thread 1 is in sequence #130

Specify log: {=suggested | filename | AUTO | CANCEL}
E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

alert log:
ALTER DATABASE RECOVER LOGFILE 'E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG'
Tue Oct 12 06:12:51 2004
Media Recovery Log E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
Incomplete recovery applied all redo ever generated.
Recovery completed through change 14511308
Media Recovery Complete
Completed: ALTER DATABASE RECOVER LOGFILE 'E:\ORACLE\ORADA
Tue Oct 12 06:14:00 2004
alter database open resetlogs
Tue Oct 12 06:14:01 2004
RESETLOGS after complete recovery through change 14511308
Resetting resetlogs activation ID 3709337036 (0xdd17fdcc)
Tue Oct 12 06:14:20 2004
Assigning activation ID 3724130951 (0xddf9ba87)
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG
Successful open of redo thread 1.
Tue Oct 12 06:14:21 2004
SMON: enabling cache recovery
Tue Oct 12 06:14:23 2004
Errors in file c:\oracle\admin\train1\udump\train1_ora_2432.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists

Tue Oct 12 06:14:23 2004
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 2432
ORA-1092 signalled during: alter database open resetlogs...
10-12-2004, 10:38 AM
itwizzkid

I'm going around in circles on this. Does anyone have any leads? Please see below:

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> Recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF';
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF'
10-12-2004, 10:43 AM
gandolf989

Have you done recover datafile 'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF'; ???

Every time it tells you to recover a datafile, do a recover datafile for that file.
10-12-2004, 11:45 AM
itwizzkid

It's exactly what Iam saying. I recover all the datafiles. When I attempt to open the database, I get SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced.

I then start the db mount.. alter database open; and get ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'.

I'm going round in cycles.
10-12-2004, 12:00 PM
gandolf989

Quote:

Originally posted by itwizzkid
It's exactly what Iam saying. I recover all the datafiles. When I attempt to open the database, I get SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced.

I then start the db mount.. alter database open; and get ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'.

I'm going round in cycles.

Try going around in a few more cycles,
and see if it allows you to open the database.
10-12-2004, 12:14 PM
itwizzkid

Hi,

I have tried this a few times.

Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'

SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\DRSYS01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\EXAMPLE01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\INDX01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\ODM01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\TOOLS01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\USERS01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\XDB01.DBF'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM120040519091720_DB.DBF'

Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\DM_NTDISTRIB20040514033149_DB.DB
F'
Media recovery complete.
SQL> recover datafile 'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM1_INDX1.ORA'
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
10-12-2004, 01:07 PM
gandolf989

"recover database until cancel;", help it find any archive logs that that it needs, including pointing it to the online redor logs. When you get to a point where you can't find what it wants type "cancel", and then "open database resetlogs;"
10-13-2004, 01:24 AM
akhadar

Final action what I can sujjest is to use _allow_resetlogs_corruption=TRUE

Do as follows

1.Shutdown the database.

2. Add the following parameter in the init.ora file:

_allow_resetlogs_corruption=TRUE

3. Startup mount;

4. Recover database using backup controlfile until cancel;

5. If prompted for a logfile, type CANCEL.

6. alter database open resetlogs;

Since the database is in Noarchive mode this is the last option.u need to be careful while using _allow_resetlogs_corruption parameter as it may make ur database inconsistent.Since as per replies u only wants ur database open so u can use this option.
10-13-2004, 08:08 AM
itwizzkid

Hi,

None of these have worked so far. I am well and truly scuppered.
10-13-2004, 10:31 AM
gandolf989

Quote:

Originally posted by itwizzkid
Hi,

None of these have worked so far. I am well and truly scuppered.

I really don't understand why you are still having problems.
Is there another DBA that can have a look at the database?
10-13-2004, 11:01 AM
itwizzkid

We have a couple of dba's on this. Every time we try to open the database we get:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

The alert log has the following entries:-
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont...
Wed Oct 13 05:01:28 2004
ALTER DATABASE RECOVER LOGFILE 'E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG'
Wed Oct 13 05:01:28 2004
Media Recovery Log E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG
Incomplete recovery applied all redo ever generated.
Recovery completed through change 14491308
Media Recovery Complete
Completed: ALTER DATABASE RECOVER LOGFILE 'E:\ORACLE\ORADA
Wed Oct 13 05:02:38 2004
alter database open resetlogs
Wed Oct 13 05:02:38 2004
RESETLOGS after complete recovery through change 14491308
Resetting resetlogs activation ID 3724171580 (0xddfa593c)
Online log 1 of thread 1 was previously cleared
Wed Oct 13 05:02:48 2004
Online log 3 of thread 1 was previously cleared
Wed Oct 13 05:02:51 2004
Assigning activation ID 3724197347 (0xddfabde3)
Thread 1 opened at log sequence 1
Current log# 2 seq# 1 mem# 0: E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG
Successful open of redo thread 1.
Wed Oct 13 05:02:52 2004
SMON: enabling cache recovery
Wed Oct 13 05:02:52 2004
Errors in file c:\oracle\admin\train1\udump\train1_ora_2312.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists

Wed Oct 13 05:02:52 2004
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Wed Oct 13 05:02:52 2004
Errors in file c:\oracle\admin\train1\bdump\train1_pmon_2636.trc:
ORA-00704: bootstrap process failure
10-13-2004, 11:06 AM
gandolf989

Quote:

Errors in file c:\oracle\admin\train1udump\train1_ora_2312.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists

Have you looked at the "c:\oracle\admin\train1udump\train1_ora_2312.trc" file? Do you want to post it here? Is there another file that is missing? i.e. what do you think that "ORA-08103: object no longer exists" relates to?
10-13-2004, 11:13 AM
itwizzkid

Hi,

This is the train1_ora_2312.trc:-
Dump file c:\oracle\admin\train1\udump\train1_ora_2312.trc
Mon Oct 11 06:25:25 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 0

Oracle process number: 12

Windows thread id: 2312, image: ORACLE.EXE

*** SESSION ID:(9.1) 2004-10-11 06:25:25.000
(blkno = 0x8a, size = 276, max = 1, in-use = 0, last-recid= 0)
(blkno = 0x8b, size = 56, max = 145, in-use = 0, last-recid= 0)
Dump file c:\oracle\admin\train1\udump\train1_ora_2312.trc
Tue Oct 12 07:28:11 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 1

Oracle process number: 12

Windows thread id: 2312, image: ORACLE.EXE

*** SESSION ID:(9.3) 2004-10-12 07:28:11.000
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 34s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
*** 2004-10-12 07:30:21.000
*** 2004-10-12 07:30:42.000
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists
Dump file c:\oracle\admin\train1\udump\train1_ora_2312.trc
Tue Oct 12 07:33:11 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 1

Oracle process number: 12

Windows thread id: 2312, image: ORACLE.EXE

*** SESSION ID:(9.3) 2004-10-12 07:33:11.000
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 2s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 2s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 2s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 2s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 1s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
*** 2004-10-12 08:26:26.000
KCRA: start recovery claims for 0 data blocks
*** 2004-10-12 08:26:26.000
KCRA: buffers claimed = 0/0, eliminated = 0
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists
Dump file c:\oracle\admin\train1\udump\train1_ora_2312.trc
Wed Oct 13 04:52:20 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 0

Oracle process number: 12

Windows thread id: 2312, image: ORACLE.EXE

*** SESSION ID:(9.17) 2004-10-13 04:52:20.000
Archiving is disabled
Archiving is disabled
Dump file c:\oracle\admin\train1\udump\train1_ora_2312.trc
Wed Oct 13 04:56:38 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 0

Oracle process number: 13

Windows thread id: 2312, image: ORACLE.EXE

*** SESSION ID:(9.3) 2004-10-13 04:56:38.000
Archiving is disabled
Archiving is disabled
Dump file c:\oracle\admin\train1\udump\train1_ora_2312.trc
Wed Oct 13 05:01:32 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: 2312, image: ORACLE.EXE

*** SESSION ID:(9.3) 2004-10-13 05:01:32.000
----- Redo read statistics for thread 1 -----
Read rate (ASYNC) = 0Kb/sec => 1 blocks in 30s
Read buffer = 8192Kb (16384 blocks)
Longest record = 0Kb
Record moves = 0/1 (0%)
----------------------------------------------
*** 2004-10-13 05:02:38.000
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-08103: object no longer exists

I think that ORA-08103: object no longer exists refers to the undo tablespace.

regards
10-13-2004, 11:29 AM
gandolf989

Ok, do a "alter batabase backup control file to trace;", while the databas eis mounted. Go find the file in the c:\oracle\admin\train1\bdump, or something like it, directory, rename it to something meaningful and post it here.
10-13-2004, 11:50 AM
itwizzkid

gandolf989,

Here it is:- Backup controlfile

Dump file c:\oracle\admin\train1\udump\train1_ora_2760.trc
Wed Oct 13 08:42:26 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 0

Oracle process number: 12

Windows thread id: 2760, image: ORACLE.EXE

*** SESSION ID:(9.1) 2004-10-13 08:42:26.000
(blkno = 0x7a, size = 276, max = 1, in-use = 1, last-recid= 0)
(blkno = 0x7b, size = 56, max = 145, in-use = 1, last-recid= 1)
Dump file c:\oracle\admin\train1\udump\train1_ora_2760.trc
Wed Oct 13 08:44:29 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 0

Oracle process number: 12

Windows thread id: 2760, image: ORACLE.EXE

*** SESSION ID:(9.3) 2004-10-13 08:44:29.000
Archiving is disabled
Archiving is disabled
Dump file c:\oracle\admin\train1\udump\train1_ora_2760.trc
Wed Oct 13 08:47:17 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: train1

Redo thread mounted by this instance: 1

Oracle process number: 12

Windows thread id: 2760, image: ORACLE.EXE

*** SESSION ID:(9.3) 2004-10-13 08:47:17.000
*** 2004-10-13 08:47:17.000
# The following are current System-scope REDO Log Archival related
# parameters and can be included in the database initialization file.
#
# LOG_ARCHIVE_DEST=''
# LOG_ARCHIVE_DUPLEX_DEST=''
#
# LOG_ARCHIVE_FORMAT=ARC%S.%T
# REMOTE_ARCHIVE_ENABLE=TRUE
# LOG_ARCHIVE_MAX_PROCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\ora92\RDBMS'
# LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'
# LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
# LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
# LOG_ARCHIVE_DEST_STATE_1=ENABLE
#
# Below are two sets of SQL statements, each of which creates a new
# control file and uses it to open the database. The first set opens
# the database with the NORESETLOGS option and should be used only if
# the current versions of all online logs are available. The second
# set opens the database with the RESETLOGS option and should be used
# if online logs are unavailable.
# The appropriate set of statements can be copied from the trace into
# a script file, edited as necessary, and executed when there is a
# need to re-create the control file.
#
# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TRAIN1" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG' SIZE 100M,
GROUP 2 'E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG' SIZE 100M,
GROUP 3 'E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\UNDOTBS011.DBF',
'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DRSYS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\EXAMPLE01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\INDX01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\ODM01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\TOOLS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\USERS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\XDB01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM120040519091720_DB.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_NTDISTRIB20040514033149_DB.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM1_INDX1.ORA'
CHARACTER SET AL32UTF8
;
# Take files offline to match current control file.
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\TRAIN1\UNDOTBS011.DBF' OFFLINE DROP;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\TRAIN1\TEMP01.DBF' REUSE;
# End of tempfile additions.
#
# Set #2. RESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TRAIN1" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG' SIZE 100M,
GROUP 2 'E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG' SIZE 100M,
GROUP 3 'E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\UNDOTBS011.DBF',
'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DRSYS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\EXAMPLE01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\INDX01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\ODM01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\TOOLS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\USERS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\XDB01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM120040519091720_DB.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_NTDISTRIB20040514033149_DB.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM1_INDX1.ORA'
CHARACTER SET AL32UTF8
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\TRAIN1\TEMP01.DBF' REUSE;
# End of tempfile additions.
#
10-13-2004, 12:02 PM
gandolf989

I edited what you sent and created a file. Take a look at it and see if it helps. I removed all references to the undo tablespace. You will need to rectreate that once you are able to reopen your database. You should also add additional log groups and duplex them. But if this is only a test database then that may not be neccessary.

Let mw know if this helps.
10-13-2004, 12:04 PM
gandolf989

1 Attachment(s)

I had to rename the file to a text file.
Apparently .sql could contain a virus! :rolleyes:
10-13-2004, 12:17 PM
itwizzkid

Hi,

This what happened with the script-

SQL> @restart_db.sql
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\TRAIN1\TEMP01.DBF' REUSE
*
ERROR at line 1:
ORA-01109: database not open
10-13-2004, 12:20 PM
itwizzkid

The lert log shows this entries:-

CREATE CONTROLFILE REUSE DATABASE "TRAIN1" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG' SIZE 100M,
GROUP 2 'E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG' SIZE 100M,
GROUP 3 'E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG' SIZE 100M
DATAFILE
'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DRSYS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\EXAMPLE01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\INDX01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\ODM01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\TOOLS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\USERS01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\XDB01.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM120040519091720_DB.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_NTDISTRIB20040514033149_DB.DBF',
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM1_INDX1.ORA'
CHARACTER SET AL32UTF8

Wed Oct 13 09:15:02 2004
Successful mount of redo thread 1, with mount id 3724243540.
Wed Oct 13 09:15:02 2004
Completed: CREATE CONTROLFILE REUSE DATABASE "TRAIN1" RESETLO
Wed Oct 13 09:15:02 2004
ALTER DATABASE RECOVER DATABASE
Media Recovery Start
Media Recovery failed with error 1610
ORA-283 signalled during: ALTER DATABASE RECOVER DATABASE ...
Wed Oct 13 09:15:03 2004
ALTER DATABASE OPEN RESETLOGS
ORA-1194 signalled during: ALTER DATABASE OPEN RESETLOGS...
Wed Oct 13 09:15:03 2004
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\TRAIN1\TEMP01.DBF' REUSE
ORA-1109 signalled during: ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORAD...

Thanks
10-13-2004, 01:14 PM
gandolf989

Can you check to see if 'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF' actually exists?
While you are at it check that all of the remaining files exist.

'E:\ORACLE\ORADATA\TRAIN1\REDO01.LOG'
'E:\ORACLE\ORADATA\TRAIN1\REDO02.LOG'
'E:\ORACLE\ORADATA\TRAIN1\REDO03.LOG'
'E:\ORACLE\ORADATA\TRAIN1\SYSTEM01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\CWMLITE01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\DRSYS01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\EXAMPLE01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\INDX01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\ODM01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\TOOLS01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\USERS01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\XDB01.DBF'
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM120040519091720_DB.DBF'
'E:\ORACLE\ORADATA\TRAIN1\DM_NTDISTRIB20040514033149_DB.DBF'
'E:\ORACLE\ORADATA\TRAIN1\DM_W2KVM1_INDX1.ORA'
10-14-2004, 04:45 AM
itwizzkid

All the datafiles exist
10-14-2004, 05:40 AM
Tago

Hi, itwizzkid. About 2 months ago i had a problem with recover db.
As i have ORACLE 7.2.3 so my suggestions may be not correct, but during my attempts i understood:

1.While endless attempts it is necessary to restore the data files from tape ANEW.

2.While recovering DB "until time 'XXX'",
this time must be equal the time of finishing backup or later, NOT EARLIER!

--Do you have copies of original files?
You had error ORA-01194
It means:" The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery." -OEMes
If you have copy then refresh your files and try to recover until time xxx -the time of last log.
Tago.