[기타] DB Link 구성

설명

• 페이지 '다중 인스턴스 구성 (clone 생성 및 복원)' 의 후속 작업
  • [기타]다중 인스턴스 구성 (clone 생성 및 복원) :: 일상및기술기록 (tistory.com)
• 단일서버내 다중 인스턴스 (Src. : EHR / Dst. : EHRCLONE)

LISTENER.ORA 작업 (예시)

•  
• [root@localhost ~]# cd $TNS_ADMIN

  [root@localhost ~]# su - oracle

  [localhost.localdomain:/app/oracle]cd $TNS_ADMIN

  [localhost.localdomain:/app/oracle/product/10.2.0/db_1/network/admin]vi listener.ora

  -- 하단의 CLONE SID를 기존 LISTENER List에 추가

           SID_LIST_LISTENER =

           (SID_LIST =

           (SID_DESC =

           (SID_NAME =EHR )

           (ORACLE_HOME = /app/oracle/product/10.2.0/db_1))

           (SID_DESC =

           (SID_NAME =CLONE )

           (ORACLE_HOME = /app/oracle/product/10.2.0/db_1))

  )

TNSNAME.ORA 작업 (예시)

• [root@localhost ~]# cd $TNS_ADMIN

  [root@localhost ~]# su - oracle

  [localhost.localdomain:/app/oracle]cd $TNS_ADMIN

  [localhost.localdomain:/app/oracle/product/10.2.0/db_1/network/admin]vi listener.ora

  -- 하단의 CLONEDB를 (LISTENER 내 명시된) CLONE을 바라보도록 설정

           EHR =

           (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.109.133)(PORT = 1521))

           (CONNECT_DATA =

           (SERVER = DEDICATED)

           (SERVICE_NAME = EHR)

           (SID=EHR)

           )

           )

           CLONEDB =

           (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.109.133)(PORT = 1521))

           (CONNECT_DATA =

           (SERVER=DEDICATED)

           (SERVICE_NAME = CLONE)

           (SID=CLONE)

           )

           )
   

LISTNER 재시작 (예시)

• [localhost.localdomain:/app/oracle/product/10.2.0/db_1/network/admin]lsnrctl

  LSNRCTL> stop listener

           Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.109.133)(PORT=1521)))

           The command completed successfully

   

  LSNRCTL> start listener

           Starting /app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

   

           -중략-

           Services Summary...

           Service "CLONE" has 1 instance(s).

           Instance "CLONE", status UNKNOWN, has 1 handler(s) for this service...

           Service "EHR" has 1 instance(s).

           Instance "EHR", status UNKNOWN, has 1 handler(s) for this service...

           The command completed successfully

   

  --TNSPING 테스트

  [localhost.localdomain:/app/oracle/product/10.2.0/db_1/network/admin]tnsping clonedb

           --중략--

           Used TNSNAMES adapter to resolve the alias

           Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.109.133)         (PORT = 1521)) (CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = CLONE) (SID=CLONE)))

           OK (0 msec)
   

DBLink 등록 (예시)

• SQL> create database link clink

  connect to dba_jyoh identified by "AAaa!!11"

  using 'CLONEDB'; 

           Database link created.

   

  SQL> set linesize 1000

  set pagesize 100

  column OWNER format a10

  column DB_LINK format a20

  column USERNAME format a20

  column HOST format a20

  SQL> SELECT * FROM DBA_DB_LINKS;

   

           OWNER DB_LINK USERNAME HOST CREATED

           ---------- -------------------- -------------------- -------------------- ------------

           SYS CLINK DBA_JYOH CLONEDB 07-MAR-22

   

  -- 조회

  SQL> select * from dba_jyoh.utest01@CLINK;

   

           NO

           ----------

           2