Oracle备份与恢复系列 （二）停机一致性备份

关闭数据库的备份与恢复

关闭数据库备份原理很简单，就像我们备份照片、备份word文档一样。复制一份原文件并放置在其它位置备用，如果磁盘空间紧张通常会复制到移动硬盘或网盘上。当原文件损坏时，将备份再复制回来就可以了。当然数据库有自己的备份专属地，磁带库，而不会备份到移动硬盘上。

本次试验分为以下步骤
1，创建备份脚本
2，运行备份脚本
3，删除数据文件
4，运行还原脚本
5，打开数据库
6，确认数据库还原

在其它窗口关注警告日志的动态
tail -f /u01/app/oracle/diag/rdbms/practice/PRACTICE/trace/alert_PRACTICE.log

step1 创建备份脚本
su - root
mkdir /backup/scripts
mkidr /backup/closed_backup
chown oracle:oinstall /backup

vi /backup/scripts/closed_backup.sql

set feedback off heading off verify off
set pagesize 0 linesize 200

define dir = '/backup/closed_backup'
define fil = '/backup/scripts/closed_backup_commands.sql'
prompt *** Spooling to &fil

spool &fil

select 'host cp '|| name   ||' &dir' from v$datafile    order by 1;
select 'host cp '|| member ||' &dir' from v$logfile     order by 1;
select 'host cp '|| name   ||' &dir' from v$controlfile order by 1;
select 'host cp '|| name   ||' &dir' from v$tempfile    order by 1;

spool off;
prompt *** Spooling end ***
shutdown immediate;

@&fil

startup;

step2 运行备份脚本

注意备份之前数据库关闭时间
insert into snow.date_log values(sysdate,'lunchtime');
commit;
select * from snow.date_log order by create_time;
CREATE_TIME         NAME
------------------- ------------------------------
2014/07/31 11:23:42 --
2014/07/31 11:24:42 --
2014/07/31 11:25:16 lunchtime   <==插入数据的时间点

@/backup/scripts/closed_backup_commands.sql

数据库将要一致性关闭，复制所有文件到备份目录，成功后重新启动数据库

查看备份是否存在
ls -al /backup/closed_backup
total 2042840
drwxr-xr-x 2 oracle oinstall      4096 Jul 31 10:49 .
drwxr-xr-x 4 oracle oinstall      4096 Jul 31 09:55 ..
-rw-r----- 1 oracle oinstall   9748480 Jul 31 11:25 control01.ctl   <==冷备的时间点
-rw-r----- 1 oracle oinstall   9748480 Jul 31 11:25 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jul 31 11:25 example01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 31 11:25 indx.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 31 11:25 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 31 11:25 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 31 11:25 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Jul 31 11:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Jul 31 11:25 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Jul 31 11:25 temp01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 31 11:25 tools01.dbf
-rw-r----- 1 oracle oinstall 115351552 Jul 31 11:25 undotbs01.dbf
-rw-r----- 1 oracle oinstall  10493952 Jul 31 11:25 users01.dbf
-rw-r----- 1 oracle oinstall  10493952 Jul 31 11:25 users02.dbf

step3 删除全部数据文件

shutdown immediate;
rm -rf /oradata/PRACTICE/*

step4 运行还原脚本
这一步同rman备份中的restore作用相同

cp /backup/closed_backup/* /oradata/PRACTICE/
mv /oradata/PRACTICE/control02.ctl /u01/app/oracle/fast_recovery_area/PRACTICE/

step5 打开数据库

startup mount;

检查最后检查点时间与备份前的关闭是否一致
set linesize 200;
select file#,status,checkpoint_change#,checkpoint_time,last_change#,last_time from v$datafile;

     FILE# STATUS                CHECKPOINT_CHANGE# CHECKPOINT_TIME     LAST_CHANGE# LAST_TIME
---------- --------------------- ------------------ ------------------- ------------ -------------------
         1 SYSTEM                           1169838 2014/07/31 11:06:35
         2 ONLINE                           1169838 2014/07/31 11:06:35
         3 ONLINE                           1169838 2014/07/31 11:06:35
         4 ONLINE                           1169838 2014/07/31 11:06:35
         5 ONLINE                           1169838 2014/07/31 11:06:35
         6 ONLINE                           1169838 2014/07/31 11:06:35
         7 ONLINE                           1169838 2014/07/31 11:06:35
         8 ONLINE                           1169838 2014/07/31 11:06:35
select group#,sequence#,status,first_change#,first_time from v$log order by first_change#;

GROUP#  SEQUENCE# STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ------------------------------------------------ ------------- -------------------
         1          7 INACTIVE                                               1116891 2014/07/30 19:42:28
         2          8 INACTIVE                                               1138909 2014/07/30 22:01:09
         3          9 CURRENT                                                1169837 2014/07/31 11:06:35

step6 确认数据库还原
alter database open;
select * from snow.date_log order by create_time;
CREATE_TIME         NAME
------------------- ------------------------------
2014/07/31 11:23:42 --
2014/07/31 11:24:42 --
2014/07/31 11:25:16 lunchtime  <==还原成功
2014/07/31 11:25:42 --
2014/07/31 11:32:27 --



归档模式下的完全数据库恢复

本次试验分为以下步骤
1 配置数据库归档
2 运行备份脚本
3 切换重做日志
4 删除一个数据文件
5 还原丢失的数据文件
6 恢复还原的数据问价
7 确认数据库恢复


step1 配置数据库归档

设置归档日志存放路径
alter system set log_archive_dest_1="location=/archive";

设置归档格式
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;

开启归档模式
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

验证设置结果，其中status=valid为成功
col DESTINATION for a20
select DEST_ID,STATUS,DESTINATION from v$archive_dest where dest_id=1;

DEST_ID STATUS    DESTINATION ---------- --------- --------------------------------------------------          1 VALID     /archive

或者通过archive log list命令查看
archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archive
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10


step2 运行备份脚本
跟之前备份区的区别是这次在archivelog模式下执行

shutdown immediate;
startup mount;
@/backup/scripts/closed_backup.sql

ls -l /backup/closed_backup/
total 2042836
-rw-r--r-- 1 oracle oinstall      2814 Jul 31 19:23 closed_backup_commands.sql   <==冷备时间19:23
-rw-r----- 1 oracle oinstall   9748480 Jul 31 19:23 control01.ctl
-rw-r----- 1 oracle oinstall   9748480 Jul 31 19:23 control02.ctl
-rw-r----- 1 oracle oinstall 362422272 Jul 31 19:23 example01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 31 19:23 indx.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 31 19:23 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 31 19:23 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 31 19:23 redo03.log
-rw-r----- 1 oracle oinstall 597696512 Jul 31 19:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Jul 31 19:23 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Jul 31 19:23 temp01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 31 19:23 tools01.dbf
-rw-r----- 1 oracle oinstall 115351552 Jul 31 19:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall  10493952 Jul 31 19:23 users01.dbf
-rw-r----- 1 oracle oinstall  10493952 Jul 31 19:23 users02.dbf

step3 切换重做日志

备份结束后，查看当前日志状态
select group#,sequence#,archived,status,first_change#,first_time,next_change#,next_time from v$log;

GROUP#  SEQUENCE# ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------- ------------- ------------------- ------------ -------------------
         1         22 NO        CURRENT           1293718 2014/07/31 19:46:56   2.8147E+14    <==当前日志为第22号
         2         20 YES       INACTIVE         1271522 2014/07/31 19:23:43      1272202 2014/07/31 19:28:04
         3         21 YES       INACTIVE         1272202 2014/07/31 19:28:04      1293718 2014/07/31 19:46:56

   

select sequence#,first_change#,first_time,next_change#,resetlogs_change# from v$log_history;

 SEQUENCE# FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# RESETLOGS_CHANGE#
---------- ------------- ------------------- ------------ -----------------
        17       1218933 2014/07/31 13:35:53      1224098            995548
        18       1224098 2014/07/31 14:56:11      1250987            995548
        19       1250987 2014/07/31 15:23:34      1271522            995548
        20       1271522 2014/07/31 19:23:43      1272202            995548
        21       1272202 2014/07/31 19:28:04      1293718            995548

手工插入数据Alex1
insert into snow.date_log values(sysdate,'snow1');
commit;
alter system switch logfile;

查看切换日志后的状态

alert日志
Thu Jul 31 19:50:08 2014
Thread 1 advanced to log sequence 23 (LGWR switch)
  Current log# 2 seq# 23 mem# 0: /oradata/PRACTICE/redo02.log
Thu Jul 31 19:50:08 2014
Archived Log entry 18 added for thread 1 sequence 22 ID 0xb57f8923 dest 1:   <==第22号日志已经被归档，刚才的current redolog 被归档了

select group#,sequence#,archived,status,first_change#,first_time,next_change#,next_time from v$log;

GROUP#  SEQUENCE# ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ---------- ------------- ------------------- ------------ -------------------
         1         22 YES       ACTIVE           1293718 2014/07/31 19:46:56      1294188 2014/07/31 19:50:08
         2         23 NO        CURRENT          1294188 2014/07/31 19:50:08   2.8147E+14    <==最新的当前日志为23号
         3         21 YES       INACTIVE         1272202 2014/07/31 19:28:04      1293718 2014/07/31 19:46:56    
 
select name,sequence#,first_change#,next_change# from v$archived_log

NAME                            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------------------------------ ---------- ------------- ------------
/archive/1_17_854302181.arc            17       1218933      1224098
/archive/1_18_854302181.arc            18       1224098      1250987
/archive/1_19_854302181.arc            19       1250987      1271522
/archive/1_20_854302181.arc            20       1271522      1272202
/archive/1_21_854302181.arc            21       1272202      1293718
/archive/1_22_854302181.arc            22       1293718      1294188   <==snow1 应该就在此处

重复以上插入数据的步骤
insert into snow.date_log values(sysdate,'snow2');
commit;
alter system switch log file;

insert into snow.date_log values(sysdate,'snow3');
commit;
alter system switch log file;

insert into snow.date_log values(sysdate,'snow4');
commit;
alter system switch log file;

查看归档日志
select name,sequence#,first_change#,next_change# from v$archived_log

NAME                            SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------------------------------ ---------- ------------- ------------
/archive/1_22_854302181.arc            22       1293718      1294188
/archive/1_23_854302181.arc            23       1294188      1294332
/archive/1_24_854302181.arc            24       1294332      1294354
/archive/1_25_854302181.arc            25       1294354      1294362

step4 删除一个数据文件users01.dbf
rm /oradata/PRACTICE/users01.dbf

关闭数据库提示 /oradata/PRACTICE/users01.dbf不存在，无法写入SCN
shutdown immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata/PRACTICE/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

shutdown abort;

step5 还原丢失的数据文件

startup mount;

select file#,error,change# from v$recover_file;

     FILE# ERROR                   CHANGE#
---------- -------------------- ----------
         4 FILE NOT FOUND                   <==change#为空

从备份中复制users01.dbf文件，此时recover_file动态视图产生了变化

cp /backup/closed_backup/users01.dbf /oradata/PRACTICE/

select file#,error,change# from v$recover_file;

     FILE# ERROR                 CHANGE#
---------- ------------------ ----------
         4                         1293716    <==user01.dbf为4号数据文件，SCN为 1293716

注意checkpoint_change#的值大于change#,为了一致性打开数据库change#需要从 1195007 恢复到 1197189

select FILE#,CHECKPOINT_CHANGE#, CHECKPOINT_TIME from v$datafile;

FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1            1294362 2014/07/31 19:55:21 <==其它文件的SCN 1294362 比要刚刚复制的文件SCN  1293716 大
         2            1294362 2014/07/31 19:55:21
         3            1294362 2014/07/31 19:55:21
         4            1294362 2014/07/31 19:55:21
         5            1294362 2014/07/31 19:55:21
         6            1294362 2014/07/31 19:55:21
         7            1294362 2014/07/31 19:55:21
         8            1294362 2014/07/31 19:55:21

需要将file4 的scn 恢复到与其它数据文件一致才可以打开数据库

select sequence#,first_change#,first_time,next_change#,resetlogs_change# from v$log_history order by sequence# desc;

 SEQUENCE# FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# RESETLOGS_CHANGE#
---------- ------------- ------------------- ------------ -----------------
        25       1294354 2014/07/31 19:55:05      1294362            995548
        24       1294332 2014/07/31 19:54:42      1294354            995548
        23       1294188 2014/07/31 19:50:08      1294332            995548
        22       1293718 2014/07/31 19:46:56      1294188            995548
        21       1272202 2014/07/31 19:28:04      1293718            995548


step6 恢复还原的数据文件
recover database;

ORA-00279: change 1293716 generated at 07/31/2014 19:40:48 needed for thread 1
ORA-00289: suggestion : /archive/1_21_854302181.arc
ORA-00280: change 1293716 for thread 1 is in sequence #21

Specify log: {=suggested | filename | AUTO | CANCEL}  <==输入回车继续

ORA-00279: change 1293718 generated at 07/31/2014 19:46:56 needed for thread 1
ORA-00289: suggestion : /archive/1_22_854302181.arc
ORA-00280: change 1293718 for thread 1 is in sequence #22

Specify log: {=suggested | filename | AUTO | CANCEL}   <==输入回车继续

ORA-00279: change 1294188 generated at 07/31/2014 19:50:08 needed for thread 1
ORA-00289: suggestion : /archive/1_23_854302181.arc
ORA-00280: change 1294188 for thread 1 is in sequence #23

Specify log: {=suggested | filename | AUTO | CANCEL}   <==输入回车继续

Log applied.
Media recovery complete.

查看alert日志

select * from v$recover_file;
no rows selected     <==恢复成功，不再显示内容


在看看alert日志中提供的数据
ORA-279 signalled during: ALTER DATABASE RECOVER  database  ...
Thu Jul 31 20:02:43 2014
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Media Recovery Log /archive/1_21_854302181.arc
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Media Recovery Log /archive/1_22_854302181.arc
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Media Recovery Log /archive/1_23_854302181.arc
Thu Jul 31 20:02:46 2014
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
  Mem# 0: /oradata/PRACTICE/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0: /oradata/PRACTICE/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0
  Mem# 0: /oradata/PRACTICE/redo02.log
Media Recovery Complete (PRACTICE)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT

step7 确认数据库恢复

alter database open;
select * from snow.date_log order by create_time;

CREATE_TIME         NAME
------------------- ------------------------------
2014/07/31 19:35:48 --
2014/07/31 19:36:48 --
2014/07/31 19:37:48 --
2014/07/31 19:38:48 --
2014/07/31 19:39:48 --
2014/07/31 19:40:48 --
2014/07/31 19:47:01 --
2014/07/31 19:48:02 --
2014/07/31 19:49:02 --
2014/07/31 19:49:54 snow1
2014/07/31 19:50:02 --
2014/07/31 19:51:02 --
2014/07/31 19:52:02 --
2014/07/31 19:53:02 --
2014/07/31 19:54:02 --
2014/07/31 19:54:28 snow2
2014/07/31 19:54:54 snow3
2014/07/31 19:55:02 --
2014/07/31 19:55:11 snow4
2014/07/31 19:56:02 --
2014/07/31 19:57:02 --
2014/07/31 19:58:02 —

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1243347/，如需转载，请注明出处，否则将追究法律责任。