Tribhuvan University

Institute of Science & Technology (IOST)

LAB REPORT
of
Database Administrator
(BIT 352)

Submitted to
Bachelor in Information Technology(BIT)
Department of Science & Technology
Thakur Ram Multiple Campus, Birgunj

Submitted by
Name: - PRAKASH KUMAR GIRI
Symbol No:- 783/078
Year:- 2081
BIT 6th Semester

Under the Supervision of

Mr. Alok Chaurasiya
Faculty (BIT 352)
Table of Contents

Lab no. Title Page

1 Installing and Configuring Oracle 19c Database 3-6

2 Configuring and Managing Tablespace and Control Files (SPfile 7-9

or pfile or redo log file)

3 Managing User Accounts with Roles and Privileges 10-11

4 Creating CDB and PDBs (Container Database and Pluggable 12-13

Databases)

5 Creating Users in CDB and PDB 14-15

6 Oracle Net Configuration Using netca Command 16-17

7 Backup and Recovery Using RMAN Backup 18-19

8 Creation of Event-Based Schedule 20-21

 Lab-1

Title: Installing and Configuring Oracle 19c Database

Objective: The objective of this lab is to install and configure Oracle Database 19c on a local machine.

Equipment/Tools Required:

1. Computer with administrative privileges

2. Internet connection

3. Oracle Database 19c installation files

Procedure:

1. Downloading Oracle Database Software:

• Navigate to the Oracle Database Downloads page

(https://www.oracle.com/database/technologies/).
• Select the appropriate version of Oracle Database 19c for your operating system.
• Click on the download link and accept the license agreement.
• The download should commence. Wait for the download to complete.

2. Extracting Installation Files:

• Once the download is complete, navigate to the directory where the downloaded file is located.
• Extract the installation files from the downloaded archive to a directory on your local machine.

3. Running the Installer:

• Locate the runInstaller script in the extracted directory.

• Open a terminal or command prompt with administrative privileges.
• Change directory to the location where the runInstaller script is located.
• Run the runInstaller script.
• This will start the Oracle Universal Installer (OUI).

4. Oracle Universal Installer (OUI):

• Follow the prompts in the Oracle Universal Installer.

3|Page
 • Choose "Install database software only" if you are setting up a standalone database.
• Provide the necessary details such as the installation directory and Oracle Home.

5. Database Edition and Configuration:

• Select the appropriate edition of Oracle Database (e.g., Enterprise Edition).

• Configure the database by providing details such as the global database name, SID, and
administrative password.

6. Installation Progress:

• Allow the installer to complete the installation process. This may take some time depending on
your system specifications.

7. Configuration Assistant:

• Once the installation is complete, run the Database Configuration Assistant (DBCA) to create and
configure the database.
• Follow the prompts in DBCA to configure the database settings.

8. Listener Configuration:

• Configure the Oracle Listener to enable database connections.

• Specify the listener port and protocol.

9. Completing Installation:

• Once all configurations are done, complete the installation process.

• Verify that the installation was successful.

Results: Oracle Database 19c has been successfully installed and configured on the local machine.

Screenshots:

1. Oracle Database Downloads Page:

4|Page
2. Extracted Installation Files:

3. Oracle Universal Installer (OUI):

5|Page
4. Database Configuration Assistant (DBCA):

5. Installation Complete:

Conclusion: In this lab, we have successfully installed and configured Oracle Database 19c on a local machine.
This database can now be used for various development and testing purposes.

6|Page
 Lab-2

Title: Configuring and Managing Tablespace and Control Files(SPfile or pfile or redo log
file)

Objective: The objective of this lab is to configure and manage tablespaces, control files, and redo log files in
an Oracle Database environment.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. SQL*Plus or SQL Developer tool for executing SQL commands

Procedure:

1. Connecting to the Database:

• Open SQL*Plus or SQL Developer.

• Connect to the Oracle Database using the appropriate credentials.

2. Creating a Tablespace:

• Use the following SQL command to create a new tablespace:

• Replace tablespace_name with the desired name, path_to_datafile with the path to the
datafile, and size_in_MB with the size of the tablespace.

3. Managing Tablespaces:

• List all existing tablespaces:

7|Page
 Alter tablespace attributes (e.g., resizing):

4. Managing Control Files:

• View the current control file locations:

• Add a new control file:

5. Managing Redo Log Files:

• View the current redo log groups and members:

• Add a new redo log group:

Results: Tablespaces, control files, and redo log files have been successfully configured and managed in the
Oracle Database environment.

8|Page
Conclusion: In this lab, we have learned how to configure and manage tablespaces, control files, and redo log
files in an Oracle Database. These components play a crucial role in database administration and ensuring the
integrity and performance of the database.

9|Page
 Lab-3

Title: Managing User Accounts with Roles and Privileges

Objective: The objective of this lab is to learn how to manage user accounts, assign roles, and grant privileges
in an Oracle Database environment.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. SQL*Plus or SQL Developer tool for executing SQL commands

Procedure:
1. Connecting to the Database:

• Open SQL*Plus or SQL Developer.

• Connect to the Oracle Database using the appropriate credentials.

2. Creating User Accounts:

• Use the following SQL command to create a new user account:

• Replace username with the desired username and password with the desired password.
3. Granting Roles to Users:

• Grant a predefined role to the user:

• Replace role_name with the name of the predefined role.

4. Creating Custom Roles:

• Create a custom role with specific privileges:

• Grant privileges to the custom role:

10 | P a g e
 • Replace privilege_name with the desired privilege.
5. Assigning Roles to Users:

• Assign the custom role to the user:

6. Granting Privileges Directly to Users:

• Grant specific privileges directly to the user;

• Replace privilege_name with the desired privilege.

7. Revoking Privileges:

• Revoke previously granted privileges from users or roles:

Results: User accounts have been successfully managed, roles have been assigned, and privileges have been
granted in the Oracle Database environment.

Conclusion: In this lab, we have learned how to manage user accounts by creating users, assigning predefined
and custom roles, and granting privileges in an Oracle Database. Proper management of user accounts and
privileges is crucial for maintaining database security and controlling access to database resources.

11 | P a g e
 Lab-4

Title: Creating CDB and PDBs (Container Database and Pluggable Databases)

Objective: The objective of this lab is to learn how to create a Container Database (CDB) and Pluggable
Databases (PDBs) in an Oracle Database environment.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. SQL*Plus or SQL Developer tool for executing SQL commands

Procedure:

1. Connecting to the Database:

• Open SQL*Plus or SQL Developer.
• Connect to the Oracle Database using the appropriate credentials.

2. Creating a Container Database (CDB):

• Use the following SQL command to create a Container Database:

• Replace cdb_name with the desired name for the Container Database.
3. Creating a Pluggable Database (PDB):
• After creating the CDB, use the following SQL command to create a Pluggable Database:

12 | P a g e
 • Replace pdb_name with the desired name for the Pluggable Database, pdb_admin with the
administrative user, pdb_password with the password for the administrative user, and
/pdb_source_location/ and /pdb_destination_location/ with the source and destination paths for the
PDB files.
4. Opening the PDB:
• Once the PDB is created, use the following SQL command to open the Pluggable Database:

5. Connecting to the PDB:

• Connect to the newly created Pluggable Database using the following SQL command:

6. Managing PDBs:
• List all Pluggable Databases in the Container Database:

• Perform administrative tasks specific to individual PDBs as needed.

Results: A Container Database (CDB) has been successfully created, and Pluggable Databases (PDBs) have
been created within the CDB in the Oracle Database environment.

Conclusion: In this lab, we have learned how to create a Container Database (CDB) and Pluggable Databases
(PDBs) in an Oracle Database environment. CDBs and PDBs provide a flexible and efficient way to manage
multiple databases within a single Oracle instance.

13 | P a g e
 Lab-5

Title: Creating Users in CDB and PDB

Objective: The objective of this lab is to create users in both the Container Database (CDB) named "Prithvii"
and the Pluggable Database (PDB) named "Yadav" in an Oracle Database environment.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. SQL*Plus or SQL Developer tool for executing SQL commands

Procedure:

1. Connecting to the Database:

• Open SQL*Plus or SQL Developer.
• Connect to the Oracle Database using the appropriate credentials for the CDB "Prithvii".

2. Creating Users in CDB :

• Use the following SQL command to create a user in the CDB:

• Replace cdb_user with the desired username and password with the desired password.
3. Granting Privileges to CDB User:
• Grant necessary privileges to the user (e.g., create session, create table) as required:

4. Switching to PDB :
• Switch to the PDB "Yadav" using the following SQL command:

14 | P a g e
 5. Creating Users in PDB:
• Create users in the PDB "Yadav" using the same SQL commands as in the CDB.
• Connect to the PDB using the appropriate credentials:

6. Granting Privileges to PDB User:

• Grant necessary privileges to the user created in the PDB:

7. Verifying Users:
• Verify that the users have been created in both the CDB and the PDB:

Results: Users have been successfully created in both the Container Database (CDB) "Prithvii" and the
Pluggable Database (PDB) "Yadav" in the Oracle Database environment.

Conclusion: In this lab, we have learned how to create users in both a Container Database (CDB) and a
Pluggable Database (PDB) in an Oracle Database environment. Creating users at both levels allows for better
management and control of access to database resources.

15 | P a g e
 Lab-6

Lab Report: Oracle Net Configuration Using netca Command

Objective: The objective of this lab is to configure Oracle Net using the netca command, which is a graphical
user interface (GUI) tool provided by Oracle for configuring Oracle Net Services.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. netca command-line utility

Procedure:

1. Launching netca:
• Open a terminal or command prompt on your computer.

2. Navigating to Oracle Home:

• Navigate to the Oracle Home directory where the Oracle Database software is installed.

3. Running netca:
• Execute the netca command to launch the Oracle Net Configuration Assistant.

4. Oracle Net Configuration Assistant (netca) GUI:

• The Oracle Net Configuration Assistant GUI will appear. Follow the on-screen instructions to configure
Oracle Net Services.
• Click on "Next" to proceed.

5. Select Operation:
• Choose the operation you want to perform. In this case, select "Configure." Click "Next" to continue.

16 | P a g e
 7. Select Listener:
• Choose the listener you want to configure. If there are no listeners configured, you can create a new one.
Select the appropriate option and click "Next."

8. Listener Configuration:
• Provide the necessary information to configure the listener, such as the listener name, protocol, port
number, etc. Click "Next" to proceed.

9. Select Database:
• Choose the databases for which you want to configure the listener. If there are no databases listed, you
may need to configure them separately. Click "Next" to continue.

10. Listener Configuration Summary:

• Review the summary of the listener configuration. Click "Finish" to complete the configuration process.

11. Configuration Progress:

• Wait for the configuration process to complete. Once finished, you will receive a confirmation message.

12. Verification:
• Verify the listener configuration by checking the listener status using the lsnrctl command.

Results: Oracle Net has been successfully configured using the netca command-line utility.

Conclusion: In this lab, we have learned how to configure Oracle Net Services using the netca command-line
utility. This tool provides a user-friendly interface for configuring listeners and other network components in an
Oracle Database environment.

17 | P a g e
 Lab-7

Title: Backup and Recovery Using RMAN Backup

Objective: The objective of this lab is to perform backup and recovery operations using Oracle Recovery
Manager (RMAN), a powerful tool provided by Oracle for managing backups and restoring databases.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. Oracle Recovery Manager (RMAN)

3. SQL*Plus or SQL Developer tool for executing SQL commands

Procedure:

Backup Operation:

1. Connecting to the Database:

• Open SQL*Plus or SQL Developer.
• Connect to the Oracle Database using the provided credentials.

2. Starting RMAN:
• Launch RMAN by typing the following command in the terminal or command prompt:

3. Taking Full Database Backup:

• Use the following RMAN command to take a full database backup:

1. Monitoring Backup Progress:

• Monitor the progress of the backup operation in the RMAN console. RMAN will display status
messages indicating the progress of the backup.

4. Verifying Backup:
18 | P a g e
 • Once the backup operation is complete, verify the backup files generated by RMAN.

Recovery Operation:

1. Simulating Data Loss:

• Simulate data loss by dropping a table or performing any other operation that results in data loss.

2. Connecting to the Database:

• Connect to the Oracle Database using SQL*Plus or SQL Developer.

3. Starting RMAN:
• Launch RMAN in the terminal or command prompt.

4. Performing Recovery:
• Use the following RMAN command to perform recovery:

5. Monitoring Recovery Progress:

• Monitor the progress of the recovery operation in the RMAN console. RMAN will display status
messages indicating the progress of the recovery.

6. Verifying Recovery:
• Once the recovery operation is complete, verify that the lost data has been successfully recovered.

Results: Backup and recovery operations using RMAN have been successfully performed in the Oracle
Database environment.

Conclusion: In this lab, we have learned how to perform backup and recovery operations using Oracle
Recovery Manager (RMAN) with the provided hostname and password. RMAN provides a comprehensive set of
features for managing backups and restoring databases, ensuring data protection and minimizing downtime in
case of data loss or disaster.
19 | P a g e
 Lab-8

Title: Creation of Event-Based Schedule

Objective: The objective of this lab is to create an event-based schedule in an Oracle Database environment.
Event-based schedules allow users to schedule tasks or jobs based on specific events occurring within the
database.

Equipment/Tools Required:

1. Computer with Oracle Database 19c installed and configured

2. SQL*Plus or SQL Developer tool for executing SQL commands

Procedure:

1. Connecting to the Database:

• Open SQL*Plus or SQL Developer.
• Connect to the Oracle Database using the provided credentials.

2. Creating an Event-Based Schedule:

• Use the following SQL command to create an event-based schedule:

• Replace event_schedule_name with the desired name for the event-based schedule and
event_queue_name with the name of the event queue associated with the schedule.

20 | P a g e
 3. Verifying Schedule Creation:
• Verify that the event-based schedule has been created successfully by querying the

4. Associating Jobs with the Schedule (Optional):

• If needed, associate jobs with the event-based schedule using the
DBMS_SCHEDULER.SET_ATTRIBUTE procedure.

5. Testing the Schedule:

• Test the event-based schedule by performing the event that triggers the schedule (e.g., database
startup).
6. Monitoring Schedule Execution:
• Monitor the execution of jobs associated with the event-based schedule using the appropriate
scheduler views and procedures.

Results: An event-based schedule named "event_schedule_name" has been successfully created in the Oracle
Database environment using the provided hostname and password.

Conclusion: In this lab, we have learned how to create an event-based schedule in an Oracle Database
environment. Event-based schedules allow users to schedule tasks or jobs based on specific events occurring
within the database, providing flexibility and automation in task management.

21 | P a g e