Jun 25

TNS-12519 w/o Max Processes Reached

I got a call from someone that they were getting TNS-12519 errors in their application. Sure enough, those messages were in the listener.log file as well.

TNS-12519: TNS:no appropriate service handler found

For those that aren’t familiar with this error, it typically means one of two things. Either the service name is not registered with the listener or that the database has reached its maximum number of processes. For the latter, what happens is that the Listener knows that the database can’t accept any new processes so it takes the service name out-of-service so to speak. A quick “lsnrctl status” shows me that the service name is registered correctly. So it must be the latter. I then issue the following query and confirm my suspicions.

 

SQL> select resource_name,current_utilization,max_utilization
 2 from v$resource_limit where resource_name='processes';
RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION
--------------- ------------------- ---------------
processes                       299             300

Sure enough. I’ve reached max processes, which is defined in my SPFILE to be 300. I increased the parameter to 600 and bounced the instance.  We hit the error again with double the number of processes. I obviously need to dig into this further.

For some background, and for something that will be important later on, it is important to know that this database supports our automated testing efforts. We have a test harness that exercises our primary production application. This test suite launches the application, connects to the database, pushes a few buttons and selects a few menu items and then disconnects.

I examined the listener.log file to see where the connection requests were coming from. These requests were coming from a rogue application server, not our test suite. I knew something was amiss because we had not yet started the test suite and we were getting the errors. We fixed the application server and I didn’t see the errors return. We then fired up the test suite and some time later, the TNS-12519 errors returned. Hmmm…I thought I found the culprit. But let’s check our process utilization.

SQL> select resource_name,current_utilization,max_utilization
 2 from v$resource_limit where resource_name='processes';
RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION
--------------- ------------------- ---------------
processes                       89             157

 

So I’m currently seeing 89 processes with a maximum utilization of 157. I’m nowhere near my new limit of 600. So what gives? It took me awhile to figure out what the issue was. The service name is correctly registered and I’m nowhere near my limit. MOS NOTE 552765.1 talks about how the Listener arrives at the TNS-12519 error. Previously, I was seeing the most common cause. Max PROCESSES had been reached. But not this time So what gives?

After investigation, I found my answer in the listener’s log. But it wasn’t obvious like some big error message. The first occurrence of the TNS-12519 error was at 9:38 am.  I grep’ed for “service_update” in the listener log and saw these entries.

25-JUN-2015 09:17:08 * service_update * orcl * 0
25-JUN-2015 09:17:26 * service_update * orcl * 0
25-JUN-2015 09:17:29 * service_update * orcl * 0
25-JUN-2015 09:17:44 * service_update * orcl * 0
25-JUN-2015 09:17:50 * service_update * orcl * 0
25-JUN-2015 09:17:53 * service_update * orcl * 0
25-JUN-2015 09:18:56 * service_update * orcl * 0
25-JUN-2015 09:18:59 * service_update * orcl * 0
25-JUN-2015 09:19:50 * service_update * orcl * 0
25-JUN-2015 09:20:11 * service_update * orcl * 0
25-JUN-2015 09:21:27 * service_update * orcl * 0
25-JUN-2015 09:22:09 * service_update * orcl * 0
25-JUN-2015 09:24:05 * service_update * orcl * 0
25-JUN-2015 09:27:53 * service_update * orcl * 0
25-JUN-2015 09:29:32 * service_update * orcl * 0
25-JUN-2015 09:34:07 * service_update * orcl * 0
25-JUN-2015 09:41:45 * service_update * orcl * 0

Notice that this service update occurs regularly at 9:17 and 9:18, but the time between the service updates takes longer and longer. Notice that there was 8 minutes 38 seconds between service updates at the end (9:34 to 9:41). Why is this important?

This is an Oracle 11.2.0.4 database. For 11.2 and earlier, PMON is responsible for cleaning up after processes and for passing information to the Listener. On database startup, PMON tries to register the services with the Listener. One other thing PMON does is to tell the Listener how many max processes can be serviced. In this case, PMON tells the listener that it can have up to 600 processes. PMON does more, but for purposes of this discussion, that’s enough.

One important piece to know is that the Listener never knows how many processes are currently connected. It only knows how many connection requests it has helped broker. The Listener never knows if processes disconnect from the database. The service_update above is where PMON is telling the Listener how many processes are actually being used. So at 9:34:07, the PMON service update tells the Listener that there are 145 processes in use. The Listener is now up-to-date. When a new connection request comes in, the Listener increments this to 146 processes. Between the service updates, the Listener is totally unaware that 1 or more processes may have been terminated, normally or abnormally. It keeps incrementing its count of processes until the next service update when the Listener gets an accurate account of how many processes are spawned.

So we have that 8.5 minute gap between service updates. Why did it take PMON so long to get back to the Listener? Well the clue for that is in the listener.log as well. I stripped out everything from the log prior to the 9:34 service_update and after the 9:41 service update. From there, it was easy to grep for “(CONNECT_DATA=” in what remained and pipe to “wc -l” to get a count of lines.

During this 8.5 minute interval, I had well over 450 new connection requests! Yet most of those new connections terminated as evidenced by V$RESOURCE_LIMIT showing me that I had a max of 150.  PMON was so busy cleaning up for the application exiting its database connections that it had a big lag before it updated the Listener. As far as the Listener was concerned, the 150 current connections plus the 450 new connections meant that it reached its limit of 600.

PMON can take up to 10 minutes to get back to the Listener with its next service update. Cleaning up after sessions exit the instance has a higher priority than service updates to the Listener. At the 10 minute mark, PMON makes the service update the top priority if the service update had not been previously done in that time interval.

Remember that this is a database to support automated testing. We have to live with this many connect/disconnect operations because we have an automated robot testing out our application in rapid-fire fashion. We don’t want to change how the application works because it works very well when run by a single user. Our automated test suite is executing the application differently than what it was designed to do. But we want to exercise the application as its written to potentially expose bugs before the code changes hit production.

For now, I’ve increased the PROCESSES parameter to a value that we’ll never reach. All this so that the Listener can not hit the limit in its internal counter. The more PROCESSES, the more memory needed in the SGA to support that higher number. But this database can handle it.

If anyone knows of a way to get the service update to occur in a 5 minute window, please let me know. There aren’t any documented parameters to control this behavior and I’ve been unable to find an undocumented one as well.

Lastly, this issue is in one of my 11.2.0.4 database. Oracle 12c changes the architecture a bit. The new Listener Registration (LREG) background process handles the work that PMON used to do. I don’t have a system quite yet to test, but I bet that LREG won’t have the same issue in 12c that PMON is exhibiting here in 11g as LREG won’t have to handle cleanup duties that PMON does. MOS Note 1592184.1 shows that LREG will do the service updates.

Jun 18

ADDM on SearchOracle.com

Here is another article I wrote for SearchOracle.com in case you missed it. This one focuses on how to use ADDM to quickly diagnose a performance problem.

http://searchoracle.techtarget.com/tip/Oracle-ADDM-helps-resolve-SQL-performance-problems

 

Jun 18

Which Linux Partition?

I’m trying to upgrade a system’s Grid Infrastucture to the latest/greatest version. The OUI says that I need 7GB of free space and I’m a little short. This is on virtual machines so we can grow the disk easily enough. Everything is installed in the root partition (this isn’t production) and my SysAdmin is asking me if he needs to grow /dev/sda1 or /dev/sda2. Well the disk device is /dev/sda which is the first disk device on the system. This disk device has two partitions, named sda1 and sda2. Which partition is for the root file system? Today, I stumbled across the lsblk command which shows me exactly how this disk is partitioned. The lsblk command is short for List Block Devices. Here is the output on my system.

lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda    8:0    0   42G 0 disk
├─sda1 8:1    0  400M 0 part /boot
└─sda2 8:2    0 31.6G 0 part /
sr0   11:0    1 1024M 0 rom

The output above clearly shows that sda1 is for the boot partition and sda2 is for the root partition.

Jun 17

Oracle Certifications

I ran into a question on the forums today asking how to obtain certification information. Oracle includes this if you have a paid support contract. This blog post will show how to see certification information for an Oracle product on http://support.oracle.com for those that have access.

After you sign on to My Oracle Support, click on the Certifications tab.

cert1

This will take you to the Certification Search box. After the last big upgrades to My Oracle Support, this functionality has become much easier. Simply type in the product you are interested in. In my case, I typed “oracle database”. Notice the popup menu below shows me my most recent searches that match this criteria as well as a full product list. I click on one of the options to complete my entering of the product of interest.

cert2

Next, I need to enter in a product version in the Release box. Just like before, a popup menu shows me my most recent searches and well as a complete list of versions.

cert3

Once the version is entered, press the Search button. and you will have your search results. I can now see all operating systems that are certified for that specific product and version.

cert4

If this isn’t the information I am looking for, I can press the Edit Search link at the top right and change my search criteria.

cert5

May 28

SCP Client on Windows

Most of my Oracle databases run on Linux. And prior to that, one form of Unix or another. I’ve grown quite accustomed to using scp to quickly transfer a file between systems.

While my databases may live in a *nix world, my workstation is normally a Windows OS. Natively, Windows has ftp, but not scp for file transfers. Most Oracle DBA’s using Windows for their workstation rely on Putty for an ssh client. Putty is simple, lightweight, and it just does the job. Putty can be downloaded here. The download is an .exe file and there is no installation. Just double-click the executable and you’re off and running.

I don’t know why, but when I first installed Putty, I put the executable in my C:\oracle directory. I didn’t want it on my desktop because “putty.exe” looks silly to me. I’d rather see a shortcut with the name “Putty” and that’s it. So my first experience with Putty was just that. Save the file in a directory and create a shortcut.

Later on, I wanted the scp utility on Windows. Thankfully, the same wonderful people who gave us Putty also gave us PSCP, which I call the Putty SCP client. The pscp.exe utility is downloaded from the same page as Putty.

None of this is new and not blog-worthy. But here’s the little trick I do. I saved pscp.exe in C:\oracle as well (feel free to use a different directory). I then renamed the executable to be just “scp.exe”. I also put C:\oracle in my PATH environment variable. Now, whenever I am in a CMD window, it looks like I’m using my regular *nix scp utility when I’m not.

C:\Users\bpeasland\Desktop>scp sqlt.zip oracle@host01:.
oracle@host01's password:
sqlt.zip | 908 kB | 908.7 kB/s | ETA: 00:00:00 | 100%

I can easily go back and forth from my Windows environment to Linux environments and use what appears to be the same program. I don’t have to remember that one system has one utility and another has a differently named utility.

 

May 21

SQL Monitoring in SQL Developer

In my last article for SearchOracle (referenced in my last post to this blog) I showed an example of how I quickly got to the root cause of a poor performing query using SQL Monitoring in Enterprise Manager. Today, That Jeff Smith of SQL Developer fame showed me a blog entry he wrote last October on using SQL Monitoring in SQL Developer. Pretty cool stuff! I can’t explain it any better than he can so please click on the last link there to see a video of it in action.

May 19

Proper Tools Make Tuning Fast Work

I am starting to write articles for publishing on SearchOracle.com. I have been loosely affiliated with them since 2001, mostly in their Ask The Experts section. My first article is on the benefits that tools can play in making your tuning life much easier. This article is posted here: http://searchoracle.techtarget.com/tip/Proper-tools-make-fast-work-of-SQL-performance-tuning

I’m excited to publish more articles. While I get about 40,000 hits per month on this blog, SearchOracle.com gets so much more.

May 15

SEC_CASE_SENSITIVE_LOGON Deprecated in 12c

I’m working on getting my companies databases upgraded to Oracle 12.1.0.2 before our 11.2.0.4 databases lose free Extended Support in Jan 2016. One of the “gotchas” for us is that the SEC_CASE_SENSITIVE_LOGON parameter is deprecated in 12c. It’s still there, but who knows for how long.

Before I arrived at this company, they upgraded to Oracle 11.1 when this parameter was introduced. The DBA at the time used the parameter to kick the proverbial can down the road and avoid having to deal with changing the application to use case-sensitive logons. So now its time for me to make sure it gets handled before the parameter is extinct.

May 07

SQL Developer 4.1 Released

SQL Developer 4.1 was released on Monday. Here is the link to download the latest/greatest version.

 

ThatJeffSmith has a nice recap of the new features on his blog. I like the new formatting options and the Multi-Cursor Support is awesome! The Instance Viewer is cool, but I typically have a session on in EM12c which meets most of my needs.

 

 

Apr 23

Oracle RAC on my Laptop with Virtual Box

This really isn’t a new topic. I’m not first one to publish something like this. But I had a need to get these instructions posted on my blog. So here is how I created a two-node Oracle RAC testbed on my laptop. An earlier blog post shows why I think testbeds are important. When I wrote Oracle RAC Performance Tuning, I used such a testbed for much of the material I put in the book. At one point, I had a 3-node RAC cluster on my laptop by adding another VM and adding it to the cluster.

My instructions show how I got Oracle RAC 12.1.0.2 on my MacBook Pro with Virtual Box. If you use another VM hypervisor, then you can most likely get instructions with a simple Google search.

The instructions were too big for a blog entry, so I put it in a Word document (9.63MB).

http://www.peasland.net/documents/Build_Oracle_RAC_VMs.docx

Older posts «