Re: Need help with standby database REAL TIME APPLY

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 28 Jul 2020 11:12:51 -0500
Message-ID: <CAJvnOJZmpnEtnb+4_Kv35JghqXaVdGPE98EjHqcKZym6vEr=xA_at_mail.gmail.com>



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
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Jul 27, 2020 at 10:45 PM Leng Burgess <lkaing_at_gmail.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi Sandra,
>>>>>>>>>
>>>>>>>>> Given that you have 2 primary instances (each with 4 redo groups),
>>>>>>>>> you need to create 2x4=8+2 standby redo logs.
>>>>>>>>>
>>>>>>>>> So try adding 6 more add more standby redo log groups.
>>>>>>>>>
>>>>>>>>> Please let me know how it goes.
>>>>>>>>>
>>>>>>>>> Cheers,
>>>>>>>>>
>>>>>>>>> Leng.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On 28 Jul 2020, at 7:10 am, Sandra Becker <sbecker6925_at_gmail.com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> OS: RHEL6
>>>>>>>>> Oracle: EE 12.1.0.2
>>>>>>>>> Primary: RAC
>>>>>>>>> Standby: Single instance
>>>>>>>>>
>>>>>>>>> We are moving our 2-node RAC database to a single instance
>>>>>>>>> primary-standby configuration. We have only 1 instance running; we are not
>>>>>>>>> using both nodes. I am trying to create the first standby and ensure it's
>>>>>>>>> syncing with Real Time Apply before proceeding. I have created the standby
>>>>>>>>> database, but can't seem to manage to get it to actually use REAL TIME
>>>>>>>>> APPLY. It says it started managed recover with Real Time Apply in the
>>>>>>>>> alert log, but then I see it's not using the SRLs I added. I've looked at
>>>>>>>>> several blogs and docs in MOS as well, and still have not been able to get
>>>>>>>>> it to recognize the SRLs. It will ship and apply the log if we do a log
>>>>>>>>> switch, but our DR policy is to use RTA. I created SRLs without the THREAD
>>>>>>>>> parameter and it created them for thread 0. That didn't work for me so I
>>>>>>>>> created SRLs for thread 1 and 2. The alert log says there are not standby
>>>>>>>>> redo logfiles available for T-2. I also ensured the size matched the
>>>>>>>>> primary online redo. Do I need more SRLs for T-2 and none for T-0 and
>>>>>>>>> T-1? Completely lost and confused at this point. I could really use some
>>>>>>>>> help figuring out where I went wrong.
>>>>>>>>>
>>>>>>>>> *Primary Queries*
>>>>>>>>> *select dest_id, status, recovery_mode, dest_name from
>>>>>>>>> v$archive_dest_status where dest_id = 2;*
>>>>>>>>> DEST_ID STATUS RECOVERY_MODE DEST_NAME
>>>>>>>>> ---------- ---------- ----------------------- ------------------
>>>>>>>>> 2 VALID MANAGED REAL TIME APPLY LOG_ARCHIVE_DEST_2
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> *SELECT l.inst_id, l.group#, l.thread#, l.sequence#,
>>>>>>>>> l.members, l.bytes/1024/1024 mbytes, l.archived, l.status, l.first_timeFROM
>>>>>>>>> gv$log lORDER BY l.thread#, l.group#/*
>>>>>>>>>
>>>>>>>>> INST_ID GROUP# THREAD# SEQUENCE# MBR MBYTES ARC STATUS
>>>>>>>>> FIRST_TIME
>>>>>>>>> ------- ------- ------- ---------- --- ---------- --- ----------
>>>>>>>>> -------------------
>>>>>>>>> 2 10 1 29353 2 1024 YES INACTIVE
>>>>>>>>> 2018-10-06 16:02:40
>>>>>>>>> 2 11 29356 2 1024 YES INACTIVE
>>>>>>>>> 2018-10-06 22:31:22
>>>>>>>>> 2 12 29354 2 1024 YES INACTIVE
>>>>>>>>> 2018-10-06 22:30:16
>>>>>>>>> 2 13 2 187312 2 1024 NO CURRENT
>>>>>>>>> 2020-07-27 20:30:25
>>>>>>>>> 2 14 187310 2 1024 YES INACTIVE
>>>>>>>>> 2020-07-27 20:14:09
>>>>>>>>> 2 15 187311 2 1024 YES INACTIVE
>>>>>>>>> 2020-07-27 20:15:54
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> *Standby Queries*
>>>>>>>>> *select thread#, group#, sequence#, round(bytes/1024/1024) bytes,
>>>>>>>>> status from v$standby_log;*
>>>>>>>>> THREAD# GROUP# SEQUENCE# BYTES STATUS
>>>>>>>>> ------- ------- ---------- ---------- -----------
>>>>>>>>> 0 10 0 1024 UNASSIGNED
>>>>>>>>> 0 11 0 1024 UNASSIGNED
>>>>>>>>> 0 12 0 1024 UNASSIGNED
>>>>>>>>> 0 13 0 1024 UNASSIGNED
>>>>>>>>> 1 20 0 1024 UNASSIGNED
>>>>>>>>> 1 21 0 1024 UNASSIGNED
>>>>>>>>> 1 22 0 1024 UNASSIGNED
>>>>>>>>> 1 23 0 1024 UNASSIGNED
>>>>>>>>> 2 24 0 1024 UNASSIGNED
>>>>>>>>> 2 25 0 1024 UNASSIGNED
>>>>>>>>> 2 26 0 1024 UNASSIGNED
>>>>>>>>> 2 27 0 1024 UNASSIGNED
>>>>>>>>>
>>>>>>>>> *dgmgrl*
>>>>>>>>> DGMGRL> *validate database 'UTILS_DB1';*
>>>>>>>>>
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>> Flashback Database Status:
>>>>>>>>> UTILS: Off
>>>>>>>>> UTILS_DB1: Off
>>>>>>>>>
>>>>>>>>> Standby Apply-Related Information:
>>>>>>>>> Apply State: Running
>>>>>>>>> Apply Lag: 27 minutes 18 seconds (computed 56 seconds
>>>>>>>>> ago)
>>>>>>>>> Apply Delay: 0 minutes
>>>>>>>>>
>>>>>>>>> Current Log File Groups Configuration:
>>>>>>>>> Thread # Online Redo Log Groups Standby Redo Log Groups
>>>>>>>>> Status
>>>>>>>>> (UTILS) (UTILS_DB1)
>>>>>>>>> 0 12 4
>>>>>>>>> Insufficient SRLs
>>>>>>>>> 1 3 0
>>>>>>>>> Insufficient SRLs
>>>>>>>>> Warning: standby redo logs not configured for thread 1 on
>>>>>>>>> UTILS_DB1
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> *Alert Log Excerpt*
>>>>>>>>> Starting background process MRP0
>>>>>>>>> Mon Jul 27 20:28:50 2020
>>>>>>>>> MRP0 started with pid=28, OS id=423445
>>>>>>>>> Mon Jul 27 20:28:50 2020
>>>>>>>>> MRP0: Background Managed Standby Recovery process started
>>>>>>>>> (UTILS_DB1)
>>>>>>>>> Mon Jul 27 20:28:55 2020
>>>>>>>>> Started logmerger process
>>>>>>>>> Mon Jul 27 20:28:55 2020
>>>>>>>>> Managed Standby Recovery starting Real Time Apply
>>>>>>>>> Mon Jul 27 20:28:55 2020
>>>>>>>>> Parallel Media Recovery started with 10 slaves
>>>>>>>>> Mon Jul 27 20:28:55 2020
>>>>>>>>> Waiting for all non-current ORLs to be archived...
>>>>>>>>> Mon Jul 27 20:28:55 2020
>>>>>>>>> All non-current ORLs have been archived.
>>>>>>>>> Mon Jul 27 20:28:55 2020
>>>>>>>>> Media Recovery Waiting for thread 2 sequence 187311 (in transit)
>>>>>>>>> Completed: alter database recover managed standby database
>>>>>>>>> disconnect
>>>>>>>>> Mon Jul 27 20:29:43 2020
>>>>>>>>> .
>>>>>>>>> .
>>>>>>>>> .
>>>>>>>>> Primary database is in MAXIMUM PERFORMANCE mode
>>>>>>>>> RFS[8]: Assigned to RFS process (PID:423753)
>>>>>>>>> RFS[8]: No standby redo logfiles available for T-2
>>>>>>>>> RFS[8]: Opened log for thread 2 sequence 187312 dbid 3599144416
>>>>>>>>> branch 818022052
>>>>>>>>> Mon Jul 27 20:30:27 2020
>>>>>>>>> Media Recovery Waiting for thread 2 sequence 187312 (in transit)
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Sandy B.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Sandy B.
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Andrew W. Kerber
>>>>>>>
>>>>>>> 'If at first you dont succeed, dont take up skydiving.'
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Sandy B.
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> Sandy B.
>>>>>
>>>>>
>>>>
>>>> --
>>>> Andrew W. Kerber
>>>>
>>>> 'If at first you dont succeed, dont take up skydiving.'
>>>>
>>>
>>>
>>> --
>>> Sandy B.
>>>
>>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
> --
> Sandy B.
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 28 2020 - 18:12:51 CEST

Original text of this message