Computer Knowledge

Database Management Systems

5,543 Questions

Database Management Systems (DBMS) form the core framework for data storage, retrieval, and security in modern software applications. Concepts such as the E-R model, backup planning, SQL integration, and big data architecture are essential for computer knowledge sections. This hub offers a comprehensive set of practice questions to master DBMS fundamentals and advanced database operations.

E-R Model ConceptsBackup and RecoverySQL Server UpgradesJDBC and ODBCBig Data CharacteristicsData VirtualizationOracle Database

Database Management Systems Questions

Multiple choice

A media failure occurred causing NOARCHIVELOG mode database to crash. It was discovered that two of the data files are corrupt. The last whole closed database backup was taken one week ago with a online redo log sequence number of 83. At the time the data files were corrupted, the online redo log sequence number was 85. Although there were few committed transactions after the last backup, as much data as possible has to be recovered. Which recovery process should be used?

  1. Shut down the instance, restore all of the Oracle files from the most recent backup, and restart the instance.

  2. Manually restore and recover the two corrupt data files from the most recent backup.

  3. Shut down the instance, restore the two corrupt data files from the most recent backup, and restart the instance.

  4. Shut down the instance, restore all of the Oracle files from the most recent backup, restart the instance, and open the database using the 'ALTER DATABASE OPEN RESETLOGS' statement.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

In NOARCHIVELOG mode, you cannot apply redo logs for recovery since archived logs are not maintained. The only recovery option is to restore the entire database from the last consistent backup - partial restoration would create an inconsistent database state because redo logs from sequence 83 to 85 cannot be applied. Any committed transactions after the backup will be lost. Option B is incorrect because restoring only corrupt files without applying redo leaves the database inconsistent. Option C has the same issue. Option D's RESETLOGS is only valid in ARCHIVELOG mode after recovery.

Multiple choice

Consider the following RMAN script:

RUN{

SET NEWNAME FOR DATAFILE '/disk1/database/db1.dbf' TO '/disk2/database/db1.dbf';

SWITCH DATAFILE ALL;

RESTORE DATABASE;

RECOVER DATABASE;

ALTER DATABASE OPEN;

}

When executed, what result will occur?

  1. RMAN will restore the data file to disk1.

  2. The control file will be updated with the data file's new location, and the recovery will be successful.

  3. All database data files will be moved to the new location and recovered.

  4. The control file will not be updated with the data file's new location because the RENAME DATAFILE command is missing.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The RMAN script sets a new name for the data file, but the RESTORE DATABASE command executes before the SWITCH DATAFILE ALL command. The SWITCH command updates the control file to point to the new location, but since it comes after RESTORE, the restore operation still uses the original location specified in the control file. Therefore, the data file will be restored to disk1 (the original location), not disk2. After SWITCH runs, the control file would point to disk2, but the restore has already completed by then. For this to work as intended, SWITCH should come before RESTORE.

Multiple choice

Five data files, that are spread across multiple disks have to be recovered. Which initialization parameter should be set to speed up redo application?

  1. RECOVERY_PARALLELISM

  2. FAST_START_MTTR_TARGET

  3. LOG_CHECKPOINT_INTERVAL

  4. FAST_START_PARALLEL_ROLLBACK
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

RECOVERY_PARALLELISM is the initialization parameter that controls the degree of parallelism used during media recovery. When recovering multiple data files spread across different disks, setting this parameter allows Oracle to spawn multiple recovery processes that can apply redo logs in parallel to different data files simultaneously. This parallelization significantly speeds up redo application compared to sequential processing. FAST_START_MTTR_TARGET controls crash recovery time, LOG_CHECKPOINT_INTERVAL affects checkpoint frequency, and FAST_START_PARALLEL_ROLLBACK manages transaction rollback parallelism - none of these specifically control parallel redo application during media recovery.

Multiple choice

What steps should be taken to gather information about checkpoints?

  1. Set the LOG_CHECKPOINTS_TO_ALERT initialization parameter to TRUE. Monitor the alert log file.

  2. Set the LOG_CHECKPOINT_TIMEOUT parameter. Force a checkpoint by using the FAST_START_MTTR_TARGET parameter. Monitor the alert log file.

  3. Set the LOG_CHECKPOINT_TIMEOUT parameter. Force a log switch by using the command ALTER SYSTEM FORCE LOGSWITCH. Force a checkpoint by using the command ALTER SYSTEM FORCE CHECKPOINT. Monitor the alert log file.

  4. Set the FAST_START_MTTR_TARGET parameter to TRUE. Force a checkpoint by using the command ALTER SYSTEM FORCE CHECKPOINT. Monitor the alert log file.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Setting LOG_CHECKPOINTS_TO_ALERT to TRUE writes checkpoint information to the alert log, which is the standard method for monitoring checkpoint activity. The other options mix incorrect parameters or unnecessary force operations - you only need to enable logging and monitor the alert log file.

Multiple choice

While attempting to connect to a remote server, the following error appeared:

ORA-12533: TNS:illegal ADDRESS parameters

To attempt to resolve the error, the tnsnames.ora file was examined. What should be verified in this file?

  1. That the correct service name exists in the tnsnames.ora file.

  2. That the service name of the database was entered correctly in the connect string.

  3. That the connect descriptor's' protocol-specific parameters located in the ADDRESS section are correct.

  4. That the service name of the database was entered correctly in the connect string.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The ORA-12533 error specifically indicates illegal ADDRESS parameters in the TNS connect descriptor. You need to verify the protocol-specific parameters (PORT, HOST, etc.) in the ADDRESS section of tnsnames.ora. The service name (B, D) is irrelevant to this specific error.

Multiple choice

What is the difference between CYCLE/NO CYCLE in a oracle Sequence?

  1. For a cycle values for the column are regenerated MAXVALUE is reached.

  2. For a cycle values for the column are regenerated from MINVALUE once MAXVALUE is reached.

  3. For a no cycle values for the column are regenerated MAXVALUE is reached.

  4. For a no cycle values for the column are regenerated from MINVALUE once MAXVALUE is reached.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The CYCLE option causes a sequence to automatically restart at MINVALUE when MAXVALUE is reached, creating a cycle of values. NO CYCLE (the default) raises an error when MAXVALUE is exhausted. Option B correctly describes the CYCLE behavior; options A and C incorrectly describe regeneration at MAXVALUE without specifying the restart point.

Multiple choice

What does the initialization parameter FAST_START_MTTR_TARGET determine?

  1. The instance recovery time

  2. The undo retention period

  3. The database restore time

  4. The number of redo log groups
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

FAST_START_MTTR_TARGET specifies the target mean time to recover (MTTR) after instance failure, which determines how frequently checkpoints must occur. It does not control undo retention (B), restore time (C), or redo log groups (D). A lower value forces more frequent checkpoints.

Multiple choice

What type of failure occurs when Oracle fails due to an operating system or computer hardware failure?

  1. Application failure

  2. Instance failure

  3. Media failure

  4. Rollback failure
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Instance failure occurs when the Oracle instance (the memory structures and background processes) terminates due to operating system crashes, hardware failures, or power loss. This is distinct from media failure (disk damage), application failure (program errors), or rollback failure (undo segment issues). When the OS or hardware fails, the instance itself fails, making option B correct.

Multiple choice

TKPROF is ________.

  1. a utility that enables SQL code to run faster

  2. used to format the trace file generated by SQL_TRACE

  3. a diagnostic tool used to capture operating system statistics

  4. a utility used to capture the statistics of all SQL currently running in the database
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

TKPROF is a Oracle utility that formats and interprets raw trace files generated by SQL_TRACE. The raw trace file is binary and hard to read directly. TKPROF converts it into human-readable format showing execution statistics, SQL statements, and timing information for performance analysis.

Multiple choice

Which system privilege must the RMAN user have to be able to connect and execute RMAN commands on a target database?

  1. SYSDBA

  2. SYSOPER

  3. No system privileges are required for the RMAN user.

  4. Only SYS user can connect and execute RMAN commands on a target database.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

RMAN (Recovery Manager) requires the SYSDBA system privilege to connect to a target database and perform backup and recovery operations. SYSDBA is the highest administrative privilege that allows database startup, shutdown, and complete management. SYSOPER has fewer privileges and cannot perform all RMAN operations.

Multiple choice

A latch is defined as ____.

  1. a mechanism used by the Oracle optimizer to run user queries in parallel

  2. a mechanism to prevent data files that make up the database from auto-extending

  3. a mechanism used by the background process PMON for cleaning up the caches and freeing up resources the user processes were using

  4. a low-level serialization mechanism to protect shared data structures in the system global area
Reveal answer Fill a bubble to check yourself
C Correct answer
Multiple choice

Examine the following statement: ALTER DATABASE BACKUP CONTROL FILE TO 'control01.bkp'; What does the statement generate?

  1. A file containing a SQL statement which will re-create the database

  2. A text copy to the control file

  3. A binary copy of the control file

  4. A file containing a SQL statement which will re-create the control file
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The ALTER DATABASE BACKUP CONTROL FILE TO 'filename' command creates a binary copy of the control file at the specified location. This is different from the TO TRACE option which generates a SQL script. The binary backup preserves all control file contents in their native format.

Multiple choice

The files that are created when an oracle background process encounters an exception are _______.

  1. redo files

  2. exec files

  3. control files

  4. trace files
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

When an Oracle background process encounters an exception or error, it writes diagnostic information to trace files in the user_dump_dest or background_dump_dest directory. These trace files contain error details, stack traces, and context information useful for troubleshooting database issues.