Interview Questions.

Q1. What Is The Purpose Of Password File?

It is used to authenticate any consumer is connecting as SYSDBA from a far off gadget.

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

Both. If the facts which to be sorted is extra than sort location size of PGA, then temp tablespace will be used.

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

It is in a unmarried place defined by means of 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 can not provide whole records approximately the mistake, wherein case it will generate trace file. Depends on the mistake, it'll generate historical past, core or consumer hint documents.

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

SGA_TARGET and value is depending on no of tractions within 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 referred to in customer TNS access or now 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 records from database buffer cache, then its referred to as logical read. If we fetch it from database, its called bodily read as it includes an I/O operation.

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

Base tables are dictionary facts of the database. They will be created on the time of database creation the usage of SQL.BSQ script.

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

Control report is needed and it is for maintenance of a few database movements.

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

A statistics block is the smallest unit of logical storage for a database item. As gadgets develop they take chunks of extra storage that are composed of contiguous records blocks. These groupings of contiguous records blocks are called extents. All the extents that an item takes while grouped collectively are taken into consideration the section of the database item.

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 isn't always set to specific.

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

It is a new issue brought in 11g. It will store the rows of a query immediately this avoids the want of executing a announcement.

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

It is file which facilitates in diagnosing all of the mistakes befell in the database.

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

Spfile is a binary document and it allows in converting parameters successfully than a pfile.

Q17. What Is Scn And Which Process Writes That?

SCN is a completely unique number assigned to a traction which modifies statistics within the database. CKPT method will continually replace this SCN to both datafiles header and control record.

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

Semantic checking me checking for the privileges for the person or in different words authorizing the person. Base tables or dictionary will assist 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 deliver database to NOMOUNT kingdom and instance will be began (backgroung tactics might be started and memory could be allotted to SGA from RAM) in this section.

Q20. What Are Hidden Parameters And Their Use?

Hidden parameters are to be used best on advice of oracle assist and every now and then they help us in supplying work round to any serious problem within the database.

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

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

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

We can open every other terminal and issue close abort. Then once again startup and do shut instantaneous.

Q23. What Is Pga?

PGA is a reminiscence region that is used to shop person’s one-of-a-kind information like consultation statistics and allows in executing a question.

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

SYSTEM.

SYSAUX.

TEMP.

UNDO.

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

SMON (right here system me database).

Q26. What Are Bind Variables?

With bind variable in SQL, oracle can cache queries in a unmarried time inside the SQL cache place. This avoids a tough parse every time, which saves on numerous locking and latching aid we use to test object existence and so forth.

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

Oracle SID is the specific name that uniquely identifies your example/database wherein because the carrier name is the TNS alias can be same or one of a kind 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  principal purposes: To set up a statistics consistency and enable faster database Recovery.

The following are the parameter with the intention to be utilized by DBA to modify time or interval of how often its checkpoint need to arise in database.

LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour 

LOG_CHECKPOINT_INTERVAL = one thousand; # quantity of OS blocks.

Q29. What Is Ifile And When It Is Used?

Ifile is a index document that may assist database while pfile or spfile are not in default place.

Q30. What Does Database Do During The Mounting Process?

While mounting the database oracle reads the records from control record that's used for verifying bodily database files during sanity test. Background strategies are commenced earlier than mounting the database only.

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

Setting SGA_TARGET parameter is referred to as ASMM. This enables in coping with all SGA additives automatically and MMAN is the heritage procedure facilitates 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 walking devoted server then process facts saved in the manner global location (PGA) and when you are the usage of shared server then the process information saved internal person global area (UGA).

Q34. What Is Log Switch?

The point at which oracle ends writing to one on line redo log record and starts offevolved writing to another is called a log transfer.  Sometimes you could force the log switch.

ALTER SYSTEM SWITCH LOGFILE;

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

MMAN – memory manager which enables in retaining ASMM

RVWR – enables in generating flashback logs incase flashback is turned on

CTWR – hepls incremental backup in RMAN

MMNL – memory screen light, which enables in AWR facts series

MMON – managebility display, which enables in automated problem detection and self-tuning.

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

Might be there may be a traction that's large and rollback is taking place for the same.

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

By growing the scale exact by way of SERVER_RESULT_CACHE or via growing MEMORY_TARGET parameter if AMM is used.

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

The device global location is a group of shared reminiscence vicinity that is dedicated to oracle instance. All oracle system makes use of the SGA to preserve information. The SGA is used to store incoming information and internal manage information that is wanted by means of the database. 

You can manage the SGA memory through placing the parameter db_cache_size, shared_pool_size and log_buffer.

Shared pool portion incorporate 3 fundamental place:  Library cache (parse SQL assertion, cursor statistics and execution plan), records dictionary cache (contain cache, person account facts, privilege consumer facts, segments and volume facts, records buffer cache for parallel execution message and manipulate shape.

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

Values of individual parameters will act as minimal and ASMM as most.

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

It is a rule which says database associated documents ought to break up across a couple of disks and yes i will suggest to put in force it.

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

SGA size isn't always dependant on database length, it relies upon on what number of tractions going on inside the database.

Q42. What Are The Responsibilities Of Smon?

It will do example recuperation, coalesces the tablespace and could release the temp segments occupied by way of a traction while it is finished.

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

Block is a simple garage unit in which as quantity is a small memory region allotted to a desk and section is a item which occupies area.

Q44. What Are Log File States?

CURRENT state me that redo information are currently being written to that organization. It could be till a log transfer happens. At a time there may be most effective one redo organization modern-day.

If a redo organization containing redos of a grimy buffer that redo institution is stated to be ACTIVE country. As we know log report maintain changes made to the statistics blocks then records blocks are changed in buffer cache (grimy blocks). These dirty blocks need to be written to the disk (RAM to permanent media).

And whilst a redo log organization incorporates no redo data belonging to a dirty buffer it's miles in an "INACTIVE" state. These inactive redo log can be overwritten.

One extra kingdom UNUSED initially when you create new redo log institution its log document is empty on that time it is unused. Later it could be any of the above noted state.

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

In 8i, we need to set one of a kind character parameters where as in 9i putting most effective one parameter PGA_AGGREGATE_TARGET would be sufficient.

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

Yes, however load might be extra on gadget tablespace.

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

SCN is a fee this is incremented on every occasion a dirty examine happens.

SCN is incremented whenever a deadlock takes place. 

SCN is a cost that maintains song of express locks. 

SCN is a value that is incremented whenever database modifications are made.

Q48. What Information Control File Contains?

It includes ultra-modern SCN, all locations and sizes of datafiles and redolog documents, database creation date and timestamp and controlfile parameters.

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

All the history methods are accountable.

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

If server procedure starts offevolved searching at MRU end,  there may be a chance that facts may additionally get flush from buffer cache by way of the ime it reaches LRU quit. In that case again to fetch information, we want to do an I/O which is pricey. So oracle designed its architecture that server manner will search most effective from LRU stop.