Oracle database connectivity
Oracle Net is a component that resides on both Client and Database server and establishes the connection between Client and Server and exchanges messages between them using industry standard protocols. It is layered on top of "Oracle protocol support", rules that determine the connectivity protocol and transmission of data over the network.
|----------------| => Application TCP/IP RDBMS => |---------------|
| CLIENT | => Oracle Net <---------> Oracle | Database |
| | Net => | Server |
|----------------| |---------------|
Client/Server application connections
It establishes connections from the traditional Client servers to the Oracle Database servers.
Java Client application connections
The Java Client application makes a call to JDBC OCI driver which translates the call to the Oracle Net layer which then connects to the DB server.
Shared Server Architecture
With shared servers you can scale up the number of connections to the database from an application without any changes in the application.
Also the number of applications connecting to the database can be increased. This is possible through dispatchers. Request for a db connection is not sent directly to the database server process - a db process that handles client requests, but to a dispatcher. The dispatcher put the requests in a common queue. Any idle shared server that is available picks up the request and does the required db connection and processing.
Dedicated Server Architecture
In dedicated server Architecture there is one dedicated server process for each client. This puts a restriction on the number of client connections based on the server configuration and its capacity to handle multiple concurrent connections.
Connection pooling
In many a web applications, when the clients connect to the database, these connections may not be required on a continuous basis. Eg - A request is made from a particular client to the database, the request is processed and the data is returned to the application. It will take some time for the user to process this data and for this time interval this connection will not be used again, till the next request is made to the database. In such cases the connection becomes INACTIVE and it is returned to a connection pool. This connection is now available to other users wanting to make a database request. This saves the overhead of having a large number of db connections for the number of clients and also the overhead of making a new db connection all over again. In this way a few db connections can service a large number of client requests.
Multiplexing
In this, multiple connections can be funnelled through a single Connection Manager thus reducing the resources required for multiple sessions. This enables a greater number of connections to be made to the database. One single Oracle Connection Manager having multiple gateways can handle thousands of concurrent client connections to the database.
Connecting to the server
Connection is made to the database using username/password@connect_descriptor. The connect descriptor is mapped to a service name which is a logical representation of the database. In case of local naming, this mapping is present in tnsnames.ora file which is in ORACLE_HOME/network/admin folder. This file has the ip address,port of the host db server and the protocol used to connect to the database over the network for transfer of data. When a database is created with dbca, this Local Naming file gets created.
On the database host, there is a LISTENER process which manages incoming client connections and directs them to the database server. For this, on the database host there is a listener configuration file. The default name for this is listener.ora.
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.orcl.com) (PORT=1521)))
This entry in the listener.ora file describes the database host name, the port and the protocol used for listening for client requests. In this case it is tcp. The name of the default listener is LISTENER.
A database registers with a listener that is configured within minutes of starting up. If a new service for another database on the same server has to be registered with the listener, a corresponding entry for that service(mentioning the ORACLE_SID and ORACLE_HOME of the new database) is to be made in the listener.ora file and the listener is to be restarted.
New entry to be made to SID_LIST for database orcl2 to be registered with the listener
(SID_DESC =
(SID_NAME = orcl2)
(ORACLE_HOME = /u02/app/oracle/product/10.2.0/db_2)
)
Once changes are done and saved listener can be restarted from command prompt as follows
lsnrctl stop
lsnrctl start
To check if the service for the new database is registered with the listener on the listener prompt type the following
LSNRCTL services
To check the status of the listener type the following on the command prompt
lsnrctl status
Similarly a new listener can also be added and database services can be registered with the new listener.
The tools used for Network Configuration are
Network Configuration Assistant - NETCA
Oracle Enterprise Manager - OEM
Oracle Net Manager
Oracle Network Security
An important factor to be considered when connecting to the database across networks is Network Security. Access to the database can be controlled using firewall access control. This can be done using Oracle Connection Manager. This can be configured to grant or deny access to a particular client to the database. Filtering rules can be set to allow clients with specific ip addresses/hostname to connect to specific db servers.
Quote for the day “We Generate Fears While We Sit. We Overcome Them By Action.”- Dr. Henry Link
Oracle Net is a component that resides on both Client and Database server and establishes the connection between Client and Server and exchanges messages between them using industry standard protocols. It is layered on top of "Oracle protocol support", rules that determine the connectivity protocol and transmission of data over the network.
|----------------| => Application TCP/IP RDBMS => |---------------|
| CLIENT | => Oracle Net <---------> Oracle | Database |
| | Net => | Server |
|----------------| |---------------|
Client/Server application connections
It establishes connections from the traditional Client servers to the Oracle Database servers.
Java Client application connections
The Java Client application makes a call to JDBC OCI driver which translates the call to the Oracle Net layer which then connects to the DB server.
Shared Server Architecture
With shared servers you can scale up the number of connections to the database from an application without any changes in the application.
Also the number of applications connecting to the database can be increased. This is possible through dispatchers. Request for a db connection is not sent directly to the database server process - a db process that handles client requests, but to a dispatcher. The dispatcher put the requests in a common queue. Any idle shared server that is available picks up the request and does the required db connection and processing.
Dedicated Server Architecture
In dedicated server Architecture there is one dedicated server process for each client. This puts a restriction on the number of client connections based on the server configuration and its capacity to handle multiple concurrent connections.
Connection pooling
In many a web applications, when the clients connect to the database, these connections may not be required on a continuous basis. Eg - A request is made from a particular client to the database, the request is processed and the data is returned to the application. It will take some time for the user to process this data and for this time interval this connection will not be used again, till the next request is made to the database. In such cases the connection becomes INACTIVE and it is returned to a connection pool. This connection is now available to other users wanting to make a database request. This saves the overhead of having a large number of db connections for the number of clients and also the overhead of making a new db connection all over again. In this way a few db connections can service a large number of client requests.
Multiplexing
In this, multiple connections can be funnelled through a single Connection Manager thus reducing the resources required for multiple sessions. This enables a greater number of connections to be made to the database. One single Oracle Connection Manager having multiple gateways can handle thousands of concurrent client connections to the database.
Connecting to the server
Connection is made to the database using username/password@connect_descriptor. The connect descriptor is mapped to a service name which is a logical representation of the database. In case of local naming, this mapping is present in tnsnames.ora file which is in ORACLE_HOME/network/admin folder. This file has the ip address,port of the host db server and the protocol used to connect to the database over the network for transfer of data. When a database is created with dbca, this Local Naming file gets created.
On the database host, there is a LISTENER process which manages incoming client connections and directs them to the database server. For this, on the database host there is a listener configuration file. The default name for this is listener.ora.
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.orcl.com) (PORT=1521)))
This entry in the listener.ora file describes the database host name, the port and the protocol used for listening for client requests. In this case it is tcp. The name of the default listener is LISTENER.
A database registers with a listener that is configured within minutes of starting up. If a new service for another database on the same server has to be registered with the listener, a corresponding entry for that service(mentioning the ORACLE_SID and ORACLE_HOME of the new database) is to be made in the listener.ora file and the listener is to be restarted.
New entry to be made to SID_LIST for database orcl2 to be registered with the listener
(SID_DESC =
(SID_NAME = orcl2)
(ORACLE_HOME = /u02/app/oracle/product/10.2.0/db_2)
)
Once changes are done and saved listener can be restarted from command prompt as follows
lsnrctl stop
lsnrctl start
To check if the service for the new database is registered with the listener on the listener prompt type the following
LSNRCTL services
To check the status of the listener type the following on the command prompt
lsnrctl status
Similarly a new listener can also be added and database services can be registered with the new listener.
The tools used for Network Configuration are
Network Configuration Assistant - NETCA
Oracle Enterprise Manager - OEM
Oracle Net Manager
Oracle Network Security
An important factor to be considered when connecting to the database across networks is Network Security. Access to the database can be controlled using firewall access control. This can be done using Oracle Connection Manager. This can be configured to grant or deny access to a particular client to the database. Filtering rules can be set to allow clients with specific ip addresses/hostname to connect to specific db servers.
Quote for the day “We Generate Fears While We Sit. We Overcome Them By Action.”- Dr. Henry Link
No comments:
Post a Comment