Showing posts with label Oracle DBA interview questions. Show all posts
Showing posts with label Oracle DBA interview questions. Show all posts

RAC/Grid startup sequence in Oracle 11gR2


OHASD has access to OLR (oracle local registry). OHASD then reads the OLR content and initialize accordingly. 

OHASD brings up GPnP (ora.gpnpd)Daemon and CSS (ora.cssd) Daemon. 

CSS Daemon has access to the GPNP Profile stored on the local file system. I even found a copy of GPNP Profile directly stored  in OLR (in Oracle 12c release 2)

The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

OHASD starts an ASM instance. The ASM instance uses special code to locate the contents of the ASM SPFILE, if it is stored in a Diskgroup. 

With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRS.

OHASD then starts CRSD (ora.crsd)damon with access to the OCR in an ASM Diskgroup.

And thus Clusterware completes initialization and brings up other cluster managed resources defined in OCR.

Level 1: OHASD Spawns:
  • cssdagent – Agent responsible for spawning CSSD.
  • orarootagent – Agent responsible for managing all root owned ohasd resources.
  • oraagent – Agent responsible for managing all oracle owned ohasd resources.
  • cssdmonitor – Monitors CSSD and node health (along wth the cssdagent).
Level 2: OHASD rootagent spawns:
  • CRSD – Primary daemon responsible for managing cluster resources.
  • CTSSD – Cluster Time Synchronization Services Daemon
  • Diskmon
  • ACFS (ASM Cluster File System) Drivers
Level 2: OHASD oraagent spawns:
  • MDNSD – Used for DNS lookup
  • GIPCD – Used for inter-process and inter-node communication
  • GPNPD – Grid Plug & Play Profile Daemon
  • EVMD – Event Monitor Daemon
  • ASM – Resource for monitoring ASM instances
Level 3: CRSD spawns:
  • orarootagent – Agent responsible for managing all root owned crsd resources.
  • oraagent – Agent responsible for managing all oracle owned crsd resources.
Level 4: CRSD rootagent spawns:
  • Network resource – To monitor the public network
  • SCAN VIP(s) – Single Client Access Name Virtual IPs
  • Node VIPs – One per node
  • ACFS Registery – For mounting ASM Cluster File System
  • GNS VIP (optional) – VIP for GNS
Level 4: CRSD oraagent spawns:
  • ASM Resouce – ASM Instance(s) resource
  • Diskgroup – Used for managing/monitoring ASM diskgroups.
  • DB Resource – Used for monitoring and managing the DB and instances
  • SCAN Listener – Listener for single client access name, listening on SCAN VIP
  • Listener – Node listener listening on the Node VIP
  • Services – Used for monitoring and managing services
  • ONS – Oracle Notification Service
  • eONS – Enhanced Oracle Notification Service
  • GSD – For 9i backward compatibility
  • GNS (optional) – Grid Naming Service – Performs name resolution

What is the PGA in Oracle? What is the use of it?


In Oracle PGA is specific to operation system process or thread that is not shared by the other process or threads and it is process specific, it could not establish in SGA.

PGA is a heap that contains session dependent variables required by a dedicated or shard server process, server process allocates memory structures that requires in PGA.

Private Sql Area:
A private sql area contains the information about parsed sql statements and other session specific information for processing. When a server process executes a sql or pl/sql statements/codes the process uses the private sql area to store the bind variable values, query execution state information and query execution work areas.

Private sql area can be placed Program global are (PGA) in dedicated server architecture, In shared server architecture private sql area creates at SGA.




Oracle DBA 6 years experience interview questions

1. What is the pga?
2. What are the pga parameters?
3. What is the relation between pga and temporary tablespace?
4. If incase PGA memory is having 2gb, and the statement required more memory then what hapen?
5. What is the smon?
6. What are the states of the redo logfiles?
7. What happens if we deletes all the redo log members? how to restore and recover?
8. What happens once we place the tablespace in begin backup mode?
9. why archives generates heavily at begin backup mode?
10.what is the difference between avialable and expired in rman backup?
11. what is the crosscheck?
12. What is the checkpoint?
13. what is the difference between cpu and psu patches?
14. What are the steps to applying the psu patches?
15. What process can fetch data into buffer cahce from datafiles?
16. is uncommitted data can writes into datafiles?
17. What is the chache coherence? What maintaince this?
18. What is the GRD, where it placed and which instance it resides?
19. what is node eviction? what causes for the node eviction?
20. What parameters need to create standby?
21. Which background process applies redo at standby side?
22.In which view we can verify the psu patch applied or not?
23. What are the statistics? what is the use of it and what are the incremental statistics?
24. How to migrate database from one os to another os?
25. Why we can run the roo.sh and rootinst.sh in oracle installation?
26. Tell me the select statement execution process?
27. tel me about update statement in oracle?
28. what are the sequence of steps in rac 11gr2?
29. What are the rac specific background process?
30. How to move the datafile created at file system to RAC (ASM)?
31. What is your recently resolved performance issue?


1.how to know which databases are using asm instance
2.show parameter in exp
3.check the status of the  exp dump file
4.restore datafile to perticuler location using rman
5.how to check size of a directory in linux
6.status of  partitioned disk after it assigened to asm disk group
7.how to stop job in expdp
8.how many types of load balances available in RAC

9.when we issue startup upgrade command what will happen
10.what is db replay
11.compression
12.rman 11g features
13.total db size
14.rman backup size and time
15.logical backup size,time and schema size
16.where can we find node membership polling machanism in RAC
17.how much database size  grows daily

18.are the idexes are always benficial or not
19.how to stop crs without connecting to root user
20.how to see listener status if the listener is passwor protected
21.how to improve performance of a impdp command
22.what is default time and location for ocr backup
23.difference between cpu,psu,bug fixes patch

24.How to check backup type in list backup command.
25.Can we rename asm instance
26.Asm instance control file
27.Can we create more than one asm instance on one box
28.What is conflict check in opatch

29.Crs should be up or down to take voting disks backups using dd command
30.Why expdp is faster than normal exp
31.Difference between active dataguard and normal dataguard
32.Will rman create temporary tablespace after cloing or restore process.
33.Difference between 10g load balance machanism and 11gr2 load balance machanism

34.What is the use of pwd file
35.Why mrp process runs in single on standby 3 node rac
36.Name of view that stores plsql compilation errors
37.Diffrence between cloning and refresh
38.How to demonstrate to the developper taht if they created index on perticular column it would hep
39.Order of background process when we issue startup command
40.What is the shortest way to restore a table if we have only rman backup of db size 1.5tb
41.How will you recover spfile if we dont have backup and it got corrupted
42.How does scan identify that node is a least loaded node
43.How to change the rebalance value in asm
44.Where cluster checks the details of asm diskgroups before starting the css process and pass these names to kfed
45.Which process starts 1st after issuing  crs start command
46.What will be state of database if we kill dwr background process
47.How to find scan name of a cluster
48.How to find number of nodes in cluster from os level with out using rac or database commands
49.How to find location of inventory file
50.What information contains in inventory.xml file
51.What is use of napply in opatch
52.What is use of CONSISTENT parameter in exp
53.How to find sql query of  session if  i have only  os process id
54.How to create spfile for asm instance