来源：David Dai -- Focus on Oracle
连接到CDB
12c debug
和普通实例一样的连接。 指定ORACLE_SID 以后可以使用OS认证，也可以使用密码进行连接。
[oracle@Ora12c /]$ echo $ORACLE_SID
cndba
[oracle@Ora12c /]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production onMon Apr 28 11:33:43 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL> conn system/oracle
Connected.
SQL>
--查看CDB中可用的service：
SQL> COLUMN name FORMAT A30
SQL> SELECT name,pdb
2 FROM v$services
3 ORDER BY name;
NAME PDB
------------------------------------------------------------
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
cndba CDB$ROOT
cndbaXDB CDB$ROOT
pcndba2 PCNDBA2
pdbcndba PDBCNDBA
6 rows selected.
--通过lsnrctl 也可以判断：
[oracle@Ora12c /]$ lsnrctl service
LSNRCTL for Linux: Version 12.1.0.1.0 -Production on 28-APR-2014 11:35:31
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "cndba" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cndbaXDB" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: Ora12c, pid: 10085>
(ADDRESS=(PROTOCOL=tcp)(HOST=Ora12c)(PORT=14696))
Service "pcndba2" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdbcndba" has 1instance(s).
Instance "cndba", status READY, has 1 handler(s) for thisservice...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@Ora12c /]$
通过这些service，就可以远程连接CDB。
--EZCONNECT
C:UsersDave>sqlplussystem/oracle@192.168.1.10:1521/cndba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:36:48 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL>
--通过TNSNAMES.ORA连接：
在tnsnames.ora 中配置如下：
cndba =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cndba)
)
)
--连接：
C:UsersDave>sqlplussystem/oracle@cndba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:40:01 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL>
2.
登录到Oracle database 12c多租户数据库中,第一个要确定的事情---我当前连对数据库
时间：2017-03-19 07:06:36
Oracle database 12c多租户出现之后,Oracle数据库第一次出现了类似于"多库结构"的概念.
多库结构的意思是在一个数据库服务下有多个数据库在运行.
IBM DB2,SAP Sybase ASE,MS SQLServer,MySQL,PostgreSQL均是多库结构.
既然是一个数据库服务下有多个数据库,那么就存在错误连接到不是自己想登陆的数据库下的可能性.
因此,登陆到Oracle数据库12c之后,第一个需要确认的是自己当前登陆的是哪个数据库.
C:Documents and SettingsAdministrator>sqlplus system/aaaaaa@highgo1
SQL*Plus: Release 11.2.0.4.0 Production on 星期六 3月 18 22:21:24 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show con_name ---------->>>>第一种:由于这是11gR2的client,因此无法识别con_name
concat "." (hex 2e)
SP2-0158: 未知的 SHOW 选项 "_name"
SQL> select sys_context('Userenv','Con_Name') "Container DB" from dual;
Container DB
--------------------------------------------------------------------------------
HIGHGO1
SQL>--------------->>>>第二种就是如上的sql语句
如上两种摘自:
Oracle Multitenant Option - 12c : Frequently Asked Questions (文档 ID 1511619.1)
如下SQL经过我测试,也是可以的,可以作为第三种方法
SQL> select sys_context('Userenv','db_name') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
HIGHGO1
SQL>
3. 在不同Container database中切换
在12c的架构中，因为有CDB和 PDB的存在，所以会有很多不同的container，所以在连接到不同的container时，就需要进行切换。
--查看当前的container：
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT
SQL> set lin 140
SQL> select con_id, dbid,guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ------------------------------------------ ---------- ----------
2 4088301206 F7C1E3C96BBF0585E0430A01A8C05459 PDB$SEED READ ONLY
3 426143573F7C209EB1DFC0854E0430A01A8C0B787 PDBCNDBA READ WRITE
4 1231796139 F812DE1B6A8F363AE0430A01A8C0C759 PCNDBA2 READ WRITE
--切换container：
SQL> alter session setcontainer=pcndba2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PCNDBA2
3 连接到PDB
--EZCONNECT:
C:UsersDave>sqlplussystem/oracle@192.168.1.10:1521/pcndba2
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:54:30 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL>
--TNSNAMES.ora
在tnsnames.ora 中添加如下内容：
pcndba =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pcndba2)
)
)
C:UsersDave>sqlplussystem/oracle@pcndba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 4月 30 11:55:50 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL>