RAC & Failsafe :: Why Sessions Are Not Disconnected After Service Reallocation
			Feb 2, 2013
				i have 2 nodes RAC with service piodbserv2 which is active only on 2nd node. client is connected for (100% sure) to piodbserv2 on node2. i am doing relocation of this service to node1 using  -f (force) option:
[oracle@rac1 ~]$ srvctl relocate service -d piodb -s piodbserv2 -i piodb2 -t piodb1 -f
[oracle@rac1 ~]$ srvctl start service -d piodb -s piodbserv2 -i piodb1
PRKP-1064 : Service piodbserv2 is already running on instance piodb1.
[oracle@rac1 ~]$ srvctl start service -d piodb -s piodbserv2 -i piodb2
-f (force) option is described as this which forcibly rellocate users from one to another node simply disconnecting sessions from node2 in my example
but i am surprised because my session is still opened on node2 after piodbserv2 relocation
SQL> select sys_context('userenv','instance') from dual;
SYS_CONTEXT('USERENV','INSTANCE')
-------------------------------------------------------------
2
SQL> select sys_context('userenv','instance') from dual;
SYS_CONTEXT('USERENV','INSTANCE')
-------------------------------------------------------------
2
SQL> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
2
SQL>
at this moment piodbserv2 is only on node2. when i kill my user session and try connect again it will connect to node1. but i suspected that after forcibly rellocated service, my sessions will be disconnected from node2 forcibly also.
	
	View 6 Replies
  
    
	ADVERTISEMENT
    	
    	
        Sep 11, 2012
        We created a job yesterday which will call the below procedure. if we start this job, it initiates 92 sessions parellely. How it is initiating 92 sessions parelley?
procedure prc_HECTOR_CIDB_IN_PURGE
IS
      cursor CUR_PROC is
      select rowid from CUSTMODEL.HECTOR_CIDB_IN where PROCESS_FLAG in ('Y','F');
      TYPE TYP_CUR_DATA IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
      v_typ_cur_data typ_cur_data;
[code]....
	View 4 Replies
    View Related
  
    
	
    	
    	
        May 30, 2013
        I have few doubts in rac environment
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?
	View 9 Replies
    View Related
  
    
	
    	
    	
        Oct 26, 2013
        OS  :
6100-01-09-1015
oralce:
11.2.0.1.0 - 64bit RAC
	View 1 Replies
    View Related
  
    
	
    	
    	
        Jul 8, 2013
        suggest on the dependenecy between the database service and the database instance?
srvctl start instance -i db1 
will this start service as well for which the above instance is either preferred or available instance?
will it start the listener as well?
srvctl stop instance -i db1 
will this stop service as well for which the above instance is either preferred or available instance?
will it stop the listener as well?
srvctl start service -d db -s dbserve
will this start service as well for which the above instance is either preferred or available instance?
srvctl stop servive -d db -s dbserve
will this stop service as well for which the above instance is either preferred or available instance?
srvctl start database -d db
will it start instance, listener and service as well?
srvctl stop database -d db
will it stop instance, listener and service as well?
	View 1 Replies
    View Related
  
    
	
    	
    	
        Apr 7, 2011
        while statrp database am gettion error message like 
" ORA-01092:ORACLE Instance terminated. Disconnected forced"
alter log file:
Errors in file g:\oracle\product\10.1.0\admin\hrdev\udump\hrdev_ora_196.trc:
ORA-01578: ORACLE data block corrupted (file # 1, block # 57371)
ORA-01110: data file 1: 'G:\ORACLE\PRODUCT\10.1.0\ORADATA\HRDEV\SYSTEM01.DBF'
Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Thu Apr 07 10:06:54 2011
Errors in file g:\oracle\product\10.1.0\admin\hrdev\bdump\hrdev_pmon_86\0.trc:
ORA-01578: ORACLE data block corrupted (file # , block # )
[code]....
	View 3 Replies
    View Related
  
    
	
    	
    	
        Sep 18, 2012
        We have a 2-Node RAC11g R2.0.3 installed on Linux 5.5. 
My problem to have a private connection disconnected on daily bases at 12:00 PM and 3:00 PM ONLY,and come back life within 2 minuets,I am using cross-over cable to connect those private interface , So in the ocssd.log Stating :- 
2012-09-18 15:06:43.735: [    CSSD][1096161600]clssnmPollingThread: node racmain1 (1) at 50% heartbeat fatal, removal in 14.340 seconds
2012-09-18 15:06:50.752: [    CSSD][1096161600]clssnmPollingThread: node racmain1 (1) at 75% heartbeat fatal, removal in 7.330 seconds
2012-09-18 15:07:19.821: [    CSSD][1096161600]clssnmPollingThread: node racmain1 (1) at 90% heartbeat fatal, removal in 2.480 seconds, seedhbimpd 1
And the instance is restarted accordingly.
	View 5 Replies
    View Related
  
    
	
    	
    	
        Jul 23, 2010
        I have set my processes and session value in my database as 1000 and 1248 respectively. I am using 11.2.0.1 but when I restart my DB it showing me sessions=1524...its too high value then what I set.I don't know why it showing higher value than the value I set... 
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=1248 SCOPE=SPFILE;
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u02/app/oracle/product/11.2.0/db_1/dbs/spfileeng11g02.ora
System parameters with non-default values:
  processes                 = 1000
  sessions                    = 1524
And one more thing  is there any recommended formula for calculate sessions in 11g? [ I found one formual from google: 
SESSIONS derived: (1.1 * PROCESSES) + 5
	View 3 Replies
    View Related
  
    
	
    	
    	
        Aug 31, 2012
        One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
	View 9 Replies
    View Related
  
    
	
    	
    	
        Dec 12, 2011
        In our databace there are around 300 sessions were in inactive state.
Database is not releas the sessions. 
	View 2 Replies
    View Related
  
    
	
    	
    	
        Apr 22, 2011
        our db shows more than 200 INACTIVE Sessions ; and the DBA plans to reboot the db to get rid of these sessions . Can we not KILL these sessions and avoid the reboot ?
	View 4 Replies
    View Related
  
    
	
    	
    	
        Jan 17, 2013
        I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
	View 9 Replies
    View Related
  
    
	
    	
    	
        Nov 20, 2012
        Oracle database 11g Expression Edition 11.2.0.2
I want to change the the number of processes to 150 (default is 100) I did this by issuing the below command.
alter system set processes=150 scope=spfile;But why that modified value is not being reflected?
select name, value from v$parameter where name in ('sessions','processes','transactions');
NAME                 VALUE
-------------------- -----
processes            100
sessions             172
transactions         189
	View 8 Replies
    View Related
  
    
	
    	
    	
        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
	View 6 Replies
    View Related
  
    
	
    	
    	
        May 6, 2011
        The following link states
Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
[URL].....
Of course the following is not working even when resource_limit is TRUE
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;
How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.
	View 5 Replies
    View Related
  
    
	
    	
    	
        Apr 11, 2011
        I have an assignment, i am suppose to disconnect all the users that are blocking sessions for over three hours. Below is my code which is not working 
Create or replace procedure b_user IS
Declare 
ssid number;
serial number;
Begin
[code]......
	View 7 Replies
    View Related
  
    
	
    	
    	
        Apr 27, 2012
        how to avoid oracle deadlock with two sessions. Here is my example, We use Select query with For Update in order to get the unique number from a table. When one user has accessed this query, while the other user tries, System gives a Performace slow and when checked it is due to locking of this select query with For update.
We don't want to use For update WAIT or FOR UPDATE NOWAIT since these will result in missing of number.
	View 3 Replies
    View Related
  
    
	
    	
    	
        Oct 30, 2010
        why are there in the Administration -->   --> Session  2 entries for 1 database connection (here with benthic golden).But if I login in a browser, there is only 1 entry for this database connection
	View 3 Replies
    View Related
  
    
	
    	
    	
        Oct 20, 2011
        I have a RAC on 9i and I want to know the number of active sessions that it's executing the same sql sentence.I'm using this sql but I'm not sure that this query give me te correct result. 
SELECT se.inst_id,se.status,se.SQL_HASH_VALUE,se.USERNAME,substr(sq.SQL_TEXT,1,50) SQL,count(*)
FROM GV$SESSION se, gv$sql sq
WHERE se.STATUS='ACTIVE'
and se.username is not null
[code]...
	View 2 Replies
    View Related
  
    
	
    	
    	
        May 27, 2011
        I create table in session1: 
create table marco_tmp_270511 
as 
select 5 as t from dual 
Then I log in session2 and perform query to 
select * from marco_tmp_270511  and receive 5 in column t. 
Then I return to session1 and perform: 
delete marco_tmp_270511;
insert into marco_tmp_270511
select 99 as t from dual ;
select * from marco_tmp_270511;
Then I go again to session2 and perform query to 
select * from marco_tmp_270511 
and still receive 5 in column t. How do I synchronize data between sessions? 
	View 3 Replies
    View Related
  
    
	
    	
    	
        Mar 1, 2012
        I want to create a procedure that kills the sessions of the user TEST. 
I have coded the below, but that dont work.
CREATE OR REPLACE PACKAGE BODY SYSTEM.test AS procedure killsessions IS
v_sessions_sql varchar2(40);
--v_sessions_sql2 varchar2(40);
[Code].....
	View 4 Replies
    View Related
  
    
	
    	
    	
        Jul 3, 2013
        On the oracle 11g, we see too many invalid sessions. Because of which, login is getting restricted. What do i need to do to kill those invalid session periodically, which will avoid of restarting of system.
	View 4 Replies
    View Related
  
    
	
    	
    	
        Apr 14, 2011
        I am calling one procedure through job and after submitting the job i am using commit. problem is when job close the session it commit the code which is inside procedure and as per my understanding in another session also, which i want to avoid . 
my procedure is
Create My_proc is
Begin
Insert into my_table values(1,2,3);
End;
And my job proc is
Create my_job_proc is
Begin
dbms_job.submit(jobno,'my_proc;',sysdate,null,null,false);
commit;
End;
now i am calling my job proc is
Declare
Begin
my_job_proc;
End;
Now problem is once it finish, it will insert the data into my_table which i don't want until I call Commit.I already try 'Set autocommit off'
	View 9 Replies
    View Related
  
    
	
    	
    	
        Oct 4, 2012
        I need to identify, whether my current session is one of slave sessions scheduled as Oracle scheduler job via DBMS_PARALLEL_EXECUTE or not. I already succeeded using join of dba_scheduler_running_jobs, user_parallel_execute_chunks and v$session, but I feel that this is not optimal approach.
	View 1 Replies
    View Related
  
    
	
    	
    	
        Aug 23, 2010
        lsnrctl RELOAD [listener_name] Hope the above command wont terminate the current sessions to the database.
	View 2 Replies
    View Related
  
    
	
    	
    	
        Jun 26, 2013
        here' my code.
delimiter //
SELECT CONNECTION_ID()//
LOCK TABLES source.jos_daikin_control_card_fcu_model WRITE//
[Code].....
ERROR 1192 (HY000): Can't execute the given command because you have active lock
ed tables or an active transaction
Is there a way to prevent other session from accessing the tables called in the procedure?
	View 3 Replies
    View Related
  
    
	
    	
    	
        Mar 9, 2012
        My application is opening a lot of sessions in my DB server. I applied resource_limit=true and idle_time=15 min. ans assign this profile to all application user.
Now I am seeing a lot of sessions having status sniped in v$session.
I want to clean up these sniped sessions and what they mean.
	View 19 Replies
    View Related
  
    
	
    	
    	
        Aug 19, 2010
        I am using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.
But there is still one problem.  During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user. 
The blocked sessions will be displayed in the admin page under blocking sessions link.  There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.
	View 1 Replies
    View Related
  
    
	
    	
    	
        Feb 21, 2011
        My statspack reports are sometimes showing figures such as this from the Foreground Wait Events section:
Avg          %Total
                                          %Tim Total Wait   wait    Waits   Call
Event                               Waits  out   Time (s)   (ms)     /txn   Time
---------------------------- ------------ ---- ---------- ------ -------- ------
PL/SQL lock timer                   1,430  100      6,982   4883      0.0   18.0
enq: TM - contention                    3    0      6,438 ######      0.0   16.6
db file sequential read           310,385    0      1,765      6      3.5    4.6
TCP Socket (KGAS)                  12,593   13        180     14      0.1     .5
db file scattered read             23,673    0         87      4      0.3     .2
log file sync                      19,409    0         82      4      0.2     .2
ksfd: async disk IO                 1,680    0         29     17      0.0     .1
[code].....
The TM lock that occurred 3 times appears to be disastrous.Historically, this could be caused by missing indexes on foreign key columns. 
How can I be alerted when this event occurs, so that I can do some real-time investigation into the sessions and the SQL that hit it? I suppose I could schedule a job to query v$lock/dba_waiters/dba_blockers every few minutes, but is there a better way? Any standard edition scripts for this?
	View 4 Replies
    View Related
  
    
	
    	
    	
        Sep 10, 2012
        I need to trace all sessions created from specific user and specific machine . Got some code from internet and customised .
CREATE OR REPLACE TRIGGER SYS.enable_trace_trigger
AFTER LOGON ON DATABASE
DECLARE
[Code]....
I have yet not created it on db . 
	View 3 Replies
    View Related