Skip to Main Content
  • Questions

  • "alter session sync with primary" with Maximum Performance Protection Mode

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Matthias.

Asked: February 23, 2024 - 11:39 am UTC

Last updated: March 01, 2024 - 1:56 am UTC

Version: 19.18

Viewed 1000+ times

You Asked

"alter session sync with primary"
raises ORA-03173 for us.

SQL> select database_role, open_mode, db_unique_name from v$database;

DATABASE_ROLE    OPEN_MODE            DB_UNIQUE_NAME
---------------- -------------------- ------------------------------
PHYSICAL STANDBY READ ONLY WITH APPLY mdpams

SQL> alter session sync with primary;
ERROR:
ORA-03173: Standby may not be synced with primary


Is this expected behaviour in protection mode "Maximum Performance" or do we maybe hit some bug ?

dgmgrl shows nothing suspicious

DGMGRL> show configuration

Configuration - fsc

  Protection Mode: MaxPerformance
  Members:
  mdpfra - Primary database
    mdpams - Physical standby database
    mdpdev - Snapshot standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 61 seconds ago)

DGMGRL> show database mdpams

Database - mdpams

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 4.94 MByte/s
  Real Time Query:    ON
  Instance(s):
    mdpams1 (apply instance)
    mdpams2

Database Status:
SUCCESS

DGMGRL>

and Connor said...

From the docs (and thanks to Ludovico Caldara for his direction)

The following additional restrictions apply if STANDBY_MAX_DATA_DELAY is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is used:
- The standby database must receive redo data via the SYNC transport.
- The redo transport status at the standby database must be SYNCHRONIZED and the primary database must be running in either maximum protection mode or maximum availability mode.
- Real-time apply must be enabled.

So my guess is you are perhaps running ASYNC not SYNC ?

Rating

  (6 ratings)

Comments

indeed, we have

Matthias, February 28, 2024 - 12:23 pm UTC 

primary > select value from v$parameter where name = 'log_archive_dest_2';

VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
service="mdpams", ASYNC NOAFFIRM delay=0 optional compression=enable max_failure=0 reopen=300 db_unique_name="mdpams" net_timeout=30, valid_for=(online_logfile,all_roles)a

we will use our own sync_with_primary

Matthias, February 28, 2024 - 12:42 pm UTC

given that we have a db-link primary, we will use
create procedure sync_with_primary is
declare
  scn_primary number default dbms_flashback.get_system_change_number@primary;
begin
  loop
    exit when dbms_flashback.get_system_change_number > scn_primary;
    dbms_lock.sleep(1e-3);
  end loop;
end;
/

(to be called from standby)

thus avoiding all restrictions concerning
alter session sync with primary

(which we do not really comprehend)
Connor McDonald
February 29, 2024 - 2:16 am UTC

(which we do not really comprehend)

To avoid querying the primary’s SCN at every request (performance and network impact), the sessions on the standby get the last commit SCN from the standby memory structures (last received SCN). Because it’s synchronous, there’s guarantee that whatever has been committed on the primary at any given moment, has been already transferred to the standby RFS processes. So the session can wait for that SCN to be applied (another memory structure on the standby) and consistently read what’s been committed on the primary.

If the transport is ASYNC, the standby does not know if it received all the committed transactions, and would have to get the SCN from the primary for every request, then wait for that SCN to be both transported and applied.


In terms of your proc,

1. I'd use dbms_session not dbms_lock
2. I don't think we go to millisecond granularity, eg

SQL> set timing on
SQL> begin
  2  for i in 1 .. 100 loop
  3    dbms_session.sleep(0.01);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.97

SQL> begin
  2  for i in 1 .. 1000 loop
  3    dbms_session.sleep(0.001);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02


3. I'm not a fan of loop forever, perhaps have a "get out of jail" in there
4. In terms of the logic, you'd need to check how the SCNs handled when you access the db link.

I dont have an ADG handy but with standard databases, SCNs get aligned when you do db link operations, eg

DB1 , displaying SCN every second and then in DB2 we ask for the SCN at DB1 via database link at point highlighted below

                  SCN
---------------------
       16331128531417
       16331128531417
       16331128531418
       16331128531418
       16331128531418
       16331128559406   <<<======== 
       16331128559406


The SCN is bumped to align with the other node.

I'm *assuming* we don't do this with an ADG setup, but you want to test that

workaround

Matthias, February 28, 2024 - 4:04 pm UTC

Do you see any issues with this workaround ?
(besides that it does not compile :-) - which can be fixed easily)

I mean: wouldn't it behave the same as
alter session sync with primary

? - besides maybe being less performant

my test

Matthias, February 29, 2024 - 8:46 am UTC

I'm *assuming* we don't do this with an ADG setup, but you want to test that

Looks to me you are right, here my test-case
primary > set arrays 1
primary > with
  2  function x return varchar2 is
  3  begin
  4  -- sleep until the next full second
  5  dbms_session.sleep(extract(second from sysdate+1/86400-systimestamp));
  6  return to_char(systimestamp, 'HH24:MI:SSxFF3') || to_char(dbms_flashback.get_system_change_number, '9999999999999999999990');
  7  end x;
  8  select
  9    x() as r
 10  from dual
 11  connect by level<=10
 12  /

R
--------------------------------------------------------------------------------
09:38:00.030           246607823788
09:38:01.054           246607828402
09:38:02.014           246607833054
09:38:03.039           246607836866
09:38:04.063           246607840909
09:38:05.022           246607844308
09:38:06.046           246607847742
09:38:07.007           246607850961
09:38:08.030           246607854854
09:38:09.055           246607859045

10 rows selected.



standby > set arrays 1
standby > with
  2  function x return varchar2 is
  3  begin
  4  -- sleep until the next full second
  5  dbms_session.sleep(extract(second from sysdate+1/86400-systimestamp));
  6  return to_char(systimestamp, 'HH24:MI:SSxFF3') || to_char(dbms_flashback.get_system_change_number@mdp.vwd.com@mdp_rw, '9999999999999999999990');
  7  end x;
  8  select
  9    x() as r
 10  from dual
 11  connect by level<=10
 12  /


R
--------------------------------------------------------------------------------
09:38:00.998           246607828142
09:38:02.014           246607833077
09:38:03.038           246607836877
09:38:04.062           246607840931
09:38:05.023           246607844325
09:38:06.046           246607847762
09:38:07.006           246607850972
09:38:08.030           246607854863
09:38:09.054           246607859059
09:38:10.014           246607862778

10 rows selected.


So, e.g.
09:38:02.014 246607833054 (prim) 246607833077 (standby)
09:38:08.030 246607854854 (prim) 246607854863 (standby)

just a tiny difference caused by network delay

putting it together

Matthias, February 29, 2024 - 9:06 am UTC 

create or replace procedure sync_with_primary is
-- see also discussion on https://asktom.oracle.com/ords/f?p=100:11:::::P11_QUESTION_ID:9548507600346179700
  "ORA-03176" exception;
  pragma exception_init("ORA-03176", -03176); -- ORA-03176: ALTER SESSION SYNC WITH PRIMARY only works on an open physical standby database
  "ORA-03173" exception;
  pragma exception_init("ORA-03173", -03173); -- ORA-03173: Standby may not be synced with primary
procedure workaround_known_restriction is
  scn_primary number default dbms_flashback.get_system_change_number@primary;
  sd date default sysdate + 1/24; -- try not longer than an hour
begin
  loop
    exit when dbms_flashback.get_system_change_number > scn_primary;
    dbms_session.sleep(.01);
    if sysdate > sd then 
      raise_application_error(-20999, 'sync_with_primary takes longer than an hour - check status of standby');
    end if;
  end loop;
end workaround_known_restriction;
begin
  execute immediate
    'alter session sync with primary';
  return;
exception 
  when "ORA-03176" then
    return; -- that's ok
  when "ORA-03173" then
    workaround_known_restriction;
end;


now should do it.

(
Sigh - why can't Oracle internally not do this workaround for me when I issue "alter session sync with primary" - I wish it would do.
Maybe a suggestion to improve.
)

But now comes the strangest thing here:
"alter session sync with primary" indeed worked until we (for the first time on this database) tested switchover (and switchover again to keep the status quo)

Why is still a mystery to me, I don't think the DBA changed anything in the Dataguard configuration, he only did a switchover twice.
But maybe he did, need to find out.

Connor McDonald
March 01, 2024 - 1:56 am UTC

Maybe a suggestion to improve.


I'll pass around internally

Thanks

Matthias, March 01, 2024 - 7:30 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.