DBA chapter 12

18.Which network architecture connection uses session memory from the SGA? A. Dedicated Server B. Shared Server C. Database Resident Connection Pooling D. All of the above

. B. Dedicated server uses session memory from PGA. A pooled server connection is similar to dedicated server once connected; therefore, the memory is coming from PGA as well. For a shared server, the server process is shared; therefore, the request and response information must be kept in the SGA.

Which statements regarding Database Resident Connection Pooling (DRCP) are true? (Choose two.) A. When a DRCP pooled server connection is made by an application, it is equivalent to a dedicated server connection. B. When a database has DRCP enabled, all connections default to a pooled connection unless DEDICATED server is explicitly specified in the connect string. C. When an application using the DRCP connection disconnects, the server connection (process) is handed off to the broker. D. When using the Oracle Net Easy Connect method, it is not possible to utilize DRCP.

A, C. Even if DRCP is enabled in the database, the client or application connection must always request a pooled connection using SERVER=POOLED in connect syntax. To use Easy Connect and DRCP, the syntax is host:port/service_name:POOLED.

9.A client wants to connect to the database service dbprod.com located on the dbprod.com server through a nondefault port (1522) using Oracle Easy Connect. Which of the following connect strings are the choices for the client to use? (Choose two.) A. CONNECT scott/[email protected]:1522 B. CONNECT scott/tiger@1522:dbprod.com/dbprod.com C. CONNECT scott/tiger@//dbprod.com/1522:dbprod.com D. CONNECT scott/[email protected]:1522/dbprod.com

A, D. The correct syntax to use with the Oracle Easy Connect method when you are connecting to a non-URL location is connect username/password@host:port/service_name. If the service name and the host are identical, you do not have to include the service name. If the port is any port other than the default port of 1521, it must be specified. Because you want to connect to a nondefault port where the database name and the hostname are the same, the best answer is A, but D is also correct.

12. The administrator wants to allow a user to connect via a dedicated connection into a database configured in Shared Server mode. Which of the following options accomplishes this? A. (SERVER=DEDICATED) B. (CONNECT=DEDICATED) C. (INSTANCE=DEDICATED) D. (MULTITHREADED=FALSE) E. None of the above

A. A user must explicitly request a dedicated connection when a server is configured in Shared Server mode. Otherwise, the user gets a Shared Server connection. The correct parameter is (SERVER=DEDICATED).

1. Which of the following files must be present on the Oracle server to start a nondefault Oracle listener? A. listener.ora B. lsnrctl.ora C. sqlnet.ora D. tnsnames.ora

A. The listener is the process that manages incoming connection requests. The listener.ora file is used to configure the listener and must be configured to start a nondefault listener. The sqlnet.ora file is an optional client- and server-side file. The tnsnames.ora file is used for doing local naming resolution. There is no such file as lsnrctl.ora. You do not need the listener.ora file to start a default listener on port 1521.

static service registration

After you create the listener with the name, protocol, and listening location information, you can define the network services to which the listener is responsible for connecting.

7. A client receives the following error message: "ORA-12154 TNS:could not resolve the connect identifier specified" Which of the following could be possible causes of the error? Choose all that apply. A. The listener is not running on the Oracle server. B. The user entered an invalid net service name. C. The user supplied the correct net service name, but the net service name is misspelled in the tnsnames.ora file on the client file. D. The listener is not configured to listen for this service.

B, C. Supplying a net service name that is not contained in the tnsnames.ora file can cause this error. Problems with the tnsnames.ora file can cause this error, too. Listener problems will not cause this error.

2.Which of the following is the correct way to start a listener called LISTENER? A. lsnrctl startup listener B. lsnrctl start C. netca start D. netmgr start listener

B. Because the default listener name is LISTENER, simply enter lsnrctl start. The name LISTENER is assumed to be the listener to start in this case.

19.Communication between two Oracle databases is configured using: A. Database Resident Connection Pooling B. Database link C. Connection Manager D. Oracle Net Manager

B. Database links facilitate communication between databases. Using a database link access syntax (@dblink_name), data from a remote database is available locally.

5.Which is a requirement for using host naming? A. You must use tnsnames.ora on the client. B. You must be using TCP/IP. C. You must have an OID present. D. You must have a sqlnet.ora file present on the client. E. None of the above.

B. Host naming is typically used in small installations that have few Oracle databases. This is an attractive option when you want to minimize client-side configuration. TCP/IP is a requirement when you use host naming.

20.DBA user MIKE ran the SQL statement CREATE DATABASE LINK SCOTT.SALES_LINK.BJS.COM CONNECT TO SALES_INT IDENTIFIED BY SALESPWD1 USING 'ocasvr:1522/ocadb'. Choose the option that is true. A. Use of 'ocasvr:1522/ocadb' is invalid in database link definition. B. A private database link is created under user MIKE. C. A private database link is created under user SCOTT. D. If the SALES_INT user does not exist or its password is not SALESPWD1, the database link will not be created.

B. Private database links are created under the user who executes the CREATE DATABASE LINK statement. You cannot create a database link under another schema. A link by the name SCOTT.SALES_LINK.BJS.COM is created under schema MIKE. You can use the Easy Connect syntax or the Oracle Net connect syntax, or you can resolve the alias name by using tnsnames.ora in the USING clause. During database link creation, a connection is not made to the target database to validate the connection or credentials.

10.All of the following are reasons to configure the server using Shared Server except which one? A. Overall memory utilization is reduced. B. The system is predominantly used for decision support with large result sets returned. C. The system is predominantly used for small transactions with many users. D. The number of idle connections on the server is reduced.

B. Shared Server is a scalability option of Oracle. It provides a way to increase the number of supported user processes while reducing the overall memory usage. This configuration is well suited to high-volume, small-transaction-oriented systems with many users connected. Because users share processes, the number of overall idle processes is also reduced. It is not well suited for large data-retrieval-type applications such as decision support.

14.What is the first step that the dispatcher performs after it receives a request from the user? A. Pass the request to a shared server. B. Place the request in a request queue in the PGA. C. Place the request in a request queue in the SGA. D. Process the request.

C. Once a dispatcher receives a request from the user process, it places the request on the request queue. Remember that in a Shared Server environment, a request can be handled by a shared server process. This is made possible by placing the request and user information in the SGA.

16. Which of the following is false about request queues? A. They reside in the SGA. B. They are shared by all the dispatchers. C. Each dispatcher has its own request queue. D. The shared server processes remove requests from the request queue.

C. Request queues reside in the SGA, and there is one request queue per instance. This is where shared server processes pick up requests that are made by users. Dispatchers have their own response queues, but they share a single request queue

. What is the process that notifies the listener after a database connection is established? A. SMON B. PMON C. LREG D. LGWR

C. The LREG process notifies the listener after a client connection is established. This is so that the listener can keep track of the number of connections being serviced by each dispatcher. In versions prior to Oracle Database 12c, the functions of LREG were performed by PMON.

13.In which of the following files would you find the Shared Server configuration parameters? A. listener.ora B. mts.ora C. init.ora D. tnsnames.ora E. sqlnet.ora

C. The Shared Server configuration parameters exist in the init.ora or the SPFILE file on the Oracle Server machine.

3.When dynamic service registration is used, you will not see the service listed in which of the following files where it would normally be located? A. sqlnet.ora B. tnsnames.ora C. listener.ora D. None of the above

C. When services are dynamically registered with the listener, their information is not present in the listener.ora file.

15.When configured in Shared Server mode, which of the following is contained in the PGA? A. Cursor state B. Sort information C. User session data D. Stack space E. None of the above

D. A small PGA is maintained even though most of the user-specific information is moved to the SGA (specifically called the UGA in the shared pool or the large pool). The only information left in the reduced PGA is stack space.

4.Connection Manager provides which of the following? A. Multiplexing B. Cross-protocol connectivity C. Network access control D. All of the above

D. Connection Manager is a middleware solution that provides for the multiplexing of connections, cross-protocol connectivity, and network access control. All the answers describe Connection Manager.

8.What portion of the tnsnames.ora file specifies the name or IP address of the server where the listener process is listening? A. CONNECT_DATA B. SERVER C. SERVICE_NAME D. HOST

D. The HOST portion specifies the name of the server to contact. CONNECT_DATA specifies the database service to connect to. The PORT portion specifies the location where the listener is listening on the HOST. Option C, SERVICE_NAME, is the name of the actual database service.

11.Which of the following is true about Shared Server? A. Dedicated connections cannot be made when Shared Server is configured. B. It is recommended that DSS type batch jobs be performed when connected via Shared Server. C. The database can be started when connected via Shared Server. D. The database cannot be stopped when connected via Shared Server.

D. Users can still request dedicated connections in a Shared Server configuration. Bequeathed and dedicated connections are one and the same. The database cannot be stopped or started by the DBA when connected over a Shared Server connection.

Firewall support

IP-Filtering Firewalls Proxy-Based Firewalls

Proxy-Based Firewalls

Proxy-based firewalls prevent information from outside the firewall from flowing directly into the corporate network. The firewall acts as a gatekeeper, inspecting packets and sending only the appropriate information to the corporate network. This prevents any direct communication between clients outside the firewall and applications inside the firewall. Check Point Software Technologies and Cisco are examples of vendors that market proxy-based firewalls.

start listener1

To start the default listener named LISTENER, type start at the prompt. To start a different listener, type start and then that listener name. For example, typing start listener1 starts the LISTENER1 listener.

Transparent Data Encryption (TDE)

__ helps to encrypt data so that only authorized users are able to read it. __ allows applications to continue working seamlessly as before, and does not require any application modification. It automatically encrypts data when it is written to disk, and then automatically decrypts the data when applications access it.

lsnrctl

a command-line interface,, to administer the listener.

dynamic service registration.

allows an Oracle instance to automatically register itself with an Oracle listener. The benefit of this feature is that it does not require you to perform any updates of server-side network files when new Oracle instances are created.

The two-tier configuration

also referred to as the client/server architecture, and more recently the n-tier architecture has been introduced. clients connect to servers over a network using a network protocol,

Dedicated Connections: Direct Handoff Method

are possible when the client and database exist on the same server. The client contacts the Oracle listener after resolving the service name. The listener starts a dedicated process, and the client connection inherits the dedicated-server-process network-connect end point from the listener. The client now has an established connection to the dedicated server process. Larger View

Single-tier is the simplest type

characterized by the use of terminals for serial connections to the Oracle server. mainframe-type applications.

IP-Filtering Firewalls

firewalls monitor the network packet traffic on IP networks and filter out packets that either originated or did not originate from specific groups of machines. The information contained in the IP packet header is interrogated to obtain this information. Vendors of this type of firewall include Network Associates and Axent Communications.

Oracle listener

he main server-side Oracle networking component that allows connections to be established between client computers and an Oracle database. You can think of the listener as a big ear that listens for connection requests to Oracle services.

Dedicated Connections: Redirect Method

occur in a dedicated server environment when the client exists on a machine that is separate from the listener and database server. The listener must inform the client of the address of the spawned process in order for the process to contact the newly created dedicated server process. The following steps, which show the connection process for redirect connections in a dedicated server environment, are illustrated in Figure 12.7: The client contacts the Oracle listener after resolving the service name. The listener starts a dedicated process. The listener sends an acknowledgment back to the client with the address of the dedicated-server-connect end point on the database server to which the client will connect. The client establishes a connection to the dedicated-server-connect end point.

listener.ora

one__ is configured per machine; there may be numerous listeners on a server, and this file contains all the configuration information for every listener configured on the server

Oracle Shared Server: Redirect Method

redirect the user to a server process or a dispatcher process when using Oracle Shared Server. This type of connection can occur when the operating system does not directly support direct handoff connections or the listener is not on the same physical machine as the Oracle server. The following steps are illustrated in Figure 12.9: The client contacts the Oracle server after resolving the service name. The listener sends information to the client, redirecting the client to the dispatcher port. The original network connection between the listener and the client is disconnected. The client then sends a connect signal to the server or dispatcher process to establish a network connection. The dispatcher or server process sends an acknowledgment to the client. LREG sends information to the listener about the number of connections being serviced by the dispatchers. The listener uses this information to maintain consistent loads between the dispatchers. Larger View Figure 12.9: Oracle Shared Server: redirect connection method Database Resident Connection Pooling For DRCP, the connection request typically originates from the application server. The following steps are illustrated in Figure 12.10. The client (or middle-tier application) contacts the Oracle server after resolving the service name. The listener sends information to the client, redirecting the client to the DRCP connection broker. When an application wants to communicate with the database, the pooled-server process connection to the connection broker is handed off to the client. The application then sends a connect signal to the server process to establish a network connection. The server process sends an acknowledgment to the application (client). At this point, the connection behaves like a dedicated session. After the request is finished, the server process is released into the pool. The connection from the client is restored to the broker. Larger View Figure 12.10: Database Resident Connection Pooling Managing Oracle Listeners You can configure the server-side listener files in a number of ways. As part of the initial Oracle installation process, the installer prompts you to create a default listener (see Figure 9-15 in Chapter 9, "Creating and Operating Oracle Database 12c"). This creates a basic listener configuration file (listener.ora). You can administer and manage the Oracle network file configurations by using Oracle Net Manager, Oracle Enterprise Manager Cloud Control, the server control utility srvctl, or the command-line facility lsnrctl. In the next few sections, you will learn to configure and manage the listener and service name files using these tools. Managing Listeners with Oracle Net Manager Oracle Net Manager is a tool you can use to create and manage most client- and server-side configuration files. Oracle Net Manager has evolved from the Oracle 7 tool, Network Manager, to the latest Oracle Database 12c version. Throughout this evolution, Oracle has continued to enhance the functionality and usability of the tool. If you are using a Windows environment, you can start Oracle Net Manager by choosing Start ⇒ Programs ⇒Your Oracle 12c Programs choice⇒ Configuration and Migration Tools ⇒ Net Manager. In a Unix environment, you can start it by running netmgr from your $ORACLE_HOME/bin directory. Figure 12.11 shows an example of the Oracle Net Manager opening screen. Note A simplistic version of Net Manager is available as a tool named NetCA (Network Configuration Assistant). This can be invoked on Windows by using the Net Configuration Assistant program or on Unix by running $ORACLE_HOME/bin/netca. You can add, modify, and delete simple listener configurations using this tool. Because this tool has only a subset of features offered in Net Manager, NetCA is not discussed in detail here. However, we do encourage you to invoke and explore the tool. Larger View Figure 12.11: The Oracle Net Manager opening screen Configuring Listener Services Using Oracle Net Manager Oracle Net Manager provides an easy-to-use graphical interface for configuring most of the network files you will be using. By using Oracle Net Manager, you can ensure that the files are created in a consistent format, which will reduce the potential for connection problems. When you first start Oracle Net Manager, the opening screen displays a tree structure with a top level called Oracle Net Configuration. If you click the plus (+) sign next to this icon, you will see the Local folder. The choices under the Local folder relate to different network configuration files. Here are the network file choices and what each configures: Icon File Configured Profile sqlnet.ora Service Naming tnsnames.ora Listeners listener.ora Open table as spreadsheet Creating the Listener Earlier we said that, by default, Oracle creates a listener called LISTENER when it is initially installed. The default settings that Oracle uses for the listener.ora file are as follows: Section of the File Setting Listener name LISTENER Port 1521 Protocols TCP/IP and IPC Hostname Default Host Name SID name Default Instance Open table as spreadsheet You can use Oracle Net Manager to create a nondefault listener or change the definition of existing listeners. Oracle Net Manager has a wizard interface for creating most of the basic network elements, such as the listener.ora and tnsnames.ora files. Follow these steps to create the listener: Click the plus (+) sign next to the Local icon. Click the Listeners folder. Click the plus sign icon, or choose Edit ⇒ Create to open the Choose Listener Name dialog box. Oracle Net Manager defaults to LISTENER or to LISTENER1 if the default listener is already created. Click OK if this is correct, or enter a new name and then click OK to open the Listening Locations screen, as shown in Figure 12.12. Larger View Figure 12.12: The Listening Locations screen To configure the listening locations, click the Listening Locations drop-down list, and make your selection. Then click the Add Address button at the bottom of the screen to open a new window. The prompts on this screen depend on your protocol. By default, TCP/IP information is displayed. If you are using TCP/IP, the Host and Port fields are filled in for you. The host is the name of the machine in which the listener is running, and the port is the listening location for TCP/IP connections. The default value for the port is 1521. To save your information, choose File ⇒ Save Network Configuration, and then look in the directory where the file was saved. You can also add listeners by following these steps. Listeners must have unique names and listen on separate ports, so assign the listener a new name and a new port (1522, for example). You also must assign service names to the listener. You'll see how to add service information in the next section. Oracle Net Manager actually creates three files during this process: listener.ora, tnsnames.ora, and sqlnet.ora. The tnsnames.ora file does not contain any information. The sqlnet.ora file may contain a few entries at this point, but you can ignore them for the time being. The listener.ora file contains information, as shown in the following code: Larger View# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = btlnx63)(PORT = 1522)) ) ADR_BASE_LISTENER1 = /u01/app/oracle LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = btlnx63)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle Tip To figure out where the files are stored, just look at the top banner of the Oracle Net Manager screen. Adding Service-Name Information to the Listener After you create the listener with the name, protocol, and listening location information, you can define the network services to which the listener is responsible for connecting. This is called static service registration, because Oracle is not automatically registering the service with the listener. In releases of Oracle prior to Oracle8i, static service registration was the only method to associate services with a listener. A listener can listen to an unlimited number of network service names. Follow these steps to add the service name information: To select the listener to configure, click the Listeners icon, and highlight the name of the listener that you want to configure. From the drop-down list at the top right of the screen, select Database Services. Click the Add Database button at the bottom of the screen. This opens the window that allows you to add the database (see Figure 12.13). Larger View Figure 12.13: The Database Services screen Enter values in the Global Database Name, Oracle Home Directory, and SID fields. The entries for SID and Global Database Name are the same if you are using a flat naming convention. Choose File ⇒ Save to save your configuration. Here is an example of the completed listener.ora file: Larger View# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = btlnx63)(PORT = 1522)) ) SID_LIST_LISTENER1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = OCPSAMPLE) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = C12DB1) ) ) ADR_BASE_LISTENER1 = /u01/app/oracle Table 12.1 describes each of the listener.ora parameters for the Listening Location section of the listener.ora file. Table 12.1: Parameters for the Listening Location Section of listener.ora Open table as spreadsheet Parameter Description Listener_Name Indicates the starting point of a listener definition. This is actually the name of the listener being defined. The default name is LISTENER. DESCRIPTION Describes each of the listening locations. ADDRESS Contains address information about the locations where the listener is listening. PROTOCOL Designates the protocol for this listening location. HOST Holds the name of the machine on which the listener resides. PORT Contains the address on which the listener is listening. SID_LIST_Listener Defines the list of Oracle services for which the listener (named LISTENER) is configured. SID_DESC Describes each Oracle SID. GLOBAL_DBNAME Identifies the global database name. This entry should match the SERVICE_NAMES entry in the init.ora file for the Oracle service. ORACLE_HOME The Oracle Home location on the server. SID_NAME Oracle SID name for the Oracle instance. Optional listener.ora Parameters You can set optional parameters that add functionality to the listener. To do so, select a parameter from the General Parameters drop-down list at the top right of the screen. Table 12.2 describes these parameters and where you can find them in Oracle Net Manager. Understanding Service Registration Oracle Database 12c allows two types of service registration. Static service registration occurs when entries are added to the listener.ora file manually by using one of the Oracle tools. It is static because you are adding this information manually. Static service registration is necessary if you will be connecting to pre-Oracle8i instances using Oracle Enterprise Manager or if you will be connecting to external services. Another way to manage listeners that does not require manual updating of service information in the listener.ora file is called dynamic service registration. Dynamic service registration allows an Oracle instance to automatically register itself with an Oracle listener. The benefit of this feature is that it does not require you to perform any updates of server-side network files when new Oracle instances are created. Dynamic service registration will be covered in more detail later in this chapter in the section "Dynamically Registering Services." Table 12.2: Optional listener.ora Parameter Definitions Open table as spreadsheet Net Manager Prompt listener.ora Parameter Description Startup Wait Time STARTUP_WAIT_TIME Defines how long a listener will wait before it responds to a STATUS command in the lsnrctl command-line utility. (Not available in Net Manager) INBOUND_CONNECT_TIMEOUT Defines how long a listener will wait for a valid response from a client once a session is initiated. The default is 10 seconds. Save Configuration On Shutdown SAVE_CONFIG_ON_STOP Specifies whether modifications made during an lsnrctl session should be saved when exiting. Enable ADR ADR_BASE Specify if log and trace files are written to Automatic Diagnostic Repository location. If checked, cannot specify the location for trace and log location. Log File LOG_FILE. Will not be in the listener.ora file if the default setting is used. By default, listener logging is enabled with the log created in the default location. Specifies where a listener will write log information. This is ON by default and defaults to $ORACLE_HOME/network/log/listener.log. Trace Level TRACE_LEVEL. Not present if tracing is disabled. The default is OFF. Sets the level of detail if listener connections are being traced. Valid values include Off, User, Support, and Admin. Trace File TRACE_FILE Specifies the location of listener trace information. Defaults to $ORACLE_HOME/network/trace/listener.trc. Require A Password For Listener Operations PASSWORDS Specifies password required to perform administrative tasks in the lsnrctl command-line utility. As you will see, you cannot add some parameters directly from the Oracle Net Manager and must do so manually. These optional parameters also have the listener name appended to them so that you can identify the listener definition to which they belong. For example, if the parameter STARTUP_WAIT_TIME is set for the default listener, the parameter created is STARTUP_WAIT_TIME_LISTENER. Note Oracle EM Database Express cannot be used to manage a listener or services, but Oracle EM Cloud control can be used. You can add, stop, start, and configure the listener. Managing Listeners with lsnrctl You can also use a command-line interface, lsnrctl, to administer the listener. This tool gives you full configuration and administration capabilities. If you have been using Oracle, this tool should be familiar. This command-line interface has been around since the early releases of the Oracle product. Other Oracle network components, such as Connection Manager, also have command-line tools that are used to administer their associated processes. Tip In Windows, the listener runs as a service. Services are programs that run in the background in Windows. You can start the listener from the Windows Services panel. Choose Start ⇒ Settings ⇒ Control Panel ⇒ Services. Then select the name of the listener service from the list of services. If the name of your listener is LISTENER, for example, look for an entry such as OracleOraDB12Home1TNSListenerO12C01_LISTENER. Select the listener name, and click Start. To invoke the command-line utility, type lsnrctl at the command line. The following code shows a resulting login screen: $ lsnrctl LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 28-NOV-2013 15:35:53 Copyright (c) 1991, 2013, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> The listener has commands to perform various functions. You can type help at the LSNRCTL> prompt to display a list of these commands. The SET command is used to set configuration parameters, and the SHOW command displays the current value. LSNRCTL> help The following operations are available An asterisk (*) denotes a modifier or extended command: start stop status services version reload save_config trace spawn quit exit set* show* LSNRCTL> help show The following operations are available after show An asterisk (*) denotes a modifier or extended command: rawmode displaymode rules trc_file trc_directory trc_level log_file log_directory log_status current_listener inbound_connect_timeout startup_waittime snmp_visible save_config_on_stop dynamic_registration enable_global_dynamic_endpoint oracle_home pid connection_rate_limit valid_node_checking_registration registration_invited_nodes registration_excluded_nodes remote_registration_address LSNRCTL> help set The following operations are available after set An asterisk (*) denotes a modifier or extended command: rawmode displaymode trc_file trc_directory trc_level log_file log_directory log_status current_listener inbound_connect_timeout startup_waittime save_config_on_stop dynamic_registration enable_global_dynamic_endpoint connection_rate_limit valid_node_checking_registration registration_invited_nodes registration_excluded_nodes LSNRCTL> You can perform a variety of functions from within the lsnrctl utility. Let's take a look at the most common functions you'll perform on the listener using this utility. Starting the Listener To start the default listener named LISTENER, type start at the prompt. To start a different listener, type start and then that listener name. For example, typing start listener1 starts the LISTENER1 listener. The following code shows the results of starting the default listener: Larger ViewLSNRCTL> start listener1 Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.1.0 - Production System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/12.1.0/dbhome_1/network/log/listener1.log Trace information written to /u01/app/oracle/product/12.1.0/dbhome_1/network/trace/listener1.trc Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=btlnx63)(PORT=1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=btlnx63)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias listener1 Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 28-NOV-2013 15:39:03 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level support Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/12.1.0/dbhome_1/network/log/listener1.log Listener Trace File /u01/app/oracle/product/12.1.0/dbhome_1/network/trace/listener1.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=btlnx63)(PORT=1522))) Services Summary... Service "OCPSAMPLE" has 1 instance(s). Instance "C12DB1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully LSNRCTL> This listing shows a summary of information, including the services that the listener is listening for, the log locations, and whether tracing is enabled for the listener. Reloading the Listener If the listener is running and modifications are made to the listener.ora file manually, with Oracle Net Manager or with Enterprise Manager, you must reload the listener to refresh the listener with the most current information. The reload command rereads the listener.ora file for the new definitions. As you can see, it is not necessary to stop and start the listener to reload it. Although stopping and restarting the listener can also accomplish a reload, using the reload command is better because the listener is not actually stopped, which makes this process more efficient. The following code shows an example of the reload command: Larger ViewLSNRCTL> reload listener1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=btlnx63)(PORT=1522))) The command completed successfully LSNRCTL> Note Reloading the listener has no effect on clients connected to the Oracle server. In the previous code example, Oracle reread the listener.ora file and applied any changes you made to the file against the currently running listener process. You can see the address, protocol, and port designation of the default listener. Notice that this listener is listening on port 1522. Security best practices advice you not run the listener on the default known ports. Showing the Status of the Listener You can display the status of the listener by using the status command. The status command shows whether the listener is active, the locations of the logs and trace files, how long the listener has been running, and the services for the listener. This is a quick way to verify that the listener is up and running with no problems. The following code shows the result of the lsnrctlstatus command. Note that no listener name is specified; therefore, the status of default listener listener is shown: Larger ViewLSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=btlnx63)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 28-NOV-2013 12:36:14 Uptime 0 days 3 hr. 5 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/btlnx63/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=btlnx63)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=btlnx63)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/C12DB1/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "C12DB1" has 1 instance(s). Instance "C12DB1", status READY, has 1 handler(s) for this service... Service "C12DB1XDB" has 1 instance(s). Instance "C12DB1", status READY, has 1 handler(s) for this service... Service "bt1pdb" has 1 instance(s). Instance "C12DB1", status READY, has 1 handler(s) for this service... Service "c12pdb1" has 1 instance(s). Instance "C12DB1", status READY, has 1 handler(s) for this service... The command completed successfully LSNRCTL> This code example depicts a listener that has recently been started. You also see what the log file and parameter file locations are for the listener. This is a good facility to use to get a quick listing of vital information for the listener. Tip Use lsnrctl status to see how long the listener was up. Look for Uptime. Listing the Services for the Listener The lsnrctlservices command displays information about the services, such as whether the services have any dedicated, prespawned server processes or dispatched processes associated with them, and how many connections have been accepted and rejected per service. Use this method to check whether a listener is listening for a particular service. The following code shows an example of running the services command: Larger ViewLSNRCTL> services listener1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=btlnx63)(PORT=1522))) Services Summary... Service "OCPSAMPLE" has 1 instance(s). Instance "C12DB1", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER The command completed successfully LSNRCTL> In this example, you can see that the listener is listening for connections to the service OCPSAMPLE. The line "DEDICATED" established:0 refused:0 shows you how many connections to this service have been accepted or rejected by the listener. One reason why a listener may reject servicing a request is if the database is not available. Other Commands in lsnrctl You can run other commands in lsnrctl. Table 12.3 summarizes these other commands. Type the command at the LSNRCTL> prompt to execute it. Table 12.3: A Summary of the lsnrctl Commands Open table as spreadsheet Command Definition change_password Allows a user to change the password needed to stop the listener. exit Exits the lsnrctl utility. quit Performs the same function as exit. save_config Copies the listener.ora file called listener.bak when changes are made to the listener.ora file from lsnrctl. services listener Lists a summary of services and details information about the number of connections established and the number of connections refused for each protocol service handler. start listener Starts the named listener. status listener Shows the status of the named listener. stop listener Stops the named listener. trace Turns on tracing for the listener. version Displays the version of the Oracle Net software and protocol adapters. Using the set Commands in lsnrctl The lsnrctl utility also has commands called set commands. To issue these commands, type setcommandname at the LSNRCTL> prompt. You use the set commands to modify the listener.ora file. For example, you can use this command to set up logging and tracing. You can set most of these parameters using the Oracle Net Manager. To display the current setting of a parameter, use the show command, which displays the current settings of the parameters set using the set command. Table 12.4 lists the most commonly used lsnrctlset commands. Type set or show to display a listing of all the commands (shown earlier in this section). Table 12.4: A Summary of the lsnrctl set Commands Open table as spreadsheet Command Description current_listener Sets the listener to modify or show the name of the current listener. displaymode Sets the display for the lsnrctl utility to RAW, COMPACT, NORMAL, or VERBOSE. log_status Shows whether logging is on or off for the listener. log_file Shows the name of the listener log file. log_directory Shows the log directory location. rawmode Shows more detail on STATUS and SERVICES when set to ON. Values are ON or OFF. startup_waittime Sets the length of time that a listener will wait to respond to a status command in the lsnrctl command-line utility. spawn Starts external services that the listener is listening for and that are running on the server. save_config_on_stop Saves changes to the listener.ora file when exiting lsnrctl. trc_level Sets the trace level to OFF, USER, ADMIN, or SUPPORT. trc_file Sets the name of the listener trace file. trc_directory Sets the name of the listener trace directory. inbound_connect_timeout Sets the time in seconds for the client to complete the connection request. valid_node_checking_registration When set to on, only local IP address connections are allowed. Stopping the Listener To stop the listener, you must issue the lsnrctl stop command. This command stops the default listener. To stop a nondefault listener, include the name of the listener. For example, to stop LISTENER1, type lsnrctl stop listener1. If you are in the lsnrctl> facility, you will stop the current listener defined by the current_listener setting. To see what the current listener is set to, use the show command. The default value is LISTENER. Stopping the listener does not affect clients already connected to the database. It only means that no new connections can use this listener until the listener is restarted. The following code shows what the stop command looks like: Larger View$ lsnrctl stop listener1 LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 28-NOV-2013 15:52:10 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=btlnx63)(PORT=1522))) The command completed successfully The listener writes status, trace, startup, shutdown, and connection information to a listener log file named listener.log. This file can be found under <ADR>/diag/tnslsnr/listener/trace. Dynamically Registering Services Oracle databases can automatically register their presence with an existing listener. The instance registers with the listener defined on the local machine. Dynamic service registration allows you to take advantage of other features, such as load balancing and automatic failover. The LREG process is responsible for registering this information with the listener. When dynamic service registration is used, you will not see the service listed in the listener.ora file. To see the service listed, run the lsnrctl services command. Be aware that if the listener is started after the Oracle instance, there may be a time lag before the instance actually registers information with the listener. For an instance to automatically register with a listener, the listener must be configured as a default listener, or you must specify the init.ora parameter LOCAL_LISTENER. The LOCAL_LISTENER parameter defines the location of the listener with which you want the Oracle server to register. You may specify the connection description for the parameter or use a tns alias resolved by the tnsnames.ora file. Note When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes its relevant parameters. If it is not running, then LREG periodically attempts to contact it. You can register the instance with the listener by executing ALTER SYSTEM REGISTER. You must configure two other init.ora parameters to allow an instance to register information with the listener. Two parameters are used to allow automatic registration: INSTANCE_NAME and SERVICE_NAMES. The INSTANCE_NAME parameter is set to the name of the Oracle instance you want to register with the listener. The SERVICE_NAMES parameter is a combination of the instance name and the domain name. The domain name is set to the value of the DB_DOMAIN initialization parameter. For example, if your DB_DOMAIN is set to BJS.COM and your Oracle instance is DBA, set the parameters as follows: Instance_name = DBA Service_names = DBA.BJS.COM If you are not using domain names, set the INSTANCE_NAME and SERVICE_NAMES parameters to the same values. One optional parameter to configure for dynamic registration is the REMOTE_LISTENER. A remote listener is a listener residing on one computer that redirects connections to a database instance on the same or different computer. Remote listeners are typically used in an Oracle Real Application Clusters (Oracle RAC) environment, or when local and SCAN listeners are used in the environment. The syntax for configuring REMOTE_LISTENER is the same as LOCAL_LISTENER. Tip Dynamic service registration is configured in the database initialization file. It does not require any configuration in the listener.ora file. However, listener configuration must be set to listen on the ports named in the database initialization file. Oracle Net Logging and Tracing on the Server If a network problem persists, you can use logging and tracing to help resolve it. Oracle generates information into log files and trace files that can assist you in tracking down network connection problems. You can use logging to find general information about the success or failure of certain components of the Oracle network. You can use tracing to get in-depth information about specific network connections. Note By default, Oracle produces logs for clients and the Oracle listener. Logging records significant events, such as starting and stopping the listener, along with certain kinds of network errors. Errors are generated in the log in the form of an error stack. The listener log records information such as the version number, connection attempts, and the protocols for which it is listening. You can enable logging at the client, middle-tier, and server locations. Tracing, which you can also enable at the client, middle-tier, or server location, records all events that occur on a network, even when an error does not occur. The trace file provides a great deal of information that logs do not, such as the number of network round-trips made during a network connection or the number of packets sent and received during a network connection. Tracing enables you to collect a thorough listing of the actual sequence of the statements as a network connection is being processed. This gives you a much more detailed picture of what is occurring with connections that the listener is processing. Use Oracle Net Manager to enable most logging and tracing parameters. Many of the logging and tracing parameters are found in the sqlnet.ora file. Let's take a look at how to enable logging and tracing for the various components in an Oracle network. Server Logging By default, the listener is configured to enable the generation of a log file. The log file records information about listener startup and shutdown, successful and unsuccessful connection attempts, and certain types of network errors. Here's what everything means by default: The listener log location is <DIAGNOSTIC_DEST>/diag/tnslsnr/<hostname>/listener/trace on Unix. The default name of the file is listener.log. The XML version of the listener log is under <DIAGNOSTIC_DEST>/diag/tnslsnr/<hostname>/listener/alert, and the filename is log.xml. If the DIAGNOSTIC_DEST parameter is not defined, Oracle defaults it to $ORACLE_BASE. Real World Scenario: Use Tracing Sparingly Use tracing only as a last resort if you are having connectivity problems between the client and server. Complete all the server-side checks described earlier before you resort to tracing. The tracing process generates a significant amount of overhead, and depending on the trace level set, it can create some rather large files. This activity will impede system I/O performance because of all the information that is written to the logs, and if left unchecked, it could fill your disk or file system. We were once involved with a large project that was using JDBC to connect to the Oracle server. We were having difficulty with connections being periodically dropped between the JDBC client and the Oracle server. We enabled tracing to try to find the problem. We did eventually correct the problem (it was with how our DNS names server was configured), but the tracing was left on inadvertently. When the system eventually went into production, the trace files grew so large that they filled the disk where tracing was being collected. To prevent this from happening, periodically ensure that the trace parameters are not turned on, and if they are, turn them off. Information in the listener.log file contains information about connection attempts, the name of the program executing the request, and the name of the client attempting to connect. The last field contains a zero if a request was successfully completed. Server Tracing As mentioned earlier, tracing gathers information about the flow of traffic across a network connection. Data is transmitted back and forth in the form of packets. A packet contains sender information, receiver information, and data. Even a single network request can generate a large number of packets. In the trace file, each line starts with the name of the procedure executed in one of the Oracle Net layers and is followed by a set of hexadecimal numbers. The hexadecimal numbers are the actual data transmitted. If you are not encrypting the data, sometimes you will see the actual data after the hexadecimal numbers. Tip If you are doing server-to-server communications and have a sqlnet.ora file on the server, you can enter information in the Server Information section located on the Tracing tab of the Profile screen in Oracle Net Manager tracing. This provides tracing information for server-to-server communications. Enabling Server Tracing You can enable server tracing from the same Oracle Net Manager screens shown earlier. Simply click the Tracing Enabled radio button. The default trace file location is $DIAGNOSTIC_DEST/diag/tnslsnr/hostname/listener/trace in Unix. You can set the trace level to OFF, USER, ADMIN, or SUPPORT. The USER level detects specific user errors. The ADMIN level contains all the user-level information along with installation-specific errors. SUPPORT is the highest level and can produce information that might be beneficial to Oracle Support personnel. This level also can produce large trace files. The following example shows a section of the listener.ora file with the logging and tracing parameters enabled: TRACE_LEVEL_LISTENER = ADMIN TRACE_FILE_LISTENER = LISTENER.trc LOGGING_LISTENER = ON LOG_FILE_LISTENER = LISTENER.log

Oracle Net

responsible for handling client-to-server and server-to-server communications, and it can be configured on the client, the middle-tier application, web servers, and the Oracle server. Oracle Net manages the flow of information in the Oracle network infrastructure

Oracle Shared Server: Direct Handoff Method

the client connection can also be established using a direct handoff method. This would be the case, for example, when the client request originates from the same machine on which the listener and database are running. Figure 12.8 outlines the connection steps when using Oracle Shared Server and the direct handoff method: The client contacts the Oracle listener after resolving the service name. The Oracle listener passes the connection request to the dispatcher with least load. The client now has an established connection to the dispatcher process. LREG (process monitor) sends information to the listener about the number of connections being serviced by the dispatchers.

stop listener

to stop LISTENER1, type lsnrctl stop listener1.