Oracle DBA by Example. Code samples, Scripts, Reference.: ORA-00600

Showing posts with label ORA-00600. Show all posts

Monday, March 28, 2016

Oracle is stuck during shutdown and ORA-10873 upon startup

=======================
Scenario.
=======================
Following several ORA-600 coredumps, scheduled backup process were in failed/stuck status, which resulted in DB being hand for any DML operation.
The reson for ORA-600 is out of scope for this post.

Simple SHUTDOWN IMMEDIATE command could not be completed, because Oracle could not either commit or rollback transactions.

The solution in this case is to issue SHUTDOWN ABORT command.

Upon STARTUP, there is an ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

This issue is addressed by forcing backup completion by:

ALTER DATABASE END BACKUP;

From trace file igt_ora_10009.trc

=============================================================
Dump continued from file: /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_10009.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4153], [16], [], [], [], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programm
========= Dump for incident 88824 (ORA 603) ========

*** 2016-03-27 01:00:17.944
----- Error Stack Dump -----
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4153], [16], [], [], [], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kcocdm()+37] [SIGSEGV] [ADDR:0x0] [PC:0x7B9B88F] [SI_KERNEL(general_protection)] []
----- SQL Statement (None) -----
Current SQL information unavailable - no SGA.

Dump continued from file: /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_10009.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4153], [16], [], [], [], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programm
========= Dump for incident 88824 (ORA 603) ========

*** 2016-03-27 01:00:17.944
----- Error Stack Dump -----
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4153], [16], [], [], [], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kcocdm()+37] [SIGSEGV] [ADDR:0x0] [PC:0x7B9B88F] [SI_KERNEL(general_protection)] []
----- SQL Statement (None) -----
Current SQL information unavailable - no SGA.

SQL> SHUTDOWN IMMEDIATE;

The database just hangs.

SQL> ALTER SYSTEM CHECKPOINT;

The database just hangs.

SQL> SHUTDOWN ABORT;

ORACLE instance shut down.

SQL> STARTUP RESTRICT;

ORACLE instance started.

Total System Global Area 8551575552 bytes

Fixed Size 2161400 bytes

Variable Size 4093641992 bytes

Database Buffers 4429185024 bytes

Redo Buffers 26587136 bytes

Database mounted.

ORA-10873: file 1 needs to be either taken out of backup mode or media

recovered

ORA-01110: data file 1: '/oracle_db/db1/db_igt/ora_system_01.dbf'

SQL> STARTUP NOMOUNT;

ORACLE instance started.

SQL> ALTER DATABASE OPEN;

ALTER DATABASE OPEN

ERROR at line 1:

ORA-10873: file 1 needs to be either taken out of backup mode or media

recovered

ORA-01110: data file 1: '/oracle_db/db1/db_igt/ora_system_01.dbf'

SQL> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 ACTIVE 4119977880 27-MAR-16

2 ACTIVE 4119977880 27-MAR-16

3 ACTIVE 4119977880 27-MAR-16

4 ACTIVE 4119977880 27-MAR-16

5 ACTIVE 4119977880 27-MAR-16

6 ACTIVE 4119977880 27-MAR-16

7 ACTIVE 4119977880 27-MAR-16

8 ACTIVE 4119977880 27-MAR-16

9 ACTIVE 4119977880 27-MAR-16

10 ACTIVE 4119977880 27-MAR-16

11 ACTIVE 4119977880 27-MAR-16

12 ACTIVE 4119977880 27-MAR-16

13 ACTIVE 4119977880 27-MAR-16

14 ACTIVE 4119977880 27-MAR-16

15 ACTIVE 4119977880 27-MAR-16

16 ACTIVE 4119977880 27-MAR-16

17 ACTIVE 4119977880 27-MAR-16

18 ACTIVE 4119977880 27-MAR-16

19 ACTIVE 4119977880 27-MAR-16

20 ACTIVE 4119977880 27-MAR-16

21 ACTIVE 4119977880 27-MAR-16

21 rows selected.

SQL> ALTER DATABASE END BACKUP;

Database altered.

SQL> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 NOT ACTIVE 4119977880 27-MAR-16

2 NOT ACTIVE 4119977880 27-MAR-16

3 NOT ACTIVE 4119977880 27-MAR-16

4 NOT ACTIVE 4119977880 27-MAR-16

5 NOT ACTIVE 4119977880 27-MAR-16

6 NOT ACTIVE 4119977880 27-MAR-16

7 NOT ACTIVE 4119977880 27-MAR-16

8 NOT ACTIVE 4119977880 27-MAR-16

9 NOT ACTIVE 4119977880 27-MAR-16

10 NOT ACTIVE 4119977880 27-MAR-16

11 NOT ACTIVE 4119977880 27-MAR-16

12 NOT ACTIVE 4119977880 27-MAR-16

13 NOT ACTIVE 4119977880 27-MAR-16

14 NOT ACTIVE 4119977880 27-MAR-16

15 NOT ACTIVE 4119977880 27-MAR-16

16 NOT ACTIVE 4119977880 27-MAR-16

17 NOT ACTIVE 4119977880 27-MAR-16

18 NOT ACTIVE 4119977880 27-MAR-16

19 NOT ACTIVE 4119977880 27-MAR-16

20 NOT ACTIVE 4119977880 27-MAR-16

21 NOT ACTIVE 4119977880 27-MAR-16

21 rows selected.

SQL>

SQL> ALTER DATABASE OPEN;

Database altered.

Sunday, March 20, 2016

ORA-00600: internal error code, arguments: [13013], [5001], [32001] by Example.

====================================================================================
ORA-600 Investigation by Example
====================================================================================

===========================
Issue:
===========================
Disk space is nearly 100%.
After checking, there are many core dumps under /software/oracle/diag/rdbms/orainst/orainst/trace.

===========================
Evidences:
===========================
trace file:
/software/oracle/diag/rdbms/<instance>/orainst/incident/incdir_71006/orainst_j000_21475_i71006.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /software/oracle/111
System name: Linux
Node name: my_host
Release: 2.6.18-274.el5
Version: #1 SMP Fri Jul 8 17:36:59 EDT 2011
Machine: x86_64
Instance name: orainst
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 21475, image: oracle@my_host (J000)

*** 2016-03-15 02:06:53.220

*** SESSION ID:(178.24245) 2016-03-15 02:06:53.220

*** CLIENT ID:() 2016-03-15 02:06:53.220

*** SERVICE NAME:(SYS$USERS) 2016-03-15 02:06:53.220

*** MODULE NAME:() 2016-03-15 02:06:53.220

*** ACTION NAME:() 2016-03-15 02:06:53.220

Dump continued from file: /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_j000_21475.trc

ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []

========= Dump for incident 71006 (ORA 600 [13013]) ========

*** 2016-03-15 02:06:53.221

----- Current SQL Statement for this session (sql_id=911naufz7sfrr) -----

MERGE INTO DIM_CAMPAIGNS TGT USING (SELECT CAMPAIGN_ID, CATEGORY_ID, CAMPAIGN_NAME, STATUS, SUBSCRIBER_REQ_FLAG, WELCOME_BACK_FLAG, DEFAULT_CAMPAIGN_FLAG, ITEM_ID FROM TEMP_PSMS_CAMPAIGNS) SRC ON (SRC.CAMPAIGN_ID = TGT.CAMPAIGN_ID) WHEN MATCHED THEN UPDATE SET TGT.CATEGORY_ID = SRC.CATEGORY_ID, TGT.CAMPAIGN_NAME = SRC.CAMPAIGN_NAME, TGT.ACTIVATION_FLAG = SRC.STATUS, TGT.SUBSCRIBER_REQ_FLAG = SRC.SUBSCRIBER_REQ_FLAG, TGT.WELCOME_BACK_FLAG = SRC.WELCOME_BACK_FLAG, TGT.DEFAULT_CAMPAIGN_FLAG = SRC.DEFAULT_CAMPAIGN_FLAG, TGT.ITEM_ID = SRC.ITEM_ID WHEN NOT MATCHED THEN INSERT (TGT.CAMPAIGN_ID, TGT.CATEGORY_ID, TGT.CAMPAIGN_NAME, TGT.ACTIVATION_FLAG, TGT.SUBSCRIBER_REQ_FLAG, TGT.WELCOME_BACK_FLAG, TGT.DEFAULT_CAMPAIGN_FLAG, TGT.ITEM_ID) VALUES (SRC.CAMPAIGN_ID, SRC.CATEGORY_ID, SRC.CAMPAIGN_NAME, SRC.STATUS, SRC.SUBSCRIBER_REQ_FLAG, SRC.WELCOME_BACK_FLAG, SRC.DEFAULT_CAMPAIGN_FLAG, SRC.ITEM_ID)

There are many incident files, all are with same ORA-00600 Error
oracle@my_host:/software/oracle/diag/rdbms/orainst/orainst/incident>% find . type f -name "*.trc" | xargs grep "ORA-00600"
./incdir_72323/orainst_j000_14429_i72323.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []
./incdir_71006/orainst_j000_21475_i71006.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []
./incdir_72056/orainst_j000_17111_i72056.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []
./incdir_72059/orainst_j000_20690_i72059.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []
./incdir_72321/orainst_j000_11053_i72321.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []
./incdir_66908/orainst_j000_16417_i66908.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []
./incdir_72318/orainst_j000_7252_i72318.trc:ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []

===========================

Oracle Metalink.
===========================

Search In Oracle Metalink is leading to a related document:

New and Improved: ORA-600 [13013] "Unable to get a Stable set of Records" (Doc ID 1438920.1)

ERROR:

Format: ORA-600 [13013] [a] [b] [c] [d] [e] [f]

DESCRIPTION:

During the execution of an UPDATE statement, after several attempts (Arg [a] passcount) we are unable to get a stable set of rows that conform to the WHERE clause.

ARGUMENTS:

6 Argument format

=================

This format relates to Oracle Server 8.0.3 and above

Arg [a] Passcount

Arg [b] Data Object number

Arg [c] Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated

Arg [d] Row Slot number

Arg [e] Decimal RDBA of block being updated (Typically same as [c])

Arg [f] Code

IMPACT:

PROCESS FAILURE

POSSIBLE INDEX CORRUPTION

SUGGESTIONS:

This error could indicate a corrupt index.

Check for corrupted indices using the following command :

ANALYZE TABLE <table_name> VALIDATE STRUCTURE CASCADE;

Where <table_name> is the table being updated.

Drop and recreate any indexes that show problems.

===========================

Resolution
===========================

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = 32001;

OWNER OBJECT_NAME OBJECT_TYPE

------------------------------ ------------------------------ ---------------------------

MY_USER DIM_CAMPAIGNS TABLE

SELECT owner FROM dba_tables WHERE table_name = 'DIM_CAMPAIGNS';

OWNER

------------------------------

MY_USER

A. Check the index structure:

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'DIM_CAMPAIGNS';

INDEX_NAME

------------------------------

CAMPAIGNS_PK

SET LONG 1000

SET LINESIZE 200

SELECT DBMS_METADATA.get_dependent_ddl('INDEX','DIM_CAMPAIGNS') AS ddl_cmd FROM DUAL;

CREATE UNIQUE INDEX "MY_USER"."CAMPAIGNS_PK" ON "MY_USER"."DIM_CAMPAIGNS" ("CAMPAIGN_ID")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "DWH_INDEX";

B. Rebuild the Index

ANALYZE TABLE MY_USER.DIM_CAMPAIGNS VALIDATE STRUCTURE CASCADE;

There is no output

ALTER INDEX CAMPAIGNS_PK REBUILD ONLINE;

Index was rebuild.

C. Rebuild table.
If issue still persist, need to rebuild the table.
To get the full table Create statement:

SET LONG 1000

SET LINESIZE 200

SELECT DBMS_METADATA.get_ddl('TABLE','DIM_CAMPAIGNS') AS ddl_cmd FROM DUAL;

Monday, August 17, 2015

ORA-00600: internal error code, arguments: [17059], [], [],

=========================
General
=========================
Oracle version is 11.1.x
In alert.log there are constantly, every minute these error messages:
ORA-00600: internal error code, arguments: [17059],[],[]

For each error message a trc file is generated, until system is 100% full.

=========================
Solution
=========================
The issue is related to Oracle bug, resolved in 11.2.x.
The problem is with SQL statement(s) having too many child.
The solution is to flush the shared pool:

ALTER SYSTEM FLUSH SHARED_POOL;

After the shared pool was flushed, trc files stopped to be generated.

=========================
Evidences
=========================

From alert.log
Fri Aug 14 00:33:29 2015
Errors in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_16366.trc (incident=289308):
ORA-00600: internal error code, arguments: [17059], [0x247F4DA60], [0x247F4D428], [0x257A51948], [], [], [], [], [], [], [], []
Fri Aug 14 00:33:59 2015
Errors in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_18479.trc (incident=289309):
ORA-00600: internal error code, arguments: [17059], [0x247F4DA60], [0x247F4D428], [0x257A51948], [], [], [], [], [], [], [], []
Fri Aug 14 00:34:00 2015
Sweep Incident[289309]: completed

files under /software/oracle/diag/rdbms/igt/igt/trace/
-rw-r----- 1 oracle dba 170 Aug 17 13:36 orainst_ora_18080.trm
-rw-r----- 1 oracle dba 1280223 Aug 17 13:36 orainst_ora_18080.trc
-rw-r----- 1 oracle dba 148 Aug 17 13:37 orainst_ora_20183.trm
-rw-r----- 1 oracle dba 1280194 Aug 17 13:37 orainst_ora_20183.trc
-rw-r----- 1 oracle dba 160 Aug 17 13:37 orainst_ora_23175.trm
-rw-r----- 1 oracle dba 1280194 Aug 17 13:37 orainst_ora_23175.trc
-rw-r----- 1 oracle dba 148 Aug 17 13:38 orainst_ora_25198.trm
-rw-r----- 1 oracle dba 1280194 Aug 17 13:38 orainst_ora_25198.trc
-rw-r----- 1 oracle dba 149 Aug 17 13:38 orainst_ora_28348.trm
-rw-r----- 1 oracle dba 1280223 Aug 17 13:38 orainst_ora_28348.trc
-rw-r----- 1 oracle dba 148 Aug 17 13:39 orainst_ora_30470.trm
-rw-r----- 1 oracle dba 1280223 Aug 17 13:39 orainst_ora_30470.trc
-rw-r----- 1 oracle dba 148 Aug 17 13:39 orainst_ora_31577.trm
-rw-r----- 1 oracle dba 1280194 Aug 17 13:39 orainst_ora_31577.trc
-rw-r----- 1 oracle dba 147 Aug 17 13:40 orainst_ora_3278.trm
-rw-r----- 1 oracle dba 1280192 Aug 17 13:40 orainst_ora_3278.trc
-rw-r----- 1 oracle dba 147 Aug 17 13:40 orainst_ora_6237.trm
-rw-r----- 1 oracle dba 1280221 Aug 17 13:40 orainst_ora_6237.trc
-rw-r--r-- 1 oracle dba 0 Aug 17 13:41 time_of_shared_pool_flush.txt

After the Shared pool was flushed, no more trc files:
oracle@lx263:/software/oracle/diag/rdbms/orainst/orainst/trace>% date
Mon Aug 17 13:51:09 CEST 2015
oracle@lx263:/software/oracle/diag/rdbms/orainst/orainst/trace>%

=========================
Making this run via crontab
=========================
crontab entry, to run once a month at 08:00
0 8 1 * * /software/oracle/oracle/scripts/flush_shared_pool.sh

flush_shared_pool.sh bash script, logging execution times and calling to sqlplus
oracle@my_server:~>% less /software/oracle/oracle/scripts/flush_shared_pool.sh
#!/bin/bash
. /software/oracle/oracle/.set_profile
export RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`
export LOG_FILE=/software/oracle/oracle/scripts/flush_shared_pool.log

touch $LOG_FILE
echo "============================================" >> $LOG_FILE
echo Starting flush_shared_pool.sh at $RUN_DATE>> $LOG_FILE
sqlplus / as sysdba @/software/oracle/oracle/scripts/flush_shared_pool.sql
echo Finished flush_shared_pool.sh at $RUN_DATE>> $LOG_FILE
echo "============================================" >> $LOG_FILE

flush_shared_pool.sql file, doing actual work
oracle@my_server:~>% less /software/oracle/oracle/scripts/flush_shared_pool.sql
ALTER SYSTEM FLUSH SHARED_POOL;
EXIT;

.set_profile file.

It is needed because crontab is not aware of environment variables.

oracle@my_server:~>% less .set_profile

export ORACLE_HOME=/software/oracle/111

export ORACLE_SID=ora_inst

export PATH=$PATH:/software/oracle/111/bin

=========================
Reference
=========================

In Oracle Metalink there are several technotes related to ORA-00600: internal error code, arguments: [17059]

Doc ID 973149.1: Select Fails With ORA-600 [17059]

Doc ID 1452263.1: ORA-600 [17059]: New and Improved

Pages