This chapter introduces you to the Oracle Enterprise Manager, the Web-based interface for managing an Oracle database. Understanding this tool is essential to managing the Oracle database as described in the rest of this book.
This chapter contains the following topics:
Introduction to the Oracle Enterprise Manager Database Control
SQL Statements and Other Management Tools: SQL*Plus and iSQL*Plus
The Web-based Database Control serves as the primary tool for managing your Oracle database and sets a new standard in ease-of-use. It is installed with the database.
The following topics introduce you to the Oracle Enterprise Manager Database Control:
From the Oracle Enterprise Manager, you can perform administrative tasks such as creating schema objects (tablespaces, tables, and indexes), managing user security, backing up and recovering your database, and importing and exporting data. You can also view performance and status information about your database.
You can access context sensitive online Help by clicking Help displayed on every page. On any help page, click View Contents for links to all help topics. A search facility enables you to search the contents of Help.
Navigational features of Oracle Enterprise Manager include the following:
Property pages on each home page help you organize management tasks into distinct categories; for example, click Maintenance to perform tasks associated with backing up and maintaining your data.
Figure 3-1 Database Home Page Navigational Property Pages
Drill down links that provide increasing levels of detail.
Figure 3-2 Database Home Page Drill Down Links
Clicking a link shown in Figure 3-2 provides additional levels of detail about the database host name or listener.
Suggested related links for more information.
Figure 3-3 Database Home Page Related Links
To access the Oracle Enterprise Manager Console from a client browser, the dbconsole
process needs to be running on the server. The dbconsole process is automatically started after installation.
However, in the event of a system restart, you can start it manually at the command line or start it as a service in Windows.
To start the dbconsole
process from the command line:
Navigate into your ORACLE_HOME/bin
directory.
Run the following statement:
./emctl start dbconsole
Additionally, you can stop the process and view its status.
To stop the dbconsole
process:
./emctl stop dbconsole
To view the status of the dbconsole
process:
./emctl status dbconsole
In Windows, in addition to using the command line, you can start the dbconsole
process as a service.
To start dbconsole
as a service:
From the main menu, click Start, Control Panel, Administrative Tools, Services. The Services page appears.
Oracle services begin with Oracle
. The dbconsole
service is listed as OracleDBConsole
ORACLE_SID
, where ORACLE_SID
is your SID. The status of this process is listed in the Status column, either Started or Stopped.
Double click the service. The property page appears.
In the properties page, ensure that the Startup Type is either Manual or Automatic and not Disabled. Click Start, if the process is not already started. Click OK.
You can also use the Services page to stop the process.
At the end of a successful database creation, Oracle Enterprise Manager is automatically launched in your Web browser and the database is started. To access Enterprise Manager Database Control at other times, make sure the dbconsole process is running on the server, then follow these steps:
Point your Web browser to the following URL:
http://hostname:portnumber/em
For example, if you installed the database on a host computer named comp42
, and the installer indicated that your Enterprise Manager Console HTTP port number is 5500 (also recorded in the $ORACLE_HOME/install/portlist.ini
file), enter the following URL:
http://comp42:5500/em
If the database is up, Enterprise Manager displays the Database Control Login page.
If the database is down and needs to be re-started, Enterprise Manager displays the Startup/Shutdown and Perform Recovery page. If this is the case, click Startup/Shutdown and enter the host and target database login usernames and passwords. For the database user and password, use SYS
and the password you specified during installation.
Click OK to start the database. In the Confirmation screen, click YES to start the database in open mode.
Log in to the database using a username that is authorized to access the Database Control. This initially could be SYS
or SYSTEM
. Use the password you specified for the account during the database installation.
Enterprise Manager displays the Database Home page (Figure 3-4).
The property pages across the top of the page enable you to access performance, administration, and maintenance pages for managing your database. The functionality provided by these pages is discussed in other chapters of this book.
The various sections of the Database Home page and related links provide information about the database's environment and health. For example, the Alerts, Related Alerts, and Diagnostic Summary sections warn you of errors and performance problems that are impacting the operation of your database. You can click the provided links to see more detail about the problem area, and even to obtain recommendations for resolving the problem. This is discussed in Chapter 10, "Monitoring and Tuning the Database".
When you log in to the Oracle Enterprise Manager using the SYS
or SYSTEM
user account, you are logging in as the Oracle Enterprise Manager super user. These are the only accounts that are automatically granted the roles and privileges required to access all the management functionality provided the Database Control.
To grant management access to other database users, use the following procedure:
Start your Web browser and log in to the Database Control as the SYS
or SYSTEM
database user.
Click Setup at the top of the Database Home page.
Click Administrators in the left navigation bar.
If you need to create a new administrator, you must first create the user and then assign administrative privileges as described in this section. To create a new user, see "Administering Database Users".
Click Create to create a new Enterprise Manager user by assigning the management privileges to an existing database user.
Click the flashlight icon next to the Name field and select an existing database user.
Enter the password for the selected user and click Finish.
Enterprise Manager assigns the management privileges to the selected user. The selected database user is now included in the list of management users on the Setup Administrators page. All the users shown on this page can log in to the Database Control and perform database management tasks.
Enterprise Manager enables you to set up preferences that help you manage the database. These include the following:
These settings enable Oracle to e-mail you alerts. Alerts are notifications of when a database is in an undesirable state and needs your attention. By default, the Enterprise Manager home page lists all alerts. However, set up is required for e-mail notification. For more information about alerts and setting up notifications, see "Setting up Direct Alert Notification" in Chapter 10, "Monitoring and Tuning the Database".
When you have planned downtime for database maintenance, you can indicate that you do not want false alerts to be sent to you. To do so, you define a blackout period. See "Defining Blackout Periods" .
Enterprise Manager can automatically execute many routine administrative tasks, such as backups. This is done using sophisticated job scheduling system built into the Enterprise. To keep your environment secure, setting up tasks for automatic execution in Enterprise Manger requires you to provide login information for the machine and database. To avoid having to enter this information every time you create a job or task, Enterprise Manager enables you to save this information as preferred credentials. Preferred credentials are stored in the database in encrypted mode to protect them from unauthorized use. See "Setting Preferred Credentials" .
When you plan to bring your database down for maintenance, you can indicate that you do not want alert notifications to be sent to you. Alerts are notifications of when the database is in an undesirable state and needs your attention. For more information about alerts, see "Alerts".
To define a blackout time period, follow these steps
From the home page, click Setup at the top of the page. The Setup page appears.
Click Blackouts in the left hand pane. The Blackouts page appears.
Click Create to start the Create Blackout wizard. On the Properties page, name your blackout, enter a reason, and select the target that has planned downtime. Click Next.
On the Schedule page, enter the start time of your planned blackout, or choose Immediately if you are bringing the database down now. Also select the duration of the blackout, either as indefinite, as a length or time, or until a time in the future. Under Repeating, accept the default of Do Not Repeat. If you want to repeat the blackout periodically, you can select a repeat frequency in the pull down menu. Click Next.
The review page appears. Review what you have entered, and click Finish. You can click Back to change a setting.
You can have Enterprise Manager automatically fill in host and database login credentials for you, such as when you schedule jobs and tasks to perform administrative operations like backup and recovery. For security, Oracle stores preferred credentials in encrypted mode.
To set preferred credentials for the database, do the following:
From the home page, click Preferences at the top of the page. The Preferences page appears.
Click Preferred Credentials in the left hand pane. The Preferred Credentials page appears. For the Database target, click the icon under Set Credentials. The Database Preferred Credentials page appears.
For your database, enter the credentials for Normal Username/Password, SYSDBA Username/Password, and Host Username/Password.
Click Test to test your credentials. You should get a confirmation message if your credentials can be verified.
Click Apply to apply the changes.
The underlying operations in Enterprise Manager are performed using Structured Query Language (SQL) statements. SQL is an English-like computer programming language for accessing and manipulating data contained in a database. It is an industry-standard language for accessing databases.
The following is an example of a SQL query:
SELECT COUNTRY_ID, COUNTRY_NAME FROM HR.COUNTRIES;
SQL is a powerful language and can be used to perform a variety of database administrative tasks. While performing these tasks, you can click Show SQL to see the underlying SQL statements being issued.
For more information on SQL and on using SQL for database administration, see Oracle Database SQL Reference.
In addition to Enterprise Manager, you can use other Oracle tools to issue SQL statements such as SQL*Plus and its Web version iSQL*Plus. These tools enable you to perform the same database management operations, as well as to query, insert, update or delete data directly in the database.
SQL*Plus is a command line program you use to issue SQL statements to an Oracle database. You can issue these statements or in batch. SQL*Plus is installed with the database and is located in your $ORACLE_HOME/bin
directory.
To start SQL*Plus, enter the following at the command line on all platforms:
sqlplus username/password
You can use the SYS
administrative user and password you set up during installation. SQL*Plus starts and connects you to the default database. At the SQL prompt, you can enter statements that perform administrative tasks such as database startup and shutdown. You can also query, insert, update, and delete data.
On Windows, you can use the SQL*Plus Windows GUI to administer the database. From the main menu, select Start, Programs, Oracle-OraDB10g_home1, Application Development, SQL*Plus.
For more information about SQL*Plus, see SQL*Plus User's Guide and Reference.
For more information about SQL statements, see Oracle Database SQL Reference.
iSQL*Plus is the Web version of SQL*Plus. Before you can connect to it with a URL, you must start the iSQL*Plus application server. To do so, enter the following at the command line:
isqlplusctl start
After the server is started, you can connect to iSQL*Plus with a URL such as
http://machine_name:5560/isqlplus
Alternatively, you can connect to iSQL*Plus using Enterprise Manger. To do so from the Enterprise Manager home page, click iSQL*Plus under Related Links.
For more information about iSQL*Plus, see SQL*Plus User's Guide and Reference.
The following are some specific tasks to perform to become familiar with your database. They are presented as links to the chapters that contain information about these tasks and other related database management tasks.
Start up your instance and open the database. At the end of installation, your instance is already started and your database is open. In the future there will be times, perhaps for doing database maintenance or because of a power or media failure, that you shut down your database or instance and later restart it. See "Shutting Down and Restarting the Instance and Database with Enterprise Manager".
Optionally configure the network environment to enable clients to connect to your database. See Chapter 4, "Configuring the Network Environment".
Review your database storage structure: tablespaces and datafiles, redo log files, and control files. See Chapter 6, "Managing Database Storage Structures".
Review, unlock, and reset passwords as necessary for initial database users. Create new users and assign privileges and roles to them. See Chapter 7, "Administering Users and Security".
Create users and necessary schema objects, including tablespaces and tables. Populate tables with data. See Chapter 8, "Managing Schema Objects".
Review a backup strategy for the database and back up the database. See Chapter 9, "Performing Backup and Recovery".
Enable archiving of redo logs, if not already done. See "Configuring Your Database for Basic Backup and Recovery".
Monitor database performance, diagnose performance problems and tune the database as necessary. See Chapter 10, "Monitoring and Tuning the Database".
Keep Oracle software up-to-date with the latest patch releases. See Chapter 11, "Managing Oracle Software".
Oracle by Example (OBE) has a series on the Oracle Database 2 Day DBA book. This OBE steps you through the tasks in this chapter, and includes annotated screen shots.
To view the Getting Started OBE, point your browser to the following location:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/2day_dba/gettingstarted/gettingstarted.htm