<< SYS SESSION >>

[oracle@oracle ~]$ cd $ORACLE_HOME/network/admin

[oracle@oracle admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin



■ static 서비스 등록
listener.ora에 메뉴얼하게 서비스 등록해준다.

# 리스너 백업
  [oracle@oracle admin]$ cp listener.ora listener.bak
# 리스너 구성정보 수정
  [oracle@oracle admin]$ vi listener.ora

SID_LIST_L01 =
    (SID_LIST = 
        (SID_DESC = 
            (GLOBAL_DBNAME = ora11g)
            (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
            (SID_NAME = ora11g)
        )
    )   -- L01이 담당할 DB 서비스 정보

L01 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    )   -- 리스너 이름

ADR_BASE_LISTENER = /u01/app/oracle

: 내용 추가
# static하게 구성한 listener 시작
  - 리스너명을 붙여서 호출

[oracle@oracle admin]$ lsnrctl start L01
STATUS of the LISTENER
------------------------
Alias                     L01
...
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1522)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


# static하게 구성한 listener 상태 확인
[oracle@oracle admin]$ lsnrctl status L01


# static하게 구성한 listener 중지
[oracle@oracle admin]$ lsnrctl stop L01
  # 기본 listener 상태 확인
[oracle@oracle admin]$ lsnrctl status


# 기본 listener 중지
[oracle@oracle admin]$ lsnrctl stop


# 기본 listener 시작
[oracle@oracle admin]$ lsnrctl start





■ client 환경에서 오라클에 접속

1. 간단한 연결 (Easy connect)
  - 기본적으로 활성화
- 클라이언트 측 구성이 필요없다.
- TCP/IP 만 지원
- HOST, PORT, SID, 프로토콜은
2. 로컬 이름 지정
  - 클라이언트 측 이름 분석(names resolution) 필요
- 모든 oracle net 프로토콜 지원


# XE 홈 디렉터리
C:\> oraclexe\app\oracle\product\11.2.0\server


# TNS 구성정보 수정
C:\> $ORACLE_HOME\network\ADMIN\tnsnames.ora
  
ora11g =        -- 로컬이름
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )

: 내용 추가


** HOST = 192.168.56.102 : 호스트이름으로는 접속이 안된다. 아이피주소로 활용하자.

 



■ 데이터베이스 링크
데이터베이스 링크는 다른 데이터베이스의 객체에 액세스할 수 있도록하는 데이터베이스 객체

1. client XE DB에 tnsnames.ora 내용 추가
  << CMD >>

C:\> $ORACLE_HOME/network/admin/tnsnames.ora

ora11g =            -- TNS이름
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )
2. XE DB에서 remote ora11g로 접속
  << CMD >>

# LINUX ORACLE 접속
C:\> sqlplus hr/hr@ora11g        -- TNS이름
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


# XE 접속
C:\> sqlplus hr/hr
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production


# 계정 이동
SQL> conn hr/hr@ora11g
3. XE에 있는 hr유저에서 시스템 권한 확인
  << XE - HR >>
SQL> SELECT * FROM session_privs;
PRIVILEGE
---------------------
CREATE DATABASE LINK
...
4. XE의 hr유저에 데이터베이스 링크를 생성
  << XE - HR >>

CREATE DATABASE LINK ora11g_hr  -- DB Link 이름
CONNECT TO hr IDENTIFIED BY hr  -- 접속하려는 계정 정보
USING 'ora11g';                 -- TNS 이름


SELECT * FROM user_db_links;
DB_LINK              USERNAME   PASSWORD   HOST       CREATED
-------------------- ---------- ---------- ---------- ----------
ORA11G_HR            HR                    ora11g     24/01/05


SELECT * FROM departments@ora11g_hr;      -- DB Link 이름
>> 조회 가능
# TABLE JOIN
  << ora11g - INSA01 >>

SELECT * FROM session_privs;
PRIVILEGE
-----------------
SELECT ANY TABLE
...


CREATE TABLE insa01.dept
AS
SELECT * FROM hr.departments;


SELECT * FROM insa01.dept;
>> 조회 가능
  << XE - HR >>

CREATE DATABASE LINK ora11g_insa01
CONNECT TO insa01 IDENTIFIED BY oracle
USING 'ora11g';


SELECT * FROM user_db_links;
DB_LINK              USERNAME   PASSWORD   HOST       CREATED
-------------------- ---------- ---------- ---------- ----------
ORA11G_INSA01        INSA01                ora11g     24/01/05
ORA11G_HR            HR                    ora11g     24/01/05


SELECT * FROM dept@ora11g_insa01;
>> 조회 가능
  # JOIN
XE : hr.employees
ora11g : insa01.dept


SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, dept@ora11g_insa01 d
WHERE e.department_id = d.department_id
AND e.job_id = 'AD_VP';
EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME               
----------- ------------------------- ------------------------------
        102 De Haan                   Executive
        101 Kochhar                   Executive
5. DB LINK 삭제
  DROP DATABASE LINK ora11g_insa01;

 

6. PUBLIC DATABASE LINK 생성
  - DBA 고유 권한
- PUBLIC : 모든 유저가 사용 가능

<< XE - DBA >>
CREATE PUBLIC DATABASE LINK ora11g_insa01
CONNECT TO insa01 IDENTIFIED BY oracle
USING 'ora11g';

SELECT * FROM dba_db_links;
OWNER   DB_LINK        USERNAME  HOST    CREATED
------- -------------- --------- ------- ---------
PUBLIC  ORA11G_INSA01  INSA01    ora11g  24/01/05
...
7. TABLE JOIN
  << XE - HR >>

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, dept@ora11g_insa01 d
WHERE e.department_id = d.department_id
AND e.job_id = 'AD_VP';
EMPLOYEE_ID  LAST_NAME  DEPARTMENT_NAME
------------ ---------- ----------------
102          De Haan    Executive
101          Kochhar    Executive
8. PUBLIC SYNONYM 생성
  << XE - DBA >>
CREATE PUBLIC SYNONYM ora11g_dept FOR dept@ora11g_insa01;

SELECT * FROM all_synonyms WHERE synonym_name = 'ORA11G_DEPT';
OWNER   SYNONYM_NAME  TABLE_OWNER  TABLE_NAME  DB_LINK
------- ------------- ------------ ----------- --------------
PUBLIC  ORA11G_DEPT                DEPT        ORA11G_INSA01
9. TABLE JOIN
  << XE - HR >>

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, Ora11g_Dept d
WHERE e.department_id = d.department_id
AND e.job_id = 'AD_VP';
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
------------------------------------------------------------------------------
102         De Haan     Executive
101         Kochhar     Executive
10. 개체 삭제
  << XE - SYS >> 

DROP PUBLIC SYNONYM ora11g_dept;

DROP PUBLIC DATABASE LINK ora11g_insa01;


* 계정접속 시 @TNS이름
* DB조회 시 @DBLink이름

# 계정접속에 주의하자
SQL> conn hr/hr
Connected.

SQL> show user
USER is "HR"		 SQL> conn hr/hr@ora11g
Connected.

SQL> show user
USER is "HR"






■ server 구성

1. DEDICATED SERVER PROCESS
- user process 와 server process 가 1:1 환경으로 구성
- 하나의 server process 가 하나의 user process 만 처리하는 프로세스

dedicated server 환경에서는 server process 안에 PGA안에 UGA가 있다.

PGA(Program Global Area)
    - UGA(User Global Area)
        - user session data
        - cursor state          
- stack space (바인드 변수 생성)


2. SHARED SERVER PROCESS
- user process 와 server process 가 N:1 환경으로 구성
- 하나의 server process 가 여러 user process 를 처리하는 프로세스
- user process 는 dispatcher 와 연결하는 방식
- pmon 프로세스는 dispatcher 정보를 listener 한테 등록한다.
- 이점
    - instance 에 대한 process 수를 줄인다.
    - 메모리 사용량과 시스템 오버헤드를 줄인다.

shared server 환경에서는 server process 안에 PGA에는 stack space(바인드 변수 생성) 영역만 있고 UGA가 SGA영역에 저장 한다.

PGA(Program Global Area)
    - stack space (바인드 변수 생성)

# shared server 환경에서 수행하지 말아야 할 작업
- 데이터베이스 관리
- 백업 및 복구 작업
- 대량로드 작업
- 일반적으로 dba 작업은 dedicated server 환경에서 수행하자.


# shared server 환경으로 구성

1. dispatchers 구성
  << ora11g - SYS >>

SQL> show parameter dispatchers
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=ora11gXDB)
max_dispatchers                      integer


# instance 시작 시 생성 할 dispatchers 수
ALTER SYSTEM SET dispatchers = '(protocol=tcp)(dispatchers=2)';


# 최대로 사용할 수 있는 dispatchers 수
ALTER SYSTEM SET max_dispatchers = 10;
개당 100건 정도 담당 

SQL> show parameter dispatchers
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (protocol=tcp)(dispatchers=2)
max_dispatchers                      integer     10
2. shared server process 구성
  << ora11g - SYS >>

SQL> show parameter shared_servers
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer
shared_servers                       integer     1


# instance 시작 시에 생성 할 shared server process 수
alter system set shared_servers = 2;


# 최대로 사용할 수 있는 shared server process 수
alter system set max_shared_servers = 10;


SQL> show parameter shared_servers
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     10
shared_servers                       integer     2

>> 바로 가동

3. client - tnsnames.ora 수정
  << Win File >>

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora

ora11g_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )    -- shared server

ora11g_d =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

4. ora11g - listener.ora 수정
  << SQLPLUS - SYS >>

[oracle@oracle admin]$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

: 기본값 다시 추가


[oracle@oracle admin]$ lsnrctl stop

[oracle@oracle admin]$ lsnrctl start

[oracle@oracle admin]$ lsnrctl status

[oracle@oracle admin]$ sqlplus / as sysdba

SQL> alter system register;
>> pmon한테 프로세스 작업을 빨리 수행해라.

[oracle@oracle admin]$ lsnrctl status

[oracle@oracle admin]$ lsnrctl service
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status READY, has 3 handler(s) for this service...
    Handler(s):
      "D001" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=15941))
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=18127))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

* D001, D000 -> DISPATCHER 2개
5. 계정 연결
  > sqldeveloper > ora11g : hr - TNS - ora11g_s

> CMD > sqlplus hr/hr@ora11g_s

> CMD 새창 > sqlplus hr/hr@ora11g_d


<<ora11g - sys >>

SELECT * FROM v$session;
USERNAME  SERVER     PROGRAM        계정 연결 방법
--------- ---------- -------------- --------------------------------------------
HR        NONE       SQL Developer  sqldeveloper > ora11g : hr : TNS : ora11g_s
HR        NONE       sqlplus.exe    CMD > sqlplus > hr/hr@ora11g_s
HR        DEDICATED  sqlplus.exe    CMD > sqlplus > hr/hr@ora11g_d

>> ora11g_s : shared server 환경으로 구성



 

# PORT 수정, shared 구성
  << WIN File >>

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora

ora11g_d =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED) 
      (SERVICE_NAME = ora11g)
    )
  )        -- dedicated server

ora11g_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )        -- shared server 

: wq

[oracle@oracle admin]$ lsnrctl stop L01

[oracle@oracle admin]$ lsnrctl start L01

[oracle@oracle admin]$ lsnrctl status L01

[oracle@oracle admin]$ lsnrctl service L01
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.102)(PORT=1522)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully


>>> sqldeveloper TNS - ora11g_s 연결이 안된다?!

>>> 해결

<< ora11g - SYS >>

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string
remote_listener                      string


# listener 기본값으로 지정
ALTER SYSTEM SET local_listener = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))';

[oracle@oracle admin]$ lsnrctl service L01
...
Services Summary...
Service "ora11g" has 2 instance(s).
  Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "ora11g", status READY, has 3 handler(s) for this service...
    Handler(s):
      "D001" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER </machine: oracle, pid: 11587>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=29475))
      "D000" established:0 refused:0 current:1 max:1022 state:ready
         DISPATCHER </machine: oracle, pid: 11585>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=24695))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

 

저작자표시

'Oracle 11g > Oracle DB' 카테고리의 다른 글

58 ORACLE SQL Loader, Listener  (1) 2024.01.04
57 ORACLE Constraints, Spool, SQL Loader  (1) 2024.01.03
56-2 ORACLE 종속성  (0) 2023.12.28
55 ORACLE Audit, Trigger  (1) 2023.12.27
54-2 ORACLE Audit  (1) 2023.12.26
'Oracle 11g/Oracle DB'의 다른글
  • 현재글59 ORACLE Listener, DB Link, Server

티스토리툴바