Connect an Idle, Nomount or Restricted Database Locally
Users who belong to OSDBA or OSOPER can access a database in any states including idle, NOMOUNT or RESTRICT on local host. It's probably not a problem on local, but remote or external access would be an issue to connect the database.
Connect an Idle, Nomount or Restricted Database Remotely
Normally, you cannot connect to an idle database from other hosts via the listener. This is because the database is idle, no instance registers its service names with the listener. The listener has no way to know which dynamic services should listen to.
As for a NOMOUNT or RESTRICT database, things get complicated a little bit. The listener will block or restrict connections from being established with the instance.
Sometimes, we have to connect to an idle, NOMOUNT or RESTRICT database from a remote client other than the local host to do some operations. For example, we need to connect to a NOMOUNT auxiliary database to duplicate a standby database by RMAN like this:
$ rman
RMAN> connect target sys@primdb1 auxiliary sys@standb1
RMAN> duplicate target database for standby ...
...
Static Service Registration
Static service registration is the cure that allows other hosts to connect to an idle, NOMOUNT or RESTRICT database. For example:
[oracle@ora11g ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ORCL)
)
)
In the above static service registration, you have told the listener at least two things:
- $ORACLE_HOME
- $ORACLE_SID
They are also required for OS authentication.
After restarting the listener service, let's see the status of the static service.
[oracle@ora11g ~]$ lsnrctl status
...
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
For more explanation about static service registration, you may read this post: How to Add Static Service Registered in Listener.
If you have some concerns about the static service which is widely opened for every client, you can keep reading the post. Maybe there's some solutions for you.
Connect a Nomount Database
NOMOUNT is a state of database instance, which notifies the listener to block all connections by default, although the listener knows what dynamic service it should listen to.
For passing through blocked mode, UR=A should be added to the connect descriptor of a connect identifier to signal the listener to ignore the blocking state. So as to establish connections.
UR=A is a sub-attribute of attribute CONNECT_DATA for a connect identifier in tnsnames.ora, it's meant for lifting blocked state of dynamic services.
Let's see how we can make it work.
1. Before Adding UR=A
First of all, restart the instance to NOMOUNT state.
[oracle@ora11g ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
...
SQL> startup nomount
...
Now check the status of listener.
[oracle@ora11g ~]$ lsnrctl status
...
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
Trying to connect the database through the connect identifier ORCL before adding UR=A.
C:\Users\ed>sqlplus sys/password@ORCL as sysdba
...
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
It did block our connection with error ORA-12528.
2. Adding UR=A
For example, we added (UR=A) for a connect identifier ORCL.
[oracle@ora11g ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)(UR=A)
)
)
3. After Adding UR=A
Let's try to connect the NOMOUNT database again.
C:\Users\ed>sqlplus sys/password@ORCL as sysdba
...
Connected.
SQL>
We are in, even though the database is at NOMOUNT.
Connect a Restricted Database
Just like we saw BLOCKED in the listener above. The restricted mode makes the listener explicitly prohibit to establish connections with the database instance. Let's see how it works.
First of all, we restart the database in restricted mode by adding an option RESTRICT when startup.
SQL> shutdown immediate;
...
SQL> startup restrict
...
Let's check the listener.
[oracle@ora11g ~]$ lsnrctl status
...
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status RESTRICTED, has 1 handler(s) for this service...
...
The command completed successfully
Trying to connect the database through the connect identifier ORCL.
C:\Users\ed>sqlplus sys/password@ORCL as sysdba
...
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
ORA-12526 was thrown. The solution is the same as we did for NOMOUNT state. You may configure a static service to listener or add UR=A in your connect identifier to lift the restriction off.
For those users who want to connect restricted database, they should be additionally granted RESTRICTED SESSION system privilege in advance.
C:\Users\ed>sqlplus hr/hr@ORCL
...
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Without RESTRICTED SESSION, we got ORA-01035 eventually. So we'd better grant the right privilege to the user to solve ORA-01035.
SQL> conn / as sysdba
Connected.
SQL> grant RESTRICTED SESSION to hr;
Grant succeeded.
Service Status in Listener
In summary, so far we have learned several service statuses including:
- UNKNOWN is for a static service
- BLOCKED is for a NOMOUNT database
- RESTRICTED is for a RESTRICT database.
What else statuses we may see in a listener? Here is a complete list of service statuses in a listener, which is quoted from Configuring and Administering Oracle Net Listener in Oracle documentation.
- READY
- UNKNOWN
- BLOCKED
- RESTRICTED
- READY/SECONDARY
It means the instance can accept connections.
It means the instance is registered statically in the listener.ora file rather than dynamically with service registration. Therefore, the status is non known.
It means the instance cannot accept connections.
It means the instance is in restricted mode. The listener blocks all connections to this instance.
It means the is a secondary instance in an Oracle Real Application Clusters primary/secondary configuration and is ready to accept connections.