Basics of client connectivity in Oracle Data Guard configuration

Oracle Net Services Overview

Oracle Net Services provide enterprise-wide connectivity solutions in distributed, heterogeneous computing environments. It enables a network session from a client application to an Oracle Database server.

A dedicated server process is a type of service handler that the listener starts when it receives a client request. The slide depicts the process of establishing a connection from a client to an Oracle Database (non-shared server architecture) by using the following steps:

The listener receives a client connection request.
The listener starts a dedicated server process.
The listener provides the location of the dedicated server process in a redirect message.
The client connects directly to the dedicated server.

Note: Depending on the operating system and transport protocol, step three may be eliminated. In this case, the dedicated server process inherits the connection request from the listener. The result is the same—a network session established is between the client and the dedicated server process. If the client and database exist on the same computer, then the application initiating the session can spawn a dedicated server process without going through the listener. This is known as the bequeath protocol and is often used when starting a database instance.

Understanding Name Resolution

A naming method is a resolution method used by a client application to resolve a connect identifier to a connect descriptor when attempting to connect to a database service. The following methods are available:

Local naming (uses static text-based configuration files)
Directory naming (uses a dynamic LDAP-compliant server)
Easy Connect naming (uses simple hard-coded strings)
External naming (uses a third-party naming service)

SQL> connect hr@xyz

Resolved into:

SQL> connect hr@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=host03.example.com)(PORT=1521))(CONNECT_DATA=
(SERVICE_NAME=london.example.com)))

A client application needs a connect descriptor to create a network session. The connect descriptor contains two components:

Location of the listener through a protocol address, also known as the listening endpoint
A specific database service name or Oracle system identifier recognized by the listener

The connect descriptor can be explicitly specified as in the second example in the slide, which does not require name resolution. This technique is used by the Data Guard broker when the broker modifies the LOG_ARCHIVE_DEST_n parameters for redo transport. Also, a direct TCP/IP address can be specified in place of the hostname to avoid additional lookups such as using Domain Name Services (DNS).

In most cases though, a user initiates a connection request by providing a connection string that can include a username, password, and a simplified connect identifier. This requires that the simplified connect identifier be resolved into the appropriate details using one of the many name resolution methods that are available. There are advantages and disadvantages to each naming method. The sqlnet.ora file indicates which naming methods are available to the client or server. This course will utilize the local naming method, which stores network service names and their connect descrip g tors in a localized configuration file named tnsnames.ora.

Local Naming Configuration Files

The local naming method uses text-based configuration files stored on both the client and the database server to resolve network service names (connect identifiers) into detailed connect descriptors. Operating-system environment variables determine the location of these files. The TNS_ADMIN variable is checked first. It allows the configuration files to be centrally located (for example, on a cluster file system and shared among many machines).

If the TNS_ADMIN variable is not defined, then the ORACLE_HOME variable is used to locate the configuration files. Several different ORACLE_HOME locations can be present on the same host machine for different reasons. Each may contain network configuration files if desired. For example, the tnsnames.ora and sqlnet.ora file could be found in the ORACLE_HOME location for each of the grid infrastructure sof location for each of the grid infrastructure software database software middleware software , database software, middleware software, and Enterprise Manager software products. The value of the ORACLE_HOME environment variable will point to a single ORACLE_HOME location at a time. The listener.ora configuration file is used only for database software ORACLE_HOME locations.

Note: In a Real Application Cluster environment, the Oracle Database listener usually runs from the ORACLE_HOME of the Grid Infrastructure software and not the ORACLE_HOME of the database software. The ORACLE_HOME of the Grid Infrastructure software will need working configuration if ASM is being deployed. If separation of duties exist between the cluster software administrator and the database administrator, the database listener could run from the ORACLE_HOME of the database software.

Local Naming: tnsnames.ora

Database parameter file (spfileNAME.ora/initNAME.ora)

 LOG_ARCHIVE_DEST_2='SERVICE=london ...'

Oracle Net Configuration (on database host machine): $ORACLE_HOME/network/admin/tnsnames.ora

LONDON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST=host03.example.com)
      (PORT=1521)(SEND_BUF_SIZE=10485760)
      (RECV_BUF_SIZE=10485760))
    )
    (SDU=65535)
    (CONNECT_DATA=(SERVICE_NAME=london.example.com))
  )

Shown above is an example of using the local naming method for a Data Guard environment. Located inside of the database parameter file, the LOG_ARCHIVE_DEST_n parameter with the SERVICE attribute is used to define the remote Oracle database instance to which redo data will be sent. A network service name (shown in the slide with the value LONDON) will be resolved into the connect descriptor using the local naming method. The connect descriptor provides the protocol and address information needed to contact the listener on its listening endpoint. After the listener is contacted, a network session is requested for the specified SERVICE_NAME. There is no requirement that the network service name (LONDON) have the same value or similarly named value as the SERVICE_NAME(london.example.com).

Connect-Time Failover: Planning for Role Reversal

Connect-time failover is turned on by default for multiple address lists (ADDRESS_LIST used) and does not have to be specified.

PRMY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER=on)
      (ADDRESS=(PROTOCOL=TCP)(HOST=host01.example.com)
      (PORT=1521)(SEND_BUF_SIZE=10485760)
      (RECV_BUF_SIZE=10485760))
      (ADDRESS=(PROTOCOL=TCP)(HOST=host03.example.com)
      (PORT=1521)(SEND_BUF_SIZE=10485760)
      (RECV_BUF_SIZE=10485760))
    )
    (SDU=65535)
    (CONNECT_DATA=(SERVICE_NAME=prmy.example.com))
)

Role-reversal operations such as failover and switchover can modify which host the primary or standby database is currently running on. The client configuration should include a connect descriptor that includes all potential host that a particular service can run on. This can be configured by specifying an ADDRESS_LIST with multiple listening endpoints or addresses within it. Connect-time failover instructs Oracle Net to fail over to a different listener if the first listener fails. The use of the ADDRESS_LIST clause turns on connect-time failover by default.

It is not necessary to use the FAILOVER=on statement, as indicated by the bold text in above example. When the application connections are being made, if they should happen to attempt to connect to an old primary host that is unavailable the connection attempt to that host should connect to an old primary host that is unavailable, the connection attempt to that host should last no longer than 3 seconds. This allows for connection attempts to get through the ADDRESS_LIST quickly until a new primary host is found. This can be configured with the following entry placed into the sqlnet.ora file:

SQLNET.OUTBOUND_CONNECT_TIMEOUT=3

Note: The ADDRESS_LIST clause is optional. Multiple addresses can be defined without explicitly using the ADDRESS_LIST syntax. In this case, the failover mode would default to be off and it would explicitly require the FAILOVER=on syntax to enable connect-time failover.

Listener Configuration: listener.ora

Oracle Net Configuration (on database host machine): $ORACLE_HOME/network/admin/listener.ora

The following entry defines two listening endpoints:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS=(PROTOCOL=TCP)(HOST=host03.example.com)
      (PORT=1521)(SEND_SDU=10485760)
      (RECV_SDU = 10485760)))
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)))
  )
ADR_BASE_LISTENER = /u01/app/oracle
LISTENER2 = ...
ADR_BASE_LISTENER2 = /u01/app/oracle

A listener is configured with one or more listening protocol addresses, information about supported services, and parameters that control its runtime behavior. The listener configuration is stored in a configuration file named listener.ora. Because the configuration parameters have default values, it is possible to start and use a listener with no configuration. In an Oracle Data Guard environment, best practices usually indicate a need to adjust the session data unit (SDU) buffer to improve network performance. This is especially important on a wide area network (WAN) that has long delays. Modifying this value will require that the listener.ora file be created with non-default values.

The bold text in above example indicates the name of a listener. This name is used in commands that refer to the listener such as commands that refer to the listener such as lsnrctl start LISTENER2 lsnrctl start LISTENER2. If the default If the default name is used, it is not necessary to identify it in most commands. For example, the command lsnrctl start will start a listener with the default name of LISTENER.

One listener can listen for many databases, or there can exist a single listener for each database. Inside of a single listener.ora file, multiple listener configurations can be created by duplicating the contents of the file and changing the listener name to a different value for each occurrence. In the slide, the blue text shows a second listener named LISTENER2. Each separate listener requires a distinct listening endpoint. This technique can allow the listener to be started and stopped separately for each database on the same host machine.

Dynamic Service Registration

Dynamic service registration is configured in the database initialization file. It does not require any configuration in the listener.ora file. If not specified, the value for the SERVICE_NAMES parameter defaults to the global database name, a name comprised of the DB_UNIQUE_NAME and DB_DOMAIN parameters in the initialization parameter file. If not explicitly defined, the DB_UNIQUE_NAME parameter defaults to the value DB_NAME. The value for the INSTANCE_NAME parameter defaults to the Oracle system identifier (SID). All of these names can be explicitly defined to non-default values.

SERVICE_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. You can specify multiple service names to distinguish among different us names to distinguish among different uses of the same database For example:

SERVICE_NAMES=PROD,DG_PRMY,DG_RW,MAIN_REPORTING

By default, the LREG process registers service information with its local listener on the default local address of TCP/IP, port 1521. To have the LREG process register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

A remote listener is a listener residing on A remote listener is a listener residing on one computer that redirects connections to a computer that redirects connections to a database instance on another computer. You can configure registration to remote listeners using the REMOTE_LISTENER parameter.

Static Listener Entries: listener.ora

Static listener entries are needed for Recovery Manager and Data Guard broker operations.

SID_LIST_LISTENER =
  (SID LIST = (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = london.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = london))
    (SID_DESC =
      (GLOBAL_DBNAME = london_DGMGRL.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = london))
  )

Dynamic service registration allows the listener registration process (LREG) of a database instance to identify its available services to the listener without entries in the listener.ora configuration file. The listener then acts as a port mapper for those services. However, when the database instance is stopped, the listener discards all information for the dynamic services related to that database. Any attempt to establish a network session to the unknown service will usually receive the error message “ORA-12514: Listener does not currently know of service requested in connect descriptor.” Static registration allows the listener to know of a service even if the database instance is not running. This is often important with tools and utilities that try to remotely start and stop a database instance. Configuration of static service information is necessary g in the following cases:

Use of external procedure calls
Use of heterogeneous services
Use of Oracle Data Guard
Remote database startup from a tool other than Oracle Enterprise Manager Cloud Control

To enable DGMGRL to restart instances during the course of broker operations a static To enable DGMGRL to restart instances during the course of broker operations, a static service must be registered with the local listener and assume a static service name of db_unique_name_DGMGRL.db_domain.

Optimizing Oracle Net for Data Guard

To achieve high network throughput, especially for a high-latency, high-bandwidth network, the minimum recommended setting for the sizes of the TCP send and receive socket buffers is the bandwidth-delay product (BDP) of the network link between the primary and standby systems. BDP is the product of the network bandwidth and latency. Tests have shown incremental throughput increases with socket buffer settings up to three times the BDP. Socket buffer sizes are set using the Oracle Net parameters RECV_BUF_SIZE and SEND_BUF_SIZE. For example, if bandwidth is 622 Mbit/s per second and latency is 30 ms, you would calculate the minimum size for the RECV_BUF_SIZE and SEND_BUF_SIZE parameters as follows: 622,000,000 / 8 x 0.030 = 2,332,500 bytes. Then, multiply the BDP 2,332,500 x 3 for a total of 6,997,500. Set the send and receive buffer sizes at either the value you calculated or 10 MB (10485760), whichever is larger.

Oracle Net buffers data into what is called a session data unit (SDU), with a default size of 8192 bytes. These data units are then sent to the network layer when they are either full, flushed, or read by the client. Generally Data Guard sends redo in much larger chunks than 8192 bytes, so this default is insufficient, because you can end up having to send more pieces (chopping up the data) to Oracle Net Services. Increase this to 64 KB.

To preempt delays in buffer flushing in the TCP protocol stack, disable the TCP Nagle algorithm by setting TCP.NODELAY to YES in the sqlnet.ora file on both the primary and standby systems.

The actual value of the SEND_BUF_SIZE and RECV_BUF_SIZE parameters may be less than the value specified because of limitations in the host operating system or due to memory constraints. The default values for these parameters are operating-system specific. The following are the defaults for the Linux operating system:

SEND_BUF_SIZE: 131,072 bytes (128 KB)
RECV_BUF_SIZE: 174,700 bytes

These values are typically modified to be higher when the oracle-rdbms-server-12cR1- preinstall package is installed, but the modified values are still lower than the Data Guard best practice recommended values.

The default and maximum amount for the receive socket memory can be determined with:

# cat /proc/sys/net/core/rmem_default
# cat /proc/sys/net/core/rmem_max

The default and maximum amount for the send socket memory can be determined with:

# cat /proc/sys/net/core/wmem_default
# cat /proc/sys/net/core/wmem_max

Adjust values with the following commands:

# echo 'net.core.wmem_max=10485760' >> /etc/sysctl.conf
# echo 'net.core.rmem max=10485760' >> /etc/sysctl conf

You must also set minimum size, initial size, and maximum size in bytes:

# echo 'net.ipv4.tcp_rmem= 10240 131072 10485760' >> /etc/sysctl.conf
# echo 'net.ipv4.tcp_wmem= 10240 131072 10485760' >> /etc/sysctl.conf

Reload the changes made by using the following command:

# sysctl –p

Note: Starting with Oracle Database 12 Starting with Oracle Database 12c Release 1 (12 1) Oracle Net supports large session Release 1 (12.1), Oracle Net supports large session data unit (SDU) sizes, with a new upper limit of 2 MB. The larger SDU size can be used to achieve better utilization of available network bandwidth in networks that have high bandwidth delay products and host resources, according to application characteristics. At the moment in time of writing this course, current best practices recommend a value of 64 KB. This may change in the future with additional testing.