Oracle Data Guard Queries

-
  • Standby database process status: You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1      69479     932864        261
ARCH      CLOSING               1      69480     928768        670
ARCH      CLOSING               2      75336     933888        654
ARCH      CLOSING               2      78079     930816        842
ARCH      CLOSING               1      69475     943104         79
RFS       IDLE                  0          0          0          0
...
RFS       RECEIVING             1      69481     688130       1024
MRP0      WAIT_FOR_LOG          2      78080          0          0
RFS       IDLE                  2      78080     873759          3




  • 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 29-JAN-2019 16:05:25          1
         1      69486       69485 29-JAN-2019 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).

SQL> set lines 200
SQL> col name format a40
SQL> col value format a20
SQL> select * from v$dataguard_stats;
NAME                     VALUE             UNIT        TIME_COMPUTED         DATUM_TIME
------------------------ ----------------- ------      --------------------- ---------------------
transport lag            +00 00:09:44      …           01/29/2019 15:49:29   01/29/2019 15:49:27
apply lag                +00 00:09:44      …           01/29/2019 15:49:29   01/29/2019 15:49:27
apply finish time        +00 00:00:00.001  …           01/29/2019 15:49:29
estimated startup time   27                second      01/29/2019 15:49:29   


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

SQL> set lines 200
SQL> col type format a30
SQL> col ITEM format a20
SQL> col comments format a20
SQL> select * from v$recovery_progress;
START_TIM TYPE             ITEM                 UNITS        SOFAR      TOTAL TIMESTAMP COMMENTS
--------- ---------------- -------------------- ------------------ ---------- --------- ----
29-JAN-19 Media Recovery   Log Files            Files         3363          0
29-JAN-19 Media Recovery   Active Apply Rate    KB/sec       21584          0
29-JAN-19 Media Recovery   Average Apply Rate   KB/sec        3239          0
29-JAN-19 Media Recovery   Maximum Apply Rate   KB/sec       48913          0
29-JAN-19 Media Recovery   Redo Applied         Megabytes  2953165          0
29-JAN-19 Media Recovery   Last Applied Redo    SCN+Time         0          0 29-JAN-19
29-JAN-19 Media Recovery   Active Time          Seconds     233822          0
29-JAN-19 Media Recovery   Apply Time per Log   Seconds         57          0
29-JAN-19 Media Recovery   Checkpoint Time per  Seconds         11          0
                           Log
29-JAN-19 Media Recovery   Elapsed Time         Seconds     933565          0
29-JAN-19 Media Recovery   Standby Apply Lag    Seconds        483          0
11 rows selected.
 
 
You can also use below before 11gR2. (Deprecated in 11gR2):
SQL> select APPLY_RATE from V$STANDBY_APPLY_SNAPSHOT;
  • 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
  


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.

SQL> SET PAGESIZE 9999
SQL> col day format a15
SQL> 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
--------------- ---------- ---------- ---------- ------------
2019-01-29              24         35         46        98304
2019-01-28              44         13         34       180224
2019-01-24               6         10         12        24576
2019-01-16               2          9          9         8192
2019-01-10               2          8          8         8192
2019-01-08               2          7          7         8192
2019-01-04               2          6          6         8192
2018-12-30               2          5          5         8192
2018-12-20               8          1          4        32768

9 rows selected.


  • Archive Lag Histogram: The  V$STANDBY_EVENT_HISTOGRAM view came with 11gR2 and shows the historical occurance of archive lags in terms of seconds.
SQL> col name format a10
SQL> select * from  V$STANDBY_EVENT_HISTOGRAM;
 
 
 
 The last one is a shell command and lists the archive log apply records of standby database alert log with the corresponding times at the end of the line. This is useful to see a clean picture of redo apply status on the standby database.
 
 tail -10000  /u01/app/oracle/diag/rdbms/odatdg/odatdg1/trace/alert_odatdg1.log |awk -v x="" '{if (index($0,"Media Recovery Log ")!=0) print $0" "x; else if($1=="Mon"||$1=="Tue"||$1=="Wed"||$1=="Thu"||$1=="Fri"||$1=="Sat"||$1=="Sun") x=$0}'
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_2_seq_20.473.998831039
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_1_seq_18.474.998831039
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_2_seq_21.477.998834931
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_1_seq_19.478.998834931
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_2_seq_22.480.998834957
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_1_seq_20.479.998834955
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_1_seq_21.486.998839527
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_2_seq_23.481.998835075
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_2_seq_24.487.998839587
Media Recovery Log +RECO/ODATDG/ARCHIVELOG/2019_01_29/thread_1_seq_22.485.998839527
 
 
 

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

-
SQL SERVER – Event ID 107- Report Server Windows Service cannot connect to the report server database. Error with Report Server One of my clients reported an error which is the subject of the blog. In the event viewer they were seeing below error:     Event Type: Error Event Source: Report Server Windows Service (MSSQLSERVER) Event Category: Management Event ID: 107 Computer: SQLSERVER Description: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database The error is generic because it doesn’t tell the exact error which is causing a connectivity error. My immediate question was that, are there any more errors? So, I asked them to look at Reporting Service Log file. Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. The application domain WindowsService_17 failed to initialize. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report se
Read more

SQL Server Builds Information

-
Image
Very Useful information about SQL Server Builds .  It is nicely presented in the blog . Please see  below for our reference . Microsoft SQL Server Version List What version of SQL Server do I have? This unofficial build chart lists all of the known Service Packs ( SP ), Cumulative Updates ( CU ), patches, hotfixes and other builds of MS SQL Server 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Useful articles: How to identify your SQL Server version and edition SQL Server Internal Database Versions Microsoft SQL Server Support Lifecycle Microsoft SQL Server Home Microsoft SQL Server Developer Center Microsoft TechNet: Microsoft SQL Server Microsoft Knowledge Base Sqlservr.exe versions   Quick summary:   RTM (no SP ) SP1 SP2 SP3 SP4   SQL Server 2017      codename vNext 14.0.1000.169 *new           SQL Server 2016 13.0.1601.5 13.0.4001.0 or 13.1.4001.0 13.0.5026.0 or 13.2.5026.0   S
Read more

Using DBCA silent install and disabling automatic memory management

-
Image
Avoid issue [DBT-11211] /app/oracle/product/12.2.0/dbhome_1/bin=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 20 -emConfiguration NONE [FATAL] [DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.    CAUSE: The current total physical memory is 11GB. Solution : disable automatic memory management with in the response file and you will be fine :) .  /app/oracle/product/12.2.0/dbhome_1/assistants/dbca/templates=> dbca -silent -createDatabase -templateName db_kp_template.dbt -gdbname dgtest1 -sid dgtest1 -responseFile NO_VALUE -characterSet AL32UTF8 -automaticMemoryManagement FALSE -memoryPercentage 20 -emConfiguration NONE Enter SYS user password: Enter SYSTEM user password: [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.    CAUS
Read more