oracle 12c 数据库实例监听无法注册问题一例

在某次重启数据库后，发现实例服务一直无法注册，而仅有asm实例的服务注册：

lsnrctl statusLSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JAN-2020 19:43:44Listening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "+ASM_DATA" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "+ASM_MGMT" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "+ASM_OCR" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...The command completed successfully

在ORACLE 12C中注册监听服务是有lreg进程来决定的，此时我通过strace来追踪lreg进程是否存在异常，发现在POLL是持续发生timeout:

epoll_wait(9, [], 1024, 3000)           = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 66310}, ru_stime={0, 31995}, ...}) = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)epoll_wait(9, [], 1024, 3000)           = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 66310}, ru_stime={0, 32157}, ...}) = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)epoll_wait(9, [], 1024, 3000)           = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 66310}, ru_stime={0, 32271}, ...}) = 0open("/proc/loadavg", O_RDONLY)         = 13fstat(13, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f1a71503000read(13, "0.16 0.20 0.33 4/1395 210929\n", 1024) = 29close(13)                               = 0munmap(0x7f1a71503000, 4096)            = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)epoll_wait(9, [], 1024, 3000)           = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 66310}, ru_stime={0, 32503}, ...}) = 0poll([{fd=4, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=6, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=12, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}, {fd=7, events=POLLIN|POLLPRI|POLLRDNORM|POLLRDBAND}], 4, 0) = 0 (Timeout)

关于poll的描述如下：

poll的是一种查询的方式函数原型：int poll(struct pollfd    *fds ，nfds_t    nfds ，int    timeout);fds为指向待查询的设备文件数组；nfds描述第一个参数fds中有多少个设备；timeout为查询不到我们期望的结果进程睡眠的时间；返回值：查询到期望状态的设备文件个数功能过程描述：应用程序中调用poll查询文件的状态，首先将fds里面的每个设备文件fd取出，调用它们驱动程序的poll函数，查询是否出现我们期望状态，查询完fds里面所有的设备文件得到满足期望状态的设备文件的数量，如果这个数为0，则poll调用将导致进程就进入睡眠状态，睡眠时间由poll函数设定，如果程序在睡眠状态中fds的某个文件出现我们期望状态，那么poll立即返回，否则一直睡眠到睡眠时间结束为止，返回值为0；如果这个数大于0 ，poll返回满足条件的设备数量。poll相当于open("/dev/xxx",O_RDWR)阻塞打开文件，区别在于当设备文件无数据可读时poll只导致程序休眠固定时间，而open将导致程序一直休眠到有数据为止。

此时我想难道是进程存在异常，于是通过sqlplus 来重启数据，之后重新追踪lreg进程，发现不再出现poll函数 timeout：

epoll_wait(9, [], 1024, 3000)           = 0getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 11203}, ru_stime={0, 21388}, ...}) = 0epoll_wait(9, [], 1024, 3000)           = 0getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 11234}, ru_stime={0, 21447}, ...}) = 0epoll_wait(9, [], 1024, 3000)           = 0getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 11264}, ru_stime={0, 21505}, ...}) = 0

但是数据库实例监听还是无法注册到监听.

此时开始懵圈了，监听能够注册asm实例的服务，说明监听应该没有问题，数据库的lreg进程能够持续进行注册，说明注册没有问题，那应该是这之间存在什么异常。

于是，我用 oradebug Event 10257 进行追踪lreg进程：

*** 2020-01-17T20:17:21.365862+08:00 (CDB$ROOT(1))kmlwait: status: succ=0, wait=0, fail=0kmmlrl: update for process drop delta: 357 357 149 150 5999kmmlrl: 149 processeskmmlrl: instance load 2kmmgdnu: O12DB         goodness=0, delta=1, pdb=1,         flags=0x104:unblocked/not overloaded, update=0x2:G/-/-kmmgdnu: O12DBXDB         goodness=0, delta=1, pdb=1,         flags=0x105:unblocked/not overloaded, update=0x2:G/-/-kmmlrl_network_hdlr_state: updatekmmlrl_network_hdlr_state: update for network '-oracledefault-'kmmlrl_network_hdlr_state: beq handler: load=149, max=5999, flag=0x2002, upd=0x2------------------------------Start Registration Information------------------------------ Last update: 53704792 (3 seconds ago)Flag: 0x4, 0x0State: succ=0, wait=0, fail=0 CDB: root pdb 1 last pdb 4098 open max pdb 2 Dispatcher configuration index: cur 1 max 1 Network '-oracledefault-' pdb 1 :   Local listeners:   Remote listeners:   Handlers:    Dedicated       flg=0x2002, upd=0x2, srvl=1       services=O12DB       hdlr load=149, max=5999       nam=DEDICATED       adr=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/app/oracle/product/12.2.0/dbhome_1/bin/oracle)(ARGV0='oracle./O12DB1')(ARGS='(LOCAL=NO)'))       inf=LOCAL SERVER       pri=0x7fea7aa8a208 *** 2020-01-17T20:17:21.365862+08:00 (CDB$ROOT(1))kmlwait: status: succ=0, wait=0, fail=0kmmlrl: update for process drop delta: 357 357 149 150 5999kmmlrl: 149 processeskmmlrl: instance load 2kmmgdnu: O12DB         goodness=0, delta=1, pdb=1,         flags=0x104:unblocked/not overloaded, update=0x2:G/-/-kmmgdnu: O12DBXDB         goodness=0, delta=1, pdb=1,         flags=0x105:unblocked/not overloaded, update=0x2:G/-/-kmmlrl_network_hdlr_state: updatekmmlrl_network_hdlr_state: update for network '-oracledefault-'kmmlrl_network_hdlr_state: beq handler: load=149, max=5999, flag=0x2002, upd=0x2------------------------------Start Registration Information------------------------------ Last update: 53704792 (3 seconds ago)Flag: 0x4, 0x0State: succ=0, wait=0, fail=0

这里发现Local listeners: 和Remote listeners:等的变量都是空的，查看数据库的local_listener参数发现了异常当前为oraagent-dummy：

SQL> show parameter localNAME                                 TYPE       VALUE------------------------------------ ----------- ------------------------------local_listener                       string     -oraagent-dummy-

此时再次查看crs资源状态发现实例1的状态是offline的，那是因为我是从sqlplus 直接启动数据库，并没有从集群资源来启动

ora.o12db.db

1 ONLINE OFFLINE STABLE

于是，用srvctl启动后，集群资源变为正常，数据库实例监听也正确注册到了监听：

Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "+ASM_DATA" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "+ASM_MGMT" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "+ASM_OCR" has 1 instance(s).  Instance "+ASM1", status READY, has 1 handler(s) for this service...Service "O12DB" has 1 instance(s).  Instance "O12DB1", status READY, has 1 handler(s) for this service...Service "O12DBXDB" has 1 instance(s).  Instance "O12DB1", status READY, has 1 handler(s) for this service...The command completed successfully