Which is the correct order of these steps in this case?

Upon starting your database, you receive the following error:
SQL> startup
ORACLE instance started.
Total System Global Area 171581440 bytes
Fixed Size 1298640 bytes
Variable Size 146804528 bytes
Database Buffers 20971520 bytes
Redo Buffers 2506752 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/oracle01/oradata/orcl/redo01.log’
ORA-00312: online log 1 thread 1: ‘/oracle01/oradata/orcl/redo01a.log’
You can choose from the following steps:
a: Restore the database datafiles.
b: Issue the alter database clear unarchived logfile group 1 command.
c: Issue the alter database open command.
d: Issue the alter database open resetlogs command.
e: Recover the database using point-in-time recovery.
f: Issue the Startup Mount command to mount the database.
g: Back up the database.
Which is the correct order of these steps in this case?

A.
a, f, e, d, g

B.
f, e, d

C.
f, b, c, g

D.
a, f, c

E.
The database cannot be recovered.

Explanation:
1. one step of process must be ALTER DATABASE OPEN, since the database instance is
started and mounted from clean shutdown.
2. After redo log clear done, you must backup the database immediately, so that you have a
backup you can use for complete recovery without relying on the cleared log group.

Show Hint

← Previous question

Next question →

Leave a Reply 4

marcin

why mounting the database while it is already mounted?

Eamon

SQL> set line 2000

SQL> select * from v$logfile ;

GROUP# STATUS TYPE MEMBER
———- ——- ——- ————————————————————————————————————
3 ONLINE D:\ORADATA\AHEADPRD\REDO03.LOG
2 ONLINE D:\ORADATA\AHEADPRD\REDO02.LOG
1 ONLINE D:\ORADATA\AHEADPRD\REDO01.LOG

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

C:\Windows\system32>move D:\ORADATA\AHEADPRD\REDO03.LOG D:\ORADATA\AHEADPRD\xREDO03.LOG
1 file(s) moved.

C:\Windows\system32>sqlplus / AS SYSDBA

SQL> startup
ORACLE instance started.

Total System Global Area 3407261696 bytes
Fixed Size 2180504 bytes
Variable Size 1962936936 bytes
Database Buffers 1426063360 bytes
Redo Buffers 16080896 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 12416
Session ID: 127 Serial number: 3

— The Contents of the alert log file show …
— Errors in file c:\app\alan.obrien\diag\rdbms\aheadprd\aheadprd\trace\aheadprd_lgwr_12984.trc:
— 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’

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
ERROR:
ORA-03114: not connected to ORACLE

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Windows\system32>sqlplus / AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 15 15:17:20 2016

Connected to an idle instance.

SQL> STARTUP FORCE MOUNT
ORACLE instance started.

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 (‘D:\ORADATA\AHEADPRD\REDO03.LOG’) SIZE 50M;

Database altered.

SQL> set line 2000
SQL> select * from v$logfile ;

SQL> alter database open;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> select * from v$logfile ;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 31 52428800 512 1 YES INACTIVE 1383052 15-FEB-16 1383055 15-FEB-16
2 1 32 52428800 512 1 NO CURRENT 1383055 15-FEB-16 2.8147E+14
3 1 30 52428800 512 1 YES INACTIVE 1383049 15-FEB-16 1383052 15-FEB-16

SQL>

Eamon

After further consideration, if after receiving these errors in teh 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. If so then you have to either FLASHBACK the database or do a DPITR to when the problem occured. If it was not, (and typically this happens after a consistent shutdown which is 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.

its only correct if its not the current log group.