OEM12c: List of targets not uploading

SELECT c.target_name Agent,a.target_type,a.target_name Target,
      to_char(alt.last_load_time,'yyyy-mm-dd hh24:mi:ss') Last_load_time ,
      to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') Current_Time
 FROM sysman.mgmt_targets a, sysman.mgmt_targets_load_times alt,
      sysman.mgmt_current_availability b,
      sysman.mgmt_targets c
WHERE c.target_guid in (select target_guid from sysman.mgmt_targets where
target_type='oracle_emd')
  AND c.emd_url = a.emd_url
  AND a.target_guid = b.target_guid
  AND alt.target_guid = a.target_guid
  AND b.current_status = 1
  AND a.broken_reason = 0
  AND a.target_type NOT IN ('oracle_emd','oracle_beacon','oracle_emrep')
  AND alt.last_load_time < ((mgmt_global.sysdate_tzrgn(a.timezone_region)) - (120 / (24 * 60))) order by 3,4;

AGENT           TARGET_TYPE          TARGET               LAST_LOAD_TIME      CURRENT_TIME
--------------- -------------------- -------------------- ------------------- ---------------
abcdb02:3872    rac_database       PRDOAU          2015-07-30 05:54:54 2015-07-31 10:31:47
abcdb02:3872    rac_database       PRDSPOT         2015-07-30 05:55:19 2015-07-31 10:31:47
abcdb02:3872    rac_database       PRDTAX8         2015-07-30 05:54:36 2015-07-31 10:31:47
abcdb02:3872    cluster            abcdb-cluster01 2015-07-30 05:52:48 2015-07-31 10:31:47

Compare scan on all nodes

mybox.linux.com:/app/grid/product/12.1.0/grid/bin>cluvfy comp scan

Verifying SCAN

Checking Single Client Access Name (SCAN)...

Checking TCP connectivity to SCAN listeners...
TCP connectivity to SCAN listeners exists on all cluster nodes

Checking name resolution setup for "mybox-scan01.linux.com"...

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
All nodes have same "hosts" entry defined in file "/etc/nsswitch.conf"
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Checking SCAN IP addresses...
Check of SCAN IP addresses passed

Verification of SCAN VIP and listener setup passed

Verification of SCAN was successful. 

list of files stored in ASM and CURRENTLY NOT OPENED

This is the first example of the results of the execution of the script.  Note it includes all type of files like ARCHIVELOGS, FLASHBACK LOGS, REDOLOGS. Once it has been confirmed the files are not required any more, then they can be deleted using ASMCMD or SQL running the command ALTER DISKGROUP ... DROP FILE '<path>';

set pagesize 0
set linesize 200
col full_alias_path format a80
/*+ ----------------------------------------------------------------
    Query will return all the files stored on ASM but not currenlty
    opened by any database client of the diskgroups
    ordered by group number, file type
    ---------------------------------------------------------------*/

select * from (
/*+ -----------------------------------------------------------------
    1st branch returns all the files stored on ASM
    -----------------------------------------------------------------*/
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
/*+ --------------------------------------------------------------
    2nd branch returns all the files stored on ASM
    and currently opened by any database client of the diskgroups
    -----------------------------------------------------------------*/
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
;

How to configure Auto delete of logs in Logical standby database

This one enables automatic deletion of applied arclogs.

    Exec DBMS_LOGSTDBY.APPLY_SET ( 'LOG_AUTO_DELETE' , TRUE ) ;

This one sets the length of time, in minutes, that an applied arclog will be retained until deletion. The default is 1440 or 24 hours. We set in lower so as not to use so much space for the arclogs.

    Exec DBMS_LOGSTDBY.APPLY_SET ( 'LOG_AUTO_DEL_RETENTION_TARGET' , 120 ) ;

Archive log report

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';

Session altered.

SQL>

select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;

TIME              SIZE_MB
------------- ----------------------------
2015-07-04 11             19.5673828
2015-07-06 12              4.00683594
2015-07-12 07             55.2021484
2015-07-12 08             1.97949219
2015-07-20 09             49.2480469
2015-07-22 10             22.5185547

Oracle Database catbundle.sql

Introduction : A database bundle series is a sequence of patches where each patch in the series includes the contents of the previous patch in the series. That is, the patches are cumulative. Database patches can consist of both code changes (.o files) that are linked into the Oracle Database binary file, and/or database changes (.sql or .plb files) that are loaded into the database after the binary has been patched. Code changes are applied through the OPatch utility, which takes care of archiving the provided .o files into the correct library, and relinking Oracle.

Installation of the database changes is done by catbundle.sql, which takes input from an XML file named bundledata_<bundle series>.xml. This file specifies the particular scripts that need to run for installation or rollback of each patch in the bundle series. The catbundle.sql script is executed as part of the application of a database bundle patch. It loads the script files (.sql, .plb) that are a part of the patch into a database. To minimize the amount of change when applying a bundle patch, the minimum list of scripts are executed. catbundle.sql determines the last bundle applied to the database and executes only the scripts in the patch that have changed since the last bundle patch was applied.


2 catbundle.sql and Patch Apply

During a bundle patch apply, catbundle.sql determines the last bundle in the series that was loaded in the database from information stored in the dba_registry_history view. It processes the information in bundle_data_<bundle series>.xml, which is in each bundle patch. It determines the script files that are to be loaded into the database for both a patch apply and a patch rollback. The patch apply script is then generated to catbundle_<bundle series>_<database SID>_APPLY.<TIMESTAMP>.sql, and the rollback script is generated to catbundle_<bundle series>_<database SID>_ROLLBACK.sql.

After generating both the patch apply and rollback scripts, catbundle.sql automatically runs the patch apply script, which adds a row to dba_registry_history view to document which patch bundle has been applied.


3 catbundle.sql and Patch Rollback

If a bundle patch needs to be rolled back, then catbundle_<bundle series>_<database SID>.sql is executed as part of the rollback. This script reloads all script files that are a part of the bundle series, then adds a row to the dba_registry_history view to indicate a rollback was executed.


4 Files Generated by catbundle.sql

The catbundle.sql and bundledata_<bundle series>.xml files are located in $ORACLE_HOME/rdbms/admin.

For Oracle Database 11.2.0.2 and later releases, the files generated by catbundle.sql are located in $ORACLE_BASE/cfgtoollogs/catbundle. For earlier releases, the generated files are located in $ORACLE_HOME/cfgtools/catbundle.

These include:

catbundle_<bundle series>_<database SID>_APPLY>_ROLLBACK.sql

catbundle_<bundle series>_<database SID>_ROLLBACK.sql

catbundle_<bundle series>_<database SID>_GENERATE_<TIMESTAMP>.log

catbundle_<bundle series>_<database SID>_APPLY_<TIMESTAMP>.log

catbundle_<bundle series>_<database SID>_ROLLBACK_<TIMESTAMP>.log


5 catbundle.sql and dba_registry_history View

catbundle.sql modifies the dba_registry_history view as follows:

Name                               Null?    Type
--------------------------------- ------- ----------------------------
ACTION_TIME                                TIMESTAMP(6)
ACTION                                     VARCHAR2(30)
NAMESPACE                                  VARCHAR2(30)
VERSION                                    VARCHAR2(30)
ID                                                      NUMBER
BUNDLE_SERIES                              VARCHAR2(30)
COMMENTS                                   VARCHAR2(255)

The ID, bundle_series, and comments columns come directly from the bundledata.xml input file. For a given database, the dba_registry_history view provides a log of the bundle patches for which the scripts have been loaded in that database. You can use the following query to report the actions for a given database:
select action_time, action, version, id, comments
  from dba_registry_history
  where bundle_series = '<bundle series>'
  order by action_time;
 
6 catbundle.sql after Database Creation and Upgrade

Execution of catbundle.sql is not always required for new and upgraded databases. The Readme documentation indicates when it is required. However, you can executecatbundle.sql when it is not required so that the new or upgraded database has an updated dba_registry_history table.
Starting with Database 11.2.0.2.0, a dummy catbundle.sql is run at database upgrade and creation time, which creates a dba_registry_history entry with bundle series PSUand ID = "0". Reports that query on this view for the PSU series returns a row for every upgraded and newly created database.

7 Bundle Series

The following table lists current bundle series and their names tracked in the dba_registry_history view.
Table 1 Names of Bundle Series in dba_registry_history view

Bundle Series                                            Name
----------------------------------------------------------------------
Exadata Database Recommended Patches                          EXA
Windows Bundles                                              WINBUNDLE
Critical Patch Updates                                       CPU
Patch Set Updates                                             PSU
Overlay Patch Set Updates                                      OPSU


8 Enterprise Manager Grid Control catbundle.sql Wrapper Scripts

Bundle patches include the wrapper scripts that invoke catbundle.sql, such as catcpu.sql and catcpu_rollback.sql.
These scripts enable Enterprise Manager Grid Control to apply the patch. Customers should not rely on these scripts,
but should directly execute catbundle.sql.

Reference : 605795.1

Script for Checking the Grid Control Agent CPU, Memory & Threads Usage ( 464414.1)

This is a shell script. The only prerequisite is that the agent is running while running the script.

Download the Script or save its content in a file called "agent_snap.sh" under Agent's ORACLE_HOME/bin directory.

Change the permissions on the script to make it executable:
chmod u+x agent_snap.sh

export ORACLE_HOME=<path to agent home>
$ORACLE_HOME/bin/emctl status agent
cd $ORACLE_HOME/bin

./agent_snap.sh &

4.Verify that the agent_snap.log file is created under AGENT_HOME/sysman/log or
AGENT_HOME/<nodename>/sysman/log (for RAC agents) and populated with the expected information.

5. The script is set to collect data by default every 120 seconds. You can change the interval as required by modifying the following line of the script:

sleep 120

Leave the script running for as long as you need to gather relevant information for the problem you are diagnosing.
Note that you can customize the script as needed to run other commands to capture additional information every 120 seconds.

6. To stop the script identify the process id and kill it:

$ ps ef | grep agent_snap.sh
$ kill 9 <pid of agent_snap.sh>

LINUX
=====

#!/bin/sh

emhome=`$ORACLE_HOME/bin/emctl getemhome | tail -1 | awk 'BEGIN {FS="="} {print $2}'`
echo -e "date\t\tcpu%\tvmem\trmem\tthreads\trunning\tready\tscheduled" >> $emhome/sysman/log/agent_snap.log
while [ 2 -gt 1 ]
do

pid=`$ORACLE_HOME/bin/emctl status agent|grep "Agent Process ID" | awk '{print $5}'`
dateStr=`date "+%m/%d %H:%M:%S"`
cpu=`ps -p $pid -o "pcpu"| tail -1 | sed 's/^[ ]*//'`
threads=`ps -p $pid -o "thcount"| tail -1 | sed 's/^[ ]*//'`
vmem=`ps -o vsz $pid | tail -1 | sed 's/^[ ]*//'`
rmem=`ps -o rss $pid | tail -1 | sed 's/^[ ]*//'`

$ORACLE_HOME/bin/emctl status agent scheduler > /tmp/snapschedule
running_line=`grep -n "Running entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
ready_line=`grep -n "Ready entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
scheduled_line=`grep -n "Scheduled entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
final_line=`grep -n "Agent is" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
running=`expr $ready_line - $running_line - 1`;
ready=`expr $scheduled_line - $ready_line - 1`;
scheduled=`expr $final_line - $scheduled_line - 2`;

echo -e "$dateStr\t$cpu\t$vmem\t$rmem\t$threads\t$running\t$ready\t$scheduled" >> $emhome/sysman/log/agent_snap.log

sleep 120
done

SOlARIS:
=========

#!/bin/sh

emhome=`$ORACLE_HOME/bin/emctl getemhome | tail -1 | awk 'BEGIN {FS="="} {print $2}'`
echo "date\t\tcpu\tvmem\trmem\tthreads\trun\tready\tsched" >> $emhome/sysman/log/agent_snap.log
while [ 2 -gt 1 ]
do
pid=`$ORACLE_HOME/bin/emctl status agent|grep "Agent Process ID" | awk '{print $5}'`
dateStr=`date "+%m/%d %H:%M:%S"`
cpu=`ps -p $pid -o "pcpu"| tail -1 | sed 's/^[ ]*//'`
threads=`ps -p $pid -o "nlwp"| tail -1 | sed 's/^[ ]*//'`
vmem=`ps -p $pid -o "vsz"  | tail -1 | sed 's/^[ ]*//'`
rmem=`ps -p $pid -o "rss" | tail -1 | sed 's/^[ ]*//'`
$ORACLE_HOME/bin/emctl status agent scheduler > /tmp/snapschedule

running_line=`grep -n "Running entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
ready_line=`grep -n "Ready entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
scheduled_line=`grep -n "Scheduled entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
final_line=`grep -n "Agent is" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`
running=`expr $ready_line - $running_line - 1`;
ready=`expr $scheduled_line - $ready_line - 1`;
scheduled=`expr $final_line - $scheduled_line - 2`;

echo "$dateStr\t$cpu\t$vmem\t$rmem\t$threads\t$running\t$ready\t$scheduled" >> $emhome/sysman/log/agent_snap.log

sleep 120

done

AIX

=====

#!/bin/sh

emhome=`$ORACLE_HOME/bin/emctl getemhome | tail -1 | awk 'BEGIN {FS="="} {print $2}'`

echo "date\t\tcpu\tvmem\trmem\tthreads\trun\tready\tsched" >> $emhome/sysman/log/agent_snap.log

while [ 2 -gt 1 ]

do

pid=`$ORACLE_HOME/bin/emctl status agent|grep "Agent Process ID" | awk '{print $5}'`

dateStr=`date "+%m/%d %H:%M:%S"`

cpu=`ps -p $pid -o "pcpu"| tail -1 | sed 's/^[ ]*//'`

threads=`ps -p $pid -o "thcount"| tail -1 | sed 's/^[ ]*//'`

vmem=`ps v $pid | tail -1 | sed 's/^[ ]*//' | awk '{print $6}'`

rmem=`ps v $pid | tail -1 | sed 's/^[ ]*//' | awk '{print $7}'`

$ORACLE_HOME/bin/emctl status agent scheduler > /tmp/snapschedule

running_line=`grep -n "Running entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`

ready_line=`grep -n "Ready entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`

scheduled_line=`grep -n "Scheduled entries" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`

final_line=`grep -n "Agent is" /tmp/snapschedule | awk 'BEGIN {FS=":"} {print $1}'`

running=`expr $ready_line - $running_line - 1`;

ready=`expr $scheduled_line - $ready_line - 1`;

scheduled=`expr $final_line - $scheduled_line - 2`;

echo "$dateStr\t$cpu\t$vmem\t$rmem\t$threads\t$running\t$ready\t$scheduled" >> $emhome/sysman/log/agent_snap.log

sleep 120

done

The agent_snap.log file will show the timestamp, CPU usage, virtual memory usage, physical memory usage and number of threads spawned by the agent process. It will also show the number of running, ready and scheduled collection items. The latter are based on the 'emctl status agent scheduler' and can show interesting trends in the number of running metric collections that
can then be linked to what we see in terms of resource consumption.

DBConsole Not starting: An instance of Oracle Enterprise Manager Database Control is already running

Attempting to start the DBConsole fails with:

An instance of Oracle Enterprise Manager Database Control is already running.

Issuing emctl stop dbconsole returns a status saying that it is successfully stopped.

emoms.trc log shows the following:

20120126 07:56:56 Thread12968 WARN http: snmehl_connect: connect failed to (<MY HOST DOMAIN:PORT>): Connection refused (error = 146)
20120126 07:56:56 Thread12968 ERROR pingManager: nmepm_pingReposURL: Cannot connect to <MY HOST DOMAIN:PORT> /em/upload/: retStatus=32
20120126 07:57:13 Thread12971 ERROR upload: Error in uploadXMLFiles. Trying again in 300.00 seconds.

CAUSE

Process did not shutdown cleanly form a previous run.

SOLUTION

1) Ensure all dbconsole related processes are not running:

ps ef | grep dbconsole
ps ef| grep emagent
kill 9 <returned pid's above>

2) rename/remove the existing pid file:

$ORACLE_HOME/<hostname>_<SID>/sysman/emctl.pid


3) start back the DB Console.

kill command for AWS RDS environment

exec rdsadmin.rdsadmin_util.kill(1401,36093) -- (SQL_ID, serial number)