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

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.