Oracle手工恢复案例（非归档模式）

转自http://www.2cto.com/database/201307/224409.html

1.首先把数据库切成非归档模式：

startup mountforce

alter database noarchivelog;

alter database open;

再次查看数据库模式

sys@SIQIAN11>archive log list

Database log mode              No Archive Mode

Automaticarchival             Disabled

Archivedestination            /backup/arch

Oldest online log sequence    15

Current log sequence           17

2.冷备

3.查看当前日志信息

sys@SIQIAN11>select group#,sequence#,archived,status from v$log;



    GROUP#  SEQUENCE# ARC STATUS

-------------------- --- ----------------

         1         19 NO  CURRENT

         2         17 NO  INACTIVE

         3         18 NO  INACTIVE

4.用test用户登录建表插入数据

test@SIQIAN11>create table t01(id int) tablespace test;



test@SIQIAN11>begin

  2  for i in 1..10

  3  loop

  4  insert into t01 values(i);

  5  end loop;

  6  end;

  7  /

  commit;

  再次查看当前日志

  sys@SIQIAN11>select group#,sequence#,archived,status from v$log;



    GROUP#  SEQUENCE# ARC STATUS

---------- ------------- ----------------

         1         19 NO  CURRENT

         2         17 NO  INACTIVE

         3         18 NO  INACTIVE

    说明这次变化记录在了第1组日志中，此时日志没有被覆盖。

5.关库删除数据文件

shutdown abort

[oracle@siqian siqian11g]$ rm -f /u01/oradata/siqian11g/test01.dbf

6.起库并查看要恢复的数据文件

startup

出错：

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/oradata/siqian11g/test01.dbf'

查看要恢复的数据文件

sys@SIQIAN11>select file#,error from v$recover_file;

     FILE# ERROR

---------- -----------------------------------------------------------------

         1 UNKNOWNERROR

         2 UNKNOWNERROR

         3 UNKNOWNERROR

         4 UNKNOWNERROR

         5 UNKNOWNERROR

         6 FILE NOT FOUND



6 rows selected.

7.还原数据文件并做恢复

[oracle@siqian siqian11g]$ cp /backup/cold/test01.dbf /u01/oradata/siqian11g/

sys@SIQIAN11>recover datafile 6;

Media recoverycomplete.

sys@SIQIAN11>alter database open;

8.验证

sys@SIQIAN11>select * from test.t01;

        ID

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

        10

10 rows selected.

 

 

案例二：日志发生切换，历史日志被覆盖（只能作不完全恢复）

承接上面的例子，这次做完一些操作后切日志使其切换。

 

模拟环境：

1.test用户登录再在t01表中插入若干数据

test@SIQIAN11>begin

  2  for i in 11..20

  3  loop

  4  insert into t01 values(i);

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

commit;

2.查看当前日志信息



sys@SIQIAN11>select group#,sequence#,archived,first_change# from v$log;



    GROUP#  SEQUENCE# ARC FIRST_CHANGE#

-------------------- --- -------------

         1         19 NO        2200111

         2         20 NO        2225498

         3         18 NO        2179197

也就是说刚才的变化记录在了第1组中

3.切换日志，使其被覆盖

alter system switch logfile;

多切几次然后再次查看：

sys@SIQIAN11>select group#,sequence#,archived,first_change# from v$log;

    GROUP#  SEQUENCE# ARC FIRST_CHANGE#

---------- ------------- -------------

         1         22 NO        2226207

         2         23 NO        2226211

         3         21 NO        2226205

现在序列19已经被覆盖。

而且t01表中有20条记录。

4.关库删除相应的数据文件

shutdown abort

[oracle@siqian siqian11g]$ rm -f /u01/oradata/siqian11g/test01.dbf

5.起库并查看要恢复的数据文件

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/oradata/siqian11g/test01.dbf'

sys@SIQIAN11>select file#,error from v$recover_file;

     FILE# ERROR

---------------------------------------------------------------------------

         1 UNKNOWN ERROR

         2 UNKNOWN ERROR

         3 UNKNOWN ERROR

         4 UNKNOWN ERROR

         5 UNKNOWN ERROR

         6 FILE NOT FOUND



6 rows selected.

6.还原数据文件并恢复

[oracle@siqian siqian11g]$ cp /backup/cold/test01.dbf /u01/oradata/siqian11g/

sys@SIQIAN11>recover datafile 6;

ORA-00279: change 2201687 generated at 06/29/2013 18:36:58 needed for thread 1

ORA-00289: suggestion : /backup/arch/arch_1_819372290_19.log

ORA-00280: change 2201687 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/backup/arch/arch_1_819372290_19.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3



ORA-00308: cannot open archived log '/backup/arch/arch_1_819372290_19.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

因为序列19的日志没有被归档，所以在恢复时会报错。所以只能做不完全恢复。

7.还原所有控制文件和数据文件来做不完全恢复

[oracle@siqian siqian11g]$ cp /backup/cold/ *.ctl /u01/oradata/siqian11g/

[oracle@siqian siqian11g]$ cp /backup/cold/ *.dbf /u01/oradata/siqian11g/

8.把库起到Mount并查看数据文件头和控制文件头的SCN信息

startup mount force

查看SCN

sys@SIQIAN11>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

----------------------------

         1            2201687

         2            2201687

         3            2201687

         4            2201687

         5           2201687

         6            2201687



6 rows selected.

sys@SIQIAN11>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

----------------------------

         1            2201687

         2            2201687

         3            2201687

         4            2201687

         5            2201687

         6            2201687



6 rows selected.

但redo里的SCN和数据文件、控制文件里的SCN是不同步的，所以现在开库会出问题

alter database open;

ERROR at line 1:

ORA-00338: log 1 of thread 1 is more recent than control file

ORA-00312: online log 1 thread 1: '/u01/oradata/siqian11g/redo01.log'

7.对整个数据库做不完全恢复

recover database untilcancel;

8.以resetlogs方式打开数据库

alter database open resetlogs;

9.验证

select * from test.t01;

发现只有10条记录。

10.冷备

 

因为数据库是以resetlogs方式打开的，所以此时最好做一个数据库全备。