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

Reference from: http://blog.csdn.net/tianlesoftware/article/details/5251916

When a checkpoint occurs, the SCN is written in four places: three in the control file and one in the datafile header.

1. The experiments are as follows:

-- the three places of Control fil e are:

1.1 System checkpoint SCN = > (SYSTEM CHECKPOINT SCN in control file)

SQL > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

-

3779864

1.2 Datafile checkpoint SCN = > (DATAFILE CHECKPOINT SCN in control file)

SQL > set lines 200

SQL > col name for A60

SQL > select name,checkpoint_change# from v$datafile

NAME CHECKPOINT_CHANGE#

/ u01/app/oracle/oradata/DBdb/system01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/users01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/example01.dbf 3779864

1.3 Stop SCN = > (STOP SCN in control file)

SQL > select name,last_change# from v$datafile

NAME LAST_CHANGE#

/ u01/app/oracle/oradata/DBdb/system01.dbf

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf

/ u01/app/oracle/oradata/DBdb/users01.dbf

/ u01/app/oracle/oradata/DBdb/example01.dbf

Normal datafile under read-write mode last_change# must be NULL

-- another place is in datafile header

1.4 Start SCN = > (DATAFILE HEADER)

SQL > select name, checkpoint_change# from v$datafile_header

NAME CHECKPOINT_CHANGE#

/ u01/app/oracle/oradata/DBdb/system01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/users01.dbf 3779864

/ u01/app/oracle/oradata/DBdb/example01.dbf 3779864

SQL >

II. Related issues

2.1 Why is it stored in CONTROL FILE divided into two places (SYSTEM CHECKPOINT SCN,DATAFILE CHECKPOINT SCN)?

When you set a tbs to read-only, its SCN will freeze and stop, and the DATAFILE CHECKPOINT SCN will not 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.

2.2 what happens to SCN after a normal shutdown database? We can open the database in mount mode, as follows:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

SQL >

SQL > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

-

3782319

SQL > select name,checkpoint_change# from v$datafile

NAME CHECKPOINT_CHANGE#

/ u01/app/oracle/oradata/DBdb/system01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/users01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/example01.dbf 3782319

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

NAME CHECKPOINT_CHANGE# LAST_CHANGE#

-

/ u01/app/oracle/oradata/DBdb/system01.dbf 3782319 3782319

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782319 3782319

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782319 3782319

/ u01/app/oracle/oradata/DBdb/users01.dbf 3782319 3782319

/ u01/app/oracle/oradata/DBdb/example01.dbf 3782319 3782319

You can see that the three SCN locations stored in control file are all the same. Note that the stop scn will not be NULL, but equal to start scn.

SQL > select name,checkpoint_change# from v$datafile_header

NAME CHECKPOINT_CHANGE#

/ u01/app/oracle/oradata/DBdb/system01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/users01.dbf 3782319

/ u01/app/oracle/oradata/DBdb/example01.dbf 3782319

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 checks 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 they are still the same, the database will open normally, otherwise recovery... will be required. When the database is opened, the stop scn stored in control file will return to null, 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 the scn in other locations, but equal to NULL, which means that Oracle does not perform checkpoint during shutdown, and the next boot must be crash recovery.

Crash recovery:

Roll forward must be done first (starting with the current start SCN in redo log file and redoing subsequent submitted transactions). Then do rollback unfinished (dead transaction) transactions from rollback segment. Verify that SCN in controlfile will be equal to SCN in datafile header

2.3 backup first: (database is in mount state, cold backup)

RMAN > backup database tag='full database'

Starting backup at 28-NOV-17

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=21 device type=DISK

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbf

Input datafile file number=00001 name=/u01/app/oracle/oradata/DBdb/system01.dbf

Input datafile file number=00003 name=/u01/app/oracle/oradata/DBdb/undotbs01.dbf

Input datafile file number=00002 name=/u01/app/oracle/oradata/DBdb/sysaux01.dbf

Input datafile file number=00005 name=/u01/app/oracle/oradata/DBdb/example01.dbf

Channel ORA_DISK_1: starting piece 1 at 28-NOV-17

Channel ORA_DISK_1: finished piece 1 at 28-NOV-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_28/o1_mf_nnndf_FULL_DATABASE_f1t8rv9q_.bkp tag=FULL DATABASE comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:03:15

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Including current control file in backup set

Including current SPFILE in backup set

Channel ORA_DISK_1: starting piece 1 at 28-NOV-17

Channel ORA_DISK_1: finished piece 1 at 28-NOV-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_28/o1_mf_ncsnf_FULL_DATABASE_f1t8z23m_.bkp tag=FULL DATABASE comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-NOV-17

RMAN >

-shutdown abort database:

SQL > select status from v$instance

STATUS

-

MOUNTED

SQL > alter database open

Database altered.

SQL > shutdown abort

ORACLE instance shut down.

SQL >

-- opening the library:

SQL > conn / as sysdba

Connected to an idle instance.

SQL >

SQL > startup nomount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

SQL >

SQL > alter database mount

Database altered.

-- query scn status:

SQL > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

-

3782322

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

NAME CHECKPOINT_CHANGE# LAST_CHANGE#

-

/ u01/app/oracle/oradata/DBdb/system01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/users01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/example01.dbf 3782322

Stop scn is not equal to scn in other locations, but is equal to NULL, indicating the need for crash recovery

SQL > select name,checkpoint_change# from v$datafile_header

NAME CHECKPOINT_CHANGE#

/ u01/app/oracle/oradata/DBdb/system01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/users01.dbf 3782322

/ u01/app/oracle/oradata/DBdb/example01.dbf 3782322

2.4 comparison between crash recovery and media recovery

When starting the database, if STOP SCN = NULL is found, it means that crash recovery; is needed to start the database. If you find that the START SCN of datafile header is not equal to the DATAFILE SCN stored in CONTROLFILE, it means that Media recovery is needed.

STOP SCN equal NULL = = > NEED CRASH RECOVERY

DATAFILE HEADER START SCN not equal CONTROLFILE SCN = = > NEED MEDIA RECOVERY

3. Two common problems of RECOVERY DATABASE

3.1 RECOVER DATABASE UNTIL CANCEL = = > OPEN DATABASE RESETLOG

= > DATAFILE HEADER SCN must be less than the DATAFILE SCN of CONTROLFILE

If you do RESTORE DATAFILE, the DATAFILE HEADER SCN of the RESTORE must be smaller than the DATAFILE SCN of the current CONTROLFILE. At this time, the database cannot be opened and media recovery must be performed. Redo the archive log until the SCN=current scn of the datafile header

After restore datafile, you can mount database and then check the SCN of controlfile and datafile header

Select 'controlfile' "SCN location", name,checkpoint_change#

From v$datafile where name like'% users01%'

Union

Select 'file header',name,checkpoint_change#

From v$datafile_header where name like'% users01%'

3.2 RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; = = > OPEN DATABASE RESETLOG

= > DATAFILE HEADER SCN must be greater than CONTROLFILE's DATAFILE SCN

If only a certain TABLE is dropped by DROP, and the overall data structure of the database is not destroyed, you can also use NCOMPLETE RECOVERY to solve the problem. If a certain TABLESPACE OR DATAFILE is dropped by DROP, because the file structure has been destroyed, there is no information about the DATAFILE in the current CONTROL FILE. Even if you only RESTORE DATAFILE and then INCOMPLETE RECOVERY can not save the DATAFILE of the DROP.

You have to RESOTRE the CONTROLFILE backed up before (the DROP DATAFILE Metadata still exists at this time). However, after RESTOREC CONTROLFILE, Oracle will find that the SYSTEM SCN in the CONTROLFILE will be smaller than the current DATAFILE HEADER SCN, and it is not equal to the SCN currently stored in the LOG FILE. In this case, you must use the SCN from RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE to DROP DATAFILE OR DROP TABLESPACE.

Another special situation is that if unfortunately all CONTROL FILE is lost, it must be saved in this way, so please do MULTIPLEXING.

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.