Beginning with the release of Oracle8i, SQL*Plus has been enhanced to allow you to perform several administrative functions that previously required the use of Server Manager. Using SQL*Plus, you can now perform the following tasks:
Start up or shut down an Oracle instance
Turn archive log mode on and off
View memory usage of the System Global Area (SGA)
Look at the settings for various initialization parameters
Initiate media recovery for a database
Server Manager is still around, at least in the initial release of Oracle8i, but Oracle’s strategy is to make SQL*Plus the sole command-line interface to Oracle. Look for Server Manager to be desupported in some future release.
In order to start up, shut down, change archive log settings, or recover an Oracle database, you must log into the database in a way that is different from your normal approach. Usually you connect as yourself, but to do many of the tasks described in this chapter, you need to log in either as an operator or as a database administrator. Section 10.1.3, later in this chapter, shows you how to do this. In addition to logging in correctly, you must have the system privileges needed for the task you are performing. These are described next.
To perform administrative tasks, you need to have been granted one of the following two roles:
| SYSOPER |
| SYSDBA |
Exactly what you can do depends on which of the two roles has been granted to you. The SYSOPER role was created for computer operators, who, in the absence of a database administrator, may need to perform such tasks as starting, stopping, and backing up the database. A user with the SYSOPER role is limited to the following commands:
| STARTUP |
| SHUTDOWN |
| ALTER DATABASE OPEN |
| ALTER DATABASE MOUNT |
| ALTER DATBASE BACKUP |
| ARCHIVE LOG |
| RECOVER |
The SYSDBA role, on the other hand, is intended for database administrators. There are no restrictions on what a user with SYSDBA can do.
These roles are granted at the database level, just like any other role, unless you are using operating-system authentication. In that case, the operating system roles OSOPER and OSDBA correspond to SYSOPER and SYSDBA, respectively.
When you connect to a database instance, Oracle needs a way to authenticate you as a valid user. Normally Oracle does this by looking at the DBA_USERS table to see if the password you have supplied matches the one stored in the database. This works fine when the database is up and running, but what if it’s not? Most users do not connect to a database when it’s closed. After all, what would be the point? Database administrators, however, need to be able to start and stop the database, as well as perform other administrative tasks. That means they need to be able to connect and be authenticated, even when the database is closed. The dilemma is that usernames and passwords are stored in the database and cannot be accessed when it’s closed.
To resolve this dilemma of needing to authenticate database administrators when the database is closed, Oracle resorts to the method of storing their usernames and passwords in a file. This file is referred to as the password file, and is encrypted for obvious reasons. Any users who have been granted the SYSDBA or SYSOPER privileges have their passwords stored in this file as well as within the database.
Tip
The INTERNAL password is also stored in the password file. See Section 10.1.3.2.
Whenever you connect using one of the keywords SYSDBA, SYSOPER, or INTERNAL, Oracle validates your password using the password file, not the database. You can read more about this file, including how to create and manage it, in Chapter 1 of the Oracle8 Administrator’s Guide.
To perform an administrative task, such as starting the database, you must connect to Oracle in your capacity as a database administrator. You do this by using the CONNECT command with the AS keyword to tell Oracle what role you intend to play. For example, if your username were RAYMOND, you would issue a command such as the following to connect in your role as an operator:
CONNECT raymond/my_secret AS SYSOPER;
This use of the AS keyword can be confusing to people, especially when they are first introduced to the subject. What exactly does it mean to connect “as” something? Many people take it to mean that you are really connecting as another user, but that’s not quite the case. Take a look at the following command, and assume that you are the user named RAYMOND:
CONNECT raymond/good_man;
In this case, you are connecting to Oracle as yourself. This is easy enough to understand. You are attached to your own schema and see all of your own objects. If you are a database administrator, you may even have a number of system privileges that let you perform many administrative tasks without connecting as SYSOPER or SYSDBA.
When you connect AS a role, AS SYSOPER, for example, you are connected to Oracle and associated with the SYS schema. Why couldn’t you just connect as user SYS in the first place? Because then every DBA would need to know the password for SYS. Sharing passwords is not a good security practice. Also, Oracle needs to be able to determine whether you should have SYSOPER privileges or SYSDBA privileges. Your username is key to making that determination.
The bottom line is this. When you connect to Oracle AS something, think of it as connecting to Oracle in your special capacity as a database administrator, or as an operator. You are connecting to perform some administrative task outside the scope of what you typically do.
In order to connect to Oracle in one of the administrative roles, you must run SQL*Plus and connect to the database using a special form of the CONNECT command, as shown in the following example:
C:>SQLPLUS /NOLOGSQL*Plus: Release 8.1.3.0.0 - Beta on Tue Oct 20 18:46:21 1998 (c) Copyright 1998 Oracle Corporation. All rights reserved. SQL>CONNECT raymond/good_man AS SYSDBAConnected.
The /NOLOG option is used on the SQL*Plus command line so that SQL*Plus doesn’t attempt to connect you as a normal user before you have a chance to connect as an operator or administrator. This is particularly necessary if the database is shut down and starting it is the reason you are connecting in the first place. As you can also see from the example, a special form of the CONNECT command is used. The syntax for it looks like this:
CONNECT [logon[@service]] ASrole;
where:
- logon
If you are using operating-system authentication, this should simply be a forward slash ( / ) character. Otherwise, this should be your username and password, in the normal username/password notation.
- service
Is a SQL*Net or NET8 service name, and should match an entry in your
TNSNAMESfile.- role
The role must be either SYSDBA or SYSOPER, depending on which you have.
When you connect like this, Oracle authenticates you using the Oracle password file. That way you can be authenticated as a privileged user even when the database is not open, which is important if you need to start the database.
If you are running on a Windows system, you may want to create a special shortcut to SQL*Plus that has /NOLOG as a command option. The Target field of the shortcut would look like this:
C:ORAWIN95BINSQLPLUSW.EXE /NOLOG
The exact path and executable name may vary depending on the specific versions of Windows and Oracle you are running. This will give you an icon to use when you need to start SQL*Plus without automatically connecting to the database. Once you’ve done that, you can use the CONNECT command as described earlier to connect as SYSDBA or SYSOPER.
Another option for performing database administration tasks is to connect using the INTERNAL keyword. This option exists mainly for backwards compatibility with previous versions of Oracle, and normally shouldn’t be used. However, it can be helpful, and sometimes necessary. It is possible to have a database where no user has SYSDBA or SYSOPER. It is always possible to connect as INTERNAL. Here is the command to use:
CONNECT INTERNAL[/password]In some cases, you may not need to supply a password when connecting internally. Not all DBAs configure their systems to require an internal password. With an operating system such as Unix, the DBA, or the Oracle user, may have operating-system privileges that allow him to connect internally. If you do need to supply a password, it is authenticated against the one stored in your database’s password file.