This Comprehensive Oracle Database Tutorial Explains What is a Database, Architecture, Oracle Tools & Features. Also includes Database Installation Process:
Oracle database is one of the most commonly used Database Management System that supports multiple data models against one integrated backend. Organizations which are into the business of maintaining Data Warehouses or running OLTP transactions, prefer Oracle database the most, due to its variety of features which supports high performance, security and scalability with an ease of use.
Oracle 19c is the latest database version launched by Oracle Corporation, which supports below environments:
- Oracle Cloud
Before getting into details of the Oracle database and its features, let’s understand the definition of the database and the purpose of using the same.
What You Will Learn:
- What Is A Database?
- Relational Database Management System (RDBMS)
- Steps To Install Oracle Database
- Features Of Oracle Database
- Oracle Tools
- Oracle Utilities
- Oracle Database Cloud
- Oracle Database Architecture
- Database Logical Architecture
What Is A Database?
A database is a repository of data or information which is stored in the database tables in an organized format of rows and columns. This data can be accessed by database users upon appropriate authentication and can be manipulated as well.
Generally, organizations use databases to store various departmental information like HR, Operations, Sales, Finance, etc. to keep them segregated and in a consolidated format. These databases can be accessed later to process relevant information, derive reports or perform business analysis, etc.
Elements of a Database Management System (DBMS):
On a higher level, any Database Management System consists of 3 elements. These are:
- Kernel Code: This code handles memory and takes care of the information stored within the database.
- Data Dictionary: This is a repository within the database, used to handle metadata information.
- Query Language: Query Language is used by users or applications to read the information stored within the database.
Relational Database Management System (RDBMS)
Any Database Management System that follows the relational model is known as Relational Database Management System i.e. RDBMS.
To follow a relational model, the database needs to
- Define all objects stored within a database.
- Apply integrity rules on these objects to maintain a relationship between them.
A relational database allows data to be stored in a relational format so that it can be retrieved and manipulated by applications while maintaining database integrity. In a relational database, data gets stored within database tables and referential integrity will be implemented through Primary & Foreign keys.
Oracle has joined the league of relational databases along with Microsoft SQL Server, Sybase, and DB2, etc.
It has come up in a range of versions till now starting from Oracle v2 till Oracle 18c. With each version, it has come up with improvements or enhancements. Version numbers labeled with software follows a custom numbering pattern, like “c”, stands for “Cloud” in Oracle 18c while in previous releases,”g” stands for “Grid” in Oracle 10g and “i” for “Internet” in Oracle 9i.
Oracle database is available in five editions where each one is designed to fulfill a specific purpose.
- Oracle Database Enterprise Edition (EE).
- Oracle Database Express Edition (XE).
- Oracle Database Personal Edition (PE).
- Oracle Database Standard Edition Two (SE2).
- Oracle Database Standard Edition One (SE1).
- Oracle Database Standard Edition (SE).
Various platforms supported by Oracle database are Red Hat Linux, Microsoft Windows, Oracle Solaris, IBM AIX, HP-UX, etc.
Steps To Install Oracle Database
To install a database (for example, Oracle 12c) on a computer, the user needs to download the authentic Oracle installer or software from Oracle official website.
Once installation files are downloaded on the computer, the user needs to extract the same as files would be downloaded in ZIP format. Further, double-click on the setup file available within the extracted files, which will start the installation process.
There are a total of 9 steps required to complete the installation. Let us see the installation process step by step.
Step #1: The user is asked to provide an email address to be informed of security issues or updates if any.
Step #2: Here, the user needs to select the installation option, whether he needs software installation only or the creation of the database is required along with installation or can opt for a database upgrade.
Step #3: In this step, the user needs to select the system class based on the system on which the user is installing software.
Step #4: In this step, the user needs to provide Windows User Account details using which user needs the installation to be performed.
Step #5: Here the user needs to provide typical installation information required for database installation.
- Provide the location where the software needs to be installed on the machine.
- Provide Global Database credentials.
- Provide Pluggable DB.
Step #6: Pre-requisite check is performed in this step.
Step #7: In this step, summarized information about database configuration will be displayed to the user.
Step #8: Database Installation process will be kicked off in this step.
Step #9: Database Installation will take some time to get installed. Once installed, it will reach the ‘Finish’ step confirming users about successful completion.
Mostly, database set up is done by Database Administrators known as DBAs. Not only database setup, but there are many other administrative responsibilities of a DBA. These includes:
- DBA performs Database installation & configuration.
- They evaluate Hardware and Software requirements and do Capacity Planning.
- Performs Database Migration and Upgrade activities.
- Monitors database performance and does DB Tuning.
- Responsible for database backup & recovery process.
Features Of Oracle Database
There is a wide range of features supported by Oracle Database, which ensured the sustenance of Oracle in the world market today.
The features are as follows:
#1) Oracle Availability: To support 24*7 availability of the database, Oracle offers a functionality named as Oracle Data Guard. Using these functions, secondary standby databases can be maintained as a copy of primary databases and can be used as alternatives during a failover.
#2) Oracle Security: Oracle Advanced Security offers two solutions to protect sensitive information at the source itself i.e. TDE (Transparent Data Encryption) and Data Redaction. It allows data encryption at the source itself and post export. Redaction is performed at the application level. Other security features developed by Oracle are Oracle Database Vault, which manages user privileges and Oracle Label Security.
#3) Oracle Scalability: Oracle RAC (Real Application Cluster) is the perfect example of Oracle Scalability that offers capabilities like rolling instance migrations, performing upgrades, maintaining application continuity, quality of service management, etc. in a clustered environment.
#4) Oracle Performance: Oracle offers performance enhancement solutions like Oracle Advanced Compression, Oracle Database In-Memory, Oracle Real Application Testing, and Oracle Times Ten Application-Tier Database Cache which aims at optimizing system performance at level best.
#5) Oracle Analytics: In the field of analytics, Oracle has come up with the below solutions.
- OLAP (Oracle Analytic Processing) is Oracle’s implementation used for analyzing business data by performing complex analytical calculations.
- Oracle Advanced Analytics: Oracle Advanced Analytics is a technical composition of Oracle R Enterprise and Oracle Data Mining and it helps users determine the predictive business models by performing data and text mining and computing statistical data.
#6) Oracle Management: Oracle has developed a database management solution known as Oracle Multitenant, with a consolidated architecture of a single container database and multiple pluggable databases.
To aid non-developers, organizations are stepping into wizard-based environments, so that they can develop simple software applications without possessing technical expertise. Oracle too has developed many tools, catering to different kinds of development requirements through each tool.
A few of the most used tools developed by Oracle Corporation are mentioned below.
#1) Oracle APEX
Oracle APEX (Application Express) tools come with Oracle database only and aid developers in designing database-driven software applications.
#2) OracleSQL Developer
This is another free of cost tool that allows developers to leverage their SQL & PL/SQL skills by developing software applications. It allows programmers to browse through database objects within a tree-like structure, execute SQL commands and scripts, debug the code, and other administrative tasks.
#3) Java Based Tools
Oracle gives users an option, to develop applications that could be based out of Java and PL/SQL, using the below tools.
- Oracle JDeveloper
- Oracle Forms
- Oracle Reports
#4) Oracle Rest Data Services (ORDS)
ORDS is a Java-based, a middle-tier application that helps programmers develop REST interfaces for data available in Oracle Database. It allows mapping of DB transactions with HTTP(s) methods like POST, GET, PUT, etc. and results would be returned in JSON format.
#5) Oracle Database Upgrade Assistant (DBUA)
This tool provides an interface which supports database upgrade.
#6) Oracle OPatch
Using Oracle OPatch, interim patches can be applied to Oracle software. If required, the applied patch can be rolled back too, using the Opatch tool.
#7) Oracle SQLTXPLAIN
It is again free of cost tool, developed by Oracle that facilitates tuning of Oracle SQL code.
To serve database users, Oracle has built different utilities which simplified the jobs of developers by automating the tasks and saving a lot of time.
Few of the utilities developed by Oracle are listed below:
#1) Data Pump: Data Pump is an Oracle utility that helps users perform data import and export activities within databases.
#2) SQL* Loader: SQL* Loader is another utility that aids data loading from any non-Oracle data source to an Oracle database with high performance. Various features supported by this tool are listed below.
- Has the capability to load data with a wide range of formats.
- Can load multiple source files at once into multiple database tables.
- Can perform data loading from source files that exist on physical disks, named pipes or tapes to databases.
#3) OradeBug: This utility has been designed to support developers with the debugging and tracing features against Oracle Database errors.
Oracle Database Cloud
A Cloud Database is the one that is designed for a virtual environment that could be public, private, or hybrid. The purpose of building a Cloud Database is to optimize the storage and bandwidth expenses by paying on a per user basis.
Proven by enterprises, Oracle Cloud has been a successful solution, supporting a range of workloads starting from the development or testing phase till massive production deployments. Being in the Cloud environment, Oracle database doesn’t compromise on database security, features, or other aspects. It still offers the same level of trust to its users as on-premises, while maintaining the ease of use.
Oracle Cloud would still maintain the below characteristics as on Oracle On-premises.
- High Performance
Oracle Database Architecture
The above image shows the high-level Oracle architecture, having different segments designed to serve different purposes. Let’s explore the same in detail.
Oracle architecture can be divided into 4 major categories. These are listed below:
- Memory Structures (Instance).
- Physical Structures.
- Logical Components.
Let go through each segment to understand database architecture in a detailed manner.
To understand the memory structures, let’s understand the Database Instance first.
An instance is a group of memory structures and background processes that helps in managing data, existing within database physical files. It also helps users to access one database at a time.
Now, an instance is primarily composed of two components:
- Memory Structures (SGA, PGA).
- Background Processes
- Mandatory (PMON, SMON, DBWR, LGWR, CKPT).
Let’s gather some knowledge about the components of a database instance.
#1) Memory Structures
These are used to store different types of database information like,
- User session information
- Code of program
- Cached Data Blocks and Redo Log entries, etc.
Within Oracle, memory structures mainly consist of two areas:
- SGA (System Global Area)
- PGA (Program Global Area)
Now, what is SGA and PGA, and how they are different from each other?
Whenever a database instance starts, some memory gets allocated and that memory is termed as SGA i.e. System Global Area. Along with memory allocation, one or more background processes will also be kicked off to serve the same.
Oracle processes, which could be server or background processes, share this common memory area, which is SGA, and hence, SGA is sometimes called as Shared Memory Area as well. SGA is used to store data as well as control information about one database instance through its various subcomponents, where each component is dedicated for a specific purpose.
Below listed are the components within SGA.
- Database Buffer Cache
- Redo Log Buffer
- Java Pool
- Large Pool
- Shared Pool
- Library Cache
- Data Dictionary Cache
The size of these memory components can be defined in Initialization Parameter File (PFILE) by a DBA before instance startup and the size of complete SGA can be decided using parameter SGA_MAX_SIZE.
PGA is the memory area that holds data and control information about server processes. This memory area gets allocated, whenever a server process starts and unlike SGA, this memory area is not shared with other processes. Each server process will have their dedicated PGA which gets released once the server process ends.
Now let’s see what Oracle background processes are.
#2) Background Processes
Oracle has a collection of processes where background processes manage memory, perform disk I/O operations, and other maintenance activities. These processes are categorized as mandatory and optional processes.
Below listed are a few of the database required processes, hence mandatory background processes.
- System Monitor Process (SMON): This process performs system-level recovery and maintenance tasks like Instance Recovery in case of failure, Data Dictionary Management, Reuse of Temporary Spaces, and Managing Undo Tablespaces, etc.
- Process Monitor Process (PMON): This process keeps monitoring other background processes and does Process Recovery whenever a server process terminates abruptly.
- Database Writer Process (DBWR): As the name suggests, this process writes the data blocks from the Database Buffer Cache (SGA) to physical Data Files.
- Log Writer Process (LGWR): Similarly, this process writes redo blocks from Redo Buffer Cache to Online Redo Log Files.
- CheckPoint (CKPT): This process maintains the data files and control files with the most recent checkpoint information.
Other optional background processes are Archiver (ARCn), Coordinator Job Queue (CJQ0), and Space Management Coordinator (SMCO).
Physical components within a database are the physical files that hold and manage the organization’s data. These files are divided into 3 categories.
- Data Files: These files hold the actual data within a database.
- Redo Log Files: These files are used to hold all the changes made to the database. Redo log files can be utilized during the database recovery process to retrieve the original information.
- Control Files: It is a binary file that holds database status-related information like Database Name, Data File, and Redo Log file Names, File Locations, and Log Sequence Number, etc.
As listed below, there are a few more files that contribute to database management.
- Parameter File: This file holds the parameters, which defines the way the database is expected to start up.
- Password File: As stated by the name, this file holds user passwords and hence maintains database security.
- Archived Redo Log Files: These are the redo log files kept archived. They are preserved to support database recovery in case database crashes.
Processes within a database can be categorized into 3 different types.
- User Processes: User or a client process gets established once the user sends a connection request to Oracle Server.
- Server Processes: The server process connects the user to the database and performs activities on the client’s behalf as per client’s program like executing SQL statements or retrieving data from the database etc.
- Background Processes: They get started along with database startup and perform different maintenance tasks like database recovery, instance clean up, archived redo log files, etc.
Database Logical Architecture
Logically, database architecture is divided into the below components:
- Data Blocks
Now, let’s see the logical components in detail.
While data is stored in physical data files in a database, a tablespace is a logical storage space for that data. This data will be stored in the form of database objects like tables, views, indexes, constraints, clusters, etc. Each tablespace can be associated with one or more physical data files but it can belong to only one database.
SYSTEM and SYSAUX are two mandatory system tablespaces that exist in every database and used for maintaining Data Dictionary and Metadata information. Except for these two tablespaces, others can be taken offline for maintenance purposes like Backup, Recovery, and Clean up, etc.
A tablespace can have multiple data files but a data file can store DB objects of single tablespace only.
A tablespace is typically divided into multiple segments that are used to store a specific type of database objects or structures within a tablespace. Usually, each database object will be referred to as its segment. For example, data within a database table will get stored within its particular table segment.
A segment is further split into extents and a database object can be stored in one or more extents. As the size of an object like table or index grows, DBA can add extents further to the segment as per requirement.
#4) Data Blocks
A Block or Data Block is the smallest unit of storage and data is stored in these blocks. The size of a Data Block is defined using parameter DATA_BLOCK_SIZE at the time of database creation and cannot be changed later on.
Summarizing the physical and logical architecture of Oracle database as below:
- Physical Structure consists of Data Files, Redo Log files, and Control File.
- Logical Structure comprises of Tablespaces, Segments, Extents, and Data Blocks.
In this article, we have learned the Oracle database in detail starting with the installation steps and then moving forward to learn the features, architecture, and list of most used tools developed by Oracle Corporation.
I hope reading this article gave you a clear understanding of the Oracle database and its architecture and will benefit you in the area of database technology.