oracle 12c 连接到CDB、PDB、在CDB与PDB容器切换

访问量:1851 创建时间:2021-04-08

参考链接：https://www.cnblogs.com/andy6/p/6815918.html

查看oracle数据库当前的服务

Oracle 数据库 12 c 多租户选项允许单个容器数据库（CDB）来承载多个单独的可插拔数据库 (PDB)。

V$SERVICES视图可以显示数据库中的可用服务。

SQL> col pdb for a40
SQL> col name for a30;
SQL> SELECT name, pdb FROM v$services ORDER BY name;

NAME                           PDB
------------------------------ ----------------------------------------
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
esbqas                         CDB$ROOT
esbqasXDB                      CDB$ROOT
esbqaspdb                      ESBQASPDB

lsnrctl status 显示可用的服务

[oracle@esbqasdb oracle]$ lsnrctl status
####省略部分输出
Listener Parameter File   /oracle/u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /oracle/u01/app/oracle/diag/tnslsnr/esbqasdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=esbqasdb)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "bf6def21e4ac4674e0538034a8c0a46b" has 1 instance(s).
  Instance "esbqas", status READY, has 1 handler(s) for this service...
Service "esbqas" has 1 instance(s).
  Instance "esbqas", status READY, has 1 handler(s) for this service...
Service "esbqasXDB" has 1 instance(s).
  Instance "esbqas", status READY, has 1 handler(s) for this service...
Service "esbqaspdb" has 1 instance(s).
  Instance "esbqas", status READY, has 1 handler(s) for this service...
The command completed successfully

链接容器数据库 (CDB)

sqlplus语法:

sqlplus username/password  
#如：普通用户登录  
sqlplus scott/tiger

sqlplus username/password@net_service_name 
#如: 
sqlplus scott/tiger@orcl

sqlplus  username/password as sysdba 
#如：
sqlplus sys/admin as sysdba
sqlplus username/password@host:port/sid

连接到数据库的根容器和连接以前版本的单个数据库实例相同。在数据库服务器上，您可以使用操作系统身份验证。

linux服务器本机登陆：

[oracle@esbqasdb oracle]$ sqlplus / as sysdba   
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>

windows 安装sqlplus登陆远程oracle服务器：

下载地址

下载 instantclient-sqlplus-windows.x64-19.10.0.0.0dbru.zip和 instantclient-basic-windows.x64-19.10.0.0.0dbru.zip 创建目录C:\oracleclient\ ，将上面2个压缩文件解压到C:\oracleclient\的当前目录

配置环境变量

TNS_ADMIN=C:\Program Files\instantclient_11_2\network\admin
NLS_LANG=AMERICAN_AMERICA.UTF8
增加path环境变量内容： C:\oracleclient\instantclient_19_10

登陆命令：

C:\Users\2019051954>sqlplus system/abcdefg@192.168.52.128:1521/esbqas

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 8 14:38:09 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, 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

还可以配置tnsnames.ora 来省略一些链接信息，这里不再演示

注意：PDB 具有 SYSDBA、 SYSOPER、 SYSBACKUP 或 SYSDG 特权的用户可以连接到已关闭的 PDB。 PDB 的所有其他用户（CONNECT SESSION权限的用户）仅可以连接已经打开的PDB。

容器间切换

[oracle@esbqasdb oracle]$ sqlplus / as sysdba
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ESBQASPDB                      READ WRITE NO

SQL> ALTER SESSION SET container=ESBQASPDB;
Session altered.

SQL>  show con_name
CON_NAME
------------------------------
ESBQASPDB

SQL> alter session set container=cdb$root;
Session altered.

SQL>  show con_name
CON_NAME
------------------------------
CDB$ROOT

<< 上一章节

下一章节 >>

登陆评论: 使用GITHUB登陆