Unit 1: Introduction (5 Hrs.

DBA Roles and Responsibilities; Database Architecture; ORACLE logical and physical
database structure; Memory and Process Structure, SQLPLUS Overview, creating a
database;

Oracle Database architecture

Oracle Database manages the storage of data, organizes it in a manner for easy retrieval, and
provides access to data for multiple users. Let’s look at some of the concepts and components in the
Oracle architecture.

Physical vs. logical storage

Oracle database architecture depends upon two types of storage: physical and logical. Physical (disk)
storage contains all the files in the database. Logical storage structures, such as tablespaces,
segments, extents, and blocks, appear on the disk but are not part of the dataset. Logical storage
helps users locate specific data and improves the efficiency of the retrieval process, enabling a
modular data storage system in which capacity can be adjusted without affecting performance.

Logical storage is the contextual information that appears. Rather, it’s a guide to help you find
specific information or mark your place.

Database objects

Understanding the difference between physical storage structures and logical storage structures is
an excellent foundation for understanding database objects.

Logical storage structures differ from database objects in their degree of visibility. While logical
storage structures reside within the database to help organize data, database objects consist of
conceptual representations of data.

Examples of database objects include rows, tables, indexes, and views that display data.

Database objects represent the logical view of data stored in files from multiple locations. To achieve
this, database objects rely on the logical storage structures, which locate the targeted data.

Database vs. instance

The term “database” is often used to refer to the database and instance as a whole. It’s more precise
to use the term “database” when referring specifically to the physical disk or file storage that
contains the data and metadata. In contrast, an “instance” is the processes and memory set aside to
access the information within the database.

Databases exist on disk, while instances reside in memory and run as processes. Memory structures
store data and metadata. Processes help run the database, enable communication between various
components, and keep the data between memory and disk in sync.

Processes
While memory structures contain information, processes control what happens within the data
server. Put another way, processes determine the series of steps that are applied to the data. Oracle
Database processes can be organized into four primary categories:

 Client processes — allow client applications to connect to the server and run its code.

 Server processes — bridge the client and the database. In dedicated server mode, a server
caters only to one client. In shared server mode, a server can cater to multiple clients
simultaneously.

 Background processes — handles administration activities such as I/O. Examples include

process monitor (PMON), system monitor (SMON), and log writer (LGWR).

 Slave processes — assist the other processes to achieve their objectives.

Memory structures

Oracle Database stores in memory information that includes program code, sessions, locking details,
and program execution requirements, along with data. Oracle organizes this information into four
distinct areas:

 System global area (SGA): Every Oracle database instance has one SGA. The data inside SGA
is shared across multiple processes and sessions. Examples of data stored here include
cached data and shared SQL.

 Program global area (PGA): PGA contains data and control information, exclusive to each
process. A process such as database log writer has details specific to its logs in its PGA.

 User global area (UGA): UGA is memory allocated exclusively for a session. As a user session
ends, its UGA is deleted. When you connect to a database instance and execute SQL
commands and transactions, these happen inside your UGA. In dedicated server mode, UGA
is stored inside PGA, whereas in a shared server mode, UGA is stored inside SGA.

 Code areas: These are memory areas used to store and run code.

Logical Database structures

Logical structures include tablespaces, schema objects, data blocks, extents and segments.

Tablespaces
Database is logically divided into one or more tablespaces. Each tablespace creates one or more
datafiles to physically store data.

Schema objects
Schema objects are the structure that represents database's data. Schema objects include structures
such as tables, views, sequences, stored procedures, indexes, synonyms, clusters and database links.

Data Blocks
Data block represents specific number of bytes of physical database space on disk.

Extents
An extent represents continuous data blocks that are used to store specific data information.

Segments
A segment is a set of extents allocated for a certain logical structure.

Physical database structure

The physical database structure comprises of datafiles, redo log files and control files

Datafiles
Datafiles contain database's data. The data of logical data structures such as tables and indexes is
stored in datafiles of the database. One or more datafiles form a logical unit of database storage
called a tablespace.

Redo log files

The purpose of these files is to record all changes made to data. These files protect database against
failures.

Control files
Control files contain entries such as database name, name and location of datafiles and redo log files
and time stamp of database creation.

Database Administrator
A database administrator (DBA) is the information technician responsible for directing or performing
all activities related to maintaining a successful database environment. A DBA makes sure an
organization's database and its related applications operate functionally and efficiently.

The DBA is responsible for understanding and managing the overall database environment. By
developing and implementing a strategic blueprint to follow when deploying databases within their
organization, DBAs are instrumental to the ongoing efficacy of modern applications that rely on
databases for data storage and access.

Without the DBA's oversight, it is inevitable that application and system outages, downtime and
slowdowns will occur. Problems such as these result in business outages that can negatively affect
revenue, customer experience and company reputation.

DBAs are the experts for database management systems and all related topics, including DBMS
implementation and configuration; database design; SQL coding; data extraction, transformation
and loading (ETL); test data management; problem resolution; data integrity; database security;
optimization; and database backup and recovery.

Roles and Responsibilities

A DBA can, and does, assume many different roles and responsibilities within the IT department
involving database systems and applications. When adopting a new DBMS, the DBA is responsible
for designing, implementing, and maintaining the database system. Often, this includes installing the
DBMS and setting up the IT infrastructure to allow applications to access databases.

The DBA also must establish policies and procedures pertaining to the management, security,
maintenance and use of the database management system. The DBA group creates training
materials and instruct employees in the proper usage and access of the database management
system.

When problems arise, the DBA is the focal point for resolution, including troubleshooting, root cause
analysis, fine tuning and optimizing the performance of tasks and programs that access the
database.

The DBA is responsible for ensuring that databases and data are backed up appropriately and can be
recovered correctly and quickly in the event of failure. Furthermore, the DBA ensures that databases
are protected and secured. The DBA enacts measures to maintain the database integrity in terms of
data accuracy and makes sure that unauthorized users cannot access the data.

Additionally, DBAs must be good communicators. They need to be able to converse and work with
application programmers, business end users, IT and business managers and other DBAs.

Memory Structures and Processes

Regardless of the type of computer executing Oracle and the particular memory and process options
being used, every running Oracle database is associated with an Oracle instance. Every time a
database is started on a database server, Oracle allocates a memory area called the System Global
Area (SGA) and starts one or more Oracle processes. The combination of the SGA and the Oracle
processes is called an Oracle database instance. The memory and processes of an instance work to
manage the database's data efficiently and serve the one or multiple users of the associated
database.
Oracle starts an instance, then mounts a database to the instance. Multiple instances can execute
concurrently on the same machine, each accessing its own physical database. In clustered and
massively parallel systems, the Oracle Parallel Server allows a single database to mount multiple
instances. When you use Trusted Oracle in OS MAC mode, a single instance can mount multiple
databases.

Process Structure
A process is a "thread of control" or a mechanism in an operating system that can execute a series of
steps. Some operating systems use the terms job or task. A process normally has its own private
memory area in which it runs.

The process structure of Oracle is important because it defines how multiple activities can occur and
how they are accomplished. For example, two goals of a process structure might be

 to simulate a private environment for multiple processes to work simultaneously, as though

each process has its own private environment

 to allow multiple processes to share computer resources, which each process needs, but no
process needs for long periods of time

The Oracle's process architecture is designed to maximize performance.

Single-Process Oracle Instance

Single-process Oracle (also called single-user Oracle) is a database system in which all Oracle code is
executed by one process. Different processes are not used to separate execution of the parts of
Oracle and the client application program. Instead, all code of Oracle and the single user's database
application is executed by a single process.

The single process executes all code associated with the database application and Oracle.

Figure : A Single-Process Oracle Instance

Only one user can access an Oracle instance in a single-process environment; multiple users cannot
access the database concurrently. For example, Oracle running under the MS-DOS operating system
on a PC can only be accessed by a single user because MS-DOS is not capable of running multiple
processes.

Multiple-Process Oracle Instance

Multiple-process Oracle (also called multi-user Oracle) uses several processes to execute different
parts of Oracle, and a separate process for each connected user. Each process in a multiple-process
Oracle instance performs a specific job. By dividing the work of Oracle and database applications
into several processes, multiple users and applications can simultaneously connect to a single
database instance while the system maintains excellent performance. Most database systems are
multi-user, because one of the primary benefits of a database is managing data needed by multiple
users at the same time.

Figure: illustrates a multiple-process Oracle instance. Each connected user has a separate user
process and several background processes are used to execute Oracle. This figure might represent
multiple concurrent users running an application on the same machine as Oracle; this particular
configuration is usually on a mainframe or minicomputer.

In a multiple-process system, processes can be categorized into two groups: user

processes and Oracle processes.

User Processes

When a user runs an application program, such as a Pro*C program, or an Oracle tool, such as Server
Manager, Oracle creates a user process to run the user's application.

Oracle Processes

In multiple-process systems, two types of processes control Oracle: server processes and
background processes.

Oracle creates server processes to handle the requests of user processes connected to the instance.
Often, when the application and Oracle operate on the same machine rather than over a network, a
user process and its corresponding server process are combined into a single process to reduce
system overhead. However, when the application and Oracle operate on different machines, a user
process communicates with Oracle via a separate server process.

Server processes (or the server portion of combined user/server processes) created on behalf of
each user's application may perform one or more of the following:

 parse and execute SQL statements issued via the application

 read necessary data blocks from disk (datafiles) into the shared database buffers of the SGA,
if the blocks are not already present in the SGA

 return results in such a way that the application can process the information

To maximize performance and accommodate many users, a multi-process Oracle system uses some
additional Oracle processes called background processes.

An Oracle instance may have many background processes; not all are always present. The
background processes in an Oracle instance include the following:

 Database Writer (DBWR)

 Log Writer (LGWR)

 Checkpoint (CKPT)

 System Monitor (SMON)

 Process Monitor (PMON)

 Archiver (ARCH)

 Recoverer (RECO)

 Lock (LCKn)

 Snapshot Refresh (SNPn)

 Dispatcher (Dnnn)

 Server (Snnn)

Figure : illustrates each background process's interaction with the different parts of an
Oracle database, and the following sections describe each process.
CREATE DATABASE Statement

To create the new database, use the CREATE DATABASE statement.

CREATE DATABASE mynewdb

USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M
BLOCKSIZE 512,
GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M
BLOCKSIZE 512,
GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M
BLOCKSIZE 512
 MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL*Plus Overview

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or
Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI)
and the iSQL*Plus web-based user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It
enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to
perform the following:

 Format, perform calculations on, store, and print from query results

 Examine table and object definitions

 Develop and run batch scripts

 Perform database administration

We can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and
to output the results to text file, to screen, or to HTML file for browsing on the Internet. We can
generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic
reporting capability of iSQL*Plus to run a script from a web page

iSQL*Plus Architecture

iSQL*Plus is a browser-based interface which uses the SQL*Plus processing engine in a three-tier
model comprising:

 Client (Web browser).

 Middle tier (Application Server).

 Database (Oracle Database).

The iSQL*Plus Server is installed on the same machine as the Application Server. The client may or
may not also be on this machine. The middle tier coordinates interactions and resources between
the client tier and the database tier. The database is Oracle8i, Oracle9i or Oracle Database
10g accessed through Oracle Net.

Web Browser

The iSQL*Plus user interface comprises web pages served to our web browser through the Internet
or our intranet. There is no installation or configuration required for the iSQL*Plus user interface.
We only need to know the URL of the Application Server to access an available Oracle database.

Application Server

The Application Server is installed when Oracle Database is installed.

The middle tier contains a Java2 Enterprise Edition (J2EE) compliant application server. It uses Oracle
Containers for Java (OC4J) as the server engine. The Application Server enables communication and
authentication between the iSQL*Plus user interface and Oracle Database.

Oracle Database

Oracle Net components provide communication between the iSQL*Plus Application Server and
Oracle Database in the same way as for a client server installation of Oracle Database.

Use of SQL*Plus

The basic functionality is very simple. With SQL*Plus, you can do the following:

 Issue a SELECT query and view the results

 Insert, update, and delete data from database tables

 Submit PL/SQL blocks to the Oracle server for execution

 Issue DDL commands, such as those used to create, alter, or drop database objects such as
tables, indexes, and users
  Execute SQL*Plus script files

 Write output to a file

 Execute procedures and functions that are stored in a database

Product Description

SQL, which stands for Structured Query Language, is an ANSI (and ISO)

standard language used for querying, modifying, and managing relational databases.

SQL It is used to insert, delete, update, and retrieve data.

PL/SQL is a proprietary procedural language developed by Oracle as an extension to SQL.

Like SQL, it also executes inside the database. It was created as a tool for coding

PL/SQL business rules and procedures at the database level.

SQL*Plus is an Oracle-developed tool that allows you to interactively enter

SQL*Plus and execute SQL commands and PL/SQL blocks.

-------------------------------------------------The End-----------------------------------------------