Thursday, April 07, 2011

ORA-12526: TNS:listener: all appropriate instances are in restricted mode

Nothing special... just tested some about Database in Restricted Mode.
Opening the Database in Restricted Mode,that will only allow users with special privileges (RESTRICTED SESSION)
SQL> select * from dba_sys_privs where privilege='RESTRICTED SESSION';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA RESTRICTED SESSION YES
SYS RESTRICTED SESSION NO
On Server: just opened instance in restricted mode.
SQL> startup restrict
On client: TNSNAMES.ORA file
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

DBA role: connected by using user who has DBA role.
$ sqlplus system@orcl

Enter password:
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
Found!!! ORA-12526: TNS:listener: all appropriate instances are in restricted mode
Then solved it by changed TNSNAMES.ORA: using "(UR=A)"

orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Tested for connection again.
$ sqlplus system@orcl

Enter password:

SQL>
OK... It's work. In case with NONE-DBA role:
$ sqlplus nondba@orcl

Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
What did I learn? When instance is restricted mode.
$ lsnrctl status
.
.
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status RESTRICTED, has 1 handler(s) for this service...
PMON updates the listener with that information and blocks new connection(establish).

The (UR=A) clause for TNS connect strings in TNSNAMES.ORA file. This clause insert into:
(CONNECT_DATA =
(UR=A)
It allows a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users.

However, On database LISTENER.ORA file, what can we do? Static service!!! by using SID_DISC=
Example:
$ lsnrctl status
.
.
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status RESTRICTED, has 1 handler(s) for this service...

$ sqlplus system@orcl

Enter password:
ERROR:
ORA-12526: TNS:listener: all appropriate instances are in restricted mode
Then change listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (ORACLE_HOME = /oracle/11gR2) (SID_NAME = orcl ))
)
$ lsnrctl reload

$ lsnrctl status
Services Summary...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status RESTRICTED, has 1 handler(s) for this service...
What did we see? "status UNKNOWN" and "status RESTRICTED" ... then tested...
$ sqlplus system@orcl

Enter password:

SQL>
It's work... however, test with some user (NON-DBA role)
$ sqlplus nondba@orcl

Enter password:
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
^ ^ Now!!! We can use "DBA" role to do something with database by using LISTENER, when the Database open in Restricted Mode.

2 comments:

Anonymous said...

Good info. Thank you!

Anonymous said...

Very good info, we just found that our plsql from weblogic servers can't run while in restricted mode. We had to add the SID_LIST to the listener.ora along with the (UR=A) in the tnsnames.ora to get everything to work correctly while in restriced mode. I'm glad I found your page, it saved me alot of time. Thank you.