4
Database Administration

Database administration functionality and wizards are integrated into Oracle Enterprise Manager. You can access the database administration functionality through the Console. The Console can either be launched with a connection to an Oracle Management Server, which utilizes Oracle Enterprise Manager's three-tier framework, or launched standalone, which connects directly to a database.

The databases folder contains all databases that are accessible to you. You can expand a specific database to administer any of the following from a unified tree view without launching separate tools:

• Instances, including startup, shutdown, and initialization.

• Schemas, including tables, indexes, and all other schema objects.

• Security, including user accounts, roles, and privileges.

• Storage, including tablespaces, datafiles, and rollback segments.

• Replication, including multimaster and materialized view replication.

• JVM, including namespaces, CORBA and EJB components, and their permissions.

• OLAP, including tools for developing and deploying internet-based business intelligence applications and services that support complex statistical, mathematical, and financial calculations within a multidimensional data model.

• Workspace, including a virtual environment that one or more users can share to make versioned changes to data.

• Other database features, depending on the products installed with your database.

This integrated access to database administration functionality simplifies switching between DBA tasks and provides an accurate view of database configuration status.

The Backup and Recovery wizards are also available to help you back up or restore and recover various objects such as the database, datafiles, and tablespaces. The Backup wizard also allows you to make an image copy of the datafiles and the current controlfile.

Common Features of DBA Management Functionality

This section discusses the common features shared by the database administration functionality in Enterprise Manager.

Tree Views

The Console displays a tree view of connected databases, which can be expanded to show subordinate objects.

General Information about Databases

When you select a database node in the tree, a non-editable General page appears on the right where you can view information about the host, port, SID, TNS descriptors, and listener.

Comprehensive Overview Pages

When you select one of the database management functionality nodes such as Instance, Schema, and so on, comprehensive overview pages with links to related Quick Tours or functional overviews appear on the right.

Property Sheets

When relevant, if you select an object in the tree, a property sheet appears on the right where you can view or edit database properties. Wizards also display tree views and property sheets as necessary.

Multi-Column Lists

In most cases when you select a folder under one of the database management functionality nodes such as Schema (with the exception of Advanced Queues), Instance, and so on, a multi-column list of all the folder's objects appears on the right side of the window, providing a quick summary of information about each object in the selected folder.

Database Version Awareness

All database administration functionality and wizards are aware of the features that are available in each database version. When you select a database in a tree view, the tool only displays objects and properties that are enabled in that database version.

Database Reports

You can extract information from the database such as object definitions, object dependencies, database configuration, or reports, including custom SQL queries.

Logging of Database Changes

You can now log all Data Definition Language (DDL) and Data Manipulation Language (DML) changes made by an application when connected to a database.

Showing Object DDL

Data Definition Language (DDL) commands set up the data such as creating and altering databases and tables. You can display the Data Definition DDL for objects.

Show SQL

Though one of the benefits of Enterprise Manager is that DBA tasks can be performed without manually entering SQL, you do have the option of viewing the SQL code generated for you. By selecting Show SQL button, you can review this code before implementing any changes, as well as copy and paste it into your own SQL scripts if you wish.

Show Dependencies

Database object dependencies and dependents can be viewed by right-mouse clicking an object in the tree view and choosing Show Dependencies. Dependencies show what the selected object depends on, such as the tablespace location and the owner of the selected object. Dependents rely on the selected object, such as which indexes will be dropped and which synonyms will be affected if you drop the selected object.

Right-Mouse Commands

With database administration functionality, you can right-mouse click any folder or object in a tree list to perform administrative tasks. Right-mouse clicking an object shows all the tasks that can be performed on the object, such as connecting to or disconnecting from the database, creating users, adding or removing profiles, assigning privileges, showing dependencies, and bringing up wizards.

DB Search Capabilities

Database Search allows you to search for any object in a database given a flexible set of criteria:

• Names of the objects that you want to find. You can enter wild card characters for the object name.

• Database that you want to search.

• Object types that you want to include in the search.

• Schemas that you want to include in the search.

The object definitions that match the search criteria are displayed in a multi-column table.

DBA Management Functionality and Wizards

This section describes DBA Management functionality and wizards.

DBA Management Functionality	Task	See Page
Instance Management	Manages instances and sessions	4-61
Schema Management	Manages schema objects	4-67
Security Management	Manages security parameters	4-70
Storage Management	Manages database storage	4-73
Replication Management	Enables an administrator to quickly set up, configure, and manage an Oracle replication environment	4-76
Oracle9i JVM	Enables you to manage the namespace, browse CORBA and EJB components published in the namespace, change permissions on the published components, and execute the main() method in schema-resident Java classes and view the output.	4-77
OLAP Services	Develops and deploys internet-based business intelligence applications and provides services that support complex statistical, mathematical, and financial calculations within a multidimensional data model	4-77
Workspace Management	Allows you to version-enable tables and create, modify, refresh, and merge workspaces	4-78
SQL*Plus Worksheet	Executes SQL and PL/SQL commands	4-78
Wizards	Assist with importing, exporting, loading, backing up, and recovering data, as well as analyzing and creating tables and views	4-79

Instance Management

Instance Management functionality helps you manage database instances and sessions in your Oracle environment. With the Instance Management functionality you can:

• Start up and shut down a database.

• View and edit the values of instance parameters.

• Manage users' sessions, and view currently running SQL and its explain plan.

• Manage in-doubt transactions.

• Monitor long-running operations.

• Control processing resources via Resource Plans.

• Manage stored configurations (only when connected to Oracle Management Server).

• Administer locks and sessions consuming the highest amounts of resources (if the Diagnostics Pack is installed).

When you expand the Instance node under the database in the tree view, the following list of objects and folders appears:

• Configuration

• Stored Configurations (only when connected to Oracle Management Server)

• Sessions

• Locks

• In-Doubt Transactions

• Resource Consumer Groups

• Resource Plans

• Resource Plan Schedule

Figure 4-1 Instance Management

Text description of the illustration instlist.gif

Configuration Operations

When you select the Configuration node under Instance, a property sheet of tabbed pages appears on the right for viewing information about the database instance and editing database properties. In this property sheet, the following information can be viewed and/or edited:

• Status of the instance, including the database version and any installed options, and allows you to start up and shut down a database.

  Note: For a cluster database, do not accept the option to restart the database unless you are sure that no instance is running other than the current one. If some more instances are running; then, you need to arrange for their shut down using the right mouse menu of the cluster database or individual instances.

• Location of the spfile if the database had been started with an spfile

• Persistent parameters which allow you to modify, apply changes, and re-initialize the database.

  Note: The Configured mode for a cluster database displays an additional "Instance Name" column, which helps distinguish the parameters as either database-wide or instance-specific. If this column is blank for a parameter; then, it implies that the parameter's value is available database wide (i.e. for all instances). A string in this column specifies the SID of a specific cluster database instance to which the parameter's value is applicable.

• Memory usage of the current database

• Estimated mean time to recover the database from an instance crash

• Current state of redo log archival

• Performance statistics of an active plan and of each consumer group associated with the active resource plan

• Information about the undo tablespace including the name of the current undo tablespace and the retention time.

Figure 4-2 Instance Management Window

Text description of the illustration instance.gif

Stored Configurations

When the database is connected to the Oracle Management Server, the Stored Configurations folder appears in the tree view with which you can create multiple database start-up configurations without the need to track initialization parameter files (INIT<SID>.ORA). Stored configurations exist in the Oracle Enterprise Manager repository (they are not external files) and can be created, edited, and deleted. You can also add and delete parameters and export a configuration to a file.

Note: If you are connected to an Oracle9i database, you can also start up the database by using the SPFILE on the server side. The database knows the location of the SPFILE and will look for it when it starts up to find the startup parameters. An SPFILE is similar to an init.ora file but located on the server-side and maintained by the server.

Sessions List

The Sessions List page displays the top number of sessions that you specify using database instance resources in real time. Sessions are displayed in descending order based upon the delta value of the statistic chosen as the sort statistic. You can use the information in the chart to isolate executing SQL or to kill a problem session.

Sessions Folder

The Sessions folder lists all users connected to the discovered database. When you select a user in the list, the Sessions property sheet appears with which you can edit user properties, view information about the status of each user, view current SQL or the last run SQL for the database session, and view the database session explain plan.

Locks

The Locks list contains information about the locks currently held by the Oracle server and outstanding requests for a lock or latch. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource--either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows. In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.

Note: Background sessions holding locks are not problematic and should not be killed.

In-Doubt Transactions

The In-Doubt Transactions folder contains information about distributed transactions that failed in the PREPARED state. You can sort the Transactions list on each of the columns by clicking on the column heading.

The In-Doubt Transactions property sheet displays information about distributed transactions in which a commit was interrupted by a system, network, or any failure resulting from external factors.

Resource Consumer Groups

The Resource Consumer Groups folder lists sets of users who have similar resource usage requirements. When you select a resource consumer group object in the folder, a property sheet appears in which you can view or specify properties and assign or remove users from the resource consumer group.

Resource Plans

The Resource Plans folder lists objects that represent resource plans, which are ways of allocating resources among consumer groups. Resource plans contain directives that specify the resources to be given to each group and can be specified in hierarchical fashion using subplans.

The Resource Plans property sheet, which appears when you select an object representing a Resource Plan, allows you to choose available groups/subplans to include in the resource plan, select the percentage of CPU resources allocated to a group, and then activate the plan. You can also view the degree of parallelism for each consumer group.

Resource Plan Schedule

The Resource Plan Schedule property sheet allows you to automate when to activate a resource plan.

Schema Management

With the Schema Management functionality, you can create, alter, or drop database schema objects such as clusters, indexes, materialized views, tables, and views, as well as view dependencies of schema objects. The Schema Management functionality also supports index organized tables, partitioned tables and indexes, advanced queues, Java stored procedures, and unicode. In 9i, the unicode feature allows you to select a column of "character" type and specify the length in bytes or characters.

Figure 4-3 Schema Management Window

Text description of the illustration schemama.gif

When you expand the Schema node under the database in the tree view, a list of schema objects appears.

Figure 4-4 Schema Objects

Text description of the illustration schemaob.gif

Tree List by Schema

Databases contain at least one named schema for each database user. Each schema object belongs to one of these named schemas, regardless of object type. Several objects belonging to the same schema can be edited by opening the View menu and selecting By Schema. The tree view then reorders all schema objects within a set of schema folders listed alphabetically by name.

Editing an Object

To edit an object, you use the object's property sheet, which appears when you select the object in the tree view. You can then modify the object's parameters.

The Schema Management functionality also includes the Table Data Editor content viewer, which allows you to view, update, and delete the contents of a table and display the contents of a view or synonym by selecting a right-mouse command on a table in the tree view.

Creating Objects

Schema Management allows you to create an object or a clone of an object by selecting Create or Create Like from the Object menu. When creating a clone of an object, all attributes are identical except for the name. Parameters for new objects and cloned objects are specified in property sheets which appear when you select Create and the object from the Create dialog or Create Like from the Object menu.

Security Management

In a large network environment, security parameters for objects, administrators, and users are in constant change. With the Security Management functionality, an administrator can make these necessary changes quickly and efficiently.

When you expand the Security node under the database in the tree view, folders for users, roles, and profiles appear.

Figure 4-5 Managing Users, Roles, and Profiles

Text description of the illustration security.gif

Figure 4-6 Security Management Window

Text description of the illustration securita.gif

User Operations

The Security Management functionality helps you manage the database users in your network by helping you create users and clones of users, add and remove user permissions and roles, grant or revoke the switch privilege of resource consumer groups for a user or role, alter user properties, including account status and default profiles, and set up database users to act as proxy for a user. Security Management capabilities also allow you to easily see users' dependents and dependencies.

Role Operations

With Security Management role operations functionality, you can modify role properties as easily as user properties. You can also create roles and clones of roles, add and remove permissions from roles, and see grantees of roles, including consumer groups.

Profile Operations

A profile is a set of limits on a user's database resources. As with users and roles, you can create a profile or a clone of a profile, alter a profile's properties, and assign and remove profiles from users. You also have the ability to see profile dependents and dependencies, as well as grantees of profiles.

Profiles, roles, and the users to which they are assigned can easily be seen in security lists provided by Security Management. Administrators can then use property sheets to determine security parameters, simplifying the process of making changes.

The Security Management functionality also supports Oracle password management, which increases system security. Supported functionality includes: account locking, password lifetime and expiration, password history, password complexity, verifications, and export/import of passwords.

Storage Management

The Storage Management functionality helps you administer tablespaces (permanent, temporary, and undo), datafiles, redo logs, archive logs, and rollback segments for optimum database storage.

Figure 4-7 Storage Management Window

Text description of the illustration storagem.gif

When you connect to a database, the Storage Management branch of the tree view lists an icon for the Controlfile and five folders which contain all the storage objects in the selected database. The five folders include:

• Tablespaces

• Datafiles

• Rollback Segments

• Redo Log Groups

• Archive Logs

Figure 4-8 Managing Database Storage Parameters

Text description of the illustration storageo.gif

The following sections describe the Storage Management operations that can be performed with the objects in each of these folders:

Controlfile Operations

When you select the Controlfile icon, a property sheet appears where you can see the number of controlfiles created for the database and other statistics.

Tablespace Operations

Using the contents of the Tablespaces folder, you can create, edit, or drop a tablespace, switch to a new default temporary tablespace, add a datafile or rollback segment, take a tablespace off- or on-line, make a tablespace read-only or writable, and set tablespace storage parameters, including multiple block sizes which is key in facilitating the transportability of tablespaces from one database type to another. You can also click the tablespace to see the used and free space of the tablespace or datafile.

In Oracle9i, you can also allocate your undo space in a single undo tablespace, instead of distributing them into a set of statically allocated rollback segments. For each Oracle instance, you will only have to allocate enough disk space for the workload in that instance in an undo tablespace.

Datafile Operations

With the contents of the Datafiles folder, you can create a datafile or a clone of a datafile, edit a datafile, and take a datafile off- or on-line. You can also click the datafile to see the used and available space.

Rollback Segment Operations

Using the Rollback Segments folder, you can create, alter, drop, or shrink a rollback segment as well as take one off- or on-line.

Redo Log Group Operations

Using the Redo Log Groups folder, you can switch the current redo log group, trigger a checkpoint in a redo log group, create a new redo log group, and rename, remove, or add new redo log group members.

Archive Log Operations

The archive log folder allows you to view the current archive logs in the database.

Replication Management

Replication Management enables an administrator to quickly set up, configure, and manage an Oracle replication environment. Specifically, the Replication Management functionality provides a comprehensive wizard to prepare a database for supporting multimaster and/or snapshot replication. Once set up, additional wizards help you replicate schema objects using either multimaster or a combination of multimaster and snapshot replication. After you have configured your replication environment, you can use Replication Management and the other database administration functionality in the Oracle Enterprise Manager Console to monitor and manage your replication environment.

Oracle9i JVM

Oracle9i JVM is the name of the Java engine embedded in Oracle database. CORBA and EJB components implemented in Java can be stored and executed inside the database. Client applications access these components through a name service.

Use the Oracle Enterprise Manager Console to:

• Manage the namespace

• Browse CORBA and EJB components published in the namespace

• Change permissions on the published components

• Execute the main() method in schema-resident Java classes and view the output

OLAP Services

Oracle OLAP Services is an Oracle9i database installation option that provides tools for developing and deploying internet-based business intelligence applications. OLAP (Online Analytical Processing) products provide services that support complex statistical, mathematical, and financial calculations within a multidimensional data model.

Oracle OLAP Services consists of the following components:

• Oracle's Java API for OLAP

• One or more OLAP services that run as child processes of a database instance

• An OLAP metadata repository in each database instance

• Tools within Oracle Enterprise Manager for creating OLAP metadata and managing OLAP services

For information on OLAP Services, see Oracle9i OLAP Services Concepts and Administration Guide. For information on Oracle's Java API for OLAP, see Oracle9i OLAP Services Developer's Guide to the Oracle OLAP API. For information on data warehousing, see Oracle9i Data Warehousing Guide.

Workspace Management

Workspace Management allows you to version-enable tables and create, modify, refresh, and merge workspaces.

Oracle Workspace Manager provides a long transaction framework, in which multiple data versions are stored in the database as different workspaces. Users can create new versions of data to update, while maintaining a copy of the old data.

Versioning improves concurrent access of data in the database and allows multiple what-if analyses to be run against the data simultaneously.

For detailed information about Workspace Manager concepts and the application programming interface (API), see the Oracle Workspace Manager User's Guide.

SQL*Plus Worksheet

When you need to administer your database environment with SQL, PL/SQL, or SQL*Plus commands, use Oracle SQL*Plus Worksheet. With Oracle SQL*Plus Worksheet, you can enter SQL and PL/SQL code and DBA commands dynamically and run scripts which are stored as files.

The SQL*Plus Worksheet window consists of an Input pane (top) where commands are entered, and an Output pane (bottom) where the results of your commands appear after you click the Execute button.

SQL*Plus Worksheet maintains a history of the commands you have entered, allowing you to edit and re-execute an earlier command without having to retype it. The last 50 command executions can be displayed by clicking the Command History button. Selections from the Command History dialog box can then be copied and inserted into the Input pane.

With SQL*Plus Worksheet, you can have multiple copies of the worksheet open at a time, each of which is separate from the others; so work can be committed or rolled back in each worksheet independently.

Wizards

For help with database administration tasks, Oracle Enterprise Manager offers a variety of wizards:

• Analyze Wizard: The Analyze wizard allows you to collect statistics about the objects and store them in the data dictionary, delete statistics about the objects from the data dictionary, validate the structure of the objects, and identify migrated and continued rows of tables or clusters.

• Backup and Recovery Management Wizards: The Backup and Recovery wizards are also available to help you back up or restore and recover various objects such as the database, datafiles, and tablespaces. The Backup wizard also allows you to make an image copy of the datafiles and the current controlfile.

• Create Table Wizard: The Create Table Wizard facilitates the creation of a table.

• Cube Wizard: The Create Cube Wizard helps you build a cube object. Cubes represent multidimentional data stored in your data warehouse fact tables.

• Data Management Wizards (Import/Export/Load): Oracle Data management wizards automate the transfer of data to and from an Oracle database.

• Dimension Creation Wizard: The Create Dimension Wizard will help you build a dimension object. Dimensions represent columns in your data warehouse dimension tables as levels and attributes. Dimensions typically define hierarchical relationships between their levels.

• Resource Plan Wizard: The Resource Plan Wizard helps you group user sessions that have similar processing and resource usage requirements and allocate resources among the consumer groups.

• Summary Advisor Wizard: The Summary Advisor Wizard provides advice as to which materialized views should be created, dropped, or retained.

• View Wizard: The View Wizard facilitates the creation of a view, a tailored presentation of the data contained in one or more tables (or other views).

For more information on wizards, see the Oracle Enterprise Manager Quick Tour or the Oracle Enterprise Manager Online Help.