Catalog Maintenance   «Prev 

RMAN syntax

Recovery Manager Syntax


Connecting to Your Database

Problem

You are new to Oracle and wonder how to connect to your database via SQL*Plus so that you can perform basic commands, such as starting and stopping your database and enabling archivelog mode.

Solution

Before you connect to an Oracle database, you must establish operating system variables before connecting to the database, and you also need access to either a privileged OS account or a database user granted the appropriate privileges (via a password file). These topics are discussed in the following subsections.

Establishing OS Variables

The OS environment variables are usually set when you log on to your database server. DBAs typically set these variables automatically in a startup file (such as .bashrc on Linux/Unix). Minimally, you need to set:
  1. ORACLE_SID to the name of your target database
  2. ORACLE_HOME to the parent directory where you installed the Oracle RDBMS software (binaries)
  3. PATH to include ORACLE_HOME/bin
I n a Linux/Unix environment, Oracle provides an oraenv script for the Korn/Bash/Bourne shells and coraenv for the C shell to set the required OS variables. For example, you can run the oraenv script from the operating system prompt as follows:

$ .oraenv
You will be prompted for the name of your database. You can verify the settings of these variables as follows:
$ echo $ORACLE_SID
$ echo $ORACLE_HOME
$ echo $PATH

If the need arises, you can override these settings by establishing OS environment variables from the command line. Here is an example of manually establishing these variables in a Linux/Unix environment:
$ export ORACLE_SID=o12c
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH

Keep in mind the prior code is just an example. You will need to modify the prior commands to match the target ORACLE_SID and ORACLE_HOME in your environment.
Note: In Windows the operating system variables are set in the registry.
After you have 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

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. 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)
The 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"