Chapter 5
Connecting to the Database
In This Chapter
Starting and stopping the database
Connecting to the database instance
Avoiding pitfalls
You can’t use a database until it’s running and you connect to it. In this chapter, we cover how to make that happen. First, we cover the setup of your DBA environment so you can log in to the database and begin your startup work. Next, we cover the various startup modes and states that a database can be in depending on your type of work. Furthermore, shutting down a database can happen in several ways. We cover all the options so that you can start up and shut down with the proper parameters.
You can connect locally or remotely to a database with Oracle Net. We examine the role of the database listener process and how to configure, start, and stop it. Next, we show how to set up client-side connections to the database. Finally, we cover a few common problems you might encounter when setting up Oracle Net.
Starting and Stopping the Database
Before users connect to a database instance to do work, it obviously must be running. Starting up entails allocating the database instance memory, starting the database processes, and opening the control and database files in a mode accessible and appropriate for the users. Depending on the type of work being done, there are several states a database can be in for the users or DBA to access. Alternatively, sometimes you need to shut down a database instance for a multitude of reasons. When you have to do a shutdown, though, what happens to users logged in and doing work? When performing a shutdown, you can handle existing users and the state of their work in several ways.
Environmental requirements
Before starting or stopping an Oracle database instance, a few environmental requirements must be met. These environment requirements get you logged in to the server as the correct user with the right environment variables so that you can do your DBA work.
Log in to the database server
Log in to the server where the database resides to do your key database administrator (DBA) work. Yes, you can do some of this via Enterprise Manager but only after you’ve set up your environment and created your database in the first place.
Most critical DBA work occurs on the database server itself because it provides the most flexibility and is the simplest for starting DBAs.
Log in as the Oracle DBA account
You should be in the operating system DBA account that owns the Oracle software to start and stop the database. Commonly this is the oracle user account and is in the DBA group:
$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba))
The oracle user is in groups oinstall and dba.
Set up your environment variables
Many environment variables exist for your oracle user, and we cover them in detail in Chapter 4. However, at a minimum you want to have these variables set:
ORACLE_BASE
ORACLE_HOME
ORACLE_SID
$ echo $ORACLE_BASE
/u01/app/oracle
$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/dbhome_1
$ echo $ORACLE_SID
dev12c
Be sure to verify the ORACLE_BASE, ORACLE_HOME, and ORACLE_SID variables before you do any type of DBA work. It is very easy to define the wrong ORACLE_HOME, in which case you work with the wrong database software. Worse yet, it’s even easier to incorrectly define ORACLE_SID and stop the wrong database! If it occurs frequently, you’re making what we refer to as a “career-limiting move.”
Start SQL*Plus as a DBA
The command-line interface into Oracle databases is SQL*Plus. To do serious DBA work such as startup or shutdown, you need to be logged in as SYSDBA.
To log in this way, you must be the oracle operating system user as described earlier in this chapter. Then start SQL*Plus with the "/ as sysdba" option:
$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.1.0 - Production on Sun May 19 07:46:53 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
SQL>
An alternative: When you’re in SQL*Plus, issue connect as sysdba to log in as SYSDBA, provided you’re on the database server as the oracle operating system user.
SQL> connect / as sysdba
Connected.
SQL>
When you’re connected as SYSDBA, you can begin your DBA work.
Database parameter file
Before starting the database, you must have a parameter file listing all the different runtime parameters, such as SGA configuration. This is covered in detail in Chapter 4, so we don't rehash the details here. However, we assume you have your SPFILE created and in a default location so that Oracle can find it. If it isn't in a default location, or you want to use a different parameter file, you may use the pfile='PATH TO SPFILE/FILENAME'syntax with your startup commands.
Improper environment setup is a common error and is something you should correct before beginning your database work. Doing so will save you time and frustration troubleshooting unnecessary errors.
Starting the database
You don’t actually start a database per se; you start the instance.
A database is defined as the actual data, index, redo, temp, and control files that exist on the files system. The instance consists of the processes (PMON, SMON, DBWR, LGWR, and others) and the SGA (memory pool) that access and process data from the database files. The instance is what accesses the database, and it is the instance that users connect to. Thus, it is the instance (not the database) that you actually start.
Are we splitting hairs here? Not in this case; you need to understand the relationship between the instance and the database to understand startup and shutdown.
As an Oracle instance starts, it proceeds through various states until it and the database are fully open and accessible to users. At each state, different components are started and opened. Furthermore, at each state you may perform different types of DBA or user work. You may specify your startup command to take the database instance into a specific state depending on what you need to do.
In ascending order, during startup the database instance goes through these states:
NOMOUNT
Read Parameter File
Allocate SGA
Start Background Processes
Only SGA and Background Processes Running
Used for CREATE DATABASE (only SYS can access)
Specified by STARTUP NOMOUNT
MOUNT
Read Parameter File
Allocate SGA
Start Background Processes
Open and Read Control File
SGA and Background Processes Running and Control Files Open
Used for database maintenance and recovery operations (only SYS can access)
Specified by STARTUP MOUNT
OPEN
Read Parameter File
Allocate SGA
Start Background Processes
Open and Read Control File
Open All Database Files
SGA and Background Processes Running, Control Files Open, All Database Files Open
Default OPEN state for database and is accessible by users and applications
Specified by STARTUP or STARTUP OPEN
Unless you’re performing specialized maintenance, the default is as follows:
STARTUP with the parameter file read
Background processes and SGA started
Control files open and read
All database files open
In this open state, users access the database normally.
Here’s what it looks like when starting the database into the default OPEN mode. Because we’re using the default parameter file, we don’t need to specify one.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.1.0 - Production on Sun May 19 09:59:12 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 789172224 bytes
Fixed Size 2148552 bytes
Variable Size 557844280 bytes
Database Buffers 218103808 bytes
Redo Buffers 11075584 bytes
Database opened.
SQL>
Although we normally go straight to the fully open mode, you can increment the modes. For example, you could do database maintenance with the database in MOUNT mode and, once done, issue ALTER DATABASE OPEN to take the database to open mode so users can start work.
That’s what’s done here:
SQL> startup mount
ORACLE instance started.
Total System Global Area 789172224 bytes
Fixed Size 2148552 bytes
Variable Size 570427192 bytes
Database Buffers 205520896 bytes
Redo Buffers 11075584 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL>
Note that you can only go forward to a more open state; you can’t move to a more restrictive state without issuing a shutdown.
In most cases, when you open a database you want it open for every user. Sometimes, however, you want to block all or some users even though the database is in the OPEN state.
To do this, put the database in RESTRICTED SESSION mode via one of these ways:
STARTUP RESTRICT
ALTER SYSTEM ENABLE RESTRICTED SESSION
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 789172224 bytes
Fixed Size 2148552 bytes
Variable Size 570427192 bytes
Database Buffers 205520896 bytes
Redo Buffers 11075584 bytes
Database mounted.
Database opened.
SQL>
SQL> alter system enable restricted session;
System altered.
When the database is OPEN, you must grant users CREATE SESSION to connect.
When the database is RESTRICTED, users must have CREATE SESSION and they also must have RESTRICTED SESSION to connect.
The only backdoor is if the user was already logged in when an ALTER SYSTEM ENABLE RESTRICTED SESSION was issued; then the user can remain logged in. Therefore, you should kill all user sessions after putting the database in RESTRICTED mode to kick them out. If they don’t have RESTRICTED SESSION, they get this Oracle error when they try to log in:
$ sqlplus barb/test123
SQL*Plus: Release 12.1.0.1.0 - Production on Sun May 19 11:26:41 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
Why would you want to do this (other than just to frustrate your users)? Although frustrating users is the secret pleasure of every administrator (especially security administrators), some valid technical reasons exist. Major data, table, or application updates often need a stable system with no updates or locks to contend with so they can process successfully. Some database maintenance operations also require a restricted session.
If you need to allow in a subset of users or perhaps the application user processing a database job, you may grant them RESTRICTED SESSION:
SQL> grant restricted session to barb;
Grant succeeded.
SQL> connect sdeas/test123
Connected.
Revoke the RESTRICTED SESSION from any non-DBA user once the user’s work is done. Also, don’t forget to take the instance out of restricted session.
SQL> alter system disable restricted session;
System altered.
Starting up database instances isn’t terribly difficult, and most times you use the default STARTUP command to take the database instance to the OPEN state. Only occasionally does the situation require a RESTRICTED SESSION.
If the database startup seems to take a few minutes, it may be because of a large SGA during which time memory is being allocated. Or there may be many database files to open.
If the database crashed or a SHUTDOWN ABORT occurred prior to the startup, database instance recovery is occurring, which can take time. If this occurs, leave your screen with the STARTUP command open; let it run. View the alert log with another window. We cover the alert log in Chapter 12. If more severe errors occur (such as media recovery), they appear both on the startup screen and in the alert log file. Of course, you can prevent many of these issues if you stop the database in a clean manner. Carry on to the next topic.
Stopping the database
Just as there is an order of events to starting a database instance, there is also an order for how a database instance is stopped. Ideally, this is what happens during a database shutdown:
New connections to the database are denied.
Existing transactions are either committed or rolled back with proper updates to online redo log files.
User sessions are terminated.
Database file headers are updated and files are closed.
SGA is shut down.
Background processes are terminated.
It is preferable for all the steps to occur naturally during shutdown, which ensures that
All transactions are neatly committed or rolled back.
Online redo log files are properly updated.
All files are closed properly without corruption.
If the preceding steps don’t occur during shutdown because of a server or database instance crash or SHUTDOWN ABORT, the cleanup operations must occur during startup in a phase called instance recovery.
During instance recovery, Oracle won’t open a database instance until it’s satisfied that all transactions are accounted for and all data files are opened. If it can’t complete these tasks, error messages appear and the DBA must address them. Instance recovery is successful most of the time, but it may take several minutes to process the cleanup.
Shutdown types
When a database needs to be shut down, several methods exist to do so with varying effects on current users and their transactions.
SHUTDOWN [NORMAL]
New connections to the database are denied.
Existing transactions continue normally until either they roll back or commit.
Users log out normally on their own.
After the last user logs out, database file headers are updated and files are closed.
SGA is shut down.
Background processes are terminated.
Specified by the SHUTDOWN or SHUTDOWN NORMAL command.
SHUTDOWN TRANSACTIONAL
New connections to the database are denied.
Existing transactions continue normally until they either roll back or commit.
After an existing transaction is completed, user sessions are terminated.
Database file headers are updated and files are closed.
SGA is shut down.
Background processes are terminated.
Specified by the SHUTDOWN TRANSACTIONAL command.
SHUTDOWN IMMEDIATE
New connections to the database are denied.
Existing transactions are rolled back.
User sessions are terminated.
Database file headers are updated, and files are closed.
SGA is shut down.
Background processes are terminated.
Specified by the SHUTDOWN IMMEDIATE command.
SHUTDOWN ABORT
New connections to the database are denied.
Existing transactions are not rolled back.
User sessions are terminated.
SGA is shut down.
Background processes are terminated.
Specified by the SHUTDOWN ABORT command.
Instance recovery is required on startup.
Shutdown decisions
When do you use each shutdown type?
Generally, SHUTDOWN IMMEDIATE is what you want because it cleanly commits or rolls back existing transactions, terminates user sessions when they are complete, and then closes the database in a clean manner.
Don’t use SHUTDOWN NORMAL very often because even one user still logged in (after he’s left for the day) can hang the shutdown.
SHUTDOWN TRANSACTIONAL doesn’t buy you much because it forces you to wait on users to finish their transactions. If you want to wait, you can just enter SHUTDOWN NORMAL. However, if you want to force them off the database instance, you use SHUTDOWN IMMEDIATE. There are times SHUTDOWN TRANSACTIONAL is useful, but it’s not as common as you might think. This method is most commonly used in clustered environments.
Here’s how a typical SHUTDOWN IMMEDIATE executes. Keep in mind that you must be logged in as SYSDBA to run the shutdown command.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Use SHUTDOWN ABORT only when you have to. It essentially crashes the database and expects instance recovery to pick up the pieces. You may have to do that if the system is hung, but it shouldn’t be your first choice (unless you want to do real database recovery sometime). If you can issue commands on the database instance, issue an ALTER SYSTEM SWITCH LOGFILE to force a checkpoint to close file headers and flush the online redo logs before issuing the SHUTDOWN ABORT. Forcing a check point allows for an easier instance recovery during the next startup.
Before you issue any commands changing the running state of the instance, make sure you’re connecting to the correct instance. On Linux, if the instance is not started yet, type the following at the command line:
$ echo $ORACLE_SID
If the instance is already running and you want to shut it down or restrict it, type this:
$ sqlplus / as sysdba
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev12c
Connecting to the Database Instance
A database instance isn’t much good if you can’t connect to it. Establishing a reliable, persistent, and secure connection to the database from the client is essential.
Oracle has established a network architecture of protocols, processes, utilities, and configuration files to support communication into the database. Oracle Net (formally called SQL*Net or Net8) is the Oracle networking protocol.
Oracle Net is supported by
DBA-managed listener processes
Client- and server-side configuration files
Command-line utilities
Optional GUI administration tools
Additionally, connections can come into the database via several lighter-weight non-Oracle protocols such as ODBC or JDBC. However, even these non-Oracle protocols use the same underlying server-side Oracle components as Oracle Net connections. For these reasons, we focus on the Oracle specific components.
Local versus remote connections
Connections into the database can be one of two kinds:
Local (bequeath): A local connection originates from the same server the database is on and doesn’t use the database listener process. When you connect to SQL*Plus as “/ as sysdba”, you’re connecting locally.
Remote: All other connections from outside the database server or those from the server using the listener are remote connections. The easy way to determine whether a user is connecting remotely is if you have @TNS_ALIAS in the connect screen. For example, sqlplus scott@dev12c indicates a remote connection to the dev12c database.
Communication flow
Connections to an Oracle database typically come across from a client located away from the database; over a network infrastructure; to the database server; through a database listener process; and, finally, into the database itself.
On the client side, the program calling the database references tnsnames.ora to find the database server host and protocol to send the request to. The request then leaves the client and goes onto the network utilization OracleNet. The default port for Oracle Net communications is 1521, although that’s configurable. Over this Oracle Net protocol is where database communications traffic flows between the client and database server.
After a client’s communication request reaches the database server host, it’s handed off to the listener. The database listener is a separate Oracle software process on the database server that listens for incoming requests on the defined OracleNet port (1521). When it gets a request, the listener identifies which database instance is targeted for that request and establishes a connection to that database instance. On the server side, the listener uses the listener.ora file to make this determination. When the connection is established and the session begins, the listener steps out of the picture and allows communication between the database and client. Each client session has a dedicated server process on the server side. Within this dedicated server process, the user’s session code is executed. Figure 5-1 represents the communication flow.
The client contains the client application and tnsnames.ora file. It communicates to the database server over Oracle Net on port 1521. On the database server, a listener process is configured by way of the listener.ora file. The listener routes the incoming request to the target database instance (either dev11g or db01) and establishes the initial connection handshake between the database instance and client.
Figure 5-1: Communica-tion flowchart.
Setting up tnsnames.ora
You must provide the address or location of the database you’re trying to connect to. This information is often stored in the tnsnames.ora text file, which exists on the client you’re connecting from. Other methods of locating your database exist such as referencing an Oracle Internet Directory (OID), but tnsnames.ora is the most common method for clients.
Note this “client” can be a user’s workstation, a web application server, or even another database server.
Here is a sample tnsnames.ora file that can be found in ORACLE_HOME_DIRECTORY/network/admin:
dev12c =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dev12c)
)
db01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasvr02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
This particular tnsnames.ora contains 2 TNS (Transport Network Substrate) aliases, one for dev12c and one for db01. When connecting to a database instance, you actually specify the TNS alias (not database name). For example, sqlplus barb@dev12c uses dev12c as the alias. The TNS alias can be any name (such as dev12c or something more generic like dev or trainingdb); it doesn’t have to be the actual database name. That flexibility means you can have a generic alias and not hardcode the database name.
Under HOST you specify either the DNS host name or the IP address of the server containing the database instance. Again, try to avoid hardcoded values such as IP address and use DNS names if possible.
PORT is the port the server-side listener process is listening on. It’s also the port you connect across the network on for your OracleNet traffic (thus the firewalls must be open on that port).
SERVICE_NAME is the service name of the database instance you’re attempting to connect to. You can also use SID, although Oracle is promoting the use of SERVICE_NAME instead.
The tnsnames.ora file is text based, and you can edit it by hand. After making changes, it’s not necessary to restart the database or listener process.
Configuring the database listener with listener.ora
The key file to the listener process is the listener.ora configuration file. This file identifies two things:
Each database it will listen for
On what ports (default 1521)
The file is located in ORACLE_HOME/network/admin.
Here’s a sample listener.ora file:
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/lis
tener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db01)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = db01)
)
(SID_DESC =
(GLOBAL_DBNAME = dev12c)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = dev12c)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
In the preceding code, you see two main sections: SID_LIST_LISTENER and LISTENER. The SID_LIST_LISTENER section identifies each database instance that the listener will service connections for. It lists the global database name, ORACLE_HOME, and SID.
As you need more databases, simply add the following section and then customize the relevant information:
(SID_DESC =
(GLOBAL_DBNAME = dev12c)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = dev12c)
)
The LISTENER section identifies what host the database exists on and what port it accepts connections on:
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
)
Here you see the listener will listen on the HOST (server) orasvr01, and the port is 1521. Requests on other ports will not be acknowledged.
You can add more databases, even if they’re different database versions to the listener.ora. If you have multiple database versions, run your listener with the highest version of the database software you have. You can also add additional LISTENER processes (if you want to listen on multiple ports, for example).
You should be aware of one configuration option we don’t necessarily recommend: Shared Servers (also known as Multi-Threaded Servers, or MTS). With this method, each user connection uses a shared process rather than a dedicated server process on the database server. In theory, having connections share a server-side process reduces memory use and is good for systems with lots of concurrent users. However, we’ve never seen it provide a noticeable benefit, and we wouldn’t consider it a common configuration.
Note that this is different from connection pooling with application servers, which we do recommend. What we outline in the earlier “Communication flow” section is the dedicated server mode, which is more common, provides better performance, and is required for DBA connections.
The sqlnet.ora file is one additional configuration file. It can be client or server side, usually located with the listener.ora or tnsnames.ora file. The sqlnet.ora file is a special options file where you can add parameters to the Oracle Net architecture. This file can exist both on servers to impact the listener process and on clients to influence TNS settings. For example, you can
Add commands to force increased tracing, logging options, or encryption.
Tell the listener to add a domain name to each database.
Direct the listener to look up connection information in an LDAP instead of a tnsnames.ora file.
Here is a simple sqlnet.ora file:
$ more sqlnet.ora
NAMES.DIRECTORY_PATH=TNSNAMES
The setting simply tells the client to use the tnsnames.ora file instead of any other resource (such as an LDAP).
If you’re experiencing connection issues and your tnsnames.ora and listener.ora files look fine, don’t forget the sqlnet.ora. There may be a forgotten setting there causing issues.
Starting and stopping the database listener
The database listener process reads the listener.ora and sqlnet.ora files for its configuration; the DBA manages it by using the lsnrctl command-line utility. You can use the utility to do these things to the listener:
Start
Stop
Check status
There is no direct relationship between the listener process and the database itself; the processes operate independently. That means you can start the listener before or after the database.
However, remember that the listener must be started before the database can service remote connections.
To start the listener, issue the lsnrctl start command:
[oracle@orasvr01 dbs]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 19-MAY-2013 12:10:44
Copyright (c) 1991, 2013, Oracle. All rights reserved.
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/diag/tnslsnr/orasvr01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.66)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 19-MAY-2013 12:10:44
Uptime 0 days 0 hr. 0 min. 0 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/orasvr01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=orasvr01)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/dev12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dev12c" has 1 instance(s).
Instance "dev12c", status READY, has 1 handler(s) for this service...
Service "dev12cXDB" has 1 instance(s).
Instance "dev12c", status READY, has 1 handler(s) for this service...
Service "prod12c" has 1 instance(s).
Instance "prod12c", status READY, has 1 handler(s) for this service...
Service "prod12cXDB" has 1 instance(s).
Instance "prod12c", status READY, has 1 handler(s) for this service...
The command completed successfully
If you need to stop the listener, you can issue the lsnrctl stop command:
$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 19-MAY-2013 12:10:40
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.66)(PORT=1521)))
The command completed successfully
$
After changing the listener.ora file, you must restart the listener process. You can do this via the stop and start commands. An easier method is the lsnrctl reload command. It effectively restarts the listener process without the explicit stop and start.
To determine what databases the listener is configured to service requests, you can read the listener.ora configuration file. Or, more easily, you can issue the lsrnctl status command:
$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 19-MAY-2013 12:13:09
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.66)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 19-MAY-2013 12:10:44
Uptime 0 days 0 hr. 2 min. 25 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/orasvr01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=orasvr01)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/dev12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dev12c" has 1 instance(s).
Instance "dev12c", status READY, has 1 handler(s) for this service...
Service "dev12cXDB" has 1 instance(s).
Instance "dev12c", status READY, has 1 handler(s) for this service...
Service "prod12c" has 1 instance(s).
Instance "prod12c", status READY, has 1 handler(s) for this service...
Service "prod12cXDB" has 1 instance(s).
Instance "prod12c", status READY, has 1 handler(s) for this service...
The command completed successfully
$
This code shows listening for connections for the dev12c and prod12c databases.
Logs for the listener process are stored in the listener.log file. Depending on database setup, the listener.log may be in one of these two spots:
In ORACLE_HOME/network/admin
Under the ADR infrastructure in ADR_BASE/diag/tnslsnr tree
For more information on this file, see Chapter 12.
Testing the connection
The best way to test a connection is via the application, but that isn’t always possible. Preferably, you’re on the client tier and actually go through the same network path as the client applications. If you don’t do that, you may not be executing a valid test.
To execute a connectivity test, follow these steps to determine whether you can connect to the database instance:
1. Go to the client tier.
2. See whether Oracle client software such as SQL*Plus and tnsnames.ora is installed.
3. Execute a sqlplus username@tns_alias, such as sqlplus mwhalen@dev12c.
4. Enter the password to connect to the database.
Using the tnsping utility is an even faster method that doesn’t require a password. This utility connects over the network via the listener and establishes a handshake. It then terminates the connection and reports the results, which you see here:
$ tnsping dev12c
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 19-MAY-2013 12:20:07
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev12c)))
OK (20 msec)
Testing connections is a good verification step for the DBA. If problems occur, it lets you catch them first instead of relying on users to report them later.
Oracle Net Configuration Assistant
It’s important to understand how the listener works and what different files control the communication process; that’s why we explain those parts first in this chapter.
Many DBAs simply copy the same template files from one server to the next, making only minor changes. However, Oracle does provide a GUI database assistant tool called Oracle Net Configuration Assistant to preconfigure tnsnames.ora and listener.ora for you on the server side. It can also be executed on the client side. It walks you through generating your configuration files and even tests the connection for you.
We encourage you to test the Oracle Net Configuration Assistant and see whether it’s easy for you, but we caution you to understand the files themselves. Through that understanding, you can better fix problems and gotchas.
Sidestepping Connection Gotchas
Setting up connections to an Oracle database doesn’t have to be difficult, but sometimes initial setup can be tough. Most of the time, you, as the DBA, review the configuration and figure out the issue. In other cases, you need to work with the network people to trace connections or open firewalls. In still other cases, you work with the application experts and determine who the client application is attempting to connect to the database.
Remember to start the listener. This sounds obvious, but it’s not uncommon to start the database and forget to start the listener process. Scripting these steps helps eliminate these errors.
Keep open the firewall on the listener port. It is common to have a firewall separating the database server from the users or web application servers. That means Oracle Net traffic may be blocked; in fact, you should assume that you need to have the firewall opened until proven otherwise. Using the tnsping utility can help you test these connections.
Watch out for multiple tnsnames.ora files. On users’ workstations, multiple installations of Oracle client software are likely to have multiple tnsnames.ora files. That becomes a problem when an update to tnsnames.ora is necessary, but not all the tnsnames.ora files are updated. This issue manifests itself in some applications working and others that don’t. Either have one common file or a script to update all the files.
Copy and paste existing entries and change only the key parameters. Because tnsnames.ora is a text file, it invites people to edit it by hand. That’s fine, but it’s easy to transpose a number (1512, for instance), misspell a server name, or insert an extra parenthesis somewhere. Also, avoid using IP addresses for host information unless you really need to. Use the DNS server name instead and you won’t have to worry about the IP address changing without warning.
None of these errors is insurmountable, but checking these items may save you some time.
Although we use the default port of 1521 in many of our examples, best practice says to modify this port. If you listen with your database on the default port, you make your system more vulnerable to hackers. Choose an unused port that no one has common knowledge of.