sql error 600 on accessing table SNAP_BEG

[Former Member]

Hi all,

I am getting error while accessing some tcodes in SAP with my id.

I have checked the alert log. I can see many ORA errors like follows:

ORA-00600: internal error code, arguments: [17124], [0x000000000], [], [], [],

[], [], [], [], [], [], [].

I have checked for corruption, no corruptions found. I have checked for the status of datafile, all are online.

Kindly suggest on it.

Regards,

Karthik.R

[Former Member · Accepted solution]

go through the note 1868324 - Handling ORA-600/ORA-7445 error and attach the logs as mentioned in the note.

[Former Member · Accepted solution]

Hi,

My oracle version is 11.2.0.3.0 and kindly see the o/p.

SQL> select * from dba_registry_history order by action_time;

select * from dba_registry_history order by action_time

              *

ERROR at line 1:

ORA-00600: internal error code, arguments: [17124], [0x000000000], [], [], [],

[], [], [], [], [], [], []

I am getting above errors in alert log also.

[ACE-SAP · Accepted solution]

Have your tried restarting the DB ?

Could you please provide a pfile file for that instance (init<SID>.ora )

in /oracle/<SID>/112_64/dbs  or  <drive>:\oracle\<SID>\11203\database

No other errors in the alert log ?

Regards

[Former Member · Accepted solution]

Hi ,

PFA for the pfile. In the .trc files i can see the below error.

********** Internal heap ERROR 17124 addr=(nil) *********

********** Internal heap ERROR 17124 addr=(nil) *********

pfile.txt.zip

[ACE-SAP · Accepted solution]

Who did add these (dirty) ones ?

*._allow_resetlogs_corruption=TRUE

*._no_recovery_through_resetlogs=TRUE

Does that system has been refreshed or went through a recovery ?

How much memory is available on that DB server ?

[Former Member · Accepted solution]

The refresh activity was done last July by one of the colleagues i suppose.

The free memory is 1900MB.

[ACE-SAP · Accepted solution]

Well these two parameters might have been set then... the refresh might have been though / rough... but this should not be the cause of you problems

So the system is a pre-prod / sandbox or something like that ?

How much memory is available on the DB server  (ST06 if both SAP & DB are running on the same host) ?

Why is that undocumented one set  ?

No recommendation from SAP about it, it is supposed to be RAC related...

*._gc_read_mostly_locking=FALSE

[Former Member · Accepted solution]

its a test system and refreshed from RAC to non-RAC.

Memory	Physical memory	32.881.112	KB
Memory	Configured swap size	43.831.280	KB
Memory	Free swap size	43.032.480	KB
Memory	Maximum swap size	43.831.280	KB
Memory	Actual swap size	43.831.280	KB
Memory	Free	22	%
Memory	Page In	0	KB/s
Memory	Page Out	0	KB/s
Memory	Page In of RAM	0	%/h
Memory	Page Out of RAM	0	%/h

[ACE-SAP · Accepted solution]

Remove the _gc_read_mostly_locking parameter

Isn't you production system running on RAC ?

What is the total memory on the server ?

Is the DB running on the same host has a SAP system ?

Oracle memory usage could go up to almost 60Gb...

SGA might not set big enough to fit shared pool + DB Cache + fixed...

*.shared_pool_reserved_size= 1 826 066 792

*.shared_pool_size=         18 270 388 224

*.db_cache_size  =          18 270 388 224

*.sga_max_size=             36 624 662 528

*.pga_aggregate_target=     24 347 557 232

                            60 972 219 760

[ACE-SAP · Accepted solution]

Ok, this might not be the cause but you should lower all the memory related parameters by a factor 2 at least.

Remove the undocumented RAC param _gc_read_mostly_locking, and remove log_buffer

Why does disk_asynch_io has been set to FALSE, are you on HP ?

[Former Member · Accepted solution]

Ya the production system is in RAC.  Total memory is 125 GB.

DB and SAP in separate host.

Do u want me to change the SGA and the mentioned gc parameter?

How the DB is ran fine for last 6 to 7 months with all those above params.

Suddenly why its throwing error

[ACE-SAP · Accepted solution]

If the error appears suddenly changing Oracle parameters might not fix your problem...

Even if this might not solve your issue you should

- set Oracle memory parameters accordingly with system available RAM.

- remove RAC related param  gc_read_mostly_locking

- remove reset logs parameters _allow_resetlogs_corruption &  _no_recovery_through_resetlogs

- remove log_buffer param

Was the DB restarted since the 6 months ?

Did you problem still occur if you restart the DB ?

[Former Member]

Karthik R wrote:

I am getting error while accessing some tcodes in SAP with my id.

Can you attach errors that have been received from SAP?

Karthik R wrote:

I have checked the alert log. I can see many ORA errors like follows:
ORA-00600: internal error code, arguments: [17124], [0x000000000], [], [], [],
[], [], [], [], [], [], [].

Can you attach alert log file snippet around this error? It is also highly desirable to attach related trace files.

[ACE-SAP]

Does the error only occur on table SNAP_BEG ?

From where did you get the info that the error comes from reading SNAP_BEG ?

SNAP_BEG is a projection view on SNAP, it does not exist at Oracle level

You must also have SAP dumps, how does the SAP system log looks like ?

[Former Member]

Hi,

I am getting dumps on most of the tables. In corruption table. I can see below details.

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

       105    1041931         62         6.6871E+10 NOLOGGING

       105    1042009         48         6.6871E+10 NOLOGGING

        76    1020422          2         6.6877E+10 NOLOGGING

        76    1020606          2         6.6877E+10 NOLOGGING

        76    1020614          3         6.6877E+10 NOLOGGING

        76    1020630          2         6.6877E+10 NOLOGGING

        76    1020654          2         6.6877E+10 NOLOGGING

        76    1020662          3         6.6877E+10 NOLOGGING

        76    1020670          2         6.6877E+10 NOLOGGING

        76    1020806          3         6.6877E+10 NOLOGGING

        76    1021278          3         6.6877E+10 NOLOGGING

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

        87    1016662          3         6.6886E+10 NOLOGGING

        91    1654061          4         6.6885E+10 NOLOGGING

[ACE-SAP]

Did you have DBV and "Analyze validate" scheduled in DB13 as recommended ?

As V$DATABASE_BLOCK_CORRUPTION is filled up you should be using Rman.

Check the latest log for these actions.

Use the here under query to identify which segments are corrupted

select OWNER, SEGMENT_NAME, SEGMENT_TYPE

from DBA_EXTENTS db, V$DATABASE_BLOCK_CORRUPTION dc

where db.file_id = dc.file# and dc.block# between block_id and (block_id + db.blocks - 1);

[Former Member]

I am not able to run the query. Its throwing the error.

ORA-00600: internal error code, arguments: [17124], [0x000000000], [], [], [],

[], [], [], [], [], [], []

[ACE-SAP]

Well things are in a bad shape.

Update Oracle parameters in Pfile, reboot server if possible, restart the DB only using the pfile (startup pfile='/oracle/<SID>/112_64/dbs/init<SID>.ora')

Then try to run the query for identifying the corrupted segments, if it still fails then I'm afraid you'll have to scrap the DB and perform refresh from production.

[Former Member]

Will the recovery of the corrupted datafiles wont help.

[fidel_vales]

Yves KERVADEC wrote:

Well things are in a bad shape.
Update Oracle parameters in Pfile, reboot server if possible, restart the DB only using the pfile (startup pfile='/oracle/<SID>/112_64/dbs/init<SID>.ora')
Then try to run the query for identifying the corrupted segments, if it still fails then I'm afraid you'll have to scrap the DB and perform refresh from production.

Hi,

Just saw the thread and I'm with Yves.

Also I'm a bit "amazed" at the issue description as there are several contradictions and very bad practices.

So let me try to resume everything to see if I understand

1.- you get an ora600 (internal oracle error) with the argument [17124] accessing some transaccinons

2.- you mentioned that you have checked for corruptions (HOW?) and no corruptions have been found () (but you have V$DATABASE_BLOCK_CORRUPTION full of errors (NOLOGGING which might mean that your DB is in nologing mode or an operation in nologing was done and not "solved" after refresh)

3.- you also get the mentioned error accessing ORACLE DATA DICTIONARY VIEWS! (system is in very bad shape)

4.- you have some not supported parameters (from oracle) that allow to recover through a corruption. When those parameters are used, the database is in a non supported and inconsistant state, Oracle indicate that after using those parameters the datamase MUST BE RECREATED via export and import (and the parameters eliminated)

a) who recommended those parameters?

b) where they used with the help of support (SAP/Oracle) or found on the internet?

5.- You did a refresh (probably then the mentioned parameters were set) from a RAC to non RAC. you eliminated some RAC parameters (like the UNDO_TABLESPACE) but not others (like the memory parameters)

6- not related to this, do you understand your setting of log_archive_dest_1='LOCATION=/oracle/SID/oraarch/SIDarch?)

to analyze an ora600 it would be necessary to check the alert log and the trace file(s) generated (which usually is out of the scope of a forum) but in the current state of the DB (non supported state)

I would start as mentioned by Yves. and then check the alert log properly and the trace files properly (not available here)

As it is an undocumented error, Oracle development support should check it, but they will not do it if a refresh with the mentioned parameters has been done.

[fidel_vales]

Hi,

The recovering over a NOLOGGING operation is what causes that kind of corruption, therefoere recovering will not help. SAP Note 365481 is a nice one to deal with corruptions and 547464

you have to identify the objects and act.
if they are indexes it is easy, you can rebuild them

if they are tables ... you have lost data

[Former Member]

Hi Yves,Fidel,

The issue was with the parameter settings only, the restart of database solved it.

Thanks for all your suggestions.

[ACE-SAP]

Hi

The Oracle ORA-600 Troubleshooter has does not provide any information for that one (17124)

What is you Oracle version ?

It could be memory related, what are your SGA / PGA size, available memory ?

Regards