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

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

ERROR at line 1:

ORA-12801: error signaled in parallel query server P00Y, instance gani3:server (3)

ORA-06512: at "SYS.DBMS_STATS", line 40799

ORA-06512: at "SYS.DBMS_STATS", line 30302

ORA-08103: object no longer exists

ORA-06512: at "SYS.DBMS_SQL", line 1735

ORA-06512: at "SYS.DBMS_STATS", line 30219

If you sea the ORA-12801 It means the in RAC database level all the PARALLEL_MAX_SERVERS has reached for Particular instance, If it is STANDALONE then it reached the PARALLEL_MAX_SERVERS. So, avoid this issue KILL the un-wanted session at Database level else you can wait for completion of Current running session at DB level. "OR" You can increase the PARALLEL_MAX_SERVERS at database level, If you change the parameter at DB level, Then DB bounce/Restart is required because it is a static PARAMETER... Command Like : ALTER SYSTEM SET PARALLEL_MAX_SERVERS=90 SCOPE=BOTH;
If you sea the ORA-06512 It means the error is ORACLE PL-SQL/SQL procedure or Code level error, So you can go through the procedure and pl-sql code then FIX the Procedure or Code...
If you sea the ORA-08103 It means, When my application team retrieves the info from the database, through SQL/PL-SQL query for a particular TABLE or it may be a PARATITION TABLE that time we facing below error In RAC OR Standalone Database.

So, many time this issue faced by the application team and database team while running the SELECT statement for particular table to check the object related data. This is a very common error for an oracle DBA.

Root Cause : -

This issue come basically when the Block is Corrupted, We are access the data after the Truncation or DROP of the table, Invalid Block, Block has no data, Table/Index level corrupted & it may be a new BUG. That time we are getting below error.
So we are found that this error happens when my application /DBA team run the Procedure or Scheduler job that time we are getting the above error. Due to corrupted BLOCK.

Error --: ORA-08103: object no longer exists || Check database level : -

To check first the all the details of the Table/ Partition Table like Check the total size of table.
Ensure the object still exists and is valid in the database. Query the DBA_OBJECTS view for its status.
If the object exists but is flagged as invalid, consider rebuilding indexes or refreshing statistics to resolve the error.
Since this is happening in an RAC environment, review the instance and alert logs for any node or session-related issues. Try in different node.
check the Planc and execution plan for the sql query, if any changes their the take decision according and troubleshoot the issue.
check the last analyzed of TABLE and PARATITION table with it's INDEXES. If is is STALE state then perform stats-gather of Table.
Try rerunning the DBMS_STATS operation once you have verified the object’s existence and status . And re-run the Procedure.
Check the table if any corrupted block is their.

We are observed that some Insert/delete is happen in table so, we perform stats gather that time we are getting below error.

SQL> BEGIN dbms_stats.gather_table_stats(ownname=>'GANESH',tabname=>'GA07',

partname=>'SYS_P5167' ,granularity=>'PARTITION',METHOD_OPT=>'for all columns size 1', CASCADE=>true,degree=>32,estimate_percent=>20); END;

ERROR at line 1:

ORA-12801: error signaled in parallel query server P00Y, instance rptappl1:dbrept3 (3)

ORA-06512: at "SYS.DBMS_STATS", line 40799

ORA-06512: at "SYS.DBMS_STATS", line 30302

ORA-08103: object no longer exists

ORA-06512: at "SYS.DBMS_SQL", line 1735

ORA-06512: at "SYS.DBMS_STATS", line 30219

So, We are fix the error permanently then we have valid the backup or valid export of the TABLE.
Else There are many solution to solve this error, but we are Solve this issue using DBMS_REPAIR.

DBMS_REPAIR :-

It is a package of procedure that help to detect and repair the corrupted block of TABLE/PARATITION TABLE and it's INDEXES. So, this issue is detected only database admin.

We have observed that the there is an issue with only two partitions "SYS_P5167" and "SYS_P5145" of IG07 object. Upon analysis, we have observed data Object Id mismatch errors (corruption errors) for many blocks because of which user is facing "ORA-08103: object no longer exists" error.

TABLE_OWNER : GANESH & TABLE_NAME : GA07 , REPAIR_TABLE : GA07

TROUBLESHOOT USING DBMS_REPAIR:-

First we create a repair table in the database
Second create a orphan Key table using DBMS_REPAIR
Find the corrupted Blocks
Fix the corrupted Blocks
Skip the corrupted Blocks

01 . We create a repair table in the database : -

You have to mention the only TABLESPACE_NAME in The below procedure. Those who are available in your DB. This REPAIRE_TABLE provide only table corrupted details.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'SYSTEM');
END;
/

If you create a repair table then run the below command for the check corrupted block in table. after that create a orphan table.

02 . Second create a orphan key table using DBMS_REPAIR : -

Orphan Key basically provides all the corrupted details of INDEXES in database level.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'SYSTEM');
END;
/

03 . Find the corrupted Blocks : -

To find the corrupted blocks then mention the table name and it's Object_name then it is automatically detected.

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'GANESH',
OBJECT_NAME => 'GA07',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

After that once above step is completed then find the exact corrupted list of OBJECT name like GA07 Table.

SQL > SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;

BLOCK_ID CORRUPT_TYPE   CORRUPT_DESCRIPTION
   ----------            ------------------   ----------------------------------------
412154 6151 ORA-08103   Data Object Id Mismatch
412155 6151 ORA-08103     Data Object Id Mismatch
412156 6151 ORA-08103    Data Object Id Mismatch
412157 6151 ORA-08103   Data Object Id Mismatch
412158 6151 ORA-08103   Data Object Id Mismatch

04 . Fix the corrupted Blocks : -

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'GANESH',
OBJECT_NAME=> 'GA07',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/

Using above procedure you can fix the corrupted blocks. To Confirm this then the below output you will get like MARKED_COR is TRUE it means the corrupted blocks are fixed.

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;

OBJECT_NAME BLOCK_ID MARKED_COR
----------------------- ----------------- ----------------------
GA07 38 TRUE

05 . Skip the corrupted Blocks : -

You can skip the corrupted blocks using below procedure if these blocks are not needed, So corrupted block data will be not fetched . This procedure will allow future DML statements by skipping the corrupted blocks.

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'GANESH',
OBJECT_NAME => 'GA07',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/

For more details Please Go to MOS ORA-8103 "Object No Longer Exists" Primary Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)

------------------------------------------------ Thanks for Reading --------------------------------------------------

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.

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