Oracle Enterprise Manager Administrator's Guide Release 2.2 Part Number A85248-01 |
Library | Service | Contents | Index

# 6
DBA Management Pack

The primary database administration components of Oracle Enterprise Manager are contained in the DBA Management Pack, which is a suite of database administration tools and wizards bundled with Oracle Enterprise Manager. This functionality can be accessed with the new DBA Studio tool or launched separately from the Oracle Enterprise Manager Console or operating system. DBA Management Pack features can be used to perform most of your database administration tasks, and support all versions of Oracle databases.

DBA Studio or individual DBA Management Pack tools can be launched by either connecting to the Oracle Management Server using Oracle Enterprise Manager's three-tier framework, or by connecting directly to a database.

DBA Management Pack includes the following functionality:

- Instance Management

- Schema Management

- Security Management

- Storage Management

- Replication Management

- JServer Management

- Cache Management

- SQL*Plus Worksheet

With this functionality you can:

- Complete database administration operations quickly and conveniently using a mouse rather than manually entering SQL commands

- Concurrently perform multiple tasks in multiple Oracle Enterprise Manager windows

- Administer multiple databases simultaneously

- Perform database administration tasks from a central location for both local and remote databases running on any Oracle platform in any location worldwide

DBA Management Pack also includes wizards for assisting with such tasks as importing, exporting, loading, backing up, and recovering data.

This chapter contains the following sections:

Topic | See Page
--- | ---
DBA Studio | 6-2
Common Features of DBA Management Pack Functionality | 6-3
DBA Management Pack Functionality and Wizards | 6-5

## DBA Studio

DBA Management Pack features can now be accessed from the new DBA Studio, as well as from the Oracle Enterprise Manager Console or operating system. DBA Studio is an interface from which all DBA Management Pack features can be accessed (with the exception of SQL*Plus Worksheet) and can either be run with a connection to an Oracle Management Server, which utilizes Oracle Enterprise Manager's three-tier framework, or connected directly to a database.

With DBA Studio, you can manage database security, schemas, instances, and storage from a unified tree view without launching separate tools. This integrated access to DBA functionality simplifies switching between DBA tasks and provides an accurate view of database configuration status.

DBA Studio can be launched from the operating system, from the Enterprise Manager Console, or from a browser.

## Common Features of DBA Management Pack Functionality

This section discusses the common features shared by the different tools in DBA Management Pack.

### Tree Views and Property Sheets

Most DBA tools display a tree view of connected databases, which can be expanded to show subordinate objects. When you select a database or 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

When you select a folder in a tree list, 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 DBA Management Pack tools 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, and save it in a variety of formats (text, HTML, or comma-separated-values).

### 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. In DBA Studio, you can display the Data Definition DDL for objects.

### Show SQL

Though one of the benefits of DBA Management Pack 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, 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 DBA Management Pack, 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.

### Font Settings

DBA Studio provides you the option of changing the font settings for its graphical interface. You can apply a different font or different font size, apply or remove the bold style, apply or remove italics style, or restore the application's default font.

### Advanced Mode

DBA Management Pack features can be run in Advanced Mode, which displays additional tabbed pages in property sheets from which a tool's advanced database administration features can be accessed.

## DBA Management Pack Functionality and Wizards

This section describes DBA Management Pack's features and wizards.

DBA Studio Tool | Task | See Page
--- | --- | ---
Instance Management | Manages instances and sessions | 6-6
Schema Management | Manages schema objects | 6-10
Security Management | Manages security parameters | 6-13
Storage Management | Manages database storage | 6-16
Replication Management | Enables an administrator to quickly set up, configure, and manage an Oracle replication environment | 6-18
JServer Management | 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. | 6-19
Cache Management | Enables you to set up, manage, and monitor caches created by Oracle8i Cache. | 6-19
SQL*Plus Worksheet | Executes SQL and PL/SQL commands | 6-20
Wizards | Assist with importing, exporting, loading, backing up, and recovering data, as well as analyzing and creating tables and views | 6-20

### Instance Management

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

- Start up and shut down a database

- View and edit the values of initialization parameters

- View current session SQL and the explain plan

- Manage stored configurations (when connected to the Oracle Management Server)

- Manage resource allocations (with Oracle8i)

- Manage users' sessions

- Monitor long running operations (with Oracle8i)

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

- Database

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

- Sessions

- In-Doubt Transactions

- Resource Consumer Groups (with Oracle8i)

- Resource Plans (with Oracle8i)

- Resource Plan Schedule (with Oracle8i Release 2)

#### Figure 6-1 Instance Management Tree

Text description of the illustration instlist.gif

#### Database Operations

When you select the Database 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 database, including the database version and any installed options, and allows you to start up and shut down a database

- Memory usage of the current database

- Current state of redo log archival

- Resource management (with Oracle8i)

#### Figure 6-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.

#### Sessions

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.

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

Note: The scheduling job is implemented using DBMS_JOB.

### Schema Management

With DBA Management Pack's 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. Oracle Schema Management features also support Oracle8i objects, which include index organized tables, partitioned tables and indexes, advanced queues, and Java stored procedures.

#### Figure 6-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 6-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.

Schema Management features also include 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 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 DBA Management Pack's Security Management features, 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 6-5 Managing Users, Roles, and Profiles

Text description of the illustration security.gif

#### Figure 6-6 Security Management Window

Text description of the illustration securita.gif

#### User Operations

Security Management features help you manage the database users in your network by helping you create users and clones of users, add and remove user permissions and roles, and alter user properties, including account status and default profiles. Security Management capabilities also allow you to easily see users' dependents and dependencies.

#### Role Operations

With Security Management role operations features, 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.

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

Security Management features also support Oracle8 password management, which increases system security. Supported features include: account locking, password lifetime and expiration, password history, password complexity, verifications, and export/import of passwords.

### Storage Management

DBA Management Pack's Storage Management features help you administer tablespaces, datafiles, redo logs, and rollback segments for optimum database storage.

#### Figure 6-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 6-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, 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. You can also click the tablespace to see the used and free space of the tablespace or datafile.

#### 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

The Replication Management tool is part of DBA Studio and enables an administrator to quickly set up, configure, and manage an Oracle replication environment. Specifically, the Replication Management tool 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 the Replication Management tool and the other tools in DBA Studio to monitor and manage your replication environment.

### JServer Management

JServer is the name of the Java engine embedded in Oracle8i 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 DBA studio 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

### Cache Management

With Cache Manager, you can set up, manage, and monitor caches created by Oracle8i Cache.

Oracle8i Cache increases the capacity of applications, such as Web applications, enterprise resource planning (ERP) applications, and vertical applications, by adding intelligent middle-tier caching. Use Oracle8i Cache to cache frequently used data on the middle-tier node, increasing the number of requests that your environment can process.

Oracle8i Cache maintains a history of queries and, based on that history, intelligently routes queries either to the caches on the middle-tier nodes or to the origin database. The origin database is the original and primary storage for the data. For more information about Oracle8i Cache, see the Oracle8i Cache Concepts and Administration Guide.

Cache Manager lets you:

- Add tables to or remove tables from one or more caches.

- Allocate disk space and memory to the caches.

- Set the synchronization policy of each table.

- Synchronize tables in one or more caches.

- Start and stop caches and enable and disable caching.

- Monitor the ratio of queries that are satisfied by the cache to the queries that are routed to the origin database.

- Monitor the performance of the caches.

- Update the list of users in a cache.

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

SQL*Plus Worksheet can be accessed from the Enterprise Manager Console or the browser.

Note: Additional information on all tools and wizards in DBA Management Pack can be found in the Oracle Enterprise Manager online Help.

### Wizards

For help with database administration tasks, the DBA Management Pack offers a variety of wizards:

- Analyze Wizard

- Backup and Recovery Management Wizards

- Create Table Wizard

- Data Management Wizards (Import/Export/Load)

- Dimension Creation Wizard

- Resource Plan Wizard

- Summary Advisor Wizard

- View Wizard

These wizards increase the ease-of-use of the DBA applications.

For more information on wizards, see the DBA Management Pack Quick Tour or the Oracle Enterprise Manager online Help.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index