Oracle database error ORA-600: [4194] processing after recovery

Error ORA-600: [4194] Processing after Oracle database recovery

Fault phenomenon
Solution
- Rebuild UNDO tablespace
- ORA-600 [4137] error
- Possible cleanup work

Fault phenomenon

Phenomena: After completing the recovery of the NBU tape library, the test library will hang up within a few minutes after it is opened.

The alarm log error is as follows:

Errors in file /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/ORCL_smon_201857.trc (incident=592157):
ORA-00600: internal error code, arguments: [4194], [546.27.149175], [0], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/diag/rdbms/ORCL_0/ORCL/incident/incdir_592157/ORCL_smon_201857_i592157.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Oct 30 09:17:09 2023
PMON (ospid: 201781): terminating the instance due to error 474
System state dump requested by (instance=1, osid=201781 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/ORCL_diag_201796_20231030091710.trc
Errors in file /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/ORCL_ora_203196.trc:
ORA-00474: SMON process terminated with error
ORA-00600: Internal error code, parameters: [4194], [u do not have the SHARED lock on this object.], [], [], [], [], [], [], [], [ ], [], []
ORA-00600: Internal error code, parameters: [4194], [ unlock objec], [], [], [], [], [], [], [], [], [], []
Errors in file /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/ORCL_ora_203196.trc:
ORA-00474: SMON process terminated with error

Query ORA-00600: internal error code, argument: [4194] in MOS and get the following explanation (Doc ID 39283.1):

A mismatch has been detected between Redo records and rollback (Undo) records.
...
This error may indicate a rollback segment corruption.
...
This may require a recovery from a database backup depending on the situation.

For specific solutions, please refer to Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1).

Here’s how I went about it.

Processing measures

Rebuild UNDO tablespace

Check the latest SCN recorded in the control file and data file headers:

idle> startup mount;

SQL> col checkpoint_change# for 999999999999999
SQL> select distinct checkpoint_change# from v$datafile; --SCN of the last checkpoint recorded in the control file

CHECKPOINT_CHANGE#
------------------
     1053731346332

SQL> select distinct checkpoint_change# from v$datafile_header; --SCN recorded in the data file header

CHECKPOINT_CHANGE#
------------------
     1053731346332

It is found that the SCN recorded in the control file and data file headers are consistent. Consider rebuilding the UNDO table space.

Generate an initialization parameter file:

SQL> create pfile='initORCL_new.ora' from spfile;

File created.

Modify pfile, modify UNDO management to manual mode, store it in the SYSTEM table space, and set the 10513 event to disable transaction recovery:

[oracle@dbhost dbs]$ cat initORCL_new.ora | grep undo
*._optimizer_undo_cost_change='11.2.0.4'
*._undo_autotune=FALSE
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

[oracle@dbhost dbs]$ vi initORCL_new.ora
[oracle@dbhost dbs]$ cat initORCL_new.ora | grep undo
*._optimizer_undo_cost_change='11.2.0.4'
*._undo_autotune=FALSE
*.undo_management='MANUAL'
*.undo_retention=10800
*.undo_tablespace='SYSTEM'
*.event='10513 trace name context forever, level 2'

Note: I did not set the 10513 event during my actual operation, which may cause an ORA-600 [4137] error, which will be mentioned later.

Start the database using pfile:

shutdown immediate;
startup pfile='initORCL_new.ora';

There must be no errors, otherwise the errors must be handled separately.

Create a new UNDO tablespace:

SQL> show parameter undo

NAME TYPE VALUE
---------------------------------------------------- -------------------------------------
_optimizer_undo_cost_change string 11.2.0.4
_undo_autotune boolean FALSE
undo_management string MANUAL
undo_retention integer 10800
undo_tablespace string SYSTEM

SQL> CREATE UNDO TABLESPACE UNDOTBS2;

Tablespace created.

SQL> alter tablespace undotbs2 add datafile;
alter tablespace undotbs2 add datafile;
alter tablespace undotbs2 add datafile;
alter tablespace undotbs2 add datafile;
alter tablespace undotbs2 add datafile;
alter tablespace undotbs2 add datafile;
alter tablespace undotbs2 add datafile;

Tablespace altered.

SQL> select file_name,sum(bytes)/1024/1204/1204 from dba_data_files where tablespace_name like 'UNDOTBS%' group by file_name;

FILE_NAME SUM(BYTES)/1024/1204/1204
-------------------------------------------------- -------------------------------------------------- --------------------------
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmskcjmq_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmqyxqqg_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs2_lmy7b0py_.dbf .070639397
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmqyo3pk_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lms93876_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs2_lmy7br2f_.dbf .070639397
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmrb0qkx_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmqytnn7_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmsb8plf_.dbf 46.2942131
/oradata/ORCL_0/datafile/o1_mf_undotbs1_lmsl7d0o_.dbf 46.2942131
...

Modify pfile again, set the UNDO management mode to automatic, and set the UNDO table space to the newly created UNDOTBS2:

[oracle@dbhost dbs]$ vi initORCL_new.ora
[oracle@dbhost dbs]$ cat initORCL_new.ora | grep undo
*._optimizer_undo_cost_change='11.2.0.4'
*._undo_autotune=FALSE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS2'

Restart the database:

SQL> shutdown immediate;
SQL> create spfile from pfile='initORCL_new.ora';
SQL> startup;

SQL> show parameter undo

NAME TYPE VALUE
---------------------------------------------------- ----------------------------------------
_optimizer_undo_cost_change string 11.2.0.4
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS2

Check the ALERT log and find a new error ORA-600 [4137] (this may be an error that occurs when the 10513 event is not set):

[oracle@dbhost ~]$ tail -n300 /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/alert_ORCL.log
...
Sweep [inc2][624461]: completed
Sweep [inc2][624460]: completed
ORACLE Instance ORCL (pid = 36) - Error 600 encountered while recovering transaction (546, 27).
Errors in file /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/ORCL_smon_224148.trc (incident=640172):
ORA-00600: internal error code, arguments: [4137], [546.27.149175], [0], [0], [], [], [], [], [], [], [], [ ]
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance ORCL (pid = 36) - Error 600 encountered while recovering transaction (546, 27).
Mon Oct 30 11:14:27 2023
Sweep [inc][640172]: completed
Sweep [inc][624467]: completed

ORA-600 [4137] error

Querying MOS shows that the explanation of the error ORA-600 [4137] is as follows:

There is a mismatch between the XID in the undo segment header and the XID in the undo block
during rollback or transaction recovery.

This would indicate a corrupted rollback segment

Try removing old UNDO:

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU314_3300756365$' found, terminate dropping tablespace

SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME STATUS SEGMENT_NAME
-------------------------------------------------- ----------------------------------------------- -----------------------
SYSTEM ONLINE SYSTEM
UNDOTBS1 PARTLY AVAILABLE _SYSSMU546_811175239$
UNDOTBS1 PARTLY AVAILABLE _SYSSMU360_2198386275$
UNDOTBS1 PARTLY AVAILABLE _SYSSMU347_654930751$
UNDOTBS1 PARTLY AVAILABLE _SYSSMU314_3300756365$
UNDOTBS2 ONLINE _SYSSMU1274_2513395007$
UNDOTBS2 ONLINE _SYSSMU1273_1341585299$
UNDOTBS2 ONLINE _SYSSMU1272_34058637$
UNDOTBS2 ONLINE _SYSSMU1271_4040385653$
UNDOTBS2 ONLINE _SYSSMU1270_1270536444$
UNDOTBS2 ONLINE _SYSSMU1269_402143936$
UNDOTBS2 ONLINE _SYSSMU1268_4100704859$
UNDOTBS2 ONLINE _SYSSMU1267_2250107085$
UNDOTBS2 ONLINE _SYSSMU1266_94778785$
UNDOTBS2 ONLINE _SYSSMU1265_4196515074$

15 rows selected.

The reason why it cannot be deleted is that UNDOTBS1 still has segments that are not offline and the status is PARTLY AVAILABLE.

After a while, the database went down again. After checking, it was found that a large number of trace files were generated and filled up the Oracle directory. It may be because the 10513 event is not set, and a large number of transaction recovery failed logs are constantly flushed to the trace file.

[oracle@dbhost trace]$ du -sh $ORACLE_BASE/diag/rdbms/ORCL_0/${ORACLE_SID}/
5.2G /oracle/app/diag/rdbms/ORCL_0/ORCL/
[oracle@dbhost trace]$ du -sh $ORACLE_BASE/diag/rdbms/ORCL_0/${ORACLE_SID}/trace/
31G /oracle/app/diag/rdbms/ORCL_0/ORCL/trace/
[oracle@dbhost trace]$ df -h | grep oracle
/dev/mapper/VolGroup-lv_oracle 50G 50G 848M 99% /oracle

Ignore the rollback segments in UNDOTBS1 that are not offline through implicit parameters:

SQL> select tablespace_name, status, segment_name from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status != 'OFFLINE';

TABLESPACE_NAME STATUS SEGMENT_NAME
-------------------------------------------------- ----------------------------------------------- -----------------------
UNDOTBS1 PARTLY AVAILABLE _SYSSMU546_811175239$
UNDOTBS1 PARTLY AVAILABLE _SYSSMU360_2198386275$
UNDOTBS1 PARTLY AVAILABLE _SYSSMU347_654930751$
UNDOTBS1 PARTLY AVAILABLE _SYSSMU314_3300756365$

Modify initORCL_new.ora to add implicit parameters:

*._corrupted_rollback_segments='_SYSSMU546_811175239$','_SYSSMU360_2198386275$','_SYSSMU347_654930751$','_SYSSMU314_3300756365$'

Start the database:

SQL> create spfile from pfile='initORCL_new.ora';
SQL> startup;

--Confirm whether it has been ignored
SQL> select segment_name,tablespace_name,status from dba_rollback_segs where tablespace_name='UNDOTBS1' and status != 'OFFLINE';

SEGMENT_NAME TABLESPACE_NAME STATUS
---------------------------------- ------------------ ---------------------------------------- ---------- -----------------------
_SYSSMU314_3300756365$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU347_654930751$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU360_2198386275$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU546_811175239$ UNDOTBS1 NEEDS RECOVERY

It is best that the above SQL has no output, but the actual test found that the UNDO segment status changes to NEEDS RECOVERY and the UNDOTBS1 table space can also be deleted.

Delete the old UNDO tablespace:

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> select segment_name,tablespace_name,status from dba_rollback_segs where tablespace_name='UNDOTBS1' and status != 'OFFLINE';

no row selected.

Check the ALERT log for errors.

Possible finishing work

Stop the database to remove the 10513 event and the _corrupted_rollback_segments implicit parameters:

SQL> shutdown immediate;
SQL> create pfile from spfile;

Remove the following parameters from pfile:

##*.event='10513 trace name context forever, level 2'
##*._corrupted_rollback_segments"='_SYSSMU546_811175239$','_SYSSMU360_2198386275$','_SYSSMU347_654930751$','_SYSSMU314_3300756365$'

Rebuild the spfile and pull up the database:

SQL> create spfile from pfile='initORCL.ora';
SQL> startup;

Check the ALERT log for errors. Errors you may encounter when the TEMP table space is empty:

**************************************************** **********************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored. It may be necessary to add files to these
         tablespaces. That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*************************************************** *********************

Just add temporary files to the temporary table space:

SQL> alter tablespace temp add tempfile;

REFs
【1】https://www.modb.pro/db/48609
【2】https://blog.csdn.net/sinat_36757755/article/details/130333335
【3】https://www.modb.pro/db/45428
【4】Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)