5 Replies Latest reply on Dec 12, 2010 2:00 PM by bpolarski

    Oracle Streams checkpoint interval

    668990
      From: http://download.oracle.com/docs/cd/E11882_01/server.112/e17069/strms_adcapture.htm#CACIFFHF
      ====================
      Capture Process Checkpoints
      A checkpoint is information about the current state of a capture process that is stored persistently in the data dictionary of the database running the capture process. A capture process tries to record a checkpoint at regular intervals called checkpoint intervals
      ====================

      I would like to know: Is (Oracle Stream) Checkpoint intervals, mentioned above, equal to LOG_CHECKPOINT_INTERVAL? If yes, what is the unit, (e.g. mill-second, or second) of LOG_CHECKPOINT_INTERVAL in v_$parameter; If no, how to set the Checkpoint interval for Oracle Streams?

      Thanks
        • 1. Re: Oracle Streams checkpoint interval
          bpolarski
          Nothing to do. From the Oracle Doc we read:
          The LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of 
            the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log.
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams109.htm

          So this parameters deals with the LGWR and the archives and its unit is not a time but a size


          The required checkpoint interval you are speaking about is called 'Required Checkpoint SCN' :
                      The SCN that corresponds to the lowest checkpoint for which a capture process requires redo data is the required checkpoint SCN. 
                      If a capture process is stopped and restarted, then it starts scanning the redo log from the SCN that corresponds to 
                      its required checkpoint SCN.  The required checkpoint SCN is important for recovery
                      if a database stops unexpectedly. Also, if the first SCN is reset for a capture process, then it must be set to a value
                      that is less than or equal to the required checkpoint SCN for the captured process. You can determine the required checkpoint
                      SCN for a capture process by querying the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view.
          Here is a query that explain all usage of the figure related to streams SCN capture counters which deals with the streams position in the logs:
          ====================
          Oracle definitions:
          ====================
          First SCN : The first SCN is the lowest SCN in the redo log from which a capture process can capture changes.
                      If you specify a first SCN during capture process creation, then the database must be able to access
                      redo data from the SCN specified and higher.
          
          Start SCN : The start SCN is the SCN from which a capture process begins to capture changes. You can specify a start SCN
                      that is different than the first SCN during capture process creation, or you can alter a capture process to set
                      its start SCN. The start SCN does not need to be modified for normal operation of a capture process. Typically,
                      you reset the start SCN for a capture process if point-in-time recovery must be performed on one of the destination
                      databases that receive changes from the capture process. In these cases, the capture process can be used to capture
                      the changes made at the source database after the point-in-time of the recovery.
          
          Required Checkpoint SCN  : The SCN that corresponds to the lowest checkpoint for which a capture process requires redo data
                      is the required checkpoint SCN. If a capture process is stopped and restarted, then it starts scanning the redo log
                      from the SCN that corresponds to its required checkpoint SCN. The required checkpoint SCN is important for recovery
                      if a database stops unexpectedly. Also, if the first SCN is reset for a capture process, then it must be set to a value
                      that is less than or equal to the required checkpoint SCN for the captured process. You can determine the required checkpoint
                      SCN for a capture process by querying the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view.
          
          Instantiation SCN  : The system change number (SCN) for a table which specifies that only changes that were committed after the SCN
                      at the source database are applied by an apply process.
          
          Checkpoint_retention_time : Controls the amount of days of metadata retained by moving  FIRST_SCN  forward
          
                    First scn must be <= Start Scn
                    First scn must be <= applied scn (only when applied scn > 0)
                    First scn must be <= required chkpoint SCN
          
          Prompt Report Streams SCN positioning
          prompt
          SQL="break on capture_name on report
          col start_scn for 999999999999 head 'Start scn'
          col first_scn for 999999999999 head 'First scn'
          col CAPTURED_SCN for 999999999999 head 'Captured scn'
          col las for 999999999999 head 'Applied scn'
          col LAST_ENQUEUED_SCN for 999999999999 head 'Last scn|Enqueued' justify c
          col REQUIRED_CHECKPOINT_SCN for 999999999999 head 'Required |Checkpoint scn' justify c
          col MAX_CHECKPOINT_SCN for 999999999999 head 'Max |Checkpoint scn' justify c
          col capture_name for a22 head 'Capture name'
          set linesize 150
          select CAPTURE_NAME, FIRST_SCN, start_scn, APPLIED_SCN las, CAPTURED_SCN, CHECKPOINT_RETENTION_TIME, LAST_ENQUEUED_SCN,
                   REQUIRED_CHECKPOINT_SCN, MAX_CHECKPOINT_SCN from SYS.DBA_CAPTURE;
          • 2. Re: Oracle Streams checkpoint interval
            668990
            SCN is not time-based figure. I would like to set Oracle Stream checkpoint interval to some specific value, e.g. 10ms, so that the capture process can wake up every 10ms and check whether redo log entry availabe to sync in redo log file.

            How to set the interval i mentioned above?

            Thanks
            • 3. Re: Oracle Streams checkpoint interval
              bpolarski
              This is not the checkpoint retention, but propagation latency :

              http://download.oracle.com/docs/cd/B28359_01/server.111/b28322/best_prop.htm#CEGCHGIF
              col DESTINATION_QUEUE_NAME new_value DESTINATION_QUEUE_NAME noprint
              col DESTINATION_DBLINK new_value DESTINATION_DBLINK noprint
              col source_queue_name new_value source_queue_name noprint
              
              select SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME, DESTINATION_DBLINK from SYS.DBA_PROPAGATION  where propagation_name = upper('<PROPAGATION_NAME');
              
              
               execute DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE( queue_name => 'STRMADMIN.&source_queue_name',  
                          destination =>'&DESTINATION_DBLINK',  destination_queue=>'&DESTINATION_QUEUE_NAME',  
                          latency=> 0.1                                 -- Latency is expressed in Seconds. I am not sure if you can set value of less than 1 second.
                         duration =>null, next_time=>null) 
              /
                
              esac
              • 4. Re: Oracle Streams checkpoint interval
                668990
                Hi bpolarski ,

                But it is propagation level (queue). Do you know how often the capture process check the redo log? how to change the interval ?
                • 5. Re: Oracle Streams checkpoint interval
                  bpolarski
                  In 10g, the capture process check the DML when they are written out from the log_buffer into the current redo log. Streams is built-in in Oracle Kernel; You can't beat that. But do not mix capture evaluation rule and the propagation process. The capture is immediate while it is the propagation process that is responsible of the LCR movement.