Tuesday, July 19, 2011

Oracle Enterprise Manager 11g (11.1.0.1.0) agent installation using response file

Download and extract agent's binaries in a directory in your server, e.g. /var/opt/oragrid/binaries.
Open file /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp and do the following changes:
SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
#MYORACLESUPPORT_USERNAME=
#MYORACLESUPPORT_PASSWORD=
#COLLECTOR_SUPPORTHUB_URL=
ORACLE_AGENT_HOME_LOCATION=[directory where agent software will be installed, e.g. /var/opt/oragrid]
OMS_HOST=[server's name where OMS is installed]
OMS_PORT=[server's port, default 4889]
AGENT_REGISTRATION_PASSWORD=[sysman's password]

If you are installing an agent in a RAC, change also:
CLUSTER_NODES="[node1's name],[node2's name],..."

If you don't want to mess with server's oracle central inventory, use a custom inventory, by creating an oraInst.loc file, e.g. /var/opt/oragrid/oraInst.loc.
Open it and add:
inventory_loc=[path where this inventory will be placed, e.g. /var/opt/oragrid/oraInventory]
inst_group=[agent's user group, dba or oinstall]

Go to /var/opt/oragrid/binaries/hpi/agent/install and run:
runInstaller -invPtrLoc /var/opt/oragrid/oraInst.loc -silent -responseFile /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp

If you are installing in a RAC, find cluster name:
cd $CRS_HOME/bin
./cemutlo -n

Now, run:
runInstaller -invPtrLoc /var/opt/oragrid/oraInst.loc -silent -responseFile /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp -CLUSTER_NAME=[cluster name] CLUSTER_NODES="{[node1's name],[node2's name],...}"

There is a bug that fills emagent.trc pretty fast.
So, at the end, install patch 9756966.

Finally, run [agent's home]/root.sh as root.

In the RAC installation, all onoff patches that were supposed to be installed after agent's installation, failed, because although I had specified my custom oraInst.loc in the runInstaller command, OPatch was using oraInst.loc under agent's home directory.
Probably, you will not face this issue, if you don't specify a custom inventory.
If that happens also to you, go to [agent's home]/install/oneoffs/111010 and install them manually.

If you try to monitor a 9i database not in 9.2.0.8.0 patchset, you will probably get many ORA-07445 in the alert log and core dumps in your database's cdump directory.
To fix this, edit [agent's home]/sysman/config/emd.properties and add the parameter AvoidOCIPing=1.
Then, restart the agent.

Wednesday, July 6, 2011

Oracle Enterprise Manager 11g: Install & configure EM CLI in agents for failovers in Cold Failover Cluster

First of all, install agents in all cluster nodes, discover  and configure the targets running in them.
If a node fails and you move your oracle service to a working node, you would want to inform OMS for this relocation.
This is accomplished, using Enterprise Manager Command Line Interface (EM CLI).

Download emclikit.jar from your grid installation:

http(s)://[host]:[port]/em/console/emcli/download

The following must be executed for every agent deployed in cluster nodes.

Set your JAVA_HOME enviroment (Java 1.6.0 or greater is needed):
export JAVA_HOME=[java directory]
export PATH=$JAVA_HOME/bin:$PATH

Under agent's home create a "emcli" directory and put emclikit.jar in it.
Run:
java -jar emclikit.jar client -install_dir=[directory where emclikit.jar is placed]

Setup EM CLI:
emcli setup -url="http(s)://[host]:[port]/em" -username=sysman -password=[sysman password] -licans=YES -dir=[directory where emclikit.jar is placed] -trustall -localdirans=YES

You can check targets discovered by your OMS, running the following query:
SELECT * FROM SYSMAN.MGMT_TARGETS ORDER BY TARGET_TYPE;

Or by giving the following command:
emctl config agent listtargets

Now, lets say you relocate a target e.g. a database called TESTDB from node1 to node2.
If node1's agent is still running, you can leave everything as it is. An agent can monitor targets running in other nodes.
If node1 completely fails and its agent stops running, you should instruct OMS to monitor this target using another agent, so execute:
emcli relocate_targets -src_agent=node1:[agent's port, default 3872] -dest_agent=node2:[agent's port, default 3872] -target_name=TESTDB -target_type=oracle_database -copy_from_src -force=YES

If you get a "target [database instance, oracle_database] is broken: cannot compute dynamic properties in time" error:
emctl stop agent

Edit [agent's home]/sysman/config/emd.properties and change value of dynamicPropsComputeTimeout to 120.

emctl start agent
emctl upload agent

Find which queries use a specific segment

If you want to find out which of the cursors currently loaded in the library cache use a specific table or index:
SELECT
   P.INST_ID,
   P.OBJECT_NAME,
   P.OPERATION,
   P.OPTIONS,
   S.SQL_ID,
   S.MODULE,
   S.ACTION,
   S.PARSING_SCHEMA_NAME,
   S.SQL_TEXT
FROM
   GV$SQL_PLAN P,
   GV$SQL S
WHERE P.OBJECT_OWNER = 'AX'
AND P.OBJECT_NAME = 'AX_EVENTS_N1'
AND P.SQL_ID = S.SQL_ID
AND P.INST_ID = S.INST_ID;

You may also use the following query to get the result based on AWR data:
SELECT
   P.OBJECT_NAME,
   P.OPERATION,
   P.OPTIONS,
   S.SQL_ID,
   S.MODULE,
   S.ACTION,
   S.PARSING_SCHEMA_NAME,
   T.SQL_TEXT
FROM
   DBA_HIST_SQL_PLAN P,
   DBA_HIST_SQLSTAT S,
   DBA_HIST_SQLTEXT T
WHERE P.OBJECT_OWNER='AX'
AND P.OBJECT_NAME='AX_EVENTS_N1'
AND P.SQL_ID = S.SQL_ID
AND S.SQL_ID = T.SQL_ID;

Remember, by default AWR collects the top-N (default N=30) heaviest SQL queries in the library cache each time.
So, both queries cannot provide you a 100% way to monitor the usage of segments.