Loading...

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

2 comments:

yelkarama 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)
)
)

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