Oracle恢复实验（一）

环境：Oracle10g、Red Hat 4，Oracle运行在归档模式。 

模拟场景：user01.dbf数据文件损坏，关闭数据库条件下恢复。 

具体步骤： 

1、先做个全库冷备份，备份控制文件、数据文件、参数文件 
SQL> select name from v$controlfile 
  2  union 
  3  select name from v$datafile; 

NAME 
-------------------------------------------------------------------------------- 
/home/oracle/oracle/product/oradata/orcl/control01.ctl 
/home/oracle/oracle/product/oradata/orcl/control02.ctl 
/home/oracle/oracle/product/oradata/orcl/control03.ctl 
/home/oracle/oracle/product/oradata/orcl/sysaux01.dbf 
/home/oracle/oracle/product/oradata/orcl/system01.dbf 
/home/oracle/oracle/product/oradata/orcl/undotbs01.dbf 
/home/oracle/oracle/product/oradata/orcl/users01.dbf 

7 rows selected. 

　也就是关库，备份上面几个文件，并且备份参数文件，口令文件可备可不备。 

2、用scott用户登录，建个表，插入些数据。提交，检查一下数据行数。 

SQL> conn scott/oracle 
Connected. 
SQL> create table recover_test tablespace users as select * from emp; 

Table created. 

SQL> insert into recover_test (select * from emp); 

14 rows created. 

SQL> insert into recover_test select * from recover_test; 

56 rows created. 

SQL> / 

112 rows created. 

SQL> / 

224 rows created. 

SQL> commit; 

Commit complete. 

SQL> select count (*) from recover_test; 

  COUNT(*) 
---------- 
       448 
       
3、多做几次日志切换，这样有且于了解Oracle恢复的原理。 
    
SQL> conn /as sysdba 
Connected. 
SQL> alter system switch logfile; 

System altered. 

SQL> / 

System altered. 

SQL> / 

System altered. 

SQL> / 

System altered. 

SQL> / 

System altered. 

SQL> / 

System altered. 

4、主机断电，删除user01.dbf，模拟数据文件损坏 
SQL> shutdown abort 
ORACLE instance shut down. 
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/user01.dbf 

5、尝试打开数据库 
SQL> startup 
ORACLE instance started. 

Total System Global Area  130023424 bytes 
Fixed Size                  1218100 bytes 
Variable Size              62917068 bytes 
Database Buffers           62914560 bytes 
Redo Buffers                2973696 bytes 
Database mounted. 
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file 
ORA-01110: data file 4: '/home/oracle/oracle/product/oradata/orcl/users01.dbf' 

此时提示数据文件找不到。警告日志中可以看到更加详细的信息。 

6、恢复数据文件，尝试打开数据库 
SQL> !cp /disk2/bak/users01.dbf /home/oracle/oracle/product/oradata/orcl/ 

SQL> alter database open; 
alter database open 
* 
ERROR at line 1: 
ORA-01113: file 4 needs media recovery 
ORA-01110: data file 4: '/home/oracle/oracle/product/oradata/orcl/users01.dbf' 

此时由于数据库不一致，提示需要介质恢复。检查一下scn情况。 
select 'scnOfDatafile',name,checkpoint_change# from v$datafile 
union 
select 'scnOfDatafileHeader',name,checkpoint_change# from v$datafile_header; 

'SCNOFDATAFILE'     NAME                                                         CHECKPOINT_CHANGE# 
------------------- ------------------------------------------------------------ ------------------ 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf                    743872 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/system01.dbf                    743872 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf                   743872 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/users01.dbf                     743872 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf                    743872 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/system01.dbf                    743872 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf                   743872 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/users01.dbf                     721971 

9 rows selected. 

可以明显的看到数据文件users01.dbf的scn比较小，这是备份时的scn。 

7、介质恢复 
SQL> recover database; 
ORA-00279: change 721971 generated at 09/25/2011 13:27:36 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_7_760842868.dbf 
ORA-00280: change 721971 for thread 1 is in sequence #7 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 
auto 
ORA-00279: change 723634 generated at 09/25/2011 14:05:56 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_8_760842868.dbf 
ORA-00280: change 723634 for thread 1 is in sequence #8 
ORA-00278: log file '/home/oracle/oracle/product/archive/1_7_760842868.dbf' no longer needed for this recovery 


ORA-00279: change 723636 generated at 09/25/2011 14:05:57 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_9_760842868.dbf 
ORA-00280: change 723636 for thread 1 is in sequence #9 
ORA-00278: log file '/home/oracle/oracle/product/archive/1_8_760842868.dbf' no longer needed for this recovery 


ORA-00279: change 743752 generated at 09/25/2011 14:10:32 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_10_760842868.dbf 
ORA-00280: change 743752 for thread 1 is in sequence #10 
ORA-00278: log file '/home/oracle/oracle/product/archive/1_9_760842868.dbf' no longer needed for this recovery 


ORA-00279: change 743864 generated at 09/25/2011 14:10:58 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_11_760842868.dbf 
ORA-00280: change 743864 for thread 1 is in sequence #11 
ORA-00278: log file '/home/oracle/oracle/product/archive/1_10_760842868.dbf' no longer needed for this recovery 


ORA-00279: change 743866 generated at 09/25/2011 14:10:59 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_12_760842868.dbf 
ORA-00280: change 743866 for thread 1 is in sequence #12 
ORA-00278: log file '/home/oracle/oracle/product/archive/1_11_760842868.dbf' no longer needed for this recovery 


ORA-00279: change 743868 generated at 09/25/2011 14:11:05 needed for thread 1 
ORA-00289: suggestion : /home/oracle/oracle/product/archive/1_13_760842868.dbf 
ORA-00280: change 743868 for thread 1 is in sequence #13 
ORA-00278: log file '/home/oracle/oracle/product/archive/1_12_760842868.dbf' no longer needed for this recovery 


使用auto选项进行恢复，可以看到使用了8-13号日志，到操作系统中看一下： 
[oracle@localhost archive]$ ls -lt 
total 4112 
-rw-r-----  1 oracle oinstall    4608 Sep 25 14:11 1_15_760842868.dbf 
-rw-r-----  1 oracle oinstall    1024 Sep 25 14:11 1_14_760842868.dbf 
-rw-r-----  1 oracle oinstall    1024 Sep 25 14:11 1_13_760842868.dbf 
-rw-r-----  1 oracle oinstall    2048 Sep 25 14:11 1_12_760842868.dbf 
-rw-r-----  1 oracle oinstall    1024 Sep 25 14:10 1_11_760842868.dbf 
-rw-r-----  1 oracle oinstall   53760 Sep 25 14:10 1_10_760842868.dbf 
-rw-r-----  1 oracle oinstall    1536 Sep 25 14:10 1_8_760842868.dbf 
-rw-r-----  1 oracle oinstall   30720 Sep 25 14:10 1_9_760842868.dbf 
-rw-r-----  1 oracle oinstall 4043776 Sep 25 14:10 1_7_760842868.dbf 
还有14、15两个归档日志，可这两个并没有使用。这是为什么？ 
查看一下警告日志 
[oracle@localhost bdump]$ tail -f alert_orcl.log 
  Mem# 0 errs 0: /home/oracle/oracle/product/oradata/orcl/redo01.log 
Sun Sep 25 14:33:19 2011 
Recovery of Online Redo Log: Thread 1 Group 2 Seq 15 Reading mem 0 
  Mem# 0 errs 0: /home/oracle/oracle/product/oradata/orcl/redo02.log 
Sun Sep 25 14:33:19 2011 
Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0 
  Mem# 0 errs 0: /home/oracle/oracle/product/oradata/orcl/redo03.log 
Sun Sep 25 14:33:19 2011 
Media Recovery Complete (orcl) 
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  
根据以上信息，可以看到：Oracle使用的redo日志。 
结论：Oracle介质恢复，使用归档日志+Redo日志，优先使用Redo日志。应用完Redo日志，数据库就可以打开，如果需要Undo日志，继续Undo日志做回滚。可以在第2步发起一个未提交事务，abort关库后，可以看到效果。 

8、验证一下SCN 
select 'scnOfDatafile',name,checkpoint_change# from v$datafile 
union 
select 'scnOfDatafileHeader',name,checkpoint_change# from v$datafile_header 
union 
select 'scnOfDatabase','database',checkpoint_change# from v$database; 

'SCNOFDATAFILE'     NAME                                                         CHECKPOINT_CHANGE# 
------------------- ------------------------------------------------------------ ------------------ 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf                    743906 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/system01.dbf                    743906 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf                   743906 
scnOfDatafile       /home/oracle/oracle/product/oradata/orcl/users01.dbf                     743906 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/sysaux01.dbf                    743906 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/system01.dbf                    743906 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/undotbs01.dbf                   743906 
scnOfDatafileHeader /home/oracle/oracle/product/oradata/orcl/users01.dbf                     743906 

8 rows selected. 
发现SCN号已经一致。此时数据库已经处于同步状态。 

9、检查数据是否恢复 
SQL>  select count(*) from scott.recover_test; 

  COUNT(*) 
---------- 
       448