恢复被误删除的oracle数据文件(二)

原创

fly1116 2013-12-09 13:29:34 博主文章分类：Oracle ©著作权

文章标签 oracle备份恢复误删除数据文件 rm数据文件无备份恢复 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者fly1116的原创作品，请联系作者获取转载授权，否则将追究法律责任

       恢复被误删除的oracle数据文件(一) 分析的是users表空间下所有数据文件被操作系统命令rm误删除的情况，我们拷贝对应的数据文件到原来的位置后，只需要在数据库中对该数据文件先offline,然后进行recover datafile就可以恢复了，该篇博文的链接为:
http://fly1116.blog.51cto.com/8301004/1337681
       那么，如果，连system表空间下的数据文件都被误删除了呢，system表空间的数据文件是不可以在线offline的，我们要怎么进行恢复呢
       下面我们就来看下所有数据文件和归档日志都被误删除的情况下，如果数据库没有崩溃，要如何恢复，恢复的原理请看上面链接的文章，这边只是讲述恢复的过程
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              Archive Mode
Automatic archival             Enabled
Archive destination            /archivelog
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22

2、在fly用户下创建fly表，在sys用户下创建表fly_sys表,fly,,fly_sys表记录分别为4513536和70525

SQL> conn fly/fly
Connected.
SQL> create table fly as select * from dba_objects;
Table created.
SQL> insert into fly select * from fly;
70524 rows created.
SQL> /
141048 rows created.
SQL> /
282096 rows created.
SQL> /
564192 rows created.
SQL> /
1128384 rows created.
SQL> /
2256768 rows created.
SQL>commit;
Commit complete.
SQL> select count(*) from fly;
COUNT(*)
----------
4513536
SQL>conn sys/oracle as sysdba
Connected.
SQL> create table fly_sys as select * from dba_objects;
Table created.
SQL> select count(*) from fly_sys;
COUNT(*)
----------
70525

3、删除所有数据文件和归档日志
3.1. fly.sh脚本用来查看数据文件和归档文件以及组装删除这些文件的脚本fly_datafile.sh,注意fly.sh脚本包含fly.sql脚本

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）输出每行的拖尾空格，缺省为off
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

3.2. 以oracle执行fly.sh脚本,查看所有数据文件和归档日志，以及生成fly_datafile.sh脚本

oracle@fly007:~> ./fly.sh
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Dec 9 11:29:28 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;
NAME
----------------------------------------------------------------------------------------------------
/archivelog/1_8_833412001.dbf
/archivelog/1_9_833412001.dbf
/archivelog/1_10_833412001.dbf
/archivelog/1_11_833412001.dbf
/archivelog/1_12_833412001.dbf
/archivelog/1_13_833412001.dbf
/archivelog/1_14_833412001.dbf
/archivelog/1_15_833412001.dbf
/archivelog/1_16_833412001.dbf
/archivelog/1_17_833412001.dbf
/archivelog/1_18_833412001.dbf
/archivelog/1_19_833412001.dbf
/archivelog/1_20_833412001.dbf
/archivelog/1_21_833412001.dbf
14 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:~>

3.3.查看fly_datafile.sh脚本，并执行fly_datafile.sh脚本删除所有数据文件和归档日志

oracle@fly007:~> cat fly_datafile.sh
rm /home/oracle/oradata/fly/datafiles/fly01.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 /archivelog/1_513_809364352.dbf
rm /archivelog/1_514_809364352.dbf
rm /archivelog/1_512_809364352.dbf
rm /archivelog/1_511_809364352.dbf
oracle@fly007:~> ./fly_datafile.sh
oracle@fly007:~>

4、用户查询数据以及创建表报错

SQL> conn fly/fly
Connected.
SQL> select count(*) from fly;
select count(*) from fly
*
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
SQL> create table fly008 as select * from dba_objects;
create table fly008 as select * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.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 12:50:52
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、查看dbwr进程，进入该进程的fd（文件描述符）目录，查看哪些文件被删除了

oracle@fly007:~> ps aux | grep dbw0 | grep -v grep
oracle   23038  0.0  8.0 3431484 646716 ?      Ss   10:18   0:03 ora_dbw0_apple
oracle@fly007:~> cd /proc/23038/fd
oracle@fly007:/proc/23038/fd> ls -l | grep delete
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 10 -> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 22 -> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 23 -> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 24 -> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 25 -> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 26 -> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 27 -> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 28 -> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 29 -> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 30 -> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 31 -> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 32 -> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 33 -> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)
lrwx------ 1 oracle oinstall 64 2013-12-09 11:36 34 -> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)

7、拼出批量拷贝数据文件到原来位置的脚本

oracle@fly007:/proc/23038/fd> ls -l | grep delete  | grep dbf | awk '{print $8,$10}' > /tmp/copy_datafile.sh
oracle@fly007:/proc/23038/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
27 /home/oracle/oradata/fly/datafiles/fly01.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
oracle@fly007:/proc/23038/fd> sed -i -e "s/^/cp /g" -e "s/$/\ \&/g" /tmp/copy_datafile.sh
oracle@fly007:/proc/23038/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 27 /home/oracle/oradata/fly/datafiles/fly01.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 &

8、执行/tmp/copy_datafile.sh脚本，并行的将数据文件拷贝回原位置

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

9、查看拷贝进程是否结束

oracle@fly007:/proc/22297/fd> watch -n 1 "ps aux | grep cp | grep -v grep"
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   12449  1.0  0.0   5952   712 pts/1    D    10:30   0:00 cp 22 /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf
oracle   12450  1.2  0.0   5948   712 pts/1    D    10:30   0:00 cp 23 /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf
oracle   12451  2.2  0.0   5952   712 pts/1    D    10:30   0:00 cp 24 /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf
oracle   12453  1.2  0.0   5948   712 pts/1    D    10:30   0:00 cp 26 /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf

10、拷贝结束，确认在线日志，控制文件没有被删除后，关闭数据库，进行recover操作，数据完全恢复

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> shutdown abort //如果不能正常关闭，则只能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> select count(*) from fly_sys;
COUNT(*)
----------
70525
SQL> select count(*) from fly.fly;
COUNT(*)
----------
4513536