SQL Trace and X-ADG in the Oracle Autonomous Database

Two very different in nature but equality useful features are now available in the Oracle Autonomous Database:

1. SQL Tracing in Autonomous Database

2. Cross-Region Autonomous Data Guard in ADB-S

Here is how to enable and use them:

SQL Trace in ADB:

You need first a standard bucket as SQL tracing files are only supported with buckets created in the standard storage tier. Also, create a token (you can have at most 2 tokens) and do not use your OCI password when creating the credentials.

Next, you have to create a credential for your Cloud Object Storage account. Note the full username below – do not simply use the one with what you login to the console.

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

    credential_name => 'JULIANDON_CREDENTIAL',

    username => 'oracleidentitycloudservice/juliandon@yahoo.com', 

    password => 'generated_token'

);

END;

/

PL/SQL procedure successfully completed.

Afterwards, set the init.ora parameters DEFAULT_LOGGING_BUCKET to specify the Cloud Object Storage URL for a bucket for SQL trace files:

SET DEFINE OFF;

ALTER DATABASE PROPERTY SET 

   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/juliandon/b/adbkofa/o/';

 

Database altered.

Next, specify the credentials to access the Cloud Object Storage. Note that although I am doing this as the ADMIN user, I still have to prefix the credential with ADMIN. Otherwise, you get an error message.

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.JULIANDON_CREDENTIAL';

 

Database altered.

Before we can enable SQL trace, we configure the database to save SQL Trace files:

exec DBMS_SESSION.SET_IDENTIFIER('sqltrace_jd');

 

PL/SQL procedure successfully completed.

 

exec DBMS_APPLICATION_INFO.SET_MODULE('module_jmd', null);

 

PL/SQL procedure successfully completed.

 

ALTER SESSION SET SQL_TRACE = TRUE;

After running the SQLs, disable SQL tracing so that the collected data for the session is written to a table in your session and to a trace file in the bucket you configured when you set up SQL trace.

ALTER SESSION SET SQL_TRACE = FALSE;

ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';

The SQL Trace facility writes the trace data collected in the session to Cloud Object Store in the following format:

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

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.

SELECT trace FROM SESSION_CLOUD_TRACE ORDER BY row_number;

After you close the session, the data is no longer available in SESSION_CLOUD_TRACE.

DESC SESSION_CLOUD_TRACE

 

Name       Null? Type

---------- ----- ------------------------------

ROW_NUMBER       NUMBER

TRACE            VARCHAR2(32767)

Cross-Region Autonomous Data Guard in ADB-S

Autonomous Data Guard provides a standby database instance in a different availability domain in the same region or in a standby database instance in different region.

If you create the standby database in the current/local region and if the primary instance becomes unavailable – the Autonomous Database automatically switches the role of the standby database to primary and begins recreating a new standby database.

ADB currently supports up to 2 standby databases – a local one in the same-region and an additional one which is remote – called cross-region.

So, with the new cross-region standby database, you can perform a manual failover to the standby database if the current region goes down.

Note that each region has one or a few nearby paired regions in which a remote standby may be created. As you can see from the screenshot above my tenancy in Frankfurt is subscribed to 3 remote regions in which I can create a remote standby.

It is important to know that ADB-S does not allow us access to the standby databases but after a switchover or failover, the database wallet downloaded in the primary database region can be used in the remote region.

It is extremely simple to manually switchover to the other region – in my case from Frankfurt to Zurich, just with a click of a button:

Source: juliandontcheff.wordpress.com