Oracle Knowledge Table

Thursday, September 4, 2014

AWR Warehouse - The what and where

Am not talking how cool this feature is, just want to give some information on the prerequisites, where in documentation you will find more details and what support notes are relevant.

Hope this is helpful.

AWR Warehouse requires Diagnostic Pack
AWR Warehouse repository is certified on DB12.1.0.2 and 11.2.0.4 (+ patch*) as a repository and requires an EM patch to enable the features in EM 12.1.0.4
Discover the database within Enterprise Manager Cloud Control 12.1.0.4
Apply the OMS patch (see Support Note for patch details). This will enable AWR Warehouse in the Databases menu.
Apply the Agent patch (see Support Note for patch details) to the agent on each of the servers running the source database that will send AWR data to the AWR warehouse.
Configure the AWR warehouse in EM to point to the database designated as the AWR Warehouse repository. One EM environment can only support one AWR Warehouse repository in this release.
Add each of the source databases (using DBA preferred credentials) to the AWR warehouse.

Documentation

- (in my opinion quite hidden) is in DB12.1.0.2 - 2 Day + Performance Tuning Guide - here

- Support note 1907335.1: Master Note on AWR Warehouse

*For 11.2.0.4, use the below patch:
o (Linux x86-64) 18547891: https://support.oracle.com/epmos/faces/PatchDetail?patchId=18547891&requestId=17617464
o (Exadata BP 11.2.0.4.4) 18547891: https://support.oracle.com/epmos/faces/PatchDetail?patchId=18547891&requestId=17617463

Tuesday, July 22, 2014

Oracle Database 12.1.0.2 is out

Many interesting new features and improvements:

Oracle Database In-Memory
Oracle Big Data SQL
Oracle JSON Document Store
Oracle REST Data Services
Improvements to Oracle Multitenant
Advanced Index Compression
Zone Maps
Approximate Count Distinct
Attribute Clustering
Full Database Caching
Rapid Home Provisioning

Available on edelivery at edelivery.oracle.com

Friday, May 30, 2014

How to discover new PDB targets in EM12c

I just created a new Pluggable database in my container, PDB2. I want to discover it in EM12c Rel 3.

Go to Setup -> Add Target -> Add Targets Manually

Choose "Add Targets Using Guided Process", for Targets type choose Oracle Database, Listener and ASM

Specify host, then click next

Input the pass and then Next

Review the findings and click save

The confirmation window appears

Going to Targets -> Databases, you can find the pluggable database in the list.

Thursday, May 8, 2014

EM12c: Job Step Scheduler is shown as down – EM jobs remain in scheduling indefinitely

Problem:

EM12c: Job Step Scheduler is shown as down – EM jobs remain in scheduling indefinitely and don't run

Change made:

Upgrade to Rel 3 12.1.0.3 and apply DB Plugin 12.1.0.5

The repository job called “Job Step Scheduler” is shown as down on Management Services and Repository Page.

Go to Setup > Manage Cloud Control > Repository

In Repository Scheduler Jobs Status section, the "Job Step Scheduler" job is shown as down with a red arrow.

All the other jobs look good and are running successfully.

So, found this metalink note, and applied the same logic

EM 12c : After Upgrade to R2 12.1.0.2 Repository Job Purge Is Shown As Down on Management Services And Repository Page (Doc ID 1496431.1)

SQL> select dbms_jobname from mgmt_performance_names where display_name = 'Job Step Scheduler';

EM_JOB_OBSERVER.run_cycle

SQL> select job from all_jobs where what='EM_JOB_OBSERVER.run_cycle'

no rows selected

SQL> select count(*) from all_jobs

SQL> select owner, job_name, job_action, enabled, state, failure_count, last_start_date, last_run_duration

from dba_scheduler_jobs where job_name LIKE 'EM_JOB%'

SYSMAN EM_JOB_PURGE_POLICIES EM_JOB_PURGE.APPLY_PURGE_POLICIES(); TRUE SCHEDULED 0 08-MAY-14 05.07.11.883712000 AM -05:00 0 0:0:17.949089

SYSMAN EM_JOBS_STEP_SCHED EM_JOB_OBSERVER.run_cycle FALSE DISABLED 0 24-JAN-14 06.29.08.570318000 PM EST5EDT 0 0:0:0.013817

I can see the job is disabled.

I will enable it

SQL> BEGIN

sys.dbms_scheduler.enable(name=>'"SYSMAN"."EM_JOBS_STEP_SCHED"');

END;

SQL> select owner, job_name, job_action, enabled, state, failure_count, last_start_date, last_run_duration

from dba_scheduler_jobs where job_name LIKE 'EM_JOB%'

SYSMAN EM_JOBS_STEP_SCHED EM_JOB_OBSERVER.run_cycle TRUE SCHEDULED 0 24-JAN-14 06.29.08.570318000 PM EST5EDT 0 0:0:0.013817

SYSMAN EM_JOB_PURGE_POLICIES EM_JOB_PURGE.APPLY_PURGE_POLICIES(); TRUE SCHEDULED 0 08-MAY-14 05.07.11.883712000 AM -05:00 0 0:0:17.949089

The job is now scheduled.

Will also update mgmt._performance_names

SQL> UPDATE MGMT_PERFORMANCE_NAMES

SET display_name = NULL,dbms_jobname = NULL, is_dbmsjob = 'N', is_deleted = 'Y'

WHERE UPPER(DBMS_JOBNAME) LIKE 'EM_JOB_OBSERVER.RUN_CYCLE';

1 row updated

SQL> COMMIT;

Now checking the status of the job shows to be up.

The jobs run successfully again.

References

EM 12c : After Upgrade to R2 12.1.0.2 Repository Job Purge Is Shown As Down on Management Services And Repository Page (Doc ID 1496431.1)

EM 12c: The EM Jobs Service Target is Shown with Down Status in the Enterprise Manager 12.1.0.2 Cloud Control Console (Doc ID 1589084.1)

Monday, May 5, 2014

Compliance policies - DBLM

Recently had to retrieve the list of all Compliance Standard Rules in EM12c Rel 3.
I had to build a script to do this as Compliance Standard Rules cannot be exported within the Cloud Control interface (or at least was not able to do it after selecting all rules).

The script I come up with is the following:

SELECT rule_dname,description,rationale,
target_type,
decode(SEVERITY,20,'Warning',25,'Critical',18,'Minor Warning') as Severity
FROM EM_RULE
WHERE target_type in ('oracle_database','oracle_listener','rac_database','oracle_pdb','oracle_dbmachine')
order by target_type, rule_dname;

A sample of the output (unformatted) looks like the below:

RULE_NAME

"Domain Users" Group Member of local "Users" Group

$ORACLE_HOME/network/admin Directory Owner

$ORACLE_HOME/network/admin File Permission

$ORACLE_HOME/network/admin File Permission(Windows)

Access to *_CATALOG_* Roles

Access to ALL_SOURCE View

DESCRIPTION

Ensures domain server local Users group does not have Domain Users group

Ensures $ORACLE_HOME/network/admin ownership is restricted to the Oracle software set and DBA group

Ensures the files in $ORACLE_HOME/network/admin ownership is restricted to the Oracle software set, group is restricted to DBA group and Public does not have write permission

Ensure grant of *_CATALOG_* is restricted

Ensures restricted access to ALL_SOURCE view

RATIONALE

Including Domain Users group in local Users group of a domain server can cause serious security issues.

Not restricting ownership of network/admin to the Oracle software set and DBA group may cause security issues by exposing net configuration data to malicious users

TARGET_TYPE	SEVERITY
oracle_database	Warning
oracle_database	Warning
oracle_database	Warning
oracle_database	Warning
oracle_database	Critical

Friday, January 24, 2014

Predicate N = N-1 returns rows?

Following a tweet from @jeffreykemp "Quiz Q for the day: "select * from T where N = N - 1;" returns rows - how?", I did a small setup to try to answer the question.

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--number

SQL> drop table tt
2 ;

Table dropped.

SQL> create table tt as select 1/0f N from dual;

Table created.

SQL> desc tt;
Name Null? Type
----------------------------------------- -------- -------------
N BINARY_FLOAT

SQL> select * from tt where N=N-1;

N
----------
Inf

SQL>

Tuesday, January 7, 2014

EM12c - Deploy the Latest Database Plugin to the OMS in offline mode

As of the date this was written, the latest EM12c database plugin is 12.1.0.5.0.

If you are a fan of command line utilities over the GUI interfaces (like me), you will love emcli which will help accomplish many operations in EM12c.

The steps would be:

1. Aquire the latest database plug-in software using Self Update

2. Deploy the plugin on the management server aka OMS

We will now synchronize emcli with the OMS

[oracle@em12c bin]$ ./emcli sync

Error: Session expired. Run emcli login to establish a session.

Let’s check the status:

[oracle@em12c bin]$ ./emcli status

Oracle Enterprise Manager 12c EMCLI12.1.0.3.0.

Instance Home : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli

Verb Jars Home : null

Status : Configured

EMCLI Home : /u01/app/oracle/Middleware2/oms/bin/.

EMCLI Version : 12.1.0.3.0

Java Home : /u01/app/oracle/Middleware2/jdk16/jdk/jre

Java Version : 1.6.0_43

Log file : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/.emcli.log

EM URL : https://em12c.localdomain:7801/em

EM user : SYSMAN

Auto login : false

Trust all certificates : true

[oracle@em12c bin]$ ./emcli login -username=SYSMAN -password=***

[oracle@em12c bin]$ ./emcli sync

Synchronized successfully

1) Download the latest updates catalog in offline mode

In the page, select Plugins and Click “Check Updates”

A popup with the link to download will appear:

https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

Now we need to import the master catalog with is: Patch 9348486: master catalog

[oracle@em12c bin]$ ./emcli import_update_catalog -file=/home/oracle/p9348486_112000_Generic.zip -omslocal

Processing catalog for Middleware Profiles and Gold Images

Processing catalog for Agent Software

Processing catalog for Management Connector

Processing catalog for Extensibility Development Kit

Processing update: Extensibility Development Kit - 'Extensibility Development Kit (EDK) is a standalone tool designed to help development of Enterprise Manager extensions (For example: Plug-ins)'

Processing catalog for Plug-in

Processing update: Plug-in - Enterprise Manager for Oracle Utilities provides management and monitoring for the Oracle Utilities area.

Processing update: Plug-in - Enterprise Manager for Oracle Enterprise Taxation and Policy Management provides management and monitoring for the Oracle Enterprise Taxation and Policy Management area.

Processing update: Plug-in - Enterprise Manager for Oracle Exadata provides comprehensive management for Oracle Exadata and related targets such as Database Machine, etc.

Processing update: Plug-in - Enables cloud self service framework and portal in Oracle Enterprise Manager.

Processing update: Plug-in -

Enterprise Manager Storage Management Framework provides Snap Clone functionality by leveraging the snapshot and Copy-on-Write (CoW) features of the underlying storage.

Processing update: Plug-in - Enterprise Manager for Oracle Siebel consists of monitoring and management functionalities for Oracle Siebel.

Processing update: Plug-in - Enterprise Manager for Fusion Apps consists of monitoring and management for Oracle Fusion and diagnostics in fusion application area.

Processing update: Plug-in - Enterprise Manager for Oracle Consolidation Planning and Chargeback provides metering, chargeback and consolidation planning for various Enterprise Manager targets.

Processing update: Plug-in - Enterprise Manager for Fusion Middleware consists of monitoring and management for Oracle Fusion Middleware and diagnostics in middleware area.

Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.

Processing update: Plug-in - Enterprise Manager for Big Data Appliance provides comprehensive management for Oracle Big Data Appliance and related targets of Hadoop

Processing update: Plug-in - Enterprise Management Plug-in for Oracle ZFS Storage Appliances

Processing update: Plug-in - Enterprise Manager for Oracle Instant Messaging Server is used for monitoring/managing the Oracle Instant Messaging Server

Processing update: Plug-in - Enterprise Manager for Oracle Virtual Networking

Processing update: Plug-in - Enterprise Manager for Ops Center Infrastructure stack

Processing update: Plug-in - Enterprise Manager for Engineered System Healthcheck provides proactive healthcheck alerts for Oracle Engineered Systems

Processing update: Plug-in - Enterprise Manager for Oracle Audit Vault and Database Firewall (AVDF) provides monitoring and management of AVDF system.

Processing update: Plug-in - Enterprise Manager for Oracle Communications enables management of Oracle Communications products

Processing update: Plug-in - Oracle Application Management Pack for Oracle E-Business Suite consists of System Management and Change Management Feature Sets

Processing catalog for Test Data Management templates

Processing catalog for Oracle Virtual Templates and Oracle Virtual Assemblies

Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Functional Version 12.3.0.1.400

Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Version 12.3.0.1.376

Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Agent Version 12.3.0.1.376

Processing catalog for Informational

Processing catalog for Provisioning Bundle

Successfully uploaded the Self Update catalog to Enterprise Manager. Use the Self Update Console to view and manage updates.

[oracle@em12c bin]$

Now Click on Plug-In Updates -> choose Oracle Database (it will show Available) -> Download

You will be prompted with a message like the below.

You shall be downloading it from the URL provided.

Now you can import it to Enterprise Manager using the below command:

[oracle@em12c bin]$ ./emcli import_update -omslocal -file=/home/oracle/p17628065_112000_Generic.zip

Successfully uploaded the update to Enterprise Manager. Use the Self Update Console to manage this update.

Let’s now check the existing plugins:

[oracle@em12c bin]$ ./emcli list_plugins_on_server

OMS name is em12c.localdomain:4890_Management_Service

Plug-in Name Plugin-id Version [revision]

Oracle Engineered System Healthchecks oracle.em.sehc 12.1.0.3.0

Oracle Database oracle.sysman.db 12.1.0.4.0

Oracle Fusion Middleware oracle.sysman.emas 12.1.0.4.0

Oracle Consolidation Planning and Chargeback oracle.sysman.emct 12.1.0.4.0

Oracle MOS (My Oracle Support) oracle.sysman.mos 12.1.0.5.0

Oracle Storage Management Framework oracle.sysman.smf 12.1.0.2.0

Oracle Cloud Application oracle.sysman.ssa 12.1.0.6.0

Oracle Virtualization oracle.sysman.vt 12.1.0.5.0

Oracle Exadata oracle.sysman.xa 12.1.0.4.0

Oracle Utilities oracle.tugbu.outl 12.1.0.2.0

2) Deployment

Expand the option Databases -> Oracle Databases -> Deploy on -> Choose Management Server

Input the password for SYS (on the repository database of course)

Wait for <5 minutes for the prerequisites to complete:

Check the box which asks if you have backed up the repository and configuration of first management server and click Deploy:

As OMS will be taken down during deployment of plug-in, you can monitor the progress using

./emctl status oms -details

Oracle Enterprise Manager Cloud Control 12c Release 3

Enter Enterprise Manager Root (SYSMAN) Password :

Console Server Host : em12c.localdomain

HTTP Console Port : 7789

HTTPS Console Port : 7801

HTTP Upload Port : 4890

HTTPS Upload Port : 4901

EM Instance Home : /u01/app/oracle/gc_inst/em/EMGC_OMS1

OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log

OMS is not configured with SLB or virtual hostname

Agent Upload is unlocked.

OMS Console is unlocked.

Active CA ID: 1

Console URL: https://em12c.localdomain:7801/em

Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information

Domain Name : GCDomain

Admin Server Host : em12c.localdomain

Admin Server HTTPS Port: 7102

Managed Server Information

Managed Server Instance Name: EMGC_OMS1

Managed Server Instance Host: em12c.localdomain

WebTier is Down

Unable to deploy or undeploy the plug-in. Oracle Management Server is down possibly because the following plug-ins are being deployed or undeployed from it. Try again after some time.

Plug-in Deployment/Undeployment Status

Destination : Management Server - em12c.localdomain:4890_Management_Service

Plug-in Name : Oracle Database

Version : 12.1.0.5.0

ID : oracle.sysman.db

Content : Plug-in

Action : Deployment

Status : Deploying

Steps Info:

---------------------------------------- ------------------------- ------------------------- ----------

Step Start Time End Time Status

---------------------------------------- ------------------------- ------------------------- ----------

Submit job for deployment 1/7/14 7:20:00 AM EST 1/7/14 7:20:00 AM EST Success

Initialize 1/7/14 7:20:08 AM EST 1/7/14 7:20:26 AM EST Success

Install software 1/7/14 7:20:28 AM EST 1/7/14 7:20:30 AM EST Success

Validate plug-in home 1/7/14 7:20:34 AM EST 1/7/14 7:20:34 AM EST Success

Perform custom preconfiguration 1/7/14 7:20:34 AM EST 1/7/14 7:20:34 AM EST Success

Check mandatory patches 1/7/14 7:20:34 AM EST 1/7/14 7:20:34 AM EST Success

Generate metadata SQL 1/7/14 7:20:34 AM EST 1/7/14 7:20:34 AM EST Success

Preconfigure Management Repository 1/7/14 7:20:34 AM EST 1/7/14 7:20:34 AM EST Success

Preregister DLF 1/7/14 7:20:34 AM EST 1/7/14 7:20:34 AM EST Success

Stop management server 1/7/14 7:20:39 AM EST N/A Running

---------------------------------------- ------------------------- ------------------------- ----------

What interest us are the actions following the stop of management server. We need to rerun the above command; we cannot see the progress as we do with tail –f for example.

Stop management server 1/7/14 7:20:39 AM EST 1/7/14 7:22:19 AM EST Success

Configure Management Repository 1/7/14 7:22:21 AM EST N/A Running

Configure middle tier 1/7/14 7:22:23 AM EST N/A Running

---------------------------------------- ------------------------- ------------------------- ----------

…More details on the progress…

Stop management server 1/7/14 7:20:39 AM EST 1/7/14 7:22:19 AM EST Success

Configure Management Repository 1/7/14 7:22:21 AM EST N/A Running

Configure middle tier 1/7/14 7:22:23 AM EST 1/7/14 7:28:33 AM EST Success

---------------------------------------- ------------------------- ------------------------- ----------

…More details on the progress…

Stop management server 1/7/14 7:20:39 AM EST 1/7/14 7:22:19 AM EST Success

Configure Management Repository 1/7/14 7:22:21 AM EST 1/7/14 7:46:15 AM EST Success

Configure middle tier 1/7/14 7:22:23 AM EST 1/7/14 7:28:33 AM EST Success

OPSS jazn policy migration 1/7/14 7:31:30 AM EST 1/7/14 7:31:55 AM EST Success

Perform custom postconfiguration 1/7/14 7:49:53 AM EST N/A Running

---------------------------------------- ------------------------- ------------------------- ----------

…And the last step of starting management server…

Stop management server 1/7/14 7:20:39 AM EST 1/7/14 7:22:19 AM EST Success

Configure Management Repository 1/7/14 7:22:21 AM EST 1/7/14 7:46:15 AM EST Success

Configure middle tier 1/7/14 7:22:23 AM EST 1/7/14 7:28:33 AM EST Success

OPSS jazn policy migration 1/7/14 7:31:30 AM EST 1/7/14 7:31:55 AM EST Success

Perform custom postconfiguration 1/7/14 7:49:53 AM EST 1/7/14 7:49:55 AM EST Success

Update inventory 1/7/14 7:49:55 AM EST 1/7/14 7:49:59 AM EST Success

Start management server 1/7/14 7:49:59 AM EST N/A Running

---------------------------------------- ------------------------- ------------------------- ----------

The deployment completes after approximately 40 minutes. The OMS is up again.

[oracle@em12c bin]$ ./emctl status oms -details

Oracle Enterprise Manager Cloud Control 12c Release 3

Enter Enterprise Manager Root (SYSMAN) Password :

Console Server Host : em12c.localdomain

HTTP Console Port : 7789

HTTPS Console Port : 7801

HTTP Upload Port : 4890

HTTPS Upload Port : 4901

EM Instance Home : /u01/app/oracle/gc_inst/em/EMGC_OMS1

OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log

OMS is not configured with SLB or virtual hostname

Agent Upload is unlocked.

OMS Console is unlocked.

Active CA ID: 1

Console URL: https://em12c.localdomain:7801/em

Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information

Domain Name : GCDomain

Admin Server Host : em12c.localdomain

Admin Server HTTPS Port: 7102

Admin Server is RUNNING

Managed Server Information

Managed Server Instance Name: EMGC_OMS1

Managed Server Instance Host: em12c.localdomain

WebTier is Up

Oracle Management Server is Up

We now list the plugins on server

[oracle@em12c bin]$ ./emcli list_plugins_on_server

OMS name is em12c.localdomain:4890_Management_Service

Plug-in Name Plugin-id Version [revision]

Oracle Engineered System Healthchecks oracle.em.sehc 12.1.0.3.0

Oracle Database oracle.sysman.db 12.1.0.5.0

Oracle Fusion Middleware oracle.sysman.emas 12.1.0.4.0

Oracle Consolidation Planning and Chargeback oracle.sysman.emct 12.1.0.4.0

Oracle MOS (My Oracle Support) oracle.sysman.mos 12.1.0.5.0

Oracle Storage Management Framework oracle.sysman.smf 12.1.0.2.0

Oracle Cloud Application oracle.sysman.ssa 12.1.0.6.0

Oracle Virtualization oracle.sysman.vt 12.1.0.5.0

Oracle Exadata oracle.sysman.xa 12.1.0.4.0

Oracle Utilities oracle.tugbu.outl 12.1.0.2.0

We can see that Oracle Database shows correctly it is at version 12.1.0.5

In EM, the same information is visible

Reference:
How to Deploy the Latest Database Plugin to the OMS and the Agent in 12C Cloud Control (Doc ID 1388143.1)