MRP on Oracle Dataguard !

1) How we can understand MRP is running or not ?

ps -aef | grep mpr

After running the below command ,
'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;'

we can check the status using below command :
select thread#,sequence#,process,status from v$managed_standby;

It shows the extra processes called 'MRP' .


2) Installing Standby Database Data Guard Managed Recovery

Creating archive destination LOG_ARCHIVE_DEST_2: 'stdby1'
ARC1: Complete FAL archive (thread 1 sequence 224 destination stdby1)

At this point, there will be archives logs on the standby site, but they will not be applied to the standby database until the primary database is put in managed recovery mode. This can be achieved by issuing the following statement:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

There are few other options for the RECOVER MANAGED Oracle instance statement, but at this information will be limited to the options that are relevant for putting the database in the managed recovery mode its creation.

The managed recovery can be started as a foreground or background process. The“disconnect from session” option allows the background process to do the managed recovery. It will start a MRP (managed recovery process) on the standby site, which is responsible for applying the archived redo logs onto the standby database. If this keyword is omitted, it will be necessary to keep a session open for the recovery.

This does not start MRP (managed recovery process) on the standby site. In almost all scenarios, the DISCONNECT FROM SESSION option will be used to have a background process take care of recovery. This process was introduced in Oracle9i in an effort to ease the administration of the standby database.

When the MRP process starts to apply log files, it finds the log files fromstandby_archive_dest directory and created the log file name using thelog_archive_format parameter. If it detects a missing log file from this directory, it will send a request to the primary database to transfer the file again. The name of the primary database is found from the fal_server parameter, and it sends the fal_client parameter as the destination service where the files will be resent.

The following extract from the alert log file of the Oracle instance shows the Managed Recovery Process requested the archived log files to be sent to the resolve gap:

Completed: alter database recover managed Oracle instance di
Tue Sep 23 10:35:46 2003
Fetching gap sequence for thread 1, gap sequence 231-231
Trying FAL server: primary1
Media Recovery Log /oracle/appsdb/arch/stdby.1_231.dbf
Media Recovery Log /oracle/appsdb/arch/stdby.1_232.dbf

he general administration tasks include controlling various modes of recovery, starting up, and shutting down the standby database.

Controlling the Managed Recovery Process

To start the database in managed recovery mode; mount it first, and then start the managed recovery. This is a three-step process involving:

•  Starting up the database using:

STARTUP NOMOUNT;

•  Mounting the Oracle instance using:

ALTER DATABASE MOUNT STANDBY DATABASE;

•  Starting the recovery process using:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Controlling the Managed Recovery Process
These statements should be executed by a user having SYSDBA privileges.

The RECOVER MANAGED Oracle instance statement has several options for controlling the recovery process. Either a foreground or a background recovery process can be started. The above-mentioned statement will initiate a foreground recovery process, and a session open will need to remain open. If recovery is started in a foreground session, use the TIMEOUT clause to stop recovery after the time specified by TIMEOUT.

For example:

ALTER DATABASE RECOVER MANAGED Oracle instance TIMEOUT 5;

This clause will stop the recovery process, if a new archive log does not arrive within five minutes.

A better option is to run the recovery process in background. The DISCONNECT FROM SESSION clause of the RECOVER MANAGED Oracle instance starts recovery in a background session. A complete statement to start recovery in background is:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

If the database is DML intensive, a multiple parallel recovery process can be started on the standby site to spread the load during the log apply operation.  The parallel clause is a request to start five parallel processes.

Oracle may decide to choose to start a different number of parallel processes depending on the resources available on the host machine. The following statement will start five parallel recovery processes to apply archived redo logs on the standby site:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION PARALLEL 5;

To stop the managed recovery, the Cancel command can be issued on the standby database. The complete statement is:

ALTER DATABASE RECOVER MANAGED Oracle instance CANCEL;

This statement will stop the MRP. There are several other options for the RECOVER MANAGED Oracle instance statement, which can be found in the Oracle documentation.