[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.
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 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.
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 11736828790 2014-10-27 17:52:12 856621627
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.
SCOTT@test> alter system checkpoint ;
System altered.
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 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;
Tablespace altered.
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 11736829197 2014-10-27 17:57:39 856621628
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文件中.
--这样热备份期间产生的日志比较多,应该避开业务高峰做这种操作.
SCOTT@test> alter tablespace users begin backup;
Tablespace altered.
$ 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 ;
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> @viewredo
NAME STATISTIC# VALUE
------------------------------------------------------------ ---------- ----------
user commits 6 1
redo size 178 9572
redo wastage 183 0
data blocks consistent reads - undo records applied 293 0
-- 9572-772=8800.
-- 实际上如果在修改这个块的记录,日志产生没有这么大.
SCOTT@test> update dept set loc='YYYY' where deptno=70 ;
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> @viewredo
NAME STATISTIC# VALUE
------------------------------------------------------------ ---------- ----------
user commits 6 2
redo size 178 10188
redo wastage 183 0
data blocks consistent reads - undo records applied 293 0
-- 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
7 rows selected.
--修改同一块的数据,就没有这个多redo.
SCOTT@test> update dept set loc='ZZZZ' where deptno=60 ;
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> @viewredo
NAME STATISTIC# VALUE
------------------------------------------------------------ ---------- ----------
user commits 6 3
redo size 178 10764
redo wastage 183 0
data blocks consistent reads - undo records applied 293 0
--另外实际上这些修改信息是可以写盘的.
SCOTT@test> @lookup_rowid AABBrlAAEAAAAWFAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
269029 4 1413 0 4,1413 alter system dump datafile 4 block 1413
SCOTT@test> alter system checkpoint ;
System altered.
--使用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
ORA-01110: data file 4: '/u01/app/oracle11g/oradata/test/users01.dbf'
--提示file#=4需要恢复.
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.
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.
SYS@test> select * from v$recover_file;
no rows selected
SYS@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
---------------- ------------------ ---------------- -------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT 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.
--可以发现recover datafile 4;可以关闭热备份模式.
4.再做一次在热备份异常关机的情况.
SYS@test> alter database open ;
Database altered.
SYS@test> alter tablespace users begin backup;
Tablespace altered.
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
ORA-01110: data file 4: '/u01/app/oracle11g/oradata/test/users01.dbf'
SYS@test> alter tablespace users end backup;
Tablespace altered.
SYS@test> select * from v$backup ;
FILE# STATUS CHANGE# TIME
---------------- ------------------ ---------------- -------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 11736857045 2014-10-28 10:10:59
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.
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 database open ;
Database altered.
SYS@test> alter tablespace users offline ;
Tablespace altered.
SYS@test> alter tablespace users online ;
Tablespace altered.