利用文件扫描符恢复数据库.txt
模拟故障
cd /u01/oracle/oradata/orcl
删除所有的cqwr_TB文件
[oracle@txy orcl]$ rm -fr cqwr_TB_m*
SQL> conn cqwr/cqwr
Connected.
SQL> select * from T_LTE_CARD_INFO;
select * from T_LTE_CARD_INFO
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/oracle/oradata/orcl/cqwr_TB_m2.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
发现/u01/oracle/oradata/orcl/ 目录下
cqwr_TB_m2.dbf
select A.FILE#,A.NAME,A.TS# from V$DATAFILE a where NAME like '%cqwr_TB%'
6 /u01/oracle/oradata/orcl/cqwr_TB_m1.dbf 7
7 /u01/oracle/oradata/orcl/cqwr_TB_m2.dbf 7
8 /u01/oracle/oradata/orcl/cqwr_TB_m3.dbf 7
9 /u01/oracle/oradata/orcl/cqwr_TB_m4.dbf 7
----！！！！这个时候千万不要关闭数据库！！！！-----
[oracle@txy ~]$ ps -ef | grep dbw | grep -v grep
oracle 24560 1 0 11:47 ? 00:00:04 ora_dbw0_orcl
[oracle@txy ~]$ ls /proc/2456
24560/ 24562/ 24564/ 24566/ 24568/
[oracle@txy ~]$ ls /proc/24560/fd
0 1 10 11 12 13 14 15 16 17 18 19 2 20 21 22 23 24 25 26 27 28 29 3 4 5 6 7 8 9
[oracle@txy ~]$ cd /proc/24560/fd
[oracle@txy fd]$ ls -l | grep oracle
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 10 -> /u01/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 12 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 13 -> /u01/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 14 -> /u01/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 15 -> /u01/oracle/product/10.2.0/db_1/dbs/lkORCL
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 16 -> /u01/oracle/oradata/orcl/control01.ctl
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 17 -> /u01/oracle/oradata/orcl/control02.ctl
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 18 -> /u01/oracle/oradata/orcl/control03.ctl
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 19 -> /u01/oracle/oradata/orcl/system01.dbf
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 20 -> /u01/oracle/oradata/orcl/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 21 -> /u01/oracle/oradata/orcl/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 22 -> /u01/oracle/oradata/orcl/users01.dbf
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 23 -> /u01/oracle/oradata/orcl/example01.dbf
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 24 -> /u01/oracle/oradata/orcl/cqwr_TB_m1.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 25 -> /u01/oracle/oradata/orcl/temp01.dbf
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 26 -> /u01/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 27 -> /u01/oracle/oradata/orcl/cqwr_TB_m2.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 28 -> /u01/oracle/oradata/orcl/cqwr_TB_m3.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 29 -> /u01/oracle/oradata/orcl/cqwr_TB_m4.dbf (deleted)
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 8 12:42 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan 8 12:42 5 -> /u01/oracle/admin/orcl/udump/orcl_ora_24549.trc
l-wx------ 1 oracle oinstall 64 Jan 8 12:42 6 -> /u01/oracle/admin/orcl/bdump/alert_orcl.log
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 7 -> /u01/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)
l-wx------ 1 oracle oinstall 64 Jan 8 12:42 8 -> /u01/oracle/admin/orcl/bdump/alert_orcl.log
l-wx------ 1 oracle oinstall 64 Jan 8 12:42 9 -> pipe:[1280144]
可以看见有几个字符表示(deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 24 -> /u01/oracle/oradata/orcl/cqwr_TB_m1.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 27 -> /u01/oracle/oradata/orcl/cqwr_TB_m2.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 28 -> /u01/oracle/oradata/orcl/cqwr_TB_m3.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 8 12:42 29 -> /u01/oracle/oradata/orcl/cqwr_TB_m4.dbf (deleted)
首先创建一个新的目录复制文件到新的目录
mkdir -p /u01/oracle/oradata/orcl/cqwr_TB
复制文件到新分配的空间
cp /proc/24560/fd/24 /u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m1.dbf
cp /proc/24560/fd/27 /u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m2.dbf
cp /proc/24560/fd/28 /u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m3.dbf
cp /proc/24560/fd/29 /u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m4.dbf
将相对应的文件offline
SQL> alter database datafile 6 offline ;
SQL> alter database datafile 7 offline ;
SQL> alter database datafile 8 offline ;
SQL> alter database datafile 9 offline ;
RENAME方式更改数据文件位置
alter database rename file '/u01/oracle/oradata/orcl/cqwr_TB_m1.dbf' to '/u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m1.dbf' ;
alter database rename file '/u01/oracle/oradata/orcl/cqwr_TB_m2.dbf' to '/u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m2.dbf' ;
alter database rename file '/u01/oracle/oradata/orcl/cqwr_TB_m3.dbf' to '/u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m3.dbf' ;
alter database rename file '/u01/oracle/oradata/orcl/cqwr_TB_m4.dbf' to '/u01/oracle/oradata/orcl/cqwr_TB/cqwr_TB_m4.dbf' ;
recover datafile 6 ;
recover datafile 7 ;
recover datafile 8 ;
recover datafile 9 ;
alter database datafile 6 online ;
alter database datafile 7 online ;
alter database datafile 8 online ;
alter database datafile 9 online ;
至此数据库恢复成功
----找到文件的方式
[oracle@txy fd]$ lsof -p 24560
lsof: WARNING: can't stat() fuse.gvfs-fuse-daemon file system /root/.gvfs
Output information may be incomplete.
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 24560 oracle cwd DIR 8,3 4096 785092 /u01/oracle/product/10.2.0/db_1/dbs
oracle 24560 oracle rtd DIR 8,3 4096 2 /
oracle 24560 oracle txt REG 8,3 112467986 801946 /u01/oracle/product/10.2.0/db_1/bin/oracle
oracle 24560 oracle DEL REG 0,4 3473429 /SYSV3f327308
oracle 24560 oracle mem REG 8,3 156928 1712755 /lib64/ld-2.12.so
oracle 24560 oracle mem REG 8,3 5624 1712800 /lib64/libaio.so.1.0.1
oracle 24560 oracle mem REG 8,3 1926800 1712756 /lib64/libc-2.12.so
oracle 24560 oracle mem REG 8,3 145896 1712757 /lib64/libpthread-2.12.so
oracle 24560 oracle mem REG 8,3 22536 1712759 /lib64/libdl-2.12.so
oracle 24560 oracle mem REG 8,3 38872 1311734 /usr/lib64/libnuma.so.1
oracle 24560 oracle mem REG 8,3 116368 1700662 /lib64/libnsl-2.12.so
oracle 24560 oracle mem CHR 1,5 3802 /dev/zero
oracle 24560 oracle mem REG 8,3 65928 1700638 /lib64/libnss_files-2.12.so
oracle 24560 oracle mem REG 8,3 599384 1712779 /lib64/libm-2.12.so
oracle 24560 oracle mem REG 8,3 1552 802290 /u01/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
oracle 24560 oracle mem REG 8,3 3796601 793326 /u01/oracle/product/10.2.0/db_1/lib/libnnz10.so
oracle 24560 oracle mem REG 8,3 123345 789754 /u01/oracle/product/10.2.0/db_1/lib/libdbcfg10.so
oracle 24560 oracle mem REG 8,3 64041 792777 /u01/oracle/product/10.2.0/db_1/lib/libclsra10.so
oracle 24560 oracle mem REG 8,3 11385162 787604 /u01/oracle/product/10.2.0/db_1/lib/libjox10.so
oracle 24560 oracle mem REG 8,3 516097 792743 /u01/oracle/product/10.2.0/db_1/lib/libocrutl10.so
oracle 24560 oracle mem REG 8,3 691049 792742 /u01/oracle/product/10.2.0/db_1/lib/libocrb10.so
oracle 24560 oracle mem REG 8,3 681761 792741 /u01/oracle/product/10.2.0/db_1/lib/libocr10.so
oracle 24560 oracle mem REG 8,3 8545 789865 /u01/oracle/product/10.2.0/db_1/lib/libskgxn2.so
oracle 24560 oracle mem REG 8,3 1772385 792776 /u01/oracle/product/10.2.0/db_1/lib/libhasgen10.so
oracle 24560 oracle mem REG 8,3 177809 792850 /u01/oracle/product/10.2.0/db_1/lib/libskgxp10.so
oracle 24560 oracle 0r CHR 1,3 0t0 3800 /dev/null
oracle 24560 oracle 1r CHR 1,3 0t0 3800 /dev/null
oracle 24560 oracle 2r CHR 1,3 0t0 3800 /dev/null
oracle 24560 oracle 3r CHR 1,3 0t0 3800 /dev/null
oracle 24560 oracle 4r CHR 1,3 0t0 3800 /dev/null
oracle 24560 oracle 5w REG 8,3 637 922898 /u01/oracle/admin/orcl/udump/orcl_ora_24549.trc
oracle 24560 oracle 6w REG 8,3 565684 916573 /u01/oracle/admin/orcl/bdump/alert_orcl.log
oracle 24560 oracle 7u REG 8,3 0 801099 /u01/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)
oracle 24560 oracle 8w REG 8,3 565684 916573 /u01/oracle/admin/orcl/bdump/alert_orcl.log
oracle 24560 oracle 9w FIFO 0,8 0t0 1280144 pipe
oracle 24560 oracle 10u REG 8,3 1552 802290 /u01/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
oracle 24560 oracle 11r CHR 1,5 0t0 3802 /dev/zero
oracle 24560 oracle 12r CHR 1,5 0t0 3802 /dev/zero
oracle 24560 oracle 13r REG 8,3 849408 792810 /u01/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
oracle 24560 oracle 14u REG 8,3 1552 802290 /u01/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
oracle 24560 oracle 15uR REG 8,3 24 802291 /u01/oracle/product/10.2.0/db_1/dbs/lkORCL
oracle 24560 oracle 16u REG 8,3 7847936 916583 /u01/oracle/oradata/orcl/control01.ctl
oracle 24560 oracle 17u REG 8,3 7847936 916584 /u01/oracle/oradata/orcl/control02.ctl
oracle 24560 oracle 18u REG 8,3 7847936 916585 /u01/oracle/oradata/orcl/control03.ctl
oracle 24560 oracle 19uW REG 8,3 534781952 916577 /u01/oracle/oradata/orcl/system01.dbf
oracle 24560 oracle 20uW REG 8,3 7119839232 916579 /u01/oracle/oradata/orcl/undotbs01.dbf
oracle 24560 oracle 21uW REG 8,3 304095232 916578 /u01/oracle/oradata/orcl/sysaux01.dbf
oracle 24560 oracle 22uW REG 8,3 5251072 916580 /u01/oracle/oradata/orcl/users01.dbf
oracle 24560 oracle 23uW REG 8,3 104865792 916603 /u01/oracle/oradata/orcl/example01.dbf
oracle 24560 oracle 24u IPv4 1332239 0t0 UDP *:60343
oracle 24560 oracle 25uW REG 8,3 214966272 916599 /u01/oracle/oradata/orcl/temp01.dbf
oracle 24560 oracle 26r REG 8,3 849408 792810 /u01/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
oracle 24560 oracle 27uW REG 8,3 524296192 923105 /u01/oracle/oradata/orcl/cqwr_TB_m1.dbf
oracle 24560 oracle 28uW REG 8,3 524296192 923106 /u01/oracle/oradata/orcl/cqwr_TB_m2.dbf
oracle 24560 oracle 29uW REG 8,3 524296192 923107 /u01/oracle/oradata/orcl/cqwr_TB_m3.dbf
oracle 24560 oracle 30uW REG 8,3 524296192 923108 /u01/oracle/oradata/orcl/cqwr_TB_m4.dbf
[oracle@txy fd]$ ls
0 1 10 11 12 13 14 15 16 17 18 19 2 20 21 22 23 24 25 26 27 28 29 3 30 4 5 6 7 8 9
对应的FD号就是 27 28 29 30
