案例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视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html