Oracle veritabanı 12c’de Container veritabanına (CDB) ve Pluggable veritabanlarına (PDB) bağlanma

Root container’a bağlantı şekli, önceki versiyonlardaki veritabanı instance’ına bağlanma ile aynıdır. Veritabanı sunucusu üzerinde OS authentication kullanılarak bağlantı sağlanabilir.

[oracle@oradb ~]$ sql / as sysdba

SQLcl: Release 12.2.0.1.0 RC on Sat Dec 15 15:11:41 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> set sqlformat ansiconsole
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

V$SERVICES görüntüsünden veritabanındaki mevcut servis isimlerini tespit edebiliriz.

SQL> select NAME,PDB,CON_ID from v$services;
NAME                                       PDB       CON_ID
ORCL.svcsubnetad3.svcvcn.oraclevcn.com     CDB$ROOT  1
ORCL.svcsubnetad3.svcvcn.oraclevcn.comXDB  CDB$ROOT  1
SYS$BACKGROUND                             CDB$ROOT  1
SYS$USERS                                  CDB$ROOT  1
pdb1.svcsubnetad3.svcvcn.oraclevcn.com     PDB1      3

Aynı zamanda lsnrctl aracı ile de servis isimlerini belirlemek mümkün.

[oracle@oradb ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-DEC-2018 15:14:24

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADB.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                12-DEC-2018 19:17:31
Uptime                    2 days 19 hr. 56 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ORADB/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oradb.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ORCL/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "7cd91ac74a8a1f40e0530320000a2fa7.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.comXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "pdb1.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully


[oracle@oradb ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-DEC-2018 15:14:44

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADB.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=1521)))
Services Summary...
Service "7cd91ac74a8a1f40e0530320000a2fa7.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.comXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oradb, pid: 12265>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oradb.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=23026))
Service "pdb1.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Servis isimlerini belirledikten sonra , easy connet methodu ile aşağıdaki gibi bağlantı sağlayabiliriz.

[oracle@oradb ~]$ sqlplus ahmet/ahmet@oradb.svcsubnetad3.svcvcn.oraclevcn.com:1521/pdb1.svcsubnetad3.svcvcn.oraclevcn.com

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 15 15:17:55 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Dec 15 2018 10:40:22 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
PDB1

Naming methodu ile aşağıdaki gibi bağlanabiliriz. Öncesinde $ORACLE_HOME/network/admin/tnsnames.ora dosyasında belirtilen TNS adının var olduğundan ve doğru olduğundan emin olmamız gerekiyor.

tnsnames.ora dosyasındaki TNS bilgisi :

PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORADB.svcsubnetad3.svcvcn.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.svcsubnetad3.svcvcn.oraclevcn.com)
)
)

[oracle@oradb admin]$ sqlplus ahmet/ahmet@PDB1

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 15 15:21:00 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Dec 15 2018 15:17:55 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
PDB1

Root container’a (CDB$ROOT) bağlandıktan sonra alter session komutu ile de ilgili pluggable veritabanlarına ya da pluggable veritabanlarıdan root container’a geçmek mümkündür.

[oracle@oradb admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 15 15:22:43 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>
SQL>
SQL> select name from v$pdbs;

NAME
--------------------------------------------------------------------------------
PDB$SEED
PDB1

SQL>
SQL>
SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO
SQL>
SQL>
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

Loading

Tags: easy connect,naming method,oracle database 12,pluggable database connection,root container connection

Leave Your Comment