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
相关推荐
OPC:Error connecting to opc 2.0 server browser 没有注册类别 问题解决
Connecting to 222.73.218.96:22... Connection established. To escape to local shell, press 'Ctrl+Alt+]'. Last login: Tue Oct 30 17:09:29 2012 from 114.221.117.202 [root@S2-PC ~]# ifconfig eth0 Link ...
Bruce Goldstein's COGNITIVE PSYCHOLOGY: CONNECTING MIND, RESEARCH, AND EVERYDAY EXPERIENCE connects the study of cognition to your everyday life. A wealth of concrete examples and illustrations help ...
Mobile Tutorial_ Connecting to an Enterprise Database from a Mobile Client (iOS and Android) - RAD Studio.pdf
Connecting Generative Adversarial Network and Actor-Critic Methods.pdf
How to Connecting HTML Help to C++/MFC Programs
Administrators and developers will learn how to use PowerShell to create and manage Azure websites, virtual machines, SQL databases, and so on. Starting with an introduction to Windows PowerShell, ...
Connecting to databases, executing SQL queries, and using form and table views Advanced model/view programming: custom views, generic delegates, and more Implementing online help, ...
Connecting the Atmel ARM-based Serial Synchronous Controller (SSC) to an I2S-compatible Serial Bus
解决license无法启动问题 1、检查安装的license是否是crack下的可破解版 2、检查service.TXT是否已替换,用户名是否已更改、文件是否完整.....
and drag-and-drop Building custom widgets: Widget Style Sheets, composite widgets, subclassing, and more Making the most of Qt 4.2's new graphics/view architecture Connecting to databases, executing ...
ROS系统中导航规划包navigation架构下的局部规划器插件eband_local_planner算法对应的论文。http://wiki.ros.org/eband_local_planner
如何使用Excel的数据透视表的功能展现SAP BW中的内容
We currently support MySQL, Oracle, ... We have had successful reports of connecting to Progress, SQLite and DB2 via ODBC. We hope more people will contribute drivers to support other databases. ...
问题描述 更新系统中的包管理工具卡住 可能原因 网络不通 –检查网络是否正常通 系统自带源有问题 –更改系统源 解决方法 编辑 /etc/apt/sources.list文件,建议修改前对文件做备份。 # cat /dev/null > /etc/apt/...
This book will show you how to manipulate data in modern R structures and includes connecting R to data bases such as SQLite, PostgeSQL, and MongoDB. The book closes with a hands-on section to get R ...
windows系统下最好用的SSH服务器软件
公司电脑OPCClient连接OPC服务器时报错 Error connecting to OPC server Browser.没有注册类 该组件解决了此问题。