|Oracle Enterprise Manager Administrator's Guide
Part Number A96670-01
The database administration features and wizards are integrated into Oracle Enterprise Manager. You can access the database administration features 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 Console's Databases folder allows you to administer database instances, schemas, security, and storage, and other database features from a unified tree view. The unified access to administration functions offered by the Databases folder makes it easy to switch between tasks and to gain an accurate overall view of the database configuration status. When you expand a database in the tree, a list of database features appears below.
You can use the Database folder features with or without connecting to an Oracle Management Server.
The Backup and Recovery wizards are also available to help you back up or restore and recover various objects such as the tablespaces, datafiles, or archivelogs. With the Backup wizard you can also make an image copy of the datafiles and the current controlfile. Beginning with Oracle Enterprise Manager 9.2, the Backup Wizard allows the setting of additional options, such as backup retention policy, deleting obsolete backups and specifying the archivelog deletion policy. With Oracle 9.2, recovery includes Block Media Recovery which improves the speed of recovery significantly in the case of block corruptions.
Oracle Enterprise Manager now features the SQL Scratchpad, which provides a user interface for you to enter, edit, and execute SQL quickly and easily.
This section discusses the common features shared by the database administration features in Enterprise Manager.
The Console displays a tree view of connected databases, which can be expanded to show subordinate objects.
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, setup information (Oracle_Home and Listeners), and Operating System information.
When you select any of these database features, a brief description of the feature appears in the comprehensive overview page on the right side of the Console. Depending on the feature, the page may contain a link to obtain more information, or to start a process, or a button to launch the related Quick Tour or Help screen.
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.
In most cases when you select any of the database features 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 Console, providing a quick summary of information about each object in the selected folder.
All database features 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.
You can extract information from the database such as object definitions, object dependencies, database configuration, or reports, including custom SQL queries.
You can now log all Data Definition Language (DDL) and Data Manipulation Language (DML) changes made by an application when connected to a database.
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.
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.
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.
With the database features, 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.
Database Search allows you to search for any object in a database given a flexible set of criteria:
The object definitions that match the search criteria are displayed in a multi-column table.
This section describes Database Management features and wizards.
|DBA Management Features||Task||See Page|
Manages instances and sessions
Manages schema objects
Manages security parameters
Manages database storage
Manages in-doubt transactions, database links, streams, advanced queues, and advanced replication
Manages the performance of a data warehouse (Summary Management) and OLAP metadata using the CWMLite Release 1 APIs (OLAP management manages).
Allows you to version-enable tables and create, modify, refresh, and merge workspaces
Manages the storing and retrieving of XML objects and optimizing access and updates to XML objects.
Executes SQL and PL/SQL commands
Provides a user interface for you to enter, edit, and execute SQL quickly and easily
Assist with importing, exporting, loading, backing up, and recovering data, as well as analyzing and creating tables and views
The Instance Management feature helps you manage database instances and sessions in your Oracle environment. With the Instance Management feature you can:
When you expand the Instance node under the database in the tree view, the following list of objects and folders appears:
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.
The General Page shows the following information which can be viewed and/or edited:
For information on cluster databases, refer to Oracle9i Real Application Clusters Administration.
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.
The Resource Monitors page allows you to view the performance statistics of an active plan and of each consumer group associated with the active resource plan.
The Undo Page contains information about the undo tablespace including the name of the active undo tablespace and the current undo retention time. Through this page, you can modify the retention time based on your largest transaction time and immediately view the space required for the undo tablespace. Undo generation rates are calculated based on statistics available for undo space consumption for the current instance. From this page, you can decide on an optimal size for your undo tablespace and ensure that even your longest transactions always complete.
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 Oracle9.x 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.
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. The resumable sessions are highlighted.
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.
A small clock appears on the session icon in the tree view for sessions with currently running in-progress operations. Select the Long Operations tab on the top of a Session detail view to view the status of long-running operations on Oracle8i or 9i databases. You can monitor the type of operation it is, how long it has been running, and the estimated time of completion.
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 you to lock data manually.
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.
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.
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, specify the maximum number of parallel execution servers associated with a single operation for each resource consumer group, specify the maximum number of concurrently active sessions allowed within a consumer group, specify a maximum in kilobytes on the total amount of undo generated by a consumer group, specify a maximum execution time in seconds allowed for an operation if there were no other work on the system, specifying criteria that causes the automatic switching of sessions to another consumer group, and then activate the plan.
The Resource Plan Schedule property sheet allows you to automate when to activate a resource plan.
With the Schema Management feature, 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. Storage layout information is also available for a table or an index if they are on EMC devices.
The Schema Management feature also supports index organized tables, partitioned tables and indexes, advanced queues, Java classes and sources, and unicode. Advanced queuing offers message transformation which can be used to transform and validate message communication amongst different business processes. The unicode feature allows you to select a column of "character" type and specify the length in bytes or characters.
You can also compile multiple objects such as functions, packages, package bodies, and triggers from their Summary View panel, and you can edit the storage and options information for clusters, indexes, and tables if the values apply to multiple objects.
Beginning with 9.2, you can specify your navigator preferences for Schema Management.
Databases contain at least one named schema for each database user. Regardless of object type, each schema object belongs to one of these named schemas.
If you need to edit several objects belonging to the same schema, select to view objects "First by schema and then by object type". Refer to Figure 10-5, "Schema Objects and Flat List" and Figure 10-6, "Schema Objects and Categories".
If you manage the same type of objects from different schemas, select to view them "First by object type and then by schema". Refer to Figure 10-7, "Objects and Flat List" and Figure 10-8, "Objects and Categories".
Depending on a second selection, the tree view will reorder all objects accordingly, presenting object folders in a categorized list or a flat list.
To view/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.
For clusters, indexes, and tables, you can use the Edit Multiple Objects feature to edit the storage and options information that apply to multiple objects at the same time.
The Schema Management feature 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.
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.
In a large network environment, security parameters for objects, administrators, and users are in constant change. With the Security Management feature, 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.
The Security Management feature 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.
With the Security Management role operations feature, 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.
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 feature also supports Oracle password management, which increases system security. Supported features includes: account locking, password lifetime and expiration, password history, password complexity, verifications, and export/import of passwords.
The Storage Management feature helps you administer tablespaces (permanent, temporary, and undo), datafiles, redo logs, archive logs, and rollback segments for optimum database storage.
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:
The following sections describe the Storage Management operations that can be performed with the objects in each of these folders:
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.
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.
Beginning with Enterprise Manager 9.2, you can choose to use bitmaps to manage the free space within segments. Bitmaps allow Oracle to manage free space more automatically, and offers high performance for free space management.
In addition, you can click the tablespace to see the used and free space of the tablespace or datafile.
Beginning with 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. In additon, using Enterprise Manager, you can create or alter an undo tablespace.
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. In 9.2, you can delete datafiles when dropping a tablespace. Also, The storage layout feature, which includes a page to display the storage layout information for a datafile and a page to display the overview of the file map, is available for datafiles on EMC devices.
Using the Rollback Segments folder, you can create, alter, drop, or shrink a rollback segment as well as take one off- or on-line.
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.
The archive log folder allows you to view the current archive logs in the database.
Oracle supports data replication and messaging technologies to support distributed applications and distributed database systems.
With Distributed Database Management, you can:
A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Oracle supports complex analysis of warehouse data by on-line analytical processing (OLAP) applications. Oracle also provides mechanism to improve performance of your warehouse by using summaries.
OLAP Management enables an administrator to create and edit OLAP catalog metadata for an existing star or snowflake schema. OLAP catalog metadata is required by business intelligence applications that use Oracle's Java-based OLAP API.
Within the OLAP catalog repository, owned by OLAPSYS, there are two sets of metadata tables, each with its own write APIs (PL/SQL packages): CWMLite Release 1 and CWMLite Release 2.
The OLAP Management interface within Enterprise Manager uses CWMLite Release 1 only. CWMLite Release 2 metadata must be created programmatically using the CWM2_OLAP supplied PL/SQL packages.
Both CWMLite 1 and CWMLite 2 are supported by the OLAP API.
For more information, see Oracle9i OLAP User's Guide.
Oracle supports Materialized Views, also known as Summaries, to improve the performance of a data warehouse. Summaries pre-calculate expensive joins and aggregation operations and store them in a table. Oracle speeds-up query execution by transparently re-writing queries to use summaries. Dimensions aid query re-write operation. MV Logs track changes to master table for incremental refresh of Materialized Views.
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. You 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 Oracle9i Application Developer's Guide - Workspace Manager.
Oracle provides support for an XML Database, a high-performance object system based on the XML data model. An XML Database is designed to store and retrieve XML Objects via a hierarchical pathname. It also optimizes access and updates to XML objects stored in it and provides access control and configuration management for these objects.
From the XML Database container, you can perform the following tasks:
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.
Additional information on the database administration features and wizards in the Oracle Enterprise Manager can be found in the Oracle Enterprise Manager Online Help.
Oracle Enterprise Manager now features the SQL Scratchpad, which provides a user interface for you to enter, edit, and execute SQL quickly and easily.
Features of SQL Scratchpad are listed below:
For help with database administration tasks, Oracle Enterprise Manager offers a variety of wizards:
For more information on wizards, see the Oracle Enterprise Manager Quick Tour or the Oracle Enterprise Manager Online Help.