MRP (managed recovery process) stuck after creating standby database

  

Recently I created Physical standby database using ZDM for one of big oracle database migration.

After creating standby database, everything looks good and when I checked the status of MRP process, it shows "APPLYING_LOGS". 

SQL> select INST_ID,PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK# from GV$MANAGED_STANDBY;

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
---------- --------- ------------ ---------- ---------- ----------
         1 DGRD      ALLOCATED             0          0          0
         1 ARCH      CLOSING               1     533782     116736
         1 DGRD      ALLOCATED             0          0          0
         1 ARCH      CLOSING               1     533712    3309568
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0
         1 ARCH      CONNECTED             0          0          0

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CONNECTED             0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       RECEIVING             1     533783     146785
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0
         1 RFS       IDLE                  0          0          0

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
---------- --------- ------------ ---------- ---------- ----------
         1 RFS       IDLE                  0          0          0
         1 MRP0      APPLYING_LOG          1     533707    1789286

After sometime I ran the same command again and I see the MRP process running but Sequence# not changed and even Block# not changed.
After investigation I saw archive received properly from primary but it's not applying on standby.
When I ran the below query, I saw gap was increasing even MRP process is running.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;  2    3    4    5    6    7
    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                 533607                533590         17

Then I get to know that MRP is stuck somehow and not moving.

So I tried below steps but nothing worked.
1. Restart MRP.
2. Restart standby database.
3. Try parallel MRP.
After lots of search I found a solution in a big Oracle document, but I want to specify this particular scenario so it help others..

Solution

SQL> shutdown abort;
SQL>startup mount;
- Then do a clean shutdown again to release any stuck process
SQL> shutdown immediate;
Check if any standby process still running , if yes, kill it
kill -9 ospid
SQL> startup mount;
Now start the MRP process..
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Now if we check, archives are applying and no MRP stuck issue.

How to setup scheduled backup of compute instance in OCI

 In this blog post I will show you how to take backup of compute instance and schedule it.

There are 2 backup types available:

Full = includes all changes since the volume was created.
Incremental = includes only the changes since last backup

So backup of compute instance basically means backup of  "Boot volume + block volume."

This is point in time backup of data to Object storage.

Steps:

1. Create Backup policy:

First we need to create backup policy to attach with volume group, so it automatically take backups as per policy.

Go to OCI --> Click on Storage --> Block Storage --> Backup Policy

You will see we already have 3 Oracle defined policies. but

Note: Oracle defined backup policies are not supported for scheduled volume group backups.

So let's create user defined backup policy:

Click on create backup policy and provide any name, I am not using any cross region copy target so keep None and click on Create Backup policy.

Now we need to add schedule for backups, Click Add schedule

 

Add Schedule for full backup (I am taking weekly full backup), click add schedule.

Now click again on add schedule and add for incremental backup

You can see both schedule

2. Create a volume group:

A volume group can include both types of volumes, boot volumes, which are the system disks for your compute instances, and block volumes for your data storage.

Go to OCI --> Click on Storage --> Block Storage --> Volume Groups 

Click on Create volume group, give any name and select availability domain and click next

Next , select the volumes used in compute instance (boot volume + if you are using any block volume)

I have one block volume which is attached to my VM, so I select boot volume + one block volume. If you using more block volumes, add all here.

click next, I am not enabling cross region replication

Next select the backup policy we created earlier

Next review the summary page for all info and click create

It takes few seconds and volume group will create.

3. After few days when you Click on Volume Group Backups you will see the volume group backups as per the job policy.

something like that

This way we can take or scheduled compute instance backups.

In the next Blog I will show you how to restore from volume group backup and create a new instance.

stay tuned!