Re: Need help with standby database REAL TIME APPLY
Date: Tue, 28 Jul 2020 21:49:59 +0000
Message-ID: <DB7PR10MB2090264F4F2395C6AABAAF6E85730_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto: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<mailto:andrew.kerber_at_gmail.com>>
Cc: Leng Burgess <lkaing_at_gmail.com<mailto:lkaing_at_gmail.com>>; oracle-l <oracle-l_at_freelists.org<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 28 2020 - 23:49:59 CEST