ORA-27037: unable to obtain file status, ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

 

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

에러 : rac asm 환경에서 1번노드 로컬에 tablespace, datafile 생성=> 2번노드 에러 발생

Tue Jul 31 13:42:11 2018
Errors in file /oracle/app/oracle/diag/rdbms/orac/ORAC2/trace/ORAC2_dbw0_29632.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/app/oracle/oradata/orac/local01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/app/oracle/diag/rdbms/orac/ORAC2/trace/ORAC2_dbw0_29632.trc:
ORA-01186: file 6 failed verification tests

 

 

시나리오

기존 데이터 파일 조회

 

SQL> select file_id, file_name, tablespace_name from dba_data_files
 
   FILE_ID FILE_NAME                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
     1 +DATA/orac/datafile/system.259.979730789          SYSTEM
     2 +DATA/orac/datafile/sysaux.260.979730793          SYSAUX
     3 +DATA/orac/datafile/undotbs1.261.979730797      UNDOTBS1
     4 +DATA/orac/datafile/undotbs2.263.979730809      UNDOTBS2
     5 +DATA/orac/datafile/users.264.979730809          USERS

 

 

1번노드에만 있는 디렉토리에 테이블스페이스, 데이터파일 생성

SQL> create tablespace local1 datafile '/oracle/app/oracle/oradata/orac/local01.dbf' size 50m;

 

 

1번노드 dba_data_files 확인

SQL> select file_id, file_name, tablespace_name from dba_data_files
 
   FILE_ID FILE_NAME                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
     1 +DATA/orac/datafile/system.259.979730789          SYSTEM
     2 +DATA/orac/datafile/sysaux.260.979730793          SYSAUX
     3 +DATA/orac/datafile/undotbs1.261.979730797      UNDOTBS1
     4 +DATA/orac/datafile/undotbs2.263.979730809      UNDOTBS2
     5 +DATA/orac/datafile/users.264.979730809          USERS
     6 /oracle/app/oracle/oradata/orac/local01.dbf     LOCAL1

 

 

1번노드 alert log 확인

Tue Jul 31 13:42:11 2018
create tablespace local1 datafile '/oracle/app/oracle/oradata/orac/local01.dbf' size 50m
Completed: create tablespace local1 datafile '/oracle/app/oracle/oradata/orac/local01.dbf' size 50m

 

 

2번노드 dba_data_files 확인

SQL> select file_id, file_name, tablespace_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/app/oracle/oradata/orac/local01.dbf'
 
no rows selected

=> 에러발생으로 조회 불가

 

 

2번노드 alert log 확인

Tue Jul 31 13:42:11 2018
Errors in file /oracle/app/oracle/diag/rdbms/orac/ORAC2/trace/ORAC2_dbw0_29632.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/app/oracle/oradata/orac/local01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/app/oracle/diag/rdbms/orac/ORAC2/trace/ORAC2_dbw0_29632.trc:
ORA-01186: file 6 failed verification tests

(비정상)

 

 

 

해결 방법 : filesystem에 있는 데이터파일을 asm으로 복사 후 db상에서 datafile rename 작업 진행

 

1번노드에서 datafile을 ASM으로 복사

$ rman target /
RMAN> copy datafile '/oracle/app/oracle/oradata/orac/local01.dbf' to '+DATA/';
 
Starting backup at 18/07/31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=ORAC1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/oracle/app/oracle/oradata/orac/local01.dbf
output file name=+DATA/orac/datafile/local1.268.982936667 tag=TAG20180731T135747 RECID=1 STAMP=982936667
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18/07/31

 

 

sqlplus 접속 후 데이터파일 offline

SQL> alter database datafile '/oracle/app/oracle/oradata/orac/local01.dbf' offline;

 

 

데이터파일 rename

SQL> alter tablespace local1 rename datafile '/oracle/app/oracle/oradata/orac/local01.dbf' to '+DATA/orac/datafile/local1.268.982936667';

 

 

데이터파일 online

SQL> alter database datafile '+DATA/orac/datafile/local1.268.982936667' online;

 

 

데이터파일 확인

SQL> select file_id, file_name, tablespace_name, online_status from dba_data_files;
 
   FILE_ID FILE_NAME                          TABLESPACE ONLINE_STATUS
---------- -------------------------------------------------- ---------- ---------------------
     1 +DATA/orac/datafile/system.259.979730789          SYSTEM     SYSTEM
     2 +DATA/orac/datafile/sysaux.260.979730793          SYSAUX     ONLINE
     3 +DATA/orac/datafile/undotbs1.261.979730797      UNDOTBS1     ONLINE
     4 +DATA/orac/datafile/undotbs2.263.979730809      UNDOTBS2     ONLINE
     5 +DATA/orac/datafile/users.264.979730809          USERS     ONLINE
     6 +DATA/orac/datafile/local1.268.982936667          LOCAL1     ONLINE

 

 

1번노드 alert log 확인

Tue Jul 31 14:16:33 2018
alter tablespace local1 rename datafile '/oracle/app/oracle/oradata/orac/local01.dbf' to '+DATA/orac/datafile/local1.268.982936667'
Completed: alter tablespace local1 rename datafile '/oracle/app/oracle/oradata/orac/local01.dbf' to '+DATA/orac/datafile/local1.268.982936667'
Tue Jul 31 14:16:53 2018
alter database datafile '+DATA/orac/datafile/local1.268.982936667' online
Completed: alter database datafile '+DATA/orac/datafile/local1.268.982936667' online

 

 

2번노드 alert log 확인

Tue Jul 31 14:14:09 2018
QMNC started with pid=38, OS id=8011 
Completed: alter database open
Tue Jul 31 14:19:11 2018
Starting background process SMCO
Tue Jul 31 14:19:11 2018
SMCO started with pid=42, OS id=9376 

(정상)

 

 

 

원인 : 한쪽 노드에만 존재하는 경로(파일시스템)에 datafile 생성으로 인해 2번노드에서 해당 파일을 읽을 수 없어 발생하는 에러

한쪽 노드에만 존재하는 경로(파일시스템)에 datafile 생성으로 인해 2번노드에서 해당 파일을 읽을 수 없어 발생하는 에러

 

 

참조 :