Re: Need help with standby database REAL TIME APPLY

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 28 Jul 2020 08:45:47 -0500
Message-ID: <CAJvnOJYNo2utRLQm6y60HYq-u14X=qdkWVkt2NLeyUSa6Oq=oQ_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 28 2020 - 15:45:47 CEST

Original text of this message