INTRODUCTION & OVERVIEW

ABOUT ORACLE
LESSON 1
UP UNIVERSITY | INSTRUCTOR: UY TRY | BACHELOR DEGREE
BASIC INFORMATION
• What is data?
• What is database?
• What is dbms/rdbms
• What is table?
• What is sql & pl/sql?
• Type of sql statement
• What is relational database?
• What is normalization?
• Problem without normalization
• Type of hosting oracle database 12c or 18c?
BASIC INFORMATION
WHAT IS DATA?
• Facts or figures, pieces of information (Unprocessed, unorganized)
WHAT IS DBMS ?
• Database management system to store and retrieve and modify
data in the database.
WHAT IS DATABASE?
• It is organized collection of information. •It is organized collection
of information.

❑ And because Oracle is relational database, then we have

RDBMS which is: Relational database management system.
BASIC INFORMATION
The purpose of oracle database is to store, organize, and retrieve data for
your applications (application is interface between users and DB)
WHAT IS TABLE?
•It is the basic storage unit of an RDBMS ( it is logical unit , not physical )
The table consists of rows and columns
BASIC INFORMATION
• To access Oracle database you need SQL
WHAT IS SQL?
• SQL is Structure Query Language (SQL)
BASIC INFORMATION

Oracle Is A Relationship Database?

A relational database stores data in a
set of simple relations
BASIC INFORMATION
What is Normalization?
1. Normalization is a database design technique which organizes tables in a
manner that reduces redundancy and dependency of data.
2. It divides larger tables to smaller tables and links them using
relationships.
BASIC INFORMATION
Problem without Normalization
Extra memory space
Difficult to handle and update the database
Data inconsistency
BASIC INFORMATION
Hosting Oracle 12c/ 18c

Hosting Oracle 12c/ 18c

The Software installed in your Host the database and all its
Own environment (in your components in Cloud (Oracle
server room or data center) Cloud, AWS or Ms. Azure etc)
BRIEF HISTORY OF ORACLE VERSION
• Brief History of Oracle Database
• In 1977, Larry Ellison, Bob Miner, And Ed Oates Started The Consultancy Software Development
Laboratories, Which Became Relational Software Inc(RSI).
• In 1983, RSI Became Oracle Systems Corporation And Then Later Oracle Corporation until today.
• In 1979, Oracle v2 :The First Commercially Available SQL-based RDBMS
• In 1983, Oracle v3: The First Relational Database To Run On Mainframes, Minicomputers, And
Pcs. The Database Was Written In C, Enabling The Database To Be Ported To Multiple Platforms.
• In 1984, Oracle v4: Multi-version read consistency
• In 1985, Oracle v5: supported client/server computing and distributed database systems
• In 1988, Oracle v6: brought enhancements to disk I/O, row locking, scalability, and backup and
recovery, introduced the first version of the PL/SQL language.
BRIEF HISTORY OF ORACLE VERSION
• In 1992, Oracle 7 :Introduce PL/SQL store procedure and triggers
• In 1997, Oracle 8: Was released the object-relational database, supporting many new data
types. Additionally, Oracle v8 support partitioning of larges tables.
• In 1999, Oracle 8i: Provided native support for internet protocols and server-side support for Java
• In 2001, Oracle 9i: Introduced Oracle RAC (Real Application Cluster), enabling multi-instances to
access a single database additionally, introduce Oracle XML database.
• In 2003, Oracle 10g: Introduced Grid-Computing, A Computer Architecture that coordinates
larges number of servers and storages to act as a SINGLE LARGE COMPUTER.
• In 2007, Oracle 11g: Active Data Guard, Secure Files, Exadata and many new features,
• In 2013, Oracle 12c: Designed for cloud, Multi-Tenant Architecture.
• In 2018,Oracle 18c: Cloud, Multi-Tenant Architecture, and many new features.
https://en.wikipedia.org/wiki/Oracle_Database
ORACLE ENTERPRISE EDITION
What you will learn in this lecture?
What are the available database editions in 12c?
What are the available database editions in 18c?
What is the purpose of each editions?
Oracle database software can be very expensive or very cheap (even free),
depend on what you buy and use.
ORACLE ENTERPRISE EDITION
Oracle DB 12c editions
Oracle Database Personal Edition (PE)
supports single-user development, includes all of the components that are included with Enterprise Edition, no Oracle RAC, no
Oracle Management Packs.
Oracle Database Standard Edition 2 (SE2) starting from Release 1 (12.1.0.2).
Included all the features necessary to develop workgroup and Web applications, support RAC, no parallel execution, no data
guard, no management pack, no cloud control, ONLY pluggable database allowed. Noted: before SE2: we have SE & SE1 and
both no RAC option.
Oracle Database Enterprise Edition (EE)
Oracle Database Enterprise Edition provides performance, availability, scalability, and security for developing applications such
as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses, and demanding Internet
applications. Oracle Database Enterprise Edition.
Oracle Database Express Edition ( XE)

Free but limited , use 1 CPU, 1 GB memory

ORACLE ENTERPRISE EDITION
To know more about Oracle Editions
12C
https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC-GUID-
B6113390-9586-46D7-9008-DCC9EDA45AB4

18C
https://docs.oracle.com/en/database/oracle/oracle-
database/18/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-
9008-DCC9EDA45AB4
DATABASE VS DATABASE INSTANCE
Oracle DB consists of 2 major components
1. The Database Storage: Is an organized collection of information. It contains collection of
database files stored in disk storage (physical files).

2. The Oracle Instance:

A Oracle or Database Instance is a set of
a) Memory Structure
b) Process Structure
That Oracle Instance manages database files

Note: A database instance exists only in memory

(memory is OS memory allocated to database
instance during DB creation)
DATABASE VS DATABASE INSTANCE
1. The Oracle program, or binary, loaded
into the Server / OS RAM

2. Created by Oracle every time when you

start up your database

3. Reading from memory is a lot faster

compared to reading from disk

Every running Oracle database is associated with at least one Oracle database instance.
Because an instance exists in memory and a database exists on disk, an instance can exist without a
database and a database can exist without an instance.
DATABASE VS DATABASE INSTANCE
1. The Oracle program, or binary, loaded
into the Server / OS RAM

2. Created by Oracle every time when you

start up your database

3. Reading from memory is a lot faster

compared to reading from disk

Every running Oracle database is associated with at least one Oracle database instance.
Because an instance exists in memory and a database exists on disk, an instance can exist without a
database and a database can exist without an instance.
DATABASE VS DATABASE INSTANCE
Another Simple Way to Understand Database (DB) and Instance Example:
Microsoft Office Word and Word Document
DATABASE VS DATABASE INSTANCE
Oracle Database Server Architecture
• Configuration Optional

3:
RAC (Real Application
Cluster)
•High Availability
1: 2: •Scalable
Single Database Multi Single Instance DBs •High Performance
Instance on Same Server
ORACLE INSTANCE
(Server Process + PGA)

What will you learn in this lecture ?

What is the server processes ?
Task of server process ?
What is the PGA?
Oracle dedicated server process
Oracle Shared server Process
ORACLE INSTANCE
Database Instance in-depth
A database instance is a set of memory structures and processes
that manage database files

Memory:
System Global Area (SGA)
Program Global Area (PGA)

Process:
Background Process
Server Process
ORACLE INSTANCE
(Server Process + PGA)

What will you learn in this lecture ?

What is the server processes ?
Task of server process ?
What is the PGA?
Oracle dedicated server process
Oracle Shared server Process
ORACLE INSTANCE
Server processes + PGA
Oracle starts up server processes to handle the requests of client process
(User sessions)
Perform the work for the client program. Example: Parsing and running SQL
statements, retrieving results to the client program and this mean reading
database data from the database storage and loading that data into the Oracle
instance buffer cache.
Act on behalf of client sessions
Each server process has it own cache dedicated for each connecting client .
This caches is known as PGA
ORACLE INSTANCE
(Server Process + PGA)

What will you learn in this lecture ?

What is the server processes ?
Task of server process ?
What is the PGA?
Oracle dedicated server process
Oracle Shared server Process
ORACLE INSTANCE
(Server Process + PGA)

What will you learn in this lecture ?

What is the server processes ?
Task of server process ?
What is the PGA?
Oracle dedicated server process
Oracle Shared server Process
ORACLE INSTANCE
(Server Process + PGA)

So here in this example, I have one User Session doing select statement ,so
this mean Oracle will create SP (Server Process) and this SP has its own PGA
More users = more dedicated server processes
PGA: non-shared memory region reserved only for the specific user session
connected to that specific server process.

Note: it contains data and control information used by the server processes when
sorting data, joining large tables together as part of a SQL statement, and so on
ORACLE INSTANCE
Server Process
Oracle shared server processes
client user processes connect to a dispatcher.
The dispatcher can support multiple client connections
concurrently
Note: Shared server architecture
eliminates the need for a dedicated
server process for each connection.

Oracle dedicated server process (Mostly used)

One to one relation
Between connecting user sessions and server process
SYSTEM GLOBAL AREA (SGA) PART 1
What will you learn in this lecture ?
What is System Global Area (SGA) ?
Main tasks of SGA
Shared Pool
Database Buffer Cache
Redo Log Buffer
SYSTEM GLOBAL AREA (SGA) PART 1
Database Instance
A database instance is a set of What is System Global Area (SGA)
memory structures and processes that The SGA is a group of shared memory structures
That contain data and control information for one
manage database files
database instance.

Main Tasks of SGA

Maintaining internal data structure that many
processes access concurrently
Caching data blocks read from disk
Buffering redo data before writing it to the
online redo log files
Storing SQL execution plan
SYSTEM GLOBAL AREA (SGA) PART 1

Shared Pool
The most important component of SGA
Cache non user Data like Data dictionary
(metadata: data about data ex: DBA_tables)
Shared Pool contains sub components like Data
dictionary cache, library cache ,server result cache and
many others
Store the cached information about each SQL
statement that is executed, like execution plan
SYSTEM GLOBAL AREA (SGA) PART 1

Database buffer cache

Responsible for caching database user data
The buffer cache caches frequently accessed database data into memory
Note: The buffer cache caches blocks of data instead of individual rows
(1 block=8 kb by default)
All users who are concurrently connected to the database shared and
access a single database buffer cache
The buffer cache including the keep pool, recycle pool, and others
SYSTEM GLOBAL AREA (SGA) PART 1

Circular buffer that hold information about changes made to the database
Designed to store redo entries (small records that reflect any changes made to
the database as part of transactions or database structure changes).

Note: Redo entries are used for database recovery when and if needed.
The Redo Log entries in the Redo Log buffer are also written on a periodic basis
to a set of files in our Oracle database storage known as Redo logs files
SYSTEM GLOBAL AREA (SGA) PART 1
Large Pool
Java Pool
Fixed SGA
Stream Pool
Sizing SGA and PGA
SYSTEM GLOBAL AREA (SGA) PART 2

Large Pool
Contain memory used by special
Oracle feature like:
Shared Server Processes
Parallels Queries
SELECT /*+ PARELLEL(emp,4) */* FROM EMP.
Database Backup and Recovery Operation
I/O Server Processes
 SYSTEM GLOBAL AREA (SGA) PART 2

Java Pool
➢ Parsing of Java Code and Scripts.

Fixed SGA
➢ Contains general information about the state of the
database and the instance
Streams Pool
➢ Provide memory for Oracle Streams Process.
SYSTEM GLOBAL AREA (SGA) PART 1
1 2
Sizing SGA & PGA

1. Using Automatic Shared memory 2. Using Manual Shared

Management Memory Management
ASMM SGA_Target: actual memory You must manually configure
is used by the current SGA several SGA component size
SGA_Max_Size: the largest amount of
memory that will be available for the SGA in A. Set the Memory_Target
the instance initialization Parameter to 0.
Oracle Database automatically distributes B. Set the SGA_Target initialization
this memory amount the various of SGA Parameter to 0.
components to ensure the most effective C. Manual configure SGA component
memory utilization
SYSTEM GLOBAL AREA (SGA) PART 2
1 Sizing PGA 2

1. Using Automatic PGA Memory Management 2. Using Manual PGA Memory Management
PGA_Aggregate_Target: total amount of PGA You must manually configure several PGA
memory allocated across all database server component size. Not Recommended !
processes and background processes
Oracle strongly recommends that you leave
automatic PGA memory management enabled.
DATABASE INSTANCE - BACKGROUND PROCESS
What will you learn in this lecture ?
What are the Background Processes?
The Main Purpose of Each Background Process
DATABASE INSTANCE - BACKGROUND PROCESS
Background Processes:
Bunch of dedicated server-side
processes running in the background
TASKS:
• Writing database blocks to disk
• Writing redo entries to disk
• Making sure all of the database
files on disk are synchronized.
DATABASE INSTANCE - BACKGROUND PROCESS
• Database Writer Process (DBWn):
responsible for writing contents of the
database buffers to data files on disk
• Checkpoint process (CKPT):This process
handle database checkpoints
• Log Writer (LGWR): responsible for writing
redo records from the redo log buffering
memory to disk (log files)
An Oracle Checkpoint is a database event
which synchronizes modified data blocks in
memory from buffer caches with data files
on disk.
DATABASE INSTANCE - BACKGROUND PROCESS
•System Monitoring Process (SMON): Perform
recovery during the startup sequence of the
Oracle Instance if required. Responsible for
cleaning up any unused temporary segments
•Process Monitoring (PMON): Perform process
recovery when a user process or session fails.
Responsible for cleaning up any changes made
to blocks in the database buffer cache, and
releasing resource that were previously used by
a failed user session.
DATABASE INSTANCE - BACKGROUND PROCESS
• Recover Process: Used as part of distributed
database transaction. Distributed transactions
are transactions that involve multiple database,
and should either commit a rollback on both
database at once.
• Listener registration process (LREP): Register
Oracle Instance with Oracle network listener.
The Listener accepting remote incoming user
connections.
• Achiever Process (ARCn): copy the redo log
files to a remote storage device/location after
redo log switch occurs
Note: Copying the database redo logs to another storage system is very important from a backup
and recovery perspective
ORACLE DATABASE FILES
Database Files Database: Organized collection of information It contains collection
of database files store in disk storage (Physical Files).
ORACLE DATABASE FILES
Datafiles:
Contains the actual users data, applications data, metadata.
(Tables, Rows, indexes,procedures, views…)
Note: If you lose Datafiles, you lose your database. The extension for datafilesis .dbf
Control files: Stores metadata about the datafiles and online redo log files (like names,
locations and statuses ) and this info is required by the database instance to open the
database.
Note: If you lose control files, you lose your database. The extension for Control files is .ctl
Redo log files:
Stores changes to the database as they occur and are used for data recovery.
Note: If you lose Redo log files, you lose your database. The extension for redo log file is .log
ORACLE DATABASE FILES
Database Backups Files
include any backups of your database that you have taken and placed somewhere safe
Note: it should include the datafiles, control files, redo log files. The extension for redo log
file is .log

Archived Redo Log Files (groups of redo log files after backup)
Contains ongoing history of the data changes .
Using these files + backups files you can recover your database
ORACLE DATABASE FILES
ORACLE DATABASE FILES
Parameter file ( spfile, pfile)
This file defines how the database instanceis configured when it starts up.
Example: the PGA size PGA_AGGREGATE_TARGET
Note: spfile is binary file, you can not edit directly, it should by oracle commands.
Password File
Stores passwords for users with administrative privileges (sys user) in order to connect
remotely.
Note: DBA password cannot be stored in the database, because Oracle cannot access the
database before the instance is started, Therefore, the authentication of the DBA must
happen outside of the database
Alert Log File
is a chronological log of messages and errors written out by an Oracle Database. So this is
your go-to file in case you're trying to troubleshoot a problem with your database.
Example: You can see when the database was started and stopped.
Trace File: Each server and background process writes to a trace file.
When a process detects an internal error, it writes information about the error to its trace
file.
LOGICAL STORAGE STRUCTURE
Tablespaces are logical storage groups
that can be used to store logical database constructs,
such as tables and indexes
Logically it stores the database files

A Segment is a set of Extents,

one or more Extents,
allocated for certain logical structures
inside the database ( ex: table, index)

An Extent is a set of contiguous Oracle Data Blocks it is

much more efficient,
when allocating space
Oracle data stored in data blocks
1 Block= 8 K by default
A single Oracle Data Block contains one or more rows
LOGICAL STORAGE STRUCTURE

SAN: Storage area

network

NAS: network attached

storage

NFS: network file system

ASM: automatic
storage management
MULTITENANT DATABASE 12C /18C
MULTITENANT DATABASE 12C /18C

➢ Reduce cost
➢ Saving resources
➢ Easy maintenance
➢ Easy backup
➢ Easy cloning
➢ Separation of rules
(PDB Admin vs CDB Admin
MULTITENANT DATABASE 12C /18C
Every CDB has the following
containers:
1.Exactly one CDB root
container called CDB$ROOT
2.Seed PDB called PDB$SEED
(it is a template)
3. Zero or more user-created
PDBs

Note: starting from 12c R2 , we have something called application container, but it is out of scope of this
course. Application Container is optional. Application container seems to be a mini CDB, within CDB root.
ORACLE DBA TOOL
DBA Tasks and tools ❖ Managing the storage structures of the database
❖ Installing Oracle software ❖ Managing users and security
❖ Creating Oracle databases ❖ Managing database objects, such as tables, indexes,
❖ Performing upgrades of the and views
database and software to new ❖ Backing up the database and performing recovery
release levels operations when necessary
❖ Starting and shutting down the ❖ Monitoring the state of the database ❖Monitoring and
database instance
tuning database performance
❖ Reporting critical errors to Oracle Support Services
Common Oracle DBA Tasks
ORACLE DBA TOOL
DBA Tasks and tools
❖ Installing Oracle software
❖Oracle Universal Installer is a utility that
installs your Oracle software and options.
It can automatically start Oracle Database
Configuration Assistant to install a
database.
ORACLE DBA TOOL
❖Creating Oracle databases
Oracle Database Configuration
Assistant (DBCA) is a utility that
creates a database from templates
that are supplied by Oracle
ORACLE DBA TOOL
❖Database Upgrade Assistant (DBUA) is a
tool that guides you through the upgrade
of your existing database to a new Oracle
Database release
ORACLE DBA TOOL
❖Net Configuration Assistant is a utility
that enables you to configure listeners and
naming methods, which are critical
components of the Oracle Database
network.
ORACLE DBA TOOL
❖Oracle Enterprise Manager Database
Express The primary product for managing
your database, a Web-based interface
EM Express used to manage your
database in very simple ways like simple
wizard
ORACLE DBA TOOL
❖Oracle SQL Developer SQL Developer
provides another GUI for accessing your
Oracle database. SQL Developer