Features list of all databases

Did you ever want to know what all features you are actually using in your databases, and what features are unnecessarily installed in your databases?
OEM holds all the this information. You just need the right query.
In this example, I had to hack the SQL instead of using LISTAGG since the database was pre 11.2 version.

create or replace
type clobagg_type as object(
text clob,
static function ODCIAggregateInitialize(
sctx in out clobagg_type
)
return number,
member function ODCIAggregateIterate(
self in out clobagg_type,
value in clob
)
return number,
member function ODCIAggregateTerminate(
self in clobagg_type,
returnvalue out clob,
flags in number
)
return number,
member function ODCIAggregateMerge(
self in out clobagg_type,
ctx2 in clobagg_type
)
return number
);
/
create or replace
type body clobagg_type
is
static function ODCIAggregateInitialize(
sctx in out clobagg_type
)
return number
is
begin
sctx := clobagg_type(null) ;
return ODCIConst.Success ;
end;
member function ODCIAggregateIterate(
self in out clobagg_type,
value in clob
)
return number
is
begin
self.text := self.text || value ;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self in clobagg_type,
returnvalue out clob,
flags in number
)
return number
is
begin
returnValue := self.text;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self in out clobagg_type ,
ctx2 in clobagg_type
)
return number
is
begin
self.text := self.text || ctx2.text;
return ODCIConst.Success;
end;
end;
/
create or replace
function clobagg(
input clob
)
return clob
deterministic
parallel_enable
aggregate using clobagg_type;
/

I nicked the above package queries from Oracle forum; credits to BluShadow.

Now for the query which pulls all the information from the OMR database.

select
trim(',' from clobagg(name||',')) as feature_name, host , database_name, target_type ,
instance_name , currently_used ,
max(first_usage_date) ,version as VERSION
from mgmt$db_featureusage
GROUP BY host, database_name, target_type,
INSTANCE_NAME, CURRENTLY_USED, VERSION

Mass promotion of OEM12c targets

Once you have all the OEM12c agents installed on the servers, the next step is to get all the targets added on to OEM12c. OEM12c auto gathers the properties of the targets that you configure the agents to collect data on. Once done, you should see all the targets under auto configure results. The results shoudl include database instances, listeners, oracle homes, clusters, oracle HAS, rac databases.
There is no option to mass promote targets from OEM12c console so that these targets can be then monitored.

However emcli can run script files with multiple commands and a SQL can be used to generate the commands.

To generate the SQL commands, connect to the OMS database and run below SQLs to generate the commands.

alter session set CURRENT_SCHEMA=SYSMAN;

For Oracle Databases

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_database" -host="' ||
ManagementEntityEO.HOST_NAME || '" -credentials="UserName:dbsnmp;password:dbsnmp;Role:Normal" -properties="SID:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%SID')) || ';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%PORT')) || ';OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) ||
';MachineName:' || ManagementEntityEO.HOST_NAME || ';"'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='oracle_database'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'));

For Listeners

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="oracle_listener" -host="' || ManagementEntityEO.HOST_NAME ||'" -properties="LsnrName:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%LSNRNAME')) || ';ListenerOraDir:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%LISTENERORADIR')) ||
';Port:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND (UPPER(PROPERTY_NAME) LIKE '%PORT'))
|| ';OracleHome:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1) ||
';Machine:' || ManagementEntityEO.HOST_NAME || ';"'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='oracle_listener'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'));

For Clusters

select
'add_target -name="' || ManagementEntityEO.ENTITY_NAME
|| '" -type="cluster" -host="' ||ManagementEntityEO.HOST_NAME
|| '" -monitor_mode="1" -properties="OracleHome:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1)
|| ';eonsPort:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE 'EONSPORT%'))
|| ';scanName:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE 'SCANNAME%'))
|| ';scanPort:' ||
(SELECT PROPERTY_VALUE FROM SYSMAN.MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE 'SCANPORT%'))
|| ';" -instances="' ||ManagementEntityEO.HOST_NAME||':host;'||
(select decode(b.host_name,null,'"',b.host_name||':host"')
FROM SYSMAN.GC_MANAGEABLE_ENTITIES a , SYSMAN.GC_MANAGEABLE_ENTITIES b WHERE a.PROMOTE_STATUS=1
AND a.MANAGE_STATUS=1
AND a.ENTITY_TYPE!= 'host'
AND a.ENTITY_TYPE='cluster'
and b.HOST_NAME like REGEXP_REPLACE(a.HOST_NAME, '([a-z]+)[0-9].*', '\1%')
and b.host_name <> a.host_name
and b.entity_type='host'
and a.host_name=ManagementEntityEO.host_name
)
FROM SYSMAN.GC_MANAGEABLE_ENTITIES ManagementEntityEO,SYSMAN.MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='cluster'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
SYSMAN.mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'))

For RAC databases

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="rac_database" -host="' ||
ManagementEntityEO.HOST_NAME ||
'" -monitor_mode="1" -properties="ServiceName:'||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%SERVICENAME%')) ||';ClusterName:'||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID = ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%CLUSTERNAME%')) ||'" -instances="'||
(select listagg(sub.entity_name,':oracle_database;') within group (order by sub.entity_name) FROM GC_MANAGEABLE_ENTITIES sub
where sub.ENTITY_TYPE='oracle_database' and sub.entity_name like ManagementEntityEO.ENTITY_NAME||'%')||':oracle_database”'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='rac_database'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'))

For ASM instances

select 'add_target -name="' || ManagementEntityEO.ENTITY_NAME || '" -type="osm_instance" -host="' ||
ManagementEntityEO.HOST_NAME || '" -credentials="UserName:sys;password:sTroNGPassWd;Role:sysdba" -properties="OracleHome:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%HOME' AND UPPER(PROPERTY_NAME)!='MW_HOME' OR PROPERTY_NAME ='INSTALL_LOCATION') AND ROWNUM = 1)
|| ';MachineName:' || (SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%MACHINENAME')) || ';SID:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%SID%'))||
';Port:' ||
(SELECT PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES WHERE TARGET_GUID =
ManagementEntityEO.ENTITY_GUID AND
(UPPER(PROPERTY_NAME) LIKE '%PORT%')) ||'"'
FROM GC_MANAGEABLE_ENTITIES ManagementEntityEO,MGMT_TARGET_TYPES ManagementEntityTypeEO
WHERE ManagementEntityEO.PROMOTE_STATUS=1
AND ManagementEntityEO.MANAGE_STATUS=1
AND ManagementEntityEO.ENTITY_TYPE!= 'host'
AND ManagementEntityEO.ENTITY_TYPE='osm_instance'
AND ManagementEntityEO.ENTITY_TYPE= ManagementEntityTypeEO.TARGET_TYPE
AND (NOT EXISTS(SELECT 1 FROM
mgmt_type_properties mtp WHERE mtp.target_type= ManagementEntityEO.entity_type AND mtp.property_name ='DISCOVERY_FWK_OPTOUT'AND
mtp.property_value='1'))

The output of this can be saved into an command argument file (in this example discover.dat)

EMCLI can then be invoked to run this command file to mass promore targets

emcli login -username=OEMDBA -password=sTrOnGPassWd
Login successful

emcli sync
Synchronized successfully

emcli argfile discover.dat

Mass deployment of OEM12c agents

Oracle Enterprise Manager has a cool feature that allows mass deployment of agents across multiple hosts. It has certain limitations ; for instance it cannot use dynamic paths for ORACLE_HOME locations.
For deployment of 5 servers you might spend about 5 x 20mins =100mins. But imagine for 600 servers that is 200 hours.
So here is a wrapper script that will login to every server create a designated ORACLE_HOME two levels below the ORACLE_HOME of the database and then install the OEM agent on the server. It creates a sumfile(Summary file)
It also does some cool things like strip out the OEM 10g agent details from the inventory so that pre-requisites dont fail.
Im sure there are plenty of tweaks and improvements that can be done. Love to hear your thoughts.

Couple of things before we start

1. Create the named credentials for each of your password

./emcli create_named_credential
-cred_name=DBA_B0
-auth_target_type=host
-cred_type=HostCreds
-attributes="HostUserName:oracle;HostPassword:B0password"

2. Create a password file with your password file. Encrypt it with Blowfish(optional).

#!/usr/bin/perl
use Crypt::Blowfish;
use strict;
use warnings;

# Password File - Blowfish encyrpted
open (PASSWDFILE, " > pass.dat") or die "Could not open pass.dat because $!";

my $key = pack("H16","0123456789ABCDEF"); # put your key here
my $crypt = new Crypt::Blowfish $key;
my $ciphertext = $crypt->encrypt('B0password'); # Your password here. Multiple lines of your passwords
print PASSWDFILE $decrypt->decrypt($ciphertext);

3. Have a list file with all the server names.

4. Run this from the OMS App server

#!/usr/bin/env perl
use strict;
use warnings;
use Net::OpenSSH;
use Crypt::Blowfish;
my $key = pack("H16","0123456789ABCDEF"); # Put your password file key here
my $cipher = new Crypt::Blowfish $key;
my $username = "oracle";
my $cmd = "uname -a";
my $count = 0;
my $flag=0; #Variable to check if last password was ok.

# Host file
open (HOSTFILE, "hosts.lstpreprod.full") or die "Could not open host.dat because $!";

# Password File - Blowfish encyrpted
open (PASSWDFILE, "pass.dat") or die "Could not open pass.dat because $!";

# Open the log file
open (LOGFILE, "> logfile.dat") or die "Could not open logfile.dat because $!";

# Open the summary file
open (SUMFILE, "> sumfile.dat") or die "Could not open logfile.dat because $!";

# Open the Server_pass file - Use this if you want to generate a host,passwd file for future references instead of trying new passwords
# open (SERVPASS, "> serv_pass.dat") or die "Could not open logfile.dat because $!";

# Loop the Hosts File
while (my $row = ) {
#Bring the flag to reset
$flag=0;
# counter
$count = $count + 1;

chomp($row); #remove garbage characters at trailing or leading end.
seek(PASSWDFILE,0,0); #move marker to first line of Password file
print LOGFILE "-----------------------------------------------------------------\n";
print LOGFILE "Server - $row\n";
print SUMFILE "-----------------------------------------------------------------\n";
print SUMFILE "$count. Server - $row\n";

#Loop through the password file
while (my $epassword = and $flag==0) {
# Get the decrypted password
my $password = $cipher->decrypt($epassword);
my $ssh = Net::OpenSSH->new($row, user=>"oracle", passwd=>$password,strict_mode=>1, timeout => 10,kill_ssh_on_timeout => 1);

if($ssh->error)
{

if (($ssh->error =~ m/password/) || ($ssh->error =~ m/timeout/))
{
next;
}
else
{
print LOGFILE $ssh->error."\n";
last;
}
}

#If you are here it means that the password worked. Set the flag so that you skip to next host.
$flag=1;

#generate server password file- if you want to
#print SERVPASS $row . "," . $password;
#Get the Oracle home
my $cmd = "export DB_NAME=`ps -ef|grep pmon| grep -v grep| awk '{print \$8}'|cut -c10-\$NF|grep -v AS | sed 's/[1-3]\$//'` && grep -i \"^\$DB_NAME\" /etc/oratab | grep -v \"^#\" | grep -v \"^\*\"| grep \":\" | awk -F\":\" '{print \$2}'|uniq";
print LOGFILE $cmd;
# Get Oracle Home
my $ohome = $ssh->capture($cmd);
print LOGFILE "Oracle home is ".$ohome;
if ($ohome ne "") # No Oracle_HOME, then get out!
{
chomp($ohome);

$ssh->system('cd '.$ohome)
or print LOGFILE "Error getting to oracle home directory" and last;
#inventory - Rip of the existing Agent home from inventory, this to overcome pre-requisites.
my $inventory = $ssh-> capture("cat /etc/oraInst.loc | grep -i inventory_loc | awk -F= '{print \$2}'");
print LOGFILE "Inventory found to be " . $inventory . "\n";
chomp($inventory);

#Strip out agent home information
my $inventory_file = $inventory . "/ContentsXML/inventory.xml";
my $inventory_bfile = $inventory . "/ContentsXML/inventory.xml_bak";
my $inventory_afile = $inventory . "/ContentsXML/inventory.agent";
print LOGFILE "Inventory file ". $inventory_file;
print LOGFILE "\nInventory backupfile". $inventory_bfile;
print LOGFILE "\nInventory agent file". $inventory_afile . "\n";


# Create backup of inventory file
$ssh->system('cp '.$inventory_file. ' '.$inventory_bfile)
or print LOGFILE "Error backing up inventory file\n" and last;

#Strip the inventory agent details
my $cmd="awk \'BEGIN {IGNORECASE = 1; discard = 0;} \/\ ' .$inventory_file;


$ssh->system($cmd)
or print LOGFILE "Error stripping agent inventory\n";

print LOGFILE "Command to strip the inventory ". $cmd . "\n";

#Send stripped data to another file
$cmd="awk 'BEGIN {IGNORECASE = 1; discard = 0;} \/\ ' .$inventory_afile;

$ssh->system($cmd)
or print LOGFILE "Error stripping agent inventory to another file\n";

print LOGFILE "Command to send the agent inventory to another file". $cmd . "\n";


# Create directory for OEM 12c agent home
$cmd="mkdir -p ". $ohome . "/../../agent12c";
# print LOGFILE "Command is ". $cmd. "\n";
$ssh->system($cmd)
or print LOGFILE "Error creating agent home directory" and last;

#switch to new directory
$cmd="cd ". $ohome . "/../../agent12c";
#print LOGFILE "Command is ". $cmd."\n";
$ssh->system($cmd)
or print LOGFILE "Error getting to agent home directory" and last;


my $ahome = $ssh->capture($cmd ."&& pwd"); # Get the Agent Oracle Home
print LOGFILE "Agent home is ". $ahome."\n";
chomp($ahome);

# Login to OEM 12c
print LOGFILE "Starting the OEM 12c part\n";
my $emcliop = `/app/oracle/Middleware12cr3/oms/bin/emcli login -username=MasterDBA -password=maximumSecurity 2>&1`;
print LOGFILE "Login result - ".$emcliop."\n";
if ($emcliop =~ m/successful/ )
{
# Sync with repository
$emcliop = `/app/oracle/Middleware12cr3/oms/bin/emcli sync`;
print LOGFILE "Sync result - ".$emcliop."\n";
if ($emcliop =~ m/successful/ )
{
# Sumbit the push job
my $passfrag = substr $password, 0, 2;
print LOGFILE "Shredded Password - ". $passfrag."\n";
#Sumbit the job
my $emclicmd = "/app/oracle/Middleware12cr3/oms/bin/emcli submit_add_host -host_names=".$row." -platform=226 -port=3872 -installation_base_dir=".$ahome." -credential_name=DBA_".$passfrag." -session_name=\"Agent-".$row."\" -wait_for_completion";
print LOGFILE "Agent install command run is " .$emclicmd."\n";

$emcliop = `$emclicmd`;

print LOGFILE $emcliop;

#get the job status
$emclicmd = "/app/oracle/Middleware12cr3/oms/bin/emcli get_add_host_status -session_name=\"Agent-".$row."\" -format=\"name:csv\" | awk -F, '{if(NR>3)print \$3,\$4,\$5,\$6}'";
print LOGFILE "Status Command run is " .$emclicmd."\n";

$emcliop = `$emclicmd`;

print LOGFILE $emcliop;


if ($emcliop =~ /Succeeded Succeeded Succeeded/)
{
print LOGFILE "All done! SUCCESS\n";
print SUMFILE "All done! SUCCESS\n";

}
else
{
print LOGFILE "Error in installing the agent- DBA to intervene\n";
print SUMFILE "Error in installing the agent - DBA to intervene\n";
#restore the backup inventory file
$ssh->system('mv '. $inventory_file .' '. $inventory_file .'.script')
or print LOGFILE "Error moving existing inventory file for restore";
$ssh->system('cp '.$inventory_bfile. ' '.$inventory_file)
or print LOGFILE "Error restoring the inventory from backup file" and last;
}
#logout irrespective of success or failure
$emclicmd = "/app/oracle/Middleware12cr3/oms/bin/emcli logout";
$emcliop = `$emclicmd`;
print LOGFILE "Logout result - ".$emcliop."\n";
}
}
}
}
}