Checkpoint process (CKPT) in Oracle

The Checkpoint process (not surprisingly) controls database checkpoints. Every 3 seconds the Checkpoint process determines the earliest redo log entry for which changes have not been written to the database. This becomes the checkpoint, and it is recorded in the control file and in all the datafiles. The following series of images illustrates this process:

You can see the Oracle background processes with this query:

select *  
from
   v$session
where
   type ='BACKGROUND';

Here are some of the most important Oracle background processes:

ARCH	(Optional) Archive process writes filled redo logs to the archive log location(s). In RAC, the various ARCH processes can be utilized to ensure that copies of the archived redo logs for each instance are available to the other instances in the RAC setup should they be needed for recovery.
CJQ - Job Queue Process	(CJQ) - Used for the job scheduler. The job scheduler includes a main program (the coordinator) and slave programs that the coordinator executes. The parameter job_queue_processes controls how many parallel job scheduler jobs can be executed at one time.
CKPT:	Checkpoint process writes checkpoint information to control files and data file headers
CQJ0:	Job queue controller process wakes up periodically and checks the job log. If a job is due, it spawns Jnnnn processes to handle jobs.
DBWR:	Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.
FMON:	The database communicates with the mapping libraries provided by storage vendors through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information. When you specify the FILE_MAPPING initialization parameter for mapping data files to physical devices on a storage subsystem, then the FMON process is spawned.
LGWR:	Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance thread of redo logs.
LMON:	Lock Manager process
MMON:	The Oracle 10g background process to collect statistics for the Automatic Workload Repository (AWR).
MMNL:	This process performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation.
MMAN:	is used for internal database tasks that manage the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components.
PMON:	Process Monitor process recovers failed process resources. If MTS (also called Shared Server Architecture) is being utilized, PMON monitors and restarts any failed dispatcher or server processes. In RAC, PMON role as service registration agent is particularly important.
Pnnn:	(Optional) Parallel Query Slaves are started and stopped as needed to participate in parallel query operations.
RBAL:	This process coordinates rebalance activity for disk groups in an Automatic Storage Management instance.
SMON:	System Monitor process recovers after instance failure and monitors temporary segments and extents. SMON in a non-failed instance can also perform failed instance recovery for other failed RAC instance.
WMON:	The "wakeup" monitor process

In Oracle, CQJ0 is a background process that is related to the Oracle Job Scheduler. Specifically, CQJ0 handles the Scheduler jobs within the Oracle Database. It works alongside the CJQ0 (Job Queue Coordinator) process to manage and execute jobs that have been scheduled in the database.

• CQJ0 is responsible for scheduling and managing Scheduler jobs, which include more advanced jobs, such as time-based and event-based jobs introduced with the Oracle Scheduler framework.
• CJQ0 typically handles job queues for the older DBMS_JOB framework but also plays a part in the execution of DBMS_SCHEDULER jobs by managing the job queue and distributing jobs to job slaves (Jnnn processes) for execution.

CQJ0 is related to job scheduling, specifically for Oracle's Scheduler functionality, ensuring scheduled tasks like backups, maintenance, and batch processes are executed at the specified times or events.

1. The checkpoint and data recovery: Recording the checkpoint is important for recovery purposes. The checkpoint identifies the first redo log entry that Oracle will need to process in a recovery situation. All changes related to previous redo log entries have been written to the disk.
2. Checkpoint Process (CKPT): When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work. The statistic DBWR checkpoints displayed by the System_Statistics monitor in Enterprise Manager indicates the number of checkpoint requests completed.

1. Bigfile tablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
2. Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
3. Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
4. Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
5. Performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of traditional tablespaces. However, increasing the datafile size might increase time to restore a corrupted file or create a new datafile.

Evolution of Bigfile tablespaces in Oracle 11g to Oracle 19c
Bigfile tablespaces continued to be used in Oracle 11g and cloud-enabled databases such as Oracle 12c, 18c, and 19c with similar objectives and additional enhancements. Key Points of Bigfile Tablespaces in Oracle 11g and beyond:

1. Automatic Storage Management (ASM) and Logical Volume Managers:
   • In Oracle 10g, bigfile tablespaces were indeed intended for use with ASM or other logical volume managers that support dynamically extensible logical volumes, striping, or RAID.
   • This concept continued in Oracle 11g and newer versions like 12c, 18c, and 19c, especially in environments using ASM. ASM simplifies storage management by automating the allocation of storage and provides scalability and resilience.
2. Cloud-enabled Databases (12c, 18c, 19c):
   • Oracle 12c introduced multitenant architecture, and bigfile tablespaces work well in a pluggable database (PDB) context. The flexibility of bigfile tablespaces fits the cloud environments, as storage needs in the cloud often involve dynamically scalable infrastructure.
   • With the use of bigfile tablespaces, managing a single large data file is easier compared to traditional smallfile tablespaces, particularly in cloud-enabled environments where storage elasticity and automatic resizing are crucial.
   • Oracle 18c and 19c (as long-term releases) continued to support bigfile tablespaces, especially in cloud environments where scalability is a key factor.
3. Use Cases for Bigfile Tablespaces:
   • Bigfile tablespaces are useful in very large databases (VLDB) where the goal is to reduce the number of data files and simplify storage management. By supporting a single data file up to 128 TB (depending on the block size), bigfile tablespaces can drastically reduce the administrative overhead in managing numerous data files.
   • They are ideal when used with ASM or similar storage solutions that provide features like automatic striping and mirroring for data protection and performance.
4. Dynamic Storage Expansion:
   • One of the key benefits of bigfile tablespaces in cloud environments (and on-premises with ASM) is the ability to dynamically expand storage without the need to add more data files, making it suitable for cloud databases where scaling storage is often required without manual intervention.

Summary Bigfile tablespaces were indeed designed with ASM and other volume managers in mind, starting from Oracle 10g, and this feature continued to evolve and be supported in Oracle 11g, 12c, 18c, and 19c. In cloud environments, bigfile tablespaces, combined with ASM or other storage solutions, provide the flexibility and scalability needed for modern dynamic storage management, especially for large-scale databases.