Oracle System Architecture Interview Questions
Program GLobal area (pga) is a memory buffer that contains data and control information for a server process.
What is database security? How will you use it?
That is Acl . Those levels are
Manager,
designer,
editor,
author,
reader,
depositor,
no access
What is sga? Where it is located? Why the system performance will be slow after loading the Oracle?
Dont install oracle on C:(or on which drive you installed OS). Its not sure that it gives you great result, it better result then previous one.
sga stands fro system global area.. which is the part of the oracle instance(includes memory struture and background processes). for faster executon and better performance everthing comes in the sga.s...
What do database buffers contain ?
Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet been permanently written to disk.
Database Buffer cache has four states: Pinned: multiple sessions are kept from wirting to same block @ same time. Other session waits to access these blocks Clean : Now the session is unpinned ...
Database buffers consists of the dirty blocks. When ever we are trying to update some table/etc. First it verifies whether the data exists in database buffer , If not it fetches the information from d...
What are the components of sga ?
Database buffers, redo log buffer the shared pool and cursors.
SGA has six parts on it
Shared pool
JAVA POOL
Stream Pool
Large Pool
Data Buffer cache - MRU, LRU
Redo log buffer
Yes. they are part of SGA. Sharedpool comprises of Library cache and data dictionary cache.
Why is Oracle called kernel package?
Oracle introduced the Unbreakable Enterprise Kernel for Oracle Linux during Sep 2010 which runs directly on Oracle LINUX 5 with fast, modern, reliable kernel that is optimized for Oracle software and hardware.
What is the approach taken by Oracle to pass acid test of a database transaction?
What is a cluster and how it is used
An Oracle Database Cluster is a set of database instances on different nodes accessing the database related files on a shared storage.
the deployment of a single database across a cluster of servers—providing unbeatable fault tolerance, performance and scalability
What constitute an Oracle instance ?
Sga and Oracle background processes constitute an Oracle instance. (or) combination of memory structure and background process.
Oracle Instance is an combination of background process and memory structures.
Oracle instance is the combination of SGA and
background processes.
When user sends a connection request server process starts and it starts instance.
What is the function of checkpoint (ckpt)?
The checkpoint (ckpt) process is responsible for signaling dbwr at checkpoints and updating all the data files and control files of the database.
Ckpt (checkpoint) is a background process. It synchronize SCNof all the database files likedatafiles, control files and redo log files.
checkpoint is backgroud process. it'll occur in some situations like during log switch, or on the basis of log_checkpoint_interval or log_checkpoint_timeout parameter in parameter file. ...
The system GLobal area (sga) is a shared memory region allocated by Oracle that contains data and control information for one Oracle instance.
System Global Area (SGA) is component of instance which consist shared memory & control information of the instance
It consist of shared pool, database buffer cached, redo log buffer, large pool, Java pool.
It is a shared memory region allocated by oracle that contains data and control information for an Oracle Instance. An SGA compromises of Buffer Cache, Redo Log Buffers and Shared Pool Area.
When does dbwr write to the database ?
Dbwr writes when more data needs to be read into the sga and too few database buffers are free. The least recently used data is written to the data files first. Dbwr also writes when checkpoint occurs.
DBWR writes
* when checkpoint occures
* when dirty buffers reach threshold
* when RAC ping requested
* when free buffers are not available
* when tablespace taken offline,droped
DBWn writes when:
1)checkpoint occurs
2)dirty buffers reach threshold
3)there are no free buffers
4)timeout occurs
5)RAC ping request is made
6)when any tablespace is taken offline,read only,drop or truncated & when begin backup
When does lgwr write to the database ?
Lgwr writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.
Log writer writes
* when transaction commits
* every three seconds
* when 1/3 of redo full
* when 1mb of redo generated
* before dbwn writes
The LGWR writes redo log entries into an on-line redo log file when
1. transaction is committed.
2. More than 1 MB of redo log buffer enteries.
3. 1/3 of the redo log buffer is full.
4. Every 3 seconds.
What are the functions of smon ?
System monitor (smon) performs instance recovery at instance start-up. In a multiple instance system (one that uses the parallel server), smon of one instance can also perform instance recovery for other instance that have failed smon also cleans up temporary segments that...
If server unplanned shutdown, Power failiure SMON Instant recovery. there is two type of Instant recovery 1st is Roll forward 2nd Roll back.
Rollback means comitted transaction.
Rollforward means uncommitted transaction.
If server unplanned shutdown, Power failure SMON Instant recovery. there is two type of Instant recovery 1st is Roll forward 2nd Roll back.
Rollback means committed transaction.
Rollforward means uncommitted transaction.
Shared pool is a portion of the sga that contains shared memory constructs such as shared SQL areas.
Shared Pool compromises of Library Cache and Dictionary Cache.Library Cache stores and shares sql stmts where as plsql stmts in memory.Dictionary Cache: Oracle continously request and update informati...
Hi,Actually Shared pool is composed of two parts one is Library Cache and another Data Dictionary cache The Library cache contains the shared sql areas, private sql areas, PL/SQL procedures and packag...
What do redo log buffers contain ?
Redo log buffer stores redo entries a log of changes made to the database.
It stores redo log entries a log of changes made to a Database.
Redo log buffers consists of change vectors where each vector contains information about which block is changed and where is the change done in the memory. This information will be useful when you are...
A cursor is a handle ( a name or pointer) for the memory associated with a specific statement.
cursor is nothing but it is temprary storage area.In other word cursor is a private sql work area whenever issuing sql and pl/sql statements ,it creates a memory area in server.this memory area is know as cursor area.
A cursor can be viewed as a "Pointer" into the result table of an SQL SELECT statement. Cursors are needed whenever the result contains more than one row. 3rd generation programs like COBOL and ...
A shared SQL area is required to process every unique SQL statement submitted to a database and contains information such as the parse tree and execution plan for the corresponding statement.
HiShared SQL areas are shared memory areas; any Oracle process can use a shared SQL area. The use of shared SQL areas reduces memory usage on the database server, thereby increasing system throu...
Log writer (lgwr) writes redo log entries generated in the redo log buffer of the sga to on-line redo log file.
LGWR is oracle background process.. all changes which have been made inthe database gets recorded in the redolog buffer.. so when the users fires an commit statement , LGWR writes these changes ...
hi,It is one of the main background process in oracle . In oracle and unix environment you can check this process using ps -ef |pg command . which is writting all the uncommited transaction from SGA to redologfile .regrdsrajat dey
User Panel
Categories
Interview Coaching
Ads
Connect
Ads
Interview Question
Latest Questions
Ads
Interview & Career Tips
Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.
NH
Pga is a memory area that contains data and control information for the oracle server processes. The size and contains of the pga depends on the Oracle server options we have installed.
Main function of PGA is SORTING(Order by, Join and so on) will takes place on PGA memory.. If PGA memory is not enough in such cases temporary tablespaces will be used.