Release 8.0
A58397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Administrator's Guide Release 8.0 A58397-01 |
|
This chapter describes the responsibilities of the person who administers the Oracle Server, the database administrator.
The following topics are included:
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:
Because an Oracle 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:
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.
An application developer designs and implements database applications An application developer's responsibilities include the following tasks:
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 interact with the database via applications or utilities. A typical user's responsibilities include the following tasks:
At some sites there may be one or more network administrators. Network administrators may be responsible for administering Oracle networking products, such as Net8.
See Also: "Network Administration" in Oracle8 Distributed Database Systems
To accomplish administrative tasks in Oracle, 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:
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.
In addition, Enterprise Manager requires that your operating system account or ID be distinguished in some way to allow you to use operating system privileged Enterprise 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.
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 Oracle 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 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 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.
When a database is created, the user SYSTEM, identified by the password MANAGER, is also 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.
A predefined role, named "DBA", is automatically created with every Oracle database. This role contains all database system privileges. Therefore, it is very powerful and should only be granted to fully functional database administrators.
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:
The following methods for authenticating database administrators replace the CONNECT INTERNAL syntax provided with earlier versions of Oracle (CONNECT INTERNAL continues to be supported for backward compatibility only):
Depending on whether you wish to administer your database locally on the same machine where the database resides or 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.
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 Oracle8 Concepts.
If you choose, you can have your operating system authenticate users performing database administration operations.
CONNECT / AS SYSOPER CONNECT / AS SYSDBA
If you successfully connect as INTERNAL using an earlier release of Oracle, you should be able to continue to connect successfully using the new syntax shown in Step 3.
Two special operating system roles control database administrator logins when using operating system authentication: OSOPER and OSDBA.
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, Oracle 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 Net8 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 Net8 you are using.
See Also: For information about OS authentication of database administrators, see your operating system-specific Oracle documentation.
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.
ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
The privilege SYSDBA permits the user to perform the same operations as OSDBA. Likewise, the privilege SYSOPER permits the user to perform the same operations as OSOPER.
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> where 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:
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 pathname for the file. The contents of this file are encrypted, and the file is not user-readable. This parameter is mandatory.
The types of file names allowed for the password file are operating system specific. Some platforms require the password file to be a specific format (for example, orapw <SID>) and located in a specific directory. Other platforms 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 the Oracle Parallel Server, the environment variable for each instance should point to the same password file.
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.
Setting this parameter to NONE causes Oracle 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.
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.
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. |
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.
CONNECT SYS/change_on_install AS SYSDBA
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:
GRANT SYSDBA TO scott
Use the REVOKE command 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 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.
See Also: For more information about system privileges, see Chapter 21, Managing User Privileges and Roles.
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.
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 Enterprise Manager CONNECT command to connect with administrator privileges.
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:
CONNECT scott/tiger AS SYSDBA 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.
To connect to Oracle as a privileged user over a non-secure connection, you must meet the following conditions:
To connect to Oracle 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" .
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.
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.
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.
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.
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, Oracle retrieves the value of this parameter from the initialization 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 these values do not match, the value stored in the file is overwritten.
Several utilities are available to help you maintain and control the Oracle Server.
The following topics are included in this section:
Enterprise Manager allows you to monitor and control an Oracle database. All administrative operations discussed in this book are executed using Enterprise Manager. Enterprise Manager has both GUI (Graphical User Interface) and line mode interfaces.
Enterprise Manager uses a superset of ANSI/ISO standard SQL commands. The most common administrative commands are available in the menus of Enterprise Manager/GUI. Commands used less frequently can be typed into a Enterprise Manager SQL Worksheet and executed.
See Also: Oracle Enterprise Manager Administrator's Guide.
SQL*Loader is used by both database administrators and users of Oracle. It loads data from standard operating system files (files in text or C data format) into Oracle database tables.
See Also: Oracle8 Utilities.
The Export and Import utilities allow 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.
See Also: Oracle8 Utilities.
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 Oracle Server and database on any type of computer system. The following sections include details about each step.
|
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 Oracle8 Migration. |
As the database administrator, you must install the Oracle 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 Oracle Net8 drivers necessary to connect the remote machines to the computer that executes Oracle.
See Also: For more information, see "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 Net8 drivers.
After installation, 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 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 Also: See Chapters 9 through 17 for specific information on creating logical storage structures, objects, and integrity constraints for your 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 Oracle. 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 Oracle, 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 of this book for this procedure.
See Also: See Chapter 3 for database and instance startup and shutdown procedures.
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 Also: See Chapters 8 through 17 for instructions on creating logical storage structures and objects for your 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 Also: See the Oracle8 Backup and Recovery Guide for instructions on customizing your backup operations and performing recovery procedures.
Once you have backed up the database structure, you can begin to enroll the users of the database in accordance with your Oracle license agreement, create roles for these users, and grant appropriate roles to them.
See Also: See Chapters 18 through 20 for the procedures to create user accounts and roles, and information on complying with your license agreement.
Optimizing the database system's performance is one of your ongoing responsibilities.
See Also: Oracle8 Tuning, for information about tuning your database and applications.
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:
An Oracle Server distribution tape might be labeled "Release 8.0.4.1." The following sections translate this number.
The version number, such as 8, is the most general identifier. A version is a major new edition of the software, which usually contains significant new functionality.
The maintenance release number signifies different releases of the general version, starting with 0, as in version 8.0. The maintenance release number increases when bug fixes or new features to existing programs become available.
The patch release number identifies a specific level of the object code, such as 8.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.
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 8.0.4.1. or 8.0.4.1.3. An emergency patch is not usually intended for wide distribution; it usually fixes or works around a particular, critical problem.
The following examples show possible release numbers for Oracle8:
As Oracle Corporation introduces new products and enhances existing ones, the version numbers of the individual products increment independently. Thus, you might have an Oracle Server Release 8.0.12.2 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.)
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 3.4.1.0.0 Production NLSRTL 3.1.3.0.0 Production Oracle8 Server 3.2.1.0.0 Beta Release PL/SQL 2.2.1.0.0 Beta TNS for SunOS: 2.1.4.0.0 Production 5 rows selected
