Connecting to a DB System

This topic explains how to connect to an active DB system. How you connect depends on the client tool or protocol you use, the purpose of the connection, and how your cloud network is set up. You can find information on various networking scenarios in Networking Overview, but for specific recommendations on how you should connect to a database in the cloud, contact your network security administrator.

Prerequisites

This section describes prerequisites you'll need to perform various tasks in this topic.

  • To use the Console or the API to get the default administration service connection strings, you must be given the required type of access in a policy  written by an administrator, whether you're using the Console or the REST API with an SDK, CLI, or other tool. If you try to perform an action and get a message that you don’t have permission or are unauthorized, confirm with your administrator the type of access you've been granted and which compartment  you should work in. See Authentication and Authorization for more information on user authorizations for the Oracle Cloud Infrastructure Database service.
  • To connect to the database, you'll need the public or private IP address of the DB system.

    Use the private IP address to connect to the system from your on-premises network, or from within the virtual cloud network (VCN). This includes connecting from a host located on-premises connecting through a VPN or FastConnect to your VCN, or from another host in the same VCN. Use the Exadata system's public IP address to connect to the system from outside the cloud (with no VPN). You can find the IP addresses in the Oracle Cloud Infrastructure Console as follows:

    • Cloud VM clusters (new resource model): On the Exadata VM Cluster Details page, click Virtual Machines in the Resources list.
    • DB systems: On the DB System Details page, click Nodes in the Resources list.

    The values are displayed in the Public IP Address and Private IP Address & DNS Name columns of the table displaying the Virtual Machines or Nodes of the Exadata Cloud Service instance.

  • For Secure Shell (SSH) access to the DB system, you'll need the full path to the file that contains the private key associated with the public key used when the DB system was launched.

If you have problems connecting, see Troubleshooting Connection Issues.

Database Services and Connection Strings

Database services allow you to control client access to a database instance depending on the functionality needed. For example, you might need to access the database for administration purposes only or you might need to connect an application to the database. Connection strings are specific to a database service.

When you provision a DB system, a default database administration service is automatically created. For 12c and later Oracle Databases, this service is for administrating the database at the CDB level. Because this service provides limited functionality, it is not suitable for connecting an application. Oracle recommends that you create a default application service for the initial database after you create your DB system. For 12c and later Oracle Databases, application services connect at the PDB level. Here are some important functions an application service can provide:

  • Workload identification
  • Load balancing
  • Application continuity and Transaction Guard
  • Fast Application Notification
  • Resource assignment based on the service name

For details about these and other High Availability capabilities, see Client Failover Best Practices for Highly Available Oracle Databases.

Creating an Application Service

You use the srvctl utility to create an application service. Before you can connect to the service, you must start it.

To create an application service for a PDB or an 11g Oracle database
  1. Log in to the DB system host as opc.
  2. Switch to the oracle user, and set your environment to the Oracle Database you want to administer.

    $ sudo su - oracle
    $ . oraenv
    ORACLE_SID = [oracle] ? <database_name>
    The Oracle base has been set to /u01/app/oracle
    
  3. Create the application service for the database. Include the pdb option only if you are creating an application service for a PDB.

    $ srvctl add service
    -db <DB_unique_name>
    -pdb <PDB_name>
    -service <app_service_name>
    -role PRIMARY 
    -notification TRUE 
    -session_state dynamic 
    -failovertype transaction 
    -failovermethod basic 
    -commit_outcome TRUE 
    -failoverretry 30
    -failoverdelay 10
    -replay_init_time 900
    -clbgoal SHORT 
    -rlbgoal SERVICE_TIME 
    -preferred <rac_node1>,<rac_node2> 
    -retention 3600
    

    Note that the preferred option is required only for multi-node databases to specify the hostname of the node in the RAC.

  4. Start the application service.

    $ srvctl start service -db <DB_unique_name> -s <app_service_name>

For more information about services for a PDB, see Managing Services for PDBs.

Database Connection Strings

You must use the appropriate connection string to access a database administration or application service. You can use the Console or the API to get the string for connecting to the default administration service from within a VCN. For 12c and later Oracle Databases, this service is for administrating the database at the CDB level. The string is provided in both the Easy Connect and in the full connect descriptor (long) format. Use the long format for the connection if hostname resolution is not available. You can also use the long format to create an alias in the tnsnames.ora file.

For accessing a database service within the VCN, the connection string for a Real Application Cluster (RAC) DB system uses the Single Client Access Name (SCAN) while the connection string for single instance DB system uses the hostname instead.

The private SCAN name is a Round Robin DNS entry created when you launch a 2-node RAC DB system. The private SCAN name is resolvable only within the VCN. If the client and the database are in the same VCN, the connection mechanism is the same as an on-premises RAC database; all the features provided by VIPs and SCAN VIPs, such as server side load balancing and VIP failover, are available.

Note

If you manually change the DB_UNIQUE_NAME, DB_DOMAIN, or listener port on the DB system, the connection strings you see in the Console or API will not reflect your changes. Ensure that you use the actual values of these parameters when you make a connection.
To get the connection strings for the default administration service
  1. Open the navigation menu. Under Oracle Database, click Bare Metal, VM, and Exadata.
  2. Choose your Compartment.

  3. Find the DB system you're interested in, and click the name.

  4. Click DB Connection.

  5. Click the applicable link to view or copy the connection string.

You can derive the connection strings for other database services by replacing part of the default application service connection string with the applicable values.

To derive the connection string for a PDB administration service or an application service
  1. Follow the procedure to get the Easy Connect string for the default administration service. That string should have the following format:

    <hostname|SCAN>:1521/<DB_unique_name>.<DB_domain>
  2. Make the appropriate substitution:

    • For the PDB administration service, replace DB_unique_name with the PDB name.

      <hostname|SCAN>:1521/<PDB_name>.<DB_domain>
    • For an application service, replace DB_UNIQUE_NAME with the name of the application service.

      <hostname|SCAN>:1521/<app_service_name>.<DB_domain>

Connecting to a Database Service by Using SQL*Net

This section describes how to connect to a database service from a computer that has a SQL*Net client installed. Port 1521 must be open to support the SQL*Net protocol.

Connecting from Within the VCN

For security reasons, Oracle recommends that you connect to your database services from within the VCN. You can use this method whether you are connecting to an administration service or to an application service.

To connect using SQL*Plus, you run the following command using the applicable connection string:

sqlplus system/<password>@<connection_string>

Consider the following:

  • If your system is not using the VCN Resolver, ensure that the DB system's hostname (for single-node systems) or SCAN name (for multi-node systems) can be resolved. See DNS in Your Virtual Cloud Network for information about DNS name resolution.
  • For connecting to the administration service of a PDB, ensure that the PDB is open or the service will not be available.
  • For connecting to an application service, ensure that the service is started. For Fast Application Notification to work, ensure that port 6200 can be reached. See Client Failover Best Practices for Highly Available Oracle Databases for information about Fast Application Notification.

Connecting from the Internet

Although Oracle does not recommend connecting to your database from the Internet, you can connect to a database service by using a public IP address if port 1521 is open to the public for ingress.

To use this method, you run the following command using the public IP address instead of the hostname or SCAN in the connection string:

sqlplus system/<password>@<public_IP>:1521/<service_name>.<DB_domain>

Consider the following:

  • SCANs and hostnames are not resolvable on the Internet, therefore load balancing and failover for multi-node DB systems, which rely on these names, cannot work.
  • For multi-node DB systems, which normally use SCANs, you must specify the IP address of one of the RAC hosts to access the database.
Important

Do not use this method to connect to the database from within the VCN. Doing so negatively impacts performance because traffic to the database is routed out of the VCN and back in through the public IP address.

Example: Connecting in SQL Developer Using SQL*Net

Prerequisites:

  • Ensure that port 1521 is open for the Oracle default listener. (You can do this by checking the DB system's security list.)
  • If port 1521 is open only to hosts in the VCN, then you must run your SQL Developer client from a machine that has direct access to the VCN. If you are connecting to the database from the Internet instead, then the public IP address of your computer must be granted access to port 1521 in the security list. (Alternatively, the security list can grant full access to port 1521, however, this is not recommended for security reasons.) You must use the public IP address of the host because connecting from the Internet does not support SCAN name resolution.
To connect from within the VCN using a private IP address

After the prerequisites are met, start SQL Developer and create a connection by supplying the following connection details:

  • Username: sys as sysdba
  • Password: The Database Admin Password that was specified in the Launch DB System dialog in the Console.
  • Hostname: The hostname as it appears in the Easy Connect format of the connection string. (See Database Connection Strings for help with getting the connection string and identifying the hostname.)
  • Port: 1521
  • Service name: The concatenated name of the service and host domain name, for example, db1_phx1tv.example.com. You can identify this value as the last part of the Easy Connect string, <service_name>.<DB_domain>.

Connecting to a Database with a Public IP by Using SSH Tunneling

You can access the services of DB system databases with public IP addresses by using SSH tunneling. The main advantage of this method is that port 1521 does not need to be opened to the public internet. However, just like accessing the database with a public IP using a SQL*Net client, load balancing and failover for multi-node DB systems cannot work because they rely on SCANs and hostnames.

Oracle SQL Developer and Oracle SQLcL and are two tools that facilitate the use of tunneling for Oracle Database access.

To open a tunnel, and then connect to a database service by using SQLcL, you run commands like the following:

SQL> sshtunnel opc@<public_IP> -i <private_key> -L <local_port>:<private_IP>:1521
Using port:22
SSH Tunnel connected
SQL> connect system/<password>@localhost:<local_port>/<service_name>.<DB_domain>

See Oracle SQL Developer and Oracle SQLcL for information about these tools.

Connecting to a Database by Using SSH and the Bequeath Protocol

This method allows you to connect to the database without using the network listener. It should be used to connect only for administration purposes.

When connecting to a multi-node DB system, you'll SSH to each individual node in the cluster.

To connect from a UNIX-style system

Use the following SSH command to access the DB system: $ ssh –i <private_key> opc@<DB_system_IP_address>

<private_key> is the full path and name of the file that contains the private key associated with the DB system you want to access.

Use the DB system's private or public IP address depending on your network configuration. For more information, see Prerequisites.

To connect from a Windows system
  1. Open putty.exe.
  2. In the Category pane, select Session and enter the following fields:

    • Host Name (or IP address): opc@<DB_system_IP_address>

      Use the DB system's private or public IP address depending on your network configuration. For more information, see Prerequisites.

    • Connection type: SSH
    • Port: 22
  3. In the Category pane, expand Connection, expand SSH, and then click Auth, and browse to select your private key.
  4. Optionally, return to the Session category screen and save this session information for reuse later.
  5. Click Open to start the session.
To access a database after you connect
  1. Log in as opc and then sudo to the grid user.

    
    login as: opc
    
    [opc@ed1db01 ~]$ sudo su - grid
    
  2. List all the databases on the system.

    root@ed1db01 ]# srvctl config database -v
    
    cdbm01 /u02/app/oracle/product/12.1.0/dbhome_2 12.1.0.2.0
    exadb /u02/app/oracle/product/11.2.0/dbhome_2 11.2.0.4.0
    mmdb /u02/app/oracle/product/12.1.0/dbhome_3 12.1.0.2.0
  3. Connect as the oracle user and get the details about one of the databases by using the srvctl command.

    [root@ed1db01 ~]# su - oracle
    [oracle@ed1db01 ~]$ . oraenv
    ORACLE_SID = [oracle] ? cdbm01
    The Oracle base has been set to /u02/app/oracle
    [oracle@ed1db01 ~]$ srvctl config database -d cdbm01
    Database unique name: cdbm01 <<== DB unique name
    Database name:
    Oracle home: /u02/app/oracle/product/12.1.0/dbhome_2
    Oracle user: oracle
    Spfile: +DATAC1/cdbm01/spfilecdbm01.ora
    Password file: +DATAC1/cdbm01/PASSWORD/passwd
    Domain: data.customer1.oraclevcn.com
    Start options: open
    Stop options: immediate
    Database role: PRIMARY
    Management policy: AUTOMATIC
    Server pools:
    Disk Groups: DATAC1,RECOC1
    Mount point paths:
    Services:
    Type: RAC
    Start concurrency:
    Stop concurrency:
    OSDBA group: dba
    OSOPER group: racoper
    Database instances: cdbm011,cdbm012 <<== SID
    Configured nodes: ed1db01,ed1db02
    Database is administrator managed
  4. Set the ORACLE_SID and ORACLE_UNIQUE_NAME using the values from the previous step.

    
    [oracle@ed1db01 ~]$ export ORACLE_UNIQUE_NAME=cdbm01
    [oracle@ed1db01 ~]$ export ORACLE_SID=cdbm011
    [oracle@ed1db01 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 19 04:10:12 2017
    
    Copyright (c) 1982, 2014, Oracle. All rights reserved.
    
    Connected to:
    Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
    OLAP, Advanced Analytics and Real Application Testing options

Troubleshooting Connection Issues

The following issues might occur when connecting to a DB system or database.

ORA-28365: Wallet is Not Open Error

For a 1-node DB system or 2-node RAC DB system, regardless of how you connect to the DB system, before you use OS authentication to connect to a database (for example, sqlplus / as sysdba) be sure to set the ORACLE_UNQNAME variable. Otherwise, commands that require the TDE wallet will result in the error ORA-28365: wallet is not open.

Note that this is not an issue when using a TNS connection because ORACLE_UNQNAME is automatically set in the database CRS resource.

SSH Access Stops Working

If the DB system’s root volume becomes full, you might lose the ability to SSH to the system (the SSH command will fail with permission denied errors). Before you copy a large amount of data to the root volume, for example, to migrate a database, use the dbcli create-dbstorage command to set up storage on the system’s NVMe drives and then copy the database files to that storage. For more information, see Setting Up Storage on the DB System.

What Next?

Before you begin updating your DB system, review the information in Updating a DB System.

For information about setting up an Enterprise Manager console to monitor your databases, see Monitoring a Database.