问题描述：搭建dg时，连接报错ORA-12528，如下所示：

数据库：oracle 11.2.0.4

1、异常重现

[oracle@hisdbdg ~]$ sqlplus sys/oracle_4U@orcldg as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 29 14:33:30 2023

 

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

 

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

 

 

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

2、异常分析

[oracle@hisdbdg admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

   (SID_LIST =

      (SID_DESC =

         (GLOBAL_DBNAME = orcldg)

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

         (SID_NAME = orcldg)

      )

   )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

异常原因：搭建dg时，修改监听文件后没有重启监听导致.

3、解决过程

[oracle@hisdbdg ~]$ lsnrctl stop

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-MAR-2023 14:34:36

 

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

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.215)(PORT=1521)))

The command completed successfully

[oracle@hisdbdg ~]$ lsnrctl start

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-MAR-2023 14:34:43

 

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

 

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/hisdbdg/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.133.215)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.215)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                29-MAR-2023 14:34:43

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/hisdbdg/listener/alert/log.xml

Listening Endpoints Summary...

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

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "orcldg" has 1 instance(s).

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

The command completed successfully

4、结果验证

[oracle@hisdbdg ~]$ sqlplus sys/oracle_4U@orcldg as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 29 14:35:01 2023

 

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, OLAP, Data Mining and Real Application Testing options

 

说明：如上所示，成功连接.

 

参考网址：https://blog.csdn.net/weixin_34382015/article/details/116323884