Oracle 23ai: Quick Overview

 

Oracle Database 23ai

• Oracle database 23ai is the next long-term support release of Oracle database.
• It brings AI to your data with the addition of AI Vector search to Oracle's relational database. 

The difference between Oracle database 23c and Oracle database 23ai

• Oracle database 23c is renamed as database 23ai following the importance of AI technology in this release. 
• Oracle database 23ai is the version 23.4.0.0.0 and will be referred to as moving forward.

Why should you upgrade to Oracle database 23ai?

• Oracle database 23ai offers five years of premium support and three years of extended support.
• Oracle database 19c premier support ends on April 30, 2024, and while Oracle waived the additional fees for Extended Support from May 1, 2024 to April 30, 2026, Extended Support will end on April 30, 2027, giving you a longer support overlap with Oracle Database 23ai. (Oracle Database Release MOS Doc ID 742060.1).

• Oracle database 23ai unifies the relational and document data models to provide the best of both data models worlds in one database with the addition of JSON Relational Duality Views. So instead of using two separated databases (relational and non-relational databases), developers can operate on the same underlying data as either JSON documents, using standard document APIs, or as relational, using standard SQL. To learn more.
• Oracle database 23ai introduces AI Vector Search feature. It is the capability, that stores and searches the semantic content of documents, images, and other unstructured data as vectors and use these to run fast similarity queries. A vector is a popular data structure used in AI applications. Oracle AI Vector Search allows you to generate, store, index, and query vector embeddings along with other business data, using the full power of SQL. To lean more.
• Oracle database 23ai offers a faster database processing comparing with previous database releases.  
• In addition to more than 300 new features compared to previous databases releases such as SQL Firewall and True Cache. 

Upgrade from a previous version to Oracle database 23ai

• Oracle provides a direct database upgrade path from Oracle database 19c and 21c to Oracle database 23ai.
• All other versions of the database need to upgrade to 19c or 21c versions, and then to 23ai.
• The specific upgrade process may vary depending on the current version and configuration of your particular Oracle Database deployment.

Oracle database 23ai free trial

• Oracle offers free trials for Oracle Database 23ai through:
• Oracle Cloud Infrastructure (OCI) with Autonomous Database Free Tier.
• Oracle Database 23ai Free is available for download as a Linux RPM file, an Oracle Linux-based Docker image, or Oracle Virtual Box VM. To get free Oracle 23ai.

Oracle 23ai: Bigfile Tablespace Shrink

 

- Overview:

• If you try to resize/shrink a datafile before Oracle Database 23ai, you'll get "ORA-03297: file contains used data beyond requested RESIZE value", because there are segments' extents (your tables, indexes, partitions, etc.) that are blocking it from shrinking..
• Oracle 23ai database introduces the option to shrink a Bigfile tablespace to reclaim datafile's free space.
• A new procedure (SHRINK_TABLESPACE) has been added to DBMS_SPACE package to shrink tablespace and reclaim datafile's free space.

In this blog, I'll demonstrate the steps to shrink a tablespace.

Prerequisites:

• Oracle Database 23ai.

Preparation Steps:

 1. Create a new Bigfile tablespace.

   - Connect to a pluggable database and create a new tablespace.

2. Create new tables on the new tablespace and populate with data.

  - In my example, I used below commands to create and populate tables with data.

  - Create tables:

create table segments tablespace TEST_SHRINK as select * from dba_segments;

create table objects tablespace TEST_SHRINK as select * from dba_objects;

create table my_tables tablespace TEST_SHRINK as select * from dba_tables;

  - Populate tables by running below inserts multiple times:

insert into segments select * from segments; 

insert into objects select * from objects;

insert into my_tables select * from my_tables;

  - Check datafile's free space and write it down for comparison later.

3. Delete some data or truncate table/s. 

  - In my example, I truncated OBJECTS table.

  - Notice that datafile's free space increased from 316M to 724M.

Shrink Tablespace Steps:

1. Analyze a tablespace shrink by executing below command.

execute dbms_space.shrink_tablespace('TEST_SHRINK',shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE)

  - It is highly recommended to determine the potential of a shrink operation by running it in analyze mode first. 

  - The result of this analysis contains useful information including a list of unsupported objects, a list of movable objects, the total size of movable objects in the tablespace, and the suggested target size for the tablespace. 

  - Analyzing a tablespace will take much less time that actually shrinking it.

2. Execute shrink tablespace by executing below command.

execute dbms_space.shrink_tablespace('TEST_SHRINK')

  - Notice that datafile's size was shrunk from 1024M to 202M and datafile's free space was decreased from 724M to 7M.

Oracle 23ai: Managing Flashback Database Logs Outside the Fast Recovery Area

 

- Overview:

• In previous database releases, you could store flashback database logs only in the fast recovery area.
• Oracle 23ai database introduces the option to store flashback database logs in a separate location.
• With this new option, you can choose to write the flashback logs to faster disks.
• Using a separate destination also eliminates the manual administration to manage the free space in the fast recovery area.
• A new database instance initialization parameter (DB_FLASHBACK_LOG_DEST) has been introduced to specify a separate location for the flashback database logs. This parameter is a system modifiable on CDB level.

In this blog, I'll demonstrate the steps to set a separate location for the flashback database logs.

Prerequisites:

• Oracle Database 23ai running in archive log mode.

Setting Steps:

1. Connect to a root container using SYS user and confirm the database is running in archive log mode and flashback is ON. 

- Notice that FRA destination is +DATA, and flashback logs were generated in FRA (+DATA).

- Currently DB_FLASHBACK_LOG_DEST is not configured.

2. Before assigning a value for the initialization parameter (DB_FLASHBACK_LOG_DEST), you must specify the size of flashback area by setting the initialization parameter (DB_FLASHBACK_LOG_DEST_SIZE) to a nonzero value. 

3. When modifying the DB_FLASHBACK_LOG_DEST parameter with the ALTER SYSTEM statement, you must disable and reenable flashback logging.

- Notice that flashback destination is +RECO.

SQL> alter system set db_flashback_log_dest_size=100G scope=both;

SQL> alter system set db_flashback_log_dest='+RECO' scope=both;

- Archive log files are still generated in FRA (+DATA).

Oracle 23ai: Read-Only Users and Sessions

 

- Overview:

• Oracle 23ai database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the privileges of the user that is connected to the database. 
• The READ_ONLY user only applies on a PDB local database user.
• The READ_ONLY session applies to any type of user for any type of container.
• With the capability to disable and re-enable the read-write capabilities of any user or session without revoking and re-granting privileges provides you with more flexibility to temporarily control the privileges of users or sessions for testing, administration, or application development purposes.
• It also gives you a simple way to control the read-write behavior within different parts of an application that are used by the same user or session.
• You can set the access of a local user to a PDB to READ ONLY or READ WRITE with the ALTER USER or CREATE USER statement.
• After READ ONLY access is enabled for a PDB user, whenever that user connects to the PDB, the session operates as if the database is open in read-only mode and the user cannot perform any write operation.
• A new column (READ_ONLY) has been added to dictionary views *_USERS to show if READ_ONLY is enabled/disabled for a database user.

In this blog, I'll demonstrate enable/disable READ ONLY for a PDB local database user.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• A pluggable database with HR database schema.

A Demo Steps:

1. Connect to a PDB using HR local database user and run a DML statement.

- Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

2. Connect to a PDB using SYSTEM common user and enable READ ONLY for HR user using below SQL command.

SQL> alter user &USERNAME read only;

3. Reconnect to a PDB using HR local database user and run a DML statement.

- Dictionary view *_USERS currently shows that READ_ONLY is enabled for HR user.

- You will get below error message.

SQL Error: ORA-28194: Can perform read operations only

4. Using SYSTEM database session, disable READ ONLY for HR user using below SQL command.

SQL> alter user &USERNAME read write;

5. Reconnect to a PDB using HR local database user and run a DML statement. DML statement will run without error.

- Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

Oracle 23ai: Hybrid Read-Only Mode for Pluggable Databases

 

- Overview:

• Oracle 23ai database introduces a new feature to open Pluggable database in a new mode called hybrid read-only.
• Hybrid read-only mode enables a PDB to operate as either read-write or read-only, depending on the user who is connected to the PDB.
• For Oracle Multitenant database common users (like SYSTEM, or C##<USER>), the PDB will be in read-write mode.
• For Pluggable database local users (like HR, or SCOTT), the PDB will be restricted to read-only mode.
• Hybrid read-only mode enables you to patch and maintain an application in a safe mode for open PDBs without the risk of local users, including higher privileged common users, interfering with the ongoing maintenance operation of the PDB.

In this blog, I'll demonstrate opening a PDB in a hybrid read-only mode, then test a DML command from  common and local users.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• A pluggable database with HR database schema.

Step #1: Open a PDB in Hybrid Read-Only Mode

- Connect to the root container as SYS, close the PDB, then open the PDB in hybrid read-only mode.

SQL> alter pluggable database &PDB_NAME close immediate instances=all;

SQL> alter pluggable database &PDB_NAME OPEN HYBRID READ ONLY instances=all;

Note: The pluggable database open mode will be READ WRITE even after opening in hybrid read-only mode.

Step #2: Testing a DML from a PDB Database Local User

- Connect to the PDB as HR and run a DML statement.

- You will get below error message

SQL Error: ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

Step #3: Testing a DML from a Common Database User

- Connect to the PDB as system HR and run a DML statement.

- A DML statement will be executed without error.

ExaCC: Disable Oracle Archivelog Automatic Deletion Job

 

- Problem:

• Some of my (ExaCC) customers reported that Oracle archive logs are automatically deleted for some databases running on ExaCC cluster, even they haven't deleted or not configured a job to backup and delete archive logs.  

- Overview:

• Whenever we create a database on ExaCC cluster from OCI console, Oracle will add a crontab job  in VM cluster nodes to auto delete archive logs every 30 minutes.
• Oracle uses "bkup_api" tool to auto delete archive logs.

- Checking:

   - As root user run below command to get the contents of "bkup_api" config file for your database.

     /var/opt/oracle/bkup_api/bkup_api get config --file=/<output_file> --dbname=<DB_NAME>

   - Search output config file for parameter "bkup_archlog_cron_entry". If the value is "yes", then this confirms that there is a scheduled job to automatic cleanup of archive logs.

  $ cat /tmp/exadb_bkp.conf | grep bkup_archlog_cron_entry

bkup_archlog_cron_entry=yes

    - As root user search "/etc/crontab" file for a cleanup entry specific to your database.

$ cat /etc/crontab_dba_bkp | grep exadb

19,49 * * * * oracle /var/opt/oracle/bkup_api/bkup_api bkup_archlogs --cron --dbname=exadb

     As you can see above, there is a crontab job that cleanup archive logs for database exadb every 30 minutes. 

- Solution:

- The recommended solution is configuring archive log deletion policy in RMAN repository.

For example,

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK;

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE 'SBT_TAPE';

- A workaround solution, which is not recommended:

 

1. As a root user, take a backup of "/etc/crontab" file on ExaCC VM 1st node.

2. As a root user, edit file "/etc/crontab" and remove entry related to archive maintenance for each database.

In our example, remove below line.

19,49 * * * * oracle /var/opt/oracle/bkup_api/bkup_api bkup_archlogs --cron --dbname=exadb

NOTE:  You need to remove line completely from crontab, commenting out may not work in ExaCC.

3. Check "/etc/crontab" file on other DB nodes also and make sure there are no entries for archive maintenance.

MySQL: Move MySQL OCI DB System to Different Compartment

 

- Overview:

• There is no option on OCI console to move MySQL DB system to different compartment.
• The only option, which is available at the time of writing this blog, is to create a new DB system in different compartment by restoring the backup of the original MySQL DB system. 
• There are three types of MySQL DB system backups to restore from.
•  Automatic Backup.
• Manual Backup.
• Operator Backup.
• When you create a new DB system from a backup, it retains the administrator credentials of the original DB system.
• When you restore an automatic, manual, or operator backup, you restore the complete data of the original DB system in the same tenancy.
• You cannot create a DB system that has the same IP address as a running DB system. If you want to use the same IP address, delete the original running DB system. 

In this blog, I'll demonstrate the steps to create new MySQL DB system by restoring from the backup of another running DB system using OCI console. 

 - Prerequisites:

• An Oracle cloud fee trial or paid account.
• An existing MySQL DB system.  
• An existing manual or Automatic backup for the MySQL DB system.

Steps Restoring From a Backup

1. Sign in to the OCI console.

2. Open the navigation menu and navigate to "Databases -> DB Systems".

3. Choose your compartment. The list of MySQL DB system is displayed. Click your DB system name.

4. Choose the "Restore to new DB system" from the Actions menu.

5. On the "Restore to a new DB System" panel, there are two options to restore from: 

- Restore from DB system at a point-in-time

- Restore from a backup

 In our example, select "Restore from a backup" and click "Select backup".

6. On the "Browse all Backups" panel, select the backup from the list of available backups, and click "Select backup". I'll use the manual backup, which was taken previously.

7. On "Provide DB system information" panel, provide the information of new DB system.

- Compartment: make sure to select the right target compartment.

- DB system name.

- DB system type: Select Standalone for a single-instance DB system, and select High availability for a three-instance DB system.

- Configure networking: select VCN and private subnet.

- Select a shape: you can select a different shape other than the shape of the original running DB system.

- Define Data storage size: should be equal or greater than the storage size of the original running DB system.

- Configure backup plan.

8. Finally, click "Restore".

9. On DB system details home page and under General information section, it will show in the description "Restored from backup".

Now, you have a new standalone DB system, which has the complete data of the original DB system in different compartment.