You have lost all your online redo logs. As a result, your database has crashed. You have
tried to restart the database and clear the online redo log files, but when you try to open the
database you get the following error.
SQL> startup
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1298612 bytes
Variable Size 142610252 bytes
Database Buffers 20971520 bytes
Redo Buffers 2514944 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database clear logfile group 2;
alter database clear logfile group 2 * ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’
ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’
What steps must you take to resolve the error?
a: Issue the recover database redo logs command.
b: Issue the Startup Mount command to mount the database.
c: Restore the last full database backup.
d: Perform a point-in-time recovery, applying all archived redo logs that are available.
e: Restore all archived redo logs generated during and after the last full database backup.
f: Open the database using the alter database open resetlogs command.
g: Issue the alter database open command.
A.
b, a, f
B.
e, b, a, f
C.
e, b, a, g
D.
b, a, g
E.
c, e, b, d, f
Explanation:
If the online redo log is in ACTIVE or CURRENT status, you cannot issue CLEAR LOGFILE
GROUP n command, it occurs ORA-01624 error.
The option (a) is invalid, there is NO such recover database redo log command, so that the
answer must be (c, e, b, d, f).
It applies an incomplete recovery, then open database with RESETLOGS option.
If you have lost multiple groups of the online redo log, then use the recovery method for the most difficult log to recover. The order of difficulty is, from most difficult to least difficult, is as follows:
The CURRENT online redo log (most difficult)
An ACTIVE online redo log
An INACTIVE unarchived online redo log
An INACTIVE online redo log
I notice that during the attempt to start the database “instance recovery” didn’t manage to resolve the problem.
As can be seen from the error above …
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
…we could have either
1. ACTIVE online redo log failure
or
2. CURRENT online redo log failure (the most difficult)
hmmm what to do ??
Also none of the solutions here involve a
ALTER SYSTEM CHECKPOINT;
which would be the solution for an “ACTIVE online redo log” failure. (obviously in a real world situation we would not get this hint. Maybe somebody could tell me how I could be certain of this without querying V$LOG and V$LOGFILE)
anyway lets assume then that our situation is that of
“CURRENT online redo log failure” (the most difficult)
We have 2 options DPITR (database point in time recovery) or “FLASHBACK DATABASE”
no answers pertain to “FLASHBACK DATABASE” so lets assume we must “DPITR to before the problem occurred”. Generally this involves
1. Mount the database.
2. Restore all the datafiles (and the controlfile if necessary).
3. Recover the database until a certain point.
4. Open the database with RESETLOGS.
Steps 1 and 2 above can be reversed i.e. mount before restoring
but as we all know, many roads lead to Rome (hello all you Romans) i.e. there are many ways to code this
The possible solutions given above are …
A (b, a, f) No such thing as a recover database redo logs command, i.e. wrong
B (e, b, a, f) No such thing as a recover database redo logs command, i.e. wrong
C (e, b, a, g) No such thing as a recover database redo logs command. Does not OPEN RESETLOGS, i.e. wrong
D (b, a, g) No such thing as a recover database redo logs command. Does not OPEN RESETLOGS, i.e. wrong
E (c, e, b, d, f) lets check out this solution now ….
c: Restore the last full database backup.
e: Restore all archived redo logs generated during and after the last full database backup.
b: Issue the Startup Mount command to mount the database.
d: Perform a point-in-time recovery, applying all archived redo logs that are available.
f: Open the database using the alter database open resetlogs command.
That fits ….
Answer is E
After further consideration ….
So your database has crashed …
This is similar to recent question …but I will post the details here also
if after receiving these errors in the alert log file
— ORA-00313: open failed for members of log group 3 of thread 1
— ORA-00312: online log 3 thread 1: ‘D:\ORADATA\AHEADPRD\REDO03.LOG’
You should first MOUNT the database and query V$LOG and V$LOGFILE to see if the redo log group with the problem was CURRENT one. (The database has crashed so this is probably the case)
If so then you have to either
– FLASHBACK the database
or
– DPITR to when the problem occured.
If it was not, (and typically this happens after a consistent shutdown which is NOT the case in this question) then you
ALTER DATABASE CLEAR (UNARCHIVED) LOGFILE GROUP n (RECOVERABLE DATAFILE);
Attempt without UNARCHIVED and RECOVERABLE DATAFILE.
If UNARCHIVED is required then you must backup after.
If RECOVERABLE DATAFILE is required then proceed as if it was the CURRENT logfile that had the problem.
Here we do a DBPITR (Flashback is not one of the options given here)
E (c, e, b, d, f) lets check out this solution now ….
c: Restore the last full database backup.
e: Restore all archived redo logs generated during and after the last full database backup.
b: Issue the Startup Mount command to mount the database.
d: Perform a point-in-time recovery, applying all archived redo logs that are available.
f: Open the database using the alter database open resetlogs command.
That fits ….
Answer is E