Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10.1)
Part Number B10739-01
Go to Documentation Home
Home		 Go to Book List
Book List< /a>		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
 View PD F

1< /span> Overview of Administering an Oracle Database

This chapter presents an over view of the environment and tasks of an Oracle Database administrator (DBA). It also discusses DBA security and how you obtain the ne cessary administrative privileges.

The following topics are discussed:

Types of O racle Database Users

The types of users and their roles and responsibilities depe nd on the database site. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people and among sever al areas of specialization.

This section contains the following topics:

Database Administrators

Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-pers on job, but a job for a group of DBAs who share responsibility.

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

  • Installing and upgrading the Oracle Database server and application tools

  • Allocating system storage and planning future storage requirements for the databas e system

  • Creating primary database storage structures (tablespaces) after application developers have de signed an application

  • Creating primary objects (tables, views, indexes) once application developers have designed an application

  • Modifying the database structure, as necessary, from information given by appli cation developers

  • Enrolling users and maintaining system security

  • Ensuring compliance with Oracle license agreements

  • Controlling and monitoring user access to the database

  • Monitoring and optimizing the performance of the database

  • Planning for backup and recovery of database information

  • Maintaining archived data on tape

  • Backing up and restoring the database

  • Contacting Oracle for technical support

Security Officers

In some cases, a site assigns one or more security officers to a database. A security officer enrolls users, controls and monitors user access to the database, and maintains system security. As a DB A, you might not be responsible for these duties if your site has a separate security officer. Please refer to Oracle Database Security Guide for information about the duties of security officers.

Network Administrators

Some sites have one or mor e network administrators. A network administrator, for example, administers Oracle networking products, such as Oracle Net Services. Please refer to Oracle Net Services Administrator's Guide< /a> for information about the duties of network administrators.


See Also:

Part VII, " Distributed Database Management ", for information on network administration in a distributed environment

Application Developers

Application developers d esign and implement database applications. Their responsibilities include the following tasks:

  • Designing and developing the database application

  • Designing the database structure for an application

    • Estimating storage requirements for an application

  • Specifying modifications of the dat abase structure for an application

  • Relaying this information to a database administrator

  • Tuning the application during development

  • Establishing security measures for an applicatio n during development

Application developers can perform some of these tasks in collaboration with DBAs. Please refer to Oracle Database Application Developer's Guide - Fundamentals for information about application development tasks.

Application Administrators

An Oracle Database site can assign one or more application administrators to administer a particular a pplication. Each application can have its own administrator.

Database User s

Database users interact with the database through applications or utilities. A typical user's responsibilities inclu de the following tasks:

  • Entering, modifying, and deleting data, where permitted

  • Generating reports from the data

Tasks of a Database Administrator

The following tasks present a prioritized a pproach for designing, implementing, and maintaining an Oracle Database:

Task 1: Evaluate the Database Ser ver Hardware

Task 2: Install the Oracle Database Software

Task 3: Plan the Database

Task 4: Create and Open the Database

Task 5: Back Up the D atabase

Task 6: Enroll System Users

Task 7: Implement the Database Desi gn

Task 8: Back Up the Fully Functional Database

Task 9: Tune Database Performance

These tasks are discussed in the sections that follow.


Note:

When upgrading to a new release, back up your existing production environm ent, both software and database, before installation. For information on preserving your existing production database, see Oracle Database Upgrade Guide.

Task 1: Evaluate the Database Server Hardware

Evaluat e how Oracle Database and its applications can best use the available computer resources. This evaluation should reveal the following information:

  • How many disk drives are available to the Oracle products

  • How many, if any, dedicated tape drives are available to Oracle products

  • How much memory is available to th e instances of Oracle Database you will run (see your system configuration documentation)

Task 2: Install the Oracle Database Software

As the database administrator, you install the Oracle Database server software and any front-end tools and database applications that access the database. In some distributed proce ssing installations, the database is controlled by a central computer (database server) and the database tools and applications are e xecuted on remote computers (clients). In this case, you must also install the Oracle Net components necessary to connect the remote machines to the computer that executes Oracle Database.

For more information on what software to install, see "Identifying Your Oracle Database Software Release".


See Also:

For specific requirements and instructions for installation, refer to the followi ng documentation:

  • The Oracle documentation specific to your operating system

  • T he installation guides for your front-end tools and Oracle Net drivers


Task 3: Plan the Database

As the database administrator, you must plan:

  • The logical storage structure of the database

  • The overall database design

  • A backup strategy for the data base

It is important to plan how the logical storage structure of the database will affect system performance and va rious database management operations. For example, before creating any tablespaces for your database, you should know how many datafi les will make up the tablespace, what type of information will be stored in each tablespace, and on which disk drives the datafiles w ill be physically stored. When planning the overall logical storage of the database structure, take into account the effects that thi s structure will have when the database is actually created and running. Consider how the logical storage structure of the database w ill affect:

  • The performance of the computer executing running Oracle Database

  • The performance of the database during data access operations

  • The efficiency of backup and recovery p rocedures for the database

Plan the relational design of the database objects and the storage characteristics for ea ch of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly a ffect the performance of the database as a unit. Be sure to plan for the growth of the database.

In distributed database envir onments, this planning stage is extremely important. The physical location of frequently accessed data dramatically affects applicati on performance.

During the planning stage, develop a backup strategy for the database. You can alter the logical storage struc ture or design of the database to improve backup efficiency.

It is beyond the scope of this book to discuss relational and dis tributed database design. If you are not familiar with such design issues, please refer to accepted industry-standard documentation.< /p>

Part II, " Oracle Database Structure and Storage ", and Part IV, " Schema Objects", provide specific information on creating logical storage structures, objects, and integrity constraints for you r database.

Task 4: Create and Open the Database

After you complete the database design, you can create the database and open it for normal use. You can create a database at installation time, using the Database Configuration Assistant, or you can supply your own scripts for creating a database.

Please refer to Chapter 2, " Creating an Oracle Database", for information on creating a database and Chapter 3, " Starting Up and Shutting Down " for guidance in starting up the database.

Task 6: Enroll System U sers

After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, and grant appropriate privileges and roles to these users. Please refer to Chapter 22, " Managing Users and Securing the Database" for guidance in this task.

Task 7: Implement the Database Design

After you create and start the database, and enroll the system users, you c an implement the planned logical structure database by creating all necessary tablespaces. When you have finished creating tablespace s, you can create the database objects.

Part II, " Oracle Database Structure and Storage " and Part IV, " Schema Objects" provide information on creating logical storage structures and objects fo r your database.

Task 8: Back Up the Fully Functional Database

When the database is fully implemented, again back up the database. In addition to regularly scheduled backups, you should always back up your database immediately after implementing changes to the database structure.

Task 9: Tune Database Performance

Optimizing the performance of the database is one of your ongoing res ponsibilities as a DBA. Oracle Database provides a database resource management feature that helps you to control the allocation of r esources among various user groups. The database resource manager is described in Chapter 24, " Using the Database Resource Manager".


See Al so:

Oracle Database Performance Tuning Guide for information about tuning your database and applications

Identifying Your Oracle Database S oftware Release

Because Oracle Database continues to evolve and can require maintenance, Oracle periodically produces new releases. Not all customers initially subscribe to a new release or require specific maintenance for their existing release. As a result, multiple releases of the product exist simultaneously.

As many as five numbers may be required to fully identify a re lease. The significance of these numbers is discussed in the sections that follow.

Release Number Forma t

To un derstand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0. 1.0".

Figure 1-1 Example of an Oracle Database Release Number

Description of admin002.gif follows
Description of the illustration admin002.gif


Note:

Starting with release 9.2, maintenance releases of Oracle Database are denoted by a change to the second digit of a r elease number. In previous releases, the third digit indicated a particular maintenance release.

Major Database Release Number

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Numbe r

The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (Oracle AS).

Component-Specific Release Number

The fourth digit identifies a release level specific to a c omponent. Different components can have different numbers in this position depending upon, for example, component patch sets or inter im releases.

Platform-Specific Release Number

The fifth digit identifies a platform-specific relea se. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the af fected platforms.

Checking Your Current Rele ase Number

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the data base server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT
_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- -----------
 -----------
NLSRTL                                   10.1.0.2.0  Production
Oracle Database 10g Enterprise Edition   10.1.0.2
.0  Prod
PL/SQL                                   10.1.0.2.0  Production
...

It is important to convey to Oracle the resu lts of this query when you report problems with the software.

Database Administrator Security and Privileges

To perform the administrative tasks of an Oracle Database DBA, you need specific privileges 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 contains the following topics:

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 on which Oracle Database is running, you might need an operating system account or ID to gain access to the operating system. If so, your operating system account might require operating system privileges or access rights that other database users do not requ ire (for example, to perform Oracle Database software installation). Although you do not need the Oracle Database files to be stored in your account, you should have access to them.


See Also:

Your operating system specific Oracle documentation. The method of creating the account of th e database administrator is specific to the operating system.

< /div>
< h3>Database Administrator Usernames

Two user accounts are automatically created when Oracle Database is installed:

  • SYS (default password: CHANGE_ON_INSTALL)

  • SYSTEM (default password: MANAGER)



    Create at least one additional administrative user and grant to that u ser an appropriate administrative role to use when performing daily administrative tasks. Do not use SYS and SYSTE M for these purposes.


    Note:

    Both Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) now p rompt for SYS and SYSTEM passwords and do not accept the default passwords "change_on_install" or "manager" , respectively.

    If you create the database manually, Oracle strongly recommends that you specify passwords for SYS a nd SYSTEM at database creation time, rather than using these default passwords. Please refer to "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM" for more information.

    Note Regarding S ecurity Enhancements:

    In this release of Oracle Database and in subsequent releases, several enhancements are bei ng made to ensure the security of default database user accounts. You can find a security checklist for this release in Oracle Database Security Guide. Oracle recommends that you read this checklist and configure your database accordingly.

    < div class="sect3">

    SYSTEM< /h4>

    When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

    The SYSTEM username is used to create additional tables and views t hat display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use th e SYSTEM schema to store tables of interest to nonadministrative users.

    The DBA Role

    A predefined DBA role is automatically created with every Oracle Database installation. This role conta ins most database system privileges. Therefore, the DBA role should be granted only to actual database administrators.


    Note:

    The DBA role does not include the SYSDBA or SYSOPER system privileges. These are special administrative privileges that allow an administrat or to perform basic database administration tasks, such as creating the database and instance startup and shutdown. These system priv ileges are discussed in "Administrative Privileges".

D atabase Administrator Authentication

As a DBA, you often perform special operations such as shutting down or starting up a database. Because only a DBA should perform these operations, the database administrator usernames require a secure authenticati on scheme.

This section contains the following topics:

Administrative Privileges

Admini strative privileges that are required for an administrator to perform basic database operations are granted through two special syste m privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.


Note:

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

The SYSDBA and S YSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for wh ich privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connec t to the database by specifying CONNECT AS SYSDBA.


SYSDB A and SYSOPER

The following operations are authorized by the SYSDBA and SYSOPER system privileges:

System Privilege Operations Authorized
SYSDBA
  • Perform STARTUP and SHUTDOWN operations

  • ALTER DATABASE: open, mount, back up, or change character set

  • C REATE DATABASE

  • DROP DATABASE

  • CREATE SPFILE

  • ALTER DATABASE ARCHIVELOG

  • ALTER DATABASE RECOVER

  • Includes the RESTRICTED SESSION privilege

Effectively, this system privil ege allows a user to connect as user SYS.

SYSOPER
  • Perform STARTUP and SHUTDOWN operations

  • CREATE SPFILE

  • ALT ER DATABASE OPEN/MOUNT/BACKUP

  • ALTER DATABASE ARCHIVELOG

  • ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL |CONTROLFILE requires connecting as SYSDBA.)

  • Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.


The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.

When you connect with SYSDBA or S YSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

Connecting with Administrative Privileges: Example

This example illustrates that a user is assigned another schema (< code>SYS) when connecting with the SYSDBA system privilege. Assume that the sample user oe has been granted the SYSDBA system privilege and has issued the following statements:

CONNECT oe/oe
CREATE TABLE
 admin_test(name VARCHAR2(20));

Later, user oe issues these statements:

CONNEC
T oe/oe AS SYSDBA
SELECT * FROM admin_test;

User oe now receives the following error:

ORA-00942: table or view does not exist

Having connected as SYSDBA, user oe now refere nces the SYS schema, but the table was created in the oe schema.

Selecting an Authentication Method

The following methods are available for authenticating database administrators:

  • Operating system (OS) authentica tion

  • A password file


    Notes:

    • These methods replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle Database. CONNECT INTERNAL is no longer supported.

      < /li>

    • Operating system authentication takes precedence over password file authentication. If you meet the requirem ents for operating system authentication, then even if you use a password file, you will be authenticated by operating system authent ication.


Your choice will be infl uenced by whether you intend to administer your database locally on the same machine where the database resides, or whether you inten d to administer many different databases from a single remote client. Figure 1-2 illustrates the choices you have for database administrator authentication schemes.

Figure 1-2 Database Administrator Authentication Meth ods

Description of admin001.gif follows
Description of the illustration admin001.gif

If you are performing remote database administration, consult your Oracle Net documentation to determine whether you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.

Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password fi le authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in "Using Password File Aut hentication".

Local Connections and Secure Remote Connections

You can connect to Oracle Databa se as a privileged user over a local connection or a secure remote connection in two ways:

  • If the databa se has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can conne ct and be authenticated by a password file.

  • If the server is not using a password file, or if you have n ot been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operat ing system authentication. On most operating systems, authentication for database administrators involves placing the operating syste m username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.

Using Operating System Authentication

This section describes how to authenticate an administrator using the operating system.

Preparing to Use Operating System Authentication

To enable operating system authentication of an admin istrative user:

  1. Create an operating system account for the user.

  2. Add the user to the OSDBA or OSOPER operating system defined groups.

  3. Ensure that the initialization param eter, REMOTE_LOGIN_PASSWORDFILE, is set to NONE, the default.

Connecting Usin g Operating System Authentication

A user can be authenticated, enabled as an ad ministrative user, and connected to a local database by typing one of the following SQL*Plus commands:

CONNECT / AS SYSDBA
CONNECT / AS SYSOPER

For a remote database connection over a secure connection, the user must also sp ecify the net service name of the remote database:

CONNECT /@net_service_name AS SYSDBA
CONNEC
T /@net_service_name AS SYSOPER

See Also:

SQL*Plus User's Guide and Re ference for syntax of the CONNECT command

OSDBA and OSOPER

< a id="sthref97" name="sthref97">Two special operating system groups control database administrator connections when using operati ng system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The specific names vary depending upon your operating system and are listed in th e following table:

Operating System Group UNIX Windows
OSDBA dba ORA_DBA
OSOPER oper ORA_OPER

The default names assumed by the Oracle Universal Installer c an be overridden. How you create the OSDBA and OSOPER groups is operating system specific.

Membership in the OSDBA or OSOPER g roup affects your connection to the database in the following ways:

  • If you are a member of the OSDBA gro up and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.

  • If you are a member of the OSOPER group and you specify AS SYSOPER w hen you connect to the database, then you connect to the database with the SYSOPER system privilege.

  • If you are not a member of either of these operating system groups, the CONNECT command fails.


    See Also:

    Your operating system sp ecific Oracle documentation for information about creating the OSDBA and OSOPER groups

Using P assword File Authentication

This section describes how to authenticate an administrative user using password file auth entication.

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

  1. Create an operating system account for the user.

  2. If not already created, create the passwor d file using the ORAPWD utility:

    ORAPWD FILE=filename PASSWORD=password
ENTRIES=max_users

  3. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXC LUSIVE.

  4. Connect to the database as user SYS (or as another user with the administrative privileg es).

  5. If the user does not already exist in the database, create the user. Grant the SYSDBA or SYS OPER system privilege to the user:

    GRANT SYSDBA to oe;

    This statement adds the user to the password file, thereby enabling connection AS SYSDBA.


    See Also:

    "Creating and Maintaining a Passwor d File" for instructions for creating and maintaining a password file

Connecting Using Password File Authentication

Administrative users can b e connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect usi ng their username and password and the AS SYSDBA or AS SYSOPER clause. For example, user oe ha s been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe
/oe AS SYSDBA

However, user oe has not been granted the SYSOPER privilege, so the following com mand will fail:

CONNECT oe/oe AS SYSOPER

Note:

Operating system authentication takes precedence over password file authentic ation. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify.

I f you are not in the OSDBA or OSOPER groups, and you are not in the password file, then the connection will fail.



See Also:

SQL*Plus User's Gui de and Reference for syntax of the CONNECT command

Creating and Maintaining a Password File

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

This section con tains the following topics:

Using ORA PWD

When you invoke this 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> pas
sword=<password> entries=<users>
where
file - name of password file (mand),
password - password for SYS (mand),
entries -
 maximum number of distinct DBAs and OPERs (opt),
There are no spaces around the equal-to (=) character.

The following co mmand creates a password file named acct.pwd that allows up to 30 privileged users with different passwords. In this exa mple, the file is initially created with the password secret for users connecting as SYS.

orapwd FILE=acct.pwd PASSWORD=secret ENTRIES=30

The parameters in the ORAPWD utility are descri bed in the sections that follow.

FILE
< dd>

This parameter sets the name of the password file being created. You must specify the full path name for the file. The content s of this file are encrypted, and the file cannot be read directly. This parameter is mandatory.

The types of filenames allowe d for the password file are operating system specific. Some operating systems require the password file to adhere to a specific forma t and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and loca tion of the password file. See your operating system documentation for the names and locations allowed on your platform.

If yo u are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instanc e should point to the same password file.


Cau tion:

It is critically important to the security of your system that you protect your password file and the envir onment variables that identify the location of the password file. Any user with access to these could potentially compromise the secu rity of the connection.

PASSWORD

This parameter sets the password for user SYS. If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both t he password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.


Note:

You cannot change the p assword for SYS if REMOTE_LOGON_PASSWORDFILE is set to SHARED. An error message is issued if y ou attempt to do so.

ENTRIES

This parameter specifies the number of entries that you require th e password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORA PWD utility continues to assign password entries until an operating system block is filled. For example, if your operating sys tem block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGON_PAS SWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this param eter is required.


Caution:

When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

Setting REMOTE_LOGIN_ PASSWORDFILE< /font>

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:

  • < code>NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist . That is, no privileged connections are allowed over nonsecure connections. NONE is the default value for this paramete r.

  • EXCLUSIVE: An EXCLUSIVE password file can be used with only one database. O nly an EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE passwo rd file lets you grant SYSDBA and SYSOPER system privileges to individual users and have them connect as th emselves.

  • SHARED: A SHARED password file can be used by multiple databases run ning on the same server. However, the only user recognized by a SHARED password file is SYS. You cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must c onnect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple dat abases.


Suggestion:

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

< /a>

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are a dded to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parame ter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED) Oracle Database issue an error if you attempt to grant these privileges.

A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.


Creating a Password File and Adding New Users to It

Use the following procedure to create a password and add new users to it:

  1. Follow the instructions for creating a pa ssword file as explained in "Using ORAPWD".

  2. Set the REMOTE_LOGIN_PASSWORDFILE i nitialization parameter to EXCLUSIVE.

  3. Connect with SYSDBA privileges as shown in the follo wing example:

    CONNECT SYS/password AS SYSDBA

  4. Start up the instance and c reate the database if necessary, or mount and open an existing database.

  5. Create users as necessary. Grant SYSD BA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoki ng SYSDBA and SYSOPER Privileges".

Granting the SYSDBA or SYSOPER privilege to a user causes that user's username to be added to the password file. This enables the user to connect to the database as SYSDBA or SYSOPER by specifying username and password (instead of using SYS). The use of a password file does not prevent operating system authenticated users from connecting if they meet the criteria for operating system authentication.

Granting and Revoking SYSDBA and SYSOPER Privileges

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

GRANT SYSDBA TO oe;

U se the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe;

Because SYSDBA and SYS OPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT state ment. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a use r currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER system pr ivileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.


< /tbody>

See Also:

Oracle Database Security Guide for more information on system privileges

Viewing Password File Members

Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSO PER system privileges for a database. The columns displayed by this view are as follows:

Column Description USERNAME This column contains the name of the user that is recognized by the password file. SYSDBA If the v alue of this column is TRUE, then the user can log on with SYSDBA system privileges. SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.

Maintaining a Password File

This section describes how to:

  • Expand the number of password file users if the password file becomes full

  • Remove the password file

  • Avoid changing the state of the password file

Expanding the Number of Password File Users

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


Replacing a Password File

Use the following procedure to replace a password file:

  1. Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.

  2. Shut down the database.

  3. Delete the existing password file.

  4. Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORA PWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.

  5. < p>Follow the instructions in "Adding Users to a Password File".

Removing a Pa ssword File

If you determine that you no longer require a password file to auth enticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform database ad ministration operations.


Caution:

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

< /div>

Changing the Password File State

The password file state is stored in the password file. When you first create a pa ssword file, its default state is SHARED. You can change the state of the password file by setting the initialization pa rameter REMOTE_LOGIN_PASSWORDFILE. When you start up an instance, Oracle Database retrieves the value of this parameter from the parameter file used by the instance. When you mount the database, the database compares the value of this parameter to the v alue stored in the password file. If the values do not match, the database overwrites the value stored in the file.


Caution:

Ensure that an EXCLUSIVE password file is not accidentally changed to SHARED. If you plan to allow instance startup from multiple clients, e ach 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 w as started.

Server Manageability

Oracle Database is a sophist icated self-managing database that automatically monitors, adapts, and repairs itself. It automates routine DBA tasks and reduces the complexity of space, memory, and resource administration. Several advisors are provided to help you analyze specific objects. The ad visors report on a variety of aspects of the object and describe recommended actions. Oracle Database proactively sends alerts when a problem is anticipated or when any of the user-selected metrics.

In addition to its self-managing features, Oracle Database p rovides utilities to help you move data in and out of the database.

This section describes these server manageability topics:< /p>

< a id="sthref137" name="sthref137">

Automatic Manageability Feature s

Oracle Database has a self-management infrastructure that allows the database to learn about itself and use this inf ormation to adapt to workload variations and to automatically remedy any potential problem. This section discusses the automatic mana geability features of Oracle Database.

Automatic Workload Repo sitory

Automatic Workload Repository (AWR) is a built-in repository in every Oracle Database. At regular intervals, th e database makes a snapshot of all its vital statistics and workload information and stores them in AWR. By default, the snapshots ar e made every 30 minutes, but you can change this frequency. The snapshots are stored in the AWR for a period of time (seven days by d efault) after which they are automatically purged.


See Also:


Automatic Maintenance Tasks

Oracle Database uses the information store d in AWR to identify the need to perform routine maintenance tasks, such as optimizer statistics refresh and rebuilding indexes. Then the database uses the Scheduler to run such tasks in a predefined maintenance window.

Server-Generated Alerts

Some pro blems cannot be resolved automatically and require the database administrator's attention. For these problems, such as space shortage , Oracle Database provides server-generated alerts to notify you when then problem arises. The alerts also provide recommendations on how to resolve the problem.


See Also:< /strong>

"Server-Generated Alerts" in this book for detailed information on using APIs to administer server-generated alerts

Advisors

Oracle Database provides advisors to help you optimize a number of sub systems in the database. An advisory framework ensures consistency in the way in which advisors are invoked and results are reported. The advisors are use primarily by the database to optimize its own performance. However, they can also be invoked by administrators to get more insight into the functioning of a particular subcomponent.


See Also:


Data Util ities

Several utilities are available to help you maintain the data in your Oracle Database. This section introduces two of these utilities:

SQL*Loader

SQL*Loader is used both by database administrators and by other users of Oracle Database. It loads data from standard operating system files (such as, files in text or C data format) into dat abase tables.

Export and Import Utilities

Oracle export and import utilities enable you to move existing data in Oracle format between one Oracle Database and another. For example, export files can archive database data or move data among different databases that run on the same or different operati ng systems.

Go to previous page
Previous		 Go to next page
Next
 Ora cle
Copyright © 2001, 2003 Oracle Corporatio n
All Rights Reserved.
Go to Documentation Home
Home		 Go to Book List
Bo ok List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback