Thank
raju, December 24, 2002 - 1:08 pm UTC
Tom,
You are a star....Thanks ...I got things working by following your comments.
Something more on Startup..
raju, December 24, 2002 - 1:19 pm UTC
Hi Tom,
Why starting the WindowsNT service always starts the database ? The reason I ask is ... I tried shutting down the database by connecting as SYS, but the service was still running. So I stopped the service and then wanted to startup the database by connecting as SYS. This is what happened when I did that ...
D:\>sqlplus "sys/sys@ora92 as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Sun Sep 9 18:15:21 2001
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process
So I had to start the service which also started the database. Is it OK to start the database via services ?
Thanks
December 24, 2002 - 1:40 pm UTC
You need to have the service running -- the database may or may not be started -- but the SERVICE must be running.
In order to set the autostart status of the database -- see the documentation in the window nt admin guide for oradim. this tool will set that.
Yes, it is OK to start/stop the database via the services. back when I used windows -- it is what I did on my testing machines.
12526 Error
Sanjeev Vibuthi, February 14, 2006 - 2:31 am UTC
Hi Tom,
We have 10g Db Re2 Running on Windwos 2000 Adv. Server (hp proliant dl580) (for development purpose) ....
It is configured as Dedicated Server...
Fixed Size 1248600
Variable Size 150995624
Database Buffers 134217728
Redo Buffers 7139328
Since last 3 days, it is giving the following error when the users try to connect to Database from their system through Java or Oracle Client:
ERROR:
ORA-12516: TNS:listener could not find available handler with matching protocol
stack
After some time, without doing anything, if we try again it is connecting.....
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.128.0.71)(PORT = 1521))
)
TRACE_LEVEL_LISTENER = USER
Trace File Contents
-----------------------------
[14-FEB-2006 11:03:55:109] snlinGetNameInfo: Using numeric form of host's address 192.128.0.36
[14-FEB-2006 11:03:55:109] nsglbgetRSPidx: returning ecode=0
[14-FEB-2006 11:03:55:109] nscon: sending NSPTRF packet
[14-FEB-2006 11:03:55:109] ntt2err: soc 268 error - operation=5, ntresnt[0]=524, ntresnt[1]=997, ntresnt[2]=0
[14-FEB-2006 11:03:55:109] nstimarmed: no timer allocated
[14-FEB-2006 11:03:55:125] snttcallback: op = 5, bytes = 0, err = 995
[14-FEB-2006 11:03:55:125] ntt2err: soc 268 error - operation=5, ntresnt[0]=530, ntresnt[1]=995, ntresnt[2]=0
[14-FEB-2006 11:03:55:125] nsclose: closing transport
[14-FEB-2006 11:03:55:125] nsclose: global context check-out (from slot 5) complete
[14-FEB-2006 11:03:55:125] nsgldissolve: Deallocating cxd 0xee9f08.
[14-FEB-2006 11:03:58:890] snttcallback: op = 5, bytes = 278, err = 0
[14-FEB-2006 11:03:58:890] ntt2err: soc 284 error - operation=5, ntresnt[0]=524, ntresnt[1]=997, ntresnt[2]=0
[14-FEB-2006 11:03:58:890] nsdo: 268 bytes from NS buffer
[14-FEB-2006 11:03:58:906] nsglgrDoRegister: inst loads: ld1:18 mld1:200 ld2:129 mld2:170
[14-FEB-2006 11:03:58:906] nsdo: 116 bytes to NS buffer
TNSLSNR.LOG
===============
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-JAN-2006 20:47:56
Copyright (c) 1991, 2005, Oracle. All rights reserved.
System parameter file is c:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Log messages written to c:\oracle\product\10.2.0\db_1\network\log\listener.log
Trace information written to c:\oracle\product\10.2.0\db_1\network\trace\listener.trc
Trace level is currently 0
Started with pid=3080
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database_server)(PORT=1521)))
Listener completed notification to CRS on start
14-FEB-2006 11:16:13 * (CONNECT_DATA=(SID=testdb)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.128.0.36)(PORT=2315)) * establish * testdb * 12519
TNS-12519: TNS:no appropriate service handler found
14-FEB-2006 11:16:17 * (CONNECT_DATA=(SID=testdb)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.128.0.36)(PORT=2316)) * establish * testdb * 12519
TNS-12519: TNS:no appropriate service handler found
TNS-12571: TNS:packet writer failure
TNS-12560: TNS:protocol adapter error
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
14-FEB-2006 11:18:46 * service_update * testdb * 0
14-FEB-2006 11:18:46 * service_update * rep * 0
14-FEB-2006 11:18:46 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
14-FEB-2006 11:18:46 * service_update * testdb * 0
14-FEB-2006 11:18:46 * ping * 0
14-FEB-2006 11:18:46 * (CONNECT_DATA=(SID=REP)(CID=(PROGRAM=D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=database_server)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.128.0.172)(PORT=3135)) * establish * REP * 0
14-FEB-2006 11:18:46 * (CONNECT_DATA=(SID=testdb)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) *
One more database is there in the same system which is connecting without any problem.....
what could be the problem.... increasing of no. of connections leads to this error..?
Since last 2 or 3 days, our testing team also working on the same Database with some tools.. is it also creats such problems....?
due to this we are facing so many problems.....
Plz help me ....
Thanx in Adv..
Sanjeev Vibuthi
February 14, 2006 - 8:11 am UTC
are you 32 bit, how many sessions are you trying to get. Windows 32bit is very very limited in the amount of memory you can actually use, regardless of how much is installed.
Sanjeev Vibuthi, February 14, 2006 - 9:54 am UTC
Hi Tom,
Thank you ...
Yes it is 32 bit with 1GB RAM...
About 90 sessions are connected... apart from this Testing team is doing some testing with a Testing Tool....
we got this problem... when they start Tested only....
We are migrating to Solaris... Whether we get same problem in that machine also.....?
How to solve this problem on Windowss becuase till that time we have to work on the same system...
One more thing is we have some application tools also on the same System...
Only we are getting ORA-12526 Error, but when we try to connect to Database locally it is connecting...
Thanx in Adv.
Sanjeev Vibuthi
February 14, 2006 - 11:07 am UTC
you have about 275mb in your sga.
that leaves about 750mb.
the OS takes it's chunk, let's say there is about 700mb left.
"test team doing something with a test tool"
so - tell us, is there enough ram and CPU left over for your 91st session to get onto this machine, with 1 gig of ram, I'll assume 1 cpu? You might be "sort of out of steam on this machine"
Sanjeev, March 23, 2006 - 3:42 am UTC
Hi Tom,
Due to the above mentioned problem, We have shifted our Database to SUN v440 system (Solaris 10)
It has 2GB RAM ....
Total System Global Area 1912602624 bytes
Fixed Size 1979584 bytes
Variable Size 486542144 bytes
Database Buffers 1409286144 bytes
Redo Buffers 14794752 bytes
System swap space is 6 GB
We have kept this system exclusively for Database
Most of the Procedures returns refcursor which are using to populate data in Java...
Initilization Parameters:
sessions 555,
processes 500
Transactions 610
session_cached_cursors 200
cursor_sharing FORCE
open_cursors 300
But suddently we got the same ORA-12516 error ... some time it is connecting
and some times it is giving error...
the number of sessions and users are same as previous (Actual developers 25, sessions 170 ....)
My fear whether it will work when we implement this project...
that time no. of users will be increased ....(1000 concurrent Users approx)
What could be the problem.. is it in configuring of Database or Listener or any other problem....
I have registered database with Listerner also.. But problem is not solving
Thanx in Adv.
Sanjeev Vibuthi
March 23, 2006 - 10:47 am UTC
if you have 2gig of ram
why is your SGA 2gig, you've sort of USED IT ALL before the first connection comes in???!?!?!?!?!
ORA-12516
Sanjeev Vibuthi, March 24, 2006 - 2:30 am UTC
Hi Tom,
This error is on development server. Initially when i was created, it was 4gb, now they have changed to 2gb...
Can i reduced these values now?......
I am facing a lot of problems with this error ... plz help me how to go about it...
Sanjeev
March 24, 2006 - 9:48 am UTC
look at your memory settings.
you have a 2gig sga
you have 2gig of ram
think about it - do the math, there isn't anything left over. Reduce the size of your SGA.
ORA-12516 TNS:listener could not find available handler with matching protocol stack
jaweed, January 29, 2008 - 10:50 am UTC
Hi Tom,
i usually find all solution from ur existing answer this time i am little bit confuse regarding assigning sessions and sga size i want to know on which base we can assign the no of sessions and no of processes and the size of sga i will be very thankful to u for your guidance
January 30, 2008 - 9:04 am UTC
"U" is never around when you want them, are they.
How about if I answer instead?
I don't know what relationship you think there is between the number of sessions and the number of processes and the SGA size - since the major components of the SGA are
o buffer cache (which might be 5gb for a single user, or 100mb for 10,000 users - it is not a function of users that sizes a buffer cache)
o shared pool (again, not a function of users, but a function of the amount of unique sql/plsql you execute)
sessions and processes will contribute to the fixed size of the SGA and marginally impact the variable component, but in a negligible fashion.
Performance Tuning
Sanjeev Vibuthi, March 03, 2008 - 8:04 pm UTC
Hi Tom,
Your query gives previous main begin date in all records but we need previous main begin date for each member.
eg.
With the above data, if I run the report in Feb 2008, It should print the following persons
Person_id Begin_Date Sal Allowances
1 1-jan-2008 5000 200
2 1-jan-2008 5000 200
3 1-jan-2008 5000 200
IF THE REPORT RUNS IN JAN, It should print 2 and 3 person (bcoz 1 is in Jan month)
Person_id Begin_Date Sal Allowances
2 1-nov-2007 5000 200 (For this member prev date is Nov)
3 1-oct-2007 5000 200 (For this member prev date is Oct)
I modified the query:
Select wr.org_id, wh.person_id, wr.main_begin_Date, wh.sal, wh.allowances
From person_header wr, person_details wh
Where wr.header_id = wh.header_id
And wr.main_End_Date = (select max(main_end_date)
from person_header ph1, person_details pd1
where ph1.header_id = pd1.header_id
and pd1.person_id = pd.person_id -- to main query
and main_begin_date >= add_months( trunc(sysdate,'mm'), -1 )
group by main_end_date
having count(*) = 1 -- only one record and not this months record...
and count(case when main_begin_date = trunc(sysdate,'mm') then 1 end) = 0
But this also taking long time....
Thanks for your help
Sanjeev Vibuthi
March 03, 2008 - 9:40 pm UTC
huh?
what is "bcoz" by the way, I've looked it up - nothing...
no idea where this one came from - doesn't belong on this page.
http://www.netlingo.com/lookup.cfm?term=BCOZ
A reader, March 04, 2008 - 2:45 am UTC
March 04, 2008 - 7:21 am UTC
So, we are not using cell phones here. You have a keyboard, use it. I feel like I'm talking to a 12 year old when they are too lazy to use real words.
Of course one can translate 'bcoz'. That is not the point.