DBA Tools

There is no one right way to do your job. As a DBA, you need to be flexible and able to use as many of these tools to do your job as you can because different positions require use of different interfaces.

SQL*Plus

Oracle has provided SQL*Plus as a tool that allows users, developers, and DBAs to connect to, interact with, and manipulate the database. SQL*Plus provides a command-line interface through which you can provide commands to the database.

SQL*Plus provides the capability to start up and shut down the database, run and create queries, add or delete rows in tables, modify data that exists in tables, and write custom text reports drawing data out of the database using a subset of the ANSI standard SQL with the addition of specific (PL/SQL) additions.

SQL*Plus runs natively on whatever platform you have your database installed on and can be run as a client application, connecting to the database remotely.

Figure 2.3 shows an example of SQL*Plus running in a Unix environment natively, and Figure 2.4 shows a Windows version of SQL*Plus connecting to a database, regardless of where the database is running.

Figure 2.3. SQL*Plus in a Unix environment.

Figure 2.4. SQL*Plus in the Windows environment.

But SQL*Plus isn't the only Oracle provided tool that you can use to help administer your database. Oracle Enterprise Manager (OEM) is a fully functional graphical user interface into the database that can assist you with many of your complex day-to-day database tasks.

iSQL*Plus is a browser-based implementation of the SQL*Plus interface. iSQL*Plus can be used to connect a user to an Oracle database over the Internet to perform the same tasks that can be performed through the traditional SQL*Plus command-line interface. Although the interface is supported only in the Windows platform at this time, iSQL*Plus allows users to use a Web browser as their interface through which it makes use of an Oracle HTTP Server with the iSQL*Plus Server add-on to access an Oracle database.

Oracle 9i provides iSQL*Plus only on Windows platforms.

Oracle Enterprise Manager (OEM)

OEM serves as a graphical centralized systems management console primarily for the use of the DBA. It provides a common set of services that help to manage the Oracle environment, including integrated applications. It can manage not only your databases but also web servers and the listeners associated with both. Through OEM you can administer, diagnose, and tune multiple databases across multiple servers, including parallel servers and replicated databases, from one central location.

OEM's unified management framework is a Java-based console that encompasses a suite of tools and services, a network of servers and intelligent agents. It can provide both a hierarchical tree view of the overall system and a graphical representation of the objects and their relationships in the system.

Through OEM, you can use the following components simply and elegantly:

• Instance Manager performs startup, shutdown, and monitoring of the database. Figure 2.5 shows an example of what can be found in the Instance Manager.

  Figure 2.5. Instance Manager.

  
  
  

• Security Manager allows you to manage users and their privileges and roles. Figure 2.6 shows an example of what can be found in the Security Manager.

  Figure 2.6. Security Manager.

  
  
  

• Storage Manager assists you in maintaining tablespaces, data files, rollback segments, and redo log groups. Figure 2.7 shows an example of what can be found in the Storage Manager.

  Figure 2.7. Storage Manager.

  
  
  

• Schema Manager helps you to create and manage objects such as tables, indexes, clusters, views, synonyms, stored procedures, and other program units. Figure 2.8 shows an example of what can be found in Schema Manager.

  Figure 2.8. Schema Manager.

  
  
  

• SQL*Plus Worksheet provides the capability to issue SQL statements against any database. Figure 2.9 shows the SQL*Plus Worksheet interface.

  Figure 2.9. SQL*Plus Worksheet.

  
  
  

Two additional subcomponents allow you to perform hands-off monitoring and maintenance of the database. These are the Job Scheduling System and the Event Management System and are discussed in the following sections.

Job Scheduling System

The Job Scheduling System allows you to schedule and manage any job tasks that an administrator can perform through the operating system locally or with SQL scripts and perform this administration across the network, even remotely across an intranet.

With the Job Scheduling System, you can perform tasks on multiple databases or other managed nodes without having to maintain active connections to all targets, and what's more, the scheduled jobs can run simultaneously on different nodes in the system without your even having to be there to launch or monitor them.

The three tiers of OEMConsole, Oracle Management Server, and Intelligent Agentsrunning on the managed nodes, all work together to schedule and execute the desired jobs.

Event Management System

The Event Management System allows you to efficiently monitor a large system even from remote locations. Using the Event Management System together with Intelligent Agent monitored managed nodes, you can effectively monitor any number of databases, nodes, or other targets 24 hours a day. You can have the system alert you whenever a problem arises or a specific condition is detected, or you can have the system take its own action (for example adding another 25MB to a tablespace if the tablespace approaches the point of running out of space). You can pinpoint only the targets you want to monitor or monitor the overall system.

Events are, simply, a group of tests that you want to have routinely run on your managed systems. OEM includes a wide variety of predefined tests that you can use when creating your own events. The event tests are grouped by target type, for instance:

• Databases

• Listeners

• HTTP servers

• Concurrent managers in Oracle E-Business Suite

• Nodes

You can create your own custom events using the predefined event tests that have been installed with OEM as the basis. Your custom events are created with information that you enter in the Event property sheet of the Event Management System. You determine the parameters necessary for the events that you are definingparameters such as the target being monitored, the specific tests that you want to have the Event Management System perform, the frequency that you want to have the event test executed, and whether other administrators can share the events and which administrators should be notified if the event condition is met. Some of the predefined event tests have their own parameters with threshold values that you can customize for your system.

As a part of the management server, you can create an Oracle Management Server (OMS) repository. Within this repository will reside all the relevant information that will allow jobs to run and that will allow actions to be taken based on those jobs and their outcomes.

OMS Repository

The repository is a set of tables that gets created when you set up OMS. OMS uses this repository as its persistent back-end data store. It can be configured to allow for historical trending and so that more than one OMS can share a repository to provide fault tolerance to the system and ensure the accessibility and reliability of the OEM interface to the system.

OEM allows you to perform both routine and advanced administrative tasks and to automate those that meet your own defined criteria. Optional add-on packs are available that can allow you to perform even more tasks. These add-ons include the Diagnostic Pack, Tuning Pack, Change Management Pack, and Oracle Applications Manager (designed primarily for Oracle E-Business Suite).

OEM is based on a highly scalable three-tier model architecture and consists of the components discussed in the following sections.

OEM Console

The first tier of the system is comprised of the client consoles and management applications that provide the graphical interface to the administrators for all relevant tasks. This tier can either be a standalone front-end through which the maintenance tasks are performed, or a web interface through which the DBA can maintain the database. This first tier depends on the second tier, the Oracle Management Server, for the bulk of its application logic.

The Console is the central launching point for all applications. It can be run in either thin client (through a web browser) or fat client (locally installed client Console files) mode and can be launched either as standalone or through the Oracle Management Server.

Through this central interface, you can access Instance Manager, Schema Manager, Storage Manager, and SQL*Plus Worksheet.

You can launch the Console either by entering the following at the command line

 oemapp console 

or by selecting Start, Programs, Oracle Enterprise Manager Console.

At the Login dialog box (see Figure 2.10), you can determine whether you want to run the application in standalone mode or from the OMS service.

Figure 2.10. OEM Login dialog box.

For the purpose of the exam, it generally is assumed that you will be running standalone.

After you are logged in, you can add databases to the database tree and start maintaining that database. To do this, you need to select Add Database to Tree from the Navigator menu and enter the values for Hostname, SID, Port Number, and Net Service Name of the desired database.

Now that you have a database, you can expand this working database from the Database folder, right-click the working database, and select Connect. Enter the username, password, and service name for the given database and click OK.

The Add Database to Tree dialog box appears automatically when you launch the Console in standalone mode the first time. It assumes that you want to monitor or maintain a database when you launch the Console, and, because it doesn't have one already discovered, it is waiting for you to tell it where you want it to go.

Oracle Management Server (OMS)

The OMS tier is the core of the OEM framework. It provides the administrative user accounts, processes the functions (jobs and evens), and manages the distribution of information between the first and third tier (the managed nodes).

OMS uses a repository schema (either in its own database or in a schema in an existing database) to store all system data, all application data, information about all managed nodes, and information concerning any add-on packs that you might have purchased and are running.

Managed Nodes

The third tier, the managed nodes, contains the targets for the management server. These targets can be databases or any other managed service.

Residing on each node has to be an Oracle Intelligent Agent, which communicates with the OMS and performs tasks on the managed services sent by the consoles and the client management applications. Only one Intelligent Agent is required per node, regardless of how many services and databases are managed on that node.

The Intelligent Agent functions totally independently from the databases and independently from the Console and Management Servers. This independence allows the Intelligent Agent to perform tasks such as starting up and shutting down the database and remaining operational if any part of the system becomes unavailable for any reason. This also allows the Intelligent Agents the capability to alert the responsible people when problems exist in the system that make the services unavailable.