Oracle Dba Interview questions

                                   Oracle  Dba Interview questions


 Generic : 

1)  What error  did u faced in career and how we  can  restrict it 

>  manual error

>  patching missed on another nodes -- maker checker 

>  Block corruption :    dr lag of  15 min . 

Backup and recovery  --  Done jan 2026

1)  What is  intrablock  recovery  and  interblock recovery
2)  what is  use of snapshot controlfile
3)  what is diffrece between obsolete backup and expire backup?
4) what do you mean by consistent and inconsistent backup? and RMAN how it does?
5)

à installation ,

à how to debug  rman

à How to backup a controlfile

à How to recreate a controlfile .

à repository benefit and concept

à  why we have separate backup set for datafile and redo logs 

à where does file gets generated server or  client 

à can we take rman backup on client machine

à  cpu is used of client or server ,

à block level recovery

à 10g  block change tracking

à  benefits, backup piece and backup set ,

à LIMIT NUMBER OF BACKUP PIECE AND BACKUP SET ,

à Limit size of backup set

à what decides the backup location

à  if I am not using catalog database and current controlfile is lost

à  what does rc_ver and rc_outpackage does ,

à what internal packages are used in rman

à can I take rman backup if database in noarchivelog mode)   

à What are fractured block . and how to cover it

      à What is fractured block and is it in user managed or rman backup .

fractured block happens in user managed hot backup :

http://forums.oracle.com/forums/thread.jspa?threadID=565366

http://oracleonline.info/types_of_backup.html

à What happens when we put tablespace in backup mode. Is data written on datafiles  when we put tablespace in backup mode.

Ans : yes datafiles are accessible when we put tablespace in backup mode. Datafiles

header are frozen . data are written to datafiles and also image copy is written to  redo

logs hence more redo generation .

à how  strongly is your rman configured .

ans : few log switch before archive backup ,backup piece size , autobackup of spfile and controlfile , multiple channel -- parallelism , redundancy 90 days  ,  deleting obsolete backup . backup optimization on , backup tag , backup archive log + delete input ,  10g: shrink ,

à why we use channel in rman.  – format path , parallelism .

à transportable tablespace.

à Hot backup : tablespace offline / tablespace offline immediate

Ans : tablespace offline immediate does not create a checkpoint .

à What is instance recovery . which background process performs instance recovery

Ans :  smon

à What happens on instance recovery . (does uncommitted data also written on crash

à Rman : stored script,  what happens on resync catalog ,

 à How to take user managed incremental backup  à using exp utility .

 à How to find how many times I have done resetlogs .

select * from v$database_incarnation ;

select * from v$restore_point ;

Rman --> list incarnation ;

à rman : see incarnation information , how to switch to old incarnation .

à how to apply archivelog of previous incarnation in 9i

à Rman : change tracking file

      --  ctwr process, out of process memory (large pool),

 --  Monitor 9i : rc_ views if recovery catalog

 -- Monitor 9i : v$backup_set  if current control file is used

 --  Monitor 10g: v$rman_status.

       à Imp commit=y : how to make it fast .

à How to make import fast .

       à what is the command to check invalid/expired backups(RMAN)?

à command to delete expired backups

à how to delete obsolete backup

à difference between obsolete and expired

à command to synchronize the backup

àdifference between user managed and rman hot backup .:

       à does checkpoint happens in user managed hot backup .

 à lost all controlfile when db open

à recreate control file .

http://www.databasejournal.com/features/oracle/article.php/3738736/Recovering-from-Loss-of-All-Control-Files.htm

à lost of current log group  ( db open)

       à  lost of active undo tablespace when db open .

            http://forums.oracle.com/forums/thread.jspa?messageID=1841466&

,

 à  When we open in resetlogs .

 à  Which files are restored first in rman

 à When do we open using “backup controlfile “

 à restore backup of old controlfile

à Sequence in which rman restores file when we give restore database .

à first file to be restored in cloning

à IN user managed + rman  hot  BACKUP MODE IS DDL OPERATION ALLOWED  ,  CAN WE ADD/REMOVE TABLESPACE ??

à When we give compress=y in export . what does it compress. What option in 10g for compress . .

Ans : extents are compressed

à rman : commands , command for incremental level 1 backup . , validate backup set , list corruption , 

à restore 10g datafile file to 11g :

Ans : start upgrade after restore

à user managed hot backup restore : steps after transferring files

à rman restore steps :

à rman to tape

 à benefit of rman  :

à rman feature to ensure after backup archivelog are not deleted unless its applied to standby :10g new feature

   Answer : CONFIGURE ARCHIVELOG DELETION POLICY TO

            APPLIED ON STANDBY; (331924.1)

à how rman come to know block is corrupt

à recover block corruption without rman

Ø  use  dbms_repair : but data loss is there

http://www.oracleflash.com/42/Use-DBMS_REPAIR-to-fix-physically-corrupt-data-block.html

à .check status of rman

à Difference between rman level 0 backup and full backup

à Restore using  obsolete backups  in 9i

Ans :  a) increase retention policy  b) run crooscheck after keeping file in location

à Restore Using expired backups . in 9i and 10g

Ans : place backups in location and run crosscheck command

à If newly created datafile is lost and we don’t have any backup :

Ans : create datafile. If we have all archives

à if we don’t have sufficient space on server to take backup :

Ans :  use compressed backupset option  ,  limit the size of  backup piece , take datafile or tablespace wise backups , 11gr2 Comcodession backupset ,

à restore old copy of controlfile .

à steps for restoring rac database : cluster_database

à  how to restore system datafile .: will it require resetlogs

à   why we keep snapshot controlfile on shared location . is snapshot controlfile accessed during restore or only in backup , what is new in 11g for Snapshot.controlfile .

à using archives of previous incarnation

     à what resetlogs does in background  --  will scn be changed

     à How to speedup rman backup and recovery ?

    Ans : channel , large_pool , 579158.1

    à data dump: pre requisite  , which background process responsible and which sga

         Component

    à What will we do if current log group is corrupt ( archivelog and noarchivelog) .

    à  in rman , can I take rman backup if database in noarchivelog mode)

    à What are fractured block .

   à What happens when we put tablespace in backup mode. Is data written on datafiles  

        when we put tablespace in backup mode.

Ans : yes datafiles are accessible when we put tablespace in bakup mode. Datafiles

header are frozen . data are written to datafiles and also image copy is written to  redo

logs hence more redo generation .

à  after rman restore . not able to recognize system datafile .

à  if all archives are applied if it still ask for more archives what to look before opening

     with reset logs

 ans : location of last online redolog

à rman : what is backup optimization on. . what is Crosscheck

à catalog : do we need controlfile in case of catalog /

à lost of system datafile  and no backup

à if database in backup mode and database goes down .

    Ans : remove database from  backup mode and V$recover_file .

à use of snapshot controlfile

à taking rman of different incarnation 

à how expdp parallelism works

à validate / cross check :

à rman : expired and obsolete backup .

à rman : controlfile keep time

à recovery window 3 type:

à begin backup :  what happens when we begin backup 

à  if I have recovery window of 40 days and control_file_record_keep_time as 7 then   

      how its functions .

 à what are expired and obsolete backup.

 à how to use obsolete and expired backups :

     Ans : first run crosscheck

 à how to delete it obsolete backups .   :

 à how to change incarnation of database , how to take rman backup as backup of

    different incarnation .

à how to confirm incremental backups are cumulative or differential .

à restore solaris to linux , backup archive log ,

à restore 10g file to 11g :

à Rman stores backup metadata in :  ??

à You have just had to restore from backup and do not have any control files. How

       would you go about bringing up this database?

àwhile duplicate from where it will read/ seach backup files from

Cloning :

1) What u mean by cloning , ways to do it.

Ans : rman, dbca, imp/exp , cold backup

2)   why cloning over restore

Ans : in cloning we can change db name and datafiles path . (block size should be same.) . Also we can skip unwanted data like tablespace etc .

3)  Cloning : what if cloning fails(how to apply remaining archives ) ,

4)  change dbid of database .

5) Cloning : channels ( auxiliary + normal channel  )

 Ans : need auxiliary channel because we connect to 2 database . auxiliary as well .

6) which file is stored first in cloning .

     Ans : controlfile

7)  NOFILENAMECHECK;

8)  why in cloning we need access to target database :

Performance Tuning   -- Done jan 2026

https://expertoracle.com/2017/11/15/db-tuning-basics-1-selectivity-and-cardinality/

1) What are main  cause  of  Database locking
2) How we reduce locking  issue
3) What are main cause  of database latch
4) How we reduce  database latching issue
Answer :
. The size of the shared pool is too small.
. Cursors are not being shared.
. The buffers are being read into the buffer cache, but some other session is changing the buffers.

5)  In  what case  query will perform  full table scan even if index is there
Answer :
. When stats are stale
. More data is accessed
. db_file_multi_block_read_count  parameter is high

6)   what is pctfree and pctused and what is default size?
7) What is enabled plan / accepted plan / Fixed plan
8) What is better for plan fix : Sql plan baseline or SQL PROFILES
9)   What is  Dynamic sampling  and  when it happens
10)  What is  cardinality feedback
11)  what is multi column statistics
12)

1)     What is the recommended interval at which to run statspack snapshots, and why?

2)     view for deadlock/long operations

ans.: V$SESSION_LONGOPS, DBA_WAITERS, DBA_BLOCKERS,V$LOCK

3)     Why does Oracle not permit the use of PCTUSED with indexes?

 Ans : because there is no updates in indexes

4)     What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?

5)     Components of statspack

6)     Components of awr report

7)      

8)     How to find which session is making full table scan .

Ans : join between v$sql_plan and v$sql , v$session , scattered read indicates full tale scan .

9)     What difference between using dbms package and analyze command to gather table statistics.

Ans : a) analyze command only populates dba_tables and not the statistics to be used by 

          CBO

b) dbms_stats gathers global statistics and allows to transport statistics .

c) dbms_stats takes long time..

d) table statistics gathered by DBMS_STATS include the number of rows, number of

    blocks currently containing data, and average row length but not the number of chained

    rows, average free space, or number of unused data blocks.

10) Difference between sequential read and scattered read .

Ans : sequential read indicates index scan / single block read , scattered read indicates full table scan / multiblock read .

11) Different method of analyzing Row chaining and table fragmentation .

12) How to do table reorg without downtime.

Ans: using dbms_redefination which is new feature in 9i . first redefine a table and then then do move . in 10g we can use shrink command to do it online

13) Which parameter of index to analyze before rebuild .

14) What does gathering statistics does / why we gather statistics .

15) How to monitor chained rows in rule based optimizer .

Ans : using analyze .. list chained rows.

32) can we gather statistics in case of rule based optimizer.

And : yes .

16) Name some of the tuning database parameters .

17) Tuning (whats difference between index single scan and index full scan)

18) How to find buffer cache hit ratio from tkrpof.

19)  Database parameter effecting sql statements.

Sort_area_size

Hash_area_size

Hash_join_enabled

Optimizer_mode

Optimizer_index_caching

Optimizer_index_cost_adj

Cursor_sharing

20) What is pctused and pctfree .

21) What are main tuning parameters .

Ø  cursore sharing  :

Ø   

22)  Why do we use hints : to change sql plan . ---- /*+  hint */

     All_rows

     First_rows

     Parallel

     Index (table index_name)

      Parallel (table 4)

23) Name different types of index’s with explanation and what are clustered / non-clustered indexes.

24) How is database organized .

Ans : reorg and index rebuilding .

25) What is rule based optimizer and cost based optimizer . explain the difference in detail.

26) what is characterset and ncharacterset

27) how to confirm whether memory allocated is fine

ans :  hit ratio , target_advice

28) difference between latch and lock (example of latch contention )

ans : latch is memory structure lock but lock is dictionary lock .

29) How to generate statspack/ awr .(main components ) :

$ORACLE_HOME/rdbms/admin/awrrpt.sql – dbms_workload_repository

30) How disable awr , AWR : how to purge awr  , how to change frequency of AWR report .

31) How to change retention of awr

32) Top wait events :

33) what is the use of 07_dictionary_accessibility

34) what is HWM of table

35) What happens when user fires select statement

à ( parsing ), what is parsing

à open cursor .

à First it Parses according to their priority. (i.e Check 

the Correct syntax)

During parsing, the SQL statement is 

passed from the user process to Oracle and a parsed 

representation of the SQL statement is loaded into a shared 

SQL area. Many errors can be caught during this phase of 

statement processing. Parsing is the process of 

•       translating a SQL statement, verifying it to be a 

valid statement 

•       performing data dictionary lookups to check table 

and column definitions 

•       acquiring parse locks on required objects so that 

their definitions do not change during the statement's 

parsing 

•       checking privileges to access referenced schema 

objects 

•       determining the optimal execution plan for the 

statement 

•       loading it into a shared SQL area 

•       for distributed statements, routing all or part of 

the statement to remote nodes that contain referenced data 

A SQL statement is parsed only if a shared SQL area for an 

identical SQL statement does not exist in the shared pool. 

In this case, a new shared SQL area is allocated and the 

statement is parsed

Although the parsing of a SQL statement validates that 

statement, parsing only identifies errors that can be found 

before statement execution

à Binding the variables 

à Fetch for proper data from proper table or not

à Execute the whole query

à Finally u'll got OUTPUT.

36) what is stored in/ component  shared pool .

37) Along with chained rows what else do we find out ( table fragmentation and how )

38) What is use of ckpt process .  what happens on checkpoint . what is checkpoint not complete . does committed and uncommitted data both flushed to datafiles .

39) Parallelism à how does it works .

40)  how do we pin object in shared_pool

41)  What happens when we analyze and where is data collected from analyze stored :

Oracle will collect statistic on the number of rows, the number of empty data blocks, the number of blocks below the high water mark, the average data block free space, the average row length, and the number of chained rows in a table when the Oracle ANALYZE TABLE command is performed and store those statistics in the data dictionary.

When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

42) Contents of tkprof :

à sort option ( left hand top )

à sql queries

à wait events ( if level 12 tracing was used )

à explain plan if we explain plan while doing tkprof

43) Reorg : process of shrink table (enable row movement / compact ).

à shrink space compact : remove fragmentation below hwm but does not reset HWM .

à shrink space : remove fragmentation and also reset HWM and deallocate unused space to tablespace.

à row movement should be enabled , asssm .

44)  Manage latch contention .

45) purpose of CKPT,DBWR (at checkpoint)

46) can we change java_pool dynamically in 9i?

47) what is the first thing we need to look in statspack?

             à wait events

48) what are different types of buffer : (dirty / clean buffer )

49) soft parsing / hard parsing

50) what if we set sga more than available ram on server

51) Types of contention in oracle : latch ( what are latch ) , lock ( what are lock ) ,

Enqueue ( what are enqueue ) .

use of dbwr and ckpt   process :

how index rebuilding hamper

Tuning : 10g session wait  ,   p1 and p2 different for different wait events , main wait events,

52) important wait events : log file sync

53) session tracing 2 events . when to set what .

   10053/10054 are optimizer tracing

   10046 Is default tracing

54) different lock modes ., lock mode 6 . dx_lock

55) how to resolve ora-60 . deadlock . max_trans already high

56) fts : reflects full table scan and index fast full scan .

57) which parameter decides how much data to fetch from file .

à db_file_mutiblock_read_count

58) explain plan access path : difference between index full scan and index fast full scan

59) in awr see hard parsing . how to resolve hard parsing . when is hard parsing good and when is hard parsing bad .

60) in 10g apart from awr what we use to diagnose performance :

ans : ash , addm .

61)  describe  rowid structure

62)  MMON background process .

63) Contents of awr and statspack

64) in what cases will index wont be used

65) steps which u normally used for performance tuning

66)  which area is most imp in AWR report.

67) what is the importance of ASH report.

68) why enable row movement enable in 10g shrink

69) 10g sga_target and 11g memory target

70) what are metrics in 10g

71)  what are hot blocks

72) what are cost : when u see explain plan cost sometimes reflect 10 o r 20 .. what 

         that reflects

73) What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?

    Ans : Optimizer_index_cost_adj

74) What difference between using dbms package and analyze command to gather  

       table statistics.

Ans : analyze command does not allow use of dml statement while analyzing.

   75) Difference between sequential read and scattered read .

Ans : sequential read indicates index scan / single block read , scattered read indicates full table scan / multiblock read/ index fast full scan  .

   76) How to do table reorg without downtime.

Ans: using dbms_redefination which is new feature in 9i . first redefine a table and then then do move . in 10g we can use shrink command to do it online.

77) Which parameter of index to analyze before rebuild

   78) What does gathering statistics does / why we gather statistics .

   79) How to monitor chained rows in rule based optimizer .

Ans : using analyze .. list chained rows.

  80) can we gather statistics in case of rule based optimizer.

And : yes .

  81) Name some of the tuning database parameters .

  82) Tuning (whats difference between index single scan and index full scan)

 83) How to find buffer cache hit ratio from tkrpof.

84) Database parameter effecting sql statements.

85) if i don’t set a pga

86) assm .

87) what are reason for sql plan to change

Answer :  parsing , gather statistics .

88) contents of AWR .

89) how to come to know if my sql plan changed

Answer: dba_hist_sql_stats / dba_hist_sql_plan

90) how to confirm when my oracle initialization parameter was changed .

     Answer:

1)     enable sys operation audit

2)     enable alter system audit

     3)  awr report / WRH$_PARAMETER / dba_hist_parameter / alert log .

91) awr report : baseline : to  keep certain snap for long time

92) execution plan / explain plan

93) tkprof :6 contents :

94) logminer : 

95) query optimization :

96) buffer low :

97) lock : level

98) flush shared pool :  benefit and loss

99) checkpoint : different type , tune 

100) index rebuilding hamper

101) when does swapping occurs

102) In which phase of query execution is plan generated .

103) what can be used to evaluate new plan for query

104) which process updates the listener

105) elapsed time of query includes :

106) what  are sharedpool components .

107)  V$ACTIVE_SESSION_HISTORY (the ASH view) contains snapshots of active database sessions. How frequently are these snapshots taken?

108) What is the purpose of histograms?

109) A SQL statement has been running for 30 minutes, and is still on going. The statement uses bind variables. To help troubleshoot, you need to know what bind variable values were specified when the statement was executed. Where can you obtain this information from? Note: the statement is already running, so turning on SQL tracing / 10046 tracing is NOT the solution.

110) difference between ora-600 / ora-7445

111) (On UNIX) When should more than one DB writer process be used? How many

should be used?

If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is

not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB

writers should be specified by use of the db_writers initialization parameter.

112) What can cause a high value for recursive calls? How can this be fixed?

A high value for recursive calls is cause by improper cursor usage, excessive dynamic space

management actions, and or excessive statement re-parses. You need to determine the cause and

correct it By either re-linking applications to hold cursors, use proper space management techniques

(proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.

113)  how to find index  contention in awr   ()

114) how to enforce use of index   -- optimizer parameter

115)

DataGuard   ( Done - jan 2026 ) 

1)  what it does standby_file_management parameter?
2) what is mandatory parameter for standby database?
3) What is reinstate database in case of switch over environment

à Data guard { manual installation steps(cold backup + rman),  monitoring methods , how to add a standby logfile. , which process is responsible for log transport and which process is responsible for log apply., does v$log_history sows last archive applied.}

 à how to check data guard in sync without logging into the database.

Ans: check alert log of standby database to see last log archived.

à Difference between physical and logical dataguard .

à logical data guard is open in read mode while its getting updated

à not all data types are supported in sql apply .

à if fal_server is not set and if there is gap what needs to be done .(register + recover)

à how do we confirm whether mrp process is running .( ps -ef |grep mrp / alert/v$managed_standby for mrp process)

à how to cancel the recovery

à what are mrp and rfs process in data guard

à how to check whether archives are shipped from primary to standby?

      à v$archived_log (applied) on standby

This column is meaningful at the physical standby site for the ARCHIVED_LOG entries with REGISTRAR='RFS' (which means this log is shipped from the primary to the standby database). If REGISTRAR='RFS' and APPLIED is NO, then the log has arrived at the standby but has not yet been applied. If REGISTRAR='RFS' and APPLIED is YES, the log has arrived and been applied at the standby database

    à v$archived_dest_status 

à how to check archive gaps in standby?

à Data guard : what does fal indicates . , in standby_file_management= manual  what happens if datafile is not created on dataguard ,  if fal is not set how to

Apply missing log , renaming a file ,

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm

à Dataguard : protection mode , log transport service , log archive set 2 parameters ,

à fal server new feature in  11g

à what value  we set for fal_server and fal_client

à primary node parameters and standby parameters : db_unique_name

à how to activiate the dataguard

à log_file_name_convert

à your standby was shutdown for two hours, once u started it in mount stage, which 

     process will observe, recovery should be started?

à What’s difference between physical and logical standby database.

à standby : standby file management on production

      à benifit of dg broker , how to if we are using dataguard broker

à dataguard:  reopen , difference between standby and dataguard

      à in switchover why we give finish :

      à primary and standby both on  different solaris version 

      à what is default protection mode of dataguard .

      à FSFO : fast start failover is available from which version

      à What is CASCADING STANDBY database

      à Is it possible to configure primary server and stand by server on different OS?

        à what are different  log transport attributes =  sync , afrrm etc

    à  what are benefits of Active dg 

Rac and Asm  ( Done - jan 2026 ) 

1) How  do we reduce ASM rebalance   time
2) Why we need scan listener if we already have  Vip
3) how many scan listener we need .
4)  What  happens to Vip if  node attached to that vip reboots
5) How  to  recover from lost of ocr if you dont have backup
6)  List background  processes  specific to rac
7) What are rman methods to move  database to ASM
Answer :   Backup as Copy and   Rman  Convert
8) What is use of scan  listener
9) Why we create services
>  For load balancing
>  For  restricting  connections
>  Create services attached to trigger  enabled based on  database role
10) What is Gpnp profile
11)  What is  Use of HAIP
12)

à ocr file, voting disk ,

à which parameter of asm is responsible for load balancing (asm_power_limit )

à how we check rac status (crs_stat, crsctl check crs , ocrcheck , crsctl query css votedisk ) ,

à why there is separate undo for each nodes

ans : (for instance recovery: if node fails the surviving node performs instance recovery . one node has read only access to another node redo log and undo tablespace.) , 

à why we should not store voteting disk and ocr on asm 

Ans : (because ocr and voting disk are required for bringing crs resource up, which will bring node app service up first then the asm instance.).

à if we start the database with sqlplus , it does not reflect in crs_stat ,  what we need to do so it will reflect in that crsstat .

à asm uses which background process to interact with rdbms instance, which also exists in rdbms instance.

Ans : asmb

à for removing file using rm command of asmcmd , it does not delete the file if we are giving the name displayed there since these is alias , we need the file name from v$asm_disk .

à why we use switch and not lan card for private interconnect . also  why we use straight cable for private interconnect .

à what consideration we need to do before implementing rac .

à How to apply patch on RAC . ( upgrade + Migrate )

à recovering  datafile in RAC

à pfile  parameters for db creation in RAC?  cluster_database:  racdb1.undo_tablespace=UNDOTBS1 

      à purpose of cluster manager?

      à Is it necessary to stop all the instances while performing datafile recovery in RAC?

à RAC :  recovery in rac ,

à adding new node ,

à taf : select and session

à rac files  : voting disk ,  ocr disk , common files , common issue faced

à What is use of srvctl  in rac

à RAC : node eviction :

searchsystemschannel.techtarget.com/generic/0,295582,sid99_gci1254273,00.html

294430.1 --> node eviction .

high cpu , network , database  hung ,

  à rconfig , 10g : for converting single instance to  rac .

  à role of cache fusion and how cache fusion functions , (ges and gcs )

  à how to check if ocr is corrupted and how to handle It .

  à will clusterware will be affected if OCR is corrupted

  à how to restore ocr

  à will clusterware will be affected if voting disk is corrupted

        à how to restore voting disk

à how to backup  voting disk and ocr on asm

ans : backup method remains same . but for for voting disk . we must allocate

odd number of disk like 3,5 etc .and voting disk uses full disk as one voting disk .while for ocr  physical file is created on disk .

  à how  backup and restore asm metadata

  à how to check if voting disk is corrupted and how to handle it

  à how to check available backup of ocr and voting disk

  à manually take backup of ocr and voting disk .

  à how many oracle home and crs home we have .in rac . Do these home need to be 

       on shared location .

  à is it necessary that oracle home needs to be on same location on both nodes .

 à how many ocr files can we have

Ans : if asm , it depends  upon redundancy  level

à does ocr and voting disk exist on shared location .

à use of VIP and benefit of VIP  :

à ip configuration in 9i and 10g .

à why we need separate home for crs

à migrating non asm files to asm

à in asm can we have disk of different size .

à load balancing : how to monitor load on each node : service and awr

à asm rebalancing : how to monitor , how to put it off .

activities on rac :

à node addition

à upgradation

à patching

à putting db in archivelog

à adding space

à benefit of asm

à benefit of rac               

à challenges faced handling large database :

  à  Performance , backup ,

  à Why we have always odd no of voting disk ( 2*f +1).

Answer : You need more than 50% of properly working voting disks as prerequisite that clusterware works. That is the reason. If you have 3 disks you can loss one and clusterware will work. If you could have 2 if one fail whole clusterware would fail.

 à ASM doing the rebalancing among disk  all the time or on request.

Ans : whenver we add disk . also on demand .

 à in case one voting disk among three is corrupted , then its effect in OCR.

 à How to shutdown 20 non-rac database from a single command.

à RAC linux :  in /etc/sysconfig/rawdevices  what is major and minor ,

à RAC linux :  where does v$asm_file gets information from .

Ans : http://asmsupportguy.blogspot.in/2012/01/asm-file-number-1.html

à RAC : how to backup asm library ,  how to restore asm library , what if asm library is lost .

à user equivalence : what is use , can we disable user equivalence after rac installation .

à steps for restoring rac database : cluster_database

à can we restore ocr online ..

à is large_pool_size mandatory parameter for ASM

ANS : The INSTANCE_TYPE is the only mandatory parameter . large pool is required for parallel executions .

     à  oracle rac local and global inventory :

Ans : Global Inventory:

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)

You will see entry like

HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1″/

Local Inventory :

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

à what are rac gc wait events for

à steps for  Linux to add disk : start from fdisk

à what is node eviction in RAC .

à How to upgrade ASM instance in single node and rac .

à in rac ocr  disk are no automatically backed up . what we do At time of installation to  

     backup ocr disk automatically

à in rac : how do 1 node come to know that another node is not available

à in rac if  backup fails : may be because if 1 node down

à in rac if 1 node down . :how to restore node

à  what is buffer state , what is cr copy , if block is getting updated by one node and    

      other node wants to update same block how it functions , .

à what is remastering :

à rac : if 2 nodes modifies separate rows in same blocks and don’t commit it .. what 

    will happen .

à how to find master node :

Ans : The node with the lowest node number will become master node and dynamic remastering of the resources will take place.
To find out the master node for particular resource, you can query v$ges_resource for MASTER_NODE column.
To find out which is the master node, you can see ocssd.log file and search for "master node number".

à steps to install 3 node rac . (ip required etc)

à background processes in rac

à restore ocr at different location

à restore voting disk as different location

à  if stack is not up (effect on existing runing + on reboot)

à  if node looses contact with voting disk what happens

à what is node eviction and reasons of node eviction

à  if manually started database not reflecting in crsstat –t

à  how to create voting disk and ocr if we don’t have a backup .

à  use of voting disk + ocr file

à  cache fusion

à  if oracle home is deleted on one node what will we do

Answer:  run rootaddnode.sh or copy home to another node .

à change of VIP / interconnect / ip address

Answer : using oifcfg tool .

à 11g rac new feature of cluster :

  à can move file from asm to non asm

  à  scan

  à  in cache fusion how 1 node comes to know that block is been used by another node

  à  adding lun to voting disk .... how oracle recognize voting disk ( silent installation )

Answer :  create a raw device for it .

à can we run rac without VIP

Ans :  yes . we can remove vip after installation

à do we need enterprise edition for rac .. or we ca run rac in standard edition .

à 11g asm can have voting disk and ocr file

à what is rac stack .

à 3 node :  now many ip

à how many copy of ocr and voting disk

à name of cluster

à root.sh failed  3rd node (vipca)

à css information of all nodes

à what ons

à cpu patch in rac – downtime

à scripts which we run at end crs and oracle software installation and their use .

à rac : fan , ownership of ocr and voting disk .

à rac : can user connect using both : vip and public ip .(since listener only listen on vip)

à how to configure vip : difference between physical ip and virtual ip .

à remote listener and local listener

à Convert single instance to rac using rconfig .

à why use vip :

ans : 1) failover even if taf is not use . user can make connection using vip of down node

         2) no need to wait for TCP/IP timeout error dictation in case of node

           down

à rac : patching , rolling upgrade  patch .

à rac : node addition pre-requisite check ,

à rac : ::::  lun to raw using dd .

à In rac which process is responsible to ship block across node .          

Ans : LMS .

à which parameters  should  be same on all nodes

à  Explain Row Locking and Multi-version Read Consistency in RAC

  

- What type of Health Checks to be done on RAC databases?

- ORA-15196: invalid ASM block header [kfr.c:6030] [endian_kfbh] [3] [10778] [0 != 1]

- Formatting Journals: mkfs.ocfs2: Unable to find available bit while formatting journal "journal:0001

- How to enable ARCHIVE LOGS in RAC environment?

- Can you have many database versions in the same RAC?

- How do you add the node to existing RAC setup without downtime ?

- My storage is ASM and my archive log destination is full? Now what is the status of DB? how will you fix this?

- How can I take 2-node archivelog backup from 1-node ?

- My OCR file is corrupted and I don’t have the backup. What will happened to Database ? how will you resolve this?

- ORA-15063: ASM discovered an insufficient number of disks for diskgroup "string"

- What is the minimum size of the SGA for an ASM Instance? Does it has Controlfiles and Redo?

- How to enable Global Cache Services on an each Instance ?

- ASM : Explain Discovering Disks ? What parameter set ? What happens with out set parameter?

- What type of status available for Diskgroups? (V$ASM_DISK)

- Identify the current interfaces and IP addresses

- How to resolve VIP running manually last node in which the errors occurred.

- What are the components of ASM?

- Explain briefly ASM General architecture?

- RAC: Explain briefly ASMB Process?

- ASM Background Processes

- Installing Oracle RAC Fast Application Notification

- What Can Fast Connection Failover Do?

- Comparison of Fast Connection Failover and TAF

- Installing the cvuqdisk Package for Linux

- Grid Computing

- Difference - starting component with srvctl and sqlplus

- RAC: Steps to change SCAN IP address in 11gR2 RAC

- RAC : Parallel recovery

- How to get Extended RAC Connectivity ?

- RAC: Finding HAIP Interconnects (11R2)

- RAC: COMMON CAUSES OF CSSDAGENT OR CSSDMONITOR EVICTIONS

- RAC: Why Cluster Health Monitor ?

- RAC : Node Eviction - COMMON CAUSES OF OCSSD EVICTIONS

- rAC: Node Eviction - PROCESS ROLES FOR REBOOTS

- RAC: Disable Transparent HugePages on SLES11, RHEL6, OL6 and UEK2 Kernels

- RAC: The node rebooted because it was evicted due to missing network heartbeats.

- RAC: The node rebooted, but the log files do not show any error or cause

- RAC: The CRS recycled automatically, but node did not reboot

- RAC: Node is rebooted after asm or database instance hang or got evicted.

- RAC : Node reboot or eviction: How to check if your private interconnect CRS can transmit network heartbeats?

- RAC : 11gR2 Clusterware Startup Sequence

- How to Check Clusterware Version and Name ?

- CRS-4535: Cannot communicate with Cluster Ready Services

- Clusterware - What information does log in to Cluster logfiles ?

- Migrate Single Instance to RAC Instance

- Changing timezone in Oracle 11g RAC

- What is the significance of VIP address failover?

- Give situations under which VIP address failover happens:-

- Why should we have seperate homes for ASm instance?

- Difference between server-side and client-side connection load balancing?

- Verify that an instance has been removed from OCR after deleting an instance?

- What is OMOTION Utility ?  -- oracle one node

- what is the purpose of the ONS daemon?

- How many nodes are supported in a RAC Database?

- What is the purpose of Private Interconnect ?

- What is SCAN?

- What are Oracle Clusterware processes for 10g

- What are Oracle database background processes specific to RAC

- What are Oracle Clusterware Components

- Parameters That Must Have Identical Settings on All Instances

- Troubleshooting TAF Issues in 10g RAC

- What are different monitors in the RAC?

- What is the purpose of node eviction ?

- What processes can evict nodes from the cluster or cause a node reboot.?

- Do we have need handcheck-timer mod in 11G R2?

- What is handcheck-timer ?

difference between  start cluster and start crs

à if you have   scan  why we still need  vip

Golden Gate  : 

1)



Partitioning  

1) What are  different types of  partitioning
2)  Which  partition is used in which cases
3)   Why we  go for partitioning
Answer :   For performance improvement ,  reduce risk of  full table scans  ,  less  time of  table and index maintenance
4) what is partition pruning
5)  What is cardinality feedback
6)  When  does  dynamic sampling
7)  How to force  optimizer to use  index
8)





Memory related :    ( Done - jan 2026 )

1)  How AMM  is  related to huge pages

In Oracle, Automatic Memory Management (AMM) and HugePages are generally incompatible, as AMM uses /dev/shm (shared memory files) for SGA allocation, while HugePages require reserving large

2)  Difference between  AMM and  ASMM
3)  What  components are managed by AMM and what components are managed by ASMM




AWS:

1) Whats is difference between EC2 and RDS
2) what is data sharding



Other General    ( Done - jan 2026 )

1)     How do you tell what your machine name is and what is its IP address?

2)     You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

3)     What would you do with an “in-doubt” distributed transaction?

4)     How to change character set of database.

5)     What is current database size , export size , time for import / export

6)     Hit ratio

7)     Tablespace  free space , segment management , extent management , tablespace fragmentation management .

8)     What to do max number of datafiles exceeding .

           Ans : increase the database parameter db_files , no need to re-create the controlfile

9)     Rename a  database .(recreate a controlfile. Or use nid utility)

10)  Can we block /as sysdba in linux

      Ans : delete the password file , change sys user password and restart the database .

11) Get object source code (dba_source , dbms_metadata)

12) What is ora-1555 , and how is it resolved.

13) Explain the dependency of  oracle block size on o/s block size .

14) What is deadlock and how is it resolved.

15) difference between locally manage and dictionary managed tablespace.

à fragmentation , segment space management auto

16) on what basis do we decide the block size of tablespace

      ans : average row length of a table

17) difference between normal jfs  file system and raw file system .

18)  when are cpu patch released .

19) unix : group primary and secondary  + accord

20)  use of  catalog and catproc : which one to run first

catalog.sql = data dictionary views

catproc.sql = p/sql supported packages

20) What is new in expdp

21) On aix when we transfer file its time stamp changes , what we need to do so it does not changes the time stamp.

22) 10g : flashback ( enable / parameter ) , data pump (backup of procedure) , dbms_metadata new feature , sga_target (manages which sga processes / what if I give fix to one parameter , which bg process handles these ) , 10g transportable tablespace (cross platform/  endian format)  , in 10g we can use redo logs of before reset logs (what does r stands for) , new background processes .

23) What is endian format and incarnation of database .

24) Does  listener listen on 2 ports / how to configure it / how to confirm whether listener is listening on 2 ports .

25) Moving object to higher block size  tablespace .  deciding block size of table/tablespace .

26)  If given machine having dead database on dead machine and need to find 

à /etc/oratab /var/opt/oracle/oratab

27) What consideration shall be taken while making data wharehousing database .

à larger block size , etc.

28) Difference between dba_jobs and dba_scheduler

29) what is the backup strategy?

30) which file will be having all the files header information?

31) will it store the backup information in target database  control file if you are using   

            catalog?

32) RMAN enhancements in10g?

33) How to check whether listener is running in OS level?

34) How to find scn number in 9i and 10g .

35) Use of sysaux tablespace

36) New background processes in 10g .

37) Why prefer oracle over sql server .

38) Block corruption recover  methods :   a)block recover ,  b) some package

39) What are mandatory components of database . Can we add/remove  individual

Components .

      40)  pending offline state of old undo tablespace .

40) Different oracle sub versions of every versions 

41) Some 8i features.

42) Which process fetch data in buffer cache from datafile : server process .

43)  What are major L3 activities .

44) What is oracle mts .

45) Use of oracle inventory .

46) local listener / remote listener :

47) in 10g what if my inventory is reflecting corrupt in lsinventory

    rebuild inventory : 556834.1

    48)  to resize  sysaux tablespace what u consider

    @$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

    49) size of data , size of backup set (rman + expdp) , time taken (expdp + rman

    50)  when we avoid keeping segment space management auto .

   51)  at time of  fresh oracle installation when we run root.sh and rootpre.sh at end .

          what it does .

   52) what is use of oraInventory

   53)  what is use of oratab  ,

   54)  what if oratab lost ,

   55) what does root.sh  does

   56)  What is the recommended interval at which to run statspack snapshots, and why?

   57)  Why does Oracle not permit the use of PCTUSED with indexes?

   58)  Get object source code (dba_source , dbms_metadata

   59)  if  processes are high what ora error message  we get

   60) most frequent issue/ ora error  faced .

   61)  cross platform migration .

   62) partitioning :::   adding partition after max partition defined ( split partition  )

   63)  Explain the difference between a data block, an extent and a segment.

   64)  Compare and contrast TRUNCATE and DELETE for a table.

   65)  How would you determine the time zone under which a database was operating?

   66)  When a user process fails, what background process cleans up after it?

   67) You have just compiled a PL/SQL package but got errors, how would you view the

errors?

 68)   what is Archive_lag_target parameter what is default?

69) where password is stored(in which table) if you create any new user?




Patching / Upgradation :     ( Done - jan 2026 )

1)     Difference between upgradation and migration , steps for upgradation and migration.

2)     10g migration : backup of dblink , roll back / downgrade , revert compatibility to lower version

3)     How to lower the compatibility version , to roll back the migration .

4)     How check 10g migration was perfect .

à dba_registry

5)     10g upgrade : what happens when we  open in upgrade mode , what happens if some system objects are invalid after utlrp.sql .

6)     migrating 8i to 10g :

7)     upgrade from 10g to 11g

8)     what does catupgrade.sql does

9)     before migration need to set job_queue_processes to 0 .

10) oraenv / oratab :

11) 8i to 11g:

12)  before atsrating patch 

-- check opatch version 

-- analyze 

-- space check 

-- conflict check 

-- registry check  and invalid OBJECT check 

à How to apply cpu patch , bug patch .frequency of patch .

à Difference  between cpu and psu .

à After cpu/psu which script we run on database .

Ans : catbundle.sql psu/cpu apply

à migration pre checks , migratition steps , scripts that we run during migrations ,

      à cpu patch in rac