Monday 25 July 2016

Authentication - Oracle Service Terminated Unexpectedly

This morning, a public facing portal database was not starting up after a restart. I could not login the database as sysdba to startup owing to

ORA-12560: TNS:protocol adapter error.

Looking at the services, the OracleService<SID> was down.
When starting the OracleService<SID>, I received an unexpected "Oracle service terminated unexpectedly" prompt.
When investigating the alert log, I see the database begins starting up but ends with

ORACLE_BASE from environment = E:\Oracle

The next step in the startup would be

alter database mount exclusive

- but it does not get this far.

I check the listener file at E:\Oracle\diag\tnslsnr\<HOST>\listener_<SID>\trace\listener_<SID>.log
and found
TNS-12518: TNS:listener could not hand off client connection
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   64-bit Windows Error: 203: Unknown error

I also checked the E:\Oracle\diag\clients\user_<USER>\host_2051522117_80\trace\sqlnet.log file for the SYSTEM user
 
***********************************************************************

Fatal NI connect error 12637, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracle<SID>)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')))(CONNECT_DATA=(SID=<SID>)(CID=(PROGRAM=E:\Oracle\product\11.2.0\dbhome_1\bin\oradim.exe)(HOST=<SERVER>)(USER=SYSTEM))))

  VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.3.0 - Production
  Time: 25-JUL-2016 09:18:42
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12637
    
TNS-12637: Packet receive failed
    ns secondary err code: 12532
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

The issue is related to connectivity or authentication.
I checked ora_dba privileges and found that the group had the correct users within.
I checked the user starting the OracleService<SID> service and it was the Local System users.

I suspected domain authentication issues and so I changed the SQLNET.ORA parameter

SQLNET.AUTHENTICATION_SERVICES = (NTS)

to

SQLNET.AUTHENTICATION_SERVICES = (NONE)

This worked and the database was able to startup using the service.
However, I could no longer execute backups or use sysdba but at least the database was up for now.

After much digging and investigating, it turned out the issue was that one service was not started upon the recent restart of the server machine. That service is the workstation service. This service allows a machine on the Microsoft domain to access files on remote machines. I guesstimate that the remote call aims to authenticate the user starting the service but fails to receive a valid response if this service is down.

Upon starting the service up, the database was taken out of NONE authentication and put back into NTS. The backups are running successfully and I am able to connect again as SYSDBA.



No comments:

Post a Comment