In summary, so far we have learned several service statuses including: So we'd better grant the right privilege to the user to solve ORA-01035. Without RESTRICTED SESSION, we got ORA-01035 eventually. C:\Users\ed>sqlplus ORACLE only available to users with RESTRICTED SESSION privilege You may configure a static service to listener or add UR=A in your connect identifier to lift the restriction off.įor those users who want to connect restricted database, they should be additionally granted RESTRICTED SESSION system privilege in advance. The solution is the same as we did for NOMOUNT state. ORA-12526: TNS:listener: all appropriate instances are in restricted mode Trying to connect the database through the connect identifier ORCL. Instance "ORCL", status RESTRICTED, has 1 handler(s) for this service. Let's see how it works.įirst of all, we restart the database in restricted mode by adding an option RESTRICT when startup. The restricted mode makes the listener explicitly prohibit to establish connections with the database instance. Just like we saw BLOCKED in the listener above. We are in, even though the database is at NOMOUNT. Let's try to connect the NOMOUNT database again. (ADDRESS = (PROTOCOL = TCP)(HOST = standby01)(PORT = 1521)) ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora Adding UR=Aįor example, we added (UR=A) for a connect identifier ORCL. It did block our connection with error ORA-12528. ORA-12528: TNS:listener: all appropriate instances are blocking new connections Trying to connect the database through the connect identifier ORCL before adding UR=A. Instance "ORCL", status BLOCKED, has 1 handler(s) for this service. Before Adding UR=Aįirst of all, restart the instance to NOMOUNT state. 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. 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.įor 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. If you have some concerns about the static service which is widely opened for every client, you can keep reading the post. Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service.įor more explanation about static service registration, you may read this post: How to Add Static Service Registered in Listener. They are also required for OS authentication.Īfter restarting the listener service, let's see the status of the static service. In the above static service registration, you have told the listener at least two things: For example: ~]$ vi $ORACLE_HOME/network/admin/listener.ora Static service registration is the cure that allows other hosts to connect to an idle, NOMOUNT or RESTRICT database. RMAN> connect target auxiliary duplicate target database for standby. For example, we need to connect to a NOMOUNT auxiliary database to duplicate a standby database by RMAN like this: $ rman 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. The listener will block or restrict connections from being established with the instance. The listener has no way to know which dynamic services should listen to.Īs for a NOMOUNT or RESTRICT database, things get complicated a little bit. This is because the database is idle, no instance registers its service names with the listener. Normally, you cannot connect to an idle database from other hosts via the listener. Connect an Idle, Nomount or Restricted Database Remotely It's probably not a problem on local, but remote or external access would be an issue to connect the database. Users who belong to OSDBA or OSOPER can access a database in any states including idle, NOMOUNT or RESTRICT on local host. Connect an Idle, Nomount or Restricted Database Locally
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |