Soumya's Database Blog : May 2024

OEM 13c Agent status shows 'Diagnose for Agent Blocked (Bounce Counter Mismatch)'

Problem description:-

Recently for a customer we had performed a DR Drill . The customer had OEM agent installed in all oracle database server . Now after the drill was successful there was a requirement to restore one of the server from VM snapshot backup which was taken before the drill. Now after restoration of that vm , the OEM agent for that target was showing status as 'Diagnose for Agent Blocked (Bounce Counter Mismatch)'

Solution:-

To resolve this problem , we did the following.

Logged into target DB server where agent status was showing as follows

E:\Agent13c\agent_inst\agent_13.5.0.0.0\bin>emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 5

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

Agent Version : 13.5.0.0.0

OMS Version : (unknown)

Protocol Version : 12.1.0.1.0

Agent Home : E:/Agent13c/agent_inst/agent_inst

Agent Log Directory : E:/Agent13c/agent_inst/agent_inst\sysman\log

Agent Binaries : E:\Agent13c\agent_inst\agent_13.5.0.0.0

Core JAR Location : E:\Agent13c\agent_inst\agent_13.5.0.0.0\jlib

Agent Process ID : 6428

Parent Process ID : 5376

Agent URL : https://WMDR.com:3872/emd/main/

Local Agent URL in NAT : https://WMDR.com:3872/emd/main/

Repository URL : https://OEM-DBMGR.com:1159/empbs/upload

Started at : 2024-05-13 03:30:26

Started by user : WMDR16$

Operating System : Windows version 10.0 (amd64)

Number of Targets : 6

Last Reload : (none)

Last successful upload : (none)

Last attempted upload : (none)

Total Megabytes of XML files uploaded so far : 0

Number of XML files pending upload : 672

Size of XML files pending upload(MB) : 0.91

Available disk space on upload filesystem : 44.17%

Collection Status : Collections enabled

Heartbeat Status : Agent is blocked

Blocked Reason : 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.

Last attempted heartbeat to OMS : 2024-05-13 04:55:01

Last successful heartbeat to OMS : (none)

Next scheduled heartbeat to OMS : 2024-05-13 04:58:01

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

Agent is Running and Ready

As we can see the Hearbeat status is showing “Agent is Blocked” and reason behind block is agent is out of sync with its repository.

To resolve this problem, We logged into OMS Server and executed following

E:\>cd E:\u01\app\middleware\bin

E:\u01\app\middleware\bin>set OMS_HOME=E:\u01\app\middleware

E:\u01\app\middleware\bin>SET JAVA_HOME=E:\u01\app\agent\agent_13.5.0.0.0\oracle_common\jdk

E:\u01\app\middleware\bin>SET ORACLE_HOME=E:\u01\app\middleware

E:\u01\app\middleware\bin>emcli login -username=sysman

Enter password :

E:\u01\app\middleware\bin>emcli sync

Synchronized successfully

E:\u01\app\middleware\bin>emcli resyncAgent -agent=WMDR.com:3872

Resync job RESYNC_20240513083609008 successfully submitted

After a while, when we checked the oem hearbeat status was showing OK.

E:\Agent13c\agent_inst\agent_13.5.0.0.0\bin>emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 5

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

Agent Version : 13.5.0.0.0

OMS Version : 13.5.0.0.0

Protocol Version : 12.1.0.1.0

Agent Home : E:/Agent13c/agent_inst/agent_inst

Agent Log Directory : E:/Agent13c/agent_inst/agent_inst\sysman\log

Agent Binaries : E:\Agent13c\agent_inst\agent_13.5.0.0.0

Core JAR Location : E:\Agent13c\agent_inst\agent_13.5.0.0.0\jlib

Agent Process ID : 2660

Parent Process ID : 7172

Agent URL : https://WMDR.com:3872/emd/main/

Local Agent URL in NAT : https://WMDR16.com:3872/emd/main/

Repository URL : https://OEM-DBMGR.com:1159/empbs/upload

Started at : 2024-05-13 05:33:22

Started by user : WMDR16$

Operating System : Windows version 10.0 (amd64)

Number of Targets : 6

Last Reload : (none)

Last successful upload : 2024-05-13 05:37:19

Last attempted upload : 2024-05-13 05:37:19

Total Megabytes of XML files uploaded so far : 0.08

Number of XML files pending upload : 3

Size of XML files pending upload(MB) : 0

Available disk space on upload filesystem : 44.15%

Collection Status : Collections enabled

Heartbeat Status : Ok

Last attempted heartbeat to OMS : 2024-05-13 05:36:42

Last successful heartbeat to OMS : 2024-05-13 05:36:42

Next scheduled heartbeat to OMS : 2024-05-13 05:37:43

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

Agent is Running and Ready

ORA-01111: name for data file is unknown – rename to correct file

Recently I asked one of my junior colleague to add a datafile in a tablespace. The database had a physical standby database running. When she added the datafile , the parameter “standby_file_management” in standby database server was set to manual. So after addition of the datafile the file was created like “UNNAMED00006”

And since then the dataguard synchronization was broken.

In alert log the event of datafile addition was showing like this

2024-04-05T05:48:11.121082-07:00

File #6 added to control file as 'UNNAMED00006' because

the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL

The file should be manually created to continue.

PR00 (PID:5572): MRP0: Background Media Recovery terminated with error 1274

2024-04-05T05:48:11.183592-07:00

Errors in file E:\APP\diag\rdbms\edwprddr\edwprdint\trace\edwprdint_pr00_5572.trc:

ORA-01274: cannot add data file that was originally created as 'E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.DBF'

PR00 (PID:5572): Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovery stopped due to failure in applying recovery marker (opcode 17.30).

By checking the dataguard broker , we found

DGMGRL> show configuration

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

edwprd_int - Primary database

edwprddr - Physical standby database

Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: Disabled

Configuration Status:

ERROR (status updated 48 seconds ago)

Solution: To resolve this issue, we took following actions

First we needed to identify which file id was not created properly.

So login to primary database and look for file# 6 which we retrieved from alert log.

SQL>Select file#, name from v$datafile where file#=6;

FILE# NAME

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

6 E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.DBF

So , now we will have to recreate the data file in proper location as it was not created in proper location because standby_file_management parameter set to manual.

SQL> SQL> alter database recover managed standby database cancel;

alter database recover managed standby database cancel

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

This command we used to cancel the MRP but , it failed as MRP process was not active .

Now create a datafile in proper location by referring current 'UNNAMED00006' file

SQL> alter database create datafile 'E:\APP\ORACLE\PRODUCT\19.3.0\DBHOME_1\DATABASE\UNNAMED00006' as 'E:\APP\ORADATA\EDWPRD_INT\SYSTEM02.dbf';

Database altered.

SQL>alter system set standby_file_management='AUTO';

Now start the MRP process, it should start applying all the archives and put the database back in sync

SQL> Alter database recover managed standby database disconnect from session;

Database altered.

Now after checking the dataguard broker , status showed success

DGMGRL> show configuration

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

edwprd_int - Primary database

edwprddr - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 35 seconds ago)

How to disable audit trail in E Business suite R12.2

There could a situation where you want to stop auditing, then you must set the audit group state to either “Disable-prepare for archive” or
“Disable-Interrupt Audit” and run the “AuditTrail Update Tables” report.

1. Disable Prepare for Archive

Copies the current value of all rows in the audited table into the shadow table and then disables the auditing triggers. There is no
longer any recording of any changes. The shadow table should be archived at this point.

2. Disable Interrupt Audit

Modifies the triggers to store one "final" row in the shadow table for each row that is modified in the audit table. There is no
longer any recording of any changes.

3. Disable Purge Table

Drops the auditing triggers and views and deletes all data from the shadow table.

These options are available from the "Define Audit Groups" form. Login as the System Administrator User Responsibility: Navigate AuditTrail Groups

Select one of the above options contained in the "State" field to disable auditing. Then run the Audit Trial Update Tables report.