But we have a problem with this
A reader, May 05, 2006 - 10:13 am UTC
Tom,
We have a highly available environment where we have recently upgraded to 10.2.0.1 and are facing an issue with this new feature. We have a primary db and a secondary db.
The secondary is aleays in restricted mode and as soon as the secondary is taken out of restricted mode the connections immediately fail-over from primary to it. So anytime the secondary is started up it is brought up in a restricted mode.
So how do we workaround this as we need to be able to login as sys to this secondary database.
May 05, 2006 - 2:40 pm UTC
what WHAT exactly.
what is a "secondary db"
and why in the world would anyone know how to connect to it?
You set up the network connections, what did you do - and what is a "secondary db"
Not helpful at all
Jeremy Bettis, May 15, 2006 - 5:48 pm UTC
Thanks for copying the same error message that Oracle already printed out.
How do I get my database OUT of restricted mode? If even DBA users cannot connect is there any way to fix it other than restarting the service or machine?
May 15, 2006 - 6:18 pm UTC
Your dba doesn't know how to get the database out of restricted mode? You have big problems then (pretty basic)
I sort of thought the "action said it all"
The DBA should connect without using the network - just connect / as sysdba and then issue alter system disable restricted session - do not use the listener.
They (your DBA) sort of put it into restricted session, you would hope they could undo that bit.
and if you read the original question, well, the question was simply:
<quote>
Has this behaviour changed in 10g?
</quote>
I answered promptly that "yes it is, here is the error message, here is the action to take and here be the documentation on it"
If someone had asked "so, how do we get it out of restricted session", I guess I would have answered that - but they didn't, so I didn't.
They didn't ask how to backup a database, how to join two tables, how to export from windows and import to linux either, guess you could fault me for not answering those as well?
Crystal Ball
Mark A. Williams, May 15, 2006 - 6:23 pm UTC
Time to shine up the old crystal ball again, eh?
- Mark
Just in case someone stumbles across this thread like I did...
A reader, July 24, 2007 - 1:23 pm UTC
Subject: Connections Via the Listener to the Database Fail with TNS-12526, TNS-12527 or TNS-12528
Doc ID: Note:444120.1
Read this note, basically in 10g if you want your privileged users to still take advantage of their restricted session privilege across the network you need to add (UR=A) to your tnsnames entry; from the note:
Here's an example of a connect string configured with (UR=A):
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myoraclehost)(PORT = 1521)))
(CONNECT_DATA =
(UR=A)
(SERVICE_NAME = orcl.mydomain)
)
)
Please note that the (UR=A) clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. SID may connect to a statically configured handler.
difference between sys/ as sysdba and sys/password as sysdba
Yuna, May 28, 2008 - 1:28 pm UTC
Hi Tom,
In oracle 8i, the privileges are different when log in as the following format:
sys/ as sysdba
passowrd:
and
sys/ pawwords as sysdba
What is the difference?
Thank you very much!
May 28, 2008 - 3:54 pm UTC
they are the same and identical to:
connect santa/claus as sysdba
sys%ORA10GR2> connect santa/claus as sysdba
Connected.
sys%ORA10GR2> show user
USER is "SYS"
You would find you can use any username you want, any password you want - it is the same as
connect / as sysdba
we use OS authentication.
connect as sys / as sysdba
A reader, May 29, 2008 - 9:17 am UTC
Thank you Tom for the answer.
In my system, it is different.
When connected as
sys / as sysdba
passwords:
and run SQL> Archive log list
it gives insufficient privileges.
But the other one sys/password as sysdba is ok.
It seems there is difference among them
May 29, 2008 - 9:49 am UTC
no, you are doing something else there.
connect santa/claus as sysdba
same as
connect / as sysdba
ops$tkyte%ORA9IR2> connect santa/claus as sysdba
Connected.
ops$tkyte%ORA9IR2> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /tmp/
Oldest online log sequence 189
Next log sequence to archive 190
Current log sequence 190
ops$tkyte%ORA9IR2> connect sys/foobar as sysdba
Connected.
ops$tkyte%ORA9IR2> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /tmp/
Oldest online log sequence 189
Next log sequence to archive 190
Current log sequence 190
ops$tkyte%ORA9IR2>
Alexander the ok, May 29, 2008 - 12:21 pm UTC
Tom,
Can you do any damage by creating/dropping (accidently) objects in SYS?
I know if you modify SYS Oracle objects you're in serious trouble. But do you think we're ok if a user defined object accidently gets created there?
My crusade to get cooworkers to stop using SYS for everything hasn't been successful yet :(
May 29, 2008 - 1:22 pm UTC
...
Can you do any damage by creating/dropping (accidently) objects in SYS?
.......
sure - if it was something WE owned.
If it was table T that you 'accidentally' created, it would be safe to drop it. Well, maybe not in your case since your coworkers are so accident prone.
difference between the two connections
Yuna, May 29, 2008 - 1:40 pm UTC
oracle@acpic-s373# ORACLE_SID=XXXXXX
oracle@acpic-s373# sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 29 13:31:15 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: sys/ as sysdba
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL ACPIC-S373 >archive log list
ORA-01031: insufficient privileges
SQL ACPIC-S373 >connect sys/YYYYYY as sysdba
Connected.
SQL ACPIC-S373 >archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/product/8.1.7/dbs/arch
Oldest online log sequence 6727
Current log sequence 6729
SQL ACPIC-S373 >quit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
oracle@acpic-s373# sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 29 13:32:03 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: sys/YYYYYYY as sysdba
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL ACPIC-S373 >archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/product/8.1.7/dbs/arch
Oldest online log sequence 6727
Current log sequence 6729
SQL ACPIC-S373 >connect sys / as sysdba
Enter password:
Connected.
SQL ACPIC-S373 >archive log list
ORA-01031: insufficient privileges
SQL ACPIC-S373 >
May 29, 2008 - 9:27 pm UTC
ah, I think I see the problem - really old sqlplus
connect sys/ as sysdba
is the same (because of sqlplus parsing) as
connect sys/
you are not getting in as sysdba....
just connect / as sysdba, that is what you want - or really - what you want is to be on software written this century.
had to dig up a really old server to play with that.
Yuna, May 29, 2008 - 1:42 pm UTC
Hi Tom,
I run the scripts as following and get the following results. Thank you !
oracle@acpic-s373# ORACLE_SID=XXXXXX
oracle@acpic-s373# sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 29 13:31:15 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: sys/ as sysdba
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL ACPIC-S373 >archive log list
ORA-01031: insufficient privileges
SQL ACPIC-S373 >connect sys/YYYYYY as sysdba
Connected.
SQL ACPIC-S373 >archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/product/8.1.7/dbs/arch
Oldest online log sequence 6727
Current log sequence 6729
SQL ACPIC-S373 >quit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
oracle@acpic-s373# sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 29 13:32:03 2008
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter user-name: sys/YYYYYYY as sysdba
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL ACPIC-S373 >archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/product/8.1.7/dbs/arch
Oldest online log sequence 6727
Current log sequence 6729
SQL ACPIC-S373 >connect sys / as sysdba
Enter password:
Connected.
SQL ACPIC-S373 >archive log list
ORA-01031: insufficient privileges
SQL ACPIC-S373 >
Sean, June 18, 2008 - 3:49 pm UTC
RDBMS 11.1.0.6.0
SQL> conn d/df@usitprod as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn fddf/dfdfdf@usitprod as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn junk/junk@usitprod as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>
Why so?
June 18, 2008 - 4:04 pm UTC
platform is?
sqlnet authentication services are set to?
sounds like you are using OS authentication - which means that user/pass is not/needed
what happens with
connect /@usitprod as sysdba
(and when connected as sysdba, regardless of the credentials, your schema will be sys - no matter what)
Sean, June 18, 2008 - 8:17 pm UTC
platform is?
Windows.
sqlnet authentication services are set to?
NTS
what happens with
connect /@usitprod as sysdba
SQL> conn /@usitprod as sysdba
Connected.
SQL>
yes, it is the OS authentication.
interesting enough, it does ask passwd if:
SQL> conn junk@usitprod as sysdba
Enter password:
Connected.
you have to response to it with anything such the Enter, or any key(s).
June 19, 2008 - 9:54 am UTC
correct, that last bit is sqlplus which thinks "ah, if you needed a username - and you know better than I what you are doing - you need a password, so let's prompt for it"
that is the UI thinking "if username needed then password is too"
sapna, October 08, 2008 - 1:44 am UTC
Modify SQLNET Authentication to NONE. It will resolve the issue.
Now unknown connection is giving error -
SQL> conn junk/junk@usitprod as sysdba
What is the purpose of this "feature"
Russ Cannon, September 22, 2009 - 1:35 pm UTC
Tom,
I work in an environment in which there are hundreds of databases with as many as 40 on a single server. Moreover, databases are moved between servers from time to time. Up to now in our 9i environment, we have used listener autodiscovery to prevent having to maintain and modify listener configuration files on the many different servers. We are now migrating to 11g and have encountered the feature you mention that a listener doing autodiscovery will not permit connections to restricted databases but return ORA-12526 instead. This means that we now have to revert to explicit connection declarations in listener configuration files.
This feature forces us to throw out the very useful feature of autodiscovery. What purpose does this change serve, and what do we gain by it?
Kind regards,
Russ
September 28, 2009 - 1:11 pm UTC
see Note 444120.1 on metalink
Thanks.
Russ Cannon, September 30, 2009 - 3:43 pm UTC
This is exactly what I needed.
kanchan, March 03, 2011 - 6:16 am UTC
i am using windows 7.
conn sys as sysdba
enter password:
connected
but
conn /as sysdba not connecting
error:sp2-01031 insufficent privileges
oracle 11 g
fayez, October 27, 2011 - 3:55 am UTC
i have an inviroment of database 11g and i forget the password i have the administration of windows 2008 server
how could i get the password or goin to change the password
October 27, 2011 - 11:18 am UTC
forgot WHAT password.
as the oracle software owner, you don't need a password to get into the database, you just connect as sysdba.
Something for Spanish readers
Andrew, October 29, 2011 - 4:08 pm UTC
I like Tom Kyte’s examples. Some of them are a bit over my head but I understood most of this one. For the benefit of Spanish speakers, I have translated some of it and posted it at the URL below:
Me gustan los ejemplos de Tom Kyte. Algunos están un poco difíciles para mí pero comprendí la mayor parte de éste. He hecho mi propio ejemplo basado en el ejemplo del señor Kyte. Lo he traducido en español y lo he puesto aquí:
http://international-dba.blogspot.com/2011/10/ora-12526-in-spanish-en-espanol.html