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,但是不建议使用。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。