Thread 0 - an undocumented change of behaviour between Oracle 11 and 12 which
isn't fully cooked. Still undocumented I think.
https://chandlerdba.com/2019/01/03/data-guard-unexpected-lag/
Neil
________________________________
From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
Sent: 28 July 2020 16:09
To: Neil Chandler <neil_chandler@xxxxxxxxxxx>
Cc: sbecker6925@xxxxxxxxx <sbecker6925@xxxxxxxxx>; oracle-l
<oracle-l@xxxxxxxxxxxxx>
Subject: Re: Need help with standby database REAL TIME APPLY
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@xxxxxxxxxxx<mailto:neil_chandler@xxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Sandra Becker <sbecker6925@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>>
Sent: 28 July 2020 15:30
To: Andrew Kerber <andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>>
Cc: Leng Burgess <lkaing@xxxxxxxxx<mailto:lkaing@xxxxxxxxx>>; oracle-l
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>> wrote:
Ok, redoing the SRLs.
Sandy
On Tue, Jul 28, 2020 at 7:45 AM Andrew Kerber
<andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>> 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@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>> 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@xxxxxxxxx<mailto:lkaing@xxxxxxxxx>> 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@xxxxxxxxx<mailto:sbecker6925@xxxxxxxxx>> 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.'
