Apr 22, 2022
Tagging Trace Files- Files-2

PROBLEM_ID PROBLEM_KEY

LAST_INCIDENT LASTINC_TIME

There was recently an ORA-700 error in my database. I can now see what was affected by that error by issuing the show incident command:

adrci> show incident

INCIDENT_ID PROBLEM_KEY CREATE_TIME
———— ————————— ————————-
1 ORA 700 [pga physmem limit] 021-01-24 19:33:31.863000 +00:00
2402 ORA 700 [pga physmem limit] 021-01-24 19:34:50.006000 +00:00
4803 ORA 700 [pga physmem limit] 021-01-24 19:35:20.619000 +00:00
7204 ORA 700 [pga physmem limit] 021-01-24 19:42:03.463000 +00:00
9605 ORA 700 [pga physmem limit] 021-01-24 19:49:46.391000 +00:00

I can see there are several incidents, and I can identify the information related to each incident via the show tracefile command:

adrci> show tracefile -I 2402
diag/rdbms/cdb/CDB/incident/incdir_2402/CDB_ora_5317_i2402.trc

This shows me the location of the trace file for the incident number listed. Further, I can see a lot of detail about the incident if I so choose:

adrci> show incident -mode detail -p “incident_id=2402”
ADR Home = /opt/oracle/diag/rdbms/cdb/CDB:

INCIDENT INFO RECORD 1

INCIDENT_ID 2402

STATUS ready

PROBLEM_ID 1

CLOSE_TIME

FLOOD_CONTROLLED none

ERROR_FACILITY ORA

ERROR_NUMBER 700

ERROR_ARG1 pga physmem limit

And, finally, I can create a “package” of the incident that is useful for support. The package will contain everything a support analyst needs to begin working on the problem.

This section is not intended to be a full overview or introduction to the ADRCI utility, which is documented fully in the Oracle Database Utilities manual. Rather, I just wanted to introduce the existence of the tool—a tool that makes using trace files easy.

The database information is important to have when you go to http://support. oracle.com to file the service request or to search to see if what you are experiencing is a known problem. In addition, you can see the Oracle instance on which the error occurred. It is quite common to have many instances running concurrently, so isolating the problem to a single instance is useful.

Here’s another section of the trace file to be aware of:

*** SESSION ID:(266.55448) 2021-02-15T15:39:09.939349+00:00
*** CLIENT ID:() 2021-02-15T15:39:09.939354+00:00
*** SERVICE NAME:(SYS$USERS) 2021-02-15T15:39:09.939358+00:00
*** MODULE NAME:(sqlplus@localhost (TNS V1-V3)) 2021-02-­ 15T15:39:09.939364+00:00
*** ACTION NAME:() 2021-02-15T15:39:09.939368+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-02-15T15:39:09.939372+00:00
*** CONTAINER ID:(1) 2021-02-15T15:39:09.939376+00:00

This part of the trace file shows the session information available in the columns ACTION and MODULE from V$SESSION. Here, we can see that it was a SQL*Plus session that caused the error to be raised (you and your developers can and should set the ACTION and MODULE information; some environments such as Oracle Forms and APEX already do this for you).

Additionally, we have the SERVICE NAME. This is the actual service name used to connect to the database—SYS$USERS, in this case—indicating we didn’t connect via a TNS service. If we logged in using user/pass@CDB, we might see

*** SERVICE NAME:(CDB) 2021-02-15T15:55:42.704845+00:00

Here, CDB is the service name (not the TNS connect string; rather, it’s the ultimate service registered in a TNS listener to which it connected). This is also useful in tracking down which process or module is affected by this error.

Lastly, before we get to the actual error, we can see the session ID (266 in this example), session serial number (55448 in this example), and related date/time information (all releases) as further identifying information:

*** SESSION ID:(266.55448) 2021-02-15T15:39:09.939349+00:00

From here, you can dig further into the trace file and try to determine what is causing the problem. The other important pieces of information are the error code (typically 600, 3113, or 7445) and other arguments associated with the error code. Using these, along with some of the stack trace information that shows the set of Oracle internal subroutines that were called in order, we might be able to find an existing bug (and workarounds, patches, and so on).

Typically, you’ll do a Google search of any relevant error messages. If you don’t readily find an answer, you can create a service request with Oracle Support and attach the trace files to the request. Oracle Support can help you identify if you’ve hit a bug or provide options on workarounds.

More Details

Leave a Reply

Your email address will not be published. Required fields are marked *