Thursday, March 24, 2016

Database Admin Interview Questions and Answers

The following Database Admin Interview Questions and Answers are just glimpse  of  what me may get face, however we have to understand what these are actually do internally, go in depth and learn more and not just these..

The most important thing is that interviewer at times can easily find out whether the answers given are the answers one can find in google search or answers from experience. As everyone know we may not experience each and concept in execution however we must add our experience wherever we can and learn concept in depth not just one or few lines answers, able to explain it in detail.

--- Oracle GoldenGate  --------

1. what is tranlog? trandata?

TRANLOG specifies the transaction log as the data source.
trandata command adds database schema level supplemental logging. This is necessary for all the transactional changes to be captured and written to the redo logs.
 The command must follow a successful DBLOGIN.

2. classic mode and integrated mode?

3. RBA
 Relative Byte Address (RBA).
 the RBA is mostly a marker within the trail file to identify the location of the transaction.
  Oracle is using the RBA to help calculate the amount of data being extracted
  RBA to “estimate” the amount of data being applied by a replicat.

4. How do I see ports used by OGG ex,rp processes
 send manager childstatus debug

5. what is cleanup  replicat, cleanup extract
cleanup replicat * , save 100
cleanup extract * , save 100

6. how can I see info of trail files (size, path, RBA)
info exttrail *

7. what is dblogin?

8. how can we encrypt passwords?

9. how to cleanup check point table?.. can we have checkpoint table in integrated mode?

10. How can I see ggserr.log from GGSCI command prompt?
 view ggsevt

 ============================== Database Admin =========================

 1.  How many scan listener in 8 node rac
 By default it is 3.

GNS based dynamic IP scheme still has no way to change number of SCAN listeners in rac environment.
 We can add extra scan listeners in DNS based static IP configuration.

 We can use nslookup to find IP addresses and DNS details.

2.  how do you solve 4031 errors

3.  how do you solve 600 errors

4.  How to check if node is alive or not from anohter node
 crsctl stat res -t
 crsctl status resource -t
 srvctl status nodeapps -n <>
 or telnet

5. How to fix node eviction issues and problems you faced in the last six months ?

6. Tell me the top 5 RAC events you fixed in last six months, how did you fix and the RAC events you usually see more often

7. How do you reduce the size of 1 T bye LOG segment storage?

8. How do you fix a query problem that is running t0o slow when nothing changed in the db.

Q: Number of table spaces created when creating db with dbca with default configuration?
A: Five


Q:What is High Water Mark (HWM)?
A: Oracle uses the high water mark to identify the highest amount of space used by a particular segment. It acts as the boundary between used and unused space.

Q: Oracle 11gR2 RAC architecture and Back ground processes?
The GCS, GES and GRD processes collaborate to enable Cache Fusion,
In addition to the normal database background processes , we also see the following bg processes

ACMS - per instance

12. How to find committed rows and uncommited rows?

13. Instance recovery and crash recovery, explain what happens in the database while these are happening?

Crash recovery is used to recover from a failures of either a single-instance database crashes or all instances of an RAC database crashes
Subsequent startup will conduct crash recovery

power outage, shutdown abort results instance recovery

Crash recovery is the term used when we have a cluster crashed where we have multi instance configuration.

Both recoveries are done by oracle automatically.

14. You are working on a Node , connecting to a specific rac db instance, when someone switched off that particular node, what happens to the current ongoing DMLs, what happens next?

15. Which protocol used for private inter connect i.e private network ?

user datagram protocol (UDP)
UDP is the default interface protocol for Oracle RAC and Oracle Clusterware.

16. How UDP is different from TCP/IP?

With UDP, packet transfer is considered complete, as soon as packet is sent and error handling is done by Oracle code itself.

With TCP/IP, packet transfer has some extent of overhead, connection setup, packet sent,  must wait for TCP Acknowledgement before considering the packet send as complete.

17. what is Multipath Disks in oracle cluster or oracle RAC?

18.  What are the mandatory back ground processes?
A: I see there are 7 mandatory oracle background processes.

1. Database writer-DBWn
2. Log writer -LGWR
3. Check point -CKPT
4. System monitor -SMON
5. Procesess Monitor- PMON
6. Recover process -RECO
7. Manageability Monitor Processes (MMON and MMNL)

19. What is HAIP? explain about it.

20. what are nodeapps in oracle RAC?
Nodeapps are set of Oracle application services used for RAC environments, such as the Net Listener,Virtual IP's ,Global Services Deamon and Oracle Notification Services.
Those services are started automagically on each RAC node as a node application and can be relocated from one node to another using the VIP's during the failover.

21. How NFS is different from SAN. which one you prefer?

SAN (Storage Area Networks) - generally using fibre to connect to the SAN
NAS (Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI

add some others.. answer is not sufficient.

22. Explain the top five events you see in the AWR in both RAC and single instance?

the more explanation is good here.. 
pay attention to the RAC wait events..  example: GC CR request, GB buffer busy.. add few more 

23.  What is library cache , how do you check library contention?

It stores parsed or compiled  form of recently referenced SQL and PL/SQL code, java classes.
Its size get increases, decreases automaticallly

Shared pool should be size properly, as library cache is part of shared pool.

read database performance guide , chapter 14 for more info.

24. How do you fix user sessions slowness in oracle database?

25. Explain two RAC complex issues you solved in your career?

Though we do not like this Q, the Q give us an opportunity to explain our level of expertise, 

keep a note of your RAC db problems and fixed you do on regular base, this helps you when you face this Q.


learn more about other processes, optional , RAC specific, ASM specific, many more..


  1. Mr Reddy this post looks quiet good one for helping the new learners for Oracle DBA.

  2. I like to help learners as much as I could... one can post the Qs here.. i will post answers .. thanks for your comment..