小測某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
}