首页 » ORACLE 9i-23ai » Recovery Loss Of Datafile For Which No Backup Is Available(恢复没有备份的数据文件)

Recovery Loss Of Datafile For Which No Backup Is Available(恢复没有备份的数据文件)

  • 2014/02/24
  • ORACLE 9i-23ai
  • 459 views
  • Recovery Loss Of Datafile For Which No Backup Is Available(恢复没有备份的数据文件)已关闭评论

来模拟一种刚建的表空间，还没来的及备份，数据文件被删除，但archive log 都在时的恢复。

sys@ANBOB>create tablespace tbs_rm datafile '/oradata/anbob/tbs_rm01.dbf' size 10m;
Tablespace created.

sys@ANBOB>conn anbob
Enter password: 
Connected.

anbob@ANBOB>create table test_rm tablespace tbs_rm as  select rownum id from dual connect by level<=10;
Table created.

anbob@ANBOB>select * from test_rm;

                  ID
--------------------
                   1
...
                   9
                  10

破坏数据文件，使用mv

[oracle@db231 ~]$ mv /oradata/anbob/tbs_rm01.dbf /oradata/anbob/tbs_rm01_dbf

sys@ANBOB>alter system flush buffer_cache;
System altered.

anbob@ANBOB>select * from test_rm;
                  ID
--------------------
                   1
...
                  10

anbob@ANBOB>exit
Disconnected 

sys@ANBOB>alter system flush buffer_cache;
System altered.

sys@ANBOB>select * from anbob.test_rm;
select * from anbob.test_rm
                    *
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

恢复数据文件

sys@ANBOB>col name for a55

sys@ANBOB>select file#,name,status from v$datafile;

               FILE# NAME                                                    STATUS
-------------------- ------------------------------------------------------- -------
                   1 /u01/app/oracle/oradata/anbob/system01.dbf              SYSTEM
                   2 /u01/app/oracle/oradata/anbob/sysaux01.dbf              ONLINE
                   3 /u01/app/oracle/oradata/anbob/undotbs01.dbf             ONLINE
                   4 /u01/app/oracle/oradata/anbob/users01.dbf               ONLINE
...
                  12 /oradata/anbob/tbs_rm01.dbf                             ONLINE

sys@ANBOB>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
..
TBS_RM                         ONLINE

sys@ANBOB>select file_name,tablespace_name,status from dba_data_files;
ERROR:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

sys@ANBOB>select name,status,error,file# from v$datafile_header;

NAME                                                    STATUS  ERROR                                            FILE#
------------------------------------------------------- ------- ------------------------------------------------------
/u01/app/oracle/oradata/anbob/system01.dbf              ONLINE                                                       1
/u01/app/oracle/oradata/anbob/sysaux01.dbf              ONLINE                                                       2
/u01/app/oracle/oradata/anbob/undotbs01.dbf             ONLINE                                                       3
/u01/app/oracle/oradata/anbob/users01.dbf               ONLINE                                                       4
...
                                                        ONLINE  CANNOT OPEN FILE                                    12
														
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db231 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 24 17:02:59 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ANBOB (DBID=1190288265)

RMAN> restore datafile 12;

Starting restore at 2014-02-24 17:03:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1343 device type=DISK

creating datafile file number=12 name=/oradata/anbob/tbs_rm01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/24/2014 17:03:10
ORA-01182: cannot create database file 12 - file is in use or recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'

sys@ANBOB>alter tablespace tbs_rm offline;
alter tablespace tbs_rm offline
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

sys@ANBOB>alter database create datafile 12;
alter database create datafile 12
*
ERROR at line 1:
ORA-01182: cannot create database file 12 - file is in use or recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'

sys@ANBOB>alter database  datafile '/oradata/anbob/tbs_rm01.dbf' offline;
Database altered.

[oracle@db231 ~]$ rman target /

RMAN> restore datafile 12;

Starting restore at 2014-02-24 17:09:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=578 device type=DISK

creating datafile file number=12 name=/oradata/anbob/tbs_rm01.dbf   >>> In 10g or later, Oracle creates the missing datafile .
restore not done; all files read only, offline, or already restored
Finished restore at 2014-02-24 17:09:32

RMAN> recover datafile 12;


sys@ANBOB>alter database  datafile '/oradata/anbob/tbs_rm01.dbf' online;
Database altered.
sys@ANBOB>select * from anbob.test_rm;

                  ID
--------------------
                   1
..
                  10

Other way:


[oracle@db231 ~]$ mv /oradata/anbob/tbs_rm01.dbf /oradata/anbob/tbs_rm01_dbf      
[oracle@db231 ~]$ ora
bash: ora: command not found
[oracle@db231 ~]$ exit
exit

sys@ANBOB>alter system flush buffer_cache;
System altered.

sys@ANBOB>select * from anbob.test_rm;
select * from anbob.test_rm
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

sys@ANBOB>alter database  datafile 12 offline;
Database altered.

sys@ANBOB>alter database create datafile 12;
Database altered.

sys@ANBOB>alter database  datafile '/oradata/anbob/tbs_rm01.dbf' online;
alter database  datafile '/oradata/anbob/tbs_rm01.dbf' online
*
ERROR at line 1:
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'


sys@ANBOB>recover datafile 12;
Media recovery complete.
sys@ANBOB>alter database  datafile '/oradata/anbob/tbs_rm01.dbf' online;

Database altered.

sys@ANBOB>select * from anbob.test_rm;

                  ID
--------------------
                   1
...
                  10

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：