Oracle DBA Interview Questions and Answers

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 point 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.

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.

Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks.

These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

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 point in time.

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point 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.

Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup

ALTER DATABASE MOUNT - The database is mounted

ALTER DATABASE OPEN - The database is opened

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Explain difference between Oracle Database and Instance

Oracle database consist of following:

      Datafiles, Control Files, Redo Log Files

Oracle instance consist of following:

        SGA and Oracle processes (DBWR, LGWR, SMON, PMON, ARCH, CKPT, etc.)

What is main purpose of CHECKPOINT?

A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.

A checkpoint has two purposes:

    1. To establish data consistency

    2. Enable faster database recovery.

How do you rename a database?

1. Alter Database backup control file to trace;

2. Above step will create a text control file in user_dump_dest directory.

3. Change name of the Database in above file and in init.ora file.

4. STARTUP NOMOUNT

5. Run the script that was modified in step 3

6. ALTER DATABASE OPEN RESETLOGS;

 What is a deadlock and Explain?

A deadlock is a condition where two or more users are waiting for data locked by each other.

Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement.

Statement rolled back is usually the one which detects the deadlock. Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multi-table deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock.

What are the system resources that can be controlled by profile?

1. Number of concurrent sessions by user

2. CPU processing time

3. Amount of Logical I/O

4. Amount of Idle time

When a user process fails, what background process cleans up after it?

PMON

How do you resize a data file?

alter database datafile '/ora01/oradata/users02.dbf' resize 100M;

What is database?

A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.

 

What is the difference between DBMS and RDBMS?

DBMS is persistent and accessible when the data is created or exists, but RDBMS tells about the relation between the table and other tables.

RDBS supports a tabular structure for data and relationship between them in the system whereas DBMS supports only the tabular structure.

DBMS provide uniform methods for application that has to be independently accessed, but RDBMS doesn’t provide methods like DBMS but provide relationship which link one entity with another.

What is a Dataguard?

Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios

What is the difference between Active Dataguard, and the Logical Standby implementation of dataguard?

Active dataguard is almost like a physical standby. We can use physical standby for testing without compromising on data. We can open the physical standby on read/write mode, so that we can do some destructive things in it (drop tables, change data, whatever – run a test – perhaps with real application testing). While we are using it for reporting, redo will still stream from production.

What is difference between physical and standby databases?

The main difference between physical and logical standby databases is the manner in which apply services apply the archived redo data:

a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle Database.

b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database.

What is Data Guard Broker?

Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager.

It can be used to perform:

a) Create and enable Data Guard configurations, including setting up redo transport services and apply services

b) Manage an entire Data Guard configuration from any system in the configuration

c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases

d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.

e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

What are the Data guard Protection modes and summarize each?

Maximum availability :

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

Maximum performance:

This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

Maximum protection:

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

If you have a low-bandwidth WAN network, what can you do to improve the Oracle 11g data guard configuration in a GAP detected situation?

Oracle 11g introduces the capability to compress redo log data as it transports over the network to the standby database. It can be enabled using the compression parameter. Compression becomes enabled only when a gap exists and the standby database needs to catch up to the primary database.

alter system set log_archive_dest_1=’SERVICE=DBA11GDR COMPRESSION=ENABLE’;

How can you control when an archive log can be deleted in the standby database in oracle 11g ?

In Oracle 11g, you can control it by using the log_auto_delete initialization parameter. The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. Default is 24 hours. For archivelog retention to be effective, the log_auto_delete parameter must be set to true.

Can Oracle Data Guard be used with Standard Edition of Oracle?

Yes and No. The automated features of Data Guard are not available in the standard edition of Oracle. You can still however, perform log shipping manually and write scripts to manually perform the steps. If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server. Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.

What is dump destination? What are bdump, cdump and udump?

Trace files for Oracle processes are stored in dump destination.

Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.

Why and how the deadlock situation arises

A deadlock situation arises when two or more users wait for the same resource locked by one another or two or more processes wait to update rows which are locked by other processes. Oracle if detects a deadlock, rolls back the session chosen by the deadlock victim.

What is Cache Fusion Technology?

In Cache fusion, multiple buffers join to act as one. It eliminates disk i/o operations by making use of a scalable shared cache. It treats multiple buffer caches as one thereby resolving data consistency issues. Cash fusion technology can provide more resources and increases concurrency of users.

What is the difference between Cloning and Standby databases?

The clone database is a copy of the database which can be opened in read write mode. It is treated as a separate copy of the database that is functionally completely separate.

The standby database is a copy of the production database used for disaster protection. In order to update the standby database; archived redo logs from the production database can be used. If the primary database is destroyed or its data becomes corrupted, one can perform a failover to the standby database, in which case the standby database becomes the new primary database.

Where are the Clusterware files stored on a RAC environment?

The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)

Where are the database software files stored on a RAC environment?

The base software is installed on each node of the cluster and the
database storage on the shared disks.

What is a raw device?

A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

What is the Cluster Verification Utiltiy (cluvfy)?

The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.

How do I identify the voting disk location ?

#crsctl query css votedisk

How do you backup voting disk ?

#dd if=voting_disk_name of=backup_file_name

How do I identify the OCR file location

check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

What is SCAN?

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

What is VIP used for?

It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.