Friday, January 27, 2012

Enterprise Downgrade to Standard Edition

Product:  RDBMS
Version: 10.1
Platform:  Linux

* This is a work in process, so may be some gaps. (AR 1/27/12)

This can be a little tricky to evaluate, but the money saving in licensing fees makes it well worth the effort.  I normally don't get too involved with licensing as a DBA consultant, but I do like to make recommendations where possible.

The first task will be to obtain a list of SE vs SE1 vs EE features to see what features will be available with each.  I have sourced Metalink for this information, doc id: 271886.1

There are a few high level things to consider that could be be deal breakers.  These are CPU and memory requirements.  This is what I have found:

Oracle Product Editions

Key Feature Summary Express Edition
Standard Edition One Standard Edition Enterprise Edition
Maximum 1 CPU 2 Sockets 4 Sockets No Limit
RAM 1GB OS Max OS Max OS Max
Database Size 4GB No Limit No Limit No Limit
64 Bit Support

For this case study, I am going to restrict focus to a comparison between EE and SE because I know that 2 socket will NOT be enough to support our needs under the circumstances I am faced with immediately.  I am pleased to see there are no memory restriction, although I do know we will likely be installing on 32-bit Linux which will restrict us per database to 4gb unless we implement VLM (very large memory) support.  Fortunately, I am not looking at any db over the size of 2gb so this will not present a problem.

* If you are wondering WHY we are staying at 32 bit for a db server... The client simply does not want to introduce another variable unnecessarily at this point in time.  This SE-32bit setup is only  temporary for the next year or 2.

CPU may present a problem.  What I understand here in addition to a little more research is that, for SE, licensing is based strictly on sockets, while EE is based upon cores and is much more complex to price out with consideration to multiple factors which I will not be discussing here.

So, after our downgrade to SE, we will be limited to 4 sockets.  The consideration here will be... How many databases can we consolidate per machine?  This will be a somewhat subjective evaluation, but I will be using reports from Grid Control to look at historical CPU usage - not forget that processing needs vary with online vs. batch workloads throughout the day. 

I think we will be okay to move forward with SE thus far.

Next we need to look at the databases to determine whether or not they are using EE features and whether or not the features can be given up in a move to SE.

I have written a script to pull features from dba_feature_usage_statistics, but first I let's see what this view contains about features.

SQL> desc dba_feature_usage_statistics
Name                                      Null?    Type
----------------------------------------- -------- --------------
DBID                                      NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(64)
VERSION                                   NOT NULL VARCHAR2(17)
DETECTED_USAGES                           NOT NULL NUMBER
TOTAL_SAMPLES                             NOT NULL NUMBER
CURRENTLY_USED                                     VARCHAR2(5)
FIRST_USAGE_DATE                                   DATE
LAST_USAGE_DATE                                    DATE
AUX_COUNT                                          NUMBER
FEATURE_INFO                                       CLOB
LAST_SAMPLE_DATE                                   DATE
LAST_SAMPLE_PERIOD                                 NUMBER
SAMPLE_INTERVAL                                    NUMBER
DESCRIPTION                                        VARCHAR2(128)

We will primarily be interested in knowing what features are CURRENTLY_USED, but depending on the feature, we may need to consider LAST_USAGE_DATE.

Let's first get a list of ALL features so that we can compare it to our metalink note 271886.1.  Note, this list will vary greatly from one version of Oracle to another with features being added and/or removed.  I have placed a "Y" next to those features listed in Oracle's doc AND included in SE.  I have placed a "N" next to those features listed but NOT included in SE.  An "X" represents a feature not listed at all in Oracle's doc and will require further research into what the add-on Packs contain.

SQL> select distinct name from dba_feature_usage_statistics;

N - Advanced Replication
N - Advanced Security
Audit Options
Automatic Database Diagnostic Monitor
Automatic SQL Execution Memory
Automatic Segment Space Management (system)
Automatic Segment Space Management (user)
Y - Automatic Storage Manager
Automatic Undo Management
Automatic Workload Repository
N - Change-Aware Incremental Backup
Client Identifier
N -Data Guard
N -Data Guard Broker
N - Data Mining
Dynamic SGA
File Mapping
N - Flashback Database
Internode Parallel Execution
N - Label Security
Y - Locally Managed Tablespaces (system)
Y - Locally Managed Tablespaces (user)
N - MTTR Advisor
N - Messaging Gateway
Multiple Block Sizes
N - OLAP - Analytic Workspaces
N - OLAP - Cubes
Y - Oracle Managed Files
Y - PL/SQL Native Compilation
N - Parallel SQL DDL Execution
N - Parallel SQL DML Execution
N - Parallel SQL Query Execution
N - Partitioning (system)
N - Partitioning (user)
Protection Mode - Maximum Availability
Protection Mode - Maximum Performance
Protection Mode - Maximum Protection
Protection Mode - Unprotected
Y - RMAN - Disk Backup
RMAN - Tape Backup
Y - Real Application Clusters (RAC)
Recovery Area
Y - Recovery Manager (RMAN)
Resource Manager
SQL Access Advisor
SQL Tuning Advisor
SQL Tuning Set
Segment Advisor
Server Parameter File
Shared Server
N - Spatial
Standby Archival - ARCH
Standby Archival - LGWR
Standby Transmission
N - Streams (system)
N - Streams (user)
Transparent Gateway
Undo Advisor
N - Virtual Private Database (VPD)

59 rows selected.

The following script returns feature usage info when executed against each db.  Unfortunately, it doesn't seem to include everything that shows on Oracle's licensing guide, or with some features the info is not enough to know whether there will be a negative impact from a downgrade or not.  I will follow with some additional checks - I may be OVERLY thorough, but I'd prefer not to miss anything or misinterpret what I see and find out later that a downgrade would not work.

set pages 500 lines 150
col name for a50
col version for a10
col currently_used for a10
col LAST_USAGE_DATE for a20

alter session set nls_date_format='DD-MON-RRRR';

sho parameter db_n

from dba_feature_usage_statistics
and dbid=(select dbid from v$database)
order by 1;

I found this link to be helpful as well.  They have checks for just about everything.  I will include some of their check along with mine.

Orafaq - Oracle Licensing

The downgrade process for 10g is relatively simple.  I have reviewed Metalink doc id: 465189.1 for instructions:

Monday, January 16, 2012

Manually register a new agent with grid control

This is a simple task.  You only need the Secure Agent Password.  This is NOT the same as the repository sysman password.  It is a special password required for administering the OMS/Agents.

Go to the server where the agent is installed and set your $ORACLE_HOME to the $AGENT_HOME.

Then execute the process to secure the agent.

$ORACLE_HOME/bin/emctl secure agent

Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Agent successfully stopped...   Done.
Securing agent...   Started.
Enter Agent Registration Password :
Agent successfully restarted...   Done.
Securing agent...   Successful.

If this fails, review the log for errors.  The log is located at:


It will be appended to with each attempt at agent registration.

The most common error is simply an invalid agent password:

2012-01-16 16:13:19,646 [main] ERROR agent.SecureAgentCmd secureAgentWithOMS.382 - INIT operation failed: ERROR:203:Invalid agent registration password
2012-01-16 16:13:19,655 [main] ERROR agent.SecureAgentCmd main.210 - Failed to secure the Agent:
java.lang.Exception: INIT operation failed

In this case, you will need to find or change the secure agent password.  Instructions to change the password are here.

Another common error is the incorrect REPOSITORY_URL port in the $AGENT_HOME/sysman/config/ file.  To determine the correct port, go to the OMS server and execute the following:

$ $OMS_HOME/bin/emctl status oms -details

Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host :
HTTP Console Port   : 7788
HTTPS Console Port  : 7799
HTTP Upload Port    : 4889
HTTPS Upload Port   : 1159
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1

For the purpose of securing the agent, you will want to use the secure upload port in red above.  To ensure the agent is hitting the correct port, look for the REPOSITORY_URL entry in the following file on the agent server and correct the value to match what the OMS reported.


You can retry the"emctl secure agent" as many times as necessary until it succeeds.  It will automatically stop and restart the agent each time the secure agent is executed.

Deploying Additional 11g Grid Management Agents

This is a basic How-to with minimal explanation.  If you have questions, please comment or contact me by email.

Go to your OMS server and cd into the following directory.


$ ls -lrt
drwxr-xr-x  8 oracle dba      4096 Sep 16 10:39 linux
-rw-------  1 oracle dba     10008 Sep 16 10:39 agent_download.rsp
-rw-r-----  1 oracle dba     10008 Sep 23 11:16 agent_download.rsp.bak
-rw-r-----  1 oracle dba      1874 Sep 23 11:16
drwxr-----  2 oracle dba      4096 Sep 23 11:17 stage
-rw-r-----  1 oracle dba      2978 Sep 23 11:17 readme.txt
drwxr-----  8 oracle dba      4096 Sep 23 11:17 linux_x64

This is a 64-bit OMS, but the files include 32-bit (linux) as well.

Tar up and gzip the appropriate directory.  Will transfer teh resulting filr to the new agent destination server.

$ tar -cvf linux.tar linux
$ gzip linux.tar
$ scp linux.tar.gz destination_server:/u01/app/oracle/stage/agent11g/linux/response

Go to the destination server and unzip the agent files.

$ gunzip linux.tar.gz
$ tar -xvf linux.tar

Navigate to ...linux/response

Documentation states that you will find a file named additional_agent.rsp.  I only see the following:

$ ls -lrt
-rw-r--r--  1 oracle oinstall 5922 Nov 15  2010 upgrade_agent.rsp
-rw-r--r--  1 oracle oinstall  489 Nov 15  2010 staticports.ini

The response file is relatively simple to create.  There happens to be a an additional_agent.rsp on the OMS server in the linux_x64 directory.  I have scp'd that file over to the destination server and will edit it thereas follows:

$ ls -lrt
-rw-r--r--  1 oracle oinstall  5922 Nov 15  2010 upgrade_agent.rsp
-rw-r--r--  1 oracle oinstall   489 Nov 15  2010 staticports.ini
-rw-r-----  1 oracle oinstall 10502 Jan 16 15:08 additional_agent.rsp

In the interest of saving space, I am only including the parameter lines here.  The actual rsp file contains instructions and examples.  The values I provided are in red.

$ vi additional_agent.rsp


Most values are self explanatory.  To find the OMS_PORT, go to the OMS server, and do the following.

$OMS_HOME/bin/emctl status oms -details

Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host :
HTTP Console Port   : 7788
HTTPS Console Port  : 7799
HTTP Upload Port    : 4889
HTTPS Upload Port   : 1159
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1

The port we need is the secure upload port, 1159.  Executing this command will also let you test your sysman secure password -- you will be prompted for it.

You are now ready to install the agent on the destination host.

cd to the runInstaller directory:

$ cd /u01/app/oracle/stage/agent11g/linux/agent

Note: This errors if permissions are not set properly for oracle to execute the installer.  Do the following if necessary:

$ chmod +x /u01/app/oracle/stage/agent11g/linux/agent/*
$ chmod +x /u01/app/oracle/stage/agent11g/linux/agent/install/*

Launch the installer as follows:

./runInstaller -silent -responseFile /u01/app/oracle/stage/agent11g/linux/response/additional_agent.rsp

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 150 MB.   Actual 2275 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 6008 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-01-16_03-47-43PM. Please wait ...

Output will continue to screen as installation proceeds.  There will be no GUI interaction since we specified -silent -responseFile.

The installation should complete successfully in 10 minutes or so.

If for any reason the agent configuration fails, you most likely will not need to reinstall it.  You should be able to just manually secure it later.  For instructions to do that, see this post.

Once the agent is secured, it should begin to upload to the OMS and the server should be visible on the host Target page.