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

文档课题：搭建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)

  )

)

说明：按如上黄颜色高亮部分修改，此后监听状态正常.

[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