Oracle recover a lost datafile

测试环境

sqlplus / as sysdba

SQL>

create tablespace test datafile

'/oradata/10g/drbak/dgrman/test1.dbf'  size 10m autoextend off, 

'/oradata/10g/drbak/dgrman/test2.dbf'  size 10m autoextend off;

SQL>create  user  test   default tablespace test identified by ank88ank

SQL>grant dba to test ;

SQL> grant dba to test 

 

SQL> connect test/ank88ank

Connected.

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit ;

Commit complete

SQL> col segment_name for a10

SQL> select owner,segment_name from dba_segments where tablespace_name='TEST';

OWNER                          SEGMENT_NA

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

TEST                           TEST

SQL>alter system switch logfile;

在os下删除

mv  /oradata/10g/drbak/dgrman/test1.dbf  /oradata/10g/drbak/dgrman/test1.dbf.bak

mv  /oradata/10g/drbak/dgrman/test2.dbf  /oradata/10g/drbak/dgrman/test2.dbf.bak

恢复测试

关闭数据库

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

启动到mount状态

SQL> startup nomount

ORACLE instance started.

Total System Global Area  184549376 bytes

Fixed Size                  1272696 bytes

Variable Size              67110024 bytes

Database Buffers          113246208 bytes

Redo Buffers                2920448 bytes

SQL> startup mount

mount

SQL> alter database mount

  2  /

Database altered.

SQL> alter database open

  2  /

alter database open

*

ERROR at line 1:

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

ORA-01110: data file 6: '/oradata/10g/drbak/dgrman/test2.dbf'

发现数据文件丢失需要恢复

SQL> col error for a20

 select * from v$recover_file

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME

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

         5 OFFLINE OFFLINE FILE NOT FOUND                0

丢失号5,查找5,找到需要的文件

SQL> col name for a50

SQL> select name ,file# from v$datafile where file#=5;

NAME                                                    FILE#

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

/oradata/10g/drbak/dgrman/test1.dbf                         5

SQL>alter database create datafile  '/oradata/10g/drbak/dgrman/test2.dbf' 

as '/oradata/10g/drbak/dgrman/baktest2.dbf'  size 10m reuse;

alter database create datafile  '/oradata/10g/drbak/dgrman/test1.dbf' 

as '/oradata/10g/drbak/dgrman/baktest1.dbf'  size 10m reuse;

SQL> select name ,status  from v$datafile;

     FILE# NAME                                               STATUS

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

         1 /oradata/10g/drbak/dgrman/system01.dbf             SYSTEM

         2 /oradata/10g/drbak/dgrman/undotbs01.dbf            ONLINE

         3 /oradata/10g/drbak/dgrman/sysaux01.dbf             ONLINE

         4 /oradata/10g/drbak/dgrman/users01.dbf              ONLINE

         5 /oradata/10g/drbak/dgrman/baktest1.dbf             RECOVER

         6 /oradata/10g/drbak/dgrman/baktest2.dbf             ONLINE

SQL>  select * from v$recover_file

  2  /

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         5 OFFLINE OFFLINE                                                                       366582 13-JUL-11

         6 ONLINE  ONLINE                                                                        366584 13-JUL-11

--open 需要进行恢复

SQL> alter database open ;

alter database open

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery if it was restored from backup, or END BACKUP if it was not

ORA-01110: data file 5: '/oradata/10g/drbak/dgrman/baktest1.dbf'

--打开库

SQL> recover database 

Media recovery complete.

SQL> alter database open ;

Database altered.

--正常

SQL> select * from test.test ;

        ID

----------

         1