Interview questions from ORACLE OD

Hi,
Below are some of the interview questions of ORACLE OD:

1)Describe oracle 9i Architecture?
Ans:


This is one of the basic question asked in interview.To explain in brief,oracle 9i Server consist of
1)ORACLE INSTANCE &
2)ORACLE DATABASE
1)ORACLE INSTANCE:Oracle Instance consists of SGA and group of mandatory back ground processes like SMON,PMON,DBWR,LGWR & CKPT

SGA:SGA Consist of Shared pool which in turn consists of library cache and Data Dictionary cache.In the Library cache recently executed SQL,PL/SQL statements are stored.Dictionary cache is used to give privileges to the users or roles.

Database buffer cache:The database buffer cache is a portion of the System Global Area (SGA) , which is responsible for caching the frequently accessed blocks of a segment. The subsequent transactions requiring the same blocks can then access them from memory, instead of from the hard disk. The database buffer cache works on the basis of the least recently used (LRU) algorithm, according to which the most frequently accessed blocks are retained in memory while the less frequent ones are phased out.

Redolog Buffer cache:A log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in the redo entries. Redo entries contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations. Redo entries are primarily used for database recovery as necessary.

The server processes generate redo data into the log buffer as they make changes to the data blocks in the buffer. LGWR subsequently writes entries from the redo log buffer to the online redo log.

Program Global Area (PGA)

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a non-shared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. It contains a private SQL area and a session memory area

JAVA Pool:The JAVA Pool holds the JAVA execution code,used by export/import activities.

LARGE POOL:
Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.

1. Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved
2. I/O Server Processes
3. Parallel Query Buffers
4. Oracle Backup and Restore Operations using RMAN

Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead

UGA:The User Global Area (UGA) is a memory area (RAM) that holds session-based information.

UGA comes in to picture only in case of Shared server connection because of the below reasons:

Dedicated Server :
When running in Dedicated Server mode (one session = one dedicated process), the UGA is stored in the PGA (process global area).

Shared Server:
When running in Shared Server mode (MTS(Multi-threaded server)) with shared servers and dispatchers), sessions can be served by multiple server processes. As a result, the UGA cannot be stored in the PGA, and is moved to the SGA (shared global area).

Mandatory Background process and their short descriptions are as follows:
-------------------------------------------------------------------------

1)SMON:The System Monitor process performs instance recovery when a failed instance starts up again.
2)PMON:The Process Monitor process performs process recovery when a user process fails . PMON is responsible for cleaning up the cache and freeing resources that the failed process was using and for checking on the dispatcher and server processes and restarting them if they have failed.
3)DBWR:Database Writer writes all modified blocks from the database buffer cache out to the proper datafiles.
4)LGWR:The Log Writer process writes redo log entries from the redo log buffer out to the online redo logs on disk. If your database has multiplexed redo logs, log writer writes the redo log entries to the entire group of online redo log files and commits only when all the redo has been written to disk.
5)CKPT:The checkpoint ensures that the checkpoint number is written into the datafile headers and along with the log sequence number, archive log names (if the database is in archive log mode), and the system change numbers is written into the control file.
i.e The checkpoint process is responsible for syncronization of the Datafiles and control file with the same SCN number.
Note:CKPT does not write blocks to disk; DBWn always performs this task. CKPT triggers DBWR to write the blocks out to their respective files.

Archiver (ARCn)
Archiver (an optional process) copies the online redo log files to archival storage after a log switch has occurred for databases running in archive log mode. Although a single archiver process (ARC0) is usually sufficient for most systems, you can specify up to 10 ARCn processes by using the dynamic initialization parameter LOG_ARCHIVE_MAX_PROCESSES. If the archiving workload gets to be too much for the current number of archiver processes, log writer automatically starts another archiver process up to the maximum of 10 processes. ARCn is active only when a database is in ARCHIVELOG mode and automatic archiving is enabled.

Recoverer (RECO)
The Recoverer (an optional process) is used to resolve any distributed transactions left pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to the associated remote databases and, if it is successful, automatically complete the commit or rollback of the local portions of any pending distributed transactions


Physical structure of the Database:
physical components of oracle database are control files redo log files and datafiles.
1)Datafiles:Datafiles are the physical files which stores data of all logical structure.

2)Control file: control file is read in the mount state of database. control file is a small binary file which records the physical structure of database which includes
*database name
*names and locations of datafiles and online redo log files.
*timestamp of database creation
*check point information
*current log sequence number.

3)Redo log files: Redo log files saves all the changes that are made to the database as they occur. This plays a great role in the database recovery.


2)How to check concurrent manager is up & running ?
Ans: One way to see if a concurrent manager is up & running is to use the 'Administer concurrent Managers' form.
Navigate to Concurrent->Managers->Administer.You will see two columns labeled 'Actual' and 'Target'.The Target column lists the number of processes that should be running for each manager for this particular workshift.The Actual column lists the number of processes that are actually running.If the Actual column is zero,there are no processes running for this manager.If the Target column is zero,then either a workshift does not specify any target processes.If the target column is not zero,then the manager processes have either failed to start up,or gone down.We should check the manager's logfile and the ICM(Internal concurrent manager) logfile.
Note:It is possible for the form to be inaccurate,i.e it may show actual processes even thought they are not really running.So check the below command.
(OR)
In Unix environment:We can also check for OS processes using the 'ps' command.
$ps -ef|grep FNDLIBR
In windows environment:We can check to see if the concurrent manager services is running using the service control panel.

3)How to check the Apache or Http server is up and running?
Ans: We can use the below command:
$ps -ef|grep httpd

4)What is resumable space allocation?
Ans: Resumable Space Allocation
Long running operations such as imports and batch processes sometimes fail because the server is unable to allocate more extents for an object. This may be because the object has reached max_extents or there isn't sufficient room in the tablespace for the object to expand. In previous releases the operation would have to be rerun, possible with some manual cleanup necessary. In Oracle9i operations that would fail due to space allocation problems can be suspended and restarted once the problem is fixed.

It can be enabled or disabled as follows:
ALTER SESSION ENABLE RESUMABLE;
ALTER SESSION DISABLE RESUMABLE;

5)How to create password file in oracle & List the options used to create the password file?
Ans: We can create a password file using the password file creation utility,ORAPWD .
Eg:orapwd FILE=orapworcl ENTRIES=30
Where:
FILE :Name to assign to the password file.We must specify the full path name for the file.
ENTRIES
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.


FORCE
This argument, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE
If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.

6)What is conflict Resolution Manager in oracle Application?Ans: Concurrent Managers read requests to start concurrent programs running.The conflict Resolution Manager checks concurrent program definitions for incompatibility rules.
If a program is identified as Run Alone,then the Conflict Resolution Manager prevents the concurrent manager from starting other programs in the same conflict domain.
when a program lists other programs as being incompatible with it,the conflict Resolution Manager prevents the program from starting until any incompatible
programs in the same domain have completed running.

7)How to check the process consuming more cpu & what action you will take on this?
Ans: At the OS level we can use the below commands:
sar & top to get CPU utilization details.

sar -u 2 5
-u=>user level
Report CPU utilization for each 2 seconds. 5 lines are displayed.
(OR)
$top -c
Database level:
------------------
The join query involving mainly v$process,v$session can also be used to get CPU and I/O consumption of the users:

Users and Sessions CPU and I/O consumption can be obtained by below query:
-- shows Day wise,User wise,Process id of server wise- CPU and I/O consumption
set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and round((ss.value/100),0) > 10
order by 8;

8)What will be the state of Database when the current online redolog gets corrupted?
Ans: Database should be up and running.

If the CURRENT redo log is lost and if the DB is closed consistently, OPEN RESETLOGS can be issued directly without any transaction loss.
SQL>Alter Database open resetlogs;
For more such scenarios check the below link:
http://drdatabase.wordpress.com/2009/03/13/missing-redo-logs-scenario/

9))What will happen to Database when any of the mandatory Background process gets killed?
Ans: Instance should get crashed.

10)What is ORA-60 error?
Ans: ORA-60 is the Deadlock error.We can see this error in the alert log file of the Database.For more detail regarding this error check the alert log file which will tell you the trace file location which is usually present in UDUMP Directory(User related error and trace file Directory location).We can check this trace file to see which session is blocking which session and we can also get the exact SQL statement which is causing the deadlock from this trace file.Usually Deadlocks are detected and resolved automatically by oracle.In most of the cases I Found Deadlock happens due to Application designing issue.In order to prevent them happening next time we need to change the Application design which is causing deadlock.

11)How often you take production Database backup?What kind of Backup do you take?
Ans: We have to be very careful when answering Backup strategy related questions usually we don't shut down or bounce our production Database daily,production database is bounced weekly once to take cold backup.But we take hot backup/online backup monday to Saturday after export backup.Cold backup is done only on sundays after doing logical export backup in my current organization.

Backup strategy we follow for production Database is as follows:

1)Full export at 3 am followed by cold backup on sunday.
2)Full export at 6 am followed by online backup on monday-saturday.

We usually take hot and cold backup for our production Database.


Hope these questions help in interview preparation and interview clearance.


Best regards,

Rafi.