[Oracle] ORA-12541: TNS:no listener
Why ?
¿©·¯°¡Áö ¹®Á¦Á¡À» °®À» ¼ö ÀÖ½À´Ï´Ù. ÇØ´ç Alert Log¿Í Trace FileÀ» ºÐ¼®ÇÏ¿©¼ °¢ »óȲ¿¡ ¸Â°Ô ´ëóÇÏ½Ã¸é µÉ °Í °°½À´Ï´Ù.
1. Listener Log FileÀÌ 4G ÀÌ»óÀÏ °æ¿ì ¹ß»ý ÇÒ ¼ö ÀÖ½À´Ï´Ù.
2. Ŭ¶óÀ̾ðÆ®ÀÇ µ¥ÀÌÅͺ£À̽º Á¢±Ù¿¡ ´ëÇÑ ¿äûÀ» ¹Þ¾ÆµéÀÏ ¼ö ÀÖ±âÀ§Çؼ °¡µ¿µÇ¾î¾ß ÇÒ ¸®½º³Ê°¡ Á¦´ë·Î °¡µ¿µÇÁö ¾Ê°í ÀÖÀ» °æ¿ì ¹ß»ý ÇÒ ¼ö ÀÖ½À´Ï´Ù.
3. Listener ÆÄÀÏ ¼³Á¤ÀÌ ¿Ã¹Ù¸£°Ô µÇÁö ¾Ê°í ÀÖÀ» °æ¿ì ¹ß»ý ÇÒ ¼ö ÀÖ½À´Ï´Ù.
How ?
Checked The Database Alert Log File & Listener Log File
1. ¸®½º³Ê ¼ºñ½º¸¦ Áß´ÜÇÏ°í ÇØ´ç ¸®½º³Ê·Î±× ÆÄÀÏ ¸íÀ» º¯°æÇϰųª »èÁ¦(¹é¾÷ ¿©ºÎ´Â º»ÀÎÀÇ ÆÇ´Ü)ÇÏ°í ¸®½º³Ê¸¦ Àç½ÃÀÛÇÕ´Ï´Ù.
SQL *Net V2, Net8 ¿¡¼´Â listener.log´Â ¹Ýµå½Ã Ãâ·ÂµË´Ï´Ù. BackupÀÇ Å¸Àֵ̹îÀ¸·Î »èÁ¦ÇÏ´Â ¹æ¹ýÀ» °ËÅäÇÏ´Â °ÍÀÌ ÁÁÁö¸¸, Net8 ¿¡¼´Â ¾Æ·¡ ¹æ¹ýÀ¸·Î ¸®½º³Ê·Î±×¸¦ »çÀü¿¡ ½×Áö ¾Ê°Ô ÇÒ ¼ö ÀÖ½À´Ï´Ù.
Windows C:\> cd \oracle\ora92\network\log C:\oracle\ora92\network\log> lsnrctl set log_status off C:\oracle\ora92\network\log> rename listener.log listener.old C:\oracle\ora92\network\log> lsnrctl set log_status on UNIX % cd /u01/app/oracle/product/9.2.0/network/log % lsnrctl set log_status off % mv listener.log listener.old % lsnrctl set log_status on -- 11g ÀÌ»óÀº ¾Æ·¡¿¡ Ç¥½ÃµÈ °æ·Î·Î À̵¿ÇÏ¿© ¼öÇàÇÕ´Ï´Ù. |
2. ¸®½º³Ê ·Î±× ÆÄÀÏ »çÀÌÁ º¯°æÇÕ´Ï´Ù.
Windows C:\> cd \oracle\ora92\network\log C:\oracle\ora92\network\log> lsnrctl set log_status off C:\oracle\ora92\network\log> rename listener.log listener.old C:\oracle\ora92\network\log> lsnrctl set log_status on UNIX % cd /u01/app/oracle/product/9.2.0/network/log % lsnrctl set log_status off % mv listener.log listener.old % lsnrctl set log_status on -- 11g ÀÌ»óÀº ¾Æ·¡¿¡ Ç¥½ÃµÈ °æ·Î·Î À̵¿ÇÏ¿© ¼öÇàÇÕ´Ï´Ù. |
3. Trace FileÀ» È®ÀÎÇÏ¿© ORA-02062 ¶Ç´Â Trace FileÀÇ À̸§ÀÌ reco·Î »ý¼ºµÇ¸é ¾Æ·¡¿Í °°ÀÌ ÇØ°áÇÕ´Ï´Ù. (Reproduce Âü°í)
cf - ¸®½º³Ê·Î±× ÆÄÀÏÀº ¾Æ·¡ÀÇ À§Ä¡¿¡¼ ãÀ» ¼ö ÀÖ½À´Ï´Ù.
Ãâó: http://ora-sysdba.tistory.com/28 [Welcome To Ora-SYSDBA] |
Reproduce
Trace File ¿¡ ¾Æ·¡¿Í °°Àº ¸Þ½ÃÁö°¡ Ç¥½ÃµÇ¾úÀ» °æ¿ì¸¦ °¡Á¤ÇÕ´Ï´Ù.
|
·ÎÄà DB¿¡¼ µðºñ¸µÅ©¸¦ ÅëÇؼ ¸®¸ðÆ® DB(¿ø·¡ÀÇ ID = dcb2d01b)¿¡ ´ëÇØ ¿¬°áÀ» ½ÃµµÇßÁö¸¸ ¸®¸ðÆ® µðºñ°¡ ´Ù¸¥ ID(6827025b)¸¦ °®°í ÀÖ¾î ¿¡·¯°¡ ¹ß»ýÇÑ µí º¸ÀÔ´Ï´Ù. µû¶ó¼, ¸®¸ðÆ® DB »óŸ¦ È®ÀÎÇÏ°í DB LINK¸¦ ´Ù½Ã »ý¼ºÇØ¾ß ÇÕ´Ï´Ù. Âü°í·Î DB¸¦ º¹±¸ÇÒ ¼ö ¾ø¾î¼, DB¸¦ °°Àº À̸§À¸·Î ´Ù½Ã »ý¼ºÇصµ »õ·Î¿î DBID¸¦ °®°Ô µË´Ï´Ù.
ÇØ´ç DBID´Â ¾Æ·¡ Äõ¸®·Î ±¸º° °¡´ÉÇÕ´Ï´Ù.
SELECT
DBID
FROM
V$
DATABASE
;
¶Ç ´Ù¸¥ °æ¿ì·Î ÇöÀç Trace File ¸íÀ» º¸°Ô µÇ¸é reco°¡ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù. ÀÌ °æ¿ì´Â DB LINK¸¦ ÅëÇØ TransactionµµÁß ¿¬°áÀÌ ²÷¾îÁö°í °è¼Ó RollbackÀ» ½ÃµµÁßÀÏ ¼ö µµ ÀÖ½À´Ï´Ù. ÀÌ °æ¿ì´Â dba_2pc_pendigÀ» Á¶È¸ÇÏ¿© ÇØ´ç Á¤º¸°¡ ³²¾Æ ÀÖÀ¸¸é purge ÇÏ¿©¾ß ÇÕ´Ï´Ù.
SYS> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME, 'dd-mon-yyyy HH24:MI:SS' ),STATE, MIXED FROM DBA_2PC_PENDING; LOCAL_TRAN_ID GLOBAL_TRAN_ID TO_CHAR(FAIL_TIME, 'DD-M STATE MIX ---------------------------------------- ---------------------------------------- ----------------------- ---------------- --- 68.26.9771 DYAMS11P.7e048ab7.68.26.9771 01-jul-2011 21:45:18 collecting no Elapsed: 00:00:00.00 SYS> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (' 68.26.9771'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.06 SYS> commit ; Commit complete. Ãâó: http://ora-sysdba.tistory.com/28 [Welcome To Ora-SYSDBA] |