Oracle 23c : New Feature- Hybrid Read only mode for PDB

In General, DBA's open pluggable database or PDB either in Read Write Mode or Read-only, what if the database behaves in read-only, and read-write mode based on the connected user. One such feature is introduced in Oracle 23c. As we know there are two types of users in multitenant database starting from Oracle12c, local user and common user, Local users are local to the pluggable database and common users are common to all pluggable and container databases. Common users can connect to a container and several pluggable databases. 

As the Oracle database 23c release date was announced, everybody was excited about new features for DBA's in Oracle 23c. So here is one feature with many benefits: Hybrid Read-only mode for a pluggable database. Hybrid Read only mode enables the pluggable database to operate either in read-only or read-write mode.

For common users- a pluggable database or PDB will be in both read-write and read-only mode

For Local users/application users - a pluggable database or PDB will be restricted to read-only mode

To enable Hybrid read-only mode, alter pluggable database command needs to be executed against PDB.

we will not be able to see hybrid read-only mode  through v$database or v$pdbs, it can be viewed through v$container_topology with new column IS_HYBRID_READ _ONLY

SQL> desc v$container_topology

 Name                    Null?    Type

 ---------------------- -------- ----------------------------

 INSTANCE_NUMBER                     NUMBER

 CON_NAME                            VARCHAR2(128)

 OPEN_MODE                           VARCHAR2(10)

 CPU_COUNT                           NUMBER

 CON_ID                              NUMBER

 RESTRICTED                                VARCHAR2(3)

 IS_HYBRID_READ_ONLY                       VARCHAR2(3)

 

While we can see some different output in v$pdbs as well, for local users it will be read-only for common users it will be read-write mode

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

 

CON_NAME        OPEN_MODE  IS_HYBRID_READ_ONLY

-------------------- ---------- --------------------------

FREEPDB1        READ WRITE NO

 

common users

 

SQL> show user

USER is "SYS"

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

----------------- -----------------------------------

FREEPDB1        READ WRITE

As local user 

SQL> show user

USER is "LOCAL_USR"

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

----------------- -----------------------------------

FREEPDB1        READ WRITE

 

SQL> 

Now change the mode of the pluggable database to Hybrid read-only mode. If the database is in open mode, close it using the close immediate command and open the pluggable database in hybrid read-only by using the below steps

 

SQL> alter pluggable database close immediate;

 

Pluggable database altered.

 

SQL> alter pluggable database open hybrid read-only;

 

Pluggable database altered.

 

SQL> show con_name

 

CON_NAME

------------------------------

FREEPDB1

SQL> select con_name , open_mode , is_hybrid_read_only from v$container_topology;

 

CON_NAME        OPEN_MODE                    IS_HYBRID_READ_ONLY

-------------------- ----------------------------------- -----------------------------------

FREEPDB1        READ WRITE             YES

 

SQL> show user

USER is "SYS"

SQL> 

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

------------------ -----------------------------------

FREEPDB1        READ WRITE

 

SQL> conn local_usr/Test123@freepdb1

Connected.

SQL> 

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

------------------ -----------------------------------

FREEPDB1        READ ONLY

 

SQL> 

In the above output, we can see, with common user SYS, it is READ WRITE mode, while with the local user it is READ ONLY mode

 

Reverting to the original state (Read Write):  follow simple steps to revert Oracle pluggable database to read-only mode

 

SQL> conn sys@freepdb1 as sysdba

Enter password: 

Connected.

SQL> alter pluggable database close immediate;

 

Pluggable database altered.

 

SQL> alter pluggable database open;

 

Pluggable database altered.

 

SQL> select name,open_mode from v$pdbs;

 

NAME            OPEN_MODE

-------------------- -----------------------------------

FREEPDB1        READ WRITE

In this way, we simply tested Oracle database 23c new feature of hybrid read-only mode, looks impressive feature, we will discuss more such features associated with oracle 23c.

 

Read More

Want to Convert number to text/word form? Here is the solution

Oracle Database has great functionality that converts any number to its word format, suppose number "2 "you want to write it as "two", you can do it using Oracle database. you may not need it for single double, triple, or quadruple numbers but what if your number is big and it makes you lazy to read it or convert it into Word format, Here is the solution.

Make use of a dummy table dual along with a few other functions such as to_char and to_date, Here is the exact query to convert your number 3434590 into word or text format

SQL> select to_char(to_date(3434590,'j'),'jsp') from dual;

TO_CHAR(TO_DATE(3434590,'J'),'JSP')

------------------------------------------------------------

three million four hundred thirty-four thousand five hundred ninety

Where J stands for Jubilian date and which starts from 1 to 5373484 and JSP stands for Julian dates SPelled(SP)

as there is no direct function in oracle to convert number to words, you can try this method. Here is another simple example

Read More

Oracle 23c : Download and Install on Windows 10

As we have discussed features and release details of Oracle 23c, in the previous article, now we are going to install and make our hands dirty with Oracle database 23c on a virtual machine, Oracle has provided 23c in the form of a Virtual machine

Details and requirements for installation can be found on the Oracle website provided below 

https://www.oracle.com/database/technologies/databaseappdev-vm.html

This is only for testing purposes and should not be used for production deployments

Before the actual download of Oracle 23c, download and install the Oracle VM Virtual box from the link 

https://www.oracle.com/virtualization/technologies/vm/downloads/virtualbox-downloads.html 

You can find a complete article about the installation of the Virtual box here

To download Oracle 23c, click on the below, size is around 6.5G, and it will take a while to complete the download

Once Oracle 23c file is downloaded and Virtual box is installed in the system, Open Oracle VM Virtual Box and follow the below steps 

Click on Import appliance

In the next step, provide the location of Oracle_Database_23c_Free_Developer_Appliance, downloaded earlier

Click on Finish and agree to terms

Wait for the import to be finished

Once the import is completed, you will see below the window, that indicates, the import was successful. 

Now click on START to turn on the VM Box

The below screen show, installation is going on 

Once installation is completed, you will see the below window and readme.txt file which has all connection details 

Open sqlcl to connect to the database using the username and password provided in readme.txt

Similarly, you can use the below method to connect to the container database i.e. CDB

Hurray, we tested the connectivity of Oracle 23, lets try to find out new DBA features

Read More

Install Oracle Virtual box(VM Box) on windows system

 In this article, we will discuss the installation of the Oracle virtual box. Use the below link to download the Oracle virtual box 

https://www.oracle.com/virtualization/technologies/vm/downloads/virtualbox-downloads.html

Once the download is finished, you can click on the .exe file 

You will see below the welcome window, follow the regular next, next options, and provide inputs  like size, location, memory, etc if required

Once you click on enough next option, the below status window will appear

Once installation is completed, you can see the below window, you can simply click on the Finish button.

That's it!!! we are done with oracle virtual box installation on Windows Oracle machine, Next will be downloading and installing Oracle 23c

Read More

Oracle Database 23c Features

The hottest topic of the week is the release of Oracle database version 23c, developer release. This version 23c is ready for Database Administrators and developers to test new features and functionalities.

DBAs and Developers can plan future strategies based on new releases. This latest version of Oracle database 23c is the next long-term upgrade after 19c. There was not much fuss about 18c,20c,21c, and 22c. but now 23c is more in an autonomous database and the free version is already in the DBAs and Developers' system. 

Below few important features identified by different sources 

JSON relational duality

Build an application in either relational or JSON paradigm with a single source of truth and benefit from the strengths of both, Data is held once but can be accessed, written, and modified with either approach.

JavaScript stored Procedures 

JavaScript code now be executed closer to data by writing JavaScript stored procedures or loading existing JavaScript libraries into oracle dba

JSON Schema 

Ensure and validate JSON document structures via industries' standard JSON demons

Operational Property graphs

Build both transaction and analytical property graph applications using new SQL standard property graph queries support.

Oracle Kafka APIs

Kafka application runs against the Oracle database 

SQL Domain 

The new Domain can act as a lightweight type modified that centrally documents intended data usage extending and drastically improving SQL standard domains.

Annotation

database metadata can now be stored directly alongside the data.

Refer to another article to download and try your hands on Oracle 23c.

Read More

Processing select statement in oracle database

Understanding the execution of any query in the Oracle database needs an understanding of different components of the Oracle database such as SGA, Processes, datafiles, memory, etc.

Here, we will discuss the execution of a simple select query like “select * from

 hr.emp

First of all, a connection to the database is required, and to achieve that you need to know database connection details such as port number, hostname, sid, etc. Once you have these details, the user will log in to the database and a connection is established using the listener.

Now user can execute any query, the query sent by the user once reaches the database, gets picked up by the server process, and from here onwards, processing will be carried out by the server process.

The server process will create a hash for the SQL query sent by the user, the hash is just hexadecimal coding in the form of SQL id and SQL hash value.

Now server process searches in the shared pool and checks if any SQL area is present for the user query, if SQL area is already present for this query, if the result finds in SQL area, data will be returned to the user from memory itself, no further process required.

In case the SQL area is not present for this SQL query, that means the query is getting executed the first time in the database. The server process will start building SQL area for the query

Processing of the select query is taken in 3 main parts

• Parsing
• Execution
• Fetching

Parsing

Parsing is also known as validation of the query sent by the user, it will be done in three steps

• Syntax check
• Semantics check
• Privilege check

Syntax Check

            Here, the syntax of the SQL query will be verified as per SQL standards and keywords, sometimes we misspell some keywords such as “SELECT” is written as “SLECT” or “SELCT”, syntax check will throw an error, once the syntax check is passed, it moves to semantic check

Semantic check

            In semantic check, the server process verifies the structure of the query, i.e., columns names, tables, or schema names used will be verified with the help of a dictionary cache

Privilege check

In this stage of parsing, privileges on users will be verified, the client is executing the query using USER1 schema and accessing the table from USER2, so the server process will check if USER1  has access to USER2

Once all these checks are verified as part of the parsing, it will start the execution of the SQL query based on the SQL plan described by OPTIMIZER, OPTIMIZER is an internal utility in the database, that keeps all the statistics of the database objects

The server process will bring data in the buffer cache i.e., memory from datafiles, once data is loaded in the memory, a complete SQL area will be created and the server process will return data to the user, this completes the processing of the SQL query. so, processing the query involves a lot of components of the database

Read More

Snowflake : System defined roles in Snowflake

There are a total 5 default system-defined roles in snowflake, those are

ACCOUNTADMIN

SYSADMIN

SECURITYADMIN

USERADMIN

PUBLIC

Below is a graphical hierarchy of these roles in snowflake

ACCOUNTADMIN is a top-level role in snowflake architecture, it has privileges of both SYSADMIN and SECURITYADMIN

PUBLIC is a lower-level role in snowflake and USERADMIN role inherits all the privileges of PUBLIC and passes them on to SECURITYADMIN

 

Here we will discuss, what users can do when these roles are assigned.

 

ACCOUNTADMIN

inherits privilege of SYSADMIN and SECURITYADMIN

It should be granted to very limited users, and users with this role can do anything with snowflake objects

 

SECURITYADMIN 

USERADMIN role is granted to SECURITYADMIN role

user with this role can manage users and roles in snowflake architecture

user can manage any object grants globally

 

SYSADMIN 

user with this role can create data warehouses and databases 

once custom roles are created, need to assign it to SYSADMIN role

 

USERADMIN

user level role and assigned to individual users 

user with this role can create users and roles

 

PUBLIC

this role is automatically assigned to every user

user will be able to create their own objects

Read More

PostgreSQL : How to check size of the database

As a PostgreSQL database administrator, you may come across situations where you want to check the size of the full database, here is a quick solution for the same

Here is my PostgreSQL cluster details, there is one database called technodb, and I need to get the size of this database, here is how we can check

There is a command called pg_database_size('<database name>'), here is how we can execute it

postgres=# select pg_database_size('technodb');

 pg_database_size

------------------

          7773319

(1 row)

by default, it will result in bytes and if you want to bring a result in megabytes (MB), here is how you can do it 

postgres=# select pg_database_size('technodb')/1024/1024;

 ?column?

----------

        7

(1 row)

There is another way to check the size of the database, just execute the command \l+ <database name>

postgres=# \l+ technodb

                                                         List of databases

   Name   |  Owner   | Encoding |      Collate       |       Ctype        | Access privileges |  Size   | Tablespace | Description

----------+----------+----------+--------------------+--------------------+-------------------+---------+------------+-------------

 technodb | postgres | UTF8     | English_India.1252 | English_India.1252 |                   | 7591 kB | pg_default |

(1 row)

and if you want to get the size of all databases together, here is how you can fetch details using pg_database view

 

postgres=# select datname as Database , (pg_database_size(datname))/1024/1024 as Database_size_MB from pg_database;

 database  | database_size_mb

-----------+------------------

 postgres  |                7

 mydb      |                7

 template1 |                7

 template0 |                7

 technodb  |                7

 

[Also read - Types of shutdown in PostgreSQL database]

Read More

Snowflake : show databases command optimized

In snowflake, we use the "show databases" command to see details about default and created databases, this command shows a lot of details about databases, what if we just need the names of the database not whole details about them, after digging out I found this "databases" is table present in INFORMATION_SCHEMA schema, so we can query the details from "databases" tables. 

To fetch details you need to select the data warehouse, database, and schema 

 

technosnow#(no warehouse)@(no database).(no schema)>use WAREHOUSE TECHNO_WS;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

1 Row(s) produced. Time Elapsed: 0.644s

technosnow#TECHNO_WS@(no database).(no schema)>use SNOWFLAKE;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

1 Row(s) produced. Time Elapsed: 0.810s

technosnow#TECHNO_WS@SNOWFLAKE.(no schema)>use schema INFORMATION_SCHEMA;

+----------------------------------+

| status                           |

|----------------------------------|

| Statement executed successfully. |

+----------------------------------+

1 Row(s) produced. Time Elapsed: 0.313s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>desc databases;

+----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------+

| name           | type              | kind   | null? | default | primary key | unique key | check | expression | comment                                                         | policy name |

|----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------|

| DATABASE_NAME  | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | Name of the database                                            | NULL        |

| DATABASE_OWNER | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | Name of the role that owns the schema                           | NULL        |

| IS_TRANSIENT   | VARCHAR(3)        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Whether this is a transient table                               | NULL        |

| COMMENT        | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Comment for this database                                       | NULL        |

| CREATED        | TIMESTAMP_LTZ(9)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Creation time of the database                                   | NULL        |

| LAST_ALTERED   | TIMESTAMP_LTZ(9)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Last altered time of the database                               | NULL        |

| RETENTION_TIME | NUMBER(9,0)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Number of days that historical data is retained for Time Travel | NULL        |

+----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------+

7 Row(s) produced. Time Elapsed: 0.402s

 

Here you see the columns for the "databases" table, now you can fetch anything you want

 

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select database_name from databases;

+-----------------------+

| DATABASE_NAME         |

|-----------------------|

| EXERCISE_DB           |

| SNOWFLAKE_SAMPLE_DATA |

| TECHNODB              |

+-----------------------+

3 Row(s) produced. Time Elapsed: 1.093s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

[Also Read - Different Editions of Snowflake]

[Also Read - How to Undrop database]

 

Read More