Oracle under Service Guard ORACLE_SID vs TWO_TASK

[John Eaton]

Can't find a single occurance of two_task searching the forums, so maybe someone can help? I've posted it under the SG topic not DB topic as it pertains to an SG configuration.

We are running a 2 node cluster of rp7420's with 2 1.6tb 9i DBs, and various apps. I'm trying to finalize the SG configuration for all of this, but I keep getting the run-around from our DBAs on ORACLE_SID vs TWO_TASK.
Not being a DBA, I don't know the 'real' difference, and can't find much to help on metalink, or oracle docs.
The DBA says we need to abandon the use of ORACLE_SID, and only use TWO_TASK. If we were client-server ONLY it would make sense, but the app is normally local to the DB, unless we have a failover occur, then it's remote.
Specifically we have 3 3-tier apps (PC-app-DB), 1 1-tier app (always local to DB for permformance reasons), and the main DB. The other DB is a replica under shareplex. (anyone have tips on shareplex under SG when source and target are on same server - ie failed over?)
My understanding is ORACLE_SID is for local connections only, and TWO_TASK is sqlnet only. If it's a local connection using TWO_TASK, is it still going to connect local (beq) not via sqlnet?
Can anyone cut throught the DBA mumbo-jumbo and help me understand this? Thanks!

[TwoProc · Accepted solution]

John,

Leaving out a lot detail and other options - you've basically got it.
TWO_TASK is what I can use to make a connection to a database that is not defined within my local ORACLE_HOME's environment (via ORACLE_SID) - AUTOMATICALLY.

In other words, If TWO_TASK is defined then all applications making connections only have to provide user/password combinations to make a connection - they don't have to explicity say a certain database by name.

Example sqlplus Remote Connection w/o TWO_TASK:

sqlplus me/mypassword@MYDATABASE

now, if I define:
TWO_TASK=MYDATABASE
I can just do:
sqlplus me/mypassword
to connect to the database.

What the difference is not really local vs remote (you can use TWO_TASK on the same machine), but whether or not you're using the SAME ORACLE_HOME (and thus ORACLE_SID) for your application that the database is using to host the database.

So, I can use One ORACLE_HOME just for the database, and another one that just has client tools. ONE*(see below) way of automating the connection between the two is to set the TWO_TASK variable to match the connection in the tnsnames.ora file (found in the client's ORACLE_HOME/network/admin directory). Once set you can connect w/o specifying the database connection.

This works as well from the same machine as a remote machine. This fact is probably why your guys are trying to set everything up via TWO_TASK - so that application client/database server combination can float pretty much place to place without having to fix the connection method each time.

ONE* - there is another way to connect without the overhead of using SQLNET for the same machine, and yet still be able to have two different ORACLE_HOMEs and it involves using the bequeath protocol - but that doesn't give you a no mess/no fuss portable solution.

What your guys are going for is something that is very portable and independent of tier location. If the app and db server HAPPEN to be on the same tier - or not; the setup for the application remains the same. This will allow SG to do its work for you more simply - without having to write scripts to fix files for you with a lot dependency cases to run for the different possible combinations.

[TwoProc]

John,

Question answered?

[John Eaton]

John,
Quite an answer, and I'm not sure it if answers my question. It might, but it's a bit beyond my knowledge level.

A few comments -

'sqlplus' is not used as a command for the app. It's a complied app with hooks into oracle libraries. It may be a mechanism to validate our setup, however. Normally (when used) we never need to supply any arguments to sqlplus, ORACLE_SID is set, and the users are not permitted to put username/password on the command line.

ORACLE_HOME is not an issue. There is only 1, and there has never been any discussion of others. Don't know if it would matter, or not.

I negleted to metion that TNS_ADMIN is also 'required', as the DBAs have created 1 for each DB, on each server. My understanding is that when we failover the DB, the tns file is no longer correct as the DB is now local (tns files differ for the same DB on different servers I'm told by the apps guy. He's frustrated by the DBAs as well!). Does this make sense?

Portability is key, but I've yet to see it work when the DB and app are on seperate servers -OR- on the same server with the same settings.

[Rita C Workman]

Hope you don't mind me jumping in here...I find this TWO_TASK interesting;

I was just wondering if one of the reasons to use this is so that there are no issues with remote connection to the DB, then couldn't this same effect have been achieved by pointing everybody to a pkg name in your tns files. Even the clients would point to the pkg name, hence when you fail to a remote box, the only change you'd have to make is to set the IP to the pkg to the new subnet, refresh DNS and bring it back up.
No end-user or DB overhead to change...

Rita

[John Eaton]

Rita,
Don't think this is any help.
The package already uses a floating IP, and refreshing DNS is not an option.
The issue is how the connection to oracle is made, via memory (bequeath) or the network (sqlnet). The system calls are different.

[TwoProc]

John,

Sqlplus is just a command line tool to connect to ORACLE. I mention it because of its universality for using against Oracle databases (sorta like if you're using a UNIX box you can expect to have vi available). The attempt was merely to show you (with some sort of tool) how the TWO_TASK variable works.

TNS_ADMIN is just an environment variable that tells the system where the sqlnet configuration files (listener.ora and tnsnames.ora) files live. It looks like your DBA's use the same ORACLE_HOME, but different TNS_ADMIN and ORACLE_SID to manage different running databases (alll sound strategies). The place where I mentioned the tnsnames.ora file location is just the default when TNS_ADMIN is not defined.

If there is only one ORACLE_HOME wherein BOTH the client tools and the database are on the same server, sharing the same ORACLE_HOME - then I really wouldn't suggest using TWO_TASK. They really only need a direct connection via ORACLE_SID. Using TWO_TASK - invokes sqlnet, which is actually slower - even on the same machine than a direct connection (unless bequeath protocol IPC is used - but there are limitations to what can connect to it; e.g. jdbc-thin libraries don't like IPC).

But, that is really determined by the following test:
What "parts" of the system are moving with service guard that we are speaking of currently?
Are you moving just the database(s)? If so, you would need to invoke the two_task strategy.

But, you just said that there is only one ORACLE_HOME, therefore this implies that the server-side applications actually run off of the same ORACLE_HOME - and ALL of this would move - TOOGETHER.
This means that you don't need to invoke a TWO_TASK - all you need is an ORACLE_SID.

The Rule: A TWO_TASK is ONLY needed if your server side application is going to (at any time through service guard invocation of the package) be running on either a different server, or a different ORACLE_HOME than the database server itself. That's it.

If, when service guard kicks in - it moves BOTH the application and the database to the same new server, and the two both share the same ORACLE_HOME - then you don't need to use TWO_TASK.

If they insist on doing this even though they share the same server (even if different ORACLE_HOMES) - then please have them check out using bequeath (IPC) protocol and see if their application still works. This will avoid much SQLNet latency and unnecessary overhead.

This is because not all the of the tnsnames.ora files have to be the same, the ones that are local to the machine that is hosting the database can use an IPC connection, while connections from other servers can use regular sqlnet (TPC) connections. Of course, configure the listener with two different ports - one for remote TPC, and one for same machine IPC.

Also, when using service guard, this is easier to manage if the listener.ora and tnsnames.ora files DON'T use server names, instead using the actual assigned virtual IP address of SG from the cluster.

Connection usage for DB SERVER, and SERVER SIDE Application (tier 2 of 3)
-------------------------------------------
I] SAME SERVER-SAME ORACLE_HOME
a)ORACLE_SID (local connection - no SQLNet).
b)TWO_TASK using TPC (full SQLNet connection - slower with extra overhead )
c) TWO_TASK using IPC (sub-part of SQLNet - speed approaching local connection speed, but "some" extra overhead is present over direct connection).

II] SAME SERVER w/ DIFFERENT ORACLE_HOME
two options:
a) TWO_TASK using TPC (full SQLNet connection, slower)
b) TWO_TASK using IPC (sub-part of SQLNet - speed approaching local connection speed)

III] DIFF SERVER -
a) TWO_TASK using TPC (full SQLNet connection)

[TwoProc]

Right Rita, you would want to point to the package with the correct virtual(moving) IP. I'm trying to determine in my mind why the need to convert now to using TWO_TASK, while offering some suggestions and principles as to why/why not.

If the scenario for the package is that EVERYTHING (Oracle db's and application server side code, etc) moves with package to a single standby server - then there really is no need for a new connection method (as what I think you're getting at).

If the scenario becomes one in which a server fails, and one database goes one direction, and another goes another direction, while application server side asset(s) possibly go another - well then I think standardizing with a TWO_TASK is a valid way to help simplify the administration of packaged assets as they move around from place to place. The only downside is that when you are running both the DB and server side application code on the same box, there can be unnecessary overhead. However, this could be avoided with some carefully crafted logic in the package initiation and teardowns, if one were so inclined.

And of course, your input is always appreciated, and you can just step in with suggestions whenever you feel like it. I know that myself and many others in the forums always respect and appreciate your opinions.

[John Eaton]

Sqlplus is just a command ....
Yes I realize this. Thought you were referring to it in a different sense.

TNS_ADMIN is just ...
Again, I am aware.

If there is only one ORACLE_HOME ....
No jdbc, the application is Natural (IBM). Using TWO_TASK when local (db & app on same server) is my primary concern. The application is very heavy on IO. Due to it's origin, and poor coding since then, the application ususally reads way more data than it needs and sorts it out itself, rather than using more highly selective oracle queries. It's getting better, but with bug fixes and new features, little is done to make the older code more robust.

...
What "parts" of the system ...
4 packages:

1) DB and 'batch' application. Always together for performance reasons, but share application binaries and config files with the applications packages. Oracle environment variables included. This is a standard configuration used on 4 other servers with about 10 apps variations talking to 10 other DBs. Used for concurrent development, testing and training.

2, 3, 4) 3 tier apps supporting 3 different aspects of the application (call center, IVR (phone in), interfaces)

But, you just said that there is only one ORACLE_HOME...
As is strongly recommended for SG, application files are not shared, only data. Both servers in the custer have (nearly) identical ORACLE_HOMEs. Only differences are configuration files.

The Rule: A TWO_TASK is ONLY needed ...
Same server or different server. Depends on reasons for failure.

If, when service guard kicks in ...
see previous answer.

If they insist on doing this even though they share the same server (even if different ORACLE_HOMES) - then please have them check out using bequeath (IPC) protocol and see if their application still works. This will avoid much SQLNet latency and unnecessary overhead.
This is a key issue and could be at the heart of what I need.

This is because not all the of the tnsnames.ora files have to be the same...
Another key issue, and concern, TCP vs IPC

Also, when using service guard, ... virtual IP...
We only use the virtual IPs, but by name not number.

Connection usage for DB SERVER, and SERVER SIDE Application (tier 2 of 3)

I] SAME SERVER-SAME ORACLE_HOME
a)ORACLE_SID (local connection - no SQLNet).
current configuration.
Also will be SG default situation, or full failover (server crash)

II] SAME SERVER w/ DIFFERENT ORACLE_HOME
not applicable

III] DIFF SERVER -
a) TWO_TASK using TPC (full SQLNet connection)
Will be in SG partial failover situation

Can't have I] a) AND III] a) at the same time based without 'changing' config files as part of the failover event.

I think the TCP/IPC item is the key to it all, and may make for the deciding method.
One consideration we are looking at is to move the 3 apps packages, if the DB moves. We'd rather not, but because of a 'bug' in the current version, it cannot reconnect to the DB if it goes down. We have to stop and start the 3 apps packages anyway. Only difference would be the node they start on.
I think you have filled in all the blanks for me at this point. Thanks!