Re: Need help with standby database REAL TIME APPLY

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 28 Jul 2020 10:09:04 -0500
Message-ID: <CAJvnOJZT-z9OQkGO7f-dEca+eOMGGVgKe5mEuRhPVcxM-Snd_A_at_mail.gmail.com>



Ok. Also, I seem to remember some issues when thread 0 shows up for the redo logs. You may need to drop the redo logs for thread 0 since that thread doesnt actually exist. I could be misremembering at this point though, I only saw it once before.

:
*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

On Tue, Jul 28, 2020 at 10:06 AM Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> Sandra,
>
> What's the thread for groups 22/23/24/25 and 32/33/34/35 as the latest
> output shows it as blank (unless you have "break on thread#" in your
> SQL*Plus session). You have to specify it explicitly in each command:
>
> alter database add standby logfile *thread 1* group 22 '.......' size 1024M;
> alter database add standby logfile *thread 1* group 23 '.......' size 1024M;
>
> alter database add standby logfile *thread 2* group 32 '.......' size 1024M;
> etc
>
> On both the primary and the standby databases.
>
>
> Neil Chandler
> https://chandlerdba.com/2019/01/03/data-guard-unexpected-lag/
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Sandra Becker <sbecker6925_at_gmail.com>
> *Sent:* 28 July 2020 15:30
> *To:* Andrew Kerber <andrew.kerber_at_gmail.com>
> *Cc:* Leng Burgess <lkaing_at_gmail.com>; oracle-l <oracle-l_at_freelists.org>
> *Subject:* Re: Need help with standby database REAL TIME APPLY
>
> 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_time FROM gv$log l
> ORDER 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:09:04 CEST

Original text of this message