-
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
-
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.
-
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
-
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.
-
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.
-
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.
-
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...
-
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'
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|