Shell Programming and Scripting

Hi everybody,

My bash script is trying to connect to a remote database using the 'sqlplus' binary.For this i set(export) the variable TWO_TASK to the value of database name excluding the dot extension part.

Can anyone explain me what's the significance of this TWO_TASK variable while connecting to a remote database and how the flow is happening?

Your help is appreciated

With Regards
Dileep Pattayath

It's very simple: the variable TWO_TASK defines the connect string used by sqlplus for connecting to a remote db.

For example, if you issue:

Code:

$ORACLE_HOME/bin/sqlplus scott/tiger@remote_db

will be the same as:

Code:

export TWO_TASK=remote_db
$ORACLE_HOME/bin/sqlplus scott/tiger

So if i entered the command

Code:

$ORACLE_HOME/bin/sqlplus scott/tiger

the value of TWO_TASK is identified and it will match the same in the tnsentry(tnsnames.oa file)and fetches the host name and port and then establish a connection to it.Correct me if am wrong.
My intention is to understand the flow of establishing the db connection.

Yes it's right. The connect string's part after the "@" character (or the evaluated TWO_TASK variable) must always be resolved into 4 pieces of information: an IP address and port number of a remote listener, the network protocol and the instance name you wish to connect to. If you use local naming resolution (tnsnames.ora on the client side), then the alias you specify has all the informations oracle needs for contacting the remote site and asking the listener to spawn a new server process (or redirect your request to a dispatcher if you're using mts) and initiate a session for serving your request.

Ok.fine i understood,but i am confused at the line in red in your reply.What is mts?Is it any method of connecting to a remote database without using tnsnames.ora file?

MTS are Multi-Threaded Servers, also called Shared Servers. It's an option you can configure on the db server (only if you really need it).

Usually, if MTS are not configured, your client contacts the remote listener, which spawns a Dedicated Server Process on an assigned port and puts in communication your user process with the server process. At this point the listener has finished its work and your client is in direct contact with a dedicated process (oracle<$SID>) on a dedicated port which serves only your session.

If you configure MTS on the server-side, instead, new processes are involved in the connection mechanism. First, at the instance startup, are spawned one or more Dispatcher Processes (ora_dNNN) and one or more Shared Server Processes (ora_sNNN). In this case, when your client asks the remote listener for establishing a session, the listener will not spawn any new process: it will simply tell your user process a port number on which a pre-started dispatcher process is listening. Your user process will then send the sql statements to the dispatcher, and the dispatcher puts the request in a common input queue. All the shared server processes are continuosly monitoring the common queue and pick up requests for executing the work. When the work is done, the result will be putted in the response queue of the dispatcher which made the request and the dispatcher fetches the result back to the user process.

MTS are used when the number of connections made to the db is so high that spawning a dedicated server process for each session will reduce the server performance due to the continuos context switches the OS has to do for mantaining all the processes active.

great!!I have some more queries related to this.In some of the scripts i saw some settings other than what we discussed till now as given below.Can you please give the instance when these was used and why?

export ORAENV_ASK=NO/YES
export ORACLE_SID="INRDELP1" (Oracle instance name)
export GLOBAL_NAME="INDBR432_INRDELP1.world" (Oracle database name with suffix)