搭建rac dg备库连接数据库时报错ORA-12514

2023-06-24 09:07
登录后复制

文档课题：搭建rac dg备库连接数据库时报错ORA-12514，如下所示：

[oracle@hisdb3 ~]$ sqlplus sys/oracle_4U@healdg as sysdba



SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 12:01:15 2022



Copyright (c) 1982, 2013, Oracle. All rights reserved.



ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor





Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied





Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied





SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[oracle@hisdb3 ~]$ tnsping healdg



TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-NOV-2022 12:17:13



Copyright (c) 1997, 2013, Oracle. All rights reserved.



Used parameter files:





Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.220)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.221)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = healdg)))

OK (0 msec)



[oracle@hisdb3 ~]$ lsnrctl status



LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-NOV-2022 10:44:44



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 Linux: Version 11.2.0.4.0 - Production

Start Date 27-NOV-2022 10:44:11

Uptime 0 days 0 hr. 0 min. 32 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/hisdb3/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.220)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.222)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "heal" has 1 instance(s).

 Instance "healdg1", status UNKNOWN, has 1 handler(s) for this service...

Service "healdg" has 1 instance(s).

 Instance "healdg1", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

异常原因：

监听文件中GLOBAL_DBNAME应该是healdg，被写成heal.

[grid@hisdb3 admin]$ cat listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent



LISTENER =

 (DESCRIPTION_LIST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.220)(PORT = 1521))

 )

 )



SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (SID_NAME = PLSExtProc)

 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

 (PROGRAM = extproc)

 )

 (SID_DESC =

 (GLOBAL_DBNAME = healdg)

 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

 (SID_NAME= healdg1)

 )

)

说明：按如上将global_dbname从heal修改为，此后监听状态正常.

[oracle@hisdb3 ~]$ lsnrctl status



LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-NOV-2022 12:17:25



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 Linux: Version 11.2.0.4.0 - Production

Start Date 27-NOV-2022 12:16:36

Uptime 0 days 0 hr. 0 min. 48 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/grid/diag/tnslsnr/hisdb3/listener/alert/log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.220)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.222)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

 Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "healdg" has 2 instance(s).

 Instance "healdg1", status UNKNOWN, has 1 handler(s) for this service...

 Instance "healdg1", status BLOCKED, has 1 handler(s) for this service...

The command completed successfully

--同时正常连接

[oracle@hisdb3 ~]$ sqlplus sys/oracle_4U@healdg as sysdba



SQL*Plus: Release 11.2.0.4.0 Production on Sun Nov 27 12:19:22 2022



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


 
 

  
  1.

  
  2.

  
  3.

  
  4.

  
  5.

  
  6.

  
  7.

  
  8.

  
  9.

  
  10.

  
  11.

  
  12.

  
  13.

  
  14.

  
  15.

  
  16.

  
  17.

  
  18.

  
  19.

  
  20.

  
  21.

  
  22.

  
  23.

  
  24.

  
  25.

  
  26.

  
  27.

  
  28.

  
  29.

  
  30.

  
  31.

  
  32.

  
  33.

  
  34.

  
  35.

  
  36.

  
  37.

  
  38.

  
  39.

  
  40.

  
  41.

  
  42.

  
  43.

  
  44.

  
  45.

  
  46.

  
  47.

  
  48.

  
  49.

  
  50.

  
  51.

  
  52.

  
  53.

  
  54.

  
  55.

  
  56.

  
  57.

  
  58.

  
  59.

  
  60.

  
  61.

  
  62.

  
  63.

  
  64.

  
  65.

  
  66.

  
  67.

  
  68.

  
  69.

  
  70.

  
  71.

  
  72.

  
  73.

  
  74.

  
  75.

  
  76.

  
  77.

  
  78.

  
  79.

  
  80.

  
  81.

  
  82.

  
  83.

  
  84.

  
  85.

  
  86.

  
  87.

  
  88.

  
  89.

  
  90.

  
  91.

  
  92.

  
  93.

  
  94.

  
  95.

  
  96.

  
  97.

  
  98.

  
  99.

  
  100.

  
  101.

  
  102.

  
  103.

  
  104.

  
  105.

  
  106.

  
  107.

  
  108.

  
  109.

  
  110.

  
  111.

  
  112.

  
  113.

  
  114.

  
  115.

  
  116.

  
  117.

  
  118.

  
  119.

  
  120.

  
  121.

  
  122.

  
  123.

  
  124.

  
  125.

  
  126.

  
  127.

  
  128.

  
  129.

  
  130.

  
  131.

  
  132.

  
  133.

  
  134.

  
  135.

  
  136.
上一篇：oppo手机如何快速整理桌面 oppo怎样快速整理桌面下一篇：计算机专业就业方向及前景分析
搭建rac dg备库连接数据库时报错ORA-12514

作者文章
