溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

checkpoint時的SCN寫文件動作

發(fā)布時間：2020-08-10 21:42:46 來源：ITPUB博客 閱讀：127 作者：不一樣的天空w 欄目：關(guān)系型數(shù)據(jù)庫
參考自：http://blog.csdn.net/tianlesoftware/article/details/5251916

當發(fā)生checkpoint時，會把SCN寫到四個地方去：三個地方于control file內(nèi)，一個在datafile header。

一、實驗，如下：
--Control fil e三個地方為：
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

正常datafile在read-write mode下  last_change#一定是NULL


--另外一個地方在datafile header內(nèi)

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>   

二、相關(guān)問題

2.1 為什么儲存在CONTROL FILE中要分為兩個地方(SYSTEM CHECKPOINT SCN,DATAFILE CHECKPOINT SCN) ?

當你把一個tbs設(shè)為read-only時，他的SCN會凍結(jié)停止，此時DATAFILE CHECKPOINT SCN是不會再遞增改變的， 但是整體的SYSTEM CHECKPOINT SCN卻仍然會不斷遞增前進。
所以，這就是為什么需要分別在兩個地方儲存SCN

2.2 正常shutdown database后，SCN會發(fā)生什么變化？我們可以把數(shù)據(jù)庫開在mount mode，如下：
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

可以看到儲存在control file中的三個SCN位置都是相同，注意此時的stop scn不會是NULL，而是等于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

當clean shutdown 時，checkpoint會進行，并且此時datafile的stop scn和start scn會相同。 等到我門開啟數(shù)據(jù)庫時，Oracle檢查datafile header中的start scn和存于control file中的datafile的scn是否相同， 如果相同，接著檢查start scn和stop scn是否相同，如果仍然相同，數(shù)據(jù)庫就會正常開啟，否則就需要recovery... 等到數(shù)據(jù)庫開啟后，儲存在control file中的stop scn就會恢復為NULL值，此時表示datafile是open在正常模式下了。

如果不正常SHUTDOWN (shutdown abort)，則mount數(shù)據(jù)庫后，你會發(fā)現(xiàn)stop scn并不是等于其它位置的scn， 而是等于NULL，這表示Oracle在shutdown時沒有進行checkpoint，下次開機必須進行crash recovery。

crash recovery：
必須先進行roll forward(從redo log file中從目前的start SCN開始，重做后面的已提交之交易)。再從roll back segment 做rollback未完成(dead transaction)交易。檢驗controlfile中的SCN會等于datafile header的SCN

2.3 先進行備份：（數(shù)據(jù)庫處于mount狀態(tài)，冷備）；
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數(shù)據(jù)庫：
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
--啟庫：
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.

--查詢scn狀態(tài)：
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并不是等于其它位置的scn， 而是等于NULL，表示需要進行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 crash recovery 和media recovery 的比較
啟動數(shù)據(jù)庫時，如果發(fā)現(xiàn)STOP SCN = NULL，表示需要進行crash recovery；啟動數(shù)據(jù)庫時，如果發(fā)現(xiàn)有datafile header的START SCN 不等于儲存于CONTROLFILE的DATAFILE SCN，表示需要進行Media recovery

STOP SCN equal NULL ==> NEED CRASH RECOVERY

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


三、RECOVERY DATABASE 兩種常見問題
3.1 RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG
==> DATAFILE HEADER SCN一定會小于CONTROLFILE的DATAFILE SCN

如果你有進行RESTORE DATAFILE，則該RESTORE的DATAFILE HEADER SCN一定會小于目前CONTROLFILE的DATAFILE SCN，此時會無法開啟數(shù)據(jù)庫，必須進行media recovery。 重做archive log直到該datafile header的SCN=current scn

restore datafile后，可以mount database然后去檢查controlfile and datafile header的SCN

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一定會大于CONTROLFILE的DATAFILE SCN

如果只是某TABLE被DROP掉，沒有破壞數(shù)據(jù)庫整體數(shù)據(jù)結(jié)構(gòu)，還可以用NCOMPLETE RECOVERY解決 如果是某個TABLESPACE OR DATAFILE被DROP掉，因為檔案結(jié)構(gòu)已經(jīng)破壞，目前的CONTROL FILE內(nèi)已經(jīng)沒有 該DATAFILE的信息，就算你只RESTORE DATAFILE然后進行INCOMPLETE RECOVERY也無法救回被DROP的DATA FILE。

只好RESOTRE 之前備份的CONTROL FILE(里頭被DROP DATAFILE Metadata此時還存在)，不過RESTOREC CONTROL FILE后 此時Oracle會發(fā)現(xiàn)CONTROL FILE內(nèi)的SYSTEM SCN會小于目前的DATAFILE HEADER SCN，也不等于目前儲存于LOG FILE內(nèi)的SCN， 此時就必須使用RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE到DROP DATAFILE OR DROP TABLESPACE之前的SCN。

另一種特殊狀況就是，萬一不幸地所有CONTROL FILE都遺失了，也必須用這種方式救回，所以請做MULTIPLEXING。



向AI問一下細節(jié)

免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手