Dontcheff

Archive for the ‘OS’ Category

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.

Oracle and computers with zero CPUs

In Bugs, DBA, Grid Control, Oracle database, OS on January 20, 2011 at 20:50

How do you license a computer with zero CPUs? Of course, there are no such computers but still according to Oracle’s Grid Control this is not always the case 🙂 Have a look:

These hardware inventions should not bring us any cost, shouldn’t they 🙂

It is worth asking the question are there any computers with zero IO devices and NULL CPU boards:

Looks like also that the number of CPU boards cannot be visible for some computers, not to mention that although the documentation claims that “MGMT$CSA_COLLECTIONS displays top-level information about all client configurations”, I can still see nothing from this view:

You notice that this is in Grid Control 11g? I remember similar cases in 10g as well, here is an old snapshot of mine:

When will computers run without CPUs? But it is like a person without a brain says a colleague of mine.

And finally, is Oracle wrong or did HP forget to put a ventilator in the computer 🙂