关闭数据库的备份与恢复
关闭数据库备份原理很简单，就像我们备份照片、备份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 —
818
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
