NFS mount options for Oracle database

OEM 12c job failed to create standby database for Oracle 10.2 on Oracle Linux.
NFS mounted staging area is used.
Production database alert log shows
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/mnt/software/oracle_CCASEPRD_linux/CCASEPRD_43/CONTROL_FILE_BACKUP_STANDBY_NO_RECOVERY'

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 2944'

which is also confirmed by SQLPLUS running the same command.

The problem is solved by using nolock option for NFS mount.

The following NFS options are successful:

rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,nolock,vers=3,timeo=600

Useful info on Oracle Support:
Mount Options for Oracle files when used with NFS on NAS devices (Doc ID 359515.1)
Howto Optimize NFS Performance with NFS options. (Doc ID 397194.1)
Poor Performance of APPS 11i or 12 When Using Shared APPL_TOP with NAS (Doc ID 802704.1)

OEM 12c Named Credentials - SSH Key Credentials

The following commands can be used on OMS host to configure SSH connection from OMS host to target host. Check/backup authorized_keys on OMS host. sshUserSetup.sh overwrites authorized_keys instead of just adding public key (it created backup though authorized_keys.tmp).
cd /oui/prov/resources/scripts
sshUserSetup.sh -setup -user -hosts

Alternately (if you do not have password for oracle on target host) generate SSH keys on OMS host with ssh-keygen and add id_rsa.pub to authorized_keys on target host (/home/oracle/.ssh) using root or sudo.

Then in OEM navigate to Setup > Security > Named Credentials

and create New Credential with Credential Type = SSH Key Credentials, using private/public keys from OMS host.

OEM Jobs Report

Environment

Oracle Enterprise Manager (OEM) Grid Control 11.1

Report

This report shows backup jobs scheduled in the given time interval. It also shows HOST name and IP address of the database target which is handy for preparing for network, server or co-location outages.

Modify parameters in the end of the SQL.

Attention! The report looks for NEXT execution time (not all future ones). If a job is supposed to run before the given interval as well as in the interval, it will not appear in the report.

Run it in OEM repository (OMR) as any user who can select SYSMAN tables.

SELECT

        j.job_name,

        t.target_name, --t.target_type, t.target_guid

        --j.job_owner,  j.job_type,

        --j.job_id,

        --e.execution_id,  e.start_time, e.end_time,

        DECODE(status,

                1, 'Scheduled',

                2, 'Running',

                3, 'Error',

                4, 'Failed',

                5, 'Succeeded',

                6, 'Suspended By User',

                7, 'Suspended: Agent Unreacheable',

                8, 'Stopped',

                9, 'Suspended on Lock',

               10, 'Suspended on Event',

               11, 'Stop Pending',

               13, 'Suspend Pending',

               14, 'Inactive',

               15, 'Queued',

               16, 'Failed Retried',

               17, 'Suspended',

               18, 'Skipped', status) status,

        e.scheduled_time,

        DECODE(s.timezone_info, 3, to_char(s.timezone_offset), s.timezone_region) timezone_region,

        DECODE (s.timezone_info,

                    1, 'Repository',

                    2, 'Agent',

                    3, 'Specified Offset/Region',

                    4, 'Specified Offset/Region',

                    s.timezone_info) timezone_type,

        DECODE (s.frequency_code, 1, 'One Time',

                                     2, 'Interval',

                                     3, 'Daily',

                                     4, 'Weekly',

                                     5, 'Monthy',

                                     6, 'Yearly') schedule_type,

        s.interval,

        TH.TARGET_NAME host_name, TP.PROPERTY_VALUE IP_address

      FROM

        sysman.MGMT_JOB j,

        sysman.MGMT_JOB_EXEC_SUMMARY e,

        sysman.MGMT_JOB_TARGET jt,

        sysman.MGMT_TARGETS t,

        sysman.MGMT_TARGET_ASSOCS ta,

        sysman.MGMT_TARGETS th,

        sysman.MGMT_TARGET_PROPERTIES tp,

        sysman.MGMT_JOB_SCHEDULE s

      WHERE

        j.schedule_id = s.schedule_id AND

        J.IS_LIBRARY = 0 and

        j.system_job = 0 AND

        j.nested=0 AND

        j.job_id=e.job_id AND

        j.is_corrective_action=0 AND

        e.job_id=jt.job_id (+) AND

        e.execution_id=jt.execution_id (+) AND

        jt.target_guid=t.target_guid (+)

        and jt.target_guid=TA.SOURCE_TARGET_GUID

        and TA.ASSOC_TARGET_GUID=Th.TARGET_GUID and Th.TARGET_TYPE='host'

        and TA.ASSOC_TARGET_GUID=TP.TARGET_GUID and TP.PROPERTY_NAME='IP_address'

        --

        -- you can modify the following parameters

        --

        and Job_name like 'BACKUP%'

        --and status = 1 -- Scheduled

        and scheduled_time between to_date('2012-10-27 16:00','yyyy-mm-dd hh24:mi') and to_date('2012-10-28 10:00','yyyy-mm-dd hh24:mi');

High temp space consumption by OEM agent DBSNMP

Environment

Solaris 10, Oracle 11.2.0.2, OEM agent 11.1.0.1.0

Symptoms

Agent (DBSNMP) is consuming CPU and huge amount of temp space by selecting from v$lock

    /* OracleOEM */ WITH blocked_resources AS

(select id1 ,id2 ,SUM(ctime) as blocked_secs ,MAX(request) as max_request ,COUNT(1) as blocked_count

from v$lock

where request > 0 group by id1,id2 ) ,blockers AS

(select L.* ,BR.blocked_secs ,BR.blocked_count

from v$lock L ,blocked_resources BR

where BR.id1 = L.id1 and BR.id2 = L.id2 and L.lmode > 0 and L.block <> 0 )

select B.id1||'_'||B.id2||'_'||S.sid||'_'||S.serial# as id ,'SID,SERIAL:'||S.sid||','||S.serial#||',LOCK_TYPE:'||B.type||',PROGRAM:'||S.program||',MODULE:'||S.module||',ACTION:'||S.action||',MACHINE:'||S.machine||',OSUSER:'||S.osuser||',USERNAME:'||S.username as info ,B.blocked_secs ,B.blocked_count

from v$session S ,blockers B

where B.sid = S.sid;

Solution

Check and gather statistics for fixed objects.

select OWNER, TABLE_NAME, object_type, LAST_ANALYZED from dba_tab_statistics where table_name IN

('X$KSUSE',

'X$KDNSSF',

'X$KSQEQ',

'X$KTADM',

'X$KTATRFIL',

'X$KTATRFSL',

'X$KTATL',

'X$KTSTUSC',

'X$KTSTUSS',

'X$KTSTUSG',

'X$KTCXB',

'X$KSQRS',

'X$KSLWT',

'X$KSLED');

exec dbms_stats.GATHER_FIXED_OBJECTS_STATS;

Useful Info

Check the following Oracle Support documents

Query Against v$lock Run from OEM Performs Slowly [ID 1328789.1]

High temp space consumption by DBSNMP [ID 1389377.1]

EM Agent DBSNMP Using Up Excessive Temp Space In Database [ID 748251.1]