Disclaimer

Saturday 2 November 2024

Physical Standby Data Guard useful SQL Queries

 
Below are some of the frequently used Physical Standby Data Guard related SQL queries



1: Basic information of database (Primary or Standby)  

set lines 200
set pages 200
col DATABASE_ROLE for a18
SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE    INSTANCE      OPEN_MODE     PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------- ------------- -------------------- -------------------- --------------------
PRIMARY          orcl          READ WRITE    MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED



2: Check for messages/errors

set lines 200
set pages 200

SELECT 
    ARCH.THREAD# AS "Thread", 
    ARCH.SEQUENCE# AS "Last Sequence Received", 
    APPL.SEQUENCE# AS "Last Sequence Applied", 
    (ARCH.SEQUENCE# - APPL.SEQUENCE#) AS "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;

Thread Last Sequence Received Last Sequence Applied Difference
------- ---------------------- --------------------- ----------
      1                    107                   107          0



3: To display current status information for specific physical standby database background processes.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      APPLYING_LOG          1        107     333979     409600


4: To display Show received archived logs on physical standby

Run this query on physical standby

SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;

REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
–––-    –––-    –––––   –––––    ––––––-         ––––––
RFS     ARCH     1      29       1630326           1631783
RFS     ARCH     1      30       1631783           1632626
RFS     LGWR     1      31       1632626           1669359
RFS     ARCH     1      33       1676050           1676124
RFS     ARCH     1      32       1669359           1676050
RFS     ARCH     1      35       1681145           1681617
RFS     ARCH     1      34       1676124           1681145
RFS     ARCH     1      37       1688494           1688503
RFS     ARCH     1      36       1681617           1688494
RFS     ARCH     1      38       1688503           1689533
RFS     LGWR     1      39       1689533           1697243


5: To check the log status : Primary and Standby

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

LOGS                 TIME
-------------------- ---------------------------
Last Log applied :   01-NOV-24:13:25:58
Last Log received :  01-NOV-24:13:25:58




6: To display various information about the redo data. 

This includes redo data generated by the primary database that is not yet available on the standby database and how much redo has not yet been applied to the standby database.

set lines 132
col value format a20
select name, value from V$DATAGUARD_STATS;

NAME                             VALUE
-------------------------------- --------------------
transport lag
apply lag
apply finish time
estimated startup time           24



7: To monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress:

SQL> select to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time,
item, round(sofar/1024,2) "MB/Sec"
from v$recovery_progress
where (item='Active Apply Rate' or item='Average Apply Rate');

START_TIME             ITEM                      MB/SEC
–––––-––-––-          –––––––––––––––-           ––––----
27-MAR-14 15:49:44    Active  Apply Rate         8.5
27-MAR-14 15:49:44    Average Apply Rate         6.30



8: To check the log status : Primary and Standby

SQL> select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';

TO_CHAR(MAX(FIRST_T
-------------------
13:25:57 01/11/2024




9: To produce a list of defined archive destinations. It shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is. For a physical standby we should have at least one remote destination that points the primary set.

Primary :-

column destination format a35 wrap
column process format a7
column ID format 99
column mid format 99
SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

   THREAD#    DEST_ID DESTINATION                         STATUS    TARGET           SCHEDULE PROCESS MID
---------- ---------- ----------------------------------- --------- ---------------- -------- ------- ---
         1          1 /data1/ORCL/ARCH                    VALID     LOCAL            ACTIVE   ARCH      0
         1         32 /data1/ORCL/ARCH                    VALID     LOCAL            ACTIVE   RFS       0



Standby :-

column destination format a35 wrap
column process format a7
column ID format 99
column mid format 99
SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

   THREAD#    DEST_ID DESTINATION                         STATUS    TARGET           SCHEDULE PROCESS MID
---------- ---------- ----------------------------------- --------- ---------------- -------- ------- ---
         1          1 /data1/ORCLDG/ARCH                  VALID     PRIMARY          ACTIVE   ARCH      0
         1          2 orcl                                ERROR     STANDBY          PENDING  LGWR      0



10: Verify the last sequence# received and the last sequence# applied to standby database.

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# 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;

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1               107              107



-- Find which logs were applied in the last day
SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ;



-- Find last applied log
select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';
 

-- What are the managed standby processes doing?
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
 

-- Are we on production or standby?
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
 

-- Check for errors
 SELECT MESSAGE FROM V$DATAGUARD_STATUS;
 

-- Check that the DB was openned correctly 
 SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;


-- important lag statistics
 select * from v$dataguard_stats;



-- configure log shipping on primary
alter system set log_archive_dest_3='SERVICE=DEVPCOMB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=DEVPCOMB';

alter system set log_archive_dest_state_3='enable';


-- stopping and starting managed recovery on standby
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;


-- Manually register an archive log on standby
alter database register physical logfile '<fullpath/filename>';


-- Check if standby logs are configured right
set lines 100 pages 999
col member format a70
select st.group#
, st.sequence#
, ceil(st.bytes / 1048576) mb
, lf.member
from v$standby_log st
, v$logfile lf
where st.group# = lf.group#
/




Last applied log: Run this query on the standby database to see the last applied archivelog sequence number for each thread.

SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          69479
         2          78079




Archivelog difference: Run this on primary database. (not for real time apply)

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;
   THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
---------- ---------- ----------- -------------------- ----------
         2      78083       78082 01-JUL-2024 16:05:25          1
         1      69486       69485 01-JUL-2024 16:08:21          1



Apply/transport lags: v$dataguard_stats view will show the general synchronization status of standby database. Better to use on 11gR2 even with the latest PSU (Check bugs : 13394040, 7119382, 9968073, 7507011, 13045332, 6874522).

set lines 200
col name format a40
col value format a20
select * from v$dataguard_stats;

NAME                     VALUE             UNIT        TIME_COMPUTED         DATUM_TIME
------------------------ ----------------- ------      --------------------- ---------------------
transport lag            +00 00:09:44      …           07/01/2024 15:49:29   07/01/2024 15:49:27
apply lag                +00 00:09:44      …           07/01/2024 15:49:29   07/01/2024 15:49:27
apply finish time        +00 00:00:00.001  …           07/01/2024 15:49:29
estimated startup time   27                second      07/01/2024 15:49:29   






Apply rate: To find out the speed of media recovery in a standby database, you can use this query:

set lines 200
col type format a30
col ITEM format a20
col comments format a20
select * from v$recovery_progress;

START_TIM TYPE             ITEM                 UNITS        SOFAR      TOTAL TIMESTAMP COMMENTS
--------- ---------------- -------------------- ------------------ ---------- --------- ----
20-JUN-24 Media Recovery   Log Files            Files         3363          0
20-JUN-24 Media Recovery   Active Apply Rate    KB/sec       21584          0
20-JUN-24 Media Recovery   Average Apply Rate   KB/sec        3239          0
20-JUN-24 Media Recovery   Maximum Apply Rate   KB/sec       48913          0
20-JUN-24 Media Recovery   Redo Applied         Megabytes  2953165          0
20-JUN-24 Media Recovery   Last Applied Redo    SCN+Time         0          0 01-JUL-24
20-JUN-24 Media Recovery   Active Time          Seconds     233822          0
20-JUN-24 Media Recovery   Apply Time per Log   Seconds         57          0
20-JUN-24 Media Recovery   Checkpoint Time per  Seconds         11          0
                           Log
20-JUN-24 Media Recovery   Elapsed Time         Seconds     933565          0
20-JUN-24 Media Recovery   Standby Apply Lag    Seconds        483          0
11 rows selected.




To check Redo apply mode on physical standby database:
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS where dest_id=2;
RECOVERY_MODE
-----------------------
MANAGED




To check what MRP process is waiting:
select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b where a.sid=b.sid and b.sid=(select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0'))
EVENT                                           WAIT_TIME SECONDS_IN_WAIT
---------------------------------------------- ---------- ---------------
parallel recovery control message reply                 0               0



Archive Lag Histogram: The  V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds. For example following output shows that in 07/01/2024 archive lag reached 5 hours and in 06/15/2024 gap was 22 hours which was resolved after more than a week.



col name format a10
select * from  V$STANDBY_EVENT_HISTOGRAM;

NAME             TIME UNIT             COUNT LAST_TIME_UPDATED
---------- ---------- ------------  -------- --------------------
apply lag           0 seconds              0
apply lag           1 seconds              1 04/13/2024 01:40:23
apply lag           2 seconds              1 04/13/2024 01:40:24
apply lag           3 seconds              1 04/13/2024 01:40:25
apply lag           4 seconds              1 04/13/2024 01:40:26
...
apply lag          25 seconds              3 05/21/2024 06:31:19
apply lag          26 seconds              3 05/21/2024 06:31:20
apply lag          27 seconds              3 05/21/2024 06:31:23
apply lag          28 seconds              5 05/21/2024 06:31:22
apply lag          29 seconds              1 05/15/2024 07:47:46
apply lag          30 seconds              4 05/21/2024 06:31:24
...
apply lag          44 seconds              8 06/26/2024 00:33:14
apply lag          45 seconds              8 06/26/2024 00:33:15
apply lag          46 seconds              8 06/26/2024 00:33:17
apply lag          47 seconds              8 06/26/2024 00:33:18
apply lag          48 seconds              9 06/26/2024 00:33:19
...
apply lag          57 seconds             29 06/26/2024 06:33:02
apply lag          58 seconds             25 06/26/2024 06:33:27
apply lag          59 seconds             28 06/26/2024 06:33:28
apply lag           1 minutes              0
apply lag           2 minutes           9316 06/30/2024 18:33:45
apply lag           3 minutes          94601 07/01/2024 14:23:11
apply lag           4 minutes         209262 07/01/2024 14:56:13
apply lag           5 minutes         355744 07/01/2024 16:02:33
apply lag           6 minutes         522176 07/01/2024 16:03:30
apply lag           7 minutes         634199 07/01/2024 16:01:10
...
apply lag          47 minutes          28174 07/01/2024 05:14:53
apply lag          48 minutes          28231 07/01/2024 05:14:49
apply lag          49 minutes          27099 07/01/2024 05:14:44
apply lag          50 minutes          26532 07/01/2024 05:14:40
...
apply lag           3 hours           564493 07/01/2024 05:00:08
apply lag           4 hours           511628 06/22/2024 07:43:26
apply lag           5 hours           448572 06/22/2024 07:34:03
apply lag           6 hours           369037 06/22/2024 07:09:59
apply lag           7 hours           206117 06/21/2024 00:53:27
apply lag           8 hours           137932 06/21/2024 00:33:53
apply lag           9 hours           137091 06/21/2024 00:03:33
apply lag          10 hours            98103 06/20/2024 23:26:34
apply lag          11 hours           104157 06/20/2024 22:53:12
apply lag          12 hours           102141 06/20/2024 22:14:07
apply lag          13 hours            89214 06/20/2024 21:32:22
apply lag          14 hours            64880 06/20/2024 21:04:29
apply lag          15 hours            43471 06/20/2024 21:01:45
apply lag          16 hours            38075 06/20/2024 20:59:37
apply lag          17 hours            38449 06/20/2024 20:55:34
apply lag          18 hours            22049 06/16/2024 01:22:55
apply lag          19 hours            19873 06/16/2024 00:53:55
apply lag          20 hours            15985 06/15/2024 23:52:16
apply lag          21 hours            13290 06/15/2024 03:08:49
apply lag          22 hours             7330 06/15/2024 02:07:26
apply lag          23 hours             1606 02/15/2024 22:16:11
apply lag           1 days              3216 02/15/2024 22:00:42
apply lag           2 days             16768 02/15/2024 20:54:06
144 rows selected.





Redo switch report of primary database can be seen with the following query. This information may be helpful when investigating the possible causes of archive gaps, apply lags or data guard performance issues.


SET PAGESIZE 9999
col day format a15
SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM gv$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B;

DAY                 COUNT#       MIN#       MAX# DAILY_AVG_MB
--------------- ---------- ---------- ---------- ------------
2024-07-01             442     147345     147566       452608
2024-06-30             526     147083     147347       538624
2024-06-29             532     146817     147082       544768
2024-06-28             928     146353     146816       950272
2024-06-27             760     145973     146352       778240
2024-06-26             708     145619     145972       724992
2024-06-25             560     145338     145618       573440
2024-06-24             498     145090     145339       509952
2024-06-23             104     145038     145089       106496
2024-06-22             338     144869     145037       346112
2024-06-21             748     144495     144868       765952
2024-06-20             748     144121     144494       765952
2024-06-19             952     143645     144120       974848
2024-06-18             882     143204     143644       903168
2024-06-17             914     142746     143203       935936
2024-06-16             454     142520     142747       464896
2024-06-15            1520     141760     142519      1556480
2024-06-14            1862     140829     141759      1906688
2024-06-13             970     140343     140828       993280
2024-06-12             598     140045     140345       612352
2024-06-11             550     139770     140044       563200
2024-06-10             516     139511     139769       528384
2024-06-09             178     139423     139512       182272
2024-06-08             296     139275     139422       303104
2024-06-07             490     139030     139274       501760
2024-06-06             572     138744     139029       585728
2024-06-05             488     138499     138743       499712
2024-06-04             554     138223     138500       567296






at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

killing session in Oracle

  Killing session :- INACTIVE and MACHINE  set lines 300 pages 300 ; col module for a40 ; col machine for a10 ; select sid , machine ,SQL_ID...