What is OS Watcher and how it works

Oracle OS Watcher (OSWatcher) is a tool to help DBA's to trouble shoot Database performance, Cluster reboot, node eviction, DB server reboot, DB instance Crash related issues and many more.

As we know, OS stats like top, mpstat, netstat plays an important role in Database trouble shooting but there is no way to keep historical date for these stats. Here, OS Watcher is the only rescue for Database Administrator. Suppose Yesterday, There was some performance issue on Database Node but you were not aware about that and when you know that the issue was resolved itself.

Now, DBA can get Database related stats from AWR reports but not OS related stats for last day, To overcome this challenge Oracle introduce OS Watcher utility, which collects OS stats data at a frequency of five minutes and keep it for seven days (default settings). So Now, DBA need not to worry about historical OS stats.

To Trouble shoot Database performance related issues AWR, ADDM and OS Watcher logs are the first place to start for a Remote DBA. Where as for Cluster reboot, node eviction, DB server reboot Alter log files, OS Watcher and System messages (/var/log/messages) plays an important role.

How to Install OS Watcher Utility

1. Download tar file from Oracle Support Article "OSWatcher Black Box.

2. Copy the file oswbb601.tar to the directory where oswbb is to be installed.

3. Extract tar file with “oracle” user
# tar xvf oswbb601.tar

4. Change to oswbb directory created.

5. Start OS Watcher utility using below command.

Example 1:

./startOSW.sh 60 10

This would start the tool and collect data at 60 second intervals and log the last 10 hours of data to archive files.

Example 2:

./startOSW.sh

This would use the default values of 30, 48 and collect data at 30 second intervals and log the last 48 hours of data to archive files.

Example 3:

./startOSW.sh 20 24 gzip

This would start the tool and collect data at 20 second intervals and log the last 24 hours of data to archive files. Each file would be compressed by running the gzip utility after creation.

STOPPING OSW:

To stop the OSW utility execute the stopOSW.sh command. This terminates all the processes associated with the tool.

Example:

./stopOSW.sh

The default location of OS Watcher files is /opt/oracle.oswatcher/osw/archive. To collect OS Watcher files for a particular day use below command.

# cd /opt/oracle.oswatcher/osw/archive

# find . -name '*25.05.16*' -print -exec zip /tmp/osw_`hostname`.zip {} \;
{where 16- year 05- Month 25-day}

Below are the list of sub folders created under archive folder

-bash-4.1$ ls

osw_ib_diagnostics   oswiostat            oswnetstat           oswps                oswvmstat
osw_rds_diagnostics  oswmpstat            oswprvtnet           oswtop

Archived log generation report per hour and day basis

V$ARCHIVED_LOG displays information about the archived logs generated by your database and haven't yet aged out from your control file.

We are going to use this view to generate a report, displaying some useful information about it.
The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN.

SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

The below script - which will give archive log count - hour-by-hour, daily - for a month: view on ONE Page. This script runs on ORACLE Database Version 8i/9i/10g/11g.

This is a 'SELECT only' QUERY - to be run run as SYSTEM account.

This SQL QUERY finishes in 'few' seconds only (so, no adverse effect on database performance).
For your safety, advisable to run on your Development or Test instance (whichever is running in ARCHIVE MODE) first, before running on Production instance.

This script will generate output file named archive_log_count.html in the current directory (i.e. working directory).
If your Oracle database is running on non-Windows server, you need to transfer (ftp or WinSCP or similar) out-put file archive_log_count.html from non-Windows host to Windows PC and open the file in the web browser.

Best way to run this script is COPY & PASTE blue color text below at your SQL> prompt logged in as 'SYSTEM' account.

set echo off

set feedback off

set termout off

set pause off

set head on

set lines 100

set pages 500

clear columns

clear computes

set markup html on spool on preformat off entmap on -

head ' -

  <title>Redo Log Switch Report</title> -

  <style type="text/css"> -

    body              {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -

    p                 {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -

    table,tr,td       {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -

    th                {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} -

    h1                {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -

    h2                {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -

    a                 {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.link            {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLink          {font:9pt Arial,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLinkBlue      {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLinkRed       {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLinkDarkRed   {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLinkGreen     {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

    a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -

  </style>' -

body   'BGCOLOR="#C0C0C0"' -

table  'BORDER="1"'

set heading on

prompt Redo Log Switches for a month

CLEAR COLUMNS BREAKS COMPUTES

COLUMN DAY   FORMAT a75              HEADING 'MM/dd; Time'

COLUMN H00   FORMAT 999,999B         HEADING '00'

COLUMN H01   FORMAT 999,999B         HEADING '01'

COLUMN H02   FORMAT 999,999B         HEADING '02'

COLUMN H03   FORMAT 999,999B         HEADING '03'

COLUMN H04   FORMAT 999,999B         HEADING '04'

COLUMN H05   FORMAT 999,999B         HEADING '05'

COLUMN H06   FORMAT 999,999B         HEADING '06'

COLUMN H07   FORMAT 999,999B         HEADING '07'

COLUMN H08   FORMAT 999,999B         HEADING '08'

COLUMN H09   FORMAT 999,999B         HEADING '09'

COLUMN H10   FORMAT 999,999B         HEADING '10'

COLUMN H11   FORMAT 999,999B         HEADING '11'

COLUMN H12   FORMAT 999,999B         HEADING '12'

COLUMN H13   FORMAT 999,999B         HEADING '13'

COLUMN H14   FORMAT 999,999B         HEADING '14'

COLUMN H15   FORMAT 999,999B         HEADING '15'

COLUMN H16   FORMAT 999,999B         HEADING '16'

COLUMN H17   FORMAT 999,999B         HEADING '17'

COLUMN H18   FORMAT 999,999B         HEADING '18'

COLUMN H19   FORMAT 999,999B         HEADING '19'

COLUMN H20   FORMAT 999,999B         HEADING '20'

COLUMN H21   FORMAT 999,999B         HEADING '21'

COLUMN H22   FORMAT 999,999B         HEADING '22'

COLUMN H23   FORMAT 999,999B         HEADING '23'

COLUMN TOTAL FORMAT 999,999,999      HEADING 'Total'

break on report

compute  avg LABEL 'Average:' sum LABEL 'Total:' of total  ON report

SELECT

    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || TO_CHAR(first_time, ' Dy') DAY

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23

  , COUNT(*)                                                                      TOTAL

FROM

  v$log_history  a

  where trunc(first_time) >= trunc(sysdate)-30

GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || TO_CHAR(first_time, ' Dy')

order BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || TO_CHAR(first_time, ' Dy')

;

spool archive_log_count.html

/

spool off

exit

Output report:

Oracle DBA Interview Questions and Answers - Architecture

1. What is difference between oracle SID and Oracle service name?

Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.

2. What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?

Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.

3. What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

4. What is the difference between data block/extent/segment?

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

5. What is the difference between PGA and UGA?

When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

6. What is SGA? Define structure of shared pool component of SGA?

The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.

Shared pool portion contain three major area:
Library cache (parse SQL statement, cursor information and execution plan),
data dictionary cache (contain cache, user account information, privilege user information, segments and extent information,
data buffer cache for parallel execution message and control structure.


7. What is the difference between SMON and PMON processes?

SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.

8. What is a system change number (SCN)?

SCN is a value that is incremented whenever a dirty read occurs.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.

9. 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 two main purposes: To establish a data consistency and enable faster database Recovery.

The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600;  # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.

10. What happens when we fire SQL statement in Oracle?

First it will check the syntax and semantics in library cache, after that it will create execution plan.
If already data is in buffer cache it will directly return to the client.
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

11. What is the use of large pool, which case you need to set the large pool?

You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.

12. What does database do during the mounting process?

While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.

13. What are logfile states?

“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.

14. What is log switch?

The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
How to check Oracle database version?
SQL> Select * from v$version;

15. Explain Oracle Architecture?

Oracle Instance:
a means to access an Oracle database,always opens one and only one database and consists of memory structures and background process.
Oracle server:
a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.
Oracle database:
a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Instance memory Structures:

System Global Area (SGA):
Allocated at instance startup, and is a fundamental component of an Oracle Instance.


SGA Memory structures:
Includes Shared Pool,  Database Buffer Cache, Redo Log Buffer among others.
Shared Pool :
Consists of two key performance-related memory structures Library Cache and  Data Dictionary Cache.
Library Cache:
Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
Data Dictionary Cache :
Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
Database Buffer Cache:
Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.
Redo Log Buffer :
Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.
User process:
Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.
Server process:
Connects to the Oracle Instance and is Started when a user establishes a session.
fulfills calls generated and returns results.
Each server process has its own nonshared PGA when the process is started.
Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.
In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.
Program Global Area (PGA):
Memory area used by a single Oracle server process.
Allocated when the server process is started, deallocated when the process is terminated and used by only one process.
Used to process SQL statements and to hold logon and other session information.

Background processes:
Started when an Oracle Instance is started.
Background Processes Maintains and enforces relationships between physical and memory structures
There are two types of database processes:
      1.      Mandatory background processes
      2.      Optional background processes
Mandatory background processes:
– DBWn, PMON, CKPT,  LGWR,  SMON
Optional background processes:
– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach threshold
• There are no free buffers
• Timeout occurs
• RAC ping request is made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP
Log Writer (LGWR) writes:
• At commit
• When 1/3rd full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes


System Monitor (SMON) Responsibilities:
• Instance recovery
– Rolls forward changes in redo logs
– Opens database for user access
– Rolls back uncommitted transactions
• Coalesces free space
• Deallocates temporary segments.
Process Monitor (PMON) Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarting dead dispatchers
Checkpoint (CKPT) Responsible for:
• Signaling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database

16. Why do you run orainstRoot and ROOT.SH once you finalize the Installation?

orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.
Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.
orainstRoot.sh
[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to dba.
The execution of the script is complete
root.sh
[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
For Oracle installation on unix/linux, we will be prompted to run a script 'root.sh' from the oracle inventory directory.this script needs to run the first time only when any oracle product is installed on the server.


17. Oracle Database 11g New Feature for DBAs?

1) Automatic Diagnostic Repository [ADR]
2) Database Replay
3) Automatic Memory Tuning
4) Case sensitive password
5) Virtual columns and indexes
6) Interval Partition and System Partition
7) The Result Cache
8) ADDM RAC Enhancements
9) SQL Plan Management and SQL Plan Baselines
10) SQL Access Advisor & Partition Advisor
11) SQL Query Repair Advisor
12) SQL Performance Analyzer (SPA) New
13) DBMS_STATS Enhancements
14) The Result Cache
15) Total Recall (Flashback Data Archive)
Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

18. What is the Difference Between Local Inventory and Global Inventory? What is oraInventory ?

oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.
There are basically two kind of inventories,
One is Local Inventory (also called as Oracle Home Inventory) and other is  Global Inventory (also called as Central Inventory).

19. What is Global Inventory ?

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)
You will see entry like
HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

20. What is Local Inventory ?

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

21. What is Oracle Home Inventory?

Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:
$ORACLE_HOME/inventory
It contains the following files and folders:
·         Components File
·         Home Properties File
·         Other Folders

22. Can I have multiple Global Inventory on a machine ?

Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

23. What to do if my Global Inventory is corrupted ?

No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option
-attachHome
./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc
ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”
CLUSTER_NODES=”{}”

24. What is RESULT Cache?

11G Backgroung Processes?

The following process are added in 11g as new background processes.
1 dbrm DB resource manager
2 dia0 Diagnosability process
3 fbda Flashback data archiver process
4 vktm Virtual Timekeeper
5 w000 Space Management Co-ordination process
6 smc0 Space Manager process
NOTE : The above six are mandatory processes.
But 11g has 56 new processes added which can be queried using

25. If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted ?

Background processes are started automatically when the instance is started.
Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional, will be invoked if that particular feature is activated.
If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted.
Any issues related to backgroud processes should be monitored and analyzed from the trace files generated and the alert log.

26. Literal Vs. Bind Variables?

27. Select * from emp where dept=10; what level should i configure for better performance Where Clause .... Literal or Bind Variables? Expalin?

28. What is a Baseline?

29. Incarnation? Explain in detail? Where the incarnation information will be stored? 

30. Hard Parse Vs. Soft Parse?

31. What is semaphores, semaphores?

32. What is latch?

33. What is Enqueue?

34. What is SGA_TARGET and SGA_MAX_SIZE ?

What is Database link(DB link), Private and Public DB link

A database link enables a one-way connection to a remote database from a local database. The link is one-way only. The remote database users can’t use this link to connect to the local database—they must create a separate database link for that.

A database link allows us to gain access to a different database though a remote database user account; we don’t have to be a user in the remote database. Our privileges on that database will be identical to the privileges of the user account you use when creating the database link. Database links are useful when we want to query a table in a distributed database or even insert data from another database’s table into a local table. Database links allow users to access multiple databases as a single logical database.

We can create private and public database links. In the following sections, we’ll look at examples of how to create both types of database links.

Creating a Private Database Link

A private database link is owned by the user that creates the link. In the following statement, the SYSTEM user creates a private database link. The database link enables a connection to the remote database using the hr user’s username and password in that database.

SQL> CONNECT system/system_passwd@finance
Connected.
SQL>
SQL> CREATE DATABASE LINK MONITOR
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'monitor';
Database link created.
SQL>

Note: To create a database link, a user must have the CREATE PRIVATE DATABASE LINK privilege or the CREATE PUBLIC DATABASE LINK privilege in the local database.

After the link is created, the SYSTEM user can query the hr.employees table in the remote
database.

SQL> SELECT COUNT(*) FROM hr.employees@monitor;
COUNT(*)
----------
107

In the preceding statement, note that the database link’s name is MONITOR, which is the same as the remote database’s TNS name alias (Oracle Net Service alias), but it could be anything we want. The CONNECT TO . . . IDENTIFIED BY clause means that the user of this database link will use that username and password to enter the remote database. The USING 'monitor' clause simply specifies the TNS name alias for the linked remote database.
Because this is a private database link, only the SYSTEM user can use it. When the hr user tries to use this link to a remote database, this is what happens:

SQL> CONNECT hr/hr;
Connected.
SQL> SELECT count(*) FROM hr.employees@monitor;
select count(*) from hr.employees@monitor
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL>

Creating a Public Database Link

A public database link, unlike a private database link, enables any user or any PL/SQL program unit to access the remote database objects. The creation statement is very similar to that for a private database link. We just add the PUBLIC keyword to the CREATE DATABASE LINK statement:

SQL> connect system/system_passwd as sysdba;
Connected.

SQL> CREATE PUBLIC DATABASE LINK MONITOR
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'monitor';
Database link created.
SQL>

Once the public MONITOR link is created, any user can log into a remote database using that
link. In the following example, the user tester uses the public database link to query the remote database, MONITOR.

SQL> CONNECT tester/tester1;
Connected.
SQL> SELECT COUNT(*) FROM hr.employees@monitor;
COUNT(*)
----------
107
SQL>

Note: We can create a public database link if several users require access to a remote Oracle database from a local database. Otherwise, create a private database link, which will allow only the owner of the private database link to access database objects in the remote database.

Step by step --- How SQL query gets executed in Oracle database

All the SQL query goes through the below 4 phases while in execution:

1. Parsing

2. Binding

3. Execute

4. Fetch

Parsing:  During the parse phase, Oracle checks whether the statement is ok in terms of syntactically and symentically.

Syntax checking: It checks for all keywords the keywords are present in the statement "select . . . from", etc . .

Symentic checking: Oracle check the object names in the statement against the dictionary - Are all table names are spelled correctly also checks for the mentioned objects exist and are accessible, etc..etc...

Then it creates the execution plan for the statement.  Parsing does not return an error if the statement is not syntactically correct. Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution.  There are two types of parses, soft parse and hard parse.

Bind phase: Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables known to Oracle.

Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.

Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them available to the user.

Step by step ---How insert steatement get executed in Oracle database

1. A user requests a connection to the Oracle database through his login credenital.

2. This login credential is accepted by the listener on server side and then listener hand over the login credential to PMON background process.

3. PMON checks the autheticity of the user in the data dictionary cache of the Shared pool area. If the detail of user is found in the data dictionary then PMON creates a server process and allocate a memory area PGA to that user. And sends acknowlegement for successfull connection to user.

4. If PMON does not find the detail in the data dictionary cache, it checks in the datafiles and if details found a copy of that is placed in the data dictionary cache and user is acknowleged  for successfull connection.

5. If details are not found in the datafile also then PMON sends acknowledgement of unsuccessfull connection to user.


6.  Then the user executes a statement to insert a new row into a table.

7. PMON checks the user’s privileges to make sure the user has the necessary rights to perform the insertion. If the user’s privilege information isn’t already in the library cache, it will have to be read from disk into that cache.

8. If the user has the requisite privileges, server process checks whether a previously executed SQL statement that’s similar to the one the user just issued is already in the shared pool. If there is, Oracle executes this version of the SQL; otherwise Oracle parses and executes the user’s SQL statement. Oracle then creates a private SQL area in the user session’s PGA.

9. Oracle first checks whether the necessary data is already in the data buffer cache. If not, the server process reads the necessary table data from the datafiles on disk.

10. Oracle immediately applies row-level locks, where needed, to prevent other processes from trying to change the same data simultaneously.

11. The server process writes the change vectors(redo) to the redo log buffer.

12. The server process modifies the table data (inserts the new row) in the data buffer cache.

13. The user commits the transaction, making the insertion permanent. Oracle releases the row locks after the commit is issued.

14. The log writer process immediately writes out the changed data in the redo log buffers to the online redo log file.

15. The server process sends a message to the client process to indicate the successful completion of the INSERT operation. The message would be “COMMIT COMPLETE” in this case. (If it couldn’t complete the request successfully, it sends a message indicating the failure of the operation.)

16. Changes made to the table by the insertion may not be written to disk right away. The database writer process writes the changes in batches, so it may be some time before the inserted information is actually written permanently to the database files on disk.