Can't log in to Oracle

[amberlynn]

Help!

My database is locked up.
When I try to log in through EM as sys, I get the error ORA-00018 (max # of sessions).
If I try to log in through SQLPlus as sys, it says 'connected', but if I try to run any view, it says 'Not logged on'.

Any suggestions how I can get in??

 

[SantaMufasa]

Amberlynn,

If I were in your situation, I would connect, via SQL*Plus, using the following command(s), then issue these instructions:

sqlplus /nolog
SQL> connect / as sysdba
SQL> show parameter sessions
(This shows how many sessions your instance allows.)
SQL> select count(*) from v$session;
(This confirms how many sessions currently are connected.)
SQL> shutdown immediate
(At this point, you can increase your "PROCESSES=<new, higher number>" parameter in your init<SID>.ora file (if you are not using an spfile) to boost the number of sessions your instance can handle.)
SQL> startup

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amberlynn]

When I type connect / as sysdba
I get ORA-01031 (insufficient privileges).

 

[SantaMufasa]

Typically, that means that you are not connected to the operating system as a member of the DBA group for your operating system.

To help isolate this issue, we must first know which operating system you are using for your Oracle server. (Also, Amberlynn, are you an authorised DBA for your IT organisation?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amberlynn]

My server is running Windows Server 2003.
I should have full DBA rights.
I am the DBA for a small system that sits outside of our IT group. They control my permissions, but they should have given me full DBA control...

Is there a way I can test this??

Thanks,
Amber

 

[SantaMufasa]

Yes:[ul][li]Start -> Control Panel -> User Accounts[/li][li]Look for your User name on the list.[/li][li]Confirm that one of the entries for you shows ORA_DBA as a Group to which you are a member.[/li][/ul]If you do not have such a membership, then have your either try adding yourself to the ORA_DBA Group or have an Administrator add you to the group.


If you are a member of the ORA_DBA Group, then ensure that this line appears in your sqlnet.ora file (that resides in your ORACLE_HOME\network\admin path):

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Then update us on your progress.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[amberlynn]

I don' have 'User Accounts' in the Control Panel - the closest to this is 'Stored User Names & Passwords'.

 

[SantaMufasa]

Sorry, Amberlynn, I'm not running Windows 2003, so, if the command set that I posted doesn't match the behaviour of Win2003, then I must defer to information available via Google. One such referend is Checking a Windows NT/2000/XP/2003 user account, which should list the correct command sequence to verify user settings and group memberships.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

amber,

I do run windows 2003 at work, and when I recently installed oracle, I had to get the system administrators to add me to the ORA_DBA group. You aren't added automatically, so get this done as a first step.

Being a member of this group allows one to log in as sys without a valid password, which will then give you enough privileges to do what you need.

Regards

T

Grinding away at things Oracular