하나의 호스트에 2개의 DB인스턴스 실행

하나의 호스트에 2개의 DB인스턴스 실행

작성자: 강명규
OS: Linux 2.2.14
Oracle: Oracle 8.1.5

dev, dev2 2개의 Database가 있다.
기존 dev라는 DB가 있는 상황에서 dev2를 생성했다.
이는 ORACLE_SID만 주의해서 생성하면 되므로 이에 대한 설명은 생략한다.
설명 요청하면 다시 여기에 적도록 하겠습니다.

리스너설정
[oracle@dev2 admin]$ cat listener.ora
# LISTENER.ORA Configuration File:/u01/app/oracle/product/8.1.5/network/admin/listener.ora
# Generated by Oracle Net8 Assistant

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dev2.dbakorea.pe.kr)(PORT = 1521))
    (PROTOCOL_STACK =
      (PRESENTATION = TTC)
      (SESSION = NS)
    )
  )

sid_list_listener=
(sid_list=
        (sid_desc=
                (global_dbname=dev.dbakorea.pe.kr)
                (sid_name=dev)
                (oracle_home=/u01/app/oracle/product/8.1.5)
        )
        (sid_desc=
                (global_dbname=dev2.dbakorea.pe.kr)
                (sid_name=dev2)
                (oracle_home=/u01/app/oracle/product/8.1.5)
        )
)

클라이언트측 설정(동일 호스트라 크게 의미는 없다)
[oracle@dev2 admin]$ cat tnsnames.ora 
# TNSNAMES.ORA Configuration File:/u01/app/oracle/product/8.1.5/network/admin/tnsnames.ora
# Generated by Oracle Net8 Assistant

DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev.dbakorea.pe.kr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev.dbakorea.pe.kr)
    )
  )

DEV2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev2.dbakorea.pe.kr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev2.dbakorea.pe.kr)
    )
  )

DEV2_MTS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev2.dbakorea.pe.kr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev2.dbakorea.pe.kr)
      (SERVER=SHARED)
    )
  )
  

DB ALIAS(tnsnames.ora - Net8을 이용한 접속)를 이용하여 접속시
[oracle@dev2 admin]$ echo $ORACLE_SID
dev
[oracle@dev2 admin]$ sqlplus system/xxxxxx@dev

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Sep 17 21:06:18 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Release 8.1.5.0.2 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> select name from v$database;

NAME
---------
DEV

SQL> quit
Disconnected from Oracle8i Release 8.1.5.0.2 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

[oracle@dev2 admin]$ echo $ORACLE_SID
dev
[oracle@dev2 admin]$ sqlplus system/xxxxxx@dev2

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Sep 17 21:07:43 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Release 8.1.5.0.2 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> select name from v$database;

NAME
---------
DEV2

SQL> 




DB ALIAS없이 접속시(Net8이용하지 않음)
[oracle@dev2 admin]$ echo $ORACLE_SID;         
dev
[oracle@dev2 admin]$ sqlplus system/xxxxxx

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Sep 17 21:10:29 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Release 8.1.5.0.2 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> select name from v$database;

NAME
---------
DEV

SQL> quit
Disconnected from Oracle8i Release 8.1.5.0.2 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production
[oracle@dev2 admin]$ export ORACLE_SID=dev2
[oracle@dev2 admin]$ echo $ORACLE_SID
dev2
[oracle@dev2 admin]$ sqlplus system/xxxxxx

SQL*Plus: Release 8.1.5.0.0 - Production on Mon Sep 17 21:10:54 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Release 8.1.5.0.2 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> select name from v$database;

NAME
---------
DEV2

SQL> 




PS>
리스너를 각각 다른 포트로 사용하고 싶으면 다음과 같이 한다.
김영선님꼐서 질문하신 내용이었습니다.


[listener.ora의 내용]

HAHA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dev.dbakorea.pe.kr)(PORT = 1522))
  )

sid_list_HAHA =
(sid_list=
        (sid_desc=
                (global_dbname=dev.dbakorea.pe.kr)
                (sid_name=dev)
                (oracle_home=/u01/app/oracle/product/8.1.5)
        )
)

HOHO =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev2.dbakorea.pe.kr)(PORT = 1521))
    )
  )


sid_list_HOHO =
(sid_list=
        (sid_desc=
                (global_dbname=dev2.dbakorea.pe.kr)
                (sid_name=dev2)
                (oracle_home=/u01/app/oracle/product/8.1.5)
        )
)



[리스너 기동]

lsnrctl start HAHA
lsnrctl start HOHO


[tnsnames.ora] 의 내용

DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev.dbakorea.pe.kr)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev.dbakorea.pe.kr)
    )
  )

DEV2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dev2.dbakorea.pe.kr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev2.dbakorea.pe.kr)
    )
  )



[접속]
sqlplus kang/maddog@dev
sqlplus kang/maddog@dev2