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 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_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
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.
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
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
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/220.127.116.11/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
.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:
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).
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:
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:
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:
oraenvuses oratab to set the OS variables.
dbstartuses it to start the database automatically on server reboots (if the third field in
dbshutuses it to stop the database automatically on server reboots (if the third field in
The oraenv tool is discussed in the following section.
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
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/18.104.22.168/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_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/22.214.171.124/db_1 $ export ORAENV_ASK=NO $ cd /orahome/app/oracle/product/126.96.36.199/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/188.8.131.52/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
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:
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
Operating System Group
Database System Privilege
Start up, shut down, alter database, create and drop database, toggle archivelog mode, back up, and recover database.
Install and upgrade Oracle binaries.
Start up, shut down, alter database, toggle archivelog mode, back up, and recover database.
Available starting with Oracle 12c, this privilege allows you to start up, shut down, and perform all backup and recovery operations.
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
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:
- Create the password file with the
- Set the initialization parameter
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
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:
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
$ 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
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.orafile 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.
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:
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).