Oracle DBA Interview Questions

Hi Friends,

In this section you can find out some nice and solid tips about Oracle DBA Interview Questions and Answers.

Let’s start with some backup and recovery Interview questions.

1.            Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2.            You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3.            Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

4.            What command would you use to create a backup control file?

Alter database backup control file to trace.

5.            What is the purpose of the IMPORT option IGNORE? What is it’s default setting?

The IMPORT  IGNORE option tells import to ignore “already exists” errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the table’s data will be inserted. The default value is N.

6.            You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not

We are unable to take hot backup in noarchive mode. You can’t use hot backup without being in archivelog mode. So no, you couldn’t recover.

7.            How many redo logs should you have and how should they be configured for maximum recoverability

You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.

8.            Users aren’t being allowed on the system. The following message is received:

ORA-00257 archiver is stuck. Connect internal only, until freed : What is the problem

The archive destination is probably full, take backup of archivelogs and remove them .  archiver  process will re-start.

9.            You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do

As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following:

CONNECT sys as sysdba


(Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE …. OFFLINE;)



(bring read-only tablespaces back online)

Shutdown and backup the system, then restart

If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well.

If no backup of the control file is available then the following will be required:




However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.

10. What are the steps involved in Database Startup?

Start an instance, Mount the Database and Open the Database.

11. What are the steps involved in Instance Recovery?

Rolling forward to recover data that has not been recorded in data files yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

12. Can Full Backup be performed when the database is open?


13. What are the steps involved in Database Shutdown ?

Close the Database, Dismount the Database and Shutdown the Instance.

14. What is Archived Redo Log ?

Archived Redo Log consists of Redo Log files that have archived before being reused.

15. What is Restricted Mode of Instance Startup?

An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

More details and real time scenario based Oracle DBA Interview Questions, you can find out from following link.

Oracle DBA Interview Questions

Many sites and resources are available with different kind of Oracle DBA Interview Questions but those all are confusing Oracle DBAs. Due to this reason, I tried to provide single unique resource for all Oracle DBA Interview Questions. Wish you All the best.




  1. Mihir Shah
    December 16, 2013 at 5:26 am

    Very useful information about Oracle DBA Interview Questions. I liked above questions and answers. Do you think these types of questions are helpful to crack interview? Is there any book available to provide me fresh interview questions with answers and explanations?

  2. M.Venkatesh
    December 16, 2013 at 4:02 pm

    Oracle DBA interviews are very tough and we need these kind of questions for preparing. Appreciate your efforts and thanks for sharing. I bought Gitesh Trivedi’s Oracle DBA interview Questions & Answers book. It is great. It contains more than 400 questions with answers and explanation. Great tip. Thanks once again.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: