12 Replies Latest reply: Dec 24, 2013 8:14 AM by TSharma-Oracle RSS

    Find out lag between Production and Logical, If production crashes!

    misterimran

      Dear All,

       

      My Oracle Database is 10gR2 on Windows 2003 Server, I have a Logical standby for my production database.

      I just want to know how to find out how much transaction and time lag there will be if the production database is crashed and current archive is not shipped on the logical standby.

      How to document this thing, we can't say that this is a real time replication since there is no data guard setup. Its just log shipping and applying.

       

      My redo log file sizes are 10MB each. In peak time log switch may happen twice in a minute on certain occasion but mostly it is from 8 to 1 minutes.

       

      Is there any configuration without using a data guard you can have zero percent of data loss in logical standby setup?

       

      But still the important thing is to determine the loss if primary goes down and standby role needs to be change as primary manually.

       

      Regards, Imran

        • 1. Re: Find out lag between Production and Logical, If production crashes!
          TSharma-Oracle

          /*** Check last applied logs or check the sync or Lag***/

           

          SET LONG 1000

          ALTER SESSION SET NLS_DATE_FORMAT  = 'DD-MON-YY HH24:MI:SS';

          column APPLIED_SCN format 999999999999999999

          column NEWEST_SCN format 999999999999999999

          SELECT APPLIED_SCN,APPLIED_TIME, NEWEST_SCN,NEWEST_TIME FROM DBA_LOGSTDBY_PROGRESS;

           

          Difference between applied and newest time is the lag between primary and logical standby database;

           

           

          /*** Use to check errors ***/

           

          SET LONG 1000

          ALTER SESSION SET NLS_DATE_FORMAT  = 'DD-MON-YY HH24:MI:SS';

          SELECT EVENT_TIME, COMMIT_SCN, EVENT, STATUS FROM DBA_LOGSTDBY_EVENTS order by event_time;

          • 2. Re: Find out lag between Production and Logical, If production crashes!
            misterimran

             

            As per the result from the query newest_time and applied_time has difference in seconds. But if i see result of this query:

             

            sequence#, first_time, next_time, file_name, dict_begin, dict_end, applied from dba_logstdby_log order by sequence# desc;

             

            Archive log files are sometimes shipped after 20 minutes, obviously depending on the load on database. Very rarely they are shipped twice in a minute.

             

            But still the time lag between the production and standby is in seconds, how changes are applied when the file recived time is 8:16:06 AM while applied_time is 8:23:27?

             

            This is what I am looking for and need to understand the logic behind. I am not using data guard, it is just a logical standby.

            Regards,
            Imran

            • 3. Re: Find out lag between Production and Logical, If production crashes!
              misterimran

              Dear All,

               

              Despite of seeking help from different websites I am unable to find the answer of few questions that are very confusing for me. I am recently being involve in creating and managing Logical and Physical Standbys in 10gR2 on Windows platforms as discussed above.

               

              Question # 1: Its the same when I query DBA_LOGSTDBY_PROGRESS most of the time the applied time and newest time have difference in seconds, sometime even 1 or 2 seconds. But when i see the last archived log shipped from production database it is 15-20 minutes to the current time. So how come my lag is just in seconds?

               

              Question # 2: To my understanding I haven't implement any data guard between primary and logical standby. How to check if the data guard is enabled? or it is just log shipping and applying process as what is ment by Logical Standby?

               

              Question # 3: Why in Logical standby the date and time of data and index files is like 20 days to current day, where as the number of records are the same in production and logical?

               

              Question # 4: How to manage archive log deletion in production? if lag is in seconds as per query from DBA_LOGSTDBY_PROGRESS so i can delete all logs completed before 'sysdate-1'? OR I have to keep all archivelogs since that are on production after the date & time in datafile of standby?

               

              These might seem stupid questions... But these are going in my mind and I am unable to find answers after reading so many documents.

               

              Kindly help me to clear logic. I have studied and created physical and logical database both and they are working fine. But these are kind of post creation questions that will clear the concept.

               

              Thanks, Imran

              • 4. Re: Find out lag between Production and Logical, If production crashes!
                TSharma-Oracle

                misterimran wrote:

                 

                Dear All,

                 

                Despite of seeking help from different websites I am unable to find the answer of few questions that are very confusing for me. I am recently being involve in creating and managing Logical and Physical Standbys in 10gR2 on Windows platforms as discussed above.

                 

                Question # 1: Its the same when I query DBA_LOGSTDBY_PROGRESS most of the time the applied time and newest time have difference in seconds, sometime even 1 or 2 seconds. But when i see the last archived log shipped from production database it is 15-20 minutes to the current time. So how come my lag is just in seconds?

                IT could be little bit different if the applying process is in working. Shipped time and applied will be different as shipped archived log will take to time to be applied. Time taken depends upon the size of the logfile or it could also depends upon your speed of logical standby database.

                 

                Question # 2: To my understanding I haven't implement any data guard between primary and logical standby. How to check if the data guard is enabled? or it is just log shipping and applying process as what is ment by Logical Standby?

                 

                I don't understand  the question properly. Logical standby database is a type of dataguard. Let me assume you are either talking about data guard broker or guard status in logical standby database.

                For Data guard Broker: It automates the creation and monitoring of the DG configurations. Switchover and failover becomes very easy if you use dgmgrl. You can check if in your init file you have "dg_broker_start=true".

                For guard_status: select name,guard_status from v$database;

                 

                Question # 3: Why in Logical standby the date and time of data and index files is like 20 days to current day, where as the number of records are the same in production and logical?

                 

                Question # 4: How to manage archive log deletion in production? if lag is in seconds as per query from DBA_LOGSTDBY_PROGRESS so i can delete all logs completed before 'sysdate-1'? OR I have to keep all archivelogs since that are on production after the date & time in datafile of standby?

                You can simply define RMAN retention and deletion policy on production. RMAN on production will not delete the archive file if it is not shipped and needed to recover(apply) on logical standby database. RMAn knows if the files are needed in logical standby database and won't delete.

                 

                These might seem stupid questions... But these are going in my mind and I am unable to find answers after reading so many documents.

                 

                Kindly help me to clear logic. I have studied and created physical and logical database both and they are working fine. But these are kind of post creation questions that will clear the concept.

                 

                Thanks, Imran

                • 5. Re: Find out lag between Production and Logical, If production crashes!
                  misterimran

                  Dear Sharma,

                   

                  Thanks for reading my questions and giving a detail reply.

                  Regarding Question # 1, this is the oppsoite of what I am asking:

                  "It could be little bit different if the applying process is in working. Shipped time and applied will be different as shipped archived log will take to time to be applied. Time taken depends upon the size of the logfile or it could also depends upon your speed of logical standby database."

                  My confusion is that applied time is newer than the shipped time, if it was opposite, I would have thought ok file is transfers and now it is being processed by the apply processes. In this case applied time should be either equal or less than the last shipped file. This sounds logical. But why Applied Time is alsmost equal to the current time while the last file shipped is 20 minutes old?

                   

                  Regarding Question # 2, People say that Logical Standby Database is not data guard. Data guard is a process on top of production and standby databases that manages and monitor them. Yes Data Guard Broker is a different thing and needs to be implement. But even if data guard broker is not implemented we can still say log shipping and appliying process is a part of data guard?

                   

                  Regarding Question # 3, that you missed I can see that the actual data files and index files that are part of different tablespaces, there update date and time is the time when I created this Logical Standby Setup. Why the time is not chnaging as logs are being applied to these files, though the standby is in sync with production.

                   

                  I really appreciate giving your time for reading and replying this thread.

                  • 6. Re: Find out lag between Production and Logical, If production crashes!
                    TSharma-Oracle

                    Answer#1: You seems to contradicting yourself. Applied time will always be newer than log shipped time. This is what you are also saying "Time is alsmost equal to the current time while the last file shipped is 20 minutes old"

                    Your above statement is a true statement. Files will be shipped first and then the apply process will apply the logs.

                     

                    Answer2#: Ok, I think this is what you want to know. Dataguard is an automatic way to transfer and apply logs to the standby database using log_archive_dest parameters or FAL_CLIENT or FAL_SERVER. In standby database(like for standard edition), you cannot use this automation because it is an enterprise option, so in that case you will be writing your own scripts to transfer and apply logs and checking gaps.

                     

                    Answer3#: I honestly do not know. This could be the OS specially windows thing. I have both primary and logical standby database on unix. I do not see any time difference, it is always current. Sorry.

                    • 7. Re: Find out lag between Production and Logical, If production crashes!
                      misterimran

                      Thanks again Sharma,

                       

                      We will discuss only question # 1 now still confused, lets make it simple.

                      Lets say a file is shipped from production to standby at 9:00 AM today, obviously it will be containing data from the production database untill 9:00 AM.

                      Now its 9:13 AM, no file has been shipped after the last file that was shipped at 9:00 AM. Now the apply process is working on the last file, its time will be newer than 9:00 AM since its started applying after the file was shipped, like the time could be current time. This is what you are saying.

                       

                      BUT it means that the lag... the actual time difference between primary and standby database is NOT the time difference between applied_time and newest_time, BUT it is the difference between time stamp of last shipped file and newest_time. In the case above since file was shipped at 9:00 and now its 9:13, applied time is also 9:13 but the time lag between production and standby is 13 minutes.

                       

                      This is confusing for me. Other then the last shipped log file where else the data can come from?

                      • 8. Re: Find out lag between Production and Logical, If production crashes!
                        TSharma-Oracle

                        I still do not understand. Time lag is the difference between the redo log received and redo applied on a standby database.

                         

                        Newest_time: Estimate of the time and date of the NEWEST_SCN. This will always before archived log timestamp on standby. It means the latest scn time in the archived file on the primary and then only it will start transferring to standby destination and then it will be applied.

                         

                        So the scenario could be:

                         

                        Log switch occur: 9:00

                        Newest_time: 9:00

                        Transfer takes 1 minute : timestamp: 9:01

                        applied time: less than 9:00

                        After applying,

                        applied time = newest_time

                         

                        Make sense?

                        • 9. Re: Find out lag between Production and Logical, If production crashes!
                          misterimran

                          Time lag is the difference between the redo log received and redo applied on a standby database! agreed!

                          Newest_time: Estimate of the time and date of the NEWEST_SCN. This will always before archived log timestamp on standby! This is not happening!

                          the scenario is

                          Log switch occur: 9:00 OK

                          Newest_time: 9:00, NO its 9:13

                          Transfer takes 1 minute : timestamp: 9:01 OK

                          applied time: less than 9:00 NO, applied_time is 9:13

                          After applying,

                          applied time = newest_time TRUE

                           

                          Event count(*) from from tables where insert is performed after every few seconds are updated on standby in seconds between 9:00 and 9:13, tables were always update.

                           

                          So the only question left is that why newest time is greater than transfer timestamp?

                          • 10. Re: Find out lag between Production and Logical, If production crashes!
                            TSharma-Oracle

                            Like I said it (timestamp) could be the issue with windows. we have hardly any database on windows.

                            But Newest time is the estimate(could be off a little) of the newest_scn.

                             

                            >>applied time: less than 9:00 NO, applied_time is 9:13

                             

                            Sometimes the sync happens too fast, it takes a moment to be in sync.

                             

                            You can stop logical standby apply and then switch the log file and do your analysis.

                            • 11. Re: Find out lag between Production and Logical, If production crashes!
                              misterimran

                              What I have found is that, if i start the apply services using ALTER DATABASE START LOGICAL STANDBY APPLY; it works exactly like you mentioned. Applied time is equal to the archive log file received and there will be a gap in applied_time and newest_time until and unless a new archivelog file is recived from primary.

                              BUT when i start the apply service ALTER DATABSE START LOGICAL STANDBY APPLY IMMEDIATE; it enables the real time apply and redo data is applied directly from the standby redo log files as they are being filled, without requiring the redo data to be filled and archived.

                               

                              But how can we name these two behaviours? APPLY and APPLY IMMEDIATE

                              • 12. Re: Find out lag between Production and Logical, If production crashes!
                                TSharma-Oracle

                                If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.