Re: Need help with standby database REAL TIME APPLY

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.


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