Oracle备份与恢复系列（三）alter tablspace begin backup

完全恢复试验

此次试验需要以下步骤
1 产生数据库活动
2 创建备份脚本
3 运行热备份
4  破坏数据库
5 还原丢失的数据文件
6 恢复还原的数据文件
7 确认数据库恢复

每个数据文件在其首部都有一个序号，该序号给出了系统文件的最新数据库状态。
在一个数据库可以被打开前，所有联机数据文件必须拥有同样的系统变更号（SCN）。
当这些联机数据文件拥有相同的SCN时，它们就是一致的。在数据库open状态下进行的备份不会产生一致的数据文件。
这样，在使用不一致的数据文件还原数据库之前必须进行介质恢复。一旦所有必须的重做信息都被应用而保持SCN一致时，就可以打开数据库了。

step1 产生数据库活动

insert into snow.date_log values(sysdate,'Elle');
commit;
alter system switch logfile;
insert into snow.date_log values(sysdate,'Flow');
commit;
alter system switch logfile;
insert into snow.date_log values(sysdate,'Gigi');
commit;
alter system switch logfile;

创建备份目录
mkdir -p /backup/open_backup
chown oracle:oinstall  /backup/open_backup

step2 创建备份脚本

vi /backup/scripts/open_backup.sql set feedback off set pagesize 0 set heading off set verify off set linesize 100 set trimspool on define dir = '/backup/open_backup' define fil = '/backup/scripts/open_backup_commands.sql' define spo = '&dir/open_backup_output.lst' set serveroutput on spool &fil prompt spool &spo prompt archive log list;; prompt alter system switch logfile;; prompt alter system archive log all;; DECLARE CURSOR cur_tablespace IS   SELECT tablespace_name     FROM dba_tablespaces where tablespace_name<>'TEMP'; CURSOR cur_datafile (tn VARCHAR) IS   SELECT file_name     FROM dba_data_files    WHERE tablespace_name = tn; BEGIN   FOR ct IN cur_tablespace LOOP     dbms_output.put_line ('alter tablespace '||ct.tablespace_name||' begin backup;');     FOR cd IN cur_datafile (ct.tablespace_name) LOOP        dbms_output.put_line ('host cp '||cd.file_name||' &dir');     END LOOP;     dbms_output.put_line ('alter tablespace '||ct.tablespace_name||' end backup;');   END LOOP; END; / prompt alter system switch logfile;; prompt alter database backup controlfile to '&dir./control01.ctl' REUSE;; prompt archive log list;; prompt spool off spool off;--@&fil

备份pfile文件
create pfile='/backup/open_backup/init.ora' from spfile;

step3 运行热备份
@/backup/scripts/open_backup.sql

step4 破坏数据库
rm /oradata/PRACTICE/tools01.dbf
rm /oradata/PRACTICE/users01.dbf 
alter system checkpoint;

step5 还原丢失的数据文件
查看需要恢复的数据文件

当数据文件需要恢复时，数据文件必须脱机。或者将其表空间脱机。
select * from v$recover_file;

alter database datafile 5 offline; alter database datafile 6 offline;

col FILE_NAME for a60
select FILE_ID,FILE_NAME,ONLINE_STATUS from dba_data_files;
         4 /oradata/PRACTICE/users01.dbf                                ONLINE
         3 /oradata/PRACTICE/undotbs01.dbf                              ONLINE
         2 /oradata/PRACTICE/sysaux01.dbf                               ONLINE
         1 /oradata/PRACTICE/system01.dbf                               SYSTEM
         5 /oradata/PRACTICE/example01.dbf                              RECOVER
         6 /oradata/PRACTICE/tools01.dbf                                RECOVER
         7 /oradata/PRACTICE/indx.dbf                                   ONLINE
         8 /oradata/PRACTICE/users02.dbf                                ONLINE

cp /backup/open_backup/users01.dbf /oradata/PRACTICE/
cp /backup/open_backup/tools01.dbf /oradata/PRACTICE/

发现检查点SCN
col error for a20
set linesize 200
select * from v$recover_file;

     FILE# ONLINE                ONLINE_STATUS         ERROR                   CHANGE# TIME
---------- --------------------- --------------------- -------------------- ---------- -------------------
         5 OFFLINE               OFFLINE                                       1307778 2014-08-01 12:57:34
         6 OFFLINE               OFFLINE                                       1306321 2014-08-01 12:26:27

step6 恢复还原的数据文件
recover datafile 5;
recover datafile 6;

alter database datafile 5 online; alter database datafile 6 online;

如果归档日志的位置发生变化可以指定其位置
recover from '/archive' datafile 5
recover from '/archive' tablespace tools;

step7 确认数据库恢复

select * from snow.date_log order by create_time;
2014-03-22 10:12:44 2014-03-22 10:28:58


不完全恢复

1- 插入一条记录作为参照，后面的恢复中要能查询的到
insert into snow.date_log values (sysdate,'Iceman');
commit;
select * from snow.date_log order by create_time;

2-切换日志，从v$datafile中的change#提取数据文件的checkpoint scn
alter system switch logfile;

3-查看当前日志
col status for a10
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          7 NO        CURRENT          1309379 2014-08-01 13:16:12   2.8147E+14
         2          5 YES       INACTIVE         1306249 2014-08-01 12:26:03      1306349 2014-08-01 12:26:28
         3          6 YES       ACTIVE           1306349 2014-08-01 12:26:28      1309379 2014-08-01 13:16:12

select sequence#,first_change#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') first_time from v$log_history;

SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ------------- ---------------------------------------------------------
         1       1299137 2014-08-01 10:36:27
         2       1306113 2014-08-01 12:22:18
         3       1306118 2014-08-01 12:22:22
         4       1306123 2014-08-01 12:22:30
         5       1306249 2014-08-01 12:26:03
         6       1306349 2014-08-01 12:26:28   <==该SCN为恢复参考点

4-查看数据文件的SCN
set linesize 200
select FILE# , CHECKPOINT_CHANGE#  ,CHECKPOINT_TIME,LAST_CHANGE# ,LAST_TIME   from v$datafile;
          FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     LAST_CHANGE# LAST_TIME
---------- ------------------ ------------------- ------------ -------------------
         1            1307778 2014-08-01 12:57:34
         2            1307778 2014-08-01 12:57:34
         3            1307778 2014-08-01 12:57:34
         4            1307778 2014-08-01 12:57:34
         5            1308231 2014-08-01 13:02:10
         6            1308238 2014-08-01 13:02:14
         7            1307778 2014-08-01 12:57:34
         8            1307778 2014-08-01 12:57:34

5-删除表
alter system switch log file;
alter system switch log file;
alter system switch log file;

drop table scott.emp;

7-关闭数据库
shutdown immediate;

ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/oradata/PRACTICE/users01.dbf'
ORA-01208: data file is an old version - not accessing current version

shutdown abort;

8-还原数据文件
cp /backup/open_backup/* /oradata/PRACTICE/
cp /backup/open_backup/control01.ctl /u01/app/oracle/fast_recovery_area/PRACTICE/control02.ctl

9-加载数据库，查看需要恢复的数据文件
startup mount;
select * from v$recover_file;

10-恢复数据库到SCN 1306349

由于控制文件也是从备份中还原的，没有记录之后发生的变化，所以使用using backup controlfile字句来使其应用之后产生的归档日志。

recover database until change 1306349 using backup controlfile;

ORA-00279: change 1306340 generated at 08/01/2014 12:26:28 needed for thread 1
ORA-00289: suggestion : /archive/1_5_854447787.arc
ORA-00280: change 1306340 for thread 1 is in sequence #5

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

检验数据文件的SCN恢复状况
select * from v$recover_file;

     FILE# ONLINE                ONLINE_STATUS         ERROR         CHANGE# TIME
---------- --------------------- --------------------- ---------- ---------- -------------------
         1 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         2 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         3 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         4 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         5 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         6 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         7 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28
         8 ONLINE                ONLINE                              1306349 2014-08-01 12:26:28

11-开启数据库

alter database open resetlogs;

查看scott.emp是否存在
select * from scott.emp;

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

转载于:http://blog.itpub.net/29047826/viewspace-1244022/