Release 2 (8.1.6)
Part Number A76956-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 2 (8.1.6) Part Number A76956-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:
For information on network administration in a distributed environment, see Oracle8i 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.
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.
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 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 all database system privileges. Therefore, it is very powerful and should be granted only 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.
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.
|
Note: To connect as SYSOPER or SYSDBA using OS authentication you do not need the SYSOPER or SYSDBA system privileges. Instead, the server verifies that you have been granted the appropriate OSDBA or OSOPER roles at the operating system level. CONNECT is an SQL*Plus command. For information on its usage and syntax, see SQL*Plus User's Guide and Reference. |
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.
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
GRANT SYSDBA TO scott; GRANT SYSOPER TO scott;
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.
CONNECT scott/tiger@acct.hq.com AS SYSDBA
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:
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 SYSOPER or SYSDBA:
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 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 SYSOPER and SYSDBA. If you issue the ALTER USER statement 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 specifies the number of entries that you would like the password file to accept. This corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of entries that can be entered may be somewhat higher, as the ORAPWD utility will continue to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it will hold 4 password entries and the number of password entries allocated will always be a multiple of 4.
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 SYSOPER and SYSDBA 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 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 SYSOPER and SYSDBA. 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 SYSDBA and SYSOPER; 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.
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 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 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 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.
More information on system privileges is contained in Chapter 23, "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, not the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
For example, assume user SCOTT has issued the following statements:
CONNECT scott/tiger CREATE TABLE scott_test(name VARCHAR2(20));
Later, when SCOTT issues these statements:
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:
More information on password file administration is contained in "Password File Administration".
This section describes how to expand the number of password file users if the password file becomes full and how to 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.
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 start up 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:
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.
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.
In general, you must perform a series of steps to get the database system up and running, and then maintain it. The steps are:
Step 1: Install the Oracle Software
Step 2: Evaluate the Database Server Hardware
Step 4: Create and Open the Database
Step 5: Implement the Database Design
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 Oracle8i 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.
For more information on what software to install, see "Identifying Your Oracle Database Software Release".
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 datafiles will make up the tablespace, where the datafiles 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 frequently 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.
Part III, "Database Storage" and Part IV, "Schema Objects" provide 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. You can create a database at installation time, using Oracle's 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 3, "Starting Up and Shutting Down" for guidance in starting up the database.
Once you have created and started the database, you can implement 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. Part III, "Database Storage" and Part IV, "Schema Objects" contain information which can help you create 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 Oracle8i Backup and Recovery Guide or Oracle8i Recovery Manager User's Guide and Reference 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.
The following chapters will help you in this endeavor:
Optimizing the database system's performance is one of your ongoing responsibilities. Additionally, Oracle provides a database resource management feature which allows you control how resources are allocated to various user groups. The database resource manager is described in Chapter 25, "The Database Resource Manager".
|
See Also:
Oracle8i Designing and Tuning for Performance contains information about tuning your database and applications. |
Because Oracle products continue to evolve or maintenance is required to fix problems and enhance functionality, new releases of the database server are the result. It is normal that multiple releases are present at any point in time. To fully identify a release, as many as five numbers may be required. The significance of these numbers is discussed below.
An Oracle database server distribution tape might be labeled "release 8.1.5.1.1". The following will help you understand the release level nomenclature used by Oracle.
This is the most general identifier. It represents a major new edition (or version) of the software and contains significant new functionality. Example: version 8 (may also be identified as release 8.0).
This number represents a new features release level. Example: release 8.1.
This number represents a maintenance release level. A few new features may also be included. Examples: release 8.0.4, release 8.1.6.
This number identifies a generic patch set. The patch set is applicable across all operating system and hardware platforms. Example: patch set release 8.1.5.2
This number represents a patch set that is applicable only to specific operating system and hardware platforms. Example: patch set release 8.0.4.1.1.
To identify which release of the Oracle database server 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. Note that other product release levels may increment independently of the database server.
SELECT * FROM product_component_version; PRODUCT VERSION STATUS --------------------------- --------------------------- --------------------- CORE 8.1.5.0.0 Production NLSRTL 3.4.0.0.0 Production Oracle8i Enterprise Edition 8.1.5.0.0 Production PL/SQL 8.1.5.0.0 Production TNS for 32-bit Windows: 8.1.5.0.0 Production 5 rows selected.
The information displayed by this query is important for reporting problems with your software.
Optionally, you may query the V$VERSION view to see component level information.
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|