Sybex OCA Oracle 10g Administration I Study Guide phần ppt

226 Chapter 4 Oracle Net Services

the next listener in the list is attempted. This feature increases the availability of the Oracle

service should a listener location be unavailable.

Here is an example of what a tnsnames.ora file looks like with connect-time failover enabled:

ORCL =

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=DBPROD)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=DBFAIL)(PORT=1521))

)

(CONNECT_DATA=(SERVICE_NAME=PROD)(SERVER=DEDICATED)

)

)

We will discuss the tnsnames.ora file in detail later in this chapter in the section

“Configuring Oracle Net for the Client.”

Notice the additional entry under the ADDRESS_LIST section. Two listeners are specified. If

a connection is unsuccessful when attempting to connect to the DBPROD host on port 1521, a

connection attempt is made to the DBFAIL host on port 1521. The time that the connection

waits before attempting to failover is operating system dependent.

Transparent Application Failover

The Transparent Application Failover (TAF) feature is a runtime failover for high-availability

environments, such as Oracle Real Application Clusters. TAF fails over and reestablishes

application-to-service connections. It enables client applications to automatically reconnect

to the database if the connection fails and, optionally, resume a SELECT statement that was

in progress. The reconnection happens automatically from the OCI library.

The following code shows an example of the tnsnames.ora file setup for using Transparent

Application Failover:

ORCL =

(DESCRIPTION=

(FAILOVER=ON)

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=DBPROD)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=DBFAIL)(PORT=1521))

)

(CONNECT_DATA=(SERVICE_NAME=PROD)(SERVER=DEDICATED)

(FAILOVER_MODE=(TYPE=select)(METHOD=basic))

)

)

4367.book Page 226 Monday, October 4, 2004 2:19 PM

Configuring Oracle Net on the Server 227

In this example, notice additional entries in the tnsnames.ora file that enable the Transpar￾ent Application Failover. There are two hosts, DBPROD and DBFAIL. If DBPROD becomes unavail￾able, the connections will failover to the DBFAIL host and connect to the associated service. The

database service of PROD must be the same on both the DBPROD and DBFAIL servers for this

example to work properly.

Client Load Balancing

Client Load Balancing is a feature that allows clients to randomly select from a list of listeners.

Oracle Net moves through the list of listeners and balances the load of connection requests

across the available listeners. Here is an example of the tnsnames.ora entry that allows for

load balancing:

ORCL =

(DESCRIPTION=

(LOAD_BALANCE=ON)

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=MWEISHAN-DELL)(PORT=1522))

(ADDRESS=(PROTOCOL=TCP)(HOST=MWEISHAN-DELL)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=ORCL)

)

)

Notice the additional parameter of LOAD_BALANCE. This enables load balancing between the

two listener locations specified.

Connection Load Balancing

Connection Load Balancing is a feature that enables better distribution of connection among

a group of dispatchers in an Oracle Shared Server environment. The next chapter explains this

concept in more detail.

Troubleshooting Server-Side Connection Problems

Even if it seems that you have configured Oracle server-side components correctly, network

errors can still occur that will require troubleshooting. You can experience a connection prob￾lem for a variety of reasons:

The client, middle-tier, or Oracle server is not configured properly.

The client cannot resolve the net service name.

The underlying network protocol is not active on the server; for example, the TCP/IP

process on the server is not running.

The user enters an incorrect net service name, user ID, or password.

4367.book Page 227 Monday, October 4, 2004 2:19 PM

228 Chapter 4 Oracle Net Services

You can diagnose and correct these types of errors. In the next section, “Server-Side Com￾puter and Database Checks,” you will see how to diagnose and correct connection problems

originating from the Oracle server. In the next chapter, we discuss troubleshooting problems

with client-side network configuration.

When a client has a connection problem that is up to you to fix, it is helpful to first gather

information about the situation. Make sure you record the following information:

The Oracle error that the client received.

The location of the client. Is the client connecting from a remote location, or is the client

connected directly to the server?

The name of the Oracle server to which the client is attempting to connect.

Check to see if other clients are having connection problems. If so, are these clients in the

same general location?

Ask the user what is failing. Is it the application being used or the connection?

We will now look at the particular network areas to check and the methods used to further

diagnose connection problems from the Oracle server. We will also look at the Oracle error

codes that will help identify and correct the problems.

Server-Side Computer and Database Checks

You can perform several server-side checks if a connection problem occurs. Before running such

checks, be sure that the machine is running, that the Oracle server is available, and that the lis￾tener is active. In the following sections, we’ll summarize the checks to perform on the server.

Check the Server Machine

Make sure that the server machine is active and available for connections. On some systems, it

is possible to start a system in a restricted mode that allows only supervisors or administrators

to log in to the computer. Make sure that the computer is open and available to all users.

On a TCP/IP network, you can use the ping utility to test for connectivity to the server. Here

is an example of using ping to test a network connection to a machine called matt:

C:\users\default>ping matt

Pinging cupira03.cmg.com [10.69.30.113] with 32 bytes of data:

Reply from 10.69.30.113: bytes=32 time=10ms TTL=248

Reply from 10.69.30.113: bytes=32 time=10ms TTL=248

Reply from 10.69.30.113: bytes=32 time<10ms TTL=248

Reply from 10.69.30.113: bytes=32 time=10ms TTL=248

The reply indicates that the machine can be seen on the network.

Check the Database

Make sure that the database is running. Connect to the Oracle server and log in to the database using

a tool such as SQL*Plus. First attempt a local connection, which does not use the Oracle listener.

4367.book Page 228 Monday, October 4, 2004 2:19 PM

Configuring Oracle Net on the Server 229

To connect to the Oracle server using a local connection, set your ORACLE_SID environmental

variable to the name of the Oracle instance that you want to connect to. Then, attempt to connect

to SQL*Plus.

The following example is a connection sequence on Windows that fails because the database

is not running. For example, if the database that you are attempting to connect to is named MJW,

you can use the following code example in a Windows environment for your test:

D:\oracle\ora10g\BIN>sqlplus system/manager

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 10 10:08:16 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

An ORA-01034 error indicates that the Oracle instance is not running. You need to start the

Oracle instance. The ORA-27101 error indicates that no instance is currently available to con￾nect to for the specified ORACLE_SID.

Verify That the Database Is Open to All Users

You can open a database in restricted mode. This means that only users with restricted mode

access can use the system. This is not a networking problem, but it will lead to clients being

unable to connect to the Oracle server. Here is an example of a connection that fails because the

user does not have the restricted session privilege.

D:\>sqlplus scott/tiger@ORCL

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 10 10:09:19 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

As we can see, the user Scott is attempting to connect to the ORCL service. The error message

tells us that user Scott does not have the restricted session privilege and cannot log in until the

DBA either grants this privilege to Scott or takes the database out of restricted session mode.

Check User Privileges

Make sure that the user attempting to establish the connection has been granted the CREATE

SESSION privilege to the database. This privilege is needed for a user to connect to the Oracle

4367.book Page 229 Monday, October 4, 2004 2:19 PM

230 Chapter 4 Oracle Net Services

server. If the client does not have this privilege, you must grant it to the user. To do so, follow

this example:

D:\oracle\ora10g\BIN>sqlplus matt/matt

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 10 10:09:19 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.

ERROR:

ORA-01045: user MATT lacks CREATE SESSION privilege; logon denied

Here is an example of how you can grant the CREATE SESSION privilege to a user:

SQL> grant create session to matt;

Grant succeeded

SQL>

In this example, the DBA has granted the CREATE SESSION privilege to user Matt. Matt now

has the ability to make a connection to the database.

Server-Side Network Checks

After you validate that the server where the database is located is up and available and you ver￾ify that the user has proper privileges, begin checking for any underlying network problems on

the server. In the following sections, we will detail some of the common areas of the server to

check when you are experiencing connection problems.

Check Listener

Make sure that the listener is running on the Oracle server. Make sure that you check the ser￾vices for all the listeners on the Oracle server; you can use the lsnrctl status command to do

this. The following command shows the status of the default listener named LISTENER:

D:\oracle\ora10g\BIN>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 10-JUN-

2004 10:00:36

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mweishan-

dell.corp.goxroads.net)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version

10.1.0.2.0 - Production

4367.book Page 230 Monday, October 4, 2004 2:19 PM

Configuring Oracle Net on the Server 231

Start Date 10-JUN-2004 09:58:47

Uptime 0 days 0 hr. 1 min. 50 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File D:\oracle\ora10g\network\admin\listener.ora

Listener Log File D:\oracle\ora10g\network\log\listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mweishan-

dell.corp.goxroads.net)(PORT=1522)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "orcl.com" has 1 instance(s).

Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

The command completed successful

Also check the services for which the listener is listening. You must see the service to which

the client is attempting to connect. If the service is not listed, the client may be entering the

wrong service, or the listener may not be configured to listen for this service.

Check GLOBAL_DBNAME

If the client is using the hostnaming method, make sure that the GLOBAL_DBNAME parameter is

set to the name of the host machine. You can find this parameter in the service definition of the

listener.ora file. Verify the setting by reviewing the listener.ora configuration. In the follow￾ing sample code, we can see that the GLOBAL_DBNAME parameter has been set to mweishan-dell.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = mweishan-dell) – machine listener is on

(ORACLE_HOME = d:\oracle\ora10g)

(SID_NAME = orcl)

Check Listener Protocols

Check the protocols for which the listener is configured. This is displayed by the lsnrctl ser￾vices command. You can see an example of this command in the section, “Listing the Services

for the Listener,” earlier in this chapter. Make sure that the protocol of the service matches the

protocol the client is using when requesting a connection. If the client is requesting to connect

with a protocol that the listener is not listening for, the user will receive an ORA-12541 “No

Listener” error message.

4367.book Page 231 Monday, October 4, 2004 2:19 PM