Modify the read checkpoint and write checkpoint of each process in Oracle GoldenGate (ogg ).

Modify the read checkpoint and write checkpoint of each process in Oracle GoldenGate (ogg ).

Note: exercise caution when modifying the Read and Write checkpoints of each process in Oracle GoldenGate (ogg. Make sure that you have mastered the specific meaning of the reading and writing checkpoints of each ogg process.

 

BEGIN {NOW | yyyy-mm-dd [: hh: mi: [ss [. cccccc] | EOF | SEQNO <sequence number>} -- modify the start extraction point of the extraction process: (that is, the read checkpoint of myext) alter extract myext BEGIN yyyy-mm-dd: hh: mi: ssalter extract myext BEGIN now -- modify the write checkpoint of myext: alter extract myext, etrolover
-- Modify the read checkpoint alter extract mydp, EXTSEQNO 1234, EXTRBA 0 for the transmission mydp Process
-- Modify the write checkpoint alter extract mydp etrolover of the transmission mydp Process
-- Modify the read checkpoint ALTER replicat myrep, EXTSEQNO 1234, EXTRBA 0 of myrep.

 

 

(Urgent) How to monitor the execution of Oracle Data check points is the information obtained when the database has a Check Point

Top view system checkpoint actions

We can set LOG_checkpointS_TO_ALERT to TRUE to open the trace of checkpoint, so that we can track the checkpoint operation.
Alter system set LOG_checkpointS_TO_ALERT = TRUE;

After this setting, the system checkpoint will be recorded in the alert _ $ SID. log file.

The V $ DATAFILE_HEADER also stores some information about the full checkpoint, including the checkpoint occurrence time and the number of times the corresponding SCN has been checkpoint.
Select file # NO, status, tablespace_name, name, dbms_flashback.get_system_change_number CUR_SCN,
To_char (resetlogs_time, 'yyyy-MM-DD HH24: MI: ss') RST_DT, resetlogs_change # RST_SCN,
To_char (checkpoint_time, 'yyyy-MM-DD HH24: MI: ss') CKPT_DT, checkpoint_change # CKPT_SCN, checkpoint_count CKPT_CNT
From v $ datafile_header;

/**
No status TABLESPACE_NAME CUR_SCN RST_DT RST_SCN CKPT_DT CKPT_SCN CKPT_CNT
--------------------------------------------------------------------------------------------------
1 online system 533541 16:51:53 446075 22:03:58 532354
2 ONLINE UNDOTBS1 533541 16:51:53 446075 22:03:58 532354
3 online sysaux 533541 16:51:53 446075 22:03:58 532354
4 online users 533541 16:51:53 446075 22:03:58 532354 64
5 online example 533541 16:51:53 446075 22:03:58 532354

Role of oracle checkpoint

A checkpoint is a database event that writes modified data from the cache to a disk and updates control files and data files.
There are three types of checkpoints:
1) Local checkpoint: a single instance performs a checkpoint operation on all database data files, and all dirty cache areas of this instance write data files.
Trigger command:
Svmrgrl> alter system checkpoint local;
This command triggers a local checkpoint.
2) Global checkpoint: all instances (corresponding to the parallel data server) execute a checkpoint operation on all data files in the database. All dirty cache areas of this instance write data files.
Trigger command
Svrmgrl> alter system checkpoint global;
This command triggers a global checkpoint.
3) file checkpoint: all instances need to perform a checkpoint operation on the data file set, such as using the Hot backup command alter tablespace USERS begin backup, or the tablespace offline command alter tablespace USERS offline, A checkpoint is executed for all data files in the USERS tablespace.
Checkpoint processing steps:
1) Get instance status queue: The instance status queue is obtained when the instance status changes. ORACLE obtains this queue to ensure that the database is open during the checkpoint execution;
2) Get the current checkpoint information: Get the structure of the checkpoint record information, this structure includes the current checkpoint time, active threads, the current thread for checkpoint processing, and the address information of the recovery cutoff point in the log file;
3) cache area ID: identifies all dirty cache areas. When a dirty cache area is found at the check point, it is marked as needing to be refreshed, the identified dirty cache area is written by the system process DBWR to write the content in the dirty cache area to the data file;
4) Dirty cache Refresh: After the DBWR process writes all dirty cache areas to the disk, it sets a flag to indicate that the dirty cache area has been written to the disk. Check the system process LGWR and CKPT until the DBWR process ends;
5) update control files and data files.
Note: The control file and Data File Header contain the checkpoint structure information.
In either case, the checkpoint information in the file header (when obtaining the current checkpoint information) is not updated:
1) if the data file is not in the hot backup mode, ORACLE does not know when the operating system will read the file header, and the backup copy must have the checkpoint SCN at the start of the copy;
ORACLE retains a checkpoint counter in the data file header. during normal operations, ensure that the current version of the data file is used to prevent the wrong version of the data file from being restored. Even in hot backup mode, the counters are still incremental. The checkpoint counters of each data file are also kept in the corresponding data file items of the control file.
2) When checking that the SCN is smaller than the checkpoint SCN in the file header, this indicates that the changes made by the checkpoint have been written to the disk. During the global checkpoint processing, this situation may occur when a hot backup quick checkpoint updates the file header. It should be noted that ORACLE captures and checks the SCN before much of the actual checkpoint processing, in addition, it is likely that it is interrupted by a command like the Hot backup command alter tablespace USERS begin backup for fast checkpoint processing.
Before updating the data file, ORACLE will verify its data consistency. When the verification is completed, that is, the data file header is updated to reflect the current checkpoint; unverified data files and data files with Errors During writing are ignored. If the log file is overwritten, the file may need to be restored by media. In this case, the ORACLE system process DBWR takes the data file offline.
Checkpoint Algorithm Description:
The dirty cache area uses a new queue link called the checkpoint queue. Each change to the cache area has a related redo value. The checkpoint queue contains dirty log cache areas which are sorted by their locations in log files. That is, in the checkpoint queue, the cache areas are sorted by their low redo values. Note that because the cache area is linked to the queue in the first dirty order, if you write the full text in the cache area...>