CentOS下恢复被误删除的oracle数据文件

Linux就该这么学

在生产环境,总会发生数据库的数据文件被误删除的事情,如SA不懂得数据库,数据库的在线日志为redo01.log,还是cp备份后删除的,让人哭笑不得,数据文件命名为无dbf后缀等,都容易被误删除
      在操作系统层面被误rm的,如果及时发现,还是可以挽救的,下面举例说明下:

1、查询数据库版本和是否归档模式

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.0Production
TNS forLinux: 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     1
Next log sequence to archive   1
Current log sequence           1

2、fly用户创建表fly,fly表的记录数为1128432

SQL> conn fly/fly
Connected.
SQL> create table fly asselect * from dba_objects;
Table created.
SQL> insert into fly select * from fly;
70527rows created.
SQL> /
141054rows created.
SQL> /
282108rows created.
SQL> /
564216rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from fly;
COUNT(*)
----------
1128432

3、查看fly用户默认表空间的数据文件,用操作系统命令rm删除fly用户默认表空间下的所有数据文件

SQL> conn sys/oracle assysdba
Connected.
SQL> select default_tablespace from dba_users where username='FLY';
DEFAULT_TABLESPACE
------------------------------
USERS
SQL> col file_name format a80
SQL> setlinesize 200
SQL> select file_name from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fly/datafile/users02.dbf
/home/oracle/oradata/fly/datafile/user03.dbf
SQL> host rm /home/oracle/oradata/fly/datafile/users02.dbf
SQL> host rm /home/oracle/oradata/fly/datafile/user03.dbf

4、在fly用户下创建表fly007,报错了,注意及时多执行几次创建表的SQL语句,这边都只显示user02.dbf数据文件不存在了,接下来的恢复中,我们肯定要考虑到到底被误删除了几个数据文件

SQL> conn fly/fly
Connected.
SQL> create table fly007 asselect * from dba_objects;
create table fly007 asselect * from dba_objects
*
ERROR at line 1:
ORA-01116: error inopening database file 20
ORA-01110: data file 20: '/home/oracle/oradata/fly/datafile/users02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

5、查看该文件是否仍旧被某些进程打开着

fly007:~ # lsof | grep /home/oracle/oradata/fly/datafile/users02.dbf
oracle    22297oracle   32uW     REG                8,22097233921410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted)
oracle    22301oracle   42u      REG                8,22097233921410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted)
oracle    22309oracle   30u      REG                8,22097233921410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted)
oracle    22317oracle   32u      REG                8,22097233921410008/home/oracle/oradata/fly/datafile/users02.dbf (deleted)

6、数据库的dbwr进程会打开所有的数据文件,包括控制文件,查看dbwr进程的PID为22297

fly007:~ # ps -ef | grep dbw0 | grep -v grep
oracle   222971021:21?        00:00:00ora_dbw0_fly

误删除数据文件恢复原理
       当进程打开了某个文件时,只要该进程仍旧保持打开该文件,即使将该文件删除了,该进程仍然可以向打开该文件时提供给它的文件描述符进行读取和写入操作。在/proc 目录下,包含了反映内核和进程树的各种文件。/proc目录挂载的是在内存中所映射的一块区域,所以这些文件和目录并不存在于磁盘中,因此当我们对这些文件进行读取和写入时,实际上是在从内存中获取相关信息。大多数与lsof 相关的信息都存储于以进程的PID 命名的目录中,即/proc/1116 中包含的是PID 为1116的进程的信息。每个进程目录中存在着各种文件,它们可以使得应用程序简单地了解进程的内存空间、文件描述符列表、指向磁盘上的文件的符号链接和其他系统信息。lsof 程序使用该信息和其他关于内核内部状态的信息来产生其输出。所以lsof 可以显示进程的文件描述符和相关的文件名等信息。也就是我们通过访问进程的文件描述符可以找到该文件的相关信息。
       当系统中的某个文件被意外的删除了,只要这个时候系统中还有进程正在访问该文件,那么我们就可以通过lsof从/proc目录下恢复该文件的内容

7、进入到dbwr进程的fd(文件描述符)目录下,需要确定被删除的数据文件是不是只有一个user02.dbf,结果发现不是的,10,25,32为fd(文件描述符)

fly007:~ # cd /proc/22297/fd
fly007:/proc/22297/fd # ls -l | grep delete
lrwx------ 1oracle oinstall 64Dec  621:2610-> /home/oracle/product/11g/db/dbs/lkinstfly (deleted)
lrwx------ 1oracle oinstall 64Dec  621:2625-> /home/oracle/oradata/fly/datafile/user03.dbf (deleted)
lrwx------ 1oracle oinstall 64Dec  621:2632-> /home/oracle/oradata/fly/datafile/users02.dbf (deleted)
fly007:/proc/22297/fd # ls -l /home/oracle/oradata/fly/datafile/user03.dbf
/bin/ls: /home/oracle/oradata/fly/datafile/user03.dbf: No such file or directory

8、拷贝对应文件描述符为被误删除的数据文件绝对路径,以下使用root拷贝,注意加上-p参数

fly007:/proc/22297/fd # cp -p 32/home/oracle/oradata/fly/datafile/users02.dbf
fly007:/proc/22297/fd # cp -p 25/home/oracle/oradata/fly/datafile/user03.dbf

9、在数据库中进行recover datafile恢复操作,数据没有丢失,完全恢复

fly007:/proc/22297/fd # su - oracle
oracle@fly007:~> sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0- Production on Fri Dec 621:56:452013
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> conn sys/oracle assysdba
Connected.
SQL> alter database datafile '/home/oracle/oradata/fly/datafile/users02.dbf'offline;
Database altered.
SQL> alter database datafile '/home/oracle/oradata/fly/datafile/user03.dbf'offline;
Database altered.
SQL> recover datafile '/home/oracle/oradata/fly/datafile/users02.dbf';
Media recovery complete.
SQL> recover datafile '/home/oracle/oradata/fly/datafile/user03.dbf';
Media recovery complete.
SQL> alter database datafile '/home/oracle/oradata/fly/datafile/users02.dbf'online;
Database altered.
SQL> alter database datafile '/home/oracle/oradata/fly/datafile/user03.dbf'online;
Database altered.
SQL> conn fly/fly
Connected.
SQL> select count(*) from fly;
COUNT(*)
----------
1128432
SQL> create table fly007 asselect * from dba_objects;
Table created.

     数据文件被误删除后,如果关闭数据库,则dbw0进程消失,没有了持续打开误删除文件的进程,就不可以通过此方法进行恢复,因此在数据库出现问题的时候,如果不确认情况的复杂程度,千万不要随便关闭数据库。重启数据库往往是没有意义的,甚至是致命的,另外,若控制文件被rm了,通过这种方式是无法恢复的
     同时,通过linux的rm删除了数据文件,甚至连归档日志都全部删除了,只要数据库没有崩溃,dbw0进程还在,就可以将数据完全恢复,和是否有归档日志没有关系,但是和是否开启了归档是有区别的,后续继续讨论操作系统层面误删除数据库数据文件的恢复

恢复被误删除的oracle数据文件(一) 分析的是users表空间下所有数据文件被操作系统命令rm误删除的情况,我们拷贝对应的数据文件到原来的位置后,只需要在数据库中对该数据文件先offline,然后进行recover datafile就可以恢复了,该篇博文的链接为:
http://fly1116.blog.51cto.com/8301004/1337681
      那么,如果,连system表空间下的数据文件都被误删除了呢,system表空间的数据文件是不可以在线offline的,我们要怎么进行恢复呢
      下面我们就来看下所有数据文件和归档日志都被误删除的情况下,如果数据库没有崩溃,要如何恢复,恢复的原理请看上面链接的文章,这边只是讲述恢复的过程
1、数据库的版本为11.1.0.7.0和数据库处于归档模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.0Production
TNS forLinux: 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> conn fly/fly
Connected.
SQL> create table fly asselect * from dba_objects;
Table created.
SQL> insert into fly select * from fly;
70524rows created.
SQL> /
141048rows created.
SQL> /
282096rows created.
SQL> /
564192rows created.
SQL> /
1128384rows created.
SQL> /
2256768rows created.
SQL>commit;
Commit complete.
SQL> select count(*) from fly;
COUNT(*)
----------
4513536
SQL>conn sys/oracle assysdba
Connected.
SQL> create table fly_sys asselect * 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脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
oracle@fly007:~> cat fly.sh
#!/bin/bash
rman target sys/oracle<<EOF 1>/dev/null
crosscheck archivelog all;
deletenoprompt expired archivelog all;
quit
EOF
if[ -f fly_datafile.sh ];then
rm fly_datafile.sh
fi
sqlplus /nolog<<EOF
conn sys/oracle assysdba
@fly.sql
EOF
chmod u+x fly_datafile.sh
oracle@fly007:~> cat fly.sql
setecho on
col file_name format a80
col name format a100
setlinesize 200
select file_name from dba_data_files;
select name from v$archived_log where name isnot null;
setecho off
setheading off
setnewpage none
setfeedback off
settermout off
settrimspool on   --去除重定向(spool)输出每行的拖尾空格,缺省为off
spool fly_datafile.sh
select 'rm'||' '||file_name from dba_data_files;
select 'rm'||' '||name from v$archived_log where name isnot null;
spool off
quit

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
oracle@fly007:~> ./fly.sh
SQL*Plus: Release 11.1.0.7.0- Production on Mon Dec 911:29:282013
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> Connected.
SQL> SQL> col file_name format a80
SQL> col name format a100
SQL> setlinesize 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
13rows selected.
SQL> select name from v$archived_log where name isnot 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
14rows selected.
SQL> setecho 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脚本删除所有数据文件和归档日志

1
2
3
4
5
6
7
8
9
10
11
12
13
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、用户查询数据以及创建表报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> conn fly/fly
Connected.
SQL> select count(*) from fly;
select count(*) from fly
*
ERROR at line 1:
ORA-01116: error inopening 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 asselect * from dba_objects;
create table fly008 asselect * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error inopening 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所有通过监听连接过来的连接

1
2
3
4
5
6
7
oracle@fly007:~> lsnrctl stop
LSNRCTL forLinux: Version 11.1.0.7.0- Production on 09-DEC-201312: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(文件描述符)目录,查看哪些文件被删除了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
oracle@fly007:~> ps aux | grep dbw0 | grep -v grep
oracle   230380.08.03431484646716?      Ss   10:180:03ora_dbw0_apple
oracle@fly007:~> cd /proc/23038/fd
oracle@fly007:/proc/23038/fd> ls -l | grep delete
lrwx------ 1oracle oinstall 642013-12-0911:3610-> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3622-> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3623-> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3624-> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3625-> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3626-> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3627-> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3628-> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3629-> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3630-> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3631-> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3632-> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3633-> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0911:3634-> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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脚本,并行的将数据文件拷贝回原位置

1
2
3
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、查看拷贝进程是否结束

1
2
3
4
5
6
7
8
9
10
oracle@fly007:/proc/22297/fd> watch -n 1"ps aux | grep cp | grep -v grep"
root        450.00.000?        S<   Jul24   0:00[kacpid]
root        460.00.000?        S<   Jul24   0:00[kacpi_notify]
root      28580.00.02684520?        Ss   Jul24   0:00/sbin/acpid
root      30520.00.06080700?        S    Jul24   0:00hald-addon-acpi
root      74440.00.0203562088?        S    Jul24   0:01/usr/sbin/powersaved -d -f /var/run/acpid.socket -v 3
oracle   124491.00.05952712pts/1D    10:300:00cp 22/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf
oracle   124501.20.05948712pts/1D    10:300:00cp 23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf
oracle   124512.20.05952712pts/1D    10:300:00cp 24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf
oracle   124531.20.05948712pts/1D    10:300:00cp 26/home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> conn sys/oracle assysdba
Connected.
SQL> shutdown immediate
ORA-01122: database file 1failed verification check
ORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'
ORA-01208: data file isan 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 3240239104bytes
Fixed Size                  2164048bytes
Variable Size            2499807920bytes
Database Buffers          721420288bytes
Redo Buffers               16846848bytes
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(*)
----------

恢复的原理,请查看关于该恢复主题的第一篇博文:
http://fly1116.blog.51cto.com/8301004/1337681
     在数据库非归档状态,没有任何的备份情况下,通过操作系统命令rm,误删除了某一个表空间下所有数据文件,要如何恢复呢
     1、数据库版本11.1.0.7.0和数据库处于非归档状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.0Production
TNS forLinux: 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     31
Current log sequence           33

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> conn fly/fly
Connected.
SQL> create table fly asselect * from dba_objects;
Table created.
SQL> insert into fly select * from fly;
70525rows created.
SQL> /
141050rows created.
SQL> /
282100rows created.
SQL> /
564200rows created.
SQL> /
1128400rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from fly;
COUNT(*)
----------
2256800
SQL> conn sys/oracle assysdba
Connected.
SQL> drop table fly_sys purge;
Table dropped.
SQL> create table fly_sys asselect * from dba_objects;
Table created.
SQL> select count(*) from fly_sys;
COUNT(*)
----------
70525

   3、删除fly表空间下的数据文件

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> col file_name format a80
SQL> select file_name from dba_data_files where tablespace_name='FLY';
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fly/datafiles/fly01.dbf
SQL> col file_name format a60
SQL> select file_name from dba_data_files where tablespace_name='FLY';
FILE_NAME
------------------------------------------------------------
/home/oracle/oradata/fly/datafiles/fly01.dbf
SQL> host rm /home/oracle/oradata/fly/datafiles/fly01.dbf
SQL> host ls /home/oracle/oradata/fly/datafiles/fly01.dbf
ls: /home/oracle/oradata/fly/datafiles/fly01.dbf: No such file or directory

   4、fly用户创建表报错

1
2
3
4
5
6
7
8
9
SQL> create table fly008 asselect * from fly;
create table fly008 asselect * from fly
*
ERROR at line 1:
ORA-01116: error inopening 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通过监听连接过来的进程

1
2
3
4
5
6
7
oracle@fly007:~> lsnrctl stop
LSNRCTL forLinux: Version 11.1.0.7.0- Production on 09-DEC-201317:02:56
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,查看哪些数据文件被删除了,拷贝被删除的数据文件到原来的位置

1
2
3
4
5
6
7
8
oracle@fly007:~> ps aux | grep dbw0 | grep -v grep
oracle    12570.14.33430848352280?      Ss   15:360:01ora_dbw0_apple
oracle@fly007:~> cd /proc/1257/fd
oracle@fly007:/proc/1257/fd> ls -l | grep delete
lrwx------ 1oracle oinstall 642013-12-0915:5510-> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5527-> /home/oracle/oradata/fly/datafiles/fly01.dbf (deleted)
oracle@fly007:/proc/1257/fd> cp 27/home/oracle/oradata/fly/datafiles/fly01.dbf
oracle@fly007:/proc/1257/fd>

   7、对数据文件进行offline,然后recover datafile

1
2
3
4
5
6
7
8
9
10
11
12
SQL> conn sys/oracle assysdba
Connected.
SQL> alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'offline; //由于未开归档,不能进行offline的操作
alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'offline drop; //需要使用offline drop
Database altered.
SQL> recover datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf';
Media recovery complete.
SQL> alter database datafile '/home/oracle/oradata/fly/datafiles/fly01.dbf'online;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> conn fly/fly
Connected.
SQL> select count(*) from fly;
COUNT(*)
----------
2256800
SQL> conn sys/oracle assysdba
Connected.
SQL> select count(*) from fly_sys;
COUNT(*)
----------
70525
SQL>

恢复的原理,请查看关于该恢复主题的第一篇博文:
http://fly1116.blog.51cto.com/8301004/1337681
     恢复过程使用的fly.sh脚本,及其他脚本的简要介绍,请看关于该恢复主题的第二篇博文:
http://fly1116.blog.51cto.com/8301004/1338316
     在数据库非归档状态,没有任何的备份情况下,通过操作系统命令rm,误删除了所有数据文件,要如何恢复呢
     1、数据库版本11.1.0.7.0和数据库处于非归档状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.0Production
TNS forLinux: 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> conn fly/fly
Connected.
SQL> create table fly asselect * from dba_objects;
Table created.
SQL> insert into fly select * from fly;
70525rows created.
SQL> /
141050rows created.
SQL> /
282100rows created.
SQL> /
564200rows created.
SQL> /
1128400rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from fly;
COUNT(*)
----------
2256800

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
oracle@fly007:~> cat fly.sh
#!/bin/bash
rman target sys/oracle<<EOF 1>/dev/null
crosscheck archivelog all;
deletenoprompt expired archivelog all;
quit
EOF
if[ -f fly_datafile.sh ];then
rm fly_datafile.sh
fi
sqlplus /nolog<<EOF
conn sys/oracle assysdba
@fly.sql
EOF
chmod u+x fly_datafile.sh
oracle@fly007:~> cat fly.sql
setecho on
col file_name format a80
col name format a100
setlinesize 200
select file_name from dba_data_files;
select name from v$archived_log where name isnot null;
setecho off
setheading off
setnewpage none
setfeedback off
settermout off
settrimspool on
spool fly_datafile.sh
select 'rm'||' '||file_name from dba_data_files;
select 'rm'||' '||name from v$archived_log where name isnot null;
spool off
quit
oracle@fly007:~> ./fly.sh
SQL*Plus: Release 11.1.0.7.0- Production on Mon Dec 921:11:292013
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> Connected.
SQL> SQL> col file_name format a80
SQL> col name format a100
SQL> setlinesize 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
13rows selected.
SQL> select name from v$archived_log where name isnot null;
no rows selected
SQL> setecho 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用户创建表报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
oracle@fly007:~> sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0- Production on Mon Dec 921:11:522013
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> conn sys/oracle assysdba
Connected.
SQL> conn fly/fly
Connected.
SQL> create table fly008 asselect * from dba_objects;
create table fly008 asselect * from dba_objects
*
ERROR at line 1:
ORA-01116: error inopening 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通过监听连接过来的进程

1
2
3
4
5
6
7
oracle@fly007:~> lsnrctl stop
LSNRCTL forLinux: Version 11.1.0.7.0- Production on 09-DEC-201321: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,查看哪些数据文件被删除了,拷贝被删除的数据文件到原来的位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
oracle@fly007:~> ps aux | grep dbw0 | grep -v grep
oracle    12570.04.53431904366532?      Ss   15:360:01ora_dbw0_apple
oracle@fly007:~> cd /proc/1257/fd
oracle@fly007:/proc/1257/fd> ls -l | grep delete
lrwx------ 1oracle oinstall 642013-12-0915:5510-> /home/oracle/product/11g/db/dbs/lkinstapple (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5522-> /home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5523-> /home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5524-> /home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5525-> /home/oracle/oradata/APPLE/datafile/o1_mf_users_8mfvsbdd_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5526-> /home/oracle/oradata/APPLE/datafile/o1_mf_example_8mfvwlmk_.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5528-> /home/oracle/oradata/APPLE/datafile/example02.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5529-> /home/oracle/oradata/APPLE/datafile/example03.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5530-> /home/oracle/oradata/APPLE/datafile/example04.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5531-> /home/oracle/oradata/APPLE/datafile/example05.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5532-> /home/oracle/oradata/APPLE/datafile/users02.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5533-> /home/oracle/oradata/APPLE/datafile/system03.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0915:5534-> /home/oracle/oradata/APPLE/datafile/sysaux03.dbf (deleted)
lrwx------ 1oracle oinstall 642013-12-0921:1537-> /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  921:18:302013
root        450.00.000?        S<   Jul24   0:00[kacpid]
root        460.00.000?        S<   Jul24   0:00[kacpi_notify]
root      28580.00.02684520?        Ss   Jul24   0:00/sbin/acpid
root      30520.00.06080700?        S    Jul24   0:00hald-addon-acpi
root      74440.00.0203562088?        S    Jul24   0:01/usr/sbin/powersaved -d -f /var/run/acpid.socket -v 3
oracle   101232.30.05948712pts/0D    21:160:02cp 23/home/oracle/oradata/APPLE/datafile/o1_mf_sysaux_8mfvsb9t_.dbf
oracle   101242.50.05948712pts/0D    21:160:02cp 24/home/oracle/oradata/APPLE/datafile/o1_mf_undotbs1_8mfvsbbx_.dbf
oracle   102020.00.064001356pts/0S+   21:180:00watch -n 1ps aux | grep cp
oracle   102060.00.091681532pts/0S+   21:180:00sh -c ps aux | grep cp
oracle   102080.00.03976804pts/0S+   21:180:00grep cp

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> conn sys/oracle assysdba
Connected.
SQL> shutdown immediate
ORA-01122: database file 1failed verification check
ORA-01110: data file 1: '/home/oracle/oradata/APPLE/datafile/o1_mf_system_8mfvsb5r_.dbf'
ORA-01208: data file isan 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 3240239104bytes
Fixed Size                  2164048bytes
Variable Size            2499807920bytes
Database Buffers          721420288bytes
Redo Buffers               16846848bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>

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

1
2
3
4
5
6
SQL> conn fly/fly
Connected.
SQL> select count(*) from fly;
COUNT(*)
----------
2256800

本文出自 “斜阳悠悠寸草心” 博客,请务必保留此出处http://fly1116.blog.51cto.com/8301004/1337681

本文由 CentOS中文站 - 专注Linux技术 作者:centos 发表,其版权均为 CentOS中文站 - 专注Linux技术 所有,文章内容系作者个人观点,不代表 CentOS中文站 - 专注Linux技术 对观点赞同或支持。如需转载,请注明文章来源。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注