Enhanced Resource Usage Tracking in Oracle Autonomous Database on Dedicated Exadata Infrastructure and Cloud@Customer

In the world of data-driven enterprises, Oracle Autonomous Database on Dedicated Exadata Infrastructure and Cloud@Customer (ADB-D and ADB-CC) has established itself as a game-changer, offering a fully automated service that simplifies the development and deployment of application workloads. While it provides users with a user-friendly experience and advanced features like compute auto-scaling, RAC, and Autonomous Data Guard, there's a critical aspect that operates behind the scenes: automated resource management.

This complex mechanism often remains hidden from users, sometimes leading to a sub-optimal understanding of how some of the features work or how the Exadata resources are utilized. In response to this, ADB-D is introducing Enhanced Resource Usage Tracking. This feature enables customers to gain granular insights into resource consumption, empowering them to optimize capacity utilization effectively.

Understanding the Resources

Before going deeper into how Enhanced Resource Usage Tracking improves capacity planning, let's get acquainted with the critical resources within an Autonomous Exadata VM Cluster:

• CPU Count per VM or Node: This denotes the CPUs allocated to the cluster.
• Maximum Number of Autonomous Container Databases: This influences the local storage reservation and dictates how many Autonomous Container Databases you can create in the cluster.
• Database Memory per CPU (GB): The memory allocated per CPU within the cluster.
• Database Storage (TB): The data storage allocated for Autonomous Databases within the cluster.

Memory is always allocated in relation to CPU, so in this blog we’ll focus on enhanced usage tracking for CPU, Number of Autonomous Container Databases and Database Storage. Let's dive into these key aspects and their enhancements.

Enhanced CPU Resource Management

Understanding CPU components provides users with valuable insights into how CPU resources are allocated and managed within the Autonomous Exadata VM Cluster. Reserved CPUs play a vital role in supporting various operations, including auto-scaling and VM failover, while reclaimable CPUs offer opportunities for efficient resource utilization.

Enhanced Resource Usage Tracking now helps you grasp this thoroughly by providing these metrics:

• Total CPUs: The CPUs allocated to the Autonomous Exadata VM Cluster.
• Available CPUs: CPUs available for creating or scaling Autonomous Databases.
• Provisioned CPUs: Total CPUs allocated to all Autonomous Databases.
• Reserved CPUs: CPUs reserved for auto-scaling, failover, and life-cycle management of empty Autonomous Container Databases.
• Reclaimable CPUs: CPUs that can be reclaimed after Autonomous Databases are terminated or scaled down.

Let's look at the resource usage tracking enhancements that allow you to better track your CPU usage across different OCI console views.

Autonomous Exadata VM Cluster list view

Navigate to "Navigation Menu >> Oracle Database >> Autonomous Dedicated Infrastructure >> Autonomous Exadata VM Cluster" and the list view will show the Used CPU percentage along with Available CPU and Total CPU as a color-coded bar chart. The Bar chart shows green for usage less than 70%, yellow for 70-90% and red for above 90%.

Autonomous Exadata VM Cluster details page

Select the Autonomous Exadata VM Cluster in the list view to open the details page. The resource allocation section will show the same color-coded bar chart for CPU usage.

Autonomous Exadata VM Cluster resource usage advanced view

Select the "View details" button to get the resource usage advanced view. CPU section is broken down into two charts. The doughnut chart shows the breakdown of all the CPU components for the Autonomous Exadata VM Cluster. The bar chart on the right gives the breakdown of all the CPU components for each Autonomous Virtual Machine. This view helps you understand which Autonomous Exadata VM Clusters have excess reserved or reclaimable CPUs and take necessary action to optimize capacity utilization.

Autonomous Container Database details view

The Autonomous Container Database details page will also show all the CPU components in the Resource allocation section. "View Details" will show the resource usage advanced view.

Autonomous Container Database resource usage advanced view

The advanced view will show the breakdown of each CPU component across all the VMs in graphical format.

Enhanced Autonomous Container Database Usage Tracking

Enhanced resource management also extends to the Autonomous Container Databases within an Autonomous Exadata VM Cluster. These metrics allow users to effectively manage their ACDs and optimize resource allocation based on their needs:

• Maximum Number of ACDs: The maximum number of ACDs a user can create in a cluster.
• Provisionable ACDs: The number of ACDs that can be created within the cluster.
• Provisioned ACDs: The number of ACDs that have been created within the cluster.
• Non-Provisionable ACDs: ACDs that cannot be created due to a shortage of available CPUs in the cluster.

Autonomous Database service allows you to set the maximum number of Autonomous Container Databases you want to create in your Autonomous Exadata VM Cluster and based on this value the service will reserve the local storage needed. You need local storage and a minimum of two OCPUs per VM to create an Autonomous Container Database. While the local storage on the Autonomous Exadata VM Cluster is reserved for your Autonomous Container Databases, the CPU is not. If you use all your available CPUs to create Autonomous Databases, then you will not be able to create additional Autonomous Container Databases in that cluster.

You can track the Autonomous Container Database usage on the Autonomous Exadata VM Cluster resource usage advanced view. The doughnut chart shows the maximum value at the center and the remaining components on the outer ring.

Enhanced Database Storage insights

Understanding database storage is crucial for effective resource management. Enhanced Resource Usage Tracking is now expanded to provide these metrics:

• Exadata Storage (in TBs): The total storage allocated to an Autonomous Exadata VM Cluster, including user data storage, recovery area, and internal database service storage.
• Autonomous Database Storage (in TBs): Data storage specifically allocated for Autonomous Databases within the Autonomous Exadata VM Cluster, including user data storage and the recovery area.
• Available Database Storage (in TBs): The storage space available for creating or scaling up Autonomous Databases.
• Used Database Storage (in TBs): The storage currently in use by Autonomous Databases within the Autonomous Exadata VM Cluster.

These metrics offer a comprehensive view of how storage resources are allocated and utilized. It's important to note that ADB-D allows storage overprovisioning, allowing the sum of allocated storage for all Autonomous Databases in the cluster to exceed the total allocated database storage for the Autonomous Exadata VM Cluster. This flexibility enables efficient database management. Let’s look at how to tap into this.

Autonomous Exadata VM Cluster list view

Navigate to "Navigation Menu >> Oracle Database >> Autonomous Dedicated Infrastructure >> Autonomous Exadata VM Cluster" and the list view will show the Used Database Storage percentage along with Available Database Storage and Total Database Storage as a color-coded bar chart. The Bar chart shows green for usage less than 70%, yellow for 70-90% and red for above 90%.

Autonomous Exadata VM Cluster details page

Select the Autonomous Exadata VM Cluster in the list view to open the details page. The resource allocation section will show the same color-coded bar chart for Database Storage.

Autonomous Exadata VM Cluster resource usage advanced view

Select the "View details" button to get the resource usage advanced view. The advanced view gives you info about all the resources on the cluster in the graphical or tabular format. Database storage is shown as a doughnut chart with the total Autonomous Database Storage in the center. The rings are composed of available and used database storage.

This covers all the resource usage tracking enhancements in this release. However, it doesn't end here - we are constantly working on enhancing user experience and there is a lot to come. Keep an eye on this space for all future announcements!

Source: oracle.com

Continue reading

Unveiling the Power of Oracle Globally Distributed Database: Oracle Database 23c Advancements

In the realm of database technology, Oracle Database 23c marks a transformative era by ushering in a wave of advancements. Among these advancements, a standout focus lies on augmenting the capabilities of the Oracle Globally Distributed Database. As we approach this release, our attention centers on the integration of pioneering features tailored for distributed data environments. This blog serves as a preview, shedding light on the exciting enhancements Oracle Database 23c brings to the Oracle Globally Distributed Database. These new features are poised not only to address challenges but also to reshape the landscape of distributed database operations, bringing forth heightened performance and unparalleled efficiency. As we delve into the specifics of these transformative features, Oracle Database 23c emerges as a pivotal milestone, empowering businesses with tools to harness the full potential of distributed data scenarios. This blog sets the stage for exploring these enhancements, offering insights into how Oracle Database 23c elevates the capabilities of the Oracle Globally Distributed Database, propelling it into a new era of excellence.

New Features: Oracle Globally Distributed Database and 23c

1. Raft Replication

One of the notable features introduced in Database version 23c is Raft replication (Consensus based replication protocol), offering automatic configuration of replication across all shards. Raft replication is integrated and transparent to applications. Raft replication automatically reconfigures replication in case of shard host failures or when shards are added or removed from the sharded database. The system declaratively configures the replication factor to help maintain a specified number of replicas available at all times. In the event of a node failure, failover is swift, and all nodes remain active. Crucially, this feature encompasses an automatic sub-second failover mechanism, reinforcing data integrity and operational continuity. This capability suits organizations requiring a highly available and scalable database system.

Ensuring Data Integrity and Seamless Operations

Raft Replication brings streamlined management and heightened availability to distributed database environments. This proves pivotal in scenarios where high availability and data integrity are paramount. With rapid fail-over and consistent data across nodes, Raft Replication empowers organizations to deliver optimal user experiences and meet stringent SLAs. This benefit is especially crucial in critical applications where uptime and data consistency directly influence business success.

Raft Replication

2. Directory-Based Sharding Method

Directory-based sharding introduces a versatile user-defined approach, where the location of data records associated with a sharding key is specified dynamically at the time of insert based on user preferences. The process involves storing key location information within a directory, housing an extensive collection of key values, often numbering in the hundreds of thousands. This technique not only facilitates the dynamic movement of individual key values between locations but also allows for efficient bulk movements. The capability to adaptively shift key values offers a high degree of customization, enhancing data management, scalability, and load balancing across databases or clusters.

Key Value Grouping for Enhanced Data Organization

Directory-based sharding is useful when you need to group certain key values together into the same location or chunk for affinity purposes, and when needed this group can be moved together in an efficient manner

An example is a social network application, where grouping together customers who often exchange messages on the same shard minimizes the cross-shard traffic. The grouping must be preserved during re-sharding when data is moved between shards. On the other hand, if a member of a group starts communicating more with members of another group, their data must be moved to the appropriate group with minimal impact on the application.

3. Automatic Data Move on Sharding Key Update

Automatic data move on sharding key update is a potent attribute within Oracle Globally Distributed Database that streamlines data management within sharded tables. Sometimes a sharding key value needs updating. The previous solution was to delete the data associated with the old key value and re-insert it with a new key value. The goal of this enhancement is to allow row movement both within a shard and between shards.

When the sharding key value on a particular row of a sharded table is updated, Now moving the data to a new location is handled by Oracle Globally Distributed Database, whether it is in a different partition on the same shard or on a different shard.

This feature provides you with the flexibility to update the sharding key without worrying about the destination of the records, because this feature allows Oracle Globally Distributed Database to take care of the row movement regardless of the row destination, which could be on a different shard.

Streamlined Data Updates

In a global e-commerce platform with sharded tables, automatic data movement on sharding key updates helps ensure seamless transitions. For instance, when customers relocate, their order history effortlessly shifts to the new region's shard, eliminating manual intervention. This dynamic reallocation empowers businesses with efficient sharded table management, maintaining data integrity and uninterrupted user services amidst changes.

4. Fine-Grained Refresh Rate Control for Duplicated Tables

Fine-grained refresh rate control for duplicated tables is a powerful feature that enables refresh rate control for individual duplicated tables. This feature allows configuring varying refresh intervals during table creation or via the ALTER TABLE statement. By tailoring refresh rates to data change frequency, resource utilization optimization is achieved. This capability enhances system performance and efficiency by customizing refresh rates per duplicated table.

Customized Data Refresh

In complex data environments, where data change rates vary across tables, fine-grained refresh rate control proves indispensable. Imagine a sales database with high-frequency transaction tables and reference tables that change less often. With this feature, high-transaction tables can be set to refresh more frequently, allowing real-time insights. Simultaneously, reference tables refresh less often, conserving resources. This customization maximizes efficiency, streamlining resource allocation, and bolstering system performance.

5. Synchronous Duplicated Tables

The introduction of synchronous duplicated tables offers a robust mechanism for synchronizing duplicated tables across all shards. This feature introduces a new kind of duplicated table that is synchronized on the shards ‘on-commit’ on the shard catalog. The synchronization occurs when active transactions executing Data Manipulation Language (DML) operations on the duplicated tables in the shard catalog are committed. This feature provides impeccable data consistency and real-time synchronization across all shards, underscoring a heightened level of data integrity.

Ensuring Data Consistency in Real-time

In data-intensive environments, like financial systems, the synchronous duplicated table feature plays a crucial role. As transactions span multiple shards, this feature provides instant synchronization of duplicated table data across all shards upon transaction commitment. Financial data remains uniformly accurate, reducing disparities and delivering real-time insights. Particularly vital in precision-dependent sectors, this feature enhances data integrity and reliability in distributed systems.

6. Sharded Database Coordinated Backup and Restore Enhancements

Coordinated backup and restore functionality in Oracle Globally Distributed Database has been extended to include the following:

• Enhanced error handling and diagnosis for backup jobs
• Improved automation of sharded database restore
• Support for running RMAN commands from GDSCTL
• Support for using different RMAN recovery catalogs for different shards
• Encryption of backup sets
• Support for additional backup destinations: Amazon S3, Oracle Object Storage, and ZDLRA

The benefits of this functionality are:

• Easily diagnose problems in backup jobs
• Backups sets can be encrypted so that the data is secure
• Support for additional destinations other than on-disk storage
• Support for different RMAN catalogs and destinations to abide by data residency requirements

This feature enhances diagnosability and automation in backup and restore jobs while securing data with encryption and supporting additional backup destinations.

Robust Data Protection and Recovery

Imagine a scenario where a sharded e-commerce platform faces a catastrophic data loss event. By utilizing the enhanced coordinated backup and restore functionality, the system gains superior error handling, automated restoration processes, and broader backup destination options such as Amazon S3 and Oracle Object Storage. Additionally, encrypted backup sets enhance data security. This feature helps ensure swift diagnosis of backup issues, safeguarded data integrity through encryption, and efficient data restoration for minimal downtime and maintaining operational resilience.

7. Parallel Cross-Shard DML Support

The Oracle Globally Distributed Database query coordinator can run cross-shard updates and inserts in parallel on multiple shards.

This feature improves cross-shard DML performance by running updates and inserts in parallel rather than serially.

Online Retail Inventory Management

In an online retail business with a globally distributed database, Oracle's Parallel Cross-Shard DML Support plays a crucial role in managing inventory. When restocking products or updating prices across multiple geographical regions, the feature allows simultaneous updates and inserts into different shard locations. This means that inventory data remains synchronized, prices are adjusted in real-time, and customer orders are processed efficiently, providing a seamless shopping experience for customers while optimizing inventory management for the retailer.

8. Automatic Parallel Direct Path Load Using SQL*Loader

SQL*Loader enables direct data loading into the database shards for high speed data ingest. SQL*Loader can load data faster and easier into Oracle Database with automatic parallelism and more efficient data storage.

With this release, SQL*Loader client can automatically start a parallel direct path load for data without dividing the data into separate files and starting multiple SQL*Loader clients. Instead of preparing your tables manually for parallel loads and setting the PARALLEL parameter, you can perform the same task automatically by running SQL*Loader with just one command, setting the degree of parallelism using the DEGREE_OF_PARALLELISM parameter, and setting DIRECT=TRUE.

High-Volume Data Ingestion for Financial Analytics

In the context of a financial services organization, managing vast volumes of market data from various sources is paramount for timely and accurate analytics. With the Automatic Parallel Direct Path Load feature using SQL*Loader, the organization can seamlessly ingest massive datasets into its Oracle Globally Distributed Database. Instead of labor-intensive manual preparation, SQL*Loader simplifies the process and an organization achieves efficient, high-speed data ingestion for real-time financial analysis, enabling informed decision-making and competitive advantage.

To conclude, Oracle Database 23c brings groundbreaking advancements to the world of database technology. These innovations, with a keen focus on optimizing the Oracle Globally Distributed Database, are set to revolutionize the world of distributed database operations.

From the dynamic capabilities of Raft Replication to the agility of Directory-Based Sharding, these features redefine how businesses manage data across regions. Automatic Data Move on Sharding Key Update provides seamless transitions in evolving scenarios, while Fine-Grained Refresh Rate Control optimizes resource utilization. The Synchronous Duplicated Tables' near real-time data consistency enhances reliability. Coordinated Backup and Restore Enhancements reinforce data protection, while Parallel Cross-Shard DML Support revolutionizes cross-shard data management by allowing parallel updates and inserts, boosting efficiency. Additionally, Automatic Parallel Direct Path Load Using SQL*Loader simplifies high-volume data ingestion, benefitting financial analytics and decision-making processes for organizations dealing with vast market data.

Oracle Database 23c doesn't just mark an upgrade; it signifies a new era where these features empower businesses to embrace the potential of distributed data scenarios, all underpinned by the reliability of the Oracle Globally Distributed Database.

Source: oracle.com

Continue reading

Oracle Sharding: Enterprise-Grade Distributed Database

Introduction

Distributed databases are becoming increasingly popular and significant due to their ability to handle large and complex data sets, their scalability and flexibility, and their fit for contemporary application designs and architectures. As organizations continue to adopt cloud-based solutions and microservices-based architectures, the demand for distributed databases is expected to rise. Businesses are continuously searching for new and innovative database solutions that enable horizontal scalability and ensure data durability and high availability while adopting more flexible strategies.

Scaling databases is necessary to handle transactional volume and ensure efficient performance. Moreover, as the business world becomes increasingly global, it's important to consider geographic scaling, so services can be tailored to customers located in different parts of the world.

Resilience is also a vital factor to consider when scaling databases, as businesses need to ensure they can survive the failure of an entire AZ/AD (Availability zone/Availability Domain) or cloud provider.

The growing demand for distributed databases, as well as the need for horizontal and geographic scaling, is driving businesses to adopt next-generation methodologies like Oracle Sharding to ensure efficient performance and resilience while meeting modern consumer expectations.

Oracle Sharding is a data distribution system that provides advanced ways to partition the data across multiple servers, or shards, to deliver exceptional performance, availability, and scalability.

The purpose of this blog is to discuss the concepts of Oracle Sharding, its development by Oracle to provide the greatest advantages to its users, and the different ways in which Oracle Sharding can be applied.

The Emergence of Oracle Sharding

The exponential growth of data has made it difficult for enterprises to manage and process it using traditional database systems, which led to the emergence of distributed database solutions.

Sharding is a key technique in distributed databases that allows data to be horizontally partitioned across multiple servers or shards. But, manual sharding can be a daunting task for any organization looking to scale their database. From deciding how to break up the data, to reworking application logic, to handling operational tasks, manually sharding a database can be a time-consuming and complex process that diverts precious engineering resources away from other important projects like developing new features.

Managing and operating a manually sharded database also adds complexity to management and operations tasks, such as schema modifications, backups, analytical operations and software updates. Furthermore, as companies continue to scale up, they will have to redo much of the work already done to rebalance the database, adding even more engineering hours to the already substantial cost.

This is where Oracle Sharding comes in. Oracle Sharding is a powerful and automated solution that allows organizations to horizontally scale their databases without the added complexity of manual sharding.

Oracle Sharding is an Oracle Database data distribution feature that was created to address the problems caused by the complexity and volume of data that are becoming more and more prevalent in the modern IT landscape. With Oracle Sharding, data is automatically distributed across multiple nodes, while still allowing the application to treat the database as a single instance.

Oracle Sharding builds on the generic sharding concept and extends it to offer an enterprise-grade distributed database solution that can handle massive amounts of data with ease. With Oracle Sharding, businesses can achieve higher performance and availability by distributing the data across multiple shards or servers that can be located in different regions. It ensures that data replication is automatically configured and deployed when the sharded database is created to provide high availability and disaster recovery.

Oracle Sharding provides a transparent view of the sharded database to applications, making it easy to use and deploy, while also offering robust management capabilities to database administrators. Oracle Sharding has made it possible for organizations to handle and process data on a scale that was previously impossible, while also ensuring high levels of availability and performance.

Oracle Sharding – Overview

Key Features:

• Oracle Sharding automates the distribution and placement of data on specific shards, eliminating the need for manual data preparation and saving time.
• Oracle Sharding allows the addition or removal of shards and data rebalancing without incurring any downtime or data loss.
• Oracle Sharding allows centralized management and monitoring of sharded databases as a single logical database, with the shard catalog playing a vital role. This feature supports automated shard deployment and multi-shard queries as well, enabling users to perform critical tasks like configuring shards, ensuring high availability, improving security, applying patches, and monitoring the system without needing to handle complex application code changes.
• Oracle Sharding is natively supported by Oracle Database client drivers (JDBC, OCI, UCP, ODP.NET) and allows automatic request routing to shards, automated routing of multi-shard queries to the query co-ordinator, automated failover to standbys, and automatic sharding key identification (Oracle Database 21C JDBC).
• A sharded database protects against unplanned outages and provides mechanisms for online planned operations, allowing applications to meet stringent availability SLA.
• Oracle Database 21c allows data and redo to be stored in local persistent memory, enabling faster SQL execution by bypassing traditional disk storage.

 

Stated below are some of the benefits of Oracle Sharding:

Linear Scalability

Oracle Sharding removes performance bottlenecks and allows for linear scaling of performance and capacity by adding shards.

Extreme Availability and Fault Isolation

Oracle Sharding is a shared-nothing infrastructure that eliminates single points of failure and provides strong fault isolation. The failure or slowness of one shard has no effect on the performance or availability of other shards.

Geographical Distribution of Data

Oracle Sharding allows specific data to be stored close to its consumers while also meeting regulatory requirements when data must be located in a specific jurisdiction.

Rolling Upgrades

Applying configuration changes to one shard at a time does not affect the other shards and allows administrators to test changes on a small subset of data before making broader updates.

Source: oracle.com

Continue reading

Materialized View Concurrent Refreshes in Oracle Database 23c

In previous releases on-commit materialized view refreshes were serialized. If multiple session triggered an on-commit refresh of the same materialized view, they were performed one at a time. This could impact performance on busy tables. In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.

◉ Setup

The example in this article requires the following setup.

Create and populate two tables with orders and order lines data.

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

drop table if exists order_lines purge;

drop table if exists orders purge;

create table orders (

  id            number(10),

  created_date  date,

  constraint orders_pk primary key (id)

);

insert /*+ append */ into orders

select level as id,

       trunc(sysdate - dbms_random.value(0,366)) as created_date

from   dual connect by level <= 1000;

commit;

create table order_lines (

  id            number(10),

  order_id      number(10),

  line_qty      number(5),

  total_value   number(10,2),

  created_date  date,

  constraint order_lines_pk primary key (id),

  constraint ol_o_fk foreign key (order_id) references orders(id)

);

create index ol_o_fk_i on order_lines(order_id);

insert /*+ append */ into order_lines

select level as id,

       trunc(dbms_random.value(1,1000)) as order_id,

       trunc(dbms_random.value(1,20)) as line_qty,

       round(dbms_random.value(1,1000),2) as total_value,

       trunc(sysdate - dbms_random.value(0,366)) as created_date

from   dual connect by level <= 100000;

commit;

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

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

Create materialized logs on the two tables to enable fast refreshes.

drop materialized view log if exists on orders;

create materialized view log on orders

with rowid, sequence(id, created_date)

including new values;

drop materialized view log if exists on order_lines;

create materialized view log on order_lines

with rowid, sequence(order_id, line_qty, total_value)

including new values;

◉ Concurrent Refreshes

In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.

drop materialized view if exists order_summary_rtmv;

create materialized view order_summary_rtmv

refresh fast on commit

enable concurrent refresh

as

select order_id,

       sum(line_qty) as sum_line_qty,

       sum(total_value) as sum_total_value,

       count(*) as row_count

from   order_lines

group by order_id;

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

We can see concurrent refreshes are enabled by displaying the CONCURRENT_REFRESH_ENABLED column in the USER_MVIEWS view.

column mview_name format a30

column concurrent_refresh_enabled format a30

select mview_name,

       concurrent_refresh_enabled

from   user_mviews;

MVIEW_NAME                          CONCURRENT_REFRESH_ENABLED

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

ORDER_SUMMARY_RTMV             Y

SQL>

Concurrent refreshes will only happen if there are no overlaps in the materialized view rows updated by each session.

Source: oracle-base.com

Continue reading

Hybrid Read-Only Mode for PDBs in Oracle Database 23c

Oracle 23c allows us to open PDBs in hybrid read-only mode. This allows common users to work in read-write mode, while local users and common application users are restricted to read-only mode.

Why use hybrid read-only mode? It allows application administrators connected as common users to patch applications without risk of local users blocking the maintenance.

◉ Enable Hybrid Read-Only Mode

We connect to the root container and start our PDB in hybrid read-only mode.

conn / as sysdba

alter pluggable database freepdb1 close immediate;

alter pluggable database freepdb1 open hybrid read only;

The open mode displayed will depend on the type of user issuing the query. We create a common and local user to test this. A common application user would act like a local user in this context.

conn / as sysdba

create user c##testdba identified by testdba container=all;

grant dba to c##testdba container=all;

alter session set container=freepdb1;

create user testdba identified by testdba;

grant dba to testdba;

We connect using the common user and we see the following output from the V$PDBS and V$CONTAINER_TOPOLOGY views. The PDB is showing an open mode of read-write in the V$PDBS view.

conn c##testdba/testdba@//localhost:1521/freepdb1

column name format a10

select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

FREEPDB1   READ WRITE

SQL>

column con_name format a10

column is_hybrid_read_only format a20

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>

This time we connect to the local user and repeat the queries. This time the V$PDBS view displays an open mode of read-only.

conn testdba/testdba@//localhost:1521/freepdb1

column name format a10

select name, open_mode from v$pdbs;

NAME       OPEN_MODE

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

FREEPDB1   READ ONLY

SQL>

column con_name format a10

column is_hybrid_read_only format a20

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>

Notice the V$CONTAINER_TOPOLOGY view output remains unchanged.

◉ Test Common and Local Users

We've seen how the different types of users affect the perceived open mode of the database. Let's see how this affects normal use.

We connect to the PDB with the common user and perform some DDL and DML. All operations succeed as we would expect for a read-write database.

SQL> conn c##testdba/testdba@//localhost:1521/freepdb1

Connected.

SQL> drop user if exists testuser2 cascade;

User dropped.

SQL> create user testuser2 identified by testuser2 quota unlimited on users;

User created.

SQL> grant db_developer_role to testuser2;

Grant succeeded.

SQL>create table testuser2.t1 (id  number);

Table created.

SQL> insert into testuser2.t1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>

We connect to the PDB with the local user and perform some DDL and DML. All operations fail except read-only operations.

SQL> conn testdba/testdba@//localhost:1521/freepdb1

Connected.

SQL> drop user if exists testuser2 cascade;

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> create user testuser2 identified by testuser2 quota unlimited on users;

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> grant db_developer_role to testuser2;

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> create table testuser2.t1 (id  number);

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> insert into testuser2.t1 values (1);

*

ERROR at line 1:

ORA-16000: Attempting to modify database or pluggable database that is open for

read-only access.

SQL> select * from testuser2.t1;

        ID

----------

         1

SQL>

◉ Enable Read-Write Mode

We can switch back to read-write or read-only mode at any time.

conn / as sysdba

alter pluggable database freepdb1 close immediate;

alter pluggable database freepdb1 open read write;

Source: oracle.com

Continue reading

Demystifying JSON Databases: A Guide to Autonomous Data Management

In the ever-evolving world of data management, one term that has gained immense prominence in recent years is JSON databases. JSON, which stands for "JavaScript Object Notation," is a flexible and powerful data format that has transformed the way we store, retrieve, and manage data. In this comprehensive guide, we will delve deep into the realm of JSON databases, demystifying their concepts and applications while shedding light on their role in autonomous data management.

The Rise of JSON Databases

What Is JSON?

JSON, often pronounced as "jay-sawn," is a lightweight data interchange format that is easy for humans to read and write and easy for machines to parse and generate. Its simplicity and readability make it an ideal choice for data storage and transmission, especially in web applications.

JSON data is structured as a collection of key-value pairs, similar to how objects are defined in JavaScript. This structure provides a highly organized way to represent and store data. It has gained popularity not only in web development but also in the realm of databases due to its simplicity and flexibility.

The Need for JSON Databases

Traditionally, relational databases have been the go-to choice for data storage. While they are effective for structured data, they can be cumbersome when dealing with unstructured or semi-structured data, which is becoming increasingly common in the digital age. This is where JSON databases come into play.

JSON databases, also known as NoSQL databases, are designed to handle data that doesn't fit neatly into tables and rows. They can store data in a more dynamic and flexible manner, making them a perfect fit for modern applications and data management needs. With JSON databases, you can store data with varying structures and retrieve it efficiently, making them an excellent choice for agile and dynamic systems.

Understanding JSON Databases

JSON Structure

JSON databases are built on the foundation of JSON data. Understanding the basic structure of JSON is key to comprehending how JSON databases work. JSON data is organized into objects and arrays, providing a hierarchical structure that can represent complex data relationships.

◉ Objects: In JSON, objects are enclosed in curly braces {} and consist of key-value pairs. Each key is a string, and the values can be strings, numbers, arrays, objects, booleans, or null.

Example:

{

    "name": "John Doe",

    "age": 30,

    "isStudent": false,

    "courses": ["Math", "Science"]

}

◉ Arrays: Arrays in JSON are ordered lists of values enclosed in square brackets []. These values can be of any data type, including objects and other arrays.

Example:

["Apple", "Banana", "Cherry"]

JSON Databases in Action

JSON databases employ these basic JSON structures to store data. Instead of predefined tables, JSON databases allow you to create collections or documents to store data in a flexible and scalable manner. Let's explore the key features of JSON databases:

1. Schema-less

Unlike traditional relational databases, JSON databases are schema-less. This means you don't need to define a rigid structure for your data in advance. You can simply start storing data as it comes, making them ideal for projects where the data structure may evolve over time.

2. High Performance

JSON databases are known for their high performance, especially when it comes to read and write operations. Their ability to handle large volumes of data efficiently makes them a preferred choice for applications with heavy data processing requirements.

3. Scalability

Scalability is a fundamental feature of JSON databases. They can easily scale horizontally, accommodating growing datasets without a hitch. As your application or website expands, you won't need to worry about data storage limitations.

4. Query Flexibility

JSON databases offer a variety of query options to retrieve data efficiently. You can use a combination of keys, values, and nested structures to find and manipulate data as needed.

Autonomous Data Management with JSON Databases

The autonomous data management capabilities of JSON databases are truly revolutionary. They empower businesses to automate various data-related tasks, ensuring data accuracy, availability, and security. Let's explore how JSON databases contribute to autonomous data management:

Data Integration

JSON databases simplify data integration by allowing you to store data in its natural format. This eliminates the need for complex transformation processes, reducing the chances of data errors.

Real-time Data Updates

In an age where real-time data is invaluable, JSON databases shine. They can handle real-time updates seamlessly, ensuring that the data you rely on is always up-to-date.

Data Security

JSON databases offer robust security features, allowing you to define access controls and encryption methods to protect your data. This is crucial for businesses dealing with sensitive information.

Data Availability

With JSON databases, data availability is no longer a concern. They can replicate data across multiple servers, providing high availability and fault tolerance, even in the face of hardware failures.

Conclusion

In the era of digital transformation, JSON databases have emerged as a game-changer in data management. Their flexible structure, high performance, scalability, and autonomous data management capabilities make them an ideal choice for modern businesses and applications. As we continue to witness the evolution of data management, JSON databases stand as a testament to innovation and adaptability.

Continue reading

Oracle Analytics Server 2023 is available

We are proud to announce the general availability of the Oracle Analytics Server 2023 release. Oracle Analytics Server 2023 is a continued evolution of the Oracle Analytics platform. It adds significant, innovative capabilities that enhance the analytics experience of all user personas from business users, analysts, and business data scientists to data engineers and administrators.

Complete Analytics Platform

The Oracle Analytics platform provides the capabilities required to address the entire analytics process, including data ingestion and modeling, data preparation and enrichment, and visualization and collaboration without compromising security and governance. Embedded machine learning and natural language processing technologies help increase productivity by augmenting the analytic experience for all users in an organization. Oracle Analytics Server is a customer-managed deployment of the Oracle Analytics platform that can be customized by the organization. Oracle Analytics Server can be implemented either on-premises or on cloud infrastructure such as Oracle Cloud Infrastructure (OCI), Microsoft Azure or AWS . For OCI the deployment can be streamlined using an OCI Marketplace deployment - read more about OAS on OCI Marketplace. For Oracle Business Intelligence Enterprise Edition (OBIEE) customers that are inclined to continue to manage their own deployments, Oracle Analytics Server (OAS) is the path to a modern business intelligence (BI) and analytics experience.

Highlighted New Features

Parameters

Parameters is a powerful new feature that allows an analyst to create user-defined variables to store and manage reusable values within a workbook. An analyst can use parameters as filter controls, in a visualization's properties, in logical SQL expressions, expression filters, calculations, and in data actions and workbook navigation.

Enhanced authoring experience

There are several new capabilities that allow the workbook author to create more powerful and visually appealing content. New features include an improved tile visualization that allows for multiple metrics and better layout control, overlay charts for multilayer visualizations, finer control over consumer interactions, more capabilities for conditional formatting, a slider dashboard filter, ability to control bubble sizes on maps, improved experience for the grammar and property panels, ability to configure the grid spacing between visualizations on the canvas, and many others.

Multi-table datasets with files

For the data engineer, the ability to define datasets with multiple tables has been enhanced with the ability to add file data. When the user creates a new dataset, they can now add tables from relational sources, local subject areas, and files. Each new entity created in datasets can be based on any of these data sources. This enables the user to add multiple files to one dataset where each file becomes a table, and to define joins between tables within a dataset. Files from Google Drive and Dropbox are also supported.

New Oracle Cloud Infrastructure integrations

The business data scientist can now take advantage of the advanced analytic capabilities available in Oracle Cloud Infrastructure including Oracle Functions and Oracle Vision AI.

Oracle Functions integrations allow allows registration of an API call or custom script as a function. For example, if a user wants to execute a data flow that runs a custom Python script they can upload the script to OCI Function-as-a-Service (FaaS) and define a function for it. Oracle Analytics then natively consumes the function, directly invoking the FaaS function from within a data flow both safely and securely.

OCI Vision AI models can also be registered in Oracle Analytics Server and executed directly in data flows using user-supplied images. OCI Vision can perform object detection, image classification, and text recognition on the images and return the results directly as datasets in Oracle Analytics Server.

Enhanced Auto Insights

The ability for Oracle Analytics to automatically analyze datasets and provide insights has been enhanced. Greater rendering performance and additional insights such as metrics forecasting are now available. Auto Insights now recognizes latitude and longitude data types in any dataset, and automatically renders map visualizations as part of suggested insights.

Updated Homepage

The redesigned homepage allows the user to easily customize the organization and display of content to suit their specific preferences. The result is a personalized landing page experience with improved navigation and increased ease of access to the most important content for each user.

Content Management

Administrators can manage Oracle Analytics content from the console. For example, if an employee leaves an organization, the administrator might want to assign ownership of their workbooks and machine learning models to a different employee. The Content Management interface makes this task easy, preventing a loss of productivity.

Oracle Analytics Server 2023 is a continuation of Oracle’s commitment to deliver best-in-class analytics solutions for all customers, both those who operate on-premises and in the cloud.

Source: oracle.com

Continue reading

Span of Control & Manager Assignment – A Dataset based Approach

Organizational structures are evolving year over year and managers are key in defining the structure and understanding the decision flow within any organization. Therefore, obtaining essential managerial assignment information (such as department name, business unit, country, and assignment status) becomes crucial for gaining insights into this pivotal role.

Span of Control is one such essential key performance indicator (KPI) that plays a crucial role in boosting productivity and fostering organizational growth. It represents the number of employees reporting directly to a manager, often depicted as a ratio such as 6:1 (6 employees per 1 manager). By monitoring and adjusting spans, organizations can break down teams, eliminate silos, enhance information flow, and enable quicker decision-making and implementation. Regular analysis of spans ensures the organization's efficiency and effectiveness.

Span of Control is available as a prebuilt metric in Oracle Fusion HCM Analytics. This article explains how to fetch a manager's direct reports count (span) efficiently to create a manager-employee relationship report, which can aid analysts, human resource managers, and leaders to make informed decisions.

Use datasets and workbooks in Fusion Analytics to implement Span of Control:

1. Create a Manager dataset using the HCM workforce core subject area. Select all the columns and attributes required for manager information and save it as Manager dataset.
2. Create an Employee dataset using the HCM workforce core subject area. Choose all the columns and attributes required for employee information and save it as Employee dataset.
3. Create a workbook with these two datasets as sources. Join the two datasets by joining the Employee dataset’s manager person number to the Manager dataset’s person number.

 Create the Manager dataset 

1. Click Create and select Dataset.

2. Select Local Subject Area.

3. Search for Workforce core and select the HCM - Workforce Core subject area.

Click to add the subject area.

4. Click the subject area name at the bottom of the page.

5. Select the required attributes for manager information from the available folders and add those to the Selections.

NOTE: Make sure to choose Date from the Time dimension folder and Headcount measure from the Facts – Workforce Headcount folder.

6. Double-click New Dataset to rename the dataset to DS_Manager.

to 

7. Click Save.

Create the Employee dataset

Repeat the previous steps for employee details and save as the DS_Employee dataset. Add all the columns needed for employee information.

NOTE: Make sure to choose Manager Person Number from the Manager Information folder, Date from the Time dimension folder, and Headcount measure from the Facts – Workforce Headcount folder.

Create the workbook

1. Click Create and select Workbook.

2. Select the DS_Manager dataset as the source and click Add to Workbook.

3. Click the plus (+) icon and add the DS_Employee dataset.

4. In the workbook, click the Data tab at the top of the page to link the datasets.

5. Click the linkage between the datasets to change the default link.

6. In the available list, delete all the default matches and add the new match as shown in the image.

You’re linking the manager information by joining the DS_Manager dataset Person Number to the DS_Employee dataset Manager Person Number.

7. Navigate to the Visualize tab at the top of the page to create the visualizations.

8. Create the Reportees Count (Span) measure in My Calculations as shown in the image.

NOTE: Use the Headcount and Manager Person Number from DS_Employee.

9. Add the columns from the Manager dataset and the Reportees Count from My Calculations.

The Headcount measure from DS_Manager should be present in the selected columns.

10. Add a report-level filter to remove all the managers who have their number of reportees as zero.

NOTE: Make sure to change the grouping, By to Date and Manager Person Number. Having the reportees’ names against the manager isn’t possible, because the pivot report supports only measures and not dimensional attributes.

11. Drag and drop the Date column from DS_Manager to a workbook filter. Select a specific date to get the manager and direct reports information, as on the chosen date.

12. To have employee and manager information together, duplicate this visualization and add the required columns from the DS_Employee dataset.

13. Set the Manager Info report as Use as Filter, to act as a drill down for the selected manager.

This filters the Reportee Info report to display all the reportees of this selected manager.

Call to action

By following these instructions, you can create a Span of Control analysis that can assist your organization's efficiency and effectiveness.

Source: oracle.com

Continue reading