热备份

最新推荐文章于 2024-10-05 14:16:38 发布

cjz1541

最新推荐文章于 2024-10-05 14:16:38 发布

阅读量88

点赞数

文章标签：数据库

数据库运行中处于活动状态
归档是热备的前提

alter tablespace users begin backup;
1.表空间单独存盘
2.冷冻文件头其他块继续使用此时拷贝只有文件头是好块
3.改变了日志的行为

验证表空间单独存盘
SQL> select name,checkpoint_change# from v$datafile;

NAME         CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/db254/system01.dbf            760777
/db254/undotbs01.dbf            760777
/db254/sysaux01.dbf            760777
/db254/users01.dbf            760777
/db254/mytbs01.dbf            760777

SQL> alter tablespace users begin backup;

Tablespace altered.

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

SQL>

验证冷冻头
SQL> alter system checkpoint;

System altered.

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

NAME         CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/db254/system01.dbf            761997
/db254/undotbs01.dbf            761997
/db254/sysaux01.dbf            761997
/db254/users01.dbf            761836
/db254/mytbs01.dbf            761997

SQL>

SQL> select * from v$backup;

     FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE        0
2 NOT ACTIVE        0
3 NOT ACTIVE        0
4 ACTIVE   761836 28-FEB-11
5 NOT ACTIVE        0

SQL> startup force;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size      1218992 bytes
Variable Size     79693392 bytes
Database Buffers   201326592 bytes
Redo Buffers      2973696 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/db254/users01.dbf'

SQL> select * from v$backup;

     FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE        0
2 NOT ACTIVE        0
3 NOT ACTIVE        0
4 ACTIVE   761836 28-FEB-11
5 NOT ACTIVE        0

SQL> alter database end backup;
注: 此时执行恢复也可以 recover datafile 4; 则系统自动end backup表空间并将数据文件SCN调整一致

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE        0
2 NOT ACTIVE        0
3 NOT ACTIVE        0
4 NOT ACTIVE   761836 28-FEB-11
5 NOT ACTIVE        0

SQL>

日志状态的改变验证

SQL> create table t1 tablespace users as select rownum rn,o.* from dba_objects o;

Table created.

SQL> 创建个带列序号的实验大表这样每个相邻的行都会在同一个块里
序号相距比较远的就会在不同的块上

没有开启表空间热备时日志产生量
SQL> select a.STATISTIC#,name,value from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and name ='redo size';

STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
134 redo size 6012752

SQL> update t1 set object_id=98765 where rn=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select a.STATISTIC#,name,value from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and name ='redo size';

STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
134 redo size 6013216

SQL> select 6013216 - 6012752 from dual;

6013216-6012752
---------------
464

SQL> 日志量很小

开启热备
更新3的ID号和10000的ID号这两行数据一定是在不同块中存储的
SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> update t1 set object_id=98765 where rn=3; 第一次更新3的 ID号

1 row updated.

SQL> commit;

Commit complete.

SQL> select a.STATISTIC#,name,value from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and name ='redo size';

STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
134 redo size 6022560

SQL> select 6022560-6013216 from dual;

6022560-6013216
---------------
9344

SQL> update t1 set object_id=98765 where rn=10000; 第一次更新10000的 ID号

1 row updated.

SQL> commit;

Commit complete.

SQL> select a.STATISTIC#,name,value from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and name ='redo size';

STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
134 redo size 6031392

SQL> select 6031392-6022560 from dual;

6031392-6022560
---------------
8832

SQL> update t1 set object_id=98765 where rn=10001; 更新10001的ID号它应该和10000是同一块上的

1 row updated.

SQL> commit;

Commit complete.

SQL> select a.STATISTIC#,name,value from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and name ='redo size';

STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
134 redo size 6031996

SQL> select 6031996 - 6031392 from dual;

6031996-6031392
---------------
604

SQL>

可见,处于热备的数据文件,在第一个更新块时会基于整块日志,第二次再更新这个块内数据时是基于行日志
所以我们说begin backup产生的日志量很大
实际上并不是把整体性能拖的很糟糕,因为只有第一次才产生基于块的日志.

begin backup
在cp文件的同时,数据又被dml,此时cp得到的文件就是损坏的文件需要介质恢复
在begin backup 至 end backup过程中,如果对数据做改动,改前就会产生大量日志
大量日志是为恢复时所需要的,当第一次修改行数据时会把整个块都做redo log,所以说是大量
(一个块内比如有100行,此时更新一行,实际会记录100行的日志量)
只有第一次是整块日志之后再修改这个块的数据日志量就只是行级日志了.
所以在生产中,这种热备要避开业务繁忙期, 并且一定要记得结束热备 end backup;
当end backup时将冷冻的数据文件头释放一切恢复正常

当数据文件损坏时,将cp的文件找回来,根据当时备份的数据文件头定位到所需的归档和联机日志,进行应用
如果遇到损坏的块,这个损坏的块就是在热备过程中被修改的块,无法直接使用,从日志中找到对应的整块日志
覆盖当前损坏的块,再应用以后日志即可.

10g提供了整库热备但日志压力太大
SQL> select * from v$backup;

     FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 NOT ACTIVE        0
2 NOT ACTIVE        0
3 NOT ACTIVE        0
4 NOT ACTIVE   761836 28-FEB-11
5 NOT ACTIVE        0

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS CHANGE# TIME
---------- ---------- ---------- ---------
1 ACTIVE   782599 28-FEB-11
2 ACTIVE   782599 28-FEB-11
3 ACTIVE   782599 28-FEB-11
4 ACTIVE   782599 28-FEB-11
5 ACTIVE   782599 28-FEB-11

SQL>

10G之前也可以自己完成但全库热备份日志压力太大很少用
SQL> select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)
||'! cp '||file_name||' /bk/ '||chr(10)
||'alter tablespace '||tablespace_name||' end backup;'
from dba_data_files order by tablespace_name,file_name;

'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'||CHR(10)||'!CP'||FILE_NAME||'/BK/'||CHR(10)||'ALT
----------------------------------------------------------------------------------------------------
alter tablespace MYTBS begin backup;
! cp /db254/mytbs01.dbf /bk/
alter tablespace MYTBS end backup;

alter tablespace SYSAUX begin backup;
! cp /db254/sysaux01.dbf /bk/
alter tablespace SYSAUX end backup;

alter tablespace SYSTEM begin backup;
! cp /db254/system01.dbf /bk/
alter tablespace SYSTEM end backup;

alter tablespace UNDOTBS1 begin backup;
! cp /db254/undotbs01.dbf /bk/
alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup;
! cp /db254/users01.dbf /bk/
alter tablespace USERS end backup;

SQL>

热备份的文件校验可用性

[oracle@dba ~]$ dbv

DBVERIFY: Release 10.2.0.1.0 - Production on Sat Apr 23 15:01:42 2011

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)
[oracle@dba ~]$