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

ORACLE
15 Ara by Ahmet Duruöz

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

ByAhmet Duruöz

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir