在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

CREATE_SERVICE Procedure

Creates service

DELETE_SERVICE Procedure

Deletes service

DISCONNECT_SESSION Procedure

Disconnects sessions running under this service

MODIFY_SERVICE Procedure

Modifies service

START_SERVICE Procedure

Activates service

STOP_SERVICE Procedure

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

service_name

Name of the service, limited to 64 characters in the Data Dictionary

network_name

Network name of the service as used in SQLNet connect descriptors for client connections. This is limited to the NET service_names character set (see Oracle Database Net Services Reference).

parameter_array

Associative array with name-value pairs of the service attributes. Supported names:

  • goal

  • dtp

  • aq_ha_notifications

  • failover_method

  • failover_type

  • failover_retries

  • failover_delay

  • clb_goal

  • edition

  • commit_outcome

  • retention_timeout

  • replay_initiation_timeout

  • session_state_consistency

  • sql_translation_profile

goal

Workload management goal directive for the service. Valid values:

  • DBMS_SERVICE.GOAL_SERVICE_TIME

  • DBMS_SERVICE.GOAL_THROUGHPUT

  • DBMS_SERVICE.GOAL_NONE

dtp

Declares the service to be for X/Open Distributed Transaction Processing (DTP) or any distributed transaction (especially XA)

aq_ha_notifications

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_method

Failover TYPE for the service for Application Continuity and TAF. If the failover_type is set toTRANSACTION on the service, this automatically sets COMMIT_OUTCOME to TRUE. JDBC Replay Driver uses theFAILOVER_TYPE service attribute setting of TRANSACTION for TRANSACTION failover. OCI uses the older settings of SELECT and SESSION. The server only accepts FAILOVER_METHOD = BASIC with the TRANSACTIONsetting.

failover_type

Failover TYPE for the service for Application Continuity and TAF.

failover_retries

Number of connection retries for Application Continuity and TAF. Using the failover_retries andfailover_delay parameters, the failover can be delayed until the service is next available. This parameter is for connecting. It does not control the number of failovers, which is 3 for each incident for Application Continuity.

failover_delay

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.

edition

If this argument has a non-NULL value, this provides the initial session edition for subsequent database connections using this service that do not specify an edition. If no value is specified, this argument has no effect.

During service creation or modification, no validation is performed on this parameter.

At connection time, if the connecting user does not have USE privilege on the edition, or the edition does not exist, this raises the error ORA-38802 (edition does not exist).

commit_outcome

Determines whether transaction COMMIT outcome is accessible after the COMMIT has executed. While the database guarantees that COMMIT is durable, this ensures that the outcome of the COMMIT is durable. Applications use the feature to probe the status of the commit last executed after an outage, and is available to applications to determine an outcome. Note:

  • Invoking the GET_LTXID_OUTCOME Procedure of the DBMS_APP_CONT package requires that thecommit_outcome attribute be set.

  • commit_outcome has no effect on active Data Guard and read-only databases.

  • commit_outcome is only allowed on the database service and on user-defined database services

retention_timeout

Used in conjunction with commit_outcome, it determines the amount of time (in seconds) that theCOMMIT_OUTCOME is retained. Default is 24 hours (86400). Maximum value is 30 days (2592000).

replay_initiation_timeout

For Application Continuity, replay_initiation_timeout is the difference between the time of original execution of first operation of a request, and the time that the replay is ready to start after a successful reconnect. Replay initiation time is measured from the time that the request was originally submitted until the time that replay has connected and is ready to replay. When replay is expected, keep this value high. Default is 900 seconds.

session_state_consistency

Describes how nontransactional is changed during a request (values are DYNAMIC or STATIC). This parameter is considered only if failover_type is set to TRANSACTION for Application Continuity. Examples of session state are NLS settings, optimizer preferences, event settings, PL/SQL global variables, temporary tables, advanced queues, LOBs, and result cache. If these values change after the request starts, set toDYNAMIC (default). Almost all applications should use DYNAMIC mode. If you are unsure, use DYNAMIC mode.

sql_translation_name

Name of SQL translation unit

clb_goal

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;