Disaster Recovery for the Oracle Autonomous JSON Database

“You can’t prepare for everything – but you can prepare for anything.” – Paul Youngjohns

Backup-Based Disaster Recovery is a low-cost DR option for databases with higher RTO tolerance.

As Autonomous Data Guard is currently not supported for Autonomous JSON Database workloads, the natural questions is what are the alternatives:

Option 1. The backup-based Disaster Recovery solution is one alternative. Backup-based DR uses database backups to instantiate a peer database at the time of switchover or failover. This enables you to have a lower cost and higher Recovery Time Objective (RTO) disaster recovery option for your Autonomous Database, as compared with Autonomous Data Guard.

Disaster recovery for AJD provides a peer database instance in a different availability domain (or different Exadata if there is only 1 AD in the region) or in a different region around the world. With a peer database, if the primary database becomes unavailable, disaster recovery switches to role of the peer database to primary and begins recreating a new peer database. For backup-based disaster recovery with a cross-region peer, backups are copied to the remote region. 

For local backup-based disaster recovery, existing local backups are utilized. You can edit the automatic backup retention period and the long-term backup schedule. Check that the backup state is “Active” and when last automatic backup went through. Your local peer will be in a different Availability Domain (AD) than the primary database in regions with multiple ADs, or a different Exadata machine in regions with only one AD.

There is no additional cost for local Backup-Based Disaster Recovery! And AJD already takes local backups automatically for you, so there is no additional cost to enable a local backup copy.

Backup-Based Disaster Recovery RTO and RPO numbers are:

Backup-Based Disaster Recovery Configuration	RTO	RPO
Local backup copy	one (1) hour + 1 hour per 5 TB	10 seconds
Cross-region (remote) backup copy	one (1) hour + 1 hour per 5 TB	1 min

When you have a local peer and the switchover is not successful, the Oracle Cloud Infrastructure console shows a banner with information about why the switchover was not successful and the Oracle Cloud Infrastructure console shows a failover link in the Role field that you can click to initiate a failover to the local peer. The failover link only shows when the Primary database is unavailable and a peer is available. That is, the Primary database Lifecycle State field shows Unavailable and the local peer is available.

You may have also one additional backup copy, in another region. Here is one I added to the Swiss region:

I also enabled cross region backup replication from Germany to Switzerland. Check the 2 informational boxes below (cost and replication of backups):

By default, automatic backups are created and maintained at the current Primary database and are not replicated to a cross-region peer. Optionally, you can enable replication of the automatic backups to the cross region peer (as I have done above). A cross-region Backup-Based Disaster Recovery peer can be converted to a snapshot standby. This converts the peer to a read-write database for up to two days.

Note that Backup-Based Disaster Recovery is not available with Always Free Autonomous Database.

Option 2. For having a copy of the Autonomous JSON Database in a different region (and not just in another AD), an option to consider is Refreshable Clones.

When you create a refreshable clone for an Autonomous Database instance the system clones the source database to the refreshable clone. After you create a refreshable clone you can refresh the clone with changes from the source database.

As you can see my refreshable clone is in Switzerland (while the source JSON Database is in Germany):

When you disconnect a refreshable clone the refreshable clone is disassociated from the source database. This converts the database from a refreshable clone to a regular database. Following the disconnect operation you are allowed to reconnect the disconnected database to the source database. The reconnect operation is limited to a 24 hour period.

Refreshable clones are billed based on their base ECPU count and any additional ECPU usage if compute auto scaling is enabled; they do not get billed additionally for the ECPUs of the source database. A refreshable clone in a different region than its source database is billed for twice the amount of storage that the source database is billed for.

You can check the main features of refreshable clones but it is most important to know that refreshable clones have a one week refresh age limit. If you do not perform a refresh within a week, then the refreshable clone is no longer refreshable. After a refreshable clone passes the refresh time limit, you can use the instance as a read only database or you can disconnect from the source to make the database a read/write (standard) database.

Note the important limitations on refreshable clones but these are the main ones:

• Always Free Autonomous Databases do not support refreshable clones
• You cannot create a cascading series of refreshable clones
• You cannot backup or restore a refreshable clone

For the Oracle Autonomous JSON Database, note the following when reconnecting to the source database:

• If, after you disconnect the refreshable clone, you promote both the clone and the source to Oracle Autonomous Transaction Processing (workload type Transaction Processing), you can reconnect the database to the source.
• If after you disconnect the refreshable clone, you promote the source database to Oracle Autonomous Transaction Processing (workload type Transaction Processing) and do not promote the disconnected clone, the disconnected clone must also be promoted to Oracle Autonomous Transaction Processing (workload type Transaction Processing) before you perform the reconnect operation.
• If after you disconnect the refreshable clone, you promote the disconnected database to Oracle Autonomous Transaction Processing (workload type Transaction Processing), you can still reconnect to the source but the reconnected database remains in the promoted state.

Option 3. Oracle GoldenGate is another way to replicate your data do another region. You can add a replicat for Autonomous JSON Database. It is even possible to use Oracle GoldenGate to replicate MongoDB to AJD, good for use case of migrating out of MongoDB to Oracle.

Performance Tuning in the Oracle Autonomous Database: Creating Indexes for JSON Collections

The Oracle Database and in particular the Oracle Autonomous Database offers multiple technologies to accelerate queries and improve database performance over JSON data, including indexes, materialized views, in-memory column storage, and Exadata storage-cell pushdown.

Simple Oracle Document Access (SODA) is a NoSQL query language for storing, editing and deleting JSON documents. A SODA collection is a set of documents (in particular JSON) that is backed by an Oracle Database table or view. By default, creating a SODA document collection creates the following in the Oracle Database:

• Persistent default collection metadata.
• A table for storing the collection, in the database schema to which your SODA client is connected.

If the init.ora parameter compatible is at least 20, then SODA uses JSON data type by default for JSON content, and the version method is UUID. If the init.ora parameter compatible is less than 20, then SODA uses BLOB textual data by default for JSON content and the data is character data. 

Collection metadata is composed of multiple components. The kind of database you use determines the collection metadata that is used by default, and which of its field values you can modify for custom metadata.

Let us consider a collection of movies within the ADMIN schema in a 19c Oracle Autonomous JSON Database (think of one JSON file per movie). After opening JSON from Database Actions, we will create an index on the MOVIES colection:

Creating an index for a JSON collection requires the name and the type. The different options are Functional, Spatial and Search.

Before creating a functional index on the runtime field (we will be running order-by queries against it), let us see what are the field needed (fields to enter):

• For a functional type index, the fields to enter are:
  • Unique: make all indexed values unique
  • Index Nulls: use the index in order-by queries
  • Path Required: the path must select a scalar value, even a JSON null value
  • Properties: select the property that you want to index on, or easier is to just type * to display all available document properties in the collection
  • Composite Index: use more than one property
  • Advanced: change the storage properties of the indexed property
• For search index, the options are:
  • Dataguide off-on: create JSON data guide for collection
  • Text Search off-on: index all properties in documents to support full-text search based on string equality
  • Range Search off-on: choose on to support range search when string-range search or temporal search (equality or range) is required
• Spatial index is used to index GeoJSON geographic data. The selected property should be of GeoJSON type. See Using GeoJSON Geographic Data. For spatial index, the options are:
  • Path Required: the path must select a value, even if it is a JSON null value
  • Lax: the targeted field does not need to be present or does not have a GeoJSON geometry object as its value

The properties of the selected index appear in JSON format below the listed indexes:

We can also index a singleton scalar field using SQL, here is how:

CREATE INDEX YEAR_NDX ON movies (json_value(data, '$.year.number()' ERROR ON ERROR));

Item method numberOnly() is used in the path expression that identifies the field to index, to ensure that the field value is numeric. As I have the year field in one of the JSON files as a string, I am getting the following error if I use the method numberOnly(): ORA-01722: invalid number. Method numberOnly() is used instead of method number(), because number() allows also for conversion of non-numeric fields to numbers. Clearly as some moview are produced during the same year I cannot make the index unique: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found.

Creating a collection relational views of JSON documents is simple, select the columns we need, say title, year and runtime:

In SQL, we can use the view and order the movies by runtime:

Regardless of your database release you can create whatever Oracle Database indexes you need directly, using:

(1) the JSON Page of Using Oracle Database Actions, check Creating Indexes for JSON Collections

(2) Simple Oracle Document Access (SODA)

(3) SQL — see Indexes for JSON Data in Oracle Database JSON Developer’s Guide.

Using the JSON page in ADB is perhaps the easiest approach to indexing JSON data.

The static data dictionary views USER_SODA_COLLECTIONS lists the basic features of all of your SODA collections. Correspondently, you have also DBA_SODA_COLLECTIONS and ALL_SODA_COLLECTIONS. Collection metadata, expressed in JavaScript Object Notation (JSON) can be obtained from the last column, called JSON_DESCRIPTOR.

Users will typically work with JSON collections using native language drivers, for example, SODA for Java or SODA for Python. SODA native language drivers generally provide more throughput (operations per second) than the REST driver (SODA for REST). It is recommended to configure the SODA drivers as follows:

• Enable SODA Metadata Cache: The SODA driver needs to know the metadata of each JSON collection (the column names, types, etc.). By enabling the metadata cache, roundtrips to the database can be saved, improving latency and throughput.
• Enable Statement Cache: Statement caching improves performance by caching executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. For Java, the statement cache is enabled using JDBC.
• For load-balanced systems: turn off DNS caching: Load balancing allows to distribute SODA operations across different nodes. If DNS caching is turned on, then all connections are likely to use the same node and nullifying the load balancing. For Java, the following system property should be set: inet.addr.ttl=0

The database performance tuning techniques also apply to SODA: for example, SODA collections can be partitioned or sharded, and queries can be accelerated using indexes and/or materialized views.

How to monitor all that? You can turn on performance monitoring of the SQL operations that underlie a SODA read or write operation, by adding a SQL hint to the SODA operation.

Use only hint MONITOR (turn on monitoring) or NO_MONITOR (turn off monitoring). You can use this to pass any SQL hints, but MONITOR and NO_MONITOR are the useful ones for SODA, and an inappropriate hint can cause the optimizer to produce a suboptimal query plan.

Note in addition that when using SODA with Autonomous Database the following restrictions apply:

• Automatic indexing is not supported for SQL and PL/SQL code that uses the SQL/JSON function json_exists. See SQL/JSON Condition JSON_EXISTS for more information but note that all the collection APIs use JSON_EXISTS for filtering so … the benefits might be negligible.
• Automatic indexing is not supported for SODA query-by-example (QBE)

Final note: since Auto Indexing is disabled by default in the Autonomous Database, you can enable it by running: EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

The 5 major benefits of ECPUs in the Oracle Autonomous Database

Oracle Autonomous Database (ADB) recently introduced a more advanced ECPU billing metric and is now retiring the legacy OCPU billing metric for Autonomous Data Warehouse and Autonomous Transaction Processing in the next 12 months. Oracle recommend switching from the OCPU billing metric to the ECPU billing metric, which will not incur any downtime or service interruptions.

ECPUs will provide the same great price-performance as OCPUs and continuous improvements in price-performance over time. Updating to the ECPU billing metric provides the following benefits:

• 50% lower entry cost: The smallest Autonomous Database that can be provisioned with ECPUs is 50% less expensive ($0.672 per hour vs $1.3441 per hour with OCPUs)
• Finer granularity for database scaling: Each incremental increase in ECPU database size is only $0.336
• Lower storage costs: Autonomous Data Warehouse storage price reduced from $118.40 to $25.00 per TB per month and Autonomous Transaction Processing storage can be provisioned in increments of 1GB (this is a huge thing!), with a minimum of 20GB – this brings the ADW in-database storage price on par with the object storage and thus this helps to build data lakes solely on the architectural requirements and not focusing on cost
• Up to 87% lower costs with database consolidation: Elastic Resource Pools, available on ECPU ADB Serverless databases, help consolidate deployments leading to major cost savings
• New features for Autonomous Database may only be available with ECPU’s

Note that the prices mentioned above are the current list prices for Autonomous Databases with the License Included license type. Please, refer to the ECPU billing metric FAQ for more details about ECPUs and each of these benefits.

There are also differences in backups between OCPU’s and ECPU’s. ECPU’s backup storage is billed separately, and the backup retention period may be selected between 1 and 60 days. With OCPU’s, 60-days of backup storage is included in the storage price. This new ECPU customer-controllable backup is beneficial because customers can now control the backup storage size and further reduce the cost of dev/test environments. Here is how I reduced the size from 60 to 31 days (later on I did reduce it to 7 days).

I did scale down my database in 2 phases: (1) I switched to the ECPU model (1 OCPU –> 4 ECPUs) and then (2) reduced the ECPU count from 4 to 2 and the storage from 1024GB to 20GB (those two in one go with no downtime).

Here are some general guidelines related to the new ECPU metric:

1. Provision all new Autonomous Data Warehouse and Autonomous Transaction Processing databases or clones with the ECPU billing metric
2. Update all existing databases to the ECPU billing metric, which is a simple and seamless button click or API call
3. Note that if you choose not to update your existing databases’ billing metric at this time, Oracle may convert your databases from the OCPU billing metric to the ECPU billing metric in the future

Updating your Autonomous Database Serverless to the ECPU billing metric will have no impact to your service and incur no downtime. Oracle Autonomous Database will be retiring the OCPU-based SKUs and replacing them with the ECPU-based SKUs. Starting in August 2023, some new Autonomous Database features may be available only on ECPU’s. For example, Elastic Resource Pools are only available with ECPU’s.

Note that ECPU’s have also already been introduced for MySQL Heatwave on AWS, and other services may also offer ECPU’s in the future.

Here is some additional information regarding the ECPU announcement:

• Autonomous JSON Database adopts ECPUs and per-gigabyte Storage, lowering the entry level price point for developers by Hermann Baer
• Announcing The Retirement Of The OCPU Billing Metric In Autonomous Database Serverless: MOS Note Doc ID 2998742.1
• For more information about the ECPU billing metric and its benefits refer to the ECPU FAQ
• Oracle ECPU Documentation
• Use and Manage Elastic Resource Pools on Autonomous Database
• How to Achieve up to 87% Compute Cost Savings with Elastic Resource Pools on Autonomous Database