Oracle Questions
1) What is the default location for init<SID>.ora file or spfile<SID>.ora for a database?
The default location for the spfile or init file of the database is in
%ORACLE_HOME%/database folder in Windows and
$ORACLE_HOME/dbs directory in case of Unix systems.
2) what is the use of oracle inventory?
Oracle inventory stores the information of all the
Oracle products installed in the system.
The OUI(Oracle Universal Installer) uses this oraInventory to
determine all the product versions which are installed in the system.
3) How to determine the location of Oracle Inventory on Unix systems?
In Solaris system there exists a file called oraInst.loc in the location
/var/opt/oracle/oratab. This file stores the information of the location of oraInventory.
While in case of HP UX oraInst.loc is located in
/etc/oratab directory which gives the location of oraInventory.
4) What is an Oracle Server?
Oracle Server consists of Oracle Instance and Oracle Database.
5) What is an Oracle Instance?
An Oracle Instance consists of Memory structures
(SGA/PGA) and background processes.
The Memory structure include sga nd pga.
SGA consists of shared pool,db buffer cache,log buffer
and optionally large pool and java pool
PGA is used to store bind variable,cursor,sort variables
There 5 mandatory background processes viz PMON,SMON,DBWR,LGWR,CKPT
and other background process also constitute the oracle instance like ARCH,RECO
6) What is the use of shared pool?
Shared pool is used to cache recently used sql statements so as to reduce the overall response time of the sql query if they are executed consequently by the same of diffrent user. Shared pool consists of library cache,row cache and uga(in case of shared server configuration).Shared pool is flushed using LRU algorithm
7) What is use of libarary cache?
It is used to cache execution plan of the recently executed sql queries
so that subsequent execution of the same queries can be faster
8) what is the use of row cache?
It is use to cache the data dictionary information stored into system tablespace.
9)What is the use of the DB Buffer Cache?
The DB Buffer cache is used to cache most recently accessed data.
The tables,indexes which stores the data in the datafiles in oracle database.Caching the data in the SGA make the execution of the query faster and reduces the response time.
DB buffer cache is also based on LRU algorithm.
10) What is the use of log buffer?
Log buffer is used to store redo information generated while manipulating the data in the
oracle database.Redo information is used for recovery. The redo information is flushed from
log buffer to online redo log files of the Oracle Database in the circular fashion.