ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > oracle物理删除数据文件的恢复

oracle物理删除数据文件的恢复

原创 Oracle 作者：聂风520 时间：2021-07-30 09:41:41 0 删除 编辑

一、归档模式下测试

1、open状态下物理删除数据文件，未关闭数据库情况下的恢复

SQL> create tablespace scott datafile '/data/oracle/oradata/orcl/scott.dbf' size 100M;

SQL>  create table scott_test tablespace scott as select * from dba_objects;

SQL>  select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

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

DEPT                           USERS

EMP                            USERS

BONUS                          USERS

SALGRADE                       USERS

TB                             USERS

TA                             USERS

TEST                           USERS

SCOTT_TEST                     SCOTT

T_EX_USER                      

SQL> conn / as sysdba; 

Connected.

SQL> alter system checkpoint;

System altered.

oracle@oracle[/data/oracle/oradata/orcl] ls

control01.ctl  redo02.log  scott.dbf     system01.dbf  undotbs01.dbf

redo01.log     redo03.log  sysaux01.dbf  temp01.dbf    users01.dbf

oracle@oracle[/data/oracle/oradata/orcl] rm -rf scott.dbf

oracle@oracle[/data/oracle/oradata/orcl] sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 29 10:46:24 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> conn scott/tiger;

Connected.

SQL> create table scott_t tablespace scott as select * from v$session;

create table scott_t tablespace scott as select * from v$session

                                                     *

ERROR at line 1:

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/data/oracle/oradata/orcl/scott.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL>exit

oracle@oracle[/data/oracle/oradata/orcl] ps -ef|grep dbw0

oracle   18486     1  0 10:07 ?        00:00:00 ora_dbw0_orcl

oracle   20994 18002  0 10:51 pts/0    00:00:00 grep --color=auto dbw0

oracle@oracle[~] cd /proc/18486/fd

oracle@oracle[/proc/18486/fd] ll

总用量 0

lr-x------ 1 oracle oinstall 64 7月  29 10:55 0 -> /dev/null

l-wx------ 1 oracle oinstall 64 7月  29 10:55 1 -> /dev/null

lrwx------ 1 oracle oinstall 64 7月  29 10:55 10 -> /data/oracle/product/11.2.0/db_1/dbs/lkORCL

lr-x------ 1 oracle oinstall 64 7月  29 10:55 11 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

lrwx------ 1 oracle oinstall 64 7月  29 10:55 12 -> socket:[5901296]

l-wx------ 1 oracle oinstall 64 7月  29 10:55 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 7月  29 10:55 256 -> /data/oracle/oradata/orcl/control01.ctl

lrwx------ 1 oracle oinstall 64 7月  29 10:55 257 -> /data/oracle/fast_recovery_area/orcl/control02.ctl

lrwx------ 1 oracle oinstall 64 7月  29 10:55 258 -> /data/oracle/oradata/orcl/system01.dbf

lrwx------ 1 oracle oinstall 64 7月  29 10:55 259 -> /data/oracle/oradata/orcl/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 7月  29 10:55 260 -> /data/oracle/oradata/orcl/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 7月  29 10:55 261 -> /data/oracle/oradata/orcl/users01.dbf

lrwx------ 1 oracle oinstall 64 7月  29 10:55 262 -> /data/oracle/oradata/orcl/scott.dbf (deleted)

lrwx------ 1 oracle oinstall 64 7月  29 10:55 264 -> /data/oracle/oradata/orcl/temp01.dbf

lr-x------ 1 oracle oinstall 64 7月  29 10:55 3 -> /dev/null

lr-x------ 1 oracle oinstall 64 7月  29 10:55 4 -> /dev/null

lr-x------ 1 oracle oinstall 64 7月  29 10:55 5 -> /dev/null

lr-x------ 1 oracle oinstall 64 7月  29 10:55 6 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 7月  29 10:55 7 -> /proc/18486/fd

lr-x------ 1 oracle oinstall 64 7月  29 10:55 8 -> /dev/zero

lrwx------ 1 oracle oinstall 64 7月  29 10:55 9 -> /data/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat

oracle@oracle[/proc/18486/fd] cp 262 /data/oracle/oradata/orcl/scott.dbf

oracle@oracle[/proc/18486/fd] sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 29 10:57:27 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> alter database datafile '/data/oracle/oradata/orcl/scott.dbf' offline;

Database altered.

SQL> select name,status from v$datafile;

NAME                                                                             STATUS

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

/data/oracle/oradata/orcl/system01.dbf                                           SYSTEM

/data/oracle/oradata/orcl/sysaux01.dbf                                           ONLINE

/data/oracle/oradata/orcl/undotbs01.dbf                                          ONLINE

/data/oracle/oradata/orcl/users01.dbf                                            ONLINE

/data/oracle/oradata/orcl/scott.dbf                                              RECOVER

SQL> recover datafile '/data/oracle/oradata/orcl/scott.dbf';

Media recovery complete.

SQL> alter database datafile '/data/oracle/oradata/orcl/scott.dbf' online;

Database altered.

SQL>  select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

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

DEPT                           USERS

EMP                            USERS

BONUS                          USERS

SALGRADE                       USERS

TB                             USERS

TA                             USERS

TEST                           USERS

SCOTT_TEST                     SCOTT

T_EX_USER                      

9 rows selected

SQL> select count(*) from scott_test;

  COUNT(*)

----------

    145351

2、open 状态下物理删除数据文件 ，关闭了数据库，无法启动情况下的恢复

SQL> create tablespace www datafile '/data/oracle/oradata/orcl/ size 40M;

Tablespace created.

SQL> show user;

USER is "SCOTT"

SQL> create table t1 tablespace www as select * from user_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

11:26:17 oracle@oracle[/proc/18486/fd] cd /data/oracle/oradata/orcl/

11:26:49 oracle@oracle[/data/oracle/oradata/orcl] ls

control01.ctl  redo02.log  scott.dbf     system01.dbf  undotbs01.dbf 

redo01.log     redo03.log  sysaux01.dbf  temp01.dbf    users01.dbf

oracle@oracle[/data/oracle/oradata/orcl] rm -rf  

oracle@oracle[/data/oracle/oradata/orcl] sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 29 11:27:24 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2257840 bytes

Variable Size   700451920 bytes

Database Buffers   125829120 bytes

Redo Buffers     6565888 bytes

Database mounted.

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

ORA-01110: data file 6: '/data/oracle/oradata/orcl/

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database create datafile '/data/oracle/oradata/orcl/;

Database altered.

SQL> recover datafile '/data/oracle/oradata/orcl/;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn scott/tiger;

Connected.

SQL> select count(*) from t1;

  COUNT(*)

----------

14

二、非归档模式下的恢复

SQL> archive log list;

Database log mode         No Archive Mode

Automatic archival         Disabled

Archive destination        USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     608

Current log sequence        610

SQL> conn scott/tiger;

Connected.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME        TABLESPACE_NAME

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

DEPT        USERS

EMP        USERS

BONUS        USERS

SALGRADE        USERS

TB        USERS

TA        USERS

TEST        USERS

SCOTT_TEST        SCOTT

T1        WWW

T_EX_USER

10 rows selected.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

oracle@oracle[~] cd /data/oracle/oradata/orcl/

oracle@oracle[/data/oracle/oradata/orcl] ls

control01.ctl  redo02.log  scott.dbf     system01.dbf  undotbs01.dbf 

redo01.log     redo03.log  sysaux01.dbf  temp01.dbf    users01.dbf

oracle@oracle[/data/oracle/oradata/orcl] rm -rf  

oracle@oracle[/data/oracle/oradata/orcl] sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 30 09:19:49 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

oracle@oracle[/data/oracle/oradata/orcl] ps -ef|grep dbw0

oracle   23704     1  0 7月29 ?       00:00:04 ora_dbw0_orcl

oracle   30381 29991  0 09:21 pts/0    00:00:00 grep --color=auto dbw0

oracle@oracle[/data/oracle/oradata/orcl] cd /proc/23704/fd

oracle@oracle[/proc/23704/fd] ll

总用量 0

lr-x------ 1 oracle oinstall 64 7月  30 09:21 0 -> /dev/null

l-wx------ 1 oracle oinstall 64 7月  30 09:21 1 -> /dev/null

lrwx------ 1 oracle oinstall 64 7月  30 09:21 10 -> /data/oracle/product/11.2.0/db_1/dbs/lkORCL

lr-x------ 1 oracle oinstall 64 7月  30 09:21 11 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

l-wx------ 1 oracle oinstall 64 7月  30 09:21 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 7月  30 09:21 256 -> /data/oracle/oradata/orcl/control01.ctl

lrwx------ 1 oracle oinstall 64 7月  30 09:21 257 -> /data/oracle/fast_recovery_area/orcl/control02.ctl

lrwx------ 1 oracle oinstall 64 7月  30 09:21 258 -> /data/oracle/oradata/orcl/system01.dbf

lrwx------ 1 oracle oinstall 64 7月  30 09:21 259 -> /data/oracle/oradata/orcl/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 7月  30 09:21 260 -> /data/oracle/oradata/orcl/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 7月  30 09:21 261 -> /data/oracle/oradata/orcl/users01.dbf

lrwx------ 1 oracle oinstall 64 7月  30 09:21 262 -> /data/oracle/oradata/orcl/scott.dbf

lrwx------ 1 oracle oinstall 64 7月  30 09:21 263 -> /data/oracle/oradata/orcl/ (deleted)

lrwx------ 1 oracle oinstall 64 7月  30 09:21 264 -> /data/oracle/oradata/orcl/temp01.dbf

lr-x------ 1 oracle oinstall 64 7月  30 09:21 3 -> /dev/null

lr-x------ 1 oracle oinstall 64 7月  30 09:21 4 -> /dev/null

lr-x------ 1 oracle oinstall 64 7月  30 09:21 5 -> /dev/null

lr-x------ 1 oracle oinstall 64 7月  30 09:21 6 -> /data/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 7月  30 09:21 7 -> /proc/23704/fd

lr-x------ 1 oracle oinstall 64 7月  30 09:21 8 -> /dev/zero

lrwx------ 1 oracle oinstall 64 7月  30 09:21 9 -> /data/oracle/product/11.2.0/db_1/dbs/hc_orcl.da

oracle@oracle[/proc/23704/fd] cp 263 /data/oracle/oradata/orcl/

oracle@oracle[/proc/23704/fd] sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 30 09:24:02 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2257840 bytes

Variable Size   700451920 bytes

Database Buffers   125829120 bytes

Redo Buffers     6565888 bytes

Database mounted.

Database opened.

也可以先启动到 mount 状态

再 recover datafile ' /data/oracle/oradata/orcl/';

alter database open;

SQL> conn scott/tiger;

Connected.

SQL> select table_name,tablespace_name from user_tables; 

TABLE_NAME        TABLESPACE_NAME

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

DEPT        USERS

EMP        USERS

BONUS        USERS

SALGRADE        USERS

TB        USERS

TA        USERS

TEST        USERS

SCOTT_TEST        SCOTT

T1        WWW

T_EX_USER

10 rows selected.

SQL> select count(*) from t1;

  COUNT(*)

----------

14

非归档模式下恢复测试须注意：当前所使用 redo 日志不能被覆盖(不能多次切换日志)，否则用本方法无法恢复。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27105033/viewspace-2784006/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： RMAN SET NEWNAME
下一篇： 没有了~
请登录后发表评论 登录
全部评论
聂风520

注册时间：2014-08-21

  • 博文量
    10
  • 访问量
    17079

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593