Listener issue while DR setup (EBS 12.2 with 19c database)

[karthikmca.bhc]

Hi Support,

I am following Doc ID 2617787.1 for setting up DR. Getting below error while connecting standby with its service name. Please provide fix for this issue. Im not sure where im wrong. need your support.

[oracle@p2esprod dbs]$ sqlplus sys/Dubai_12345@PROD as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 18 19:06:54 2020
Version 19.3.0.0.0
Copyight (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Fri Sep 18 2020 18:43:23 +04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>exit

[oracle@p2esprod dbs]$ sqlplus sys/Dubai_12345@PRODSTBY as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 18 19:07:32 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:

I checked tnsping to primary and standby aliases which works fine as below shown:

[oracle@p2esprod dbs]$ tnsping PROD
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 18-SEP-2020 19:09:32
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/u01/oracle/db/tech_st/19.3.0.0/network/admin/EBSPDB_p2esprod/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)(HOST = p2esprod.energy-esi-dubai.com)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = PROD)))
OK (0 msec)
[oracle@p2esprod dbs]$

[oracle@p2esprod dbs]$ tnsping PRODSTBY
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 18-SEP-2020 19:10:12
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/u01/oracle/db/tech_st/19.3.0.0/network/admin/EBSPDB_p2esprod/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprodstby.energy-esi-dubai.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD)))
OK (10 msec)
[oracle@p2esprod dbs]$

Password file seems to be correct. I have created password file in source and moved it to standby. Due to this issue the rman duplication also fails:

[oracle@p2esprodstby dbs]$ rman
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 18 18:50:37 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys/Dubai_12345@PROD;
connected to target database: PROD:EBSPDB (DBID=4028559305)
RMAN> connect auxiliary sys/Dubai_12345@PRODSTBY;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

My tnsnames.ora and listener.ora details are below:

In Primary:
------------
[oracle@p2esprod admin]$ cat tnsnames.ora
EBSPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprod.energy-esi-dubai.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EBSPDB)
    )
  )

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=tcp)(HOST = p2esprod.energy-esi-dubai.com)(PORT=1521))
    (CONNECT_DATA = (SERVICE_NAME = PROD))
  )

PRODSTBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprodstby.energy-esi-dubai.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
     )
   )

p2esprod:1521 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = p2esprod.energy-esi-dubai.com)(PORT = 1521))
  )

PROD_REMOTE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = p2esprod.energy-esi-dubai.com)(PORT = 1521))
  )

##############
# Intermedia
##############

extproc_connection_data =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCprod))
    )
    (CONNECT_DATA = (SID=PLSExtProc)(PRESENTATION = RO))
  )
[oracle@p2esprod admin]$ cat listener.ora
PROD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprod.energy-esi-dubai.com)(PORT = 1521))
    )
  )

SID_LIST_PROD =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/oracle/db/tech_st/19.3.0.0)
      (SID_NAME = PROD)
    )
  )

USE_SID_AS_SERVICE_PROD            = ON
STARTUP_WAIT_TIME_PROD             = 0
CONNECT_TIMEOUT_PROD               = 10
TRACE_LEVEL_PROD                   = OFF
LOG_DIRECTORY_PROD                 = /u01/oracle/db/tech_st/19.3.0.0/network/admin
LOG_FILE_PROD                      = prod
TRACE_DIRECTORY_PROD               = /u01/oracle/db/tech_st/19.3.0.0/network/admin
TRACE_FILE_PROD                    = prod
ADMIN_RESTRICTIONS_PROD            = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_PROD = OFF
LOG_STATUS_PROD                    = ON
INBOUND_CONNECT_TIMEOUT_PROD       = 60
DIAG_ADR_ENABLED_PROD              = ON
ADR_BASE_PROD                      = /u01/oracle/db/tech_st/19.3.0.0/log

In Standby:
-------------------
[oracle@p2esprodstby admin]$ cat tnsnames.ora
EBSPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprodstby.energy-esi-dubai.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EBSPDB)
    )
  )

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=tcp)(HOST = p2esprod.energy-esi-dubai.com)(PORT=1521))
    (CONNECT_DATA = (SERVICE_NAME = PROD))
  )

PRODSTBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprodstby.energy-esi-dubai.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PRODSTBY)
     )
   )

p2esprodstby:1521 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=tcp)(HOST=p2esprodstby.energy-esi-dubai.com)(PORT=1521))
  )

prod_REMOTE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=tcp)(HOST=p2esprodstby.energy-esi-dubai.com)(PORT=1521))
  )

##############
# Intermedia
##############

extproc_connection_data =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCprod))
    )
    (CONNECT_DATA = (SID=PLSExtProc)(PRESENTATION = RO))
  )

[oracle@p2esprodstby admin]$ cat listener.ora
PRODSTBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprodstby.energy-esi-dubai.com)(PORT = 1521))
    )
  )

SID_LIST_PROD =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/oracle/db/tech_st/19.3.0.0)(GLOBAL_NAME=PRODSTBY)
      (SID_NAME = PROD)
    )
  )

USE_SID_AS_SERVICE_PROD            = ON
STARTUP_WAIT_TIME_PROD             = 0
CONNECT_TIMEOUT_PROD               = 10
TRACE_LEVEL_PROD                   = OFF
LOG_DIRECTORY_PROD                 = /u01/oracle/db/tech_st/19.3.0.0/network/admin
LOG_FILE_PROD                      = prod
TRACE_DIRECTORY_PROD               = /u01/oracle/db/tech_st/19.3.0.0/network/admin
TRACE_FILE_PROD                    = prod
ADMIN_RESTRICTIONS_PROD            = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_PROD = OFF
LOG_STATUS_PROD                    = ON
INBOUND_CONNECT_TIMEOUT_PROD       = 60
DIAG_ADR_ENABLED_PROD              = ON
ADR_BASE_PROD                      = /u01/oracle/db/tech_st/19.3.0.0/log

IFILE=/u01/oracle/db/tech_st/19.3.0.0/network/admin/listener_ifile.ora

[oracle@p2esprodstby admin]$ cat /u01/oracle/db/tech_st/19.3.0.0/network/admin/listener_ifile.ora
PROD =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p2esprodstby.energy-esi-dubai.com)(PORT = 1521))
    )
   ))
SID_LIST_PROD =
(SID_LIST =
(SID_DESC = (ORACLE_HOME = /u01/oracle/db/tech_st/19.3.0.0)(GLOBAL_NAME=PRODSTBY)(SID_NAME = PROD))
)
USE_SID_AS_SERVICE_PROD = ON
STARTUP_WAIT_TIME_PROD = 0
CONNECT_TIMEOUT_PROD = 10
TRACE_LEVEL_PROD = OFF
LOG_DIRECTORY_PROD = /u01/oracle/db/tech_st/19.3.0.0/network/admin
LOG_FILE_PROD = STDBY
TRACE_DIRECTORY_PROD = /u01/oracle/db/tech_st/19.3.0.0/network/admin
TRACE_FILE_PROD = STDBY
ADMIN_RESTRICTIONS_PROD = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_PROD = OFF
[oracle@p2esprodstby admin]$

[ErmanArslansOracleBlog]

Hi,

I have a limited connection to my lab right now.
But, I think, you are missing some conf between the following section of DOC : 2617787.1

Between "3.2 Configure Oracle Net Communication To and From the Standby System" and  "3.3 Configure Secure Connections" ..

Please review those sections. It is probably related with NET configuration.

Having a static entry in the listener.ora of the standby may be your cure..
Check - > 4.5 Configure Oracle Net for Redo Transmission and Start the Listener, as well.

Ex:

Listener.ora
-------------

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = <db_name>)
      (ORACLE_HOME = <oracle_home>)
      (SID_NAME = <oracle_sid>)
    )
  )

An example listaner.ora file of the standby ->

CDBPROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <standby hostname>)(PORT = <PORT>)(IP = FIRST)))
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <standby Physical-VIP hostname>)(PORT = <PORT>)(IP = FIRST)))
)
)
SID_LIST_CDBPROD =
(SID_LIST =
(SID_DESC = (ORACLE_HOME = [ORACLE_HOME])(GLOBAL_NAME=<standby db_unique_name>)(SID_NAME = <instance name>))
)
USE_SID_AS_SERVICE_CDBPROD = ON
STARTUP_WAIT_TIME_CDBPROD = 0
CONNECT_TIMEOUT_CDBPROD = 10
TRACE_LEVEL_CDBPROD = OFF
LOG_DIRECTORY_CDBPROD = [ORACLE_HOME]/network/admin
LOG_FILE_CDBPROD = STDBY
TRACE_DIRECTORY_CDBPROD = [ORACLE_HOME]/network/admin
TRACE_FILE_CDBPROD = STDBY
ADMIN_RESTRICTIONS_CDBPROD = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_CDBPROD = OFF

[karthikmca.bhc]

Thanks for the update. I will have a check on your said points and see that resolves the issue.

Thanks once again for your updates which are very much straight to the point and much useful in resolving the issues.

Regards,

Karthik

On Sun, 20 Sep 2020 at 11:09 PM ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum] <[hidden email]> wrote:

Hi,



I have a limited connection to my lab right now.


But, I think, you are missing some conf between the following section of DOC : 2617787.1



Between "3.2 Configure Oracle Net Communication To and From the Standby System" and  "3.3 Configure Secure Connections" ..



Please review those sections. It is probably related with NET configuration.



Having a static entry in the listener.ora of the standby may be your cure..


Check - > 4.5 Configure Oracle Net for Redo Transmission and Start the Listener, as well.



Ex:



Listener.ora


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



SID_LIST_LISTENER =


  (SID_LIST =


    (SID_DESC =


      (GLOBAL_DBNAME = <db_name>)


      (ORACLE_HOME = <oracle_home>)


      (SID_NAME = <oracle_sid>)


    )


  )



An example listaner.ora file of the standby ->



CDBPROD =


(DESCRIPTION_LIST =


(DESCRIPTION =


(ADDRESS_LIST =


(ADDRESS = (PROTOCOL = TCP)(HOST = <standby hostname>)(PORT = <PORT>)(IP = FIRST)))


(ADDRESS_LIST =


(ADDRESS = (PROTOCOL = TCP)(HOST = <standby Physical-VIP hostname>)(PORT = <PORT>)(IP = FIRST)))


)


)


SID_LIST_CDBPROD =


(SID_LIST =


(SID_DESC = (ORACLE_HOME = [ORACLE_HOME])(GLOBAL_NAME=<standby db_unique_name>)(SID_NAME = <instance name>))


)


USE_SID_AS_SERVICE_CDBPROD = ON


STARTUP_WAIT_TIME_CDBPROD = 0


CONNECT_TIMEOUT_CDBPROD = 10


TRACE_LEVEL_CDBPROD = OFF


LOG_DIRECTORY_CDBPROD = [ORACLE_HOME]/network/admin


LOG_FILE_CDBPROD = STDBY


TRACE_DIRECTORY_CDBPROD = [ORACLE_HOME]/network/admin


TRACE_FILE_CDBPROD = STDBY


ADMIN_RESTRICTIONS_CDBPROD = ON


SUBSCRIBE_FOR_NODE_DOWN_EVENT_CDBPROD = OFF

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Listener-issue-while-DR-setup-EBS-12-2-with-19c-database-tp8786p8792.html

To unsubscribe from Listener issue while DR setup (EBS 12.2 with 19c database), click here.


NAML

--