Re: Need help with standby database REAL TIME APPLY

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 28 Jul 2020 10:00:03 -0500
Message-ID: <CAJvnOJZphqPKqfPcGQGmn+-=nTgT+TmdnHpSPde4JMZeCavw6w_at_mail.gmail.com>



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.'


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 28 2020 - 17:00:03 CEST

Original text of this message