1 Getting Started with Database Administration

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-person 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 database system

• Creating primary database storage structures (tablespaces) after application developers have designed 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 application 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 DBA, you might not be responsible for these duties if your site has a separate security officer. See Oracle Database Security Guide for information about the duties of security officers.

Network Administrators

Some sites have one or more network administrators. A network administrator, for example, administers Oracle networking products, such as Oracle Net Services. See Oracle Database Net Services Administrator's Guide for information about the duties of network administrators.

Application Developers

Application developers design 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 database structure for an application

• Relaying this information to a database administrator

• Tuning the application during development

• Establishing security measures for an application during development

Application developers can perform some of these tasks in collaboration with DBAs. See Oracle Database Advanced Application Developer's Guide for information about application development tasks.

Application Administrators

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

Database Users

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

• Entering, modifying, and deleting data, where permitted

• Generating reports from the data

Task 1: Evaluate the Database Server Hardware

Evaluate 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 the 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 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 systems to the computer that executes Oracle Database.

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

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 database

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 comprise the tablespace, what type of information will be stored in each tablespace, and on which disk drives the data files will be physically stored. When planning the overall logical storage of the database structure, consider the effects that this structure will have when the database is actually created and running. Consider how the logical storage structure of the database will affect:

• The performance of the computer running Oracle Database

• The performance of the database during data access operations

• The efficiency of backup and recovery procedures for the database

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, see accepted industry-standard documentation.

Part I, "Oracle Database Structure and Storage", and Part I, "Schema Objects", provide specific information on creating logical storage structures, objects, and integrity constraints for your 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.

See Chapter 2, "Creating and Configuring 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 5: Back Up the Database

After you create the database structure, perform 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.

Task 6: Enroll System Users

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. See Chapter 7, "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 can implement the planned logical structure database by creating all necessary tablespaces. When you have finished creating tablespaces, you can create the database objects.

Part I, "Oracle Database Structure and Storage" and Part I, "Schema Objects" provide information on creating logical storage structures and objects for 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 responsibilities as a DBA. Oracle Database provides a database resource management feature that helps you to control the allocation of resources among various user groups. The database resource manager is described in Chapter 27, "Managing Resources with Oracle Database Resource Manager".

Task 10: Download and Install Patches

After installation and on a regular basis, download and install patches. Patches are available as single interim patches and as patchsets (or patch releases). Interim patches address individual software bugs and may or may not be needed at your installation. Patch releases are collections of bug fixes that are applicable for all customers. Patch releases have release numbers. For example, if you installed Oracle Database 11.2.0.1, the first patch release will have a release number of 11.2.0.2.

Task 11: Roll Out to Additional Hosts

After you have an Oracle Database installation properly configured, tuned, patched, and tested, you may want to roll that exact installation out to other hosts. Reasons to do this include the following:

• You have multiple production database systems.

• You want to create development and test systems that are identical to your production system.

Instead of installing, tuning, and patching on each additional host, you can clone your tested Oracle Database installation to other hosts, saving time and avoiding inconsistencies. There are two types of cloning available to you:

• Cloning an Oracle home—Just the configured and patched binaries from the Oracle home directory and subdirectories are copied to the destination host and "fixed" to match the new environment. You can then start an instance with this cloned home and create a database.

  You can use the Enterprise Manager Clone Oracle Home tool to clone an Oracle home to one or more destination hosts. You can also manually clone an Oracle home using a set of provided scripts and Oracle Universal Installer.

• Cloning a database—The tuned database, including database files, initialization parameters, and so on, are cloned to an existing Oracle home (possibly a cloned home).

  You can use the Enterprise Manager Clone Database tool to clone an Oracle database instance to an existing Oracle home.

About SQL*Plus

SQL*Plus is the primary command-line interface to your Oracle database. You use SQL*Plus to start up and shut down the database, set database initialization parameters, create and manage users, create and alter database objects (such as tables and indexes), insert and update data, run SQL queries, and more.

Before you can submit SQL statements and commands, you must connect to the database. With SQL*Plus, you can connect locally or remotely. Connecting locally means connecting to an Oracle database running on the same computer on which you are running SQL*Plus. Connecting remotely means connecting over a network to an Oracle database that is running on a remote computer. Such a database is referred to as a remote database. The SQL*Plus executable on the local computer is provided by a full Oracle Database installation, an Oracle Client installation, or an Instant Client installation.

Connecting to the Database with SQL*Plus

Oracle Database includes the following components:

• The Oracle Database instance, which is a collection of processes and memory

• A set of disk files that contain user data and system data

When you connect with SQL*Plus, you are connecting to the Oracle instance. Each instance has an instance ID, also known as a system ID (SID). Because there can be multiple Oracle instances on a host computer, each with its own set of data files, you must identify the instance to which you want to connect. For a local connection, you identify the instance by setting operating system environment variables. For a remote connection, you identify the instance by specifying a network address and a database service name. For both local and remote connections, you must set environment variables to help the operating system find the SQL*Plus executable and to provide the executable with a path to its support files and scripts. To connect to an Oracle instance with SQL*Plus, therefore, you must complete the following steps:

setenv ORACLE_SID orcl
setenv ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

SET ORACLE_SID=orawin2

CONN[ECT] [logon] [AS {SYSOPER | SYSDBA}]

{username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}]

"host[:port][/service_name][:server][/instance_name]"

connect system

connect sys as sysdba

connect / as sysdba

connect salesadmin@"dbhost.example.com/sales.example.com"

connect salesadmin@"dbhost.example.com/sales.example.com:dedicated"

connect salesadmin@"dbhost.example.com:1522/sales.example.com"

connect salesadmin@"192.0.2.5/sales.example.com"

connect salesadmin@"[2001:0DB8:0:0::200C:417A]/sales.example.com"

connect salesadmin@"dbhost.example.com//orcl"

connect salesadmin@sales1

connect /@sales1

connect /@sales1 as sysdba

connect salesadmin@sales1 edition=rev21

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database release labeled "11.2.0.1.0".

Figure 1-1 Example of an Oracle Database Release Number

Description of "Figure 1-1 Example of an Oracle Database Release Number"

Checking Your Current Release 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 database server.

COL PRODUCT FORMAT A40
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   11.2.0.0.1  Production
Oracle Database 11g Enterprise Edition   11.2.0.0.1  Production
PL/SQL                                   11.2.0.0.1  Production
...

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

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 require (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.

Administrative User Accounts

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

• SYS (default password: CHANGE_ON_INSTALL)

• SYSTEM (default password: MANAGER)

  Note:

  Both Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) now prompt 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 and SYSTEM at database creation time, rather than using these default passwords. See "Protecting Your Database: Specifying Passwords for Users SYS and SYSTEM" for more information.

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

Administrative Privileges

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.

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

CONNECT oe AS SYSDBA
SELECT * FROM admin_test;

ORA-00942: table or view does not exist 

Selecting an Authentication Method for Database Administrators

Database Administrators can authenticate database administrators through the data dictionary, (using an account password) like other users. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case-sensitive. (You can disable case sensitivity and return to pre–Release 11g behavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:

• Operating system (OS) authentication

• Password files

• Strong authentication with a network-based authentication service, such as Oracle Internet Directory

These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)

The remainder of this section focuses on operating system authentication and password file authentication. See Oracle Database Security Guide for information about authenticating database administrators with network-based authentication services.

Your choice will be influenced by whether you intend to administer your database locally on the same system 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.

Figure 1-2 Database Administrator Authentication Methods

Description of "Figure 1-2 Database Administrator Authentication Methods"

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.

Using Operating System Authentication

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

CONNECT / AS SYSDBA
CONNECT / AS SYSOPER

CONNECT /@net_service_name AS SYSDBA
CONNECT /@net_service_name AS SYSOPER

Using Password File Authentication

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

CONNECT oe AS SYSDBA

CONNECT oe AS SYSOPER

Creating a Password File with ORAPWD

The syntax of the ORAPWD command is as follows:

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]

Command arguments are summarized in the following table.

There are no spaces permitted around the equal-to (=) character.

The command prompts for the SYS password and stores the password in the created password file.

Example

The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.

orapwd FILE=orapworcl ENTRIES=30

Sharing and Disabling the Password File

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication. The values recognized for REMOTE_LOGIN_PASSWORDFILE are:

• 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.

• EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

• SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

  This option is useful if you are administering multiple databases or an Oracle RAC database.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

Keeping Administrator Passwords Synchronized with the Data Dictionary

If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from NONE to EXCLUSIVE or SHARED, or if you re-create the password file with a different SYS password, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.

To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.

To synchronize the passwords for non-SYS users who log in using the SYSDBA or SYSOPER privilege, you must revoke and then regrant the privilege to the user, as follows:

1. Find all users who have been granted the SYSDBA privilege.

   SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
   

2. Revoke and then re-grant the SYSDBA privilege to these users.

   REVOKE SYSDBA FROM non-SYS-user;
   GRANT SYSDBA TO non-SYS-user;
   

3. Find all users who have been granted the SYSOPER privilege.

   SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';
   

4. Revoke and regrant the SYSOPER privilege to these users.

   REVOKE SYSOPER FROM non-SYS-user;
   GRANT SYSOPER TO non-SYS-user;
   

Adding Users to a Password File

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, or the password file is missing), Oracle Database issues 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 password file as explained in "Creating a Password File with ORAPWD".

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)

   Note:

   REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

3. Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:

   CONNECT SYS AS SYSDBA
   

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

5. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.

GRANT SYSDBA TO oe;

REVOKE SYSDBA FROM oe;

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