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

This article is to share with you about how to analyze Oracle SCN, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The meaning of SCN (system change number) in Oracle is the same as our Beijing time. SCN is the time signal in Oracle.

Why doesn't Oracle take time to define it?

I execute a DML statement at 8:00 Beijing time, then change the time on the machine to 7:00, and then execute a DML statement. If you use the time on the machine, Oracle can't tell the order in which the two DML statements are executed-- which is important for Oracle. So it uses its own generated SCN to distinguish the order of all operations.

SCN is designed to have a large value, so you don't have to worry about what to do when you reach the maximum.

You can view the current SCN number of the system:

SQL > select dbms_flashback.get_system_change_number from dual

GET_SYSTEM_CHANGE_NUMBER

-

464640326

You can also view the SCN number currently saved by the system:

SQL > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

-

464639929

If the database is corrupted at this time, it is the data between the two SCN that needs to be repaired when rebooting. This data is saved in the online redo log file:

SQL > select GROUP#, STATUS, FIRST_CHANGE# from v$log

GROUP# STATUS FIRST_CHANGE#

1 INACTIVE 464633029

2 INACTIVE 464637664

3 CURRENT 464638303

The data stored in group1 produces SCN numbers from 464633029 to 464637664 SCN in group2 from 464637664 to 4638303 and SCN from 464638303 to 4640326 (current SCN number) in group3.

Therefore, if you execute shutdown abort and restart at this time, the online redo log file used when executing crash recovery is member in group3. You can also see through the v$log.status field that the status of group3 is current.

Part I. Dialysis SCN number

SCN is a number that is automatically maintained by DBMS to accumulate increments when the Oracle database is updated. When a transaction commit, LGWR writes the log buffer to the redo log file and also writes the SCN synchronization of the transaction to the redo log file (wait-until-completed). Therefore, when you commit transaction, before the transaction success message is returned, the LGWR must complete the above actions, otherwise you will not see the successful response message.

You can query the latest SCN of the current system.

SQL > select dbms_flashback.get_system_change_number from dual

Understandably, the SCN returned here is also the latest SCN record of redo log file. Because the transaction after commit will have SCN, and once commit will be written into redo log file immediately.

The relationship between CHECKPOINT and SCN

The purpose of Checkpoint is to write the submitted transactions stored in buffer back to disk, otherwise, once crash occurs and recovery is needed, it will take a lot of time to start recovery from the last SCN transaction in redo log file, which is a waste of time and inefficient in commercial applications.

When commit makes a transaction, it will only write redo buffer into redo log file immediately, but the block (dirty block) after update will not be written back to disk datafile synchronously. This is to reduce too much disk IO, so it is written in batch mode.

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.

Under shutdown normal or shutdown immediate, also known as clean shutdown, checkpoint also triggers automatically. When checkpoint occurs, the SCN is written in four places. Three places are in control file and one is in datafile header.

The three places of Control file are:

1 、 System checkpoint SCN

SQL > select to_char (checkpoint_change#, 'XXXXXXXXXXXX') from v$database

TO_CHAR (CHECKPOINT_CHANGE#,'XX

-

7161D7365DC

2 、 Datafile checkpoint SCN

SQL > select name, to_char (checkpoint_change#,'XXXXXXXXXXXX') from v$datafile where name like'% gisdts01%'

NAME

-

TO_CHAR (CHECKPOINT_CHANGE#,'XX

-

/ gisdata/datafile/gisdts01.dbf

7161D7365DC

3 、 Stop SCN

SQL > select name,last_change# from v$datafile where name like'% gisdts01%'

NAME

-

/ gisdata/datafile/gisdts01.dbf

Normal datafile under the operation of read-write mode, last_change# must be null

There is also a SCN in the datafile header.

4 、 Start SCN

SQL > select name,to_char (checkpoint_change#,'XXXXXXXXXXXX') from v$datafile_header where name like'% gisdts01%'

NAME

TO_CHAR (CHECKPOINT_CHANGE#,'XX

/ gisdata/datafile/gisdts01.dbf

7161D7365DC

Why is it stored in control file divided into two places (system checkpoint scn, datafile checkpoint scn?). When a tbs is set to read-only, its scn will freeze and stop, and the datafile checkpoint scn will no longer change incrementally, but the overall system checkpoint scn will continue to increase. So that's why you need to store SCN in two separate places.

What happens to SCN after a normal shutdown database?

You can open the database in mount mode.

SQL > select to_char (checkpoint_change#,'XXXXXXXXXXXX') from v$database

TO_CHAR (CHECKPOINT_CHANGE#,'XX

7161D7455B9

SQL > select name,to_char (checkpoint_change#,'XXXXXXXXXXXX'), to_char (last_change#)

, 'XXXXXXXXXXXX') from v$datafile where name like'% gisdts01%'

NAME

TO_CHAR (CHECKPOINT_CHANGE#,'XX

TO_CHAR (LAST_CHANGE#,'XXXXXXXX

/ gisdata/datafile/gisdts01.dbf

7161D7455B9

7161D7455B9

You can see that the values of the three SCN stored in control file are the same. Note that the stop scn at this time will not be null, but equal to start scn.

Then query the SCN in datafile header:

SQL > select name, to_char (checkpoint_change#,'XXXXXXXXXXXX') from v$datafile_hea

Der where name like'% gisdts01%'

NAME

-

TO_CHAR (CHECKPOINT_CHANGE#,'XX

-

/ gisdata/datafile/gisdts01.dbf

7161D7455B9

When clean shutdown, the checkpoint will proceed, and the stop scn and start scn of the datafile will be the same. When we open the database, oracle will check whether the start scn in datafile header is the same as the scn in datafile stored in control file, if so, then check whether start scn and stop scn are the same. If it is still the same, the database will start normally, otherwise recovery will be required. . After the database open, the stop scn stored in control file returns to a null value, which means that datafile is open in normal mode.

If the shutdown is not normal (shutdown abort), then after the mount database, you will find that stop scn is not equal to scn in other locations, but is equal to null. This means that oracle does not checkpoint when it is in shutdown, and crash recovery must be done the next time it starts.

Analysis of Part II. Oracle SCN mechanism

As an important mechanism in oracle, SCN (System Chang Number) plays an important role in data recovery, Data Guard, Streams replication, synchronization between RAC nodes and so on. Understanding how SCN works can help you gain a deeper understanding of the above functions.

Before we understand SCN, let's take a look at how data changes in oracle transactions are written to the data file:

1. Start the transaction

2. Find the required data block in buffer cache, and if not, load it into buffer cache from the data file

3. The transaction modifies the data block of buffer cache, which is identified as "dirty data" and written to log buffer.

4. The transaction commits, and the LGWR process writes the "dirty data" in log buffer to redo log file.

5. When the checkpoint,CKPT process updates the information in the header of all data files, the DBWn process is responsible for writing the dirty data in Buffer Cache to the data file.

After the above five steps, the data changes in the transaction are finally written to the data file. However, once the database goes down unexpectedly in the above intermediate link, how do you know which data has been written to the data file and which has not been written when restarting (similarly, there is a similar question in DG and streams: what is the data that has been replicated in the last synchronization and which is not)? The SCN mechanism can solve the above problems perfectly.

SCN is a number, to be exact, a number that will only increase, not decrease. It is this only added feature that ensures that Oracle knows what should be restored and which should be replicated.

There are a total of 4 SCN: system checkpoint (System Checkpoint) SCN, data file checkpoint (Datafile Checkpoint) SCN, end SCN (Stop SCN), start SCN (Start SCN). The SCN in face 3 exists in the control file, and the last one exists in the header of the data file.

In the control file, System Checkpoint SCN is global for the whole database, so there is one, while Datafile Checkpoint SCN and Stop SCN are for each data file, so a data file corresponds to a Datafile Checkpoint SCN and Stop SCN in the control file. During the normal operation of the database, Stop SCN (which can be queried through the field last_change# of the view v$datafile) is an infinite number or NULL.

After a transaction commits (step 4 above), there is a redo record in redo log, and the system provides it with an up-to-date SCN (you can know the current latest SCN through the function dbms_flashback.get_system_change_number), which is recorded in that record. If the record is emptied when the redo log is empty (when the log is full for switching or when checkpoint occurs, all change logs have been written to the data file), then its SCN is recorded as the low SCN of redo log. Later, in the redo record where the log is written again and unprecedented, SCN becomes Next SCN.

When the log is switched or checkpoint occurs (step 5 above), all redo records from Low SCN to Next SCN are written to the data file by the DBWn process, while the CKPT process updates the Start SCN recorded on the file header of all data files (regardless of whether the data in the redo log affects the data file or not) to Next SCN (which can be queried through the field checkpoint_change# of the view v$datafile_header) At the same time, the System Checkpoint SCN in the control file (which can be queried through the field checkpoint_change# of the view v$database) and the corresponding Datafile Checkpoint of each data file (which can be queried through the field checkpoint_change# of the view v$datafile) are also updated to Next SCN. However, if the tablespace of the data file is set to read-only, neither the Start SCN of the data file nor the Datafile Checkpoint SCN in the control file will be updated.

So how does the system produce the latest SCN? In fact, this number was converted by the timestamp at that time. Whenever a new SCN-to-redo record needs to be generated, the system takes the current timestamp and converts it to a number as a SCN. We can convert it back to timestamp through the function SCN_TO_TIMESTAMP (after 10g):

SQL > select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP (dbms_flashback

.get _ system_change_number) from dual

GET_SYSTEM_CHANGE_NUMBER

-

SCN_TO_TIMESTAMP (DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)

2877076756

17-AUG-07 02.15.26.000000000 PM

You can also use the function timestamp_to_scn to convert a timestamp to SCN:

SQL > select timestamp_to_scn (SYSTIMESTAMP) as scn from dual

SCN

-

2877078439

Finally, in addition to reflecting changes in transaction data and keeping it synchronized, SCN also acts as a "heartbeat" of the system-the system SCN is refreshed every 3 seconds or so.

Next, let's take a brief look at how SCN works in database recovery.

When the database shuts down normally (shutdown immediate/normal), it will do a checkpoint first, write the data in the log file to the data file, and update the control file, the SCN in the data file (including the Stop SCN in the control file) to the latest SCN.

Database exception / unexpected shutdown will not update or only update part of the Stop SCN.

When the database starts, Oracle first checks whether each Datafile Checkpoint SCN in the control file is the same as the Start SCN in the data file, and then checks that each Datafile Checkpoint SCN and Stop SCN are the same. If a difference is found, the missing SCN is found in the Redo Log and rewritten to the data file for recovery. The specific data recovery process will not be discussed here.

As an important mechanism in Oracle, SCN plays a "controller" role in many important functions. Understand the generation and implementation of SCN, and help DBA understand and deal with the problems of recovery, DG, and Streams replication.

Finally, using SCN mechanism, some practical functions are added in Oracle10g and 11g-database flashback, database load reappearance and so on.

Part III. Scn (system change number) information and recovery

There are three kinds of SCN in control: system SCN, datafile SCN, last SCN, and there is a SCN start SCN in the header of the data file.

The system scn is obtained from the view v$database, and the corresponding checkpoint_change# field, datafile scn and last scn correspond to the checkpoint_change#,last_change#, in the view v$datafile, while the start scn is obtained from the v$datafile_header.

After the database is started normally, the system scn,datafile scn,start scn will be equal, and the last scn will be placed at infinity, in this case null.

After normal shutdown (immediate,noraml,translate), the above four scn will be equal when full checkpoint is executed.

When the system shuts down abnormally, such as shutdown abort, the last scn is still infinite, then when the instance is restarted, the system will first compare start scn and system scn. If it is consistent, then compare whether start scn and last scan are the same size. Because it is an abnormal shutdown, it will be different here, so routine recovery is needed.

If system scn > datafile scn is found when opening the database, it means that the old backup data files are used, that is, media recovery is required.

If it is system scn select checkpoint_change# from vault database;-- control the scn in the file

CHECKPOINT_CHANGE#

-

5534071

SQL > select file#,checkpoint_change# from vested datafile headers;-- start scn

FILE# CHECKPOINT_CHANGE#

--

1 5534071

2 5534071

3 5534071

4 5534071

5 5534071

SQL > select file#,checkpoint_change#,last_change# from vault datafile;-- datafile scn & last scn

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#

1 5534071

2 5534071

3 5534071

4 5534071

5 5534071

2. After the normal shutdown, and then in startup mount

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 319888364 bytes

Fixed Size 453612 bytes

Variable Size 192937984 bytes

Database Buffers 125829120 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 5534485

2 5534485

3 5534485

4 5534485

5 5534485

SQL > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

-

5534485

SQL > select file#,checkpoint_change#,last_change# from v$datafile

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#

1 5534485 5534485

2 5534485 5534485

3 5534485 5534485

4 5534485 5534485

5 5534485 5534485

-- start scn=last scn is found, which proves that the system is shut down normally

SQL > alter database open

The database has changed.

3. Perform transaction operations in the normal open state

SQL > create table t (a number)

The table has been created.

SQL > insert into t values (1)

1 line has been created.

SQL > commit

The submission is complete.

SQL > insert into t values (2)

1 line has been created.

4. Abnormal shutdown

SQL > shutdown abort

The ORACLE routine has been closed.

SQL >

5. Open it to mount and watch scn.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 319888364 bytes

Fixed Size 453612 bytes

Variable Size 192937984 bytes

Database Buffers 125829120 bytes

Redo Buffers 667648 bytes

The database is loaded.

SQL > select file#,checkpoint_change#,last_change# from v$datafile

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#

1 5534486

2 5534486

3 5534486

4 5534486

5 5534486

SQL > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

-

5534486

SQL > select file#,checkpoint_change# from v$datafile_header

FILE# CHECKPOINT_CHANGE#

--

1 5534486

2 5534486

3 5534486

4 5534486

5 5534486

-- at this time, it is found that start scn is different from last scn. Last scn is infinite and routine recovery is needed.

6. Change the database status to open, and view the running log at this stage.

SQL > select * from wen.t

Select * from wen.t

*

ERROR is on line 1:

ORA-01219: database not open: only allowed to query in fixed tables / views

SQL > alter database open

The database has changed.

SQL > select * from wen.t

A

-

one

Found that transactions that were not committed were lost.

View the log as follows:

Completed: ALTER DATABASE MOUNT

Wed May 17 21:35:46 2006

Alter database open

Wed May 17 21:35:46 2006

Beginning crash recovery of 1 threads-automatically determines whether recovery is needed, and routine recovery begins here

Wed May 17 21:35:46 2006

Started first pass scan

Wed May 17 21:35:47 2006

Completed first pass scan

206 redo blocks read, 90 data blocks need recovery

Wed May 17 21:35:47 2006

Started recovery at

Thread 1: logseq 167, block 271, scn 0.0

Recovery of Online Redo Log: Thread 1 Group 2 Seq 167 Reading mem 0-online redo log for recovery

Mem# 0 errs 0: D:ORACLEORADATADB1REDO02.LOG

Wed May 17 21:35:47 2006

Ended recovery at

Thread 1: logseq 167, block 477, scn 0.5554724

90 data blocks read, 90 data blocks written, 206 redo blocks read

Crash recovery completed successfully-recovery complete

Wed May 17 21:35:47 2006

LGWR: Primary database is in CLUSTER CONSISTENT mode

Thread 1 advanced to log sequence 168

Thread 1 opened at log sequence 168

Current log# 3 seq# 168 mem# 0: D:ORACLEORADATADB1REDO03.LOG

Successful open of redo thread 1.

Wed May 17 21:35:48 2006

SMON: enabling cache recovery

Wed May 17 21:35:48 2006

ARC0: Evaluating archive log 2 thread 1 sequence 167

ARC0: Beginning to archive log 2 thread 1 sequence 167

Creating archive destination LOG_ARCHIVE_DEST_1: 'DazzDBBKARC00167.001'

ARC0: Completed archiving log 2 thread 1 sequence 167

Wed May 17 21:35:48 2006

Undo Segment 1 Onlined

Undo Segment 2 Onlined

Undo Segment 3 Onlined

Undo Segment 4 Onlined

Undo Segment 5 Onlined

Undo Segment 6 Onlined

Part IV. Understanding of SCN

When the 1.oracle is running normally, the SCN of the control file is a large number, which is different from the SCN of the redo log file and the data file. When the checkpoint is normally closed, the SCN values of the three are the same.

Biti: the scn in the log file has two start and end (high and low), and in current log, the high scn is also infinite.

two。 When a transaction commit is successful, the SCN+1 in the redo log file, and when the changes made by the transaction are written to the data file, the SCN+1 of the data file

Add 1 for Biti:commit, and 1 for many other times, as long as the database changes. When the data is written to the data file, the scn is not added by 1 but is updated by the ckpt. When the checkpoint occurs, the checkpoint count of the data file header is modified and the scn is updated.

3. Question:

Is it true that if a transaction is relatively large and the writing of redo log entries and data buffer occurs before the transaction is committed, and the power is cut off, the SCN of the data file and redo log file is not + 1 and is the same, but the control file SCN is different, and the rollback occurs during the database startup?

Biti: the data file is updated by the ckpt process. Instead of adding 1, the scn is updated to the scn at the time the checkpoint occurred. The rollback is based on the transaction table status of the rollback segment header.

4. The data is written to the data file scn instead of adding 1, but the ckpt is updated. When the checkpoint occurs, the checkpoint count of the data file header is modified and the scn is updated.

Is that what you should say?

Data writing occurs when the ckpt is updated, while the checkpoint count of the data file header is modified and the scn is updated. When data is written in other cases (such as no free buffer, etc.), ckpt does not occur, but SCN increases.

Biti: the data block but not the data file header is modified at this time. The data file header is updated only when the checkpoint occurs, that is, only the ckpt process updates the data file header (oracle8 used to be lgwr update if there is no ckpt process), and dbwr only writes the data block.

BTW: it seems that DBWR is just some data blocks, and only the CKPT process can update the data file header.

Add one to 5.commit, and one to many other times, as long as there is a change in the database.

Very often, can you give some examples?

Biti: dml will increase scn even if it is not submitted. The job process will also generate scn. Any changes to the files in the database may generate scn,SCN: system change number, not system commit number. That is, a point in time when the system changes.

Sign. It's not a sign of submission, just because submission is also one of the changes in the system.

6.Biti: the occurrence of checkpoints has nothing to do with writing log files

The checkpoint tells DBWR to write the data file, and then ckpt updates the control file header and the data file header.

When DBWR writes a data block, if it is found that the log block of the relevant RDBA (located in the log file) of the data block has not been written to the log file, then before the dbwr writes the block, it must notify lgwr to write the log in log buffer to the log file.

The SCN in 7.data block is the SCN when block is changed.

While data files have so many block, naturally different block have different SCN.

There are commit SCN in block SCN and ITL in block

Block SCN also exists in both the head and tail of the block. Inconsistency means that the block is damaged (this may occur in the hot backup, which needs to be copied back from the redo log. If the process dies in the process of modification, pmon is responsible for cleaning it. If such inconsistencies occur outside of some cases, there will be a 1578 error in the query. Of course, the error number may also be a physical disk damage, which indicates a logical corruption! The timing of checking the header and tail SCN is related to these two parameters:

Db_block_checking boolean FALSE

Db_block_checksum boolean FALSE

For the 2 parameters, please refer to http://tahiti.oracle.com.

Commit SCN in ITL is related to consistent gets and delay block cleanout.

The SCN of the data file header is updated when the checkpoint occurs

It means that when restoring, start looking for redo in log file from this SCN point and start to recover.

8.According to Rama Velpuri's book, CKPT updates controlfiles, not their headers. It makes sense because if you look at a controlfile dump, the header doesn't even have an SCN. But the file body has sections for each datafile, and therefore each of them has an SCN to be updated.

It's odd that most books and also documentation don't even say CKPT updates controlfiles.

Follow-up to bellsz's original message. In controlfiles, the stop SCN is not a very big number; it's in fact set to infinity when the database is open. Also, SCNs are incremented for many reasons, mostly due to recursive transactions. Read Steve Adams and Hemant Chitale's answers at

Http://groups.google.com/groups?sel...t_nospam.com.sg

9.

System checkpoint scn (v$database (checkpoint_change#))

Data file checkpoint (v$datafile (checkpoint_change#))

Data file termination scn (v$datafile (last_change#))

Checkpoints stored in data files

Start scn (v$datafile_header (checkpoint_change#))

1 > system checkpoint scn

When a checkpoint action is completed, Oracle stores the SCN of the system checkpoint in the control file.

Select checkpoint_change# from v$database

2 > data file checkpoint scn

When a checkpoint action is completed, Oracle stores the scn of each data file in the control file separately.

Medium.

Select name,checkpoint_change# from v$datafile

3 > start scn

Oracle stores the scn of this checkpoint in the header of each data file, a value called startup scn

Because it is used to check whether a database recovery needs to be performed when the database instance is started.

Select name,checkpoint_change# from v$datafile_header

4 > terminate scn

The termination scn of each data file is stored in the control file.

Select name,last_change# from v$datafile

In the normal process of database operation, the termination scn of all data files in online read-write mode is null.

5 > scn value during database operation

After the database is open and running, the system checkpoint in the control file and the data file checkpoint scn in the control file

It is the same as the startup scn in each data file header. The termination scn of each data file in the control file is null.

In the process of safely shutting down the database, the system performs a checkpoint action, which terminates the scn of all data files

Will be set to the value that starts scn in the header of the data file. When the database restarts

Oracle compares the startup scn in the header with the database file checkpoint scn

If the two values match each other, oracle then compares the startup scn and the control file in the header of the data file

The termination scn of the data file in the. If the two values are also consistent, it means that most of the data blocks have been submitted, and all

None of the changes to the database were lost in the process of shutting down the database, so the process of starting the database this time

No restore operation is required, and the database can be opened at this point. When all the databases are open

The value of the data file termination scn stored in the control file is changed to null again

This means that the data file is open and ready for use.

10.

I looked for some web pages and found that SCN did not only increase when the transaction was committed. Here is a summary of the web page:

1)

SCN means "System Change Number" not "System Commit Number".

However, because the SCN is always incremented at commits and seldom otherwise, it is OK to use the two terms interchangeably.

2)

The SCN is incremented whenever a transaction commits. However, this is not the only source of increments. In a seemingly idle database, the SCN gets incremented also through AQ, SMON, job queues...

1 said that oracle seldom operations will also lead to the increase of SCN, 2 more specifically said AQ, SMON, job queues... It will lead to the increase of SCN, so it should be concluded that in addition to COMMIT leading to the increase of SCN in ORACLE, there are other ORACLE background processes that lead to the increase of SCN.

But is it the normal DML that leads to the increase of SCN, or is it the illusion of the increase of SCN caused by the background process during the DML operation? Please discuss enthusiastically!

There is also when and under what circumstances the ORACLE background process led to the increase of SCN, please also actively discuss!

Biti: I should be more accurate in this sentence.

If a dml results in a transaction, a scn is generated. This means that

If a transaction contains more than one dml, only the first dml that initially generated the transaction generates the scn and commits with a scn

If a transaction has only one dml, it looks like dml generates a scn, commit or rollback produces a scn

This has been tested by experiment. if you are interested again, you can prove it if you are not looking for information to read.

You can understand it as begin transaction and commit tansaction.

As for commit without dml, it's not called a transaction.

If you issue the rollback command without doing any dml, you will find that user rollbacks will increase and transactions will not increase in v$sysstat.

So you can define the conclusion as the beginning of the transaction and the end of the transaction will lead to the increase of SCN, others such as AQ/JOB will also produce SCN.

The scn will also increase after the continuous occurrence of 255DML in a transaction on the same block

……

11.sys@DBAP01 > select max (ktuxescnw*power (2PM32) + ktuxescnb) from x$ktuxe

MAX (KTUXESCNW*POWER (2) 32) + KTUX

-

52211024

Time spent: 00: 00: 00.00

Sys@DBAP01 > alter system checkpoint

The system has changed.

Time spent: 00: 00: 00.06

Sys@DBAP01 > select CHECKPOINT_CHANGE# from v$database

CHECKPOINT_CHANGE#

-

52211055

Time spent: 00: 00: 00.00

Sys@DBAP01 > select max (ktuxescnw*power (2PM32) + ktuxescnb) from x$ktuxe

MAX (KTUXESCNW*POWER (2) 32) + KTUX

-

52211053

X$ktuxe calculates the commit scn of the latest transaction that has ended, so it can be smaller than the current system scn. The checkpoint scn is naturally smaller than the current system scn. However, the size calculated by the checkpoint scn and x$ktuxe depends on the state of the system.

Current scn is the largest scn currently generated by the system, and may be the scn generated by the current unclosed transaction. You can get this value in the dbms_flashback.get_system_change_number of 9i, which should be greater than or equal to x$ktuxe SCN (this view records the maximum scn of the current database to end the transaction)

The above is how to analyze Oracle SCN, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.