RR TECHNOSOFT

ARCHITECTURE

1. Explain briefly about Oracle database architecture

a. Oracle database architecture is a combination of instance and database. Instance is a
combination of memory structures and background processes which helps in reading
and writing the data to/from the database.
2. Which background process is used during user connectivity?
a. PMON
3. What are base tables? When and how they will get created?
a. Base tables are dictionary information of the database. They will be created at the
time of database creation using SQL>BSQ script.
4. What are different views DBA uses? Which script will create them?
a. We use data dictionary views to look into permanent information about the database
and dynamic performed after database and dynamic performance views to get
ongoing actions in the database. Both the views will be created after database
creation using catalog.sql script.
5. Why to execute catproc.sql script?
a. It will create necessary packages and procedures which DBA use for certain actions.
6. Explain the phases of SQL execution
a. SQL execution contains 2 phases.
i. Parsing – in which syntax checking, semantic checking and dividing the
statement into literals will be done.
ii. Execution – in which parsed statement will get converted into ASCII format
and will be executed.
iii. Fetch – in which data will be fetched either from database buffer cache or
database.
7. What is mean by semantic checking? Which component helps in that?
a. Semantic checking means checking for the privileges for the user or in other words
authorizing the user. Base tables or dictionary will help in doing this.
8. Explain how a select statement will process in the database
a. Answer is available in the class notes.
9. What is server process?
a. It is a process created to help the user process either in reading/writing the data in the
database.

DBA INTERVIEW CRACKS | version 1.0 1

 RR TECHNOSOFT

10. What is the difference between physical and logical read?

a. If we fetch data from database buffer cache, then its called logical read. If we fetch it
from database, its called physical read as it includes an I/O operation.

11.What is Segment?

a. A segment is a set of extents allocated for a certain logical structure.

11. Why to maintain a copy of data in database buffer cache?

a. When the same query is run by same or different user, data can be picked from buffer
cache thus avoiding I/O and improving performance.
12. Why server process will not start searching from MRU end?
a. If server process starts searching at MRU end, there is a chance that data may get flush
from buffer cache by the time it reaches LRU end. In that case again to fetch data, we
need to do an I/O which is costly. So oracle designed its architecture that server
process will search only from LRU end.
13. What are the logical structures of the database?
a. Tablespace , segment ,extent & oracle data block are logical structures.
14. What are the 4 mandatory tablespaces req to run the database?
a. SYSTEM,SYSAUX,TEMP and UNDO
15. Can I have a database without SYSAUX tablespace in 10g?
a. Yes, but load will be more on system tablespace
16. If we have physical structure, why do we need logical structures?
a. Logical structure are defined to provide the easeness in maintenance
17. Explain the difference between a block, extent and segment
a. Block is a basic storage unit where as extent is a small memory area allocated to a
table and segment is a object which occupies space.
18. Explain how a DML statement will process in the database.
a. Answer is available in the class notes.
19. What are redo entries? When and where they will be created?
a. A single automatic change happened to the database is called redo entry. They will be
created when we run any DML or DDL commands or when any changes are done in the
database. They will be created in PGA.
20. What is different status for a block in database buffer cache?
a. UNUSED,FREE,PINNED and DIRTY

DBA INTERVIEW CRACKS | version 1.0 2

 RR TECHNOSOFT

21. What is write-ahead protocol?

a. LGWR writing before DBWR is called write-ahead protocol.
22. Will there be any undo generated in case of DDL statement processing? If so why?
a. Yes. Reason in even though it is a DDL externally, it will be DML to base tables and to
rollback them always oracle requires undo.
23. What is PGA?
a. PGA is a memory area which is used to store user’s exclusive information like session
information and helps in executing a query.
24. Which is correct? Sorting will take place in PGA or temp tablespace?
a. In 8i, we need to set different individual parameters where as in 9i setting only one
parameter PGA_AGGREGATE_TARGET would be enough.
25. Which is correct? Sorting will take place in PGA or temp tablespace?
a. Both. If the data which to be sorted is more than sort area size of PGA, then temp
tablespace will be used.
26. How you will define INSTANCE and DATABASE?
a. INSTANCE is a combination of memory structures and background processes which
helps in reading/writing the data. DATABASE is mix of physical and logical structures
which helps in storing user data.
27. What is SGA ?
a. SGA is a combination of different memory structures which helps in several actions in
the database.
28. What are the responsibilities of SMON?
a. It will do instance recovery, coalesces the tablespace and will release the temp
segments occupied by a transaction when it is completed.
29. How SMON will perform instance recovery?
a. Answer is available in the class notes.
30. Why oracle maintains 2 redolog files?
a. To avoid any space constraints
31. What is a logswitch? What happens when it occurs?
a. Answer is available in the class notes
32. What are the activities of PMON?
a. Answer is available in the class notes
33. What are the situations when DBWR and LGWR will write?
a. Answer is available in the class notes

DBA INTERVIEW CRACKS | version 1.0 3

 RR TECHNOSOFT

34. What is SCN and which process writes that?

a. SCN is a unique number assigned to a transaction which modifies data in the database.
CKPT process will always update this SCN to both datafiles header and control file.
35. What are the other new background processes in 10g that you know?
a. MMAN – memory manager which helps in maintaining ASMM
b. RVWR – helps in generating flashback logs incase flashback is turned on
c. CTWR – helps in incremental backup in RMAN
d. MMNL – memory monitor light, which helps in AWR statistics collection
e. MMON – manageability monitor, which help in automatic problem detection and self-
tuning.
36. What information control file contains?
a. It contains latest SCN , all locations and sizes of datafiles and redolog files, database
creation date and timestamp and controlfile parameters.
37. What is lfile and when it is used?
a. lfile is a index file which can help database when pfile or spfile are not in default
location.
38. What is server parameter file and how it is different from parameter file?
a. Spfile is a binary file and it helps in changing parameter efficiently than a pfile
39. How to recover if I lost parameter pfile or spfile?
a. We can recover from alert log file which contains non-default parameters
40. If we have both pfile and spfile in place, which file oracle will use during startup?
a. Spfile
41. What are hidden parameters and their use?
a. Hidden parameters are to be used only on recommendation of oracle support and
sometimes they help us in providing work around to any serious problem in the
database.
42. What is the purpose of password file?
a. It is used to authenticate any user is connecting as SYSDBA from a remote machine
43. Even though there is a password file, still I observed that any user is being able to connect
as sysdba from remote machine without a password. What went wrong?
a. REMOTE_LOGIN_PASSWORD is not set to exclusive
44. While creating password file, what is the use of ENTRIES?
a. To specify how many users with sysdba role can connect to the database remotely
45. What is ASMM? Which background process helps in that?

DBA INTERVIEW CRACKS | version 1.0 4

 RR TECHNOSOFT

a. Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA
components automatically and MMAN is the background process helps in that
46. How database will behave when you have both ASMM and individual parameters are
configured?
a. Values of individual parameters will act as minimum and ASMM as maximum
47. You increased the SGA_MAX_SIZE parameter to a higher value. But when trying to
increase SGA_TARGET, its throwing error that “it cannot increase”. What might be the
reason?
a. Kernel parameter SHMMAX may be reached. We should increase that first
48. What is AMM in 11g?
a. Managing both SGA and PGA automatically by oracle is called Automatic Memory
Management(AMM)
49. Which parameter helps ASMM to be affective? What is the value it should be set to?
a. SGA_TARGET and value is dependent on no of transactions in the database
50. My database size is 1 TB, how much SGA will you configure?
a. SGA size is not dependant on database size, it depends on how many transactions
happening in that database
51. What is alert log? How it is helpful for DBA?
a. It is file which helps in diagnosing all the errors occurred in the database
52. What are the contents of alert log file?
a. Database startup and shutdown times, non-default parameters, any logical or physical
structural changes and all oracle errors etc.
53. What happens if we remove alert log file when database is in use?
a. No effect on the database functionality. Oracle will create a new alert log file
54. If we have alert log file to diagnose the problem , why we need trace files? What are they?
a. Alert log itself cannot provide complete information about the error , in which case it
will generate trace file. Depends on the error, it will generate background, core or
user trace files.
55. Which background process is responsible for writing into alert log file?
a. All the background processes are responsible

56. Which process will start first when instance is started?

DBA INTERVIEW CRACKS | version 1.0 5

 RR TECHNOSOFT

a. PSPO, process sp owner. This background process will start other processes like
PMON, SMON etc. But in alert log first it will show PMON
57. Which file is req to place database in NOMOUNT state and what happens in that phase?
a. Pfile or spfile is required to bring database to NOMOUNT state and instance will be
started (background processes will be started and memory will be allocated to SGA
from RAM) in this phase
58. Which file is req to bring into MOUNT phase and what is the use of this phase?
a. Control file is required and it is for maintenance of some database actions
59. What files are needed to open the database and How oracle knows the locations of them?
a. Datafiles and Redolog files are required to open the database and oracle will get that
information from controlfiles
60. What you will do when SHUTDOWN IMMEDIATE command hanging for last 30 min?
a. We can open another terminal and issue shut abort. Then once again startup and do
shut immediate
61. What could be reason for SHUTDOWN IMMEDIATE command hanging for long time?
a. Might be there is a transaction which is large and rollback is happening for the same
62. What is server result cache? What benefit we get out of it?
a. It is a new component introduced in 11g. It will store the rows of a query directly thus
avoids the need of executing a statement.
63. Explain how a select statement will get the benefit by using server result cache?
a. Read how a select statement will process in 11g from class notes
64. What parameters to be used to make 11g database use server result cache?
a. SERVER_RESULT_CACHE
65. How to increase the size of server result cache?
a. By increasing the size specified by SERVER_RESULT_CACHE or by increasing
MEMORY_TARGET parameter if AMM is used
66. How to increase the size of server result cache?
a. Where all the trace files located in 11g?
67. Which background process clears sessions and releases locks when system fails?
a. SMON (here system means database)

68. What is OFA? Do you recommend to implement it?

DBA INTERVIEW CRACKS | version 1.0 6

 RR TECHNOSOFT

a. It is a rule which says database related files should split across multiple disks and yes I
will recommend to implement it
69. Why oracle recommends OFA? How it is related to OS?
a. As one disk will have only one I/O header, it will be burden to the database if we place
all the files in single hard disk. So oracle recommends OFA.
70. What is DISPATCHER in shared server architecture?
a. It is a service which server multiple user requests
71. Explain shared server architecture?
a. Answer is in class notes
72. As a DBA , when you will take decision to enable shared architecture?
a. When we observed ORA-04030 or ORA-04031 errors very frequently in alert log file
73. You configured all the parameters to enable shared server architecture, but still users
facing memory allocation issues. What you would do?
a. We need to check if SERVER=SHARED is mentioned in client TNS entry or not
74. What are the parameters you use to configure shared server architecture?
a. DISPATCHERS
b. MAX_DISPATCHERS
c. SHARED_SERVER_PROCESS
d. MAX_SHARED_SERVER_PROCESSES
75. How many slave process we can have for DBWR and SMON?
a. DBWR – 20, SMON – 16
76. I connected to database as sysdba, but its not allowing me to shutdown the database,
what may be the reason?
a. The connection would have been shared server connection in which case oracle will
not allow to shutdown/startup the database.

INSTALLATION

77. Explain the steps of installing oracle 10g on linux machine?

a. Refer to the installation guide in class notes.
78. What is the purpose of adding oinstall and dba groups?
a. Oinstall – to provide the user to install any oracle software
b. Dba – allows to administer the database

DBA INTERVIEW CRACKS | version 1.0 7

 RR TECHNOSOFT

79. Is it compulsory that we need to give group names as oinstall and dba? Or can we give
any other name?
a. We can give any name, but those are oracle standards.
80. What are kernel parameters and why to set them?
a. They will define the memory allocation from physical memory to Oracle database
81. What is the difference in installing 10g and 11g on linux?
a. 11g allows to set kernel parameter later wheras in 10g we need to do it before
82. What is Oracle inventory?
a. It is a location which provides the oracle product information which are installed on
a particular host
83. Why to run orainstRoot.sh and root.sh scripts at the end of installation?
a. orainstRoot.sh will change the permissions for oraInventory and root.sh will create
oratab file
84. When I am trying SQLPLUS, its throwing command not found error. How you will
handle it?
a. We need to set oracle bin directory in PATH environment variable
85. Where you will set environment variables in linux? Why to set them?
a. We need to set in .bash_profile file. Setting them will help in easy access to
commands
86. How you will set environment variables in windows?
a. We can set in advanced tab in my computer properties
87. When trying to connect to the database , I am getting “net service name is incorrectly
specified” error. What is the reason?
a. ORACLE_SID is not set
88. How to check how many databases are up and running at OS level?
a. Ps –ef | grep smon. Even though this is the generic command that is used, this is not
the perfect answer as it will give output even if instance is started. So the correct
answer would be ps –ef |grep oracle. In the output, we need to see for processes
which says LOCAL=NO which means someone connected to the database from
remote machine.
89. How to check the version and bit version of oracle software?
a. SQL>select * from v$version;
90. How to check how many databases are existing on the server?
a. Using oratab file. Bat this may not give perfect answer if manually created
databases are not updated.

DBA INTERVIEW CRACKS | version 1.0 8

 RR TECHNOSOFT

91. How many databases can we create on a single server?

a. Unlimited until cpu and memory configuration are supported.
92. How many database are there in your environment and categorize them?
a. Tell any value between 70 to 100. Out of that say 35 are prod and rest are dev and
test
93. What is the highest size of the databases you are maintaining?
a. Tell any value between 400 to 700 GB
94. How many servers are there in your project? Which platforms you are comfortable to
work in?
a. Tell any value between 30 to 50. Platforms are all unix and windows flavors
95. Can we change the block size after database creation?
a. No, we cannot
96. What are the 3 important steps after database created manually?
a. Executing catalog.sql script
b. Executing catproc.sql script
c. Updating oratab file
97. How to see background process at DB level?
a. SQL> select program from v$version;
98. How to start the database if pfile is not in default location?
a. SQL> startup pfile=’location of pfile’
99. I am creating a database manually and while executing script, I am getting “ ORACLE
INSTANCE TERMINATED, DISCONNECTION FORCED” error. What went wrong
and how you will trobleshoot?
a. This is due to UNDO tablespace name mismatch between create database script and
pfile. Once fixed, problem will be resolved

100. What is the difference between ORACLE_BASE and ORACLE_HOME?

a. ORACLE_BASE is the location where we can install oracle software and

ORACLE_HOME is the database home location

101. How to automate database startup during linux bootup?

a. Using dbshut and dbstart scripts

102. What is your strategy to delete old traces?
a. We will delete them every 3 months except alert log. We will take backup of alertlog
and will trim the file
103. How to check RAM size at OS level?

DBA INTERVIEW CRACKS | version 1.0 9

 RR TECHNOSOFT

a. We can user free or top command

104. How to check the size of a single directory?
a. Du –sk *
105. What is VMSTAT command?
a. It is used to know the IO and memory statistics
106. While starting the database, I am getting “END OF FILE ON COMMUNICATION
CHANNEL” error. You are the only user connected to the database. What could be
the reason for this?
a. If a particular mount point is full, in that case we will get that error
107. What are different types of SCN available?
a. We have TOP, START,STOP,BLOCK SCN etc
108. How many types of checkpoints are there?
a. We have nearly 10 checkpoints which helps in making data permanent in different
stages of transaction.
109. What is the use of oratab file?
a. To identify how many databases existing on a server
110. How do you check if oracle installed on a server?
a. By checking orainventory location
111. If I have 5 databases on a server, how to find which version they are?
a. By checking oratab file.

REDOLOG FILES & CONTROL FILES MANAGEMENT

112. Why to multiplex redolog files?

a. To avoid any data loss
113. What is log Switch?
a. LGWR switching between two redolog groups
114. If we have multiple members in a single group, how LGWR will write into them?
a. It will write paralley if ASYNC I/O is enabled at OS level. Otherwise, it will write in
sequential order

115. Can we have different sizes for members in the same group?
a. Not possible

DBA INTERVIEW CRACKS | version 1.0 10

 RR TECHNOSOFT

116. Can we have different groups with different sizes?

a. Yes. But it is not recommended as checkpoint occurrence will change and it will not
be consistent
117. What is the use of FAST_START_MTTR_TARGET parameter?
a. It is used to specify at what frequency checkpoint should occur in order to make
data permanent
118. What is the min size that can be given during redolog file creation?
a. 10MB
119. How to resize the redolog files?
a. Not possible. If we want, we can create new group with more size and drop existing
group
120. Where to check the status of redolog group? What it mean when we see INVALID
status?
a. We can check from v$log view and INVALID means redolog file or group is
corrupted
121. Where we can check controlfile location?
a. Using CONTROL_FILES parameter
122. What is archive log mode? Why it is required?
a. Archive log mode is a mode in which database stores all the changes occurs in the
form of archives. It is used to recover the database when we have old backup
123. What is the default location of archives in 10g?
a. Flash recovery area
124. If archive destination is full, how you will react?
a. We will first see for possibility of taking backup, if not we will move some archives
to other mount point or in worst case we will delete them. But when we delete them,
immediately we need to take full database backup

TABLESPACE MANAGEMENT

125. What is the different between dictionary manages tablespace and locally managed
tablespace?
a. In dictionary manages tablespace, free block information is maintained in data
dictionary cache there by increases IO. In locally managed tablespace, the same
information is maintained in datafiles header itself there by reducing the IO. Hence
LMT is better than DMT.

DBA INTERVIEW CRACKS | version 1.0 11

 RR TECHNOSOFT

126. What is coalescing and which process will handle this?

a. Combining free blocks together is called coalescing and SMON will hangle this only
if ASSM is enabled for the tablespace.
127. Can we create dictionary manages tablespace in 10g/11g?
a. Yes, but SYSTEM tablespace also should be dictionary for that
128. Explain PCTFREE and PCTUSED?
a. PCTFREE is the reserved space which is to be used by rows for future updates
b. PCTUSED is the level of data in the block which allows further data insertion only
after the level decreases beyond that
129. Can we take SYSTEM and SYSAUX tablespace offline?
a. We can take SYSAUX, but not SYSTEM
130. How to change the segment management type for an existing tablespace?
a. Once defined, we cannot change segment space management for any tablespace
131. A select query is executing and you observed Temp tablespace is getting full. You
added 50GB, but even that is eaten up. What is your next action?
a. We need to see what that query is and if required we need to tune it
132. What happens when you take a tablespace/datafile offline immediate?
a. Any existing transactions will be stopped and datafiles of this tablespace need
recovery when we make them online.
133. How to identify which datafiles are modified today?
a. By looking at the timestamp of the files at OS level.
134. Is it possible to reduce the size of datafile?
a. Yes, but data level should not have been crossed the limit
135. What is the new feature of 11g tablespace management?
a. Encryption of the tablespace
136. What is bigfile tablespace and its use?
a. It gives easy manageability for VLDB by providing terabytes size to a single datafile
itself.
137. How will do capacity planning for your production databases?
a. We will take every quarter for normal databases and every week for critical and fast
growing databases.
138. What is ORA-1555 error and how to avoid that?
a. It is snapshot too old error which will occur if data is not found in undo or datafile
by a select statement. Increasing undo tablespace size, undo retention time, using
retention gurantee clause with DML statement are the solutions.

DBA INTERVIEW CRACKS | version 1.0 12

 RR TECHNOSOFT

139. If a transaction is running and DBA has taken particular rollback segment offline .
What happens to that transaction?
a. It will start using SYSTEM tablespace
140. How to convert dictionary manages permanent tablespace to temporary tablespace?
a. It is not possible
141. You are t rying to add a datafile, but its not allowing and saying you cannot add it.
What might be the reason?
a. Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile
might have reached its max value
142. What is OMF? What are its advantages and disadvantage?
a. It helps in managing files by oracle automatically. But naming convention will be
the problem.
143. How you will get timezone of database?
a. NLS_TIMESTAMP_TZ_FORMAT
144. How will you find out the current user who are using temporary tablespace segment?
a. By checking in v$tempseg_usage
145. How to drop a datafile without dropping a tablespace?
a. SQL> alter database datafile ‘path’ offline drop;
146. How to check SCN of the database?
a. SQL> select current_scn from v$database;
147. Can we make temporary tablespace read only?
a. Not possible
148. What is the relation between db_files and maxdatafiles?
a. Both will specify how many max datafiles can be there in the database.
149. When a tablespace is offline, can we make it read only?
a. Not possible

USER MANAGEMENT

150. What is the difference between a user and schema?

a. A user will not have any of his own objects and will always access others objects.
Schema is a collection of objects
151. After creating user, what are the privileges you would assign?
a. We will give CONNECT role in 10g(CREATE SESSION if 9i) and other roles or
privileges specified by the application support
152. What is the disadvantages of resource role?
a. It will override the quota granted for a user and makes it unlimited
DBA INTERVIEW CRACKS | version 1.0 13
 RR TECHNOSOFT

153. How you will identify the privileges and roles assigned to a user?
a. Using following views
i. dba_sys_privs
ii. dba_tab_privs
iii. dba_role_privs
iv. role_tab_privs
v. role_role_privs
vi. role_role_privs
154. How to create user and grant the permission in a single command?
a. SQL> grant create session to user_a identified by <pwd>;
155. What happens to the objects if we change the default tablespace for the user?
a. Nothing. They will continue to work as normal.
156. You got a requirement to run a script as user “MARK” and you don’t have the
password of MARK. How you will take next step?
a. We will ask application support. In case they cannot be reached, we can take ASCII
password stored in dba_users and change the password. Once work is done we can
change the password back using ASCII that we stored earlier.
b. Also we can set current_user option in SQLPLUS.
157. What is the view to check default peramanent and temp tablespace values?
a. databae_properties
158. Have you implemented password policies?
a. Yes. Using profile management
159. What are SNIPPED sessions? If there any impact on database to have them
a. Those are sessions terminated from database because of crossing IDLE_TIME limit.
But disadvantage is even though oracle level sessions are cleared, at OS level
processes will still occupy resources which is a burden to the server.
160. I am using IDLE_TIME as 15 min, but observed that even aftr 20min idle time, session
is not getting disconnected, what is the reason?
a. RESOURCE_LIMIT parameter is not set to TRUE
161. AS a DBA will you recommend to implement auditing in the database?
a. Depends on the type of database, but it would be wise to implement atleast on some
critical tables.
162. What factors you will choose before you enable auditing?
a. We need to check about database performance and what level of auditing is
required.

DBA INTERVIEW CRACKS | version 1.0 14

 RR TECHNOSOFT

163. If auditing is not in place, how you will get to know if a user performed update or not
yesterday?
a. We can use logminer to read the archives which are generated yesterday
164. Are you maintaining any SOX compliance database?
a. No. We don’t have any SOX database as of now
165. How to check which users are granted with sysdba role?
a. We can check from v$syspw_file view
166. If we want to restrict users to connect to the database, but want to allow some users to
connect, how can we do that?
a. We need to grant restricted session privilege for the users whom we want to connect.

NETWORKING

167. What is the importance of SQLNET>ORA file?

a. It helps in choosing the type of connection to be established from client to server
168. What is the difference between TNSAMES.ORA and SQLNET.ORA file?
a. Tnsnames.ora will specify the entries on to which database to connect where as
sqlnet.ora will tell which type of connection can be established.
169. How to ensure users are connecting to shared server architecture?
a. By specifying SERVER=SHARED in TNS entry.
170. While user trying to connect as sysdba on windows machine, he is getting “insufficient
privileges” error. What could be the reason?
a. The OS username is not added to ORA_DBA group
171. In a server, we have 12 oracle 10g databases. How many listeners will be created by
default?
a. Only one. But it is always preferred to have separate listeners
172. How to check listener status at OS level?
a. Ps –ef | grep tns
173. What are the troubleshoot steps you follow when user complains about database
connectivity problem?
a. Checking tns entry
b. Checking listener status
c. Do tnsping from client
d. Based on the above results, we can analyze where the problem is

DBA INTERVIEW CRACKS | version 1.0 15

 RR TECHNOSOFT

174. 100 users connected to the database. At that time DBA stopped listener, what happens
to those connections?
a. Nothing will happen as they are already connected.
175. How will you check if a port number can be used or not while configuring a listener?
a. Netstat
176. What is database link and what is its use?
a. It is a object used to pull remote database’s data to local database.
177. What is the difference between snapshot and materialized views?
a. Snapshot won’t have log mechanism by which refresh time will increase day by day
which is avoided in materialized views
178. What is the difference between normal view and materialized view?
a. Normal view is just a stored query and it doesn’t contain any data. Materialized
view will store the data.
179. If a user complains that MV refresh is running slow, how you will react to that
problem?
a. First we will check if MV log sizes is more than the table. If so, we need to drop and
recreate only MV log.
b. Sometimes it may happen due to many materialized views on a single table, in such
case we need to identify which are not in use.
180. Which background process helps in MV refresh?
a. Job queue coordinator(CJQ)
181. What is distributed database management system?
a. Having data in multiple databases and combining them into a pool is called DDMS.
182. What is two phase commit? Which env will use it?
a. It is used in DDMS by which a transaction should be commited on both the
databases, if not transaction will be rolled back.
183. When to use database triggers?
a. In case we want to track some changes in the database.

EXP/IMP & DATAPUMP

184. How can I make export faster?

a. By using DIRECT=Y and BUFFER parameters
185. Which processes is responsible for writing data into dumpfile?
a. Server process
186. What is the use of consistent=y parameter in export?
a. It will take consistent values while taking export of a table
DBA INTERVIEW CRACKS | version 1.0 16
 RR TECHNOSOFT

187. What the parameter COMPRESS will do during exportmpfile is valid?

a. During import, it will put entire data in a single extent
188. How can we confirm that export dumpfile is valid?
a. By using SHOW=Y option during import
189. If you got a dumpfile to import and don’t know the from user, how you will get that
information?
a. We can check that in export log file. If not, we can do import with SHOW=Y which
generates a log file. From user will be there in that log file.
190. What precautions you will take perform a schema exp/imp between a prod and dev
database?
a. We need to check if already user existing in dev database
b. If so, drop the user (take DDL and permissions info well before) or drop all the
objects.
191. What are the advantages of datapump over exp/imp?
a. It is faster than exp/imp as we can use parallel and other options
b. List all other from your class notes
192. Can we import a 11g dumpfile into 10g database using datapump? If so, is it also
possible between 10g and 9i?
a. Yes we can import from 11g to 10g using VERSION option. This is not possible
between 10g and 9i as datapump is not there in 9i.
193. We exported a table and imported into dev database. After checking we found table is
residing in SYSTEM tablespace. What could be the reason?
a. The user is having RESOURCE role assigned. If we assign RESOURCE role, it will
give some quota on SYSTEM tablespace which is dangerous.
194. What you will do when export is running slow?
a. We need to skip taking export of indexes, use BUFFER and DIRECT parameters
195. Import process is running slow, what can you do now to make it faster?
a. We can skip importing indexes a it is the most time taking job during import
196. You are observing undo tablespace error during import, how you will avoid it?
a. We can use COMMIT=Y option

BACKUP & RECOVERY

197. What is the difference between cold and hot backup?

a. COLD backup will be taken by shutting down the database, where as HOT backup
will be taken while database is up and running
198. What happens when a database or tablespace is placed in begin backup mode?
DBA INTERVIEW CRACKS | version 1.0 17
 RR TECHNOSOFT

a. Datafile header will get freezed i.e, CKPT will not update latest SCN
b. DBWR still will write data into datafiles.
c. When end backup, CKPT will update the latest SCN to datafile header.
199. Why more redo will generate during hot backup?
a. It is to avoid fractured block as oracle will copy entire block as redo entry
200. What is fractured block?
a. A block which might contain inconsistent data. This happens because the speed of
DBWR is different than OS copy during hot backup.
201. What is the difference between complete and incomplete recovery?
a. No data loss in complete recovery whereas some data loss is observed in incomplete
recovery.
202. What will happen if we use resetlogs?
a. It will create new redolog files(If not already there)
b. It will reset log sequence number to 1,2…etc
203. What is the difference between resetlogs and noresetlogs?
a. Resetlogs will create new redologfiles(if not already existing) and will reset the log
sequence number whereas noresetlogs will not have any effect on database

204. When to use resetlogs?

a. When we perform any incomplete recover, we need to open the database with
resetlogs option
205. If you open database in resetlog mode, can we use previous backups?
a. Not possible till 9i. But possible from 10g RMAN with the help of “recovering to
previous incarnation” option
206. Out of 100 datafiles, I lost 29 files. How you will identify which files to recover?
a. Using the view v$recovery_file
207. How to check if incomplete recovery is performed in the database?
a. SQL> select RESETLOGS_TIME,RESETLOGS_CHANGE from v$database;
208. I placed a tablespace in hot backup mode and datafile which is being backup is lost.
How you will recover it?
a. We can restore from old backup and apply all the archives till now
209. Yesterday night backup is successful. Today morning we added a datafile at 11 AM.
After noon 3’o clock the newlyadded datafile is lost. Can I recover that datafile? If so,
how?

DBA INTERVIEW CRACKS | version 1.0 18

 RR TECHNOSOFT

a. Yes we can recover it. We need to create that datafile using “alter database create
datafile ‘path’:” command and then apply all the archives
210. What is the importance of archives during recovery process?
a. As they will store all the changes happened in the database, always we can do
complete recovery if we have a perfect backup
211. How you will recover database when all copies of control file are lost?
a. We can do a complete recovery if we have a latest trace of it. Or else, we need to do
incomplete recovery by restoring controlfile from last successful backup.
212. Application team informed thatan important table is dropped. How you will recover
it?
a. We can recover it by doing until time recovery. But this will affect other user
transactions, so we need to get approval for this first
213. What are the pre-requisite factors you will consider before performing until time
recovery?
a. We need to see if other users are not getting any affect by doing this
214. You need to restore the database and the realized there are no control files. How you
will proceed?
a. Either we can restore controlfiles first and do recovery(but a data loss is there as its
incomplete recovery) or we can create new controlfile if we have a latest trace.

RMAN

215. What are the advantages of RMAN?

a. Validation of the backup
b. Parallelism
c. Block level backup
d. Incremental backup
e. Recovery catalog etc
216. Explain how RMAN performs the backup
a. When RMAN is initiated, it will start reading datafile headers for used block
information. It will get that from bitmap blocks
217. During RMAN backup we are getting “failed to create sequential file” error. What is
the reason?
a. The reason would be lost of contact with tape drive, so we need to check with
backup team.
218. We are getting “control file enqueue” error in RMAN backup log file, what action you
will take?
DBA INTERVIEW CRACKS | version 1.0 19
 RR TECHNOSOFT

a. It means two backups on same database are trying to take controlfile backup and
one of the backup is placed in queue until first one completes
219. What is your backup strategy?
a. Daily we will take full RMAN backup for all prod database and will take structural
export backup. For dev and other environments, it is a weekly twice procedure.
Apart from this, we have archive backup scheduled for all prod databases every
30min.
220. If you have a 2 TB database and need to design a backup policy, how you will do that?
a. We need to choose RMAN incremental level 1 backup as it will complete faster.
Weekend we will take full RMAN level 0 backup.
221. What is your backup retention policy is set to?
a. 90 days.
222. What is the need of BACKUP OPTIMIZATION parameter in RMAN?
a. It will help in avoiding to take backup of a datafile which is not changed when we
are performing image copy of database through RMAN
223. Why RMAN is best over other backup methods?
a. Because it uses block level backup which reduces backup size and time

224. What are the new features of oracle 10g RMAN?

a. We have encryption of backup, using archive deletion policy etc
225. What are the new features of Oracle 11g RMAN?
a. We have some new commands like list failure, validate database etc and also we can
now use virtual private recovery catalog.
226. What is the need of recovery catalog?
a. Generally RMAN backup information will be stored in target database controlfile.
In case we lost all controlfiles, it will be a problem in using existing backups. So
recovery catalog is another database that to be configured, so that all the target
RMAN backup information will now be stored in that.
227. Suppose we lost datafiles in recovery catalog, how to recover them?
a. As recovery catalog is also a database, we will be performing backup for the same.
So we can restore and recover from that backup.
228. What is mean by channel allocation in RMAN?
a. It is allocating a medium to send backup to backup device.
229. If we don’t use recovery catalog, where the backup information is stored and till how
many days?

DBA INTERVIEW CRACKS | version 1.0 20

 RR TECHNOSOFT

a. It will be stored in controlfiles and no of days are dependent on

CONTROLFILE_RECORD_TIME parameter. Generally it is 7 days.
230. How many recovery catalogs you will create for 100 prod databases?
a. Only one is enough
231. What are the advantages of differential and cumulative incremental backup?
a. Differential will take incremental backup in a daily fashion where as cumulative will
take backup so that it includes all the other day changes also
232. If you are the DBS, will you choose differential or cumulative and why?
a. I will choose differential because it will complete faster.
233. How RMAN performs incremental backup of database?
a. RMAN will first gets block information from bitmap block
b. It will start comparing block SCN with the last level 0 backup
c. If block is changed, SCN will differ in which case RMAN will take backup otherwise
not.
234. What is the difference between full backup and level 0 backup?
a. Level 0 backup will try to compare the block SCN with last backup, it will fail and
then performs backup where as full backup no comparison is required.
235. How can I make incremental backup faster?
a. Using change tracking file in 10g
236. I have a image copy of database, can we apply level 1 incremental backup on that and
recover the database?
a. Yes we can apply
237. Which process is responsible in writing latest SCN to controlfiles which are recreated?
a. Server process

PERFORMANCE TUNING

238. Till yesterday a query is giving output in 1 min today it is taking 5 min. how you will
troubleshoot this problem?
a. We will start looking at the network issues
b. Check if the query is changed from yesterday to today
c. Check table statistics are up to date
d. Generate explain plan and see where is the problem
239. A job which will generally execute in 1 hour, now running since 5 hours, what can be
done to find out the problem?
a. Check if any code change in the job
b. Check table statistics
DBA INTERVIEW CRACKS | version 1.0 21
 RR TECHNOSOFT

c. Generate explain plan for all the queries and DML/DDL statements in the job
d. Better to disable indexes for time being in order to make it faster
240. Users are complaining that database is running slow everyday between 11AM to 2PM.
How to find out what’s going wrong during that time?
a. We can generate statspack or AWR report and will observe different sections
241. What is the purpose of collecting statistics?
a. It will help optimizer to generate best execution plan
242. If you need to schedule a new analyze job for a single large schema, how you will do
that?
a. We will schedule daily analyze job for frequently accessed tables
b. Schedule weekly job for semi dynamic tables
243. What things you will analyze in explain plan?
a. We will check if query is using proper indexes are not. If not we will either create
new indexes or can use hints to specify which indexes to use
244. Have you ever done partitioning? What is its use?
a. Yes. It will help in searching data easily as it is stored in multiple partitions
245. Why to go for composite partitioning?
a. In case we have very large table and want faster access
246. What is the difference between local and global index?
a. Local index is the one which we can create for a single partition. Global index can
be used for entire table at a time.
247. How you will re-organize the database?
a. If 9i we can use exp/imp or move table. From 10g, we can use shrink compact
command
248. Why indexes will become unusable during a table move?
a. Because the row id’s will change when we move a table and index is based on that
row id.
249. Can we move a table to the same tablespace?
a. Yes, but we need to have enough free space
250. What should the frequency of performing re-organization in a database?
a. If we are using ASSM, there is no need to re-org. Eles, it is preferred to do every 6
months.
251. How can avoid fragmentation in a table?
a. By using automatic segment space management.
252. What is the difference between row chaining and row migration?

DBA INTERVIEW CRACKS | version 1.0 22

 RR TECHNOSOFT

a. Row chaining means splitting same row across multiple blocks. Row migration
means moving row to another block. In both the cases, I/O will be increased.
253. What is mean by row chaining and when it will occur? Solutions to avoid it?
a. Splitting a single row into various blocks is called row chaining. It will occur when
the data size is more than block size. We can use non-default block size tablespaces
to avoid this.
254. What is the benefit of searching for data
a. It will speed up the process of searching for data
255. How index works in the database?
a. Index will be stored as tree format and when a comparison is required , it will start
with root node and will spread to branch or leaf nodes if required.
256. You want to drop and re-create a parent table. How you will do that without affecting
child tables?
a. By disabling constraints first
257. How you would go about increasing buffer cache hit ratio? Which view will help in this
case?
a. V$buffer_cache_advice

258. What is the difference between locks and latches?

a. Locks are to maintain read consistency and will be applied on tables. If the same
king is on instance memory structures, it is called latches
259. How you will manage locks?
a. We doesn’t need to do anything about locks, oracle will take care of them
260. What are the 3 advisory statistics that you can collect?
a. Memory, segment and hit ratio’s advisory
261. What you will analyze out of TKPROF report?
a. It will help us in knowing how much time and cost a query is using in the database
during its execution
262. What options we can use with tkprof report?
a. So many, listed in google(generally we will not use them)
263. What is the use of keep cache and recycle cache?
a. Keep cache can be used to store a table which is being frequently used and if we
want to not flush from the instance. Recycle is used for full scan tables to flush the
data immediately.
264. How you will determine for which resources sessions are waiting for?

DBA INTERVIEW CRACKS | version 1.0 23

 RR TECHNOSOFT

a. V$session_waits or v$system_waits
265. What is the difference between a statspack and AWR report?
a. AWR is advanced version of statpack report in which statistics collection will be
done automatically everyone hour
266. How you will analyze AWR report?
a. Refer to the documents provided along with class notes
267. What are top 5 timed events in AWR report?
a. Those will represent for what database is waiting mostly for
268. What is ADDM in 10g?
a. It is a utility helps in providing some recommendations based on the statistics
collected every hour
269. What is the difference between ANALZE and DBMS_STATS?
a. DBMS_STATS will collect more information than ANALYZE command
270. When to use more than one DBWR process?
a. If we have datafiles spread across multiple disks and mostly it is used in OLTP
environment.
271. When a database object becomes invalid?
a. If the underlying base objects gets modified

272. How to find the no of blocks a table is occupied?

a. Dba_tables
273. In which situations you will increase SHARED_POOL_SIZE parameter?
a. When we observe that a frequenty executed statement is undergoing hard parsing
every time.
274. Which parameter helps in giving complete output for TKPROF report?
a. SOL_TRACE
275. When looking at v$sysstat, you observed sort(disk) is high, is this good or bad? If bad,
how you correct it?
a. Its bad. We should not have more physical I/O
276. What is the difference between instantaneous and cumulative buffer hit ratio?
a. Reading data from instance is better than cumulative in which data should be read
from disk which is costly.
277. If a tablespace is having zero pct increase value, what could be the problem with
SMON?
a. SMON will not coalesce the tablespace
278. How can you tell if a tablespace has excessive fragmentation?

DBA INTERVIEW CRACKS | version 1.0 24

 RR TECHNOSOFT

a. If a select against dba_free_space shows the count of a tablespace extents is greater

than the count of its datafiles, then it is fragmented
279. What can cause high value for recursive calls? How to fix it?
a. Improper cursor usage which should be fixed by code writers
280. How you will identify that a deadlock occurred and what immediate action you will
take?
a. Oracle itself will detect that and will resolve that by rolling back a transaction
281. What is ORA-600 error and what causes it to occur? How to resolve it?
a. It is an internal error which can be occurred due to various reasons. We need to contact
oracle support or using ora-600 lookup tool in metalink
282. How oracle will maintain read consistency always?
a. By using locks on the tables
283. How many types of optimizers are there? What is the difference between them?
a. Cost based and rule based optimization. Cost will generated better execution plan
than rule based
284. How you will specify a query to use full table scan even though it is having index?
a. Using hint

285. How do you identify which sessions are locking the objects? What is the way to clear
them?
a. By combing V$session
286. If we observe latch hit ratio is high, is it a good or bad sign?
a. It is a good sign as every statement need not to wait for the free buffer
287. In your experience, what are the different wait events noticed and tell how you resolved
them?
a. Read the PT docs provided and explain any 2 or 3 of them with solutions

MISCELLANEOUS

288. What are your daily activities?

a. Checking the alertlog file, backup status, performing any refreshes, resolving tickets
and handling application team requests etc.
289. What is your typical day? Or What is the biggest problem you faced till now?
a. Explain any one problem you faces during practice or any error you observed
290. Why should we hire you?

DBA INTERVIEW CRACKS | version 1.0 25

 RR TECHNOSOFT

a. With my experience and knowledge, I can deliver some optimized solutions to the
client where it would benefit client and company as well to get much more projects.
291. Are you a nuts-n-bolts DBA or tolls-n-props DBA?
a. Nuts-n-bolts DBA means who uses command line interface and tools-n-props means
who is fine with GUI’s like EM etc. So tell the answer as both
292. Which other teams you will interact daiy?
a. Unix team, application team and incident management team mostly
293. What is the difference between hard link and soft link?
a. Hard link carries same permissions and size as original file where as soft link
doesn’t
294. How to perform RMAN cloning? Brief the step?
a. Explain the steps and mentioned in the document
295. We are getting “recovery successful but database cannot be opened with resetlogs”
error. Why this will occur and what could be the solution?
a. It will occur during HOT backup cloning. During this, we need to either generate
an archive in source database and apply it on target or can provide the path of
redolog file

296. You have installed oracle and creating a database manually. Its been a hour, still script
is executing. What is the first thing you will check here?
a. We need to check if archive destination is full.
297. How to find dead process?
a. Using top command. Here we need to look for zombie processes
298. You used a query with order by asc clause and that column contains NULL values.
Will they appear on top or bottom?
a. Bottom
299. Can we have multiplexing for datafiles? If so, how?
a. Not directly. But we can achieve similar using ASM or RAID levels which provides
striping and mirroring
300. What is the difference between truncate and delete?
a. Delete can be rolled back where as truncate cannot
301. What is normalization? How it is related to E.F.Codd rules?
a. It is the process of applying rules on tables to achieve consistency and to
avoid/reduce redundancy
302. Explain the steps for performing database upgradation?
a. Tell them as per the procedure you followed

DBA INTERVIEW CRACKS | version 1.0 26

 RR TECHNOSOFT

303. What is the difference between patchset and interim patch?

a. Patchset is a group of patches and when applied version of database will change.
Interim patch is some security bug fix release
304. How you will find the latest patchset or patch installed on your database?
a. Using “opatch-lsinventory” command
305. Do we require to shutdown all databases to apply a critical patch?
a. Yes
306. For upgrading a database, which method you will opt? DBUA or manual method?
Why?
a. Manual as we can perform upgrade multiple times even if it fails. DBUA can be
used only once
307. What are advantages and disadvantages of DBUA and manual method of upgradation?
a. It can be used only once
308. What are important pre-upgradation steps?
a. Taking full database cold backup (if approved), backing up oracle home and oracle
inventory

309. What is dataguard?

a. Providing a disaster recovery solution to a database by having a standby database in
another location is called dataguard
310. How many types of standby databases are there?
a. Two. Physical and logical
311. In how many modes a physical standby will work?
a. Maximum performance
b. Max protection
c. Max availability
312. What is the use of FAL_SERVER and FAL_CLIENT parameters?
a. They can fix the archive gap occurred due to network failure between primary
and standby
313. Explain how to setup physical standby database?
a. Describe the steps as per the document
314. What factors to be considered before configuring dataguard?
a. We need to choose which mode to be enabled and also clearly analyze the client
requirement
315. What is the difference between RAC and Dataguard? Which is efficient?

DBA INTERVIEW CRACKS | version 1.0 27

 RR TECHNOSOFT

a. RAC is a multiple instance, single database concept. Both are having their own adv
and disadv.
316. Is it good to use dataguard broker or not? Why?
a. Yes. We can automate failover if required and also it helps in monitoring the sites.
317. What is observer’s role in dataguard setup?
a. It checks the heartbeat between primary and standby sites and in case of any failure
it will report to us.
318. What is the difference between switchover and failover?
a. Switchover means testing the standby environment by manually doing it. Failover
means failure o primary site
319. Which parameters should be use in case primary and standby servers are not having
same file systems?
a. Db_file_name_convert and log_file_name_convert

320. What is the command to switchover a standby to primary?

a. SQL> alter database commit to switchover to primary;
321. You observed archive logs are not shipping to standby, Where you will check for the
reason?
a. In the alert log of the primary database
322. You observed archive logs are shipping, but not applying at standby, where to check?
a. We will check in alert log of stand by database
323. Which parameter defines the mode of standby database?
a. Log_archive_dest_2 in which we mention LGWR ASYNC/LGWR SYNC
AFFIRM/LGWR SYNC NOAFFIRM
324. How you will check if database is primary or standby?
a. SQL> select database_role,protection_mode from v$database;
325. For a given database, How to know if there is standby dataset or not?
a. By checking database role from v$database. Also we can check in alert log file of
primary
326. How to apply a patch for a database which is having a physical standby?
a. Explain the steps what you performed

DBA INTERVIEW CRACKS | version 1.0 28

 RR TECHNOSOFT

327. Brief the steps for upgrading a standby database along with primary. Can we do both
the environments at same time?
a. Yes we can do both provided we have approval for shutting down primary
328. My database performance is bad, can I opt RAC as solution?
a. RAC is not a solution for improving performance. It should be used only as disaster
recovery
329. What I RAC? What is its benefit?
a. It is a multiple instance, single database architecture. If one instance fails, users can
still work with database using another instance
330. What is grid? Its benefits?
a. It allows to share the resources of multiple databases by bringing them into a single
pool
331. What is ASM? What is the benefit it gives us?
a. It provides benefit in performance with the use of raw devices as it has striping
and mirroring concept as RAID do at OS level
332. What are the different environment you worked in?
a. Production, development, quality, test and staging

333. How can we guarantee that you will join if offered with a job?
a. If you offer significant role with the monetary benefit that I am expecting , there is
no reason for me to reject the offer
334. Share your happiest moment of your career?
a. Explain that you resolved some critical performance issue which helped your client a
lot and you got good appreciation for that from top level executives
335. Share the saddest moment of your career and how you overcome that?
a. If you want tell them nothing I faced like that till now
336. Are you an active team member? Which you believe yields best results? Individual
work or team?
a. Tell them that you are both, but you believe mostly in team work as it yields
wonderful results than individual work.

DBA INTERVIEW CRACKS | version 1.0 29

 RR TECHNOSOFT

DBA INTERVIEW CRACKS | version 1.0 30