Friday 29 November 2019

ORA-00600: internal error code, arguments: [4194], [59], [58], [], [], [], [], [], [], [], [], []

Error:

Block recovery completed at rba 431394.4853.16, scn 3257.2985719940
Errors in file /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/bacdb_m000_3280.trc  (incident=1907003) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [4194], [59], [58], [], [], [], [], [], [], [], [], []
Incident details in: /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/incident/incdir_1907003/bacdb_m000_3280_i1907003.trc
Thu Nov 28 13:01:39 2019
Dumping diagnostic data in directory=[cdmp_20191128130139], requested by (instance=1, osid=4294970576 (M000)), summary=[incident=1907003].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Nov 28 13:01:40 2019
Errors in file /d01/app/oracle/diag/rdbms/bacdbpr/bacdb/trace/bacdb_m000_3280.trc:
ORA-00600: internal error code, arguments: [4194], [59], [58], [], [], [], [], [], [], [], [], []
Thu Nov 28 13:02:34 2019
Sweep [inc][1907003]: completed
Sweep [inc2][1907003]: completed

Reason:

This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details:
Undo corruption may be caused after a shrink and the same undo block may be used
for two different transactions causing several internal errors like:
ORA-600 [4193] / ORA-600 [4194] for new transactions
ORA-600 [4137] for a transaction rollback

Solution:

1. Create pfile from spfile to edit
2. Shutdown the instance and change the undo_management parameter to MANUAL

idle@SYS> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
idle@SYS>

alter system set undo_management='MANUAL' scope=spfile;

3. Check the segments

idle@SYS> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME                STATUS           SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM

idle@SYS>


4. Create new undo tablespace - example
SQL>create undo tablespace datafile size 2000M;


CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '+DATA/bacdb/undotbs03.dbf' SIZE 29696M AUTOEXTEND ON NEXT 10M MAXSIZE 29696M,
  '+DATA/bacdb/undotbs04.dbf' SIZE 16316M AUTOEXTEND ON NEXT 10M MAXSIZE 29696M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

5. Drop old undo tablespace
SQL>drop tablespace including contents and datafiles;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

6. SQL>shutdown immediate;

7. SQL>startup nomount;  --> Using your Original spfile

8. Modify the spfile with the new undo tablespace name

SQL> Alter system set undo_tablespace = '' scope=spfile;

Alter system set undo_tablespace = 'UNDOTBS2' scope=spfile;
alter system set undo_management='AUTO' scope=spfile;

9. SQL>shutdown immediate;

10. SQL>startup;  --> Using spfile

BACDB@CDB$ROOT@SYS> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
BACDB@CDB$ROOT@SYS>



No comments:

Post a Comment