8 Replies Latest reply: Nov 15, 2012 6:16 PM by mseberg RSS

    real-time apply cascaded logical standby database

    Ora_83
      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
          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
            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
              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
                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
                  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
                    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
                      My apologies,

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

                        Thanks for closing the question.

                        Best Regards

                        mseberg