Proper SQL comes to MongoDB applications .. with the Oracle Database!

As some of you might know, an exciting part of my job is working with the Oracle Database API for MongoDB (short MongoDB API), a part of Oracle's converged database that brings MongoDB's document store API to the world's best database (personal opinion, no need to rebuff here but to discuss offline). No, it's not about Oracle wanting to be a MongoDB - they're probably better at chasing their niche - but to offer their simple and widely used document store APIs and framework integration just like Mongo does, together with all of Oracle's powerful support of all workloads and datatypes. And the integration continues.

MongoDB added a new operator $sql to their aggregation pipeline framework not too long ago (as of end of February 2024 it's currently still in beta), so we at Oracle figured, hey, we have SQL, too ;-). But unlike them, we've been doing SQL for quite some time, so why not support that operator and offer our customers the world of Oracle's powerful SQL within the realms of the MongoDB API? That's precisely what we did.

Use Oracle SQL with the Oracle MongoDB API and instantaneously benefit from Oracle's converged database.

What can I use it for?

• Have data in classical relational tables that you want to share as a collection in your MongoDB app? We got you covered.
• Have data in classical relational tables that you want to combine and process together with collections in your MongoDB application? We got you covered.
• Want to leverage some advanced SQL functionality that is hard or impossible to do in MongoDB? We got you covered.
• Have some procedural logic you want to integrate in an aggregation pipeline? We got you covered.
• Need a pragmatic and straightforward way to deal with the ever-decreasing little things we have not gotten to implement without leaving the Mongo ecosystem? We got you covered with that, too.

Yes, you use Oracle SQL without leaving the world of MongoDB API and integrate and work jointly with relational data and MongoDB collections side-by-side.

A Quick Walk-Through

Let me give you some simple examples to give you a glimpse of what's doable here. The following are simple mongosh examples for illustration, but needless to say that any integration - like using bind variables - can be fully embedded in your application.

Expose relational data

Suppose you just simply want to expose data coming from your relational core system as a read only collection without persisting and periodically updating the data as Mongo collection. No need to copy or transfer data: you just select the information dynamically with SQL from the pure relational structures and put it in your application.

db.aggregate([{$sql: `

             select fiscal_year, fiscal_quarter_number, sum(amount_sold) as sum_amount

             from sh.times t join sh.sales s on (t.time_id = s.time_id)

             group by fiscal_year, fiscal_quarter_number order by 1,2`}

])

In real world this is often embedded in the context of an application and previous filters and values, so let's use bind variables to limit the result set in your application. Using binds just like in JDBC helps to improve performance and prevents any sort of SQL injection:

db.aggregate([ {$sql: 

                  { statement: ` 

                      select 

                         fiscal_year, fiscal_quarter_number, sum(amount_sold) as sum_amount 

                      from sh.times t join sh.sales s on (t.time_id = s.time_id) 

                      where fiscal_year = :1 

                      group by fiscal_year, fiscal_quarter_number 

                      order by 1,2`, 

                    binds: [

                        { index: 1, value: 2019}

                        ]

                    }}

])

That just works fine in any Oracle Database 19c and above, on-premises and with Autonomous Database.

Simple lookup with relational data

Let's make things a bit more interesting: you're running your Mongo application for a specific business unit that now wants to augment their collections with data from common corporate entitities, stored centrally in your enterprise database. You can do so with a simple "$sql lookup" - join in the relational lingo - and add as many additional common attributes as you like. Within an aggregation pipeline, you are using $sql just like any other stage that consumes the input documents from the previous stage and produces documents for subsequent stages.

Since I am an Oracle person for a long time and there are many of us out there, I assume - in fact, hope - that some of you are reading this blog. I also assume that some of you have heard about EMP and DEPT, one of the oldest relational examples out there. I figured I am just using this to illustrate the functionality I am talking about. Yes, you can use this schema everywhere.

Let's first JSON-ize our EMP table and consider this our document collection (we name it empJSON), with a referencing model linking to our purely relational table DEPT. We want to expose our employee information (excluding salary) in a simple web application. Let's use mongosh for that:

jason> db.aggregate([{$sql:`

                         select json{empno, ename, job, mgr, hiredate, deptno} from emp`

                      },

...                   {$out: "empJSON"}])

jason> db.empJSON.findOne()

{

  _id: ObjectId('65ea34720af5351d8f7bf901'),

  empno: 7839,

  ename: 'KING',

  job: 'PRESIDENT',

  mgr: null,

  hiredate: ISODate('1981-11-17T00:00:00.000Z'),

  deptno: 10

}

That wasn't too hard, was it?

However, we want to not show the department number, but the name of the department in our app. And for some reason, we don't want to persist the department name in our stored collection. So let's just look it up in realtime using a $sql stage whenever we need the result, joining our collection with our purely relational table.

jason> db.empJSON.aggregate([{$sql: `

                                select json_transform(e.data, set '$.dname' = d.dname, remove '$.deptno') 

                                from input e, dept d 

                                where e.data.deptno.number() = d.deptno`

                               },

...                            {$limit: 1}])

[

  {

    _id: ObjectId('65ea34720af5351d8f7bf901'),

    empno: 7839,

    ename: 'KING',

    job: 'PRESIDENT',

    mgr: null,

    hiredate: ISODate('1981-11-17T00:00:00.000Z'),

    dname: 'ACCOUNTING'

  }

]

What you see in this little example is how we integrated the $sql stage transparently into Mongo's aggregation pipeline framework: collections produced by previous stages are represented as a JSON collection table INPUT with a single column DATA, containing your documents. We simply joined our collection empJSON with the relational table DEPT, added the field 'dname' and removed the unnecessary field 'deptno'. Mission accomplished, EMP and DEPT are now officially a part of MongoDB demos.

Using the aggregation pipeline in such a manner requires Oracle Database 23c.

Leverage analytics and encapsulated business logic

So you are as savvy in SQL as you are in MongoDB lingo? Choose what you do best and fastest. The following is a rather simple example that aggregates and ranks your yearly gross revenue with your movies using SQL, sorts the data and gives us the key attributes for the top ten.

db.movies.aggregate([

          {$match: {year:2019, gross : {$ne: null}}},

          {$sql:`

              select json_mergepatch(i.data, json {'rank': rank() over (order by i.data."gross" desc)})

              from input i`},

          {$project: { rank: 1, year: 1, title: 1, gross: 1, "_id": 0 }},

          {$match: {rank : {$le : 10}}},

          {$sort: {rank: 1}}

])

In this simple example, we are doing the ranking in SQL, but the sorting and limiting to the top ten in the aggregation pipeline, just to show the interchangeability. We could have done everything in SQL (or the Mongo aggregation pipeline for that matter), but decided to only do the ranking (and implicitly required sorting) in Oracle, letting Oracle's enterprise performance features loose to munge through the data. 

But wait. 

Our finance department had worked hard on our magical global financial gross adjustment that is applied everywhere and encapsulated in SQL. What now? Well, just add a pipeline stage and apply the magic to your Mongo collection, calculate the right number, and add the adjusted gross revenue to your collection:

db.movies.aggregate([{$sql: `

                      select json_mergepatch(i.data,

                             json{'adjGross':adjust_gross(i.data.gross.number())})

                      from input i, dual`}

])

Bridge the gaps

Last but not least, you can use SQL for everything that Oracle MongoDB API does not support. As briefly mentioned before, our vision and aim is not to be a Me-Too Mongo. Our vision is the enterprise and the completeness of a converged database, supporting any datatype with any workload. There will most likely always be gaps in functionality as long as MongoDB and Oracle exist.

One of the most prominent gaps as of today is the lack of index creation through the MongoDB API in Oracle Database 19c. Prior to the $sql operator, you had to leave the MongoDB eco system, connect with a SQL tool, and create any index from there. With the introduction of the $sql operator, you still use SQL, but there is no need to leave the MongoDB eco system. Just use the $sql operator in Oracle Database 19c, and bridge this gap pragmatically for now, until Oracle Database 23c is on your radar. Oracle Database 23c supports index creation through the MongoDB API, but prior to that - like in Autonomous Database - our $sql stage comes to the rescue.

db.aggregate([{ $sql: `

                create index i_movies_sku

                on movies(json_value(data, '$.sku.stringOnly()' ERROR ON ERROR))`}

])

You will see the successful index creation right afterwards:

jason> db.movies.getIndexes()

[

  {

    name: 'I_MOVIES_SKU',

    indexNulls: false,

    unique: false,

    v: 2,

    key: { 'sku.stringOnly()': 1 },

    ns: 'jason.movies'

  },

  { v: 2, key: { _id: 1 }, ns: 'jason.movies', name: '_id_' }

]

See the index at work:

jason> db.movies.find({"sku":"NTV55017"}).explain()

{

  queryPlanner: {

    plannerVersion: 1,

    namespace: 'jason.movies',

    indexFilterSet: false,

    parsedQuery: { sku: { '$stringOnly': 'NTV55017' } },

    rewrittenQuery: { sku: { '$stringOnly': 'NTV55017' } },

    winningPlan: {

      stage: 'SELECT STATEMENT',

      inputStage: {

        stage: 'TABLE ACCESS',

        options: 'BY INDEX ROWID BATCHED',

        source: 'MOVIES',

        columns: '"MOVIES"."ID"[RAW,4000], "CREATED_ON"[TIMESTAMP,11], "LAST_MODIFIED"[TIMESTAMP,11], "VERSION"[VARCHAR2,255], "DATA" /*+ LOB_BY_VALUE */ [JSON,8200]',

        inputStage: {

          stage: 'INDEX',

          options: 'RANGE SCAN',

          source: 'I_MOVIES_SKU',

          columns: `"MOVIES".ROWID[ROWID,10], JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.sku.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR)[VARCHAR2,4000]`,

          filterType: 'access',

          filter: `JSON_VALUE("DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.sku.stringOnly()' RETURNING VARCHAR2(4000) ERROR ON ERROR)=:1`,

          path: "$.sku.stringOnly()'"

        }

      }

    },

    rejectPlans: []

  },

  serverInfo: { host: 'localhost', port: 27017, version: '4.2.14' },

  ok: 1

}

That's quite a list of cool things you can do now, isn't it?

Source: oracle.com

Continue reading

PGQL Property Graphs and Virtual Private Database (VPD)

Securing your data and controlling its access is and should always be a significant concern. The Oracle Database has plenty of built-in security features that help reduce the risk of data breaches or provide granular access control. Virtual Private Database (VPD) is one such feature for the latter.

Oracle Graph enables you to focus on exploring and analysing connections in your data using a Property Graph Query Language such as PGQL, graph algorithms, or graph machine learning.

The question is how you can combine these two things: Using your Oracle Database as a Graph Database and still securing access to your data used in graphs.

The example I will discuss in my post uses an Oracle Autonomous Database Serverless 19c. If you don´t have access to the Oracle Cloud Infrastructure (OCI) and Autonomous Database yet, don´t mind. Everything I describe works nicely on the Oracle Database wherever you have it installed and running.

The data

Let us start with providing data, we want to control access to and which we also explore later using Oracle Graph. One representing a typical financial services use case, where we have bank accounts money being transferred between the accounts, is provided if you run Lab 1 of the LiveLabs tutorial "Find Circular Payment Chains with Graph Queries in Autonomous Database". The tutorial uses resources on OCI.

I use the LiveLabs tutorial, since it sets up everything I need by applying a Terraform stack. You will find the following resources ready-to-use:

• An Autonomous Database (ADW or ATP) with a randomized password for the default database user ADMIN (check the end of the "Apply" log displaying the output).
• A database user named GRAPHUSER with a randomized password (check the end of the "Apply" log displaying the output).
• Two database tables, BANK_ACCOUNTS and BANK_TXNS with data imported, primary and foreign key constraints properly in place.

Make yourself familiar with the data set. It is stripped to basic information only to demonstrate the use case. See that table BANK_TXNS has a column AMOUNT.

select amount, count(*)

from bank_txns

group by amount

order by 1 desc;

Secure access to your data

There is a regulatory requirement that amounts higher than 5000 are only revealed to a specific user. We will use VPD to ensure that the requirement is met.

VPD is based on two things, a policy function and a policy. We define both as follows using the ADMIN user:

-- Policy function

CREATE OR REPLACE FUNCTION hide_big_txns (

  p_schema IN VARCHAR2,

  p_object IN VARCHAR2

)

RETURN VARCHAR2 AS

  l_predicate VARCHAR2 (200);

  l_user VARCHAR2(100);

BEGIN

  select user into l_user from dual;

  if l_user != 'GRAPHUSER' then

    l_predicate:='amount <= 5000';

  end if;

  RETURN (l_predicate);

END hide_big_txns;

/

-- Policy

BEGIN

  DBMS_RLS.ADD_POLICY (

    object_schema            => 'graphuser',

    object_name              => 'bank_txns',

    policy_name              => 'hide_big_txns_policy',

    function_schema          => 'admin',

    policy_function          => 'hide_big_txns',

    statement_types          => 'select',

    sec_relevant_cols        => 'amount',

    sec_relevant_cols_opt    => DBMS_RLS.ALL_ROWS

  );

END;

/

You can test, if your security requirement defined by VPD is met. Check it first for user GRAPHUSER, who should still be able to see transactions with amounts larger than 5000.

As GRAPHUSER run:

SELECT *

FROM bank_txns

WHERE amount > 5000

ORDER BY amount DESC

FETCH FIRST 5 ROWS ONLY;

Then as ADMIN user we set up a new user granting SELECT privileges to the tables owned by GRAPHUSER.

CREATE USER testuser IDENTIFIED BY <PWD>;

GRANT RESOURCE, CONNECT, CREATE SESSION, CREATE TABLE TO testuser;

ALTER USER testuser QUOTA UNLIMITED ON data;

GRANT SELECT ON graphuser.bank_txns TO testuser;

GRANT SELECT ON graphuser.bank_accounts TO testuser;

Now log in using TESTUSER and run the query again. No transactions should come back as result.

VPD and Graph combined

The next step is to verify that access to the data is also restricted if we use transaction information in a graph and query that graph using PGQL.

For the next steps we use Graph Studio, one of the tools available for Autonomous Databases. Log in to Graph Studio with the user GRAPHUSER.

Define your graph

Log into Graph Studio using GRAPHUSER. Create the following paragraphs and run them once the environment is attached to the Graph Studio session.

%pgql-rdbms

/* Create the graph. Rows in table BANK_ACCOUNTS become vertices, rows in table BANK_TXNS become edges of a graph named BANK_GRAPH_VPD. */

CREATE PROPERTY GRAPH bank_graph_vpd

    VERTEX TABLES (

        graphuser.bank_accounts

        KEY (acct_id)

        LABEL account

        PROPERTIES ( acct_id, name )

    )

    EDGE TABLES (

        graphuser.bank_txns

        KEY (txn_id)

        SOURCE KEY ( src_acct_id ) REFERENCES bank_accounts

        DESTINATION KEY ( dst_acct_id ) REFERENCES bank_accounts

        LABEL transfers

        PROPERTIES ( txn_id, amount, src_acct_id, dst_acct_id, description )

  ) OPTIONS (PG_PGQL)

Create a graph using PGQL

Load the graph into memory

%python-pgx

# Load the graph into memory

GRAPH_NAME="BANK_GRAPH_VPD"

# try getting the graph from the in-memory graph server

graph = session.get_graph(GRAPH_NAME);

# if it does not exist read it into memory

if (graph == None) :

    session.read_graph_by_name(GRAPH_NAME, "pg_view")

    print("Graph "+ GRAPH_NAME + " successfully loaded")

    graph = session.get_graph(GRAPH_NAME)

else :

    print("Graph '"+ GRAPH_NAME + "' already loaded")

Load the graph in memory using the Python API

Query the graph

%pgql-pgx

/* Show transactions querying the in-memory graph using PGQL */

SELECT *

FROM MATCH (s IS ACCOUNT)-[t IS TRANSFERS]->(d IS ACCOUNT) ON bank_graph_vpd

Query the in-memory representation of the graph using PGQL

%pgql-pgx

/* Show transactions with amount >= 5000 querying the in-memory graph */

SELECT *

FROM MATCH (s IS ACCOUNT)-[t IS TRANSFERS]->(d IS ACCOUNT) ON bank_graph_vpd

WHERE t.amount >= 5000

Query the graph using PGQL: Find transactions with amount >= 5000

You can see that as GRAPHUSER you are allowed to see all transaction amounts including the larger one.

How does it look like running the same PGQL queries as TESTUSER? According the the VPD policy, the amount > 5000 should be returned as NULL. Let us verify it.

Log out from Graph Studio and log in again, this time as TESTUSER. Create a notebook using with the first two paragraphs that GRAPHUSER used to create and load the graph.

%pgql-pgx

/*

 * Show transactions querying the in-memory graph using PGQL.

 * Order the result by the transaction amount, the highest first.

 * According to the VPD policy, no amount should be higher than 5000.

 */

SELECT *

FROM MATCH (s IS ACCOUNT)-[t IS TRANSFERS]->(d IS ACCOUNT) ON bank_graph_vpd

ORDER BY t.amount DESC

FETCH FIRST 10 ROWS ONLY

Query the graph with access restricted by VPD

You probably wonder, why the transactions with amounts > 5000 do not appear at all in the graph. The NULL values for the transaction amount are converted to 0.0 by loading the graph into memory. Hence you can reveal them by running the following PGQL query:

%pgql-pgx

/*

 * Show transactions querying the in-memory graph using PGQL.

 * Order the result by the transaction amount, the highest first.

 * According to the VPD policy, no amount should be higher than 5000.

 */

SELECT s.acct_id as src_acct_id, t.amount, d.acct_id AS dst_acct_id

FROM MATCH (s IS ACCOUNT)-[t IS TRANSFERS]->(d IS ACCOUNT) ON bank_graph_vpd

ORDER BY t.amount ASC

FETCH FIRST 10 ROWS ONLY

Query the graph using PGQL against the in-memory graph with access restricted by VPD

If you would rather see the NULL displayed, you can also execute a PGQL query directly against the database, bypassing the graph loaded into memory. 

%pgql-rdbms

/*

 * Show transactions querying the database directly graph using PGQL.

 * Order the result by the transaction amount, the highest first.

 * According to the VPD policy, no amounts higher than 5000 should be displayed .

 */

SELECT s.acct_id as src_acct_id, t.amount, d.acct_id AS dst_acct_id

FROM MATCH (s IS ACCOUNT)-[t IS TRANSFERS]->(d IS ACCOUNT) ON bank_graph_vpd

ORDER BY t.amount DESC

FETCH FIRST 10 ROWS ONLY

Query the graph using PGQL against the database graph with access restricted by VPD

How can you distinguish when the PGQL is executed against the in-memory graph and when against the database directly? Look at the interpreter specified in the first line of each paragraph.

• %pgql-pgx executes a PGQL query against the in-memory graph
• %pgql-rdbms executes a PGQL query directly against the database

Quod erat demonstrandum

Virtual Private Database policies restrict access to your data. They are also effective when accessing graphs built from the data. Setting up policies once and using them everywhere, including with graphs, is an excellent way to ensure that everybody can see and use what they intend to see and use, regardless of the way, they access the data.

Source: oracle.com

Continue reading

Introducing Zero to low-cost Autonomous Database for Developers

Oracle has recently been recognized as a leading cloud service provider (CSP), providing a full suite of cloud computing solutions including Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and industry-specific application solutions via Software as a Service (SaaS).

To accomplish this, we created a next-generation cloud experience that focuses on enterprise performance, availability, security and cloud economics where you pay for what you use. The economic benefits of Oracle Cloud Infrastructure (OCI) are substantial, enabling workloads such as AI and Oracle Database to achieve outstanding price-performance.

We are now further improving OCI economics for our Oracle Database cloud service portfolio with the introduction of Oracle Autonomous Database for Developers, which provides Autonomous databases for developers on Dedicated Exadata Infrastructure and Exadata Cloud@Customer at no additional cost. 

Oracle Autonomous Database is an ideal database for developers. It provides multi-model database capabilities for many types of data (including relational, JSON, spatial, graph, multimedia, XML, files, and more), many workloads (transactional, data warehouse, and analytics), and typical developer interfaces (full SQL, REST data access, and language drivers). It comes with free development tools such as Database Actions, Oracle Application Express (APEX) for low-code app creation, and Oracle REST Data Services. It also includes in-database machine learning algorithms and Select AI which enables users to query data using generative AI powered natural language processing.

Autonomous Database for Developers enables developers to experiment with Autonomous Database and build applications with no additional cost. The free developer databases are intended solely for development and functional testing. There is no limit on the number of developer databases users can create on their Exadata and these databases have no expiration dates. Autonomous Database for Developers supports Transaction Processing and Data Warehousing workload types. Each developer database instance has 4 ECPUs, 20 GB of data storage, and supports up to 30 concurrent database sessions.

Except for Autonomous Data Guard, Database In-Memory, Autoscaling, and long-term backups, all other features of Autonomous Database, such as backup and restore, cloning, patching, APEX, Database Actions, ORDS, Performance Hub, Select AI, APIs, metrics, and notifications are included in Autonomous Database for Developers. While developer databases may lack a few production database features, they are otherwise 100% compatible with the Autonomous databases used in production environments, letting developers create and test their applications against identical database environments.

Developer databases are automatically patched following the same schedule as regular Autonomous databases. Developers can file service requests (SR) to Oracle Support to get assistance with their developer databases; however, there is no severity 1 SR support or critical one-off patches. Autonomous Database for Developers adheres to a 99.5% service level objective (SLO).

While Autonomous Database for Developers is for development and functional testing only, users can access the full suite of Autonomous Database features or scale up the database for non-development deployments such as load/stress testing and production by cloning a developer database to a full-featured Autonomous Database instance and running there.

Oracle Autonomous Database offers developers a powerful and user-friendly platform for building and deploying mission-critical applications with high performance, scalability, and built-in security while minimizing administrative overhead and costs. With the introduction of Autonomous Database for Developers and it’s free Autonomous databases for developers, there is now an even more compelling reason to start all new application development with Autonomous Database.

Source: oracle.com

Continue reading

Disaster Recovery for the Oracle Autonomous JSON Database

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.

Source: juliandontcheff.wordpress.com

Continue reading

Maximizing Business Intelligence with Oracle AnalyticsOps

Introduction

In the fast-paced world of modern business, data reigns supreme. Every decision, every strategy, every move is driven by data-driven insights. And in this data-centric landscape, Oracle AnalyticsOps emerges as a beacon of efficiency and effectiveness.

Understanding Oracle AnalyticsOps

Oracle AnalyticsOps represents a paradigm shift in the way organizations handle their analytics operations. It is not just another analytics tool; it is a comprehensive solution that streamlines the entire analytics workflow, from data ingestion to insights delivery.

Data Integration and Preparation

At the core of Oracle AnalyticsOps lies its robust data integration and preparation capabilities. It enables organizations to seamlessly integrate data from disparate sources, whether it's structured data from databases or unstructured data from social media feeds. With powerful data cleansing and transformation features, Oracle AnalyticsOps ensures that the data is clean, accurate, and ready for analysis.

Advanced Analytics

Oracle AnalyticsOps empowers organizations to go beyond basic reporting and dashboards. With advanced analytics capabilities such as predictive analytics, machine learning, and artificial intelligence, organizations can uncover hidden patterns, trends, and correlations in their data. This predictive insight allows businesses to anticipate future trends, mitigate risks, and seize opportunities before they arise.

Collaborative Analytics

Collaboration is key to driving meaningful insights from data. Oracle AnalyticsOps provides a collaborative environment where teams can work together, share insights, and collaborate in real-time. Whether it's data scientists building predictive models or business analysts creating interactive dashboards, Oracle AnalyticsOps fosters collaboration across the entire organization.

Key Features of Oracle AnalyticsOps

Self-Service Analytics

Gone are the days when analytics were confined to data scientists and IT professionals. With Oracle AnalyticsOps, anyone can become an analyst. Its intuitive self-service analytics tools empower business users to explore data, create visualizations, and derive insights without relying on IT support.

Scalability and Performance

Oracle AnalyticsOps is built to scale with your business. Whether you're dealing with terabytes or petabytes of data, Oracle's scalable architecture ensures that you can handle any workload with ease. Plus, its high-performance engine ensures lightning-fast query response times, even on the most complex analytics workloads.

Real-Time Insights

In today's fast-paced business environment, timeliness is crucial. With Oracle AnalyticsOps, you can get real-time insights into your data, allowing you to make informed decisions on the fly. Whether it's monitoring sales performance, tracking customer sentiment, or detecting anomalies in your data, Oracle AnalyticsOps provides real-time insights that drive action.

Benefits of Oracle AnalyticsOps

Improved Decision-Making

By providing timely, accurate, and actionable insights, Oracle AnalyticsOps enables organizations to make better decisions. Whether it's identifying new market opportunities, optimizing operational efficiency, or mitigating risks, Oracle AnalyticsOps empowers decision-makers with the insights they need to drive business success.

Cost Savings

Oracle AnalyticsOps eliminates the need for multiple, disparate analytics tools, saving organizations time and money. By streamlining the analytics workflow and providing a unified platform for data integration, preparation, analysis, and visualization, Oracle AnalyticsOps reduces the total cost of ownership and delivers a strong return on investment.

Competitive Advantage

In today's hyper-competitive business landscape, gaining a competitive advantage is essential for survival. Oracle AnalyticsOps gives organizations the edge they need to outperform the competition. By harnessing the power of advanced analytics, organizations can uncover hidden insights, identify emerging trends, and capitalize on opportunities before their competitors do.

Conclusion

In conclusion, Oracle AnalyticsOps is more than just a tool; it's a game-changer for organizations looking to harness the power of data. With its advanced analytics capabilities, scalable architecture, and real-time insights, Oracle AnalyticsOps empowers organizations to drive innovation, make better decisions, and gain a competitive edge in today's data-driven world.

Continue reading

The Definitive 1Z0-921 Study Guide: Essential Tips and Tricks

The 1Z0-921 certification, also known as the Oracle MySQL 2021 Implementation Essentials certification, marks a pivotal step in advancing your career in MySQL Database Administration. This certification not only validates your expertise in MySQL but also opens up numerous opportunities in the tech industry. Through this article, we aim to provide you with a comprehensive guide filled with essential tips and tricks to help you prepare effectively for this challenging exam.

Understanding the 1Z0-921 Exam

Gain a solid understanding of the 1Z0-921 exam in this section. Learn about the topics covered, exam format, and important dates to keep in mind.

Here are some essential details to help you navigate the 1Z0-921 exam:

1. Topics Covered: The exam covers a wide range of topics,
• Oracle MySQL Enterprise Product suite
• Installation and Architecture
• Database Design
• MySQL Security
• Leverage MySQL Enterprise Monitor
• Leverage MySQL Backup
• MySQL - Overview of High Availability and Replication
• MySQL Database Service and HeatWave
• MySQL Kubernetes
2. Exam Format: The exam consists of multiple-choice questions and scenario-based questions that require critical thinking and problem-solving skills. It is proctored and delivered online through the Oracle Certification Program.
3. Exam Duration: The exam duration is approximately 90 minutes, and you will need to answer a certain number of questions within the given time frame.
4. Passing Score: To pass the exam, you must achieve a minimum score of 60%.
5. Exam Eligibility: There are no specific prerequisites for taking the 1Z0-921 exam. However, it is recommended that candidates have a strong understanding of MySQL concepts and experience working with MySQL databases before attempting the exam.
6. Important Dates: Stay updated with the exam schedule, registration deadlines, and any changes or updates announced by the Oracle Certification Program. Visit the official Oracle website for the most accurate and up-to-date information.

Preparing for the 1Z0-921 exam requires a solid understanding of the exam topics, thorough practice, and effective study strategies. Let's explore the study materials and resources available to help you succeed in your preparation journey in the next section.

Study Materials for 1Z0-921 Preparation

When it comes to preparing for the 1Z0-921 exam, having the right study materials can make all the difference. Luckily, there is a wide range of resources available to help you succeed. Whether you prefer traditional books, online courses, or practice tests, there is something for everyone. Below, we've compiled a list of recommended study materials to assist you in your preparation:

1. Online Resources

Oracle Learning Library: Access a wide range of free online tutorials, videos, and documentation provided by Oracle. These resources cover various topics related to the 1Z0-921 exam and are a great supplement to your study materials.

Oracle Community Forum: Join the Oracle community and engage with fellow exam takers and experts. This forum is an excellent place to ask questions, seek guidance, and gain valuable insights from experienced professionals.

2. 1Z0-921 Practice Tests

Oracle Certification Practice Exam for 1Z0-921: Test your knowledge and readiness with Oracle's official practice exam for the 1Z0-921 certification. This practice test provides an accurate representation of the actual exam and helps you identify areas where you need to focus your studies. 

DBExam Oracle Database Administration I Certification Exam (1Z0-921) Practice Tests: These practice tests offer a simulated exam environment to help you gauge your readiness. These simulated assessments replicate the actual exam environment, helping you become familiar with the format and pacing. Analyze your performance to identify strengths and weaknesses. Use this feedback to refine your study approach and enhance your confidence for the actual exam.

3. Books

There are several books available that cover MySQL in depth. Look for books authored by experts in the field or those specifically tailored to the 1Z0-921 exam objectives.

By utilizing these study materials, you can effectively prepare for the 1Z0-921 exam and increase your chances of success. Remember to create a study plan, allocate time for review, and stay consistent in your preparation. Good luck!

Effective 1Z0-921 Study Strategies

Preparing for the 1Z0-921 exam requires effective study strategies that can maximize your preparation efforts. By implementing the right techniques, you can enhance your understanding of the exam topics and improve your overall performance. In this section, we will explore some proven study strategies that can help you excel in the 1Z0-921 exam.

1. Create a Study Schedule

One of the key aspects of effective studying is to have a well-structured study schedule. By creating a study schedule, you can allocate specific time slots for each exam topic, ensuring comprehensive coverage of the syllabus. This not only helps in managing your time efficiently but also prevents last-minute cramming. A study schedule also allows you to track your progress and identify areas that require additional focus.

2. Utilize Flashcards

Flashcards are a powerful tool for reinforcing your knowledge and retaining important information. Create flashcards with key concepts, definitions, formulas, and other relevant information. Use them to test yourself regularly and reinforce your understanding of the subject matter. Flashcards are particularly effective for memorizing facts and understanding complex concepts by breaking them down into smaller, digestible pieces.

3. Leverage Group Study Sessions

Studying in a group setting can be highly beneficial, as it allows for collaborative learning and fosters the exchange of ideas. Join a study group with peers who are preparing for the 1Z0-921 exam and schedule regular study sessions. In a group, you can discuss challenging topics, clarify doubts, and learn from each other's perspectives. Additionally, teaching concepts to others can help solidify your own understanding and enhance your retention.

4. Practice with 1Z0-921 Sample Questions

Practice is crucial for exam success. Seek out sample questions or previous exam papers and attempt them under simulated exam conditions. This will help you become familiar with the format and style of the questions, as well as identify areas where you need to improve. Analyze your performance, review incorrect answers, and understand the reasoning behind the correct answers. Practicing with sample questions will not only boost your confidence but also train you to manage your time effectively during the actual exam.

By implementing these effective study strategies, you can optimize your preparation for the 1Z0-921 exam and increase your chances of achieving a successful outcome. Remember to personalize these strategies based on your own learning style and preferences. With consistent effort and dedication, you'll be well-prepared to tackle the exam and achieve your desired results.

1Z0-921 Exam Day Tips and Tricks

Preparing for a successful test day is crucial to perform your best on the 1Z0-921 exam. With our invaluable tips and tricks, we've got you covered. Follow these strategies to manage your time effectively and stay calm under pressure.

1. Plan Your Schedule

Before the exam, create a study schedule that allows you to revise all the important topics while leaving ample time for rest and relaxation. Break down your study sessions into manageable chunks to maintain focus and prevent burnout.

2. Familiarize Yourself with the Exam Format

Being familiar with the exam format is essential for efficient time management on test day. Review the structure of the 1Z0-921 exam, the number of questions, and the allocated time for each section. This knowledge will help you strategize your approach and allocate appropriate time to each question or task.

3. Practice Time Management

Time management is crucial during the exam. Take practice tests or simulate exam conditions to get a sense of how long you should spend on each question. Learn to prioritize and move on if you encounter a difficult question. Allocate more time to questions that carry more weight in the scoring.

4. Stay Calm and Focused

During the exam, it's natural to feel nervous or stressed. Practice relaxation techniques, such as deep breathing or visualization, to stay calm and focused. Avoid getting stuck on difficult questions and remember to pace yourself.

5. Read and Understand Instructions Carefully

Before diving into the questions, it's important to carefully read and understand all instructions. Misinterpreting instructions can lead to errors or wasted time. Take a moment to clarify any doubts before proceeding.

Practice Questions and Mock Exams

Practice is the key to success when it comes to preparing for the 1Z0-921 exam. In this section, we will delve into the importance of practicing with sample questions and taking mock exams. By engaging in these activities, you can assess your knowledge and identify areas where you need improvement.

Mock exams simulate the real exam environment, allowing you to familiarize yourself with the format, timing, and difficulty level of the test. They provide a valuable opportunity to gauge your readiness and build your confidence. Additionally, they help you develop effective time management skills, enabling you to complete the exam within the allotted time.

When it comes to practice questions and mock exams, quality is crucial. Ensure that you choose reliable resources that accurately reflect the content and structure of the 1Z0-921 exam. Here are some recommended sources where you can find practice materials:

1. Official Oracle 1Z0-921 Study Guide: This comprehensive guide includes sample questions and practice exams designed by experts.
2. Online forums and communities: Participate in discussions and access shared practice questions from fellow exam takers.
3. Oracle certification websites: Visit the official Oracle certification website for additional sample questions and mock exams.

Take advantage of these resources to enhance your preparation and gain a deeper understanding of the exam topics. Remember, practicing with sample questions and taking mock exams will not only increase your chances of passing the 1Z0-921 exam but also boost your confidence and reduce exam anxiety.

Benefits of Practice Questions and Mock Exams
1. Assess your knowledge and identify areas for improvement.
2. Familiarize yourself with the exam format, timing, and difficulty level.
3. Develop effective time management skills.
4. Build confidence and reduce exam anxiety.
5. Enhance your understanding of exam topics.

Conclusion

In conclusion, armed with the knowledge and strategies provided in this comprehensive 1Z0-921 study guide, you are well-equipped to excel in the exam. The guide has covered all the essential topics, exam formats, and study materials to help you prepare effectively. By following the recommended study strategies, practicing with sample questions, and taking mock exams, you can boost your confidence and be fully prepared for the 1Z0-921 exam.

Remember to create a study schedule that suits your needs and utilize flashcards, group study sessions, and online resources to deepen your understanding of the exam topics. On exam day, manage your time wisely, stay calm, and apply the exam day tips and tricks provided in the guide. With these preparations in place, you can confidently tackle each question and achieve the results you desire.

Good luck on your 1Z0-921 exam journey! By leveraging the study guide's insights and putting your preparation into action, you can pave the way for success and open new doors of opportunities in your career.

Continue reading