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.

Simple Oracle Document Access (SODA) in the Autonomous JSON Database

In Autonomous, DBA, OCI, Oracle database on November 12, 2021 at 12:09

“I ordered a soda – caffeine-free, low sodium, no artificial flavors. They brought me a glass of water.” – Robert E. Murray

SODA is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the data in the documents is stored in the database.

Oracle database stores, manages, and indexes JSON documents, and developers can access these via document-oriented APIs in a NoSQL style.

A recent white paper written by Vlad Kamys, Francesc Mas and Sai Penumuru explained how to modernize your applications and increase business resilience and security with JSON on Oracle Database, and new cloud-based Oracle Autonomous JSON Database.

Here are few examples on how to use JSON and SODA in the Oracle Autonomous Database.

From the Tools tab, start “Database Actions”, and then select “SQL”:

The following below are typical SODA commands:

(1) list the collections

(2) create the EMP collection

(5), (6) and (7) insert three JSON documents into the collection

(10) gets all documents where the name is “Francesc”

(11) gets all documents where the salary is greater than 300

(12) gets all documents where the jobs starts with “D”

(13) gets all documents where the jobs contains the string “play”

Here is the output from command extracting all documents where the jobs starts with “D”:

If we do not have a text index on “job”, then we get an ORA-40467:

We can also run standard SQL on the EMP table which gets created as a result of creating the collection:

Here is how to get the output from EMP by using JSON_VALUE:

You can think of SODA as a programming bridge between the NoSQL model and the relational model.

There is also the DBMS_SODA package in the database. You can drop the EMP collection simply with “soda drop emp” but you can also run select DBMS_SODA.DROP_COLLECTION(’emp’) from dual; The function will return 1 when it succeeds and 0 when it fails.

Work with JSON Documents in Autonomous Database provides examples of how Java code opens a SODA collection of cart documents, how to use SQL with a SODA collection, etc.

Finally, there is JSON DB/SODA DB Health-Check Script that is a tool developed by Oracle Support Services. The tool, also known as jsonsodadb_hc, is used to check the environment in which a single SQL statement runs, checking for the current status of JSON DB and SODA DB components, makes recommendations based on current settings and checks if the components are being used.

Session settings and timeouts in OCI

In Cloud, DBA, OCI on October 26, 2021 at 14:41

The following question in the Oracle Groundbreakers Developer Community forum made me investigate on how to change the session timeout in the Oracle Cloud console. Have a look, it is still for some reason unanswered:

For those who use Oracle Cloud Infrastructure on daily basis, they know that the default session timeout is 480 minutes although in my case it is an hour – so often after you switch to the OCI tab, you see the following screen:

Here is the way how you can change it to a longer period with 32767 minutes being the maximum allowed.

Step 1: Open the Service User Console

Step 2: Open the Identity Cloud Service Admin Console:

Step 3: From the Dashboard, choose “Settings” and from there “Session Settings”:

Step 4: Set the “Session Duration” to a longer period of time:

Step 5: Saving the settings to the new value: I chose the maximum which is 32767 minutes:

Here are some additional resources:

Administering Oracle Identity Cloud Service

How to Change the Default Session Timeout Value for IDCS (Oracle Identity Cloud Service) in a P6 Cloud Environment (Doc ID 2812372.1)

Note that there are other setting besides session session: user settings, default settings and partner settings.

You can try also the Console Settings (thanks to Simo Vilmunen) but there I am getting a show stopper: