Resolution of an error deleting a file from an Oracle Database runtime RM

There are a number of discussions on the web that can be used to delete data files from the Oracle runtime, and for files deleted using the RM operating system command, you can recover files with the copy file descriptor, although the database is still running.

However, when the DB is still running, copy the file descriptor directly to recover the file, there are several problems with the operation:

1. Additional storage space required during copy
   

2. If the file that was mistakenly deleted includes the system tablespace, all copy operations must complete before checkpoint is initiated, otherwise the data file will be completely lost due to the instance termination, and the data file in copy is not valid, even if the system table space is not included. Copy must also be completed before checkpoint, otherwise the header of the copied data file may be corrupted
   

3. This is likely to be invalid if you mistakenly delete a file that includes online redo or undo

4. The operation of the recovery data file must be the corresponding table space offline or shut down the database, which will inevitably affect the online business

   
   

To think about it, the database process reads and writes data files, using file descriptors (hereinafter referred to as FD) to manipulate the corresponding data files on the hard disk. Since these mistakenly deleted data files can also be read and write in memory, through the FD operation files, and the data files of these mistakenly deleted itself occupies the space is not released, it can be a similar undelete way to retain the content of FD will be on the original hard disk?

In fact, there are several projects on GitHub to achieve this, including Fdlink, Vfs-undelete and so on. But there are also problems with these programmes:

1. The kernel module needs to be compiled for implementation. However, this requirement is relatively high, after all, the server generally does not install the kernel source code, and even if the download will take time.
   

2. Requires root privileges

3. The actual effect is hard to say. I measured down, centos5.6 system, the default kernel 2.6.18-238.el5, delete a single data file, with Fdlink and Vfs-undelete can not be successfully restored.

So this method is basically not feasible.

Is there any other way? In fact, further thinking, since FD can also fully operate these data files mistakenly deleted, FD can be directly treated as a data file. In order to keep the original data file path unchanged, we can set up a soft link that links the data file back to FD so that all Oracle processes can find the data file so that the mis-delete operation does not affect the normal business.

But the problem of doing this again, after all, FD is not the file on the hard disk, DB or system restart, power outage will invalidate the FD, the data files mistakenly deleted or may be lost, and the direct copy of the data files generated by the paper and the beginning of this article, how to operate to take into account?

It is no trouble to solve the problem. As long as the characteristics of FD, as long as there is a process to hold the FD, the corresponding data file content can be read and write. We can use the tail command to always occupy this fd, so even if the db is closed or the data file offline, the fd corresponding data file content can be read, and because the DB is closed or the data file has been offline, naturally these data files are not written operation, The copied data file is now clean and secure.

Here is a demonstration of this process (the test table is highlighted on the table space users01.dbf, important actions):

$ ls
Bbed.par control01.ctl filelist.txt redo01.log redo03.log system01.dbf undotbs01.dbf
BIFILE.BBD example01.dbf log.bbd redo02.log sysaux01.dbf temp01.dbf users01.dbf
$ ps-ef|grep dBW
Oracle 574 5117 0 10:48 pts/1 00:00:00 grep DBW
$ sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Tue Nov 4 10:48:09 2014

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

Connected to an idle instance.

Sql> Startup
ORACLE instance started.

Total System Global area 1870647296 bytes
Fixed Size 2229424 bytes
Variable Size 452987728 bytes
Database buffers 1409286144 bytes
Redo buffers 6144000 bytes
Database mounted.
Database opened.
Sql> Select COUNT (*) from test;

COUNT (*)
----------
618237

$ ll
Total 2218432
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
-RW-R-----1 Oracle Oinstall 9814016 Nov 4 10:48 control01.ctl
-RW-R-----1 Oracle Oinstall 362422272 Nov 4 10:48 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo01.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo02.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo03.log
-RW-R-----1 Oracle Oinstall 639639552 Nov 4 10:48 sysaux01.dbf
-RW-R-----1 Oracle Oinstall 943726592 Nov 4 10:48 system01.dbf
-RW-R-----1 Oracle Oinstall 20979712 Nov 4 10:48 temp01.dbf
-RW-R-----1 Oracle Oinstall 57679872 Nov 4 10:48 undotbs01.dbf
-RW-R-----1 Oracle Oinstall 83894272 Nov 4 10:48 users01.dbf
$rm-f users01.dbf

$Ps-ef|grep ORA_DBW
Oracle 628 1 0 10:48? 00:00:00 ora_dbw0_kv
Oracle 773 5117 0 10:48 pts/1 00:00:00 grep ora_dbw$ ll/proc/628/fd
Total 0
LR-X------1 Oracle Oinstall 4 10:48 0-/dev/null
L-WX------1 Oracle Oinstall 4 10:48 1-/dev/null
LR-X------1 Oracle Oinstall 4 10:48/dev/zero
LR-X------1 Oracle Oinstall 4 10:48-/dev/zero
LRWX------1 Oracle Oinstall 4 10:48/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_kv.dat
LR-X------1 Oracle Oinstall 4 10:48/U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1/RDBMS/MESG/ORAUS.MSB
LR-X------1 Oracle Oinstall 4 10:48/PROC/628/FD
LR-X------1 Oracle Oinstall 4 10:48/dev/zero
LRWX------1 Oracle Oinstall 4 10:48/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_kv.dat
LRWX------1 Oracle Oinstall 4 10:48-/U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1/DBS/LKKV
LR-X------1 Oracle Oinstall 4 10:48/U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1/RDBMS/MESG/ORAUS.MSB
L-WX------1 Oracle Oinstall 4 10:48 2-/dev/null
LRWX------1 Oracle Oinstall 4 10:48/u01/app/oracle/oradata/kv/control01.ctl
LRWX------1 Oracle Oinstall 4 10:48 257-/u01/app/oracle/fast_recovery_area/kv/control02.ctl
LRWX------1 Oracle Oinstall 4 10:48 258-/u01/app/oracle/oradata/kv/system01.dbf
LRWX------1 Oracle Oinstall 4 10:48 259-/U01/APP/ORACLE/ORADATA/KV/SYSAUX01.DBF
LRWX------1 Oracle Oinstall 4 10:48 260-/U01/APP/ORACLE/ORADATA/KV/UNDOTBS01.DBF
LRWX------1 Oracle Oinstall 4 10:48 261/u01/app/oracle/oradata/kv/users01.dbf (deleted)
LRWX------1 Oracle Oinstall 4 10:48 262-/u01/app/oracle/oradata/kv/example01.dbf
LRWX------1 Oracle Oinstall 4 10:48 263-/U01/APP/ORACLE/ORADATA/KV/TEMP01.DBF
LR-X------1 Oracle Oinstall 4 10:48 3-/dev/null
LR-X------1 Oracle Oinstall 4 10:48 4-/dev/null
LR-X------1 Oracle Oinstall 4 10:48 5-/dev/null
LR-X------1 Oracle Oinstall 4 10:48 6-/dev/null
LRWX------1 Oracle Oinstall 4 10:48 7-/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_kv.dat
LR-X------1 Oracle Oinstall 4 10:48 8-/dev/null
LR-X------1 Oracle Oinstall 4 10:48 9-/dev/null
$tail-n +0-f/proc/628/fd/261 >/dev/null &
[1] 785
$!sqlplus
Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Tue Nov 4 10:49:36 2014

Copyright (c) 1982, 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> alter system checkpoint;
Alter system checkpoint
*
ERROR at line 1:
Ora-03113:end-of-file on communication channel
Process id:792
Session id:142 Serial Number:5


Sql> disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

$ tac/u01/app/oracle/diag/rdbms/kv/kv/trace/alert_kv.log |less
Instance terminated by CKPT, PID = 636
Dumping diagnostic data in directory=[cdmp_20141104104959], requested by (Instance=1, osid=636 (CKPT)), summary=[ Abnormal instance termination].
CKPT (ospid:636): Terminating the instance due to error 63999
System state dumped to trace FILE/U01/APP/ORACLE/DIAG/RDBMS/KV/KV/TRACE/KV_DIAG_611.TRC
System State Dump requested by (Instance=1, osid=636 (CKPT)), summary=[abnormal instance termination].
Tue Nov 04 10:49:59 2014
Additional Information:3
linux-x86_64 error:2: No such file or directory
Ora-27041:unable to open File
Ora-01110:data file 4: '/U01/APP/ORACLE/ORADATA/KV/USERS01.DBF '
Ora-01116:error in opening database file 4
Ora-63999:data file suffered media failure
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/KV/KV/TRACE/KV_CKPT_636.TRC:
Additional Information:3
linux-x86_64 error:2: No such file or directory
Ora-27041:unable to open File
Ora-01110:data file 4: '/U01/APP/ORACLE/ORADATA/KV/USERS01.DBF '
Ora-01116:error in opening database file 4
Ora-63999:data file suffered media failure
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/KV/KV/TRACE/KV_CKPT_636.TRC:
Tue Nov 04 10:49:58 2014
CJQ0 started with pid=27, OS id=726
Tue Nov 04 10:48:23 2014
Starting background Process CJQ0
Space available in the underlying filesystem or ASM DiskGroup.
Database for recovery-related files, and does not reflect the amount of
User-specified limit on the amount of space that would be used by this
Db_recovery_file_dest_size of 20000 MB is 8.36% used. This is a
Tue Nov 04 10:48:22 2014
Arc0:becoming the Heartbeat ARCH
Arc0:starting ARCH PROCESSES Complete
Arc3:archival started
Arc2:archival started
Completed:alter DATABASE OPEN
QMNC started with pid=24, OS id=697
Tue Nov 04 10:48:21 2014
Starting background Process QMNC
Replication_dependency_tracking turned off (no async multimaster replication found)
No Resource Manager Plan Active
Database Characterset is we8mswin1252
smon:enabling TX Recovery
Verifying 11g file Header compatibility for tablespace encryption completed
Verifying file header compatibility for 11g tablespace encryption:
Undo initialization finished serial:0 start:429462324 end:429462404 diff:80 (0 seconds)
[676] successfully onlined Undo tablespace 2.
ARC3 started with pid=23, OS id=693
Tue Nov 04 10:48:20 2014

$ ll
Total 2136420
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
-RW-R-----1 Oracle Oinstall 9814016 Nov 4 10:49 control01.ctl
-RW-R-----1 Oracle Oinstall 362422272 Nov 4 10:48 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo01.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo02.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo03.log
-RW-R-----1 Oracle Oinstall 639639552 Nov 4 10:49 sysaux01.dbf
-RW-R-----1 Oracle Oinstall 943726592 Nov 4 10:49 system01.dbf
-RW-R-----1 Oracle Oinstall 20979712 Nov 4 10:48 temp01.dbf
-RW-R-----1 Oracle Oinstall 57679872 Nov 4 10:49 undotbs01.dbf
$ ps-ef|grep Tail
Oracle 785 5117 0 10:49 pts/1 00:00:00 tail-n +0-f/proc/628/fd/261
Oracle 844 5117 0 10:51 pts/1 00:00:00 grep tail
$ll/proc/785/fd
Total 0
LRWX------1 Oracle Oinstall 4 10:52 0-/DEV/PTS/1
L-WX------1 Oracle Oinstall 4 10:52 1-/dev/null
LRWX------1 Oracle Oinstall 4 10:50 2-/DEV/PTS/1
LR-X------1 Oracle Oinstall 4 10:52 3/u01/app/oracle/oradata/kv/users01.dbf (deleted)
$CP/PROC/785/FD/3 users01.dbf
$ ll
Total 2218432
-rw-r--r--1 Oracle Oinstall (OCT) 12:04 Bbed.par
-rw-r--r--1 Oracle oinstall 18432 Oct 13:48 BIFILE.BBD
-RW-R-----1 Oracle Oinstall 9814016 Nov 4 10:49 control01.ctl
-RW-R-----1 Oracle Oinstall 362422272 Nov 4 10:48 example01.dbf
-rw-r--r--1 Oracle Oinstall 259 Oct 12:03 filelist.txt
-rw-r--r--1 Oracle Oinstall 1725 Oct 13:48 LOG.BBD
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo01.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo02.log
-RW-R-----1 Oracle Oinstall 52429312 Nov 4 10:48 Redo03.log
-RW-R-----1 Oracle Oinstall 639639552 Nov 4 10:49 sysaux01.dbf
-RW-R-----1 Oracle Oinstall 943726592 Nov 4 10:49 system01.dbf
-RW-R-----1 Oracle Oinstall 20979712 Nov 4 10:48 temp01.dbf
-RW-R-----1 Oracle Oinstall 57679872 Nov 4 10:49 undotbs01.dbf
-RW-R-----1 Oracle Oinstall 83894272 Nov 4 10:52 users01.dbf
$ sqlplus/as SYSDBA

Sql*plus:release 11.2.0.3.0 Production on Tue Nov 4 10:52:39 2014

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

Connected to an idle instance.

Sql> Startup
ORACLE instance started.

Total System Global area 1870647296 bytes
Fixed Size 2229424 bytes
Variable Size 452987728 bytes
Database buffers 1409286144 bytes
Redo buffers 6144000 bytes
Database mounted.
Database opened.
Sql> Select COUNT (*) from test;

COUNT (*)
----------
618237

Sql> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

This article is from the "wandering in 1521" blog, please be sure to keep this source http://kiever.blog.51cto.com/771547/1571603

Resolution of an error deleting a file from an Oracle Database runtime RM