DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c

The DBMS_JOB package has been deprecated since 12cR2. Oracle 19c takes the demise of the DBMS_JOB package a step further by converting any DBMS_JOB jobs to DBMS_SCHEDULER jobs.

◉ Create a Job Using DBMS_JOB

In Oracle 19c jobs created using the DBMS_JOB package are implemented as DBMS_SCHEDULER jobs, as demonstrated below.

We can see from the output below we don't have any jobs for this user.

conn test/test@pdb1

column what format a30

select job, what from user_jobs;

0 rows selected.

SQL>

column job_name format a30

column job_action format a30

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

We create a job using the DBMS_JOB.SUBMIT procedure, but we are not going to issue a COMMIT statement.

declare

  l_job  pls_integer;

begin

  dbms_job.submit (

    job       => l_job,

    what      => 'begin null; end;',

    next_date => trunc(sysdate)+1,

    interval  => 'trunc(sysdate)+1'

  );

end;

/

We can see the job is listed in the USER_JOBS and USER_SCHEDULER_JOBS views.

select job, what from user_jobs;

       JOB      WHAT

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

         1        begin null; end;

1 row selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

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

DBMS_JOB$_1                    begin null; end;

1 row selected.

SQL>

Notice the JOB_NAME of "DBMS_JOB$_?" for the DBMS_SCHEDULER job that has been generated.

◉ Transactional Jobs

One of the reasons people still use the DBMS_JOB package is it allows you to create jobs that are part of a bigger transaction. If a failure causes an exception, all the current work along with the jobs defined as part of it can be rolled back. We can demonstrate this using the job created above. Remember, we didn't issue a COMMIT, so the job is not visible from another session connected to the same user.

Without closing the original session, open a new connection and check for the jobs.

conn test/test@pdb1

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

Now return to the original session and the jobs are still visible.

select job, what from user_jobs;

       JOB     WHAT

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

         1      begin null; end;

1 row selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

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

DBMS_JOB$_1                    begin null; end;

1 row selected.

SQL>

Issue a ROLLBACK, and the job definition will be removed.

rollback;

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

As a result, the DBMS_JOB package can still be used to create transactional jobs, that are implemented using the DBMS_SCHEDULER scheduler. This also provides backwards compatibility.

◉ Materialized View Refresh Groups

Up to and including Oracle 18c, materialized view refresh groups were implemented using the kernel APIs exposed by the old DBMS_JOB package. In Oracle 19c things look a little different.

Create a table, materialized and refresh group including that materialized view.

create table t1 (id number);

create materialized view t1_mv

refresh force

on demand

as

select * from t1;

begin

   dbms_refresh.make(

     name                 => 'MINUTE_REFRESH',

     list                 => '',

     next_date            => sysdate,

     interval             => '/*1:mins*/ sysdate + 1/(60*24)',

     implicit_destroy     => false,

     lax                  => false,

     job                  => 0,

     rollback_seg         => null,

     push_deferred_rpc    => true,

     refresh_after_errors => true,

     purge_option         => null,

     parallelism          => null,

     heap_size            => null);

end;

/

begin

   dbms_refresh.add(

     name => 'MINUTE_REFRESH',

     list => 'T1_MV',

     lax  => true);

end;

/

We don't see a job in the USER_JOBS view, but we do see one in the USER_SCHEDULER_JOBS view.

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

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

MV_RF$J_0_S_210                dbms_refresh.refresh('"TEST"."

                               MINUTE_REFRESH"');

1 row selected.

SQL>

But this job is transactional, in that a ROLLBACK will remove the job, along with the refresh group definition.

rollback;

select job, what from user_jobs;

0 rows selected.

SQL>

select job_name, job_action from user_scheduler_jobs;

0 rows selected.

SQL>

It would appear the refresh group functionality has been re-implemented using the kernel APIs that sit under the DBMS_SCHEDULER package, but without the implicit commit. Similar to the way the DBMS_JOB interface has been re-implemented. This is not 100% backwards compatible, as the associated job is not visible in the USER_JOBS view. If you have any functionality that relies on the link between the refresh groups and the old scheduler, it will need revisiting. I can't imagine that will be a problem for most people.

You can clean up the test table and materialized view using these commands.

drop materialized view t1_mv;

drop table t1 purge;

◉ Security : The CREATE JOB Privilege is Required?

At first glance the loophole discussed here sounds really bad, but remember that even in Oracle 18c, any user connected to the database could create a job using the DBMS_JOB interface, so this loophole is no worse than what came before. It just breaks the DBMS_SCHEDULER security.

As Connor McDonald pointed out, the conversion means users require the CREATE JOB privilege to allow them to create jobs using the DBMS_JOB package, where previously they didn't. We can see this if we create a user with just the CREATE SESSION privilege and attempt to create a job.

create user test2 identified by test2;

grant create session to test2;

conn test2/test2@pdb1

declare

  l_job  pls_integer;

begin

  dbms_job.submit (

    job       => l_job,

    what      => 'begin null; end;',

    next_date => trunc(sysdate)+1,

    interval  => 'trunc(sysdate)+1'

  );

end;

/

Error report -

ORA-27486: insufficient privileges

ORA-06512: at "SYS.DBMS_ISCHED", line 9387

ORA-06512: at "SYS.DBMS_ISCHED", line 9376

ORA-06512: at "SYS.DBMS_ISCHED", line 175

ORA-06512: at "SYS.DBMS_ISCHED", line 9302

ORA-06512: at "SYS.DBMS_IJOB", line 196

ORA-06512: at "SYS.DBMS_JOB", line 168

ORA-06512: at line 4

27486. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a scheduler operation without the

           required privileges.

*Action:   Ask a sufficiently privileged user to perform the requested

           operation, or grant the required privileges to the proper user(s).

SQL>

There is a loophole caused by the refresh group implementation. If we repeat the previous refresh group example, we can see we are able to create a job without the CREATE JOB privilege.

begin

   dbms_refresh.make(

     name                 => 'MINUTE_REFRESH',

     list                 => '',

     next_date            => sysdate,

     interval             => '/*1:mins*/ sysdate + 1/(60*24)',

     implicit_destroy     => false,

     lax                  => false,

     job                  => 0,

     rollback_seg         => null,

     push_deferred_rpc    => true,

     refresh_after_errors => true,

     purge_option         => null,

     parallelism          => null,

     heap_size            => null);

end;

/

select job_name, job_action from user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

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

MV_RF$J_0_S_242                dbms_refresh.refresh('"TEST2".

                               "MINUTE_REFRESH"');

1 row selected.

SQL>

That in itself is not devastating because it's for a very specific purpose, but most of Oracle's security is based on you being able to do whatever you want with objects you already own, so what happens if we try to change the attributes?

begin

  dbms_scheduler.set_attribute (

    name      => 'MV_RF$J_0_S_242',

    attribute => 'job_action',

    value     => 'begin null; end;'

  );

end;

/

SELECT job_name, job_action FROM user_scheduler_jobs;

JOB_NAME                       JOB_ACTION

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

MV_RF$J_0_S_242                begin null; end;

1 row selected.

SQL>

So we can create a job using the DBMS_REFRESH package, then alter it to suit our purpose, giving us the ability to create a job without the need for the CREATE JOB privilege.

It would appear the re-implementation of the DBMS_REFRESH package has not followed the same security rules as that used by the other scheduler implementations. I'm sure this will get fixed in a future release.

Until this issue is resolved, you should probably revoke EXECUTE on the DBMS_REFRESH package from PUBLIC, as you may already do for the DBMS_JOB package.

Note. I raised this issue as "SR 3-20860955641 : Jobs can be created without the CREATE JOB privilege". This is now Bug 30357828 and is being worked on.

Miscellaneous

 

◉ The CREATE JOB privilege is necessary to create jobs using the DBMS_JOB package.

◉ During upgrades to 19c, any jobs defined using DBMS_JOB get converted to DBMS_SCHEDULER jobs. See Mike Dietrich's post about this.

◉ The SCHEDULER$_DBMSJOB_MAP dictionary table provides the mapping between the old DBA_JOBS job and the DBA_SCHEDULER job.

SQL> desc scheduler$_dbmsjob_map

Name            Null?    Type

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

DBMS_JOB_NUMBER NOT NULL NUMBER

JOB_OWNER       NOT NULL VARCHAR2(128)

JOB_NAME        NOT NULL VARCHAR2(128)

SQL>

Source: oracle-base.com

Continue reading

Bigfile Tablespace Shrink in Oracle Database 23ai

From Oracle database 23ai onward we can use the DBMS_SPACE package to shrink a bigfile tablespace to reclaim unused space.

◉ Setup

We need a tablespace to run some tests. In Oracle database 23ai the default file size for a tablespace is bigfile, so we don't need to specify it explicitly.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

-- Create a tablespace and user for the test.

drop user if exists reclaim_user cascade;

drop tablespace if exists reclaim_ts including contents and datafiles;

create tablespace reclaim_ts datafile size 10m autoextend on next 1m;

create user reclaim_user identified by reclaim_user default tablespace reclaim_ts quota unlimited on reclaim_ts;

grant create session, create table to reclaim_user;

grant select_catalog_role to reclaim_user;

-- Create and populate two tables in the test schema.

conn reclaim_user/reclaim_user@//localhost:1521/freepdb1

create table t1 (

  id   number,

  col1 varchar2(4000),

  col2 varchar2(4000),

  constraint t1_pk primary key (id)

);

create table t2 (

  id   number,

  col1 varchar2(4000),

  col2 varchar2(4000),

  constraint t2_pk primary key (id)

);

insert /*+append*/ into t1

select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')

from dual

connect by level <= 100000;

commit;

insert /*+append*/ into t2

select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')

from dual

connect by level <= 100000;

commit;

exec dbms_stats.gather_table_stats(null, 't1');

exec dbms_stats.gather_table_stats(null, 't2');

We check the size of the datafile associated with the tablespace and the tables.

select tablespace_name, blocks, bytes/1024/1024 as size_mb

from   dba_data_files

where  tablespace_name = 'RECLAIM_TS';

TABLESPACE_NAME                    BLOCKS    SIZE_MB

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

RECLAIM_TS                                      427520       3340

SQL>

column table_name format a10

select table_name, blocks, (blocks*8)/1024 as size_mb

from   user_tables

where  table_name in ('T1', 'T2')

order by 1;

TABLE_NAME     BLOCKS    SIZE_MB

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

T1                          200696      1567.9375

T2                          200694     1567.92188

SQL>

We truncate the first table, leaving a gap in the datafile before the table segments start.

truncate table t1;

exec dbms_stats.gather_table_stats(null, 't1');

We can repeat this setup between tests to start cleanly.

◉ Analyze Bigfile Tablespace

We run an analyze to see how much space we can save by performing a shrink. We call the SHRINK_SPACE procedure in the DBMS_SPACE package, passing in the name of the bigfile tablespace name and the TS_MODE_ANALYZE shrink mode constant.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

set serveroutput on

execute dbms_space.shrink_tablespace('RECLAIM_TS', shrink_mode => dbms_space.ts_mode_analyze);

-------------------ANALYZE RESULT-------------------

Total Movable Objects: 2

Total Movable Size(GB): 1.56

Original Datafile Size(GB): 3.39

Suggested Target Size(GB): 3.19

Process Time: +00 00:00:00.053777

PL/SQL procedure successfully completed.

SQL>

It doesn't think we can save much space, which sounds suspicious as we have truncated one table, which takes up approximately half of the space in the data file.

◉ Shrink Bigfile Tablespace

We run a shrink operation by calling the SHRINK_SPACE procedure with the tablespace name.

set serveroutput on

execute dbms_space.shrink_tablespace('RECLAIM_TS');

-------------------SHRINK RESULT-------------------

Total Moved Objects: 2

Total Moved Size(GB): 1.56

Original Datafile Size(GB): 3.26

New Datafile Size(GB): 1.63

Process Time: +00 00:00:30.586722

PL/SQL procedure successfully completed.

SQL>

Despite what the analyze said, we have reduced the associated datafile to approximately half its original size.

The previous command is the equivalent of calling the procedure with a shrink mode of TS_MODE_SHRINK and a target size of TS_TARGET_MAX_SHRINK.

set serveroutput on

execute dbms_space.shrink_tablespace('RECLAIM_TS', shrink_mode => dbms_space.ts_mode_shrink, target_size => dbms_space.ts_target_max_shrink);

Source: oracle-base.com

Continue reading

Auditing Enhancements in Oracle Database 23ai

This post describes some of the auditing enhancements in Oracle database 23ai.

◉ Desupport of Traditional Auditing

Traditional auditing was deprecated in Oracle 21c, and has been desupported in Oracle 23ai. Make sure you are using Unified Auditing. 

◉ Audit Individual Columns for Tables and Views

In Oracle 23ai we can create audit policies on individual columns of tables and views, which allows us to thin out the contents of the audit trail by ignoring actions that don't affect the columns of interest. For a table or view column we can audit the following actions, as described here.

ALL, ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, SELECT, UPDATE

To demonstrate this we create a test table.

conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists audit_test_tab purge;

create table audit_test_tab (

  id  number generated always as identity,

  col1 varchar2(10),

  col2 varchar2(10),

  col3 varchar2(10)

);

insert into audit_test_tab (col1, col2) values ('apple', 'banana');

commit;

We connect to a privileged user and create a new audit policy. We want to audit updates on COL1 or COL2, and queries of COL2. Notice we supply a comma-separated list of columns the audited action applies to.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

noaudit policy test_audit_policy;

drop audit policy test_audit_policy;

create audit policy test_audit_policy

  actions update(col1, col2) on testuser1.audit_test_tab,

          select(col2) on testuser1.audit_test_tab

  container = current;

audit policy test_audit_policy;

We check the audit trail for actions against the table, and we can see there are no actions audited.

column event_timestamp format a30

column dbusername format a10

column action_name format a20

column object_schema format a10

column object_name format a20

column sql_text format a40

select event_timestamp,

       dbusername,

       action_name,

       object_schema,

       object_name,

       sql_text

from   unified_audit_trail

where  object_name = 'AUDIT_TEST_TAB'

order BY event_timestamp;

no rows selected

SQL>

We perform some operations against the test table, some of which are auditable actions.

conn testuser1/testuser1@//localhost:1521/freepdb1

-- Not audited.

insert into audit_test_tab (col1, col2) values ('apple2', 'banana2');

update audit_test_tab

set    col3 = 'pear'

where  col3 is null;

commit;

select id from audit_test_tab;

        ID

----------

         1

         2

SQL>

-- Audited.

update audit_test_tab

set    col1 = 'apple1'

where  col1 = 'apple';

update audit_test_tab

set    col2 = 'banana1'

where  col2 = 'banana';

select col2 from audit_test_tab;

COL2

----------

banana1

banana2

SQL>

We check the audit trail.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

column event_timestamp format a30

column dbusername format a10

column action_name format a20

column object_schema format a10

column object_name format a20

column sql_text format a40

select event_timestamp,

       dbusername,

       action_name,

       object_schema,

       object_name,

       sql_text

from   unified_audit_trail

where  object_name = 'AUDIT_TEST_TAB'

order BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME          SQL_TEXT

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

14-JUN-23 19.31.17.231940 PM   TESTUSER1  UPDATE               TESTUSER1  AUDIT_TEST_TAB       update audit_test_tab

                                                                                               set    col1 = 'apple1'

                                                                                               where  col1 = 'apple'

14-JUN-23 19.31.17.248862 PM   TESTUSER1  UPDATE               TESTUSER1  AUDIT_TEST_TAB       update audit_test_tab

                                                                                               set    col2 = 'banana1'

                                                                                               where  col2 = 'banana'

14-JUN-23 19.31.17.252646 PM   TESTUSER1  SELECT               TESTUSER1  AUDIT_TEST_TAB       select col2 from audit_test_tab

SQL>

Notice only those actions on the specified columns were audited. The query of the ID column, and the updates to the COL3 column were not audited.

Source: oracle-base.com

Continue reading

ARGUMENT Command in SQL*Plus 23ai and SQLcl 22.4

The ARGUMENT command in SQL*Plus 23ai and SQLcl 22.4 allows us to manage arguments passed to a script. This includes the prompt or default value if an argument is missing, and whether the user input from a prompt should be echoed to the screen.

Remember, SQLcl is shipped independently of the database, so we can use the ARGUMENT command against any version of the database from 11.2 onward.

1. PROMPT

The PROMPT option of the ARGUMENT command allows us set the prompt text displayed if the argument is missing from the command line. Create a file called "test.sql" with the following contents.

set verify off

argument 1 prompt "Enter a value for the first argument:"

column arguments format a10

select '&1' as arguments;

undefine 1

Let's breakdown what we are doing here.

◉ We use SET VERIFY OFF so we don't display old and new values for the argument.

◉ We use the ARGUMENT command to define the prompt text for argument number 1.

◉ We use the argument in a query to display the value.

◉ We use UNDEFINE to make sure the current value of the argument is not reused. In SQLcl we could use SET PARAMETERPOLICY ISOLATE to achieve this, but using UNDEFINE works for both tools.

We test the script. In the first example we are prompted and provide the value "banana". In the second example we are prompted and provide the value "apple". In the third example we provide the value "pear" on the command line, and we are not prompted to enter a value.

SQL> @test.sql

Enter a value for the first argument:banana

ARGUMENTS

----------

banana

SQL> @test.sql

Enter a value for the first argument:apple

ARGUMENTS

----------

apple

SQL> @test.sql pear

ARGUMENTS

----------

pear

1 row selected.

SQL>

2. PROMPT and HIDE (SQL*Plus Only)

Adding the HIDE keyword to the ARGUMENT command means the text we enter is no longer echoed to the screen. This is useful when entering passwords. We amend the previous script to add the HIDE keyword.

set verify off

argument 1 prompt "Enter a value for the first argument:" hide

column arguments format a10

select '&1' as arguments;

undefine 1

We test the amended script. In the first example we are prompted and provide the value "secret". Notice this is not echoed to the screen. In the second example we provide the value "secret2" on the command line, and we are not prompted to enter a value.

SQL> @test.sql

Enter a value for the first argument:

ARGUMENTS

----------

secret

1 row selected.

SQL> @test.sql secret2

ARGUMENTS

----------

secret2

1 row selected.

SQL>

3. DEFAULT

Not surprisingly the DEFAULT keyword allows us to define a default value to use if an argument is not specified. We amend the previous script to assign a default value.

set verify off

argument 1 default "banana"

column arguments format a10

select '&1' as arguments;

undefine 1

We test the amended script. If we don't provide a command line argument the default value of "banana" is used. If we provide the command line argument, it is used.

SQL> @test.sql

ARGUMENTS

----------

banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS

----------

apple

1 row selected.

SQL>

4. ARGUMENT with DEFINE

In the previous examples we have used the arguments directly, but we could use them in conjunction with a DEFINE command. We amend the test script giving it the following contents. In this example we have used the first argument to define "arg1", which we use the in the subsequent query.

set verify off

argument 1 prompt "Enter a value for arg1:"

define arg1 = '&1';

column arguments format a10

select '&arg1' as arguments;

undefine 1

We test the amended script and it performs as expected.

SQL> @test.sql

Enter a value for arg1:banana

ARGUMENTS

----------

banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS

----------

apple

1 row selected.

SQL>

5. ARGUMENT with VARIABLE

We can also use arguments with variables defined in SQL*Plus and SQLcl. We amend the test script giving it the following contents. In this example we have defined a variable called "var1" and set it to the value if the first argument. We use the variable in the subsequent query.

variable var1 varchar2(10);

set verify off

argument 1 prompt "Enter a value for var1:"

set feedback off

exec :var1:= '&1';

set feedback on

column arguments format a10

select :var1 as arguments;

undefine 1

We test the amended script and it performs as expected.

SQL> @test.sql

Enter a value for var1:banana

ARGUMENTS

----------

banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS

----------

apple

1 row selected.

SQL>

Source: oracle-base.com

Continue reading

AI-Fueled Enterprise Data Management: The Rise Of Oracle Database 23ai

In an era where artificial intelligence is reshaping industries, Oracle has once again positioned itself at the forefront of innovation with the release of Oracle Database 23ai.

This latest iteration of Oracle's flagship database software (the latest long-term support release) is not just an update; the new release integrates robust AI capabilities directly into the core of its architecture, allowing enterprises to harness the full potential of their data using the same database systems already trusted with their most critical data. However, AI is only part of the picture, as the new release arrives with over 300 new features.

Oracle recently held an event for industry analysts. Juan Loaiza, Oracle's executive vice president of mission-critical database technologies, walked analysts through the new release's capabilities. Larry Ellison, Oracle’s founder, chairman and CTO, also spoke at the event, amplifying the significance that the new features bring to Oracle's flagship database.

Deep AI Integration for Smarter Operations

Oracle Database 23ai brings deep integration of AI technologies to enhance the efficiency, intelligence, and security of enterprise applications.

AI Vector Search is one of the standout features, introducing a groundbreaking approach to data querying and analysis. This feature supports a new generation of AI models, particularly LLMs, that allow for generating and storing vector embeddings of various data types such as text, documents, images, videos, and sounds.

AI Vector Search enables semantic search capabilities within the database, allowing users to conduct searches based on the conceptual content of the data rather than just keywords or data values. This significant enhancement revolutionizes how businesses access and analyze information, providing more accurate and contextually relevant search results than ever before.

Bridging the Gap with Large Language Models

Further integrating with the current AI landscape, Oracle Database 23ai enhances its utility by providing seamless integration with large language models. This integration overcomes traditional LLM limitations, such as lack of context and organizational data specificity, by allowing the database to furnish relevant data that enriches the LLM's responses.

Users can pose questions in natural language, which are then processed by LLMs to generate SQL queries executed by the database. This simplifies interactions and makes advanced data analytics accessible to a broader range of users.

Developer-Centric Innovation

With the 23ai release, Oracle takes on the critical role thatdevelopers in application development and data managementplay in the enterprise. It introduces several tools and capabilities designed to simplify and enhance the developer experience.

Among these is JSON Relational Duality Views, which allows developers to leverage both JSON and relational data modeling within the same application. This dual approach eliminates the need to choose between modeling techniques at the onset of projects, thus saving significant development time and resources.

Oracle also delivers enhancements to SQL and the integration of Oracle APEX for rapid application development using JSON data. This aligns with modern web and mobile development standards, catering to the growing demand for flexible, efficient, and scalable application development frameworks.

Mission-Critical Data Handling and Security

Oracle Database 23ai introduces new mission-critical features to ensure high availability, robust performance, and enhanced security when handling sensitive data across diverse industries.

One of the key features is Oracle Globally Distributed Database with RAFT, which enhances database reliability and availability with replication between the physical databases, enabling automatic failover with zero data loss in single digit seconds. This ensurescontinuous operation even in the event of regional disruptions.

Additionally, Oracle True Cache is an in-memory, always consistent, application transparent, high-performance middle-tier cache that significantly boosts database performance for high-speed data access and real-time processing. All Oracle SQL, JSON, and Graph query capabilities are available in True Cache.

For security, Oracle Database 23ai includes an in-database SQL Firewall. This feature protects against unauthorized SQL commands by monitoring and blocking potentially harmful activities, thereby preventing SQL injection attacks, common vectors for data breaches.

And of course, there’s Exadata System Software 24ai, that optimizes the parallelism of vector processing across multiple database servers, increasing the performance and availability of GenAI workloads in the process.

Analyst’s Take

Oracle's recent rollout of Database 23ai marks a pivotal advancement in the convergence of databases and artificial intelligence. This release is particularly significant because it encapsulates Oracle's ambition to streamline and intensify AI integration within enterprise data management systems and modern applications.

It's hard to compare Oracle Database to its competition in delivering scalable and performant database services for mission-critical workloads, as it's an offering that's generally without peer in the industry. While organizations may use the open-source PostgreSQL, IBM's Db2, or even Amazon's Aurora or Redshift for database workloads, none of these solutions arrive with a robust mix of built-in mission-critical capabilities and native AI integration. This converged approach is what continues to set Oracle apart.

The new Oracle Database 23ai release further reinforces Oracle’s leadership in the enterprise database market and sets a new standard for enterprise data management in an AI-driven era. Integrating advanced AI capabilities directly into the database core empowers businesses to operate more intelligently and efficiently, turning data into a pivotal asset for innovation and competitive advantage.

Oracle Database 23ai is a massive step forward in making AI a fundamental component of database management. It offers organizations the tools to leverage AI for improved data insights and decision-making. As businesses continue to navigate their digital transformation journeys, Oracle Database 23ai stands ready to provide the tools they need to succeed in the AI-driven future.

Source: forbes.com

Continue reading

Oracle Globally Distributed Database

Introduction to Oracle Globally Distributed Database

In the ever-evolving landscape of database management, the Oracle Globally Distributed Database stands out as a pioneering solution designed to address the challenges of data distribution across multiple geographic locations. This robust database system is engineered to provide seamless data access, improved performance, and enhanced reliability, making it an ideal choice for enterprises with global operations. In this comprehensive article, we delve into the intricacies of the Oracle Globally Distributed Database, exploring its architecture, benefits, and use cases.

Understanding the Architecture of Oracle Globally Distributed Database

The architecture of the Oracle Globally Distributed Database is meticulously crafted to ensure high availability and scalability. It leverages a combination of Oracle Real Application Clusters (RAC), Oracle Data Guard, and Oracle GoldenGate technologies to create a resilient and efficient data management system.

Oracle Real Application Clusters (RAC)

Oracle RAC enables multiple computers to run Oracle RDBMS software simultaneously, providing high availability and scalability. Each node in the cluster can access the shared database, allowing for load balancing and redundancy. This configuration ensures that even if one node fails, the others continue to operate, maintaining uninterrupted service.

Oracle Data Guard

Oracle Data Guard offers disaster recovery and data protection capabilities. It maintains standby databases that can be quickly activated in the event of a primary database failure. Data Guard ensures data consistency and integrity across multiple sites, making it a critical component of the Oracle Globally Distributed Database.

Oracle GoldenGate

Oracle GoldenGate facilitates real-time data integration and replication across different environments. It supports high-volume transactional data and ensures that changes made in one database are immediately reflected in others. This capability is essential for businesses that require real-time data synchronization across their global operations.

Key Benefits of Oracle Globally Distributed Database

Implementing the Oracle Globally Distributed Database brings numerous advantages to organizations. Here are some of the most significant benefits:

Enhanced Performance and Scalability

By distributing data across multiple geographic locations, the Oracle Globally Distributed Database reduces latency and improves access speeds. Users experience faster query responses and transactions, which is crucial for applications requiring real-time data access. Additionally, the system can scale horizontally by adding more nodes to the cluster, accommodating increasing data loads and user demands.

High Availability and Disaster Recovery

The combination of Oracle RAC and Data Guard ensures that the database remains available even during hardware failures or natural disasters. Standby databases can be activated within minutes, minimizing downtime and ensuring business continuity. This high level of availability is essential for mission-critical applications that cannot afford extended outages.

Data Consistency and Integrity

Oracle GoldenGate ensures that data remains consistent and synchronized across all database instances. This real-time replication mechanism prevents data discrepancies and maintains data integrity, which is vital for businesses that rely on accurate and up-to-date information for decision-making.

Cost Efficiency

By optimizing data distribution and reducing the need for expensive, high-performance hardware at a single location, the Oracle Globally Distributed Database offers cost savings. Organizations can utilize existing infrastructure and resources more effectively, reducing the total cost of ownership.

Use Cases for Oracle Globally Distributed Database

The versatility of the Oracle Globally Distributed Database makes it suitable for a wide range of applications. Here are some notable use cases:

Global E-commerce Platforms

E-commerce companies with a global customer base require fast and reliable access to their databases to ensure a seamless shopping experience. The Oracle Globally Distributed Database provides the performance and reliability needed to handle high transaction volumes and diverse geographic locations, ensuring customers receive timely and accurate information.

Financial Services

Financial institutions must manage vast amounts of data while ensuring compliance with regulations and maintaining data integrity. The Oracle Globally Distributed Database offers the high availability, disaster recovery, and data consistency required to support financial operations, including real-time trading, risk management, and customer transactions.

Healthcare Systems

Healthcare providers need to access patient records and medical data across various locations quickly and securely. The Oracle Globally Distributed Database ensures that healthcare professionals have access to the most up-to-date information, enabling better patient care and efficient management of medical facilities.

Telecommunications

Telecommunications companies operate extensive networks that generate massive amounts of data. The Oracle Globally Distributed Database allows these companies to manage and analyze this data in real time, optimizing network performance and providing better services to customers.

Implementation Best Practices

To maximize the benefits of the Oracle Globally Distributed Database, organizations should follow best practices during implementation:

Assessing Business Requirements

Before deployment, it is essential to understand the specific needs of the business. This includes evaluating the required level of data availability, consistency, and performance. By aligning the database architecture with business goals, organizations can ensure a successful implementation.

Optimizing Network Infrastructure

A robust network infrastructure is critical for the efficient operation of a globally distributed database. Organizations should invest in high-speed, low-latency network connections to facilitate smooth data replication and access across different locations.

Regular Monitoring and Maintenance

Continuous monitoring and maintenance are vital to ensure the optimal performance of the Oracle Globally Distributed Database. Organizations should implement automated monitoring tools to track database health, performance metrics, and potential issues. Regular maintenance, including software updates and hardware upgrades, is also necessary to keep the system running smoothly.

Data Security and Compliance

Ensuring data security and compliance with regulatory requirements is crucial for organizations handling sensitive information. The Oracle Globally Distributed Database provides advanced security features, including encryption, access controls, and auditing. Organizations should implement these features to protect data and maintain compliance with industry standards.

Future Trends and Developments

As technology continues to advance, the Oracle Globally Distributed Database is poised to evolve, offering even greater capabilities and efficiencies. Emerging trends such as machine learning, artificial intelligence, and blockchain are expected to integrate with distributed databases, providing enhanced data analytics, automation, and security. Staying abreast of these developments will enable organizations to leverage new opportunities and maintain a competitive edge.

Conclusion

The Oracle Globally Distributed Database represents a significant advancement in database management, offering unparalleled performance, reliability, and scalability for organizations with global operations. By understanding its architecture, benefits, and use cases, businesses can effectively implement this powerful solution to meet their data management needs. Adopting best practices and staying informed about future trends will ensure that organizations maximize the potential of the Oracle Globally Distributed Database, driving success in an increasingly data-driven world.

Continue reading