• Oracle误删除数据文件恢复---惜分飞

    有客户通过sftp误删除oracle数据文件,咨询我们是否可以恢复,通过远程上去检查,发现运气不错,数据库还没有crash,通过句柄找到被删除文件

    oracle@cwgstestdb[testwctdb]/proc/20611/fd$ls -ltr

    total 0

    lr-x------ 1 oracle oinstall 64 Feb 20 14:03 9 -> /oracle/db19c/rdbms/mesg/oraus.msb

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 8 -> /oracle/db19c/dbs/lkTESTWCTDB

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 7 -> /oracle/db19c/dbs/hc_testwctdb.dat

    lr-x------ 1 oracle oinstall 64 Feb 20 14:03 6 -> /var/lib/sss/mc/passwd

    lr-x------ 1 oracle oinstall 64 Feb 20 14:03 5 -> /proc/20611/fd

    lr-x------ 1 oracle oinstall 64 Feb 20 14:03 4 -> /oracle/db19c/rdbms/mesg/oraus.msb

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 305 -> /oradata/ftms_zx_test01_data8.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 304 -> /oradata/ftms_zx_test01_data7.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 303 -> /oradata/ftms_zx_test01_data6.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 302 -> '/oradata/ftms_zx_test01_data5.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 301 -> '/oradata/ftms_zx_test01_data4.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 300 -> '/oradata/ftms_zx_test01_data3.dbf (deleted)'

    lr-x------ 1 oracle oinstall 64 Feb 20 14:03 3 -> /dev/null

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 299 -> '/oradata/ftms_zx_test01_data2.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 298 -> '/oradata/ftms_zx_test01_data1.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 297 -> '/oradata/ftms_zx_test01_data.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 296 -> /oradata/ftms_zx_test_data.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 295 -> '/oradata/TESTWCTDB/sd.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 294 -> /oradata/TESTWCTDB/ftms_cs3_jiamiceshi

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 293 -> /langchao/dumpdata/FTMS_CS_TDE.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 292 -> /oradata/ftms_zx_test01.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 291 -> /langchao/dumpdata/FTMS_CS_DATA4.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 290 -> '/oradata/ftms_zx_data5.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 289 -> /langchao/dumpdata/FTMS_CS_DATA3.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 288 -> /langchao/dumpdata/FTMS_CS_DATA2.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 287 -> /langchao/dumpdata/FTMS_JD_DATA2.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 286 -> '/oradata/LCBIPECDS _TEMP_DAT.DBF'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 285 -> '/oradata/rTB_MBFE_TEMP (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 284 -> '/oradata/TESTWCTDB/temp01.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 283 -> '/oradata/ftms_credit_data5.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 282 -> /oradata/ftmshtdata.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 281 -> '/oradata/dump_data/FTMS_CSBF_DATA.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 280 -> /langchao/dumpdata/FTMS_NEWBL2_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 279 -> /langchao/dumpdata/FTMS_CS_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 278 -> /oradata/LCBIPECDS_DAT.DBF

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 277 -> /oradata/rTB_MBFE

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 276 -> /oradata/udpcount_02.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 275 -> /oradata/udpcount_01.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 274 -> '/oradata/ftms_credit_data_6.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 273 -> /langchao/dumpdata/FTMS_JD_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 272 -> '/oradata/ftms_old.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 271 -> '/oradata/ftms_credit_data2.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 270 -> /langchao/dumpdata/PJDIP_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 269 -> '/oradata/ftms_credit_data.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 268 -> /langchao/dumpdata/FTMS_NEWBL_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 267 -> '/oradata/ftms_zx_data4.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 266 -> /langchao/dumpdata/QIANZHANG_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 265 -> '/oradata/ftms_zx_data3.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 264 -> '/oradata/ftms_zx_data2.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 263 -> '/oradata/ftms_zx_data.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 262 -> /langchao/dumpdata/FTMSDIP_DATA.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 261 -> /oradata/TESTWCTDB/users01.dbf

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 260 -> '/oradata/TESTWCTDB/undotbs01.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 259 -> '/oradata/TESTWCTDB/sysaux01.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 258 -> '/oradata/TESTWCTDB/system01.dbf (deleted)'

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 257 -> /oradata/TESTWCTDB/control02.ctl

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 256 -> /oradata/TESTWCTDB/control01.ctl

    l-wx------ 1 oracle oinstall 64 Feb 20 14:03 2 -> /dev/null

    lrwx------ 1 oracle oinstall 64 Feb 20 14:03 10 -> 'socket:[823411]'

    l-wx------ 1 oracle oinstall 64 Feb 20 14:03 1 -> /dev/null

    lr-x------ 1 oracle oinstall 64 Feb 20 14:03 0 -> /dev/null

    查询数据文件大小（被删除的文件文件大小通过v$datafile查询为0）

    SQL> select name,bytes/1024/1024/1024 from v$datafile;

    NAME                                                                             BYTES/1024/1024/1024

    -------------------------------------------------------------------------------- --------------------

    /oradata/TESTWCTDB/system01.dbf                                                                     0

    /oradata/TESTWCTDB/sysaux01.dbf                                                                     0

    /oradata/TESTWCTDB/undotbs01.dbf                                                                    0

    /oradata/TESTWCTDB/users01.dbf                                                             .004882813

    /langchao/dumpdata/FTMSDIP_DATA.dbf                                                                 3

    /oradata/ftms_zx_data.dbf                                                                           0

    /oradata/ftms_zx_data2.dbf                                                                          0

    /oradata/ftms_zx_data3.dbf                                                                          0

    /langchao/dumpdata/QIANZHANG_DATA.dbf                                                               5

    /oradata/ftms_zx_data4.dbf                                                                          0

    /langchao/dumpdata/FTMS_NEWBL_DATA.dbf                                                             30

    /oradata/ftms_credit_data.dbf                                                                       0

    /langchao/dumpdata/PJDIP_DATA.dbf                                                                  20

    /oradata/ftms_credit_data2.dbf                                                                      0

    /oradata/ftms_old.dbf                                                                               0

    /langchao/dumpdata/FTMS_JD_DATA.dbf                                                                15

    /oradata/ftms_credit_data_6.dbf                                                                     0

    /oradata/udpcount_01.dbf                                                                            5

    /oradata/udpcount_02.dbf                                                                            5

    /oradata/rTB_MBFE                                                                              .03125

    /oradata/LCBIPECDS_DAT.DBF                                                                         .5

    /langchao/dumpdata/FTMS_CS_DATA.dbf                                                                30

    /langchao/dumpdata/FTMS_NEWBL2_DATA.dbf                                                            30

    /oradata/dump_data/FTMS_CSBF_DATA.dbf                                                               0

    /oradata/ftmshtdata.dbf                                                                    .087890625

    /oradata/ftms_credit_data5.dbf                                                                      0

    /langchao/dumpdata/FTMS_JD_DATA2.dbf                                                                3

    /langchao/dumpdata/FTMS_CS_DATA2.dbf                                                       31.9999847

    /langchao/dumpdata/FTMS_CS_DATA3.dbf                                                               10

    /oradata/ftms_zx_data5.dbf                                                                          0

    /langchao/dumpdata/FTMS_CS_DATA4.dbf                                                        12.109375

    /oradata/ftms_zx_test01.dbf                                                                19.0527344

    /langchao/dumpdata/FTMS_CS_TDE.dbf                                                                  1

    /oradata/TESTWCTDB/ftms_cs3_jiamiceshi                                                     .029296875

    /oradata/TESTWCTDB/sd.dbf                                                                           0

    /oradata/ftms_zx_test_data.dbf                                                             .009765625

    /oradata/ftms_zx_test01_data.dbf                                                                    0

    /oradata/ftms_zx_test01_data1.dbf                                                                   0

    /oradata/ftms_zx_test01_data2.dbf                                                                   0

    /oradata/ftms_zx_test01_data3.dbf                                                                   0

    /oradata/ftms_zx_test01_data4.dbf                                                                   0

    /oradata/ftms_zx_test01_data5.dbf                                                                   0

    /oradata/ftms_zx_test01_data6.dbf                                                          12.5976563

    /oradata/ftms_zx_test01_data7.dbf                                                          9.08203125

    /oradata/ftms_zx_test01_data8.dbf                                                                6.25

    45 rows selected.

    把数据文件拷贝回来

    cp /proc/20611/fd/302   /langchao/orabak/

    cp /proc/20611/fd/301   /langchao/orabak/

    cp /proc/20611/fd/300   /langchao/orabak/

    cp /proc/20611/fd/299   /langchao/orabak/

    cp /proc/20611/fd/298   /langchao/orabak/

    cp /proc/20611/fd/297   /langchao/orabak/

    cp /proc/20611/fd/295   /langchao/orabak/

    cp /proc/20611/fd/290   /langchao/orabak/

    cp /proc/20611/fd/285   /langchao/orabak/

    cp /proc/20611/fd/284   /langchao/orabak/

    cp /proc/20611/fd/283   /langchao/orabak/

    cp /proc/20611/fd/281   /langchao/orabak/

    cp /proc/20611/fd/274   /langchao/orabak/

    cp /proc/20611/fd/272   /langchao/orabak/

    cp /proc/20611/fd/271   /langchao/orabak/

    cp /proc/20611/fd/269   /langchao/orabak/

    cp /proc/20611/fd/267   /langchao/orabak/

    cp /proc/20611/fd/265   /langchao/orabak/

    cp /proc/20611/fd/264   /langchao/orabak/

    cp /proc/20611/fd/263   /langchao/orabak/

    cp /proc/20611/fd/260   /langchao/orabak/

    cp /proc/20611/fd/259   /langchao/orabak/

    cp /proc/20611/fd/258   /langchao/orabak/

    由于涉及system表空间数据文件被删除,无法在open情况下直接操作,直接关闭数据库,启动到mount状态,重命名数据文件路径,recover数据文件,open库,恢复完成
    参考以前类似恢复:
    Solaris rm datafile recovery—利用句柄误删除数据文件恢复
    如果数据库已经关闭,需要考虑以下类似恢复方式:
    dbca删除库和rm删库恢复
    记录一次rm -rf 删除数据文件异常恢复

  • 相关阅读:
    字字珠玑，GitHub 爆赞的网络协议手册，被华为指定内部必学？
    拼多多怎么引流商家？建议收藏的几个方法，拼多多引流脚本详细使用教学分享
    java 匿名内部类
    Android kotlin内联函数(inline)的详解与原理
    LeetCode-152. 乘积最大子数组
    使用nodejs-koa2-mysql-sequelize-jwt 实现项目api接口
    Spring 02
    ThreadLocal 核心源码分析
    Linux下应用程序调试
    数学建模值TOPSIS法及代码
  • 原文地址：https://blog.csdn.net/xifenfei/article/details/136221680