Oracle 12c Agent Blocked due to Plugin Mismatch [NOT_FOUND_IN_INPUT]

I recently ran into this problem when Oracle Management Agent 12c was in blocked state and was showing below errors:

Run below query as SYSMAN on repository database. Provide hostname of blocked agent when prompted:

SQL> select * from mgmt_blocked_agents where TARGET_GUID in (select target_guid from mgmt_targets where lower(target_name) like '%&hostname%');

TARGET_GUID                      BLOCKED_T

-------------------------------- ---------

BLOCKED_BY

------------------------------------------------------------------------------------------------------------------------------------------------------

BLOCKED_REASON_MSG

------------------------------------------------------------------------------------------------------------------------------------------------------

BLOCKED_REASON_NLS_ID

----------------------------------------------------------------

BLOCKED_REASON_NLS_PARAMS

------------------------------------------------------------------------------------------------------------------------------------------------------

016FFE88BD675EE4DBD5E5ACD779314B 08-MAR-17

SYSMAN

Plugin mismatches between agent and repository. Check plugin:[ The plug-in is oracle.sysman.db

Version in pla inventory : 12.1.0.2.0

Version on agent : null

Content type : AGENT

Error code : NOT_FOUND_IN_INPUT

Error Msg : This plug-in is present in the inventory for the given agent but has not been provided in the input,  The plug-in is oracle.sysman.oh

Version in pla inventory : 12.1.0.2.0

Version on agent : null

Content type : AGENT

Error code : NOT_FOUND_IN_INPUT

Error Msg : This plug-in is present in the inventory for the given agent but has not been provided in the input,  The plug-in is oracle.sysman.db

Version in pla inventory : 12.1.0.2.0

Version on agent : null

Content type : DISCOVERY

Error code : NOT_FOUND_IN_INPUT

Error Msg : This plug-in is present in the inventory for the given agent but has not been provided in the input,  The plug-in is oracle.sysman.oh

Version in pla inventory : 12.1.0.2.0

Version on agent : null

Content type : DISCOVERY

Error code : NOT_FOUND_IN_INPUT

Error Msg : This plug-in is present in the inventory for the given agent but has not been provided in the input]

BLK_AGENT_FOR_MISMATCH

The problem actually started when agent had gone out-of-sync from repository:

INFO - Reason the OMS blocked the agent: Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console.

This should be resolved by re-synchronizing the agent via OEM or command-line:

1. Using OEM Console
   Navigate to All Targets > Agent Running on Host >Agent Menu > Resynchronization
   This would submit the job for resync

OR

2. Using command-line
   Set environment for OMS on host where OMS is running
   $ emcli login -username=SYSMAN
   $ emcli sync
   $ emcli resyncAgent -Agent="Agent_Hostname:Port"

However, in my case the resync resulted in success but still the agent was not unblocked and errors were still appearing in EM.

I went ahead and investigated resync logfiles on agent host under "/<Agent_Dir>/agent_inst/install/logs" and found

below errors in: agentplugindeploy_29.log
--------------------------------------------------------
The attachhome /usr/oracle/agent12c/core/12.1.0.2.0/oui/bin/runInstaller  -attachHome -silent -waitforcompletion
-invPtrLoc /usr/oracle/agent12c/core/12.1.0.2.0/oraInst.loc ORACLE_HOME=/usr/oracle/agent12c/core/12.1.0.2.0/../../plugins/oracle.sysman.oh.discovery.plugin_12.1.0.2.0
ORACLE_HOME_NAME=oracle_sysman_oh_12_1_0_2_0_discovery_Home4 "HOME_DEPENDENCY_LIST={/usr/oracle/agent12c/core/12.1.0.2.0/../../plugins/oracle.sysman.oh.discovery.plugin_12.1.0.2.0:/usr/oracle/agent12c/core/12.1.0.2.0}" 
-force -ignoreSysPrereqs   failed : trying for 1 time  return value is : 65280
--------------------------------------------------------
 
and more errors in: agentplugindeploy_29.log.err
--------------------------------------------------------
You do not have sufficient permissions to access the inventory '/usr/oracle/11.2.0.1'.
Installation cannot continue. Make sure that you have read/write permissions to the inventory directory and restart the installer.:
The file access permissions do not allow the specified action.
--------------------------------------------------------

This error indicates that resync operation is not able to access local inventory of an Oracle Home (/usr/oracle/11.2.0.1). The home was actually empty and owned by root:system. This must have been left out due to improper deinstallation of old home.

$ ls -a /usr/oracle/11.2.0.1

.   ..

$ ls -ld /usr/oracle/11.2.0.1

drwxr-xr-x    2 root     system          256 29 Nov 2012  /usr/oracle/11.2.0.1

Since the ownership was not correct and the directory wasn't accessible, this needed to be fixed.

$ chown oracle:dba /usr/oracle/11.2.0.1

$ ls -ld /usr/oracle/11.2.0.1

drwxr-xr-x    2 oracle     dba          256 29 Nov 2012  /usr/oracle/11.2.0.1

I re-submitted resync job and the problem was resolved.

Cheers!
Anurag

Creating Pluggable Database on Standby or Dataguard

As you might be aware of fact that we can create Standby Database on a Container Database (CDB) of Multitenant instance, thus it implies that we cannot create a standby database only for a PDB.

A Container Database (CDB) shares its resources like instance, control files, online redo logfiles and standby redo logfiles with PDBs.

Thus when a new pluggable database (PDB) is created on PRIMARY database, the changes are shipped and it is created automatically on STANDBY database as well. An important thing to keep in mind is having standby_file_management set to AUTO, for automatic management of datafiles.

When creating a PDB in dataguard environment, it is important to consider STANDBYS clause in CREATE PLUGGABLE DATABASE statement. This clause accepts two values:

1. ALL - [default] This will create new PDB on all STANDBY databases
2. NONE - This will not create new PDB on any STANDBY databases

Another clause commonly used is PATH_PREFIX. I will discuss this later in this post.

Steps to Create Pluggable Database in Dataguard Environment

1. Prepare directory for keeping datafiles

My environment had same directory structure on primary and standby databases, thus I have created below directory on both:

$ mkdir -p /u01/app/oracle/oradata/data/rackdb1/pdb2

2. Run CREATE PLUGGABLE DATABASE statement on PRIMARY database

SQL> select db_unique_name, database_role from v$database;

DB_UNIQUE_NAME  DATABASE_ROLE
--------------- -------------
rackdb1         PRIMARY

SQL> CREATE PLUGGABLE DATABASE PDB2 
ADMIN USER PDB2ADMIN IDENTIFIED BY "<pass>"
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/data/rackdb1/pdbseed/','/u01/app/oracle/oradata/data/rackdb1/pdb2/')
/

================ALERT LOG (PRIMARY)====================
Thu Mar 02 01:27:06 2017
CREATE PLUGGABLE DATABASE PDB2 ADMIN USER PDB2ADMIN IDENTIFIED BY *FILE_NAME_CONVERT=('/u01/app/oracle/oradata/data/rackdb1/pdbseed/','/u01/app/oracle/oradata/data/rackdb1/pdb2/')
Thu Mar 02 01:27:10 2017
****************************************************************
Pluggable Database PDB2 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#42 to file$(old file#5)
Adding new file#43 to file$(old file#7)
Successfully created internal service pdb2 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB2 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB2 ADMIN USER PDB2ADMIN IDENTIFIED BY *FILE_NAME_CONVERT=('/u01/app/oracle/oradata/data/rackdb1/pdbseed/','/u01/app/oracle/oradata/data/rackdb1/pdb2/')
=======================================================

================ALERT LOG (STANDBY)====================
Thu Mar 02 06:27:11 2017
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /u01/app/oracle/oradata/data/rackdb1/pdb2/system01.dbf from /u01/app/oracle/oradata/data/rackdb1/pdbseed/system01.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /u01/app/oracle/oradata/data/rackdb1/pdb2/system01.dbf
Successfully added datafile 42 to media recovery
Datafile #42: '/u01/app/oracle/oradata/data/rackdb1/pdb2/system01.dbf'
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /u01/app/oracle/oradata/data/rackdb1/pdb2/sysaux01.dbf from /u01/app/oracle/oradata/data/rackdb1/pdbseed/sysaux01.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /u01/app/oracle/oradata/data/rackdb1/pdb2/sysaux01.dbf
Successfully added datafile 43 to media recovery
Datafile #43: '/u01/app/oracle/oradata/data/rackdb1/pdb2/sysaux01.dbf'
=======================================================

 



3. Check status of newly created PDB2

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name

NAME     OPEN_MODE
-------- ----------
PDB$SEED READ ONLY
PDB1     READ WRITE
PDB2     MOUNTED








The database is created in MOUNTED state, this can be opened in read write mode using below ALTER statement.

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

Pluggable database altered.

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

NAME      OPEN_MODE
--------- ----------
PDB$SEED  READ ONLY
PDB1      READ WRITE
PDB2      READ WRITE

4. Check status of PDB2 on STANDBY database

SQL> select db_unique_name, database_role from v$database;

DB_UNIQUE_NAME  DATABASE_ROLE
--------------- ----------------
rackdb1dg       PHYSICAL STANDBY

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

NAME     OPEN_MODE
-------- ---------
PDB$SEED READ ONLY
PDB1     READ ONLY
PDB2     MOUNTED





The new pluggable database is in MOUNTED mode on standby database. Since this is an Active Dataguard instance, I can open it in read only.

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ ONLY;

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

NAME     OPEN_MODE
-------- ---------
PDB$SEED READ ONLY
PDB1     READ ONLY
PDB2     READ ONLY

Cheers!
Anurag

Audit Table (AUD$) Purging and Cleanup

Often you may observe after enabling auditing, the AUD$ or FGA_LOG$ table starts growing. This may grow larger and larger causing its tablespace to run out of space (usually default is SYSAUX). Thus maintenance of audit data becomes important and with a retention period in mind and daily purge job, this is an easy task to do.

I have written another blog on enabling session audit in database, follow this link to see it.

First things first
You may be curious to know or might already know the magnitude of your audit table. You may get the basic details using below four queries:

> Number of rows
SELECT COUNT(*) FROM AUD$;

>  Size of table
SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME='AUD$';

> Oldest record
SELECT MIN(NTIMESTAMP#) FROM AUD$;

> Tablespaces holding audit data
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
---------- ---------------
AUD$       SYSAUX
FGA_LOG$   SYSAUX

It would be good to keep note of these to validate things after purge has been executed.

In case you need to change default tablespace for AUD$ or FGA_LOG$ data to be stored, run below procedure with appropriate modifications:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   audit_trail_location_value  => '<tablespace_name>') ;
END;
/

Below audit trail types are valid:
AUDIT_TRAIL_AUD_STD
AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_OS
AUDIT_TRAIL_XML

For all of them: AUDIT_TRAIL_ALL

Before we start, you can verify that Cleanup has not been initialized by running the following.
Cleanup has not yet been initialized if there are no "DEFAULT CLEAN UP INTERVAL" parameters listed in the output.

set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME            PARAMETER_ AUDIT_TRAIL
------------------------- ---------- --------------------
DB AUDIT TABLESPACE       SYSAUX     STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       SYSAUX     FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000      OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000      XML AUDIT TRAIL
AUDIT FILE MAX AGE        5          OS AUDIT TRAIL
AUDIT FILE MAX AGE        5          XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000      STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000      FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000       OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000       XML AUDIT TRAIL

In case you see DEFAULT CLEAN UP INTERVAL parameters configured, I would recommend to follow this link to blog where I have explained how to deconfigure audit cleanup. It would be good to start afresh then from below section.

Initialize Audit Cleanup

You can run below procedure to initialize cleanup of audit data, the procedure accepts cleanup interval in hours (1 to 999).

I am initializing for 14 days as below:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    DEFAULT_CLEANUP_INTERVAL => 24*14 /*hours x days*/
    );
END;
/

After procedure has been completed you can re-check the config parameters:

SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME            PARAMETER_ AUDIT_TRAIL
------------------------- ---------- --------------------
DB AUDIT TABLESPACE       SYSAUX     STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       SYSAUX     FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000      OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000      XML AUDIT TRAIL
AUDIT FILE MAX AGE        5          OS AUDIT TRAIL
AUDIT FILE MAX AGE        5          XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000      STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000      FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000       OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000       XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        XML AUDIT TRAIL

Once this is configured, we have two options to purge audit data:
1. Complete Purge
2. Partial Purge using LAST ARCHIVE TIMESTAMP

As their name suggests, complete purge will clean-up whole tables retaining nothing, whereas partial purge can retain data that has timestamp greater than last archive timestamp.

After initializing, it is time to implement a procedure that needs to be called when executing purge. I like this way because you can customize procedure to write in alertlog when the job has been run or use it to notify via email etc.

Procedure to Purge Audit Data

Below procedure would delete data from all audit trails, you can update the for specific ones by using specific constants as mentioned earlier in this blog.

**FOR COMPLETE PURGE**
The timestamps for each audit trail can be cleared to allow a complete purge using the CLEAR_LAST_ARCHIVE_TIMESTAMP procedure. This can be scheduled using DBMS scheduler for desired time intervals to perform a full cleanup.

/*  SP_PURGE_AUDIT_TRAIL
Complete Purge
Script by: Anurag Tripathi - v1.0*/

CREATE OR REPLACE PROCEDURE SP_PURGE_AUDIT_TRAIL
AS
BEGIN

  DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
    AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);

   SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/

**FOR PARTIAL PURGE**
This will first set last archive timestamp according to specified retention variable and then schedule purge using next steps.

/*  SP_PURGE_AUDIT_TRAIL
Partial Purge
Script by: Anurag Tripathi - v1.0*/

CREATE OR REPLACE PROCEDURE SP_SET_LAST_ARCH_TS
AS
  retention NUMBER;
BEGIN
  retention := 14 /* days */;
 

   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - retention
   );
END;
/

After the procedures have been created, it is time to schedule a job to run either one of these procedure using dbms scheduler.

Create Job Schedule using DBMS Scheduler for Updating Last Archive Timestamp

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB (
        job_name => 'JOB_SET_LAST_ARCH_TS',
        schedule_name => 'SYS.MAINTENANCE_WINDOW_GROUP',
        job_class => 'DEFAULT_JOB_CLASS',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN SP_SET_LAST_ARCH_TS(); END;',
        comments => 'Job to purge all audit trails'
    );
  
    SYS.DBMS_SCHEDULER.ENABLE(name => 'JOB_SET_LAST_ARCH_TS');
  
END;
/

Create Job for Purging Audit Data

Using below PL block you may schedule the job to run every 24 hours. Thus cleanup will be performed every day.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
        audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        audit_trail_purge_interval => 24,

        audit_trail_purge_name => 'JOB_PURGE_AUDIT_TRAIL',

        use_last_arch_timestamp => TRUE

    );  
END;
/

After creating the purge job, enable the job using SET_PURGE_JOB_STATUS procedure

BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  audit_trail_purge_name      => 'JOB_PURGE_AUDIT_TRAIL',
  audit_trail_status_value    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

After the job has been enabled and running. You may check status of clean events using query:

SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;

Thats all!

Cheers,
Anurag

Deconfigure or Remove Audit Cleanup Job

It may be required at times to deconfigure auditing from database, once the audit parameters are unset it is important to remove audit data maintenance jobs to ensure they don't unnecessarily keep running and contribute to impacting database performance.

Please follow below steps to deconfigure audit purge / cleanup job:

You can verify that Cleanup has been initialized by running the following:

set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME            PARAMETER_VALUE AUDIT_TRAIL
------------------------- --------------- --------------------
DB AUDIT TABLESPACE       SYSAUX          STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       SYSAUX          FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000           OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000           XML AUDIT TRAIL
AUDIT FILE MAX AGE        5               OS AUDIT TRAIL
AUDIT FILE MAX AGE        5               XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000           STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000           FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000            OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000            XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              XML AUDIT TRAIL

If there are "DEFAULT CLEAN UP INTERVAL" parameters listed in the output then it is initialized. In above output 24 hours is set for cleanup interval for all four audit types viz Standard, FGA, OS and XML.

You can see which audit job is responsible for clean-up tasks, this is explicitly set by DBA, so if there is no output then most probably there can be some other job working for cleanup.

SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME               JOB_STAT AUDIT_TRAIL      JOB_FREQUENCY
---------------------- -------- ---------------- -----------------------
PURGE_ALL_AUDIT_TRAILS ENABLED  ALL AUDIT TRAILS FREQ=HOURLY;INTERVAL=24

In case you do not see any output from above query:

SELECT JOB_NAME, ENABLED, JOB_ACTION FROM DBA_SCHEDULER_JOBS 
WHERE JOB_NAME LIKE '%PURGE%' OR JOB_NAME LIKE '%CLEAN%';

JOB_NAME                  STATE           ENABLED
------------------------- --------------- ------
PURGE_ALL_AUDIT_TRAILS    SCHEDULED       TRUE

JOB_ACTION
--------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;


If even this does not helps, then please monitor other jobs in database that are cleaning up your AUD$ table.

Once you have identified the job you can drop it using below procedures:

EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB('PURGE_ALL_AUDIT_TRAILS');

OR

EXEC DBMS_SCHEDULER.DROP_JOB('PURGE_ALL_AUDIT_TRAILS');





After dropping the job, you can de-initialize cleanup by running procedure:


BEGIN
  DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
    AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/



If you have only one or two types of auditing enabled then modify accordingly. Below audit trail types are valid:
AUDIT_TRAIL_AUD_STD
AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_OS
AUDIT_TRAIL_XML

That's all!

Cheers,
Anurag