Study Oracle

孑孓宝贝
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

linux删除数据文件无备份恢复

Posted on 2014-01-29 13:38  Ziyoo  阅读(446)  评论(0编辑  收藏  举报

参考 : http://www.lunar2013.com/2013/06/linux-%E8%AF%AF%E5%88%A0%E9%99%A4%E6%96%87%E4%BB%B6%E6%81%A2%E5%A4%8D.html

Last login: Sun Jan 26 22:36:10 2014 from 10.10.10.5

root>/root#su - oracle

oracle>/home/oracle$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 29 02:32:35 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

--查看表空间信息

SYS@ora10gr2>select tablespace_name from dba_tablespaces ;

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
-- 查看数据文件信息
SYS@ora10gr2>select name from v$datafile ;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
--创建测试表空间
SYS@ora10gr2>create tablespace ziyoo_test datafile '/home/oracle/ziyoo_test01.dbf' size 10m ;

Tablespace created.
--创建测试用户
SYS@ora10gr2>create user ziyoo default tablespace ziyoo_test identified by "oracle_4U" ;

User created.
--授予用户权限
SYS@ora10gr2>grant connect ,resource to ziyoo ;

Grant succeeded.
--以测试用户登录
SYS@ora10gr2>conn ziyoo
Enter password:
Connected.
--创建测试表
ZIYOO@ora10gr2>create table test_ziyoo ( name varchar2(300)) tablespace ziyoo_test;

Table created.
--插入测试数据
ZIYOO@ora10gr2>insert into test_ziyoo values ('Love you foreve,my baby,Happy new Year!') ;

1 row created.

ZIYOO@ora10gr2>commit ;

Commit complete.

ZIYOO@ora10gr2>select * from test_ziyoo ;

NAME
--------------------------------------------------------------------------------
Love you foreve,my baby,Happy new Year!
--删除数据文件
ZIYOO@ora10gr2>! rm -rf /home/oracle/ziyoo_test01.dbf
--确认是否删除
ZIYOO@ora10gr2>! ls -l /home/oracle/ziyoo_test01.dbf
ls: /home/oracle/ziyoo_test01.dbf: No such file or directory

ZIYOO@ora10gr2>conn ziyoo
Enter password:
Connected.
--查询数据
ZIYOO@ora10gr2>select * from test_ziyoo ;

NAME
--------------------------------------------------------------------------------
Love you foreve,my baby,Happy new Year!

ZIYOO@ora10gr2>conn / as sysdba
Connected.
--上面有数据是由于data buffer的原因,清空缓存
SYS@ora10gr2>alter system flush buffer_cache ;

System altered.

SYS@ora10gr2>conn ziyoo
Enter password:
Connected.
--验证数据丢失
ZIYOO@ora10gr2>select * from test_ziyoo ;
select * from test_ziyoo
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/ziyoo_test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

-- 获取dbwr进程信息
ZIYOO@ora10gr2>! ps -ef | grep dbw
oracle    6400     1  0 02:49 ?        00:00:01 ora_dbw0_ora10gr2
oracle    6523  6388 12 02:58 pts/1    00:00:00 /bin/bash -c  ps -ef | grep dbw
oracle    6525  6523  6 02:58 pts/1    00:00:00 grep dbw

--lsof 显示系统打开的文件
ZIYOO@ora10gr2>!/usr/sbin/lsof -p 6400

COMMAND  PID   USER   FD   TYPE DEVICE       SIZE    NODE NAME
oracle  6400 oracle  cwd    DIR    8,3       4096  163398 /u01/app/oracle/product/10.2/db_1/dbs
oracle  6400 oracle  rtd    DIR    8,3       4096       2 /
oracle  6400 oracle  txt    REG    8,3   96725784  174475 /u01/app/oracle/product/10.2/db_1/bin/oracle
oracle  6400 oracle  mem    REG    8,3     112388 1060803 /lib/ld-2.3.4.so
oracle  6400 oracle  mem    REG    8,3    1551908   81891 /lib/tls/libc-2.3.4.so
oracle  6400 oracle  mem    REG    8,3      16808 1060804 /lib/libdl-2.3.4.so
oracle  6400 oracle  mem    REG    8,3     215180   81892 /lib/tls/libm-2.3.4.so
oracle  6400 oracle  mem    REG    8,3     108536   81893 /lib/tls/libpthread-2.3.4.so
oracle  6400 oracle  mem    REG    8,3     101924 1060811 /lib/libnsl-2.3.4.so
oracle  6400 oracle  DEL    REG    0,6                  0 /SYSVa0a094cc
oracle  6400 oracle  mem    CHR    1,5               2777 /dev/zero
oracle  6400 oracle  mem    REG    8,3       1544  174538 /u01/app/oracle/product/10.2/db_1/dbs/hc_ora10gr2.dat
oracle  6400 oracle  mem    REG    8,3      47488  538610 /lib/libnss_files-2.3.4.so
oracle  6400 oracle  mem    REG    8,3      14104 1872904 /usr/lib/libnuma.so.1
oracle  6400 oracle  mem    REG    8,3       2628 1876353 /usr/lib/libaio.so.1.0.1
oracle  6400 oracle  mem    REG    8,3    5656445  173189 /u01/app/oracle/product/10.2/db_1/lib/libnnz10.so
oracle  6400 oracle  mem    REG    8,3     102317  167881 /u01/app/oracle/product/10.2/db_1/lib/libdbcfg10.so
oracle  6400 oracle  mem    REG    8,3      71921  172640 /u01/app/oracle/product/10.2/db_1/lib/libclsra10.so
oracle  6400 oracle  mem    REG    8,3    9082654  165671 /u01/app/oracle/product/10.2/db_1/lib/libjox10.so
oracle  6400 oracle  mem    REG    8,3     756215  172606 /u01/app/oracle/product/10.2/db_1/lib/libocrutl10.so
oracle  6400 oracle  mem    REG    8,3    1314712  172605 /u01/app/oracle/product/10.2/db_1/lib/libocrb10.so
oracle  6400 oracle  mem    REG    8,3     914160  172604 /u01/app/oracle/product/10.2/db_1/lib/libocr10.so
oracle  6400 oracle  mem    REG    8,3       8069  167992 /u01/app/oracle/product/10.2/db_1/lib/libskgxn2.so
oracle  6400 oracle  mem    REG    8,3    2425042  172639 /u01/app/oracle/product/10.2/db_1/lib/libhasgen10.so
oracle  6400 oracle  mem    REG    8,3     149502  164651 /u01/app/oracle/product/10.2/db_1/lib/libskgxp10.so
oracle  6400 oracle    0r   CHR    1,3               2776 /dev/null
oracle  6400 oracle    1r   CHR    1,3               2776 /dev/null
oracle  6400 oracle    2r   CHR    1,3               2776 /dev/null
oracle  6400 oracle    3r   CHR    1,3               2776 /dev/null
oracle  6400 oracle    4r   CHR    1,3               2776 /dev/null
oracle  6400 oracle    5w   REG    8,3        680  962968 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_6389.trc
oracle  6400 oracle    6w   REG    8,3     110983  343618 /u01/app/oracle/admin/ora10gr2/bdump/alert_ora10gr2.log
oracle  6400 oracle    7u   REG    8,3          0  174533 /u01/app/oracle/product/10.2/db_1/dbs/lkinstora10gr2 (deleted)
oracle  6400 oracle    8w   REG    8,3     110983  343618 /u01/app/oracle/admin/ora10gr2/bdump/alert_ora10gr2.log
oracle  6400 oracle    9u   REG    8,3       1544  174538 /u01/app/oracle/product/10.2/db_1/dbs/hc_ora10gr2.dat
oracle  6400 oracle   10r   CHR    1,5               2777 /dev/zero
oracle  6400 oracle   11r   CHR    1,5               2777 /dev/zero
oracle  6400 oracle   12r   REG    8,3     849408  172673 /u01/app/oracle/product/10.2/db_1/rdbms/mesg/oraus.msb
oracle  6400 oracle   13u   REG    8,3       1544  174538 /u01/app/oracle/product/10.2/db_1/dbs/hc_ora10gr2.dat
oracle  6400 oracle   14uR  REG    8,3         24  174539 /u01/app/oracle/product/10.2/db_1/dbs/lkORA10GR2
oracle  6400 oracle   15u   REG    8,3    7356416  343496 /u01/app/oracle/oradata/orcl/control01.ctl
oracle  6400 oracle   16u   REG    8,3    7356416  343497 /u01/app/oracle/oradata/orcl/control02.ctl
oracle  6400 oracle   17u   REG    8,3    7356416  343498 /u01/app/oracle/oradata/orcl/control03.ctl
oracle  6400 oracle   18uW  REG    8,3  513810432  343490 /u01/app/oracle/oradata/orcl/system01.dbf
oracle  6400 oracle   19uW  REG    8,3  183508992  343492 /u01/app/oracle/oradata/orcl/undotbs01.dbf
oracle  6400 oracle   20uW  REG    8,3  251666432  343491 /u01/app/oracle/oradata/orcl/sysaux01.dbf
oracle  6400 oracle   21uW  REG    8,3 2111578112  343493 /u01/app/oracle/oradata/orcl/users01.dbf
oracle  6400 oracle   22uW  REG    8,3   31465472  343510 /u01/app/oracle/oradata/orcl/temp01.dbf
oracle  6400 oracle   23r   REG    8,3     849408  172673 /u01/app/oracle/product/10.2/db_1/rdbms/mesg/oraus.msb
oracle  6400 oracle   24uW  REG    8,3   10493952  571688 /home/oracle/ziyoo_test01.dbf (deleted)

-- 信号量信息
ZIYOO@ora10gr2>! ls -ltar /proc/6400/fd/
total 25
dr-xr-xr-x  5 oracle oinstall  0 Jan 29 02:49 ..
dr-x------  2 oracle oinstall  0 Jan 29 02:59 .
lr-x------  1 oracle oinstall 64 Jan 29 02:59 0 -> /dev/null
lr-x------  1 oracle oinstall 64 Jan 29 02:59 1 -> /dev/null
lr-x------  1 oracle oinstall 64 Jan 29 02:59 3 -> /dev/null
lr-x------  1 oracle oinstall 64 Jan 29 02:59 2 -> /dev/null
l-wx------  1 oracle oinstall 64 Jan 29 02:59 5 -> /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_6389.trc
lr-x------  1 oracle oinstall 64 Jan 29 02:59 4 -> /dev/null
l-wx------  1 oracle oinstall 64 Jan 29 02:59 6 -> /u01/app/oracle/admin/ora10gr2/bdump/alert_ora10gr2.log
l-wx------  1 oracle oinstall 64 Jan 29 02:59 8 -> /u01/app/oracle/admin/ora10gr2/bdump/alert_ora10gr2.log
lrwx------  1 oracle oinstall 64 Jan 29 02:59 7 -> /u01/app/oracle/product/10.2/db_1/dbs/lkinstora10gr2 (deleted)
lrwx------  1 oracle oinstall 64 Jan 29 02:59 9 -> /u01/app/oracle/product/10.2/db_1/dbs/hc_ora10gr2.dat
lr-x------  1 oracle oinstall 64 Jan 29 02:59 10 -> /dev/zero
lr-x------  1 oracle oinstall 64 Jan 29 02:59 12 -> /u01/app/oracle/product/10.2/db_1/rdbms/mesg/oraus.msb
lr-x------  1 oracle oinstall 64 Jan 29 02:59 11 -> /dev/zero
lrwx------  1 oracle oinstall 64 Jan 29 02:59 15 -> /u01/app/oracle/oradata/orcl/control01.ctl
lrwx------  1 oracle oinstall 64 Jan 29 02:59 14 -> /u01/app/oracle/product/10.2/db_1/dbs/lkORA10GR2
lrwx------  1 oracle oinstall 64 Jan 29 02:59 13 -> /u01/app/oracle/product/10.2/db_1/dbs/hc_ora10gr2.dat
lrwx------  1 oracle oinstall 64 Jan 29 02:59 17 -> /u01/app/oracle/oradata/orcl/control03.ctl
lrwx------  1 oracle oinstall 64 Jan 29 02:59 16 -> /u01/app/oracle/oradata/orcl/control02.ctl
lrwx------  1 oracle oinstall 64 Jan 29 02:59 19 -> /u01/app/oracle/oradata/orcl/undotbs01.dbf
lrwx------  1 oracle oinstall 64 Jan 29 02:59 18 -> /u01/app/oracle/oradata/orcl/system01.dbf
lrwx------  1 oracle oinstall 64 Jan 29 02:59 21 -> /u01/app/oracle/oradata/orcl/users01.dbf
lrwx------  1 oracle oinstall 64 Jan 29 02:59 20 -> /u01/app/oracle/oradata/orcl/sysaux01.dbf
lr-x------  1 oracle oinstall 64 Jan 29 02:59 23 -> /u01/app/oracle/product/10.2/db_1/rdbms/mesg/oraus.msb
lrwx------  1 oracle oinstall 64 Jan 29 02:59 22 -> /u01/app/oracle/oradata/orcl/temp01.dbf
lrwx------  1 oracle oinstall 64 Jan 29 02:59 24 -> /home/oracle/ziyoo_test01.dbf (deleted)

ZIYOO@ora10gr2>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


oracle>/home/oracle$cd /proc/6400/fd/
oracle>/proc/6400/fd$ls
0  1  10  11  12  13  14  15  16  17  18  19  2  20  21  22  23  24  3  4  5  6  7  8  9

--读取数据
oracle>/proc/6400/fd$cat 24 > ~/ziyoo01.dbf
oracle>/proc/6400/fd$ls -l /home/oracle/
total 45888
-rw-r--r--  1 oracle oinstall      711 Jan 26 04:35 afiedt.buf
drwxr-xr-x  2 oracle oinstall     4096 Oct 18 07:22 Desktop
-rwxr-xr-x  1 oracle oinstall     5742 Jan 14 02:34 netca.rsp
-rw-r--r--  1 oracle oinstall  8591630 Jan  2 11:30 oracle.txt
-rw-r--r--  1 oracle oinstall 27337709 Jan 26 02:47 patch.log
-rw-r--r--  1 oracle oinstall   178473 Feb 26  2008 README.html
drwxrwxr-x  8    500 dba          4096 Oct 18 11:18 rlwrap-0.37
-rw-r--r--  1 oracle oinstall   251438 Oct 18 11:16 rlwrap-0.37.tar.gz
-rw-r--r--  1 oracle oinstall    22172 Jan 14 03:25 test_clob.txt
-rw-r--r--  1 oracle oinstall     5239 Jan 25 23:46 upgrade_info.log
-rw-r--r--  1 oracle oinstall 10493952 Jan 29 03:02 ziyoo01.dbf
oracle>/proc/6400/fd$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 29 03:02:37 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SYS@ora10gr2>alter database datafile '/home/oracle/ziyoo_test01.dbf' offline ;

Database altered.

-- 更新控制文件信息
SYS@ora10gr2>alter database rename file '/home/oracle/ziyoo_test01.dbf' to '/home/oracle/ziyoo01.dbf' ;

Database altered.

SYS@ora10gr2>alter database datafile '/home/oracle/ziyoo01.dbf' online ;
alter database datafile '/home/oracle/ziyoo01.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/home/oracle/ziyoo01.dbf'


SYS@ora10gr2>recover datafile 5 ;
Media recovery complete.
SYS@ora10gr2>alter database datafile 5 online ;

Database altered.

SYS@ora10gr2>conn ziyoo
Enter password:
Connected.
ZIYOO@ora10gr2>select * from tab ;

TNAME                                                        TABTYPE
------------------------------------------------------------ --------------
 CLUSTERID
----------
TEST_ZIYOO                                                   TABLE

--查询恢复后的数据
ZIYOO@ora10gr2>select * from test_ziyoo ;

NAME
--------------------------------------------------------------------------------
Love you foreve,my baby,Happy new Year!

hello