내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
에러 : rac asm 환경에서 1번노드 로컬에 tablespace, datafile 생성=> 2번노드 에러 발생
rac asm 환경에서 1번노드 로컬에 tablespace, datafile 생성=> 2번노드 에러 발생
|
1
2
3
4
5
6
7
8
9
|
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
|
시나리오
기존 데이터 파일 조회
|
1
2
3
4
5
6
7
8
9
|
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번노드에만 있는 디렉토리에 테이블스페이스, 데이터파일 생성
|
1
|
SQL> create tablespace local1 datafile '/oracle/app/oracle/oradata/orac/local01.dbf' size 50m;
|
1번노드 dba_data_files 확인
|
1
2
3
4
5
6
7
8
9
10
|
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 확인
|
1
2
3
|
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 확인
|
1
2
3
4
5
6
|
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 확인
|
1
2
3
4
5
6
7
8
9
|
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으로 복사
|
1
2
3
4
5
6
7
8
9
10
11
12
|
$ 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
|
1
|
SQL> alter database datafile '/oracle/app/oracle/oradata/orac/local01.dbf' offline;
|
데이터파일 rename
|
1
|
SQL> alter tablespace local1 rename datafile '/oracle/app/oracle/oradata/orac/local01.dbf' to '+DATA/orac/datafile/local1.268.982936667';
|
데이터파일 online
|
1
|
SQL> alter database datafile '+DATA/orac/datafile/local1.268.982936667' online;
|
데이터파일 확인
|
1
2
3
4
5
6
7
8
9
10
|
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 확인
|
1
2
3
4
5
6
|
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 확인
|
1
2
3
4
5
6
7
|
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번노드에서 해당 파일을 읽을 수 없어 발생하는 에러
참조 :
'ORACLE > Trouble Shooting' 카테고리의 다른 글
| CVE-2018-3110에 대한 Oracle 보안 경고 | Oracle Security Alert for CVE-2018-3110 (0) | 2018.08.13 |
|---|---|
| ORA-07445: exception encountered: core dump [LpxFSMSaxSE()+504] (0) | 2018.08.09 |
| clssgmpcBuildNodeList: nodename for node 0 is NULL 해결 (0) | 2018.07.31 |
| ORA-27302: failure occurred at: sskgxpsnd2 (2) | 2018.07.31 |
| [DataGuard] ORA-01153: an incompatible media recovery is active 해결 (0) | 2018.07.13 |