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
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:
Connect to the database and check which instance I am connected to, eg (on SQL*Plus):
select instance_name, host_name from v$instance;
Shut down the instance I am connected to using
shutdown abort(in a separate session)
On the same session where I performed step 1, if I re-run the same
selectstatement, 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.