Re: Need help with standby database REAL TIME APPLY

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

--
http://www.freelists.org/webpage/oracle-l