In the past, data was stored in a local server room with limited expansion opportunities. Each server had a specific hardware capability. Upgrades were often costly and technically demanding, resulting in the need to buy new equipment. Later, distributed architectures were created to ensure robustness and resilience, but one way or another, the solution was not so complex and robust. Scalability can partially be achieved by dynamically reacting to the current and expected workload; however, cloud storage and databases provide the technical foundation needed for easy scalability. In terms of Oracle Cloud databases, autonomous management and technologies are a significant milestone.

Automation is now present almost everywhere, whether in smart devices and smartphones, modern cars full of sensors that are partially operated autonomously, or smart homes and cities, including advanced functions associated with Machine Learning (ML), Artificial Intelligence (AI), or the Internet of Things (IoT). Autonomous Databases (ADBs) go even further by providing a complex environment for your data handling, apps, and services to produce effective outcomes, reducing the costs and time required to set parameters, optimize the configuration, and so on.

As a business expands, the amount of data to be handled grows exponentially. It will no longer be sufficient to only cover current valid data. Historical data needs to be stored, manipulated, and evaluated, either in an original form or analytically aggregated in data warehouses, marts, or other analytical structures. As the data quantity grows, it cannot be managed manually by one local machine. It is necessary to ensure availability in an error-prone environment. Thus, additional servers must be employed to serve as backups, standby, and so on. The whole environment needs to be secured and properly interconnected over networks.

Managers and administrators have also realized that putting whole structures in one building is neither suitable nor secure, resulting in the need to rent other server rooms, usually geographically distributed rooms. That’s exactly where the cloud comes into play. The entire administration, securing, distribution, and backup strategies are moved to the cloud environment, so we arrive at the concept of autonomous processing here again.

In this chapter, we’re going to cover the following main topics:

• Oracle Cloud core concepts and the Always Free option
• Defining Oracle ADBs and their types and principles
• Deployment models and database architectures
• Process of database provisioning and connecting
• Database system architecture overview – database and instance levels

Note that the source code can be found in the GitHub repository accessible via this web address: https://github.com/PacktPublishing/Developing-Robust-Date-and-Time-Oriented-Applications-in-Oracle-Cloud/tree/main/chapter%2001.

Alternatively, you can scan the following QR code as well:

Oracle ADBs provide you with complexity, robustness, availability, and security with the following enhancements:

• Self-driving: ADBs reduce the human activity required to provision, secure, monitor, back up and recover, as well as troubleshoot and perform maintenance activities to optimize and tune the database and its overall performance. They strongly reduce the work required by administrators so that they can focus on other tasks, apps, and database optimization strategies. Moreover, ADBs are converged. They can serve any data structures and types – relational, graph, temporal, spatial, streams or object structures, XML, JSON, and so on. Therefore, provisioned databases do not need to be oriented to a single purpose. The Oracle Cloud architecture is based on the Exadata platform, covering dynamic Online Transaction Processing (OLTP) in Autonomous Transaction Processing (ATP) or an analytical support layer, defined in Autonomous Data Warehouses (ADW). A specific type is covered by Oracle Autonomous JSON Database (AJD), which is specialized for NoSQL-style applications that use JavaScript Object Notation (JSON) documents. It is a feature-scoped service for storing, manipulating, and retrieving JSON documents using SQL and Document APIs. JSON is very flexible, allowing us to process schemaless data by offering dynamic reactions to application changes. There’s no need to normalize the content into relational tables. Oracle AJD typically uses Simple Oracle Document Access (SODA) APIs.
• Self-securing: Specific services ensure system protection via firewalls and threat detection. Individual updates and patches are applied without requiring user or administrator intervention, and even with zero downtime. Data always goes through end-to-end encryption. The cloud ensures security at all levels.
• Self-repairing: ADBs are more powerful, robust, and reliable than manually administered local databases. Data images are automatically mirrored and spread across different regions. This automatically protects the system from any physical failure (at the server or data center level) by shifting the workload to different standby databases. The fact that systems are multiplicated allows the system to be upgraded with no downtime.

Oracle Cloud Infrastructure (OCI) extends the original on-premises systems with high-performance computing power running in a cloud environment. The main advantage is elasticity, allowing the system to dynamically reflect the current workload, processing demands, and user activity. In addition, it uses Oracle autonomous services, an integrated security layer, robust functionality, and optimization techniques. OCI improves your performance and processing through autonomous services, easy migration, cost reduction, and performance enhancements.

The following list summarizes the available product categories in OCI:

• Oracle Analytics uses built-in ML and AI to propose a robust solution for the company and offer better decision-making opportunities. It covers Oracle Analytics Cloud, Oracle Big Data Service, Oracle Big Data SQL Cloud Service, Oracle Data Science, OCI Data Flow, and many more.
• The application development environment handles data-driven application development by simplifying the whole development process. It covers the API Gateway service, Blockchain Platform, OCI Data Science, Oracle Digital Assistant, Java functionality, OCI Events Service, Mobile Hub, Oracle MySQL Database Service, and more. Two solutions should be emphasized – Oracle Application Express (APEX) and Visual Builder. These tools provide a complex environment to create web- or mobile-based applications based on SQL, PL/SQL, or JavaScript functionality. Thus, using these tools makes implementation far easier, aided by rapid development. The solution can be created overnight.
• Applied software technologies include AI, blockchain, ML, data science, and digital assistants.
• Compute nodes ensure scalability and overall performance.
• Databases such as ATP, ADW, AJD, Oracle Base Database Service (formerly known as Database Cloud Service) (bare-metal/Virtual Machine (VM)), Exadata Cloud Service, and so on are offered.
• Integration is performed using API Gateway, Oracle GoldenGate, Oracle Data Integrator, OCI Data Integration, and Oracle SOA Cloud Service.
• Observability and management are offered by logging, monitoring, notifications, and other techniques, along with the OCI Resource Manager service.
• Networking and connectivity are managed by the DNS, email delivery, FastConnect, health checks, load balancing, Virtual Cloud Networks (VCNs), and so on.
• Security, identity, and compliance reduce the constant threat risk with security-first design principles, utilizing built-in tenant isolation and least privilege access. There are several defense layers that can be implemented, including Identity and Access Management (IAM), OCI Vault key management, Security Zones, Cloud Guard, Web Application Firewall, Bastion, and Vulnerability Scanning Service. The core element of overall security, however, is the always-on data encryption. Automated security is responsible for reducing complexity, the number of human errors, and costs with automated patching.
• Storage includes Archive Storage, Block Volume, Data Transfer, File Storage, Local NVMe SSD storage, Object Storage, and Storage Gateway.

Oracle Cloud technology is in widespread use across the whole world by both commercial and government entities. In Europe, clouds are located in multiple cities, including Amsterdam, London, Frankfurt, Zürich, and Newport. New locations are still being opened over time, and availability is widespread.

Now that you understand the core properties of Oracle Cloud, it’s time to provision the database and get started with practicing. Oracle offers you the Always Free option with time-unlimited resources for testing and studying purposes. Although it is resource limited, as the name suggests, it’s free. Later on, you can apply for the paid option to extend the functionality and resources if required.

The Always Free option

OCI has launched a significant project to offer cloud services to students and developers for testing and evaluation. The Oracle Cloud Always Free tier is provided to students to test the suitability of their environments. Services are time-unlimited with the following resource limitations:

• 2 ADBs, each limited to 1 OCPU and 20 GB of disk storage
• Compute VMs
• 2 Block Volume storage instances – 100 GB in total
• 10 GB object storage
• 10 GB archive storage

If you are unfamiliar with OCI, the Oracle Cloud Always Free option makes sense. It is implemented inside the Oracle Cloud Free Tier, which comes with a 30-day free trial with $300 of free credits and access to a wide range of Oracle Cloud services during the trial period (containing the Database, Analytics, Compute, and Container Engine for Kubernetes services). This is limited to no more than 8 instances across these services, and up to 5 terabytes (TB) of storage. After the free 30 days are up, it shifts to the Always Free tier.

Scan the following QR code or use the link to get Oracle Cloud Always Free option:

https://www.oracle.com/cloud/free/

The registration is straightforward – a wizard will walk you through it. First, you must specify some parameters: a username, which defines the tenancy, and a password. Note that a credit card must be provided, but it will not be charged. This allows you to dynamically make your account a paid account to take advantage of the multiple features offered. It is also used to make sure that a real person is registering and not a bot.

If you are a student, ask your teachers and representatives to provision a cloud account for you. If the school is part of the Oracle Academy project, the whole registration process is simplified and significantly faster. Moreover, there is no requirement to provide a credit card at all. Oracle Academy (https://academy.oracle.com) is free for schools and universities, offering you many resources, learning materials, and other teaching resources.

Figure 1.1 – Oracle Cloud home screen

Among that, Oracle offers OCI tutorials for free, such as LiveLabs, the OCI Architecture Center, or GitHub repositories:

LiveLabs	OCI Architecture Center	GitHub repositories

Figure 1.1 shows the home screen of the Oracle Cloud environment and highlights its main functionality. As you can see, multiple database types can be provisioned. In the next section, you will get an overview of ATP databases and analytical model covered by ADW.

ADB processing is available only in OCI and Exadata Cloud@Customer environments. There is no on-premises version of an ADB. There are three types of ADBs, which are distinguished by their formats and workload types:

• ATP is used for operational data defined by the short transactions changing the data.
• ADW is analytically oriented by focusing on storing long-term data. It involves a complex data retrieval process.
• AJD provides specialized document-oriented storage and management.

Besides these types, there is also an APEX type built for Oracle APEX application development, which provides the ability to define data-driven, low-code solutions. However, generally, APEX applications can be developed on any type of database.

We will now describe and compare these three main types. Selecting the right type for your workload is performance critical.

ATP handles online transactional data changes. The data structure should be normalized with no data redundancy or anomalies. Values, which can be calculated, are not stored in the system. ATP requires data consistency and integrity, ensured by the transactions that shift the database from one consistent state to another. The aim is to ensure the performance of data manipulation operations – data modifications (insert, update, and delete), as well as data retrieval (select). Thus, the index set must be balanced to serve all these operations.

ADW is a specific database repository that deals with data analysis. The essential operation is the data retrieval process, so overall optimization is focused on the select statement performance, ensured by a huge amount of indexes (B+trees and bitmaps). Data is often denormalized. Precalculated outputs are stored in a column format, compared to the row format used in ATP. There is no focus on data modification, which can, in principle, last any amount of time. The process of analytical processing of large data amounts through complex queries is essential and is the goal of performance optimization.

The main differences between ATP and ADW are summarized in the following table:

Table 1.1 – ATP and ADW summary

The following table shows the resource service priority types. Although, generally, any type can be used for both workload types but in the environment operated in parallel, it is always important to set the operation priorities properly to ensure efficient performance of the online transaction operations. This is because the analytical reports can be too demanding and resource-consuming.

Table 1.2 – Resource service priority

ADW is determined by three priority levels – Low, Medium, and High (as seen in Table 1.1). The Medium and High options support parallelism, while the Low type uses a serial approach. These levels are used for priority definition in reporting and batch processing. ATP can use all priority levels, but the Tp and Tpurgent types are preferred for Transaction Processing (TP). The highest priority is covered by the Tpurgent option.

Finally, AJD is a non-relational database used to store, manage, and obtain data in JSON document format. The structure is not fixed, allowing a variety of data to be stored in each row. There is no normalization strategy.

Considering the historical evolution of cloud migration, it is clear that administrators and managers tended to store all the data locally in their companies. They simply believed that data was secure if it was stored and administered by them and the storage and all the hardware capabilities were physical and visible. However, sooner or later, the hardware could collapse, and data would be lost if the backups were not managed properly. Even if backups were available, a company’s credibility was corrupted if any amount of online data was lost.

On the other hand, local on-premises data management was sometimes required due to various challenging limitations – the cost of service, interoperability, laws (meaning that data could not be stored outside the company), regional availability (cloud repositories were now uniformly distributed across regions), security, integration techniques (some applications were not able to run in the cloud), and so on.

Most of these challenges were solved consecutively using mechanisms that can handle operations and their complexity. By using cloud solutions, almost all the activities related to the infrastructure, hardware, and security are covered by the cloud vendors. You do not need to patch and update your system. It is always available and backups are handled automatically, creating reliable and secure solutions. In a nutshell, cloud companies generally offer these service models:

• Infrastructure as a Service (IaaS) provides a lower abstraction level and supplies the machines (both physical and virtual) with storage capacity, firewalls, network gateways, and workload balancers. Thus, the cloud provides you with the storage, server, network, OS, and overall software tools to run Oracle. You are allowed to bring your own software image and are responsible for application software, maintaining the OS, and installing patches.
• Platform as a Service (PaaS) provides a higher level of abstraction. You, as the customer, are not responsible for administering infrastructure and other cloud resources, such as the OS, database, and so on. PaaS serves you the database with no necessity to install Oracle software or configure its environment.
• Software as a Service (SaaS) offers the highest abstraction level. The user is not responsible for the platform or infrastructure. Applications are located in the cloud, and the whole responsibility is shifted to the vendor. You just pay for the usage.

Now that you have been familiarized with the types of cloud services available, which offer several different levels of abstraction, it is important to remember that from the physical access point of view, either individual cloud resources can be shared or private separate hardware can be provisioned just for you. A database is always private, but the instance resources can be shared by multiple users to optimize resources, as well as costs. To help you understand resource sharing, Oracle has provided four deployment models. The next section will walk you through these deployment models.

Many users and companies still require their data to be stored in their local data center but would like to enjoy the benefits of the cloud’s robustness, stability, and power. To serve the varied requirements of different businesses, four deployment models have been introduced – public, private, hybrid, and community cloud.

A public cloud

The general solution is covered by a public cloud, in which all resources are part of the cloud provider data center, shared by the users. Users do not need to invest in the hardware. They just rent the resources available. Moreover, this ensures the dynamic scalability of individual resources, which can be provisioned at any time on demand, reflecting the workload. The disadvantage is that you do not have local data under your control. Thus, if laws and contracts do not allow you to store data outside of your company, you cannot use this option.

Note that the Oracle database can be run on various cloud providers. It is not strictly limited to Oracle Cloud – for example, Microsoft Azure or Amazon Web Service (AWS) can also be used. In 2022, Oracle and Microsoft announced the general availability of Oracle Database Service for Microsoft Azure. Microsoft Azure services can be directly provisioned, managed, and accessed in OCI. Thanks to this cooperation, users can build new applications (or migrate existing ones) on Azure and connect them to the high-performance, high-availability, managed Oracle Database services on OCI. This is done via the Oracle Azure Interconnect services.

A private cloud

This model provides you with full control over the resources. Data is kept in your local data center, placed on-premises, but you can still use the power of the cloud. Resources are not shared by multiple customers, making data access separation highly scalable and integrated. This is used for mission-critical enterprise systems that require especially high performance. It allows portability between public and private cloud systems.

The hybrid cloud

The hybrid cloud provides an intermediary between private and public clouds by providing a universal solution. Namely, some applications run in the public cloud, but some systems cannot be migrated there. Therefore, they are operated by private cloud systems. A typical example is an application that needs to be run exclusively on an older version of a database system. The Oracle Cloud environment does not support all versions, just the newest ones available.

The community cloud

The community cloud is the fourth type of deployment model, filling the gap between the other categories already covered. Although it is mostly only used in theory, Oracle supports it and it is therefore worth referencing. A community is characterized by a set of companies sharing the same objectives. Cloud infrastructure is provisioned for the whole community and supervised by the manager responsible for the cloud system.

We will take a different view of the data itself, along with the availability and storage of resources, in the following section. Individual resources can be shared, but the benefits of cloud access can also be used in your own data center using a dedicated type of architecture.

Each database system comprises the physical data files forming the database and the instance itself, delimited by the memory structures and background processes. During the session creation process, the client contacts the database listener for interconnection and direct access. The client cannot access the database directly. It is operated by the background processes, stored in the memory, forming the result set or processing the data that has been modified.

There are two types of infrastructure options:

• With a shared deployment, all resources of Exadata Cloud Infrastructure are shared by users. This allows environments to be set up very quickly by provisioning resources and databases. Thus, the storage and instance are shared. Do not be afraid – naturally, data is not shared across users and applications.
• By contrast, a dedicated deployment allows you to separate your applications in a cloud environment in your own dedicated Exadata Cloud Infrastructure. This option is available in customers’ data centers (Exadata Cloud@Customer) or a dedicated public cloud can be used.

To provide an overview of the principles and complexity involved in the Oracle Cloud technology, it is necessary to reference the basic terms related to the Oracle Cloud environment. In the next section, we will introduce the main terms related to Oracle Cloud, especially concerning geographical location, resources, and storage management.

This section will introduce you to the core terms of Oracle Cloud, focusing on regions, availability domains, realms, consoles, tenancies, and compartments. VCNs, instances, and images are also covered. We will look at Object Storage as its form of file storage. The complexity of Oracle Cloud and all its properties are very well summarized in the books listed in the Further reading section at the end of this chapter.

Region

A region is a geographical location from which resources are provided (for example, a VCN).

Availability domains

Each region has at least one availability domain. Each availability domain is independent, isolated from other domains, and fault-tolerant. Thus, configuring multiple availability domains can ensure high availability and failure resistance. Each availability domain contains three fault domains.

Realms

A realm is a logical collection of regions. Each realm is isolated and does not share any data with other realms. A tenancy (which will be explained next) is associated with just one realm and has access to the region belonging to the realm.

Tenancies

A tenancy is a specific cloud repository, usually devoted to an organization or company, and provides secure and isolated storage and processing partitions. You can manage, create, and associate cloud resources and services across a tenancy.

Consoles

The cloud console is a web application providing access and management functions for OCI.

Compartments

A compartment comprises a cloud resource (instances, VCNs, and so on) with specific privileges and quotas. It is a logical unit rather than a physical container. Note that Oracle provides you with a tenancy after registration, which is a root compartment holding and managing all cloud resources provided. Then, you can create a resource categorization tree. Each resource is associated with a compartment by definition. The core principle is based on granting only users the resources necessary for their work and nothing more.

VCNs

A VCN is a virtualized version of a conventional network, including subnets, routers, gateways, and so on. It is located within one region and can spread across multiple availability domains.

Instances

An instance is a compute host running in the cloud. Its main advantage is flexibility. You can utilize sources (physical hardware) on demand to ensure performance, high availability, and robustness and comply with the security rules you have set.

Images

An image is a specific template covering the operating system and other software installed. In addition, Oracle provides you with several virtual hard drives, which can be used in the cloud, such as Oracle Linux, CentOS, Ubuntu, or Windows Server.

Storage management

Storage management is an inevitable part of data processing. Storage is where external database files are present and logs and backups are accessible. A block volume is a virtual hard drive that provides persistent data storage space. It works following similar principles to hard drives in ordinary computers. It is possible to attach or detach them on demand, even to another instance, without any data or application loss.

Object Storage is a storage repository architecture available and accessible from anywhere via a web interface. Physical data files can have any structure and type. Their size is limited to 50 GB per file. Object Storage is a standard repository for backups or large data objects, neither of which are usually changed very often. A bucket is a lower architectural definition. It denotes a logical container within Object Storage. Several buckets can be present in any Object Storage instance. The amount of data (in terms of both size and count) is unlimited.

Before provisioning a database, let’s reflect on the core element of OCI – IAM. This service allows you to create users, groups, and policies to control access to resources. All these resources are managed and set by the created users. During the provisioning, one user is automatically created, followed by granting them administrator privileges. Individual users can be part of a specific group with the same privileges, access options, and permissions. A policy specifies the user’s access to a particular resource. It is typically set for the whole group using a tenancy or compartment. Individual resources are grouped into compartments, forming the fundamental element of OCI, and ensuring segregation, isolation, and proper organization.

The components of technology managed in the cloud, such as compute instances, database instances, block volumes, load balancers, and so on, are called resources.

To ensure the accessibility of individual components and resources, basic knowledge of networking principles is required.

Networking

A network is formed by a set of computer or device nodes, where individual nodes can communicate with each other. Each node is uniquely identifiable by an IP address. The router is the component used for traffic routing within a network. Firewalls are used to secure the resource by blocking packets that break security rules. A VCN is a private network running in one OCI region. A VCN is an important element for the definition and configuration of application servers, databases, load balancers, and other cloud services. The overall aim is to ensure the high availability of a robust and reliable solution by applying scalability and security rules. VCNs can be divided into several subnets. The route table consists of the rules for the traffic out of a VCN. Security lists commonly act as a regular firewall for the subnets. Similarly, network security groups act as firewalls for groups of instances across subnets.

If you have created a VCN, you can define compute instances. The next section will discuss this.

Compute instances

OCI allows you to define and provision compute hosts called compute instances. Each compute instance can be operated and administered independently. OCI offers bare-metal (with dedicated physical server access for the highest performance and strong isolation) and VM instances (sometimes shortened and expressed as VMs) only.

Compute instances are used to run individual applications or installations, such as Apache, Docker, and so on. For compute instances, various operating systems and versions can be used. They can be installed from the available images already present in the cloud repository, or your own images can be used.

Each instance is delimited by the number of CPUs and network resources and the amount of memory. The list of available platform images can be found in the OCI documentation. Oracle provides images for Oracle Autonomous Linux, CentOS, Ubuntu, Windows Server, and so on, so any system can generally be supported and migrated.

We covered a basic overview of the OCI principles, technology, and available resources in this section. As is evident, Oracle Cloud provides you with a robust solution for storing data and application references, ensuring availability, backup strategies, and patching. Oracle technology is the most relevant for complex information system support. Thus, there is no question of why to migrate to Oracle Cloud since the advantages are unambiguous. The only question is how to do it.

After connecting to the cloud, you will arrive on the main dashboard screen. There is a hamburger menu in the top-left corner. Clicking on the Oracle Cloud logo navigates you to the home screen. Individual resources and configuration options are here. The top panel consists of the cloud location and profile information, containing your identification details, tenancy, user settings, and more, as shown in the following screenshot:

Figure 1.2 – Oracle Cloud home screen main menu

There are several arrows marked in Figure 1.2. The yellow arrow (1) points to the hamburger menu, while the Oracle Cloud logo (the blue arrow (2)) is used for home screen navigation. The current cloud location is present in the menu as well (indicated by the red arrow (3)). The green arrow (4) points to the user menu, language, notifications, and preference definition.

Database provisioning can be done by locating the menu on the home screen (Launch Resources) or by clicking on the hamburger menu and navigating to Oracle Database | Autonomous Database. The following screenshot shows the sub-elements for provisioning specific database types:

3

Figure 1.3 – Database provisioning

As can be seen in Figure 1.3, resource types are presented in panels, defined by their categories, along with indications on the estimated time required for the creation and whether the resource is available under the Always Free option or whether specific licensing is necessary. Clicking on Create an ATP database (the yellow arrow (1) in Figure 1.3) will take you to a new database parameter specification window, in which you can enter the Compartment information, Database name, as well as its user-friendly representation, Display name, as shown in the following screenshot. The given Database name must contain only letters and numbers. The first one should be a letter. The maximum length is 30 characters. The red arrow (2) in Figure 1.3 points to the link to Create an ADW database.

Figure 1.4 – Database parameter specification during database provisioning

Then, the workload type needs to be selected, depending on the intended usage. ADW is suitable for analytics and complex evaluation, with emphasis on the data retrieval process involving large datasets and pre-calculated values. By contrast, ATP is used for a high-concurrency environment with high transactional workloads. The third option is the JSON option, AJD, mainly associated with the document API and management of storage in JSON format. The APEX database type is optimized for building low-code (or even no-code), data-driven applications.

Then, you select the Deployment type option, choosing from Shared or Dedicated architecture, followed by the database configuration – database system version, OCPU count, and storage capacity (the value is expressed in TB).

Finally, the administrator credentials need to be defined. Oracle applies a strong password strategy. Currently, it must consist of at least 12 characters with at least 1 uppercase letter, 1 lowercase letter, and 1 number. It cannot contain admin, double quotes ("), or your username. The requirements for the credentials might evolve, but will always be summarized in the OCI documentation and outlined when entering the details in a pop-up window.

Optionally, network gateways and accessibility rules can be specified. For the licensing, it is possible to Bring Your Own License (BYOL).

New database resources are provisioned by clicking on the Create Autonomous Database button. The status of the process is visible below the ATP logo. The orange color represents any maintenance or processing currently taking place, while the green color expresses the current availability of the resources. Individual parameters and properties are listed there as well.

Several buttons are present in the upper part of the database home screen, as shown in the following screenshot:

Figure 1.5 – Database actions menu

Referring to the colored arrows in the preceding screenshot, let’s look at what each of these buttons does:

• Database actions (the yellow arrow (1)) launches the SQL Developer Web tool. It offers a graphical SQL interface, data modeler, data visualization packages, ML, or REST functionality in the Development section. There is also an Administration subsection for data import operation management, downloading Client Credentials (Wallet), and user administration and APEX management; a Monitoring subsection for performance monitoring and evaluation; a Downloads subsection for downloading Oracle Instant Client or SODA drivers; and Related Services, dealing with RESTful, SODA, and ML services.
• Database connection (the blue arrow (2)) provides you with the client credentials and connection information to connect to the cloud database. In addition, it offers you a zipped file consisting of the encrypted Client Credentials (Wallet). These are used to connect the SQL Developer Desktop environment launched locally on the client computer.
• Performance hub (the green arrow (3)) consists of extended statistics and services for monitoring activity, such as average active sessions, workloads, blocking sessions, SQL monitoring, Automatic Database Diagnostic Monitor (ADDM), and more. You can monitor within a specific time zone and for a specified time range (database (server), client (browser), or UTC).
• Manage scaling (the purple arrow (4)) provides you with the ability to extend the storage capacity or shrink it, as well as optimize the OCPU count for the required workload.
• Finally, More Actions (the gray arrow (5)) allows you to scale, start, stop, or restart the database. In addition, there are options for license management and changing the administrator password.

Among the management buttons, three tabs consist of Autonomous Database Information, Tools, and Tags, as shown in Figure 1.6. The Tools tab presents Oracle Application Express (APEX) for creating low-code database applications, Oracle ML User Administration, and SODA Drivers for JSON document processing via a set of APIs. SODA drivers are available for REST, Java, Node.js, Python, PL/SQL, and C.

Figure 1.6 – Tools list

Graph Studio, however, allows you to create and manipulate graph databases by automating models and in-memory graphs. Graph Studio is a self-service graph database providing a lot of complex data management functionality, visualization tools, and an analytical environment. It is part of the ADB Free Tier and can be applied to ADW and ATP on shared infrastructure. Currently, it is not available for AJD. Property Graph Query Language (PGQL) is commonly used as an SQL variant, focusing on property graph structures formed by the vertices and edges. Figure 1.7 shows an example query visualization.

Figure 1.7 – Graph representation example (source: https://docs.oracle.com/en/database/oracle/property-graph/20.4/spgdg/graph-visualization-application1.html#GUID-6DDB37F7-C78E-49B7-B062-1240B5D38A5C)

By scrolling down on the Oracle Cloud dashboard, some usage metrics (CPU Utilization, Storage Utilization, Sessions, Execute Count, Running Statements, and Queued Statements) are revealed. Charts can be filtered by time.

You have successfully provisioned the database and are now familiar with the Oracle Cloud console. It’s time to connect to the created database using the SQL Developer tool. We will examine both the desktop and cloud versions.

The easiest way to connect to the database is using SQL Developer Web, which is part of the cloud database menu (Database Actions).

Figure 1.8 – Launching SQL Developer Web

A new browser tab will open, requesting the Username and Password details. In our case, we will provide the details of the administrator user that were set up when the database was defined and that were applied in the provisioning process.

Figure 1.9 – Launching SQL Developer Web – username definition

Figure 1.9 shows the main screen of SQL Developer Web. It provides the interface for defining SQL scripts, along with the environment for data modeling, APEX application building, as well as managing AJD databases and defining and deploying REST APIs. There are also other categories for easy data loading and exporting using wizards, administration, and monitoring interfaces.

Figure 1.10 – SQL Developer Web – main screen

By clicking on the SQL button (the red arrow in the preceding figure), a new window is launched, consisting of three parts, as shown in the following screenshot:

Figure 1.11 – SQL Developer Web – SQL section

The left part consists of the data dictionary reference, highlighting the created objects (the yellow arrow (1) in the preceding figure). The upper part is used for SQL statement definitions and forms the core of the entire environment (the blue arrow (2)). The bottom part provides results and information summaries (the green arrow (3)).

Each ADMIN user automatically gets the privilege to use such a tool. They can also grant that privilege using the enable_schema procedure of ords_admin:

begin
 ords_admin.enable_schema
  (p_enabled => TRUE,
   p_schema => 'MICHAL', -- username for the grant
   p_url_mapping_type => 'BASE_PATH',
   p_url_mapping_pattern => 'michal',
   p_auto_rest_auth => NULL
  );
 commit;
end;
/

Besides this, REST services can be enabled in the Administration | Database Users section.

SQL Developer can also be launched locally in the desktop environment. It is downloadable from the official site:

https://www.oracle.com/tools/downloads/sqldev-downloads.html

You just need to choose the appropriate platform you are running (if you are using Windows, it is recommended to select the version, including the Java Development Kit (JDK), if it has not been installed manually before) and its version (the most up-to-date is preferred; new versions are released periodically).

Oracle SQL Developer Desktop does not need to be installed; just unzip the provided archive file. It is powered by Java and can be launched immediately. Before dealing with the database, the new connection must be defined. Click on the green plus symbol ( ) in the Connections section and specify the connection details. We will walk you through setting up the parameters and how to obtain them in the first phase. The filled-in dialog window is shown later in Figure 1.15.

The Name field of the connection is left to your preferences. Whatever you choose will then be listed in Connection List. Database Type is Oracle. SQL Developer Desktop can be used for managing different database system types if the particular drivers are installed. Leave Authentication Type set to Default. Username is ADMIN or any other user created in the ADB by you. The Password details for the ADMIN user were specified during the provisioning and can be changed at any time in the database section’s main menu (navigate to More Actions). Let Role be the default value for ordinary users. If the user belongs to a particular privilege group, such as SYSDBA, SYSOPER, and so on, choose the appropriate one. For example, ADMIN is the database administrator with SYSDBA privileges granted.

Connection Type must be changed to Cloud Wallet, prompting you to specify the Configuration File information, consisting of the connection details. The question now is how to get the Cloud Wallet configuration file. Return to the cloud console, navigate to the database, and click on the Database connection button to obtain your Wallet.

Figure 1.12 – Getting the Oracle Wallet

By clicking on the button, a new pop-up window opens. Client Credentials (Wallet) are typically downloaded for the instance; however, there is also an option to download a specific Regional Wallet (consisting of all instance wallets used for administration purposes). For development, the Instance Wallet type should be selected:

Figure 1.13 – Prompting Oracle Wallet

Cloud database connections are always secure, so you can provide the password for the wallet to be generated and downloaded. The downloaded wallet is a ZIP archive containing the following files:

• ewallet.sso: This consists of the encryption wallet details.
• sqlnet.ora: This specifies the general wallet location and encryption types.
• tnsnames.ora: This provides connection details – protocols, hosts, ports, and other parameters. The downloaded file consists of file connect strings delimited by the name, as well as parameters – Low, Medium, and High are preferred for the analytical interface, with Tp and Tpurgent for transactional processing.

Note that the connection strings can be listed when the wallet is generated in the Connection Strings subsection. The following screenshot shows an example of a connection string.

Figure 1.14 – Connection string list

The downloaded wallet can then be referenced in SQL Developer Desktop by specifying the Configuration File path (the yellow arrow (2) in the following screenshot), followed by the used service type (Low, Medium, High, Tp, or Tpurgent) (the red arrow (1)):

Figure 1.15 – Creating a new connection in SQL Developer Desktop

Passwords can be optionally stored in an encrypted format. The list of stored connections and encrypted passwords can be found in the application data. For Windows, the location is as follows:

%APPDATA%\SQL Developer\system<VERSION>\o.jdeveloper.db.connection.<VERSION>\connections.json

For Linux, the analogous path is the following:

~/.sqldeveloper/system<VERSION>/o.jdeveloper.db.connection.<VERSION>/connections.json

This file contains all the parameters specified during the connection definition.

The downloaded wallet consists of encryption keys, as well as connection details. This wallet is used by the users, developers, managers, administrators, and all other IT staff to access the Oracle Cloud databases. However, what if someone leaves the company? How can we ensure that some particular data cannot be used later on? The solution is to use wallet rotation, which invalidates existing client keys for the database instance owned by the cloud account in a region. It can be done immediately or after a grace period (from 1 to 24 hours). Even besides instances where people leave a company, it is also generally beneficial to rotate wallets regularly based on the organization’s policies. Wallet rotation can be done by clicking on the Rotate wallet button shown in Figure 1.13.

Now, the connection is specified and a new session is created, where you can write commands or statements:

Figure 1.16 – SQL Developer Desktop

The preceding screenshot shows a select statement, providing you with the current date and time value. What about the output format? How can we reference individual elements, time elements, and particular time zones? We can already perceive the complexity of the whole problem here. A complete description of date and time management in the Oracle database environment will be covered in later chapters. Enjoy!

Throughout this chapter, we have often referenced the term resource sharing. However, what this means in practice is explained in the following section. We will focus on the database system instance itself and summarize the processes and memory structures. To ensure the best performance and optimize the access strategy, it is beneficial to understand the data flow, core elements, memory structures, and database and instance interconnection.

Database systems (DBSs) are made up of databases and data management systems (DBMSs). A database comprises the physical files holding a collection of data. It consists of data files, log files, parameter files, and so on. A database is commonly controlled by a DBMS. A database instance is a set of background processes manipulating the data and memory structures used for data processing, evaluation, and retrieval. Background processes are primarily responsible for asynchronous I/O data operations but also manage memory structures and general maintenance tasks. There are many background process categories. The most relevant for this book are the following:

• Database Writer (DBWn) is responsible for writing a modified data block from the buffer cache memory structure to the particular blocks of the files that constitute the database.
• Log Writer (LGWR) is responsible for the transaction logging by capturing data and storing it in the online redo log file.
• Process Monitor (PMON) performs recovery if a user process fails by cleaning the caches and deallocating the assigned session resources.
• System Monitor (SMON) performs instance recovery in case of its failure.
• Archiver (ARCn) is responsible for copying the online redo log file if a log switch occurs. Log files are consecutively rewritten. However, by copying them, it is possible to reconstruct the database into a historical image by identifying any changes made since a certain point in the archived logs.
• Checkpoint (CKPT) is responsible for the checkpoint event management by taking all modified buffers and writing their content to data files. Control files are also modified.
• Manageability Monitor (MMON) performs maintenance tasks, such as issuing alerts, taking snapshots, and capturing statistics.
• Job Queue Processes (CJQ0 and Jnnn) are responsible for launching and executing the jobs (scheduled user-defined tasks that are executed once or planned to be executed periodically).

The general architecture of the database server is shown in Figure 1.17. To connect the client to the server, first, the user process is invoked on the client site, contacting the database server listener. By cooperating with the other background processes (mostly PMON), a new server process is created. It takes the small memory structure called the PGA that is private to the server process. It stores the current state, cursor variables, local variables, parameters, and so on. The server process is the manager of the user process on the server side, serving user requests for processing. After creating a server process, a client can communicate with the server process directly, without the listener.

Figure 1.17 – Oracle database server architecture

Each instance is formed by the background processes already discussed, along with memory structures, which we will learn about in the following section.

Memory structures

You are now adequately familiar with the background processes. However, what about the memory structures they operate? Dozens of structures are part of the memory that ensures performance and maintenance activities. In this section, we’ll mainly look at memory structures applicable to the database data. These structures are shared among the sessions forming the SGA. Let’s look at them in some detail:

• The SGA is allocated when an instance is started up and released on shutdown. It consists of various memory structures. Most of them are dynamic in size. The available structures can be divided into two types – required structures (such as buffer caches, log buffers, and shared pools) or optional structures (such as large pools, Java pools, or Streams pools).
• The Database buffer cache is a work area for executing SQL as an interlayer between databases. All data changes are done in the memory and then saved to the database. The buffer cache is a block-oriented matrix. The block itself can be clean (no changes have been made to the data held there), dirty (data with changes), or empty (not yet used).

• The Log buffer is a small, short-term staging area holding data change vectors (modifications applied to data) before they are written to the redo log file on the disk. Redo logging ensures that no data can be lost. Due to performance issues, redo data is not directly written to the redo log file, but a near-real-time approach is used, processing in batches. There are many triggers for copying redo logs from the memory to the storage:
  • Every 3 seconds
  • By reaching a commit or checkpoint
  • If it is 1/3 full

The log writer background process operates the log buffer.

• The most complex structure is a Shared pool, consisting of dozens of substructures. It is managed internally. The most important structures are the Library cache (storing recently executed code in parsed form), the Data dictionary cache (holding metadata such as object definitions: tables descriptions, indexes, users, and so on), the PL/SQL area (holding cached data to prevent repeated reading), and the Result cache (storing result sets temporarily for SQL and PL/SQL).
• The optional Large pool extends the shared pool and covers large objects such as backups. The Java pool is a heap space for Java-stored procedures to be run by the database system. The Streams pool is used by Oracle Streams architecture. The principle is to extract change vectors from the redo log to reconstruct statements to be executed, which requires additional memory.

So, now we know the core elements that make up the database systems, as well as the sub-components of each element. However, what about the interconnection between the instance and the database? Is one instance devoted to only one database? Well, in the following sections, we will highlight individual strategies, focusing on database containerization in the cloud environment. We will list all the key features and principles of database management related to system architecture and approaches.

Database system architecture

Each database system is formed by the instance characterized by the background processes and memory structures and the database itself. In general, various mapping strategies are available to operate the database, representing the ratio between them. Namely, one database can be accessed from multiple instances. However, one instance is dedicated only to one database (container) at a time.

Single-tenant databases

A single-tenant database (also known as a non-Container Database (CDB)) consists of a set of data files, control files, transaction logs (redo log files), parameter files, and metadata files. For the database, one instance is created. Before version 12c, this architecture was the only one used. Instance memory was solidly interconnected with the database (commonly named ORCL by default). The database listener was responsible for connection creation at the session level. Data was interchanged between the processes on the client and server part directly without listener interaction. Oracle has now deprecated this architecture. However, despite being unsupported in version 21c, it is still widely used.

Figure 1.18 – Single-tenant database architecture

The preceding diagram shows the single-tenant database architecture. The mapping between the instance and database is 1:1. The extension of this architecture just involves a single-tenant RAC environment consisting of one database, operated by multiple instances.

Single-tenant RAC databases

Single-tenant (non-container) database can be run on multiple computers (nodes, servers, and hosts) by managing multiple instances operating (mounting, opening, and managing) one database. The main advantages are high performance, availability, fault resistance, and scalability. Thus, new instances with additional memory and CPUs can be added dynamically to serve the workload. The client is navigated to a Single-Client Access Name (SCAN) RAC listener. The listener connects the client to the most suitable instance based on balancing the current workload.

Figure 1.19 – Single-tenant RAC database architecture

The preceding diagram illustrates the RAC architecture of the single-tenant database, meaning that one database is operated by multiple instances. The workload is balanced by SCAN listeners navigating the client to a particular instance listener. By generalizing this architecture, containerization is created. It allows the databases to be attached and detached dynamically from the root container.

Multi-tenant CDBs

Multi-tenant CDBs were introduced in Oracle Database 12c. Also known as root CDBs, these contain a limited set of data files, control files, redo log files, parameter files, and metadata. However, there are no application objects or code in the data files. This kind of database is self-contained and can be mounted and opened without any other physical structures.

A Pluggable Database (PDB) is only made up of data files. They contain application data, objects, and the code itself. No metadata is present, so each PDB needs a container to be plugged into it. This type of database inherits its data repository from the container (such as redo log files, control, files, and parameter files).

The accessibility and mapping must apply certain rules. The root container can manage multiple PDBs simultaneously, but each database is associated with just one container at a time. Thus, one instance is associated just with one (root) container; however, it can reference multiple PDBs. One PDB is part of one container at a time.

Multi-tenant RAC databases

Multi-tenant RAC databases provide a general solution consisting of SCAN listeners.

Each instance has a separate local listener; however, the overall workload is balanced using the SCAN listeners. This architecture is shown in Figure 1.20. The user process is routed to the SCAN listener layer, followed by the transition to the specific instance. The database layer is enclosed by the container with dynamic database attachment and detachment functionalities.

Figure 1.20 – A multi-tenant CDB

The preceding figure illustrates this architecture. The architecture and user management are similar to Figure 1.18, with the difference being that the database view is composed of configuration and parameter files and PDBs that can be managed dynamically. It provides a robust solution by only managing active databases.

Finally, a sharded database architecture will be discussed next, which divides the structure of the database into several fragments.

Sharded databases

Oracle Database version 12.2 introduced sharded databases – a logical database horizontally partitioned across a pool of physical databases. Each database (shard) has its own dedicated server and instance. However, from the user’s point of view, it looks like one single database. Data is distributed across shards using the sharding key of the table. Following the system architecture, there is a connection pool to a sharded database, operated by the shard directors (listeners routing the processing based on the sharding key) and shard catalog. It provides robust linear scalability, fault tolerance (as shards exist on separate independent hardware and do not influence other shards), and data independence (updates can be applied to each shard independently). Moreover, it allows distribution across any number of regions. Each shard can be also be configured with different hardware or storage systems, ensuring compliance with laws and regulations related to the specific data positioning restrictions.

Figure 1.21 – Sharded database

The practical usage of sharding is associated with the elimination of manual data preparation as a necessity, leading to time savings by emphasizing scalability, high availability, response time, and write bandwidth. Sharded tables are distributed and duplicated across servers, which are then associated with the particular application and usage by limiting the amount of data covered by the server. Moreover, each shard can be optimized for specific usage and applications via the parameters, index set, partitioning, and other optimization techniques. A significant advantage is achieved by this parallelism and elasticity – data can be resharded without any downtime.

In this chapter, you learned about the core concepts of the Oracle Cloud technology based on self-driving, self-securing, and self-repairing properties, followed by an examination of the types of ADBs that can be provisioned in any deployment model. Besides this, you were also introduced to a selection of important terms related to the Oracle Cloud environment.

As databases and applications are continuously being migrated from the on-premises world to the cloud, we summarized the various database architectures in use. It is important to know and understand the individual existing architectures and be able to adapt the existing on-premises solutions to the architecture used in the cloud environment.

At this point, you know how to create cloud databases, what the individual types are, and how to connect to them using Oracle Wallet. The next chapter deals with data management, focusing on data loading, import, and export techniques.

1. What are the main attributes of OCI?
   1. Self-repairing only
   2. Self-securing only
   3. Self-driving only
   4. Self-repairing, self-securing, and self-driving
2. Which database type is commonly associated with bitmap indexes? Reflect on the performance aspects of the index construction.
   1. ATP
   2. ADW
   3. JSON
   4. PGA
3. Which priority levels are preferred for ATP?
   1. Low
   2. Medium
   3. Medium and High
   4. Tp and Tpurgent
4. Which of these services provides you with the highest level of abstraction?
   1. IaaS
   2. PaaS
   3. SaaS
   4. All of them provide the same level
5. Which background process is responsible for instance recovery?
   1. ARCn
   2. PMON
   3. LGWR
   4. SMON
6. Which database architecture involves a one-to-one mapping between the database and instance?
   1. Single-tenant architecture
   2. A container database
   3. RACs generally
   4. A multi-tenant container database
7. A cloud repository, usually devoted to the organization or company to provide secure and isolated storage and processing partitions, is called which of the following?
   1. A compartment
   2. A tenancy
   3. An image
   4. A sub-domain
8. Oracle Cloud Wallet consists of which of the following?
   1. An Ewallet.sso file only
   2. Sqlnet.ora and the control file only
   3. An embedded connect string in the Sqlnet.ora file
   4. The Ewallet.sso, Sqlnet.ora, and Tnsnames.ora files

• Oracle Cloud Infrastructure for Solutions Architects: A practical guide to effectively designing enterprise-grade solutions with OCI services by Prasenjit Sakrar and Guillermo Ruiz. This provides practical tips and tricks to create robust cloud-based solutions: https://www.packtpub.com/product/oracle-cloud-infrastructure-for-solutions-architects/9781800566460.
• Practical SQL for Oracle Cloud by Michal Kvet, Karol Matiaško, and Štefan Toth. It emphasizes the provisioning process with the SQL and PL/SQL language used in ATP. It can be downloaded free of charge using the following web link: https://oraclecloud.uniza.sk/.

You can also scan this QR code:

• Getting Started with Oracle Cloud Free Tier: Create Modern Web Applications Using Always Free Resources by Andrian Png and Luc Demanche. This outlines the best practices for using the Oracle Always Free tier, covered by the Free Tier option.