Get Started with Oracle Enterprise Manager - Oracle
While learning the job of DBA cannot be done entirely in a few short months, a great place to start learning is this chapter. It provides an overview of the main tasks of a DBA such as the daily operations and how to manage database objects, users and space. (From the book Oracle Database 10g: A Beginner's Guide, by Ian Abramson, Michael Abbey, and Michael Corey, McGraw-Hill/Osborne, 0072230789.)
CRITICAL SKILL 3.6 -- Get Started with Oracle Enterprise Manager
Oracle Enterprise Manager (OEM) is a great tool to assist the beginner DBA through to the experienced one. You should, however, also learn the low-level commands that will allow you to do your job through an interface like SQL*Plus. OEM can help you with this by showing you the SQL that it has generated when you select the Show Sql button that exists on many windows. Given how many options OEM has to help you do your job as a DBA, we will take a quick look at them here.
First off, OEM can be used to manage all of the databases in your network. As you can see in Figure 3-3, once you expand the network, you can select Databases and manage your databases from there. The right side of this OEM panel shows all of the objects that can be managed through OEM such as instances, schemas, security, distributed, warehouse, and workspace management features.
Once you select a database, which in our example is named ora10g, you can drill down to the instance and then the configuration of that instance. Figure 3-3 shows that we can see the state of an instance as well as all initialization parameters and whether the database is running in archive log mode and if it is in a restricted state. As you can see from the tabs on the right panel of this screen, we can see and manage memory settings, recovery options, resource monitors in effect, and undo information.
Now that we have a good handle on managing our instances and databases, we can drill down to our user sessions to see exactly what is going on inside the database. By choosing a session, we can see some general information such as the user session ID, when they logged in, and what the OS username and terminal name are for this user. As you can see from Figure 3-4, we can also see the SQL that is currently running, along with the explain plan being used. You can follow the order that each explain step is being performed in by the Step # column and can step through the plan or see it in a graphical layout using the far right column. You can also manage sessions and disconnect users by right-clicking the username and issuing the kill session command. I admit that the command name may be a bit harsh, but it does get the idea across.
Resource Consumer Groups
Next, we can select the Resource Consumer Groups item to see all of the groups that exist. A resource consumer group provides a way to group together users so that they can share similar processing requirements. The DATABASE_RESOURCE_ MANAGER package is used to allocate the maximum amount of CPU that a session can use or to set a limit for parallel execution for a session or to set the number of sessions that can be active for a consumer group as a few examples of this capability. OEM can assist in managing these groups by giving us an easy way to add new groups and edit those that exist. This panel allows us to enter a description of the group and attach users and database roles to a group. If you look at Figure 3-5, you will see all of the resource consumer groups listed. If you select one of these consumer groups, you will be presented with the capabilities to manage users, roles, and general information it.
Figure 3-4.SQL explain plan
A resource plan builds on the resource consumer groups by providing a way to define how system resources will be allocated to the resource consumer groups that we just discussed. Figure 3-5 shows a list of the groups and subplans that can be set up here. We see tabs that allow us to define maximum parallelism, concurrently active sessions, undo pool space, and execution time limits for the group. Group switching allows for a session to change groups after a predefined amount of execution time has been reached. Presumably, you would move the user to a lower priority group to free resources to other sessions. The resource plan schedule can be used to set daily schedules to enable and disable resource plans. Schema, Security, and Storage Management
The next items on the OEM console are schema, security, and storage management. We will visit these in sections 3.7, 3.8, and 3.9/3.10, respectively. It is worth mentioning now, however, that all three of these can be completely managed through OEM.
Figure 3-5. Enterprise Manager resource group view
Some Oracle distributed capabilities are handled through the Distributed option. These include the ability to:
Manage in-doubt transactions that can result from two-phase commit.
Create, edit, and drop database links.
Use streams to implement messaging.
Use advanced queues and replication to pass messages and data to applications.
This can be a difficult area to manage and having a tool such as the OEM Console to help us out with this is a very welcome feature.
Warehouse options such as summary management, materialized views, and dimensions can all be dealt with through OEM, as shown in Figure 3-5.
You will notice that the toolbar has an option called Tools. This includes more advanced tools for managing our environment. Letís very quickly review the Tools that are included here. These tools can all be selected, as shown in Figure 3-6.
Database Tools to analyze data, perform backup management, and provide data management for utilities such as export, import, and load are included in the first option. Choose Tools and then Database Tools to get to these. Once Database Tools has been chosen, you will be presented with options to back up, recover, maintain, and configure your backup and recovery jobs. Backup management can be selected under Tools, as shown in Figure 3-6.
Figure 3-6. Enterprise Manage tool options
Database change management can be performed through the Change Management Pack. Under Tools, choose Change Management Pack or Standard Management Features and these will lead you to the Change Manager utility.
Database applications will provide support for the spatial index advisor, SQL*Plus Worksheet, and the Oracle Text Manager.
Tuning facilities such as performance manager, outline management, and tablespace maps are provided through Standard Management Features and Tuning Features, as shown in Figure 3-6.
As you can see from this overview of OEM console capabilities, many of the tools that we need to perform our day-to-day tasks can be found in this one console. Now that we have confidence that thereís a toolset to support us, letís take a quick look at what you need to think about when managing database objects.
This chapter is from Oracle Database 10g: A Beginner's Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.