Fwd: Session migrated to another instance
Date: Wed, 19 Jun 2013 22:46:45 +0800
Message-ID: <CAMNBsZsk_6=DUqz1JZUs1nhEpufT0p+T5RbohfU=bViZR2ky5g_at_mail.gmail.com>
Testing today
Curious. My SID has changed from 1234 to 6789 sometime between 09:56 and 11:28. I've verified that no instance in the cluster has been restarted. I had issued NO commands from this SQLPlus session. The TNS connect-string I use is a simple connect string specifying the SCAN address.
So, my session has been migrated maybe from instance 1 to instance 2 or 3 and then back to instance 1 in that 1.5 hours (while it was idle) ? Was it migrated because the service was migrated ? I think I can discount server-side TAF because no instance was restarted.
Note : I am a user, not a DBA in this database.
SQL> set time on
09:55:08 SQL> select instance_number from v$instance;
INSTANCE_NUMBER
1
1 row selected.
09:55:14 SQL> select distinct sid from v$mystat;
SID
1234
1 row selected.
09:55:22 SQL> select sid, serial# from v$session where sid=1234;
SID SERIAL#
---------- ----------
1234 28693
1 row selected.
09:55:33 SQL> select failover_type, failover_method, failed_over from v$session where sid=1234;
FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT BASIC NO
1 row selected.
09:56:35 SQL>
11:28:13 SQL> select instance_number from v$instance;
INSTANCE_NUMBER
1
1 row selected.
11:28:23 SQL> select distinct sid from v$mystat;
SID
6789
1 row selected.
11:28:32 SQL> select sid, serial# from v$session where sid=1234;
SID SERIAL#
---------- ----------
1234 28693
1 row selected.
11:28:43 SQL> select sid, serial# from v$session where sid=6789;
SID SERIAL#
---------- ----------
6789 3281
1 row selected.
11:29:07 SQL> select failover_type, failover_method, failed_over from v$session where sid=1234;
FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT BASIC NO
1 row selected.
11:29:18 SQL> select failover_type, failover_method, failed_over from v$session where sid=6789;
FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT BASIC YES
1 row selected.
11:29:26 SQL>
- Forwarded message ---------- From: Hemant K Chitale <hemantkchitale_at_gmail.com> Date: Sun, Jun 16, 2013 at 10:38 PM Subject: Session migrated to another instance To: ORACLE-L <oracle-l_at_freelists.org>
How is this done ?
At 17:11, my sqlplus session was connected to instance 3 in the RAC
cluster.
At 17:53, the session is on instance 1.
No explicit commands issued from the client.
Disclaimer : In this case I am a *user* not the DBA.
SQL> set time on
17:11:44 SQL> select sys_context('USERENV','INSTANCE') from dual;
SYS_CONTEXT('USERENV','INSTANCE')
--
3
1 row selected.
17:11:47 SQL> select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
--
3118
1 row selected.
17:11:49 SQL> select sys_context('USERENV','INSTANCE') from dual;
SYS_CONTEXT('USERENV','INSTANCE')
--
1
1 row selected.
17:53:38 SQL> select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
--
1272
1 row selected.
17:53:42 SQL>
--
Hemant K Chitale
http://hemantoracledba.blogspot.com
--
Hemant K Chitale
http://hemantoracledba.blogspot.com http://hemantscribbles.blogspot.com http://web.singnet.com.sg/~hkchital
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 19 2013 - 16:46:45 CEST