How to connect to Oracle Database 12C

Prior to connecting to your Oracle Database 12C, you must establish the required operating system variables. Additionally, if you’re going to run backup and recovery commands, you need access to either a privileged operating system (OS) account or a database user who has been granted the appropriate privileges (via a password file). These topics are discussed in the following subsections.

Establishing OS Variables

Before connecting to your database via SQL*Plus, RMAN, Data Pump (or any other Oracle utility), you must first set several OS variables:

• ORACLE_HOME
• ORACLE_SID
• LD_LIBRARY_PATH
• PATH

The ORACLE_HOME variable is important because it defines the starting point directory for locating the Oracle binary files (such as sqlplus, dbca, netca, rman, and so on) that are located in ORACLE_HOME/bin.

The ORACLE_SID (site identifier) variable defines the default name of the database you’ll connect to. ORACLE_SID is also used to establish the default name for the parameter file, which is init<ORACLE_SID>.ora or spfile<ORACLE_SID>.ora. By default, Oracle will look in ORACLE_HOME/dbs for these initialization files on Linux/Unix systems and ORACLE_HOME\database on Windows systems. The initialization file contains parameters that govern aspects of your database, such as how much memory to allocate to your database, the maximum number of connections, and so on.

The LD_LIBRARY_PATH variable is important because it specifies where to search for libraries on Linux/Unix boxes. The value of this variable is typically set to include ORACLE_HOME/lib.

The PATH variable specifies which directories are looked in by default when you type a command from the OS prompt. In almost all situations, ORACLE_HOME/bin (the location of the Oracle binaries) must be included in your PATH variable.

You can either manually set these variables or use a standard script provided by Oracle to set these variables.

Manually Setting Variables

In Linux/Unix, when you’re using the Bourne, Bash, or Korn shell, you can set OS variables manually from the OS command line with the following export command:

$ export ORACLE_HOME=/orahome/app/oracle/product/12.1.0.1/db_1

$ export ORACLE_SID=O12C

$ export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib

$ export PATH=$ORACLE_HOME/bin:$PATH

Note that the prior commands are for my particular development environment; you’ll need to adjust those to match the Oracle home and database name used in your environment.

For the C or tcsh shell, use the setenv command to set variables:

$ setenv ORACLE_HOME <path>

$ setenv ORACLE_SID <sid>

$ setenv LD_LIBRARY_PATH <path>

$ setenv PATH <path>

 Another way that DBAs set these variables is by placing the previous export or setenv commands into a Linux/Unix startup file, such as .bash_profile, .bashrc, or .profile. That way, the variables are automatically set upon login.

However, manually setting OS variables (either from the command line or by hard-coding values into a startup file) isn’t the optimal way to instantiate these variables. For example, if you have multiple databases with multiple Oracle homes on a box, manually setting these variables quickly becomes unwieldy and not very maintainable.

Using Oracle’s Script

A much better method for setting OS variables is use of a script that uses a file that contains the names of all Oracle databases on a server and their associated Oracle homes. This approach is flexible and maintainable. For instance, if a database’s Oracle home changes (e.g., after an upgrade), you only have to modify one file on the server and not hunt down where the Oracle home variables may be hard-coded into scripts.

Oracle provides a mechanism for automatically setting the required OS variables. This approach relies on two files: oratab and oraenv.

Understanding oratab

You can think of the entries in the oratab file as a registry of what databases are installed on a box and their corresponding Oracle home directories. The oratab file is automatically created for you when you install the Oracle software. On Linux boxes, oratab is usually placed in the /etc directory. On Solaris servers, the oratab file is placed in the /var/opt/oracle directory. If, for some reason, the oratab file isn’t automatically created, you can manually create it (with a text editor).

The oratab file is used in Linux/Unix environments for the following purposes:

• Automating the sourcing of required OS variables
• Automating the start and stop of Oracle databases on the server

The oratab file has three columns with this format:

<database_sid>:<oracle_home_dir>:Y|N

The Y or N indicates whether you want Oracle to restart automatically on reboot of the box; Y indicates yes, and N indicates no (the automatic restart feature requires additional tasks not covered in this article).

Comments in the oratab file start with a pound sign (#). Here is a typical oratab file entry:

O12C:/orahome/app/oracle/product/12.1.0.1/db_1:N
ORA12CR1:/orahome/app/oracle/product/12.1.0.1/db_1:N

The names of the databases on the previous lines are O12C and ORA12CR1. The path of each database’s Oracle home directory is next on the line (separated from the database name by a colon [:]).

Several Oracle-supplied utilities use the oratab file:

• oraenv uses oratab to set the OS variables.
• dbstart uses it to start the database automatically on server reboots (if the third field in oratab is Y).
• dbshut uses it to stop the database automatically on server reboots (if the third field in oratab is Y).

The oraenv tool is discussed in the following section.

Using oraenv

If you don’t properly set the required OS variables for an Oracle environment, then utilities such as SQL*Plus, RMAN, Data Pump, and so on won’t work correctly. The oraenv utility automates the setting of required OS variables (such as ORACLE_HOME, ORACLE_SID, and PATH) on an Oracle database server. This utility is used in Bash, Korn, and Bourne shell environments (if you’re in a C shell environment, there is a corresponding coraenv utility).

The oraenv utility is located in the ORACLE_HOME/bin directory. You’ll have to navigate to your ORACLE_HOME/bin directory first (you’ll have to modify the following path to match your environment):

$ cd /orahome/app/oracle/product/12.1.0.1/db_1/bin

 And then you can run oraenv manually, like this:

$ . ./oraenv

You’ll be prompted for ORACLE_SID (and if the ORACLE_SID isn’t in the oratab file, you’ll additionally be prompted for an ORACLE_HOME value):

ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ?

You can also run the oraenv utility non-interactively by setting OS variables before you run it. This is useful for scripting when you don’t want to be prompted for input:

$ export ORACLE_SID=O12C
$ export ORACLE_HOME=/orahome/app/oracle/product/12.1.0.1/db_1
$ export ORAENV_ASK=NO
$ cd /orahome/app/oracle/product/12.1.0.1/db_1/bin
$ . ./oraenv

---

Note  In Windows the operating system, variables are set in the registry.

---

You can verify that the OS variable settings with the echo command, for example:

$ echo $ORACLE_SID

O12C

$ echo $ORACLE_HOME

/orahome/app/oracle/product/12.1.0.1/db_1

After you’ve established your operating system variables, you need to connect to the database with the proper privileges. You can do this in one of two ways: using OS authentication or using a password file.

Using OS Authentication

Before you can connect to the Oracle database, you need to have the proper OS variables set (covered in the prior section). Additionally, if you want to connect to Oracle as a privileged user, then you must also have access to either a privileged OS account or a privileged database user. Connecting as a privileged user allows you to perform administrative tasks, such as starting and stopping a database. You can use either OS authentication or a password file to connect to your database as a privileged user.

The concept of a privileged user is also important to RMAN backup and recovery. RMAN uses OS authentication and password files to allow privileged users to establish a privileged database session (via the rman utility). Only a privileged account is allowed to back up, restore, and recover a database.

If your Linux/Unix account is a member of the dba group (your shop might use a different group name, but dba is the most common), you can connect to your database with the required privileges via SQL*Plus by virtue of being logged in to your Linux/Unix account.

On Windows, the OS user must be part of either the ora_dba group or the ora_oper group. In Windows environments, you can verify which OS users belong to the ora_dba group as follows: select Control Panel ⇒ Administrative Tools ⇒ Computer Management ⇒ Local Users and Groups Groups. You should see a group named something like ora_dba. You can click that group and view which OS users are assigned to it. Additionally, for OS authentication to work in Windows environments, you must have the following entry in your sqlnet.ora file:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

 On Linux/Unix, you can quickly verify the operating system groups that your account belongs to using the id command without any parameters:

$ id

uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmdba),
504(asmoper),505(asmadmin),506(backupdba)

he prior output indicates that the oracle user is included in several groups, one of which is dba. Any user who belongs to the dba group can connect to the database with SYSDBA privileges. A user with SYSDBA privileges can start and stop the database. This example uses OS authentication to connect to your database as the user SYS:

$ sqlplus / as sysdba

No username or password is required when using OS authentication (hence just the slash without a user/password) because Oracle first checks to see if the OS user is a member of a privileged OS group, and if so, connects without checking the username/password. You can verify that you have connected as SYS by issuing the following:

SQL> show user

USER is "SYS"

The privileged OS groups are established when installing the Oracle software. There are a few OS groups that pertain to backup and recovery:

• dba
• oper
• backupdba (available starting with Oracle 12c)

Each OS group corresponds to certain database privileges. Table 1 shows the mapping of OS groups to database system privileges and operations.

Table 1. Mapping of OS Groups to Privileges Related to Backup and Recovery

Using a Password File

If you aren’t using OS authentication, then you can use a password file to connect to the database as a privileged user. A password file allows you to do the following from SQL*Plus or RMAN:

• Connect to your database with sys* privileges as a non-SYS database user
• Connect to remote database (over the network) with sys* privileges

The password file must be manually created with the orapwd utility and is populated via the SQL grant command. To implement a password file, perform the following steps:

1. Create the password file with the orapwd utility.
2. Set the initialization parameter remote_login_passwordfile to exclusive.

In a Linux/Unix environment, use the orapwd utility to create a password file as follows:

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw$ORACLE_SID password=<sys password>

n a Linux/Unix environment, the password file is usually stored in the ORACLE_HOME/dbs directory, and in Windows, it’s typically placed in the ORACLE_HOME\database directory. The format of the file name that you specify in the previous command may vary by OS. For example, on Windows the format is PWD<ORACLE_SID>.ora. The following shows the syntax in a Windows environment:

c:\> cd %ORACLE_HOME%\database
c:\> orapwd file=PWD<ORACLE_SID>.ora password=<sys password>

To enable the use of the password file, set the initialization parameter remote_login_passwordfile to exclusive (this is the default value). You can verify its value as shown next:

SQL> show parameter remote_login_password

NAME                                 TYPE        VALUE
--------------------------           ------      ---------
remote_login_passwordfile            string      EXCLUSIVE

If need be, you can manually set the remote_login_passwordfile parameter as shown:

$ sqlplus / as sysdba

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

You will then need to stop and start your database for this parameter to take effect (more details on stopping/starting your database later in this chapter). The prior example assumes you are using a server parameter file (spfile). If you are not using a spfile, you will have to manually edit the init.ora file by adding this entry with a text editor:

remote_login_passwordfile=exclusive

Then stop and start your database to instantiate the parameter. Once the password file is enabled, you can then create database users and assign them the sys* privileges as required. For example, suppose you had a database user named DBA_MAINT that you wanted to grant SYSBACKUP privileges:

$ sqlplus / as sysdba

SQL> grant sysbackup to dba_maint;

The syntax for using a password file to connect to a database is as follows:

$ sqlplus <username>/<password>[@<db conn string>] as sys[dba|oper|backup]

For example, using the DBA_MAINT database user, you can connect to the database with SYSBACKUP privileges as follows:

$ sqlplus dba_maint/foo as sysbackup

Because you are providing a username/password and attempting to connect with a sys* level privilege (as a non-SYS user), Oracle will verify that a password file is in place (for the local database) and that the supplied username/password is in the password file. You can verify which users have sys* privileges by querying the V$PWFILE_USERS view:

SQL> select * from v$pwfile_users;

Here is some sample output:

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
-----------------              ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBA_MAINT                      FALSE FALSE FALSE TRUE  FALSE FALSE          0

OS AUTHENTICATION VS. PASSWORD FILE

For local connections (made while physically logged on to the database server), operating system authentication takes precedence over password file authentication. In other words, if you’re logged on to an OS account that is a member of an authenticated group, such as dba, it doesn’t matter what you type in for the username and password when connecting to a local database with sys* privileges. For example, you can connect as sysdba with a nonexistent username/password:

$ sqlplus bogus/wrong as sysdba

SQL> show user;

USER is "SYS"

The prior connection works because Oracle ignores the username/password provided, as the user was first verified via OS authentication. However, a password file is used when you’re not using OS authentication to establish a privileged local connection or when you’re trying to make a privileged connection to a remote database via the network.

One key aspect about using a password file is that this is the mechanism that allows you to use SQL*Plus or RMAN to connect to a remote database over the network with sys* privileges. For example, if you want to connect to a user named chaya with a password of heera to a remote database named HATHI with sysdba privileges, you would do as follows:

$ sqlplus chaya/heera@HATHI as sysdba

Oracle will verify that the username password combination exists in a password file on the remote server that is associated with the database defined by the HATHI net service name. In this example, Oracle uses the information in a local tnsnames.ora file to determine the location of the database on the network (host, port, and database).

---

Tip  Using a local tnsnames.ora file is known as the local naming connection method. There are other remote database name resolution methods, such as easy connect, directory naming, and external naming. See the Oracle Database Net Services Administrator’s Guide for details on how to implement these.

---

EASY CONNECT

The easy connect method allows you to connect to a remote database without the need of a tnsnames.ora file (or other methods of resolving the location of the database). If you know the name of the host, server, port, and service name, you can directly enter those on the command line. The syntax is as follows:

sqlplus username@[//]host[:port][/service_name][:server][/instance_name]

 For example, assuming the host name is hesta, the port is 1521, and the service name is O12C, then you can connect as follows:

$ sqlplus user/pass@hesta:1521/O12C

The easy connect method is handy for situations in which you’re troubleshooting connectivity issues or when you don’t have a tnsnames.ora file available (or other ways to resolve the remote connection).

Вас заинтересует / Intresting for you:

Oracle Database and Instance d... 3481 views Masha Thu, 21 Jun 2018, 18:23:39

Stopping the Oracle Database 1... 2123 views Андрей Волков Sat, 29 Feb 2020, 10:19:28

Oracle Database and Security: ... 2084 views sepia Mon, 26 Nov 2018, 12:20:58

Oracle Database 12C Backup: un... 2701 views Андрей Волков Sat, 29 Feb 2020, 10:15:04