Deerwalk Institute of Technology

(Affiliated to Tribhuvan University)

Database Administration Lab Report

BCA Batch of 2024

Submitted By:
Swastik Pandey
Roll No: 335

Lab Course Designed and Instructed By:

Roshan Kumar Ban

______________ ______________
Internal Examiner External Examiner
LAB 1

1. What is an Oracle instance? Retrieve the details of the current Oracle instance and
provide screenshots as evidence.

An Oracle instance is a combination of the Oracle Database software and the associated
memory structures and background processes that allow the database to function. When an
Oracle database is started, it creates an instance to handle tasks such as managing data
storage, processing SQL queries, and ensuring data consistency. An instance is composed
of two main components: the memory area (specifically the System Global Area, or SGA)
and the set of background processes that work together to provide access to the data stored
in the database.

OUTPUT

2. What are background processes in Oracle? Identify the background processes

running in the current Oracle instance and provide screenshots as evidence.
 Background processes in Oracle are essential components that handle various tasks
required to ensure the smooth operation of the database. These processes run automatically
in the background to manage different aspects of the database, such as data storage,
recovery, and user interaction, without requiring direct user involvement. They help
maintain the database’s performance, availability, and consistency.

OUTPUT

3. What is a session in Oracle? How to monitor sessions currently connected to the

Oracle instance?

In Oracle, a session refers to the connection established between a user or application and
the Oracle database. It is created when a user successfully logs in to the database and is
responsible for managing the interaction between the user and the database server. A
session allows users to execute SQL queries, perform transactions, and interact with the
data stored in the database.

Each session is assigned a unique session identifier and is associated with specific
resources such as memory, processes, and database objects. These resources are used to
process SQL commands and maintain the state of the session throughout its lifetime. A
session remains active until the user disconnects, either by logging out or by terminating
the session explicitly. Sessions are important for managing user-specific settings, tracking
user activity, and ensuring that database operations are isolated and secure.
 OUTPUT

4. How do you start database instance and mount it?

Starting and mounting a database is depicted in the image below:

5. How do you shut down an oracle database instance immediately?

Shutting down an oracle database instance immediately is depicted in the image below:
LAB 2

1. Write a short note on Tablespace. What is the process of creating a Tablespace with
a 100MB datafile?

A tablespace in Oracle is a logical storage unit used to group related logical structures, such
as tables and indexes, into physical storage locations. It provides a way to manage and
organize data within the database by dividing it into smaller, more manageable segments.
A tablespace is made up of one or more data files, which are physical files stored on disk.
These data files hold the actual data for the objects within the tablespace.

The process of creating a tablespace with 100MB datafile is depicted in the image below:

2. What is the process of verifying the tablespace size and available free space in Oracle?

The process of verifying the tablespace size and available free space in Oracle is depicted
in the image below:
3. What is the process of increasing the size of an existing tablespace in Oracle?

The process of increasing the size of an existing tablespace in Oracle is depicted in the
image below:

4. What is the process of listing the datafiles of a specific tablespace in Oracle?

The process of listing the datafiles of a specific tablespace in Oracle is depicted in the
image below:

5. What is the process of converting a tablespace to a read-only state in Oracle?

The process of converting a tablespace to a read-only state in Oracle is depicted in the

image below:

6. Explain the role of the DROP command in Oracle. What is the process of dropping a
tablespace along with its datafiles?

The DROP command in Oracle is used to permanently delete database objects such as
tables, views, indexes, sequences, or even entire schemas. When an object is dropped, it is
completely removed from the database, and its structure and data cannot be recovered
unless a backup exists.

For example, when a table is dropped using the DROP TABLE command, all its data,
indexes, and constraints are also removed. Unlike the DELETE or TRUNCATE
commands, which only remove data while keeping the table structure, DROP eliminates
the table entirely.

The process of dropping a tablespace along with its datafiles is depicted in the image below:
LAB 3

1. Explain how access control is managed in an Oracle database. Demonstrate the

process of creating a user in the current Container Database (CDB) and assigning a
quota.

Access control in an Oracle database is managed through users, roles, and privileges to
ensure data security and restrict unauthorized access. Each user in Oracle is assigned a
unique username and password, and their access to database objects is controlled by
privileges.

There are two types of privileges: system privileges and object privileges. System
privileges allow users to perform administrative tasks, such as creating tables or users,
while object privileges control access to specific database objects, like tables or views.
Privileges can be granted using the GRANT command and revoked using the REVOKE
command.

Oracle also uses roles to simplify privilege management. A role is a collection of privileges
that can be assigned to multiple users, making it easier to manage permissions for groups
of users rather than assigning privileges individually.

The process of creating a user in the current CDB as assigning a quota is depicted in the
image below:

2. Explain how normal users are defined in an Oracle database. Demonstrate the
process of creating a user in the current Pluggable Databases (PDBs) and assigning a
quota.
 In an Oracle database, normal users are defined as database accounts that have limited
privileges and are primarily used for accessing and managing data rather than performing
administrative tasks. Each user is created with a unique username and password, which
allows them to connect to the database and interact with its objects based on assigned
permissions.

Normal users are created by a DBA (Database Administrator) using the CREATE USER
command. By default, a new user has no privileges and cannot perform any actions until
they are granted specific system privileges (like creating a session) or object privileges
(like selecting data from a table). Users can also be assigned roles, which group multiple
privileges together for easier management.
These users typically work within their own schemas, where they can create and manage
tables, views, and other database objects if they have the necessary privileges.

The process of creating a user in the current PDB and assigning a quota is depicted in the
image below:

3. What is the process of granting login privileges to a user in Oracle?

The process of granting login privileges to a user in Oracle is depicted in the image below:
4. What is the process of granting the privilege to create a table to a user in Oracle?

The process of granting the privilege to create a table to a user in Oracle is depicted in the
image below:

5. What is the process of creating a role and assigning privileges to the created user in
Oracle?

The process of creating a role and assigning privileges to the created user in Oracle is
depicted in the image below:

6. What is the process of locking a user account for security reasons in Oracle?

The process of locking a user account for security reasons in Oracle is depicted in the image
below:
LAB 4

1. Explain the importance of multitenant architecture in Oracle. Demonstrate the

process of creating a new Pluggable Database (PDB).

The multitenant architecture in Oracle is important because it allows multiple databases,

called pluggable databases (PDBs), to run within a single container database (CDB). This
improves resource efficiency, simplifies database management, and reduces hardware and
maintenance costs.

With multitenant architecture, administrators can manage multiple databases as a single

unit, making tasks like backups, patching, and upgrades easier. Since PDBs share the same
system resources, they provide better performance and scalability compared to running
separate databases.

This architecture also enhances security and isolation, as each PDB operates independently
while benefiting from shared infrastructure. It is especially useful for cloud environments
and large enterprises that need to manage multiple applications efficiently.

The process of creating a new PDB is depicted in the image below:

2. What is the process of switching to a specific Pluggable Database (PDB) in Oracle?

The process of switching to a specific PDB in Oracle is depicted in the image below:
3. What is the process of listing all the available Pluggable Databases (PDBs) in the
current system?

The process of listing all the available PDBs in the current system is depicted in the image
below:
LAB 5

1. Explain the importance of Listener in Oracle. Demonstrate the process of creating a

new Listener.

The Listener in Oracle is important because it acts as a communication gateway between

users and the database. It is a background process that listens for incoming connection
requests and directs them to the appropriate database instance.

Without a Listener, remote users and applications would not be able to connect to the
database. It allows multiple users to access the database simultaneously by handling
connections efficiently. The Listener configuration can be managed using the listener.ora
file and controlled with commands like lsnrctl start and lsnrctl stop.

The Listener also enhances security by allowing administrators to control which users and
systems can connect to the database. It supports features like load balancing and failover,
which help maintain high availability and performance in large database environments.

The process of creating a new listener is depicted in the images below:

LAB 6

1. Explain the importance of database backup and its types. Demonstrate the process of
taking a full backup of the database using RMAN.

Database backup is important because it protects data from loss due to hardware failures,
accidental deletions, or cyberattacks. Without a backup, recovering lost or corrupted data
can be impossible, leading to serious business and operational issues. Regular backups
ensure that data can be restored quickly in case of an emergency.

There are different types of database backups. A full backup copies the entire database,
including all data and system files. An incremental backup saves only the changes made
since the last backup, reducing storage space and backup time. A differential backup stores
changes since the last full backup, making recovery faster than using multiple incremental
backups. Additionally, logical backups export specific tables or schemas, while physical
backups include actual database files.

The process of taking a full backup of the database using RMAN is depicted in the image
below:
2. Demonstrate the process of taking a backup of the control file.

The process of taking a backup of the control file is depicted in the image below:

3. Demonstrate the process of deleting obsolete RMAN backups.

The process of deleting obsolete RMAN backups is depicted in the image below:
LAB 7

1. Explain how scheduler helps to DBA. Demonstrate the process of checking all the
scheduled jobs in the database.

The Scheduler in Oracle helps a DBA by automating repetitive database tasks, reducing
manual effort and improving efficiency. It allows tasks like backups, data imports, and
performance monitoring to run automatically at scheduled times without requiring constant
supervision.

Using the DBMS_SCHEDULER package, a DBA can create jobs that execute SQL
statements, PL/SQL blocks, or external scripts at specified intervals. This helps optimize
database performance by scheduling resource-intensive tasks during off-peak hours,
preventing slowdowns during busy periods.

The process of checking all the scheduled jobs in the database is depicted in the image
below:
2. Demonstrate the process of disabling an existing job.

The process of disabling an existing job is depicted in the image below: