Two very different in nature but equality useful features are now available in the Oracle Autonomous Database:
SQL Tracing in Autonomous DatabaseCross-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.
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => ‘JULIANDON_CREDENTIAL’,
username => ‘oracleidentitycloudservice/[email protected]’,
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:
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.
Database altered.
Before we can enable SQL trace, we configure the database to save SQL Trace files:
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 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.
After you close the session, the data is no longer available in 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!
Read MoreJulian Dontcheff’s Database Blog
