[20141027]關于熱備份的問題.txt
--上午同僚問關于熱備份的問題,我認為關于熱備份這部分内容可以跳過,畢竟rman更加友善,效率更高.
--我把關于這部分内容做一些總結.
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1.測試1:
SCOTT@test> select file#,status,tablespace_name,checkpoint_change#,checkpoint_time,checkpoint_count from v$datafile_header where file# in (1,4);
FILE# STATUS TABLESPACE_NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT
---------------- ------- ------------------------------ ------------------ ------------------- ----------------
1 ONLINE SYSTEM 11736795034 2014-10-27 11:54:38 856621626
4 ONLINE USERS 11736795034 2014-10-27 11:54:38 856621626
SCOTT@test> alter system checkpoint ;
System altered.
1 ONLINE SYSTEM 11736828790 2014-10-27 17:52:12 856621627
4 ONLINE USERS 11736828790 2014-10-27 17:52:12 856621627
--可以發現checkpoint_count增加1,CHECKPOINT_CHANGE#發生變化.
SCOTT@test> alter tablespace users begin backup;
Tablespace altered.
4 ONLINE USERS 11736828918 2014-10-27 17:53:55 856621628
--可以發現users表空間觸發了檢查點,CHECKPOINT_CHANGE#發生了變化,CHECKPOINT_COUNT計數+1.
--如果這個時候修改資訊,加入檢查點,一樣寫資料檔案.
SCOTT@test> select * from dept ;
DEPTNO DNAME LOC
---------------- -------------- -------------
10 ACCOUNTING NEW YORK
...
70 aaaa BBBB
7 rows selected.
SCOTT@test> update dept set loc='NNNN' where deptno=70 ;
1 row updated.
SCOTT@test> commit;
Commit complete.
1 ONLINE SYSTEM 11736829197 2014-10-27 17:57:39 856621628
4 ONLINE USERS 11736828918 2014-10-27 17:53:55 856621629
--可以發現file#=4,CHECKPOINT_CHANGE#沒有變化(被當機),而CHECKPOINT_COUNT增加+1.
SCOTT@test> alter tablespace users end backup;
4 ONLINE USERS 11736829197 2014-10-27 17:57:39 856621630
--可以發現file#=4,CHECKPOINT_CHANGE#增加變化,而CHECKPOINT_COUNT增加+1.
--可以發現在熱備份階段當機的是CHECKPOINT_CHANGE#。
--在資料庫open的過程中,Oracle要進行兩次檢查.
--第一次檢查資料檔案頭中的Checkpoint cnt是否與對應控制檔案中的Checkpoint cnt一緻.如果相等,進行第二次檢查.
--第二次檢查資料檔案頭的開始SCN和對應控制檔案中的結束SCN是否一緻如果結束SCN等于開始SCN,則不需要對那個檔案進行恢複.
--對每個資料檔案都完成檢查後,打開資料庫.同時将每個資料檔案的結束SCN設定為無窮大.
2.熱備份産生大量日志.
--熱備份使用的備份工具作業系統指令,為了保證資料塊的資訊完整性,有時候要把整個塊的資訊記錄在redo檔案中.
--這樣熱備份期間産生的日志比較多,應該避開業務高峰做這種操作.
$ cat viewredo.sql
SELECT b.NAME, a.statistic#, a.VALUE
FROM v$mystat a, v$statname b
WHERE b.NAME IN ('redo size', 'redo wastage','user commits','data blocks consistent reads - undo records applied') AND a.statistic# = b.statistic#;
SCOTT@test> @viewredo
NAME STATISTIC# VALUE
------------------------------------------------------------ ---------- ----------
user commits 6 0
redo size 178 772
redo wastage 183 0
data blocks consistent reads - undo records applied 293 0
SCOTT@test> update dept set loc='XXXX' where deptno=70 ;
SCOTT@test> commit ;
user commits 6 1
redo size 178 9572
-- 9572-772=8800.
-- 實際上如果在修改這個塊的記錄,日志産生沒有這麼大.
SCOTT@test> update dept set loc='YYYY' where deptno=70 ;
user commits 6 2
redo size 178 10188
-- 10188-9572=616. 第2次修改就沒有這個多redo.
SCOTT@test> select rowid,dept.* from dept ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABBrlAAEAAAAWDAAB 10 ACCOUNTING NEW YORK
AABBrlAAEAAAAWDAAC 20 RESEARCH DALLAS1
AABBrlAAEAAAAWDAAD 30 SALES CHICAGO
AABBrlAAEAAAAWDAAE 40 OPERATIONS BOSTON
AABBrlAAEAAAAWEAAA 50 aaa bbb
AABBrlAAEAAAAWFAAA 60 cc aaa
AABBrlAAEAAAAWFAAB 70 aaaa YYYY
--修改同一塊的資料,就沒有這個多redo.
SCOTT@test> update dept set loc='ZZZZ' where deptno=60 ;
user commits 6 3
redo size 178 10764
--另外實際上這些修改資訊是可以寫盤的.
SCOTT@test> @lookup_rowid AABBrlAAEAAAAWFAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
269029 4 1413 0 4,1413 alter system dump datafile 4 block 1413
--使用bbed觀察:
BBED> set dba 4,1413
DBA 0x01000585 (16778629 4,1413)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8120 0x2c
BBED> x /rncc
rowdata[0] @8120
flag@8120: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8121: 0x02
cols@8122: 3
col 0[2] @8123: 60
col 1[2] @8126: cc
col 2[4] @8129: ZZZZ
--雖然檔案頭部在熱備份階段當機的是CHECKPOINT_CHANGE#。但是塊中的資訊依舊會更新寫資料檔案的.
3.在熱備份模式下無法正常關閉資料庫.
SYS@test> shutdown immediate
ORA-01149: cannot shutdown - file 4 has online backup set
ORA-01110: data file 4: '/u01/app/oracle11g/oradata/test/users01.dbf'
--出現ORA-01149錯誤.
SYS@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
---------------- ------------------ ---------------- -------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 11736835244 2014-10-28 09:46:20
5 NOT ACTIVE 0
6 NOT ACTIVE 3011239824 2012-11-08 15:43:19
7 NOT ACTIVE 0
8 NOT ACTIVE 3268230043 2014-03-20 10:13:21
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
11 rows selected.
--可以發現file#=4處在active模式,也就是在熱備份模式.
--測試異常關機後的情況.
SYS@test> shutdown abort
ORACLE instance shut down.
SYS@test> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
ORA-10873: file 4 needs to be either taken out of backup mode or media recovered
--提示file#=4需要恢複.
SYS@test> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
------ ------- ------- ------------------ ---------------- -------------------
4 ONLINE ONLINE 11736835244 2014-10-28 09:46:20
SYS@test> recover datafile 4;
Media recovery complete.
no rows selected
4 NOT ACTIVE 11736835244 2014-10-28 09:46:20
--可以發現recover datafile 4;可以關閉熱備份模式.
4.再做一次在熱備份異常關機的情況.
SYS@test> alter database open ;
Database altered.
SYS@test> alter tablespace users begin backup;
SYS@test> alter tablespace users end backup;
4 NOT ACTIVE 11736857045 2014-10-28 10:10:59
SYS@test> select open_mode from v$database ;
OPEN_MODE
--------------------
MOUNTED
--奇怪alter tablespace users end backup;指令可以在mount狀态下執行.搞不懂oracle #@%$#@%
SYS@test> alter tablespace users offline ;
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open
SYS@test> alter tablespace users online ;
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)