Oracle DBA Interview questions & material

1)Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode? benefits and disadvantages to each.

Ans : ARCHIVELOG mode is a mode that you can put the database in for creating abackup of all transactions that have occurred in the database so that you canrecover to any ball in time.
NOARCHIVELOG mode is basically the absence ofARCHIVELOG 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 towrite transactions to an archive log and thus increases the performance of the database slightly.

2) What command would you use to create a backup control file?

Ans : Alter database backup control file to trace.

3) How many redo logs should you have and how should they be configured for maximum recoverability?

Ans : 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

4)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.

5)What is Restricted Mode of Instance Startup?

Ans : 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

6)What are standby databases? Difference between Physical and logical standby databases

Ans : A standby database is a replica of the original database. In order to keep both the database synchronized, archived redo logs can be used. It is mainly used in disaster protection. It can also be opened in read only mode which allows it to be used independently for reporting.

A logical standby database allows new database objects like tables, indexes to be added to the database. On the other hand, the physical standby database is a physical or structural copy of primary database. They can be opened in read only for disaster recovery.

7)How many memory layers are in the oracle shared pool? Explain them

Ans : Oracles shared pool consists of two layers namely, Library cache and Data dictionary cache.
Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.

Data Dictionary cache: This Layer has information about the accounts of the users, their privileges and segments information

8)Explain the concept of the DUAL table.

Ans : On installing Oracle database, DUAL table Is present by default. It is a special table with just one row. It has a single column called DUMMY. The data type of this column in VARCHAR2(1). It has a value “X”. It is most commonly used to select pseudo columns in Oracle like sysdate.

Select sysdate from dual

9)How can we find how many users are currently logged into the database and their respective operating system id?
  • We can look at the v$session or v$process views
  • We can also check current user logins parameter in the v$sysstat view.
  • Or through Unix command ps –ef | grep oracle | wc –I ?
  • The above command works only for single instance database.
10)Can we reduce the space of TEMP datafile? How?
  • Yes, we can reduce the space of the TEMP datafile.
  • Prior to oracle 11g,,you had to recreate the datafile.
  • In oracle 11g you reduce space of TEMP datfile by shrinking the TEMP tablespace.It is a new feature to 11g.
  • The dynamic performance view can be very useful in determining which table space to shrink.

11)Explain the difference between a hot backup and a cold backup

  • A cold backup is done when there is no user activity going on with the system. Also called as offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made.
  • A hot backup is taken when the database needs to run all the time. It is an online backup. All files of the database are copied and there may be changes to the database during the copy.
12)Can we perform hot backup without being in archive log mode, can you recover in the event of a failure? Why or why not?
  • Basic is Archive log mode is essential for production databases where the loss of transaction is fatal,it is unnecessary in development and test environments.
  • Main reason of Archive log mode is 24/7 availability and guarantees complete data recoverability
  • As hot backups are done online it is necessary to enable archive log mode before one can start to use on-line database backups.
  • So, you can’t use hot backup without being in archivelog mode, so we couldn’t recover.

13)Describe in brief about oracle database tuning.

Oracle includes many performance tuning enhancements like:

Automatic Performance Diagnostic and Tuning Features
  • Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA
  • Wait Model Improvements - A number of views have been updated and added to improve the wait model.
  • Automatic Optimizer Statistics Collection - gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
  • Dynamic Sampling - enables the server to improve performance
  • CPU Costing - default cost model for the optimizer (CPU+I/O), with the cost unit as time
  • Optimizer Hints
  • Rule Based Optimizer Obsolescence - No more used
  • Tracing Enhancements - End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id
  • SAMPLE Clause Enhancements
  • Hash Partitioned Global Indexes

14)Explain how oracle optimizer works.

Ans : The optimization is determining the most efficient way to execute a SQL statement after considering the factors related to the objects referenced and the conditions specified in the query.This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

The optimizer first evaluates expressions and conditions containing constants.Then Statement transformation follows which is followed by Choice of optimizer approaches. Then is the Choice of access paths followed by Choice of join orders and finally the Choice of join methods.

15)How to set pga size, can you change it while the database is running?

Ans : show parameter pga_aggregate_target;
alter system set pga_aggregate_target=100m;

Yes the pga can be changed while the database is up and running.

16) What is an index, how many types of indexes you know? Why you need an index

Ans : Index is an oracle object which is used to retrieve the data much faster rather than scanning entire table.Typically this is like an index page in a book which contains the links to the pages,where we can go through easily through out the book.
If index page is not there,we have to search each and every page for our need,so we use indexes in oracle also to retrive the data quickly.

Types of indexes:
  • Btree index: Used for searches mostly when used select statements(Ex:pincode)
  • bit map index: when having low cardinolity (low priority) columns used in the statements.for example: gender column
  • function based index: sum(salary), upper(ename), lower(ename)
  • reverse index: used mostly to increase the speed of inserts (its like btree only but the key is reverse).
17) Define different types of tablespaces you know?

Ans :Permanent: system table space,sys aux,user
undo: to store undo segments
temp: to store the sort segments

18) How to find the datafiles that associated with particular tablespace? Ex: System
Ans : SQL> desc dba_data_files
SQL> select * from dba_data_files where tablespace_name=’SYSTEM';

19) What is the oracle block, can you explain?

Ans : Oracle block is the lowest level of storage structure where it contains the data(business/oracle data)
The block has divided into many sections starting from block header,row header,row directory,ITL list(Intrested Transaction List),free space.

20) What is the difference between v$ views and dba views?
  • dba views are static views
  • v$ views are dynamic views
  • dba views are available once the db is in open mode only
  • V$ can be viewed even the database is in mount state
21) What is row migration? When does it occur? Where can you find this information?

Ans : Row migration happens when update occurs at one column and the row is not adequate to fit in the block then the entire row will be moved to the new block.

select table_name,chain_cnt from dba_tables where table_name=’tablename';
Solution:
set pct_free storage parameter for table to adequate.;

22)What is profile? what is the benefit of profile? Where do you see the information of profiles? Provide an example of profile?

Ans : Profile is a set of properties assign to an user
For an example password complexity,password reuse,password expiry,idle time etc
SQL>desc dba_profiles;
SQL> select username,profile from dba_users;

23)What is fragmentation of table?

Ans : Fragmentation of a table is something when ever there is a purge or deletion of a table.Oracle will not use those unused blocks and always try to allocate the extents above high watermark.this leads the table to grow larger than its size.

24) What are latches?

Ans : latches are typically a kind of locks but held for very short time to protect the memory structures of the instance

25)What is checkpoint? why database need it?

Ans : checkponit which occurs when ever the redolog switch happens,during this ckpt process writes the check point information to control file and the data file header and tells to dbwr to flush the dirty buffer from buffer cache to disk until that check point.

26)What is the difference between Oracle home and oracle base

Ans : ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
SelectionFile type iconFile nameDescriptionSizeRevisionTimeUser