Database Administration & Management BSCS

 Outline No1

📘 Database Administration – Chapter

Summary Part 1

🔧 1. Installation
Installation is the process of setting up database software on a computer system.
In Oracle Database, this includes:
 Installing the Oracle software
 Setting up storage locations
 Creating an initial database
 Configuring network settings
Basic Steps:
1. Download Oracle from the official site.
2. Run the installer.
3. Select the installation type.
4. Configure memory, users, and data paths.

💻 2. SQL*Plus
SQL*Plus is a command-line tool that allows users to:
 Connect to the Oracle database
 Execute SQL and PL/SQL commands
 Perform administrative tasks
Example Commands:
SELECT * FROM employees;
CREATE USER test IDENTIFIED BY password;
It’s fast and commonly used by professionals who prefer typing commands over using a GUI.

3. Oracle Enterprise Manager (OEM)

OEM is a graphical user interface (GUI) tool for database management.
It helps DBAs to:
 Monitor performance and health
 Manage users, roles, and security
 Perform backups and recovery
 View alerts and logs
  Automate regular tasks
OEM is ideal for those who prefer a visual interface over command-line tools.

🧰 4. DBA Tools
DBAs use various tools for day-to-day tasks.
Here are some important Oracle DBA Tools:
 Data Pump – For exporting and importing data
 📥 SQL*Loader – To load external data into tables
 💾 RMAN (Recovery Manager) – For backup and recovery
 OEM & 💻 SQL*Plus – For general database operations
These tools help maintain performance, reliability, and safety of the database.

5. Oracle Architectural Components

Oracle has two key architectural parts:

🔸 a) Oracle Server
The Oracle Server consists of:
 📁 Database – Stores all the data in physical files
 ⚙️Instance – Uses memory and processes to interact with the
database
It ensures data is properly stored, secured, and accessible.

🔸 b) Oracle Instance
An Oracle Instance is made up of:
 🧠 SGA (System Global Area) – Shared memory area that caches
data
 🔄 Background Processes – Handle tasks like saving data and user
connections
The instance must be running for the database to be used.

 Outline No 2

📦 Oracle Database Architecture – Part 2

Physical Structure
The physical structure of an Oracle database refers to the actual files stored on the disk.
There are three main types of physical files:
 🔹 Data Files – Store user and system data like tables and indexes.
 🔹 Redo Log Files – Keep a record of all changes made to the database
(used for recovery).
 🔹 Control Files – Store metadata about the database (e.g., structure,
state).
These files are critical and work together to store and protect your data.

🧠 SGA (System Global Area)

The SGA is a shared memory area that holds data and control information for the database
instance.
It is used by all connected users and helps improve performance.
SGA includes different memory components like:
 📚 Shared Pool
 📖 Data Dictionary Cache
 🧠 Library Cache
 📏 Large Pool
 📂 Database Buffer Cache
 Redo Log Buffer

📚 Shared Pool
The Shared Pool is a part of the SGA that stores commonly used data so Oracle can reuse it
without reprocessing.
It consists of:
 🧠 Library Cache
 📖 Data Dictionary Cache
This helps improve speed and efficiency by reducing the need to repeat work.

🧠 Library Cache
The Library Cache stores recently executed SQL and PL/SQL statements.
When a user sends a query, Oracle checks this cache first. If the query is already there, it skips
parsing and runs faster.
Benefit: Saves time by avoiding repeated parsing of similar queries.
📖 Data Dictionary Cache
Also known as the row cache, this part of the shared pool contains information about database
objects like:
 Table names
 Column data types
 User privileges
This cache helps Oracle quickly validate SQL commands by referring to metadata.

📏 Large Pool
The Large Pool is an optional memory area in the SGA. It is used for:
 Parallel query execution
 Backup and recovery operations
 Session memory (shared server)
It helps reduce load on the shared pool by moving large memory operations here.

👤 User Process
The User Process is the part of the system that starts when a user runs an application or
connects to the database.
 It handles the user's actions (like running a query).
 Communicates with the Server Process to send requests.

Server Process
The Server Process does the actual work on behalf of the user, such as:
 Reading data from disk
 Executing SQL commands
 Returning results to the user
There are two types:
 Dedicated Server – One server process per user
 Shared Server – Multiple users share one or more server processes

🔁 Background Processes
Oracle has several background processes that help manage the database automatically.
Here are some important ones:
 DBWn (Database Writer): Writes data from memory to disk.
 LGWR (Log Writer): Writes redo log buffer to redo log files.
 CKPT (Checkpoint): Signals DBWn to write changes during a
checkpoint.
 SMON (System Monitor): Performs crash recovery.
  PMON (Process Monitor): Cleans up failed processes.
 ARCn (Archiver): Copies redo logs for backup if archiving is enabled.
These processes run in the background to make sure the database works smoothly.

 Outline No 3

⚙️Managing an Oracle Instance

📝 Parameter File (PFILE)

The Parameter File is a text file that contains initialization parameters used to start an Oracle
instance.
 It defines how much memory to allocate, which background processes
to run, and more.
 It is usually named init<DB_NAME>.ora.
🧠 Note: You can edit this file manually using a text editor.

📦 SPFILE (Server Parameter File)

SPFILE is a binary version of the parameter file that Oracle reads automatically during startup.
 It supports dynamic changes, meaning you can update parameters
without restarting the instance.
 It is safer and more secure than the editable PFILE.
Creating SPFILE from PFILE:
CREATE SPFILE FROM PFILE;
🔄 You can also do the reverse:
CREATE PFILE FROM SPFILE;

Oracle Managed Files (OMF)

Oracle Managed Files (OMF) simplify database administration by letting Oracle manage file
names and locations.
Benefits of OMF:
 No need to manually name and track datafiles or log files.
 Oracle handles creation, deletion, and naming automatically.
To enable OMF, set:
DB_CREATE_FILE_DEST = '/path/to/your/directory';
🔁 Startup and Shutdown of Database
Managing the database includes starting and stopping the Oracle instance.
✅ Startup Stages:
1. NOMOUNT – Reads parameter file and starts background processes.
2. MOUNT – Reads control files.
3. OPEN – Opens the database for users.
STARTUP; -- Starts and opens the database
🛑 Shutdown Types:
 SHUTDOWN NORMAL – Waits for users to disconnect
 SHUTDOWN IMMEDIATE – Disconnects users immediately (safely)
 SHUTDOWN ABORT – Forces shutdown (not safe)
SHUTDOWN IMMEDIATE;

📜 Alert Log File

The Alert Log File is a special log file that records major events in the Oracle database, such
as:
 Database startup/shutdown
 Errors
 Structural changes (adding a table, file, etc.)
📌 Location (default):
$ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE_NAME>/trace/alert_<SID>.log
This file is essential for troubleshooting and monitoring.

Background Trace File

Background trace files contain detailed information about background processes (like DBWn,
LGWR, SMON).
They are generated automatically when errors or specific events occur in these background
processes.
🧩 Use them for in-depth troubleshooting by DBAs.

👤 User Trace File

A User Trace File captures details about SQL operations done by a specific user session.
Useful for:
 Debugging slow queries
 Analyzing query performance
 Tracing specific user activities
To enable tracing:
ALTER SESSION SET SQL_TRACE = TRUE;
Trace files are stored in the same location as the alert log but named per session.

 Outline No 4

Creating a Database & 📚 Data Dictionary

Creating an Oracle Database

Creating a database means setting up the physical and logical structures needed to store and
manage data.
There are two common ways to create a database:
1. Using SQL Commands (Manual Method)
You can create a database manually using SQL in SQL*Plus:
CREATE DATABASE mydb
USER SYS IDENTIFIED BY sys123
USER SYSTEM IDENTIFIED BY system123
LOGFILE GROUP 1 ('log1a.rdo','log1b.rdo') SIZE 100M,
GROUP 2 ('log2a.rdo','log2b.rdo') SIZE 100M
DATAFILE 'system01.dbf' SIZE 500M
UNDO TABLESPACE undotbs1
DEFAULT TEMPORARY TABLESPACE temp;
This command tells Oracle:
 What to name the database
 Where to store logs and data
 Who the admin users are
2. Using DBCA (Database Configuration Assistant)
DBCA is a GUI tool that guides you step-by-step through the database creation process.
✅ Benefits of using DBCA:
 No need to write SQL
 Easy for beginners
 Automatically configures memory and storage settings

📚 Oracle Data Dictionary

The Data Dictionary is a set of read-only tables that store metadata about the database.
🧠 Metadata = "data about data"
Example: Information about tables, users, indexes, views, etc.
Purpose of the Data Dictionary:
 Oracle uses it to check permissions, validate SQL, and track
schema objects
  DBAs use it to look up structure and access information

🧾 Examples of Data Dictionary Views

Here are some common data dictionary views:
🔍 View Name 📄 Description

Shows tables owned by the current

USER_TABLES
user

ALL_TABLES Shows all tables user has access to

Shows all tables in the database

DBA_TABLES
(DBA only)

USER_TAB_COLUMNS Shows columns in user’s tables

Shows information about the current

USER_USERS
user

DBA_DATA_FILES Lists all datafiles in the database

🔐 Accessing Data Dictionary

To see data dictionary information:
SELECT * FROM USER_TABLES;
SELECT * FROM USER_TAB_COLUMNS WHERE table_name = 'EMPLOYEES';

✅ Summary
 Database creation can be done manually using SQL or easily using
DBCA
 The Data Dictionary stores information about the database structure
 Views like USER_TABLES, DBA_TABLES, and ALL_TABLES are used to query
metadata

 Outline No 5

Managing Control Files & 🔁 Redo Log Files

What are Control Files?
A Control File is a very important file that stores the structure and status of the database.
🧠 It includes:
 Database name & creation time
 Locations of datafiles and redo log files
 Information about backups
 Checkpoint and synchronization info
📌 Without a control file, Oracle cannot open the database.

Managing Control Files

✅ Best Practices:
 Always have multiple copies of control files (on different disks)
 Set them up in the initialization parameter file like this:
CONTROL_FILES = ('/u01/oradata/mydb/control01.ctl',
'/u02/oradata/mydb/control02.ctl')
🔄 Backing up Control Files:
To create a backup of the control file:
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/controlfile.bkp';
You can also backup it in trace format:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

🔁 What are Redo Log Files?

Redo Log Files store a record of all changes made to the database.
If there's a crash or failure, Oracle uses these logs to recover lost data.
Each log contains:
 SQL commands executed
 Inserted/updated/deleted data
 Transaction info
They are written by the LGWR (Log Writer) background process.

Managing Redo Log Files

✅ Multiplexing Redo Logs
To protect against file loss, Oracle allows multiplexing (keeping multiple copies):
LOGFILE GROUP 1 (
'/u01/oradata/mydb/redo01a.log',
'/u02/oradata/mydb/redo01b.log'
) SIZE 100M;
🔄 Adding a Redo Log Group
You can add a new redo log group like this:
ALTER DATABASE ADD LOGFILE GROUP 3
('/u01/oradata/mydb/redo03a.log') SIZE 100M;
Dropping a Redo Log Group
ALTER DATABASE DROP LOGFILE GROUP 3;
🔐 Note: You must make sure the group is inactive before dropping it.

✅ Summary
📁 File Type 📌 Purpose 🧰 Key Actions

Control
Stores database structure info Multiplex, backup
File

Redo Log Stores all database changes (for Add, drop, multiplex,
File recovery) monitor

 Outline No 6

📦 Managing Tablespaces & Operations with

Tablespaces

📦 What is a Tablespace?
A Tablespace is a logical storage unit in an Oracle database.
It is used to organize and group data into manageable sections.
✅ Think of it like a folder that holds many files (data blocks).
Each tablespace consists of one or more datafiles that are stored on the disk.

🔹 Types of Tablespaces
📂 Tablespace
📄 Purpose
Name

SYSTEM Stores data dictionary (metadata)

Stores extra metadata & internal

SYSAUX
components

UNDO Stores undo info for transaction

 📂 Tablespace
📄 Purpose
Name

rollback

Used for sorting and temporary

TEMP
operations

Default tablespace for user-created

USERS
objects

Creating a Tablespace
You can create a new tablespace using this SQL command:
CREATE TABLESPACE myspace
DATAFILE '/u01/oradata/myspace01.dbf' SIZE 100M;
✅ This creates a tablespace named myspace with a 100MB datafile.

➕ Adding Datafile to Tablespace

If the tablespace runs out of space, you can add a new datafile:
ALTER TABLESPACE myspace
ADD DATAFILE '/u01/oradata/myspace02.dbf' SIZE 50M;

📏 Changing Tablespace Size (Resizing)

You can increase the size of an existing datafile:
ALTER DATABASE DATAFILE '/u01/oradata/myspace01.dbf'
RESIZE 200M;

🚫 Making a Tablespace Read-Only

Sometimes you want to protect data from being changed.
You can make the tablespace read-only:
ALTER TABLESPACE myspace READ ONLY;
To allow changes again:
ALTER TABLESPACE myspace READ WRITE;

❌ Dropping a Tablespace
To remove a tablespace and its datafiles:
DROP TABLESPACE myspace INCLUDING CONTENTS AND DATAFILES;
⚠️Warning: This will permanently delete the tablespace and its data.
✅ Summary
Operation 🔍 Command Example

Create
CREATE TABLESPACE myspace DATAFILE ...
tablespace

Add datafile ALTER TABLESPACE myspace ADD DATAFILE ...

Resize datafile ALTER DATABASE DATAFILE ... RESIZE ...

Make read-only ALTER TABLESPACE myspace READ ONLY;

Drop
DROP TABLESPACE myspace INCLUDING CONTENTS AND DATAFILES;
tablespace

 Outline No 7

💾 Data File Management, 📦 Segments & 🧱

Blocks

💾 Data File Management

Datafiles are the physical files on the disk that store all the data in an Oracle database.
Each tablespace consists of one or more datafiles.
✅ Key Facts:
Datafiles are created when a tablespace is created.

They store tables, indexes, and other objects.

Datafiles can be resized or added when more space is needed.

Common Operations:
➕ Add a new datafile:
ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/users02.dbf' SIZE 100M;
📏 Resize a datafile:
ALTER DATABASE DATAFILE '/u01/oradata/users01.dbf'
RESIZE 200M;
Delete a datafile (when dropping the entire tablespace):
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
📦 Segments
A Segment is a set of extents that store a specific type of database object like:
 A table
 An index
 A temporary area
Each time you create a table, Oracle allocates a segment to store its data.
✅ Types of Segments:
📦 Segment Type 📄 Description

Data Segment Stores table data

Index Segment Stores index data

Undo Segment Stores undo info for transactions

Temporary Used for temporary operations (e.g.,

Segment sorting)

🧱 Blocks
A Block is the smallest unit of storage in the Oracle database.
 Oracle stores data in data blocks (also called Oracle blocks).
 A block typically holds multiple rows of a table.
✅ Example: One datafile contains many blocks, and each block holds rows.
📐 Block Size:
 Common sizes: 2KB, 4KB, 8KB, 16KB
 Block size is defined at the database creation time and can't be
changed later.

🧱➡️Extents ➡️Segments ➡️Tablespaces ➡️Datafiles

Here's how everything connects:
🧱 Blocks ➡ form ➡ Extents ➡ form ➡ Segments ➡ stored in ➡ Tablespaces ➡
stored in ➡ Datafiles

✅ Summary Table
🔍 Concept 📌 Description

Datafile Physical file on disk that stores

🔍 Concept 📌 Description

data

Tablespac Logical storage unit that holds

e datafiles

Set of extents for a database

Segment
object

Smallest unit of data storage in

Block
Oracle

 Outline No 8

🔄 Managing Undo Data & Undo Data Statistics

🔄 What is Undo Data?

Undo data is used by Oracle to:
 Rollback changes when a transaction fails
 Provide read-consistency (see correct data even during edits)
 Support flashback features
Undo data is stored in the UNDO tablespace.

🧠 Why is Undo Important?

 If a user cancels a transaction, Oracle uses undo to reverse changes
 Helps other users see consistent data
 Required for recovery during failures

Managing Undo Tablespace

Undo tablespace is automatically managed by Oracle when using Automatic Undo
Management.
You can check the undo tablespace name:
SHOW PARAMETER undo_tablespace;
Create an undo tablespace:
CREATE UNDO TABLESPACE undotbs1
DATAFILE 'undo01.dbf' SIZE 200M;
Switch to a different undo tablespace:
ALTER SYSTEM SET undo_tablespace = undotbs1;

📊 Undo Data Statistics

You can monitor undo usage using the data dictionary views:
SELECT * FROM V$UNDOSTAT;
This view helps you:
 Analyze undo space usage
 Understand how much undo is generated
 Troubleshoot undo-related errors

👤 Managing Tables and Users

📋 Managing Tables
➕ Create a Table
CREATE TABLE employees (
emp_id NUMBER,
name VARCHAR2(50),
salary NUMBER
);
✏️Modify a Table
Add a new column:
ALTER TABLE employees ADD (email VARCHAR2(100));
Rename a column:
ALTER TABLE employees RENAME COLUMN name TO full_name;
❌ Drop a Table
DROP TABLE employees;

👤 Managing Users
➕ Create a New User
CREATE USER john IDENTIFIED BY john123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Grant Privileges to User
Allow user to connect and create tables:
GRANT CONNECT, RESOURCE TO john;
🔒 Lock a User Account
ALTER USER john ACCOUNT LOCK;
❌ Drop a User
DROP USER john CASCADE;

✅ Summary Table
🔍 Task 📌 SQL Command Example

Create table CREATE TABLE employees (...)

Add column ALTER TABLE employees ADD (...)

Create user CREATE USER john IDENTIFIED BY john123;

Grant privileges GRANT CONNECT, RESOURCE TO john;

Monitor undo SELECT * FROM V$UNDOSTAT;

Create undo
CREATE UNDO TABLESPACE undotbs1 ...
tablespace

 Outline No 9

🔍 Indexes Management

📘 What is an Index?
An index is like a shortcut to find data faster in a table.
✅ Example: Just like an index in a book helps you find a topic quickly, a database index helps
you find rows quickly.

⚙️Why Use Indexes?

 Speeds up SELECT queries
 Reduces time for searching large tables
 Automatically used by Oracle during queries

Creating an Index
CREATE INDEX idx_emp_name
ON employees(name);
Creates an index named idx_emp_name on the name column of the employees table.
🧹 Dropping an Index
DROP INDEX idx_emp_name;
Removes the index.

🔄 Rebuilding an Index
Sometimes indexes become fragmented. You can rebuild them:
ALTER INDEX idx_emp_name REBUILD;

Maintaining Data Integrity & Constraints

What is Data Integrity?

Data integrity ensures the correctness and consistency of data in a table.
Oracle uses Constraints to enforce data integrity.

📋 Types of Constraints
🔗 Constraint
📌 Description
Type

PRIMARY KEY Uniquely identifies each row

FOREIGN KEY Links two tables together

UNIQUE Ensures no duplicate values

NOT NULL Makes sure a column cannot be empty

Ensures a value meets a condition (e.g.

CHECK
salary > 0)

Creating Constraints
✅ Primary Key:
CREATE TABLE students (
student_id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
✅ Foreign Key:
CREATE TABLE enrollments (
student_id NUMBER,
course_id NUMBER,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
✅ Not Null:
ALTER TABLE students
MODIFY name NOT NULL;
✅ Check:
ALTER TABLE students
ADD CONSTRAINT chk_age CHECK (age >= 18);

❌ Dropping a Constraint
ALTER TABLE students
DROP CONSTRAINT chk_age;

🔐 Managing Privileges

🧑‍💻 What are Privileges?

Privileges allow users to perform actions on the database.
There are two types:
🔐 Type 📄 Example

System Create tables, users,

Privileges sessions

Object Select, insert, update on a

Privileges table

Granting Privileges
Grant system privileges:
GRANT CREATE TABLE, CREATE SESSION TO john;
Grant object privileges:
GRANT SELECT, INSERT ON employees TO john;

🧽 Revoking Privileges
REVOKE INSERT ON employees FROM john;
👑 Granting with ADMIN Option
Allows the user to grant the privilege to others:
GRANT CREATE USER TO admin_user WITH ADMIN OPTION;

✅ Summary Table
📌 Task SQL Example

Create index CREATE INDEX idx_name ON table(column);

Add NOT NULL

ALTER TABLE table MODIFY column NOT NULL;
constraint

Grant system
GRANT CREATE TABLE TO user;
privilege

Revoke object
REVOKE SELECT ON table FROM user;
privilege

 Outline No 10

🌐 Basic Oracle Net Architecture

🧠 What is Oracle Net Architecture?

Oracle Net is the communication layer that allows a client to connect to an Oracle Database
Server, even if they are on different machines or networks.
It helps Oracle databases support client-server communication across local or wide area
networks (LAN/WAN).

🌍 Types of Networks

Local Area Network (LAN)

 Short-range (within the same building)
  Fast and stable
 Used for internal office communication
🌐 Wide Area Network (WAN)
 Long-distance connections (e.g., between cities)
 Slower than LAN
 Used to connect remote clients and servers

📡 Oracle Net Services

📡 What is Oracle Net?

Oracle Net is the network software that connects clients to Oracle databases over a network.
It includes:
 Listener
 Oracle Net Configuration files
 Naming methods (TNSNAMES, Easy Connect, etc.)

📂 Important Oracle Net Files:

📄 File
📌 Purpose
Name

Defines the listener

listener.ora
configuration

tnsnames.ora Contains client connection info

Controls naming methods and

sqlnet.ora
settings

👥 Oracle Shared Server

👥 What is It?
Oracle Shared Server allows multiple clients to share a pool of server processes, instead of
creating one dedicated process per user.
✅ Best for: Large number of users with limited resources
🔁 Benefits:
 Saves memory
 Handles many users
 Good for web-based apps

🔁 Connection Manager (CMAN)

🔁 What is Oracle Connection Manager?

Connection Manager (CMAN) acts like a traffic controller that manages connections between
clients and databases.
It is useful when:
 You want to control access to databases
 You want to reduce network load
 You want connection filtering
✅ CMAN can support firewall-like filtering, connection routing, and multiplexing.

🔗 Oracle Net Connections

🔗 Types of Oracle Net Connections:

🔗 Type 📌 Description

Dedicated
Each client has its own server process
Server

Shared Server Clients share a pool of server processes

Local Both client and database are on the same

Connection machine

Remote Client connects to a database on another

Connection machine
✏️Example: TNS Connection Entry
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
✅ This is written in the tnsnames.ora file and tells the client how to connect.

✅ Summary Table
🧩 Component 📌 Description

Software that connects clients to

Oracle Net
DB

Listener Waits for client requests

Shares server processes among

Shared Server
users

Connection Controls and filters client

Manager connections

Stores database connection

TNSNAMES.ORA
details

 Outline No 11

Server-Side Configuration
Server-side configuration refers to settings and processes that are configured on the database
server to allow client connections and ensure smooth communication between clients and the
Oracle database.
The main part of this configuration is the Listener.
👂 The Listener Process
The Listener is a special program that runs on the database server.
It is responsible for:
 Listening for connection requests from client computers
 Establishing communication between the client and the database
Once a connection is made, the listener hands over the session to the database and continues
waiting for new requests.

🔎 Key Points about Listener:

 It runs continuously in the background
 It listens on a specific port (default is 1521)
 Without the listener, clients cannot connect to the Oracle database
from outside the server

⚙️Configuring the Listener

The listener is configured using a file called listener.ora.
This file contains:
 The host name or IP address of the server
 The port number the listener will use
 The database service name (like ORCL)

📄 Example Configuration Includes:

 Listener name
 Protocol (e.g., TCP)
 Host and port
 SID or service name for the database

👥 Sessions
A session is a connection between a user (client) and the Oracle database.
 When a client connects, a session is created
 The listener helps to create and transfer this session to the database
 After that, the listener is no longer involved in the session
 Creating and Managing the Listener
The listener is usually managed using Oracle's LSNRCTL (Listener Control) tool.
With it, you can:
 Start the listener
 Stop the listener
 Check its status
 Reload the configuration (apply changes)
You can also use Oracle Net Manager (GUI tool) to manage the listener with a graphical
interface.

✅ Summary (Easy Explanation):

🔑
📝 Description
Component

A program that waits for client

Listener
connections

File that configures how the listener

listener.ora
behaves

Connection between a user and the

Session
database

LSNRCTL Command-line tool to control the

Tool listener

Net GUI tool to create and manage

Manager listeners easily

 Outline No 12

Client-Side Configuration
Client-side configuration allows a client computer to connect to an Oracle database over a
network.
It involves setting up network details like database name, host address, port number, and service
name.
Clients use Oracle Net to communicate with the database server.
🔗 Host Naming Method
The Host Naming Method is the simplest way to connect to a database.
✅ In this method:
 No configuration files are needed
 You use the Easy Connect syntax to connect
📌 Example:
sqlplus username/password@hostname:port/servicename
🟢 It is good for small networks or testing.

📁 Local Naming Method

The Local Naming Method uses a file called tnsnames.ora on the client computer.
This file stores connection details in a friendly name format.
📄 Example of tnsnames.ora entry:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
To connect using this method:
sqlplus username/password@ORCL
🟢 Useful in larger networks or when you want easy naming for databases.

Net Assistant
Oracle Net Assistant is a graphical tool used to:
 Configure tnsnames.ora, listener.ora, and sqlnet.ora
 Add or modify database service entries
 Easily create connection profiles
📍 Open Net Assistant with:
netasst
🟢 Good for non-technical users or for easier setup.
⚙️Other Configuration Files
📄 File
📌 Purpose
Name

Stores database connection entries (local

tnsnames.ora
naming)

Defines naming methods and security

sqlnet.ora
settings

Used on server-side to define listener

listener.ora
behavior

👥 Oracle Shared Server

🧠 What is Oracle Shared Server?

The Shared Server architecture allows multiple clients to share a small number of server
processes.
🔁 Instead of giving each user a dedicated server process, users are connected to a dispatcher,
which assigns work to available shared server processes.

✅ Benefits:
 Saves memory and CPU on the server
 Can support hundreds or thousands of users
 Ideal for web-based or OLTP systems

⚙️Configuration of Oracle Shared Server

To enable it, set these parameters in the server:
ALTER SYSTEM SET SHARED_SERVERS = 5;
You can also configure:
 Number of dispatchers
 Maximum shared servers
 Session limits
These settings go into the init.ora or spfile.
📄 Key Terms:
💡 Term 📝 Description

Accepts client requests and routes

Dispatcher
them

Shared Handles database tasks from multiple

Server clients

Dedicated Each client has a private server

Server process

✅ Summary Table
📌 Topic 📝 Description

Host Naming Direct connect using hostname &

Method service name

Local Naming Uses tnsnames.ora for named

Method connections

Net Assistant GUI tool to configure client network files

Server processes shared among many

Shared Server
users

 Outline No 13

💾 Backup and Recovery

Backup and Recovery are essential to protect your Oracle database from data loss.
 Backup means creating a copy of your data
 Recovery means restoring data from backup when something goes
wrong
🟢 Backups help when:
 Hardware fails
 Data is deleted accidentally
 The database crashes
 Instance and Media Recovery
🔄 Instance Recovery
 Happens automatically after a database crash (like power failure)
 Oracle uses redo logs to recover committed transactions
✅ No user action is needed. It’s fast and automatic.

💿 Media Recovery
 Needed when physical files (like datafiles) are lost or corrupted
 Uses backup + archive logs to restore data
 Must be done manually or with tools like RMAN

⚙️Archive Log Mode Configuration

🧾 What is Archive Log Mode?
 Oracle can archive (save) redo logs for later use
 Needed for media recovery
 Enables point-in-time recovery

🔁 How to Enable Archive Log Mode

1. Shutdown the database
2. Mount the database
3. Enable archive mode:
4. ALTER DATABASE ARCHIVELOG;
5. Open the database

🔄 User-Managed Complete Recovery

This method is used when the user performs recovery without RMAN.
Steps include:
1. Restore the missing or damaged data file from backup
2. Use archive logs to apply all changes
3. Recover the data file:
4. RECOVER DATAFILE 'file_path';
5. Bring the file online:
 6. ALTER DATABASE DATAFILE 'file_path' ONLINE;

📥 Loading Data into Database

Oracle provides multiple ways to load data:
📦 Tool 📌 Use

Loads data from text files (CSV,

SQL*Loader
etc.)

High-speed import/export of
Data Pump
large data

External Read external files like database

Tables tables

Manual
Insert data using SQL commands
Insert

📈 Tuning Tools
Oracle offers tools to monitor and improve performance:
Tool 📌 Use

AWR (Automatic Workload

Collects performance data
Repository)

ADDM (Automatic DB Diagnostic

Analyzes AWR reports
Monitor)

Generates reports on database

Statspack
performance

TKPROF Formats SQL trace files for analysis

🧠 Sizing Shared Pool

The Shared Pool stores:
 SQL and PL/SQL code
  Data dictionary cache
 Execution plans
🔍 If it's too small:
 Queries may be re-parsed
 Performance drops
✅ Use parameters like:
SHARED_POOL_SIZE = 200MB
Use AWR/ADDM reports to analyze usage.

💾 Sizing Buffer Cache

The Buffer Cache stores recently used data blocks.
🔍 If it’s too small:
 More disk reads occur
 Slower performance
✅ Use parameter:
DB_CACHE_SIZE = 500MB
Monitor using views like V$DB_CACHE_ADVICE.

💡 I/O Issues
Input/Output (I/O) problems happen when:
 The system reads/writes too often to disk
 There are slow hard drives
 Disk contention (many processes using the same disk)

📌 Tips to Reduce I/O Issues:

 Increase buffer cache size
 Use faster storage (SSD)
 Spread data across multiple disks
 Use Oracle ASM (Automatic Storage Management)

✅ Summary Table
📌 Topic 📝 Description

Backup Creating a copy of data

Instance Recovery Automatic recovery after crash

 📌 Topic 📝 Description

Manual recovery using backups and

Media Recovery
logs

Archive Log Mode Enables full recovery features

User-Managed
Manual datafile recovery
Recovery

SQL*Loader, Data Pump, External

Loading Data
Tables

Tuning Tools AWR, ADDM, Statspack, TKPROF

Stores parsed SQL and data

Shared Pool
dictionary info

Caches recently accessed data

Buffer Cache
blocks

Disk performance problems &

I/O Issues
solutions

 Outline No 14 Final

⚙️Tuning Rollback Segments

Rollback segments are used to store undo information (data before changes).
This helps in:
 Rolling back transactions
 Reading consistent data

🔧 Tuning Includes:
 Using the right size of rollback segments
 Having enough rollback segments to avoid waits
 Placing rollback segments on fast disks
🟢 Goal: Reduce contention and make transactions faster.
🔗 Latches
A latch is a low-level memory lock used by Oracle to protect shared memory areas.
🔧 Tuning Tips:
 Reduce contention by spreading work across processes
 Increase shared pool size if latch contention is high
 Use AWR or Statspack reports to find latch problems
Latches are fast but should be used efficiently to avoid wait issues.

👥 Tuning Shared Servers

Oracle Shared Server allows multiple users to share server processes.
🔧 Tuning Includes:
 Adjusting number of dispatchers and shared servers
 Monitoring response time of users
 Using V$SHARED_SERVER views for analysis
🟢 Benefits: Saves memory and handles many users effectively.

🔒 Types of Locks
Locks are used to prevent conflicts when multiple users access the same data.
🔐 Types:
🔒 Lock Type 📌 Description

Lock rows during

DML Locks
insert/update/delete

Lock objects like tables while

DDL Locks
altering

Internal Used by Oracle internally (like

Locks redo logs)

User Locks Created by users manually

Good lock management ensures data safety and avoids deadlocks.

📦 Block Efficiency & Storage Hierarchy
Data is stored in Oracle in the form of:
📦 Blocks → Extents → Segments → Tablespaces
 Block: Smallest unit of storage
 Efficient use of blocks improves performance
 Wasted block space = wasted memory and slower access
Tune block size and usage for better performance.

⛔ Avoiding Dynamic Allocation

Dynamic allocation refers to Oracle auto-assigning memory or resources during runtime.
❌ Problems:
 Can be unpredictable
 May lead to performance issues under heavy load
✅ Solution:
 Set manual values for parameters like:
o SHARED_POOL_SIZE
o DB_CACHE_SIZE
 Monitor and adjust as needed

📊 Statistics
Statistics help Oracle choose the best execution plan.
🧮 Includes:
 Table size
 Index usage
 Row count
 Column values
Use:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME');
🟢 Regularly update statistics for better query performance.
⚖️PCTFREE and PCTUSED
These are settings that control space usage in data blocks.
📌 PCTFREE:
 % of block kept empty for future updates
 Prevents row chaining
📌 PCTUSED:
 % of block that must be used before it is added back to the free list
✅ Balancing these values helps in efficient storage and reduces fragmentation.

🔎 Monitoring Index Usage

Indexes speed up data access, but unused indexes waste space.
📊 Monitoring Tools:
 view (must be enabled)
V$OBJECT_USAGE
 Oracle AWR/ADDM reports
Enable index monitoring:
ALTER INDEX index_name MONITORING USAGE;
Then later:
SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'INDEX_NAME';
🟢 Helps identify useless indexes and improve performance.

✅ Summary Table
📌 Topic 📝 Description

Store undo data, must be sized and placed

Rollback Segments
properly

Latches Protect memory, avoid contention

Shared Server Adjust server processes for multi-user

Tuning systems

Locks Manage concurrent data access safely

Block Efficiency Use blocks properly to avoid space waste

 📌 Topic 📝 Description

Avoid Dynamic
Use manual tuning for better control
Allocation

Statistics Help Oracle optimize queries

PCTFREE / PCTUSED Control block storage space

Index Monitoring Identify and remove unused indexes