Tweeted twitter.com/#!/StackDBAs/status/131510960803418114
4 added 856 characters in body
source | link

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.


If I modify the tnsnames.ora in the client to something like:

RAC =
  (DESCRIPTION =
    (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
       (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)
       )
    )
  )

Then it works the way I expect it to. And this is what had to be done prior to 11gR2, but SCAN is supposed to handle all this automatically, so a simple entry like:

RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Should work the exact same way.

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.


If I modify the tnsnames.ora in the client to something like:

RAC =
  (DESCRIPTION =
    (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
       (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)
       )
    )
  )

Then it works the way I expect it to. And this is what had to be done prior to 11gR2, but SCAN is supposed to handle all this automatically, so a simple entry like:

RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Should work the exact same way.

3 added 144 characters in body
source | link

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg:

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.

2 edited body
source | link

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg:

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: 12544XXXXX
Session ID: 194YYY Serial number: 17ZZ

What's going on?

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg:

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: 12544
Session ID: 194 Serial number: 17

What's going on?

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

  1. Connect to the database and check which instance I am connected to, eg:

    select instance_name, host_name from v$instance;
    
  2. Shut down the instance I am connected to using shutdown abort (in a separate session)

  3. On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

1
source | link