Re: Need help with standby database REAL TIME APPLY

  • From: Sandra Becker <sbecker6925@xxxxxxxxx>
  • To: Neil Chandler <neil_chandler@xxxxxxxxxxx>
  • Date: Wed, 29 Jul 2020 07:48:58 -0600

I found that output odd as well.  I don't have any online redo for Thread
0.  Yes, I do the log switch and the primary catches up, but starts to fall
behind again..

*Query Results*
*select * from v$log;*
You'll note the different blocksize.  The standby is on newer storage.
Primary:
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
      CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------- ------------- ------------------- ------------
------------------- ----------
        11          1      29356 1073741824        512          2 YES
INACTIVE      3.1313E+11 2018-10-06 22:31:22   3.1313E+11 2018-10-06
23:05:03          0
        12          1      29354 1073741824        512          2 YES
INACTIVE      3.1313E+11 2018-10-06 22:30:16   3.1313E+11 2018-10-06
22:31:17          0
        14          2     187465 1073741824        512          2 NO
 CURRENT       3.7676E+11 2020-07-29 13:30:01   2.8147E+14
             0
        15          2     187464 1073741824        512          2 YES
ACTIVE        3.7676E+11 2020-07-29 13:15:02   3.7676E+11 2020-07-29
13:30:01          0

Standby:
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
      CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------- ------------- ------------------- ------------
------------------- ----------
         1          1          0 1073741824       4096          2 YES
UNUSED                 0                                0
           0
         2          1          0 1073741824       4096          2 YES
UNUSED                 0                                0
           0
         3          2          0 1073741824       4096          2 YES
UNUSED                 0                                0
           0
         4          2          0 1073741824       4096          2 YES
UNUSED                 0                                0
           0

*select * from v$standby_log; *
Primary:
    GROUP# DBID               THREAD#  SEQUENCE#      BYTES  BLOCKSIZE
  USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
NEXT_TIME           LAST_CHANGE# LAST_TIME               CON_ID
---------- --------------- ---------- ---------- ---------- ----------
---------- --- ---------- ------------- ------------------- ------------
------------------- ------------ ------------------- ----------
        21 UNASSIGNED               1          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        22 UNASSIGNED               1          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        23 UNASSIGNED               1          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        24 UNASSIGNED               1          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        25 UNASSIGNED               1          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        31 UNASSIGNED               2          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        32 UNASSIGNED               2          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        33 UNASSIGNED               2          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        34 UNASSIGNED               2          0 1073741824        512
     0 YES UNASSIGNED
                                                       0
        35 UNASSIGNED               2          0 1073741824        512
     0 YES UNASSIGNED
Standby:
    GROUP# DBID               THREAD#  SEQUENCE#      BYTES  BLOCKSIZE
  USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
NEXT_TIME           LAST_CHANGE# LAST_TIME               CON_ID
---------- --------------- ---------- ---------- ---------- ----------
---------- --- ---------- ------------- ------------------- ------------
------------------- ------------ ------------------- ----------
        21 UNASSIGNED               1          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        22 UNASSIGNED               1          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        23 UNASSIGNED               1          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        24 UNASSIGNED               1          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        25 UNASSIGNED               1          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        31 UNASSIGNED               2          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        32 UNASSIGNED               2          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        33 UNASSIGNED               2          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        34 UNASSIGNED               2          0 1073741824       4096
     0 YES UNASSIGNED
                                                       0
        35 UNASSIGNED               2          0 1073741824       4096
     0 YES UNASSIGNED

*select * from v$logfile order by type,group#;*
I see two of the primary online log members have a status of INVALID.  I
will be fixing those immediately.
    GROUP# STATUS     TYPE     MEMBER
           IS_     CON_ID
---------- ---------- --------
------------------------------------------------------- --- ----------
        11 INVALID    ONLINE
+DATA/UTILS/ONLINELOG/group_11.1091.1046544645          NO           0
        11            ONLINE   +DATA/utils/onlinelog/group_11.921.918836807
           NO           0
        12 INVALID    ONLINE
+DATA/UTILS/ONLINELOG/group_12.1092.1046544657          NO           0
        12            ONLINE   +DATA/utils/onlinelog/group_12.923.918836815
           NO           0
        14            ONLINE   +DATA/utils/onlinelog/group_14.925.918836829
           NO           0
        14            ONLINE
+DATA/UTILS/ONLINELOG/group_14.1094.1046544697          NO           0
        15            ONLINE   +DATA/utils/onlinelog/group_15.926.918836837
           NO           0
        15            ONLINE
+DATA/UTILS/ONLINELOG/group_15.1095.1046544707          NO           0
        21            STANDBY
 +DATA/UTILS/ONLINELOG/group_21.1118.1046959599          YES          0
        21            STANDBY
 +DATA/UTILS/ONLINELOG/group_21.1119.1046959595          NO           0
        22            STANDBY
 +DATA/UTILS/ONLINELOG/group_22.1116.1046959605          YES          0
        22            STANDBY
 +DATA/UTILS/ONLINELOG/group_22.1117.1046959603          NO           0
        23            STANDBY
 +DATA/UTILS/ONLINELOG/group_23.1115.1046959611          NO           0
        23            STANDBY
 +DATA/UTILS/ONLINELOG/group_23.1114.1046959615          YES          0
        24            STANDBY
 +DATA/UTILS/ONLINELOG/group_24.1113.1046959617          NO           0
        24            STANDBY
 +DATA/UTILS/ONLINELOG/group_24.1112.1046959621          YES          0
        25            STANDBY
 +DATA/UTILS/ONLINELOG/group_25.922.1046959623           NO           0
        25            STANDBY
 +DATA/UTILS/ONLINELOG/group_25.547.1046959627           YES          0
        31            STANDBY
 +DATA/UTILS/ONLINELOG/group_31.924.1046959631           NO           0
        31            STANDBY
 +DATA/UTILS/ONLINELOG/group_31.1093.1046959633          YES          0
        32            STANDBY
 +DATA/UTILS/ONLINELOG/group_32.1104.1046959637          NO           0
        32            STANDBY
 +DATA/UTILS/ONLINELOG/group_32.1105.1046959641          YES          0
        33            STANDBY
 +DATA/UTILS/ONLINELOG/group_33.1106.1046959643          NO           0
        33            STANDBY
 +DATA/UTILS/ONLINELOG/group_33.1107.1046959647          YES          0
        34            STANDBY
 +DATA/UTILS/ONLINELOG/group_34.1108.1046959651          NO           0
        34            STANDBY
 +DATA/UTILS/ONLINELOG/group_34.1109.1046959655          YES          0
        35            STANDBY
 +DATA/UTILS/ONLINELOG/group_35.1110.1046959657          NO           0
        35            STANDBY
 +DATA/UTILS/ONLINELOG/group_35.1111.1046959661          YES          0

Standby:
    GROUP# STATUS     TYPE     MEMBER
           IS_     CON_ID
---------- ---------- --------
------------------------------------------------------- --- ----------
         1            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_1.270.1046894003        NO           0
         1            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_1.271.1046894003        NO           0
         2            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_2.274.1046894005        NO           0
         2            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_2.272.1046894005        NO           0
         3            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_3.281.1046961383        NO           0
         3            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_3.282.1046961381        NO           0
         4            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_4.340.1046961385        NO           0
         4            ONLINE
+DATA/UTILS_DB1/ONLINELOG/group_4.341.1046961383        NO           0
        21            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_21.330.1046959789       NO           0
        21            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_21.331.1046959791       YES          0
        22            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_22.332.1046959791       NO           0
        22            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_22.333.1046959793       YES          0
        23            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_23.261.1046959793       NO           0
        23            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_23.260.1046959795       YES          0
        24            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_24.259.1046959797       NO           0
        24            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_24.325.1046959797       YES          0
        25            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_25.326.1046959799       NO           0
        25            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_25.327.1046959799       YES          0
        31            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_31.328.1046959801       NO           0
        31            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_31.329.1046959801       YES          0
        32            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_32.269.1046959803       YES          0
        32            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_32.268.1046959803       NO           0
        33            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_33.266.1046959805       YES          0
        33            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_33.267.1046959805       NO           0
        34            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_34.264.1046959807       YES          0
        34            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_34.265.1046959807       NO           0
        35            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_35.263.1046959809       YES          0
        35            STANDBY
 +DATA/UTILS_DB1/ONLINELOG/group_35.262.1046959809       NO           0

*select * from v$dataguard_stats order by name;*
We are forcing a log switch every 15 minutes to ensure we don't fall too
far behind on this database.  It has periods of a few hours before it has
enough activity to do a normal switch.

SOURCE_DBID SOURCE_DB_ NAME                      VALUE
UNIT                           TIME_COMPUTED                  DATUM_TIME
              CON_ID
----------- ---------- ------------------------- -------------------------
------------------------------ ------------------------------
---------------------- ----------
          0            apply finish time
day(2) to second(3) interval   07/29/2020 13:45:41
                   0
          0            apply lag                 +00 00:00:00
 day(2) to second(0) interval   07/29/2020 13:45:41            07/29/2020
13:45:01             0
          0            estimated startup time    30
 second                         07/29/2020 13:45:41
                   0
          0            transport lag             +00 00:00:00
 day(2) to second(0) interval   07/29/2020 13:45:41            07/29/2020
13:45:01             0


On Tue, Jul 28, 2020 at 3:50 PM Neil Chandler <neil_chandler@xxxxxxxxxxx>
wrote:

Sandy,

the only thing that looks strange in the output below is:

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (UTILS)                 (UTILS_DB1)
    *0         26                      0
Insufficient SRLs*
    Warning: standby redo logs not configured for thread 0 on UTILS_DB1
    1         2                       5                       Sufficient
SRLs


You have 26 online redo log groups on Thread 0 on the primary?

on the primary and on the standby, can you remove anything that interferes
with the output, like "break on thread#" and

select * from v$log;
select * from v$standby_log;
select * from v$logfile;

and compare the output.

I assume if you do an "alter system archive log current" on the primary
and check the standby, it does a catch-up then starts to fall behind again?

select * from v$dataguard_stats order by name;

Neil Chandler





------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Sandra Becker <sbecker6925@xxxxxxxxx>
*Sent:* 28 July 2020 18:43
*To:* Hameed, Amir <Amir.Hameed@xxxxxxxxx>
*Cc:* Andrew Kerber <andrew.kerber@xxxxxxxxx>; Leng Burgess <
lkaing@xxxxxxxxx>; oracle-l <oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: Need help with standby database REAL TIME APPLY

I had dropped/recreated the log files several times already.  I did try to
defer/enable the  standby destination from the primary.  It didn't make a
difference.


Sandy

On Tue, Jul 28, 2020 at 11:33 AM Hameed, Amir <Amir.Hameed@xxxxxxxxx>
wrote:

Hi Sandy,

I have seen similar issue in the past and the solution was to drop all SRL
and recreate them. Once you do that, you may also want to DISABLE the
standby destination from primary and then re-enable it.



Thanks,

Amir

*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Sandra Becker
*Sent:* Tuesday, July 28, 2020 12:21 PM
*To:* Andrew Kerber <andrew.kerber@xxxxxxxxx>
*Cc:* Leng Burgess <lkaing@xxxxxxxxx>; oracle-l <oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: Need help with standby database REAL TIME APPLY



I verified all online and standby logs are the same size on both the
primary and the standby.  Unfortunately, we no longer have Oracle support.
My boss cancelled it earlier this year as a money saving strategy.  We do
have support from Spinnaker, but they've been less than helpful the last 3
times I tried to get help from them.  Guess that's my only option right
now.  Worst case scenario, I wipe out the standby and start all over.  I
was trying to avoid that scenario.



Thanks everyone for your help.



Sandy



Sandy



On Tue, Jul 28, 2020 at 10:13 AM Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:

Ok, kind of a shot in the dark, but look at the gv$standby_logfile view
and make sure all the standby logs and redo logs are the same size. If
thats not the issue, get with Oracle support, I cant think of anything
else.



On Tue, Jul 28, 2020 at 10:57 AM Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:

Stopped/started managed recovery.  No change.



On Tue, Jul 28, 2020 at 9:48 AM Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:

Now you might stop and start managed recovery and see if that helps.



On Tue, Jul 28, 2020 at 10:47 Sandra Becker <sbecker6925@xxxxxxxxx> wrote:

From the primary:

**  Online Redo  **

THREAD#  GROUP#  SEQUENCE# MBR     MBYTES ARC STATUS
 FIRST_TIME
------- ------- ---------- --- ---------- --- -------------------------
-------------------
      1      11      29356   2       1024 YES INACTIVE
 2018-10-06 22:31:22
      1      12      29354   2       1024 YES INACTIVE
 2018-10-06 22:30:16
      2      14     187355   2       1024 NO  CURRENT
2020-07-28 15:30:01
      2      15     187354   2       1024 YES INACTIVE
 2020-07-28 15:15:01



From the standby:

SELECT TYPE, COUNT(*) FROM V$LOGFILE GROUP BY TYPE;

TYPE       COUNT(*)
-------- ----------
ONLINE            8
STANDBY          20



Neil - I have the break on thread# set in my session.  I did explicitly
use the THREAD 1 (or 2) when I added the standby redo logs to both the
primary and the standby.  I also dropped the standby redo logs for THREAD 0.



Sandy



On Tue, Jul 28, 2020 at 9:00 AM Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:

Can you please send us the same output for the redo logs on the primary?
(not the standby redo logs)?



On Tue, Jul 28, 2020 at 9:31 AM Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:

I have 5 SRLs for each thread, but the alert log still says "No standby
redo logfiles available for T-2".



SELECT
        thread#,group#, sequence#, used, archived, status,
ROUND(bytes/1024/1024) mbytes, last_time
FROM v$standby_log
ORDER BY
        thread#,
        group#;



THREAD#  GROUP#  SEQUENCE#       USED ARC STATUS
 MBYTES LAST_TIME
------- ------- ---------- ---------- --- -------------------------
---------- -------------------
      1      21          0          0 YES UNASSIGNED
 1024
             22          0          0 YES UNASSIGNED
 1024
             23          0          0 YES UNASSIGNED
 1024
             24          0          0 YES UNASSIGNED
 1024

             25          0          0 YES UNASSIGNED
 1024

      2      31          0          0 YES UNASSIGNED
 1024
             32          0          0 YES UNASSIGNED
 1024
             33          0          0 YES UNASSIGNED
 1024
             34          0          0 YES UNASSIGNED
 1024
             35          0          0 YES UNASSIGNED
 1024



On Tue, Jul 28, 2020 at 8:08 AM Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:

Ok, redoing the SRLs.



Sandy



On Tue, Jul 28, 2020 at 7:45 AM Andrew Kerber <andrew.kerber@xxxxxxxxx>
wrote:

You need one more srl per thread on the standby than primary redo logs.
Ie, if you have 4 redo logs per thread on the primary you need at least 5
standby redo logs per thread.



On Tue, Jul 28, 2020 at 8:41 AM Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:

I added 6 more standby redo log groups to thread 2.  Didn't make any
difference.  Still seeing the message "RFS[8]: No standby redo logfiles
available for T-2".  Today's dgmgrl output:

 Database Role:     Physical standby database
  Primary Database:  UTILS

  Ready for Switchover:  No
  Ready for Failover:    Yes (Primary Running)

  Capacity Information:
    Database   Instances        Threads
    UTILS      1                2
    UTILS_DB1  1                1
    Warning: the target standby has fewer instances than the
    primary database, this may impact application performance

  Temporary Tablespace File Information:
    UTILS TEMP Files:      1
    UTILS_DB1 TEMP Files:  1

  Flashback Database Status:
    UTILS:      Off
    UTILS_DB1:  Off

  Data file Online Move in Progress:
    UTILS:      No
    UTILS_DB1:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        2 minutes 9 seconds (computed 15 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     2 minutes 9 seconds (computed 15 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    UTILS Standby Redo Log Files:      Cleared
    UTILS_DB1 Online Redo Log Files:   Cleared
    UTILS_DB1 Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (UTILS)                 (UTILS_DB1)
    0         22                      0                       Insufficient
SRLs
    Warning: standby redo logs not configured for thread 0 on UTILS_DB1
    1         2                       4                       Sufficient
SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (UTILS_DB1)             (UTILS)
    1         3                       4                       Sufficient
SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (UTILS)                   (UTILS_DB1)
    1          1024 MBytes               1024 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (UTILS_DB1)               (UTILS)
    1          1024 MBytes               1024 MBytes

  Apply-Related Property Settings:
    Property                        UTILS Value              UTILS_DB1
Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO

  Transport-Related Property Settings:
    Property                        UTILS Value              UTILS_DB1
Value
    LogXptMode                      ASYNC                    ASYNC
    RedoRoutes                      <empty>                  <empty>
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    MaxConnections                  1                        1
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE
    LogShipping                     ON                       ON

  Automatic Diagnostic Repository Errors:
    Error                       UTILS    UTILS_DB1
    No logging operation        NO       NO
    Control file corruptions    NO       NO
    SRL Group Unavailable       NO       NO
    System data file missing    NO       NO
    System data file corrupted  NO       NO
    System data file offline    NO       NO
    User data file missing      NO       NO
    User data file corrupted    NO       NO
    User data file offline      NO       NO



Sandy








-- 
Sandy B.

Other related posts: