案例2：日志发生切换，历史日志已经被覆盖（只能做不完全恢复）
1）模拟环境
SQL> insert into scott.tb01 values(777);
1 row created.
SQL> insert into scott.tb01 values(888);
1 row created.
SQL> commit;
Commit complete.
SQL> col status for a10
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
1 1 10 104857600 3 NO INACTIVE 845923 2012-03-22 14:53:47
4 1 8 104857600 3 YES INACTIVE 801096 2012-03-21 18:22:29
3 1 9 104857600 3 YES INACTIVE 821972 2012-03-22 11:42:07
2 1 11 104857600 3 NO CURRENT 866474 2012-03-22 15:22:09
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
1 1 14 104857600 3 NO INACTIVE 866795 2012-03-22 15:25:45
4 1 16 104857600 3 NO CURRENT 866803 2012-03-22 15:26:04
3 1 13 104857600 3 NO INACTIVE 866787 2012-03-22 15:25:25
2 1 15 104857600 3 NO INACTIVE 866798 2012-03-22 15:25:53
SQL> select * from scott.tb01;
ID
----------
444
555
666
888
111
222
333
777
8 rows selected. ——日志已经被覆盖
SQL> shutdown abort
ORACLE instance shut down.
SQL> !
[oracle@solaris10 ~]$rm /u01/app/oracle/oradata/anny/test*.dbf
2）启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 58722340 bytes
Database Buffers 251658240 bytes
Redo Buffers 2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/anny/test01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
5 FILE NOT FOUND
11 FILE NOT FOUND
3）恢复
——restore datafile
[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/test*.dbf /u01/app/oracle/oradata/anny/
——recover datafile
SQL> recover datafile 5,11
ORA-00279: change 846223 generated at 03/22/2012 14:56:17 needed for thread 1
ORA-00289: suggestion : /disk1/arch/anny/arch_1_10_778514791.log
ORA-00280: change 846223 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/disk1/arch/anny/arch_1_10_778514791.log'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/disk1/arch/anny/arch_1_10_778514791.log'
ORA-27037: unable to obtain file status
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
——需要归档日志。。。。。。。。。。。。。。。。
——恢复需要转储所有的控制文件和datafile
SQL> select name from v$controlfile ;
NAME
---------------------------------------------
/u01/app/oracle/oradata/anny/control01.ctl
/disk1/oradata/anny/control02.ctl
/disk2/oradata/anny/control03.ctl
07:14:12 SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/control01.ctl /u01/app/oracle/oradata/anny/
[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/control02.ctl /disk1/oradata/anny/
[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/control03.ctl /disk2/oradata/anny/
[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/*.dbf /u01/app/oracle/oradata/anny/
——启动数据库到mount
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 58722340 bytes
Database Buffers 251658240 bytes
Redo Buffers 2912256 bytes
Database mounted.
SQL> select file#,checkpoint_change# from v$datafile;——查看检查点信息是否一致
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 846223
2 846223
3 846223
4 846223
5 846223
6 846223
7 846223
8 846223
9 846223
10 846223
11 846223
12 846223
12 rows selected.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 846223
2 846223
3 846223
4 846223
5 846223
6 846223
7 846223
8 846223
9 846223
10 846223
11 846223
12 846223
12 rows selected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/disk3/oradata/anny/redo01a.log'
ORA-00312: online log 1 thread 1: '/disk1/oradata/anny/redo01b.log'
ORA-00312: online log 1 thread 1: '/disk2/oradata/anny/redo01c.log'
——如果此刻直接打开库，因为redo log 和controlfile、datafile 不同步，不能直接打开
SQL> recover database until cancel;——对数据库做基于终止的不完全恢复
Media recovery complete.
SQL> alter database open resetlogs;——对database进行resetlogs 方式打开
Database altered.
查看告警日志信息：
ALTER DATABASE RECOVER database until cancel
Thu Mar 22 15:45:10 2012
Media Recovery Start
Media Recovery Not Required
Completed: ALTER DATABASE RECOVER database until cancel
Thu Mar 22 15:46:28 2012
alter database open resetlogs
Thu Mar 22 15:46:28 2012
RESETLOGS after complete recovery through change 846223
Resetting resetlogs activation ID 1595190016 (0x5f14ab00)
Thu Mar 22 15:47:06 2012
Setting recovery target incarnation to 5
Thu Mar 22 15:47:06 2012
Assigning activation ID 1595245032 (0x5f1581e8)
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /disk3/oradata/anny/redo02a.log
Current log# 2 seq# 2 mem# 1: /disk1/oradata/anny/redo02b.log
Current log# 2 seq# 2 mem# 2: /disk2/oradata/anny/redo02c.log
Successful open of redo thread 1
——验证
SQL> select * from v$log;——数据库被resetlog ，建议立刻做一个数据库的全备。
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- ------------- -------------------
1 1 1 104857600 3 NO INACTIVE 846224 2012-03-22 15:46:28
4 1 0 104857600 3 YES UNUSED 0
3 1 0 104857600 3 YES UNUSED
2 1 2 104857600 3 NO CURRENT
SQL> select * from scott.tb01;
ID
----------
111
222
333
——只能恢复到最后一次备份
oracle视频教程请关注: