Search

Top 60 Oracle Blogs

Recent comments

SQL Trace in Oracle Database Exadata Express Cloud Service

Today I started having a look to the Oracle Database Exadata Express Cloud Service announced last week at Oracle OpenWorld. Note that since the amount of resources provided (in summary, 1 OCPU, 20 or 50 GB of database storage) is very limited, in general, in my opinion that service will only be useful for functional tests. In fact, if it wasn’t because that is the very first 12.2 release (12.2.0.0.3 according to V$VERSION) available, I doubt I would care about it…

A first important thing to know is that the service gives access to a PDB via SQL*Net only (in addition to the web-based interfaces like APEX). In other words, the OS access is precluded.

Given that no OS access is available, one of the first thing I wanted to test is whether and how SQL trace is supported. In fact, SQL trace remains a critical feature for anyone working with Oracle Database.

The first thing I tried to do is to activate SQL trace for my session through the DBMS_MONITOR package:

SQL> execute dbms_monitor.session_trace_enable
BEGIN dbms_monitor.session_trace_enable; END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_MONITOR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

As you can see, no access to that package is provided. BTW, I’m testing with the PDB_ADMIN user, the highest privileged user (AFAIK) that someone gets when using the service.

Given that the package DBMS_MONITOR isn’t limited to activate SQL trace for the current session, I wasn’t too surprised about it. That said, the next three attemps really surprised me:

SQL> ALTER SESSION SET sql_trace = TRUE;
ERROR:
ORA-01031: insufficient privileges

SQL> execute dbms_session.session_trace_enable
BEGIN dbms_session.session_trace_enable; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 307
ORA-06512: at line 1

SQL> ALTER SESSION SET events 'sql_trace';
ERROR:
ORA-01031: insufficient privileges

So, it looks like no privileges to activate SQL trace are provided. That must be a bug! Hence, as soon as I publish this post, I’ll immediated open an SR. UPDATE Sep 25, 18:45: as pointed out by Laurent Leturgez, this isn’t a bug, it’s a documented limitation.

Anyway, since I consider the previous issue a bug, I continued my research to find out how to get access to the trace files on the server. And, in similar situations (i.e. when I don’t receive the OS access), I usually take advantage of a database directory. Let’s try to create one for the directory where the trace files are stored:

SQL> SELECT value
  2  FROM v$diag_info
  3  WHERE name = 'Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc

SQL> CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/';
CREATE DIRECTORY diag_trace AS '/u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/'
*
ERROR at line 1:
ORA-01031: insufficient privileges

Unfortunately, but understandably, also creating a directory isn’t allowed. Therefore, I told me that another way of doing it should be available. After browsing through the data dictionary I found out that a number of new dynamic performance views contains DIAG and TRACE in their name:

  • V$DIAG_APP_TRACE_FILE
  • V$DIAG_OPT_TRACE_RECORDS
  • V$DIAG_SESS_OPT_TRACE_RECORDS
  • V$DIAG_SESS_SQL_TRACE_RECORDS
  • V$DIAG_SQL_TRACE_RECORDS
  • V$DIAG_TRACE_FILE
  • V$DIAG_TRACE_FILE_CONTENTS

The last two seems to provide exactly what I was looking for. Let’s have a look to them:

SQL> desc v$diag_trace_file
 Name            Null?  Type
 --------------- ------ ----------------------------
 ADR_HOME               VARCHAR2(444)
 TRACE_FILENAME         VARCHAR2(68)
 CHANGE_TIME            TIMESTAMP(3) WITH TIME ZONE
 MODIFY_TIME            TIMESTAMP(3) WITH TIME ZONE
 CON_ID                 NUMBER

SQL> SELECT trace_filename
  2  FROM v$diag_trace_file
  3  WHERE trace_filename LIKE 'cfcdbb1_1_ora_%'
  4  ORDER BY 1;

TRACE_FILENAME
-------------------------
cfcdbb1_1_ora_100689.trc
cfcdbb1_1_ora_103319.trc
cfcdbb1_1_ora_104994.trc
cfcdbb1_1_ora_113270.trc
cfcdbb1_1_ora_19243.trc
cfcdbb1_1_ora_19884.trc
cfcdbb1_1_ora_34663.trc
cfcdbb1_1_ora_34665.trc
cfcdbb1_1_ora_45183.trc
cfcdbb1_1_ora_46745.trc
cfcdbb1_1_ora_4706.trc
cfcdbb1_1_ora_4857.trc
cfcdbb1_1_ora_52669.trc
cfcdbb1_1_ora_54810.trc
cfcdbb1_1_ora_76727.trc
cfcdbb1_1_ora_81232.trc
cfcdbb1_1_ora_81236.trc

SQL> desc v$diag_trace_file_contents
 Name            Null?  Type
 --------------- ------ ----------------------------
 ADR_HOME               VARCHAR2(444)
 TRACE_FILENAME         VARCHAR2(68)
 RECORD_LEVEL           NUMBER
 PARENT_LEVEL           NUMBER
 RECORD_TYPE            NUMBER
 TIMESTAMP              TIMESTAMP(3) WITH TIME ZONE
 PAYLOAD                VARCHAR2(4000)
 SECTION_ID             NUMBER
 SECTION_NAME           VARCHAR2(64)
 COMPONENT_NAME         VARCHAR2(64)
 OPERATION_NAME         VARCHAR2(64)
 FILE_NAME              VARCHAR2(64)
 FUNCTION_NAME          VARCHAR2(64)
 LINE_NUMBER            NUMBER
 THREAD_ID              VARCHAR2(64)
 SESSION_ID             NUMBER
 SERIAL#                NUMBER
 CON_UID                NUMBER
 CONTAINER_NAME         VARCHAR2(30)
 CON_ID                 NUMBER

SQL> SELECT payload
  2  FROM v$diag_trace_file_contents
  3  WHERE trace_filename = 'cfcdbb1_1_ora_19884.trc';

PAYLOAD
--------------------------------------------------------------------------------------
Trace file /u02/app/oracle/diag/rdbms/cfcdbb1/cfcdbb1_1/trace/cfcdbb1_1_ora_19884.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production
Build label:    RDBMS_12.2.0.0.3_LINUX.X64_160720
ORACLE_HOME:    /u01/app/oracle/product/12.2.0.0.3/dbhome_1
System name:    Linux
Node name:      cfcldx0170.usdc2.oraclecloud.com
Release:        2.6.39-400.264.1.el6uek.x86_64
Version:        #1 SMP Wed Aug 26 16:42:25 PDT 2015
Machine:        x86_64
Storage:        Exadata
Instance name: cfcdbb1_1
Redo thread mounted by this instance: 1
Oracle process number: 46
Unix process pid: 19884, image: oracle@cfcldx0170.usdc2.oraclecloud.com


*** 2016-09-25T14:10:17.628206+00:00 (XZECXPHEEJ(10))

*** SESSION ID:(636.380) 2016-09-25T14:10:17.628239+00:00
*** CLIENT ID:() 2016-09-25T14:10:17.628244+00:00
*** SERVICE NAME:(xzecxpheej.usdc2.oraclecloud.com) 2016-09-25T14:10:17.628248+00:00
*** MODULE NAME:(SQL*Plus) 2016-09-25T14:10:17.628253+00:00
*** ACTION NAME:() 2016-09-25T14:10:17.628257+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2016-09-25T14:10:17.628261+00:00
*** CONTAINER ID:(10) 2016-09-25T14:10:17.628265+00:00

Backoff - 3, time increment - 60000

*** 2016-09-25T14:13:49.831644+00:00 (XZECXPHEEJ(10))

Backoff - 3, time increment - 60000

*** 2016-09-25T14:23:34.711288+00:00 (XZECXPHEEJ(10))

As you can see, provided it’ll be possible to create a trace file, the view V$DIAG_TRACE_FILE_CONTENTS provides access to its content.

Note that in my case all other dynamic performance views mentioned above return no rows. So, further investigation is necessary… But, based on their names, I expect that they return part of the information stored in a trace file.