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

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.

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 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 Development Guide for information about application development tasks.

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

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)

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

As the database administrator, you must plan the logical storage structure of the database, the overall database design, and 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, take into account 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.

Oracle Database Structure and Storage, and Schema Objects, provide specific information on creating logical storage structures, objects, and integrity constraints for your 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 Creating and Configuring an Oracle Database, for information on creating a database and Starting Up and Shutting Down for guidance in starting 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.

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 Managing Users and Securing the Database for guidance in this task.

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.

Oracle Database Structure and Storage and Schema Objects provide information on creating logical storage structures and objects for your database.

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 Managing Resources with Oracle Database Resource Manager.

After the database installation, download and install Release Updates (Updates) and Release Update Revisions (Revisions) for your Oracle software on a regular basis.

Starting with Oracle Database 18c, Oracle provides quarterly updates in the form of Release Updates (Updates) and Release Update Revisions (Revisions). Oracle no longer releases patch sets. Check the My Oracle Support website for required updates for your installation.

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 Oracle Enterprise Manager Cloud Control to clone an Oracle home to one or more destination hosts. You can 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 Cloud Control to clone an Oracle database instance to an existing Oracle home.

Oracle Database releases are categorized by five numeric segments that indicate release information.

Oracle Database releases are released in version and version_full releases.

The version release is designated in the form major release version.0.0.0.0. The major release version is based on the last two digits of the year in which an Oracle Database version is released for the first time. For example, the Oracle Database version released for the first time in the year 2018 has the major release version of 18, and thus its version release is 18.0.0.0.0.

The version_full release is an update of a version release and is designated based on the major release version, the quarterly release update version (Update), and the quarterly release update revision version (Revision). The version_full releases are categorized by five numeric segments separated by periods as shown in the following example:

Figure 1-1 Example of an Oracle Database Release Number

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

• First numeral: This numeral indicates the major release version. It also denotes the last two digits of the year in which the Oracle Database version was released for the first time.

• Second numeral: This numeral indicates the release update version (Update).

• Third numeral: This numeral indicates the release update revision version (Revision).

• Fourth numeral: This numeral indicates the increment version. This nomenclature can apply to updates in future releases.

• Fifth numeral: This numeral is reserved for future use.

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.

COL PRODUCT FORMAT A38
COL VERSION FORMAT A10
COL VERSION_FULL FORMAT A12
COL STATUS FORMAT A12
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                VERSION    VERSION_FULL STATUS
-------------------------------------- ---------- ------------ ------------
NLSRTL                                 19.0.0.0.0 19.2.0.0.0   Production
Oracle Database 19c Enterprise Edition 19.0.0.0.0 19.2.0.0.0   Production
PL/SQL                                 19.0.0.0.0 19.2.0.0.0   Production
...

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 privileges that are required for an administrator to perform basic database operations are granted through special system privileges.

These privileges are:

• SYSDBA

• SYSOPER

• SYSBACKUP

• SYSDG

• SYSKM

• SYSRAC

Excluding the SYSRAC privilege, grant these privileges to users depending upon the level of authorization they require. The SYSRAC privilege cannot be granted to users because it is used only by the Oracle agent of Oracle Clusterware to connect to the database using operating system authentication.

Starting with Oracle Database 12c Release 1 (12.1), the SYSBACKUP, SYSDG, and SYSKM administrative privileges are available. Starting with Oracle Database 12c Release 2 (12.2), the SYSRAC administrative privilege is available. Each new administrative privilege grants the minimum required privileges to complete tasks in each area of administration. The new administrative privileges enable you to avoid granting SYSDBA administrative privilege for many common tasks.

Each administrative privilege authorizes a specific set of operations.

The following table lists the operations that are authorized by each administrative privilege:

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

When you connect with an administrative privilege, you connect with a current schema that is not generally associated with your username. For SYSDBA, the current schema is SYS. For SYSOPER, the current schema is PUBLIC. For SYSBACKUP, SYSDG, and SYSRAC, the current schema is SYS for name resolution purposes. However, the current schema for SYSKM is SYSKM.

Also, when you connect with an administrative privilege, you connect with a specific session user. When you connect as SYSDBA, the session user is SYS. For SYSOPER, the session user is PUBLIC. For SYSBACKUP, SYSDG, SYSKM, and SYSRAC, the session user is SYSBACKUP, SYSDG, SYSKM, and SYSRAC, respectively.

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

CONNECT mydba AS SYSDBA
SELECT * FROM admin_test;

ORA-00942: table or view does not exist 

CONNECT mydba AS SYSBACKUP

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYS

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SYSBACKUP

• Operating System Groups
  Operating system groups are created and assigned specific names as part of the database installation process.
• Preparing to Use Operating System Authentication
  DBAs can authenticate to the database through the operating system rather than with a database user name and password.
• Connecting Using Operating System Authentication
  A user can connect to the database using operating system authentication.

You can use password file authentication for an Oracle database instance and for an Oracle Automatic Storage Management (Oracle ASM) instance. The password file for an Oracle database is called a database password file, and the password file for Oracle ASM is called an Oracle ASM password file.

• Preparing to Use Password File Authentication
  To prepare for password file authentication, you must create the password file, set the REMOTE_LOGIN_PASSWORDFILE initialization parameter, and grant privileges.
• Connecting Using Password File Authentication
  Using password file authentication, administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. By default, passwords are case-sensitive.

The ORAPWD command creates and maintains a password file.

orapwd FILE=filename 
[FORCE={y|n}] 
[ASM={y|n}] 
[DBUNIQUENAME=dbname] 
[FORMAT={12.2|12}] 
[SYS={y|n|password|external('sys-external-name')|global('sys-directory-DN')}]
[SYSBACKUP={y|n|password|external('sysbackup-external-name')|global('sysbackup-directory-DN')}]
[SYSDG={y|n|password|external('sysdg-external-name')|global('sysdg-directory-DN')}] 
[SYSKM={y|n|password|external('syskm-external-name')|global('syskm-directory-DN')}] 
[DELETE={y|n}] 
[INPUT_FILE=input-fname]

orapwd DESCRIBE FILE=filename

You can create a database password file with ORAPWD.

• Run the ORAPWD command.

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' SYSBACKUP=password FORMAT=12.2

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2 
sys=external('KerberosUserSYS@example.com')
syskm=external('KerberosUserSYSKM@example.com')

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2 INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

orapwd FILE='/u01/oracle/dbs/orapworcl' SYS=Y INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

orapwd DESCRIBE FILE='orapworcl' 
Password file Description : format=12.2

SQL> ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE;

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a database password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication.

If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from none to exclusive or shared, then you must ensure that the passwords stored in the data dictionary and the passwords stored in the password file for the non-SYS administrative users, such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM users are the same.

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;
   

5. Find all users who have been granted the SYSBACKUP privilege.

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

6. Revoke and regrant the SYSBACKUP privilege to these users.

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

7. Find all users who have been granted the SYSDG privilege.

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

8. Revoke and regrant the SYSDG privilege to these users.

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

9. Find all users who have been granted the SYSKM privilege.

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

10. Revoke and regrant the SYSKM privilege to these users.

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

When you grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to a user, that user's name and privilege information are added to the database password file.

1. Follow the instructions for creating a password file as explained in "Creating a Database Password File with ORAPWD".
2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to exclusive. (This is the default.)

   Oracle Database issues an error if you attempt to grant these privileges and the initialization parameter REMOTE_LOGIN_PASSWORDFILE is not set correctly.

   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, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to yourself and other users as appropriate. See "Granting and Revoking Administrative Privileges".

Use the GRANT statement to grant administrative privileges. Use the REVOKE statement to revoke administrative privileges.

GRANT SYSDBA TO mydba;

REVOKE SYSDBA FROM mydba;

The V$PWFILE_USERS view contains information about users that have been granted administrative privileges.

You can remove a database password file if it is no longer needed.