OCW 2024: What to Expect Around Oracle 23ai

 

I’m so excited to share that I’ll be attending Oracle CloudWorld 2024 this year!. I’m thrilled as It will be my first OCW event to attend in person !!!. 

This event has always been an ignition for innovation and cutting-edge technology, and I can’t wait to dive into what Oracle has to surprise us.

One of my main goals at OCW this year is to learn more about Oracle 23ai, Oracle’s latest and most advanced AI offering. From what I’ve gathered so far, Oracle 23ai promises to bring significant advancements in AI-powered analytics, automation, and decision-making capabilities. I’m particularly interested in how these features can be applied to streamline business operations and enhance data-driven strategies.

Another key area I’m excited to explore is Exascale, Oracle’s high-performance computing solution. The promise of Exascale lies in its ability to process massive datasets at unprecedented speeds, which could be a game-changer for industries that rely heavily on data-intensive operations. Understanding how Exascale integrates with Oracle 23ai and the broader Oracle Cloud ecosystem will be the main interest for me at the event.

Attending Oracle OCW isn’t just about gaining knowledge; it’s also about connecting with industry experts, exchanging ideas with fellow professionals, and staying ahead in the rapidly evolving tech landscape. I’m looking forward to the sessions, hands-on labs, and keynotes that will provide deeper insights into Oracle’s vision for the future of cloud computing and AI.

If you’re also attending, I’d love to connect and share experiences. Let’s make the most of this opportunity to learn, grow, and innovate together!

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: 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.