程序员人生 网站导航

Oracle 12c 如何连接到 CDB 和 PDB

栏目:数据库应用时间:2014-12-14 08:47:39


 

 

 

1  连接到CDB

 

和普通实例1样的连接。 指定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⑵014 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 星期3 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 星期3 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  在不同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 星期3 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 星期3 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>

 

 

 

 

 

 

 

--------------------------------------------------------------------------------------------

版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

 

AboutDave:

--------------------------------------------------------------------------------------------

QQ:      251097186

Email:    tianlesoftware@gmail.com

Blog:     http://blog.csdn.net/tianlesoftware

Weibo:    http://weibo.com/tianlesoftware

Twitter:  http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

Dave 的QQ群:

--------------------------------------------------------------------------------------------

注意:加群必须注明表空间和数据文件关系 | 不要重复加群

CNDBA_1: 104207940 (满)    CNDBA_2: 62697716 (满)   CNDBA_3: 283816689

CNDBA_4: 391125754   CNDBA_5: 62697850    CNDBA_6: 62697977   CNDBA_7: 142216823(满)

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐