300+ [LATEST] Oracle Architecture Interview Questions and Answers

Q1. What Is The Purpose Of Password File?

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

Q2. Which Is Correct? Sorting Will Take Place In Pga Or Temp Tablespace?

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

Q3. Where All The Trace Files Located In 11g?

It is in a single location defined by DIAG_DEST parameter.

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

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.

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

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

Q6. How Many Slave Process We Can Have For Dbwr And Smon?

DBWR – 20, SMON – 16 .

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

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

Q8. What Parameters To Be Used To Make 11g Database Use Server Result Cache?

SERVER_RESULT_CACHE.

Q9. What Is The Difference Between Physical And Logical Read?

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.

Q10. What Are Base Tables? When And How They Will Get Created?

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

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

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

Q12. What Is The Difference Between Data Block/extent/segment?

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.

Q13. 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?

REMOTE_LOGIN_PASSWORD is not set to exclusive.

Q14. What Is Server Result Cache? What Benefit We Get Out Of It?

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.

Q15. What Is Alert Log? How It Is Helpful For Dba?

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

Q16. What Is Server Parameter File And How It Is Different From Parameter File?

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

Q17. What Is Scn And Which Process Writes That?

SCN is a unique number assigned to a traction which modifies data in the database. CKPT process will always update this SCN to both datafiles header and control file.

Q18. What Is Mean By Semantic Checking? Which Component Helps In That?

Semantic checking me checking for the privileges for the user or in other words authorizing the user. Base tables or dictionary will help in doing this.

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

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.

Q20. What Are Hidden Parameters And Their Use?

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.

Q21. How To Recover If I Lost Parameter Pfile Or Spfile?

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

Q22. What You Will Do When Shutdown Immediate Command Hanging For Last 30 Min?

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

Q23. What Is Pga?

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

Q24. What Are The 4 Mandatory Table Spaces Required To Run The Database?

  1. SYSTEM.
  2. SYSAUX.
  3. TEMP.
  4. UNDO.

Q25. Which Background Process Clears Sessions And Releases Locks When System Fails?

SMON (here system me database).

Q26. What Are Bind Variables?

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.

Q27. What Is Difference Between Oracle Sid And Oracle Service Name?

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.

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

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.

Q29. What Is Ifile And When It Is Used?

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

Q30. What Does Database Do During The Mounting Process?

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.

Q31. What Is Asmm? Which Background Process Helps In That?

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

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

Spfile.

Q33. What Is The Difference Between Pga And Uga?

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).

Q34. What Is Log Switch?

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;

Q35. What Are The Other New Background Processes In 10g That You Know?

  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.

Q36. What Could Be Reason For Shutdown Immediate Command Hanging For Long Time?

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

Q37. How To Increase The Size Of Server Result Cache?

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

Q38. What Is Sga? Define Structure Of Shared Pool Component Of Sga?

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.

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

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

Q40. What Is Ofa? Do You Recommend To Implement It?

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

Q41. My Database Size Is 1 Tb, How Much Sga Will You Configure?

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

Q42. What Are The Responsibilities Of Smon?

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

Q43. Explain The Difference Between A Block, Extent And Segment?

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.

Q44. What Are Log File States?

CURRENT state me 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.

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

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

Q46. Can I Have A Database Without Sysaux Tablespace In 10g?

Yes, but load will be more on system tablespace.

Q47. What Is A System Change Number (scn)?

  • 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.

Q48. What Information Control File Contains?

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

Q49. Which Background Process Is Responsible For Writing Into Alert Log File?

All the background processes are responsible.

Q50. Why Server Process Will Not Start Searching From Mru End?

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.