Oracle Architecture Interview Questions & Answers

  1. Question 1. Explain Briefly About Oracle Database Architecture?

    Answer :

    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. Question 2. Which Background Process Is Used During User Connectivity?

    Answer :

    PMON.

  3. Oracle 10g Interview Questions

  4. Question 3. What Are Base Tables? When And How They Will Get Created?

    Answer :

    Base tables are dictionary information of the database. They will be created at the time of database creation using SQL.BSQ script.

  5. Question 4. What Are Different Views Dba Uses? Which Script Will Create Them?

    Answer :

    We use data dictionary views to look into permanent information about the 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.

  6. Oracle 10g Tutorial

  7. Question 5. Why To Execute Catproc.sql Script?

    Answer :

    It will create necessary packages and procedures which DBA use for certain actions.

  8. Oracle 9i Interview Questions

  9. Question 6. Explain The Phases Of Sql Execution?

    Answer :

    SQL execution contains 3 phases:

    1. Parsing – in which syntax checking, semantic checking and dividing the statement into literals will be done
    2. Execution – in which parsed statement will get converted into ASCII format and will be executed
    3. Fetch – in which data will be fetched either from database buffer cache or database.
  10. Question 7. What Is Mean By Semantic Checking? Which Component Helps In That?

    Answer :

    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.

  11. Oracle 9i Tutorial
    Oracle 8i Interview Questions

  12. Question 8. What Is Server Process?

    Answer :

    It is a process created to help the user process either in reading/writing the data in the database.

  13. Question 9. What Is The Difference Between Physical And Logical Read?

    Answer :

    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.

  14. Oracle 11g Interview Questions

  15. Question 10. Why To Maintain A Copy Of Data In Database Buffer Cache?

    Answer :

    When the same query is ran by same or different user, data can be picked from buffer cache thus avoiding I/O and improving performance.

  16. Oracle 8i Tutorial

  17. Question 11. Why Server Process Will Not Start Searching From Mru End?

    Answer :

    If server process starts searching at MRU end,  there is a chance that data may get flush from buffer cache by the ime 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.

  18. Oracle apps Interview Questions

  19. Question 12. What Are The Logical Structures Of The Database?

    Answer :

    Tablespace, segment, extent and oracle data block are logical structures.

  20. Oracle 10g Interview Questions

  21. Question 13. What Are The 4 Mandatory Table Spaces Required To Run The Database?

    Answer :

    1. SYSTEM.
    2. SYSAUX.
    3. TEMP.
    4. UNDO.
  22. Oracle 11g Tutorial

  23. Question 14. Can I Have A Database Without Sysaux Tablespace In 10g?

    Answer :

    Yes, but load will be more on system tablespace.

  24. Question 15. If We Have Physical Structures, Why Do We Need Logical Structures?

    Answer :

    Logical structures are defined to provide the easiness in maintenance.

  25. Oracle Apps ERP Interview Questions

  26. Question 16. Explain The Difference Between A Block, Extent And Segment?

    Answer :

    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.

  27. Question 17. What Are Redo Entries? When And Where They Will Be Created?

    Answer :

    A single atomic chance 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.

  28. Oracle 7.3 Interview Questions

  29. Question 18. What Is Different Status For A Block In Database Buffer Cache?

    Answer :

    • UNUSED
    • FREE
    • PINNED
    • DIRTY.
  30. Oracle 9i Interview Questions

  31. Question 19. What Is Write-ahead Protocol?

    Answer :

    LGWR writing before DBWR is called write-ahead protocol.

  32. Question 20. Will There Be Any Undo Generated In Case Of Ddl Statement Processing? If So Why?

    Answer :

    Yes. Reason is even tough it is a DDL externally, it will be DML to base tables and to rollback them always oracle requires undo.

  33. Oracle ADF Interview Questions

  34. Question 21. What Is Pga?

    Answer :

    PGA is a memory area which is used to store user’s exclusive information like session information and helps in executing a query.

  35. Question 22. What Is The Difference Between Memory Allocation In 8i And 9i For Pga?

    Answer :

    In 8i, we need to set different individual parameters where as in 9i setting only one parameter PGA_AGGREGATE_TARGET would be enough.

  36. Question 23. Which Is Correct? Sorting Will Take Place In Pga Or Temp Tablespace?

    Answer :

    Both. If the data which to be sorted is more than sort area size of PGA, then temp tablespace will be used.

  37. Oracle Application Framework Interview Questions

  38. Question 24. How You Will Define Instance And Database?

    Answer :

    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.

  39. Oracle 8i Interview Questions

  40. Question 25. What Is Sga?

    Answer :

    SGA is a combination of different memory structures which helps in several actions in the database.

  41. Question 26. What Are The Responsibilities Of Smon?

    Answer :

    It will do instance recovery, coalesces the tablespace and will release the temp segments occupied by a transaction when it is completed.

  42. Oracle Apps Functional Interview Questions

  43. Question 27. Why Oracle Maintains 2 Redolog Files?

    Answer :

    To avoid any space constraints.

  44. Oracle 11g Interview Questions

  45. Question 28. What Is Scn And Which Process Writes That?

    Answer :

    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.

  46. Question 29. What Are The Other New Background Processes In 10g That You Know?

    Answer :

    1. MMAN – memory manager which helps in maintaining ASMM
    2. RVWR – helps in generating flashback logs incase flashback is turned on
    3. CTWR – hepls incremental backup in RMAN
    4. MMNL – memory monitor light, which helps in AWR statistics collection
    5. MMON – managebility monitor, which helps in automatic problem detection and self-tuning.
  47. Oracle Apps DBA Interview Questions

  48. Question 30. What Information Control File Contains?

    Answer :

    It contains latest SCN, all locations and sizes of datafiles and redolog files, database creation date and timestamp and controlfile parameters.

  49. Question 31. What Is Server Parameter File And How It Is Different From Parameter File?

    Answer :

    Spfile is a binary file and it helps in changing parameters efficiently than a pfile.

  50. Question 32. What Is Ifile And When It Is Used?

    Answer :

    Ifile is a index file which can help database when pfile or spfile are not in default location.

  51. Oracle Workflow Interview Questions

  52. Question 33. How To Recover If I Lost Parameter Pfile Or Spfile?

    Answer :

    We can recover from alert log file which contains non-default parameters.

  53. Oracle apps Interview Questions

  54. Question 34. If We Have Both Pfile And Spfile In Place, Which File Oracle Will Use During Startup?

    Answer :

    Spfile.

  55. Question 35. What Are Hidden Parameters And Their Use?

    Answer :

    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.

  56. Question 36. What Is The Purpose Of Password File?

    Answer :

    It is used to authenticate any user is connecting as SYSDBA from a remote machine.

  57. Oracle Apps ERP Interview Questions

  58. Question 37. 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?

    Answer :

    REMOTE_LOGIN_PASSWORD is not set to exclusive.

  59. Question 38. While Creating Password File, What Is The Use Of Entries?

    Answer :

    To specify how many users with sysdba role can connect to the database remotely.

  60. Question 39. What Is Asmm? Which Background Process Helps In That?

    Answer :

    Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA components automatically and MMAN is the background process helps in that.

  61. Question 40. How Database Will Behave When You Have Both Asmm And Individual Parameters Are Configured?

    Answer :

    Values of individual parameters will act as minimum and ASMM as maximum.

  62. Oracle 7.3 Interview Questions

  63. Question 41. You Increase 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?

    Answer :

    Kernel parameter SHMMAX may be reached. We should increase that first.

  64. Question 42. Which Parameter Helps Asmm To Be Affective? What Is The Value It Should Be Set To?

    Answer :

    SGA_TARGET and value is dependent on no of transactions in the database.

  65. Oracle ADF Interview Questions

  66. Question 43. What Is Amm In 11g?

    Answer :

    Managing both SGA and PGA automatically by oracle is called Automatic Memory Management (AMM).

  67. Question 44. My Database Size Is 1 Tb, How Much Sga Will You Configure?

    Answer :

    SGA size is not dependant on database size, it depends on how many transactions happening in the database.

  68. Question 45. What Is Alert Log? How It Is Helpful For Dba?

    Answer :

    It is file which helps in diagnosing all the errors occurred in the database.

  69. Question 46. What Are The Contents Of Alert Log File?

    Answer :

    Database startup and shutdown times, non-default parameters, any logical or physical structural changes and alloracle errors etc.

  70. Question 47. What Happens If We Remove Alert Log File When Database Is In Use?

    Answer :

    No effect on the database functionality. Oracle will create a new alert log file.

  71. Question 48. If We Have Alert Log File To Diagnose The Problem, Why We Need Trace Files? What Are They?

    Answer :

    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.

  72. Question 49. Which Background Process Is Responsible For Writing Into Alert Log File?

    Answer :

    All the background processes are responsible.

  73. Question 50. Which Process Will Start First When Instance Is Started?

    Answer :

    PSP0, process spawner. This background process will start other processes like PMON, SMON etc. But in alert log first it will show PMON.

  74. Question 51. Which File Is Required To Place Database In Nomount State And What Happens In That Phase?

    Answer :

    Pfile or spfile is required to bring database to NOMOUNT state and instance will be started (backgroung processes will be started and memory will be allocated to SGA from RAM) in this phase.

  75. Question 52. Which File Is Required To Bring Into Mount Phase And What Is The Use Of This Phase?

    Answer :

    Control file is required and it is for maintenance of some database actions.

  76. Question 53. What Files Are Needed To Open The Database And How Oracle Knows The Locations Of Them?

    Answer :

    Datafiles and redolog files are required to open the database and oracle will get that information from controlffiles.

  77. Question 54. What You Will Do When Shutdown Immediate Command Hanging For Last 30 Min?

    Answer :

    We can open another terminal and issue shut abort. Then once again startup and do shut immediate.

  78. Question 55. What Could Be Reason For Shutdown Immediate Command Hanging For Long Time?

    Answer :

    Might be there is a transaction which is large and rollback is happening for the same.

  79. Question 56. What Is Server Result Cache? What Benefit We Get Out Of It?

    Answer :

    It is a new component introduced in 11g. It will store the rows of a query directly this avoids the need of executing a statement.

  80. Question 57. What Parameters To Be Used To Make 11g Database Use Server Result Cache?

    Answer :

    SERVER_RESULT_CACHE.

  81. Question 58. How To Increase The Size Of Server Result Cache?

    Answer :

    By increasing the size specified by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.

  82. Question 59. Where All The Trace Files Located In 11g?

    Answer :

    It is in a single location defined by DIAG_DEST parameter.

  83. Question 60. Which Background Process Clears Sessions And Releases Locks When System Fails?

    Answer :

    SMON (here system means database).

  84. Question 61. What Is Ofa? Do You Recommend To Implement It?

    Answer :

    It is a rule which says database related files should split across multiple disks and yes i will recommend to implement it.

  85. Question 62. Why Oracle Recommends Ofa? How It Is Related To Os?

    Answer :

    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.

  86. Question 63. What Is Dispatcher In Shared Server Architecture?

    Answer :

    It is a service which server multiple user requests.

  87. Question 64. As A Dba, When You Will Take Decision To Enable Shared Server Architecture?

    Answer :

    When we observed ORA-04030 or ORA-04031 errors very frequently in alert log file.

  88. Question 65. You Configured All The Parameters To Enable Shared Server Architecture, But Still Users Facing Memory Allocation Issues. What You Would Do?

    Answer :

    We need to check if SERVER=SHARED is mentioned in client TNS entry or not.

  89. Question 66. What Are The Parameters You Use To Configure Shared Server Architecture?

    Answer :

    • DISPATCHERS
    • MAX_DISPATCHERS
    • SHARED_SERVER_PROCESSES
    • MAX_SHARED_SERVER_PROCESSES.
  90. Question 67. How Many Slave Process We Can Have For Dbwr And Smon?

    Answer :

    DBWR – 20, SMON – 16 .

  91. Question 68. I Connected To Database As Sysdba, But Its Not Allowing Me To Shutdown The Database, What May Be The Reason?

    Answer :

    The connection would have been shared server connection in which case oracle will not allow to shutdown/startup the database.

  92. Question 69. What Is Sga? Define Structure Of Shared Pool Component Of Sga?

    Answer :

    The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. 

    You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.

    Shared pool portion contain three major area:  Library cache (parse SQL statement, cursor information and execution plan), data dictionary cache (contain cache, user account information, privilege user information, segments and extent information, data buffer cache for parallel execution message and control structure.

  93. Question 70. What Is The Difference Between Pga And Uga?

    Answer :

    When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

  94. Question 71. What Is The Difference Between Data Block/extent/segment?

    Answer :

    A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

  95. Question 72. What Is Difference Between Oracle Sid And Oracle Service Name?

    Answer :

    Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.

  96. Question 73. What Are Bind Variables?

    Answer :

    With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

  97. Question 74. What Is Log Switch?

    Answer :

    The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch.  Sometimes you can force the log switch.

    ALTER SYSTEM SWITCH LOGFILE;

  98. Question 75. What Are Log File States?

    Answer :

    CURRENT state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.

    If a redo group containing redos of a dirty buffer that redo group is said to be ACTIVE state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).

    And when a redo log group contains no redo records belonging to a dirty buffer it is in an “INACTIVE” state. These inactive redo log can be overwritten.

    One more state UNUSED initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.

  99. Question 76. What Does Database Do During The Mounting Process?

    Answer :

    While mounting the database oracle reads the data from control file which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.

  100. Question 77. What Is The Use Of Large Pool, Which Case You Need To Set The Large Pool?

    Answer :

    You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.

  101. Question 78. What Happens When We Fire Sql Statement In Oracle?

    Answer :

    First it will check the syntax and semantics in library cache, after that it will create execution plan. 

    If already data is in buffer cache it will directly return to the client. 

    If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

  102. Question 79. What Is The Main Purpose Of Checkpoint In Oracle Database? How Do You Automatically Force The Oracle To Perform A Checkpoint?

    Answer :

    A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.

    The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.

    LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour 
    LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.

  103. Question 80. What Is A System Change Number (scn)?

    Answer :

    • SCN is a value that is incremented whenever a dirty read occurs.
    • SCN is incremented whenever a deadlock occurs. 
    • SCN is a value that keeps track of explicit locks. 
    • SCN is a value that is incremented whenever database changes are made.