 |
DBA712S - DATABASE ADMINISTRATION - 2ND OPP - JULY 2022 |
|
|
1 Page 1 |
▲back to top |
nAm I BIA un IVERSITY
OF SCIEnCE Ano TECHnOLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENTOF COMPUTERSCIENCE
QUALIFICATION: BACHELOROF COMPUTERSCIENCE(SYSTEMSADMINISTRATION)
QUALIFICATION CODE: 07BCMS
LEVEL: 7
COURSE: Database Administration
COURSE CODE: DBA712S
DATE: July 2022
SESSION: 2
DURATION: 3 hours
MARKS: 100
SECOND OPPORTUNITY/SUPPLEMENTARY EXAMINATION QUESTION PAPER
EXAMINER(S)
MR. ISAAC NHAMU
MODERATOR:
DR. AMER DHEEDAN
THIS EXAM QUESTION PAPER CONSISTS OF 7 PAGES
(Excluding this front page)
INSTRUCTIONS
1. Answer ALL the questions on the answer scripts.
2. Write clearly and neatly.
3. Number the answers clearly.
4. When answering questions you should be guided by the allocation of marks in [ ]. Do not give too
few or too many facts in your answers.
PERMISSIBLE MATERIALS
1. Non-programmable calculator.
|
|
2 Page 2 |
▲back to top |
Section A (Multiple Choice)
[20 marks]
1. In your multitenant container database (CDB)with two pluggable database (PDBs).You want to
create a new PDB by using SQL Developer. Which statement is true?
A. The CDB must be open.
B. The CDB must be in the mount stage.
C. The COB must be in the nomount stage.
D. Alt existing PDBsmust be closed.
2. In which two scenarios do you use SQL* Loader to load data? (Choose two.)
A. Transform the data while it is being loaded into the database.
B. Use transparent parallel processing without having to split the external data first.
C. Load data into multiple tables during the same load statement.
D. Generate unique sequential key values in specified columns.
3. Your multitenant container database has three pluggable databases (PDBs): PDBl, PDB2, and
PDB3. Which two RMAN commands may be; used to back up only the PDBl pluggable
database? (Choose two.)
A. BACKUPPLUGGABLEDATABASEPDBl while connected to the root container
B. BACKUPPLUGGABLEDATABASEPDBl while connected to the PDBl container
C. BACKUPDATABASEwhile connected to the PDBl container
D. BACKUPDATABASEwhile connected to the boot container
E. BACKUPPLUGGABLEdatabase PDBl while connected to PDB2
4. You are managing an Oracle Database 12c database. The database is open, and you plan to
perform Recovery Manager (RMAN) backups. Which three statements are true about these
backups? (Choose three.)
A. The backups would be possible only if the database is running in ARCHIVELOGmode.
B. The backups need to be restored and the database has to be recovered in case of a media
failure.
C. The backups by default consist of all the data blocks within the chosen files or the full
database.
D. The backups would be consistent.
E. The backups would be inconsistent.
Page 11
|
|
3 Page 3 |
▲back to top |
5. You want to create a database with a block size other than the default 8 kilobytes (KB) by using
the Database Configuration Assistant (DBCA). Which option should you use?
A. Automatic Storage Management (ASM) for storage of data files
B. a custom database template
C. a file system for storage of data files
D. a Data Warehouse database template
6. You create a new pluggable database, HR_PDB,from the seed database. Which three
tablespaces are created by default in HR_PDB?(Choose three.)
A. SYSTEM
B. SYSAUX
C. EXAMPLE
D. UNDO
E. TEMP
F. USERS
7. What is the outcome of the SHUTDOWN ABORTcommand?
A. Uncommitted transactions are rolled back
B. Instance recovery must be requested by the DBA at the next startup
C. Pending transactions are committed and the database is closed.
D. Dirty buffers in the buffer cache and unwritten redo are not written to the data files and redo
log files respectively.
8. Which task is performed by a background process in a database instance?
A. Copying on line redo log files to offline storage
B. Creating dedicated server connections
C. Connecting between a client process and a dispatcher
D. Executing PL/SQLcode
Page I 2
|
|
4 Page 4 |
▲back to top |
9. Which two statements are true about initialization parameter files? (Choose two.)
A. Both the SPFILEand PFILEmust always reside on a file system accessible from the database
host server.
B. On startup, by default a database instance always first searches for an SPFILE,and if it does
not find any, searches for a PFILE.
C. A PFILEmust exist for an SPFILEto be created.
D. A lost or damaged SPFILEcan be re-created by using the parameter values listed in the alert
log.
E.The ALTERSYSTEMcommand cannot be used to change the value of any parameter if a
database instance has started using a PFILE.
10. Your database is open and the listener LISTENERis up. You issue the command:
LSNRCTL> RELOAD
What is the effect of RELOADon sessions that were originally established by LISTENER?
A. Existing connections are not disconnected; however, they cannot perform any operations
until the listener completes the re-registration of the database instance and service handlers.
B. Only sessions based on static listener registrations are disconnected.
C. All the sessions are terminated and active transactions are rolled back.
D. The sessions are not affected and continue to function normally.
11. You configured the Fast Recovery Area (FRA)for your database. The database instance is in
ARCHIVELOGmode. The default location for the archived redo log files is the FRA.
Which two files are removed automatically if space is required in the FRAas per the retention
policy? (Choose two.)
A. flashback logs
B. RMAN backups that are obsolete
C. user-managed backups of data files and control files
D. Archived redo log files that have multiple copies in a different archive location
Page I 3
|
|
5 Page 5 |
▲back to top |
12. Which two are true concerning a multitenant container database with three pluggable
database? (Choose two.)
A. The pluggable databases are patched together.
B. Pluggable databases are only used for database consolidation.
C. The pluggable databases increase patching time.
D. All administration tasks must be done to a specific pluggable database.
E.The pluggable databases reduce administration effort.
Page I 4
|
|
6 Page 6 |
▲back to top |
Section B {Structured Questions}
[65 marks]
Question 1
Describe what the use each of the following schema objects in Database Administration ( Do
not just define the objects but state what DBAs can use them for}:
[10]
i. View
ii. Sequence
iii. Data Dictionary
iv. Trigger
V. Package
Question 2
a. You are installing a Database and have a choice to install Enterprise Edition or Express
Edition. Give two reasons you would choose to install the Enterprise Edition over the
Express Edition and two reasons you would choose to install the Express Edition over
the Enterprise Edition.
[8]
b. There are four Database Administrative Tools used with the Oracle database namely:
i. SQL PLUS,
ii. SQL Developer,
iii. Enterprise Manage Database Express,
iv. Enterprise Manage Database Cloud Control.
Choose any two administrative tools from the above and give two limitations and
two benefits of each.
[8]
c. You are managing passwords for users in an organisation. Describe in detail what four
aspects you look into when managing a database password.
[4]
Page I 5
|
|
7 Page 7 |
▲back to top |
Question 3
You are installing your database and you have set your pluggable as Yourlastname where
Yourlastname is your surname.
a. Outline three advantages of using a pluggable database (Multitenant architecture)
over a container database.
[6]
b. Given that you create a user called YourName where YourName is your first name and
password is YourSurname. State how you would connect this user to the pluggable
database called Exampdb, assuming it has been set as the container database and is
open for reading and writing?
[2]
c. What are some of the system privileges you would give the user YourName to enable
them to connect to the pluggable database (Name two).
[2]
Question 4
Outline five challenges that have been presented by the COVID-19 pandemic to database
administration.
[10]
Question 5
a. In oracle 12c/19c there are three startup and four shutdown modes.
i. List the startup modes.
[1]
ii. Give examples of situations where you would use each of the shutdown
modes.
[8]
b. As a DBA you will normally perform database installations following the following
guidelines; pre-installation, installation then post-installation. State at least two
tasks you would perform at each of the stages of the above guidelines.
[6]
Page I 6
|
|
8 Page 8 |
▲back to top |
Section C (SQL Commands)
[15 marks]
Question 1
You are given that a user has created a text file called Yourlastname.txt where Yourlastname
is your surname in the folder c:\\Test>. This text file contains the following information.
Loini Mathews, 16,F
Adam Everson, 17,M
Jefrey Shilongo, 18,M
Joe Peters, 19,M
a. Write SQL statements to create a table, Student_Details, in your database given that
the data above is Full_name, Age ang Sex:
[5]
b. Write the contents of a control file that can be used to load the data in the file
Yourlastname.txt into the table Student_Details using the SQL Loader.
[10]
<<<<<<<<<END>>>>>>>>>
Page I 7