ORACLE RAC 裸设备数据库一节点表空间扩容错误添加数据文件到本地的处理

    下面是针对这一问题做的故障模拟及其处理方法。
    处理这个问题有2种思路：
   1、通过备份或导入导出将问题表空间的数据从错误添加数据文件的节点导出，重建表空间，然后将数据重新导入；这种方法的缺点是，如果表空间数据量大，将会耗空间、消耗时间。
   2、使用rman工具的copy命令，将数据文件复制到共享存储的裸设备中，然后进行恢复裸设备数据文件，这样速度比较快、比较省空间。
    创建业务模拟用户test，并设定其默认表空间是users表空间，然后创建业务模拟表，插入数据。
    rawrac1创建测试数据：
[oracle@rawrac1 ~]$ sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 2 18:39:28 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select * from tab;
TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST       TABLE
SQL> select * from test;
ID
----------
1
2
3
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/dev/raw/raw14
/dev/raw/raw12
/dev/raw/raw10
/dev/raw/raw13
/dev/raw/raw17
/dev/raw/raw11

6 rows selected.
SQL> !ls -l /dev/raw
total 0
crw-rw---- 1 oracle oinstall 162,  1 Sep  2 18:31 raw1
crw-rw---- 1 oracle oinstall 162, 10 Sep  2 18:36 raw10
crw-rw---- 1 oracle oinstall 162, 11 Sep  2 18:25 raw11
crw-rw---- 1 oracle oinstall 162, 12 Sep  2 18:40 raw12
crw-rw---- 1 oracle oinstall 162, 13 Sep  2 18:40 raw13
crw-rw---- 1 oracle oinstall 162, 14 Sep  2 18:25 raw14
crw-rw---- 1 oracle oinstall 162, 15 Sep  2 18:24 raw15
crw-rw---- 1 oracle oinstall 162, 16 Sep  2 18:24 raw16
crw-rw---- 1 oracle oinstall 162, 17 Sep  2 18:24 raw17
crw-rw---- 1 oracle oinstall 162, 18 Sep  2 18:24 raw18
crw-rw---- 1 oracle oinstall 162,  2 Sep  2 18:40 raw2
crw-rw---- 1 oracle oinstall 162,  3 Sep  2 18:25 raw3
crw-rw---- 1 oracle oinstall 162,  4 Sep  2 18:40 raw4
crw-rw---- 1 oracle oinstall 162,  5 Sep  2 18:40 raw5
crw-rw---- 1 oracle oinstall 162,  6 Sep  2 18:40 raw6
crw-rw---- 1 oracle oinstall 162,  7 Sep  2 18:25 raw7
crw-rw---- 1 oracle oinstall 162,  8 Sep  2 18:25 raw8
crw-rw---- 1 oracle oinstall 162,  9 Sep  2 18:40 raw9
     rawrac2验证数据
[oracle@rawrac2 ~]$ sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 2 18:39:47 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST       TABLE
SQL> select * from test;
ID
----------
1
2
3
    rawrac1模拟故障，对users表空间扩容，添加数据文件到本地目录
SQL> alter tablespace users add datafile '/opt/oracle/oradata/rawrac/raw18' size 500M;
Tablespace altered.
    rawrac2测试写入，这一点跟故障现象不一致（原因可能是测试插入的数据量太小，没有使用到刚添加的新数据文件）可以写入，但是dba_data_files视图无法查看：
SQL> insert into test values(4);
1 row created.
SQL> commit;
Commit complete.
SQL> select file_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/opt/oracle/oradata/rawrac/raw18'
no rows selected
    rawrac1查看rawrac2插入并提交的数据：
SQL> select * from test;
ID
----------
1
2
3
4
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/dev/raw/raw14
/dev/raw/raw12
/dev/raw/raw10
/dev/raw/raw13
/dev/raw/raw17
/dev/raw/raw11
/opt/oracle/oradata/rawrac/raw18
7 rows selected.
    故障处理要点，准备比添加的数据文件提交要大的裸设备，如果裸设备比添加的文件系统文件小，当使用rman进行copy时会报错如下：
[oracle@rawrac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 2 18:52:25 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: RAWRAC (DBID=1916399231)
RMAN> copy datafile '/opt/oracle/oradata/rawrac/raw18' to '/dev/raw/raw18';
Starting backup at 02-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 instance=rawrac1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/opt/oracle/oradata/rawrac/raw18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/02/2015 18:52:42
ORA-19504: failed to create file "/dev/raw/raw18"
ORA-27042: not enough space on raw partition to fullfill request
Additional information: 3
RMAN> quit
Recovery Manager complete.
    如果故障发现的比较早，可以在数据文件添加的节点rawrac1使用resize命令将数据文件体积缩小：
SQL> alter database datafile '/opt/oracle/oradata/rawrac/raw18' resize 300M;
Database altered.
    故障处理第一步、rawrac1将问题数据文件下线
SQL> alter database datafile '/opt/oracle/oradata/rawrac/raw18'  offline;
Database altered.
    故障处理第二步、rawrac1节点使用rman进行数据文件拷贝到裸设备
[oracle@rawrac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 2 18:58:58 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: RAWRAC (DBID=1916399231)
RMAN> copy datafile '/opt/oracle/oradata/rawrac/raw18' to '/dev/raw/raw18';
Starting backup at 02-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 instance=rawrac1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/opt/oracle/oradata/rawrac/raw18
output filename=/dev/raw/raw18 tag=TAG20150902T185912 recid=2 stamp=889383568
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 02-SEP-15
RMAN> quit
Recovery Manager complete.
    故障处理第三步、rawrac1将问题数据文件重定向到裸设备
[oracle@rawrac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 2 19:06:07 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter database rename file '/opt/oracle/oradata/rawrac/raw18' to '/dev/raw/raw18';
Database altered.
    故障处理第四步、rawrac1对裸设备文件进行恢复（此时如果是生产环境，可能需要rawrac2节点的归档文件，如有需要则需要将rawrac2节点相关的归档文件上传到rawrac1归档目录即可）
SQL> recover datafile '/dev/raw/raw18';
Media recovery complete.
    故障处理第五步、rawrac1将恢复完毕的裸设备文件上线
SQL> alter database datafile '/dev/raw/raw18' online;
Database altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/dev/raw/raw14
/dev/raw/raw12
/dev/raw/raw10
/dev/raw/raw13
/dev/raw/raw17
/dev/raw/raw11
/dev/raw/raw18
7 rows selected.
    rawrac1节点处理完故障后，rawrac2节点的dba_data_files视图就可以正常查看了：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/dev/raw/raw14
/dev/raw/raw12
/dev/raw/raw10
/dev/raw/raw13
/dev/raw/raw17
/dev/raw/raw11
/dev/raw/raw18
7 rows selected.