Processes / Sessions Connected To DB After ORA 20 Error?
Dec 28, 2010
Is there any way to tell what processes/sessions were connected to a DB after I get an ORA 20error. As you can see from the query results below, I did hit the max value of processes but I would like to know when it happen and who was connected.
I realize I can up the value, but before I do that I want to see what caused this to happen.
SQL> select * from v$resource_limit where resource_name in ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
LIMIT_VALU
----------
processes 221 1000 1000
1000
sessions 223 1020 1105
1105
1.In 2 node rac while adding datafile to tablespace if you forget to mention '+'then what will happen whether it is going to be create or it throws an error if it creates where exactly located and other node users how to work on that tablespace .what all steps to perform that datafile is useful for all node users.
2. In Rac environment how to check how many sessions connected to particular node.
3)In Rac any node is evicted due to network failure then after we rebuild the network .Is there any steps to do manually to access the failure node after rebuilding the network or it will automatically available in cluster group which service is perform this activity.
4.While configuring cluster ware you choose voting disk and ocr disk location and which redundancy you will choose suppose if you go for normal redundancy how many disks you can select for each file either one or two?
I first started database, then gone to option Run SQL command line. After that while creating table it is showing error like "SP2-0640: Not connected."
I am getting "ORA-03114: not connected to ORACLE" while installing "Oracle Database 11GR2" on "Oracle RAC 11R2" cluster setup. I am getting this error at the time of "Oracle Database Configuration Assistant" configuration reaches till 39% on HP-UX 11.31 IA64 platform.
I am using Oracle 10g on Fedora Linux and trying to learn a bit of pl/sql. Here is the piece of code that I today tried to run in the SQL Plus window but it back with an error message saying SP2-0640 Not connected.
set serveroutput on declare x number; begin x :=7777; dbms_output.put_line('x= '); dbms_output.put_line(x); end;
We have 11.2.0.3 RAC on Oracle Virtual Box,OEL 5.8. Storage is ASM on EMC SAN.Even when the database is not connected to application and thus open but idle and none is working on it, and all other virtual machines are powered off, we are still getting a continuous i/o in SAN.Since I am new to RAC setup I need to know the few things...
1.Is it normal behaviour? 2.If yes, what is the reason of this i/o? Is it due to continuous ping on voting disk?..write in OCR? automatic backup of OCR?
Attached File(s) SAN_I0_Issue.png ( 65.33K ) Number of downloads: 2
Through RMAN i have to connect to two databases having same name but the databases are at two different nodes.Can RMAN recognise the 2 databases.If yes how? I want to know the command.
In the program i wrote, posted in this thread I parallelize 340 jobs.
To do this, according to oracle documentation, i set the job_queue_processes parameters to 450. This works fine, all jobs are submitted, but, when i chek the dba_scheduler_job_run_details, i notice that there is a difference between REQ_START_DATE, which is my requested start date, when i enabled my job, and the ACTUAL_START_DATE, which is the date on which Oracle start this process.
And the more i submit job, the more difference between ACTUAL_START_DATE and REQ_START_DATE increase. But for each job, run duration is less than 1second. I check the processes parameter, and it is set to 100. if i have to increase the processes parameter to 450 too ?
I am having a data guard setup done in oracle 10g(10.2.0.4.0) WINDOWS Platform. some of the archive process are locked in primary db side.
PROCESS STATUS LOG_SEQUENCE STAT -------- ---------- ------------ ---- 0 ACTIVE 0 IDLE 1 ACTIVE 804462 IDLE 2 ACTIVE 0 IDLE 3 ACTIVE 0 IDLE 4 ACTIVE 0 IDLE 5 ACTIVE 800011 BUSY 6 ACTIVE 0 IDLE 7 ACTIVE 800009 BUSY 8 ACTIVE 0 IDLE 9 ACTIVE 802335 BUSY 10 ACTIVE 0 IDLE
Shall we kill the locking archive processes? killing archive processes will cause any problem?
In PL\SQL program, I am writing information from one table to file. In my current architecture, I am writing the information to approximately 1000 files.
If I put the database write operation in another package and in another method and call this method from my PL\SQL program asychronously, can that increase performance?
My Oracle DB is working properly till yesterday. When i tried connecting to it today using scott/tiger@orcl its not accepting.So i entered as system/system..it connected
when i tried to select instance_name from v$instance; it is giving me error
I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE; DROP USER test CASCADE * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - ProductionI got below error on the datases.
ORA-00020: maximum number of processesBased on the suggestion got from previous posts, I did kill some (LOCAL=NO) processes to get myself in. When I looked at the number of processes the number is 150, I changed it to 1000. After a day I got the same error.
ORA-00020: maximum number of processes (1000) exceededI killed processes again to let my self in, bases on the post below, I queried database
ORA-00020: maximum number of processes (%s) exceededHere is the query to find out if there are any processes with out session attached to it.
select spid from v$process where addr not in (select paddr from v$session);Result
SPID null 35259840 36897866 13369762
[Code]...
To determine the from where greatest number of processes originated
SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;Machine Count(*) 2 CAPITAL-D3F0092 1 DQAKDCDKLCSW01 14 desmond 20 DQAKDCDKLCSA01 52
My question is why I am keep getting this? Why do increasing process from 150 to 1000 did not resolve the issue?
What can I do to make sure that I don't get this error again?
ORA-00020: maximum number of processes (1100) exceeded
my oracle version is 10.2.0.2.0
when i was trying to login to oracle i got :ERROR at line 1: ORA-01012: not logged on
lucky i was having another sqlplus login ..
wht i should do to bring the number of process down without increasing the process value and shuting/restaring down db.
"Usually, when this happens, I want to get connected to the instance, so that I can try to figure out which user is causing the problem. One way to do that is to use the 'ps' command to identify some of the oldest server processes, and kill the 3-5 oldest processes, and then quickly try connecting to the database. Once you're in, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there."
this is what i got from [URL]....
1.if i am giving ps -ef in OS it will give lot of detail ... 2. in v$session wht i should look for ....
I don't know if there is a beginner forum out there for PL SQL, but I cannot find one.I just started taking an Intermediate Oracle course to learn PL SQL. Today is my first day and I just installed Oracle 11g. I was in SQL Plus, tried the edit command. I got the message "wrote file Afiedt.buf" and then it was just a flashing cursor in the line below. I could not type any commands. I just hit the X to close the program.
What could I have done to exit the program with that flashing cursor since I was unable to type anything?
We have an oracle db V7.1 running on Open VMS V6.2. Lately we are having problems with oracle processes crashing...When I do a 'show sys' on VMS...I dont see any oracle processes running on it.....It should show 4 oracle processes....The trace is turned off on DB and alert.log has older info...We have another log which says the following
====================================== Wed 19:32:26 NoFLT Wed 19:32:26 P19: (SendDelays) ORA-01092: (Cnct err, can't get err txt. See Servr Msgs & Codes Manua Wed 19:32:26 ORA_NAS_PMON, ORA_NAS_LGWR, ORA_NAS_SMON, ORA_NAS_RECO are DEAD Wed 19:32:26 P19: (GetNumDelaysNotFilled) ORA-03114: (Cnct err, can't get err tx t. See Servr Msgs & Codes Manua Wed 19:32:26 10 ====================================
one of the drives has lot of errors but not sure how to check those errors....
I tried to restart the server and sometimes it comes backup. I have backups but really like to solve this issue. I know where the control files are located but not sure what would be good point to start this.
I have a data base for the bank application development. So there might be more than one sessions of the application running on the data base as well as there might be more than one data base sessions being connected from the dbas.
There is refresh procedure to refresh the data base from the baseload scripts ,So for that first we need to disconnect the users (Kill the sessions) and then drop the users then recreate the users and populate the users with the base load data.
I have prepared the killing and dropping user script in the following manner, Most of the times it is working fine and killing the users but some times it is not able to kill the drop the users it is erroring out with the error that can not dorp the users as there are active sessions running ,
set termout on set echo on spool Kill_sessions_drop_users.log DECLARE v_alt_stmt VARCHAR2(1000) := 'ALTER SYSTEM DISCONNECT SESSION '''; v_kill_stmt VARCHAR2(1000); [code].......
--This will kill all the sessions except the current session
CURSOR cur_usr_dtls IS SELECT username FROM all_users WHERE username IN ('ACTIVITI','TP','TPCORR','TPTUX','TPVIEW'); v_dr_stmt VARCHAR2(1000); BEGIN EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION'; DBMS_OUTPUT.PUT_LINE('The database has been put in the restricted mode'); FOR v_cur_ssn IN cur_session [code]........
--Here we are making the processor to wait for 60 mins so that that the killing will happen at the OS label
DBMS_LOCK.SLEEP(60); FOR v_cur_usr_dtls IN cur_usr_dtls LOOP v_dr_stmt :='DROP USER '||v_cur_usr_dtls.username ||' CASCADE'; BEGIN EXECUTE IMMEDIATE v_dr_stmt; [code]........
I know that in the profiles of the user I can set limit for the number of sessions per database user, using the below command:
alter profile <profile_name> limit sessions_per_user=<required_number>;
I need to set maximum number of processes for the application user so in case they opened too many number of processes on the database they do not block the automated jobs to be run.My questions:
1. set a limit on sessions_per_user is it the same as a limit on processes? 1 proccess= 1 session ? Is there any way to limit the number of processes (not sessions) for a database user? 2. I am using RAC so the number of sessions is per the whole database, while the max number of processes is by instance (not database), so even I set a limit on the number of sessions (above the limit of processes for one of the instances) one instance may face ORA-20 errors, while the max number of sessions did not hit the limit. is there any workaround for this case?
If I'm looking on v$session - values in osuser column for some machines displayed with DOMAINNAME, and for other - without. Even same user (MYDOMAIN\ADMINISTRATOR) from 2 different machines displayed in 2 different ways on the same db:
MYDOMAIN\Administrator from machine A, and administrator from machine B.
Both machines are belongs to same windows domain. DB (oracle 10g) have no "DOMAIN" defined.
What make the difference? And can I somehow to affect this behaviour? I mean, can I define that all users will be displayed as DOMAINNAME\USERNAME or MACHINENAME\USERNAME(in case of no domain exists)?
We are currently using OCI to connect to Oracle DB using c language. Each process has its own dedicated Connection and Env handle, session handle ...
Right now we decided to use Connection Pooling. In the documentation , Connection pooling examples were using multi-threaded environment. But in our case its multi-process system and we cant modify products architecture to multi-thread.
I would like to know how we can use Connection Pooling on multiple processes.
I am facing difficulty in connecting Oracle 10g client to Oracle 10g database. Both the machines are in windows xp with service pack-2. The following observations are made in server machine
1. The loopback test of the server is successful. 2. One net service name created in the server end. 3. Database connection is successful with above service name. 4. tnsping <server ip address> is successful
# This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT.
# This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT.
I'm a beginner in the PRO*C and I have this issue/I have to maintain a multithreaded C++ PRO*C application and has the following structure
1) Application runs waiting for requirements 2) When detects a requirement it creates a Thread to work 3) The thread connects to Oracle with a EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; instruction 4) Calls some anonymous calls to execute a stored procedure
EXEC SQL EXECUTE BEGIN commit; :Status:=stored_procedure(:param1, :param2); commit; END; END-EXEC;
5) If anything went wrong then issues a EXEC SQL ROLLBACK WORK RELEASE; then reconnects and the thread die 6) If everything was OK then the thread dies, but never called a EXEC SQL COMMIT WORK RELEASE; to disconnect from Oracle
I don't know why never the application disconnects from Oracle, maybe is for performance. Sometimes the application crashes and I believe that maybe the error is for never freeing the connections, the PROC compile line is this
Which is the way to implement the connections?, connect and disconnect when done? or just keep the connection alive? Which way has better performance, connect and disconnect or just keep the opened connection?
I have two Oracle databases (test1- 10gr2,test2 - 11gr2) on rhel 5.5. is it possible to find which particular oracle process belongs to which home from OS level. was trying to find which db processes are consuming more resources on my host.
I need to get a way to find, The client fired the SQL Query is still connected to the database or not. For e.g.
I fire an SQL query using SQL Developer, Query approximately takes 5 mins to complete. As soon as I fire the query SQL devloper hangs and I close it using task manager.
Is there any way I can identify such session where SQL developer fired the query is terminated but the query fired is still running.
I have to identify such Orphan session and terminate it.
I want to install datbase schemas using RCU but it is throwing me an message
"RCU-6083:Failed - Check prerequisites requirement for selected component:WEBCENTER Please refer to RCU log at C:OracleRCUofm_rcu_win32_11.1.1.2.1_disk1_1of1 cuHome culoglogdir.2013-01-22_16-03 cu.log for details. RCU-6107:DB Init Param Prerequisite failure for: PROCESSES Current Value is 150. It should be greater than or equal to 300."
I tried to change the process using SQL> alter system set processes=300 scope=spfile; and got the msg that system altered but still the error persisit. I reffered to a link RCU-6107, RCU-6092 for the same here it is mentioned in one ofthe post that some user has changed it manually ion init.ora file... Changing manually or through cmd ....
oracle 11.2.0.3 I have insert only tables that receive srecords from multiple processes at a rate of about 200/second. Each transaction can have up to 100 records. I have another set of processes that queries this table for the latest data. These processes run anywhere from once a minute to once an hour. Processes do not get all of the data. They get data based on a type field.
Both of these are from java middle tiers. The process that queries data (The subscriber) does so at the request of many remote servers (there will be vast numbers). I am not allowed to expose these downstream databases to the internet (they are not oracle DBs anyway) so I cannot use streams or golden gate
So basicallyInsert Process: multiple sessions that combined insert records up to 200/second. There will be between 1-100 records per commit.
Query Process: Downtream process makes a request to my middle tier. This middle tier runs a query to get the latest data and passes it back. This design is set and I cannot change it.
1. right now we capture the insert time of the record. However, at this rate of inserts some processes will commit faster than others. So I cant use a 'greater than my insert time' query. 2. streams/golden gate won't work. can't register these DBs. 3. don't want to serialize my inserts because since I am not sure I can keep up with the insert rate. I don't even know what the specs will be for the production hardware. I have to actually deliver this before its decided. So I am being conservative. 4. I really want to avoid updates on this table if possible. In part due to my limited ability to test. 5. due to the number of downstream processes it is possible that it will request data and for some reason fail to insert the data locally. So the downstream application will keep track of the latest data it received. This means that a subscriber may need to request the same data again.
Is there a way to set up change data capture with multiple subscribers to handle this? if my subscribers are just queries? All the queries come from the same servers(there will be several, but all the same thing). If so, when I performance test this are there any wait issues I should keep an eye on?
