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

In-depth Analysis-detailed interpretation of Oracle SCN Mechanism

Https://mp.weixin.qq.com/s?__biz=MjM5MDAxOTk2MQ==&mid=2650276971&idx=1&sn=b5fb89b351d5b5bedd6353ff9c0b2157&chksm=be479c7d8930156bf73bd87f0bac869029f7341b3fdb4ed26a838b4e401c811116669acd5499&mpshare=1&scene=24&srcid=0927zxmXBLuBo3yxm7qsFYOy#rd

The difference between media recovery and instance recovery

Https://www.cnblogs.com/Peyton-for-2012/archive/2013/03/01/2938416.html

Recover database using backup controlfile

Http://blog.chinaunix.net/uid-20274021-id-1969571.html

To understand recover database using backup controlfile, understand recover database first, that is, without using backup controlfile

The situation. In ordinary recover database or recover tablespace, recover datafile, Oracle will record with the current controlfile

SCN shall prevail, and the redo entry of archive log and redo log shall be used to restore the block of the relevant datafile to "the SCN recorded by the current controlfile".

In some cases, the Oracle needs to restore the data to a location that is further back than the SCN recorded by the current controlfile (for example, controlfile is backup

Controlfile, or controlfile is based on trace create. ), at this point, you need to use using backup controlfile. Recovery will not be affected by the "current"

The limit of "SCN" recorded by controlfile. The restriction comes from your statement (until time, until scn), or the available archive log (until).

Cancel).

First of all, let's introduce four SCN concepts.

1. System checkpoint scn (System Checkpoint SCN)

When a checkpoint 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 (Datafile Checkpoint SCN)

When a checkpoint action is completed, Oracle stores the Datafile Checkpoint SCN of each data file in a separate control file.

Select name,checkpoint_change# from v$datafile

3. Start scn (Start SCN)

Oracle stores the scn of this checkpoint in the header of each data file, a value called startup scn, which is used to launch the SCN in the database instance

Check if you need to perform a database recovery media recovery.

Select name,checkpoint_change# from v$datafile_header

4. Terminate scn (Stop SCN)

The termination scn of each data file is stored in the control file. This SCN number is used to check whether the database startup process requires instance recovery.

Select name,last_change# from v$datafile

5.media recovery and instance recovery

1). Media recovery needs to be restored with previous backups, while INSTANCE RECOVERY is not needed.

2). Media recovery usually occurs when the data files in the database are damaged, and the recovery needs to be done by using previous backups and needs to be handled manually.

3). Instance recovery is a recovery that occurs when the instance is shut down abnormally. It comes from INSTANCE itself and does not require human intervention.

6. Scn value during database operation

1)。 After the database is open and running, the system checkpoint in the control file, the data file checkpoint scn in the control file, and the startup scn in each data file header are all

It's the same. The termination scn of each data file in the control file is null.

2)。 In the process of safely shutting down the database, the system performs a checkpoint action, and the termination scn of all data files is set to the one in the header of the data file

The value that starts the scn.

3)。 When the database is restarted, 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 in the data file header with the termination scn of the data file in the control file. If the two values are the same, it means that all the data

Most of the blocks have been committed, and all changes to the database are not lost in the process of shutting down the database, so this process of starting the database does not require any recovery operation.

The database can be opened at this point. When all databases are open, the value of the data file termination scn stored in the control file is changed again to null, which is the table

Show that the data file is open and ready for normal use.

7.SCN and Database Startup

During database startup, the database can start normally when System Checkpoint SCN, Datafile Checkpoint SCN and Start SCN are all the same.

Move, do not need to do media recovery. When one of the three is different, you need to do media recovery. If End SCN is NULL during startup

, you need to do instance recovery. During startup, Oracle first checks whether media recovery is needed, and then checks whether instance is needed.

Reco very .

8.SCN and database shutdown

If the database shuts down normally, a checkpoint will be triggered and the END SCN of the data file will be set to the Start SCN of the corresponding data file. When

When the database is started, it is found that they are consistent, then there is no need to do instance recovery. After the database starts normally, ORACLE sets END SCN to

NULL. If the database is shut down abnormally, the END SCN will be NULL.

9. The system shuts 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

10. The system shuts down abnormally:

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 is required

11. Old data file

System scn=datafile scn > start scn,stop scn null/notnull

1) system scn=datafile scn > start scn, the media needs to be restored to system scn=datafile scn=start scn

2) stopscn null, which requires instance recovery, while not null does not require instance recovery.

twelve。 Backup control file

System scn=datafile scn=start scn, which requires the media to be restored to system scn=datafile scn=start scn=redolog

Scn (maximum SCN of current log), stop scn not null

2) stopscn not null does not need instance recovery

14. Rebuild the resetlogs control file

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, which needs to be restored to system scn=datafile scn=start using using backup controlfile media.

Scn (maximum SCN of current log), stop scn not null

2) stop notnull, because SCN is already redolog scn,log can no longer be used, must resetlogs

15.recover database using backup controlfile

If you lose the current control file, use the cold backup control file to restore, which is used to tell oracle not to use scn in controlfile as the end point for recovery.

16.recover database until cancel

If you lose the current/active redo, specify the end point manually.

17.recover database using backup controlfile until cancel

If the current controlfile is lost and the current/active redo is lost, the archive log will be applied automatically to achieve maximum recovery.

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.