为了账号安全，请及时绑定邮箱和手机立即绑定
首页 手记 无归档,无备份，rm误删除所有数据文件恢复(四）

无归档,无备份，rm误删除所有数据文件恢复(四）

标签：
Oracle


     恢复的原理，请查看关于该恢复主题的第一篇博文：

http://fly1116.blog.51cto.com/8301004/1337681

      恢复过程使用的fly.sh脚本，及其他脚本的简要介绍，请看关于该恢复主题的第二篇博文：

http://fly1116.blog.51cto.com/8301004/1338316

      在数据库非归档状态，没有任何的备份情况下，通过操作系统命令rm，误删除了所有数据文件，要如何恢复呢

      1、数据库版本11.1.0.7.0和数据库处于非归档状态

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.1.0.7.0 - Production

CORE    11.1.0.7.0      Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /archivelog

Oldest online log sequence     45

Current log sequence           47

SQL>

     2、在fly用户下创建fly表,表记录为：2256800

SQL> conn fly/fly

Connected.

SQL> create table fly as select * from dba_objects;

Table created.

SQL> insert into fly select * from fly;

70525 rows created.

SQL> /

141050 rows created.

SQL> /

282100 rows created.

SQL> /

564200 rows created.

SQL> /

1128400 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from fly;

COUNT(*)

----------

2256800

   3、查看所有数据文件，以及删除所有数据文件

oracle@fly007:~> cat fly.sh

#!/bin/bash

rman target sys/oracle<<EOF 1>/dev/null

crosscheck archivelog all;

delete noprompt expired archivelog all;

quit

EOF

if [ -f fly_datafile.sh ];then

rm fly_datafile.sh

fi

sqlplus /nolog<<EOF

conn sys/oracle as sysdba

@fly.sql

EOF

chmod u+x fly_datafile.sh

oracle@fly007:~> cat fly.sql

set echo on

col file_name format a80

col name format a100

set linesize 200

select file_name from dba_data_files;

select name from v$archived_log where name is not null;

set echo off

set heading off

set newpage none

set feedback off

set termout off

set trimspool on

spool fly_datafile.sh

select 'rm'||' '||file_name from dba_data_files;

select 'rm'||' '||name from v$archived_log where name is not null;

spool off

quit

oracle@fly007:~> ./fly.sh

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Dec 9 21:11:29 2013

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

SQL> Connected.

SQL> SQL> col file_name format a80

SQL> col name format a100

SQL> set linesize 200

SQL> select file_name from dba_data_files;

FILE_NAME

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

/home/oracle/oradata/fly/datafiles/fly01.dbf

/home/oracle/oradata/APPLE/datafile/users02.dbf

/home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf

/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf

/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf

/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf

/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf

/home/oracle/oradata/APPLE/datafile/example02.dbf

/home/oracle/oradata/APPLE/datafile/example03.dbf

/home/oracle/oradata/APPLE/datafile/example04.dbf

/home/oracle/oradata/APPLE/datafile/example05.dbf

/home/oracle/oradata/APPLE/datafile/system03.dbf

/home/oracle/oradata/APPLE/datafile/sysaux03.dbf

13 rows selected.

SQL> select name from v$archived_log where name is not null;

no rows selected

SQL> set echo off

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

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

Oracle Database Vault and Real Application Testing options

oracle@fly007:~> cat fly_datafile.sh

rm /home/oracle/oradata/fly/datafiles/fly01.dbf

rm /home/oracle/oradata/APPLE/datafile/users02.dbf

rm /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf

rm /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf

rm /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf

rm /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf

rm /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf

rm /home/oracle/oradata/APPLE/datafile/example02.dbf

rm /home/oracle/oradata/APPLE/datafile/example03.dbf

rm /home/oracle/oradata/APPLE/datafile/example04.dbf

rm /home/oracle/oradata/APPLE/datafile/example05.dbf

rm /home/oracle/oradata/APPLE/datafile/system03.dbf

rm /home/oracle/oradata/APPLE/datafile/sysaux03.dbf

oracle@fly007:~> ./fly_datafile.sh

oracle@fly007:~>

    4、fly用户创建表报错

oracle@fly007:~> sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Dec 9 21:11:52 2013

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

SQL> conn sys/oracle as sysdba

Connected.

SQL> conn fly/fly

Connected.

SQL> create table fly008 as select * from dba_objects;

create table fly008 as select * from dba_objects

*

ERROR at line 1:

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/home/oracle/oradata/fly/datafiles/fly01.dbf'

ORA-27041: unable to open file

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

Additional information: 3

     5、停止监听，kill通过监听连接过来的进程

oracle@fly007:~> lsnrctl stop

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 09-DEC-2013 21:29:29

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.46.200.5)(PORT=1521)))

The command completed successfully

oracle@fly007:~> ps aux | grep LOCAL=NO | grep -v grep | awk '{print $2}' | xargs kill -9

oracle@fly007:~>

    6、查看dbw0进程pid，查看哪些数据文件被删除了,拷贝被删除的数据文件到原来的位置

oracle@fly007:~> ps aux | grep dbw0 | grep -v grep

oracle    1257  0.0  4.5 3431904 366532 ?      Ss   15:36   0:01 ora_dbw0_apple

oracle@fly007:~> cd /proc/1257/fd

oracle@fly007:/proc/1257/fd> ls -l | grep delete

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 10 -> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 22 -> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 23 -> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 24 -> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 25 -> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 26 -> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 28 -> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 29 -> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 30 -> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 31 -> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 32 -> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 33 -> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 15:55 34 -> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)

lrwx------ 1 oracle oinstall 64 2013-12-09 21:15 37 -> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)

oracle@fly007:/proc/1257/fd> ls -l | grep delete  | grep dbf | awk '{print $8,$10}' > /tmp/copy_datafile.sh

oracle@fly007:/proc/1257/fd> cat /tmp/copy_datafile.sh

22 /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf

23 /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf

24 /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf

25 /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf

26 /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf

28 /home/oracle/oradata/APPLE/datafile/example02.dbf

29 /home/oracle/oradata/APPLE/datafile/example03.dbf

30 /home/oracle/oradata/APPLE/datafile/example04.dbf

31 /home/oracle/oradata/APPLE/datafile/example05.dbf

32 /home/oracle/oradata/APPLE/datafile/users02.dbf

33 /home/oracle/oradata/APPLE/datafile/system03.dbf

34 /home/oracle/oradata/APPLE/datafile/sysaux03.dbf

37 /home/oracle/oradata/fly/datafiles/fly01.dbf

oracle@fly007:/proc/1257/fd> sed -i -e "s/^/cp /g" -e "s/$/\ \&/g" /tmp/copy_datafile.sh

oracle@fly007:/proc/1257/fd> cat /tmp/copy_datafile.sh

cp 22 /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf &

cp 23 /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf &

cp 24 /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf &

cp 25 /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf &

cp 26 /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf &

cp 28 /home/oracle/oradata/APPLE/datafile/example02.dbf &

cp 29 /home/oracle/oradata/APPLE/datafile/example03.dbf &

cp 30 /home/oracle/oradata/APPLE/datafile/example04.dbf &

cp 31 /home/oracle/oradata/APPLE/datafile/example05.dbf &

cp 32 /home/oracle/oradata/APPLE/datafile/users02.dbf &

cp 33 /home/oracle/oradata/APPLE/datafile/system03.dbf &

cp 34 /home/oracle/oradata/APPLE/datafile/sysaux03.dbf &

cp 37 /home/oracle/oradata/fly/datafiles/fly01.dbf &

oracle@fly007:/proc/1257/fd> chmod u+x /tmp/copy_datafile.sh

oracle@fly007:/proc/1257/fd> /tmp/copy_datafile.sh

oracle@fly007:/proc/1257/fd> watch -n 1 "ps aux | grep cp"

Every 1.0s: ps aux | grep cp                                                                                                         Mon Dec  9 21:18:30 2013

root        45  0.0  0.0      0     0 ?        S<   Jul24   0:00 [kacpid]

root        46  0.0  0.0      0     0 ?        S<   Jul24   0:00 [kacpi_notify]

root      2858  0.0  0.0   2684   520 ?        Ss   Jul24   0:00 /sbin/acpid

root      3052  0.0  0.0   6080   700 ?        S    Jul24   0:00 hald-addon-acpi

root      7444  0.0  0.0  20356  2088 ?        S    Jul24   0:01 /usr/sbin/powersaved -d -f /var/run/acpid.socket -v 3

oracle   10123  2.3  0.0   5948   712 pts/0    D    21:16   0:02 cp 23 /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf

oracle   10124  2.5  0.0   5948   712 pts/0    D    21:16   0:02 cp 24 /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf

oracle   10202  0.0  0.0   6400  1356 pts/0    S+   21:18   0:00 watch -n 1 ps aux | grep cp

oracle   10206  0.0  0.0   9168  1532 pts/0    S+   21:18   0:00 sh -c ps aux | grep cp

oracle   10208  0.0  0.0   3976   804 pts/0    S+   21:18   0:00 grep cp

     7、确认在线日志和控制文件没有丢失后，关闭数据库，进行recover database的操作

SQL> conn sys/oracle as sysdba

Connected.

SQL> shutdown immediate

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'

ORA-01208: data file is an old version - not accessing current version

SQL> startup mount

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3240239104 bytes

Fixed Size                  2164048 bytes

Variable Size            2499807920 bytes

Database Buffers          721420288 bytes

Redo Buffers               16846848 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>

      8、查看表的数据，完全恢复

SQL> conn fly/fly

Connected.

SQL> select count(*) from fly;

COUNT(*)

----------

2256800

©著作权归作者所有：来自51CTO博客作者fly1116的原创作品，如需转载，请注明出处，否则将追究法律责任

oracle备份恢复无归档无备份Oracle


点击查看更多内容
发表于 2019.02.13 17:40， 共 2274 人浏览

本文原创发布于慕课网 ，转载请注明出处，谢谢合作

举报
为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

作者其他优质文章

正在加载中

PIPIONE

手记
篇
粉丝
147
获赞与收藏
702

关注作者，订阅最新文章

相关文章推荐

阅读免费教程

  • 推荐
  • 评论
  • 收藏
  • 共同学习，写下你的评论
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消