Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes your responsibilities as a database administrator (DBA) who administers the Oracle database server.
The following topics are discussed:
The types of users and their roles and responsibilities at a site can vary. 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 several areas of specialization.
This section contains the following topics:
Each database requires at least one database administrator (DBA) to administer it. Because an Oracle database system can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility.
A database administrator's responsibilities can include the following tasks:
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 DBA, you might not be responsible for these duties if your site has a separate security officer.
Some sites have one or more network administrators. A network administrator can administer Oracle networking products, such as Oracle Net Services.
| See Also:
Part VI, "Distributed Database Management" for information on network administration in a distributed environment |
Application developers design and implement database applications. Their responsibilities include the following tasks:
Application developers can perform some of these tasks in collaboration with DBAs.
An Oracle site can assign one or more application administrators to administrate a particular application. Each application can have its own administrator.
Database users interact with the database through applications or utilities. A typical user's responsibilities include the following tasks:
The following tasks present a prioritized approach for designing, implementing, and maintaining an Oracle Database:
Task 1: Evaluate the Database Server Hardware
Task 2: Install the Oracle Software
Task 4: Create and Open the Database
Task 7: Implement the Database Design
Task 8: Back Up the Fully Functional Database
These tasks are discussed in succeeding sections.
|
Note: If upgrading to a new release, back up your existing production database before installation. For information on preserving your existing production database, see Oracle9i Database Migration. |
Evaluate how Oracle and its applications can best use the available computer resources. This evaluation should reveal the following information:
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 processing installations, the database is controlled by a central computer (database server) and the database tools and applications are executed 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.
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 following documentation: |
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 datafiles will make up the tablespace, what type of information will be stored in each tablespace, and on which disk drives the datafiles will be physically stored. When planning the overall logical storage of the database structure, take into account the effects that this structure will have when the database is actually created and running. Such considerations include how the logical storage structure database will affect the following:
Plan the relational design of the database objects and the storage characteristics for each of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly affect 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 frequently accessed data dramatically affects application performance.
During the planning stage, develop a backup strategy for the database. You can alter the logical storage structure or design of the database 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 documentation.
Part II, "Oracle Server Processes and Storage Structure" and Part III, "Schema Objects" provide specific information on creating logical storage structures, objects, and integrity constraints for your database.
When 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.
Either way, refer to Chapter 2, "Creating an Oracle Database", for information on creating a database and Chapter 4, "Starting Up and Shutting Down" for guidance in starting up the database.
After you create the database structure, carry out the backup strategy you planned for the database. Create any additional redo log files, take the first full database backup (online or offline), and schedule future database backups at regular intervals.
| See Also:
For instructions on customizing your backup operations and performing recovery procedures see either of the following: |
After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, create appropriate roles for these users, and grant these roles.
The following chapters will help you in this endeavor:
After you create and start the database, and enroll the system users, you can implement the planned logical structure database by creating all necessary tablespaces. When you complete this, you can create the objects for the database.
Part II, "Oracle Server Processes and Storage Structure" and Part III, "Schema Objects" contain information which can help you create logical storage structures and objects for your database.
Now that 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.
Optimizing the performance of the database is one of your ongoing responsibilities as a DBA. Additionally, Oracle provides a database resource management feature that enables you to control the allocation of resources to various user groups.
The database resource manager is described in Chapter 27, "Using the Database Resource Manager".
| See Also:
Oracle9i Database Performance Tuning Guide and Reference contains information about tuning your database and applications. |
Because the Oracle database server continues to evolve and can require maintenance, Oracle periodically produces new releases. Because only some users initially subscribe to a new release or require specific maintenance, multiple releases of the product can exist simultaneously.
As many as five numbers may be required to fully identify a release. The significance of these numbers is discussed below.
To understand the release level nomenclature used by Oracle, examine the following example of an Oracle database server labeled "Release 9.2.0.1.0."
This is the most general identifier. It represents a major new edition (or version) of the software that contains significant new functionality.
This digit represents a maintenance release level. Some new features may also be included.
This digit reflects the release level of the Oracle9i Application Server (Oracle9iAS).
This digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
This digit identifies a platform specific release. Usually this is a patch set. Where different platforms require the equivalent patch set, this digit will be the same across the effected platforms.
To identify the release of the Oracle database server that is currently installed and to see the release levels of other Oracle components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query is shown below. Other product release levels may increment independently of the database server.
COL PRODUCT FORMAT A35 COL VERSION FORMAT A15 COL STATUS FORMAT A15 SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCT VERSION STATUS ----------------------------------- --------------- --------------- NLSRTL 9.2.0.1.0 Production Oracle9i Enterprise Edition 9.2.0.1.0 Production PL/SQL 9.2.0.1.0 Production TNS for Solaris: 9.2.0.1.0 Production
It's important to convey to Oracle the information displayed by this query when you report problems with the software.
Optionally, you can query the V$VERSION view to see component-level information.
To accomplish the administrative tasks of an Oracle DBA, 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 contains the following topics:
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 Oracle, 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 Oracle software installation). Although you do not need the Oracle files to be stored in your account, you should have access to them.
| See Also:
Your operating system specific Oracle documentation. The method of distinguishing a database administrator's account is operating system specific. |
Two user accounts are automatically created with the database:
SYS (default password: CHANGE_ON_INSTALL)SYSTEM (default password: MANAGER)
|
Note: Oracle recommends that you specify passwords for If you use the default passwords, to prevent inappropriate access to the data dictionary tables or other tampering with the database, it is important that you change the passwords for the |
It is suggested that you create at least one additional administrator user, and grant that user the DBA role, to use when performing daily administrative tasks. It is recommended that you do not use SYS and SYSTEM for these purposes.
When any database is created, the user SYS 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 Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)
Ensure that most database users are never able to connect using the SYS account.
When a database is created, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create in the SYSTEM schema tables of interest to individual users.
A predefined role, named DBA, is automatically created with every Oracle database. This role contains most database system privileges. Therefore, it is very powerful and should be granted only to fully functional database administrators.
|
Note: The DBA role does not include the |
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 authentication scheme.
This section contains the following topics:
Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
The following are the operations that are authorized by the SYSDBA and SYSOPER system privileges:
The manor in which you are authorized to use these privileges depends upon the method of authentication that you choose to use.
When you connect with SYSDBA or SYSOPER 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.
This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege.
Assume that user scott has issued the following statements:
CONNECT scott/password CREATE TABLE admin_test(name VARCHAR2(20));
Later, scott issues these statements:
CONNECT scott/password AS SYSDBA SELECT * FROM admin_test;
User scott now receives the following error:
ORA-00942: table or view does not exist
This is because scott now references the SYS schema by default. The table was created in the scott schema.
The following methods are available for authenticating database administrators:
Your choice will be influenced by whether you intend to administer your database locally on the same machine where the database resides, or whether you intend to administer many different databases from a single remote client. Figure 1-2 illustrates the choices you have for database administrator authentication schemes.
If you are performing remote database administration, you should consult your Oracle Net documentation to determine if you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.
See Also:
|
To connect to Oracle as a privileged user over a non-secure connection, you must be authenticated by a password file. When using password file 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 Authentication".
To connect to Oracle as a privileged user over a local connection or a secure remote connection, you have the following options:
SYSDBA or SYSOPER system privilege.SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use OS authentication. On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group, generically referred to as OSDBA.This section describes how to authenticate an administrator using the operating system.
To enable authentication of an administrative user using the operating system you must do the following:
REMOTE_LOGIN_PASSWORDFILE, is set to NONE. This is the default value for this parameter.A user can be authenticated, enabled as an administrative 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 specify the net service name of the remote database:
CONNECT /@net_service_name AS SYSDBA CONNECT /@net_service_name AS SYSOPER
| See Also:
SQL*Plus User's Guide and Reference for syntax of the |
Two special operating system groups control database administrator connections when using OS 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 the following table:
| Operating System Group | UNIX | Windows |
|---|---|---|
|
OSDBA |
|
|
|
OSOPER |
|
|
The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.
The following describes how membership in the OSDBA or OSOPER group affects your connection to Oracle:
AS SYSDBA when you connect to the database, you are granted the SYSDBA system privilege.AS SYSOPER when you connect to the database, you are granted the SYSOPER system privilege.SYSDBA or SYSOPER system privileges, the CONNECT command fails.
This section describes how to authenticate an administrative user using password file authentication.
To enable authentication of an administrative user using password file authentication you must do the following:
ORAPWD utility:
ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.SYS (or as another user with the administrative privilege).SYSDBA or SYSOPER system privilege to the user:
GRANT SYSDBA to scott;
This statement adds the user to the password file, thereby enabling connection AS SYSDBA.
| See Also:
"Creating and Maintaining a Password File" for instructions for creating and maintaining a password file |
Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and with the AS SYSDBA or AS SYSOPER clause. For example, user scott has been granted the SYSDBA privilege, so he can connect as follows:
CONNECT scott/tiger AS SYSDBA
However, since scott has not been granted the SYSOPER privilege, the following command will fail:
CONNECT scott/tiger AS SYSOPER
| See Also:
SQL*Plus User's Guide and Reference for syntax of the |
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 contains the following topics:
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> 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 command creates a password file named acct.pwd that allows up to 30 privileged users with different passwords. In this example, the file is initially created with the password secret for users connecting as SYS.
ORAPWD FILE=acct.pwd PASSWORD=secret ENTRIES=30
Following are descriptions of the parameters in the ORAPWD utility.
This parameter sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This parameter is mandatory.
The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to be a specific format and located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. See your operating system specific Oracle documentation for the names and locations allowed on your platform.
If you are running multiple instances of Oracle using Oracle9i Real Application Clusters, the environment variable for each instance should point to the same password file.
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 the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.
This parameter specifies the number of entries that you require the 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 ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always 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_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this parameter is required.
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 as follows:
|
Suggestion: To achieve the greatest level of security, you should set the |
When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are 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 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, the user is removed from the password file.
REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.SYSDBA privileges as shown in the following example:
CONNECT SYS/password AS SYSDBA
SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges".Granting the SYSDBA or SYSOPER privilege to a user causes their 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 OS authenticated users from connecting if they meet the criteria for OS authentication.
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 scott;
Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM scott;
Because SYSDBA and SYSOPER are the most powerful database privileges, the ADMIN OPTION is not used. Only a user currently connected as SYSDBA (or INTERNAL) can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because 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.
| See Also:
Chapter 25, "Managing User Privileges and Roles" for more information on system privileges |
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database. The columns displayed by this view are as follows:
This section describes how to:
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.
SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.If you determine that you no longer require a password file to authenticate 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 administration operations.
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 initialization parameter REMOTE_LOGIN_PASSWORDFILE. When you start up an instance, Oracle retrieves the value of this parameter from the parameter file stored on your client machine. When you mount the database, Oracle compares the value of this parameter to the value stored in the password file. If the values do not match, Oracle overwrites the value stored in the file.
Several utilities are available to help you maintain the data in your Oracle database. This section introduces two of these utilities:
SQL*Loader is used both by database administrators and by other users of Oracle. It loads data from standard operating system files (such as, files in text or C data format) into Oracle database tables.
The Export and Import utilities enable you to move existing data in Oracle format to and from Oracle databases. For example, export files can archive database data or move data among different Oracle databases that run on the same or different operating systems.
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|