0

I'm trying to duplicate a database from active database, but it seems like my connection is blocked on my auxiliary. I'm using ASM just4info.

Target DB:

listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MELISA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (SID_NAME=MELISA)
          (ORACLE_HOME=/u01/app/oracle/product/19/dbhome_1)
        )
    )


ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON            # line added by Agent

tnsnames.ora:

    MELISA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MELISA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MELISA)
    )
  )

LISTENER_MELISA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = MELISA)(PORT = 1521))

PELISA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PELISA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PELISA)
    )
  )

LISTENER_PELISA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PELISA)(PORT = 1521))

Auxiliary:

listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PELISA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (SID_NAME=PELISA)
          (ORACLE_HOME=/u01/app/oracle/product/19/dbhome_1)
        )
    )

tnsnames.ora

LISTENER_PELISA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PELISA)(PORT = 1521))


PELISA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PELISA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PELISA)
    )
  )

MELISA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MELISA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MELISA)
    )
  )

LISTENER_MELISA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = MELISA)(PORT = 1521))

lsnrctl status:

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                05-JAN-2024 09:45:43
Uptime                    0 days 0 hr. 5 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/PELISA/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PELISA)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_DATA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_FRA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "PELISA" has 1 instance(s).
  Instance "PELISA", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

The auxiliary db is in nomount using spfile, and when trying to connect to rman I get following error:

connected to target database: MELISA (DBID=3534003804)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

I'm not quite sure, what could be wrong here, I stopped the firewall, since it is test machine.

Thanks in advance.

1 Answer 1

1

You only have this entry registered dynamically:

Service "PELISA" has 1 instance(s).
  Instance "PELISA", status BLOCKED, has 1 handler(s) for this service...

A dynamically registered NOMOUNT instance is in BLOCKED state and by default blocks remote connection attempts.

Static registration of PELISA is missing. You should have an entry in the listener like this (even when PELISA instance is not started):

Service "PELISA" has 1 instance(s).
  Instance "PELISA", status UNKNOWN, has 1 handler(s) for this service...

The SID_LIST_LISTENER entry in your listener.ora looks correct, but the listener needs to be restarted after adding that in listener.ora.

This not only allows remote logins to a NOMOUNT instance, but also allows starting an instance remotely through the listener, which is part of the active duplication steps.

The output of lsnrctl status also shows your listener does not use listener.ora, maybe you have it at an incorrect location.

5
  • Hi, I'm using the one in grid. [oracle@PELISA ~]$ locate listener.ora /u01/app/19/grid/network/admin/listener.ora /u01/app/19/grid/network/admin/samples/listener.ora /u01/app/oracle/crsdata/pelisa/output/listener.ora.bak.pelisa.oracle /u01/app/oracle/product/19/dbhome_1/hs/admin/listener.ora.sample /u01/app/oracle/product/19/dbhome_1/inventory/Templates/hs/admin/listener.ora.sample /u01/app/oracle/product/19/dbhome_1/network/admin/samples/listener.ora
    – paganini
    Jan 5 at 9:36
  • Maybe If I move it to the $ORACLE_HOME/network/admin it will be using the listener.ora file? I did restart the listener, when I added the entry, but same issue.
    – paganini
    Jan 5 at 9:40
  • @paganini Sounds like you manage the listener by running lsnrctl from the database $ORACLE_HOME. With Grid Infrastructure, the listener should be managed by srvctl. If you started the listener with lsnrctl, stop it, then start it with srvctl start listener -listener listener. Jan 5 at 9:45
  • thank you, that did the trick, I would have 1 more questions that would be, if using grid infra should you use the grid listener or you could in theory use the one in db home?
    – paganini
    Jan 5 at 9:51
  • @paganini With GI, the listener should run from GI, unless you have some very good reason for starting the listener from the database home. Jan 5 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.