Oracle Autonomous Database does not let us access the operating system. Not even DBA access is allowed.
So, how can we view the content of ADB log or trace files from the OS? How about the alert.log file? “Not needed as all is autonomous” is the common answer. But that is not good enough for most DBAs.
Since September 2021, SQL Trace is supported in the Autonomous Database. If DBAs enable SQL Trace, the same tracing information saved to the trace file on Cloud Object Store is available in the SESSION_CLOUD_TRACE view in the session where the tracing was enabled.
When you enable SQL Tracing, the same trace information that is saved to the trace file on Cloud Object Store is available in the SESSION_CLOUD_TRACE view in the session where the tracing was enabled.
But how about other trace files? It is still possible via Oracle’s internal views. In fact, we don’t have access directly to the V_$ views but rather to their V$ synonyms.
Full access is available to V$DIAG_OPT_TRACE_RECORDS, V$DIAG_SQL_TRACE_RECORDS, V$DIAG_SESS_SQL_TRACE_RECORDS and V$DIAG_SESS_OPT_TRACE_RECORDS.
The names of the trace files and their content is visible through V$DIAG_TRACE_FILE_CONTENTS:
We can see the contents of a a given trace file, say eqp12pod2_p009_193404.trc. The file has 253 lines, so here is a screenshot from the top of the file content:
We can view the latest trace files generated by the database from V$DIAG_TRACE_FILE:
Here are a couple of blog post and articles related to the topic:
How about the alert.log file? The content is in X$DBGALERTEXT:
select to_char(ORIGINATING_TIMESTAMP,’YYYYMMDD-HH24:MI:SS’), MESSAGE_TEXT
from X$DBGALERTEXT
order by RECORD_ID;
However, in ADB, we do not have access to X$DBGALERTEXT. Good news is that we have access to V$DIAG_ALERT_EXT which shows the contents of the XML-based alert log in the Automatic Diagnostic Repository (ADR) for the current container (PDB). V$DIAG_ALERT_EXT which came in 12.2 is sort of a subset of X$DBGALERTEXT. Here is how you can search for ORA- errors. Use:
SELECT ORIGINATING_TIMESTAMP, MESSAGE_LEVEL, MESSAGE_TEXT, PROBLEM_KEY
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE ‘%ORA-%’ AND ORIGINATING_TIMESTAMP > sysdate-7
ORDER BY ORIGINATING_TIMESTAMP DESC;
to find the ORA- errors during the past week.
Possible level message values are:
1: CRITICAL: critical errors
2: SEVERE: severe errors
8: IMPORTANT: important message
16: NORMAL: normal message
And here is how to see the top of the alert.log file content from the last 24 hours:
SELECT to_char(ORIGINATING_TIMESTAMP,’DD.MM.YYYY-HH24:MI:SS’)||’: ‘||MESSAGE_TEXT as “alert.log”
FROM V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate-1
ORDER BY ORIGINATING_TIMESTAMP DESC;
Regardless of the fact that we do not have server OS access, we can still create directories from within the Autonomous Database.
CREATE DIRECTORY creates the database directory object and also creates the file system directory if it does not already exist. If the file system directory exists then CREATE DIRECTORY only creates the database directory object.
It is possible also to create subdirectories. For example, the following command creates the database directory object version_patches and the file system directory version/patches:
CREATE DIRECTORY version_patches AS ‘version/patches;
You can list the contents of a directory in the Autonomous Database by using the function DBMS_CLOUD.LIST_FILES.
The following query lists the contents of the VERSION directory:
SELECT * FROM DBMS_CLOUD.LIST_FILES(‘VERSION’);
You can copy Files Between Object Store and a Directory in Autonomous Database.
Starting with Oracle Database 21c, Data Pump can perform exports from Oracle Autonomous Database into dump files in a cloud object store.
Thus, now we can easily migrate data out from an Oracle Autonomous Database and import it into another location.
Here is an example assuming that we have already created the credential_name JMD_OBJ_STORE_CRED:
Details on how to create the credentials can be found in the Oracle Cloud Infrastructure User Guide which has “only” 5952 pages!
Beware Bug 33323028 – DATA PUMP EXPORT TO OCI OBJECT STORAGE FAILS – present even in 21.4 – Object store ODM Library is not enabled by default – How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)
Conclusion: as you can see the OS access is close to zero. There are very few views that let us see what is residing on the the operating system. What I like most is (1) the possibility to run SQL Trace, (2) being able to view the contents of the XML-based alert log and (3) the ability to run an export directly from object store (21c and above). I would like to see access to X$DBGALERTEXT.
Read MoreJulian Dontcheff’s Database Blog
