[oracle@lunar ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 15 23:46:35 2013 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, OLAP, Data Mining and Real Application Testing options SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EZIO 6 rows selected.
创建测试表空间
SQL> create tablespace lunar_test datafile '/test/lunar_test01.dbf' size 10M; Tablespace created. SQL> alter user lunar default tablespace lunar_test; User altered.
创建表插入数据
SQL> conn lunar/lunar Connected. SQL> create table test_table (name varchar2(300)) tablespace lunar_test; Table created. SQL> insert into test_table values ('Love you forever, my baby, Happy Childrens Day!'); 1 row created. SQL> commit; Commit complete. SQL> SQL> select * from test_table; NAME -------------------------------------------------------------------------------- Love you forever, my baby, Happy Childrens Day!
删除datafile
SQL> !rm -rf /test/lunar_test01.dbf SQL> !ls -lrt /test/lunar_test01.dbf ls: /test/lunar_test01.dbf: No such file or directory SQL>
数据还在,因为从buffer cache中读到的
SQL> conn lunar/lunar Connected. SQL> select * from test_table; NAME -------------------------------------------------------------------------------- Love you forever, my baby, Happy Childrens Day!
执行flush buffer cache
SQL> conn / as sysdba Connected. SQL> alter system flush buffer_cache; System altered.
可以看见,再次查询,报错文件状态不对了(找不到了)
SQL> conn lunar/lunar Connected. SQL> select * from test_table; select * from test_table * ERROR at line 1: ORA-01116: error in opening database file 6 ORA-01110: data file 6: '/test/lunar_test01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
检查dbwr进程的spid
SQL> !ps -ef|grep dbw oracle 2757 1 0 12:29 ? 00:00:14 ora_dbw0_bb oracle 8912 8872 0 23:50 pts/3 00:00:00 /bin/bash -c ps -ef|grep dbw oracle 8914 8912 0 23:50 pts/3 00:00:00 grep dbw
找到dbwr的句柄
SQL> !/usr/sbin/lsof -p 2757 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME oracle 2757 oracle cwd DIR 253,0 4096 6947181 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs oracle 2757 oracle rtd DIR 253,0 4096 2 / oracle 2757 oracle txt REG 253,0 232399473 1179781 /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle oracle 2757 oracle DEL REG 0,4 1933317 /SYSV00000000 oracle 2757 oracle DEL REG 0,4 1966086 /SYSV00000000 oracle 2757 oracle DEL REG 0,4 1998855 /SYSV2b7268c4 oracle 2757 oracle mem REG 253,0 144776 2293762 /lib64/ld-2.5.so oracle 2757 oracle mem REG 253,0 5328 4822547 /usr/lib64/libaio.so.1.0.1 oracle 2757 oracle mem REG 253,0 1722328 2293764 /lib64/libc-2.5.so oracle 2757 oracle mem REG 253,0 23360 2293771 /lib64/libdl-2.5.so oracle 2757 oracle mem REG 253,0 615136 2293809 /lib64/libm-2.5.so oracle 2757 oracle mem REG 253,0 145872 2293775 /lib64/libpthread-2.5.so oracle 2757 oracle mem REG 253,0 53448 2293781 /lib64/librt-2.5.so oracle 2757 oracle mem REG 253,0 114352 2293986 /lib64/libnsl-2.5.so oracle 2757 oracle mem REG 253,0 58949 6955646 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so oracle 2757 oracle mem CHR 1,5 4302 /dev/zero oracle 2757 oracle mem REG 253,0 1544 6951342 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat oracle 2757 oracle mem REG 253,0 53880 2293787 /lib64/libnss_files-2.5.so oracle 2757 oracle mem REG 253,0 22368 4831836 /usr/lib64/libnuma.so.1 oracle 2757 oracle mem REG 253,0 153574 6976596 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrutl11.so oracle 2757 oracle mem REG 253,0 3319072 6976595 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrb11.so oracle 2757 oracle mem REG 253,0 1590995 6976594 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocr11.so oracle 2757 oracle mem REG 253,0 12755 6953392 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxn2.so oracle 2757 oracle mem REG 253,0 17319952 6976562 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libhasgen11.so oracle 2757 oracle mem REG 253,0 161764 6953093 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libdbcfg11.so oracle 2757 oracle mem REG 253,0 228765 6976564 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclsra11.so oracle 2757 oracle mem REG 253,0 7955322 6976828 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnnz11.so oracle 2757 oracle mem REG 253,0 1010297 4456454 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxp11.so oracle 2757 oracle mem REG 253,0 589359 6977008 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libcell11.so oracle 2757 oracle mem REG 253,0 12259 6950100 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libodmd11.so oracle 2757 oracle 0r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 1w CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 2w CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 3r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 4r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 5r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 6r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 7u REG 253,0 1544 6951342 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat oracle 2757 oracle 8r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 9r CHR 1,3 0t0 4300 /dev/null oracle 2757 oracle 10r CHR 1,5 0t0 4302 /dev/zero oracle 2757 oracle 11r CHR 1,5 0t0 4302 /dev/zero oracle 2757 oracle 12u REG 253,0 1544 6951342 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat oracle 2757 oracle 13r REG 253,0 1101312 6962758 /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb oracle 2757 oracle 14r DIR 0,3 0 10741 /proc/2757/fd oracle 2757 oracle 15r CHR 1,5 0t0 4302 /dev/zero oracle 2757 oracle 16u REG 253,0 1544 6951342 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat oracle 2757 oracle 17uR REG 253,0 24 6951341 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/lkBB oracle 2757 oracle 18r REG 253,0 1101312 6962758 /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb oracle 2757 oracle 19u IPv6 42951 0t0 UDP *:29281 oracle 2757 oracle 256u REG 253,0 10043392 4784156 /u01/app/oracle/oradata/bb/control01.ctl oracle 2757 oracle 257uW REG 253,0 524296192 4784160 /u01/app/oracle/oradata/bb/system01.dbf oracle 2757 oracle 258uW REG 253,0 1048584192 4784161 /u01/app/oracle/oradata/bb/sysaux01.dbf oracle 2757 oracle 259u REG 253,0 581967872 4784162 /u01/app/oracle/oradata/bb/undotbs01.dbf oracle 2757 oracle 260u REG 253,0 85204992 4784165 /u01/app/oracle/oradata/bb/users01.dbf oracle 2757 oracle 261uW REG 253,0 209723392 4784784 /u01/app/oracle/oradata/bb/ezio01.dbf oracle 2757 oracle 262uW REG 253,0 87040000 4784725 /u01/app/oracle/oradata/bb/temp01.dbf oracle 2757 oracle 263uW REG 253,0 10493952 1639050 /test/lunar_test01.dbf (deleted) 可以看见文件被标识为delete
进入dbwr进程的File Descriptor number目录中
SQL> !ls -ltar /proc/2757/fd/ total 0 dr-xr-xr-x 7 oracle oinstall 0 Mar 15 12:29 .. dr-x------ 2 oracle oinstall 0 Mar 15 12:29 . lr-x------ 1 oracle oinstall 64 Mar 15 23:30 9 -> /dev/null lr-x------ 1 oracle oinstall 64 Mar 15 23:30 8 -> /dev/null lrwx------ 1 oracle oinstall 64 Mar 15 23:30 7 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat lr-x------ 1 oracle oinstall 64 Mar 15 23:30 6 -> /dev/null lr-x------ 1 oracle oinstall 64 Mar 15 23:30 5 -> /dev/null lr-x------ 1 oracle oinstall 64 Mar 15 23:30 4 -> /dev/null lr-x------ 1 oracle oinstall 64 Mar 15 23:30 3 -> /dev/null lrwx------ 1 oracle oinstall 64 Mar 15 23:30 263 -> /test/lunar_test01.dbf (deleted) 可以看见dbwr的进程的FD号 lrwx------ 1 oracle oinstall 64 Mar 15 23:30 262 -> /u01/app/oracle/oradata/bb/temp01.dbf lrwx------ 1 oracle oinstall 64 Mar 15 23:30 261 -> /u01/app/oracle/oradata/bb/ezio01.dbf lrwx------ 1 oracle oinstall 64 Mar 15 23:30 260 -> /u01/app/oracle/oradata/bb/users01.dbf lrwx------ 1 oracle oinstall 64 Mar 15 23:30 259 -> /u01/app/oracle/oradata/bb/undotbs01.dbf lrwx------ 1 oracle oinstall 64 Mar 15 23:30 258 -> /u01/app/oracle/oradata/bb/sysaux01.dbf lrwx------ 1 oracle oinstall 64 Mar 15 23:30 257 -> /u01/app/oracle/oradata/bb/system01.dbf lrwx------ 1 oracle oinstall 64 Mar 15 23:30 256 -> /u01/app/oracle/oradata/bb/control01.ctl l-wx------ 1 oracle oinstall 64 Mar 15 23:30 2 -> /dev/null lr-x------ 1 oracle oinstall 64 Mar 15 23:30 18 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb lrwx------ 1 oracle oinstall 64 Mar 15 23:30 17 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/lkBB lrwx------ 1 oracle oinstall 64 Mar 15 23:30 16 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat lr-x------ 1 oracle oinstall 64 Mar 15 23:30 15 -> /dev/zero lr-x------ 1 oracle oinstall 64 Mar 15 23:30 14 -> /proc/2757/fd lr-x------ 1 oracle oinstall 64 Mar 15 23:30 13 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb lrwx------ 1 oracle oinstall 64 Mar 15 23:30 12 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat lr-x------ 1 oracle oinstall 64 Mar 15 23:30 11 -> /dev/zero lr-x------ 1 oracle oinstall 64 Mar 15 23:30 10 -> /dev/zero l-wx------ 1 oracle oinstall 64 Mar 15 23:30 1 -> /dev/null lr-x------ 1 oracle oinstall 64 Mar 15 23:30 0 -> /dev/null lrwx------ 1 oracle oinstall 64 Mar 15 23:37 19 -> socket:[42951]
恢复过程
[oracle@lunar ~]$ cd /proc/2757/fd/ --根据File Descriptor number重建数据文件: [oracle@lunar fd]$ cat 263 > /test/lunar01.dbf
检查下,文件已经恢复完成,大小为10m
[oracle@lunar fd]$ ls -lrt /test/lunar01.dbf -rw-r--r-- 1 oracle oinstall 10493952 Mar 15 23:50 /test/lunar01.dbf
将数据文件offline
SQL> alter database datafile '/test/lunar_test01.dbf' offline; Database altered. [/sql 修改控制文件的文件信息,因为已经将rm的/test/lunar_test01.dbf,创建到/test/lunar01.dbf,因此要同步控制文件中的信息 1 SQL> alter database rename file '/test/lunar_test01.dbf' to '/test/lunar01.dbf'; Database altered.
恢复datafile
SQL> recover datafile '/test/lunar01.dbf'; Media recovery complete.
将数据文件online
SQL> alter database datafile '/test/lunar01.dbf' online; Database altered.
好了,完成恢复了
SQL> conn lunar/lunar Connected. SQL> select * from test_table; NAME -------------------------------------------------------------------------------- Love you forever, my baby, Happy Childrens Day! SQL> SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EZIO LUNAR_TEST 7 rows selected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/bb/system01.dbf /u01/app/oracle/oradata/bb/sysaux01.dbf /u01/app/oracle/oradata/bb/undotbs01.dbf /u01/app/oracle/oradata/bb/users01.dbf /u01/app/oracle/oradata/bb/ezio01.dbf /test/lunar01.dbf 6 rows selected.
lunar ,我有个疑惑,‘ cat 263 > /test/lunar01.dbf ’这个操作中能否直接数据文件名改为 ‘lunar_test01.dbf’ ’?
还有这个方法除了不能重启外,还有没有其他限制?