oracle 12c 資料庫例項監聽無法註冊問題一例
在某次重啟資料庫後,發現例項服務一直無法註冊,而僅有asm例項的服務註冊:
lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JAN-2020 19:43:44 Listening 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) = 0 poll([{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}, ...}) = 0 poll([{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) = 0 poll([{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}, ...}) = 0 poll([{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) = 0 poll([{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}, ...}) = 0 open("/proc/loadavg", O_RDONLY) = 13 fstat(13, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f1a71503000 read(13, "0.16 0.20 0.33 4/1395 210929\n", 1024) = 29 close(13) = 0 munmap(0x7f1a71503000, 4096) = 0 poll([{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) = 0 poll([{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}, ...}) = 0 poll([{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) = 0 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 11203}, ru_stime={0, 21388}, ...}) = 0 epoll_wait(9, [], 1024, 3000) = 0 getrusage(0x1 /* RUSAGE_??? */, {ru_utime={0, 11234}, ru_stime={0, 21447}, ...}) = 0 epoll_wait(9, [], 1024, 3000) = 0 getrusage(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=0 kmmlrl: update for process drop delta: 357 357 149 150 5999 kmmlrl: 149 processes kmmlrl: instance load 2 kmmgdnu: 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: update kmmlrl_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, 0x0 State: 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=0 kmmlrl: update for process drop delta: 357 357 149 150 5999 kmmlrl: 149 processes kmmlrl: instance load 2 kmmgdnu: 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: update kmmlrl_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, 0x0 State: succ=0, wait=0, fail=0
這裡發現Local listeners: 和Remote listeners:等的變數都是空的,檢視資料庫的local_listener引數發現了異常當前為oraagent-dummy:
SQL> show parameter local NAME 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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29863023/viewspace-2673860/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 19C 監聽無法動態註冊例項Oracle
- 資料庫無法註冊至監聽服務解決辦法資料庫
- HPUX 監聽無法動態註冊UX
- Oracle 動態監聽註冊Oracle
- 12CR2安裝之後例項不能註冊到監聽器的問題
- Oracle Restart啟動資料庫例項故障一例OracleREST資料庫
- 資料庫異常關閉後無法啟動問題處理一例資料庫
- Oracle監聽器的靜態註冊與動態註冊,以及DB_DOMAIN問題OracleAI
- oracle監聽靜態註冊和動態註冊Oracle
- oracle監聽動態註冊與靜態註冊Oracle
- 資料庫DML監控一例資料庫
- Oracle資料庫監聽Oracle資料庫
- 【LISTENER】實現將資料庫例項同時動態註冊到兩個非預設監聽埠資料庫
- TNS-01189: 監聽程式無法驗證使用者 問題解決一例
- 記一次Oracle無法動態註冊問題Oracle
- Oracle監聽的靜態註冊和動態註冊Oracle
- Oracle監聽的動態註冊與靜態註冊Oracle
- oracle例項和監聽器Oracle
- oracle監聽啟動時報TNS-00507問題解決一例Oracle
- Oracle 靜態監聽註冊詳解Oracle
- Oracle動態監聽註冊測試Oracle
- oracle監聽動態註冊與靜態註冊[轉帖]Oracle
- 一次Oracle監聽無法動態註冊處理過程排查分析Oracle
- Oracle10g 監聽註冊問題 - Ready及Unknown狀態Oracle
- ORACLE 動態註冊,靜態註冊,多個監聽,一個監聽多個埠配置Oracle
- oracle監聽不到例項服務Oracle
- oracle 多個例項監聽不到Oracle
- 【原創】Oracle的動態監聽註冊Oracle
- 監聽動態註冊
- 動態註冊監聽
- 動態監聽註冊
- Oracle Restart啟動資料庫例項故障一例( Oracle ASM儲存Spfile解析)OracleREST資料庫ASM
- Oracle 12C 單例項資料庫靜默安裝Oracle單例資料庫
- ORACLE 11G 無法連線到資料庫例項故障排除Oracle資料庫
- Oracle日常問題處理-資料庫無法啟動Oracle資料庫
- Oracle日常問題-資料庫無法啟動(案例二)Oracle資料庫
- oracle 資料庫例項Oracle資料庫
- 【監聽】動態註冊和靜態註冊