6 Replies Latest reply on Sep 28, 2012 7:37 PM by Valentin Minzatu

    suprise with RAC behaviour


      i did some test with my RAC environment (2 nodes, 10g).

      i am using TAF, and users connect with PRECONNECT method and SELECT option.
      so teorethically if i invoke something like this on the first node:

      select from test;*

      where test contains 3 milions rows, and simulate crash of this node. my session will be reconnected on the other one and select will be reinvoked.
      and this behaves in my enviromnent as i suspected.


      but second test was with inserts. i know that DML reinvoking is not supported in RAC so i even didnt try to do this but i thought that maybe procedures execution could be reinvoked in RAC.

      lets try this exemplary procedure:

      create or replace procedure dotest is
      v1 date;
      for i in 1..1000000 loop
      select sysdate into v1 from dual;
      end loop;
      end dotest;

      when i do:

      exec dotest;

      and crash node on which this procedure was executed, the session hangs and nothing happens. i thought that maybe some ORA will be displayed but during such simulation nothing happened.

      is it normal behavior that RAC is not reinvoking procedures after node crash?

        • 1. Re: suprise with RAC behaviour
          I think it is normal because TAF documentation only mentions that SELECT queries can be replayed (running a stored procedure is different that running a SELECT query from database point of view).
          • 2. Re: suprise with RAC behaviour
            Valentin Minzatu
            Is your session failing over and the procedure stalls or the fail over does not take place at all?
            • 3. Re: suprise with RAC behaviour
              If you take a minute and think about what is happening, it makes sense. When a WRITE operation is in progress and fails, you WANT it to completely fail and rollback. No database can predict - nor do you want it to predict - the state of that operation. It MUST be managed by your application to guarantee that it does what you expect it too...
              • 4. Re: suprise with RAC behaviour
                Valentin Minzatu
                Not sure what I missed here: where do you see the WRITE operation?
                • 5. Re: suprise with RAC behaviour
                  sorry. there was no DML in my example. ... and i did it intentionally. i just misdescribed my example.

                  my first intention was to do INSERTs in LOOP (thats why I mentioned about that), but i resigned from this, because i know that RAC doe's not support DML... so i wanted do check what RAC will behave when i do SELECTs in LOOP and enforce node restart - and i know now.

                  you asked me also about: does my procedure stall and failover does not take place. i am sure that my procedure stall, and i didn't have control under my session, but i don't know if failover was perfomed or not - i didn't check it, because of lack of my time at work.
                  but i suspect that it wasn't performed (i mean failover) - probably.

                  i am thinking how RAC enviromnent behave if we have (for example) end user interface application which is based on invoking oracle stored procedures. now i know that in case of node unavailability each procedure which was executed and not finished (in the middle) will fail - even despite of fact that this procedure performs read only operation. each new activity from end user interface will create session on the new node and will work properly but procedures which was in the middle just will fail.

                  one question: how long your failover takes place when you primary instance fails? is it secounds, or ten of secounds?


                  Edited by: piotrtal on Sep 27, 2012 9:15 PM
                  • 6. Re: suprise with RAC behaviour
                    Valentin Minzatu
                    Failover duration depends on the load, but I think in your case it should be almost instantaneous as I assume this is a test db, nobody else uses. Can you check the session before you kill the node? (make sure it is configured for failover)

                    use the statement in your other post:
                    SELECT inst_id, sid, serial#, username, failover_type, failover_method, service_name FROM gv$session ... 
                    Edited by: Valentin Minzatu on Sep 27, 2012 3:41 PM