Dontcheff

SQL Trace and X-ADG in the Oracle Autonomous Database

In Autonomous, DBA, OCI, Oracle database, Replication on October 6, 2021 at 09:12

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)

Check Connor McDonald’s blog entitled SQL trace on your cloud database.

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.

A detailed blog by Nilay Panchal entitled Cross-Region Autonomous Data Guard – Your complete Autonomous Database disaster recovery solution! covers in detail how to create the remote standby database and how to manually switch over.

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:

Simple and elegant!

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: