[ORACLE] Oracle JSON-Relational Duality Views: Restrictions to use.

Hello, everyone.

How are you doing?

As I said in the last post, Oracle JSON-Relational Duality Views bring the best of both worlds - JSON and Relational.

If you want to see more information, click here.

But everything in the Database World has restrictions. And Duality Views are not different.

If you want to check out all the restrictions in the manual, click on the image below.

According to the manual, Duality Views have these restrictions:

Column data types:

TIMESTAMP WITH LOCAL TIME ZONE
BINARY_DOUBLE
BFILE
ROWID / UROWID
XMLTYPE
ANYTYPE
ANYDATA
ANYDATASET
HTTPURIType / XDBURIType / DBURIType
SDO_POINT_TYPE
SDO_ELEM_INFO_ARRAY
SDO_ORDINATE_ARRAY
SDO_GEOMETRY
SDO_TOPO_GEOMETRY
Object types (Abstract Data Types)

They cannot be used to create Duality Views:

System-partitioned tables
Sharded tables
Views
Materialized views
Editioning views
External tables
Hybrid partitioned tables
Global temporary tables
Private temporary tables
Remote tables (tables over database links)

Other restrictions:

Duality Views updates using database links are not supported.

MERGE SQL statements are not supported.

It is not possible to create a JSON functional index on a data column.

        It is not possible to create a JSON multivalue index on a data column.

It does not have support for JSON search index on subjacent table column.

Fine-grained auditing policies are not supported.

We still have 15 more restrictions, according to the manual.

That's it! I hope this post helps you!

Regards

Mario

[ORACLE] Oracle JSON-Relational Duality Views: Bringing together the best of the relational and JSON worlds!

Hello everyone!!!

I hope you're doing well!

Last weekend I was proud to organize the 12th GUOB TECH DAY 2023. 

Moreover, I had the honor to share the stage with big names from Brazil and other countries. 

I presented a session talking about JSON-relational Duality Views in Oracle 23c.

If you were not able to attend the event, don't worry. I have posted the slides from my presentation here.

As soon as possible, I'll translate the slides form Portuguese to English, I promise.

Summarize the benefits of JSON-Relational Duality Views hopes to deliver in a few topics:

1. Combines the benefits of the relational model (storage, consistency, efficiency) with the flexibility of JSON documents. 
2. Document-centric. APPs can use Oracle Database API for MongoDB, Oracle REST Data Services (ORDS), JSON document functions or SQL; 
3. Eliminate the need to use ORM (Object Relational Mapping). Native JSON support;
4. Can "replace" groups of tables, allowing easy access and updating of data via APPs;
5. Read and writes can use HTTP operations such as GET, PUT and POST;
6. Apps run optimized in the database. They allow to get and store all the necessary rows of an app-tier object in a single access, unlike ORM codes that need to make several calls to the database;
7. Advanced statistics and analytics. Advanced security features including auditing and fine-grained access control;
8. ACID (Atomicity, Consistency, Isolation, Durability) transactions between multiple documents and tables, without worrying about the data model or transactions.

If you have any question, let me know.

Regards

Mario

[Oracle RAC & VMWare] Creating disks in VMWare for Oracle RAC environments.

Hello all

How are you doing?

Some time ago, I helped my teammates Patricia Flores Solano (LinkedIn) and Thiago Ferreira (LinkedIn) create a document about a POC with VMWare and Oracle RAC that they were doing for a project.

The Oracle RAC installation follows the same steps as a Bare Metal environment, but we documented the process of creating the VMWare disks, which has some tricks and tips that can help to get better performance.

But always remember:

1. This step-by-step worked for us, but it might not work for you.
2. This post is designed to help you. Never change any productive or test environment without first testing and making sure the solution is suitable for your environment. 
3. I'm not responsible for that, ok?

Hope it helps. Let's get started!

1) We need to add on the sysctl.conf file.

       vm.swappiness = 1

       vm.dirty_background_ratio = 3

       vm.dirty_ratio = 80

       vm.dirty_expire_centisecs = 500 

       vm.dirty_writeback_centisecs = 100

2) Preparing Vmware Storage for Oracle RAC. 

For this, we will Shutdown the guest VM, edit VM, VM Option, Advanced , Edit configuration and Add new configuration params.

We'll change guest disk with enable.UUID=true.

3) Add PVSCSI

The "VMware Paravirtual SCSI adapter" is a high-performance storage controller which provides greater throughput and lower CPU use. VMware Paravirtual SCSI controllers are best suited for environments running I/O-intensive applications.

You can see more information about this here.

4) Add Disk for CRS Oracle RAC

5) Add Disk for DATA Oracle RAC in vCenter (VMs  >  linux01 > Click > Edit Settings)

6) ADD NEW DEVICE > Hard Disk

7) Deploy New Hard disk* and Edit

Location: HCI_Shared_Data

New Hard disk: 1700 GB

Disk Provisioning: Thick Provision Eager Zeroed

Sharing: Multi-writer

Disk Mode: Independent - Persistent

Virtual Device Node: SCSI controller 1

8) Now, I'll change the VM Linux 2 (VMs  >  linux02 > Click > Edit Settings)

9) ADD NEW DEVICE > Existing Hard Disk

10) Now, we'll check the disk.

[root@linux01 ~]# lsblk

NAME        MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

sdy          65:128  0  1.7T  0 disk

sdf           8:80   0  100G  0 disk

└─sdf1        8:81   0  100G  0 part

sdo           8:224  0  1.7T  0 disk

sdw          65:96   0  1.7T  0 disk

sdd           8:48   0  100G  0 disk

└─sdd1        8:49   0  100G  0 part

sdm           8:192  0  1.7T  0 disk

sdu          65:64   0  1.7T  0 disk

sdb           8:16   0  220G  0 disk

└─sdb1        8:17   0  220G  0 part /u01

sdk           8:160  0  1.7T  0 disk

sds          65:32   0  1.7T  0 disk

sdi           8:128  0  1.7T  0 disk

sdq          65:0    0  1.7T  0 disk

sr0          11:0    1 1024M  0 rom

sdg           8:96   0  100G  0 disk

└─sdg1        8:97   0  100G  0 part

sdx          65:112  0  1.7T  0 disk

sde           8:64   0  100G  0 disk

└─sde1        8:65   0  100G  0 part

sdn           8:208  0  1.7T  0 disk

sdv          65:80   0  1.7T  0 disk

sdc           8:32   0  100G  0 disk

└─sdc1        8:33   0  100G  0 part

sdl           8:176  0  1.7T  0 disk

sdt          65:48   0  1.7T  0 disk

sda           8:0    0  110G  0 disk

├─sda2        8:2    0  109G  0 part

│ ├─ol-swap 252:1    0    4G  0 lvm  [SWAP]

│ ├─ol-home 252:2    0   55G  0 lvm  /home

│ └─ol-root 252:0    0   50G  0 lvm  /

└─sda1        8:1    0    1G  0 part /boot

sdj           8:144  0  1.7T  0 disk

sdr          65:16   0  1.7T  0 disk

sdh           8:112  0  1.7T  0 disk

└─sdh1        8:113  0  1.7T  0 part

sdp           8:240  0  1.7T  0 disk

You can create as many VMDKs as needed for DATA, FRA and RECO.

After that, just proceed with the installation as usual. 

That's it, guys!!! It's very easy, isn't it?

We hope that helps!

Regards

Mario

[Oracle Autonomous JSON] How to configure and using the Oracle API for MongoDB on Oracle Autonomous JSON Database - Part 2

 Hello everyone

In my last post, I presented how you can configure the Oracle Autonomous JSON Database to use the API for MongoDB.

And today, we're going to use the API to connect into MongoDB shell, create a simple collection and use those documents in the ADJ console.

If you want to see the first post, feel free to click here. 

But always remember:

1. This step-by-step worked for me, but it may not work for you.
2. It's a basic and limited environment. The real life will be different, for sure.
3. This post is for study and testing as well, and has no concern for performance and security best practices.

I have a MongoDB Intance in OCI Compute and I'm going to connect MongoSH in my ADJ using the connection string that I created in the first post. My user is named ADMIN:

mongosh -u admin -p XXXZZZ 'mongodb://XXZZZ-IRONMAN.adb.sa-saopaulo-1.oraclecloudapps.com:27017/admin?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

I'll create a new collection and a new document using MongoSH. After that I'll create a new document using ADJ console and at the end I'll select this data in the console and MongoSH as well.

This example was adapted from the post "SQL NESTED Clause Instead of JSON_TABLE in Oracle Database 19c" from amazing ORACLE-BASE. 

So, let's get started!!!

1) I'll connect in MongoSH using my connection string:

2) Now, I'll access the ADJ console. Click on the "Database Actions" button and select the option "JSON".

  

3) First, I'll create a new decument in the MongoSH shell.

4) The second document will be created in the ADJ console. For this, click in "New" button in the ADJ console.

Click on the "Create" button and he massage bellow should appear.

And now, I have 2 documents in my collection.

And for read operations, I have two options:

1)  Click on the "Database Actions" button and select the option "SQL".

2) I'll create my SQL queries with the same SQL statements used in relational databases.

Using NESTED and WHERE, for example.

3) Finally, I may read my documents usinf MongoDB statements, for shure!!!

It's really cool, right!!!

In the next post, I'll show you how enabling REST is easy in ADJ.

I hope this post will help you!!!

Regards

Mario

[Oracle Autonomous JSON] How to configure and using the Oracle API for MongoDB on Oracle Autonomous JSON Database - Part 1

Hello all!!!

How are you doing?

Today I'll show how you can create an Autonomous Database JSON instance on OCI Free Tier and how you can connect your MongoDB with this instance using Oracle Database API for MongoDB. 

In Part 2, I'll create new collections in the MongoDB shell and demonstrate how we can use these in the ADJ console using JSON and traditional SQL syntax.

The MongoDB is running on OCI Compute Instance :) 

But always remember:

1) This step-by-step worked for me, but it may not work for you.

2) It's a basic and limited environment. The real life will be different, for sure.

3) This post is for study and testing as well, and has no concern for performance and security best practices.

So, let's get started!!!

1) Create a VCN. I'm not show the step by step for this, but it's really easy. Google helps you!!!

2) Now, I'll create the ADJ for testing. Click on Create Autonomous Database.

This is the most important setup that you need. For access the Oracle Database Actions Launchpad, you must have set "Secure access from allowed IPs and VCNs only" and put our VCN and our IP here. It's mandatory!!!

I'll insert my IP address and click "Add access control rule" to add my VCN, as well!

To finish, click "Create Autonomous Database" and wait!

3) ADJ created, it's time to check access to MongoDB.

For this verification, we need the link for mongosh. Click on "Database actions". 

In the end of the page, click on "Oracle Database API for MongoDB".

Copy the MongoDB connection string URL.

Finally, access the MongoDB server and test the connection.

Oh my god! Error!!!! Why????

The error "Client network socket disconnected before secure TLS connection was established" is easy to solve.

We also need to put the Compute Instance Public and Private IP where we run the MongoDB on "Access control list".

And now, I have succeeded!!!

In the next post, I going to create some collections and load documents into MongoDB console, and I'll work with those tables in the ADJ console using the "Traditional Select Syntax".

It's going to be cool! :) 

I hope this post helps you!

Regards

Mario

[Oracle Autonomous JSON] Create new Instance on OCI Free Tier

Hello all!!!

How are you doing?

Today I'll show how you can create an Autonomous Database JSON instance on OCI Free Tier.

I'll use the instance in future posts about JSON and Oracle.

But always remember:

1) This step-by-step worked for me, but it may not work for you.

2) It's a basic and limited environment. The real life will be different, for sure.

3) This post is for study and testing as well, and has no concern for performance and security best practices.

So, let's get started!!!

1) Select "Oracle Database" in menu

2) Click on "Autonomous JSON Database"

3) Click on "Create Autonomous Database"

4) Select the Compartment - Never use the Root compartment, put other information (Display name, Database name) and password as well.

And now, I'm waiting for the conclusion of the process.

A few minutes later, the Instance is ready for use.

That's it my friend!! Easy!!!

I hope it helps you!

Regards

Mario