Oracle Dataguard Health Check Tips and Tricks for Checking the MRP Process Status

YouTube video

In today’s article, we will be discussing how to perform a health check for the Oracle Dataguard environment. Oracle Dataguard is a disaster recovery solution that ensures there is no impact on user applications when the primary database goes down. As an Oracle database administrator, it is crucial to understand what needs to be checked in the Oracle Dataguard environment.

Before we dive into the practical demonstration, I would like to inform you that I will be starting a new Oracle Database Administration (DBA) batch next week. If you’re interested, you can reach out to me through my WhatsApp number or email, which you can find in the description section below. Additionally, if you’re not interested in joining the batch, you can still watch my free videos on my YouTube channel, where I regularly share valuable information related to Oracle DBA and other technologies.

Now let’s proceed to the practical demonstration of how to perform a database health check for the Oracle Dataguard environment.

Step 1: Checking Database Status

To begin the health check, we need to ensure that both the primary and standby databases are up and running. On the primary database server, connect to the SQL*Plus utility and execute the following script to check the database status:

sql
SELECT NAME, OPEN_MODE FROM V$DATABASE;

The result will display the name of the database and its current open mode. The primary database should be in an “OPEN” mode, indicating it is up and running.

Next, connect to the standby database server and execute the same script to check the database status. The standby database should be in the “MOUNT” mode, indicating it is in sync with the primary database.

Step 2: Sync Status and Checking Archives

The next crucial aspect of the Oracle Dataguard environment is ensuring that the primary and standby databases are in sync. To check the sync status, execute the following command on both the primary and standby databases:

sql
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

Compare the results from both databases. If the maximum sequence number is the same, it means the databases are in sync.

Additionally, you need to check whether the Managed Recovery Process (MRP) is running on the standby database. The MRP process applies the archived logs from the primary database to the standby database. Execute the following command on the standby database to check the MRP status:

sql
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

If the MRP process status is “UP” and it is waiting for the next log sequence, then you can be assured that the archives are being applied to the standby database.

Step 3: Starting and Stopping MRP Process

In some cases, you may need to start or stop the MRP process manually. To stop the MRP process, execute the following SQL command:

sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

After stopping the MRP process, you will no longer see it listed when checking the MRP process status.

To start the MRP process, use the following command:

sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

When executing this command, you may notice a temporary disconnection from the session. However, this is normal and the MRP process will start running in the background.

Step 4: Monitoring Archive Shipments and Applied Archives

If you want to track the number of archives shipped from the primary database to the standby database and the number of applied archives on the standby database, you can execute the following commands:

To check the number of archives shipped from the primary database:

sql
SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

To check the number of applied archives on the standby database:

sql
SELECT APPLIED_SEQUENCE# FROM V$ARCHIVED_LOG;

Compare the results from both databases. If the sequence numbers match, it indicates that the archives are being shipped and applied correctly.

Step 5: Troubleshooting in Dataguard Environment

In case you encounter any issues or want to troubleshoot the Dataguard environment, you can utilize various log scripts. These scripts provide information about any errors or issues in the environment.

Execute the following script to check for any issues:

sql
SELECT * FROM V$DATAGUARD_STATUS WHERE STATE <> 'TRANSPORT-ON';

If no rows are selected, it means there are no issues with the Dataguard environment.

For more detailed information, you can execute the following scripts:

sql
SELECT * FROM V$ARCHIVE_GAP;
SELECT * FROM V$ARCHIVE_DEST_STATUS;

These scripts provide additional details about the archive gaps, timestamps, and any errors related to the archive destination.

By following these steps and using the provided scripts, you can successfully perform a health check of your Oracle Dataguard environment. For the detailed scripts mentioned in this article, you can find the link in the description section below.

If you have any queries or require further assistance, feel free to comment in the description section. I will continue creating more informative videos in the future, so if you have any specific topics you would like me to cover, please let me know. Additionally, if you’re interested in joining my upcoming Oracle DBA batch, you can find the necessary contact details in the description section.

Don’t forget to like, subscribe, and share this channel for more valuable content related to Oracle DBA and other technologies. You can also follow me on Instagram, where I regularly share short videos discussing Oracle database interview questions and various technologies.

Thank you for watching, and stay tuned for more informative videos!