Blog
Database Administration MSc IT 4th Term 2019 Past Paper UOS
University of Sargodha (UOS) M.Sc. IT 4th term Database Administration (IT-3441) past paper 2019, with the full question paper and solved answers on RMAN, tablespaces, Oracle Net and redo logs.
Database Administration Past Paper 2019 (text version)
University of Sargodha – M.Sc. 4th Term Examination 2019, Information Technology
Paper: Database Administration (IT-3441) · Time: 2:30 Hours · Maximum marks: 80
Note: Objective part is compulsory. Attempt any three questions from subjective part.
Objective part (compulsory) (16*2)
- What are Oracle Managed Files?
- Write the command to start the Listener Server Process
- Write the SQL command to change the database’s default temporary tablespace to another temporary tablespace group named as TEMPGRP.
- What is Oracle Enterprise Manager?
- What is the advantage of operating in archivelog mode?
- How index works to find rows faster?
- Define Oracle Constraint?
- What is the smallest unit of storage in Oracle?
- What is an Oracle instance?
- Differentiate between Shared Pool and Large Pool.
- What are the primary types of tablespaces in Oracle?
- List any two of the system privileges for the administrators.
- Which Oracle utility is used for logical backup of a database?
- What is SGA?
- Write SQL command to create a tablespace named “bi-02”.
- What are major tasks of Database Administrator?
Subjective part (3*16)
- Q.2 Write a note on different types of backup methods supported by RMAN.
- Q.3 Write the procedure to resize Smallfile Tablespace using ALTER DATABASE.
- Q.4 Describe basic Oracle Net architecture.
- Q.5 Describe the method to move Online Redo Log Files with an example.
- Q.6 Write a note on Oracle background processes.
Solved answers – Database Administration 2019
Short questions
- Oracle Managed Files (OMF): Oracle automatically creates, names and deletes operating-system files for datafiles, tempfiles, redo logs and control files in locations set by
DB_CREATE_FILE_DESTandDB_CREATE_ONLINE_LOG_DEST_n, so the DBA does not specify file names. lsnrctl start(orlsnrctl start LISTENER); check withlsnrctl status.ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempgrp;- Oracle Enterprise Manager (OEM): Oracle’s web-based graphical tool (Database Control / Cloud Control) to administer, monitor and tune databases – storage, users, performance, backups, alerts and scheduled jobs.
- In ARCHIVELOG mode filled redo logs are archived, so online (hot) backups are possible and the database can be recovered completely or to any point in time after media failure, without data loss.
- A B-tree index stores sorted key values with ROWIDs. Oracle walks from the root through branch blocks to the leaf block holding the key (a few block reads), then uses the ROWID to read exactly the needed table block instead of scanning the whole table.
- An Oracle constraint is a rule on table data that Oracle enforces automatically: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK.
- The data block (Oracle block), set by DB_BLOCK_SIZE (commonly 8 KB).
- Oracle instance: the set of memory structures (SGA) and background processes that access an Oracle database. An instance can mount and open one database at a time (several instances can share one database in RAC).
- Shared pool caches parsed SQL/PL/SQL (library cache) and dictionary data so statements can be reused. Large pool is an optional area for large allocations – RMAN I/O buffers, shared-server session memory and parallel-query messages – keeping them out of the shared pool.
- Permanent (SYSTEM, SYSAUX, USERS – store objects), undo (before-images for rollback and read consistency) and temporary (sort and hash work areas).
- Any two of: SYSDBA, SYSOPER, CREATE USER, ALTER SYSTEM, CREATE TABLESPACE, GRANT ANY PRIVILEGE.
- Data Pump Export (expdp) – the older utility is exp; restore with impdp/imp.
- SGA (System Global Area): the shared memory area of an Oracle instance, allocated at startup and shared by all server and background processes. It contains the database buffer cache, shared pool, redo log buffer, large pool, Java pool and Streams pool.
CREATE TABLESPACE "bi-02" DATAFILE '/u01/oradata/orcl/bi02.dbf' SIZE 100M AUTOEXTEND ON;– the double quotes are needed because the name contains a hyphen.- DBA tasks: installing and upgrading Oracle, creating databases, managing storage and tablespaces, managing users and security, backup and recovery, monitoring and performance tuning, applying patches, and planning capacity.
Subjective questions
Q.2 RMAN backup methods:
- Backup sets vs image copies: backup sets are RMAN’s own format, holding only used blocks and optionally compressed; image copies are exact file copies that can be used directly (
BACKUP AS COPY). - Full vs incremental: a full backup copies all used blocks; incremental level 0 is the base, and level 1 copies changed blocks – differential (since the last level 0 or 1) or cumulative (since the last level 0). Block change tracking speeds incrementals.
- Whole vs partial:
BACKUP DATABASEvs BACKUP TABLESPACE / DATAFILE; plus archived logs (PLUS ARCHIVELOG), control file and SPFILE (autobackup). - Consistent vs inconsistent: taken mounted after clean shutdown (works in NOARCHIVELOG) vs taken open (needs ARCHIVELOG).
- Compressed, encrypted, multisection and incrementally updated backups (level 1 applied to image copies to keep them current).
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
RMAN> BACKUP AS COMPRESSED BACKUPSET TABLESPACE users;Q.3 Resizing a smallfile tablespace with ALTER DATABASE: a smallfile tablespace is resized through its datafiles. (1) Find the files and sizes: SELECT file_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name='USERS'; (2) Resize a file: ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' RESIZE 500M; – shrinking fails if data lies beyond the new size. (3) Or enable automatic growth: ALTER DATABASE DATAFILE '…/users01.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE 2G; (4) Or add space with another file: ALTER TABLESPACE users ADD DATAFILE '…/users02.dbf' SIZE 200M; (5) Verify with DBA_DATA_FILES / DBA_FREE_SPACE.
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 Moving online redo log files (database must not be using them while moving):
SQL> SELECT member FROM v$logfile; -- current locations
SQL> SHUTDOWN IMMEDIATE;
$ mv /u01/oradata/orcl/redo01.log /u02/oradata/orcl/redo01.log -- OS move
$ mv /u01/oradata/orcl/redo02.log /u02/oradata/orcl/redo02.log
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo01.log'
TO '/u02/oradata/orcl/redo01.log';
SQL> ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo02.log'
TO '/u02/oradata/orcl/redo02.log';
SQL> ALTER DATABASE OPEN;Alternative without downtime: add new members in the new location (ALTER DATABASE ADD LOGFILE MEMBER … TO GROUP 1;), switch logs with ALTER SYSTEM SWITCH LOGFILE; until the group is INACTIVE, then drop the old member. Take a backup afterwards.
Q.6
Mandatory background processes:
- DBWn (Database Writer): writes dirty buffers from the buffer cache to datafiles (at checkpoints, when free buffers are needed).
- LGWR (Log Writer): writes the redo log buffer to the online redo log files – at commit, every 3 seconds and when the buffer is one-third full.
- CKPT (Checkpoint): signals DBWn at checkpoints and updates datafile headers and the control file with checkpoint information.
- SMON (System Monitor): performs instance recovery at startup, cleans up temporary segments and coalesces free space.
- PMON (Process Monitor): cleans up after failed user processes – rolls back their transactions, releases locks and resources, and registers services with the listener.
Optional/other: ARCn (archiver – copies filled redo logs in ARCHIVELOG mode), RECO (resolves failed distributed transactions), MMON/MMNL (AWR statistics), Dnnn/Snnn (shared server dispatchers and servers), CJQ0 (job queue).
Answers prepared by Shining Study for revision. Check them against your class notes and teacher’s guidance.
