4.1 基本管理
4.1.1查看基本信息
多租户环境下的数据库状态查看,启停,打开,关闭等,操作如下:
查看当前数据库是否为多租户数据库,v$database的cdb字段显示yes,表明当前数据库为一个CDB数据库:
SQL> select name,cdb from v$database;
查看当前的CDB数据库中pdb的状态:
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ----------2 3395681427 PDB$SEED READ ONLY3 4023759499 PDB_ZY MOUNTED4 3391246397 PDB_DY READ WRITE
SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;PDB_ID PDB_NAME DBID STATUS CREATION_SCN---------- ------------------------------ ---------- --------- ------------2 PDB$SEED 3395681427 NORMAL 2423 PDB_ZY 4023759499 NORMAL 14495534 PDB_DY 3391246397 NORMAL 1676453
可以看到数据库中当前存在2个PDB,分别为pdb_zy和pdb_dy。他们的状态一个为mounted一个为redad wirte。另外还有一个PDB$SEED,在这里可以理解为类似于master数据库。
4.1.2连接数据库
本地连接到多租户数据库,sqlplus连接上后,如何确认当前连接的是cdb还是pdb,或者是连接的哪个pdb?方式如下:
sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 15 13:26:54 2015Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,OLAP, Advanced Analytics and Real Application Testing optionsSQL> select name,open_mode from v$database;NAME OPEN_MODE--------- --------------------ZHANGYU READ WRITESQL>
和传统的数据库使用sqlplus没有区别,但是我们知道这是一个容器数据库,那么如何分别登录到相应的pdb数据库呢?使用命令show con_name可以查看当前连接的是哪个数据库,以下显示当前连接为cdb。
SQL> show con_name;CON_NAME------------------------------CDB$ROOT
使用命令alter session set container=PDB名;的方式能够登录到具体的某个pdb下。
SQL> show con_name;CON_NAME------------------------------CDB$ROOT
--当前session连接为cdb
SQL> select name from v$pdbs;--查看当前数据库里面有哪些pdb
NAME------------------------------PDB$SEEDPDB_ZYPDB_DY
SQL> alter session set container=pdb_zy;--设置session连接到pdb_zy
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB_ZY
--可以看到当前session已变为pdb_zy
连接到多租户数据库,使用客户端的方式同普通数据库没有区别,先看监听状态,当前数据库环境存在两个PDB,分别为pdb_zy,pdb_dy,一个CDB叫zhangyu,可以看到监听中存在3个服务,分别为pdb_zy,pdb_dy和zhangyu。
$lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-JUL-2015 12:10:09
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - ProductionStart Date 15-JUL-2015 10:13:04Uptime 0 days 1 hr. 57 min. 5 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.oraListener Log File /u01/app/grid/diag/tnslsnr/node2/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.188.102)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.188.104)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).Instance "+ASM2", status READY, has 1 handler(s) for this service...Service "pdb_dy" has 1 instance(s).Instance "zhangyu_1", status READY, has 1 handler(s) for this service...Service "pdb_zy" has 1 instance(s).Instance "zhangyu_1", status READY, has 1 handler(s) for this service...Service "zhangyu" has 1 instance(s).Instance "zhangyu_1", status READY, has 1 handler(s) for this service...Service "zhangyuXDB" has 1 instance(s).Instance "zhangyu_1", status READY, has 1 handler(s) for this service...
下面采用简易连接的方式来连接数据库
sqlplus system/oracle@192.168.188.104:1521/zhangyu
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 15 12:59:52 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Jul 13 2015 13:05:52 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
--连接pdb_dy
sqlplus system/oracle@192.168.188.104:1521/pdb_dy
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 15 13:01:55 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Jul 15 2015 12:59:52 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
PDB_DY
以上可以看到,容器数据库的连接方式和普通数据库的连接方式没有区别,同理,配置tnsnames.ora别名的方式也同普通数据库一样。
4.1.3启停数据库
在可拔插容器数据库中,由于有PDB的存在,在启动或者停止过程中会有额外的操作,正常启动数据库如下:
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 15 13:13:41 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 801112168 bytes
Database Buffers 264241152 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
可以看到已启动数据库,但是当前连接的是CDB,可以看下CDB中的PDB状态:
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB_ZY MOUNTED
PDB_DY MOUNTED
可以看到数据库中的两个PDB状态均为mounted,此时应用端用户是无法连接到各自相应的pdb数据库的。需要使pdb可读写还需步骤如下:
SQL> alter pluggable database pdb_zy open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB_ZY READ WRITE
PDB_DY MOUNTED
现在pdb_zy已经为read write状态,如果需要关闭pdb_zy,
SQL> alter pluggable database pdb_zy close;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB_ZY MOUNTED
PDB_DY MOUNTED
上面的命令是单独打开或者关闭某个特定的pdb,还可以通过以下命令一次打开或者关闭所有的pdb
SQL>alter pluggable database all open;
SQL>alter pluggable database all close;
同时,还可以打开pdb数据库类似传统的方式,演示如下
SQL> alter session set container=pdb_zy;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB_ZY
SQL> startup
Pluggable Database opened.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB_ZY READ WRITE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27802288/viewspace-1734832/,如需转载,请注明出处,否则将追究法律责任。