Wednesday, December 18, 2013

ORA-01035 ORACLE only available to users with RESTRICTED SESSION privilege


##########################

## Error
##########################

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


### Full Error


oralin>conn rm_usr/*****@oralin

ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

##########################

#  Error Occurred
##########################

Error occured while connecting to a database in sqlplus in 11.2.0.3.0 version


##########################

## Command Executed
##########################

In Sqlplus,


conn rm_usr/*****@oralin


**************************************************************** Step By Step Analysis ***********************************************************************


#########################################

# 1) Check DB Status
#########################################

select instance_number,instance_name,startup_time,status,logins,shutdown_pending,database_status,instance_role from v$instance;


INSTANCE_NUMBER INSTANCE_NAME             STARTUP_T STATUS               LOGINS                    SHU DATABASE_STATUS      INSTANCE_ROLE

--------------- ------------------------- --------- -------------------- ------------------------- --- -------------------- --------------------
              1 oralin                    26-NOV-13 OPEN                 RESTRICTED                NO  ACTIVE               PRIMARY_INSTANCE

=====================================================================================================================


#########################################

# 2) Reason for Login Failure
#########################################

Users are not allowed to login because the database is being placed in RESTRICTED mode. So only users with restricted session privilege can login to the database.


=====================================================================================================================

Check the reason for placing the db in RESTRICTED mode or whether we can disable it.
=====================================================================================================================

#########################################

# 3) To disable ( Solution )
#########################################

alter system disable restricted session;



select instance_number,instance_name,startup_time,status,logins,shutdown_pending,database_status,instance_role from v$instance;


INSTANCE_NUMBER INSTANCE_NAME             STARTUP_T STATUS               LOGINS                    SHU DATABASE_STATUS      INSTANCE_ROLE

--------------- ------------------------- --------- -------------------- ------------------------- --- -------------------- --------------------
              1 oralin                    26-NOV-13 OPEN                 ALLOWED    NO  ACTIVE               PRIMARY_INSTANCE


=====================================================================================================================


1 comment:

Anonymous said...

Thank you, helped me fixed the problem. Now I have to find the root cause.