12c PDB监听设置

在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

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

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

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

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

连接到:
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

连接到:
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 to`TRANSACTION` on the service, this automatically sets `COMMIT_OUTCOME` to `TRUE`. JDBC Replay Driver uses the`FAILOVER_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 `TRANSACTION`setting.
`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` and`failover_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 the`commit_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 the`COMMIT_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 to`DYNAMIC` (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;

2020年二月
一	二	三	四	五	六	日
« 九
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29