环境: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
模拟场景: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