在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
Subprogram | Description |
---|---|
Creates service |
|
Deletes service |
|
Disconnects sessions running under this service |
|
Modifies service |
|
Activates service |
|
Stops service |
创建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);
Parameters
Table 140-6 CREATE_SERVICE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the service, limited to 64 characters in the Data Dictionary |
|
Network name of the service as used in SQLNet connect descriptors for client connections. This is limited to the NET |
|
Associative array with name-value pairs of the service attributes. Supported names:
|
|
Workload management goal directive for the service. Valid values:
|
|
Declares the service to be for X/Open Distributed Transaction Processing (DTP) or any distributed transaction (especially XA) |
|
Determines whether Fast Application Notification (FAN) is enabled for OCI/OCCI/ODP. In Oracle Database12c, FAN uses Oracle Notification Services (ONS). This parameter is still used to enable FAN. FAN is recommended for all High Availability systems, and is on by default for Application Continuity |
|
Failover |
|
Failover |
|
Number of connection retries for Application Continuity and TAF. Using the |
|
Delay in seconds between connection retries for Application Continuity and TAF. The default is 10 seconds for Application Continuity. Do not use a 0-second delay if the service needs time to failover and register. Long delays are good for planned outages and to failover to Data Guard. Short delays work well with RAC when the service is already available. |
|
If this argument has a non- During service creation or modification, no validation is performed on this parameter.
At connection time, if the connecting user does not have |
|
Determines whether transaction
|
|
Used in conjunction with |
|
For Application Continuity, |
|
Describes how nontransactional is changed during a request (values are |
|
Name of SQL translation unit |
|
Method used for Connection Load Balancing (see Table 140-2, "Constants Used in Connection Balancing Goal Arguments") |
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;
Pingback: Charlie