We value your privacy

We use cookies to enhance your browsing experience, serve personalised content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies.

Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

  Master in PostgreSQL DBA |Join Free Demo Session on 28 April 2025 at 7.30 PM IST| Register for Free Demo

Know More

How to Monitor Standby Database

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 28 Sep, 2023
  • 0 Comments
  • 4 Mins Read

How to Monitor Standby Database

FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY:
1
2
3
4
5
select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
CHECK THE STATUS OF DIFFERENT PROCESS:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$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
MRP0 WAIT_FOR_LOG 1 53056 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 53056 10935 2
RFS IDLE 0 0 0 0
 
9 rows selected.
LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED:
1
2
3
4
5
6
7
8
SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM
2 (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change#
3 FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd; 4
 
Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
1 49482 49482
CHECK THE MESSAGES/ERRORS IN STNADBY DATABASE:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
set pagesize 2000
set lines 2000
col MESSAGE for a90
select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate - 1/6;
MESSAGE TIMESTAMP
------------------------------------------------------------------------------------------ ---------
RFS[48]: No standby redo logfiles created 05-AUG-15
Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49481.log 05-AUG-15
Media Recovery Waiting for thread 1 sequence 49482 (in transit) 05-AUG-15
RFS[48]: No standby redo logfiles created 05-AUG-15
Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49482.log 05-AUG-15
Media Recovery Waiting for thread 1 sequence 49483 (in transit) 05-AUG-15
 
6 rows selected.
CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/
Find LAG in dataguard with ORACLE RAC :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
set echo on feed on term on
set linesize 120
col PRIMARY_TIME format a20
col STANDBY_COMPLETION_TIME format a23
spool dg_lag_minutes_&1..sql.log.txt
SELECT
prim.thread# thread,
prim.seq primary_seq,
to_char(prim.tm, ‘DD-MON-YYYY HH24:MI:SS’) primary_time,
tgt.thread# standby_thread,
tgt.seq standby_seq,
to_char(tgt.tm, ‘DD-MON-YYYY HH24:MI:SS’) standby_completion_time,
prim.seq – tgt.seq seq_gap,
( prim.tm – tgt.tm ) * 24 * 60 lag_minutes
FROM
(
SELECT
thread#,
MAX(sequence#) seq,
MAX(completion_time) tm
FROM
v$archived_log
GROUP BY
thread#
) prim,
(
SELECT
thread#,
MAX(sequence#) seq,
MAX(completion_time) tm
FROM
v$archived_log
WHERE
dest_id IN (
SELECT
dest_id
FROM
v$archive_dest
WHERE
target = ‘STANDBY’
)
AND applied = ‘YES’
GROUP BY
thread#
) tgt
WHERE
prim.thread# = tgt.thread#;
spool off

Hope it helps!

Rolling Forward Standby Database When Archives Missing In Primary In 12c
Shell Script To Delete Old Archives Using RMAN