新裝11GRAC遠端連線不上資料庫,報TNS-12547: TNS:lost contact故障處理
環境:HP-UX 11.31 ia64
資料庫版本:11.2.0.4
是否RAC:是
檔案系統或ASM:ASM
事件描述:
最近新裝了一套RAC,應用測試連線不上資料庫,連線直接報TNS-12547: TNS:lost contact,
但檢視監聽狀態,CRS狀態,資料庫狀態均正常。CRS日誌、CSS日誌及AGENT日誌均無報錯。
處理過程詳細:
一、連上資料庫檢視監聽狀態,顯示正常:
racdb1:/home/oracle(racdb1)$lsnrctl status
LSNRCTL for HPUX: Version 11.2.0.4.0 - Production on 30-JUL-2014 10:13:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 11.2.0.4.0 - Production
Start Date 23-JUL-2014 16:54:43
Uptime 0 days 17 hr. 18 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /oracle/app/grid/diag/tnslsnr/racdb1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.210)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.212)(PORT=1521)))
Services Summary...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
二、連入資料庫切換日誌,日誌可以切換,檢視db alert日誌無異常發現。
racdb1:/home/oracle(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 30 10:14:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL>
三、檢視監聽日誌發現一直在報連線失敗:
29-JUL-2014 16:52:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=13662)) * establish * racdb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe
29-JUL-2014 16:52:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=13663)) * establish * racdb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe
29-JUL-2014 16:52:32 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=13668)) * establish * racdb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe
四、分析問題的原因,導致的原因有在32位平臺中當listener.log超過2G會報這個錯,
ORACLE_HOME下的一些執行檔案許可權不對也會導致相同的錯誤,但我們這個是64位的,排除第一種,
所以去查詢執行檔案的許可權是否正常。
1)檢視問題節點的oracle執行檔案許可權
racdb1:/oracle/app/grid/diag/tnslsnr/racdb1/listener/trace(racdb1)$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-s--x 1 oracle asmadmin 573971520 Jun 23 15:44 /oracle/app/oracle/product/11.2.0/db/bin/oracle
2)檢視正常節點的oracle執行檔案許可權
racdb2:/home/oracle(racdb2)$ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 573971520 Jun 23 16:20 /oracle/app/oracle/product/11.2.0/db/bin/oracle
3)對比執行檔案發現檔案許可權不一致,問題節點少了S許可權。
五、為了防止其他執行檔案都有類似的許可權問題,故對其作了relink all,重新同步執行檔案。
racdb1:/oracle/app/grid/diag/tnslsnr/racdb1/listener/trace(racdb1)$cd $ORACLE_HOME/bin
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$relink all
writing relink log to: /oracle/app/oracle/product/11.2.0/db/install/relink.log
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 573971520 Jul 29 17:15 /oracle/app/oracle/product/11.2.0/db/bin/oracle
六、relink all之後許可權正常了,但sqlplus進去發現沒有連上資料庫,shutdown資料庫報錯,CRS狀態正常。
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 29 17:17:46 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> exit
Disconnected
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.gsd
OFFLINE OFFLINE racdb1
OFFLINE OFFLINE racdb2
ora.net1.network
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.ons
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racdb1
ora.cvu
1 ONLINE ONLINE racdb1
ora.oc4j
1 OFFLINE OFFLINE
ora.racdb.db
1 ONLINE ONLINE racdb1 Open
2 ONLINE ONLINE racdb2 Open
ora.racdb1.vip
1 ONLINE ONLINE racdb1
ora.racdb2.vip
1 ONLINE ONLINE racdb2
ora.scan1.vip
1 ONLINE ONLINE racdb1
racdb1:/oraclelog/diag/rdbms/racdb/racdb1/trace(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 29 17:18:49 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Not owner
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1100 (asmadmin), current egid = 1000 (oinstall)
SQL> exit
Disconnected
七、從報錯日誌我們可以清晰的看到資料庫是asmadmin屬組的執行檔案啟動的,而當前的屬組是oinstall,
故將正常節點跟問題節點屬組是asmadmin的檔案全部找出來進行比對並修改問題節點檔案的屬組。
racdb1:/oraclelog/diag/rdbms/racdb/racdb1/trace(racdb1)$ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 573971520 Jul 29 17:15 /oracle/app/oracle/product/11.2.0/db/bin/oracle
問題節點:
racdb1:/oraclelog/diag/rdbms/racdb/racdb1/trace(racdb1)$find /oracle/app/oracle/product/11.2.0/db -group asmadmin | xargs ls -dlrt
-rwxr-s--x 1 oracle asmadmin 573971520 Jun 23 15:44 /oracle/app/oracle/product/11.2.0/db/bin/oracleO
-rwxrwx--- 1 oracle asmadmin 12288 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_5_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 4096 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_4_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 4096 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_3_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 4096 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_2_racdb1_65557.so
。。。。。。
-rwxrwx--- 1 oracle asmadmin 16384 Jun 23 16:51 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_712_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 8192 Jun 23 16:51 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_716_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 16384 Jun 23 16:51 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_715_racdb1_65557.so
drwx------ 2 oracle asmadmin 96 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA1804573_0
drwx------ 2 oracle asmadmin 96 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA4427703_0
drwx------ 2 oracle asmadmin 96 Jul 22 16:32 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA3234315_0
-rw-r----- 1 oracle asmadmin 116 Jul 22 17:27 /oracle/app/oracle/product/11.2.0/db/rdbms/log/dp.log
-rw-r----- 1 oracle asmadmin 1161 Jul 22 18:10 /oracle/app/oracle/product/11.2.0/db/rdbms/admin/catbundle_PSU_racdb_APPLY.sql
-rw-r----- 1 oracle asmadmin 1210 Jul 22 18:10 /oracle/app/oracle/product/11.2.0/db/rdbms/admin/catbundle_PSU_racdb_ROLLBACK.sql
-rw-rw---- 1 oracle asmadmin 1544 Jul 23 17:57 /oracle/app/oracle/product/11.2.0/db/dbs/hc_racdb1.dat
-rw-r----- 1 oracle asmadmin 770 Jun 23 11:15 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_25869_20140623111537590613143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 12:03 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_1578_20140623120338071335143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4965_20140623141844878862143795.aud
-rw-r----- 1 oracle asmadmin 769 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4965_20140623141845143404143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4979_20140623141851427440143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 23 15:20 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_13235_20140623152042817429143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 23 16:08 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_26406_20140623160826869470143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 17:00 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_3128_20140623170026275422143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 25 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_3994_20140625171835029768143795.aud
-rw-r----- 1 oracle asmadmin 759 Jun 25 17:22 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4670_20140625172235345392143795.aud
-rw-r----- 1 oracle asmadmin 761 Jul 18 12:01 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_26942_20140718120142032682143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA1804573_ora_3770_20140721171810270743143795.aud
-rw-r----- 1 oracle asmadmin 774 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA1804573_ora_3770_20140721171837523949143795.aud
-rw-r----- 1 oracle asmadmin 967 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA1804573_ora_4455_20140721171837701239143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:33 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_15458_20140721173308739486143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:37 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_18255_20140721173722473913143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:42 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_22195_20140721174257759089143795.aud
-rw-r----- 1 oracle asmadmin 778 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4427703_ora_22297_20140722144429274228143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4427703_ora_22297_20140722144455293169143795.aud
-rw-r----- 1 oracle asmadmin 969 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4427703_ora_22412_20140722144455382457143795.aud
-rw-r----- 1 oracle asmadmin 21903 Jul 21 17:40 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb.ll
-rw-r----- 1 oracle asmadmin 278 Jul 21 17:40 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb.ll-stat
-rw-r----- 1 oracle asmadmin 770 Jul 22 14:47 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_24426_20140722144756679243143795.aud
-rw-r----- 1 oracle asmadmin 778 Jul 22 16:32 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA3234315_ora_26529_20140722163240095776143795.aud
-rw-r----- 1 oracle asmadmin 969 Jul 22 16:33 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA3234315_ora_27145_20140722163307623414143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 22 16:33 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA3234315_ora_26529_20140722163307491047143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 22 17:15 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_25586_20140722171512623374143795.aud
-rw-r----- 1 oracle asmadmin 768 Jul 22 18:11 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_3152_20140722181155093598143795.aud
-rw-r--r-- 1 oracle asmadmin 12466 Jul 22 18:13 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ll-stat
-rw-r----- 1 oracle asmadmin 759 Jul 23 09:24 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_5492_20140723092443335121143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_18896_20140723094405646296143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:52 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_24400_20140723095249279502143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 17:56 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_18965_20140723175608392118143795.aud
-rw-r--r-- 1 oracle asmadmin 891 Jul 29 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ccr
-rw-r--r-- 1 oracle asmadmin 67057 Jul 29 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ll
正常節點:
-rwsr-s--x 1 oracle asmadmin 573962928 Jun 23 10:33 /oracle/app/oracle/product/11.2.0/db/bin/oracleO
-rw-r----- 1 oracle asmadmin 768 Jun 23 11:15 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_6906_20140623111553826625143795.aud
drwx------ 2 oracle asmadmin 96 Jun 23 11:15 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_2
-rw-r----- 1 oracle asmadmin 770 Jun 23 12:05 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_12624_20140623120503589321143795.aud
-rw-r----- 1 oracle asmadmin 769 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4941_20140623141844933369143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4941_20140623141844871557143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4953_20140623141851159498143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 23 15:22 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_13867_20140623152257095498143795.aud
-rwsr-s--x 1 oracle asmadmin 573971520 Jun 23 16:20 /oracle/app/oracle/product/11.2.0/db/bin/oracle
-rw-r----- 1 oracle asmadmin 768 Jun 23 16:46 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_1343_20140623164613613196143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 17:08 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4375_20140623170816905854143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 25 17:26 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_21799_20140625172612676435143795.aud
drwx------ 2 oracle asmadmin 96 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA4321613_0
drwxr-xr-x 3 oracle asmadmin 96 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/log/diag
-rw-r----- 1 oracle asmadmin 778 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4321613_ora_26459_20140714144323250932143795.aud
-rw-r----- 1 oracle asmadmin 969 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4321613_ora_26548_20140714144347500235143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4321613_ora_26459_20140714144347408564143795.aud
drwxr-x--- 2 oracle asmadmin 96 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/log/diag/rdbms
-rw-r----- 1 oracle asmadmin 770 Jul 14 14:46 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_26855_20140714144624985968143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_27480_20140721174300653127143795.aud
-rw-r----- 1 oracle asmadmin 768 Jul 22 18:11 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_2792_20140722181158000036143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:46 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_19286_20140723094656723711143795.aud
-rw-r----- 1 oracle asmadmin 11 Jul 23 17:59 /oracle/app/oracle/product/11.2.0/db/log/diag/adrci_dir.mif
-rw-rw---- 1 oracle asmadmin 1544 Jul 23 18:00 /oracle/app/oracle/product/11.2.0/db/dbs/hc_racdb2.dat
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:55 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_25292_20140723095555923366143795.aud
-rw-r----- 1 oracle asmadmin 768 Jul 23 17:58 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_5558_20140723175850541243143795.aud
-rw-r--r-- 1 oracle asmadmin 891 Jul 27 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ccr
-rw-r--r-- 1 oracle asmadmin 67057 Jul 29 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ll
修改問題節點檔案的屬組(修改前屬組為oinstall,修改後屬組為asmadmin)
root@racdb1:/oracle/app/oracle/product/11.2.0/db/bin#chown oracle:asmadmin oracle
root@racdb1:/oracle/app/oracle/product/11.2.0/db/bin#ls -lrt oracle
-rwsr-s--x 1 oracle asmadmin 573971520 Jul 29 17:15 oracle
root@racdb1:/oracle/app/oracle/product/11.2.0/db/dbs#ls -lrtd /oracle/app/oracle/product/11.2.0/db/log/diag
drwxrwxr-x 3 oracle oinstall 96 Jun 23 10:48 /oracle/app/oracle/product/11.2.0/db/log/diag
root@racdb1:/oracle/app/oracle/product/11.2.0/db/dbs#cd /oracle/app/oracle/product/11.2.0/db/log/
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#chown oracle:asmadmin diag
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#ls -lrtd /oracle/app/oracle/product/11.2.0/db/log/diag
drwxrwxr-x 3 oracle asmadmin 96 Jun 23 10:48 /oracle/app/oracle/product/11.2.0/db/log/diag
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#ls -lrtd /oracle/app/oracle/product/11.2.0/db/log/diag/rdbms
drwxr-x--- 2 oracle oinstall 96 Jul 22 16:33 /oracle/app/oracle/product/11.2.0/db/log/diag/rdbms
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#cd /oracle/app/oracle/product/11.2.0/db/log/diag/
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#chown oracle:asmadmin rdbms
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#ls -lrt
total 2
drwxr-x--- 2 oracle asmadmin 96 Jul 22 16:33 rdbms
-rw-r----- 1 oracle oinstall 11 Jul 23 17:56 adrci_dir.mif
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#chown oracle:asmadmin adrci_dir.mif
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#cd /oracle/app/oracle/product/11.2.0/db/dbs/
八、將檔案許可權修改之後重啟例項
racdb1:/home/oracle(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 29 17:34:52 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.gsd
OFFLINE OFFLINE racdb1
OFFLINE OFFLINE racdb2
ora.net1.network
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.ons
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racdb1
ora.cvu
1 ONLINE ONLINE racdb1
ora.oc4j
1 OFFLINE OFFLINE
ora.racdb.db
1 OFFLINE OFFLINE Instance Shutdown
2 ONLINE ONLINE racdb2 Open
ora.racdb1.vip
1 ONLINE ONLINE racdb1
ora.racdb2.vip
1 ONLINE ONLINE racdb2
ora.scan1.vip
1 ONLINE ONLINE racdb1
SQL> startup
ORACLE instance started.
Total System Global Area 1.3202E+11 bytes
Fixed Size 2221816 bytes
Variable Size 4.1339E+10 bytes
Database Buffers 9.0463E+10 bytes
Redo Buffers 214671360 bytes
Database mounted.
Database opened.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
九、透過pl sql遠端連線資料庫,連線成功,檢視listener.log,顯示已連線上,無報錯。
listener.log:
29-JUL-2014 18:03:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36598)) * establish * racdb * 0
29-JUL-2014 18:03:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36602)) * establish * racdb * 0
29-JUL-2014 18:03:29 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36606)) * establish * racdb * 0
29-JUL-2014 18:03:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36610)) * establish * racdb * 0
29-JUL-2014 18:03:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36616)) * establish * racdb * 0
資料庫版本:11.2.0.4
是否RAC:是
檔案系統或ASM:ASM
事件描述:
最近新裝了一套RAC,應用測試連線不上資料庫,連線直接報TNS-12547: TNS:lost contact,
但檢視監聽狀態,CRS狀態,資料庫狀態均正常。CRS日誌、CSS日誌及AGENT日誌均無報錯。
處理過程詳細:
一、連上資料庫檢視監聽狀態,顯示正常:
racdb1:/home/oracle(racdb1)$lsnrctl status
LSNRCTL for HPUX: Version 11.2.0.4.0 - Production on 30-JUL-2014 10:13:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 11.2.0.4.0 - Production
Start Date 23-JUL-2014 16:54:43
Uptime 0 days 17 hr. 18 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /oracle/app/grid/diag/tnslsnr/racdb1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.210)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.212)(PORT=1521)))
Services Summary...
Service "racdb" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB" has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
二、連入資料庫切換日誌,日誌可以切換,檢視db alert日誌無異常發現。
racdb1:/home/oracle(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 30 10:14:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL>
三、檢視監聽日誌發現一直在報連線失敗:
29-JUL-2014 16:52:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=13662)) * establish * racdb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe
29-JUL-2014 16:52:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=13663)) * establish * racdb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe
29-JUL-2014 16:52:32 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=13668)) * establish * racdb * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
HPUX Error: 32: Broken pipe
四、分析問題的原因,導致的原因有在32位平臺中當listener.log超過2G會報這個錯,
ORACLE_HOME下的一些執行檔案許可權不對也會導致相同的錯誤,但我們這個是64位的,排除第一種,
所以去查詢執行檔案的許可權是否正常。
1)檢視問題節點的oracle執行檔案許可權
racdb1:/oracle/app/grid/diag/tnslsnr/racdb1/listener/trace(racdb1)$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-s--x 1 oracle asmadmin 573971520 Jun 23 15:44 /oracle/app/oracle/product/11.2.0/db/bin/oracle
2)檢視正常節點的oracle執行檔案許可權
racdb2:/home/oracle(racdb2)$ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 573971520 Jun 23 16:20 /oracle/app/oracle/product/11.2.0/db/bin/oracle
3)對比執行檔案發現檔案許可權不一致,問題節點少了S許可權。
五、為了防止其他執行檔案都有類似的許可權問題,故對其作了relink all,重新同步執行檔案。
racdb1:/oracle/app/grid/diag/tnslsnr/racdb1/listener/trace(racdb1)$cd $ORACLE_HOME/bin
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$relink all
writing relink log to: /oracle/app/oracle/product/11.2.0/db/install/relink.log
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 573971520 Jul 29 17:15 /oracle/app/oracle/product/11.2.0/db/bin/oracle
六、relink all之後許可權正常了,但sqlplus進去發現沒有連上資料庫,shutdown資料庫報錯,CRS狀態正常。
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 29 17:17:46 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> exit
Disconnected
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$
racdb1:/oracle/app/oracle/product/11.2.0/db/bin(racdb1)$crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.gsd
OFFLINE OFFLINE racdb1
OFFLINE OFFLINE racdb2
ora.net1.network
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.ons
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racdb1
ora.cvu
1 ONLINE ONLINE racdb1
ora.oc4j
1 OFFLINE OFFLINE
ora.racdb.db
1 ONLINE ONLINE racdb1 Open
2 ONLINE ONLINE racdb2 Open
ora.racdb1.vip
1 ONLINE ONLINE racdb1
ora.racdb2.vip
1 ONLINE ONLINE racdb2
ora.scan1.vip
1 ONLINE ONLINE racdb1
racdb1:/oraclelog/diag/rdbms/racdb/racdb1/trace(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 29 17:18:49 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Not owner
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 1100 (asmadmin), current egid = 1000 (oinstall)
SQL> exit
Disconnected
七、從報錯日誌我們可以清晰的看到資料庫是asmadmin屬組的執行檔案啟動的,而當前的屬組是oinstall,
故將正常節點跟問題節點屬組是asmadmin的檔案全部找出來進行比對並修改問題節點檔案的屬組。
racdb1:/oraclelog/diag/rdbms/racdb/racdb1/trace(racdb1)$ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 573971520 Jul 29 17:15 /oracle/app/oracle/product/11.2.0/db/bin/oracle
問題節點:
racdb1:/oraclelog/diag/rdbms/racdb/racdb1/trace(racdb1)$find /oracle/app/oracle/product/11.2.0/db -group asmadmin | xargs ls -dlrt
-rwxr-s--x 1 oracle asmadmin 573971520 Jun 23 15:44 /oracle/app/oracle/product/11.2.0/db/bin/oracleO
-rwxrwx--- 1 oracle asmadmin 12288 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_5_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 4096 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_4_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 4096 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_3_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 4096 Jun 23 16:50 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_2_racdb1_65557.so
。。。。。。
-rwxrwx--- 1 oracle asmadmin 16384 Jun 23 16:51 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_712_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 8192 Jun 23 16:51 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_716_racdb1_65557.so
-rwxrwx--- 1 oracle asmadmin 16384 Jun 23 16:51 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_1/JOXSHM_EXT_715_racdb1_65557.so
drwx------ 2 oracle asmadmin 96 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA1804573_0
drwx------ 2 oracle asmadmin 96 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA4427703_0
drwx------ 2 oracle asmadmin 96 Jul 22 16:32 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA3234315_0
-rw-r----- 1 oracle asmadmin 116 Jul 22 17:27 /oracle/app/oracle/product/11.2.0/db/rdbms/log/dp.log
-rw-r----- 1 oracle asmadmin 1161 Jul 22 18:10 /oracle/app/oracle/product/11.2.0/db/rdbms/admin/catbundle_PSU_racdb_APPLY.sql
-rw-r----- 1 oracle asmadmin 1210 Jul 22 18:10 /oracle/app/oracle/product/11.2.0/db/rdbms/admin/catbundle_PSU_racdb_ROLLBACK.sql
-rw-rw---- 1 oracle asmadmin 1544 Jul 23 17:57 /oracle/app/oracle/product/11.2.0/db/dbs/hc_racdb1.dat
-rw-r----- 1 oracle asmadmin 770 Jun 23 11:15 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_25869_20140623111537590613143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 12:03 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_1578_20140623120338071335143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4965_20140623141844878862143795.aud
-rw-r----- 1 oracle asmadmin 769 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4965_20140623141845143404143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4979_20140623141851427440143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 23 15:20 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_13235_20140623152042817429143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 23 16:08 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_26406_20140623160826869470143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 17:00 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_3128_20140623170026275422143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 25 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_3994_20140625171835029768143795.aud
-rw-r----- 1 oracle asmadmin 759 Jun 25 17:22 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_4670_20140625172235345392143795.aud
-rw-r----- 1 oracle asmadmin 761 Jul 18 12:01 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_26942_20140718120142032682143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA1804573_ora_3770_20140721171810270743143795.aud
-rw-r----- 1 oracle asmadmin 774 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA1804573_ora_3770_20140721171837523949143795.aud
-rw-r----- 1 oracle asmadmin 967 Jul 21 17:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA1804573_ora_4455_20140721171837701239143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:33 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_15458_20140721173308739486143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:37 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_18255_20140721173722473913143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:42 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_22195_20140721174257759089143795.aud
-rw-r----- 1 oracle asmadmin 778 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4427703_ora_22297_20140722144429274228143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4427703_ora_22297_20140722144455293169143795.aud
-rw-r----- 1 oracle asmadmin 969 Jul 22 14:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4427703_ora_22412_20140722144455382457143795.aud
-rw-r----- 1 oracle asmadmin 21903 Jul 21 17:40 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb.ll
-rw-r----- 1 oracle asmadmin 278 Jul 21 17:40 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb.ll-stat
-rw-r----- 1 oracle asmadmin 770 Jul 22 14:47 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_24426_20140722144756679243143795.aud
-rw-r----- 1 oracle asmadmin 778 Jul 22 16:32 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA3234315_ora_26529_20140722163240095776143795.aud
-rw-r----- 1 oracle asmadmin 969 Jul 22 16:33 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA3234315_ora_27145_20140722163307623414143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 22 16:33 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA3234315_ora_26529_20140722163307491047143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 22 17:15 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_25586_20140722171512623374143795.aud
-rw-r----- 1 oracle asmadmin 768 Jul 22 18:11 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_3152_20140722181155093598143795.aud
-rw-r--r-- 1 oracle asmadmin 12466 Jul 22 18:13 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ll-stat
-rw-r----- 1 oracle asmadmin 759 Jul 23 09:24 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_5492_20140723092443335121143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:44 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_18896_20140723094405646296143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:52 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_24400_20140723095249279502143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 17:56 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb1_ora_18965_20140723175608392118143795.aud
-rw-r--r-- 1 oracle asmadmin 891 Jul 29 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ccr
-rw-r--r-- 1 oracle asmadmin 67057 Jul 29 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ll
正常節點:
-rwsr-s--x 1 oracle asmadmin 573962928 Jun 23 10:33 /oracle/app/oracle/product/11.2.0/db/bin/oracleO
-rw-r----- 1 oracle asmadmin 768 Jun 23 11:15 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_6906_20140623111553826625143795.aud
drwx------ 2 oracle asmadmin 96 Jun 23 11:15 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_racdb_2
-rw-r----- 1 oracle asmadmin 770 Jun 23 12:05 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_12624_20140623120503589321143795.aud
-rw-r----- 1 oracle asmadmin 769 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4941_20140623141844933369143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4941_20140623141844871557143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 14:18 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4953_20140623141851159498143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 23 15:22 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_13867_20140623152257095498143795.aud
-rwsr-s--x 1 oracle asmadmin 573971520 Jun 23 16:20 /oracle/app/oracle/product/11.2.0/db/bin/oracle
-rw-r----- 1 oracle asmadmin 768 Jun 23 16:46 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_1343_20140623164613613196143795.aud
-rw-r----- 1 oracle asmadmin 768 Jun 23 17:08 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_4375_20140623170816905854143795.aud
-rw-r----- 1 oracle asmadmin 770 Jun 25 17:26 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_21799_20140625172612676435143795.aud
drwx------ 2 oracle asmadmin 96 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/dbs/peshm_DBUA4321613_0
drwxr-xr-x 3 oracle asmadmin 96 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/log/diag
-rw-r----- 1 oracle asmadmin 778 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4321613_ora_26459_20140714144323250932143795.aud
-rw-r----- 1 oracle asmadmin 969 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4321613_ora_26548_20140714144347500235143795.aud
-rw-r----- 1 oracle asmadmin 776 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/DBUA4321613_ora_26459_20140714144347408564143795.aud
drwxr-x--- 2 oracle asmadmin 96 Jul 14 14:43 /oracle/app/oracle/product/11.2.0/db/log/diag/rdbms
-rw-r----- 1 oracle asmadmin 770 Jul 14 14:46 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_26855_20140714144624985968143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 21 17:43 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_27480_20140721174300653127143795.aud
-rw-r----- 1 oracle asmadmin 768 Jul 22 18:11 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_2792_20140722181158000036143795.aud
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:46 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_19286_20140723094656723711143795.aud
-rw-r----- 1 oracle asmadmin 11 Jul 23 17:59 /oracle/app/oracle/product/11.2.0/db/log/diag/adrci_dir.mif
-rw-rw---- 1 oracle asmadmin 1544 Jul 23 18:00 /oracle/app/oracle/product/11.2.0/db/dbs/hc_racdb2.dat
-rw-r----- 1 oracle asmadmin 770 Jul 23 09:55 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_25292_20140723095555923366143795.aud
-rw-r----- 1 oracle asmadmin 768 Jul 23 17:58 /oracle/app/oracle/product/11.2.0/db/rdbms/audit/racdb2_ora_5558_20140723175850541243143795.aud
-rw-r--r-- 1 oracle asmadmin 891 Jul 27 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ccr
-rw-r--r-- 1 oracle asmadmin 67057 Jul 29 01:01 /oracle/app/oracle/product/11.2.0/db/ccr/state/racdb-RAC.ll
修改問題節點檔案的屬組(修改前屬組為oinstall,修改後屬組為asmadmin)
root@racdb1:/oracle/app/oracle/product/11.2.0/db/bin#chown oracle:asmadmin oracle
root@racdb1:/oracle/app/oracle/product/11.2.0/db/bin#ls -lrt oracle
-rwsr-s--x 1 oracle asmadmin 573971520 Jul 29 17:15 oracle
root@racdb1:/oracle/app/oracle/product/11.2.0/db/dbs#ls -lrtd /oracle/app/oracle/product/11.2.0/db/log/diag
drwxrwxr-x 3 oracle oinstall 96 Jun 23 10:48 /oracle/app/oracle/product/11.2.0/db/log/diag
root@racdb1:/oracle/app/oracle/product/11.2.0/db/dbs#cd /oracle/app/oracle/product/11.2.0/db/log/
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#chown oracle:asmadmin diag
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#ls -lrtd /oracle/app/oracle/product/11.2.0/db/log/diag
drwxrwxr-x 3 oracle asmadmin 96 Jun 23 10:48 /oracle/app/oracle/product/11.2.0/db/log/diag
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#ls -lrtd /oracle/app/oracle/product/11.2.0/db/log/diag/rdbms
drwxr-x--- 2 oracle oinstall 96 Jul 22 16:33 /oracle/app/oracle/product/11.2.0/db/log/diag/rdbms
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log#cd /oracle/app/oracle/product/11.2.0/db/log/diag/
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#chown oracle:asmadmin rdbms
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#ls -lrt
total 2
drwxr-x--- 2 oracle asmadmin 96 Jul 22 16:33 rdbms
-rw-r----- 1 oracle oinstall 11 Jul 23 17:56 adrci_dir.mif
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#chown oracle:asmadmin adrci_dir.mif
root@racdb1:/oracle/app/oracle/product/11.2.0/db/log/diag#cd /oracle/app/oracle/product/11.2.0/db/dbs/
八、將檔案許可權修改之後重啟例項
racdb1:/home/oracle(racdb1)$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 29 17:34:52 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.gsd
OFFLINE OFFLINE racdb1
OFFLINE OFFLINE racdb2
ora.net1.network
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
ora.ons
ONLINE ONLINE racdb1
ONLINE ONLINE racdb2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racdb1
ora.cvu
1 ONLINE ONLINE racdb1
ora.oc4j
1 OFFLINE OFFLINE
ora.racdb.db
1 OFFLINE OFFLINE Instance Shutdown
2 ONLINE ONLINE racdb2 Open
ora.racdb1.vip
1 ONLINE ONLINE racdb1
ora.racdb2.vip
1 ONLINE ONLINE racdb2
ora.scan1.vip
1 ONLINE ONLINE racdb1
SQL> startup
ORACLE instance started.
Total System Global Area 1.3202E+11 bytes
Fixed Size 2221816 bytes
Variable Size 4.1339E+10 bytes
Database Buffers 9.0463E+10 bytes
Redo Buffers 214671360 bytes
Database mounted.
Database opened.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
九、透過pl sql遠端連線資料庫,連線成功,檢視listener.log,顯示已連線上,無報錯。
listener.log:
29-JUL-2014 18:03:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36598)) * establish * racdb * 0
29-JUL-2014 18:03:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36602)) * establish * racdb * 0
29-JUL-2014 18:03:29 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36606)) * establish * racdb * 0
29-JUL-2014 18:03:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36610)) * establish * racdb * 0
29-JUL-2014 18:03:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=racdb)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20
)(DELAY=5))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.192.10.63)(PORT=36616)) * establish * racdb * 0
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30633755/viewspace-2127671/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 32位系統報錯TNS-12547: TNS:lost contact
- Oracle案例04——TNS-12547: TNS:lost contactOracle
- oracle 啟動監聽報錯TNS-12547: TNS:lost contactOracle
- 連Oracle資料庫報ORA-12547: TNS:lost contact問題處理一例Oracle資料庫
- sqlplus登入資料庫報錯ORA-12547: TNS:lost contactSQL資料庫
- 遠端客戶端連線資料庫時提示TNS-12541: TNS: 無監聽程式客戶端資料庫
- 遠端連線mysql資料庫MySql資料庫
- linux oracle建立資料庫的時候報錯ORA-12547:TNS:lost contactLinuxOracle資料庫
- plsql本機不安裝資料庫連線遠端資料庫SQL資料庫
- 使用DBCA建立資料庫時,報告錯誤:ORA-12547: TNS: lost contact錯誤資料庫
- ORACLE 配置連線遠端資料庫Oracle資料庫
- 遠端通過監聽連線報ORA-01034故障處理一例
- 資料庫連線異常故障報告資料庫
- 遠端連線別人電腦上的ORACLE資料庫Oracle資料庫
- 【ORACLE】ORA-12547: TNS:lost contactOracle
- [Oracle] ORA-12547: TNS:lost contactOracle
- 客戶端連不上資料庫客戶端資料庫
- Java連線遠端的Access資料庫Java資料庫
- SQL server資料庫連線不上SQLServer資料庫
- 網路裝置重啟造成rac監聽故障連線資料庫報ora-12170:TNS:連線超時的錯誤資料庫
- win10遠端桌面連線不上怎麼辦_window10遠端桌面連線不上如何解決Win10
- win10無法遠端桌面連線怎麼處理_遠端桌面win10連線不上解決方法Win10
- 連線資料之遠端呼叫Access資料庫(轉)資料庫
- 遠端桌面連線不上該怎麼辦?
- mysql遠端連線不上怎麼解決?MySql
- PHP 遠端使用 PDO 連線 access 資料庫PHP資料庫
- PL/SQL Developer連線遠端Oracle資料庫SQLDeveloperOracle資料庫
- MySQL資料庫遠端連線開啟方法MySql資料庫
- 用SQLyog或Navicat遠端連線資料庫SQL資料庫
- 設定如何遠端連線mysql資料庫MySql資料庫
- Oracle 11g連線遠端資料庫Oracle資料庫
- mysql 開放遠端連線許可權連不上MySql
- VScode遠端連線伺服器連線不上,各種神奇的報錯VSCode伺服器
- 修改windows遠端桌面連線埠-批處理Windows
- 遠端連線錯誤程式碼及處理
- Oracle 11g ASM 單例項安裝 DBCA 建庫 報錯 ORA-12547: TNS:lost contactOracleASM單例
- win10遠端桌面連線不上 伺服器批次遠端Win10伺服器
- JDBC連線批量處理資料入庫JDBC