Oracle login new standby instance on Windows ( ORA-12560 ORA-12523 )

記錄操作與解決的過程

ORA-12560

1. 開啟 cmd,確認作業系統環境無預設 ORACLE_HOME & ORACLE_SID 的變數

> echo %ORACLE_HOME%
> echo %ORACLE_SID%

2. 設定環境變數 (ORACLE環境變數、非GRID環境變數)

> set ORACLE_HOME=D:\app\oracle\product\19.0.0\dbhome_1
> set ORACLE_SID=orcl

3. 嘗試登入開啟空殼 instance

> sqlplus / as sysdba
ERROR:
...
ORA-12560: TNS: 協議適配器錯誤

4. 確認一下「services 服務」是否已經有配啟動 oracle instance 的服務

5. 若新的 instance 名稱不在「services 服務」上,則建立新的 instance 服務

> oradim -new -sid orcl

6. 再次嘗試登入,並開啟 instance 至 nomount

> sqlplus / as sysdba

SQL> Connect to idle instance
SQL> startup nomount

7. 接下來就可以還原 database 建 dataguard 或是操作其他程序 … etc

 

ORA-12523

1. 用Client主機遠端連線

# on Client
> sqlplus system/password@orcl

ERROR:
...
ORA-12523: TNS: 監聽程序無法找到適用於客戶機連接的例程

2. 檢查 Oracle Server,設定 ORACLE_HOME & 檢查 listener 掛載服務的狀態

# on Oracle Server
> set ORACLE_HOME=D:\app\oracle\product\19.0.0\dbhome_1
> lsnrctl status

3. 進資料庫檢查狀態

# on Oracle Server
> sqlplus / as sysdba

SQL> select open_mode from v$database;

4.  從 Oracle Server 自我連線為正常

# on Oracle Server
> sqlplus system/password@orcl

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

5. 停掉 listener & database services.msc

# on Oracle Server
> net stop OracleOraGI19Home1TNSListener
> net stop OracleServiceORCL

6. Start listener & Oracle Database

# on Oracle Server
> net start OracleOraGI19Home1TNSListener
> net start OracleServiceORCL

> sqlplus / as sysdba
connected to: idle instance

SQL> startup mount;
SQL> alter system register;

7. 重新確認連接

# on Client
> sqlplus system/password@orcl

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

 

 

參考資料:

Windows-server-2008-R2安裝Oracle-11g-R2-dataguard

ORA-12523: TNS:监听程序无法找到适用于客户机连接的例程