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