This discussion is archived
8 Replies Latest reply: Nov 15, 2012 4:16 PM by mseberg RSS

real-time apply cascaded logical standby database

Ora_83 Newbie
Currently Being Moderated
Hi


I have a primary database orcl
Pysical standby database orcl_std
Cascaded logical standby database orcl_tri which receives archivelogs from orcl_std

Real time apply is enabled both in orcl_std (physical standby) and orcl_tri (logical standby)

When I create a table in primary orcl, I am unable to see it on orcl_tri (Although real time apply is enabled)

However, when I switch log in primary, I can see the new table on orcl_tri.


My question is, why realtime apply is not working in my scenerio ?


orcl_std : ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION USING CURRENT LOGFILE;
orcl_tri: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Oracle 11.2.0.3.0
  • 1. Re: real-time apply cascaded logical standby database
    JohnWatson Guru
    Currently Being Moderated
    You need to think about the redo transport: the LNS on orcl will be transmitting redo to orcl_std where it is saved to a standby log and applied in near real-time, but it is not possible to propagate redo onwards from a standby log. You have to wait until it is archived. This is all in the docs.
  • 2. Re: real-time apply cascaded logical standby database
    Ora_83 Newbie
    Currently Being Moderated
    Hi John,

    Thanks for the info.

    Suppose I have a primary database orcl and logical standby database orcl_std. ( No other database)

    Real time apply is enabled. I have standby redologs in both primary and standby sides and I`ve started real time recovery.

    Does it mean that when I create a new table in primary database, it will not be applied to logical standby database untill the log is archived ?

    In other words, no matter I use logical or physical standby database, changes will only applied to standby database once the standby log is archived.
  • 3. Re: real-time apply cascaded logical standby database
    teits Pro
    Currently Being Moderated
    Ora_83 wrote:
    Hi John,

    Thanks for the info.

    Suppose I have a primary database orcl and logical standby database orcl_std. ( No other database)

    Real time apply is enabled. I have standby redologs in both primary and standby sides and I`ve started real time recovery.

    Does it mean that when I create a new table in primary database, it will not be applied to logical standby database untill the log is archived ?

    In other words, no matter I use logical or physical standby database, changes will only applied to standby database once the standby log is archived.
    NO. when you enable real-time apply, you see changes in the standby DB almost immediate especially when you use SYNC attribute in log_archive_dest_N.

    There is only a delay for cascade standby because redo log file that it was written to has been archived locally.
    http://docs.oracle.com/cd/E11882_01/server.112/e10700/log_transport.htm#BEHBIGHF

    HTH
    Tobi
  • 4. Re: real-time apply cascaded logical standby database
    Ora_83 Newbie
    Currently Being Moderated
    Thanks,

    just for the experiement, I`ve created a primary database orcl and logical standby database orcl_std. ( no other database)

    Real time apply is enabled. I have standby redologs in both primary and standby sides and I`ve started recovery with below command:

    ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

    When I create a new table in primary database, I am unable to see it on standby database (Although real time apply is enabled)
    However, when I switch log in primary, I can see the new table in standby database.


    Why the change is not applied immediately ? Like I said, this is not a cascaded standby.
  • 5. Re: real-time apply cascaded logical standby database
    mseberg Guru
    Currently Being Moderated
    If the Apply Service is unable to keep up with the Redo Rate then the Apply Service it will change to Archivelog Files.

    Then it will resume Real-Time Apply when the problem is resolved.

    Reasons :


    Load
    Network issue
    Setup issue
    Standby data issue
    Standby redo logs are wrong size, number or don't exist at all.

    Anything else that could ever go wrong...


    Best Regards

    mseberg
  • 6. Re: real-time apply cascaded logical standby database
    Ora_83 Newbie
    Currently Being Moderated
    Hi mseberg,


    Thanks for your reply.

    There is no load or network issue as I`ve just created these databases for the experiement.

    I have the same output from standby and primary databases.
    SQL> select bytes/1024/1024 from  v$standby_log;
    
    BYTES/1024/1024
    ---------------
                 10
                 10
                 10
    I can see below output in standby alertlog
    Fri Nov 16 08:39:51 2012
    ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
    ALTER DATABASE START LOGICAL STANDBY APPLY (orcl)
    with optional part
    IMMEDIATE
    Attempt to start background Logical Standby process
    Fri Nov 16 08:39:51 2012
    LSP0 started with pid=37, OS id=16141
    Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
    LOGMINER: Parameters summary for session# = 1
    LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
    LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
    LOGMINER: SpillScn 1953318, ResetLogScn 995548
    LOGMINER: summary for session# = 1
    LOGMINER: StartScn: 0 (0x0000.00000000)
    LOGMINER: EndScn: 0 (0x0000.00000000)
    LOGMINER: HighConsumedScn: 1955287 (0x0000.001dd5d7)
    LOGMINER: session_flag: 0x1
    LOGMINER: Read buffers: 16
    Fri Nov 16 08:39:55 2012
    LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=30 OS id=16145 sid=49 started
    Fri Nov 16 08:39:55 2012
    LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=39 OS id=16149 sid=44 started
    Fri Nov 16 08:39:55 2012
    LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=40 OS id=16153 sid=50 started
    LOGMINER: Turning ON Log Auto Delete
    LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 202, +DATA/orcl_std/archivelog/2012_11_15/thread_1_seq_202.349.799450179
    LOGMINER: End mining logfiles during commit scan for session 1
    LOGMINER: Turning ON Log Auto Delete
    LOGMINER: Begin mining logfile for session 1 thread 1 sequence 202, +DATA/orcl_std/archivelog/2012_11_15/thread_1_seq_202.349.799450179
    LOGMINER: End   mining logfile for session 1 thread 1 sequence 202, +DATA/orcl_std/archivelog/2012_11_15/thread_1_seq_202.349.799450179
    Fri Nov 16 08:40:04 2012
    LOGSTDBY Analyzer process AS00 started with server id=0 pid=41 OS id=16162
    Fri Nov 16 08:40:05 2012
    LOGSTDBY Apply process AS03 started with server id=3 pid=45 OS id=16175
    Fri Nov 16 08:40:05 2012
    LOGSTDBY Apply process AS04 started with server id=4 pid=46 OS id=16179
    Fri Nov 16 08:40:05 2012
    LOGSTDBY Apply process AS01 started with server id=1 pid=42 OS id=16167
    Fri Nov 16 08:40:05 2012
    LOGSTDBY Apply process AS05 started with server id=5 pid=47 OS id=16183
    Fri Nov 16 08:40:05 2012
    LOGSTDBY Apply process AS02 started with server id=2 pid=44 OS id=16171
    Do you think real-time apply wasnt setup properly ?
  • 7. Re: real-time apply cascaded logical standby database
    Ora_83 Newbie
    Currently Being Moderated
    My apologies,

    I realized that standby redologs are not sized properly.
  • 8. Re: real-time apply cascaded logical standby database
    mseberg Guru
    Currently Being Moderated
    No worries. I believe that was part of my list of reasons.

    Thanks for closing the question.

    Best Regards

    mseberg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points