CODEWITHSUNDEEP
oracle-databaseoracle11gwindows-serviceslistenertnsnames
IyaTaisho
IyaTaisho

Reputation: 863

Cannot log into Oracle database after restart

I'm experiencing a reoccurring issue daily with my Oracle Database. I connected it recently and now when I restart, it seems to lose it. By lose it, I mean I receive an ORA-01034 error, as well as ORA-27101 error when I try to log in as a regular user. If I try my SYS user, I get an insufficient error (ORA-01031).

I found that if I remove the service and recreate it, I can log back in. However, this is not feasible as it is my local environment and I tend to shutdown for the night typically. I could keep my box up and weekly do it, but that still doesn't seem like a feasible idea.

Below is my TNSNames and Listener files.

tnsnames.ora

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

BRACMIS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.x.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BRACMIS)
      (INSTANCE_NAME = BRACMIS)
    )

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\chad.johnson\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\chad.johnson\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (ORACLE_HOME = C:\app\chad.johnson\product\11.2.0\dbhome_1)
      (SID_NAME = BRACMIS)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.x.xx)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\app\chad.johnson\product\11.2.0\dbhome_1\log
  )

I did notice that my Oracle service for 11g and OracleInstanceBRACMIS service both were off when I started up this morning. Not sure if it is something to do with that or not either. Thanks.

EDIT: I found a way to force my DB to start up. In a command prompt, I input the command:

oradim -STARTUP -SID BRACMIS

This allowed me to start my database, which in turn allowed me to log into it at long last. This doesn't solve the overall issue of not being able to log in initially when I come on after a restart, but it does give me a way to jumpstart the DB at the very least rather than remove the Oracle Service Instance and add it back.

Upvotes: 0

Views: 4076

Answers (1)

cableload
cableload

Reputation: 4375

It appears that when you restart, oracle service is not starting. (which basically means that your Oracle Database was not started.)

Goto your windows services, and locate your Oracle Service and change the startup type of your service as Automatic . This will ensure that the Oracle Database starts after a restart.

Meanwhile open your command prompt ...do the following..

C:\> set oracle_sid=your_sid
C:\>sqlplus / as sysdba
SQL>Startup;

This will start your instance...

Upvotes: 1

Related Questions