Shulou(Shulou.com)06/01 Report--

If LGWR's next log is ACTIVE, then LWGR will suspend, warning log will report "Checkpoint not complete", and oracle will initiate alter system checkpoint operation

Logs with a status of ACTIVE may already be archived, and the ARCn process will automatically archive online logs that are not CURRENT.

V$LOG.STATUS reflects the progress of a complete checkpoint, because after alter system switch logfile, you will still find that the log that used to be ACTIVE is still in ACTIVE status, but after alter system checkpoint, you will definitely turn ACTIVE into INACTIVE (full checkpoints are written to control file and data file headers, incremental checkpoints are written only to control files)

V$LOG.STATUS=ACTIVE

Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

Indicates that the most recent complete checkpoint SCN is less than the SCN of the last redo record in the log, indicating that the complete checkpoint has not crossed the online log.

V$LOG.STATUS=INACTIVE

Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.

Indicates that the most recent complete checkpoint SCN is greater than the SCN of the last redo record in the log, indicating that the complete checkpoint has passed this online log

Archived_log.NEXT_CHANGE#, log.Firstworthy change: equal to the v$log.NEXT_CHANGE# of the previous online redo or the $archived_log.NEXT_CHANGE#, of the previous archive redo log equal to the v$archived_log.FIRST_CHANGE# of the next archive redo log

V$log.NEXT_CHANGE#:Highest change number (SCN) in the log. When STATUS=CURRENT, NEXT_CHANGE# is set to the highest possible SCN, 281474976710655

V$DATABASE displays information about the database from the control file.

V$DATABASE.CHECKPOINT_CHANGE#:Last SCN checkpointed

V$DATABASE.CONTROLFILE_CHANGE#:Last SCN in backup control file; null if the control file is not a backup

V$DATABASE.CURRENT_SCN:Current SCN; null if the database is not currently open. For a standby database, it is the checkpoint SCN of the mounted physical standby database during media recovery and is always less than the last applied SCN tracked in V$RECOVERY_PROGRESS.

V$DATAFILE displays datafile information from the control file.

V$DATAFILE.CHECKPOINT_CHANGE#:SCN at last checkpoint

V$DATAFILE_HEADER displays datafile information from the datafile headers.

V$DATAFILE_HEADER.CHECKPOINT_CHANGE#:Datafile checkpoint change#

The control file SCN saved in the control file refers to v$database.CONTROLFILE_CHANGE#

The database SCN (also known as system checkpoint SCN) saved in the control file refers to v$database.CHECKPOINT_CHANGE#

The data file SCN saved in the control file refers to v$datafile.CHECKPOINT_CHANGE#

The end of the data file saved in the control file SCN is the same as the database checkpoint scn under the null,mount in the open state

The data file header SCN saved in the data file refers to the vested datafile header.CHECKPOINT changing header, which is called start SCN, also called startup SCN.

SCN saved in redo logfile: each log file has a first scn and a next scn. In the archive log, the first SCN of the next log file is equal to the next SCN of the previous log file, in the online log, first SCN is equal to the next SCN of the last archive log, and the next SCN is infinite in both open and mount states.

There will be inconsistencies in the data file SCN because the status of the data file will appear online and offline or begin backup

V$datafile.checkpoint_change# is to record the SCN value of a data file such as An in a control file.

V$datafile_header.checkpoint_change# is a data file such as A to record its own SCN value

By default, both should be the same. About the comparison of two checkpoint_change# values:

If datafile is greater than datafile_header, then recover datafile is required, indicating that the SCN recorded in the control file is newer than that recorded in the data file. Cause: shutdown abort and so on.

If datafile is less than datafile_header, under no circumstances can it happen, and the scn in the control file cannot be compared to the data file.

When you execute alter system switch logfile, only V$DATABASE.CONTROLFILE_CHANGE# gets bigger, and other V$DATABASE.CHECKPOINT_CHANGE#, V$DATAFILE.CHECKPOINT_CHANGE#, and V$DATAFILE_HEADER.CHECKPOINT_CHANGE# remain the same.

When executing ALTER DATABASE BEGIN BACKUP, V$DATABASE.CONTROLFILE_CHANGE#, V$DATAFILE.CHECKPOINT_CHANGE#, and V$DATAFILE_HEADER.CHECKPOINT_CHANGE# all get bigger, but only V$DATABASE.CHECKPOINT_CHANGE# remains the same.

When executing alter tablespace users begin backup, V$DATABASE.CONTROLFILE_CHANGE#, the corresponding tablespace file V$DATAFILE.CHECKPOINT_CHANGE#, and the corresponding tablespace file V$DATAFILE_HEADER.CHECKPOINT_CHANGE# become larger, $DATABASE.CHECKPOINT_CHANGE# remains the same, and the V$DATAFILE.CHECKPOINT_CHANGE# and $DATAFILE_HEADER.CHECKPOINT_CHANGE# of other tablespace files remain the same.

When alter system checkpoint is executed, V$DATABASE.CONTROLFILE_CHANGE#, V$DATABASE.CHECKPOINT_CHANGE#, V$DATAFILE.CHECKPOINT_CHANGE#, and V$DATAFILE_HEADER.CHECKPOINT_CHANGE# all become larger.

Query some statements of the corresponding SCN

Select CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE#,CURRENT_SCN from v$database

Select distinct CHECKPOINT_CHANGE#,last_change# from v$datafile

Select distinct CHECKPOINT_CHANGE# from v$datafile_header

Select distinct FIRST_CHANGE#,NEXT_CHANGE#,sequence#,first_time from v$archived_log order by first_time desc

Select SEQUENCE#,STATUS,ARCHIVED,FIRST_CHANGE#,NEXT_CHANGE# from v$log

Scn will not change in mount state.

Query the SQL of the current SCN

The general use is as follows

Select to_char (dbms_flashback.get_system_change_number) scn from dual

Execution of the above statement in mount status will result in an error ORA-00904

The following query statement is incremented each time the scn is executed.

Select CURRENT_SCN from v$database

The result of executing the above statement in mount is 0

COMMIT Statement

The COMMIT statement ends the current transaction, making its changes permanent and visible to other users.

COMMIT does not trigger any checkpoint, but just triggers lgwr to write log buffer data to the online redo log and record the latest scn and commit status of the corresponding data block in the control file, but not in any view. Although V$DATABASE.CURRENT_SCN also comes from the control file, it records the latest SCN.

Database checkpoint

The thread checkpoint that has the lowest SCN. All changes in all enabled redo threads with SCNs before the database checkpoint SCN are guaranteed to have been written to disk.

The thread checkpoint with the lowest SCN. All changes to all enabled redo threads with SCN prior to the database checkpoint SCN are guaranteed to be written to disk.

Data file checkpoint

A data structure that defines an SCN in the redo thread of a database for a particular data file. Every data file has a checkpoint SCN, which you can view in Venture Datafile. CHECKPOINTINTCHANGELES. All changes with an SCN lower than this SCN are guaranteed to be in the data file.

A data structure that defines the SCN in the database redo thread of a specific data file. Each data file has a checkpoint SCN, which you can view in V$DATAFILE.CHECKPOINT_CHANGE#. Any changes whose SCN is lower than this SCN will be guaranteed in the data file.

Overview of Checkpoints

A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally. The term checkpoint has the following related meanings:

A data structure that indicates the checkpoint position, which is the SCN in the redo stream where instance recovery must begin

The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.

The writing of modified database buffers in the database buffer cache to disk

Checkpoints are the key mechanisms for consistent database shutdown, instance recovery, and Oracle database operations. The term checkpoint has the following related meanings:

The data structure that indicates the location of the checkpoint, which is the SCN in the redo stream where instance recovery must begin

The checkpoint location is determined by the oldest dirty buffer in the database buffer cache. The checkpoint location serves as a pointer to the redo stream and is stored in the control file and in each data file header.

Write the modified database buffer in the database buffer to disk

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:

Thread checkpoints

The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:

Consistent database shutdown

ALTER SYSTEM CHECKPOINT statement

Online redo log switch

ALTER DATABASE BEGIN BACKUP statement

Tablespace and data file checkpoints

The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

The checkpoint process (CKPT) is responsible for writing the checkpoint to the data file header file and the control file. Checkpoints occur in a variety of situations. For example, the Oracle database uses the following types of checkpoints:

Thread checkpoint

The database is written to disk through all buffers that are redone in a specific thread before a target. A set of thread checkpoints on all instances in the database is a database checkpoint. Thread checkpoints occur in the following situations:

Consistent database shutdown

ALTER SYSTEM CHECKPOINT statement

Online redo log switch

ALTER DATABASE BEGIN BACKUP statement

Tablespace and data file checkpoint

The database is modified by remaking before writing all buffers on disk to a specific destination. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making tablespaces read-only or offline normally, shrinking data files, or performing ALTER TABLESPACE BEGIN BACKUP.

Oracle rules: ensure that redo records are written to the persistence layer before the corresponding dirty data blocks

Therefore, if the redo caused by the same change is recorded as R and the dirty data block is D, then lgwr does not allow dbwr to write D to the data file first if R is not written to the online log. Even if dbwr makes a request first, you must wait for lgwr to empty the log buffer first. In this way, the content of the data file is never updated faster than that of the online log, that is, when the database is open, the data file is always "old" than the online log. In order to identify the extent to which the data file is "old", oracle introduces checkpoints.

Oracle reduces recovery time through checkpoints (Checkpoint). A checkpoint is just a database event, and the fundamental significance of its existence is to reduce recovery time.

Full checkpoint step

1. Identify the current (and up-to-date) redo record in the log buffer and extract its RBA and SCN as the target of the checkpoint

2.lgwr clears the log buffer and writes redo records to the online log

The 3.dbwr process writes dirty data blocks generated by checkpoint targets (RBA and SCN) and before checkpoint targets into the data file in the order of RBA

The 4.ckpt process writes checkpoint targets (RBA and SCN) to the header and control file of the data file

In this way, the checkpoint targets (RBA and SCN) in the data file header can provide the following information

1. Comparing the checkpoint scn that reads the header of the data file with the scn of the online log redo record, you can know whether the data file needs to be restored.

two。 If the data file needs to be recovered, the data file SCN is used to indicate that the recovery starts from this SCN.

Timing of complete checkpoint

1.shutdown, shutdown normal, shutdown transactional, shutdown immediate commands

2.alter system checkpoint

3.ALTER DATABASE BEGIN BACKUP

4. Execute some tablespace maintenance commands such as alter tablespace tablespacename offline | online | begein backup | end backup | read only | read write

-- only V$DATABASE.CONTROLFILE_CHANGE# gets bigger when Online redo log switch

-- $DATABASE.CHECKPOINT_CHANGE# remains the same at ALTER DATABASE BEGIN BACKUP, although V$DATABASE.CONTROLFILE_CHANGE#, V$DATAFILE.CHECKPOINT_CHANGE#, and V$DATAFILE_HEADER.CHECKPOINT_CHANGE# are all bigger.

-- when alter tablespace users begin backup, V$DATABASE.CONTROLFILE_CHANGE#, corresponding tablespace file V$DATAFILE.CHECKPOINT_CHANGE#, corresponding tablespace file V$DATAFILE_HEADER.CHECKPOINT_CHANGE# become larger, $DATABASE.CHECKPOINT_CHANGE# and? The corresponding data files CHECKPOINT_CHANGE# for other tablespaces have not changed.

Full checkpoint: individuals prefer to understand that a full checkpoint occurs only when V$DATABASE.CHECKPOINT_CHANGE# is added. Although a complete checkpoint is defined as a complete checkpoint as long as both the data file header and the control file are written at the same time. However, according to personal understanding, in fact, the above BEGIN BACKUP operation is not a complete checkpoint.

Incremental checkpoints

An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

An incremental checkpoint is a type of thread checkpoint, partly to avoid writing a large number of blocks in the online redo log switch. DBWn checks at least every three seconds to determine if there is any work to do. When DBWn writes to a dirty buffer, it checks the point location ahead of time, causing CKPT to write the checkpoint location to the control file instead of the data file header.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when mode objects are deleted or truncated.

Incremental checkpoint

The incremental checkpoint pushes dbwr to write some dirty data blocks back to the data file, but the checkpoint SCN is only recorded to the control file, that is, VroomDATABASE.CONTROLFILECHANGEI, not the header of the data file. The instance restore operation does not result in fewer requests for log records. For example, if the incremental checkpoint SCN reaches 200, it means that the highest SCN of the data block in the data file is 200, but the header of the data file may also say that the checkpoint SCN is 100. if the instance crashes at this time, the roll forward of the instance recovery will start with the redo record with scn 100. in fact, the redo record from scn number 100,200does not need to be rolled forward at all. Oracle certainly knows this, so every time you write a dirty block, dbwr adds a redo record called BWR (block written record, the block has been written). The change vector of this record does not represent any changes, but is used to mark which blocks have been written back to the data file.

Because of records like BWR, oracle actually uses "two reads" to read the online log when automatically rolling forward, still assuming that the data file checkpoint scn is 100. the incremental checkpoint has reached 200. all the redo records after the scn number 100 in the log are read for the first time in order to determine which data blocks really need to be recovered. BWR records are like a filter that can greatly reduce such data blocks. The second time oracle reads the online log, oracle is only interested in the blocks that really need to be recovered, that is, the redo records of blocks that have not been recorded by BWR. As a result, oracle skips scn redo records between 100and 200, which makes the roll forward faster.

Incremental checkpoint effect:

1. Reducing the occurrence of full checkpoints is a burden on the dbwr process

two。 Improve the speed of instance recovery

Timing of incremental checkpoints

1.oracle automatic control (when none of the three parameters are set or when none of the three parameters are set at the time FAST_START_MTTR_TARGET, LOG_CHECKPOINT_TIMEOUT, LOG_CHECKPOINT_INTERVAL)

two。 The most severe of the three parameters (FAST_START_MTTR_TARGET, LOG_CHECKPOINT_TIMEOUT, LOG_CHECKPOINT_INTERVAL)

3.lgwr toggles online logs

-- alter system flush buffer_cache does not trigger additional checkpoints

Because the lgwr switches online logs to generate incremental checkpoints, it means that the full checkpoint (data file header scn) has not passed active's online logs, but the incremental checkpoints (control file scn) have passed active's online logs and may have been in current's online logs, because recover uses two reads, reading the full checkpoint first and then going to the incremental checkpoint. All recover still need active's online log and current's online log, that is to say, if there is still active's online log when current's online log is lost, you can't only restore to active's online log, but also need current's log, because the incremental checkpoint may already be in current's log.

Three parameters of incremental checkpoint

The default value of FAST_START_MTTR_TARGET is 0, and the default is 0, and the default is 1800.

FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.

When LOG_CHECKPOINT_INTERVAL is set, the setting of LOG_CHECKPOINT_INTERVAL overrides the setting of FAST_START_MTTR_TARGET, not that 11G has disabled the function of the parameter FAST_START_MTTR_TARGET.

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. This number refers to physical operating system blocks, not database blocks

Blocks here refers to OS's block, not DATABASE's block.

Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size,checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure

When the LOG_CHECKPOINT_INTERVAL value is greater than redo log file size, the incremental checkpoint occurs when online log switching replaces LOG_CHECKPOINT_INTERVAL

Specifying a value of 0 (zero) for LOG_CHECKPOINT_INTERVAL has the same effect as setting the parameter to infinity and causes the parameter to be ignored. Only nonzero values of this parameter are considered meaningful.

When LOG_CHECKPOINT_INTERVAL is 0, the parameter LOG_CHECKPOINT_INTERVAL has no effect.

Recovery I/O can also be limited by setting the LOG_CHECKPOINT_TIMEOUT parameter or by the size specified for the smallest redo log.

Both LOG_CHECKPOINT_TIMEOUT and LOG_CHECKPOINT_INTERVAL work, but take the harsher one.

LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.

Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set

Setting LOG_CHECKPOINT_TIMEOUT to 0 is not recommended unless you set FAST_START_MTTR_TARGET

When FAST_START_MTTR_TARGET and LOG_CHECKPOINT_INTERVAL are 0, LOG_CHECKPOINT_TIMEOUT also takes effect.

In case 2 of instance recovery:

1. Compare the incremental checkpoint SCN with the SCN in the online log. If the first_change#, description larger than the online log is connected to the online log, you can restore the instance.

Because an incremental checkpoint occurs in logfile switch, the archived log always lags behind the incremental checkpoint of the instance, and the log with a status of ACTIVE may already be an archived log, so instance recovery is always an online log using current

2. In the case of mount, the instance must be restored if $datafile.last_change# is empty. There are two cases in which $datafile.last_change# is empty under mount.

2.1 、 shutdown abort

2.2. Control is restored.

The SCN number of the v$datafile.last_change# in the database OPEN state is always NULL, but when the database is shut down normally, a full checkpoint is performed and the checkpoint SCN number is updated to this field

Therefore, whether the instance recovery is needed can be determined by whether the SCN number of the v$datafile.last_change# is null in the mount state. If the Oracle does not have time to update the field when it crashes, the field is still NULL. When the SMON process finds that the field is empty, it knows that the instance did not shut down properly last time, so the SMON process starts the instance recovery. When the SMON process performs instance recovery, it obtains the checkpoint location from the control file. Therefore, the SMON process goes to the online log file, finds the checkpoint location, and then applies all the redo entries from that checkpoint location, thus restoring the state of the instance crash point in buffer cache. This process is called roll forward. After the roll forward is completed, there are not only dirty data blocks that have not been written to the data file at the time of crash, but also transactions that have been abruptly terminated, resulting in dirty data blocks that are neither committed nor rolled back. There are two kinds of dirty data blocks, one is still in buffer cache, the other is that it has been written to the disk file by dbwr. As soon as the roll forward is complete, the SMON process immediately opens the database. However, the database also contains dirty blocks that are neither committed nor rolled back, which cannot exist in the database because they are not committed and must be rolled back. After opening the database, the SMON process rolls back in the background.

Media recovery:

The scn of checkpoints in the header of each data file is inconsistent, and the SCN of adding checkpoints is much smaller than the first_change#, description of online logs and the online logs cannot be connected. Media recovery is required, and archived logs need to be used for recover (more serious cases may also require restore data files and use archived log recover)

RAMN often encounters the problem that the No. 1 file system is too new.

The following three all say that file 1 is too new, and file 1 needs more recovery.

ORA-01194: file 1 needs more recovery to be consistent

ORA-01113: file 1 needs media recovery

RMAN-06556: datafile 1 must be restored from backup older than scn 919248820

The scn of datafile 1 is greater than 919248820, that is, datafile 1 is too new, not old enough

For example, after a normal shutdown, startup mount status

Sql direct recover database will report an error ORA-00264: no recovery required

Rman directly recover database until sequence to the first few archivelog will report to RMAN-06556.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.