Getting "Invalid Option for Alter Session"

Hi,

I have been trying to find the problem for 2 days and think it's time to ask someone else... My "TEST" database was set up, configured and I was able to log into the OEM. Now I can't do that... I am getting "Invalid Option for Alter Session" when I type in any login that I normally used before. I have de-installed OEM and components, and tried to reconfigure and still no luck. I have looked at the sqlnet, tnsnames, listener, and init files thinking I would find a problem... It looks ok to me. I have one instance named orcl... I have gone into the svrmgr and done the connect Internal... and granted sysdba and sysoper to system... still can't log in or start the service.

Can someone tell me what else it might be ?

Thanks so much, Cadence

What are your OS and Oracle versions?

Sam

Give us the exact startup command that you are running and the errors that it gives you.

I am using 8.1.7, on Win2K

Everything I try to log into (DBA Studio - OEM, etc) either tells me that its the wrong UserID/Password or the "Invalid Option for Alter Session".

Thanks ....

Do you have an OEM repository created? Do you have the management server up and running. Do you have the intelligent agent running? You have to have loged in as the administrator/oracle owner to alter these privileges. The option of sysdba/sysoper gets assigned automatically.

Sam

I have reloaded the OEM & Components. I deleted the orcl instance and created a new orcl instance. I went through the Net 8 Configuration processes. But When I try to create the repository, it gives me that same error :

ORA-00604 Error occured at recursive SQL Level 1
ORA-02248 Invalid Option for Alter Session

So It will not LET me create A Repository.

If I look in "Services" the OEM is "stopping" & I cannot start either the HTTP or the Management Server components... I get this message:

"Could not start the OracleOraHOme817Management Server on Local Computer. The service did not return an error. This could be an internal Windows error or an internal service error....

The Oracle Agent is started... I am logging into the machine with Administrator privileges... ???

To do the first test, were you able to connect to the oracle ORCL instance through sqlplus? If not first check to make sure that the instance is up and running.

Sam

Hi Again,

I can set instance orcl in svrmgr. I can connect to SQL through internal, but not with system... If I try to go into SQLPlus with system it gives me the same 2 error messages.

This leads me to believe somehow I have created a permissions/login problem. I have been working on this all day today. I have created a os system user that has Admin privileges and is a member of Admin as well as ORA_DBA groups.

I will continue with this tomorrow... as I have not been able to get into the OEM.

If the ServiceOrcl in "Services" says started... does that mean that orcl is started and accessible? How else can I test it?

Thanks, Cadence

If you are able to login to oracle as sys then you can be sure that the oracle had started.

To be able to login to oracle as system, if you are trying to use the oracle's system default password, then it would be "manager" and not "change_on_install". You can change the system user's password as

SVRMGR> connect internal

SVRMGR> alter user system identified by your_password;

exit. Now

sqlplus system/your_password@service_name

would connect you in.

To set up the OEM repository, you choose oracle enterprise manager configuration assistant. Before starting with that, make sure that all your tnsnames.ora files have been configured properly. Use "sys" as the username to "logon to the database as a user with DBA privileges."

Sam

Hello Again,

I have tried what you suggested. I cannot connect as sys. I have changed the system password - and gone through the sentax per your response. When I tried to connect as system, I got the SAME 2 errors pertaining to alter session.

I wish I were more knowledgeable of SQL - I use mostly the OEM because I am new and like to use the interface better than the command line.

It just keeps coming back to the "Alter Session" problem. Even if I go into DBA Studio, and click on the orcl db there - I get the same message before even getting a login window... same 2 messages.

Unless you think there is some value I can glean from this... or others in the forum.... I am ready to blow it all away and start over. I can't imagine what I have done to screw up the database like this. I have wasted a week messing with it.

What would be the SQL statements to change the "Alter Session" settings? Any suggestions?

Thanks again for your imput...

Let me ask you one question, were you able to login to your orcl instance using sys and system as the user names through sqlplus. On such case your database is in a good shape. You don't have to worry much about the database ...

What is the gurentee that blowing every thing off and starting things from the scratch would solve your problem.

When you say that you tried to connect as system, what was you using, sqlplus/OEM?

Comming back to square one, do you have the OEM repository configured on any oracle instace?

Could you be more clear where you get the failure? I also need to know your working OS(es)? i.e. what is the OS the server is on and what is the OS the OEM is on and etc ...


Looking forward to hear from you ...

Sam

Hi Sam,

Sorry to take so long... I have been working on another project and I will have to get back to this after Tuesday. Thanks for your help. right now I have blown away the DB and created a new one (It's a test machine) and haven't gotten as far as I was... so I will be back with the results on Tuesday timeframe. Thanks again, Cadence