Pythian Group

After an in place upgrade of SQL server 2005 to SQL server 2008 R2 , I tried to open SQL errorlog from Management> SQL server logs but I got the following error

Refreshing the node will pop the error up again.

Maybe we try T-SQL!

EXEC xp_readerrorlog


Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found


As far as I know , SSMS calls a system stored procedure xp_enumerrorlogs to return the list of error logs and associated last change date.

The location of the SQL server errorlog ,and master database files, is stored in registry under following key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.instance_name\MSSQLServer\Parameters

Instance_name is SQL server instance name currently being accessed ; it was default in my case so it was MSSQLSERVER

This is the value of SQL sever errorlog entry

-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

I then verified that the folder exists and SQL server service account has enough permissions to read/write the file.

Looked at Event viewer , Found following three events

Event Type: Failure Audit
Event Source: MSSQLSERVER
Description:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: local machine]

Event Type: Error
Event Source: MSSQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18452, OS: 18452 [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication..

Event Type: Error
Event Source: MSSQLSERVER
Description:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: local machine].

What’s causing these login failures? the connection is certainly local so “untrusted domain” error looks misleading.

Xp_readerrorlog calls a DLL ,xpstar.dll, but there’s not much info how xpstar.dll works. However,the original error carried some guidance represented in “Failed to open loopback connection” part.

To me, the word loopback used to mean local communication on same server when addressing IP address 127.0.0.1 or Localhost computer name (I see it has more uses now).

ALL I did was to open Windows® hosts file C:\WINDOWS\system32\drivers\etc\hosts

and that was it , I didn’t find a map for LOCALHOST nor servername; all I did was to add that map

127.0.0.1 localhost server_name

After that , I could open the errorlog from SSMS and Xp_readerrorlog came back with results.

Hope this helps someone , It can be really annoying.

Cheers,
M

Hello Everyone,

This is one of my fist posts under Pythian’s blog. I will try to keep those short and simple at the beginning.

Recently I was troubleshooting a new GNS (Grid Name Services) functionality.

For more information please see here: Oracle Clusterware Network Configuration Concepts.

I have noticed that there is a-trace-level parameter in the GNS process string.
# ps -ef | grep gns
root 26790 1 2 14:41 ? 00:00:00 /u01/app/11.2.0/grid/bin/gnsd.bin -trace-level 0 -ip-address 10.10.193.201 -startup-endpoint ipc://GNS_hostrac01_23867_408c49e351f1f6a8
root 26825 17210 0 14:41 pts/1 00:00:00 grep gns


Unfortunately there is no description as of now in the documentation or MOS on how to change it to generate invaluable diagnostic information.
NOTE: I am sure the documentation will be updated in Database 12c version (c for Cloud ;)

For a time being the following should work for you:

# /u01/app/11.2.0/grid/bin/crsctl modify resource ora.gns -attr "TRACE_LEVEL=6"
# /u01/app/11.2.0/grid/bin/srvctl stop gns
# /u01/app/11.2.0/grid/bin/srvctl start gns


I hope that this advice will help you to diagnose your GNS issue.

I will blog about the way I resolved future GNS-related issues later on.

It looks like I have said too much for my very first post already.

See you around,

Yury

Just another DBA from down under

More than a month has passed since Sydney Oracle Meetup #2. We shot some video, but it took me a while to process it and publish a few interesting pieces, but I finally got it all.

Ric Van Dyke‘s presentation on tuning SQL queries using 10046 trace is available on the SOM website in the Files section (you must be a member). However, there were no material from Tanel Poder‘s session — it’s title started with “Zero Slides…” and Tanel demonstrated live some of his secrets of productivity in Oracle troubleshooting. Luckily, my colleague, Andrey Goryunov, managed to shoot some of it on the video and I’m publishing here a couple fragments on the visualization of Oracle performance troubleshooting.

Make sure you are watching them in HQ on YouTube to see more details and if you like these videos, make sure you rate them.

PerfSheet is a very handy solution based on Microsoft Excel scripting and let you automate extraction and charting of any data you can extract from an Oracle database (and generally speaking, any other database). The best thing is that Tanel has put great efforts in it and made it available to everyone for free. One demo is worth many words so here we go:
Read the rest of this entry . . .

Wheel of protocol, turn turn turn.
Tell us the lesson that we should learn. (with apologies to the original source)

Writing a book comes with many challenges. For me, writing a MySQL book for MySQL beginners, who may or may not be database beginners, has fed my compulsion to research and test bizarre interactions.

Today’s lesson is on what protocol is used when connecting to a local mysqld instance on a non-Windows machine. The TCP/IP protocol is used by default when connecting on a Windows machine, and connecting from any operating system to a non-local mysqld instance. I am assuming the connections are being made by a command line client such as mysql, mysqladmin or mysqldump. Connections made via connectors such as Connector/J, an ODBC connector, DBD::mysql, etc are not covered in this post.

If you seem to be having trouble connecting, check your options against your intentions. Unexpected behavior is usually seen when your intention is to connect using TCP/IP, but the connection is using a socket file instead. Note the following behaviors:

Read the rest of this entry . . .

It is quite common for Oracle Tech support, while troubleshooting any 11i E-Business Suite Self Service Applications (SSWA) related error messages, to ask to enable debug logging in JServ configuration files.

The procedure to enable debug logging in JServ is to update following line in file $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties from log=false to log=true, and then bounce Apache using:

$ cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
$ adapcctl.sh stop
$ adapcctl.sh start

After the bounce, we start seeing debug messages in the log file $IAS_ORACLE_HOME/Apache/Jserv/logs/jserv.log.

Please note, the logfile’s and config file’s locations can be different from those mentioned above. In case you have a shared tech stack implemented, replace $IAS_ORACLE_HOME with $CONF_TOP/iAS.

The main problem with this procedure is the bounce of Apache. In cases where the client is not a heavy SSWA user and the issue isn’t affecting a significant number of users, then often we will be asked to wait until off-business hours to do the bounce of Apache. These delays can make your metalink SR hop between different Oracle support offices in different timezones, which can lead to delays in resolution of the issue.

Let’s have quick review of JServ configuration files which are of interest to us. jserv.conf — this is the configuration file of mod_jserv module in Apache. Here’s a snippet from jserv.conf: Read the rest of this entry . . .

Kenny Tilton writes a blog that is mostly about LISP programming, but today he posted about database troubleshooting, and he anecdotally illustrates and elaborates on a law of troubleshooting that I strongly agree with: Always solve the first problem. Based on the way this law is named, I suspect it is his own.

In a nutshell, any time there is a multiplicity of problems affecting an endeavour, simplify mercilessly until you have only one problem left. Then solve that one.

The corollary to his law is that “there only is the first problem.” I’m not sure I entirely agree with that one, but I will admit that that corollary is true at least 90% of the time, which is often enough to make it an incredibly useful insight.

Brilliant stuff! Read it.