You may hit performance issue on your dataguard setup. You may not hit the transfer gap but You may have an apply gap on your standby site. It can because some critical issue if your primary side goes down.

There are some trick which You can increase your performance on the Standby database on your dataguard setup

I assume that your standby has apply gap more than 2 hours.

1. On standby site check current status by using following queries:

SQL> select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from gv$archived_log
where sequence# = (select max(sequence#) from gv$archived_log where applied='YES')
union
select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from gv$archived_log
where sequence# = (select max(sequence#) from gv$archived_log);

SQL> select
NAME Name,
VALUE Value,
UNIT Unit
from v$dataguard_stats
union
select null,null,' ' from dual
union
select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
from v$dataguard_stats;
SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;

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;

SQL> select status,sequence# from v$managed_standby WHERE PROCESS='MRP0'; 
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

2. As I said, We have 2 hours apply gap. So what is the cause? How We identified a problem? You need to enable trace and You need to define your problem. For this please check below syntax, please click here

3. In my case, I faced with below syntax in my trace file:

*** DATE_HERE & TIME_HERE
Recovery is unrecoverable because 6 other slaves require space.

*** DATE_HERE & TIME_HERE
Recovery is unrecoverable because it fails to get a free buffer.

4. So here are my steps to increase apply performance. I checked and increased PARALLEL_EXECUTION_MESSAGE_SIZE parameter

SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 16384
SQL> alter system set parallel_execution_message_size=32680 scope=spfile sid='*'; << Need to reboot db

5. Increase SGA_MAX and SGA_MAX_VALUE parameter(Need to reboot db), then Start to apply

SQL> alter database recover managed standby database using current logfile disconnect from session parallel 32;

If you have still hitting performance issue, You can also check below steps which is covered by MOS Data Guard Managed Recovery Apply Process Running Slowly (Doc ID 1448626.1)

Increase the buffer cache on the standby.

In addition, check and incorporate the following recommendations to your standby environment for improved recovery performance:

  • Increase the number of db_writer_processes to 4.
  • Make sure the primary online redo logs are adequately sized. In general, the larger the log size the better recovery performance is due to the decreased number of checkpoints.
  • If using block change tracking file on the standby set _bct_public_dba_buffer_size=536870912
  • Set db_block_checking=OFF on the standby
  • check if db_block_checksum set typical/off on the standby

Reference:
Data Guard Managed Recovery Apply Process Running Slowly (Doc ID 1448626.1)

Advertisements