Mar 22, 2022
Tagging Trace Files- Files-1
There is a way to “tag” your trace file so that you can find it even if you are not permitted access to V$PROCESS and V$SESSION. Assuming you have access to read the trace directory, you can use the session parameter tracefile_identifier.
With this, you can add a uniquely identifiable string to the trace file name, for example:
SQL> alter session set tracefile_identifier = ‘Look_For_Me’; Session altered.
SQL> !ls /opt/oracle/diag/rdbms/cdb/CDB/trace/Look_For_Me
ls: cannot access /opt/oracle/diag/rdbms/cdb/CDB/trace/Look_For_Me: No such file or directory
SQL> exec dbms_monitor.session_trace_enable PL/
SQL procedure successfully completed.
SQL> !ls /opt/oracle/diag/rdbms/cdb/CDB/trace/Look_For_Me /opt/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_15972_Look_For_Me.trc /opt/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_15972_Look_For_Me.trm
The * character is a wildcard instructing ls to look for any files with the string of Look_For_Me included in the file name. As you can see, the trace file is now named in the standard ora format, but it also has the unique string we specified associated with it, making it easy to find “our” trace file name. The trace file ends with the extension of .trc. There’s also a corresponding trace map file (with the extension of .trm) which contains structural information about the trace file. Usually, you’ll only be interested in the contents of the .trc file.
Trace Files Generated in Response to Internal Errors
I’d like to close this section with a discussion about those other kinds of trace files—the ones we did not expect that were generated as a result of an ORA-00600 or some other internal error. Is there anything we can do with them?
The short answer is that, in general, they are not for you and me. They are useful to Oracle Support. However, they can be helpful when we file a service request with Oracle Support. That point is crucial: if you are getting internal errors, the only way they will ever be corrected is if you file a service request (SR). If you just ignore them, they will not get fixed by themselves, except by accident.
For example, in Oracle 10g Release 1, if you create the following table and run the query, you may well get an internal error (or not; it was filed as a bug and is corrected in later patch releases):
SQL> create table t ( x int primary key ); Table created.
SQL> insert into t values ( 1 );
1 row created.
SQL> exec dbms_stats.gather_table_stats( user, ‘T’ ); PL/SQL procedure successfully completed.
SQL> select count(x) over () from t;
ERROR at line 2:
ORA-00600: internal error code, arguments: [12410], [], [], [], [], [], [], []
Now, suppose you are the DBA and all of a sudden this trace file pops up in the trace area. Or you are the developer and your application raises an ORA-00600 error, and you want to find out what happened. There is a lot of information in that trace file (some 35,000 lines, in fact), but in general it’s not useful to you and me. We would generally just compress the trace file and upload it as part of our service request processing.
A command-line tool, in conjunction with a user interface via Enterprise Manager, allows you to review the trace information in the ADR and package and transmit it to Oracle Support. The Automatic Diagnostic Repository Command Interpreter (ADRCI) utility allows you to review “problems” (critical errors in the database) and incidents (occurrences of those critical errors) and to package them up for transmission to support. The packaging step includes retrieving not only the trace information but also details from the database alert log and other configuration/test case information. For example, I set up a situation in my database that raised a critical error. (No, I won’t say what it is. You have to generate your own critical errors.) I knew I had a “problem” in my database because the ADRCI tool told me so:
$ adrci
adrci> show problem
ADR Home = /opt/oracle/diag/rdbms/cdb/CDB: