在12c database中所有的CDB,PDB都是共用一个监听的,怎么样关闭某一个PDB的监听,而不影响别的数据库的连接。可以用DBMS_SERVICE来关闭相应的SERVICE来阻止客户端的连接。
—需要会话切到PDB
alter session set container=pdb1;
服务的关闭:
SQL> exec DBMS_SERVICE.stop_SERVICE('pdb1');
PL/SQL procedure successfully completed.
服务的开启:
SQL> exec DBMS_SERVICE.start_SERVICE('pdb1');
PL/SQL procedure successfully completed.
kill掉全部连接:
SQL> EXEC DBMS_SERVICE.DISCONNECT_SESSION('pdb1');
PL/SQL procedure successfully completed.
演示如下:
[oracle@ora12c pdb1]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 15:48:33 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
SQL>
SQL>
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name
CON_NAME
——————————
PDB1
SQL>
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-AUG-2013 15:49:11
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date 09-AUG-2013 00:31:45
Uptime 0 days 15 hr. 17 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/wh12cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "pdb1" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "pdb2" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1XDB" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
The command completed successfully
SQL> exec DBMS_SERVICE.STOP_SERVICE('pdb1');
PL/SQL procedure successfully completed.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-AUG-2013 15:50:03
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date 09-AUG-2013 00:31:45
Uptime 0 days 15 hr. 18 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/wh12cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "pdb2" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1XDB" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
The command completed successfully
SQL> exec DBMS_SERVICE.start_SERVICE('pdb1');
PL/SQL procedure successfully completed.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-AUG-2013 15:50:28
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12c)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 – Production
Start Date 09-AUG-2013 00:31:45
Uptime 0 days 15 hr. 18 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/wh12cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service "pdb1" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "pdb2" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
Service "wh12cdb1XDB" has 1 instance(s).
Instance "wh12cdb1", status READY, has 1 handler(s) for this service…
The command completed successfully
SQL>
SQL> EXEC DBMS_SERVICE.DISCONNECT_SESSION('pdb1');
PL/SQL procedure successfully completed.
SQL>
可以看win一台客户端会话被中断了
C:\Users\Administrator>sqlplus system/oracle@pdb1
SQL*Plus: Release 11.2.0.3.0 Production on 星期五 8月 9 15:47:35 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> select 'oradbca' oradbca from dual;
ORADBCA
——-
oradbca
SQL> select 'oradbca' oradbca from dual;
select 'oradbca' oradbca from dual
*
第 1 行出现错误:
ORA-00028: 您的会话己被终止
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions 断开
C:\Users\Administrator>sqlplus system/oracle@pdb1
SQL*Plus: Release 11.2.0.3.0 Production on 星期五 8月 9 15:51:18 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL>
DBMS_SERVICE Package Subprograms
创建service具体参数说明:
DBMS_SERVICE.CREATE_SERVICE(
service_name IN VARCHAR2,
network_name IN VARCHAR2,
goal IN NUMBER DEFAULT NULL,
dtp IN BOOLEAN DEFAULT NULL,
aq_ha_notifications IN BOOLEAN DEFAULT NULL,
failover_method IN VARCHAR2 DEFAULT NULL,
failover_type IN VARCHAR2 DEFAULT NULL,
failover_retries IN NUMBER DEFAULT NULL,
failover_delay IN NUMBER DEFAULT NULL,
clb_goal IN NUMBER DEFAULT NULL,
edition IN VARCHAR2 DEFAULT NULL);
Examples
DBMS_SERVICE.CREATE_SERVICE('ernie.us.oracle.com','ernie.us.oracle.com');
DECLARE
params dbms_service.svc_parameter_array;
BEGIN
params('FAILOVER_TYPE') :='TRANSACTION';
params('REPLAY_INITIATION_TIMEOUT'):=1800;
params('RETENTION_TIMEOUT') :=86400;
params('FAILOVER_DELAY') :=10;
params('FAILOVER_RETRIES') :=30;
params('commit_outcome') :='true';
params('aq_ha_notifications') :='true';
DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
END;