This content has been marked as final. Show 5 replies
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.
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' :
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:
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.
==================== 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;
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?
This is not the checkpoint retention, but propagation latency :
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
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 ?
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.