物理删除oracle数据文件的恢复
admin
2023-05-11 10:22:14
0次

归档模式下测试：

(普通文件系统)：open状态下物理删除数据文件，未关闭情况恢复：

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/oracle/oradata/CPP/example01.dbf

/u01/oracle/oradata/CPP/users01.dbf

/u01/oracle/oradata/CPP/undotbs01.dbf

/u01/oracle/oradata/CPP/sysaux01.dbf

/u01/oracle/oradata/CPP/system01.dbf

SQL> create tablespace test datafile '/u01/oracle/oradata/CPP/test01.dbf' size 10M;

Tablespace created.SQL> create user test identified by test default tablespace test;

User created.

SQL> grant connect ,resource to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> create table t1 as select * from dba_objects where rownum<=1000;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME       TABLESPACE_NAME

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

T1       TEST


SQL> conn /as sysdba

Connected.

SQL> alter system checkpoint;

System altered.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/oracle/oradata/CPP/example01.dbf

/u01/oracle/oradata/CPP/users01.dbf

/u01/oracle/oradata/CPP/undotbs01.dbf

/u01/oracle/oradata/CPP/sysaux01.dbf

/u01/oracle/oradata/CPP/system01.dbf

/u01/oracle/oradata/CPP/test01.dbf

6 rows selected.

[root@orcl1 CPP]# ls

control01.ctl  redo01.log  redo03.log    system01.dbf  test01.dbf     users01.dbf

example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf

[root@orcl1 CPP]# rm -rf test01.dbf 

SQL> create table t2 as select * from t1;

create table t2 as select * from t1                               *

ERROR at line 1:

ORA-01116: error in opening database file 6

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

ORA-27041: unable to open file

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

Additional information: 3

[oracle@orcl1 ~]$ ps -ef | grep dbw0

oracle    2898     1  0 09:24 ?        00:00:00 ora_dbw0_CPP

oracle    5423  5382  3 09:50 pts/2    00:00:00 grep dbw0

[oracle@orcl1 ~]$ su - root

Password: 

[root@orcl1 ~]# cd /proc/2898/

[root@orcl1 2898]# ls

attr       clear_refs       cwd      fdinfo    maps       mountstats  oom_score      root       smaps  status

autogroup  cmdline          environ  io        mem        net         oom_score_adj  sched      stack  syscall

auxv       coredump_filter  exe      limits    mountinfo  numa_maps   pagemap        schedstat  stat   task

cgroup     cpuset           fd       loginuid  mounts     oom_adj     personality    sessionid  statm  wchan

[root@orcl1 2898]# cd fd

[root@orcl1 fd]# ls

0  1  10  11  2  256  257  258  259  260  261  262  263  264  3  4  5  6  7  8  9

[root@orcl1 fd]# ll

total 0

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 0 -> /dev/null

l-wx------ 1 oracle oinstall 64 Jan  7 09:51 1 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 10 -> /u01/oracle/product/11.2.0/db_1/dbs/lkCPP

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 11 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

l-wx------ 1 oracle oinstall 64 Jan  7 09:51 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 256 -> /u01/oracle/oradata/CPP/control01.ctl

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 257 -> /u01/oracle/fast_recovery_area/CPP/control02.ctl

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 258 -> /u01/oracle/oradata/CPP/system01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 259 -> /u01/oracle/oradata/CPP/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 260 -> /u01/oracle/oradata/CPP/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 261 -> /u01/oracle/oradata/CPP/users01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 262 -> /u01/oracle/oradata/CPP/example01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 263 -> /u01/oracle/oradata/CPP/temp01.dbf

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 264 -> /u01/oracle/oradata/CPP/test01.dbf (deleted)

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 3 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 4 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 5 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 6 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 7 -> /proc/2898/fd

lr-x------ 1 oracle oinstall 64 Jan  7 09:51 8 -> /dev/zero

lrwx------ 1 oracle oinstall 64 Jan  7 09:51 9 -> /u01/oracle/product/11.2.0/db_1/dbs/hc_CPP.dat

[oracle@orcl1 fd]# cp 264 /u01/oracle/oradata/CPP/test01.dbf

SQL> select name,status from v$datafile;

NAME     STATUS

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

/u01/oracle/oradata/CPP/system01.dbf     SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf     ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf     ONLINE

/u01/oracle/oradata/CPP/users01.dbf     ONLINE

/u01/oracle/oradata/CPP/example01.dbf     ONLINE

/u01/oracle/oradata/CPP/test01.dbf     ONLINE

6 rows selected.

SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' offline;

Database altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

/u01/oracle/oradata/CPP/system01.dbf     SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf     ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf     ONLINE

/u01/oracle/oradata/CPP/users01.dbf     ONLINE

/u01/oracle/oradata/CPP/example01.dbf     ONLINE

/u01/oracle/oradata/CPP/test01.dbf     RECOVER

6 rows selected.

SQL> recover datafile '/u01/oracle/oradata/CPP/test01.dbf';

Media recovery complete.

SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' online;

Database altered.

SQL> select name,status from v$datafile;


NAME     STATUS

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

/u01/oracle/oradata/CPP/system01.dbf     SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf     ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf     ONLINE

/u01/oracle/oradata/CPP/users01.dbf     ONLINE

/u01/oracle/oradata/CPP/example01.dbf     ONLINE

/u01/oracle/oradata/CPP/test01.dbf     ONLINE

6 rows selected.

SQL> conn test/test;

Connected.

SQL> create table t2 as select * from t1;

Table created.

（ASM文件系统）open状态下物理删除数据文件，关闭情况恢复：

SQL> create tablespace test datafile '+DATA/mecbs/datafile/test01.dbf' size 10M;

Tablespace created.

SQL> conn /as sysdba

Connected.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> conn /as sysdba

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table t1 as select * from dba_objects where rownum<=1000;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME       TABLESPACE_NAME

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

T1       TEST

SQL> select file_name from dba_data_files;


FILE_NAME

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

+DATA/mecbs/datafile/users.259.862339391

+DATA/mecbs/datafile/undotbs1.258.862339391

+DATA/mecbs/datafile/sysaux.257.862339391

+DATA/mecbs/datafile/system.256.862339387

+DATA/mecbs/datafile/example.264.862339751

+DATA/mecbs/datafile/undotbs2.265.862341013

+DATA/mecbs/datafile/system01.dbf

+DATA/mecbs/datafile/crm01.dbf

+DATA/mecbs/datafile/test01.dbf

+DATA/mecbs/datafile/cross.dbf

+DATA/mecbs/datafile/aix_trans.dbf

11 rows selected.


ASMCMD [+data/mecbs/DATAFILE] > ls

AIX_TRANS.281.868377837

CRM.276.863565267

CROSSTBS.279.868372675

EXAMPLE.264.862339751

SYSAUX.257.862339391

SYSTEM.256.862339387

SYSTEM.275.863564943

TEST.278.868380831

UNDOTBS1.258.862339391

UNDOTBS2.265.862341013

USERS.259.862339391

aix_trans.dbf

crm01.dbf

cross.dbf

system01.dbf

test01.dbf

SQL> alter tablespace test offline;

Tablespace altered.

ASMCMD [+data/mecbs/DATAFILE] > rm -rf test01.dbf

ASMCMD [+data/mecbs/DATAFILE] > 

SQL> select name,status from v$datafile;

NAME     STATUS

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

+DATA/mecbs/datafile/system.256.862339387     SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391     ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391     ONLINE

+DATA/mecbs/datafile/users.259.862339391     ONLINE

+DATA/mecbs/datafile/example.264.862339751     ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013     ONLINE

+DATA/mecbs/datafile/system01.dbf     SYSTEM

+DATA/mecbs/datafile/crm01.dbf     ONLINE

+DATA/mecbs/datafile/test01.dbf     OFFLINE

+DATA/mecbs/datafile/cross.dbf     ONLINE

+DATA/mecbs/datafile/aix_trans.dbf     ONLINE

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  484356096 bytes

Fixed Size    2254464 bytes

Variable Size  264243584 bytes

Database Buffers  209715200 bytes

Redo Buffers    8142848 bytes

Database mounted.

SQL> alter database create datafile '+DATA/mecbs/datafile/test01.dbf';

Database altered.

SQL> recover datafile '+DATA/mecbs/datafile/test01.dbf';

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

+DATA/mecbs/datafile/system.256.862339387     SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391     ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391     ONLINE

+DATA/mecbs/datafile/users.259.862339391     ONLINE

+DATA/mecbs/datafile/example.264.862339751     ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013     ONLINE

+DATA/mecbs/datafile/system01.dbf     SYSTEM

+DATA/mecbs/datafile/crm01.dbf     ONLINE

+DATA/mecbs/datafile/test01.dbf     OFFLINE

+DATA/mecbs/datafile/cross.dbf     ONLINE

+DATA/mecbs/datafile/aix_trans.dbf     ONLINE

11 rows selected.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select name,status from v$datafile;

NAME     STATUS

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

+DATA/mecbs/datafile/system.256.862339387     SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391     ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391     ONLINE

+DATA/mecbs/datafile/users.259.862339391     ONLINE

+DATA/mecbs/datafile/example.264.862339751     ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013     ONLINE

+DATA/mecbs/datafile/system01.dbf     SYSTEM

+DATA/mecbs/datafile/crm01.dbf     ONLINE

+DATA/mecbs/datafile/test01.dbf     ONLINE

+DATA/mecbs/datafile/cross.dbf     ONLINE

+DATA/mecbs/datafile/aix_trans.dbf     ONLINE

11 rows selected.

SQL> conn test/test;

Connected.

SQL> select count(*) from t1;

  COUNT(*)

----------

      1000


上一篇：oracle 11g与oracle 10g内存管理

下一篇：Oracle随机函数的用法

相关内容

热门资讯

国际最强！“九章四号”研制成功... 记者 吴长锋 记者13日从中国科学技术大学获悉，该校潘建伟、陆朝阳、张强、刘乃乐等学者联合国内研究单...
在“影视泥土”里激活学生“工程... 在“影视文化名城”的金华东阳，生成式人工智能正深刻重塑影视内容生产方式。扎根于横店影视文化产业生态圈...
实拍马斯克与小儿子X出现在人民... 5月14日，凤凰卫视记者在人民大会堂采访到特斯拉创始人马斯克。
vivo手机用户注意啦！你们的... vivo手机用户注意啦！你们的系统要来一波实用更新了！这次OriginOS 6的五月升级，最给力的就...
定位千元级！汉王录写本M6发布... 5月13日，汉王科技正式发布录写本M6。6英寸机身，厚度6.8毫米，录音、手写、阅读三合一，官方给它...
彻底反超！比亚迪干掉特斯拉：登... 快科技5月14日消息，据报道，2025年，比亚迪超越特斯拉，成为全球最大电池储能系统（BESS）集成...
还原“造神”真相：算法不是开关... （文/刘媛媛 编辑/周远方）这几年，出现了一个很神奇的现象：每隔一段时间，就会有一个普通人突然站上流...
感染专家：安第斯病毒与新冠病毒... 澎湃新闻记者 陈斯斯 邹桥 孙瑞近日，荷兰籍极地探险邮轮“洪迪厄斯”号（MV Hondius）暴发汉...
河南一企业每月给员工父母发“工... 近日，河南一企业每月1日给员工父母发“工资”一事在网上引热议，很多人点赞，更多也是好奇：咋发？发多少...
“90后夫妻同患罕见病瘫痪”引... 近日，一段“90后新婚夫妻同患罕见病瘫痪”的视频在网上流传，引发关注和热议。有网友对夫妻俩的遭遇表示...