前言
時常會接到客戶或一線工程師回報oracle資料庫報“ora-01110”等錯誤，本人過往也處理過好幾次類似的故障，發現基本上是由于開發人員或初級維護人員在執行資料庫表空間擴容時，不小心将本身需要擴容到ASM磁盤組的資料檔案擴容到了本地節點上；抑或是開發人員發現執行步驟錯誤之後直接實體删除錯誤檔案，且未通知資料庫維護人員導緻。本文通過真實操作案例分享，講述在ASM環境下如何防止誤将資料檔案擴容到本地檔案系統的方法，希望各位技術人員未來能杜絕此類誤操作發生。
在巡檢中發現，資料庫資料檔案擴容到節點2本地目錄中，導緻隻有單個節點能讀取到該資料檔案，節點一無法鎖定資料檔案。
資料檔案
節點一alert_ log檔案報錯内容
案例處理對誤建立在節點2本地的資料檔案遷移至ASM磁盤組，具體解決操作見下方。（注意：操作時需停止資料庫所有執行個體，在變更期間資料庫無法對外提供任何服務，需要提前申請停機時間操作。）
實施操作步驟節點2：
Offline相應表空間：
col CHECKPOINT_CHANGE# for 9999999999999999999
select file#,CHECKPOINT_CHANGE#,status from v$datafile where file# in ('23','25','27');
col CHECKPOINT_CHANGE# for 9999999999999999999
select file#,CHECKPOINT_CHANGE#,status from V$DATAFILE_HEADER datafile_head where file# in ('23','25','27');
CHECKPOINT_CHANGE# 号一緻
alter tablespace CSG_MD offline;
alter tablespace TBS_META_NW offline;
alter tablespace TS_KPI offline;
rman 備份資料檔案
rman target /
backup datafile 23,25,27;
rman 恢複資料檔案
rman target /
run{
set newname for datafile '/dev/shm/data/CMX_DATA1.dbf' to '+ORADATA/sjzy/datafile/CMX_DATA1.dbf';
set newname for datafile '/dev/shm/data/CMX_DATA2.dbf' to '+ORADATA/sjzy/datafile/CMX_DATA2.dbf';
set newname for datafile '/dev/shm/data/CMX_DATA3.dbf' to '+ORADATA/sjzy/datafile/CMX_DATA3.dbf';
restore datafile 23,25,27;
}
修改控制檔案指針
alter database rename file '/dev/shm/data/CMX_DATA1.dbf' to '+ORADATA/sjzy/datafile/CMX_DATA1.dbf';
alter database rename file '/dev/shm/data/CMX_DATA2.dbf' to '+ORADATA/sjzy/datafile/CMX_DATA2.dbf';
alter database rename file '/dev/shm/data/CMX_DATA3.dbf' to '+ORADATA/sjzy/datafile/CMX_DATA3.dbf';
檢查
select file#,name,status,bytes/1024 from v$datafile where file# in ('23','25','27');
name 結果為：+ORADATA/sjzy/datafile/CMX_DATA1.dbf
online 相應表空間：
alter tablespace CSG_MD online;
alter tablespace TBS_META_NW online;
alter tablespace TS_KPI online;
防止誤将資料檔案擴容到本地檔案系統的方法在rac asm環境下，建立表空間或給表空間擴容時，容易出現在資料檔案中少寫+号，或是磁盤組的名稱中出現空格等問題，将資料檔案寫到本地檔案系統，導緻隻有一個節點能讀寫，而其他節點無法讀寫存在異常的資料檔案。
解決方法：通過在資料庫部署db級别的觸發器，可以有效防止該問題的發生。建立表空間或擴容時，資料檔案名的開頭必須包含正确的磁盤組名稱：“+磁盤組名”。
腳本示例：
第一步、建立一個type，如果為了使split函數具有通用性，請将其size 設大些。
create or replace type type_split as table of varchar2(4000)
/
第二步、建立function split
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(4000) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;
/
第三步、建立觸發器：
CREATE OR REPLACE TRIGGER cs_pnp_trigger
BEFORE ALTER OR CREATE ON DATABASE
DECLARE
v_oper varchar2(32); --operation type
v_obj_name varchar2(32); --object name
v_obj_type varchar2(32); --object type
v_sql_txt ora_name_list_t;
v_n BINARY_INTEGER;
v_stmt varchar2(4000); --sql statement
v_n2 number := 0;
v_dg_count number := 1;
v_in_dg number := 1;
v_tmp_dgname varchar2(100) := '';
BEGIN
--dbms_output.put_line(v_stmt);
select ora_sysevent, ora_dict_obj_name(), ora_dict_obj_type()
into v_oper, v_obj_name, v_obj_type
from dual;
--dbms_output.put_line(ora_sysevent||':'||v_obj_type||':'||v_obj_name);
IF (v_oper = 'CREATE' OR v_oper = 'ALTER') AND v_obj_type = 'TABLESPACE' THEN
v_stmt := '';
v_n := nvl(ora_sql_txt(v_sql_txt), 0);
FOR i IN 1 .. v_n LOOP
v_stmt := v_stmt || v_sql_txt(i);
END LOOP;
for j in (select * from table(split(v_stmt, chr(39)))) loop
v_n2 := v_n2 + 1;
if v_n2 mod 2 = 0 then
select COLUMN_VALUE
into v_tmp_dgname
from table(split(j.COLUMN_VALUE, '/'))
where rownum < 2;
select count(*)
into v_dg_count
from v$asm_diskgroup
where '+' || name = upper(v_tmp_dgname);
dbms_output.PUT_LINE(j.COLUMN_VALUE || v_dg_count);
if v_dg_count = 0 then
v_in_dg := 0;
exit;
end if;
end if;
end loop;
END IF;
IF v_in_dg = 0 THEN
RAISE_APPLICATION_ERROR(-20998,
'cs_pnp_trigger:
Attempt To CREATE or ALTER TABLESPACE in CLUSTER
and the diskgroup name "' || v_tmp_dgname ||
'" is INCORRECT!');
END IF;
END cs_pnp_trigger;
/
第四步、驗證腳本：
以錯誤方式建立表空間：
SQL> create tablespace test1 datafile 'data' size 10m autoextend off;
create tablespace test1 datafile 'data' size 10m autoextend off
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998: cs_pnp_trigger:
Attempt To CREATE or ALTER TABLESPACE in CLUSTER
and the diskgroup name "data" is INCORRECT!
ORA-06512: at line 45
以錯誤方式擴容表空間：
SQL> alter tablespace users add datafile 'data_vg' size 10m autoextend off;
alter tablespace users add datafile 'data_vg' size 10m autoextend off
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998: cs_pnp_trigger:
Attempt To CREATE or ALTER TABLESPACE in CLUSTER
and the diskgroup name "data_vg" is INCORRECT!
ORA-06512: at line 45
部署觸發器後，在建立表空間或給表空間擴容時，誤操作将無法執行成功，誤操作的資料檔案不會生成，背景日志也不會報相關的操作提示。
第五步、如果不需要該觸發器，可以采用如下步驟删除
drop TRIGGER cs_pnp_trigger;
drop function split;
drop type type_split ;