Oracle Architecture Interview Questions and Answers

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

PMON.

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

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.

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

SQL execution contains 3 phases:

Parsing – in which syntax checking, semantic checking and dividing the statement into literals will be done

Execution – in which parsed statement will get converted into ASCII format and will be executed

Fetch – in which data will be fetched either from database buffer cache or database.

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.

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

If we fetch data from database buffer cache, then it’s called logical read. If we fetch it from database, it’s called physical read as it includes an I/O operation.

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

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.

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

SYSTEM, SYSAUX, TEMP and UNDO.

Yes, but load will be more on system tablespace.

Logical structures are defined to provide the easeness in maintenance.

Block is a basic storage unit whereas extent is a small memory area allocated to a table and segment is a object which occupies space.

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.

UNUSED, FREE, PINNED and DIRTY.

What is write-ahead protocol?

LGWR writing before DBWR is called write-ahead protocol.

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.

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

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

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

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.

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

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

To avoid any space constraints.

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.

MMAN – memory manager which helps in maintaining ASMM

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

CTWR – hepls incremental backup in RMAN

MMNL – memory monitor light, which helps in AWR statistics collection

MMON – managebility monitor, which helps in automatic problem detection and self-tuning.

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

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

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

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

Spfile.

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.

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

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

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

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

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

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

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

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

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

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.

All the background processes are responsible.

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

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.

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

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

[dt_sc_toggle title=”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.

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

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

Read how a select statement will process in 11g from class notes.

SERVER_RESULT_CACHE.

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

It is in a single location defined by DIAG_DEST parameter.

SMON (here system means database).

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

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.

It is a service which server multiple user requests.

Explain shared server architecture

Answer is in class notes.

DISPATCHERS

MAX_DISPATCHERS

SHARED_SERVER_PROCESSES

MAX_SHARED_SERVER_PROCESSES.

DBWR – 20, SMON – 16.