Fix the ORA-12801, ORA-08103, and ORA-06512 in Oracle RAC

 

Fix the ORA-00312,ORA-00312,ORA-17503 and related errors. 

This typically occurs when the database is being opened in READ WRITE mode after recovery, and there is corruption or inconsistency in the redo log files.

We observed the following ORA error in our 3-node RAC environment. The steps below can be followed to resolve the issue in RAC, standalone, and Data Guard environments. When we attempted to bring up the database in our 3-node RAC/standalone/Data Guard environment, the database went to the mount state. At that point, we received a "WARNING" message in our alert.log indicating that the disk group had exhausted space on '+DISKGROUP_NAME'. We then added some disks to our disk group/mount point.

After that, when we attempted to open the database, we encountered the following error:

If you want more details go to the the  Explanation session is given by below.

SQL> alter database open;

ORA-00313: open failed for members of log group 11 of thread 1

ORA-00312: online log 11 thread 1: '<path>\<Location>/onlinelog/redo01.log'

ORA-17503: ksfdopn:2 Failed to open file <path>\<Location>/onlinelog/redo01.log

ORA-15173: entry 'redo01.log' does not exist in directory 'onlinelog'

NOTE: There is no need to disable any instance. You can easily resolve this ORA error by following the steps below.

To resolve this, you can perform the following steps:

STEP 1 : Drop the Online and Standby Redo Logs

SQL> alter database drop logfile group 1;

ERROR at line 1:

ORA-00350: log 1 of instance orcl (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '<path>\<Location>\redo01.log'

STEP 2 : If You Encounter the Above Error, Clear the Redo Logs and Drop Them Again

SQL> alter database clear logfile group 1;

ERROR at line 1:

ORA-00350: log 1 of instance orcl (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '<path>\<Location>\redo01.log'

STEP 3 : Add New Online Redo Logs for Each Instance

To avoid this ORA error in RAC, standalone, and Data Guard environments, add new online redo logs for each instance.

NOTE: Before re-name or adding redo logs, ensure that the standby_file_management parameter is set to "MANUAL."

STEP 4: Set standby_file_management to 'MANUAL'

SQL> alter system set standby_file_management='MANUAL' scope=both sid='*';

STEP 5 : Add New Redo Logs or re-name redo logs in RAC or Data Guard Environment

SQL> alter database add logfile thread 1 group 101('<DISKGROUP_NAME_1>') size <REDO_LOG_SIZE>;

SQL> alter database add logfile thread 2 group 201('<DISKGROUP_NAME_1>') size <REDO_LOG_SIZE>;

SQL> alter database add logfile thread 3 group 301('<DISKGROUP_NAME_1>') size <REDO_LOG_SIZE>;

"OR"

Add new redo logs in a standalone or Data Guard environment:

SQL> alter database add logfile group 1 ('+DISKGROUP_NAME_1') size <REDO_LOG_SIZE>;

"OR"

SQL> alter database rename file  '<Current_redo_log>' to '<NEW_redo_log>';

if re-name is working fine then no need to add redo logfile, then we can proceed with direct ALTER DATABASE OPEN;
now the issue was resolved, if issue is still persist then we can go for add the redo log's and after that clear the redo log and drop it.

Once the new online redo logs have been added to the RAC/standalone database, drop the old standby/online redo logs.

STEP 6 : Clear the Redo Log Groups

SQL> alter database clear logfile group group_number;

SQL> alter database clear logfile group group_number;

SQL> alter database clear logfile group group_number;

STEP 7 : Drop the Redo Log Groups

SQL> alter database drop logfile group group_number;

SQL> alter database drop logfile group group_number;

SQL> alter database drop logfile group group_number;

STEP 8 : Revert standby_file_management to 'AUTO'

SQL> alter system set standby_file_management='AUTO' scope=both sid='*';

STEP 9 : Open the Database

SQL> alter database open;

Explanation : 

Error Breakdown :

• ORA-00313 : Failed to open members of the specified log group.

• ORA-00312 : Specifies the particular redo log file that couldn't be accessed.
• ORA-17503 : Indicates a problem with accessing the file at the OS level.
• ORA-15173 : The redo log file doesn't exist in the specified directory.
• ORA-00350 : Redo log file block corruption

Important Notes :

• Always ensure you have a valid backup before performing operations on the redo log files.
• If you're using a clustered environment (RAC), ensure that the changes are propagated correctly across all nodes.

Major Points :

Identify the Problematic Log File :

• SQL > SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE GROUP# = group_number;
• This will help you identify the members of the problematic log group.

• We are rename the 'current redo logfile' to  'new redo logfile' 
•  SQL >alter database rename file '<current_redo_logfile.log>' to '<NEW_redo_logfile.log>'; 
• If is fail to rename the redo log  then clear it and drop it.

Then Drop and Recreate the Redo Log Group : 

• If the log file is corrupted or missing, you can drop the affected log group and recreate it.

If Dropping Is Not Possible (e.g., Active Log) :

• If the log group is active, you may need to force the database to switch logs and archive the current log,
• SQL> ALTER SYSTEM SWITCH LOGFILE;
• If it is mount stage drop is not possible you have to recreate the online logfile.
• SQL>   Follow the step number 5 for your reference in oracle RAC & Standalone database.

Attempt to Open the Database :

• Once the above steps are complete, try to open the database in READ.

 If any thing missing for this particular blog then we are uploaded the missing things in my upcoming Blog.

NOTE:  If you face any issues related to database administration,feel free to write down your queries in the comments.I will do my best to resolve your issues and get back to you as soon as possible.