This article contains the most commonly asked top 80 oracle dba interview questions and answers
Question 1. What is oracle database ?
Answer: Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other
Question 2 What is the difference between Oracle database and Oracle instance?
Answer:Oracle database is the collection of datafiles,redologs and control files while Oracle instance is the SGA ,processes in the Memory.
Question 3 What is a Tablespace?
Answer: Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema’s and a schema can have multiple tablespace. Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.
Question 4 What are Datafiles?
Answer:The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
Question 5 what is Control Files?
Answer:Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files.
Question 6 What is Redo Log Files?
Answer The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.
Question 7 What is Archive Log Files ?
Answer:Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.
Question 8 What is Parameter Files (initSID.ora)
Answer:Parameter files contain a list of configuration parameters for that instance and database.
Question 9 What is schema?
Answer:A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema’s. We can create a new Schema/User. But we can’t drop default database schema’s.
Question 10 What is data blocks ?
Answer:Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk.The data blocks can be 4 K,8 K size depending on the requirement.
Question 11. What is an Extent ?
Answer:Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.
Question 12. What is a Segment ?
Answer:A segment is a collection of extents which is used for storing a specific data structure and resides in the same tablespace.
Question 13. What is Rollback Segment ?
Answer:Database contain one or more Rollback Segments to roll back transactions and data recovery.
Question 14. What are the different type of Segments ?
Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.
Question 15. What is archive-log and No archive log mode?
Answer: We all know that redo logs stored the redo information and redo log files are in circular fashion.Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving.
The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. No archive log means archive log are not generated and redo are overwritten
Question 16. What all things are present in the shared pool?
Answer: The shared pool portion of the SGA contains three major areas:
library cache(contains parsed sql statements,cursor information,execution plans)
dictionary cache (contains cache -user account information,privileges information,datafile,segment and extent information)
buffers for parallel execution messages
Question 17. What is hotbackup?
Answer:If the database must be up and running 24 hours a day, seven days a week, then you have no choice but to perform inconsistent backups of the whole database. A backup of online data files is called an online backup. This requires that you run your database in ARCHIVELOG mode.
Question 18. which views is used to finding the locking in the database?
Answer: v$lock, v$session, v$process
Question 19. You have many instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
There are two ways
SQL> oradebug setmypid SQL> oradebug ipc SQL>oradebug tracfile_name
Another way is to use
Question 20. What is Database index ?
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes.
Oracle Indexes and types of indexes in oracle with example
Question 21. What is difference between the hotbackup taken through RMAN and Manual?
Answer check the below link for Detailed explanation
Question 22. What is library cache lock?
A library cache lock means that a session is waiting to use or change an object definition or to use a SQL statement that another session is loading, changing or parsing, or is waiting to change or parse. This usually indicates that database object definitions are being changed regularly.
Another example is gathering statistics on an object. When statistics are gathered all references to that object in the shared pool or library cache become invalid, requiring a new hard parse for each SQL statement referencing the object. Statistics should only be gathered when there are no active users or system activity is very low.
Question 23. How do we find the blocker for Library cache lock?
We can run hanganalyze to find the blocking session.
Many times the below query also works in wonderful manner
select /*+ all_rows */ w1.sid waiting_session, h1.sid holding_session, w.kgllktype lock_or_pin, w.kgllkhdl address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held, decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr and h.kgllkuse = h1.saddr /
Question 24. How to take global hanganalyze dump?
ORADEBUG setmypid ORADEBUG setinst all ORADEBUG -g def hanganalyze <level>
Question 25. How do you recover the database if you lost one of the controlfile in the database?
Shutdown Instance ( abort )
sqlplus / as sysdba
Change Init.ora file to remove the lost controlfile or copy the existing controlfile to that location
startup the database
Check below link for all the useful scenario for recovery
Question 26. How do you recover the database if you lost all of the controlfile in the database?
a. Shutdown Instance (abort)
b. If the control-file backup is available ,then restore it from backup or if you have got the controlfile information in trace using alter database backup controlfile to trace
c. Once the controlfile is created, recover database using backup controlfile. You will need to apply the redo logs to complete the recovery
d, alter database open resetlogs;
We can avoid the resetlogs by using the steps below
1. After the database is mounted with restore of controlfile from backup,create a trace of the controlfile using the command below
alter database backup controlfile to trace;
2. Now take out the create controlfile statement from the trace. Choose the NORESETLOG portion
3. Recreate the controlfile using the above portion.
4. Do recover database
5. alter database open
Check below link for all the useful scenario for recovery
Question 27. What is flashback database?
It is New feature in Oracle database post 10.1 onwards. It Uses past block images to back out changes to a oracle database. As the name suggest, we can use this flashback database in previous time
a.During normal database operation, Oracle occasionally logs past block images in flashback logs
b.Flashback logs are written sequentially not archived
c. Oracle automatically creates, resizes and deletes flashback logs in the flash recovery area
d. DBA should be aware of flashback logs to monitor performance,to decide how much space to allocate to flash recovery area
e. Allows database to be recovered to a previous time to correct problems caused by logical data corruptions,user errors
Flashback database explained and limitation
Question 28. How can you rebuild an index?
Answer: We can rebuild the index using the below command
If it is to be online
alter index index_name rebuild online;
If it is to be rebuild offline
alter index index_name rebuild;
You can use parallel to speed up the rebuild
alter index index_name rebuild parallel 4;
Question 29 What is Branch Block in index ?
Answer: Branch block rows hold <separator key,dba> pairs used to guide the B-tree search
to a row in a leaf block.
Question 30. What is Leaf Block in index ?
Answer: Leaf block rows hold the <KEY, KEYDATA> pairs stored by the B-tree.
Question 31. What is High Water Mark in Oracle?
- High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.
- Delete Table operation won’t reset HWM.
- TRUNCATE will reset HWM.
- The high water mark level is just a line separate the used blocks and free blocks.
The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.
Question 32. What parameters are used to set parallelism in the database?
Answer:Following initialization parameters are required for parallelism setup in database.
PARALLEL_SERVER, PARALLEL_SERVER_INSTANCES, PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS PARALLEL_THREADS_PER_CPU
Question 33. If the table is fragmented, how would you rebuild it?
First we need to rebuild the table
alter table <table_name> move;
Secondly we need to rebuild all its indexes
We can find all the indexes
select index_name from dba_indexes where table_name=<table name> and table_owner=<table owner> alter index <index_name> rebuild
Finally we should gather the stats on the table
Question 34. What view would you use to determine free space in a tablespace?
Question 35. How do you switch from an init.ora file to a spfile?
Create spfile from pfile;
It will start using spfile
Question 36. You are experiencing high “busy buffer waits” . how can you find what’s causing it?
Answer Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache.There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo, data block or segment header wait.
Run the following query to find out the p1,p2 and p3 of a session causing buffer busy wait
sql> select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait where event = 'buffer busy waits';
After that running the following query to find the segment causing buffer busy wait:-
sql> select owner,segment_name,segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1
Question 37. How to kill the database session?
First you need to find the SID,serial# of the session select SID,SERIAL# from v$session where <>; alter system kill session 'SID,SERIAL#'; In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete.So Upon issuing the alter system kill session command, the session will be 'marked for kill'. It will then be killed as soon as possible For Oracle RAC If you want to kill session from another instance ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
Question 38. What is difference between startup mount and startup nomount?
Answer. startup mount -mount the control file
startup nomount- does not mount the controlfile
Question 39. How to check Oracle database version?
Select * from v$version;
The below matrix explains the number of the Oracle version
|Major Database Release Number||The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.|
|Database Maintenance Release Number||The second digit represents a maintenance release level. Some new features may also be included.|
|Application Server Release Number||The third digit reflects the release level of the Oracle Application Server (OracleAS).|
|Component-Specific Release Number||The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.|
|Platform-Specific Release Number||The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.|
Lets take the example of Oracle version 10.2.0.4.0
10 – Major database release number
2 – Database Maintenance release number
0 – Application server release number
4 – Component Specific release number
0 – Platform specific release number
Question 40. What is SCN (System Change Number) ?
Answer The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file
Additional Oracle DBA questions/oracle database interview questions and answers for experienced professionals
Question 41. How to apply patch to Oracle database Home software?
Answer: Oracle database Home software is patched using OPATCH utility
cd <patch no> opatch apply
opatch rollback -id <patch no>
Question 42. How to find Last password change of a user?
select du.username, du.profile, du.account_status, u.ptime last_pwd_change from dba_users du, sys.user$ u where du.username = u.name and du.username='&1' order by 2, 4 /
Question 43. What is Physical Block Corruptions?
This kind of block corruptions are normally reported by Oracle with error ORA-1578 and the detailed corruption description is printed in the alert log.
Corruption Examples are:
Bad header – the beginning of the block (cache header) is corrupt with invalid values
The block is Fractured/Incomplete – header and footer of the block do not match
The block checksum is invalid
The block is misplaced
Question 44. What is Row chaining ?
Row chaining happens when a row is too large to fit into a single database block.
For example, Suppose you have 4 KB block size for your database,and you need to insert a row of 8 KB into it, Oracle will use 3 blocks and store the row in pieces.
In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment.So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.And Row Chaining happens only when the row is being inserted and whenever it has inserted it cannot be chained.
In most cases chaining is unavoidable, especially when this involves tables with large columns such as LONGS, LOBs, etc. When you have a lot of chained rows in different tables and the average row length of these tables is not that large, then you might consider rebuilding the database with a larger block size.
Tables with more than 255 columns can potentially force Chaining
Question 45. What is Row Migration ?
We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the the «forwarding address». So, the original block just has the ROWID of the new block and the entire row is moved.
Question 46. How to find the physical location of the datafiles,redo logs,controlfile?
Prompt Control file Locations select name from sys.v_$controlfile / Prompt Redo logs Locations col Grp format 9999 col member format a50 heading "Online REDO Logs" col File# format 9999 col name format a50 heading "Online REDO Logs" break on Grp select group#,member from sys.v_$logfile / Prompt Data Files Locations col Tspace format a25 col status format a3 heading Sta col Id format 9999 col Mbyte format 999999999 col name format a50 heading "Database Data Files" select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte, decode(F.status,'AVAILABLE','OK',F.status) status, F.tablespace_name Tspace from sys.dba_data_files F order by tablespace_name;
Question 47 How to generate the AWR report
Oracle Database 12c Related questions and articles are given below
Question 48 What is the difference between CPU and PSU patching?
The CPU are the Critical Patch Update, we can summarize it as the Security bugs fixes, The new name for the critical patch updates is security patch update (SPU). the PSU contains the CPU + some technical bugs fixed.
For my point of view, it is better to apply PSU as it contains the CPU and the some others Important bugs fixes).
Question 49 How to verify whether a parameter changed required database bounce or not?
In v$parameter we can find one column ie. ISSYS_MODIFIABLE. This column contains three phases 1) Immediate 2) DEFERRED 3) False
Immediate : We can change the parameter in fly database i.e Dynamic.(only need to change the value no need to bounce)
DEFERRED : We can change the parameter in fly database but this will effect after restart the database only.(here we need to edit using spfile and bounce the database)
False : Compulsory we need to down the database i.e Static.
Question 50 How do we check if a Database upgrade is successful?
select COMP_NAME,VERSION,STATUS from dba_registry; COMP_NAME VERSION STATUS ------- ------ ------ Oracle XML Database 126.96.36.199 VALID Oracle Text 188.8.131.52 VALID
Question 51 How to find Database version ?
SQL> select * from v$version;
The command returns the release information,
Question 52 How to find opatch Version ?
opatch is utility to apply database patch , In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version”
Even if you simple write $ORACLE_HOME/OPatch/opatch, it will show the opatch version in the start
Question 53 How to find out invalid objects in the apps schema in the database?
select count(*) from dba_objects where status =’INVALID’and owner ='APPS'
Question 54 How you will see hidden files in linux/solaris?
Question 55 How to find that the database is 64-bit/32-bit?
Question 56 What is top command?
top is a operating system command, it will display top 10 processes which are taking high cpu and memory. 8. What is a patch?Answer A patch can be a solution for a bug/it can be a new feature.
Question 57 Which table you will query to check the tablespace space issues?
bytes column in dba_free_spaces and dba_data_files
Question 58 Which table u will query to check the temp tablespace space issues?
Question 59 What is temp tablespace?
Temp tablespace is used by so many application programs for sorting and other stuff.
Question 60 How to find the locks and what is the resolution?
we can find general locks with the following query:
|SELECT inst_id,DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess, id1, id2, lmode, request, type FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0)ORDER BY id1, request ;|
If it’s a dead lock, we need to kill that session.
Question 61 How to kill a database session?
alter system kill session ‘&sid,&sno’;
Question 62 How to apply a rdbms patch?
Go the patch directory
cd <patch num>
Question 63 How to find opatch is enabled or not for u r database?
If Opatch directory exists under RDBMS_ORACLE_HOME.
Question 64 What is the pre-req for applying a rdbms patch?
Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc and Oracle home must be registered in Central inventory
Question 65 What is Inventory?
The oraInventory is the location for the OUI (Oracle Universal Installer)’s book keeping. The inventory stores information about: All Oracle software products installed in all ORACLE_HOMES on a machine Other non-Oracle products, such as the Java Runtime Environment (JRE)
In a R12 Application system ,the RDBMS and iAS ORACLE_HOMEs and 10.1.2 are registered in the oraInventory.
Question 66 What are different types of inventories?
The Global inventory (or Central inventory)
The Local inventory (or Home inventory)
Question 67 What is Global inventory or central inventory?
The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc.The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs. The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone.
Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.
Question 68 What is local inventory?
There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME.The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.
Question 69. What is AWR?
AWR is a database utility to gather database and session level performance information.
Question 70 How to enable trace at database level?
set init.ora parameter sql_trace
Question 71 How to enable trace for a session?
Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at background dump dest directory
Question 72 How to enable trace for other session?
Example To enable trace for sql session with sid 9779
SQL> exec sys.dbms_system.set_sql_trace_in_session(9779,8767896,true);
PL/SQL procedure successfully completed.
To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(9779,8767896,false);
Question 73 .What is the location of init.ora ?
Question 74. What is that trace files contains and the utiliy used to read them?
Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.
Question 75 What is the syntax for tkprof?
tkprof explain=apps/ sys=no
Question 76. What is a database link? How to create it?
If we want to access objects of another database from this database then we need a database link from this database to the other.
1.Login as oracle user
2.sqlplus “/as sysdba”
3. create database link connect to identified by using ”;
create database link MY1_TO_MY2 connect to apps identified by apps using ‘MY2′;
Database link created.
SQL> select name from [email protected] MY1_TO_MY2;
SQL>select db_link from dba_db_links;
Add destination database tns entry in tnsnames.ora
Question 67 How to do database clone?
Read the below link
Question 78 How to find the database/sqlplus version?
select banner from v$version;
Question 79 How to find operating system version?
Question 80 . While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you will do, and how you will apply the patch?
We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory.
We can try to create to recreate the central inventory and If still it won’t work we will apply patch with the following command:
Opatch apply no_inventory
Top 10 oracle 12c new features
Understand Oracle Database 12c – Multitenant Architecture
5 Simple (But Important) Things To Remember About Oracle Database 12c views ,parameters and packages
Top Oracle 12c New Features for developers
Very useful 10 new things in 12c database
Basics Oracle dba questions and answers
Hope you like this compilation of core oracle dba interview questions and answers.This will be helpful to senior DBA & experienced DBA also. Please do provide the feedback
Recommended Udemy Courses for Oracle DBAOracle DBA 11g/12c – Database Administration for Junior DBA
Oracle 11g Certified Professional Exam Prep I
Oracle 11g Certified Professional Exam Prep III
OCA/OCP Oracle Database 12c All-in-One Exam Guide (Exams 1Z0-061, 1Z0-062, & 1Z0-063)Oracle Database 12c DBA Handbook (Oracle Press)Oracle DBA All-in-one Scripts – A guide every DBA must have: Oracle dba scripts collection used by expert database administrators everyday. Must have dba scripts for your daily activities! Related Articles
Must read unix shell scripting interview questions
Top 40 Oracle sql interview questions
Most commonly asked 25 oracle plsql interview questions
40 Important Weblogic Interview questions
60 Awesome oracle apps dba interview questions
19 oracle apps technical interview questions and answers
Download of oracle dba interview questions and answers as PDF is also given below
|Date:||December 16, 2017|