[ORACLE] How to configure disks for ASM using UDEV file?

Hello everyone

Do you know how to configure disks for Oracle ASM using UDEV file?

It's simple and I'll show to you!

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) I'll identify the disks using the root user.

    fdisk -l |grep /dev

    lsblk

2) I'll identify the "ID_SERIAL" for all disks.

    Example:

    udevadm info --query=all --name=/dev/sdb | egrep "ID_SERIAL"

3) I'll create the file "99-oracle-asmdevices.rules" in /etc/udev/rules.d.

    vim /etc/udev/rules.d/99-oracle-asmdevices.rules

    Insert a line for each disk:

    KERNEL=="sd*", SUBSYSTEM=="block",                     

    ENV{ID_SERIAL}=="VBOX_HARDDISK_VB843f4b1a-07e6004e", 

    SYMLINK+="data_0001",     OWNER="oracle", GROUP="oinstall", MODE="0660"

    Disks sdb. sdc. sdd = data_000?

    Disks sde, sdf, sdg  = fra_000?

    Disks sdh, sdi          = redo_000? 

4) And now, I need to reload the UDEV rules.

    udevadm control --reload-rules

    udevadm trigger --type=devices --action=change

    [root@csct-sp-ora01 u01]# ls -lrht /dev/data*

    [root@csct-sp-ora01 u01]# ls -lrht /dev/fra*

    [root@csct-sp-ora01 u01]# ls -lrht /dev/redo*

Ok, your disks are ready to be used in ASM!

Simple and easy, the UDEV file organizes the owners and accesses of your disks in a very clear way.

That's it folks!

I hope this post helps you!

Regards

Mario

[ORACLE] How can I create and check JSON schema in Oracle 23c? - Part 1

Hello everyone

I hope you're doing well!

As we know, JSON is very flexible and sometimes we need to validate the JSON structure.

Oracle 23c has an easy way to validate the JSON schema.

I'm going to access my database in docker:

docker exec -it Orcl23c /bin/bash 

I'll create a new table (I'll use the Visual Studio for better visuialization):

create table mylegos (

    id              number,

    json_data  json           validate '{

"type"      : "object",

"properties": { 

                                        "code"        : {

                                                          "type"           : "string",

  "minLength": 1,

  "maxLength": 10

     },

"collection": {

                          "type"           : "string",

  "minLength": 1,

  "maxLength": 15

     },   

"model"     : {

                          "type"           : "string",

  "minLength": 1,

  "maxLength": 20

     },

   "peaces"         : {

  "type"         : "number",

  "minimum": 0,

  "maximum": 4000

}

  },

"required" : ["code", "collection", "model", "peaces"]

}',

constraint t1_pk primary key (id)

);  

In my table, all JSON atributes are mandatory.

Now, I'll check the JSON schema:

set pages 120 lines 1000

set long 1000000

column table_name format a15

column column_name format a15

column constraint_name format a20

column json_schema format a60

select table_name, 

   column_name,

   constraint_name, 

   json_schema

from user_json_schema_columns;

And finally, I'll put some data into the table and simulated an error.

insert into mylegos (id, json_data) values 

   (1, json('

   {

"code":"42125",

"collection":"Technic",

"model":"Ferrari 488 GTE AF Corse #51",

"peaces": 1677

   }

'

)

);

Why did this happen?

I'm getting an error because the model has more than 20 characters. And it violated the "maxLength":20 constraint of the model attribute.

When I use the correct rule:

insert into mylegos (id, json_data) values 

   (1, json('

   {

 "code":"42125",

 "collection":"Technic",

 "model":"Ferrari 488 GTE AF51",

 "peaces": 1677

   }

'

)

);

select json_serialize(ml.json_data pretty) from mylegos ml;

And thats it! As simple as it sounds!

I hope this post helps you!!!

Regards

Mario

[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