当前位置:首页 > 开发 > 数据库 > 正文

Connecting to Container Databases (CDB) and Pluggable Databases (PDB)

发表于: 2015-02-04   作者:czmmiao   来源:转载   浏览次数:
摘要: The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article describes how to connect to containe

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article describes how to connect to container databases (CDB) and pluggable databases (PDB).
Connecting to a Container Database (CDB)
Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.
$ export ORACLE_SID=cdb1
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 15:29:49 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
You can connect to other common users in similar way.
SQL> CONN system/password
Connected.
The V$SERVICES views can be used to display available services from the database.
COLUMN name FORMAT A30
SELECT name, pdb
FROM   v$services
ORDER BY name;
NAME                   PDB
------------------------------ ------------------------------
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
cdb1                           CDB$ROOT
cdb1XDB                        CDB$ROOT
pdb1                           PDB1
pdb2                           PDB2
6 rows selected.
The lsnrctl utility allows you to display the available services from the command line.
$ lsnrctl service
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 20-MAY-2014 09:01:34
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "cdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "cdb1XDB" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=ol6-121.localdomain)(PORT=21196))
Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "pdb2" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
Connections using services are unchanged from previous versions.
SQL> -- EZCONNECT
SQL> CONN system/password@//localhost:1521/cdb1
Connected.
SQL> -- tnsnames.ora
SQL> CONN system/password@cdb1
Connected.
The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )
Displaying the Current Container
The SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display the current container name and ID respectively.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW CON_ID
CON_ID
------------------------------
1
They can also be retrieved using the SYS_CONTEXT function.
SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
FROM   dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SELECT SYS_CONTEXT('USERENV', 'CON_ID')
FROM   dual;
SYS_CONTEXT('USERENV','CON_ID')
--------------------------------------------------------------------------------
1
Switching Between Containers
When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.
SQL> ALTER SESSION SET container = pdb1;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDB1
SQL> ALTER SESSION SET container = cdb$root;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
Connecting to a Pluggable Database (PDB)
Direct connections to pluggable databases must be made using a service. Each pluggable database automatically registers a service with the listener. This is how any application will connect to a pluggable database, as well as administrative connections.
SQL> -- EZCONNECT
SQL> CONN system/password@//localhost:1521/pdb1
Connected.
SQL>
SQL> -- tnsnames.ora
SQL> CONN system/password@pdb1
Connected.
The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.
PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB. All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the CONNECT SESSION privilege to enable connections.
JDBC Connections to PDBs
It has already been mentioned that you must connect to a PDB using a service. This means that by default many JDBC connect strings will be broken. Valid JDBC connect strings for Oracle use the following format.
# Syntax
jdbc:oracle:thin:@[HOST][:PORT]:SID
jdbc:oracle:thin:@[HOST][:PORT]/SERVICE
# Example
jdbc:oracle:thin:@ol6-121:1521:pdb1
jdbc:oracle:thin:@ol6-121:1521/pdb1
When attempting to connect to a PDB using the SID format, you will receive the following error.
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Ideally, you would correct the connect string to use services instead of SIDs, but if that is a problem the USE_SID_AS_SERVICE_listener_name listener parameter can be used.
Edit the "$ORACLE_HOME/network/admin/listener.ora" file, adding the following entry, with the "listener" name matching that used by your listener.
USE_SID_AS_SERVICE_listener=on
Reload or restart the listener.
$ lsnrctl reload
Now both of the following connection attempts will be successful as any SIDs will be treated as services.
jdbc:oracle:thin:@ol6-121:1521:pdb1
jdbc:oracle:thin:@ol6-121:1521/pdb1

参考至:http://oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1.php

如有错误,欢迎指正

邮箱:czmcj@163.com

Connecting to Container Databases (CDB) and Pluggable Databases (PDB)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
实例和数据库的关系,其实真正的世界也有相似的关系。一个实例可以被认为是一座通往数据库的桥,而数
先说基本用法: 先按11G之前进行 conn / as sysdba; create user test identifed by test; ORA-6509
ORACLE 12C新特性——CDB与PDB 原文链接:http://www.cnblogs.com/kerrycode/p/3386917.html2013-10
本章内容着重对了NOSQL和RDBMS(关系型数据库管理系统)的不同,以及其各自背后设计时考虑的因素。
声明: 原创作品, 转载时请注明文章来自 SAP师太 技术博客( 博/客/园www.cnblogs.co
SQL Server 2008上安装Northwind和pubs数据库 1. 下载安装文件 下载地址:http://www.microsoft.com
SQL Server 2008上安装Northwind和pubs数据库 1. 下载安装文件 下载地址:http://www.microsoft.com
关于这个数据库也就是样例数据库,数据库,数据库,最可怕的就是没有数据了,对吧?没有数据你学个
A couple of weeks ago I was evaluating the possibility to use Spring Boot, Spring Data JPA an
进入到非CDB数据库中 查看数据文件的位置 [Oracle@shuang ~]$ sqlplus / as sysdba SQL*Plus: Relea
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号