Dontcheff

Operating System access from within the Autonomous Database

In Autonomous, DBA, Oracle database, OS on December 1, 2021 at 09:47

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.

  1. I am glad to read this blog it’s so helpful for me to enhance my knowledge. It is so valuable and supportive “You always provide us a piece of genuine and understandable information in your posts/blogs and that’s the reason I really encourage myself to do something more for my company which provides services of database and my audience. I read your blogs for the last many days and you are the main reason to encourage me.
    https://databasegram.com/product/all-india-doctors-database/

  2. “Thank you so much for sharing this genuine information with us” It is so valuable and supportive “You always have good information in your posts/blogs and that’s the reason I have loved to read your blogs for the last many days and you are the main reason to make me productive as a businessman. I hope you will help me to grow as I am a database service provider to help other businesses.
    https://databasegram.com/product/all-india-travel-agents-database/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: