One of our standby database running 10 min behind primary db,
as a dba we need to look at multiple areas to find root cause, we can see one of the issue for standby redo gap and solution today
Critical alert from OEM:
EM Event: Critical:prddbdg01_stdby - The standby database is approximately 736 seconds behind the primary database
Quickly checked MRP process status and alert log:
SYS@prddbdg01>select process, status, thread#, sequence#, block#, blocks from gv$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 76769 82881 1
RFS IDLE 0 0 0 0
MRP0 WAIT_FOR_LOG 1 76769 0 0
9 rows selected.
--> MRP waiting for log
Alert log:
RFS[7]: No standby redo logfiles available for thread 1
RFS[7]: Opened log for thread 1 sequence 76760 dbid -647642248 branch 1014078423
Tue Jun 23 8:21:28 2020
Media Recovery Log /san/arch/prddbdg01/1_76759_1014078423.arc
Media Recovery Waiting for thread 1 sequence 76760 (in transit)
Tue Jun 23 08:31:28 2020
Archived Log entry 31993 added for thread 1 sequence 76760 rlc 1014078423 ID 0xd96b2953 dest 3:
RFS[7]: No standby redo logfiles available for thread 1
RFS[7]: Opened log for thread 1 sequence 76761 dbid -647642248 branch 1014078423
Tue Jun 23 08:31:28 2020
Media Recovery Log /san/arch/prddbdg01/1_76760_1014078423.arc
Media Recovery Waiting for thread 1 sequence 76761 (in transit)
Tue Jun 23 08:38:56 2020
Archived Log entry 31994 added for thread 1 sequence 76761 rlc 1014078423 ID 0xd96b2953 dest 3:
RFS[7]: No standby redo logfiles available for thread 1
RFS[7]: Opened log for thread 1 sequence 76762 dbid -647642248 branch 1014078423
--> alert log clearly says that standby logs are not available for RFS to write redo logs directly ,so mrp waiting for archive to generate
-->Lets check redo logs on primary and standby logs on standby
Redologs in primary:
SYS@prddbdg01>SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 76769 4294967296 NO CURRENT
1 2 76762 4294967296 YES INACTIVE
1 3 76763 4294967296 YES INACTIVE
1 4 76764 4294967296 YES INACTIVE
1 5 76765 4294967296 YES INACTIVE
1 6 76766 4294967296 YES INACTIVE
1 7 76767 4294967296 YES INACTIVE
1 8 76768 4294967296 YES INACTIVE
8 rows selected.
Standby logs in DR Side:
SYS@prddbdg01>SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------
0 9 0 4294967296 YES UNASSIGNED
0 10 0 4294967296 YES UNASSIGNED
0 11 0 4294967296 YES UNASSIGNED
0 12 0 4294967296 YES UNASSIGNED
0 13 0 4294967296 YES UNASSIGNED
0 14 0 4294967296 YES UNASSIGNED
0 15 0 4294967296 YES UNASSIGNED
0 16 0 4294967296 YES UNASSIGNED
0 17 0 4294967296 YES UNASSIGNED
1 19 0 4294967296 YES UNASSIGNED
10 rows selected.
--> only one standby log created for thread 1,Better practice is to create one extra standby logs in DR when compare to primary redos,
Lets Create standby redologs:
Stop MRP Process:
SYS@prddbdg01> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Standby logs creation:
alter database add standby logfile THREAD 1 group 20 '/san/redo2/prddbdg01/srl_20_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 21 '/san/redo2/prddbdg01/srl_21_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 22 '/san/redo2/prddbdg01/srl_22_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 23 '/san/redo2/prddbdg01/srl_23_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 24 '/san/redo2/prddbdg01/srl_24_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 25 '/san/redo2/prddbdg01/srl_25_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 26 '/san/redo2/prddbdg01/srl_26_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 27 '/san/redo2/prddbdg01/srl_27_1.rdo' SIZE 4096M;
alter database add standby logfile THREAD 1 group 28 '/san/redo2/prddbdg01/srl_28_1.rdo' SIZE 4096M;
Verify standby logs:
SYS@prddbdg01>SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------
0 9 0 4294967296 YES UNASSIGNED
0 10 0 4294967296 YES UNASSIGNED
0 11 0 4294967296 YES UNASSIGNED
0 12 0 4294967296 YES UNASSIGNED
0 13 0 4294967296 YES UNASSIGNED
0 14 0 4294967296 YES UNASSIGNED
0 15 0 4294967296 YES UNASSIGNED
0 16 0 4294967296 YES UNASSIGNED
0 17 0 4294967296 YES UNASSIGNED
1 19 0 4294967296 YES UNASSIGNED
1 20 76770 4294967296 YES ACTIVE
1 21 0 4294967296 YES UNASSIGNED
1 22 0 4294967296 YES UNASSIGNED
1 23 0 4294967296 YES UNASSIGNED
1 24 0 4294967296 YES UNASSIGNED
1 25 0 4294967296 YES UNASSIGNED
1 26 0 4294967296 YES UNASSIGNED
1 27 0 4294967296 YES UNASSIGNED
1 28 0 4294967296 YES UNASSIGNED
19 rows selected.
--> we can delete unused thread 0 standby logs
Start mrp process:
SYS@prddbdg01>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
Apply lag from OEM Dataguard Performance:
No comments:
Post a Comment