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
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.
ReplyDeleteTo 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.
ReplyDelete(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)
)
)
Wonderful1! This was very useful for MSAccess programmers who use linked tables to SQL Server Linked Server to Oracle.
ReplyDeleteReally appreciate the blog!
Won't work with SID=, only SERVICE_NAME=
ReplyDelete