In this post I will describe the scenario where you already did a restore followed by recovery and realized too late that the filesystem/filesystems that contain the online redologs of the database is too small. When you attempt to start the database, you will get the error that the online redologs do not have enough space to generate:
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-19502: write error on file “/oracle/SID/mirrlogB/log_g14m2.dbf”, block
number 2861057 (block size=512)
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 28: No space left on device
Additional information: 4294967295
Additional information: 1048576
ORA-19502: write error on file “/oracle/SID/mirrlogB/log_g14m2.dbf”, block
number 2865153 (block size=512)
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 28: No space left on device
Additional information: 4294967295
Additional information: 1048576
These errors occur because the database is trying to write information into the redologs, in the mirrlog/origlog location, but cannot find the available space to do that.
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00392: log 6 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 6 thread 1: ‘/oracle/SID/origlogB/log_g16m1.dbf’
ORA-00312: online log 6 thread 1: ‘/oracle/SID/mirrlogB/log_g16m2.dbf’
Now, if you try to shutdown the database, add the neccessary storage in the mirrlog/orriglog FS, where the online redologs are located and after this retry to open the database, you will still get the error:
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00392: log 6 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 6 thread 1: ‘/oracle/SID/origlogB/log_g16m1.dbf’
ORA-00312: online log 6 thread 1: ‘/oracle/SID/mirrlogB/log_g16m2.dbf’
In mount state of the database, the status of the online redologs status can be checked:
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------
6 /oracle/SID/origlogB/log_g16m1.dbf
6 /oracle/SID/mirrlogB/log_g16m2.dbf
5 STALE /oracle/SID/origlogA/log_g15m1.dbf
5 STALE /oracle/SID/mirrlogA/log_g15m2.dbf
4 STALE /oracle/SID/origlogB/log_g14m1.dbf
4 STALE /oracle/SID/mirrlogB/log_g14m2.dbf
3 /oracle/SID/origlogA/log_g13m1.dbf
3 /oracle/SID/mirrlogA/log_g13m2.dbf
2 /oracle/SID/origlogB/log_g12m1.dbf
2 /oracle/SID/mirrlogB/log_g12m2.dbf
1 /oracle/SID/origlogA/log_g11m1.dbf
You can see that there are some online redologs, those that could not fit anymore on the filesystem, that are in STALE status.
Clearing the redolog groups with problem will help in this case:
SQL> alter database clear logfile group 5;
Database altered.
SQL> alter database clear logfile group 4;
Database altered.
Checking the status of the redolog groups:
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 UNUSED
6 1 CLEARING_CURRENT
4 1 CLEARING
5 1 CLEARING
3 1 UNUSED
Check again:
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------
6 /oracle/SID/origlogB/log_g16m1.dbf
6 /oracle/SID/mirrlogB/log_g16m2.dbf
5 /oracle/SID/origlogA/log_g15m1.dbf
5 /oracle/SID/mirrlogA/log_g15m2.dbf
4 /oracle/SID/origlogB/log_g14m1.dbf
4 /oracle/SID/mirrlogB/log_g14m2.dbf
3 /oracle/SID/origlogA/log_g13m1.dbf
3 /oracle/SID/mirrlogA/log_g13m2.dbf
2 /oracle/SID/origlogB/log_g12m1.dbf
2 /oracle/SID/mirrlogB/log_g12m2.dbf
1 /oracle/SID/origlogA/log_g11m1.dbf
1 /oracle/SID/mirrlogA/log_g11m2.dbf
12 rows selected.
SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 UNUSED
6 1 CURRENT
4 1 UNUSED
5 1 UNUSED
3 1 UNUSED