Concept Analysis of Oracle Database SCN

The following articles mainly introduce the concept of Oracle Database SCN. To better understand the concept of Oracle Database SCN, we will first introduce four concepts about SCN. The following is a description of the specific content. I hope it will be helpful for your future study.

1. System checkpoint scn

After a checkpoint 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

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

 
 

  
  
select name,checkpoint_change# from v$datafile； 
 
 

3. Start scn

Oracle stores the scn of this checkpoint in the file header of each data file. This value is called Startup scn because it is used when the database instance is started,

Check whether Oracle database recovery is required.

 
 

  
  
select name,checkpoint_change# from v$datafile_header 
 
 

4. Terminate scn

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

 
 

  
  
select name,last_change# from v$datafile 
 
 

The following conditions must be met:

1) Use Backup Control Files

2) re-create the resetlogs control file. If you re-create the noresetlogs, you do not need to use the using backup controlfile.

2. alter database open resetlog

Specify RESETLOGS to reset the current LOG sequence number to 1 and discard all LOG information.

The following conditions must use resetlog

1) Restore media in Incomplete recovery)

2) use backup control files

When you use resetlogs to open a database, you do not have to back up the Oracle database completely once.

3,

 
 

  
  
create controlfile resetlogs/noresetlogs 
 
 

1). When the control file is rebuilt using Noresetlogs, The datafile Checkpoint in the control file comes from the Current log header in Online logs.

2) When Resetlogs is used to reconstruct the control file, the datafile Checkpoint in the control file comes from each data file header.

When system scn, datafile scn, and start scn are not all equal, the media must be restored. If stopscn is null, the instance must restore resetlogs to discard all the log information not used in the previous restoration, make sure that the service is not reused or restored.

1. The system is shut down normally:

 
 

  
  
system scn=datafile scn=start scn=stop scn 
 
 

1) system scn = datafile scn = start scn, no media recovery required

2) stopscn not null, no instance recovery required

2. Disable system exceptions:

 
 

  
  
system scn=datafile scn=start scn，stop scn null 
 
 

1) system scn = datafile scn = start scn, no media recovery required

2) stopscn null, instance recovery required

3. Old data files

 
 

  
  
system scn=datafile scn>start scn，stop scn null/notnull 
 
 

1) system scn = datafile scn> start scn. The media must be restored to system scn = datafile scn = start scn.

2) stopscn null. instance recovery is required. not null does not require instance recovery.

4. Backup Control Files

 
 

  
  
System scn = datafile scn <= start scn (when the data file is old)
 
 

Stop scn notnull/null

1) system scn = datafile scn <= start scn, which must be restored to system scn = datafile scn = start scn = current log using the using backup controlfile Media

Scn (maximum SCN of the current log)

2) In order to guarantee that log logs are not used for the last restoration, you must resetlogs

5. Recreate the noresetlogs Control File

In the control file, the datafile Checkpoint comes from the Current log header in Online logs.

 
 

  
  
current log scn=system scn=datafile scn>=
start scn,stop scn not null/null 
 
 

1) current log scn = system scn = datafile scn> = start scn. The media must be restored to system scn = datafile scn = start scn = redolog scn (the maximum SCN of the current log ), stop scn not null

2) stopscn not null does not require instance recovery

6. resetlogs control file Reconstruction

The datafile Checkpoint in the control file comes from each data file header.

 
 

  
  
system scn>=datafile scn=start scn,stop scn not null/null 
 
 

1) system scn> = datafile scn = start scn, you need to use the using backup controlfile media to restore to system scn = datafile scn = start scn (the maximum SCN of the current log), stop scn not null

2) stop notnull. Because SCN is already redolog scn, log is no longer available and must be resetlogs

The above content describes the concept of the Oracle database SCN, hoping to help you in this regard.