Re: Need help with standby database REAL TIME APPLY
Date: Wed, 29 Jul 2020 07:48:58 -0600
Message-ID: <CAJzM94CA9Or-b3UJC5azOT96ECQbKFVWLT9XExT-DujjBYpLHw_at_mail.gmail.com>
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$standby_log; *
*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
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_at_hotmail.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on > behalf of Sandra Becker <sbecker6925_at_gmail.com> > *Sent:* 28 July 2020 18:43 > *To:* Hameed, Amir <Amir.Hameed_at_xerox.com> > *Cc:* Andrew Kerber <andrew.kerber_at_gmail.com>; Leng Burgess < > lkaing_at_gmail.com>; oracle-l <oracle-l_at_freelists.org> > *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_at_xerox.com> > 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_at_freelists.org <oracle-l-bounce_at_freelists.org> *On > Behalf Of *Sandra Becker > *Sent:* Tuesday, July 28, 2020 12:21 PM > *To:* Andrew Kerber <andrew.kerber_at_gmail.com> > *Cc:* Leng Burgess <lkaing_at_gmail.com>; oracle-l <oracle-l_at_freelists.org> > *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_at_gmail.com> > 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_at_gmail.com> > wrote: > > Stopped/started managed recovery. No change. > > > > On Tue, Jul 28, 2020 at 9:48 AM Andrew Kerber <andrew.kerber_at_gmail.com> > 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_at_gmail.com> 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_at_gmail.com> > 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_at_gmail.com> > 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_at_gmail.com> > wrote: > > Ok, redoing the SRLs. > > > > Sandy > > > > On Tue, Jul 28, 2020 at 7:45 AM Andrew Kerber <andrew.kerber_at_gmail.com> > 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_at_gmail.com> > 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. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 29 2020 - 15:48:58 CEST