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.
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.
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';
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.
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
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...
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'
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'.
Reply With Quote
Bookmarks