oracle ora-23306,Oracle 12c R2 : lsnrctl status show EM Express & PDB GUID service (PDB GUID服务)

oracle database升级到12c后(这里指12.2)，如果使用lsnrctl status查看输出结果时比之前的版本不同， 如果是多租户的环境，可能会在lsnrctl status的服务中看到一串32位的服务名如 Service “465fa0ab8243396ae0530338a8c0fc9e” has 1 instance(s).，如果仔细会发现是对应的PDB的GUID+db_domain, 该类服务是数据库访问的内部服务，用于跨CDB之间的PDB远程访问或者说是为了提供”位置透明”跨数据中心，本地PDB远程访问如Proxy PDB时使用，Proxy PDB内容下次演示。下面简单的记录lsnrctl status在12c 的一些变化。

[oracle@anbob admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 09-APR-2017 17:32:19

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anbob.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date 09-APR-2017 16:01:49

Uptime 0 days 1 hr. 30 min. 30 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u02/app/oracle/product/12.2.0/db_1/network/admin/listener.ora

Listener Log File /u02/app/oracle/diag/tnslsnr/anbob/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anbob.com)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=anbob.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/admin/anbob/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "465fa0ab8243396ae0530338a8c0fc9e.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "4a0d4eafec1b1916e0530338a8c07871.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "anbob.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "anbobXDB.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "pdbanbob.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "pdbweejar.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

The command completed successfully

1，ORACLE 12C  EM Express

第一个比12c以前版本不同的是(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=anbob.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/admin/anbob/xdb_wallet))(Presentation=HTTP)(Session=RAW))，这部分是12C的EM Express的信息，默认是HTTPS的安全访问5500端口。区别HPPT的是有没有Security的选项.

取得访问提示的方式使用sql

SQL> SELECT 'https://'||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')||':'||dbms_xdb_config.gethttpsport()||'/em/' addr from dual;

ADDR

--------------------------------------

https://anbob.com:5500/em/

禁用EM Express后lsnrctl 不在显示该内容

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(0);

PL/SQL procedure successfully completed.

[oracle@anbob ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-APR-2017 11:14:41

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anbob.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date 10-APR-2017 11:09:52

Uptime 0 days 0 hr. 4 min. 49 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u02/app/oracle/product/12.2.0/db_1/network/admin/listener.ora

Listener Log File /u02/app/oracle/diag/tnslsnr/anbob/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anbob.com)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "465fa0ab8243396ae0530338a8c0fc9e.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "4a0d4eafec1b1916e0530338a8c07871.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "anbob.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "anbobXDB.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "pdbanbob.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

Service "pdbweejar.com" has 1 instance(s).

Instance "anbob", status READY, has 1 handler(s) for this service...

The command completed successfully

EM Express是绑定在LISTENER上的, 禁用EM Express 后lsnrctl status就不再显示以上EME信息，更多EM Express的信息请点击这里

1，Service “465fa0ab8243396ae0530338a8c0fc9e.com” has 1 instance(s). GUID 服务名

在12.2的多租户环境中，每个USER pdb(排除PDB$SEED)在lsnrctl status中都对应一个GUID+domain的内部服务名， 如Service “465fa0ab8243396ae0530338a8c0fc9e.com” has 1 instance(s).通过该服务名也可以应用访问指定的PDB, 但是不建议外部使用。

[oracle@anbob admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 9 17:33:36 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR

-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------

SYS CDB$ROOT-anbob anbob 24 10339 12.2.0.1.0 20170409 23306 26 23302 000000006B27EC68 000000006CC20BD8

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDBANBOB READ WRITE NO

6 PDBWEEJAR MOUNTED

SQL> col name for a30

SQL> select con_id,guid,name from v$pdbs;

CON_ID GUID NAME

---------- -------------------------------- ------------------------------

2 465F6F2EC50932C3E0530338A8C03556 PDB$SEED

3 465FA0AB8243396AE0530338A8C0FC9E PDBANBOB

6 4A0D4EAFEC1B1916E0530338A8C07871 PDBWEEJAR

SQL> show parameter dom

PARAMETER_NAME TYPE VALUE

---------------------------- ----------- ----------------

db_domain string com

SQL> show parameter service

PARAMETER_NAME TYPE VALUE

------------------------- ----------- ----------------

service_names string anbob.com

SQL> select name from v$services;

NAME

---------------------

anbobXDB

pdbweejar.com

SYS$BACKGROUND

SYS$USERS

pdbanbob.com

anbob.com

Note:

我当前的数据库有两个USER PDB分别为pdbanbob和pdbweejar,  从v$services视图显示当前默认的四类service

1， db_unique_name +db_domain 服务

2， XDB 服务

3， 每个PDB 服务

4， 内部服务SYS$BACKGROUND 用于后台进程通信;和SYS$USERS前台不通过service访问时的默认服务,如jdbc thin 使用SID访问时对应的就是SYS$USERS

除了上面的SERVICE从lsnrctl  status上还能看到一类service, “465fa0ab8243396ae0530338a8c0fc9e.com”和 “4a0d4eafec1b1916e0530338a8c07871.com” ， 这类也是一种内部service, 如SaaS环境中如果pdb跨越了数据中心，对于隔离环境维护的当活性，12.2提供了一种位置透明的技术Proxy PDB, 我猜是因为这种GUID的服务名避免了服务名重突的风险。

SQL> conn anbob/anbob@anbob/pdbanbob.com

Connected.

SQL> select sys_context('userenv','cdb_name'), sys_context('userenv','con_name'), sys_context('userenv','service_name') from dual;

SYS_CONTEXT('USERENV','CDB_NAME') SYS_CONTEXT('USERENV','CON_NAME') SYS_CONTEXT('USERENV','SERVICE_NAME')

-------------------------------------- ------------------------------------ --------------------------------------

anbob PDBANBOB pdbanbob.com

SQL> conn anbob/anbob@anbob/465FA0AB8243396AE0530338A8C0FC9E.com

Connected.

SQL> select sys_context('userenv','cdb_name') cdb,

2 sys_context('userenv','con_name') con,

3 sys_context('userenv','service_name')s,

4 '&_connect_identifier' "connect identifier"

5* from dual

CDB CON S connect identifier

---------- ---------- ---------- ------------------------------------------

anbob PDBANBOB SYS$USERS anbob/465FA0AB8243396AE0530338A8C0FC9E.com

Note:

用PDB GUID的service也可以访问指定的PDB。所以如果使用EZCONNECT的方式，连接PDB时又增加了一种方法，如果算上Proxy PDB的方式，一共3种方式：PDB Service、Proxy PDB service、GUID Internal Service.

Summary:

在ORACLE 12.2版本中使用LSNRCTL STATUS或lsnrctl service查看监听信息时，如果启用了EM Express，从12.1版本增加了EM express的端口和访问信息;从12.2的版本起如果多租户环境，每个PDB增加了一个对应的GUID+DB_DOMAIN的服务名，无论PDB是open还是close都存在。该服务是数据库的internal service，使用该服务也可以访问PDB,但是不建议使用。

打赏

微信扫一扫，打赏作者吧～