关于linux中oracle用户进程占用内存猜测

Posted on 2012 年 04 月 28 日 by 惜分飞

本文是针对linux下面显示oracle用户进程占用大量内存的一个猜想性说明,希望各位专家和我一起继续探讨该问题
ORACLE用户进程占用私有内存分析
top命令结果

[oracle@ora02 31500]$ top -c

top - 12:13:16 up 254 days, 12:14,  2 users,  load average: 1.53, 1.62, 1.33
Tasks: 293 total,   3 running, 290 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.4% us,  0.8% sy,  0.0% ni, 94.7% id,  1.1% wa,  0.0% hi,  0.0% si
Mem:   4147172k total,  4129724k used,    17448k free,    20348k buffers
Swap:  4192956k total,   217772k used,  3975184k free,  2575320k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                   
12505 oracle    17   0 1561m 972m 970m R  100 24.0   3:45.89 oracletxzldb (LOCAL=NO)                                                                                   
12475 oracle    16   0 1561m 931m 929m S    4 23.0   4:08.65 oracletxzldb (LOCAL=NO)                                                                                   
12477 oracle    16   0 1561m 945m 942m S    2 23.3   3:36.92 oracletxzldb (LOCAL=NO)                                                                                   
12479 oracle    16   0 1561m 944m 941m S    2 23.3   4:27.29 oracletxzldb (LOCAL=NO)                                                                                   
12483 oracle    16   0 1561m 939m 937m R    2 23.2   3:49.00 oracletxzldb (LOCAL=NO)                                                                                   
12493 oracle    16   0 1561m 958m 955m S    2 23.7   3:35.35 oracletxzldb (LOCAL=NO)

补充说明:

VIRT	进程使用的虚拟内存总量
RES	进程使用的、未被换出的物理内存大小
SHR	共享内存大小

通过这里可以得出几个信息
12505进程实际使用内存情况:972-970=2M
12505进程%MEM:972*1024/4147172=0.24000162
12505进程在数据库中占用内存

SQL> select PGA_ALLOC_MEM/1024/1024 MEM from v$process where spid=12505;

       MEM
----------
1.90997028

通过这里可以看出12505进程实际上是占用了970M的共享内存,占用2M的PGA内存

ORACLE用户进程占用共享内存分析
分析12505进程的当前进程状态

[oracle@ora02 31500]$ cd /proc/12505
[oracle@ora02 12505]$ cat status
Name:   oracle
State:  S (sleeping)
SleepAVG:       92%
Tgid:   12505
Pid:    12505
PPid:   1
TracerPid:      0
Uid:    501     501     501     501
Gid:    502     502     502     502
FDSize: 32
Groups: 501 502 
VmSize:  1599004 kB    <--使用内存(包括虚拟内存)总量1599004/1024=1561.52734和top中VIRT基本吻合
VmLck:         0 kB
VmRSS:    996132 kB    <--实际使用内存996132/1024 =972.785156和top看到RES基本吻合
VmData:      832 kB
VmStk:       120 kB
VmExe:     37307 kB
VmLib:      4641 kB
StaBrk: 0ad6e000 kB
Brk:    0adf2000 kB
StaStk: bffff850 kB
ExecLim:        ffffffff
Threads:        1
SigPnd: 0000000000000000
ShdPnd: 0000000000000000
SigBlk: 0000000000000000
SigIgn: 0000000006005203
SigCgt: 00000001c9802cfc
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000

pmap命令分析

[oracle@ora02 12505]$ pmap -d 12505
12505:   oracletxzldb (LOCAL=NO)
Address   Kbytes Mode  Offset           Device    Mapping
0013f000      88 r-x-- 0000000000000000 008:00002 ld-2.3.4.so
00155000       4 r-x-- 0000000000015000 008:00002 ld-2.3.4.so
00156000       4 rwx-- 0000000000016000 008:00002 ld-2.3.4.so
00159000    1176 r-x-- 0000000000000000 008:00002 libc-2.3.4.so
0027f000       8 r-x-- 0000000000125000 008:00002 libc-2.3.4.so
00281000       8 rwx-- 0000000000127000 008:00002 libc-2.3.4.so
00283000       8 rwx-- 0000000000283000 000:00000   [ anon ]
00287000     132 r-x-- 0000000000000000 008:00002 libm-2.3.4.so
002a8000       4 r-x-- 0000000000020000 008:00002 libm-2.3.4.so
002a9000       4 rwx-- 0000000000021000 008:00002 libm-2.3.4.so
002ac000       8 r-x-- 0000000000000000 008:00002 libdl-2.3.4.so
002ae000       4 r-x-- 0000000000001000 008:00002 libdl-2.3.4.so
002af000       4 rwx-- 0000000000002000 008:00002 libdl-2.3.4.so
003b5000      56 r-x-- 0000000000000000 008:00002 libpthread-2.3.4.so
003c3000       4 r-x-- 000000000000d000 008:00002 libpthread-2.3.4.so
003c4000       4 rwx-- 000000000000e000 008:00002 libpthread-2.3.4.so
003c5000       8 rwx-- 00000000003c5000 000:00000   [ anon ]
00ba4000      72 r-x-- 0000000000000000 008:00002 libnsl-2.3.4.so
00bb6000       4 r-x-- 0000000000011000 008:00002 libnsl-2.3.4.so
00bb7000       4 rwx-- 0000000000012000 008:00002 libnsl-2.3.4.so
00bb8000       8 rwx-- 0000000000bb8000 000:00000   [ anon ]
08048000   37308 r-x-- 0000000000000000 0fd:00001 oracle
0a4b7000    8804 rwx-- 000000000246f000 0fd:00001 oracle
0ad50000     648 rwx-- 000000000ad50000 000:00000   [ anon ]
50000000 1540096 rwxs- 0000000000000000 000:00006   [ shmid=0x9000e ]
ae000000       4 r-xs- 000000005e000000 000:00006   [ shmid=0x9000e ]
ae001000    1156 rwxs- 000000005e001000 000:00006   [ shmid=0x9000e ]
ae122000       4 r-xs- 000000005e122000 000:00006   [ shmid=0x9000e ]
ae123000    2932 rwxs- 000000005e123000 000:00006   [ shmid=0x9000e ]
b79d4000    1024 rwx-- 00000000000f4000 000:0000d zero
b7ad4000     512 rwx-- 0000000000074000 000:0000d zero
b7b54000     512 rwx-- 0000000000000000 000:0000d zero
b7bd4000      36 r-x-- 0000000000000000 008:00002 libnss_files-2.3.4.so
b7bdd000       4 r-x-- 0000000000008000 008:00002 libnss_files-2.3.4.so
b7bde000       4 rwx-- 0000000000009000 008:00002 libnss_files-2.3.4.so
b7bdf000     148 rwx-- 00000000b7bdf000 000:00000   [ anon ]
b7c04000    2940 r-x-- 0000000000000000 0fd:00001 libjox9.so
b7ee3000    1088 rwx-- 00000000002de000 0fd:00001 libjox9.so
b7ff3000       8 rwx-- 00000000b7ff3000 000:00000   [ anon ]
b7ff5000       4 r-x-- 0000000000000000 0fd:00001 libskgxn9.so
b7ff6000       8 rwx-- 0000000000000000 0fd:00001 libskgxn9.so
b7ff8000       4 r-x-- 0000000000000000 0fd:00001 libskgxp9.so
b7ff9000       4 --x-- 0000000000001000 0fd:00001 libskgxp9.so
b7ffa000       4 rwx-- 0000000000001000 0fd:00001 libskgxp9.so
b7ffb000       4 r-x-- 0000000000000000 0fd:00001 libodmd9.so
b7ffc000       4 rwx-- 0000000000000000 0fd:00001 libodmd9.so
b7ffd000       4 r-x-- 0000000000000000 008:00002 libcwait.so
b7ffe000       4 rwx-- 0000000000000000 008:00002 libcwait.so
b7fff000       4 rwx-- 00000000b7fff000 000:00000   [ anon ]
bffe2000     120 rwx-- 00000000bffe2000 000:00000   [ stack ]
ffffe000       4 ----- 0000000000000000 000:00000   [ anon ]
mapped: 1599008K    writeable/private: 12944K    shared: 1544192K

补充说明:

mapped ：映射到文件的内存数量
writable/private ：进程所占用的私有地址空间数量
shared ：与其它进程共享的地址空间数量

ipcs 命令

[oracle@ora02 12505]$ ipcs -m

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x0000cace 65536      root      666        2          0                       
0x4d4e5251 98305      root      644        330752     0                       
0x55315352 131074     root      666        4096       0                       
0x44525354 163843     root      644        632832     0                       
0x53494152 196612     root      644        1024       0                       
0x00005643 229381     root      666        1024       1                       
0x00005654 262150     root      666        1024       1                       
0x992ad3dc 589838     oracle    640        1581252608 595

结合pmap和ipcs分析(shmid=0x9000e)

SQL> select to_number('9000e','xxxxxxxx') from dual;

TO_NUMBER('9000E','XXXXXXXX')
-----------------------------
                       589838

SQL> select 1540096+4+1156+4+2932 from dual;

1540096+4+1156+4+2932
---------------------
              1544192

SQL> select 1581252608/1024 from dual;

1581252608/1024
---------------
        1544192

通过这里可以得出12505进程中的共享内存,主要是数据库SGA中的共享内存

补充猜测

SQL> show sga;

Total System Global Area 1561926292 bytes
Fixed Size                   453268 bytes
Variable Size             603979776 bytes
Database Buffers          956301312 bytes
Redo Buffers                1191936 bytes
SQL> select 1561926292/1024 from dual;

1561926292/1024
---------------
     1525318.64

这里显示数据库配置的sga比ipcs中配置共享内存段小,但是进程在分配总的共享内存时候,使用的是ipcs设定的内存段大小,实际使用的内存可能是sga设置大小(未得到权威资料)

gzexe加密shell脚本

Posted on 2012 年 03 月 10 日 by 惜分飞

很多时候，我们的脚本中涉及的用户名/密码/ip等敏感信息，我们需要使用一些加密方面来屏蔽这些信息，确保我们的系统安全(主要防菜鸟)，虽然shc可以实现强大的加密功能，但是他需要另外安装shc软件，比较麻烦，应对一般的加密，个人推荐直接喜用系统自带的gzexe(大部分Linux，unix都有)

[root@xifenfei tmp]# more xifenfei.sh 
#!/usr/bin/ksh
echo 'xifenfei--'`date`

[root@xifenfei tmp]# ./xifenfei.sh 
xifenfei--Tue Mar 6 13:31:35 CST 2012
[root@xifenfei tmp]# ls -l xifenfei.sh
-rwxr-xr-x 1 root root 40 Mar  6 13:30 xifenfei.sh

--加密操作
[root@xifenfei tmp]# gzexe xifenfei.sh 
xifenfei.sh:     -5.0%
[root@xifenfei tmp]# ls -l xifenfei.sh*
-rwxr-xr-x 1 root root 449 Mar  6 13:31 xifenfei.sh
-rwxr-xr-x 1 root root  40 Mar  6 13:30 xifenfei.sh~

--加密后文件内容
[root@xifenfei tmp]# strings xifenfei.sh
#!/bin/sh
skip=14
tmpdir=`/bin/mktemp -d ${TMPDIR:-/tmp}/gzexe.XXXXXXXXXX` || exit 1
prog="${tmpdir}/`echo \"$0\" | sed 's|^.*/||'`"
if /usr/bin/tail -n +$skip "$0" | "/bin"/gzip -cd > "$prog"; then
  /bin/chmod 700 "$prog"
  trap '/bin/rm -rf $tmpdir; exit $res' EXIT
  "$prog" ${1+"$@"}; res=$?
else
  echo "Cannot decompress $0"
  /bin/rm -rf $tmpdir
  exit 1
fi; exit $res
xifenfei.sh
UOHI,IM

这里的xifenfei.sh是加密后文件，xifenfei.sh~是源码文件(加密前文件)，只需要运行xifenfie.sh即可实现原脚本功能
优点:在大部分系统上均可运行，不用安装额外软件
缺点:破解容易,放君子不防小人

shell处理alert日志

Posted on 2012 年 02 月 12 日 by 惜分飞

每天都检查oracle日志，所以写了一个比较完善的shell，让其自动处理，在运行程序之前，需要在该脚本目录下新建tmp目录

#!/usr/bin/ksh
export LANG=en

#临时目录
tmp_dest=$HOME/check/tmp
#用户名
username=username
#密码
password=password

cd $tmp_dest
sqlplus -s $username/$password<<XFF>/dev/null
set echo on
spool sqlplus.txt
col name format a20
col value format a55
select name,value from v\$parameter where name='background_dump_dest';
spool off
exit
XFF

alert_path_num=$(grep -n "background_dump_dest" $tmp_dest/sqlplus.txt |awk -F":" '{print $1}')
alert_path=$(cat $tmp_dest/sqlplus.txt |sed -n "${alert_path_num}p" | awk -F" " '{print $2}')

first_day=`cat $tmp_dest/first_day.tmp`
d_day=$(date +%e)
if [ $d_day -lt 10 ]
then
###########注意:以下两种方式选择其一###################
#部分系统出现alert日志例如:Tue Aug  7 07:44:59 2012
 last_day=$(date +%a)' '$(date +%b)'.*'$d_day'.*'$(date +%Y)

#部分系统出现alert日志例如:Thu Jun 07 13:56:18 2012
 n_day=`echo $d_day | awk 'gsub(/^ *| *$/,"")'`
 last_day=$(date +%a)' '$(date +%b)'.*0'$n_day'.*'$(date +%Y)

else
 last_day=$(date|cut -c 1-10).*$(date +%Y)
fi
echo $last_day > $tmp_dest/first_day.tmp
first_num=$(grep -n "$first_day" $alert_path/alert_$ORACLE_SID.log |head -1|awk -F":" '{print $1}')
if [ -z "$first_num" ]
then
   first_num=1
fi

#修改$last_day为'.*',表示alert日志结尾
last_num=$(grep -n ".*" $alert_path/alert_$ORACLE_SID.log |tail -1|awk -F":" '{print $1}')

point=1
export=$point

echo "########################## checking alert_log start $first_day ########################## "

sed -n "${first_num},${last_num}p" $alert_path/alert_$ORACLE_SID.log > $tmp_dest/trunc_alert
cat $tmp_dest/trunc_alert|grep ORA-|while read line
do
   line=$(echo "$line"|sed -e 's/\[/\\[/g;s/\]/\\]/g')
   time=$(grep -n "$line" $tmp_dest/trunc_alert | awk -F':' '{print $1}'|wc -l)

if [ "$time" -ge 1 ]
then
   num=$(grep -n "$line" $tmp_dest/trunc_alert|awk -F':' '{print $1}'|tail -1)
   #echo $num
   front_num=$((num-1))
   back_num=$((num+9))

   echo "++++++++++++++++++$point++++++++++++++++++++"
    sed -n "${front_num},${back_num}p" $tmp_dest/trunc_alert
   echo "++++++++++++++++++$point-End++++++++++++++++"
   point=$((point+1))

   flag=1
else
     if [ -z "$time" ]
     then
     flag=0
     fi
fi
done

#rm $tmp_dest/trunc_alert
#rm $tmp_dest/sqlplus.txt

if [ " $flag " -eq 0 ]
   then
   echo "No errors in $first_day !"
fi
echo "########################## checking alert_log end $last_day ########################## "

检查结果

########################## checking alert_log start Fri Jan 27.*2012 ########################## 
++++++++++++++++++1++++++++++++++++++++
  Current log# 3 seq# 918 mem# 0: /opt/oracle/oradata/orcl/mcrm/redo03.log
Tue Jan 31 22:00:22 2012
Errors in file /opt/oracle/admin/mcrm/bdump/mcrm_j001_23329.trc:
ORA-01114: IO error writing block to file 201 (block # 550944)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 550944
Additional information: -1
ORA-01114: IO error writing block to file 201 (block # 550922)
ORA-27072: File I/O error
Additional information: 4
Additional information: 550922
++++++++++++++++++1-End++++++++++++++++
++++++++++++++++++2++++++++++++++++++++
Additional information: 550944
Additional information: -1
ORA-01114: IO error writing block to file 201 (block # 550922)
ORA-27072: File I/O error
Additional information: 4
Additional information: 550922
Additional information: 114688
Wed Feb  1 07:24:02 2012
Thread 1 advanced to log sequence 919 (LGWR switch)
  Current log# 1 seq# 919 mem# 0: /opt/oracle/oradata/orcl/mcrm/redo01.log
Wed Feb  1 07:24:02 2012
Errors in file /opt/oracle/admin/mcrm/bdump/mcrm_arc1_3624.trc:
ORA-19504: failed to create file "/opt/oracle/oradata/archivelog/1_918_741262746.dbf"
++++++++++++++++++2-End++++++++++++++++
########################## checking alert_log end Sun Feb 12.*2012##########################

dd操作数据文件

Posted on 2012 年 01 月 13 日 by 惜分飞

1.dd主要参数

       Copy a file, converting and formatting according to the operands.

       bs=BYTES
              force ibs=BYTES and obs=BYTES

       cbs=BYTES
              convert BYTES bytes at a time

       count=BLOCKS
              copy only BLOCKS input blocks

       ibs=BYTES
              read BYTES bytes at a time

       if=FILE
              read from FILE instead of stdin

       obs=BYTES
              write BYTES bytes at a time

       of=FILE
              write to FILE instead of stdout

       seek=BLOCKS
              skip BLOCKS obs-sized blocks at start of output

       skip=BLOCKS
              skip BLOCKS ibs-sized blocks at start of input

2.拷贝数据文件头部

[oracle@node1 chf]$ dd if=system01.dbf of=/tmp/dd_xifenfei01.dbf bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 6.2e-05 seconds, 132 MB/s
[oracle@node1 chf]$ ll /tmp/dd_xifenfei01.dbf
-rw-r--r-- 1 oracle oinstall 8192 01-13 16:49 /tmp/dd_xifenfei01.dbf


BBED> set filename '/tmp/dd_xifenfei01.dbf'
        FILENAME        /tmp/dd_xifenfei01.dbf

BBED> set block 1
        BLOCK#          1

BBED> dump
 File: ././dd_xifenfei01.dbf (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 00a20000 0000c0ff 00000000 00000000 64590000 00200000 00a30200 7d7c7b7a 
 a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> set block 2
BBED-00309: out of range block number (2)


BBED> set offset 8190
        OFFSET          8190

BBED> dump
 File: ././dd_xifenfei01.dbf (0)
 Block: 1                Offsets: 8190 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0000 

 <32 bytes per line>
--证明只有1个数据块(8192),并且拷贝数据文件头部成功

3.模拟创建文件

[oracle@node1 tmp]$ ll /tmp/dd_xifenfei02.dbf -h
-rw-r--r-- 1 oracle oinstall 1.0G 01-13 16:58 /tmp/dd_xifenfei02.dbf

4.拷贝数据块到一个文件中

[oracle@node1 chf]$ dd if=/tmp/dd_xifenfei01.dbf of=/tmp/dd_xifenfei02.dbf bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 6e-05 seconds, 137 MB/s
[oracle@node1 chf]$ bbed
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Fri Jan 13 17:01:02 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/tmp/dd_xifenfei02.dbf'

BBED> set block 1
        BLOCK#          1

BBED> dump
 File: ././dd_xifenfei02.dbf (0)
 Block: 1                Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
 00a20000 0000c0ff 00000000 00000000 64590000 00200000 00a30200 7d7c7b7a 
 a0810000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>


BBED> set block 2
BBED-00309: out of range block number (2)


BBED> set offset 8190
        OFFSET          8190

BBED> dump
 File: ././dd_xifenfei02.dbf (0)
 Block: 1                Offsets: 8190 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0000 

 <32 bytes per line>

通过对比在2步骤中拷贝出来的数据文件头部，证明现在已经把该头部拷贝到了3步骤创建的新文件中

恢复被rm意外删除数据文件

Posted on 2011 年 12 月 31 日 by 惜分飞

一.模拟数据文件删除

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 31 22:00:52 2011

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

--数据库版本
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

--所有数据文件
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ora11g/system01.dbf
/opt/oracle/oradata/ora11g/sysaux01.dbf
/opt/oracle/oradata/ora11g/undotbs01.dbf
/opt/oracle/oradata/ora11g/users01.dbf
/opt/oracle/oradata/ora11g/example01.dbf

--删除example01.dbf数据文件
SQL> !rm /opt/oracle/oradata/ora11g/example01.dbf

SQL> !ls -l /opt/oracle/oradata/ora11g/example01.dbf
ls: /opt/oracle/oradata/ora11g/example01.dbf: 没有那个文件或目录

--因为数据文件被删除，创建表失败
SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;
as select * from dba_tables
                 *
ERROR at line 2:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

二.找回数据文件

--查找dbw进程spid
[oracle@node1 ~]$ ps -ef|grep dbw|grep -v grep
oracle   18387     1  0 Dec22 ?        00:00:12 ora_dbw0_ora11g

--查看该进程所有文件句柄
[oracle@node1 ~]$ ll /proc/18387/fd
总计 0
lr-x------ 1 oracle oinstall 64 12-31 22:03 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 12-31 22:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 10 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 12 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 13 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 14 -> /proc/18387/fd
lr-x------ 1 oracle oinstall 64 12-31 22:03 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 16 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 17 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lrwx------ 1 oracle oinstall 64 12-31 22:03 18 -> /opt/oracle/product/11.2.0/db_1/dbs/lkORA11G
lr-x------ 1 oracle oinstall 64 12-31 22:03 19 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
l-wx------ 1 oracle oinstall 64 12-31 22:03 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 20 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 21 -> socket:[441562]
lrwx------ 1 oracle oinstall 64 12-31 22:03 256 -> /opt/oracle/oradata/ora11g/control01.ctl
lrwx------ 1 oracle oinstall 64 12-31 22:03 257 -> /opt/oracle/oradata/ora11g/system01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 258 -> /opt/oracle/oradata/ora11g/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 259 -> /opt/oracle/oradata/ora11g/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 260 -> /opt/oracle/oradata/ora11g/users01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 261 -> /opt/oracle/oradata/ora11g/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 12-31 22:03 262 -> /opt/oracle/oradata/ora11g/temp01.dbf
lr-x------ 1 oracle oinstall 64 12-31 22:03 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 4 -> /dev/null
lrwx------ 1 oracle oinstall 64 12-31 22:03 5 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 7 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 8 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 9 -> /dev/null
--通过句柄恢复数据文件[被删除数据文件会被标示(deleted)]
[oracle@node1 ~]$ cp /proc/18387/fd/261 /opt/oracle/oradata/ora11g/example01.dbf
--确认该数据文件已经恢复成功
[oracle@node1 ~]$ ll /opt/oracle/oradata/ora11g/example01.dbf
-rw-r----- 1 oracle oinstall 362422272 12-31 22:05 /opt/oracle/oradata/ora11g/example01.dbf

三.数据文件online

SQL> alter database datafile 5 offline;
 
Database altered.
 
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
 
Database altered.

SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;

Table created.

四.补充说明
在意外使用os命令删除掉数据文件时，千万不要慌张重启数据库或者操作系统，可以通过dbwn进程相关句柄找回数据文件