Prerequisites for Establishing Connectivity
The tasks in this chapter show a TCP/IP connection between a database server and a client computer. The following conditions are assumed about the database server and client computer:
Database server
The server is running on a network that can access the client
An Oracle database is installed
A listener is configured
TCP/IP protocol support is installed
Client computer
The client computer is running on a network that can access the database server
Oracle Client is installed
TCP/IP protocol support is installed
In a TCP/IP network, each computer has a unique IP address. A name resolution service, such as Domain Name System (DNS), can be used to map the IP address of a computer with its host name. If a name resolution service is not used, then the mapping is typically stored in a centrally maintained file called . This file is located in the directory on Linux and the directory on Microsoft Windows. For example, an entry for a database server computer named may look like the following: hosts/etc\windows\system32\drivers\etcsales-server
#IP address of server host name alias
192.0.2.203 sales-server sales.us.example.com
Parent topic: Quick Start to Oracle Net Services
6.2 Confirming Network Availability
Before using Oracle Net to connect a client computer to a database server, confirm that the client computer can successfully communicate with the database server computer. Evaluating network connectivity can eliminate network-based errors.
The following procedure describes how to confirm network connectivity:
Confirm that the database server computer can communicate with itself with a loopback test as follows:
To confirm hardware connectivity, enter the following command at the command line:
ping ip_address
In the preceding command, is the IP address of the database server computer, such as the following: ip_address
ping 192.0.2.203
To confirm the DNS or host name is configured properly, enter the following command at the command line:
ping host_name
In the preceding command, is the host name of the server. host_name
To test the TCP/IP setup for the server, enter the following command:
ping 127.0.0.1
ping6 ::1
The IP address 127.0.0.1 is the standard IPv4 address for a loopback test. The IP address ::1 (0: 0: 0: 0: 0: 0: 0: 1) is the standard IPv6 address for a loopback test.
Verify the client computer can successfully communicate with the database server computer.
The method for verification varies according to the network protocol. For TCP/IP, you can use PING, FTP or TELNET utilities.
If the client computer cannot reach the server, then verify that the network cabling and network interface cards are correctly connected. Contact your network administrator to correct these problems.
Parent topic: Quick Start to Oracle Net Services
6.3 Starting Oracle Net Listener and the Oracle Database Server
Oracle Net Listener and the Oracle Database server must be running in order for the database server to receive connections. The following procedure describes how to start Oracle Net Listener:
Start the listener with the Listener Control utility. From the command line, enter the following:
lsnrctl
LSNRCTL> START [listener_name]
In the preceding command, listener_name is the name of the listener defined in the file. It is not necessary to identify the listener if you are using the default name . listener.oraLISTENER
A status message indicating that the listener has successfully started displays.
Start the database as follows:
Start SQL*Plus without connecting to the database using the following command:
SQLPLUS /nolog
Connect to the database as SYSDBA using the following command:
SQL> CONNECT username as sysdba
You will be prompted to enter a password.
Note:For simplicity, this example does not perform the password management techniques that a deployed system normally uses. In a production environment, follow the Oracle Database password management guidelines, and disable any sample accounts. See Oracle Database Security Guide for password management guidelines and other security recommendations.
Start the database using the following command:
SQL> STARTUP database_name
In the preceding command, database_name is the name of the database.
See Also:
Oracle Database Administrator’s Guide for additional information about starting the database
Confirm that database service registration with the listener has completed using the Listener Control utility and the following command:
LSNRCTL> SERVICES [listener_name]
The command lists the services supported by the database, along with at least one available service handler. If the database service registration is not listed, then enter the following SQL command: SERVICES
SQL> ALTER SYSTEM REGISTER;
See Also:
“Monitoring Services of a Listener” for additional information about the command SERVICES
Parent topic: Quick Start to Oracle Net Services
6.4 Starting Oracle Connection Manager
If Oracle Connection Manager is installed, then start Oracle Connection Manager as follows:
Start the Oracle Connection Manager Control utility (CMCTL) using the following commands:
cmctl
CMCTL> ADMINISTER [instance_name]
In the preceding command, instance_name is the name of Oracle Connection Manager to administer. You can determine the name by viewing the file. The file is located on the Oracle Connection Manager computer in the directory. cman.oraORACLE_HOME/network/admin
Oracle Connection Manager displays a status message indicating the name of the instance, and informs you that the instance has not yet been started.
Note:If you do not provide an instance name as an argument, then provide Oracle Connection Manager with a fully qualified host name. This is the default. After you issue the ADMINISTER command, CMCTL displays the instance name as follows:
CMAN_fully_qualified_host_name
Start Oracle Connection Manager that you have chosen to administer using the following command:
cmctl> STARTUP
Oracle Connection Manager confirms that the instance has been started, and provides status for the instance.
Exit from the Oracle Connection Manager Control utility using the following command:
cmctl> EXIT
Note:On Microsoft Windows, Oracle Connection Manager can also be started through the Control Panel, as follows:
Select Services in the Control Panel.
Select the HOME_NAME service, and then click Start. OracleCMan
In the Services window, click Close.
Parent topic: Quick Start to Oracle Net Services
6.5 Using Easy Connect to Connect to a Database
After network connectivity has been verified as described in “Confirming Network Availability”, you can use the Easy Connect naming method to connect to the database. This naming method provides out-of-the-box TCP/IP connectivity to databases. It extends the functionality of the host naming method by enabling clients to connect to a database server with an optional port and service name in addition to the host name of the database. The following is the syntax to connect using Easy Connect:
CONNECT username/password@host[:port][/service_name][:server_type][/instance_name]
Note:In Oracle Call Interface documentation, server is referred to as connect_type.
If Oracle Database server installation was performed in Typical mode, then the default service name used by the Oracle instance is the database name, and the following Easy Connect syntax can be used to connect to that instance:
SQLPLUS /nolog
SQL> CONNECT username@“host/db_name”
SQL> Enter password: password
Note:As of Oracle Database 10g, Oracle Database does not support the use of Oracle Names. Oracle Database 12c clients and databases cannot use Oracle Names, including those from an LDAP proxy, to resolve naming. Oracle9i clients can still use Oracle Names to resolve naming for an Oracle Database 12c database. However, customers are strongly encouraged to migrate to LDAP to take advantage of the new features of Oracle Database 12c.
See Also:
“Understanding the Easy Connect Naming Method” for additional information about this method
Parent topic: Quick Start to Oracle Net Services
6.6 Connecting to the Database
There are several methods for connecting to an Oracle database.
Table 6-1 Database Connection Methods and Syntax
Type of Connection Connection Syntax Description
From the command line
The general form of connecting an application to a database server from the command line is:
tool username@connect_identifier
You are prompted to enter your password which is encrypted.
For example:
SQLPLUS system@sales
Enter password: password
Most Oracle tools can use the operating system command line to connect, and some provide alternatives.
From a login screen
username@connect_identifier
Some tools provide a login screen as an alternative form to log in. A user can log in to a database server by identifying both the user name and connect identifier in the user name field of the tool login screen, and entering the password in the password field.
From a 3GL application
exec sql connect :username identified by :password
In the preceding connection request, :username and :password are 3GL variables that can be set within the program either statically or by prompting the user. When connecting to a database server, the value of the :username variable is in the form:
username@net_service_name
The :password variable contains the password for the database account to which you are connecting.
Applications written in 3GL, such as OCI and pre-compilers, are used by middle-tier and database application developers for direct database access from a client program.
From within SQL*Plus
SQLPLUS /nolog
SQL> CONNECT username@net_service_name
For example:
SQLPLUS /nolog
SQL> CONNECT scott@serverx
Enter password: password
In the preceding commands, username and password are the database user and password, and net_service_name is the network service name.
Some Oracle tools have commands for database connections to allow an alternative user name to be specified without leaving the tool.
Other Oracle tools use slightly different methods specific to their function or interface. For example, Oracle CDE tools use login buttons with fields for the user name, password, and remote database ID.
Using
KERBEROS5_CC_NAME parameter
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sales.example.com))
(SECURITY=(SQLNET.KERBEROS5_CC_NAME=/usr/tmp/krbcache))
)
Use this parameter to specify the complete path to the Kerberos credentials cache file for the Kerberos Principal (), when more than one Kerberos Principal needs to log in through the Database Client. user
If you are using Kerberos authentication to connect to a database, then specifying the complete path to the credential cache using the parameter is mandatory. For a single Kerberos Principal, you can specify the credential cache path in the file using the parameter. When a client needs to use more than one Kerberos Principal for making multiple database connections, specify parameter in either the string for individual connections or in the file. KERBEROS5_CC_NAMEsqlnet.oraSQLNET.KERBEROS5_CC_NAMEKERBEROS5_CC_NAMECONNECTtnsnames.ora
Using
KERBEROS5_PRINCIPAL parameter
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sales.example.com))
(SECURITY=
(KERBEROS5_CC_NAME=/tmp/krbuser1/krb.cc)
(KERBEROS5_PRINCIPAL=krbprinc1@example.com))
)
This parameter is used to specify Kerberos Principals for a database client.
This is an optional parameter. However, if you use this parameter, then ensure that the matches the Principal retrieved from the Kerberos credential cache, which is specified in the parameter. The authentication fails if the Principal name does not match. KERBEROS5_PRINCIPALKERBEROS5_CC_NAME
Using IAM database password authentication
SQLPLUS /nolog
SQL> CONNECT username@net_service_name
Enter password: password
In this connection request, username is the IAM user name, net_service_name is the network service name, and password is the IAM database password.
An IAM user can connect through a database proxy user account using password-based proxy authentication:
SQLPLUS [user1]/password123!@cdb1_pdb2
SQL> SHOW USER;
select sys_context(‘USERENV’,‘AUTHENTICATION_METHOD’) from dual;
select sys_context(‘USERENV’,‘PROXY_USER’) from dual;
select sys_context(‘USERENV’,‘CURRENT_USER’) from dual;
If you have configured Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) for user authentication and authorization, then IAM users can connect to an Oracle Autonomous Database using IAM user name and IAM database password. With this connection method, a database password verifier (an encrypted hash of password) is used from IAM to authenticate users.
After you are granted the required authorization, you can log in from any supported database client using on-premise client applications, such as SQL*Plus.
You can configure client connections (typically middle-tier environments) to use proxy authentication. In this case, you can alter an existing IAM user with necessary permissions to connect through a proxy database user account by using password-based proxy authentication. The proxy user session has all the privileges granted to the IAM user.
Using IAM token-based authentication
SQLPLUS /nolog
SQL> CONNECT /@connect_identifier
The setting enforces IAM token-based authentication: TOKEN_AUTH
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=sales_db)(PORT=1522))
(SECURITY=(SSL_SERVER_DN_MATCH=TRUE)(SSL_SERVER_CERT_DN=“C=US,O=example,CN=OracleContext”)(TOKEN_AUTH=OCI_TOKEN))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
You can use the parameter to override the default directory where the database token and private key are stored: TOKEN_LOCATION
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=sales_db)(PORT=1522))
(SECURITY=(SSL_SERVER_DN_MATCH=TRUE)(SSL_SERVER_CERT_DN=“C=US,O=example,CN=OracleContext”)(TOKEN_AUTH=OCI_TOKEN)(TOKEN_LOCATION="/home/dbuser1/.oci/db-token"))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
An IAM user can connect through a database proxy user account using token-based proxy authentication:
SQLPLUS [user1]/@pop_token_connstr
SQL> SHOW USER;
select sys_context(‘USERENV’,‘AUTHENTICATION_METHOD’) from dual;
select sys_context(‘USERENV’,‘PROXY_USER’) from dual;
select sys_context(‘USERENV’,‘CURRENT_USER’) from dual;
If you have configured IAM for user authentication and authorization, then IAM users can use a database token () to connect to an Oracle Autonomous Database. This token is a database access token with an expiration time. db-token
You can request the token from IAM using Oracle Cloud Infrastructure (OCI) Command Line Interface (CLI) or programmatically from the OCI Software Development Kit (SDK). You can use one of the IAM user credentials, such as API-key, security token, resource principal, instance principal, or delegation token (delegation token is available only in the Cloud Shell) to retrieve the and private key from IAM. db-token
A client application can send a connection request to the database client in one of the following ways:
If your client application is updated to work with IAM, then IAM directly passes the and private key to the database client using the database client API as attributes. db-token
When an IAM user logs in using , then the setting () instructs the database client to get the and private key files from either the default directory or the location specified by . /@connect_identifierTOKEN_AUTHTOKEN_AUTH=OCI_TOKENdb-tokenTOKEN_LOCATION
When an IAM user logs in using the IAM user name and IAM database password instead of , then the database client initiates the login without retrieving a token (using IAM database password authentication). /@connect_identifier
You can specify the parameter and optionally the parameter in the connect string or in the file. TOKEN_AUTHTOKEN_LOCATIONtnsnames.orasqlnet.ora
You can configure client connections to use proxy authentication. In this case, you can alter an IAM user with necessary permissions to connect through a proxy database user account by using token-based proxy authentication. The proxy user session has all the privileges granted to the IAM user.
Using Azure AD token-based authentication
SQLPLUS /nolog
SQL> CONNECT /@connect_identifier
The setting enforces Azure AD token-based authentication. You must also use the parameter to specify the directory path where the access token is stored. TOKEN_AUTHTOKEN_LOCATION
If the token file is named , then the client automatically looks for the file in the specified directory path (for example, ): token/home/dbuser1/access-token
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
(SECURITY=(SSL_SERVER_DN_MATCH=ON)(SSL_SERVER_CERT_DN=“C=US,O=example,CN=OracleContext”)(TOKEN_AUTH=OAUTH)(TOKEN_LOCATION="/home/dbuser1/access-token"))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
If the token file name is different from , then you must specify the file name (for example, ) along with the directory path: tokenmytoken
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcps)(HOST=salesserver1)(PORT=1522))
(SECURITY=(SSL_SERVER_DN_MATCH=ON)(SSL_SERVER_CERT_DN=“C=US,O=example,CN=OracleContext”)(TOKEN_AUTH=OAUTH)(TOKEN_LOCATION="/home/dbuser1/access-token/mytoken"))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))
)
If you have configured Microsoft Azure Active Directory (Azure AD) for user authentication and authorization, then Azure AD users or services can use the Azure AD OAuth2 access token to connect to an Oracle Autonomous Database. This access token is a bearer token with an expiration time and scope, and follows the OAuth2.0 standard with Azure AD extensions.
The setting () instructs the database client to get the access token from the directory specified by if the token file is named . Otherwise, you must use your token file name along with the directory location while specifying the parameter. TOKEN_AUTHTOKEN_AUTH=OAUTHTOKEN_LOCATIONtokenTOKEN_LOCATION
You can request these tokens from tools and scripts run on Linux, Microsoft PowerShell, or other environments. You can also request these tokens programmatically using the Microsoft SDKs.
An Azure AD user can request the access token from Azure AD using one of the supported Microsoft Azure AD authentication flows (resource owner password credentials, authorization code, or client credentials) and store it in a local file directory. If your client application is updated to retrieve access tokens from Azure AD, then the application can also request the access token directly from Azure AD and pass it to the database instance through a database client API as an attribute.
You can specify the and parameters in the connect string or in the file. TOKEN_AUTHTOKEN_LOCATIONtnsnames.orasqlnet.ora
Note:The parameter values specified in the connect string takes precedence over the values specified in the file. tnsnames.orasqlnet.ora
Related Topics
Authenticating and Authorizing IAM Users for Oracle Autonomous Databases
Authenticating and Authorizing Microsoft Azure Active Directory Users for Oracle Autonomous Databases
Oracle Database Net Services Reference
Using Oracle Autonomous Database on Shared Exadata Infrastructure
Parent topic: Quick Start to Oracle Net Services
