ORACLE 11g Modify Datbase Link v1.0

ORACLE 11g Modify Datbase Link v1.0

Date	Ver	Etc.
110831	v1.0

1. Database Link and Sql*Net service

Database Link 를 변경하는 작업은 다음을 의미한다.

- tnsnames.ora 의 수정

- drop database link -> create database link

database link 는 alter 작업이 없다.

2. Questions about Database Link

a. Database Link 와 tnsnames.ora 정보는 서로 독립적인가?

b. 기존 정보로 수행되고 있던 쿼리는 DB Link 가 변경되는 경우 어떻게 되는가?

c. Database Link 를 drop 하면 기존 실행플랜에 어떤일이 일어나는가?

d. tnsnames.ora 를 수정하면 기존 실행플랜에 어떤일이 일어나는가?

3. Practice

A. Database Link 와 tnsnames.ora 정보는 서로 독립적인가?

이를 확인하는 방법은 DB Link 를 생성 후 tnsnames 정보만 변경했을 때 변경된 정보를 바라보는지 확인하면 된다.

SQL> create database link dg2 connect to system identified by oracle using 'dg3';

Database link created.

SQL> select instance_name from v$instance@dg2;

INSTANCE_NAME

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

DG2

SQL> -- Modify tnsnames.ora

SQL> select instance_name from v$instance@dg2;

INSTANCE_NAME

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

DG2

SQL> alter system flush shared_pool;

System altered.

SQL> select instance_name from v$instance@dg2;

INSTANCE_NAME

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

DG2

SQL> drop database link dg2;

Database link dropped.

SQL> create database link dg2 connect to system identified by oracle using 'dg3';

Database link created.

SQL> select instance_name from v$instance@dg2;

INSTANCE_NAME

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

DG1

database link dg2 는 tnsnames dg3 를 사용한다. 수정하기 전에 tnsnames dg3 는 database dg2 를 접속하도록 기술한 상태였고 이는 v$instance 조회를 통해 확인할 수 있다. 이후 tnsnames dg3 를 database dg1 을 바라보도록 수정하였고 v$instance 조회를 했으나 값이 그대로인 걸 알 수 있었다.

이 값은 shared_pool flush 해도 영향을 받지 않는다.

다만 database link 를 재생성했을 때는 바로 변경된 기술정보로 접근함을 확인했다.

위의 경우는 세션을 유지한 상태에서의 체크였고, 세션을 종료했다 접근한 경우는 어떠한지 확인해 보았다.

SQL> select instance_name from v$instance@dg2;

INSTANCE_NAME

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

DG2

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

dg1@/home/oracle>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 09:18:46 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance@dg2;

INSTANCE_NAME

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

DG1

v$instance 를 먼저 조회하고 tnsnames 정보를 수정 후 세션을 다시 맺었다. 이후 정보를 조회하자 수정된 tnsnames 를 이용한 것을 알 수 있었다.

결론으로 database link 정보와 tnsnames 정보는 상호독립적이나 tnsnames 정보를 수정하는 경우 이 정보는 새로 맺어진 세션부터 적용된다.

B. 수행되고 있던 쿼리의 DB Link 가 변경되는 경우 어떻게 되는가?

l DB Link 가 DROP 되는 경우

#2번 세션

SQL> select count(*) from

2 (select /*+ ordered use_nl(b)*//*testing*/a.object_name, b.object_name

3 from dba_objects a, dba_objects@dg2 b);

from dba_objects a, dba_objects@dg2 b)

#1번 세션

SQL> drop database link dg2;

Database link dropped.

#2번세션 *

ERROR at line 3:

ORA-02019: connection description for remote database not found

#2번 세션에서 수행했던 쿼리의 실행플랜

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

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

| 0 | SELECT STATEMENT | | 1 | | 157K (14)| 00:31:31 | | |

| 1 | SORT AGGREGATE | | 1 | | | | | |

| 2 | NESTED LOOPS | | 4649M| | 157K (14)| 00:31:31 | | |

| 3 | VIEW | DBA_OBJECTS | 68189 | | 252 (2)| 00:00:04 | | |

| 4 | UNION-ALL | | | | | | | |

|* 5 | FILTER | | | | | | | |

|* 6 | HASH JOIN | | 72841 | 7824K| 250 (2)| 00:00:03 | | |

| 7 | INDEX FULL SCAN | I_USER2 | 87 | 348 | 1 (0)| 00:00:01 | | |

|* 8 | HASH JOIN | | 72841 | 7540K| 248 (2)| 00:00:03 | | |

| 9 | INDEX FULL SCAN | I_USER2 | 87 | 1914 | 1 (0)| 00:00:01 | | |

|* 10 | TABLE ACCESS FULL | OBJ$ | 72841 | 5975K| 246 (1)| 00:00:03 | | |

|* 11 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 | | |

|* 12 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | |

| 13 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 | | |

|* 14 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | | |

|* 15 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | | |

| 16 | NESTED LOOPS | | 1 | 6 | 2 (0)| 00:00:01 | | |

| 17 | INDEX FULL SCAN | I_LINK1 | 1 | 2 | 1 (0)| 00:00:01 | | |

|* 18 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 | | |

| 19 | REMOTE | DBA_OBJECTS | 68189 | | 2 (0)| 00:00:01 | DG2 | R->S |

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

2번세션에서 (long-run)쿼리를 수행하고 1번에서 사용 데이터베이스 링크를 삭제하자 바로 에러를 리턴하고 종료되었다.

위 경우에는 row 마다 db link 를 타고 원격지의 테이블을 풀스캔하는 실행플랜이다.

/*+ db link 를 타는 시점에 따라 에러가 날지 안날지에 대해서는 추가적인 확인이 필요하다 */

l DB Link 의 tnsnames 가 변경되는 경우

에러없이 수행이 지속되었다.

/*+ 데이터가 바뀌는가에 대해 확인할 것 */

C. (C 와 D 포함)기존 정보로 수행되고 있던 쿼리는 DB Link 가 변경되는 경우 어떻게 되는가?

여기서의 변경은 재생성과 Tnsnames 변경을 모두 포함한다.

l DB Link 재생성

SQL> select sql_id,sql_text,version_count,object_status, is_obsolete

2 from v$sqlarea where sql_text like 'select instance_name from v$instance@dg2';

SQL_ID SQL_TEXT VERSION_COUNT OBJECT_STATUS I

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

8nh9368du6jqx select instance_name 2 VALID N

from v$instance@dg2

DATABASE LINK 삭제!!!

SQL> /

SQL_ID SQL_TEXT VERSION_COUNT OBJECT_STATUS I

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

8nh9368du6jqx select instance_name 2 VALID N

from v$instance@dg2

Database link 를 사용하는 쿼리가 사용하는 shared cursor 의 상태 (object_status) 를 조회해 보았다.

중간에 database link 를 삭제해 조회했음에도 불구하고 invalid 상태로 변경되지 않았다.

추가로 database link 삭제시점에 해당 db link 를 사용하는 쿼리를 수행해 에러를 일부러 유발한 후 조회를 해도 결과는 위와 동일했다.

데이터베이스 링크를 drop 해도 shared cursor 가 invalid 로 바뀌지 않음을 알 수 있다.

l TNSNAMES 변경

DB Link 재생성과 마찬가지로 아무런 영향이 없었다.

4. References

A. 11g Release 1 (11.1) | V$SQLAREA |

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3060.htm

저작자표시 비영리

Lee

ORACLE 11g Modify Datbase Link v1.0

티스토리툴바

ORACLE 11g Modify Datbase Link v1.0

'전체' Related Articles

티스토리툴바