小测某Q友其应用人员不小心把RAC表空间tablespace数据文件datafile建在本地文件系统

背景

  某Q友谈及其公司应用人员不小心把RAC的表空间数据文件创建在本地文件系统上，造成应用访问数据库报错，查询DBA_DATA_FILES也报错。

结论

1,查阅官方手册
Oracle? Database Backup and Recovery Reference
11g Release 2 (11.2)
Part Number E10643-06


获取backup命令的使用，采用datafile的镜像复制方式，即backup as copy datafile 6 format


2,迁移RAC环境下建在某RAC节点的本地文件系统的数据文件到共享存储的ASM，脚本如下：
请见下述测试，总结：
   A，主要采用


backup as copy datafile 6 format '';


run
{
sql "alter tablespace tbs_localfilesystem2 offline immediate";--tablespace offline
switch datafile '/home/oracle/only_local.dbf' to datafilecopy '+DATA/jingfa/datafile/mig_localsystem.dbf'; --switch update controlfile
recover tablespace tbs_localfilesystem2; --recover tablespace
sql "alter tablespace tbs_localfilesystem2 online"; --tablespace online
}


3,如不小心把表空间数据文件建在本地节点，当前节点dba_data_files查询正常，但在非正常节点查询会报错
  所以一定要小心，防止造成业务不正常，因为可能应用业务会通过RAC其它节点操作数据库


4，再引申一下，一定要控制相关权限，以防不必要的人员操作数据库，引发隐患问题，责任权利一定要明晰 

5,为了防止后期再出现相关问题，以邮件通知开发相关人员，为其阐明利害

6,如果是基于已存在的表空间添加数据文件时，不小心建到了本地节点的文件系统，道理同上，（其实就是把上述的tablespace变为datafile）
脚本如下：
backup as copy datafile 7 format '+DATA/jingfa/datafile/part_dba.dbf';








{
sql "alter database  datafile 7 offline";--offline datafile
switch datafile 7 to datafilecopy '+DATA/jingfa/datafile/part_dba.dbf';
recover datafile 7; --recover datafile
sql "alter database datafile 7  online";--online datafile
}

测试

---10.2.0.1 rac
SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi


SQL> show parameter cluster_database


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2


---node1 查看当前数据文件
SQL> col file_name for a50
SQL> select file_id,file_name from dba_data_files;


   FILE_ID FILE_NAME
---------- --------------------------------------------------
         1 +DATA/jingfa/datafile/system.268.850150891
         2 +DATA/jingfa/datafile/undotbs1.267.850150919
         3 +DATA/jingfa/datafile/sysaux.264.850150935
         4 +DATA/jingfa/datafile/undotbs2.266.850150953
         5 +DATA/jingfa/datafile/users.259.850150971


---node2 查看当前数据文件
SQL> col file_name for a50
SQL> select file_id,file_name from dba_data_files;


   FILE_ID FILE_NAME
---------- --------------------------------------------------
         1 +DATA/jingfa/datafile/system.268.850150891
         2 +DATA/jingfa/datafile/undotbs1.267.850150919
         3 +DATA/jingfa/datafile/sysaux.264.850150935
         4 +DATA/jingfa/datafile/undotbs2.266.850150953
         5 +DATA/jingfa/datafile/users.259.850150971         






---node1 创建一个基于本地文件系统的表空间数据文件,可以显示出新添加的本地文件系统的表空间数据文件
SQL> create tablespace tbs_localfilesystem2 datafile '/home/oracle/only_local.dbf'  size 10m autoextend off;


Tablespace created.


SQL> select file_id,file_name from dba_data_files;


   FILE_ID FILE_NAME
---------- --------------------------------------------------
         1 +DATA/jingfa/datafile/system.268.850150891
         2 +DATA/jingfa/datafile/undotbs1.267.850150919
         3 +DATA/jingfa/datafile/sysaux.264.850150935
         4 +DATA/jingfa/datafile/undotbs2.266.850150953
         5 +DATA/jingfa/datafile/users.259.850150971
         6 /home/oracle/only_local.dbf


6 rows selected.


---node2 查询却提示在node1创建的7号表空间数据文件找不到，原因很简单，因为基于NODE1的本地文件系统，当然报错
SQL> select file_id,file_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/only_local.dbf'






no rows selected


--node1把建在本地文件系统的表空间数据文件迁移到共享存储ASM

--通过RMAN转换本地文件系统数据文件为ASM方式
RMAN> backup as copy datafile 6 format '+DATA/jingfa/datafile/mig_localsystem.dbf';


Starting backup at 25-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/home/oracle/only_local.dbf
output filename=+DATA/jingfa/datafile/mig_localsystem.dbf tag=TAG20151125T234637 recid=1 stamp=896744798
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03


--更新控制文件


run
{
sql "alter tablespace tbs_localfilesystem2 offline immediate";
switch datafile '/home/oracle/only_local.dbf' to datafilecopy '+DATA/jingfa/datafile/mig_localsystem.dbf';
recover tablespace tbs_localfilesystem2;
sql "alter tablespace tbs_localfilesystem2 online";
}




--node1及NODE2查询文件6已迁移到ASM
SQL> select file_id,file_name from dba_data_files;


   FILE_ID FILE_NAME
---------- --------------------------------------------------
         1 +DATA/jingfa/datafile/system.268.850150891
         2 +DATA/jingfa/datafile/undotbs1.267.850150919
         3 +DATA/jingfa/datafile/sysaux.264.850150935
         4 +DATA/jingfa/datafile/undotbs2.266.850150953
         5 +DATA/jingfa/datafile/users.259.850150971
         6 +DATA/jingfa/datafile/mig_localsystem.dbf


6 rows selected.


---继续在node1测试，如果是在已经存储的表空间添加一个本地文件系统的数据文件
SQL> alter tablespace tbs_localfilesystem2 add datafile '/home/oracle/part_datafile.dbf' size 10m autoextend off;


Tablespace altered.


SQL> select tablespace_name,file_id,file_name from dba_data_files;


TABLESPACE_NAME                   FILE_ID FILE_NAME
------------------------------ ---------- --------------------------------------------------
SYSTEM                                  1 +DATA/jingfa/datafile/system.268.850150891
UNDOTBS1                                2 +DATA/jingfa/datafile/undotbs1.267.850150919
SYSAUX                                  3 +DATA/jingfa/datafile/sysaux.264.850150935
UNDOTBS2                                4 +DATA/jingfa/datafile/undotbs2.266.850150953
USERS                                   5 +DATA/jingfa/datafile/users.259.850150971
TBS_LOCALFILESYSTEM2                    6 +DATA/jingfa/datafile/mig_localsystem.dbf
TBS_LOCALFILESYSTEM2                    7 /home/oracle/part_datafile.dbf


7 rows selected.


--NODE2报错
SQL> select file_id,file_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/part_datafile.dbf'






no rows selected




--NODE1开始恢复上述添加的数据文件到ASM共享存储




Tablespace altered.




RMAN> backup as copy datafile 7 format '+DATA/jingfa/datafile/part_dba.dbf';


Starting backup at 26-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=130 instance=jingfa1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/home/oracle/part_datafile.dbf
output filename=+DATA/jingfa/datafile/part_dba.dbf tag=TAG20151126T001458 recid=3 stamp=896746499
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 26-NOV-15




{
sql "alter database  datafile 7 offline";--offline datafile
switch datafile 7 to datafilecopy '+DATA/jingfa/datafile/part_dba.dbf';
recover datafile 7; --recover datafile
sql "alter database datafile 7  online";--online datafile
}