Listener

How to Remotely Connect a NOMOUNT Database

Users who belong to OSDBA or OSOPER can access a database in any states on local host if OS authentication is ON in the listener. It’s probably not a problem on local, but remote or external access would be an issue to connect to the database.

Normally, you cannot connect to a nomount state database from other hosts, because the database is not mounted, the listener is no way to know which dynamic services to listen to.

Sometimes, you will need to connect to a nomount database from a remote client other than the local to do some operations, for example, you need to connect to an auxiliary database within RMAN to duplicate a database like this:

$ rman
RMAN> connect target sys@primdb1 auxiliary sys@standb1
RMAN> duplicate target database ...
...

Static service registration is the cure that allows other hosts to connect to a nomount database. First, you need to configure and insert a entry called SID_LIST_LISTENER into listener.ora on the database to enable the function, for example:

SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (GLOBAL_DBNAME = standb.example.com)
            (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
            (SID_NAME = standb1)
        )
    )

In the above static configuration, you tell the listener at leat two thing:

  1. $ORACLE_HOME
  2. $ORACLE_SID

They are also required for OS authentication.

Secondly, add an attribute (UR=A) within the target entry in tnsnames.ora on any server that you want to connect from. For example, insert the attribute (UR=A) into CONNECT_DATA of standb1 entry in tnsnames.ora on node 1 of primary RAC:

standb1 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = standby01)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = standb.example.com)(UR=A)
        )
    )

Please note that, only users having SYSDBA privilege can access a nomount database via static service and be authenticated by password file.

UR=A is able to remove all connection restriction, but you may not need it in normal situations.

For more explanation, you may read this post: How to Add Static Service Registered in Listener

Leave a Reply

Your email address will not be published. Required fields are marked *