Here we have listed out some of common interview questions asked in  oracle DBA interview. We are trying our best to include QAs from different topics of oracle database.

1. What is the difference between database and instance
Oracle database is consists of physical files like datafile, redo logs, tempfile, undo file and controlfiles.
Whereas Oracle instance is consists of SGA(shared global area) and different background processes.

2. What are the different phases of database startup?

Startup nomount:In this phase, the database reads the initialization parameters (pfile/spfile).If any invalid parameter defined in the pfile , then it will throw error.
Startup mount:In this phase the database checks the consistency of control file which records all the physical structures of the datase files.

Startup open:During this phase the database tries to start in open mode i.e (read,write) for end users. Here it will check the consistency of the the datafiles and redologs .  In case of any inconsistency it will try to recover the database from redologs.

3. What are the different states of a redolog member and its significance.

We can find the status of redologs from v$log.

select group#,status from v$log;

Different status of redolog are:

UNUSED– Online redo log has never been used. This is the status of a redo log that was newly added, or just after a RESETLOGS, and not being used yet.
CURRENT– Means this redo log is currently getting written. This implies that the redo log is active.
ACTIVE– Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING– Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING CURRENT- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE– Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
3. What do you mean by multiplexing controlfile and how to achieve this.
4. What are the different partitioning techniques in oracle db .
5. What is global and local partitioned index.

6. What are the methods of converting non-partitioned table to partitioned table and explain on this.
4. What is an ACL.

From 11g onwards, to access network packages like utl_mail,utl_http,utl_smtp, we need to have addtional privilege through ACL.

You can check the below link for usage of ACL, for sending mail using utl_mail.

how to send mail using utl_mail in oracle 11g

5. How to make password of an user non-expiry.

Password parameters are defined in profiles. So to make an password non-expiry, First we need to create a profile with password_life_time set to UNLIMITED and assign that profile to the user.(This user will inherit all the password limits of the profile)
6. How to drop a private database link, when you are not the owner.

REFER LINK – https://dbaclass.com/article/how-to-drop-a-private-database-link-from-sys/

7. What happens when we open the database in resetlog mode.

We should reset the online logs, when we do incomplete recovery or recovery through backup control file. When we do open resetlog, a new incarnation number is generated for the database. Incarnation details can be viewed in v$database_incarnation.

 

8. What is SCN in oracle?

SCN Means, system change number. SCN is the logical point in time at which changes are made to a database. Oracle assigns every committed transaction a unique SCN. The database uses these SCNs to query and track the changes. For example, if a transaction inserts a row, then the database records the SCN at which this delete occurred.

 

9. What is buffer cache?

Oracle keeps copies of database blocks in an area of the SGA known as the buffer cache. The cache may hold more than one copy of a block from different points in time, and may contain ‘dirty’ blocks – ie: blocks which have been updated but not yet flushed back to disk.

 

10. What is buffer cache hit ratio?

The buffer cache hit ratio measures how many times a required block was found in memory rather than having to execute a expensive read operation on disk to get the block.

A good buffer cache hit ratio is generally considered when it is > 80.

11. What is library cache? What is its functionality?

12. What is the TEMPORARY tablespace and its use?

As the name suggests, Temporary tablespaces is used for  tasks such as sort operations for users and sorting during index creation. User cannot  create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of a user’s session and the temp space is released, once the session is closed.

13. Difference between pfile vs spfile.

 

The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile would only take effect when the database is restarted.

The spfile is a server-side initialization parameter file and it permits dynamic changes without requiring you to bring down the instance.

 

14. How to check whether database is using pfile or spfile.

1) SQL> SELECT name,value FROM v$parameter WHERE name = ‘spfile’;

NAME VALUE
———- ————————————————–
spfile /fsys1/oracle/product/9.2.0/spfileTEST.ora
2) SQL> show parameter spfile;

 

15. What is the default block size in database? Can we use different block sizes and how?

 

Starting from oracle 10g, the default block size(DB_BLOCK_SIZE) is 8Kb.  In previous versions it was 2Kb.

To use different block size, use the parameter DB_nK_CACHE_SIZE .

For using 2K block size, set db_2k_cache_size=2G in the init pfile and bounce the database.

Similarly for using block size of diffent type, below can be used.

DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

16. What happens when we open the database with resetlog?

We need to open the database using resetlog option after an incomplete database recovery. 

SYNTAX- ALTER DATABASE OPEN RESETLOGS;

This command will do below things in the database.

1. It recreates the online redologs and reset the log sequence to 1.
2. Updates all the datafile and online redolog files with new resetlog timestamp and scn.
3. New incarnation number is generated.

Note – It is always recommended to take a fresh full DB backup after opening the database with resetlog.

17. What is checkpoint and when it happens?

When we do any changes to the database and commit , then modified blocks are not written to datafile directly,Instead they are written to redolog.And checkpoint is the action, when these modified(committed) blockes will be flushed to the datafile. These blocks are also known as dirty blocks.
A checkpoint number is the SCN number, at which all the dirty blocks are written to datafile(disk).
Below events make a checkpoint to occur:

1. When a redo log switch happens.
2. When DBWR writes all dirty buffers from SGA to datafiles.
3. When we execute alter system switch logfile (or) alter system checkpoint;

 

18. What is SCN in oracle?

SCN is system change number. Oracle assigns unique number is each committed transaction in the database. SCN value gets incremented with each transaction.SCN information is updated in controlfile.

Below queries to find SCN:

select dbms_flashback.get_system_change_number scn from dual;

select current_scn from v$database;

 

19. What is a relation between GLOBAL_NAME parameter and DB_LINK?

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.  GLOBAL_NAMES is either set to FALSE OR TRUE.

If the value of GLOBAL_NAMES is FALSE, then any name can be given to DB_LINK. If the value is TRUE, then database link name should be same as that of the database it is pointing.

20. What is SYSTEM tablespace and what it contains?

SYSTEM tablespace is the first tablespace which is created while creating a fresh database.

It stores the data dictionary i.e the metadata ( Means data about different objects in the database).

This is a mandatory tablespace cannot be dropped. 

21. What is SYSAUX tablespace and what it contains?

As the name SYSAUX says, it is an auxiliary tablespace to SYSTEM tablespace. It contains non-sys default data about different components of the database like OLAP, WORKSPACE MANAGER, TEXT SEARCH, DATA MINING, RECOVERY CATALOG, OEM, SPATIAL ETC.

This tablespace was introduced in Oracle 10g to reduce to workload from SYSTEM tablespace. Like SYSTEM tablespace also gets created by creating the database and it cannot be dropped.

22. What do you know by SGA and its purpose?

• Database buffer cache
• Redo log buffer
• Shared pool
• Java pool
• Large pool (optional)
• Streams pool

23. What is  PGA and its purpose?

Below are the components of PGA.

 

 

Persistent area – >It contains bind variable values.

A work area -> is a private allocation of PGA memory used for memory-intensive operations. For example,

Sort area –  > a sort operator uses the sort area to sort a set of rows. S

Hash area – > Similarly, a hash join operator uses a hash area to build a hash table from its left input,

bitmap merge area -> A bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

24. What is the job of DBWR process and how many DBWR processes are there?

DBWR Means, database writer is mainly responsible for writing modified blocks(ie. dirty blocks) to the physical data files(disks).

For below events, DBWR writes to datafiles.

• Checkpoint is issued
• Too much dirty buffers in Buffer cache
• No free space in the buffer cache
• During database shutdown(Except abort method)
• Tablespace being dropped (or)taken offline(or) placed in read only mode, (or) in hot backup mode.

The parameter DB_WRITER_PROCESS controls the number of DBW processes you want to use. It can be from 1 to 20. Using more number of DBWR process will increase the write performance. However, it will increase the CPU usage on the DB server.

The default value of DB_WRITER_PROCESS is 1 or cpu_count/8 , which ever is greater.

25. What is the job of LGWR process?

LGWR, i.e log writer writes the contents of the redolog buffer to an online redo log file.

LGWR writes when any of the below event occurs.

• When a commit is issued
• Every 3 seconds
• When 1/3rd of redo buffer is full
• Before DBWr writes to disk.

26. Can we have multiple DBWR and LGWR process?

We can have multiple DBWR process , using the parameter db_writer_process. But there is by default one lgwr process. However from 12c onward,  two additional log writer slave process are introduced( ora_lg00,ora_lg01). 

Recommended value for db_writer _process =2 or cpu_count/8

There is hidden parameter _max_outstanding_log_writes , that controls the number of log writer slave processes. Default value is 2. These slave processes are technically called outstanding log writer process.

27. Is there any dependency between DBWR and LGWR?

When dbwr wants to write to datafiles, if it feels that some of redo information  in log buffer has not been written to redo log file, then DBWR will ask LGWR, to copy redo information to redo log file first, so that DBWR can write dirty buffers to disk after that. 

28. What is the function of CKPT process?

Responsible for updating the headers of the datafile, during checkpoint.

29. What is the function of SMON process?

SMON (System Monitor) background performs below tasks like

• It performs instance recovery(including crash recovery during instance startup)
• It cleans up unused temporary segments for proper space utilization 
• Shrink Undo segment

30. What is the function of PMON process?

PMON (Process Monitor) performs below tasks like

• cleanup dead processes and sessions
• terminate sessions which exceed idle timeout limit
• managing job queue processes 
•  responsible for monitor other background process and restart or stop if necessary

31. What is the function of MMON process?

MMON i.e Manageability Monitor ,background process which performs tasks like taking AWR snapshots and performing ADDM analysis.

32. How instance recovery works in oracle database?

                   When an instance terminated as abnormally or crashed the database goes down in an inconsistent state that
means all ongoing transactions committed or uncommitted were not completed.So before it can be opened, the database must be in a consistent mode.
Hence SMON performs critical role in recovering the database.  Oracle using last SCN in the control file and will apply committed or uncommitted transaction from Redo logs, which is known as roll forward. In this state database is in MOUNT state . Database then checks the accessibility of UNDO segments and opens the database. Now uncommitted transactions are rolled back with the help of UNDO ,which is called ROLL BACK.

33. Difference between dedicated server vs shared server configuration in oracle db?

34. What is oracle bequeath connection?

local connections are known as bequeath connection. I.e when we connect to database from the same db server like sqlplus / as sysdba. then it creates a bequeath connection.

35. What is user global area? and where it is located?

36. What is a direct path read?

direct path read I/O operation reads data into the session’s PGA instead of the SGA.

37. What is dynamic sampling?

38. Can we create local or global partitioned Index on a non-partitioned table?

We cannot create a local partitioned Index on a non-partitioned Table. But we can create global partitioned Index on non-partitioned Table.

39. Explain different methods to convert a non-partitioned table to a partitioned Table. 

40. Why partitioning is required?

41. What is High water mark?

42. What is the difference between Delete and truncate?

Delete will not change high water mark of a table. If truncate will reduce the HWM.

Also , we can restore deleted data , if it is present in. undo. But truncate cannot be restored at all.

43. What happens when you put the database in hot backup mode?

When we do the database or tablespace in host backup mode,

• DBWn checkpoints the tablespace (writes out all dirty blocks as of a given SCN)
• CKPT stops updating the Checkpoint SCN field in the datafile headers and begins updating the Hot Backup Checkpoint SCN field instead
• LGWR begins logging full images of changed blocks the first time a block is changed after being written by DBWn

 

Why redo generation is high during hot backup:

Full block image logging during backup eliminates the possibility that the backup will contain unresolvable split blocks. To understand this reasoning, you must first understand what a split block is. Typically, Oracle database blocks are a multiple of O/S blocks. For example, most Unix filesystems have a default block size of 512 bytes, while Oracle’s default block size is 8k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 8k chunks or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, your process is reading in O/S-block-sized increments. If DBWn happens to be writing a DB block into the datafile at the same moment that your script is reading that block’s constituent O/S blocks, your copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block. By logging the full block image of the changed block to the redologs, Oracle guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the archivelogs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved by overlaying them with the block images from the archivelogs. All of these mechanisms exist for the benefit of the backup copy of the files and any future recovery. They have very little effect on the current datafiles and the database being backed up. Throughout the backup, server processes read datafiles DBWn writes them, just as when a backup is not taking place. The only difference in the open database files is the frozen Checkpoint SCN, and the active Hot Backup Checkopint SCN.

44. Does DBWR write uncommitted data to datafiles?

Yes dbwr writes uncommited data to datafile. If  the buffer cache is full , then uncommited data will be written to the datafiles.

45. When checkpoint happens in the database?

NA

46. What are the different types of Buffer states in Buffer cache?

Free Buffers –  Means buffer data is same as that of block in disk and it has not been changed.

Dirty Buffer – the buffer has been has modified , but not copied to disk yet

Pinned Buffer –  Buffers which are currently in use by active session.

47. Which db parameter defined the buffer pool/cache size?

48. What is the parameter db_keep_cache_size?

49. If I flush the buffer cache, what will happen to the uncommitted transactions in buffer?

       If we flush the buffer cache, then all the uncommitted transactions , i.e dirty blocks will be written to disk. We can flush the buffer cache manually using command ALTER SYSTEM FLUSH BUFFER_CACHE; 

Important pont to

50. If i issue alter system flush buffer_cache command in a live production database with huge number of transaction, then what will be the impact?

     This is not recommended to do in production. As all the buffer data will be flushed and the next transaction have to do physical read on disk, i.e it will have impact on the i/o for some time.

51. What if i flush the shared pool in a live production database? Will there be any impact?

If we flush the shared pool then all the queries need to do hard parsing, and it will slow down the database trasnaction for sometime, till the hard parsing count has been decreased.

52. In RAC, how can you flush shared pool  or  buffer cache of all the nodes in one command?

 Just need to add global keyword at the end of the normal command.

alter system flush buffer_cache global;

alter system flush shared_pool global;

53. What is FREELIST and FREELIST GROUP?

54. What is an ITL?

ITL Means interested transaction lists.

55. What is row chaining ?

Row chaining occurs when one row cannot fit in a single block and it has to span into multiple blocks. This usually has serious implications, and we need to avoid this.

56. What are the different types of checkpoints in oracle?

57. What is static registration and dynamic registration in listener?

58. Are you aware of private redo? What is its use?

59. What is in-memory undo?

60.   Are redo entries for undo segements stored in redo log file?

Yes undo related redo entries are stored in redo log file also. Just like whenever changes to datablock or index block changes are written to redo record. Similarly undo records changes also written to redo log.

Why?

During Recovery, part of roll forward process is to re-create undo segments. Just like data and index , undo segments are also rollforwarded . As the rollforward completed, Now the data file blocks contain both committed and noncommited changes. And the undo segements also contain commited(inactive) and uncommited(active) changes.  And the undo segment header block contains the transaction table. So rollbackward process will access this information and rollback the uncommited transactions.

So it is necessary for the oracle to keep undo related redo in redolog file.

61. Does dml activities on temporary table generates Undo and redo?

 

62. What is force logging in oracle? In which scenarios we need to put the database in force logging mode?

If force_logging is enabled in the database/tablespace level, then logs will be generated even for nologging operations. Usually In dataguard setup, we enable force_logging .

63. What is the difference between force logging and supplemental logging?

64. What is shared pool? What are the components inside shared pool?

Components are:

Shared pool is a  part of SGA. Below are the components of shared pool.

Library Cache: Parsing is done here.

dictionary Cache: – > Information like table/index definition , referential constraint relation etc.  So whenever user want to access a table defination, it gets the details from dictionary cache, instead of hitting the system datafile. This is also known as row cache, because this cache contains rows instead of full blocks.

Result Cache: stores result set .

 

65. Explain about soft parsing and hard parsing of queries?

66. Explain the mechanism how sql query decides whether to do hard parsing or soft parsing in library cache?

 

67. What you know about parent cursor and child cursor?

Child cursor – > A parent cursor can have more than one child cursor.

It stores information about, execution plan, bind value, environmental setting etc.

Two textually same sql statements will have have one parent cursor. But can have different child cursor.

 

68. What is session cursor cache?

We can also call this as private cursor Caches.

When a statement is issued, after going through syntax and symentic check, it searches the library cache for existing cursor.If the cursor is not present, then it creates a new cursor for the statement( which is a resource intensive operations). But if the cursor is already present, then it will do soft parsing( less expensive then hard parsing). But still these soft parsing need to use some resource for searching and handling with latches .

Now when session cursor cache is enabled, sessions cursors of repetitive statements will be stored in the session cursor cache(i.e PGA/UGA). So now the session cursor cache contains a pointer into the library when the cursor was closed.  So when a sql is resubmitted, the syntax and symantic checks are bypassed( as the presence of the cursor in session cursor cache guarantee this).

So when the query is submitted, if the cursor is closed, then it will be a soft parse( but without syntax and symentic check)

But if the cursor is open, then it will skip the soft parse also.

Lets explain with an example.

We have a pl/sql block,inside which we need to execute a sql 100 times.

Without session cached cursor, soft parsing will happen around 100 times.(1st one might be hard parsing)

But with session cached cursor, parsing will be skipped completely( once the cursor is opened after 1 or 2 executions).

We can say, good value of session_cached_cursor can help in reducing library cache latch contention.

However too much session_cached_cursor value when no. of sessions are very high can cause ORA-4031 out of memory issue.

69. Are hash_value and plan_hash_value both are same or different? Explain more on this.

hash_value is generated for a sql statement(text).

70. Can two queries have same hash_value but different plan_hash_value?

71. Can two queries have same plan_hash_value but different hash_value?

72. Lets say for the query select * from EMP, cursor is present in library cache? Now i have dropped a column from EMP table? What will happen  now?

If any DDL operation happened on the table, then cursor the all the statements using this table will be invalidated. So next time the query hits library cache, it need to do hard parsing again.

73. How do i know whether a specific query is doing soft parsing or hard parsing?

We can check the parse_call column in v$sql table, If the parse_call is 1 means, it was getting parsed for the 1st time.

So if parse_call is 1, then it is doing hard_parse.

74. Explain different values of cursor_sharing parameter? What is the default value? How this parameter behaves with different values?

cursor_sharing=EXACT

75. Spinning on a latch impact CPU time or wait Time?

Spinning impacts CPU TIME.

76. What is the difference between oracle foreground events and background events?

Foreground events are those which happens due to the server process like row lock contention, buffer busy wait etc.

But background events are those which occurs due to the activities of background processes like , LGWR, DBWR etc.. Event examples are log file parallel write etc.

77. What is the difference between latches and locks.

78. What is the difference between latches and mutex.

79. When crash recovery happens and how oracle determines whether it needs crash recovery or not?

Crash recovery happen we try to open the database after instance has been terminated abruptly.

So while opening the database, it checks the SCNs of each datafile and SCN in control file.  If the SCNs are same, then database is consistent and will be opened without any need of crash recovery. But SCNs are different , then crash recovery will happen . Redo will be used to roll forward the database by processing both commited and uncommited transactions. After that uncommited transactions will be rolledback with the help of UNDO.

Again, there will another question –  > Why scn of datafile is different from scn of controlfile ?? Because For any changes happens in the database the scn will be incremented. But the scn in the datafile headers will be updated, only when checkpoint happens. So till checkpoint is not happened scn will be differnt from datafile scn.

80. What happens when we issue commit?

• scn is incremented
• data from log buffer is written to logfile.
• locks will be released.

81. Difference between small file tablespace and big file tablespace  . Explain pros and cons of both.

Bigfile tablespace

A bigfile tablespace consists of a single datafile ,which size can be extended upto 32 TB. This is useful in very large environments. Because no. of datafiles in db is controlled by db_file parameter , creating a big file tablespace of large sizes can help in this.

Also we can resize the tablespace using alter tablespace command, instead of alter database datafile command in case of smallfile tablespace.

It is always recommended to use big file tablespace with ASM ( which provides striping method ,otherwise parallel processes might impacted).

small file tablespace

It contain lot of datafiles(upto 1022) with each size of max 32GB. It gives us the flexibilty to create datafile in different directory or asm diskgroups .

But only thing is , if we want to resize a tablespace, We cannot use alter tablespace. Rather we need to resize each datafile of that tablespace individually.

 

82. Explain structure of a block.

86. What is granules in SGA?

113.  What  will be the impact after db  upgrade. If i am not doing the timezone upgrade.

No impact

114.  What is sql quarantine?

115.  Is it recommended to enable huge pages and transparent hugepages in Linux for  running oracle database? Explain 

huge pages should be enabled , But transperant hugespages should be disabled.

Why hugepages:

By default operating system memory page is of 4KB. But with hugepages we can use memory pages greater than 4KB .Keeping large page size will help in minimum resource requirement for managing the page table entries. The hugepage can vary from 2MB to 256 MB.

Why not transperant hugepages:

If transperant hugepages is enabled, then the memory will be  allocated during the run time and it might cause delay in allocation.

So oracle recommended for standard hugepages, with which memory will preallocated ,while starting the instance.

116.  Why dbwr is called as lazy writer?

117.  Difference between force logging and supplemental logging?

Force logging means , even nologging operations will be logged. Supplemental logging means, additional information of tables will be captures in the redolog, ( mostly used for goldengate extracts).

118.  Are you aware of touchcount concept in lru?

119.  What is endian format?

Endian is the storage method of multi-byte data types in memory. In other words, it determines the byte order of the data.

There are two kinds of endian,

Little – > Means end byte will be smaller( with hexadecimal represenatation)

Big – > Means end byte will be bigger( with hexadecimal represenatation)

120.  What is persistent area in PGA?

It stores the bind values of sql queries.

121.  Difference between db_name and db_unique_name?

In dataguard environment, All instances will have same db_name and different db_unique_name .

122.  What is db_flashback_retention_guarantee?

123.  A user was running a select query , it is expected to take 1 hour. But while the  query was running for 30 mins only, another user B deleted some data and committed. So Now will the user A see those deleted data?

Yes user will see those deleted data. It will see all the data as it was at the time of starting the transaction.

124.  In which scenarios we need to open the database in resetlog .

1. After doing an incomplete recovery
2. After flashback the database
3. After doing rman cloning

There might be some more scenarios .

 

125.  We know both pga and temp tablespace is used for sorting. So what is difference between thse two?

sorting is always done in pga . But if the pga becomes full, before sorting operation is completed , then pga content is swapped from pga to temp. when it is required, it will be read back to pga.

126.  Does open resetlog , resets the SCN to 0 or 1?

1

127.  Does the db upgrade time depends upon the size of the database? I mean will upgrading 5TB db will take more time than upgrading a 10gb database?

No, size doesn’t matter for upgrade. But upgrade time depends upon the number of components present in the database.

128.  Does applying patch on a 1TB database take more time than applying patch on 50 GB database?

patching time doesn’t depends upon size of the database.

 

129.  What do you mean by pinning in oracle? like cursor pin, buffer pin?

If a buffer in the buffer cache is getting changed, first it gets pinned to ensure, other processes don’t replace this buffer.

Same is for cursor. If a statement is getting executed, then its cursor gets pinned, so that cursor memory dont gets deallocated

 

130.  what is the difference between alter system kill session and kill -9 <pid>?

131.  what is the difference between rman and expdp backup?

132.  what happens internally when you ran gather stats?