Oracle7 Server Administrator's Guide Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

The Oracle7 Database Administrator

This chapter describes the responsibilities of the person who administers the Oracle7 Server, the database administrator.

The following topics are included:

Types of Oracle7 Users

At your site, the types of users and their responsibilities may vary. For example, at a large site the duties of a database administrator might be divided among several people.

This section includes the following topics:

Database Administrators

Because an Oracle7 database system can be quite large and have many users, someone or some group of people must manage this system. The database administrator (DBA) is this manager. Every database requires at least one person to perform administrative duties.

A database administrator's responsibilities can include the following tasks:

Security Officers

In some cases, a database might also have one or more security officers. A security officer is primarily concerned with enrolling users, controlling and monitoring user access to the database, and maintaining system security. You might not be responsible for these duties if your site has a separate security officer.

Application Developers

An application developer designs and implements database applications An application developer's responsibilities include the following tasks:

Application Administrators

An Oracle site might also have one or more application administrators. An application administrator is responsible for the administration needs of a particular application.

Database Users

Database users interact with the database via applications or utilities. A typical user's responsibilities include the following tasks:

Network Administrators

At some sites there may be one or more network administrators. Network administrators may be responsible for administering Oracle7 networking products, such as SQL*Net.

See Also: "Network Administration" in Oracle7 Server Distributed Systems, Volume I.

Database Administrator Security and Privileges

To accomplish administrative tasks in Oracle7, you need extra privileges both within the database and possibly in the operating system of the server on which the database runs. Access to a database administrator's account should be tightly controlled.

This section includes the following topics:

See Also: "Administrator Security" [*].

The Database Administrator's Operating System Account

To perform many of the administrative duties for a database, you must be able to execute operating system commands. Depending on the operating system that executes Oracle7, you might need an operating system account or ID to gain access to the operating system. If so, your operating system account might require more operating system privileges or access rights than many database users require (for example, to perform Oracle7 software installation). Although you do not need the Oracle7 files to be stored in your account, you should have access to them.

In addition, the Server Manager program requires that your operating system account or ID be distinguished in some way to allow you to use operating system privileged Server Manager commands.

See Also: The method of distinguishing a database administrator's account is operating system-specific. See your operating system-specific Oracle documentation for information.

Database Administrator Usernames

Two user accounts are automatically created with the database and granted the DBA role. These two user accounts are:

These two usernames are described in the following sections.

Note: To prevent inappropriate access to the data dictionary tables, you must change the passwords for the SYS and SYSTEM usernames immediately after creating an Oracle7 database.

You will probably want to create at least one additional administrator username to use when performing daily administrative tasks.


When any database is created, the user SYS, identified by the password CHANGE_ON_INSTALL, is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle7. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle7; they should never be modified by any user or database administrator, and no one should create any tables in the schema of the user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Most database users should never be able to connect using the SYS account. You can connect to the database using this account but should do so only when instructed by Oracle personnel or documentation.


Also when a database is created, the user SYSTEM, identified by the password MANAGER, is automatically created and granted all system privileges for the database.

The SYSTEM username creates additional tables and views that display administrative information, and internal tables and views used by Oracle tools. Never create tables of interest to individual users in the SYSTEM schema.

See Also: "Altering Users" [*].

"Changing Storage Parameters for the Data Dictionary" [*].

"Administrator Security" [*].

The DBA Role

A predefined role, named "DBA", is automatically created with every Oracle7 database. This role contains all database system privileges. Therefore, it is very powerful and should only be granted to fully functional database administrators.

Database Administrator Authentication

Database administrators must often perform special operations such as shutting down or starting up a database. Because these operations should not be performed by normal database users, the database administrator usernames need a more secure authentication scheme.

This section includes the following topics:

Selecting an Authentication Method

The following methods for authenticating database administrators replace the CONNECT INTERNAL syntax provided with earlier versions of the Oracle7 Server (CONNECT INTERNAL continues to be supported for backwards compatibility only):

Depending on whether you wish to administer your database locally on the same machine that the database resides or if you wish to administer many different databases from a single remote client, you can choose between operating system authentication or password files to authenticate database administrators. Figure 1 - 1 illustrates the choices you have for database administrator authentication schemes.

Figure 1 - 1. Database Administrator Authentication Methods

On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group (on UNIX systems, this is the DBA group) or giving that OS username a special process right.

The database uses password files to keep track of database usernames that have been granted administrator privileges.

See Also: "User Authentication" in Oracle7 Server Concepts.

Using Operating System Authentication

If you choose, you can have your operating system authenticate users performing database administration operations.

To Use Operating System Authentication


If you successfully connect as INTERNAL using an earlier release of Oracle7, you should be able to continue to connect successfully using the new syntax shown in step 3.

Note: Note that to connect as SYSOPER or SYSDBA using OS authentication you do not have to have been granted the SYSOPER or SYSDBA system privileges. Instead, the server verifies that you have been granted the appropriate OSDBA or OSOPER roles at the operating system level.

See Also: "Operating System Authentication" [*].


Two special operating system roles control database administrator logins when using operating system authentication: OSOPER and OSDBA.




Contains all system privileges with ADMIN OPTION, and the OSOPER role; permits CREATE DATABASE and time-based recovery.

OSOPER and OSDBA can have different names and functionality, depending on your operating system.

The OSOPER and OSDBA roles can only be granted to a user through the operating system. They cannot be granted through a GRANT statement, nor can they be revoked or dropped. When a user logs on with administrator privileges and REMOTE_LOGIN_PASSWORDFILE is set to NONE, Oracle7 communicates with the operating system and attempts to enable first OSDBA and then, if unsuccessful, OSOPER. If both attempts fail, the connection fails. How you grant these privileges through the operating system is operating system-specific.

If you are performing remote database administration, you should consult your SQL*Net documentation to determine if you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure, regardless of which version of SQL*Net you are using.

See Also: For information about OS authentication of database administrators, see your operating system-specific Oracle documentation.

Using an Authentication Password File

If you have determined that you need to use a password file to authenticate users performing database administration, you must complete the steps outlined below. Each of these steps is explained in more detail in the following sections of this chapter.

To Use a Password File to Authenticate Users

	ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users



See Also: "OSOPER and OSDBA" [*].

Some platforms provided support for password files before release 7.1. If you are currently using such a password file, you should consult your operating system-specific Oracle documentation for additional information on migrating to the new password file utility.

Password File Administration

You can create a password file using the password file creation utility, ORAPWD or, for selected operating systems, you can create this file as part of your standard installation.

This section includes the following topics:

See Also: See your operating system-specific Oracle documentation for information on using the installer utility to install the password file.


When you invoke the password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:

> orapwd
Usage: orapwd file=<fname> password=<password> entries=<users>
    file - name of password file (mand),
    password - password for SYS and INTERNAL (mand),
    entries - maximum number of distinct DBAs and OPERs (opt),
  There are no spaces around the equal-to (=) character.

For example, the following command creates a password file named ACCT.PWD that allows up to 30 privileged users with different passwords. The file is initially created with the password SECRET for users connecting as INTERNAL or SYS:


Following are descriptions of the parameters in the ORAPWD utility.

FILE This parameter sets the name of the password file being created. You must specify the full pathname for the file. The contents of this file are encrypted, and the file is not user-readable. This parameter is mandatory.

On some platforms, the name of the password file is derived from the system identifier (SID). If so, you must specify this predefined name when using the ORAPWD utility. On other systems the name of the password file is stored in an environment variable, such as ORA_sid_PWFILE. If your system uses an environment variable, you must set this variable on the server platform to match the pathname that you specified for the file before starting the instance.

If you are running multiple instances of Oracle7 using the Oracle7 Parallel Server, the environment variable for each instance should point to the same password file.

Warning: It is critically important to the security of your system that you protect your password file, and environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

PASSWORD This parameter sets the password for INTERNAL and SYS. If you issue the ALTER USER command to change the password after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. The INTERNAL user is supported for backwards compatibility only. This parameter is mandatory.


This parameter sets the maximum number of entries allowed in the password file. This corresponds to the maximum number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. Entries can be reused as users are added to and removed from the password file. This parameter is required if you ever want this password file to be EXCLUSIVE.

Warning: If you ever need to exceed this limit, you must create a new password file. It is safest to select a number larger than you think you will ever need.

See Also: Consult your operating system-specific Oracle documentation for the exact name of the password file, or for the name of the environment variable used to specify this name for your operating system.


In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are described below.

Note: To STARTUP an instance or database, you must use Server Manager. You must specify a database name and a parameter file to initialize the instance settings. You may specify a fully-qualified remote database name using SQL*Net. However, the initialization parameter file and any associated files, such as a configuration file, must exist on the client machine. That is, the parameter file must be on the machine where you are running Server Manager.

NONE Setting this parameter to NONE causes Oracle7 to behave as if the password file does not exist. That is, no privileged connections are allowed over non-secure connections. NONE is the default value for this parameter.

EXCLUSIVE An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYS and INTERNAL. Using an EXCLUSIVE password file allows you to grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.

SHARED A SHARED password file can be used by multiple databases. However, the only users recognized by a SHARED password file are SYS and INTERNAL; you cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

Suggestion: To achieve the greatest level of security, you should set the REMOTE_LOGIN_PASSWORDFILE file initialization parameter to EXCLUSIVE immediately after creating the password file.

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information is added to the password file. If the server does not have an EXCLUSIVE password file, that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, you receive an error message if you attempt to grant these privileges.

A user's name only remains in the password file while that user has at least one of these two privileges. When you revoke the last of these privileges from a user, that user is removed from the password file.

To Create a Password File and Add New Users to It

	CONNECT SYS/change_on_install AS SYSDBA

Granting and Revoking SYSOPER and SYSDBA Privileges

If your server is using an EXCLUSIVE password file, use the GRANT command to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:


Use the REVOKE command to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:


Because SYSDBA and SYSOPER are the most powerful database privileges, the ADMIN OPTION is not used. Only users currently connected as SYSDBA (or INTERNAL) can grant SYSDBA or SYSOPER system privileges to another user. This is also true of REVOKE. These privileges cannot be granted to roles, since roles are only available after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles, which are a completely independent feature.

Listing Password File Members

Use the V$PWFILE_USERS view to determine which users have been granted SYSDBA and SYSOPER system privileges for a database. The columns displayed by this view are as follows:


The name of the user that is recognized by the password file.


If the value of this column is TRUE, the user can log on with SYSDBA system privileges.


If the value of this column is TRUE, the user can log on with SYSOPER system privileges.

Connecting with Administrator Privileges

When you connect with SYSOPER or SYSDBA privileges using a username and password, you are connecting with a default schema of SYS, not the schema that is generally associated with your username.

Use the AS SYSDBA or AS SYSOPER clauses of the Server Manager CONNECT command to connect with administrator privileges.

Connecting with Administrator Privileges: Example

For example, assume user SCOTT has issued the following commands:

CONNECT scott/tiger
CREATE TABLE scott_test(name VARCHAR2(20));

Later, when SCOTT issues these commands:

SELECT * FROM scott_test;

He receives an error that SCOTT_TEST does not exist. That is because SCOTT now references the SYS schema by default, whereas the table was created in the SCOTT schema.

Non-Secure Remote Connections

To connect to Oracle7 as a privileged user over a non-secure connection, you must meet the following conditions:

Local and Secure Remote Connections

To connect to Oracle7 as a privileged user over a local or a secure remote connection, you must meet either of the following sets of conditions:

Consult your operating system-specific Oracle documentation for details on operating system authentication.

See Also: "Password File Administration" [*].

Maintaining a Password File

This section describes how to expand, relocate, and remove the password file, as well as how to avoid changing the state of the password file.

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users.

To Replace a Password File

Relocating the Password File

After you have created the password file, you can relocate it as you choose. After relocating the password file, you must reset the appropriate environment variables to the new pathname. If your operating system uses a predefined pathname, you cannot change the password file location.

Removing a Password File

If you determine that you no longer need to use a password file to authenticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After removing this file, only users who can be authenticated by the operating system can perform database administration operations.

Warning: Do not remove or modify the password file if you have a database or instance mounted using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE (or SHARED). If you do, you will be unable to reconnect remotely using the password file. Even if you replace it, you cannot use the new password file, because the timestamp and checksums will be wrong.

Changing the Password File State

The password file state is stored in the password file. When you first create a password file, its default state is SHARED. You can change the state of the password file by setting the parameter REMOTE_LOGIN_PASSWORDFILE. When you STARTUP an instance, Oracle7 retrieves the value of this parameter from the initialization parameter file stored on your client machine. When you mount the database, Oracle7 compares the value of this parameter to the value stored in the password file. If these values do not match, the value stored in the file is overwritten.

Warning: You should use caution to ensure that an EXCLUSIVE password file is not accidentally changed to SHARED. If you plan to allow instance STARTUP from multiple clients, each of those clients must have an initialization parameter file, and the value of the parameter REMOTE_LOGIN_PASSWORDFILE must be the same in each of these files. Otherwise, the state of the password file could change depending upon where the instance was started.

Database Administrator Utilities

Several utilities are available to help you maintain and control the Oracle7 Server.

The following topics are included in this section:

Server Manager

Server Manager allows you to monitor and control an Oracle7 database. All administrative operations discussed in this book are executed using Server Manager. Server Manager has both GUI (Graphical User Interface) and line mode interfaces.

Server Manager uses a superset of ANSI/ISO standard SQL commands. The most common administrative commands are available in the menus of Server Manager/GUI. Commands used less frequently can be typed into a Server Manager SQL Worksheet and executed.

See Also: Oracle Server Manager User's Guide.


SQL*Loader is used by both database administrators and users of Oracle7. It loads data from standard operating system files (files in text or C data format) into Oracle7 database tables.

See Also: Oracle7 Server Utilities.

Export and Import

The Export and Import utilities allow you to move existing data in Oracle7 format to and from Oracle7 databases. For example, export files can archive database data, or move data among different Oracle7 databases that run on the same or different operating systems.

See Also: Oracle7 Server Utilities.

Initial Priorities of a Database Administrator

In general, you must perform a series of steps to get the database system up and running, and then maintain it.

The following steps are required to configure an Oracle7 Server and database on any type of computer system. The following sections include details about each step.

To Configure an Oracle7 Server

Note: If migrating to a new release, back up your existing production database before installation. For more information on preserving your existing production database, see Chapter 1 of the Oracle7 Server Migration.

Step 1: Install the Oracle7 Software

As the database administrator, you must install the Oracle7 Server software and any front-end tools and database applications that access the database. In some distributed processing installations, the database is controlled by a central computer and the database tools and applications are executed on remote machines; in this case, you must also install the Oracle7 SQL*Net drivers necessary to connect the remote machines to the computer that executes Oracle7.

See Also: "Identifying Oracle Software Releases" [*].

For specific requirements and instructions for installation, see your operating system-specific Oracle documentation and your installation guides for your front-end tools and SQL*Net drivers.

Step 2: Evaluate the Database Server Hardware

After installation, evaluate how Oracle7 and its applications can best use the available computer resources. This evaluation should reveal the following information:

Step 3: Plan the Database

As the database administrator, you must plan:

It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many data files will make up the tablespace, where the data files will be physically stored (on which disk drives), and what type of information will be stored in each tablespace. When planning the database's overall logical storage structure, take into account the effects that this structure will have when the database is actually created and running. Such considerations include how the database's logical storage structure will affect the following items:

Plan the relational design of the database's objects and the storage characteristics for each of these objects. By planning relationships between objects and the physical storage of each object before creating it, you can directly impact the performance of the database as a unit. Be sure to plan for the growth of the database.

In distributed database environments, this planning stage is extremely important. The physical location of highly accessed data can dramatically affect application performance.

During the above planning phases, also plan a backup strategy for the database. After developing this strategy, you might find that you want to alter the database's planned logical storage structure or database design to improve backup efficiency.

It is beyond the scope of this book to discuss relational and distributed database design; if you are not familiar with such design issues, refer to accepted industry-standard books that explain these studies. See Chapters 9 through 17 for specific information on creating logical storage structures, objects, and integrity constraints for your database.

Step 4: Create and Open the Database

Once you have finalized the database design, you can create the database and open it for normal use. Depending on your operating system, a database may already have been created during the installation procedure for Oracle7. If so, all you need to do is start an instance, and mount and open the initial database.

To determine if your operating system creates an initial database during the installation of Oracle7, check your installation or user's guide. If no database is created during installation or you want to create an additional database, see Chapter 2 for this procedure. See Chapter 3 for database and instance startup and shutdown procedures.

Step 5: Implement the Database Design

Once you have created and started the database, you can create the database's planned logical structure by creating all necessary rollback segments and tablespaces. Once this is built, you can create the objects for your database.

See Chapters 8 through 17 for instructions on creating logical storage structures and objects for your database.

Step 6: Back up the Database

After you have created the database structure, carry out the planned backup strategy for your database by creating any additional redo log files, taking the first full database backup (online or offline), and scheduling future database backups at regular intervals.

See Chapters 22 through 24 for instructions on customizing your backup operations and performing recovery procedures.

Step 7: Enroll System Users

Once you have backed up the database structure, you can begin to enroll the users of the database in accordance with your Oracle7 license agreement, create roles for these users, and grant appropriate roles to them.

See Chapters 18 through 20 for the procedures to create user accounts and roles, and information on complying with your license agreement.

Step 8: Tune Database Performance

Optimizing the database system's performance is one of your ongoing responsibilities.

See Also: "Initial Tuning Guidelines" [*] describes steps you can take to start tuning your database immediately after creation.

Oracle7 Server Tuning manual, for information about tuning your database and applications.

Identifying Oracle Software Releases

Because Oracle products are always undergoing development and change, several releases of the products can be in use at any one time. To identify a software product fully, as many as five numbers may be required.

This section includes the following topics:

Release Number Format

An Oracle7 Server distribution tape might be labeled "Release" The following sections translate this number.

Figure 1 - 2. Example of an Oracle7 Release Number

Version Number

The version number, such as 7, is the most general identifier. A version is a major new edition of the software, which usually contains significant new functionality.

Maintenance Release Number

The maintenance release number signifies different releases of the general version, starting with 0, as in version 7.0. The maintenance release number increases when bug fixes or new features to existing programs become available.

Patch Release Number

The patch release number identifies a specific level of the object code, such as 7.0.4. A patch release contains fixes for serious bugs that cannot wait until the next maintenance release. The first distribution of a maintenance release always has a patch number of 0.

Port-Specific Patch Release Number

A fourth number (and sometimes a fifth number) can be used to identify a particular emergency patch release of a software product on that operating system, such as or An emergency patch is not usually intended for wide distribution; it usually fixes or works around a particular, critical problem.

Examples of Release Numbers

The following examples show possible release numbers for Oracle7:

7.0.0 the first distribution of Oracle7
7.1.0 the first maintenance release of Oracle7
7.2.0 the second maintenance release (the third release in all) of Oracle7
7.2.2 the second patch release after the second maintenance release

Versions of Other Oracle Software

As Oracle Corporation introduces new products and enhances existing ones, the version numbers of the individual products increment independently. Thus, you might have an Oracle7 Server Release system working with Oracle Forms Version 4.0.3, SQL*Plus Version 3.1.9, and Pro*FORTRAN Version 1.5.2. (These numbers are used only for illustration.)

Checking Your Current Release Number

To see which release of Oracle and its components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION, as shown below (This information is useful if you need to call Oracle Support.):

SVRMGR> SELECT * FROM product_component_version;
PRODUCT                    VERSION             STATUS
-------------------------- ------------------- ------------
CORE                        Production
NLSRTL                      Production
Oracle7 Server              Beta Release
PL/SQL                      Beta
TNS for SunOS:              Production
5 rows selected.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index