达梦表空间文件误删后的恢复

一、模拟故障生成：

1.查看当前表空间及数据文件

SQL> select tablespace_name,file_name,status from dba_data_files;

LINEID     TABLESPACE_NAME FILE_NAME                         STATUS   

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

1          SYSTEM          /opt/dm8/data/DAMENG/SYSTEM.DBF   AVAILABLE

2          DMHR            /opt/dm8/data/DAMENG/DMHR.DBF     AVAILABLE

3          BOOKSHOP        /opt/dm8/data/DAMENG/BOOKSHOP.DBF AVAILABLE

4          MAIN            /opt/dm8/data/DAMENG/MAIN.DBF     AVAILABLE

5          TEMP            /opt/dm8/data/DAMENG/TEMP.DBF     AVAILABLE

6          ROLL            /opt/dm8/data/DAMENG/ROLL.DBF     AVAILABLE

6 rows got

2.rm命令删除DMHR表空间文件

[dmdba@pd-dm-node ~]$ rm -rf /opt/dm8/data/DAMENG/DMHR.DBF

[dmdba@pd-dm-node ~]$ ll /opt/dm8/data/DAMENG/DMHR.DBF

ls: cannot access /opt/dm8/data/DAMENG/DMHR.DBF: No such file or directory

SQL> select path from v$datafile;

LINEID     PATH                                                            

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

1          /opt/dm8/data/DAMENG/SYSTEM.DBF

2          /opt/dm8/data/DAMENG/ROLL.DBF

3          /opt/dm8/data/DAMENG/TEMP.DBF

4          /opt/dm8/data/DAMENG/MAIN.DBF

5          /opt/dm8/data/DAMENG/BOOKSHOP.DBF

6          File or Directory [/opt/dm8/data/DAMENG/DMHR.DBF] does not exist

6 rows got

可看到，表空间文件被删除。

此时数据库还是可以正常用的，因为此时数据库没重启，数据库进程占用的文件句柄还没被释放：

SQL> create table dmhr.test(id int) tablespace dmhr;

executed successfully

used time: 4.408(ms). Execute id is 10901.

SQL> insert into dmhr.test select level from dual connect by level <= 100;      

affect rows 100

used time: 2.176(ms). Execute id is 10902.

SQL> commit;

executed successfully

used time: 1.134(ms). Execute id is 10903.

但某些系统视图的查询可能会出错：

SQL> select * from dba_data_files;

select * from dba_data_files;

[-2206]:Invalid parameter value.

二、恢复被删除的数据文件

1.调用系统过程 SP_TABLESPACE_PREPARE_RECOVER(tablespace_name)准备进行恢复

SQL> call SP_TABLESPACE_PREPARE_RECOVER('DMHR');

call SP_TABLESPACE_PREPARE_RECOVER('DMHR');

[-4554]:Invalid operation on file.

2.通过操作系统的 ps 命令找到当前dmserver 的 PID：

[dmdba@pd-dm-node ~]$ ps -ef | grep dmserver

dmdba    15994     1  0 09:00 pts/3    00:00:10 /opt/dm8/bin/dmserver /opt/dm8/data/DAMENG/dm.ini -noconsole

可看到数据库进程PID是15994

3.使用操作系统 ls 命令查看被删除文件对应的副本：ls -l /proc/<PID>/fd，会发现被删除的文件后有(deleted)字样：

[dmdba@pd-dm-node ~]$ ls -l /proc/15994/fd | grep deleted

lrwx------. 1 dmdba dinstall 64 Aug  4 10:42 12 -> /opt/dm8/data/DAMENG/DMHR.DBF (deleted)

4.使用操作系统的 cp 命令将文件复制到原位置

[dmdba@pd-dm-node fd]$ cp /proc/15994/fd/12 /opt/dm8/data/DAMENG/DMHR.DBF

5.复制成功后，调用系统过程 SP_TABLESPACE_RECOVER(tablespace_name)完成表空间失效文件的恢复

SQL> call SP_TABLESPACE_RECOVER('DMHR');

DMSQL executed successfully

used time: 0.520(ms). Execute id is 10906.

此时，数据文件已经成功被恢复了：

SQL> select ts.name,df.path,df.status$

2   from v$tablespace ts,v$datafile df

3   where ts.id = df.group_id;

LINEID     NAME     PATH                              STATUS$    

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

1          SYSTEM   /opt/dm8/data/DAMENG/SYSTEM.DBF   1

2          DMHR     /opt/dm8/data/DAMENG/DMHR.DBF     1

3          BOOKSHOP /opt/dm8/data/DAMENG/BOOKSHOP.DBF 1

4          MAIN     /opt/dm8/data/DAMENG/MAIN.DBF     1

5          TEMP     /opt/dm8/data/DAMENG/TEMP.DBF     1

6          ROLL     /opt/dm8/data/DAMENG/ROLL.DBF     1

6 rows got

数据也没有丢失：

SQL> select count(1) from dmhr.test;

LINEID     COUNT(1)            

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

1          100