In previous release 11G to manage database you could use Oracle Enterprise Database Console. In Oracle 12C it is replaced by new lightweight administration tool Enterprise Manager Database Express.

Features of the new version:

• out-of-box simple management for single database (or database cluster)
• small footprint 50-100 MB in database
• minimal requirements for CPU in database(sql calls), rendering is done on client side
• simple configuration
• includes
  • performance monitoring
  • configuration management
  • administration
  • diagnostic and tuning
• interface in web browser(requires flash plugin)

Architecture

Built-in webserver XML DB in database generates XML that are later rendered by the web browser.EM servlet in database handles the requests, including authentication, session management, compression and caching.

Configuration

Configuration is very simple:

1. Parameter DISPATCHER should be verified. At least one dispatcher must be configured for XMLDB service. In Oracle 12C component XMLDB is always installed.

dispatchers=(protocol=tcp)(service=ORA12CXDB)

2. Configure port for the server

secured port (secured with SSL)

exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

non secured port – in this case 0 blocks non secured connections

exec DBMS_XDB_CONFIG.SETHTTPPORT(0);

Ports can be verified using simple command

select DBMS_XDB_CONFIG.GETHTTPSPORT 
from dual;

GETHTTPSPORT
------------
        5500

select DBMS_XDB_CONFIG.GETHTTPPORT 
from dual;

GETHTTPPORT
-----------
          0

3. Connect to Enterprise Manager Database Express console with

--secured connection
https://hostname:port/em

--non secured connection
http://hostname:port/em

For nonadministrative users to have access to EM Express, they must be granted role:

• EM_EXPRESS_BASIC – enables users to connect to EM Express and to view the pages in read-only mode. Includes SELECT_CATALOG_ROLE.
• EM_EXPRESS_ALL – enables users to connect to EM Express and use all the functionality provided by EM Express (read/write access to all EM Express features)

Other option is to login as SYS or SYSTEM user but Oracle recommends to create dedicated user with one of above role.

Menu options

Following menu options are available in Enterprise Manager Database Express:

• Initialization Parameters

On this page you can change instance parameters in memory/spfile.

• Memory

This page presents current and historical memory distribution/usage used by instance. It enables to change memory settings – link “Configure Memory”.

• Database Feature Usage

On two tabs are presented information from DBA_FEATURE_USAGE_STATISTICS and view DBA_HIGH_WATER_MARK_STATISTICS. Remember some of database options are extra paid if you don’t use them turn them off and save money.

View DBA_FEATURE_USAGE_STATISTICS shows which database options were used.

View DBA_HIGH_WATER_MARK_STATISTICS displays information about database high-watermark statistics like:

• Number of User Tables

• Size of Largest Segment (Bytes)

• Maximum Number of Partitions belonging to an User Table

• Maximum Number of Partitions belonging to an User Index

• Number of User Indexes

• Maximum Number of Concurrent Sessions seen in the database

• Maximum Size of the Database (Bytes)

• Maximum Number of Datafiles

• Maximum Number of Tablespaces

• Maximum Number of CPUs

• Maximum Query Length

• Maximum Number of Services

• Current Database Properties

It presents basic properties for your database. The informations are from view DATABASE_PROPERTIES.

• Tablespaces

This page enables to manage tablespaces in database like Create/drop tablespace, add/drop datafile etc.

• Undo Management

Show statistics about UNDO segments in database. You can switch current UNDO tablespace to other one.

• Redo Log Groups

Enables to manage redo log groups in database.

• Archive Logs

Shows information about archivelogs generated in the database.

• Control Files

Just presents information about control files, backup control file to trace.

• Options Users, Role, Profiles

Here you can manage users, role and profile in the database

• Performance Hub

The most interesting part EM Express. Here you can find very detailed information about current performance of your database in real time and in historical mode.

• Real time – performance data is retrieved from in-memory views
• Historical – performance data is retrieved from the Automatic Workload Repository (AWR)

Short description for each tab

• Summary – available in both realtime and historical mode.  In realtime mode, this tab shows metrics data that gives an overview of system performance in terms of Host Resource Consumption (CPU, I/O and Memory), and Average Active Sessions.  In historical mode, the tab displays system performance in terms of resource consumption, average active sessions, and load profile information.
• Activity – this tab displays ASH Analytics, and is available in both realtime and historical mode.
• Workload – this tab is available in both realtime and historical mode, and shows metric information about the workload profile, such as call rates, logon rate and the number of sessions.  In addition, the tab also displays the Top SQL for the selected time range.  In realtime mode, the tab displays Top SQL only by DB time, but in historical mode, the user can also display Top SQL by other metrics, such as CPU time or Executions.
• RAC – this tab is only available in RAC.  It displays RAC-specific metrics such as the number of global cache blocks received, and the average block latency.
• Monitored SQL – this tab displays Monitored Executions of SQL, PL/SQL and Database Operations, and is available in both realtime and historical mode.
• ADDM – this tab is available in both realtime and historical mode.  It displays ADDM and Automatic Real Time ADDM reports.
• Current ADDM Findings – this tab is available only in realtime mode, and displays a realtime analysis of system performance for the past 5 minutes.  The contents of this tab are populated only if the time selector includes the current time, and the findings displayed are always for the past 5 minutes regardless of the length of the selected time range.

• SQL Tuning Advisor

The SQL Tuning Advisor analyzes individual SQL statements, and suggests indexes, SQL profiles, restructured SQL, and statistics to improve their performance. It runs automatically during the maintenance window or can be invoked manually on selected SQL statements.

Have a fun 🙂

Tomasz