Unit 1 Introduction Database Administration: 7 hrs.

1.1 Introduction
Overview of oracle database:
Oracle Database is a multi-model database management system produced and marketed by
Oracle Corporation. It is a database commonly used for running online transaction processing
(OLTP), data warehousing (DW) and mixed (OLTP & DW) database workloads.. Some of the
key features of Oracle Database include:
High performance and scalability,
Reliability and availability,
Security and compliance,
Ease of use and administration,
Support for a wide range of data types and models.
Oracle Database is used by a wide range of organizations, including businesses, governments,
and educational institutions. It is a popular choice for mission-critical applications that require
high performance, scalability, and reliability. Here are some of the benefits of using Oracle
Database:
1) High performance: Oracle Database is designed to deliver high performance for a wide range
of workloads.
2) Scalability: Oracle Database can be scaled to meet the needs of even the most demanding
applications.
3) Reliability: Oracle Database is designed to be highly reliable and available
4) Security: Oracle Database provides a comprehensive set of security features to protect your
data.
5) Compliance: Oracle Database is certified to meet a wide range of compliance standards
6) Ease of use: Oracle Database is easy to use and administer
7) Support: Oracle provides comprehensive support for Oracle Database.
Relational Database Concepts:
A relational database is a database that stores and organizes data in a series of tables. Each
table consists of rows and columns, and each row represents a single record of data. The
columns in a table contain the same type of data for each record, such as names, addresses, or
phone [Link] relational model is a set of rules that define how data should be stored and
organized in a database. These rules include:
Data should be stored in tables.
Each table should have a unique identifier, called a primary key.
Each row in a table should be unique.
Each column in a table should contain the same type of data.\
Relationships between tables should be defined using foreign keys.
Relational databases have a number of advantages over other types of databases, including:
Data is stored in a logical and efficient way.
Data can be easily accessed and manipulated.
Data can be easily shared between different applications.
Data is protected from corruption and damage.
Here are some of the key concepts of relational databases:
1) Tables: A table is a collection of data organized into rows and columns. Each row represents
a single record, and each column represents a single piece of data about that record.
2) Rows: A row is a horizontal record in a table. Each row contains a unique set of data for a
single record.
3) Columns: A column is a vertical column in a table. Each column contains the same type of
data for all rows in the table.
4) Primary key: A primary key is a column that uniquely identifies each row in a table. No two
rows in a table can have the same value in the primary key column.
5) Foreign key: A foreign key is a column that references a primary key in another table.
Foreign keys are used to create relationships between tables.
6) Relationships: A relationship is a connection between two tables. Relationships are created
using foreign keys.
7) Queries: A query is a statement that is used to retrieve data from a database. Queries are
written in a language called SQL (Structured Query Language).
8) Views: A view is a virtual table that is created from one or more tables. Views are used to
provide a simplified view of data or to hide sensitive data.
9) Indexes: An index is a data structure that is used to improve the performance of queries.
Indexes are created on columns that are frequently used in queries.
10) Transactions: A transaction is a unit of work that is performed on a database. Transactions
are atomic, which means that they either succeed completely or they fail completely.
11) Backups: A backup is a copy of a database that is used to restore the database in case of a
failure. Backups should be created regularly.
12) Security: Relational databases can be secured using a variety of methods, including
passwords, encryption, and access control lists.

Oracle multitenant architecture

Oracle Multitenant is a database architecture that allows multiple independent users or tenants
to share the same Oracle database. This can be a cost-effective way to provide database
services to a large number of users, as it eliminates the need to create and maintain separate
databases for each tenant. Oracle Multitenant is based on the concept of a container database
(CDB). A CDB is a single database that can contain multiple pluggable databases (PDBs). A
PDB is a self-contained database that can be created, dropped, and moved independently of
other PDBs in the CDB. When a PDB is created, it is isolated from the other PDBs in the CDB.
This isolation is achieved by using a combination of physical and logical separation. Physical
separation is achieved by using separate data files for each PDB. Logical separation is
achieved by using separate schemas for each PDB. Oracle Multitenant is a powerful and
flexible database architecture that can be used to provide database services to a large number
of users. It is a cost-effective, scalable, and secure way to share a single database among
multiple tenants.

Oracle Data Guard

Oracle Data Guard is a disaster recovery solution that provides a standby database that can be
used to recover from a failure of the primary database. Data Guard can be used to protect data
from a variety of disasters, including hardware failures, software failures, and natural disasters.
Data Guard works by continuously replicating changes from the primary database to the
standby database. The standby database can be located on the same physical system as the
primary database, or it can be located on a remote system.
There are two main types of Data Guard configurations:
Physical standby: In a physical standby configuration, the standby database is a physical copy
of the primary database. This means that the standby database has the same data and
structure as the primary database.
Logical standby: In a logical standby configuration, the standby database is a logical copy of
the primary database. This means that the standby database has the same data as the primary
database, but it has a different structure.
Data Guard provides a variety of features to protect data, including:
1) Data protection: Data Guard protects data from a variety of disasters, including hardware
failures, software failures, and natural disasters.
2) Data availability: Data Guard ensures that data is always available, even in the event of a
failure of the primary database.
3) Data consistency: Data Guard ensures that data is always consistent, even when changes
are made to the primary database.

Oracle RAC
Oracle Real Application Clusters (RAC) is a high-availability, scalable, and flexible database
solution that enables multiple servers to work together to provide a single, unified database.
RAC is a popular choice for businesses that need to ensure that their data is always available
and that they can scale their database as their needs grow. RAC works by connecting multiple
servers together into a cluster. Each server in the cluster runs an Oracle database instance. The
database instances in the cluster are connected to each other using a high-speed
network. When a user connects to a RAC database, the database will automatically distribute
the user's workload across the servers in the cluster. This ensures that the user's queries are
executed as quickly as possible. RAC also provides a number of features that help to ensure
the availability of the database. For example,
RAC uses a technique called mirroring to create multiple copies of the database. If one server in
the cluster fails, the database can be brought online on another server in the cluster.

Oracle ASM
Oracle ASM or Automatic Storage Management is a software layer that provides a high-
performance, scalable, and flexible storage solution for Oracle Database. ASM is a disk group
manager that provides a single logical view of disk storage, regardless of the physical location
of the disks. ASM also provides a number of features that improve the performance and
availability of Oracle Database, including:
1) Striping: ASM stripes data across multiple disks, which improves performance by spreading
the I/O load across multiple disks.
2) Mirroring: ASM mirrors data across multiple disks, which improves availability by providing a
backup copy of the data in case of a disk failure.
3) Rebalancing: ASM rebalances data across disks as disks are added or removed, which
ensures that data is evenly distributed across all disks.
4) Thin provisioning: ASM thin provisions storage, which means that only the space that is
actually used by the database is allocated on disk. This can save space and improve
performance by eliminating the need to preallocate space for the database.

Oracle Cloud
Oracle Cloud is a suite of cloud computing services that runs on the Oracle Cloud Infrastructure
(OCI). OCI is a global, multi-tenant cloud infrastructure that offers a broad range of services,
including compute, storage, networking, databases, analytics, machine learning, and Internet of
Things (IoT). Oracle Cloud is a fully managed cloud service, which means that Oracle takes
care of all the underlying infrastructure and management tasks. This frees up customers to
focus on their applications and data. Oracle Cloud offers a wide range of services that can be
used to build and run a wide variety of applications. These services include:

Compute: Oracle Cloud offers a variety of compute options, including virtual machines, bare
metal servers, and containers.
Storage: Oracle Cloud offers a variety of storage options, including object storage, block
storage, and file storage.
Networking: Oracle Cloud offers a variety of networking options, including virtual private
networks (VPNs), load balancers, and firewalls.
Databases: Oracle Cloud offers a variety of database options, including Oracle Database,
MySQL, and PostgreSQL.
Analytics: Oracle Cloud offers a variety of analytics options, including Oracle Analytics Cloud,
Hadoop, and Spark.
Machine learning: Oracle Cloud offers a variety of machine learning options, including Oracle
Machine Learning Cloud, TensorFlow, and PyTorch.
Internet of Things (IoT): Oracle Cloud offers a variety of IoT options, including Oracle IoT
Cloud, MQTT, and Kafka.

Why Oracle Cloud:

Oracle Cloud is a highly secure cloud service. Oracle has a long history of security innovation
and has invested heavily in security for Oracle Cloud. Oracle Cloud is certified by a number of
independent security organizations, including the Cloud Security Alliance (CSA), the National
Institute of Standards and Technology (NIST), and the Payment Card Industry Data Security
Standard (PCI DSS). Oracle Cloud is a reliable cloud service. Oracle has a proven track record
of reliability and uptime. Oracle Cloud is available in 19 regions and 69 availability domains
around the world. Oracle Cloud is a cost-effective cloud service. Oracle Cloud offers a variety of
pricing options that can be tailored to the specific needs of each customer. Oracle Cloud also
offers a number of free services, including a free tier and always free resources.
Oracle Cloud is a global cloud service. Oracle Cloud is available in 19 regions and 69
availability domains around the world. This makes Oracle Cloud a good choice for businesses
that need to support a global workforce. Oracle Cloud is a good choice for a wide variety of
businesses. Oracle Cloud is a good choice for businesses that need a secure, reliable, and
cost-effective cloud service. Oracle Cloud is also a good choice for businesses that need a
global cloud service.
Database Administration Tasks
Database Administrators play a crucial role in ensuring the availability, reliability, and security of
an organization's data. They work closely with software developers, system administrators, and
other IT professionals to meet business requirements related to data management and storage.
The specific duties of a DBA can vary depending on the organization's size, the complexity of
the database systems, and the specific DBMS being used.
1) Designing and creating databases: Database administrators (DBAs) work with
stakeholders to understand the data requirements of an organization and then design and
create databases that meet those requirements.
2) Managing database security: DBAs are responsible for ensuring that databases are secure
and that only authorized users have access to data. This includes setting up user accounts and
permissions, as well as implementing security measures such as firewalls and encryption.
3) Maintaining database performance: DBAs monitor database performance and take steps
to improve performance when necessary. This may involve optimizing queries, defragmenting
tables, or adding more memory to the database server.
4) Backing up and restoring databases: DBAs are responsible for backing up databases
regularly in case of data loss or corruption. They also need to be able to restore databases from
backups quickly and easily.
5) Troubleshooting database problems: DBAs are responsible for troubleshooting database
problems and resolving issues as quickly as possible. This may involve identifying the source of
the problem, analyzing logs, and making changes to the database or its environment.

1.2 Oracle Database Architecture:

The Oracle Database architecture is a client-server architecture, with the database server and
client connections running in separate processes. The database server is responsible for
managing the data and resources, while the client connections communicate with the server to
access and manipulate data.
The Oracle Database architecture consists of the following components:
• Database instance: A database instance is a collection of memory structures and processes
that together manage a single database. Each database has at least one database instance.
• System Global Area (SGA): The SGA is a shared memory area that contains data and control
information for the database instance. The SGA is divided into several memory pools, each of
which is used for a specific purpose.
• Program Global Area (PGA): The PGA is a private memory area that is allocated to each user
session. The PGA contains data and control information for the session, such as the user's SQL
statements and variables.
• Background processes: The database instance runs several background processes that
perform tasks such as managing the database files, logging changes to the database, and
recovering from failures.
• Data files: The data files store the actual data of the database. The data files are organized
into extents, which are groups of contiguous blocks.
• Redo logs: The redo logs record all changes made to the database. The redo logs are used to
recover the database from failures.
• Control files: The control files contain information about the database, such as the names of
the data files and redo logs.
The Oracle Database architecture can be scaled up to handle large amounts of data and traffic.
This can be done by adding more memory, CPU, and storage to the database server. The
Oracle Database can also be clustered to provide high availability and scalability.

Oracle Database Instance

An Oracle database instance is a collection of memory structures and processes that manage
the database files. It is the basic unit of operation for an Oracle database.
An Oracle database instance consists of three main parts:
System Global Area (SGA): The SGA is a shared memory area that is allocated when the
instance is started and released when it is shut down. It contains data and control information
that is used by all processes in the instance.
Program Global Area (PGA): The PGA is a private memory area that is allocated to each user
process when it connects to the database. It contains data and control information that is used
by that process only.
Background Processes: The Oracle database uses a number of background processes to
perform tasks such as managing the database files, processing queries, and recovering from
errors.
When an Oracle database instance is started, the following steps occur:
The SGA is allocated.
The background processes are started.
The database files are opened.
The database is ready for use.
When an Oracle database instance is shut down, the following steps occur:
The background processes are stopped.
The database files are closed.
The SGA is released.
The Oracle database instance is a critical component of the Oracle database system. It provides
the memory and processes that are needed to manage the database files and to process
queries.

Oracle Memory Structure

The Oracle memory structure is a collection of memory areas that are used to store data and
control information for an Oracle database instance. The Oracle memory structure is divided
into two main areas:
System Global Area (SGA): The SGA is a shared memory area that is allocated when the
instance is started and released when it is shut down. It contains data and control information
that is used by all processes in the instance.
The SGA is further divided into the following sub-areas:
1) Database Buffer Cache: The database buffer cache is used to store data blocks that have
been read from disk. This allows the database to access data without having to read it from disk
again.
2) Redo Log Buffer: The redo log buffer is used to store redo records. Redo records are used to
record changes that have been made to the database. This allows the database to recover from
errors or crashes.
3) Shared SQL Area: The shared SQL area is used to store parsed SQL statements. This
allows the database to reuse parsed SQL statements, which can improve performance.
4) Control Data Structures: The control data structures store information about the database,
such as the database name, the database size, and the database configuration.

Program Global Area (PGA): The PGA is a private memory area that is allocated to each user
process when it connects to the database. It contains data and control information that is used
by that process only.
The PGA is further divided into the following sub-areas:
1) Stack Area: The stack area is used to store local variables and subroutine return addresses.
2) Data Area: The data area is used to store data that is being processed by the user process.
3) Sort Area: The sort area is used to store data that is being sorted.

Process Architecture and Structures

Database process architecture and structure refers to the way that database processes are
organized and managed within a database system. It is a critical component of database
management, and it can help database systems to improve their efficiency, effectiveness, and
scalability.
There are many different ways to design database process architecture and structure. Some
common approaches include:
1) Centralized architecture: In a centralized architecture, all database processes are managed
by a single server. This is the simplest and most common approach, but it can be inefficient for
large databases.
2) Distributed architecture: In a distributed architecture, database processes are distributed
across multiple servers. This can improve efficiency for large databases, but it can be more
complex to manage.
3) Hybrid architecture: A hybrid architecture combines elements of centralized and distributed
architectures. This can be the most efficient approach for large databases, but it can also be the
most complex to manage.
Server and Client Process
A server-client model is a common architectural pattern used in networked applications, where a
server program provides services or resources to multiple client programs. Here's an overview
of the server and client processes in this model:

Server Process:
1) Initialization: The server process initializes and starts listening for incoming client connections
on a specific port or network address.
2) Connection Establishment: When a client wants to communicate with the server, it initiates a
connection request to the server's address.
3) Connection Acceptance: The server process accepts the incoming connection request,
creating a dedicated communication channel or socket for that client.
4) Request Handling: After the connection is established, the server process listens for incoming
requests from the client(s). These requests may include data retrieval, processing, or any other
service the server offers.
5) Processing: The server process executes the requested operations, performs the necessary
computations, retrieves or manipulates data, and generates a response.
6) Response Sending: Once the server process generates the response, it sends it back to the
client through the established communication channel.
7) Loop: The server process typically runs in a loop, continuously accepting and handling client
requests until it is explicitly shut down.
Client Process:
1) Initialization: The client process initializes and creates a socket to connect to the server's
address and port.
2) Connection Establishment: The client process initiates a connection request to the server's
address. Connection Establishment Confirmation: If the server accepts the connection request,
the client process establishes a communication channel or socket with the server.
3) Request Sending: The client process sends requests to the server through the established
communication channel. These requests contain the necessary information for the server to
perform the desired operation.
4) Waiting for Response: After sending the request, the client process waits for the server's
response.
5) Response Reception: Once the server generates a response, it sends it back to the client
through the communication channel, and the client process receives it.
6) Processing Response: The client process handles and processes the received response,
extracting any relevant data or performing further actions based on the response content.
7) Loop or Termination: Depending on the application's requirements, the client process may
continue sending requests and receiving responses in a loop or terminate after a specific task is
completed.

1.3 Administer Database Instance :

Administrative tools available to a DBA As a database administrator (DBA), you have access to
various administrative tools that help you manage and maintain databases efficiently. Here are
some commonly used administrative tools available to DBAs:
As a database administrator (DBA), having the right tools is crucial for effectively managing and
maintaining databases. Here are some must-have tools for DBAs:
Database Management System (DBMS) Console: The console provided by the specific
DBMS you are working with is an essential tool for database administration. It allows you to
perform various tasks such as database creation, user management, query execution, and
performance monitoring.
Monitoring and Performance Tuning Tools: Tools like Oracle Enterprise Manager, SQL
Server Performance Monitor, and MySQL Enterprise Monitor help monitor database
performance and identify potential bottlenecks. These tools provide insights into resource
utilization, query execution times, and other performance-related metrics, allowing you to
optimize database performance.
Backup and Recovery Tools: Tools like Oracle Recovery Manager (RMAN), SQL Server
Backup and Restore, dbForge Studio, and mysqldump (for MySQL) enable efficient backup and
recovery operations. They allow you to create scheduled backups, perform point-in-time
recovery, and ensure data integrity.
Query Profiling and Optimization Tools: Tools such as dbForge Studio, Oracle SQL
Developer, SQL Server Query Analyzer, and MySQL Query Profiler help analyze and optimize
SQL queries. These tools provide query execution plans, suggest index improvements, and
highlight areas for query optimization.
Schema & Data Comparison and Synchronization Tools: Tools like Redgate SQL Compare,
Devart dbForge Compare Bundle, and Oracle SQL Developer Data Modeler facilitate comparing
and synchronizing database schemas across different environments. They help identify
differences, generate synchronization scripts, and ensure consistency across databases.
Database Security Tools: Tools like Oracle Database Vault, SQL Server Audit, and MySQL
Enterprise Security provide features to enhance database security. These tools offer capabilities
such as user access control, data encryption, auditing, and compliance reporting.
Data Modeling Tools: Tools like ERwin, Toad Data Modeler, and MySQL Workbench offer
features for designing and visualizing database schemas. These tools help in creating entity-
relationship diagrams (ERDs), managing relationships, and generating SQL scripts for database
creation.
Automation and Scripting Tools: Tools like PowerShell, Oracle Data Pump, and MySQL Shell
allow you to automate repetitive tasks, manage scripts, and perform batch operations. These
tools streamline administrative tasks, improving efficiency and consistency.
Database Version Control Tools: Version control tools such as Git, Subversion, and Redgate
SQL Source Control help manage database scripts, track changes, and enable collaboration.
They ensure version control, ease deployment, and facilitate rollbacks.
Collaboration and Documentation Tools: Tools like Confluence, Microsoft SharePoint, and
Wiki-based platforms assist in documenting database-related information, storing procedures,
guidelines, and best practices. They promote knowledge sharing and collaboration among DBA
teams.

Use SQL*Plus in Oracle to Manage Database instance

SQLPlus is a command-line interface and interactive tool provided by Oracle for managing and
interacting with an Oracle Database instance. It can be used to create and manage databases,
tables, views, stored procedures, and other database objects. It can also be used to execute
SQL statements and PL/SQL blocks.
There are some common tasks you can perform using SQLPlus:
1) Connecting to the Database: To connect to a database using SQL*Plus, open a command
prompt and type the following command: sqlplus username/password@host:port/service_name
Replace username, password, host, port, and service_name with the appropriate values for
your database.
For example: sqlplus scott/tiger@localhost:1521/orcl
2) Executing SQL Statements: Once connected, you can execute SQL statements directly from
the SQL*Plus prompt. For example, you can run a simple query like: SELECT * FROM
employees; Press Enter to execute the statement, and the results will be displayed on the
screen.
3) Running Scripts: SQL*Plus allows you to execute SQL scripts containing multiple statements.
Create a text file (e.g., [Link]) containing your SQL statements, and then run the script using
the following command: @path_to_script
For example, if your script is located in the current directory, you can run it with: @[Link]
4) Spooling Output: SQL*Plus allows you to save query results or command output to a file. Use
the SPOOL command to enable spooling and specify the output file: SPOOL [Link]
After enabling spooling, any subsequent output from SQL statements or commands will be
written to the specified file.
5) Formatting Output: SQL*Plus provides various commands to format the output of queries. For
example: SET LINESIZE n -- Sets the number of characters per line
SET PAGESIZE n -- Sets the number of lines per page
COLUMN column_name FORMAT format
-- Specifies the format of a specific column You can use these commands to customize the
appearance of query results.
6) Managing Database Objects: SQL*Plus allows you to create, modify, or delete database
objects such as tables, views, indexes, and procedures. You can use SQL statements like
CREATE, ALTER, and DROP to perform these operations.
7) Managing Users and Privileges: As a DBA, you can use SQL*Plus to manage user accounts
and privileges. You can create new users, grant or revoke privileges, and manage roles using
SQL statements like CREATE USER, GRANT, and REVOKE.
8) Controlling Transactions: SQL*Plus provides commands to manage transactions. For
example:
COMMIT -- Commits the current transaction
ROLLBACK -- Rolls back the current transaction
You can use these commands to control transactional behavior and ensure data consistency.
Administer the Database using Enterprise Manager (EM):
Enterprise Manager is a graphical user interface (GUI) tool provided by Oracle for managing
and administering Oracle Database. It offers a comprehensive set of features for monitoring,
configuring, and tuning the database. Here's how you can administer the database using Oracle
Enterprise Manager:
1. Accessing Enterprise Manager: Enterprise Manager can be accessed through a web
browser. Open your preferred web browser and enter the URL provided by your Oracle
Database administrator. This URL typically looks like: [Link] where
hostname is the name or IP address of the server where Enterprise Manager is installed, and
port is the port number configured for Enterprise Manager.
2. Logging in: Enter your username and password to log in to Enterprise Manager. You must
have the necessary privileges to access the administrative features.
3. Database Home Page: Once logged in, you will be presented with the Database Home Page.
This page provides an overview of the database and its current status. It displays important
metrics such as CPU usage, memory utilization, storage statistics, and active sessions.
4. Monitoring Performance: Enterprise Manager allows you to monitor the performance of the
database and identify potential issues. You can view real-time performance graphs, historical
performance data, and performance-related alerts. Analyze key performance indicators such as
CPU usage, I/O statistics, and SQL execution metrics to optimize the database performance.
5. Managing Database Parameters: You can modify and manage database initialization
parameters using Enterprise Manager. Navigate to the "Administration" or "Configuration"
section, where you'll find options to view and modify various database parameters. Changing
these parameters can affect the behavior and performance of the database, so exercise caution
and consult Oracle documentation or a database expert when making modifications.
6. User Management: Enterprise Manager allows you to manage database users, roles, and
privileges. You can create new users, modify user properties, assign roles, and grant or revoke
privileges. User management features are typically available in the "Security" or "Users" section
of Enterprise Manager.
7. Backup and Recovery: Enterprise Manager provides a user-friendly interface for managing
database backups and recovery operations. You can schedule backups, perform full or
incremental backups, and restore data from backups. Additionally, you can configure recovery
catalogs, specify retention policies, and monitor backup and recovery jobs.
8. Managing Storage: Enterprise Manager offers tools for managing database storage,
tablespaces, and datafiles. You can create and resize tablespaces, add datafiles, and monitor
space usage. It also provides features for managing Automatic Storage Management (ASM) if
it's being used in your database environment.
9. Diagnosing and Resolving Issues: Enterprise Manager includes diagnostic and tuning
features to help identify and resolve database issues. It provides advisors and advisors'
recommendations for areas such as SQL tuning, memory configuration, and space
management. You can run diagnostic reports, analyze performance bottlenecks, and implement
recommended solutions.
10. Automation and Job Scheduling: Enterprise Manager allows you to automate administrative
tasks and schedule jobs. You can create and manage jobs for tasks like backup and recovery,
statistics gathering, data maintenance, and database reorganization. Set up schedules, define
job dependencies, and monitor job execution and status.
Overview of the Enterprise Manager Framework:
The Oracle Enterprise Manager (EM) framework provides a comprehensive solution for
managing and monitoring Oracle software and systems. It offers a centralized, web-based
interface that allows administrators to efficiently manage multiple Oracle products and
components within a single environment.
Here is an overview of the Enterprise Manager framework:
1. Components: The Enterprise Manager framework consists of various components that work
together to provide end-to-end management capabilities. These components include:
• Oracle Management Service (OMS): OMS is the core component of Enterprise Manager. It
acts as a central hub that receives and processes management data, executes management
tasks, and provides the web-based user interface for administrators.
• Management Agents: Agents are software components installed on managed targets, such as
databases, application servers, and hosts. Agents collect data from the targets and send it to
the OMS for processing. They also execute administrative tasks on behalf of the OMS.
• Plug-ins: Plug-ins extend the functionality of Enterprise Manager to manage specific Oracle
products or components. Each plug-in provides specialized management features and
interfaces for a particular technology, such as databases, middleware, or applications.
2. Centralized Management: The Enterprise Manager framework enables centralized
management of Oracle software and systems. It allows administrators to monitor and manage
multiple targets from a single console, providing a unified view of the entire IT infrastructure.
This centralized approach improves operational efficiency, reduces complexity, and simplifies
administrative tasks.
3. Monitoring and Performance Management: Enterprise Manager offers comprehensive
monitoring and performance management capabilities. It collects and analyzes real-time data
from managed targets, providing detailed metrics and alerts for various performance aspects.
Administrators can monitor resource utilization, track performance trends, diagnose bottlenecks,
and proactively address potential issues.
4. Configuration and Patch Management: Enterprise Manager provides tools for managing
the configuration and patching of Oracle software and systems. It allows administrators to define
and enforce standard configurations, track configuration changes, and compare configurations
across multiple targets. The framework also facilitates the discovery and application of patches
and updates to ensure system security, stability, and compliance.
5. Provisioning and Self-Service: Enterprise Manager supports automated provisioning and
self-service capabilities. It enables administrators to define standardized templates and deploy
new instances of databases, middleware, and applications. Additionally, it provides self-service
interfaces for users to request and provision resources within predefined policies and resource
limits, streamlining the provisioning process and reducing administrative overhead.
6. Incident and Change Management: Enterprise Manager helps administrators track and
manage incidents and changes within the IT infrastructure. It provides incident management
features to track and resolve system issues, capturing details such as severity, impact, and
resolution steps. Additionally, it supports change management by providing tools to plan,
implement, and track changes to system configurations, reducing the risk of unintended
disruptions.
7. Reporting and Dashboards: Enterprise Manager offers comprehensive reporting and
dashboard capabilities. It provides pre-built reports and customizable dashboards that offer
insights into various aspects of the managed environment, including performance, availability,
compliance, and resource utilization. Administrators can generate reports, schedule report
delivery, and create custom dashboards to monitor key performance indicators (KPIs) and
business metrics.
8. Extensibility and Integration: The Enterprise Manager framework is highly extensible and
can integrate with other Oracle and third-party tools. It supports APIs and frameworks for
customization and integration with external systems, enabling administrators to extend the
functionality of Enterprise Manager and integrate it into existing IT management workflows.

Enterprise Manager Cloud Control:

Enterprise Manager Cloud Control is an advanced version of Oracle Enterprise Manager that
provides enhanced capabilities for managing and monitoring Oracle software and systems in
cloud environments. It is designed to support the management of large-scale, enterprise-level
deployments with a focus on cloud- based infrastructure, platform, and software services. Here
are key features and benefits of Enterprise Manager Cloud Control:
1. Cloud Management: Enterprise Manager Cloud Control offers extensive capabilities for
managing cloud resources. It provides a unified management interface to monitor and control
the entire cloud stack, including infrastructure as a service (IaaS), platform as a service (PaaS),
and software as a service (SaaS). Administrators can manage virtual machines, databases,
middleware, and applications deployed in private, public, and hybrid cloud environments.
2. Self-Service Provisioning: Cloud Control enables self-service provisioning, allowing users to
request and provision resources within predefined policies and resource limits. It provides a self-
service portal where users can request database instances, application deployments, and other
IT resources. Administrators can define service catalogs and automated workflows to streamline
resource provisioning processes.
3. Chargeback and Metering: Enterprise Manager Cloud Control supports chargeback and
metering capabilities, helping organizations track and allocate costs for cloud resources. It
allows administrators to define cost models, assign costs to resources, and generate
chargeback reports for individual departments or customers. Metering features provide insights
into resource usage, helping organizations optimize resource allocation and control costs.
4. Service Level Management: Cloud Control enables organizations to define and enforce
service level agreements (SLAs) for cloud services. Administrators can monitor service
performance against defined SLAs, track availability and response times, and generate reports
on service level compliance. SLA management features help organizations ensure the desired
quality of service and identify areas for improvement.
5. Resource Optimization and Right-Sizing: Enterprise Manager Cloud Control provides
capabilities for optimizing resource utilization in cloud environments. It helps administrators
identify underutilized resources, analyze performance trends, and right-size deployments to
improve efficiency and reduce costs. Automated workload management features enable
workload balancing and resource allocation based on predefined policies and performance
thresholds.
6. Cloud Security and Compliance: Cloud Control includes security and compliance
management features to ensure the security and integrity of cloud deployments. It provides
tools for defining and enforcing security policies, managing user access and permissions, and
monitoring compliance with industry standards and regulations. Additionally, it offers
vulnerability assessment and patch management capabilities to address security vulnerabilities
in cloud environments.
7. Hybrid Cloud Management: Enterprise Manager Cloud Control supports the management of
hybrid cloud environments, allowing organizations to integrate and manage resources across
on-premises and cloud-based infrastructure. It provides unified monitoring, provisioning, and
management capabilities for hybrid deployments, enabling organizations to maintain consistent
management practices and policies across their entire IT landscape.
8. Extensibility and Integration: Cloud Control offers extensibility and integration capabilities,
allowing organizations to integrate with third-party tools and customize the management
environment. It supports APIs and frameworks for customizations, enabling organizations to
extend Cloud Control's functionality and integrate it with existing IT management workflows and
systems.
Initialization Parameter Files
Initialization parameter files are configuration files used by Oracle Database to specify various
settings and options during database startup. These files contain a set of parameters that define
the behavior and characteristics of the database instance. There are two types of initialization
parameter files in Oracle:
1. Server Parameter File (SPFILE): The server parameter file (SPFILE) is a binary file that is
maintained by the database itself. It stores the initialization parameters in a server-side location,
typically within the database's data dictionary. The SPFILE allows dynamic modification of
certain parameters while the database is running, without requiring a database restart.
Advantages of using an SPFILE include:
• Dynamic parameter modification: SPFILE allows you to modify certain parameters while the
database is running using the ALTER SYSTEM command.
• Automatic backup and recovery: SPFILE is automatically backed up when changes are made,
making it easier to recover from accidental parameter changes.
• Parameter file consistency: SPFILE ensures that the parameter file used by the database
matches the current configuration stored in the data dictionary.
The default name for the SPFILE is "spfile<SID>.ora", where "<SID>" represents the
system identifier of the database.
2. Initialization Parameter File (PFILE): The initialization parameter file (PFILE) is a text-based
file that contains the database initialization parameters in a plain text format. It is typically
created and maintained by database administrators or DBAs. Unlike the SPFILE, the PFILE
does not allow dynamic modifications of parameters while the database is running. To apply
changes made in the PFILE, a database restart is required.
Advantages of using a PFILE include:
• Flexibility: PFILE allows you to easily edit and modify parameters using a text editor.
• Compatibility: PFILE can be used in different operating systems and can be easily copied or
moved between systems.
• Scriptability: PFILE can be included as part of scripted database deployments or automation
processes.
The default name for the PFILE is "init<SID>.ora", where "<SID>" represents the system
identifier of the database.
Note: When starting an Oracle database, the database instance looks for an SPFILE first. If an
SPFILE is not found, it searches for a PFILE. If both files are absent, the instance uses default
parameter values.
To specify which parameter file to use during database startup, you can use the "SPFILE" or
"PFILE" clause in the STARTUP command or set the appropriate environment variable (e.g.,
"ORACLE_SID" for PFILE or "ORACLE_PFILE" for PFILE).

Assignment 1:
1) Define an oracle database and relational database. Explain the basic concept of Relational
Database.
2) Explain the concept of oracle multitenant architecture.
3) What is Oracle Data Guard? Explain types of data guard. List out the features of data guard.
4) Define RAC and ASM (Automatic Storage Management). Explain features of ASM.
5) What do you mean by oracle cloud? Explain the application of oracle cloud.
6) Explain different database administrative task.
7) Explain Oracle Database Architecture (instance and memory structure) in detail.
8) What are the different process architectures used in Oracle? Explain the process of server
and client.
9) Explain different administrative tools available to a DBA.
10) What is SQL*plus? What are common task that can perform using SQL*plus.
11) What is Oracle Enterprise Manager? How can we administer the database using Enterprise
Manager
(EM)?
12) What is Enterprise Manager Framework? How EM (Enterprise Manager) Framework can
manage
multiple Oracle Products within a single environment?
13) What is Enterprise Manager Cloud Control? What are the features (benefits) of Enterprise
Manager
Cloud Control?
14) What are the uses of initialization parameter files in Oracle Database? Explain the types of
initialization parameter files with their advantages.

Unit 2 Network Environment and Storage Structure: 5 hrs.

2.1 Configuring Network Environment:
Overview of Network Configurations
Oracle network configuration is the process of setting up the Oracle database to allow clients to
connect to it over a network. This involves configuring the listener, the [Link] file, and
the [Link] file. The listener is a process that listens for incoming connections from clients. It
is responsible for routing the connections to the appropriate database instance. The
[Link] file is a database of connection descriptors. A connection descriptor is a set of
information that Oracle uses to connect to a database instance. The [Link] file is a file that
contains Oracle Net configuration parameters.
To configure an Oracle network, you typically need to set up and configure the following
components:
1. Oracle Database Listener: The listener is responsible for establishing network connections
between client applications and the Oracle database server. It listens for incoming connection
requests and directs them to the appropriate Oracle service.
2. Oracle Net Services: Oracle Net Services provides the infrastructure for establishing and
managing network connections. It includes features such as naming methods, directory
services, and connection pooling.
3. [Link]: This configuration file defines the parameters for the Oracle Database Listener.
It specifies the listening protocol, port number, and other network-related settings. The file is
usually located in the $ORACLE_HOME/network/admin directory.
4. [Link]: This file contains the net service names, which are logical names used by
client applications to connect to Oracle databases. Each entry in the [Link] file
corresponds to a specific database connection. It is typically located in the
$ORACLE_HOME/network/admin directory.
5. [Link]: This file contains various network-related parameters and settings for Oracle
Net Services. It allows you to configure parameters such as encryption, authentication, and
SQL*Net tracing. It is also located in the $ORACLE_HOME/network/admin directory.
6. Oracle Connection Manager (if used): Connection Manager is an optional component that
provides additional network security and routing capabilities. It can be used to enhance network
performance and manage connections from multiple clients to multiple database servers.
7. Firewall and Network Security: Ensure that any firewalls or network security measures are
properly configured to allow incoming and outgoing network traffic for the Oracle database
connections. You may need to open specific ports or configure network address translation
(NAT) rules.

Oracle Net Listener Configuration and Management

Oracle Net Listener is a process that listens for incoming connections from clients. It is
responsible for routing the connections to the appropriate database instance. The listener is
configured with one or more listening protocol addresses, information about supported services,
and parameters that control its runtime behavior. The listener configuration is stored in a
configuration file named [Link].
To configure and manage the Oracle Net Listener, you can follow these steps:
1. Locate the [Link] file: The [Link] file contains the configuration parameters for the
Oracle Net Listener. By default, it is located in the $ORACLE_HOME/network/admin directory.
You can use a text editor to open and modify this file.
2. Understand the [Link] file: The [Link] file is in a plain text format and consists of
one or more listener definitions. Each definition specifies the listening protocol, port number,
and other settings. The most common protocol is TCP/IP, using the default port 1521.
3. Configure the listener: Edit the [Link] file to configure the listener according to your
requirements. Here's an example of a [Link] entry for a TCP/IP listener:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST =myhost)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC1521)) ) )
In this example, the listener is configured to listen on the TCP/IP protocol on the host "myhost"
and port 1521. It also listens on the IPC protocol using the EXTPROC1521 key.
4. Save the [Link] file: After making the necessary changes to the [Link] file, save it.
5. Restart the listener: To apply the changes, you need to restart the Oracle Net Listener. You
can do this by executing the following command:
lsnrctl stop
lsnrctl start
This stops and then starts the listener process.
6. Verify the listener status: To verify that the listener is running and configured correctly, use
the following command:
lsnrctl status
This command displays the status of the listener and its associated services.
7. Listener management: The Oracle Net Listener can be managed using the lsnrctl utility.
Some common commands for managing the listener include:
• start: Starts the listener.
• stop: Stops the listener.
• reload: Reloads the listener configuration without restarting.
• services: Displays the services registered with the listener.
• set: Modifies various listener parameters.
• save_config: Saves the current listener configuration to the [Link] file.

Oracle Net Naming Method

The term "Oracle Net" refers to a software component in Oracle Database that enables network
communication between clients and servers. It provides functionality for establishing
connections, resolving service names, and facilitating data transfer. When it comes to naming
methods in Oracle Net, there are several options available. Here are some commonly used
naming methods:
1. Easy Connect Naming Method: This method allows clients to connect to a database using a
simple connect string without the need for a full database address or configuration. The connect
string typically includes the host name or IP address of the server and the service name or SID
of the database.
Example: connect username/password@hostname/service_name
2. Local Naming Method: With this method, a client uses a local naming file ([Link]) to
resolve service names to connect descriptors. The file contains a list of service names and their
corresponding connect descriptors, including the server's address and other connection
parameters.
Example entry in [Link]:
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT =
1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
Example connect string: connect username/password@ORCL
3. Directory Naming Method: This method allows service name resolution through a directory
server, such as LDAP (Lightweight Directory Access Protocol). Clients can query the directory
server to retrieve connect descriptors for the desired service name.
4. External Naming Method: In this method, service names are resolved by an external
naming service outside the Oracle database environment. The external naming service maps
the service names to network addresses.

Tools for Configuring and Managing the Oracle Network:

Oracle provides several tools for configuring and managing the Oracle network. Here are some
of the commonly used tools:
1. Oracle Net Manager: Oracle Net Manager is a graphical user interface (GUI) tool that allows
you to configure Oracle Net Services. It provides a convenient way to create and manage
listener configurations, database service names, and other network-related parameters.
2. Oracle Net Configuration Assistant: Oracle Net Configuration Assistant is a command-line
tool that guides you through the process of configuring Oracle Net Services. It provides a step-
by-step interface for configuring listeners, naming methods, and other network components.
3. Oracle Net Services Administrator's Guide: This guide is a comprehensive resource that
provides detailed information on configuring and managing Oracle Net Services. It covers
various topics such as listener configuration, naming methods, advanced network features, and
troubleshooting.
4. Oracle Enterprise Manager: Oracle Enterprise Manager is a web-based management console
that provides a centralized interface for managing Oracle databases, including network
configuration. It allows you to configure listeners, monitor network performance, and perform
various administrative tasks related to the Oracle network.
5. Oracle Netca Command-line Utility: The Oracle Netca utility is a command-line tool that
allows you to configure Oracle Net Services. It provides a text-based interface for configuring
listeners, naming methods, and other network components. It can be useful for automating
network configuration tasks.
6. Oracle Net Services Configuration Files: Oracle uses various configuration files to manage
network settings. These include the [Link] file, [Link] file, and [Link] file. These
files can be edited manually to configure network parameters.

Using Net Configuration Assistant

Oracle Net Configuration Assistant (NETCA) is a graphical tool provided by Oracle for
configuring and managing Oracle Net Services, which enables client applications to connect to
Oracle databases. The tool simplifies the process of configuring network settings such as
listeners, naming methods, and connect descriptors. To use Oracle Net Configuration
Assistant, follow these steps:
1. Launch Oracle Net Configuration Assistant. The tool can be found in the Oracle Database
installation directory under the "bin" folder. The executable file is called netca or [Link]
depending on your operating system.
2. Once the tool is launched, you will be presented with the "Welcome" screen. Click on the
"Next" button to proceed.
3. On the "Select Configuration Operation" screen, choose the desired operation. The available
options include "Create a Listener," "Configure Oracle Net Services," "Delete a Listener," and
"Remove Oracle Net Configuration." Select the appropriate option and click "Next."
4. Depending on the operation selected, you will be prompted to provide additional information.
For example, if you chose to create a listener, you need to specify the listener name, protocol,
and port number. Fill in the required details and click "Next."
5. On the subsequent screens, you may need to provide additional information related to the
selected operation. For example, when configuring Oracle Net Services, you might be asked to
set naming methods, configure connect descriptors, or specify network security options. Fill in
the necessary details and proceed to the next step.
6. Once you have provided all the required information, review the summary screen to verify the
configuration settings. If everything looks correct, click on the "Finish" button to apply the
changes.
7. Oracle Net Configuration Assistant will perform the requested operation and display the
progress and status. Once the configuration is complete, you will see a confirmation screen.
Click "Exit" to close the tool.

Configure client connection with Net Manager

To configure client connection using Oracle Net Manager (also known as Net Manager), follow
these steps:
1. Launch Oracle Net Manager. It is typically installed along with the Oracle client software. You
can find it in the Oracle Home directory under the "bin" folder. The executable file is called
netmgr or [Link] depending on your operating system.
2. Once Net Manager is launched, you will be presented with the "Welcome" screen. Click on
the "Next" button to proceed.
3. On the "Welcome" screen, select "Local Net Service Name Configuration" and click "Next."
This option allows you to configure a local network service name on the client machine.
4. On the "Select a Configuration Method" screen, choose "Net Service Name Configuration"
and click "Next." This method enables you to configure a net service name to connect to the
Oracle database.
5. On the "Net Service Name Configuration" screen, click on the "Add" button to create a new
net service name.
6. In the "Net Service Name Wizard" dialog, enter a name for the net service name and select
the desired protocol (e.g., TCP/IP) from the list. Click "Next" to continue.
7. Enter the database connection details, including the host name or IP address of the database
server, the port number, and the service name or SID. You can also specify additional options
such as the connection type (dedicated or shared), the connect-time failover settings, and the
load balancing method. Fill in the required details and click "Next."
8. On the "Test Net Service Name" screen, you can choose to test the connectivity by clicking
on the "Test" button. This will verify if the client can successfully connect to the specified
database. Click "Next" to proceed.
9. Review the summary screen to verify the configuration settings. If everything looks correct,
click on the "Finish" button to save the net service name configuration.
10. Oracle Net Manager will save the configuration and display a confirmation screen. Click
"Exit" to close the tool.

View Listener Configurations

To view listener configurations in Oracle, you can use the following methods:
1. Oracle Net Services Administration Tools:
1. Launch the Oracle Net Manager tool by running netmgr or [Link] from the Oracle
Home directory. This tool provides a graphical interface to view and manage listener
configurations.
2. In the Oracle Net Manager, navigate to the "Local" tab and expand the "Service Naming"
node. You will find a list of configured listeners. Select a listener to view its details,
including the listening protocol, port, and associated services.

2. Oracle Net Services Configuration Files:

a. On the server where the listener is configured, locate the Oracle Net Services configuration
files. These files are typically located in the $ORACLE_HOME/network/admin directory. b. The
main configuration file is [Link]. Open this file using a text editor to view the listener
configurations. c. Look for entries starting with LISTENER followed by a name (e.g., LISTENER
= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)))).
Each entry represents a listener configuration.
3. Command-Line Interface:
1. Open a command prompt or terminal on the server where the listener is configured.
2. Use the lsnrctl command-line utility to interact with the listener. Run lsnrctl status to
display the status and configuration details of all configured listeners.
3. To view the details of a specific listener, use the lsnrctl services <listener_name>
command, replacing <listener_name> with the actual name of the listener.

Use TNSPING to test Oracle Net Connectivity

To test Oracle Net connectivity using TNSPING, you can follow these steps:
1. Open a command prompt or terminal window on your computer.
2. Type the following command to execute TNSPING:
tnsping <service_name>
 Replace <service_name> with the actual name of the Oracle service you want to test. The
service name is usually defined in the [Link] file located in the Oracle client installation
directory.
3. Press Enter to run the command.
4. TNSPING will attempt to connect to the specified Oracle service and display the results. If the
connection is successful, you will see a message similar to the following:
TNSPING OK (xxx milliseconds)
The response time in milliseconds will be displayed in place of "xxx."
5. If the connection is not successful, you may see an error message indicating the reason for
the failure.
Connect To the Database
To connect to oracle database from sqlplus:
1. Open a command prompt or terminal.
2. Type sqlplus followed by a space.
3. Enter the username, password, and connect string in the format
username/password@connect_string, where:
• username is your Oracle database username
• password is your Oracle database password
• connect_string specifies the Oracle database connection details, such as the hostname, port,
and service name
Example: sqlplus username/password@hostname:port/service_name

To connect to an Oracle database from Oracle Enterprise Manager, follow these steps:

1. Launch Oracle Enterprise Manager (EM) by accessing the URL of your EM instance. The
URL typically follows the format [Link] where <hostname> is the
hostname or IP address of the server where EM is installed, and <port> is the port number for
EM.
2. On the EM login page, enter your username and password.
3. After logging in, navigate to the "Targets" tab or the "Databases" tab, depending on your EM
version.
4. In the list of targets or databases, locate the Oracle database you want to connect to and
click on it.
5. On the database details page, you will find various options and information about the selected
database. Look for a link or button that allows you to connect to the database.
6. Click on the "Connect" link or button, which may be labeled as "Connect as SYSDBA,"
"Connect as SYSOPER," or "Connect as Normal."
7. Depending on your selection, you may be prompted for additional credentials. Enter the
required username, password, and connection details.
8. Once you provide the necessary credentials and connection details, Oracle Enterprise
Manager will establish a connection to the database and provide you with access to various
management and monitoring features.
To connect to an Oracle database using SQL Developer, follow these steps:
1) Launch SQL Developer on your computer. If you don't have SQL Developer installed, you
can download it from the Oracle website and install it.
2) Once SQL Developer is open, click on the "New Connection" button, which is typically
represented by a green plus icon in the toolbar. Alternatively, you can navigate to "File" > "New"
> "Database Connection".
3) In the "New/Select Database Connection" dialog box, enter the following details:
a. Connection Name: Provide a name for the connection (e.g., MyDatabase).
b. Username: Enter your Oracle database username
c. Password: Enter the password associated with the username.
d. Hostname: Specify the hostname or IP address of the server where the Oracle database is
located.
e. Port: Enter the port number on which the Oracle database is listening (default is 1521).
f. Service Name: Provide the service name of the Oracle database you want to connect to.
4) Click on the "Test" button to check the connection. SQL Developer will attempt to connect to
the Oracle database using the provided details and display a success or failure message.
If the test is successful, click on the "Connect" button to establish the connection to the Oracle
database.
5) SQL Developer will now connect to the Oracle database and display the main SQL Developer
interface. From here, you can write and execute SQL queries, view database objects, and
perform various database administration tasks.

Configure Net Services with Enterprise Manager

To configure network services with Oracle Enterprise Manager, follow these steps:

1. Log in to Oracle Enterprise Manager with administrative privileges.

2. Navigate to the "Targets" menu and select "All Targets."
3. Locate the target system or database for which you want to configure network services and
click on it to open the target's homepage.
4. On the target homepage, click on the "Administration" tab.
5. Under the "Administration" tab, click on "Network."
6. In the "Network" section, you will find options to configure various network services such as
Listener, Oracle Connection Manager, and Oracle Net Services. Click on the relevant option
depending on the network service you want to configure.
7. For example, if you want to configure the Listener, click on "Listener" and then click on the
"Create" button to create a new listener configuration.
8. Provide the necessary details such as the listener name, protocol, and port number. You can
also specify additional settings like the number of dispatcher processes, connection pooling,
and load balancing.
9. Once you have provided all the required information, click on "OK" to save the configuration.
10. Similarly, you can configure other network services by selecting the respective options and
providing the necessary details.
11. After configuring the network services, you can test the connectivity by using the "Test"
option provided within Oracle Enterprise Manager. This will help ensure that the services are
working correctly.
12. You can also monitor the status and performance of the network services by navigating to
the "Monitoring" tab within the target homepage

2.2 Storage Structure:

In Oracle, the storage structure refers to the organization and arrangement of data within a
database. Oracle uses multiple levels of storage structures to efficiently store and retrieve data.
Overview of data storage: Here are the key storage structures in Oracle:
1) Database: At the highest level, an Oracle database is a collection of data files, control files,
and redo log files. These files are stored on the operating system's file system or in Automatic
Storage Management (ASM), a built-in Oracle feature for managing storage.
2) Tablespaces: A tablespace is a logical storage container within an Oracle database. It
consists of one or more data files. Tablespaces are used to group related database objects
together for efficient management. Oracle provides different types of tablespaces, including
system tablespace, user tablespaces, temporary tablespaces, and undo tablespaces.
3) Data Files: A data file is a physical file that stores the actual data of database objects, such
as tables and indexes. Data files are associated with specific tablespaces and are divided into
logical units called blocks. Oracle reads and writes data at the block level.
4) Segments: Segments are the logical storage structures within a tablespace that hold the
data for database objects. Each segment corresponds to a specific type of database object,
such as tables, indexes, or partitions. Segments are made up of extents.
5) Extents: An extent is a contiguous set of data blocks within a data file. When a segment
needs space to store data, it allocates one or more extents. Extents are managed by Oracle's
extent management system and can dynamically grow or shrink depending on the data size.
6) Data Blocks: A data block is the smallest unit of storage within an Oracle database. It
represents a fixed-size chunk of data that can hold multiple rows of a table or index. Data blocks
are typically 8 KB in size but can vary based on the configuration.
7) Redo Logs: Redo logs are a crucial part of Oracle's recovery mechanism. They store a
record of all changes made to the database so that it can be recovered in the event of a failure.
Redo logs are stored in a circular fashion and are composed of multiple redo log files.
8) Control Files: Control files are binary files that store metadata about the database, including
the database name, data file and redo log file locations, and the current database status. They
are essential for database startup and recovery operations.

Database Block:
In Oracle databases, a block is the smallest unit of data storage. It represents a fixed-size
portion of data that is read from or written to the disk. Here are some key points about Oracle
database blocks:
1. Size: The size of an Oracle database block is typically 8 KB, although it can vary depending
on the configuration. This size is chosen to strike a balance between performance and disk
space utilization.
2. Logical Structure: Each block is logically divided into different sections, including the
header, table or index data, and free space. The header contains metadata about the block,
such as the block type, block address, and transaction information.
3. Storage Unit: Database blocks are the unit of I/O operations in Oracle. When reading or
writing data, Oracle reads or writes entire blocks, even if only a portion of the block is needed.
This is known as the "all-or-nothing" principle, where individual records or rows cannot be
directly accessed or modified.
4. Data Representation: Within a block, data is stored in a row format for tables or an index
format for indexes. Rows within a block are organized into clusters based on the table or index
key to optimize data retrieval.
5. Block Allocation: Blocks are allocated to different segments within a tablespace to store
data objects like tables, indexes, or clusters. When a segment requires additional space, Oracle
allocates new blocks and links them together in a linked list-like structure.
6. Block Compression: Oracle provides the option to compress data at the block level to
reduce storage space. Compressed blocks store more data and can be beneficial for tables with
high data redundancy.
7. Block Recovery: Oracle maintains a redo log to recover blocks in case of media failures or
[Link] redo log records changes made to the database, allowing for the restoration of
modified blocks to a consistent state.

Overview of Tablespaces and Datafiles:

In Oracle, a tablespace is a logical storage container that groups and organizes database
objects such as tables, indexes, and other schema objects. Each tablespace is made up of one
or more data files, which are physical files stored on disk. Here's a closer look at tablespaces
and data files in Oracle:
Tablespace:
A tablespace is created and managed within an Oracle database to hold and organize database
objects. It serves as a logical unit for data storage and management, providing a way to allocate
space and control the physical storage of data. Tablespaces can be classified into different
types based on their purpose, such as system tablespaces, user tablespaces, temporary
tablespaces, and undo tablespaces.
• System tablespace: It contains the core Oracle database objects and metadata required for
the functioning of the database.
• User tablespace: It is used to store user-specific objects, such as tables and indexes.
• Temporary tablespace: It is used for sorting and temporary storage of data during database
operations like sorting, joining, or group by clauses.
• Undo tablespace: It manages transaction undo information for rolling back changes and
supporting consistency and recovery.
Create and alter Tablespace Command
To create and alter a tablespace in Oracle, you can use the following commands:
1. Create Tablespace:
CREATE TABLESPACE tablespace_name DATAFILE 'file_path' SIZE size_spec [REUSE]
[AUTOEXTEND ON [NEXT size_spec] [MAXSIZE max_size_spec]] [LOGGING | NOLOGGING]
[DEFAULT STORAGE ( [INITIAL initial_extent] [NEXT next_extent] [MINEXTENTS
min_extents][MAXEXTENTS max_extents] [PCTINCREASE pct_increase] )] [EXTENT
MANAGEMENT {LOCAL |DICTIONARY}] [SEGMENT SPACE MANAGEMENT {AUTO |
MANUAL}];
.
2. Alter Tablespace: ALTER TABLESPACE tablespace_name [RENAME TO
new_tablespace_name] [ADD DATAFILE 'file_path' SIZE size_spec] [RESIZE DATAFILE
'file_path' SIZE size_spec] [AUTOEXTEND ON [NEXT size_spec] [MAXSIZE max_size_spec]]
[AUTOEXTEND OFF] [DEFAULT STORAGE ( [INITIAL initial_extent] [NEXT next_extent]
[MINEXTENTS min_extents] [MAXEXTENTS max_extents]
[PCTINCREASE pct_increase] )] [SEGMENT SPACE MANAGEMENT {AUTO | MANUAL}];

Data File:
A data file is a physical file associated with a tablespace that stores the actual data of database
objects. Data files are allocated space on disk and can grow or shrink dynamically based on the
configuration and storage needs. Each data file belongs to a single tablespace and is
associated with one or more segments (logical structures that hold data). Data files can be
located on different storage devices or file systems, providing flexibility in managing storage.
The size of a data file can vary based on the configuration, and Oracle reads and writes data at
the block level within the data files. Data files are managed by Oracle's storage management
system, which handles aspects like space allocation, growth, and backup and recovery.
Create and Manage Data Files:
To create and manage data files in Oracle, you can use the following commands and
operations:
1. Create Data File:
To create a data file, you can use the ALTER TABLESPACE command with the ADD
DATAFILE
clause. Here's an example:
ALTER TABLESPACE tablespace_name ADD DATAFILE 'file_path' SIZE size_spec
[AUTOEXTEND ON [NEXT size_spec] [MAXSIZE max_size_spec]];
2. Resize Data File:
To resize an existing data file, you can use the ALTER DATABASE command with the RESIZE
clause. Here's an example:
ALTER DATABASE DATAFILE 'file_path' RESIZE size_spec;
3. Drop Data File:
To remove a data file from a tablespace, you can use the ALTER TABLESPACE command with
the DROP DATAFILE clause. Here's an example:
ALTER TABLESPACE tablespace_name DROP DATAFILE 'file_path';
4. Manage Data File Auto Extension: You can enable or disable auto extension for a data file
using the ALTER DATABASE command with the DATAFILE clause. Here are the examples:
To enable auto extension:
ALTER DATABASE DATAFILE 'file_path' AUTOEXTEND ON [NEXT size_spec] [MAXSIZE
max_size_spec];
To disable auto extension:
ALTER DATABASE DATAFILE 'file_path' AUTOEXTEND OFF;
5. Monitor Data Files:
You can query the DBA_DATA_FILES view or the V$DATAFILE view to obtain information
about data files, such as their names, sizes, tablespace associations, and more.

The relationship between tablespaces and data files is that tablespaces serve as logical
containers that group related database objects, and data files provide the physical storage for
those objects. By organizing data into tablespaces and managing data files, administrators can
effectively allocate and control storage resources, optimize performance, and facilitate backup
and recovery operations in an Oracle database.

Use Enterprise Manager to View the Storage Structure of the Database

To view the storage structure of an Oracle database using Oracle Enterprise Manager,
follow these steps:
1. Log in to Oracle Enterprise Manager with administrative privileges.
2. Navigate to the "Targets" menu and select "All Targets."
3. Locate the target database for which you want to view the storage structure and click on it to
open the database's homepage.
4. On the database homepage, click on the "Administration" tab.
5. Under the "Administration" tab, click on "Storage" or "Storage Management" (the exact label
may vary depending on the version of Oracle Enterprise Manager).
6. In the "Storage" section, you will find options to view and manage various aspects of the
storage structure such as Tablespaces, Data Files, Control Fils etc. Here are a few commonly
used options:

Temporary Tablespaces:
Temporary tablespaces in Oracle are used for temporary storage during sorting, hash joins, and
other temporary operations within the database. They provide a workspace for intermediate
data processing that is not permanently stored. Temporary tablespaces are specifically
designed for optimizing performance and reducing the impact on permanent storage. By utilizing
temporary tablespaces effectively, Oracle databases can optimize temporary data processing,
improve performance for temporary operations, and reduce the impact on permanent storage.
Oracle Enterprise Manager provides tools and interfaces to monitor and manage temporary
tablespaces. It allows you to monitor space usage, performance metrics, and perform tasks like
adding or resizing temporary tablespaces. In Oracle, temporary tablespaces can also be used
for temporary undo storage, known as "temp undo." Temp undo improves performance by
storing temporary undo data in the temporary tablespace instead of the traditional undo
tablespace.
Use OMF: OMF (Oracle Managed Files) is a feature in Oracle that simplifies the management
of database files, including data files, control files, and log files. With OMF, Oracle automatically
generates and manages file names and paths, eliminating the need for manual file management
tasks. OMF simplifies the administration and management of database files by automating file
naming, path generation, and maintenance tasks. It helps reduce errors and improves overall
efficiency in managing the database files. Here's how to use OMF for creating and managing
database files:
1. Enable OMF:
• OMF is enabled by default in Oracle. However, you can verify the OMF setting by checking
the value of the DB_CREATE_FILE_DEST initialization parameter using SQL*Plus or
Enterprise Manager. If it is not explicitly set, OMF is enabled.
• If you need to enable or modify OMF, you can set the DB_CREATE_FILE_DEST
parameter to the desired directory path using the ALTER SYSTEM command. For example:
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/path/to/omf_directory';
2. Create Data Files with OMF:
• To create a data file using OMF, you only need to specify the tablespace name and the
desired size. Oracle will automatically generate a unique file name and manage the file for
you. Here's an example:
CREATE TABLESPACE tablespace_name DATAFILE SIZE size_spec;
3. View OMF-generated File Names:
• You can query the DBA_DATA_FILES view or the V$DATAFILE view to retrieve
information about the OMF-generated data files. The FILE_NAME column will display the
OMF-generated file names.
4. Alter Data Files with OMF:
• When using OMF, you don't need to manually alter the data files. Oracle handles the
management of file extensions and resizing automatically. You can use the regular SQL
commands (e.g., ALTER TABLESPACE, ALTER DATABASE) to modify other
properties of the tablespace or database.
5. Drop Data Files with OMF:
• To drop a data file created with OMF, you can use the ALTER TABLESPACE command
with the DROP DATAFILE clause. Oracle will handle the deletion of the file for you.
Here's an example:
ALTER TABLESPACE tablespace_name DROP DATAFILE 'datafile_name';

Drop Tablespaces and Data Files:

Note that dropping a data file will permanently remove it from the tablespace, so exercise
caution while performing this operation. Ensure that the data file is no longer needed and that
appropriate backups are in place. It's important to be cautious when dropping tablespaces or
data files, as the data contained within them will be permanently deleted. Always ensure that
you have proper backups and confirm that you are dropping the correct tablespaces or data files
before executing these commands.
1. Drop Tablespace: To drop a tablespace, including all its associated data files, you can use
the DROP TABLESPACE command. Here's an example:
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
• tablespace_name: The name of the tablespace you want to drop.
By including the INCLUDING CONTENTS AND DATAFILES clause, Oracle will drop the
tablespace along with its data files, freeing up the disk space.
2. Drop Data File: To drop a specific data file from a tablespace, you can use the ALTER
TABLESPACE command with the DROP DATAFILE clause. Here's an example:
ALTER TABLESPACE tablespace_name DROP DATAFILE 'file_path';
• tablespace_name: The name of the tablespace from which you want to drop the data file.
• 'file_path': The path and filename of the data file you want to drop.

Unit 3 Users and Privileges: 5 hrs.

3.1 Manage Users:
Predefined Database Administration Accounts:
In Oracle databases, there are several predefined database administration accounts that have
specific roles and responsibilities. These accounts provide privileged access to manage and
administer the database. It's important to note that the SYS, SYSTEM, SYSDBA, SYSOPER,
and DBSNMP accounts should be used with caution and only by authorized database
administrators. It is recommended to create additional user accounts with appropriate privileges
for day-to-day operations and restrict the use of these privileged accounts to essential
administrative tasks.
Here are some of the commonly used predefined Oracle database administration
accounts:
1. SYS: This is the most powerful administrative account in Oracle databases. It has full control
over the database and can perform any administrative task, including creating and managing
user accounts, creating and modifying database objects, and managing system resources.
2. SYSTEM: The SYSTEM account is created during the installation of the Oracle database and
is used for database administration tasks. It has privileges similar to the SYS account but with
some restrictions. The SYSTEM account is typically used for day-to-day administrative tasks
and managing the database schema.
3. SYSDBA: SYSDBA is a special system privilege that allows a user to perform high-level
administrative tasks, similar to the SYS account. Users with the SYSDBA privilege can start and
shut down the database, perform backup and recovery operations, and modify critical database
parameters.
4. SYSOPER: SYSOPER is another special system privilege that provides a restricted set of
administrative tasks. Users with the SYSOPER privilege can start and shut down the database,
but they have limited control over other administrative functions compared to SYSDBA.
5. DBSNMP: The DBSNMP account is used by the Oracle Intelligent Agent for monitoring and
managing the database. It is created by default during the installation and is used for database
performance monitoring, event notifications, and managing the Automatic Workload Repository
(AWR).

User Accounts
In Oracle databases, user accounts are used to control access and permissions for individual
users or applications. User accounts are created to allow users to connect to the database and
perform specific operations based on their privileges. It's essential to manage user accounts
and their privileges carefully to maintain the security and integrity of the database. Regular
reviews of user accounts and their associated privileges are recommended to ensure
appropriate access controls and minimize security risks.
Here are some key points about Oracle user accounts:
1. Creating User Accounts: Once connected as SYSTEM, simply issue the CREATE USER
command to generate a new account.
CREATE USER books_admin IDENTIFIED BY MyPassword;Here we’re simply creating a
books_admin account that is IDENTIFIED or authenticated by the specified password.

2. Granting Privileges: Once a user account is created, you can grant specific privileges to the
user. Common privileges include SELECT, INSERT, UPDATE, DELETE, and EXECUTE,
among others. Privileges can be granted at the object level (tables, views, procedures) or
system level (create table, create session, etc.).
3. Default Roles: Some commonly used default roles include CONNECT, RESOURCE, and
DBA. The CONNECT role allows basic connectivity to the database, while the RESOURCE role
provides additional privileges for creating and managing objects.
4. System and Object Privileges: In addition to roles, you can grant system and object
privileges directly to user accounts. System privileges control actions at the system level, such
as creating sessions, altering system parameters, or creating tablespaces. Object privileges
govern actions on specific database objects, such as tables, views, or procedures.
5. Revoking Privileges: If necessary, you can revoke privileges from a user account using the
REVOKE statement. This removes specific privileges that were previously granted, restricting
the user's access and permissions.
6. Locking and Unlocking User Accounts: User accounts can be locked or unlocked to
control access. Locking an account prevents the user from connecting to the database, while
unlocking enables access again. This feature is useful in scenarios where you want to
temporarily restrict a user's access.

User Authentication
Oracle provides several methods for user authentication, ensuring that only authorized users
can connect to the database. It's important to choose an appropriate authentication method
based on the security requirements of your Oracle database. A combination of strong password
policies, regular password updates, and multi-factor authentication methods can significantly
enhance the security of user authentication in Oracle databases.
Here are some commonly used methods for user authentication in Oracle:
1. Password Authentication: This is the most common and basic method of user
authentication in Oracle. Each user has a username and a corresponding password. When
connecting to the database, the user provides their username and password, which are verified
by the Oracle server. Passwords can be stored in the database as hashed values to enhance
security.
2. Operating System Authentication: Oracle allows users to be authenticated using the
operating system credentials. With this method, Oracle relies on the operating system to
authenticate users. The user's operating system username and password are used to establish
a connection to the database. This method is commonly used in environments where the
operating system handles user authentication.
3. External Authentication: Oracle supports external authentication through external
authentication services like Oracle Internet Directory, Lightweight Directory Access Protocol
(LDAP), or Microsoft Active Directory. In this method, the user's authentication is delegated to
an external service, and the Oracle server validates the user's credentials against that service.
4. Certificate-Based Authentication: Oracle also supports authentication using digital
certificates. Users are issued digital certificates, which contain public and private key pairs. The
client presents the certificate during the connection process, and the Oracle server verifies its
authenticity. This method provides enhanced security and is commonly used in environments
with strict security requirements.
5. Database Vault: Oracle Database Vault is an additional security feature that can be enabled
to provide an extra layer of protection. It allows administrators to define highly restrictive security
policies, including additional authentication factors such as time of day, IP address, or other
user-specific attributes.

Change a user’s Password

To change the password for an Oracle user account, you can use the ALTER USER statement.
Here's the syntax to change a user's password:
ALTER USER username IDENTIFIED BY new_password;
Here's an example that demonstrates how to change the password for a user named "john" to
"newpassword":
ALTER USER john IDENTIFIED BY newpassword;

Manage a user Account

To manage an Oracle user account, you can perform various operations such as altering user
properties, granting or revoking privileges, locking or unlocking the account, and deleting the
account if necessary.
Here are some common tasks for managing an Oracle user account:
1. Altering User Properties:
ALTER USER username [IDENTIFIED BY new_password] [DEFAULT TABLESPACE
tablespace_name] [TEMPORARY TABLESPACE temp_tablespace_name];
Use the ALTER USER statement to modify user properties. You can change the user's
password, default tablespace, and temporary tablespace.
2. Granting Privileges:
GRANT privilege1 [, privilege2, ...] TO username;
Use the GRANT statement to assign privileges to a user. Specify the desired privileges and the
target user account.
3. Revoking Privileges:
REVOKE privilege1 [, privilege2, ...] FROM username;
Use the REVOKE statement to remove previously granted privileges from a user. Specify the
privileges to revoke and the target user account.
4. Locking an Account:
ALTER USER username ACCOUNT LOCK;
Use the ALTER USER statement with the ACCOUNT LOCK clause to lock a user's account.
This prevents the user from connecting to the database.
5. Unlocking an Account:
ALTER USER username ACCOUNT UNLOCK;
Use the ALTER USER statement with the ACCOUNT UNLOCK clause to unlock a locked user
account, allowing the user to connect to the database again.
6. Deleting an Account:
DROP USER username [CASCADE];
Use the DROP USER statement to delete a user account. You can choose to include the
CASCADE option to remove all objects owned by the user as well.
Monitor User Information
To monitor user information in Oracle, you can query the data dictionary views that contain
relevant information about user accounts, privileges, and activity. Here are some commonly
used data dictionary views for monitoring user information:
1. DBA_USERS:
SELECT username, account_status, created, profile FROM dba_users;
This view provides information about all users in the database, including their username,
account status (e.g., OPEN, LOCKED, EXPIRED), creation date, and assigned profile.
2. DBA_ROLES:
SELECT role FROM dba_roles;
This view lists all the roles defined in the database. Roles are groups of privileges that can be
granted to users.
3. DBA_ROLE_PRIVS:
SELECT grantee, granted_role FROM dba_role_privs WHERE grantee = 'USERNAME';
This view shows the roles granted to a specific user ('USERNAME').
4. DBA_TAB_PRIVS:
SELECT grantee, table_name, privilege FROM dba_tab_privs WHERE grantee ='USERNAME';
This view displays the object-level privileges (e.g., SELECT, INSERT, UPDATE) granted to a
specific user ('USERNAME').
5. DBA_SYS_PRIVS:
SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = 'USERNAME';
This view shows the system-level privileges granted to a specific user ('USERNAME').
6. DBA_AUDIT_TRAIL:
SELECT username, action_name, timestamp FROM dba_audit_trail WHERE username =
'USERNAME';
This view contains audit trail information, such as user actions and timestamps, that can be
used for user activity monitoring.

Terminate User Session

To terminate an Oracle user session, you can use the ALTER SYSTEM statement or the KILL
SESSION command. Here's how you can terminate a user session using both methods:
1. Using ALTER SYSTEM:
ALTER SYSTEM KILL SESSION 'sid,serial#';
For example, to terminate the session with SID 123 and serial number 456, the command would
be:
ALTER SYSTEM KILL SESSION '123,456';
2. Using KILL SESSION:
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
For example, to terminate the session with SID 123 and serial number 456, the command would
be:
ALTER SYSTEM DISCONNECT SESSION '123,456' IMMEDIATE;
Create a schema Only Account

In Oracle, a schema is essentially a logical container for database objects, such as tables,
views, and procedures. A schema acts as a blueprint or template for creating and managing
objects within a database. It defines the structure, relationships, and permissions for the objects
it contains. Multiple schemas can exist within a single database, each with its own set of objects
and permissions. To create a schema-only account in Oracle, you can follow these steps:
1. Connect to the Oracle database using a privileged account, such as SYS or SYSTEM.
2. Create a new user account without granting any additional privileges or roles. You can use
the CREATE USER statement to create the user account. For example:
CREATE USER schema_name IDENTIFIED BY password;
3. Grant the necessary privileges to the schema-only account to create and manage objects
within their schema. For example, to grant the CREATE SESSION and CREATE TABLE
privileges to the schema-only account:
GRANT CREATE SESSION, CREATE TABLE TO schema_name;
4. Optionally, you can assign a specific default tablespace to the schema-only account using the
DEFAULT TABLESPACE clause when creating the user or by altering the user later. This
specifies where the user's objects will be stored. For example:
ALTER USER schema_name DEFAULT TABLESPACE tablespace_name;
5. If needed, grant additional privileges or roles to the schema-only account based on the
specific requirements of the account. For example, granting the CREATE VIEW privilege:
GRANT CREATE VIEW TO schema_name;

3.2 Manage Privileges:

Database Access In Oracle, database access privileges determine what actions and operations
a user or role can perform within the database. Privileges control access to various database
objects, such as tables, views, procedures, and system-level operations. Here are some
important database access privileges in Oracle:
1. System Privileges: System privileges grant users the ability to perform specific actions at
the system level. Some commonly used system privileges include:
• CREATE SESSION: Allows users to connect to the database.
• CREATE TABLE: Enables users to create tables.
• CREATE PROCEDURE: Grants the privilege to create procedures.
• CREATE VIEW: Allows users to create views.
• CREATE ANY TABLE: Provides the privilege to create tables in any schema.
• ALTER ANY TABLE: Allows users to alter tables in any schema.
• DROP ANY TABLE: Enables users to drop tables in any schema.

2. Object Privileges: Object privileges control access to specific database objects. They grant
users the ability to perform actions on those objects. Common object privileges include:
• SELECT, INSERT, UPDATE, DELETE: Allow users to perform respective operations on
tables.
• EXECUTE: Grants the ability to execute stored procedures or functions.
• REFERENCES: Enables users to create foreign key constraints.
• ALTER, DROP: Provide the privilege to modify or drop specific objects.
• GRANT: Allows users to grant object privileges to other users.
Object privileges are granted explicitly on individual objects or can be granted through roles.
3. Role Privileges: Roles are collections of privileges that can be assigned to users. By
granting roles to users, you can simplify privilege management. Some common predefined roles
in Oracle include CONNECT, RESOURCE, and DBA.
• CONNECT role: Provides basic connectivity privileges.
• RESOURCE role: Grants additional privileges for creating and managing objects.
• DBA role: Provides full administrative privileges.
You can also create custom roles and assign specific privileges to them.
4. Auditing Privileges: Oracle provides auditing features to track user activity and monitor
database access. Auditing privileges control the ability to enable or manage database auditing.
• AUDIT SYSTEM: Grants the privilege to audit system-level activities.
• AUDIT ANY: Allows auditing any object in the database.
• AUDIT TABLE: Provides the privilege to audit specific tables.

System and Object Level Privileges

System and object-level privileges are access rights that determine the level of control and
permissions granted to users or processes within a computer system. These privileges are
typically used in operating systems and database management systems to enforce security and
control access to resources. It's important to carefully assign and manage these privileges to
maintain system security, prevent unauthorized access, and ensure the integrity of sensitive
data.
1. System-level privileges: These privileges provide control over the entire system and are
typically granted to administrators or superusers. They include:
• Administrator privileges: These privileges allow users to perform administrative tasks, such
as installing or uninstalling software, modifying system settings, managing user accounts, and
accessing sensitive system resources.
• Kernel-level privileges: Kernel-level privileges provide direct access to the operating
system's kernel, enabling tasks such as hardware control, memory management, and low-
level system operations. These privileges are usually reserved for the operating system itself
and trusted system components.
• Service privileges: Some services or daemons running in the background may require
elevated privileges to perform specific tasks. These privileges allow services to interact with
the system, access protected resources, or communicate with other services.
2. Object-level privileges: These privileges govern access to specific objects, such as files,
directories, devices, or database records. They are more granular and determine what actions a
user or process can perform on individual resources. Examples include:
• Read privileges: Read privileges allow users to view or retrieve information from an object
but not modify or delete it. Users with read access can typically open files, view database
records, or browse directory contents.
• Write privileges: Write privileges enable users to modify or create new objects. With write
access, users can edit files, add records to a database, or create new files and directories.
• Execute privileges: Execute privileges determine whether a user or process can run or
execute a specific program or script. This privilege is essential for executing binaries or
running scripts on an operating system.
• Delete privileges: Delete privileges grant users the ability to remove or delete objects. Users
with delete access can delete files, remove database records, or erase directories and their
contents.
• Ownership privileges: Ownership privileges allow users to change the permissions or
ownership of an object. With ownership access, users can modify access control lists (ACLs),
grant or revoke privileges from other users, or change ownership of files and directories.

Oracle Supplied Roles

Oracle provides several predefined roles that offer specific sets of privileges for different
database administration and management tasks. Here are some commonly used Oracle-
supplied roles:
1. CONNECT: The CONNECT role provides basic connectivity privileges. Users assigned this
role can connect to the database and perform simple tasks, such as querying tables and
executing procedures.
2. RESOURCE: The RESOURCE role is designed for application developers. It includes
privileges necessary for creating and managing schema objects, such as tables, sequences,
and procedures. Users with this role can create and manipulate their own objects within their
schema.
3. DBA: The DBA role, also known as the database administrator role, is the most powerful role
in Oracle. Users assigned this role have extensive administrative privileges, allowing them to
perform a wide range of database management tasks, including creating users, altering
database structures, and managing backups and recovery.
4. EXP_FULL_DATABASE and IMP_FULL_DATABASE: These roles are used for Oracle
Data Pump export and import operations. The EXP_FULL_DATABASE role grants privileges
required for full database exports, while the IMP_FULL_DATABASE role provides privileges
necessary for full database imports.
5. EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE: These roles are used for
managing Oracle's catalog and data dictionary views. The EXECUTE_CATALOG_ROLE role
grants privileges necessary for executing catalog-related procedures, while the
SELECT_CATALOG_ROLE role provides read-only access to catalog views.
The Grant and Revoke Command
In Oracle Database, the "GRANT" and "REVOKE" commands are used to manage privileges
and permissions for database objects. Here's how these commands are used in Oracle:
1. GRANT Command: The GRANT command in Oracle is used to grant privileges to users or
roles. The syntax of the GRANT command is as follows:
GRANT privileges ON object TO user or role;
Example:
GRANT SELECT, INSERT ON employees TO user1;
In the above example, the user "user1" is granted the SELECT and INSERT privileges on the
"employees" table.
2. REVOKE Command: The REVOKE command in Oracle is used to revoke or remove
privileges from users or roles. The syntax of the REVOKE command is as follows:
REVOKE privileges ON object FROM user or role;
Example:
REVOKE SELECT, INSERT ON employees FROM user1;
In the above example, the SELECT and INSERT privileges are revoked from the user "user1"
on the "employees" table.

Create, Modify and Drop Role:

In Oracle Database, you can create, modify, and drop roles using SQL commands. Here's how
you can perform these actions using SQL commands in Oracle:
1. Create a Role: To create a role, you can use the CREATE ROLE command. Here's the
syntax:
CREATE ROLE role_name;
Replace role_name with the desired name for the role. For example, to create a role called
"admin", you would use:
CREATE ROLE admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO admin;
2. Modify a Role: To modify an existing role, you can use the ALTER ROLE command. Here's
the syntax:
ALTER ROLE role_name [ATTRIBUTE = value];
For example, to change the password for the "admin" role, you would use:
ALTER ROLE admin IDENTIFIED BY new_password;
3. Drop a Role: To drop or delete a role, you can use the DROP ROLE command. Here's the
syntax:
DROP ROLE role_name;
User Predefined Role

To use a user-defined role in Oracle, you need to follow these steps:

1. Connect to the Oracle database using a tool such as SQL*Plus or SQL Developer with a user
that has appropriate privileges.
2. Create a user-defined role using the CREATE ROLE statement. For example, to create a role
called "manager_role," you can use the following command:
CREATE ROLE manager_role;
3. Grant the necessary privileges to the role using the GRANT statement. For example, if you
want to grant the role the privilege to select from a specific table called "employees," you can
use the following command:
GRANT SELECT ON employees TO manager_role;
4. Assign the role to the desired user using the GRANT statement. For example, to assign the
"manager_role" to a user called "john," you can use the following command:
GRANT manager_role TO john; Alternatively, you can assign the role to a group of users or
other roles.
5. Once the role is assigned to the user, the user can enable the role using the SET ROLE
statement.
For example, for the user "john" to enable the "manager_role," the following command can be
used:
SET ROLE manager_role;

After enabling the role, the user will have the privileges associated with that role until the role is
disabled or the user session ends.
a) To create a new user with password and establish connection with this user.
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql -u newuser -p
b) To change the password of an existing user with a new password.
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
c) To alter the user to assign default tablespace.
ALTER USER username DEFAULT TABLESPACE tablespacename;
d) To grant and revoke roles to user.
GRANT role_name TO username; [Link] HR_ADMIN TO ;
REVOKE role_name FROM username; [Link] HR_ADMIN FROM johndoe;
e) To lock and unlock the [Link]
ALTER USER username ACCOUNT LOCK; Eg. ALTER USER johndoe ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK; Eg. ALTER USER johndoe ACCOUNT
UNLOCK;
f) To display user’s information as well as individual user information.
SELECT username, user_id, created, default_tablespace, temporary_tablespace
FROM dba_users;
SELECT username, user_id, created, default_tablespace, temporary_tablespace
FROM dba_users WHERE username = 'your_username';
g) To create a schema only account user that is used only on schema.
[Link] the User:
CREATE USER schema_user IDENTIFIED BY password;
[Link] the User to a Default Tablespace:
CREATE USER schema_user IDENTIFIED BY password DEFAULT TABLESPACE
your_tablespace;
[Link] Schema-Level Privileges:
GRANT CREATE TABLE, CREATE PROCEDURE TO schema_user;

h) To create, modify and drop role and grant on user.

1.. Creating a Role: CREATE ROLE role_name; For example, to create a role named
'HR_ADMIN,' you would run:
CREATE ROLE HR_ADMIN;

2.. Modifying a Role: ALTER ROLE role_name GRANT privilege;

To revoke privileges from a role, you can use:
ALTER ROLE role_name REVOKE privilege;

3. Dropping a Role: DROP ROLE role_name; For example, to drop the 'HR_ADMIN' role, you
would run:
DROP ROLE HR_ADMIN;

4. Granting a Role to a User:

GRANT role_name TO username;

Unit 4 Profile, Resources and Auditing: 4 hrs.

4.1 Profiles and Resources:
Overview of User Profile
An Oracle user profile is a collection of permissions, resource limits, and password constraints
that define the behavior of a user in the database. Profiles are used to control how users can
access and use the database, and to help ensure the security of the database.
When a user is created, they are assigned to a profile. The profile determines the following:
• The number of concurrent sessions that the user can have open.
• The amount of CPU time that the user can use per session.
• The amount of disk space that the user can use.
• The types of objects that the user can create and access.
• The types of operations that the user can perform.
• The password requirements for the user.
Profiles can be created and modified by database administrators. When a profile is created, the
DBA specifies the permissions, resource limits, and password constraints that will be applied to
users who are assigned to the profile.
Profiles can be used to implement a variety of security policies, such as:
• Preventing users from accessing sensitive data.
• Limiting the amount of resources that users can use.
• Requiring users to change their passwords regularly.
• Preventing users from creating certain types of objects.
Profiles are an important part of Oracle security. By using profiles, DBAs can control how users’
access and use the database, and help ensure the security of the database.
Here are some of the benefits of using user profiles in Oracle:
• Increased security: Profiles can be used to control how users access and use the database,
which can help to improve security. For example, profiles can be used to limit the number of
concurrent sessions that a user can have open, the amount of CPU time that a user can use,
and the amount of disk space that a user can use.
• Improved manageability: Profiles can help to improve the manageability of the database by
making it easier to manage user permissions and resource limits. For example, if a new type of
object is added to the database, the DBA can simply create a new profile that grants users
permission to create that object.
• Increased flexibility: Profiles can be used to increase the flexibility of the database by allowing
the DBA to create different profiles with different permissions and resource limits. This can be
useful for organizations that have different security requirements for different types of users.

Profile Resource Parameter

A profile resource parameter in Oracle is a setting that controls the amount of a particular
resource that a user can use. For example, there are profile resource parameters for CPU time,
disk space, and number of concurrent sessions. When a user is assigned to a profile, they
inherit the profile's resource limits. If a user exceeds a resource limit, the database will take
some action, such as terminating the user's session or returning an error. Profile resource
parameters are used to help manage the performance and security of an Oracle database. By
setting resource limits, database administrators can prevent users from using too many
resources and causing performance problems. They can also prevent users from accessing
sensitive data or performing unauthorized operations. Profile resource parameters can be
defined when a profile is created or modified. They can also be changed using the ALTER
PROFILE statement. It is important to note that profile resource parameters are not the
only way to manage resource usage in an Oracle database. There are also other methods, such
as using SQL statements to set resource limits for individual users or sessions. However, profile
resource parameters are a powerful tool that can be used to help manage the performance and
security of an Oracle database.
Here are some of the most common profile resource parameters:

•CPU_PER_SESSION: This parameter limits the amount of CPU time that a user can use per
session.
•CONNECT_TIME: This parameter limits the amount of time that a user can be connected to
the database.
• LOGICAL_READS_PER_SESSION: This parameter limits the number of logical reads that a
user can perform per session.
• PRIVATE_SGA: This parameter limits the amount of private memory that a user can allocate
in the shared pool.
• SESSIONS_PER_USER: This parameter limits the number of concurrent sessions that a user
can have open.

**
The CREATE PROFILE command in Oracle is used to create a new profile .
A profile is a collection of permissions, resource limits, and password constraints that define the
behavior of a user in the database. Profiles are used to control how users can access and use
the database, and to help ensure the security of the database.
The syntax for the CREATE PROFILE command is as follows:
CREATE PROFILE profile_name [LIMIT { resource_parameters | password_parameters}];
The profile_name parameter is the name of the profile to be created. The LIMIT clause specifies
the resource limits and password constraints for the profile. The resource_parameters clause
specifies the limits on the amount of resources that users assigned to the profile can use. The
password_parameters clause specifies the password constraints for users assigned to the
profile.
Here is an example of the CREATE PROFILE command:
CREATE PROFILE hr_profile LIMIT
SESSIONS_PER_USER 10,
CONNECT_TIME 3600,
DISK_SPACE 400M;
This command creates a profile named hr_profile. The profile limits users assigned to it to 10
concurrent sessions, 3600 seconds of connect time, and 400MB of disk space.
The CREATE PROFILE command can also be used to create a profile with default resource
limits and password constraints. To do this, omit the LIMIT clause. For example, the following
command creates a profile named default with default resource limits and password constraints:
CREATE PROFILE default;
Once a profile is created, users can be assigned to it using the ALTER USER statement. For
example, the following command assigns the hr_profile profile to the user hr:
ALTER USER hr PROFILE hr_profile;
The default profile usually sets moderate limits to balance security and usability. Some common
default parameter values found in Oracle Database include:
• FAILED_LOGIN_ATTEMPTS: This parameter sets the number of failed login attempts allowed
before the user account is locked. The default value is typically 10.
• PASSWORD_LIFE_TIME: It defines the maximum time period (in days) before a user must
change their password. The default value is typically set to unlimited or a long duration like 180
days.
• PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX: These parameters control
password reuse policies. They specify the minimum time period and number of password
changes required before a user can reuse a previous password. The default values are typically
set to unlimited or a long duration and unlimited, respectively.
• SESSIONS_PER_USER: This parameter sets the maximum number of concurrent sessions
allowed for a user. The default value is typically unlimited or a moderate value like 10 or 20.
• CPU_PER_SESSION and CPU_PER_CALL: These parameters define the limits on CPU
usage for a session or a single SQL execution. The default values are typically unlimited or high
values to avoid overly restrictive limits.
• IDLE_TIME: It specifies the maximum duration of inactivity allowed for a session. The default
value is typically unlimited or a moderate value like 15 or 30 minutes.

Manage Password with Profiles

The specific parameter values for a password profile in Oracle Database can vary based on
your requirements and security policies. However, I can provide you with some commonly used
parameter values as a reference. Here are example values for password profile parameters:
1. PASSWORD_VERIFY_FUNCTION: This parameter specifies a custom password verification
function. The value can be the name of a user-defined function that validates the password
based on specific requirements.
2. PASSWORD_COMPLEXITY: This parameter defines the complexity requirements for
passwords.
Example value: 'MIXED_CASE, SPECIAL_CHARS, MIN_LENGTH 8'.
3. PASSWORD_LIFE_TIME: It sets the maximum time period (in days) before a user must
change their password. Example value: 90 (passwords expire after 90 days).
4. PASSWORD_REUSE_TIME: This parameter determines the minimum number of days that
must pass before a password can be reused. Example value: 60 (users cannot reuse a
password within 60 days).
5. PASSWORD_REUSE_MAX: It sets the number of password changes required before a user
can reuse a previous password. Example value: 5 (users must change passwords 5 times
before reusing a previous password).
6. FAILED_LOGIN_ATTEMPTS: This parameter specifies the number of failed login attempts
allowed before the user account is locked. Example value: 3 (after 3 failed attempts, the account
is locked).
7. PASSWORD_LOCK_TIME: It sets the duration (in days) for which an account remains locked
after exceeding the maximum failed login attempts. Example value: 1 (the account is locked for
1 day).
8. PASSWORD_GRACE_TIME: This parameter determines the grace period (in days) after the
password expires, during which the user can still log in without changing the password.
Example
value: 7 (users have 7 days of grace period).

Control Resource Usage with Profiles

In Oracle, you can control resource usage using profiles. Profiles allow you to allocate specific
resource limits to database users, including limits on CPU usage, session duration, and parallel
execution.
Here are the steps to create and manage profiles in Oracle:
1. Create a profile:
CREATE PROFILE profile_name LIMIT
SESSIONS_PER_USER n,
CPU_PER_SESSION n,
CPU_PER_CALL n,
CONNECT_TIME n,
IDLE_TIME n;
Replace profile_name with the desired name for your profile and n with the numeric values
representing the limits for each parameter. Each parameter controls a specific aspect of
resource usage.
2. Assign a profile to a user:
ALTER USER username PROFILE profile_name;

3. Modify a profile:
ALTER PROFILE profile_name LIMIT
SESSIONS_PER_USER n,
CPU_PER_SESSION n,
CPU_PER_CALL n,
CONNECT_TIME n,
IDLE_TIME n,
LOGICAL_READS_PER_SESSION n,
LOGICAL_READS_PER_CALL n,
PRIVATE_SGA n,
COMPOSITE_LIMIT n;
Use this syntax to modify the limits of an existing profile. Specify the parameter you want to
modify and provide the new value for that parameter.
4. Remove a profile assignment from a user:
ALTER USER username PROFILE DEFAULT;
.
5. Drop a profile:
DROP PROFILE profile_name;

Maintain Profiles
To maintain profiles in Oracle, you can perform various tasks such as creating, modifying,
enabling, disabling, and deleting profiles. Here are the steps to maintain profiles in Oracle:
1. Create a profile: To create a profile, use the CREATE PROFILE statement followed by the
desired profile name and the resource limits you want to set. Refer to Step 1 in the previous
response for the
syntax.
2. Modify a profile: To modify an existing profile, use the ALTER PROFILE statement followed
by the profile name and the updated resource limits. Refer to Step 3 in the previous response
for the syntax.
3. Enable or disable a profile: Profiles can be enabled or disabled to control their usage.
When a profile is disabled, it is not enforced for any users assigned to it. Use the ALTER
PROFILE statement with the ENABLE or DISABLE keyword to enable or disable a profile,
respectively.
ALTER PROFILE profile_name ENABLE; ALTER PROFILE profile_name DISABLE;
4. Assign a profile to a user: To assign a profile to a user, use the ALTER USER statement
followed by the username and the profile name. Refer to Step 2 in the previous response for the
syntax.
5. Remove a profile assignment from a user: To remove a profile assignment from a user
and revert them to the default profile, use the ALTER USER statement with the PROFILE
DEFAULT
clause.
ALTER USER username PROFILE DEFAULT;
6. Delete a profile: To delete a profile from the database, use the DROP PROFILE statement
followed by the profile name.
DROP PROFILE profile_name;
4.2 Database Auditing:
Overview of database security
Database security refers to the protection of data stored in a database from unauthorized
access, use, disclosure, disruption, modification, or destruction. Databases are a crucial
component of many applications and organizations, containing sensitive and valuable
information. Therefore, ensuring the security of databases is vital to safeguarding the integrity,
confidentiality, and availability of data.
Here are some key aspects and measures involved in database security:
1. Access control: Controlling access to the database is fundamental to security. This involves
implementing user authentication mechanisms, such as usernames and passwords, and
assigning appropriate access privileges to users or roles. Access control ensures that only
authorized individuals or applications can interact with the database.
2. Encryption: Encrypting data is essential for protecting its confidentiality. Encryption
transforms data into a non-readable format, and it can only be accessed with the appropriate
decryption key. Encryption techniques can be applied to the entire database, specific tables, or
sensitive fields within the database.
3. Auditing and logging: Implementing auditing and logging mechanisms allows organizations
to track and monitor activities within the database. It involves recording events like logins, data
modifications, and access attempts. These logs can help in identifying and investigating security
incidents or policy violations. unclear a-espast
4. Data masking and anonymization: Data masking is the process of obfuscating sensitive
data, such as personally identifiable information (PII), so that it remains usable for certain
purposes while protecting individual privacy. Anonymization techniques can be employed to
remove or de-identify personally identifiable information from the database.
5. Regular updates and patches: Keeping database software up to date with the latest
security patches and updates is crucial. Vendors frequently release updates to address
vulnerabilities and enhance security. Promptly applying these updates helps protect against
known vulnerabilities.
6. Secure network communication: The data exchanged between the database server and
client applications should be transmitted securely. Using encryption protocols like SSL/TLS for
network communication helps prevent unauthorized interception or eavesdropping.
7. Backup and disaster recovery: Regularly backing up the database and having a robust
disaster recovery plan is essential for database security. Backups ensure that data can be
restored in the event of accidental deletion, corruption, or system failures. It is crucial to store
backups securely to prevent unauthorized access.
8. Database activity monitoring (DAM): DAM systems analyze and monitor database activity
in real-time, looking for suspicious or unauthorized actions. They can detect anomalies,
unauthorized access attempts, and unusual patterns of behavior, helping organizations respond
promptly to potential threats.
9. Security testing and vulnerability assessments: Regular security testing and vulnerability
assessments help identify weaknesses and vulnerabilities in the database system. Techniques
like penetration testing, vulnerability scanning, and code reviews can uncover security flaws and
assist in addressing them.
Overview of database auditing
Database auditing refers to the process of monitoring and recording activities and events that
occur within a database system. It involves tracking and logging various actions performed on
the database, such as data modifications, user access, and system changes. The primary
purpose of database auditing is to ensure data integrity, security, and compliance with
regulatory [Link], database auditing is a critical component of ensuring data
security, integrity, and regulatory compliance. It provides transparency, accountability, and the
ability to investigate and mitigate (make less) potential risks and incidents within a database
system.
Benefits of database auditing:
1. Data Integrity: Auditing helps maintain the accuracy and consistency of data by monitoring
changes made to the database. It records modifications, deletions, and additions of data,
enabling administrators to identify and rectify unauthorized or erroneous changes.
2. Security: Auditing plays a crucial role in identifying potential security breaches (agreement /
code of conduct) and malicious activities within a database. Auditing can also help in forensic
(finding evidence to solve crime) investigations by providing an audit trail (detailed chronological
order) of activities.
3. Compliance: Many industries and organizations have specific regulations and compliance
(obedience) requirements for data security and privacy, such as the General Data Protection
Regulation (GDPR) .Auditing ensures that these regulations are followed by tracking and
documenting activities related to sensitive data.
4. Accountability: Auditing provides accountability by recording and associating each action
with the corresponding user or system account. It helps in identifying who performed a particular
action, allowing administrators to hold individuals responsible for any unauthorized or
inappropriate activities.
5. Detecting Anomalies: Database auditing enables the identification of unusual or suspicious
activities that may indicate a security threat or system malfunction. By analyzing audit logs,
administrators can detect patterns or deviations from normal behavior, triggering alerts or further
investigation.
6. Forensic Analysis: Audit logs serve as a valuable source of information for forensic analysis
in the event of a security incident or data breach. The detailed records can be analyzed to
determine the cause, scope, and impact of the incident, aiding in remediation and future
prevention.
7. Performance Optimization: Auditing can also help identify inefficient queries or resource-
intensive operations that may impact database performance. By analyzing the audit logs,
administrators can optimize queries, identify bottlenecks.

Security Compliance
Security compliance refers to the adherence to specific security standards, regulations, and best
practices that are designed to protect sensitive data, prevent security breaches, and maintain
the overall security posture of an organization. Compliance is the state of being in accordance
with established guidelines or specifications, or the process of becoming so. Compliance
requirements may vary depending on the industry, geographic location, and the type of data
being handled. Non-compliance with security regulations can result in legal consequences,
financial penalties, damage to reputation, and loss of customer trust. Therefore, organizations
invest significant efforts in establishing security controls, conducting risk assessments,
implementing security policies, and maintaining security compliance to mitigate risks and protect
sensitive data.
Key aspects of security compliance:
1. Regulatory Frameworks: Various regulatory frameworks define security requirements and
compliance standards. Examples include the General Data Protection Regulation (GDPR),
Health Insurance Portability and Accountability Act (HIPAA), Payment Card Industry Data
Security Standard (PCI DSS), Sarbanes-Oxley Act (SOX), and many others. Organizations are
obliged to comply with these regulations based on their business activities and the data they
handle.
2. Data Privacy: Compliance with data privacy regulations is a crucial aspect of security
compliance. Organizations must protect personally identifiable information (PII) and sensitive
data, such as financial, medical, or personal records. Compliance often involves implementing
appropriate security controls, obtaining user consent, providing data breach notifications, and
ensuring data subject rights, such as the right to access, rectify, or erase personal data.
3. Access Control and Authentication: Compliance standards require organizations to
enforce strong access control mechanisms and authentication processes. This includes
implementing secure user authentication methods, such as two-factor authentication (2FA) or
multi-factor authentication (MFA), and controlling user access privileges based on the principle
of least privilege (PoLP). Access to sensitive systems and data should be restricted to
authorized individuals and monitored effectively.
4. Security Policies and Procedures: Organizations must establish comprehensive security
policies and procedures to guide employees in securely handling data and using IT systems.
Policies may include password management, encryption, incident response, data classification,
and employee security awareness training. Compliance involves regularly reviewing and
updating these policies to address emerging threats and industry best practices.
5. Security Incident Management: Compliance requires organizations to have incident
response plans and procedures in place to detect, respond to, and recover from security
incidents. This includes processes for reporting and investigating incidents, preserving
evidence, notifying affected parties, and implementing corrective actions to prevent future
occurrences. Compliance may also involve timely reporting of incidents to regulatory authorities.
6. Security Auditing and Monitoring: Regular security audits and monitoring are essential for
compliance. Organizations should perform internal or external audits to assess the effectiveness
of security controls, identify vulnerabilities, and ensure compliance with security standards.
Continuous monitoring, such as log analysis, intrusion detection systems (IDS), and security
information and event management (SIEM) tools, helps detect and respond to security threats
promptly.
7. Vendor and Third-Party Management: Organizations often work with vendors and third
parties who have access to their systems or handle their data. Compliance requires
organizations to assess the security practices of these vendors, conduct due diligence, and
establish contractual agreements that outline security obligations. Regular audits or
assessments may be performed to ensure third-party compliance.
Standard Auditing
Standard auditing in Oracle refers to the built-in auditing feature provided by Oracle Database. It
allows organizations to track and monitor user activities and system events within the Oracle
database environment. Oracle auditing enables the recording of specific actions and operations
performed on database objects, such as tables, views, procedures, and privileges.
It's important to note that Oracle auditing should be configured and managed carefully to ensure
proper security and system performance. Auditing can generate significant amounts of data, so
organizations need to consider the impact on storage requirements and performance overhead.
Oracle also offers additional advanced auditing features and options, such as Fine-Grained
Auditing (FGA) and Database Vault, which provide more granular auditing capabilities and
enhanced security controls. Overall, standard auditing in Oracle enables organizations to
monitor and track user activities, enhance data security, and meet compliance requirements by
maintaining a comprehensive audit trail within the Oracle database environment.

Unified Audit Trail

The Unified Audit Trail (UAT) is a feature introduced in Oracle Database 12c that provides a
centralized and comprehensive auditing [Link] UAT replaces the traditional audit trail and
provides enhanced functionality and flexibility for auditing in Oracle databases.
The Unified Audit Trail simplifies auditing in Oracle databases by consolidating audit data from
various sources, providing a centralized repository, and offering flexibility in configuration and
analysis. It enhances security, simplifies compliance reporting, and facilitates effective
monitoring and analysis of audit information.
Benefits of the Unified Audit Trail:
1. Centralized Audit Repository: The UAT stores audit records in a dedicated table within the
database, known as the AUD$ table. This centralized repository simplifies audit data
management and enables efficient querying and reporting on audit information.
2. Unified Auditing Sources: The UAT captures audit data from different sources within the
Oracle database, including Standard Auditing, Fine-Grained Auditing (FGA), and Oracle
Database Vault. It consolidates audit records related to user activities, privilege usage, data
access, and other auditable actions into a single, unified view.
[Link] Auditing Policies: The UAT allows flexible and granular configuration of auditing
policies. Administrators can define audit policies at the database, table, or even column level.
They Can select specific actions to be audited and set various parameters, such as the audit
trail destination,audit record format, and retention period.
4. Comprehensive Audit Data: The UAT captures detailed audit information, including the SQL
text,bind variables, and application context data. It provides a rich set of attributes for audited
actions,facilitating thorough analysis and investigation of security incidents or compliance
violations.
5. Enhanced Security and Performance: The UAT improves security by ensuring that audit
records cannot be tampered with or deleted by database users. It also minimizes the impact on
database performance by implementing efficient mechanisms for capturing and managing audit
data.
Separation of Audit Responsibilities with the AUDIT_ADMIN and AUDIT_VIEWER Roles

This segregation helps minimize the risk of unauthorized changes, enhances accountability, and
ensures the independence and objectivity of the audit function.

1. AUDIT_ADMIN Role:
• The AUDIT_ADMIN role is typically assigned to individuals who are responsible for
managing and overseeing the audit process.
• Users with the AUDIT_ADMIN role have privileges to perform administrative tasks related
to audits, such as creating audit policies, defining audit trails, configuring audit settings, and
managing audit logs.
• This role allows individuals to have full control and authority over the audit process, ensuring
its effectiveness and compliance with relevant standards and regulations.
2. AUDIT_VIEWER Role:
• The AUDIT_VIEWER role is usually assigned to individuals who need to review and
monitor audit activities but do not have administrative rights.
• Users with the AUDIT_VIEWER role have read-only access to audit logs and reports,
allowing them to view and analyze audit data.
• This role is suitable for stakeholders such as compliance officers, internal or external
auditors, or management personnel who need visibility into audit activities without having
the ability to make changes or perform administrative tasks.

Configure the Audit Trail

To configure an audit trail, you typically need to consider the following steps:
1. Identify the Audit Requirements: Determine the specific audit requirements for your
organization. This may include compliance regulations, internal policies, or industry standards
that dictate what needs to be audited.
2. Define Audit Events: Identify the critical events or actions that need to be audited. These
events could include user logins, data modifications, system configuration changes, file access,
or any other activity that is relevant to your organization's audit objectives.
3. Select the Audit Trail Destination: Decide where the audit trail will be stored. It can be a
dedicated server, a separate database, or a secure storage solution. Ensure that the destination
is secure, easily accessible, and provides adequate storage capacity for your audit logs.
4. Determine the Level of Detail: Define the level of detail required for each audit event. This
could range from capturing basic information such as the user, timestamp, and action
performed, to more detailed data including the old and new values, IP addresses, or additional
contextual information.
5. Implement Audit Trail Mechanism: Configure your systems, applications, or databases to
generate audit logs. This might involve enabling auditing features, configuring logging settings,
or integrating with third-party tools that facilitate audit trail management.
6. Set Access Controls: Implement appropriate access controls to ensure that only authorized
personnel can view and modify the audit trail. This helps maintain the integrity and
confidentiality of the audit data.
7. Regularly Monitor and Review Audit Logs: Assign responsibilities to individuals with the
AUDIT_VIEWER role (as discussed earlier) to regularly monitor and review the audit logs. This
ensures that any suspicious or non-compliant activities are promptly identified and addressed.

a) To create a new user profile for controlling resource.

Create the User Profile:
CREATE PROFILE limited_profile LIMIT
CPU_PER_SESSION 1000
CONNECT_TIME 60
IDLE_TIME 30;
Assign the Profile to Users:
ALTER USER username PROFILE profile_name;
Modify Profile Limits (Optional):
ALTER PROFILE profile_name LIMIT resource_limit [, resource_limit]...;

b) To create a new user profile for controlling passwords.

Create the User Profile:
CREATE PROFILE secure_password_profile LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_MAX 5;
Assign the Profile to Users:
ALTER USER username PROFILE profile_name;
Modify Profile Limits (Optional):
ALTER PROFILE profile_name LIMIT resource_limit [, resource_limit]...;

c) To assign the profile to the user.

ALTER USER username PROFILE profile_name;

d) To remove the profile from the user.

ALTER USER username PROFILE DEFAULT;

e) To modify user profile parameter of exiting user profile.

ALTER PROFILE profile_name LIMIT parameter_name new_value;
For example, if you have a profile named 'secure_password_profile' and you want to change the
maximum password age to 90 days, you would run:ALTER PROFILE secure_password_profile
LIMIT PASSWORD_LIFE_TIME 90;
f) To enable/disable the user profile.
To Enable a User Profile:
ALTER PROFILE profile_name ACCOUNT UNLOCK;
To Disable a User Profile:
ALTER PROFILE profile_name ACCOUNT LOCK;

g) To delete the user profile.

DROP PROFILE profile_name;

Unit 5 Concurrency Backup and Recovery: 7 hrs.

5.1 Concurrency:
Levels of locking in Oracle
In Oracle, there are several levels of locking that can be used to control concurrent access to
data. These levels provide different levels of isolation and concurrency control, allowing multiple
transactions to work with the data simultaneously while ensuring data [Link] are the
commonly used levels of locking in Oracle:
1. Row-Level Locking:
• Row-level locking is the most granular(small and distinct) level of locking in Oracle. It
means that locks are acquired and released at the level of individual rows.
• When a transaction modifies a row, it acquires a lock on that row, preventing other
transactions from modifying the same row concurrently.
• Row-level locks are typically used to minimize contention(opposite of conflict) and allow
maximum concurrency.
2. Table-Level Locking:
• Table-level locking means that locks are acquired and released at the level of entire tables.
• When a transaction modifies a table, it acquires a lock on the entire table, preventing other
transactions from modifying any part of the table concurrently.
• Table-level locks provide less concurrency compared to row-level locks but can be useful in
certain scenarios where entire tables need to be locked.
3. Page-Level Locking:
• Page-level locking means that locks are acquired and released at the level of data pages.
• A data page is a fixed-size block of data, and multiple rows can reside within a single data
page.
• When a transaction modifies data within a particular page, it acquires a lock on that page,
preventing other transactions from modifying the same page concurrently.
• Page-level locking can strike a balance between the granularity of row-level locking and the
overhead of table-level locking.
4. Tablespace-Level Locking:
• Tablespace-level locking means that locks are acquired and released at the level of entire
tablespaces.
• A tablespace is a logical storage unit that contains one or more data files.
• When a transaction modifies any table or data within a tablespace, it acquires a lock on the
entire tablespace, preventing other transactions from modifying any part of the tablespace
concurrently.
• Tablespace-level locking provides the least concurrency and is typically used in specialized
Scenarios.

Methods used to acquire locks: Lock acquisition refers to the process of gaining control over
a lock or unlocking mechanism to access or secure an object or space. There are various
methods used to acquire a lock, depending on the type of lock and the purpose for which it is
being acquired.
Here are some common methods:
1. Key-based methods: Most traditional locks rely on keys for lock acquisition. The lock is
designed to be opened using a specific key or set of keys that align with the lock's internal
mechanisms. The correct key is inserted into the lock, turned, and the lock mechanism is
disengaged, allowing access.
2. Lock picking: Lock picking is a skillful technique of manipulating the lock's internal
components to simulate the action of a correct key. Lock pickers use various specialized tools,
such as picks and tension wrenches, to manipulate the lock's pins, tumblers, or other
mechanisms and gain entry without using the original key.
3. Bypassing: In some cases, it may be possible to bypass the lock altogether without picking
or damaging it. Bypassing involves using alternative methods to gain access, such as using a
bypass tool, exploiting vulnerabilities, or utilizing master keys, if available. Bypassing techniques
vary depending on the specific lock type and the vulnerabilities present.
4. Impressioning: Impressioning is a method where a blank key is used to create a working
key for the lock. By inserting the blank key into the lock and manipulating it, the lock's internal
components leave unique marks on the key. These marks are then used to file and shape the
key until it successfully opens the lock.
5. Lock bumping: Bumping involves using a specially crafted key, known as a bump key, to
exploit the mechanical properties of pin-tumbler locks. The bump key is inserted partially into
the lock and then struck or "bumped" with a blunt object, causing the pins to momentarily align
and allow the lock to be turned open.

Resolve Conflicts
It's important to carefully test and monitor the effects of any changes implemented to resolve
conflicts. Monitor the system's performance, data consistency, and user experience to ensure
that the applied solutions effectively address the contention issues without introducing new
problems. To resolve conflicts and contention issues related to locks in a database system, you
can consider the following approaches:
1. Analyze the cause: Identify the underlying cause of the conflicts. This can be done by
analyzing the specific queries, transactions, or operations that are involved in the contention.
Look for patterns, common access patterns, and potential dependencies between transactions.
2. Optimize queries: Review and optimize the queries involved in the contention. Ensure that
appropriate indexes are in place to improve query performance and reduce the time required to
acquire locks. Consider optimizing the query execution plan or rewriting queries to minimize the
data accessed or locked.
3. Transaction isolation levels: Evaluate the isolation levels used by transactions. Higher
isolation levels, such as SERIALIZABLE, provide stronger consistency guarantees but can
increase the potential for conflicts. Consider adjusting the isolation level to a lower level if the
application requirements allow it. However, be cautious of the potential impact on data
consistency.
4. Lock granularity: Review the lock granularity being used. Row-level locking provides more
concurrency compared to higher-level locks such as table-level locks. Ensure that locks are
acquired at the appropriate level of granularity to minimize conflicts.
5. Lock timeouts: Set appropriate lock timeouts to prevent long waits and potential deadlocks.
If a transaction cannot acquire a lock within a reasonable time, it can be aborted or retried later.
This prevents transactions from holding locks indefinitely, improving overall system
concurrency.

Data Concurrency
Data concurrency refers to the ability of multiple users or processes to access and modify the
same data simultaneously in a shared environment, such as a database. Ensuring data
concurrency requires mechanisms and techniques to coordinate and control the access and
modification of shared data.
Here are some key aspects and methods related to data concurrency:
1. Isolation Levels: Isolation levels define the degree of concurrent access and control over
shared data. Different isolation levels, such as Read Uncommitted, Read Committed,
Repeatable Read, and Serializable, offer varying levels of data consistency and concurrency.
These levels determine the visibility and impact of concurrent transactions on each other's data.
2. Locking: Locking is a commonly used technique to manage data concurrency. It involves
acquiring locks on shared data items to prevent conflicting access or modification by other
transactions. Locks can be exclusive (write locks) or shared (read locks), and they ensure that
only one transaction can modify the data while others wait or obtain read-only access.
3. Two-Phase Locking (2PL): It follows a strict set of rules where each transaction acquires all
the necessary locks before it begins modifying data, and it releases all locks only after it has
completed its modifications.
4. Optimistic Concurrency Control: Transactions proceed independently without acquiring
locks, and only during the commit phase, conflicts are checked. If conflicts are detected, the
necessary actions are taken to resolve them.
5. Conflict Detection and Resolution: Techniques like deadlock detection, wait-die, wound-
wait, and timestamp-based conflict resolution help identify and resolve conflicts to maintain data
integrity and system efficiency.

DML Locks
DML stands for Data Manipulation Language, which is a subset of SQL (Structured Query
Language) used to manipulate and modify data in a database. When performing DML
operations, such as inserting, updating, or deleting data, it's important to understand how locks
are used to manage concurrent access to the data by multiple transactions.
Locks in a database system are used to ensure data consistency and prevent conflicts when
multiple transactions are trying to access or modify the same data simultaneously. They help
maintain the integrity of the database by preventing dirty reads, non-repeatable reads, and other
concurrency-related issues.
Here are some common types of locks used in DML operations:
1. Shared Lock (S-lock): It allows multiple transactions to read the same data simultaneously. It
prevents other transactions from acquiring an exclusive lock (X-lock) on the same data until the
shared lock is released.
2. Exclusive Lock (X-lock): It is acquired when a transaction wants to modify or delete data. An
exclusive lock prevents other transactions from acquiring any lock (shared or exclusive) on the
same data until the exclusive lock is released.
3. Intent Lock: It is a higher-level lock acquired on a parent object to indicate that a transaction
intends to acquire locks on lower-level objects. For example, an intent lock on a table indicates
that the transaction intends to acquire locks on individual rows or pages within that table.
4. Update Lock (U-lock): It is a special type of lock that allows concurrent readers but prevents
concurrent writers. It is used to optimize scenarios where a transaction wants to read a row with
the intention of modifying it later. The update lock prevents other transactions from acquiring an
exclusive lock until the transaction decides whether to perform an update or not.

Prevent Locking Problem:

Locking problems in a database system can lead to issues such as deadlocks, contention, and
decreased [Link] prevent or mitigate locking problems, consider the following
strategies:
1. Optimize transaction scope: Minimize the duration of transactions by reducing the amount
of data accessed and modified within a single transaction. This approach reduces the likelihood
of conflicts with other transactions and decreases the holding time for locks.
2. Use appropriate isolation levels: Understand the different isolation levels provided by your
database system (e.g., READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and select
the isolation level that strikes the right balance between consistency and concurrency. Higher
isolation levels provide stronger consistency guarantees but may increase the potential for
locking conflicts.
3. Use row-level locking: Whenever possible, use row-level locking instead of higher-level
locks, such as table-level locks. Row-level locking allows concurrent access to different rows,
reducing contention and increasing concurrency.
4. Avoid long-running transactions: Long-running transactions increase the likelihood of
conflicts and contention. Break down large transactions into smaller, more focused units of work
to reduce the holding time for locks and improve concurrency.
5. Batch updates: Instead of performing individual updates, consider batching multiple updates
into a single transaction. This approach reduces the number of locks acquired and released,
minimizing contention.
Use the Undo Advisor
The "Undo Advisor" is a feature in Oracle Database that provides recommendations for
optimizing the configuration of the undo tablespace. It helps database administrators (DBAs)
determine the optimal size and configuration of the undo tablespace based on the workload and
system requirements. The undo tablespace is used to store the information necessary to
rollback or undo changes made to the database.
To use the Undo Advisor in Oracle, you can follow these steps:
1. Connect to the Oracle Database with a user account that has the necessary privileges, such
as the SYS or SYSTEM user.
2. Launch the Oracle Enterprise Manager Database Control or Oracle Enterprise Manager
Cloud Control, depending on the version of Oracle you are using.
3. Navigate to the Undo Advisor section. This may vary slightly depending on the specific
version of Oracle and the management interface you are using, but typically, you can find it
under the "Performance" or "Database Configuration" section.
4. Once you access the Undo Advisor, you can provide information about your workload and
system requirements. This may include specifying the number of concurrent transactions, the
estimated transaction duration, and other relevant parameters.

Oracle Backup Solutions

Oracle provides several backup solutions that allow you to protect your database and ensure
data recoverability in case of failures or disasters. When designing your backup strategy,
consider factors such as recovery time objectives (RTO), recovery point objectives (RPO),
database size, backup storage requirements, and compliance regulations. It's also important to
regularly test and validate your backup and recovery processes to ensure their effectiveness.
Here are some Oracle backup solutions commonly used:
Recovery Manager (RMAN): RMAN is the recommended Oracle utility for managing backups
and recoveries. It provides a centralized and efficient approach to backup and recovery
[Link] can take full, incremental, and cumulative backups, as well as handle block-
level corruption detection and repair.
Oracle Data Pump: Oracle Data Pump provides export and import utilities that allow you to
create logical backups of your database. With Data Pump, you can export specific database
objects or the entire database into a binary file, which can be imported back into the database to
restore the data.
User-Managed Backup: Oracle also supports user-managed backups, where you can
manually back up database files using operating system commands or third-party backup tools.
This approach gives you more control over the backup process but requires careful
management and coordination to ensure consistency and integrity.
Oracle Secure Backup (OSB): Oracle Secure Backup is an optional backup management
solution that provides centralized management and data protection for Oracle databases. It
integrates with RMAN and offers features like deduplication, encryption, and tape library
management for efficient and secure backups.

Unit 6 Database Maintenance and Performance: 4 hrs.

6.1 Database Maintenance:
Overview of database Maintenance
Database maintenance is a critical process that involves regular tasks and activities aimed at
ensuring the optimal performance, reliability, and security of a database system. Proper
maintenance is essential to prevent data corruption, improve system efficiency, and reduce the
risk of data loss. Database maintenance is an ongoing process and requires collaboration
between database administrators, system administrators, and other relevant stakeholders.
Ways / methods of database maintenance:
1. Backup and Recovery: Regularly backing up the database is crucial in case of hardware
failures, software errors, or data corruption. Backups can be full, incremental, or differential,
depending on the organization's needs. Additionally, testing the backup files and developing a
robust recovery plan are equally important.
2. Database Optimization: Database performance can degrade over time due to growth in data
and usage. Regular optimization of the database involves defragmenting indexes, rebuilding or
reorganizing tables, and updating statistics to ensure efficient query execution and faster
response times.
3. Security Management: Ensuring the security of the database is vital to protect sensitive
information from unauthorized access, modification, or deletion. This involves managing user
access and permissions, encryption of sensitive data, and implementing security patches and
updates.
4. Monitoring and Performance Tuning: Monitoring the database's health and performance
helps identify potential issues before they become critical problems. Regularly analyzing
performance metrics allows administrators to fine-tune configurations, identify bottlenecks, and
optimize resource utilization.
5. Purging and Archiving: Over time, databases accumulate historical and obsolete data,
which can impact performance and storage requirements. Purging or archiving old data can free
up space, improve query performance, and make backups more manageable.
6. Data Integrity Checks: Regularly running integrity checks helps identify and fix data
inconsistencies or corruption. This process ensures that the data stored in the database is
accurate and reliable.

The Automatic Workload Repository

The Automatic Workload Repository (AWR) is a feature of Oracle Database that provides a
built-in repository for collecting and storing performance-related statistics and workload
information. Additionally, the retention period and snapshot frequency can be customized based
on the specific needs of the database environment. AWR plays a crucial role in proactively
managing and optimizing Oracle Database performance, making it an essential tool for DBAs
and performance analysts.
Key features and components of the Automatic Workload Repository (AWR) include:
1. Data Collection: AWR automatically collects and stores performance-related data at regular
intervals, typically once every hour by default. It captures information on database performance
metrics, system statistics, wait events, SQL execution statistics, and other performance-related
data.
2. Snapshots: AWR captures data in time-based intervals known as snapshots. These
snapshots create a historical performance baseline that allows DBAs to compare performance
metrics over different periods and identify trends and potential performance issues.
3. Retention: The data collected by AWR is retained for a specific duration, which can be
configured by the DBA. The retention period allows for long-term performance analysis and
historical comparison.
4. Report Generation: AWR provides various built-in reports, including the Automatic Database
Diagnostic Monitor (ADDM) report, which highlights performance issues and offers
recommendations for resolving them. AWR reports can be generated using SQL Developer,
Enterprise Manager, or command-line tools.
5. AWR Views: AWR data is stored in the database, and DBAs can access this data through
AWR views, which are a set of database views specifically designed to query and analyze AWR
data.
6. Tuning and Analysis: By using AWR data and reports, DBAs can identify performance
bottlenecks, problematic SQL queries, resource utilization patterns, and other database
performance-related issues. AWR provides valuable insights for database tuning and
optimization.

Monitor an Oracle Database

Database monitoring should focus on important metrics like CPU usage, memory utilization, I/O
performance, database locks, wait events, and resource-intensive SQL queries. Monitoring
these metrics helps to identify performance bottlenecks and proactively address issues before
they become critical. It's essential to establish a monitoring strategy and choose the appropriate
tools based on your organization's needs and budget. Regular monitoring can help ensure your
Oracle database runs smoothly, maximizing performance and minimizing downtime.

Here are some common methods and tools used to monitor an Oracle database:
1. Oracle Enterprise Manager (OEM): Oracle provides a comprehensive tool called Oracle
Enterprise Manager to manage and monitor Oracle databases. OEM offers a graphical interface
that allows you to monitor the database's performance, health, and manage various aspects of
the database.
2. Oracle Grid Control: It is another Oracle management tool that allows you to monitor and
manage multiple Oracle databases centrally.
3. Oracle Database Performance Views: Oracle provides a set of dynamic performance views
(e.g., V$SESSION, V$SQL, V$SYSTEM_EVENT) that contain valuable real-time information
about the database performance and resource usage.
4. Custom Scripts: You can write your own scripts using SQL and PL/SQL to monitor specific
aspects of the database, like checking tablespace usage, monitoring long-running queries, etc.
5. Operating System Tools: Use OS-level monitoring tools like top, sar, vmstat (on
Unix/Linux), or Performance Monitor (on Windows) to monitor CPU, memory, and disk usage,
which can indirectly indicate the database performance.

Use the Advisors

Oracle Advisors are a set of tools and features that assist database administrators in identifying
and resolving potential performance, security, and maintenance issues in an Oracle database.
These advisors are built-in components of Oracle Database and can be utilized through Oracle
Enterprise Manager (OEM) or directly using SQL commands.
Oracle Advisors and their functionalities:
1. SQL Tuning Advisor: The SQL Tuning Advisor helps improve the performance of poorly
performing SQL queries by providing recommendations for creating or modifying database
objects like indexes, materialized views, and SQL profiles. It analyzes the SQL statements in
the database and suggests optimizations to enhance execution plans and reduce response
times.
2. SQL Access Advisor: The SQL Access Advisor assists in designing or modifying database
structures (indexes, materialized views, partitions, etc.) to optimize SQL performance. It
considers the workload and usage patterns to recommend changes that can lead to improved
query execution.
3. Segment Advisor: The Segment Advisor analyzes the space usage of database segments
(tables, indexes, partitions) and suggests actions to reclaim wasted space or reorganize
segments for better performance.
4. Undo Advisor: This advisor helps in sizing the UNDO tablespace to ensure that sufficient
space is available for handling long-running transactions or flashback queries.
5. Memory Advisor: The Memory Advisor recommends optimal settings for memory-related
parameters like the SGA (System Global Area) and PGA (Program Global Area) to enhance the
overall database performance.

Tuning Activities
Database tuning is the process of optimizing the performance of a database system to ensure
efficient and effective data retrieval and manipulation.
It's important to note that database tuning is a complex and ongoing process. Regularly
monitoring and adjusting your database based on changing requirements and workloads is
essential to maintaining optimal performance. Additionally, consider seeking the assistance of a
qualified Oracle database administrator or performance tuning expert for complex tuning
scenarios.
Tuning activities you might perform in an Oracle database:
1. Query Optimization: Analyze and optimize the SQL queries used in your applications. Use the
EXPLAIN PLAN statement to understand how the database executes a query and identify
potential bottlenecks. Make use of appropriate indexes and ensure that queries are written in a
way that takes advantage of indexing.
2. Indexing: Properly design and maintain indexes on tables. Indexes can significantly speed up
data retrieval operations, but having too many indexes or poorly designed indexes can also slow
down insert/update/delete operations. Regularly monitor and rebuild/reorganize indexes for
optimal performance.
3. Table Partitioning: For large tables, consider partitioning the data to improve query
performance and manageability. Partitioning involves dividing a table into smaller, more
manageable pieces based on a specified criterion (such as range, list, or hash).
4. Statistics Gathering: Regularly collect and update statistics for database objects using the
DBMS_STATS package. Accurate statistics help the query optimizer make informed decisions
about query execution plans.
5. Memory Configuration: Tune the database's memory settings, including the size of the buffer
cache, shared pool, and PGA (Program Global Area). Proper memory configuration can
significantly impact query response times.

Performance Planning
Performance planning in Oracle involves a systematic approach to designing, configuring, and
maintaining your Oracle database environment to ensure optimal performance. Remember that
performance planning is an ongoing process. Regularly review and adjust your performance
strategies as your application evolves and user requirements change.
Steps and considerations for performance planning in Oracle:
1. Requirement Analysis: Understand the performance requirements of your application.
Determine factors such as the expected number of users, transaction volumes, response times,
and peak usage periods. These requirements will guide your performance planning efforts.
2. Database Design: Design your database schema, tables, and relationships to minimize data
redundancy, support efficient queries, and optimize data retrieval and manipulation. Normalize
the database where appropriate and denormalize for performance gains in specific scenarios.
3. Hardware Selection: Choose appropriate hardware based on your performance requirements.
Consider factors like CPU speed, memory capacity, disk performance (I/O throughput and
latency), and network bandwidth. Ensure that the hardware can handle the anticipated
workload.
4. Storage Configuration: Configure storage subsystems for optimal performance. Utilize
technologies such as RAID (Redundant Array of Independent Disks) for data redundancy and
performance improvement. Separate data, indexes, and logs onto different physical disks to
prevent I/O contention.
5. Memory Configuration: Adjust memory settings, including the buffer cache, shared pool, and
PGA (Program Global Area), to meet the needs of your workload. Sufficient memory allocation
can significantly reduce disk I/O and improve query response times.

Instance Tuning
Instance tuning in Oracle involves optimizing various parameters and configurations specific to
an Oracle database instance to enhance its overall performance. An Oracle instance represents
a running Oracle database and its associated memory structures and processes. Instance
tuning is an iterative process that requires continuous monitoring, analysis, and adjustments
based on changing workloads and requirements. Regularly review and refine your instance
tuning strategies to ensure optimal database performance.
Aspects of instance tuning:
1. Memory Configuration:
• SGA (System Global Area): Configure the size of the SGA components, such as the buffer
cache, shared pool, and large pool, to efficiently manage data and provide adequate memory
for caching frequently accessed data and SQL execution.
• PGA (Program Global Area): Configure the PGA memory allocation to accommodate
sorting, hash joins, and other memory-intensive operations performed by individual sessions.
2. Database Parameters:
• Configure Oracle initialization parameters based on your system's hardware, workload, and
usage patterns. Parameters control various aspects of the database behavior, such as memory
allocation, parallelism, and optimizer behavior.
• Common parameters include db_block_size, pga_aggregate_target, sga_target,
optimizer_mode, cursor_sharing, fast_start_parallel_rollback, and more.
3. Optimizing SQL Performance:
• Use the SQL Tuning Advisor to analyze and optimize SQL queries for better execution plans.
• Monitor and tune poorly performing queries using tools like Oracle Enterprise Manager, SQL
Developer, or command-line utilities.
4. Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor
(ADDM):
• Enable AWR snapshots to collect performance statistics over time. Use ADDM to
automatically analyze performance data and provide recommendations for tuning.
Unit 7 Tuning and Moving: 5 hrs.
7.1 Tuning:
SQL Tuning Advisor
The SQL Tuning Advisor is a tool in Oracle Database that helps you to improve the performance
of your SQL statements. It does this by analyzing your SQL statements and recommending
changes that can be made to improve their performance. The SQL Tuning Advisor can
recommend changes to the SQL statements themselves, such as using different indexes or
joining tables in a different order. It can also recommend changes to the database configuration,
such as increasing the number of parallel execution servers.
The SQL Tuning Advisor is a powerful tool that can help you to significantly improve the
performance of your database without having to manually tune your SQL statements. However,
it is important to note that the SQL Tuning Advisor is not a magic bullet. It is still important to
understand how your SQL statements work and to make sure that the recommendations that
the SQL Tuning Advisor makes are appropriate for your database environment.
Benefits of using the SQL Tuning Advisor:
• It can help you to improve the performance of your SQL statements without having to manually
tune them.
• It can identify performance bottlenecks in your SQL statements.
• It can recommend changes to the SQL statements themselves, the database configuration, or
both.
• It can rank the recommendations by estimated performance improvement, so you can quickly
identify the recommendations that are most likely to benefit from implementation.
• It can generate reports that you can use to track the progress of the tuning process and the
impact of the recommendations that have been implemented.
Limitations of the SQL Tuning Advisor:
• It is not always able to identify the best possible performance improvement for a given SQL
statement.
• It can sometimes make recommendations that are not appropriate for your database
environment.
• It can be time-consuming to run the SQL Tuning Advisor, especially for large SQL statements.
• It can be difficult to understand the recommendations that the SQL Tuning Advisor makes,
especially for complex SQL statements.

Overview of SQL Loader

SQLLoader is a utility provided by Oracle that allows you to efficiently load large volumes of
data from external files into Oracle databases. It's commonly used for populating tables with
data from flat files, such as CSV files, and is particularly useful when dealing with substantial
amounts of data. SQLLoader operates through command-line interfaces and allows for custom
data transformation during the loading process.
SQL*Loader is invoked from the command-line interface with a command similar to the
following:
sqlldr username/password@database control=control_file.ctl log=log_file.log
A simple example of how to load data using SQL*Loader:
Assume you have a data file named [Link] containing employee data with columns:
emp_id, first_name, last_name, and salary.
Create a control file named employee_load.ctl with the following content:
LOAD DATA INFILE '[Link]' APPEND INTO TABLE employees FIELDS TERMINATED
BY ','
OPTIONALLY ENCLOSED BY '"' (emp_id, first_name, last_name, salary)
Then, open a command prompt or terminal and run the SQL*Loader command:
sqlldr hr/hr@mydb control=employee_load.ctl

Data Pump API

Oracle Data Pump primarily operates through command-line utilities (expdp for export and
impdp for import). While these utilities provide powerful and flexible ways to perform data
movement tasks, Oracle also provides the DBMS_DATAPUMP PL/SQL package, which can be
considered an API for Data Pump operations.
The DBMS_DATAPUMP package allows you to programmatically control Data Pump operations
using PL/SQL procedures and functions.
Some of the key procedures and functions available in the DBMS_DATAPUMP package
include:
1. OPEN: Opens a Data Pump job and specifies the operation type (EXPORT or IMPORT) and
the job
name.
2. ADD_FILE: Adds a file (dump file, log file, or SQL file) to the Data Pump job.
3. ADD_DIRECTORY: Specifies the directory object to be used for Data Pump files.
4. METADATA_FILTER: Specifies metadata filters to control what objects are included in the
export
or import job.
5. START_JOB: Initiates the Data Pump job and starts the data movement process.
6. ATTACH: Attaches a Data Pump job that was started in a different session.
7. STATUS: Retrieves the status of the Data Pump job.
8. DETACH: Detaches a Data Pump job from the current session.
9. CLOSE: Closes the Data Pump job.

Unit 8 Managing Resource and Scheduling Task: 4 hrs.

8.1 Managing Resource:
Overview of the Database Resource Manager
The Database Resource Manager (DBRM) is a feature in Oracle Database that allows database
administrators to manage and allocate system resources to different users, applications, and
services within the database environment. Its primary goal is to ensure optimal utilization of
available resources while maintaining performance and fairness for all users and applications.
Key features and functionalities of the Database Resource Manager include:
1. Resource Allocation: DBRM enables administrators to allocate CPU and memory resources
to different consumer groups. A consumer group is a logical entity that represents a specific
group of users, applications, or services.
2. Resource Plans: Resource plans define the allocation of resources to consumer groups.
Each resource plan consists of allocation settings for CPU, memory, and other system
resources. By creating multiple resource plans, administrators can prioritize resource allocation
based on business requirements.
3. Resource Limits: Within a resource plan, administrators can set hard and soft limits for
resource usage. Hard limits ensure that a consumer group does not exceed a specified
resource allocation, while soft limits provide more flexibility, allowing a consumer group to use
additional resources if they are available.
4. Plan Directives: Plan directives are rules that define how the resource manager should act in
different scenarios. They include settings for things like switching between resource plans
based on database load or blocking sessions when resource usage exceeds certain thresholds.
5. Consumer Group Mapping: Users and applications are mapped to specific consumer groups
based on criteria such as usernames, application attributes, or SQL queries. This mapping
helps ensure that each user or application receives the appropriate level of resources.
6. I/O Resource Management: In addition to CPU and memory, DBRM can also manage I/O
resources. It allows administrators to control the I/O resources consumed by different consumer
groups, preventing resource contention and ensuring predictable performance.
7. Active Session Pooling: DBRM can dynamically adjust the number of active sessions in a
consumer group based on the current workload. This helps prevent overloading the database
with too many active sessions.
8. Performance Monitoring and Reporting: The resource manager provides various performance
metrics and reports to help administrators monitor resource utilization, plan effectiveness, and
any potential bottlenecks.
9. Automatic Consumer Group Switching: Based on the configuration of plan directives, the
resource manager can automatically switch sessions between different consumer groups to
ensure optimal resource utilization.
10. Maintenance Windows: Administrators can configure maintenance windows during which
specific resource plans are in effect. This allows for more aggressive resource allocation during
off-peak hours and more conservative allocation during peak usage times.

Use the Resource Manager

Using the Oracle Database Resource Manager involves several steps to set up and configure
resource plans, consumer groups, and directives.
Here's a simplified guide on how to use the Resource Manager:
1. Plan Creation: Start by creating resource plans that define how resources will be allocated to
different consumer groups.
2. Consumer Group Definition: Define consumer groups and map them to specific users,
applications, or services. Consumer groups represent the entities that will use the database
resources. For instance, you might have consumer groups like "Online Sales," "Reporting," and
"Batch Processing."
3. Plan Directives: Create plan directives that determine how the Resource Manager behaves in
different scenarios. These directives include conditions for switching between plans and actions
to take when resource consumption exceeds certain thresholds.
4. Testing and Tuning: Test the Resource Manager's behavior and resource allocation by
simulating different workloads. Adjust plan settings and directives as needed to achieve the
desired balance between resource utilization, performance, and fairness.
5. Maintenance Windows: Configure maintenance windows to control when specific resource
plans are in effect. During off-peak hours, you might allocate more resources to critical tasks,
and during peak usage times, you could limit resource consumption to ensure fair distribution.

Here's a simplified example of how you might configure the Resource Manager:
• Step 1: Create resource plans for "High Priority," "Medium Priority," and "Low Priority."
• Step 2: Define consumer groups like "Online Sales," "Reporting," and "Batch Processing."
• Step 3: Set up plan directives to switch to "High Priority" plan when CPU usage exceeds a
certain threshold.
• Step 4: Map user accounts to appropriate consumer groups based on their roles (e.g., online
salespeople are in the "Online Sales" group).
• Step 5: Enable the Resource Manager and start monitoring resource utilization and
performance.
• Step 6: Test different workloads and tune plan settings for optimal performance.
• Step 7: Configure maintenance windows for different resource plans based on time of day.
• Step 8: Regularly monitor and adjust the Resource Manager's settings as workload patterns
change.

Create Resource Plan

Creating a resource plan in Oracle Database involves defining how resources will be allocated
to different consumer groups.
Here's a general outline of the steps you would take to create a resource plan:
1. Connect to the Database: Use a database client tool or command-line interface to connect to
the Oracle Database instance with appropriate administrative privileges.
2. Create Consumer Groups: Define the consumer groups that represent different entities using
the database resources. For example:
CREATE CONSUMER GROUP online_sales, reporting, batch_processing;

3. Create Resource Plan:

CREATE RESOURCE PLAN my_resource_plan; ALTER SYSTEM SET
RESOURCE_MANAGER_PLAN = 'my_resource_plan';
4. Allocate Resources:
BEGIN DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'my_resource_plan',
group_or_subplan => 'online_sales', comment => 'Allocate 50% CPU and 60% memory',
cpu_p1 => 50, memory_p1 => 60 );
DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'my_resource_plan',
group_or_subplan => 'reporting', comment => 'Allocate 30% CPU and 30% memory', cpu_p1 =>
30, memory_p1 => 30 );
5. Set Up Plan Directives:
BEGIN DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'my_resource_plan',
group_or_subplan => 'online_sales', comment => 'Switch to high-priority plan when CPU
exceeds 70%', switch_group => 'high_priority', switch_time => 60, switch_estimate => false,
max_estimate_pct => 70 );
6. Activate the Resource Plan: Activate the resource plan to start enforcing the resource
allocations and plan directives:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'my_resource_plan';
7. Testing and Monitoring: Monitor the resource usage, performance, and the behavior of the
Resource Manager. Test different workloads to ensure the plan works as expected.
8. Adjust and Fine-Tune: Based on monitoring and testing results, fine-tune the resource plan
settings, allocations, and directives as needed to achieve the desired performance and resource
utilization balance.

Create Resource Plan Directives

Creating Resource Plan Directives in Oracle Database involves defining rules for resource
allocation within a Resource Manager Plan. These directives specify how resources such as
CPU and memory should be distributed among different consumer groups during various
database operations. Here's a general outline of
the steps to create Resource Plan Directives:
1. Connect to the Database: Log in to your Oracle Database using a tool such as SQL*Plus or
SQL Developer with appropriate administrative privileges.
2. Create Consumer Groups: Consumer Groups are logical groups to which database sessions
can be assigned. You can create Consumer Groups using the following SQL command:
CREATE CONSUMER GROUP group_name;
3. Create Resource Plan: A Resource Plan is a named collection of Resource Plan Directives
that define how resources should be allocated among the consumer groups. You can create a
Resource Plan like this:
CREATE RESOURCE PLAN plan_name;
4. Create Resource Plan Directives: Resource Plan Directives are rules that specify how much
of a resource (e.g., CPU, memory) each Consumer Group is allowed to use. You can create
Resource Plan Directives using the following SQL command:
BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( plan => 'plan_name',
group_or_subplan => 'consumer_group_name', comment => 'directive_comment',
active_sessions =>max_sessions, parallel_degree_limit => max_parallel_degree );
END;

5. Enable the Resource Plan: Once you have created the Resource Plan and its directives, you
need to activate it:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END; /
This will make your Resource Plan active and ready for use.
6. Assign Sessions to Consumer Groups: To take advantage of the Resource Plan and its
directives, you need to assign database sessions to specific Consumer Groups. You can do this
using the ALTER SESSION command or by setting up automatic mapping rules.

Allocate Resource for Resource Plans

Allocating resources for resource plans involves assigning available resources to specific tasks
or projects based on their requirements and priorities. The process aims to optimize resource
utilization, ensure tasks are completed on time, and manage overall project efficiency. Here's a
general guide on how to allocate resources for resource plans:
1. Collect Resource Requirements: Gather detailed information about the tasks or projects that
require resources. Identify the type and quantity of resources needed for each task, along with
their duration and priority.
2. Identify Available Resources: Create a list of all available resources, including personnel,
equipment, facilities, and materials. Include their availability, skill sets, and capacity.
3. Prioritize Tasks: Determine the priority of each task or project based on factors such as
deadlines, strategic importance, and overall project goals. This will help you allocate resources
more effectively.
4. Resource Matching: Match the requirements of each task with the appropriate resources.
Consider factors such as skill compatibility, availability, and capacity. For example, assign tasks
to team members who possess the required skills and have available time.
5. Resource Allocation Constraints: Take into account any resource allocation constraints, such
as limited availability of certain resources or equipment. Avoid over-allocating or double-booking
resources, as this can lead to delays and inefficiencies.
6. Resource Load Balancing: Ensure an equitable distribution of workload among resources.
Avoid overloading certain individuals or resources, as this can lead to burnout and decreased
productivity.
7. Use Resource Management Tools: Utilize project management or resource management
software to help streamline the allocation process. These tools can provide insights into
resource availability, task dependencies, and potential conflicts.
8. Monitor and Adjust: Regularly monitor the progress of tasks and the utilization of resources. If
changes in priorities, requirements, or resource availability occur, be prepared to adjust the
allocation accordingly.
9. Communication and Collaboration: Maintain open communication with team members about
resource allocation decisions. Ensure everyone is aware of their responsibilities and the overall
resource plan.
10. Review and Learn: After the completion of tasks or projects, review the resource allocation
process. Identify areas of improvement, lessons learned, and best practices for future resource
planning.

Scheduling Task:
Introduction to the scheduler
The Oracle Scheduler is part of the Oracle Database Management System and provides a
robust solution for automating routine tasks and managing resources [Link]
Scheduler provides a powerful toolset for automating tasks, optimizing resource utilization, and
streamlining database maintenance.
Access Rights
In Oracle Database, access rights are permissions or privileges that determine what actions
users or roles can perform on various database objects. These access rights are essential for
maintaining the security, integrity,and confidentiality of the data stored in the database. Oracle
provides a robust and fine-grained privilege system that allows you to control who can perform
specific operations on objects like tables, views,procedures, and more.

Scheduler Components and Workflow

The Oracle Scheduler is a powerful tool within Oracle Database that enables you to automate
and manage various tasks and processes. It consists of several key components and supports
complex workflows to help you streamline your database management and administration.
Here's an overview of the main scheduler components and how they fit into the
workflow:
1. Jobs: A job is a specific task or set of tasks that you want to automate and schedule for
[Link] can be of different types, such as SQL jobs, PL/SQL jobs, and external jobs.
Each job is associated with a program that defines the job's execution details.
2. Programs: A program specifies how a job is executed, including the job type (SQL, PL/SQL,
external), the credentials to be used, and any additional parameters required for the job. A
program is linked to one or more jobs.
3. Schedules: A schedule determines when a job is executed. Oracle Scheduler provides
various types of schedules, such as simple schedules (one-time or recurring executions),
complex schedules (more intricate time-based patterns), and event-based schedules (triggered
by events like database startup).
4. Windows: A window is a time interval during which a job can be executed. Windows can help
manage resource utilization by restricting when certain jobs are allowed to run.
5. Chains: A chain is a sequence of jobs that are linked together to form a workflow. Each job in
a chain is triggered by the completion of the previous job. Chains allow you to create complex
processes involving multiple steps, dependencies, and error handling.
6. Credentials: Credentials define the user and password that a job uses to authenticate when
connecting to the database or external systems. This helps ensure secure and controlled
access to resources.
7. Notifications: Oracle Scheduler offers notification features that allow you to receive alerts and
notifications based on job status, enabling you to stay informed about the execution progress.
8. Logging and Monitoring: Oracle Scheduler provides comprehensive logging and monitoring
capabilities. You can view logs, job history, and metrics to track the execution status and
performance of jobs.
Here's a simplified workflow of how these components fit together in the Oracle
Scheduler:
1. Define Jobs and Programs: Create specific jobs to automate tasks, such as running SQL
scripts or executing PL/SQL procedures. Define corresponding programs that determine the
execution details for each job.
2. Create Schedules: Establish schedules for when jobs should run. This could be a one-time
execution, recurring intervals, or event-based triggers.
3. Set Up Windows: If needed, configure windows to restrict job execution within specific time
intervals. This can help manage resource usage during peak and off-peak hours.
4. Create Chains: For more complex processes, create chains by linking jobs together. Define
dependencies, conditions, and error handling logic.
5. Grant Privileges: Ensure that users and roles have the necessary privileges to create, modify,
and manage jobs and related objects.
6. Execute and Monitor: Jobs are automatically executed based on their schedules. Monitor job
execution through logs, history, and notifications. Handle any exceptions or errors as needed.
Oracle Scheduler provides a comprehensive framework for automating routine tasks, managing
resource utilization, and orchestrating complex workflows within your Oracle Database
environment.
Create a Job
Creating a simple SQL job using Oracle Scheduler. Here's an example of how to create a basic
job that runs a SQL query:
-- Step 1: Create a Program
BEGIN DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'MY_SQL_PROGRAM',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN EXECUTE IMMEDIATE ''SELECT COUNT(*) FROM employees'';
END;',
enabled => TRUE
);
END;
-- Step 2: Create a Schedule
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'MY_DAILY_SCHEDULE',
repeat_interval => 'FREQ=DAILY;
BYHOUR=0;
BYMINUTE=0;
BYSECOND=0',
enabled => TRUE
);
END;
Step 3: Create a Job
BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MY_SQL_JOB',
program_name => 'MY_SQL_PROGRAM',
schedule_name => 'MY_DAILY_SCHEDULE',
enabled => TRUE );
END;

Job Classes
In Oracle Scheduler, a job class is a mechanism that allows you to group and categorize jobs
with similar resource requirements. Job classes help you manage and control the allocation of
resources, such as CPU and memory, for different types of jobs. By assigning jobs to specific
job classes, you can ensure that resource-intensive jobs don't overwhelm the system and that
critical jobs receive the necessary resources.
Here's an overview of job classes and how they work in Oracle Scheduler:
1. Creating a Job Class:
To create a job class, you use the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure.
You specify the job class name, the service attributes (CPU and memory allocations), and other
optional settings.
BEGIN DBMS_SCHEDULER.CREATE_JOB_CLASS(
job_class_name => 'HIGH_PRIORITY_CLASS',
service_attributes => 'cpu_count=2,
memory_size=256M',
enabled => TRUE );
END;
In this example, a job class named HIGH_PRIORITY_CLASS is created with specific CPU and
memory allocations.
2. Assigning Jobs to Job Classes: After creating a job class, you can assign jobs to that class
during job creation using the job_class parameter in the DBMS_SCHEDULER.CREATE_JOB
procedure.
BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MY_CPU_INTENSIVE_JOB',
program_name => 'MY_PROGRAM',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL;
END;',
job_class => 'HIGH_PRIORITY_CLASS', enabled => TRUE );
END;
In this example, the job named MY_CPU_INTENSIVE_JOB is assigned to the
HIGH_PRIORITY_CLASS job class.
3. Managing Resources: The job class's service_attributes parameter defines the resources
allocated to jobs in that class. You can specify CPU count, memory size, and other attributes.
Oracle Database Scheduler will then manage the allocation of resources to jobs in each class
based on the defined attributes.
4. Using Job Classes for Resource Management: Job classes help you prioritize and manage
resource allocation. For instance, you can ensure that critical jobs get the necessary resources,
while less critical jobs are limited in their resource usage. This prevents resource contention and
ensures smooth operation of the database.
5. Dynamic Consumer Groups: Job classes can also be used in conjunction with Oracle
Database Resource Manager's dynamic consumer groups to further manage resources.
Dynamic consumer groups automatically categorize sessions based on attributes like the job
class, allowing you to control resource allocation on a per-session basis. Job classes are a
powerful feature in Oracle Scheduler, enabling you to efficiently manage and allocate
resources to different types of jobs based on their priority and resource requirements.

Use Time Based, Event based schedule

Examples of how to use both time-based and event-based schedules in Oracle Scheduler.
1. Time-Based Schedule:
A time-based schedule triggers jobs to run at specific times or intervals.
Here's an example of how to create a time-based schedule and associate it with a job:
-- Create a Time-Based Schedule
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'DAILY_SCHEDULE',
repeat_interval => 'FREQ=DAILY;
BYHOUR=1;
BYMINUTE=0;
Compiled and prepared By Hari Bhakta Acharya
Database Administration BIT, TU, Nepal
BYSECOND=0',
enabled => TRUE
);
END;
-- Create a Job and Associate the Time-Based Schedule
BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name => 'DAILY_REPORT_JOB',
program_name => 'REPORT_PROGRAM',
schedule_name => 'DAILY_SCHEDULE',
enabled => TRUE );
END;
2. Event-Based Schedule:
An event-based schedule triggers jobs based on specific events occurring within the database.
Here's an example of how to create an event-based schedule and associate it with a job:
-- Create an Event-Based Schedule
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'STARTUP_SCHEDULE',
start_date => SYSTIMESTAMP,
event_condition => 'UPON STARTUP',
enabled => TRUE );
END;
-- Create a Job and Associate the Event-Based Schedule
BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name => 'STARTUP_JOB',
program_name => 'STARTUP_PROGRAM',
schedule_name => 'STARTUP_SCHEDULE',
enabled => TRUE );
END;

Both time-based and event-based schedules provide flexible ways to automate job execution in
Oracle Scheduler based on specific time intervals or database events.

Create an Event Based Schedule

Creating an event-based schedule in Oracle Scheduler involves defining a schedule that is
triggered by a specific event. Here's an example of how to create an event-based schedule that
triggers a job when a custom event is raised:
Create an Event-Based Schedule: Now you can create an event-based schedule that triggers a
job when an
event is raised.
-- Create the Event-Based Schedule
BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'EVENT_BASED_SCHEDULE',
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data = ''MY_EVENT''',
queue_spec => 'my_event_queue',
enabled => TRUE );
END;
-- Create a Job and Associate the Event-Based Schedule
BEGIN DBMS_SCHEDULER.CREATE_JOB(
job_name => 'EVENT_JOB',
program_name => 'EVENT_PROGRAM',
schedule_name => 'EVENT_BASED_SCHEDULE',
enabled => TRUE );
END;

Unit 9 Space Management and Oracle Support: 4 hrs.

8.1 Managing Space:
Overview of Space Management
Space management in Oracle refers to the management of physical storage space within a
database to ensure efficient utilization of resources, optimize performance, and maintain data
integrity. Oracle employs various mechanisms and techniques to manage space efficiently,
including features like tablespaces, datafiles, segments, extents, and blocks.
Here's an overview of some key concepts in Oracle space management:
1. Tablespaces and Datafiles:
• A tablespace is a logical storage unit that contains one or more datafiles. It provides a way to
group related objects (tables, indexes, etc.) together for management and administration
purposes.
• A datafile is a physical file on the operating system that stores the actual data of the objects
within a tablespace.
2. Segments:
• A segment is a database object that stores data (e.g., tables, indexes, clusters, partitions).
Each segment consists of one or more extents.
• There are different types of segments, such as tables, indexes, clusters, and partitions. Each
the type of segment manages its space differently based on its nature.
3. Extents and Blocks:
• An extent is a contiguous set of data blocks. Extents are allocated to segments as needed.
• A data block is the smallest unit of data storage in Oracle. It corresponds to a fixed-size
portion of a datafile.
4. Automatic Segment Space Management (ASSM):
• Oracle offers two types of segment space management: manual and automatic. Automatic
Segment Space Management (ASSM) is a feature that simplifies space management by
automatically managing free space within segments.
• ASSM uses bitmaps to track free and used space within segments, allowing for more efficient
space utilization.
5. Free Space and PCTUSED:
• The PCTUSED parameter specifies the percentage of a block that must be used before the
block is considered "used." This parameter is used for managing space within individual
blocks.
6. Table Compression:
• Oracle offers table compression, which reduces the amount of physical storage required by
storing data more efficiently. Compression can be both in-row (within the block) and out-of-
row (storing portions of data elsewhere).
7. Table and Index Partitioning:
• Partitioning involves dividing large tables and indexes into smaller, more manageable pieces
called partitions. Each partition is stored separately and can be managed more effectively.

Block Space Management with Segments

In Oracle databases, block space management is closely tied to segment management.
Segments are the fundamental storage structures that contain data and are made up of one or
more blocks. Here's how block space management works in the context of segments:
1. Segments and Extents:
• A segment is a logical storage structure that represents a database object, such as a table,
index, or partition. Each segment is made up of one or more extents.
• An extent is a contiguous set of data blocks. Extents are the units of space allocation and
deallocation within a segment.
2. Free Space Management:
• When you insert data into a segment (e.g., insert records into a table), Oracle needs to
allocate space to accommodate the new data. This is done by allocating new extents to the
segment.
• Oracle uses a free space management mechanism to keep track of which blocks within a
segments are available for data storage. This mechanism helps avoid fragmentation and
ensures efficient use of space.
3. Extent Allocation:
• As data is inserted into a segment, Oracle initially allocates one or more extents to the
segment. Each extent contains a number of contiguous data blocks.
• Extents can be allocated using two primary methods: dictionary-managed and locally-
managed.
4. Dictionary-Managed Extents:
• In dictionary-managed tablespaces, the database maintains a data dictionary containing
information about free and allocated space in each segment.
• When an extent needs to be allocated, the database searches the dictionary for available
space and assigns the extent.
5. Locally-Managed Extents:
• Locally-managed tablespaces use a bitmap-based approach to manage free space within
segments. This approach is more efficient than dictionary-managed tablespaces.
• Each extent in a locally-managed tablespace has a bitmap that represents the status (free or
used) of each block in the extent. This bitmap-based approach reduces contention and
simplifies space management.
6. Automatic Segment Space Management (ASSM):
• ASSM is a feature that simplifies space management by automatically managing free space
within segments, especially in tables with high insert and delete activity.
• ASSM uses bitmaps to track free and used space within segments. This reduces the need for
constant maintenance and avoids issues like "free list contention" seen in earlier versions of
Oracle.

Segments Types
In Oracle databases, segments are the fundamental storage structures that hold data. There are
several types of segments, each serving a specific purpose in storing and managing different
types of database objects.
Here are some of the main segment types:
1. Table Segment:
• A table segment stores the data of a table. It consists of one or more extents that contain the
actual rows of the table.
• Tables can be partitioned into multiple segments based on certain criteria, improving
manageability and performance.
2. Index Segment:
• An index segment stores the data that makes up an index on a table. Indexes are used to
improve the efficiency of data retrieval operations by providing fast access paths to table
data.
• Like tables, indexes can also be partitioned for better performance and management.
3. Cluster Segment:
• A cluster segment stores data for a clustered table. A clustered table is a table that shares the
same data blocks with one or more other tables, often based on a common key.
• Clustered tables and indexes are less common in modern Oracle database designs due to
improvements in other indexing techniques.
4. Bitmap Segment:
• Bitmap segments are used in bitmap indexes, which are used to efficiently store and manage
boolean or multi-valued data.
• Bitmap indexes use bitmap segments to store the index information.
5. Temporary Segment:
• Temporary segments are used for storing temporary data during query processing, sorting,
and other operations that require temporary workspace.
• Temporary segments are often used for intermediate results in complex queries.
6. LOB Segment (Large Object Segment):
• LOB segments are used to store large objects such as text, images, audio, and video files.
• LOBs can be stored inline with the rest of the table data or in separate LOB segments

Allocate Extends
In Oracle databases, the allocation of extents is a crucial part of managing storage space for
segments (tables, indexes, etc.). Extents are contiguous blocks of storage allocated to
segments, and managing their allocation helps ensure efficient data storage and retrieval. There
are different methods for allocating extents, and these methods can impact the overall
performance and organization of your database.
Here's how you can allocate extents in Oracle:
1. Automatic Segment Space Management (ASSM):
• ASSM is a feature that simplifies space management by automatically managing free space
within segments. When using ASSM, Oracle manages the allocation of extents and tracks
free space using bitmaps within each extent.
• ASSM is recommended for tablespaces that experience high levels of data insertion and
deletion activity, as it helps to reduce space fragmentation.
2. Manual Segment Space Management:
• In tablespaces with manual segment space management, you have more control over how
extents are allocated. You can specify the initial and next extent sizes for segments.
• However, manual space management may lead to free space fragmentation and requires
more manual intervention to optimize space usage.
3. Extent Management Parameters:
• When you create a tablespace, you can specify the type of extent management you want to
use: "AUTOALLOCATE" or "MANUAL." With AUTOALLOCATE, Oracle manages
extent sizes automatically based on segment requirements.
• When using MANUAL extent management, you need to specify the initial and next extent
sizes explicitly when creating segments.
4. Initial and Next Extent Sizes:
• The initial extent size is the size of the first extent allocated to a segment when it is created.
• The next extent size is the size of subsequent extents allocated to a segment as needed.
• Proper sizing these extent sizes can impact performance and space utilization. Larger initial
extents can reduce fragmentation, but they might waste space if segments don't grow that
large.
5. Monitoring and Managing Extent Allocation:
• Oracle provides various tools and views to monitor and manage extent allocation. For
example, the "DBA_SEGMENTS" view provides information about the segments and their
allocated extents.
• Regularly monitoring the usage of extents and tablespaces can help you identify potential
space issues and take corrective actions.

Allocate Space
Allocating space in Oracle databases involves managing the storage of data within database
objects like tables, indexes, and tablespaces. Oracle offers various methods for managing
space, and the choice of method depends on the nature of your application, the size of your
data, and your performance requirements.
Here's a general outline of how space allocation works in Oracle:
1. Tablespaces: A tablespace is a logical storage container that holds database objects like
tables,indexes, and other structures. You need to decide which tablespaces to use for different
types of objects.
2. Segments: Database objects like tables and indexes are stored as segments within
tablespaces. Segments can be further divided into extents.
3. Extent: An extent is a contiguous set of database blocks used to store a specific database
object. When an object needs more space, Oracle allocates additional extents.
4. Automatic Segment Space Management (ASSM): This is a method of space management
where Oracle automatically manages free space within segments using bitmaps. It helps
prevent fragmentation and simplifies space management.
5. Manual Segment Space Management: This is an older method where you manage space
manually by specifying the initial and next extents for segments. It requires more administrative
effort but might be used for specific cases.

Row chaining and Migration

Row chaining and row migration are concepts related to the physical storage of data in Oracle
databases. These concepts come into play when a single row's data cannot fit within a single
database block.
1. Row Chaining: Row chaining occurs when a single row's data is too large to fit into a single
database block. As a result, the data is split across multiple blocks. In this situation, the Oracle
Database stores part of the row in the original block (referred to as the "parent" block) and the
remaining part in one or more additional blocks (referred to as the "chained" blocks).
Row chaining can lead to performance issues because accessing a row that is spread across
multiple blocks requires more I/O operations, which can slow down queries. To address this,
you can consider strategies such as optimizing your data model to avoid excessive row
chaining, using larger block sizes, and reorganizing the table.
2. Row Migration: Row migration also occurs when a row's data cannot fit in a single block, but
it's different from row chaining. In row migration, the entire row is moved to a new block, rather
than splitting the data between multiple blocks. This movement is typically triggered by updates
that increase the size of the row.
Row migration can also lead to performance issues due to the additional I/O required to retrieve
migrated rows. To mitigate migration-related performance problems, you can use techniques
like adjusting the PCTFREE setting (percentage of free space reserved for updates) to reduce
the likelihood of row migration,or periodically reorganizing the table to eliminate migrated rows.

Both row chaining and row migration can impact database performance, so it's important to
monitor and manage them. Properly designing your schema, using appropriate data types, and
considering the potential growth of data can help minimize these issues. Additionally, regular
database maintenance, such as reorganizing tables and indexes, can help address these
challenges and maintain optimal performance.

The Enterprise Manager Support Workbench

Oracle Enterprise Manager Support Workbench was a part of Oracle Enterprise Manager, which
is a comprehensive tool for managing Oracle databases, middleware, and applications. The
Support Workbench is designed to help database administrators (DBAs) and support personnel
manage and diagnose issues related to Oracle databases.
The Enterprise Manager Support Workbench provides various features and tools to assist in
troubleshooting and resolving database issues:
1. Service Requests: It allows you to create and manage service requests directly from within
Enterprise Manager. This streamlines the process of interacting with Oracle Support for
assistance with technical issues.
2. Configuration Management: The Support Workbench can analyze your database
configuration and provide recommendations to ensure your environment aligns with Oracle's
best practices.
3. Diagnostic Tools: It includes diagnostic tools to help identify and diagnose problems. These
tools may include advisors, health checks, and automatic diagnostic tools that analyze
performance, configuration, and other aspects of the database.
4. Patch and Upgrade Recommendations: The Support Workbench can provide information
about available patches and upgrades for your database. This helps you keep your environment
up to date and secure.

Register for Security Updates

Oracle provides security updates and patches for their products, including databases, through
their Critical Patch Updates (CPUs) and Security Alerts. To register for security updates and
stay informed about the latest security patches and vulnerabilities, you can follow these steps:
Work with Oracle Support
Working with Oracle Support involves seeking assistance and guidance from Oracle's technical
support team to resolve issues, obtain solutions, and receive help with Oracle products and
services. Oracle offers various resources and channels for interacting with their support team.
Here's how you can work with Oracle Support:
My Oracle Support (MOS) Account: Create or log in to your My Oracle Support account. This
portal provides access to Oracle's support resources, including knowledge articles, patches,
updates, and the ability to open and manage service requests.
Service Request (SR): If you encounter a technical issue with an Oracle product or service, you
can log a Service Request (SR) through My Oracle Support. Provide details about the issue,
including relevant logs, error messages, and any troubleshooting steps you've taken.
Severity Levels: When opening a Service Request, you'll assign a severity level based on the
impact of the issue on your business operations. Oracle uses these severity levels to prioritize
and respond to your request accordingly.
Support Channels: Oracle offers multiple support channels, including online support, phone
support, and live chat. Choose the channel that best fits your needs and the urgency of the
issue.
Diagnostic Information: To expedite the troubleshooting process, be prepared to provide
detailed information about your environment, configuration, error messages, and any recent
changes that might be related to the issue.
Remote Diagnostic Tools: Oracle may request remote access or diagnostic tools to better
understand the problem. These tools help Oracle support engineers diagnose and resolve
issues more efficiently.
Communication and Updates: Oracle support engineers will communicate with you throughout
the support process. They may ask for additional information, provide troubleshooting steps,
and keep you updated on the progress of the investigation.

Manage Patches
Managing patches in an Oracle environment is crucial for keeping your software up to date,
secure, and optimized. Oracle releases patches to address security vulnerabilities, bugs, and
performance improvements.
Here's how you can effectively manage patches:
1. Identify Patch Requirements: Regularly check Oracle's official websites for Critical Patch
Updates (CPUs) and Security Alerts to identify patches relevant to your Oracle products and
versions.
2. Oracle Support and My Oracle Support (MOS): Log in to your My Oracle Support account
and navigate to the "Patches & Updates" section. Here, you can search for and download
patches, as well as access documentation about the patches.
3. Patch Documentation: Before applying a patch, review the patch documentation, including
the README file. This file provides important information about prerequisites, installation steps,
and known issues.
4. Patch Testing: It's recommended to test patches in a non-production environment before
applying them to your production systems. This helps ensure that the patches won't negatively
impact your applications.
5. Backup: Always backup your database or relevant systems before applying patches. This
allows you to roll back in case of any issues during or after the patching process.
6. Patch Plan: Create a patch plan that outlines the steps you'll take to apply the patches.
Include information about downtime, rollback plans, and any specific configurations needed.