在solaris中運行了lsnrctl stop將listener停止，然後運行lsnrctl start將listener重新開機，出現問題及解決辦法如下：
lsnrctl status
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lypch)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary...
Service "LSExtProc" has 1 instance(s).
Instance "LSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ammicly" has 1 instance(s).
Instance "ammicly", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
這個問題說明資料庫沒有Mount
連接資料庫,發現:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
用sqlplus /nolog
sql>connect [email=sys/ammic@amicly]sys/ammic@amicly[/email] as sysdba
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
連接的問題可以通過修改listener.ora的參數,把動態的參數設置為靜態的參數,紅色標注部分,然後從新啟動監聽,就OK
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.1.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ammicly)
(ORACLE_HOME = c:\oracle\product\10.1.0\db_1)
(SID_NAME = ammicly)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
通過修改tnsnames.ora的參數,紅色表注部分
# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\db_1
\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
AMMICLY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ammicly)
(UR=A)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
然後 sqlplus /nolog
SQL>alter database mount;
SQL>alter database open;
這樣就可以啟動資料庫了;
或者用Oradim -shutdown -sid ammicly -shuttype srvc,inst -shutmode immediate -syspwd ammic;關閉資料庫
在用oradim -startup -sid ammicly -starttype srvc,inst -syspwd ammic;啟動資料庫解決;也可以在截面上從啟動資料庫服務來解決;
總結ORA-12528問題是因為監聽中的服務使用了動態服務,系統啟動後,資料庫沒有正常的MOUNT,因此在動態模式下,就會出現這個問題，用靜態的就不會有這個問題,因此上面的方法就是把監聽設置為靜態,或者在tnsnames.ora中追加(UR=A)或者從啟動服務,三種方法解決問題;
-------------------------
用靜態註冊的風險是，如果在instance運行中，lisener重新開機，就找不到instance了。換言之，靜態註冊需要先啟動lisener，再啟動instance。而且靜態模式下，lisener status顯示的是unknown
而且，用動態的話，instance nomount狀態下，一般是dba需要進行操作（比如恢復），這個時候一般直接登陸到host進行，不使用tns遠端連線。
全站熱搜
留言列表
