오라클 11g R2 /etc/hosts 권한 부족시 발생하는 문제

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : 오라클 11g R2 /etc/hosts 권한 부족시 발생하는 문제

보안 조치로 인해 /etc/hosts 파일의 권한이 부족한 경우 오라클DB에 미치는 영향에 대해 알아봄

3가지 테스트를 진행함

테스트1. 리스너 포트 1521 인 경우(동적 리스너)

테스트2. 리스너 포트 1522 에 local_listener 등록한 경우(동적 리스너)

테스트3. 리스너 포트 1523 에 local_listener 등록하지 않은 경우(정적리스너)

테스트1. 리스너 포트 1521 인 경우(동적 리스너)

기존 /etc/hosts 파일 권한 확인

$ ls -al /etc/hosts
-rw-r--r--. 1 root root 181 Oct 15  2019 /etc/hosts

644 권한이 부여되어있음(소유자는 읽기쓰기 가능, 그룹원과 일반사용자는 읽기만 가능)

현재 권한이 정상적으로 부여되어있음

기존 db, 리스너 상태 확인(리스너 기본 1521포트, local_listener 파라미터 미사용)

리스너 상태 확인
$ lsnrctl status
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 00:55:56
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                19-SEP-2020 00:18:04
Uptime                    0 days 0 hr. 37 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCL11" has 1 instance(s).
  Instance "ORCL11", status READY, has 1 handler(s) for this service...
The command completed successfully
 
db 상태 확인
$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 19 00:56:06 2020
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select instance_name, status, version from v$instance;
 
INSTANCE_NAME     STATUS       VERSION
---------------- ------------ -----------------
ORCL11         OPEN          11.2.0.4.0
 
local_listener 파라미터 확인
SQL> show parameter local_listener
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
local_listener                 string

기존 db, 리스너 재기동 확인

db 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
db 기동
SQL> startup
ORACLE instance started.
 
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size         1426065304 bytes
Database Buffers      704643072 bytes
Redo Buffers            4923392 bytes
Database mounted.
Database opened.
 
리스너 종료
$ lsnrctl stop
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 01:02:05
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521)))
The command completed successfully
 
리스너 기동
$ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 01:02:22
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                19-SEP-2020 01:02:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

db 및 리스너 재기동시에도 문제 없이 잘 기동됨

/etc/hosts 파일 권한 변경

# chmod 600 /etc/hosts
# ls -al /etc/hosts
-rw-------. 1 root root 181 Oct 15  2019 /etc/hosts

600 권한으로 변경함(소유자는 읽기쓰기 가능, 그룹원과 일반사용자는 읽기도 불가능)

파일 권한 변경 직후

alert log, listener.log 는 특이사항 없음

$ tail -f /oracle/app/oracle/diag/rdbms/orcl11/ORCL11/trace/alert_ORCL11.log
이상없음
 
$ tail -f /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/trace/listener.log
이상없음

db, 리스너 종료

리스너 종료
$ lsnrctl stop
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 01:06:53
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
   Linux Error: 13: Permission denied
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
 
db 종료
$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 19 01:15:50 2020
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected.
SQL> shutdown immediate
ORA-00000: normal, successful completion
 
SQL> shutdown abort
ORACLE instance shut down.

리스너 종료시 TNS 오류가 발생하고

db 종료시에는 shutdown immediate 명령으로 종료되지 않음

shutdown abort 명령으로 종료함

리스너, db 기동

리스너 기동
$ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 01:17:26
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
   Linux Error: 13: Permission denied
 
Listener failed to start. See the error message(s) above...
 
db 기동
$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 19 01:17:45 2020
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))'

리스너, db 모두 기동 실패함

sqlplus 접속 시 Connected. 라는 메세지가 먼저 나옴

shutdown abort로 종료를 했으나 sqlplus 접속시마다 계속 Connected. 메세지가 나옴

테스트2. 리스너 포트 1522 에 local_listener 등록한 경우(동적 리스너)

1522 포트 동적 리스너 및 db 기동 후 local_listener 파라미터 적용

listener.ora 파일 확인
$ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
 
tnsnames.ora 파일 확인
$ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 
ORCL11_L2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11)
    )
  )
 
1522 리스너 기동
$ lsnrctl start listener2
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 01:22:34
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener2/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle11)(PORT=1522)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
   Linux Error: 13: Permission denied
 
Listener failed to start. See the error message(s) above...
 
db 기동
$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 19 01:22:45 2020
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))'

리스너 및 db 가 기동되지 않아 local_listener 파라미터도 등록불가

테스트3. 리스너 포트 1523 에 local_listener 등록하지 않은 경우(정적리스너)

1523 포트 정적 리스너 및 db 기동

listener.ora 파일 확인
$ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11)(PORT = 1523))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
  )
 
SID_LIST_LISTENER3 =
 (SID_LIST =
  (SID_DESC =
   (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
   (SID_NAME = orcl11)
  )
 )
 
tnsnames.ora 파일 확인
$ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 
ORCL11_L3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle11)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11)
    )
  )
 
1523 리스너 기동
$ lsnrctl start listener3
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-SEP-2020 01:29:15
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener3/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle11)(PORT=1523)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
   Linux Error: 13: Permission denied
 
Listener failed to start. See the error message(s) above...
 
db 기동
$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 19 01:29:36 2020
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))'

동일하게 리스너 및 db 가 기동되지 않음

결론

/etc/hosts 권한 부족시 리스너 기동이 불가하고 oracle db 도 기동이 되지 않음

접속이 정상적으로 되더라도 /etc/hosts 권한을 600으로 하는건 추후 오라클 DB운영에 문제가 발생할 수 있으니

644로 유지해줘야함

+

linux 7의 경우 /etc/hosts 파일을 읽을 수 없는 경우 리스너 기동 및 oracle db 접속이 정상적으로 가능함

/etc/hosts 파일 뿐만아니라 또 다른 어떤 파일을 참조하여 hostname을 찾는듯함

참조 : 

오라클 19c /etc/hosts 권한 부족시 발생하는 문제 https://positivemh.tistory.com/662