第二章:備份恢複原理
1、Oracle server ,Instance、oracle database、user process、server process、session、sga 、pga 的定義
2、share pool、data buffer、log buffer
large pool 的功能:在做備份和恢複時需要large pool的支援
3、redo 日志檔案的管理(redo log group) ,歸檔(備份曆史日志)和非歸檔模式(不備份曆史日志)
4、checkpoint 概念:recover的起點
1) full checkpoint :所有的髒塊都寫完,再将scn 寫入到控制檔案和datafile、redo file-----------正常關閉執行個體或 手工生成檢查點(alter system checkpoint)
09:55:26 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1086165
2 1086165
3 1086165
4 1086165
5 1086165
6 1086172
7 1086165
8 1086165
9 1086165
10 1086165
11 1086165
11 rows selected.
09:55:28 SQL> alter system checkpoint;
System altered.
09:55:41 SQL> select file#,checkpoint_change# from v$datafile_header;
1 1086203
2 1086203
3 1086203
4 1086203
5 1086203
6 1086203
7 1086203
8 1086203
9 1086203
10 1086203
11 1086203
2) incremental checkpoint(增量檢查點): 每過3s ,檢查checkpoint 隊列,檢視髒塊的寫入情況,并記錄之前最後一個髒塊的scn 寫入到controlfile
3) partial checkpoint: 當對tablespace 做以下操作時:如offline、readonly 、backup 時,在tablespace 對應的資料檔案上建立檢查點(寫入scn)
09:54:38 SQL> alter tablespace test offline;
Tablespace altered.
09:54:46 SQL> select file#,checkpoint_change# from v$datafile_header;
1 1082763
2 1082763
3 1082763
4 1082763
5 1082763
6 0
7 1082763
8 1082763
9 1082763
10 1082763
11 1082763
09:54:54 SQL> alter system checkpoint;
09:55:13 SQL> select file#,checkpoint_change# from v$datafile_header;
09:55:15 SQL> alter tablespace test online;
09:55:26 SQL> select file#,checkpoint_change# from v$datafile_header;
-------------設定表空間為read only 模式,會在資料檔案上寫入檢查點資訊
09:56:20 SQL> alter tablespace test read only;
09:56:23 SQL> select file#,checkpoint_change# from v$datafile_header;
6 1086219
09:56:27 SQL> alter system checkpoint;
09:56:37 SQL> select file#,checkpoint_change# from v$datafile_header;
1 1086231
2 1086231
3 1086231
4 1086231
5 1086231
7 1086231
8 1086231
9 1086231
10 1086231
11 1086231
09:57:02 SQL> alter tablespace test read write;
09:57:04 SQL> select file#,checkpoint_change# from v$datafile_header;
6 1086252
09:57:06 SQL> alter system checkpoint;
09:57:13 SQL> select file#,checkpoint_change# from v$datafile_header;
1 1086260
2 1086260
3 1086260
4 1086260
5 1086260
6 1086260
7 1086260
8 1086260
9 1086260
10 1086260
11 1086260
09:57:14 SQL>
5、dbwr、lgwr、ckpt、smon、pmon、arch 的功能
1)ckpt:檢查點事件發生時,會啟動ckpt ,然後ckpt通知dbwn 寫髒塊,在寫髒塊之前,通知lgwr 寫redo entries;
并将未送出的事務復原,完成後會在controlfile、data file 以及redo file 寫入scn。
-----------database synchronization 資料庫同步
SCN :system change number
--------system scn(記錄在controlfile)
10:02:11 SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1086260
-------------datafile scn (記錄在controlfile)
10:02:59 SQL> select file#,checkpoint_change# from v$datafile;
----------datafile stop scn
(記錄在controlfile)
10:03:02 SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
-------------正常關庫
10:10:31 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10:10:57 SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
Database mounted.
10:11:06 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
1 1087187 1087187
2 1087187 1087187
3 1087187 1087187
4 1087187 1087187
5 1087187 1087187
6 1087187 1087187
7 1087187 1087187
8 1087187 1087187
9 1087187 1087187
10 1087187 1087187
11 1087187 1087187
----------非正常關庫
10:12:00 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
1 1087188
2 1087188
3 1087188
4 1087188
5 1087188
6 1087188
7 1087188
8 1087188
9 1087188
10 1087188
11 1087188
10:12:05 SQL> show parameter alert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert boolean FALSE
10:12:16 SQL> alter system set log_checkpoints_to_alert=true;
10:12:26 SQL> shutdown abort
10:12:31 SQL> startup mount
10:12:41 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
10:12:49 SQL> alter database open;
Database altered.
10:13:01 SQL> select file#,checkpoint_change# ,last_change# from v$datafile;
1 1108038
2 1108038
3 1108038
4 1108038
5 1108038
6 1108038
7 1108038
8 1108038
9 1108038
10 1108038
11 1108038
10:13:35 SQL>
---------last_change# 在database open 狀态下是一個null 或無窮大的值
當database 正常關閉時,last_change#會和start scn 保持一緻;如果非正常關閉,仍然是一個空值或無窮大的值,這時候在啟動instance,smon 需要做instance recover。
----------datafile scn(記錄在datafile 頭部的scn ,也叫start scn)
10:03:53 SQL> select file#,checkpoint_change# from v$datafile_header;
---------database的一緻性,指的是在open database 時,記錄在controlfile的system scn 和 datafile scn 以及在資料檔案頭部的start scn 應該保持一緻,在一緻的情況下
database可以正常打開,如不不一緻,需要做media recover。
6、Instance Recover 的參數:
1)fast_start_mttr_target :設定生成檢查點的間隔時間,實作instance 的快速recover。
2)recovery_parallelism:recover的并行度
10:17:38 SQL> show parameter recover
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
10:18:59 SQL> alter system set recovery_parallelism=2 scope=spfile;
10:19:04 SQL> startup force;
Database opened.
10:19:19 SQL> show parameter recover
recovery_parallelism integer 2
----------提高并行度,加快roll forward的速度
3) fast_start_parallel_rollback :roll back的并行度
10:19:23 SQL> show parameter fast
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
---------low 啟動的slave process 數是cpu 個數的 2倍
10:21:11 SQL> alter system set fast_start_parallel_rollback=high;