Developing Robust Date and Time Oriented Applications in Oracle Cloud E-Book

Michal Kvet

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 591

Veröffentlichungsjahr: 2023

Beschreibung

Proper date and time management is critical for the development and reliability of Oracle Databases and cloud environments, which are among the most rapidly expanding technologies today. This knowledge can be applied to cloud technology, on premises, application development, and integration to emphasize regional settings, UTC coordination, or different time zones. This practical book focuses on code snippets and discusses the existing functionalities and limitations, along with covering data migration to the cloud by emphasizing the importance of proper date and time management.
This book helps you understand the historical background and evolution of ANSI standards. You’ll get to grips with data types, constructor principles, and existing functionalities, and focus on the limitations of regional parameters and time zones, which help in expanding business to other parts of the world. You’ll also explore SQL injection threats, temporal database architecture, using Flashback Technology to reconstruct valid database images from the past, time zone management, and UTC synchronization across regions.
By the end of this book, you’ll be able to create and manage temporal systems, prevent SQL injection attacks, use existing functionalities and define your own robust solutions for date management, and apply time zone and region rules.

Leseprobe

Developing Robust Date and Time Oriented Applications in Oracle Cloud

A comprehensive guide to efficient date and time management in Oracle Cloud

Michal Kvet

BIRMINGHAM—MUMBAI

Developing Robust Date and Time Oriented Applications in Oracle Cloud

Copyright © 2023 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Rahul Nair

Publishing Product Manager: Preet Ahuja

Senior Editor: Tanya D’cruz

Technical Editor: Rajat Sharma

Copy Editor: Safis Editing

Language Support Editor: Safis Editing

Project Coordinator: Ashwin Kharwa

Proofreader: Safis Editing

Indexer: Sejal Dsilva

Production Designer: Vijay Kamble

Marketing Coordinator: Nimisha Dua

First published: May 2023

Production reference: 1190423

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80461-186-9

www.packtpub.com

This book has been in progress for a long time, and I would like to thank my family for their continuous support and understanding, as well as Professor Karol Matiaško for the opportunity to grow professionally and become a database expert. I want to thank all the members of Packt Publishing for guiding me and helping to make the book top-quality. Finally, I want to thank God for the gift of wisdom and the people who trusted me and stood by me in any way.

– Michal Kvet

Contributors

About the author

Michal Kvet is a researcher, educator, and database expert at the University of Žilina in Slovakia. His primary focus areas are databases, analytics, performance, and cloud computing. He works closely with Oracle and Oracle Academy. He is the co-author of multiple textbooks (a SQL and PL/SQL cookbook, a book on APEX application development, a book on temporal databases, and a MySQL cookbook), coordinates multiple Erasmus+ projects (https://code-in.org/, https://beeapex.eu/, and https://evergreen.uniza.sk/), and co-organizes several research conferences and database workshops. Besides this, he supervises engineering projects and bachelor’s, master’s, and doctoral theses. Over the years, his research has been associated with date and time management and temporal databases. He has Oracle’s SQL, PL/SQL, Cloud, Analytics, and Administration certifications. His core knowledge of temporality is provided to you in this book.

About the reviewers

Heli Helskyaho is the CEO for Miracle Finland Oy. Heli holds a master’s degree (computer science) from the University of Helsinki and specializes in databases. At the moment, she is working on her doctoral studies at the University of Helsinki. Heli has been working in IT since 1990. She is an Oracle ACE Director and a frequent speaker at many conferences. She is the author of several Oracle technology-related books.

I want to thank Michal for inviting me to work on this project. It is always a pleasure to read his writing and learn from his knowledge. Thank you, Ashwin Dinesh Kharwa, and the rest of the Packt team for the support. This book was a very fun project, and I am very glad I was able to be part of it.

Dr. Frane Urem is a highly skilled vice dean and professor at the Polytechnic of Šibenik and the University of Zadar. With a Ph.D. in computer engineering and professional experience in the IT industry, Dr. Urem is skilled in different areas of computer science. He advocates for project-based and non-formal learning, encouraging students to explore and develop their skills through real-world projects. As a tech reviewer, Dr. Urem provides valuable insights into and analysis on various tech products and services. His background in software engineering, databases, and information system design allows him to evaluate different technologies comprehensively.

I dedicate this review to my wife and kids, who inspire me daily to be my best self. Thank you for your love, patience, and support. I could not have done this without you.

Table of Contents

Preface

Part 1: Discovering Oracle Cloud

1

Oracle Cloud Fundamentals

Oracle Cloud concepts

The Always Free option

Types of ADBs

Understanding the deployment models

A public cloud

A private cloud

The hybrid cloud

The community cloud

Shared versus dedicated architecture

Oracle Cloud terminology

Region

Availability domains

Realms

Tenancies

Consoles

Compartments

VCNs

Instances

Images

Storage management

Networking

Compute instances

Database provisioning

Connecting to the ADB

Database system architecture overview

Memory structures

Database system architecture

Summary

Questions

Further reading

2

Data Loading and Migration Perspectives

Understanding SQL Loader

Importing data using SQL Data Loader in SQL Developer Desktop

Importing data using SQL Data Loader in the cloud interface

Accessing data stored outside the database using an external table

Getting to know client-side import/export

Examining server-side import/export using dump files

Object Storage and buckets

Creating credentials

Authentication token

Pre-authenticated request

Import process using dump files

Export process using dump files

Understanding Transportable Tablespace Data Pump

Full Transportable Tablespace Data Pump

Exploring migration techniques

OCI Data Transfer Service

Oracle Zero Downtime Migration

Enterprise Manager Database Migration

Oracle Maximum Availability Architecture

Oracle Application Continuity

Move to Autonomous Database, Move to Oracle Cloud Database

OCI GoldenGate

Summary

Questions

Further reading

Part 2: Understanding the Roots of Date and Time

3

Date and Time Standardization Principles

Understanding relational database integrity

The ISO 8601 standard

Date value definition

Week management

Ordinal date values

Time element modeling and management

12-hour notation remarks

Composite date and time value

Periods of time

Validity interval modeling

Unlimited validity

Interval periodicity

Representation enhancement remarks

Date and time value modeling in CSV, XML, and JSON format

Summary

Questions

Further reading

4

Concepts of Temporality

What is temporality?

Introducing DST

The impact of DST

Permanent DST usage

Summer Time in Europe

Introducing UTC

Time zone perspective

Implementing DST in an Oracle database

Gregorian versus Julian calendar

Leap years in the Gregorian calendar

Leap second

The leap second in Oracle Databases

Summary

Questions

Further reading

Part 3: Modeling, Storing, and Managing Date and Time

5

Modeling and Storage Principles

Exploring data types

Exploring constructor functions

The ANSI DATE constructor

The TO_DATE function

The ANSI TIMESTAMP constructor

The TO_TIMESTAMP function

Time zone enhancements

DATE and TIMESTAMP transformation

Daylight saving time in Oracle

Storing the date as an INTEGER value

Getting the actual date and time values

Getting to know DATE arithmetic

Understanding the INTERVAL data type

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

TIMESTAMP arithmetic

Summary

Questions

Further reading

6

Conversion Functions and Element Extraction

Understanding the TO_CHAR and TO_DATE conversion functions

Working with flexible format mapping

Constructing the TIMESTAMP value

Conversion functions – century reference

TIMESTAMP precision in SQL and PL/SQL

Understanding the EXTRACT function

Reliability and integrity issues

Investigating the CAST function

Casting a character string to a DATE value

Casting a numerical value to a DATE value

Casting DATE to TIMESTAMP

Casting TIMESTAMP to DATE

Casting a DATE value to character string format

Casting a TIMESTAMP value to character string format

Casting NULL

Validating conversions

Summary

Questions

Further reading

7

Date and Time Management Functions

Getting to know the ADD_MONTHS function

Identifying the number of days in a month using LAST_DAY

Understanding the usage of the MONTHS_BETWEEN function

Exploring the NEXT_DAY and NEXT_DATE functions

Exploring the principles of the NEXT_DAY function

The impact of language definition on the NEXT_DAY function

Implementing the NEXT_DATE function

Numerical day-of-week representation related to the NEXT_DATE function

Getting the second Sunday of the month

Investigating the TRUNC function

The TRUNC function and week management

Understanding the usage of the ROUND function

Understanding the PERSONAL_ID concept and birthday management

Generating random dates

Summary

Questions

Further reading

8

Delving into National Language Support Parameters

NLS parameter overview

Exploring NLS parameters and their impact

NLS_DATE_FORMAT parameter

NLS_DATE_LANGUAGE parameter

NLS_CALENDAR parameter

NLS_TERRITORY parameter

Embedding NLS parameters in the TO_CHAR function

Other NLS parameters

Summary

Questions

Further reading

Part 4: Modeling Validity Intervals

9

Duration Modeling and Calculations

What timepoint means and how to use it

Getting the current state

Deploying duration models using timepoint borders

Closed-closed representation

Closed-open representation

Transformation of duration intervals

Interval representation remarks

Modeling unlimited validity

Getting current valid states

Managing duration – getting the elapsed time

Summary

Questions

Further reading

10

Interval Representation and Type Relationships

Relationships between time interval representations

Using positional relations in temporal space

Modeling temporal validity using Oracle Database’s embedded PERIOD

Concepts of temporal validity

Utilizing data images using the DBMS_FLASHBACK_ARCHIVE package

Identifying temporality

Final remarks

Summary

Questions

Further reading

11

Temporal Database Concepts

The origin and evolution of temporal models

The object-oriented temporal approach

The temporal aspect of management

Temporal system requirements

Exploring temporal dimensions

Validity

Transaction validity

The IPL model

The IPLT model

Future valid record management

Online and offline application modes

Replicas

Final remarks on the temporal dimensions

The attribute-oriented approach

The extended attribute-oriented temporal approach

The group-level temporal system

Temporal group – the physical representation

Conventional table with time-delimited attributes

Summary

Questions

Further reading

12

Building Month Calendars Using SQL and PL/SQL

Defining a calendar using PL/SQL

Modeling a calendar using SQL

Step 1 – Getting the day numbers for each day in the month

Step 2 – Grouping the data based on the weekday

Step 3 – Padding the data

Step 4 – Getting the header and ordering the rows in the result set

Name day management

The header of the package

The package body

Summary

Questions

Further reading

Part 5: Building Robust and Secure Temporal Solutions

13

Flashback Management for Reconstructing the Database Image

Transaction as a change vector source

Transaction log structure and log types

Reconstructing states using Flashback technology

Using FLASHBACK DATABASE to get a historical image

Understanding and referencing the DBMS_FLASHBACK package

Retrieving historical data using the AS OF query

Summary

Questions

Further reading

14

Building Reliable Solutions to Avoid SQL Injection

Understanding SQL injection

Solutions to limit SQL injection

Using bind variables

Explicit date and time value conversion

Sanitizing the input with the DBMS_ASSERT package

Implementing the ENQUOTE_LITERAL function for data enclosure

Summary

Questions

Further reading

Part 6: Expanding a Business Worldwide Using Oracle Cloud

15

Timestamp Enhancements

Applying time zones for the server and client sides

Setting the time zone value at the database and session levels

TIMESTAMP and transformation across time zones

Extending the TIMESTAMP value with the time zone using the FROM_TZ function

Using the AT TIME ZONE clause to reflect the time zone shift

Transforming DATE values across multiple time zones using the NEW_TIME function

Converting time zones

TIMESTAMP normalization

Extracting UTC

Local value reflection using TIMESTAMP data type extensions

Local versus global expansion

Summary

Questions

Further reading

16

Oracle Cloud Time Zone Reflection

Summarizing time zone management

Exploring SQL translation profiles

Developing a package to translate SQL code

Translation profile usage

Simplifying time zone management shift using SYSDATE_AT_DBTIMEZONE

Summary

Questions

Further reading

Assessments

Chapter 1 – Oracle Cloud Fundamentals

Chapter 2 – Data Loading and Migration Perspectives

Chapter 3 – Date and Time Standardization Principles

Chapter 4 – Concepts of Temporality

Chapter 5 – Modeling and Storage Principles

Chapter 6 – Conversion Functions and Element Extraction

Chapter 7 – Date and Time Management Functions

Chapter 8 – Delving into National Language Support Parameters

Chapter 9 – Duration Modeling and Calculations

Chapter 10 – Interval Representation and Type Relationships

Chapter 11 – Temporal Database Concepts

Chapter 12 – Building Month Calendars Using SQL and PL/SQL

Chapter 13 – Flashback Management for Reconstructing the Database Image

Chapter 14 – Building Reliable Solutions to Avoid SQL Injection

Chapter 15 – Timestamp Enhancements

Chapter 16 – Oracle Cloud Time-Zone Reflection

Index

Other Books You May Enjoy

Part 1: Discovering Oracle Cloud

It is evident that local data centers are relics of the past. Let this part assist you in understanding the core concepts of Oracle Cloud, deployment models, and their properties, specifics, and limitations, as well as the main terminology associated with cloud technology.

Soon, you will become familiar with Oracle Cloud registration, database definition, and provisioning. That's great. But what happens next? Data must be migrated to a cloud environment. Therefore, this part introduces SQL Loader, server, and the client-side import and export tools, and explores multiple migration techniques.

Are you are unfamiliar with the Oracle Cloud environment and do not know precisely where and how to start? This part is for you, with the whole process outlined step by step.

This part includes the following chapters:

Chapter 1, Oracle Cloud FundamentalsChapter 2, Data Loading and Migration Perspectives

1

Oracle Cloud Fundamentals

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 optionDefining Oracle ADBs and their types and principlesDeployment models and database architecturesProcess of database provisioning and connectingDatabase 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 Cloud concepts

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 storageCompute VMs2 Block Volume storage instances – 100 GB in total10 GB object storage10 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.

Types of ADBs

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:

Category

ADW

ATP

Memory configuration

Parallel joins and complex aggregation processing in memory (focus on the Private Global Area (PGA))

Transaction data processing in the System Global Area (SGA) – this limits I/O operations

Optimization

Complex SQL

Response times

Format

Column

Row

Data structure

Pre-calculation and indexes

Normalization and indexes

Statistics collection

Bulk operations

DML operations

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.

ADW

ATP

Resource service priority

Low, Medium, and High

Low, Medium, High, Tp, and Tpurgent

Type

SQL parallelism

Concurrency

Type

SQL parallelism

Concurrency

Low

1

300 x CPUs

Tp

1

300 x CPUs

Medium

4

1.25 x CPUs

Tpurgent

User specified

300 x CPUs

High

All CPUs

3 x CPUs

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.

Understanding the 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.

Shared versus dedicated 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.

Oracle Cloud terminology

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.

Database provisioning

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