Fwd: Session migrated to another instance

  • From: Hemant K Chitale <hemantkchitale@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 19 Jun 2013 22:46:45 +0800

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@xxxxxxxxx>
Date: Sun, Jun 16, 2013 at 10:38 PM
Subject: Session migrated to another instance
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>


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


--
//www.freelists.org/webpage/oracle-l


Other related posts:

  • » Fwd: Session migrated to another instance - Hemant K Chitale