mysql数据库热备份和冷备份 数据库热备份原理

数据库手动热备份和恢复

一.手动热备份原理

手动热备份是指数据库处于OPEN状态下利用操作系统命令备份数据库。

手动热备份使用begin backup命令备份数据库，只适用于备份数据文件。

手动热备份必须处于归档模式

 

设置begin backup的作用：

①锁定数据文件头的SCN

②告诉oracle需要在日志文件中额外存储发生改变的数据块信息

SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 NOT ACTIVE 0SQL> alter database begin backup; Database altered. SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 ACTIVE 365092 08-DEC-16 2 ACTIVE 365092 08-DEC-16 3 ACTIVE 365092 08-DEC-16 4 ACTIVE 365092 08-DEC-16 ACTIVE状态表示在锁定状态

这时候不允许以shutdown immediate方式关闭数据库 SQL> shutdown immediate ORA-01149: cannot shutdown - file 1 has online backup set ORA-01110: data file 1: '/DATA/ORA11G/system01.dbf'--如果使用shutdown abort关闭数据库，开启的时候会报错 SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 1.0055E+10 bytes Fixed Size 2261888 bytes Variable Size 5670702208 bytes Database Buffers 4362076160 bytes Redo Buffers 19742720 bytes Database mounted. ORA-10873: file 1 needs to be either taken out of backup mode or media recovered ORA-01110: data file 1: '/DATA/ORA11G/system01.dbf'解决方式一：end backup SQL> alter database end backup;Database altered. SQL> alter database open;Database altered. 解决方式二：按照提示，进行介质恢复 SQL> recover database; Media recovery complete. SQL> alter database open;Database altered. SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 386568 08-DEC-16 2 NOT ACTIVE 386568 08-DEC-16 3 NOT ACTIVE 386568 08-DEC-16 4 NOT ACTIVE 386568 08-DEC-16

二.手动热备份日志产生量

数据库正在被改变，使用操作命令复制数据块的过程中，就可能产生分裂，然后造成坏块。 oracle为了避免这种情况，需要将数据库内容保存在在线日志中，所以手动热备份日志产生量比正常情况大。 Release 11.2.0.4.0 发起数据库级别的BEGIN BACKUP SQL> alter database begin backup; Database altered. 查询v$STATNAME和v$MYSTAT视图观察当前会话产生的日志量为17220字节 SQL> col name for a20; SQL> select t1.name ,t2.value from v$statname t1,v$mystat t2 2 where t1.statistic#=t2.statistic# 3 and t1.name in('redo size','redo entries');NAME VALUE -------------------- ---------- redo entries 5 redo size 17220 更改一个数据块中一行数据，产生的日志量为8780字节。 SQL> update scott.employee set comm=100 where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> col name for a20 SQL> select t1.name ,t2.value from v$statname t1,v$mystat t2 2 where t1.statistic#=t2.statistic# 3 and t1.name in('redo size','redo entries');NAME VALUE -------------------- ---------- redo entries 8 redo size 26000SQL> select 26000-17220 from dual; 26000-17220 ----------- 8780 SQL> alter system dump logfile '/DATA/ORA11G/redo02.log';

正常情况下产生的日志量仅仅548字节 SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 408234 08-DEC-16 2 NOT ACTIVE 408234 08-DEC-16 3 NOT ACTIVE 408234 08-DEC-16 4 NOT ACTIVE 408234 08-DEC-16 SQL> col name for a20; SQL> select t1.name ,t2.value from v$statname t1,v$mystat t2 2 where t1.statistic#=t2.statistic# 3 and t1.name in('redo size','redo entries');NAME VALUE -------------------- ---------- redo entries 8 redo size 1648SQL> update scott.employee set comm=100 where empno=7844; 1 row updated. SQL> commit; Commit complete. SQL> col name for a20; SQL> select t1.name ,t2.value from v$statname t1,v$mystat t2 2 where t1.statistic#=t2.statistic# 3 and t1.name in('redo size','redo entries');NAME VALUE -------------------- ---------- redo entries 9 redo size 2196SQL> select 2196-1648 from dual; 2196-1648 ---------- 548 总结：begin backup产生的日志量比正常情况大约多产生8K的日志量，约一个数据块的大小。

三.手动热备份下数据文件的恢复

当数据文件出现问题时，只要将热备份数据文件使用操作系统命令复制到原路径即可。和冷备份的区别是，热备份的数据库需要先应用归档日志，当数据文件和控制文件一致之后才能打开数据库。

热备份的数据文件内部肯定处于不一致的状态，所以开启的时候都必须应用归档日志进行media recovery。修复的起点就是v$backup.CHANGE#记录的SCN。即这里的412056

SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
     FILE# STATUS         CHANGE# TIME   
---------- ------------------ ---------- ---------    
     1 ACTIVE          412056 08-DEC-16    
     2 ACTIVE          412056 08-DEC-16    
     3 ACTIVE          412056 08-DEC-16    
     4 ACTIVE          412056 08-DEC-16

下面演示一下恢复的过程

SQL> alter database begin backup; --置数据库为begin backup Database altered.
	系统cp命令拷贝数据文件到另外一个目录 oracle@test1: /DATA/ORA11G> cp users01.dbf ../
SQL> alter database end backup;  --备份完成后置数据库end backup
	这时候我们删掉数据文件 oracle@test1: /DATA/ORA11G> rm -rf users01.dbf
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 1.0055E+10 bytes Fixed Size 2261888 bytes Variable Size 5670702208 bytes Database Buffers 4362076160 bytes Redo Buffers 19742720 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/DATA/ORA11G/users01.dbf' 开启的时候提示找不到数据文件
	恢复我们刚才备份的数据文件到原路径 oracle@test1: /DATA/ORA11G> cp ../users01.dbf .
SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.Total System Global Area 1.0055E+10 bytes Fixed Size 2261888 bytes Variable Size 5670702208 bytes Database Buffers 4362076160 bytes Redo Buffers 19742720 bytes Database mounted. ORA-01113: file 4 needs media recovery --需要介质恢复 ORA-01110: data file 4: '/DATA/ORA11G/users01.dbf' SQL> recover database; Media recovery complete. SQL> startup ORA-01081: cannot start already-running ORACLE - shut it down first SQL> alter database open;Database altered. --这样我们就恢复了数据文件

不必太糾結于當下，也不必太憂慮未來