ORACLE DBA INTERVIEW QUESTIONS

Q: What are the Oracle Background Processes?

A: The Oracle Background Processes are programs or tasks that run on the Oracle background

such as log writers, db writers, archives, checkpoint, etc.

Q: Describe the V$BGPROCESS view.

A: The V$BGPROCESS view contains information about active and inactive background processes.

Q: Describe the following background processes:

PMON

DBWn

ARC0

CKPT

LGWR

SMON

RECO

A: PMON - The Process Monitor (PMON) is responsible for performing recovery if a user

process fails and rolls back the uncommitted transactions. DBWn - The Database Writer

(DBWn) is responsible for writing the changed blocks or dirty blocks in the database.

ARC0 - The Archiver (ARC0) is responsible for writing the Online redo log files into the

archive log destination.

CKPT - The checkpoint process (CKPT) is responsible for

synchronizing the buffer cache with the data file. It updates all datafile headers and the

control files.

LGWR - The Log Writer (LGWR) is responsible for writing data from redo

log buffers to the online redo log files.

SMON - The System Monitor process (SMON)

is responsible for instance recovery.

RECO - The Re-coverer Process (RECO) is

responsible for performing recovery of in-doubt transactions that often occur in distributed

transactions.

Q: Describe an archive log configuration.

A: In an archive log configuration, Oracle guarantees to recover to the point of failure.

Q: What does the ARCHIVE LOG LIST command?

A: The ARCHIVE LOG LIST command shows information about the database archive

log mode status.

Q: What are disadvantage and advantage of a database in the NOARCHIVELOG mode?

A: Disadvantage: You can’t recover to the database point of failure. Advantage: It will

use fewer resources.

Q: What are disadvantage and advantage of a database in the ARCHIVELOG mode?

A: Advantage: You can recover to the database point of failure. Disdvantage: It will use

more resources.

Q: What does the following SQL statememt?

SQL> SELECT * FROM v$bgprocess

WHERE PADDR <> '00'

/

A: It shows all the active Oracle background processes.

Q: How do you test that a database is in archivelog or not?

A: SQL> ARCHIVE LOG LIST

Q: What is the controlfile in the Oracle database?

A: The controlfile in the Oracle database is a binary file that contains the database structure,

backup information, datafiles synchronization, and more.

Q: How do you get a list of all your controlfiles’ location?

A: SQL> SELECT name
FROM v$controlfile

Q: Describe the following views:

V$CONTROLFILE view

V$CONTROLFILE _RECORD_SECTION view

A: The V$CONTROLFILE view contains the location of your controlfiles and their status.

The V$CONTROLFILE _RECORD_SECTION view shows different section information

such as record_size, records_total, records_used, etc.

Q: What do the following SQL statements?

SQL> ALTER DATABASE BACKUP CONTROLFILE
TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
A: Copies controlfile exactly as it was on the time of its backup.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
A: Gets a SQL statement. We can use it to re-create the controlfile if we needed.
SQL> SELECT * FROM v$controlfile_record_section
A: Queries information about different section in the controlfile.