Blog
Database Administration and Management MSc IT 4th Term 2021 Past Paper UOS
University of Sargodha (UOS) M.Sc. IT 4th term Database Administration & Management (IT-3441) past paper 2021, with the full question paper and solved answers including SQL commands.
Database Administration & Management Past Paper 2021 (text version)
University of Sargodha – M.Sc. 4th Term/Semester Exam 2021, I.T
Paper: Database Administration & Management (IT-3441) · Time: 2:30 Hours · Maximum marks: 80
Note: Objective part is compulsory. Attempt any three questions from subjective part.
Objective part (compulsory) (2*16)
- Name any two of the primary types of tablespaces in an Oracle database?
- Write the SQL statement to resize the “USERS” tablespace to 15MB.
- Differentiate between Oracle database and Oracle instance.
- Which data structure is used to store the values of a unique index?
- Write SQL statement to create a view on EMP table called EMP_INFO that contains the columns of Employee_Number, First_Name, and Last_Name only.
- What is PL/SQL?
- What is flashback query?
- What is the purpose of DBWn?
- Differentiate between segments and extents.
- Which initialization parameter specifies the default block size?
- How index is able to find rows faster?
- What is a disk group?
- Name the standard tool for physical backups in Oracle?
- Write the commands needed to start a database in ARCHIVELOG mode.
- What is the impact of fragmentation on the database performance?
- Name any two types of Oracle segments?
Subjective part (16*3)
- Q.2 How online backups are implemented? Write the steps to perform an online database backup.
- Q.3 Write a note on automatic storage management in Oracle.
- Q.4 Describe basic Oracle Net architecture.
- Q.5 Briefly describe the bigfile, SYSTEM, SYSAUX, and TEMP tablespaces.
- Q.6 What is the purpose of Oracle initialization parameters? How they are stored? Briefly discuss any six of the basic initialization parameters.
Solved answers – Database Administration & Management 2021
Short questions
- Any two of: permanent (e.g. SYSTEM, SYSAUX, USERS), undo and temporary tablespaces.
- Resize its datafile:
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' RESIZE 15M;(for a bigfile tablespace:ALTER TABLESPACE users RESIZE 15M;). - The database is the set of physical files on disk (datafiles, control files, redo logs). The instance is the memory (SGA) and background processes that open and work with those files; it exists only while running.
- A B-tree (balanced tree) index.
CREATE VIEW emp_info AS SELECT employee_number, first_name, last_name FROM emp;- PL/SQL is Oracle’s procedural extension of SQL, adding variables, conditions, loops, exception handling, procedures, functions, packages and triggers, executed inside the database.
- Flashback query retrieves data as it was at an earlier time using undo data:
SELECT * FROM emp AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE;– useful to recover from user mistakes. - DBWn (Database Writer) writes modified (dirty) buffers from the buffer cache to the datafiles – at checkpoints and when free buffers are needed.
- A segment is the storage for one database object (table, index, undo); it is made of extents, each a set of contiguous data blocks allocated at one time.
DB_BLOCK_SIZE.- The B-tree index keeps keys sorted with their ROWIDs, so Oracle reaches the right leaf in a few block reads and fetches the row directly by ROWID instead of a full table scan.
- An ASM disk group is a collection of disks managed together by Automatic Storage Management; files are striped across its disks and optionally mirrored (external, normal or high redundancy), e.g. +DATA, +FRA.
- RMAN (Recovery Manager).
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; ARCHIVE LOG LIST; -- verify- Fragmentation (scattered free space in tablespaces, half-empty blocks below the high-water mark, chained/migrated rows) wastes space and makes full scans read more blocks and rows need extra I/O, so queries slow down. Fixed with shrinking/moving tables, rebuilding indexes and locally managed tablespaces.
- Any two of: table, index, undo, temporary, LOB and cluster segments.
Subjective questions
Q.2 Online backups: an online (hot) backup copies the database while it is open. It is possible only in ARCHIVELOG mode, because the copied files are inconsistent and archived redo must be applied during recovery.
Steps with RMAN (recommended):
SQL> ARCHIVE LOG LIST; -- confirm ARCHIVELOG mode
$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> LIST BACKUP SUMMARY;User-managed steps:
SQL> ALTER DATABASE BEGIN BACKUP; -- or per tablespace
$ cp /u01/oradata/orcl/*.dbf /backup/ -- copy datafiles at OS level
SQL> ALTER DATABASE END BACKUP;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; -- archive current redo
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control.bkp';Also back up the archived logs created during the backup.
Q.3 Automatic Storage Management (ASM): Oracle’s integrated volume manager and cluster file system for database files. Disks are grouped into disk groups (e.g. +DATA, +FRA); ASM divides files into allocation units and stripes them evenly across all disks for performance, and mirrors them for protection (external, normal two-way or high three-way redundancy, with failure groups). A separate ASM instance (+ASM) manages metadata. Benefits: no manual file placement, automatic rebalancing when disks are added or removed, OMF-style file naming (+DATA/orcl/datafile/users.259.1), support for RAC, and management through ASMCMD, SQL*Plus or Enterprise Manager.
Q.4
Oracle Net architecture: Oracle Net (formerly SQL*Net) is the networking layer that lets clients and servers communicate.
- Client side: the application uses OCI/JDBC; Oracle Net resolves a connect identifier (e.g. ORCL) into a connect descriptor using a naming method – tnsnames.ora (local naming), Easy Connect (host:port/service), directory naming (LDAP) or external naming. sqlnet.ora holds profile settings.
- Network layers: Oracle Net Foundation Layer (establishes and maintains sessions) and Oracle Protocol Support (maps to TCP/IP, TCP/IPS with SSL, named pipes, SDP).
- Server side: the listener (configured in listener.ora, default port 1521) receives connection requests and hands them to a dedicated server process or, in shared-server mode, to a dispatcher that queues requests for shared server processes. Services register dynamically with the listener (LREG/PMON).
- Tools: Net Manager, Net Configuration Assistant (NETCA), lsnrctl and tnsping.
Q.5 Tablespaces:
- Bigfile tablespace: contains a single, very large datafile (up to 4 billion blocks, 32 TB with 8 KB blocks); simplifies management of very large databases, and is resized with ALTER TABLESPACE.
- SYSTEM: created with the database; holds the data dictionary and SYS objects. Always online and must never be used for user data.
- SYSAUX: auxiliary to SYSTEM (10g+); stores data for components such as AWR, Enterprise Manager, Oracle Text and Streams, reducing load on SYSTEM.
- TEMP: temporary tablespace for sort, hash-join and GROUP BY work that does not fit in memory; uses tempfiles, holds no permanent objects, and is assigned as the default temporary tablespace.
Q.6 Initialization parameters: they configure the instance – memory sizes, file locations, limits and features – and are read at startup. Storage: in a binary server parameter file SPFILE (spfileSID.ora, changed with ALTER SYSTEM … SCOPE=SPFILE/MEMORY/BOTH) or a text PFILE (initSID.ora, edited manually), both in $ORACLE_HOME/dbs by default. Six basic parameters:
DB_NAME– database name (up to 8 characters).CONTROL_FILES– locations of the control files.DB_BLOCK_SIZE– standard block size, fixed at creation.MEMORY_TARGET/SGA_TARGET/PGA_AGGREGATE_TARGET– memory sizes.PROCESSES– maximum number of OS processes connecting to the instance.UNDO_MANAGEMENTandUNDO_TABLESPACE– automatic undo and which undo tablespace to use.- (Also
DB_RECOVERY_FILE_DEST,COMPATIBLE,LOG_ARCHIVE_DEST_n.)
Answers prepared by Shining Study for revision. Check them against your class notes and teacher’s guidance.
