What does PMON do?

PMON (Process Monitor)

• Process Monitor is an Oracle background process created when you start a database instance. 
• The PMON process will free up resources if a user process fails (release database locks).
• Is in charge to perform process recovery when a user process fails (also when killed).
• Rolls back any open transactions for the process.
• PMON normally wakes up every 3 seconds to perform its housekeeping activities. 
• PMON must always be running for an instance. If not, the instance will terminate.

Check process

The following Unix/Linux command is used to check if the PMON process is running:

$ ps -ef | grep pmon

Speed-up PMON

To speed-up housekeeping, one may also wake-up PMON (process 2 below) manually:

SQL> oradebug setmypid

SQL> oradebug wakeup 2

Instance Recovery Performance

Instance Recovery

Instance and crash recovery are the automatic application of redo log records to Oracle data blocks after a crash or system failure. During normal operation, if an instance is shutdown cleanly as when using a SHUTDOWN IMMEDIATE statement, rather than terminated abnormally, then the in-memory changes that have not already been written to the datafiles on disk are written to disk as part of the checkpoint performed during shutdown.

• Cache Recovery (Rolling Forward)

During the cache recovery step, Oracle applies all committed and uncommitted changes in the redo log files to the affected data blocks. The work required for cache recovery processing is proportional to the rate of change to the database (update transactions each second) and the time between checkpoints.

• Transaction Recovery (Rolling Back)

To make the database consistent, the changes that were not committed at the time of the crash must be undone (in other words, rolled back). During the transaction recovery step, Oracle applies the rollback segments to undo the uncommitted changes. The work required to do transaction recovery is proportional to the number and size of uncommitted transactions when the system fault occurred.

Control the duration of startup after instance failure

Oracle recommends using the FAST_START_MTTR_TARGET initialization parameter to control the duration of startup after instance failure. Fast-start checkpointing is only available with Enterprise Edition. The FAST_START_IO_TARGET initialization parameter has been deprecated in favor of the FAST_START_MTTR_TARGET parameter. 

FAST_START_MTTR_TARGET

1. You should disable or remove the FAST_START_IO_TARGET.
1. LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT initialization parameters when using FAST_START_MTTR_TARGET. 
2. Setting these parameters to active values interferes with FAST_START_MTTR_TARGET, resulting in a different than expected value in the TARGET_MTTR column of the V$INSTANCE_RECOVERY view.
2. The maximum value for FAST_START_MTTR_TARGET is 3600, or one hour.
1. If you set the value to more than 3600, then Oracle rounds it to 3600. There is no minimum value for FAST_START_MTTR_TARGET. However, this does not mean that you can target the recovery time as low as you want. 
2. The time to do a crash recovery is limited by the low limit of the target number of dirty buffers, which is 1000, as well as factors such as how long initialization and file open take.
3. If you set the value of FAST_START_MTTR_TARGET too low.
1. Then the effective mean time to recover (MTTR) target will be the best MTTR target the system can achieve. 
2. If you set the value of FAST_START_MTTR_TARGET to such a high value that even in the worst-case recovery would not take that long, then the effective MTTR target will be the estimated MTTR in the worst-case scenario when the whole buffer cache is dirty. 
3. Use the TARGET_MTTR column in the V$INSTANCE_RECOVERY view to see the effective MTTR.

Oracle Alert Log Location

What is the alert log?

This log is there to alert you to major events in the database like:

• When the database was started and stopped.
• When the database was stopped. 
• When a log switch occurs. 
• You can see many other things. 

So the DBA should check the contents of the alert log on a regular basis. 

Alert log Location

The alert log is stored in the directory pointed to by the background dump destination and specified by the background_dump_dest initialization parameter, You can display the location of the alert log directory with SQLPlus or OS commands:

SQL Plus

1. SQL> show parameter background;
2. SQL> select value from v$parameter where name='background_dump_dest';

OS

• UNIX
• find / -name alert*.log 2>/dev/null
• Windows
• dir /s alert*.log

Oracle Database Private Link

What is a DBLink

A database link is a one-way connection between two physical database servers that allows a client to access them as one logical database. 
For example a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. 
If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.

Privileges Necessary for Creating Database Links

To see which privileges you currently have available:

SELECT DISTINCT PRIVILEGE AS "Database Link Privileges" FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE
IN ( 'CREATE SESSION','CREATE DATABASE LINK','CREATE PUBLIC DATABASE LINK')

Private Database Links Example: 

Create:

CREATE DATABASE LINK linkName
  CONNECT TO destinationDatabaseUser IDENTIFIED BY pass USING
  '(DESCRIPTION=    
   (ADDRESS=     
   (PROTOCOL=TCP)     
   (HOST=172.20.000.001)     
   (PORT=1521))    
   (CONNECT_DATA=     
   (SERVICE_NAME=Sevi_Ce)))'  ;

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. 

Drop:

DROP DATABASE LINK linkName;

You can drop a database link just as you can drop a table or view. If the link is private, then it must be in your schema. If the link is public, then you must have the DROP PUBLIC DATABASE LINK system privilege.

Close:

ALTER SESSION CLOSE DATABASE LINK linkName;

If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link.

Insert:

INSERT INTO RemoteDBTable@linkName SELECT * FROM LocalDBTable;

After you have created a database link

You can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Oracle bitmap index vs b-tree index

Difference

The b-tree indexes are used  usuall when we have too many distinct columns and for high cardinaties, and bitmap indexex are used for low cardinaties,  usually when we have repeated columns.

B-Trees 

B-Trees are the typical index type used when you do CREATE INDEX ... in a database:

• They are very fast when you are selecting just a small very subset of the index data (5%-10% max typically).
• They work better when you have a lot of distinct indexed values.
• Combining several B-Tree indexes can be done, but simpler approaches are often more efficient.
• They are not useful when there are few distinct values for the indexed data, or when you want to get a large (>10% typically) subset of the data.
• Each B-Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.
• Internal structure: A b-tree index has index nodes (based on data block size), it a tree.

This characteristics make B-Tree indexes very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID, and you want good concurrent performance.

Bitmap

Bitmap indexes are a more specialized index variant:

• They encode indexed values as bitmaps and so are very space efficient.
• They tend to work better when there are few distinct indexed values
• DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.
• They are very inefficient when inserting/updating values.
• Internal structure: A bitmap index looks like this, a two-dimensional array with zero and one (bit) values.

Bitmap indexes are mostly used in data warehouse applications, where the database is read only except for the ETL processes, and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values.

As a very short summary: 

Use B-Tree indexes (the "default" index in most databases) unless you are a data warehouse developer and know you will benefit for a bitmap index.

Oracle Bitmap Index

Every Index Prupose

Is to provide pointers to a table rows that have a value.

Bitmap Index

In a bitmap index, instead of a list of rowids, Oracle creates a bitmap for each key value of the index.

Each bit of the map corresponds to a possible rowid. If the bit is set to 1, it means that the rowid contains that key value. 

An internal Oracle function converts the position of the bit into the corresponding rowid.

If the number of different values of the index is small, then the index bitmap will be very efficient in the use of physical space.

Example 

Suppose we have the following customers table:

CLIENTE        APELLIDO       REGION
101            PEREZ          NORTE
102            GARCIA         CENTRO
103            LOPEZ          SUR
104            SAN MARTIN     SUR
105            BROWN          CENTRO
106            CANEPA         CENTRO

The region column has low cardinality, since the possible values are very few (NORTH, CENTER, SOUTH). There are only three possible values for the region so a bitmap index would be appropriate for this column. However, a bitmap index for the CLIENT or LAST column is not recommended, given its high cardinality. 

The following would be the representation of the bitmap index for the REGION column. The index has three bitmaps, one for each region.

NORTE    CENTRO    SUR         
1        0         0
0        1         0
0        0         1
0        0         1
0        1         0
0        1         0

Each entry or bit in the bitmap index corresponds to a single row in the customer table. The value of the bit will depend on the corresponding value of the row in the table. For example, for the NORTH region the bitmap has a 1 in the first position. This is because the first row of the customer table has the NORTH value in the REGION column. The bitmap then has all zeros, indicating that the rest of the rows in the table have no clients in the NORTH region.

An SQL statement on this table and with the index bitmap, would be solved in the following way.

select count(*) from CLIENTES

    where REGION in ('NORTE','SUR');

A bitmap index can resolve this statement with great efficiency by counting the amount of ones existing in the resulting bitmap as shown in the following figure:

NORTE   CENTRO   SUR   (NORTE O SUR)
1       0        0      1
0       1        0      0
0       0        1      1
0       0        1      1
0       1        0      0
0       1        0      0

The "NORTH OR SOUTH" column is the resulting bitmap used to access the table.

Additionally, unlike B-tree indices, bitmap indices may include rows with NULL values ​​within the index structure. As for partitioned tables, bitmap indices can be used only if they are local to the partition. Global bitmap indices are not supported for partitioned tables.