1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 상반기 - 오라클 성능 고도화 원리와 해법 스터디
  4. 부록 ExtProc

부록 ExtProc (by ljh1148) [2012.03.23]

참고자료
http://www.dator.co.kr/hjklab/textyle/241888
http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_17.shtml
http://www.dbforums.com/oracle/997254-ora-28575-unable-open-rpc-connection-external-procedure-agent.html

External Procedure

  • 복잡한 수식계산을 오라클에서 제공하는 기능으로만 충분하지 않을 경우에 C나 Java같은 언어로 복잡한 기능을 작성한 후
    Oracle에서는 파라미터를 넘겨서 해당 결과를 바드으면 수행 속도의 개선을 가져올 수 있다.

Oracle 9i External Procedure

동작 순서

  • 1. 사용자가 SQL에서 External Procedure에서 작성한 Function를 DB에서 요청을 한다.
  • 2. Shared SQL Area에서 해당 문장을 Parsing하면서 External Procedure을 사용하는 문장인지 파악 후 NET8 Listener한테
    사용자 SQL이 External Procedure를 호출했으니 해석해 달라고 요청한다.
  • 3. Listener는 extProc 프로세스 생성하면서 O/S의 있는 External Procedure가 있는 DDL, Procedure Name, Parameter를 알려준다.
  • 4. extProc 프로세스는 O/S에 있는 DDL 파일을 찾아서 O/S의 메모리에 Load하여 요청받은 Function의 결고를 처리한다.
  • 5. 처리된 결과를 SQL에 return해 준다.
  • 6. Session이 종료되면 extproc 프로세스도 자동으로 종료된다.

장점 및 단점

  • 장점 : 자바나 C의 재 활용성이 우수하다.
    단점 : 세션이 종료되지 않으면 extProc는 Oracle에서 메모리를 관리하는 영역이 아니라 O/S영역이기 때문에 한번 호출 될 때마다 해당 세션이
    종료되지 않으면 끝까지 살아남게 되어 지속적인 메모리에 남아 있게 된다.

JAVA

  • 익스터널 프로시를 사용하지 않음
  • 오라클내에 자바공간을 로드해서 사용함
    {CODE:SQL}
    • TestExE.java
      public class TestExE {

public static String getStr( ){

return "good";
}
}

{CODE}
{CODE:SQL}

ora10g@orastage admin$ javac TestExE.java
ora10g@orastage admin$
ora10g@orastage admin$ ls -l
total 44
-rwxrwxr-x 1 ora10g oinstall 271 Mar 23 18:15 TestExE.class
rw-rr- 1 ora10g oinstall 84 Mar 23 18:13 TestExE.java
rw-r

1 ora10g oinstall 762 Mar 23 15:24 listener.ora
rw-rr- 1 ora10g oinstall 1006 Mar 2 17:36 ora
drwxr-x--- 2 ora10g dba 4096 Feb 20 16:02 samples
rw-r
1 ora10g dba 172 Dec 26 2003 shrept.lst
rw-rr- 1 ora10g oinstall 10223 Feb 28 18:34 sqlnet.log
rw-r
1 ora10g oinstall 263 Feb 27 18:12 sqlnet.ora
rw-r
1 ora10g oinstall 526 Mar 2 17:49 tnsnames.ora

loadjava -user ua/ko0919@luxtv TestExE.javac -- 컴파일된 소스만 올림
loadjava -user ua/ko0919@luxtv -resolve -v TestExE.java --소스 및 컴파일 소스까지 올림

ora10g@orastage admin$ loadjava -user ua/ko0919@luxtv -resolve -v TestExE.java
arguments: '-user' 'ua/ko0919@luxtv' '-resolve' '-v' 'TestExE.java'
creating : source TestExE
loading : source TestExE
resolving: source TestExE
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
ora10g@orastage admin$
ora10g@orastage admin$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 금 3월 23 18:17:31 2012

Copyright © 1982, 2005, Oracle. All rights reserved.

SQL>
SQL>
SQL> conn ua/ko0919
연결되었습니다.
SQL>
SQL> CREATE OR REPLACE FUNCTION getString
2 RETURN VARCHAR2 IS
3 LANGUAGE JAVA
4 NAME 'TestExE.getStr() return java.lang.String';
5 /

함수가 생성되었습니다.

SQL> SELECT getString FROM DUAL
2 ;

GETSTRING


good

SQL> COLUMN OBJECT_NAME FORMAT A10;
SQL> COLUMN OBJECT_TYPE FORMAT A15;
SQL> COLUMN STATUS FORMAT A10;
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM user_objects
2 WHERE object_type like 'JAVA%';

OBJECT_NAM OBJECT_TYPE STATUS

--
--
TestExE JAVA CLASS VALID
TestExE JAVA SOURCE VALID

--그냥 바로 만드는게 편하다.
DROP JAVA SOURCE UA."TestExE";

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED UA."TestExE"
as
public class TestExE {

public static String getStr( ){

return "good";
}
}
/

CREATE OR REPLACE FUNCTION getString
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'TestExE.getStr() return java.lang.String';
/

{CODE}

C

listener.ora and Tns

{CODE:SQL}

  1. listener.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
  2. Generated by Oracle configuration tools.

SID_LIST_LISTENER_LUXTV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(SID_NAME = LUXTV)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(PROGRAM = extproc)
(ENVS="EXTPROC_DLLS=ANY")
)
)

LISTENER_LUXTV =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520)(QUEUESIZE = 32))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0 ))
)
)

INBOUND_CONNECT_TIMEOUT_LISTENER_LUXTV = 0
ADR_BASE_LISTENER_LUXTV = /uar/local/ora10g/product/10.2.0
DIAG_ADR_ENABLED_LISTENER_LUXTV = OFF

{CODE}

{CODE:SQL}

  1. tnsnames.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/tnsnames.ora
  2. Generated by Oracle configuration tools.

LUXTV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = luxtv)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

{CODE}

C code ( FileName : shell.c )

{CODE:sql}
/*

  • shell.c
  • Example program used to demonstrate how to call O/S
  • commands from PL/SQL using external procedures.
    *
    */

#include<stdio.h>
#include<stdlib.h>
#include<string.h>

void mailx(char *to, char *subject, char *message) {

int num;
char command[50000];

strcpy(command, "echo \"");
strcat(command, message);
strcat(command, "\" | mailx -s \"");
strcat(command, subject);
strcat(command, "\" ");
strcat(command, to);

num = system(command);

}

void sh(char *command) {

int num;

num = system(command);

}

{CODE}

Solaris

{CODE:SQL}

-bash-3.00$ gcc -m64 -fPIC -c shell.c
-bash-3.00$ gcc -m64 -shared -static-libgcc -o shell.so shell.o
-bash-3.00$ chmod 775 shell.so

or

-bash-3.00$ gcc -G -c shell.c
-bash-3.00$ ld -r -o shell.so shell.o
-bash-3.00$ chmod 775 shell.so

{CODE}

Linux

{CODE:SQL}

ora10g@orastage admin$ ls -l
total 48
-rwxrwxr-x 1 ora10g oinstall 271 Mar 23 18:15 TestExE.class
rw-rr- 1 ora10g oinstall 84 Mar 23 18:13 TestExE.java
rw-r

1 ora10g oinstall 762 Mar 23 15:24 listener.ora
rw-rr- 1 ora10g oinstall 1006 Mar 2 17:36 ora
drwxr-x--- 2 ora10g dba 4096 Feb 20 16:02 samples
rw-rr- 1 ora10g oinstall 546 Mar 23 18:46 shell.c
rw-r
1 ora10g dba 172 Dec 26 2003 shrept.lst
rw-rr- 1 ora10g oinstall 10223 Feb 28 18:34 sqlnet.log
rw-r
1 ora10g oinstall 263 Feb 27 18:12 sqlnet.ora
rw-r
1 ora10g oinstall 526 Mar 2 17:49 tnsnames.ora
ora10g@orastage admin$
ora10g@orastage admin$
ora10g@orastage admin$
ora10g@orastage admin$ gcc -fPIC -c shell.c
ora10g@orastage admin$ gcc -shared -static-libgcc -o shell.so shell.o
ora10g@orastage admin$ chmod 775 shell.so

{CODE}

Oracle Makefile

Oracle 8.0.X

{CODE:SQL}

-bash-3.00$ make -f demo_rdbms.mk extproc_nocallback SHARED_LIBNAME=shell.so OBJS=shell.o

{CODE}

Oracle 8.1.X

{CODE:SQL}

ora10g@orastage admin$ make -f /usr/local/ora10g/product/10.2.0/rdbms/demo/demo_rdbms.mk extproc_no_context SHARED_LIBNAME=shell.so OBJS=shell.o
/usr/bin/gcc -shared -L/usr/local/ora10g/product/10.2.0/lib -o shell.so shell.o

ora10g@orastage admin$ ls -l
total 60
-rwxrwxr-x 1 ora10g oinstall 271 Mar 23 18:15 TestExE.class
rw-rr- 1 ora10g oinstall 84 Mar 23 18:13 TestExE.java
rw-r

1 ora10g oinstall 762 Mar 23 15:24 listener.ora
rw-rr- 1 ora10g oinstall 1006 Mar 2 17:36 ora
drwxr-x--- 2 ora10g dba 4096 Feb 20 16:02 samples
rw-rr- 1 ora10g oinstall 546 Mar 23 18:46 shell.c
rw-rr- 1 ora10g oinstall 2000 Mar 23 18:51 shell.o
-rwxr-xr-x 1 ora10g oinstall 6230 Mar 23 19:01 shell.so
rw-r
1 ora10g dba 172 Dec 26 2003 shrept.lst
rw-rr- 1 ora10g oinstall 10223 Feb 28 18:34 sqlnet.log
rw-r
1 ora10g oinstall 263 Feb 27 18:12 sqlnet.ora
rw-r
1 ora10g oinstall 526 Mar 2 17:49 tnsnames.ora
ora10g@orastage admin$
ora10g@orastage admin$
ora10g@orastage admin$ pwd
/usr/local/ora10g/product/10.2.0/network/admin
ora10g@orastage admin$
ora10g@orastage admin$ sqlplus /nolog

{CODE}

Library Definition

{CODE:SQL}

SQL> CREATE LIBRARY shell_lib is '/usr/local/ora10g/product/10.2.0/network/admin/shell.so';
2 /

라이브러리가 생성되었습니다.

{CODE}

PL/SQL Wrapper Procedure

{CODE:SQL}

SQL> CREATE OR REPLACE PROCEDURE shell(command IN char)
2 AS EXTERNAL
3 NAME "sh"
4 LIBRARY shell_lib
5 LANGUAGE C
6 PARAMETERS (command string);
7 /

프로시저가 생성되었습니다.

SQL> CREATE OR REPLACE PROCEDURE mailx(send_to IN char, subject IN char, message IN char)
2 AS EXTERNAL
3 NAME "mailx"
4 LIBRARY shell_lib
5 LANGUAGE C
6 PARAMETERS (send_to string, subject string, message string);
7 /

프로시저가 생성되었습니다.

{CODE}

Execution

{CODE:SQL}

SQL> SHOW PARAMETER USER

NAME TYPE VALUE

---
--
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest string /usr/local/ora10g/admin/LUXTV/udump

SQL> exec shell('ls /usr/local/ora10g/admin/LUXTV/udump > /usr/local/ora10g/product/10.2.0/network/admin/myoutput.txt');
BEGIN shell('ls /usr/local/ora10g/admin/LUXTV/udump > /usr/local/ora10g/product/10.2.0/network/admin/myoutput.txt'); END;

*
1행에 오류:
ORA-28575: 외부 프로시저 대리 프로세스로 연결하기 위한 RPC을 열 수 없습니다
ORA-06512: "UA.SHELL", 줄 1에서
ORA-06512: 줄 1에서

{CODE}

{CODE:SQL}

  1. listener.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
  2. Generated by Oracle configuration tools.

SID_LIST_LISTENER_LUXTV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(SID_NAME = LUXTV)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(PROGRAM = extproc)
(ENVS="EXTPROC_DLLS=ANY")
)
)

LISTENER_LUXTV =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520)(QUEUESIZE = 32))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTERNAL )) <-- 설마했다. ㅋㅋ
)
)

INBOUND_CONNECT_TIMEOUT_LISTENER_LUXTV = 0
ADR_BASE_LISTENER_LUXTV = /uar/local/ora10g/product/10.2.0
DIAG_ADR_ENABLED_LISTENER_LUXTV = OFF

{CODE}

{CODE:SQL}

  1. tnsnames.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/tnsnames.ora
  2. Generated by Oracle configuration tools.

LUXTV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = luxtv)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTERNAL)) <-- 설마했다. ㅋㅋ
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

{CODE}
{CODE:SQL}

SQL> CREATE OR REPLACE PROCEDURE shell(command IN char)
2 AS EXTERNAL
3 NAME "sh"
4 LIBRARY shell_lib
5 LANGUAGE C
6 PARAMETERS (command string);
7 /

프로시저가 생성되었습니다.

-- ㅋㅋㅋ
SQL> exec shell('ls /usr/local/ora10g/admin/LUXTV/udump > /usr/local/ora10g/product/10.2.0/network/admin/myoutput.txt')

PL/SQL 처리가 정상적으로 완료되었습니다.

{CODE}

결과

{CODE:SQL}

ora10g@orastage admin$ vi /usr/local/ora10g/product/10.2.0/network/admin/myoutput.txt

ccf.sql
luxtv_ora_1072.trc
luxtv_ora_1163.trc
luxtv_ora_1178.trc
luxtv_ora_1199.trc
luxtv_ora_1235.trc
luxtv_ora_1275.trc
luxtv_ora_1304.trc
luxtv_ora_13293.trc
luxtv_ora_13369.trc
luxtv_ora_13398.trc
luxtv_ora_13427.trc
luxtv_ora_13434.trc
luxtv_ora_13463.trc
luxtv_ora_19828.trc
luxtv_ora_23102.trc
luxtv_ora_23109.trc
luxtv_ora_23140.trc
luxtv_ora_31135.trc
luxtv_ora_31172.trc
oratest_ora_10049.trc
oratest_ora_10078.trc
oratest_ora_12919.trc
oratest_ora_12946.trc
oratest_ora_12975.trc
oratest_ora_22451.trc
oratest_ora_26701.trc
oratest_ora_26708.trc
oratest_ora_26741.trc
oratest_ora_26805.trc
oratest_ora_27582.trc
oratest_ora_27617.trc
oratest_ora_27623.trc
oratest_ora_27650.trc
oratest_ora_27674.trc
oratest_ora_27693.trc
oratest_ora_27722.trc
oratest_ora_29279.trc
oratest_ora_29784.trc
oratest_ora_29950.trc
oratest_ora_29979.trc
oratest_ora_29999.trc
oratest_ora_30030.trc
oratest_ora_339.trc
oratest_ora_386.trc
oratest_ora_387.trc
oratest_ora_842.trc
oratest_ora_9204.trc
oratest_ora_9247.trc
oratest_ora_9276.trc
oratest_ora_9592.trc
oratest_ora_9605.trc
oratest_ora_9634.trc
oratest_ora_9677.trc
oratest_ora_9706.trc
oratest_ora_9979.trc
~
~
<l/ora10g/product/10.2.0/network/admin/myoutput.txt" 56L, 1156C 1,1

{CODE}

부록 1서버 2엔진시 key 값 같이 사용하면 에러 테스트( 한개의 서버에 엔진 두개 사용시( 유저별 ))

{CODE:SQL}

SID : ORA02

oracle@orastage ~$ lsnrctl status listener_ora02

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-MAR-2012 14:56:48

Copyright © 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1521)(QUEUESIZE=32)))
STATUS of the LISTENER


Alias listener_ora02
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 12-MAR-2012 14:43:46
Uptime 0 days 0 hr. 13 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /usr/local/oracle/product/11.2.0/db_1/network/log/listener_ora02.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=??????????)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "ora02" has 1 instance(s).
Instance "ora02", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@orastage ~$
oracle@orastage ~$ vi /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora

  1. listener.ora Network Configuration File: /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
  2. Generated by Oracle configuration tools.

LISTENER_ORA02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT=1521)(QUEUESIZE = 32))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)
)

SID_LIST_LISTENER_ORA02 = (SID_LIST =

  1. (SID_DESC =
  2. (SID_NAME = PLSExtProc)
  3. (ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)
  4. (PROGRAM = extproc)
  5. )
    (SID_DESC =
  6. (GLOBAL_DBNAME = ora02)
    (ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)
    (SID_NAME = ora02)
    )
    )

ADR_BASE_LISTENER_ORA02 = /usr/local/oracle/product/11.2.0/db_1
DIAG_ADR_ENABLED_LISTENER_ORA02 = OFF

ora10g@orastage ~$ lsnrctl status listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 14:58:17

Copyright © 1991, 2005, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
리스너의 상태


별칭 listener_luxtv
버전 TNSLSNR for Linux: Version 10.2.0.1.0 - Production
시작 날짜 12-3월 -2012 14:53:48
업타임 0 일 0 시간. 4 분. 29 초
트레이스 수준 off
보안 ON: Local OS Authentication
SNMP ON리스너 매개변수 파일 /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
리스너 로그 파일 /usr/local/ora10g/product/10.2.0/network/log/listener_luxtv.log
끝점 요약 청취 중...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=??????????)(PORT=1520)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
서비스 요약...
"LUXTV" 서비스는 1개의 인스턴스를 가집니다.
"LUXTV" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었습니다
ora10g@orastage ~$
ora10g@orastage ~$ vi /usr/local/ora10g/product/10.2.0/network/admin/listener.ora

  1. listener.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
  2. Generated by Oracle configuration tools.

SID_LIST_LISTENER_LUXTV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(SID_NAME = LUXTV)
)
)

LISTENER_LUXTV =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520)(QUEUESIZE = 32))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1 ))
)
)

ADR_BASE_LISTENER_LUXTV = /uar/local/ora10g/product/10.2.0
DIAG_ADR_ENABLED_LISTENER_LUXTV = OFF

ora10g@orastage ~$ lsnrctl stop listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 15:00:49

Copyright © 1991, 2005, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
명령이 성공적으로 수행되었습니다
ora10g@orastage ~$
ora10g@orastage ~$ lsnrctl status listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 15:01:05

Copyright © 1991, 2005, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
TNS-12541: TNS:리스너가 없습니다.
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 없습니다.
Linux Error: 111: Connection refused
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))에 연결되었습니다
TNS-12541: TNS:리스너가 없습니다.
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 없습니다.
Linux Error: 2: No such file or directory
ora10g@orastage ~$
ora10g@orastage ~$ vi /usr/local/ora10g/product/10.2.0/network/admin/listener.ora

  1. listener.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
  2. Generated by Oracle configuration tools.

SID_LIST_LISTENER_LUXTV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(SID_NAME = LUXTV)
)
)

LISTENER_LUXTV =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520)(QUEUESIZE = 32))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0 )) <-- 이부분 변경
)
)

ADR_BASE_LISTENER_LUXTV = /uar/local/ora10g/product/10.2.0
DIAG_ADR_ENABLED_LISTENER_LUXTV = OFF

ora10g@orastage ~$ lsnrctl status listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 15:02:47

Copyright © 1991, 2005, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
TNS-12541: TNS:리스너가 없습니다.
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 없습니다.
Linux Error: 111: Connection refused
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))에 연결되었습니다
리스너의 상태


별칭 listener_ora02
버전 TNSLSNR for Linux: Version 11.2.0.1.0 - Production
시작 날짜 12-3월 -2012 14:43:46
업타임 0 일 0 시간. 19 분. 1 초
트레이스 수준 off
보안 ON: Local OS Authentication
SNMP ON리스너 매개변수 파일 /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
리스너 로그 파일 /usr/local/oracle/product/11.2.0/db_1/network/log/listener_ora02.log
끝점 요약 청취 중...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=??????????)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
서비스 요약...
"ora02" 서비스는 1개의 인스턴스를 가집니다.
"ora02" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었습니다퍄
ora10g@orastage ~$
ora10g@orastage ~$ lsnrctl stop listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 15:03:39

Copyright © 1991, 2005, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
TNS-12541: TNS:리스너가 없습니다.
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 없습니다.
Linux Error: 111: Connection refused
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))에 연결되었습니다
TNS-01190: 해당 사용자는 요청된 리스너 명령을 실행할 권한이 없습니다.
ora10g@orastage ~$
ora10g@orastage ~$ vi /usr/local/ora10g/product/10.2.0/network/admin/listener.ora

  1. listener.ora Network Configuration File: /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
  2. Generated by Oracle configuration tools.

SID_LIST_LISTENER_LUXTV =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/local/ora10g/product/10.2.0)
(SID_NAME = LUXTV)
)
)

LISTENER_LUXTV =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ??????????)(PORT = 1520)(QUEUESIZE = 32))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0 )) <-- 수정
)
)

ADR_BASE_LISTENER_LUXTV = /uar/local/ora10g/product/10.2.0
DIAG_ADR_ENABLED_LISTENER_LUXTV = OFF

ora10g@orastage ~$ lsnrctl status listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 15:06:03

Copyright © 1991, 2005, Oracle. All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
TNS-12541: TNS:리스너가 없습니다.
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 없습니다.
Linux Error: 111: Connection refused
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))에 연결되었습니다
TNS-12541: TNS:리스너가 없습니다.
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 없습니다.
Linux Error: 2: No such file or directory
ora10g@orastage ~$
ora10g@orastage ~$ lsnrctl start listener_luxtv

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 12-3월 -2012 15:06:23

Copyright © 1991, 2005, Oracle. All rights reserved.

시작 /usr/local/ora10g/product/10.2.0/bin/tnslsnr: 잠시만 기다리세요...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
시스템 매개변수 파일은 /usr/local/ora10g/product/10.2.0/network/admin/listener.ora 입니다
/usr/local/ora10g/product/10.2.0/network/log/listener_luxtv.log (으)로 로그 메시지를 기록했습니다
리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=??????????)(PORT=1520)))
리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=??????????)(PORT=1520)(QUEUESIZE=32)))에 연결되었습니다
리스너의 상태


별칭 listener_luxtv
버전 TNSLSNR for Linux: Version 10.2.0.1.0 - Production
시작 날짜 12-3월 -2012 15:06:23
업타임 0 일 0 시간. 0 분. 0 초
트레이스 수준 off
보안 ON: Local OS Authentication
SNMP ON리스너 매개변수 파일 /usr/local/ora10g/product/10.2.0/network/admin/listener.ora
리스너 로그 파일 /usr/local/ora10g/product/10.2.0/network/log/listener_luxtv.log
끝점 요약 청취 중...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=??????????)(PORT=1520)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
서비스 요약...
"LUXTV" 서비스는 1개의 인스턴스를 가집니다.
"LUXTV" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었
{CODE}

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 상반기 - 오라클 성능 고도화 원리와 해법 스터디
  4. 부록 ExtProc