Thursday, March 13, 2008

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

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

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

You are trying to connect to a database that has been started in restricted mode.

When a database is put in restricted mode for maintenance operations only users with the RESTRICTED SESSION privilege can connect to it (SYSDBAs and SYSOPERs) and only by using a local database connection.

In other words, any attempt to connect even as SYSDBA or SYSOPER through the listener will result in an ORA-12526 error.
Local database connections are those opened from a terminal running on the same server where the database is located and without specifying a database connection, for instance:
sqlplus "sys/pwd as sysdba"
If you specify a database connection as in:
sqlplus "sys/pwd@orcl as sysdba"
you'll get ORA-12526 in all cases.

A database in restricted mode can be put into normal (non-restricted) mode by issuing the following command:
ALTER SYSTEM DISABLE RESTRICTED SESSION;

See message translations for ORA-12526 and search additional resources

4 comments:

Younes El-karama said...

if the database is in RESTRICTED mode, remote connections can be created only if the tnsnames.ora entry uses SID= instead of SERVICE_NAME= in CONNECT_DATA section.

amatullo said...

To connect remotely (with a user having appropriate privileges) to an instance in restricted mode, you need to add the following line in the connection string being used.

(UR=A)

Place this in the "(CONNECT_DATA = " section.


For Example:

mydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.1)(PORT = 1521))
)
(CONNECT_DATA =
(UR=A)
(SERVICE_NAME = myservicename)
(SERVER = DEDICATED)
)
)

Anonymous said...

Wonderful1! This was very useful for MSAccess programmers who use linked tables to SQL Server Linked Server to Oracle.
Really appreciate the blog!

Anonymous said...

Won't work with SID=, only SERVICE_NAME=

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles