ORA-01422: exact fetch returns more than requested number of rows in Oracle OEM 13

 We are getting the following error when we want to add exadata database instances in Oracle OEM 13
Changes not saved.
bacoredb_STLBAS : ORA-01422: exact fetch returns more than requested number of rows



The error is seen due to duplicate targets that were existing in the repository.
Run the following query as SYSMAN user on the repository database to confirm if duplicate targets are present in the repository:


SQL> ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;
SQL> column ENTITY_TYPE format a20
SQL> column ENTITY_NAME format a37
SQL> set lin 200
SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database      bacoredb_bacoredb1                    2          3 01-NOV-23
oracle_pdb         bacoredb_CDBROOT                       2          3 01-NOV-23
oracle_pdb         bacoredb_EMOB                       2          3 01-NOV-23
oracle_pdb         bacoredb_PDB1                       2          3 01-NOV-23
oracle_pdb         bacoredb_STLBAS                       2          3 01-NOV-23
oracle_pdb         bacoredb_HIKMAH                       2          1 29-DEC-23
oracle_pdb         bacoredb_ETESORO                       2          1 02-JAN-24
oracle_dbsys         bacoredb_sys                       2          3 01-NOV-23
oracle_database      bacoredb_bacoredb2_1                   1          1 09-NOV-23
rac_database         bacoredb_1                        1          1 09-NOV-23
oracle_database      bacoredb_bacoredb2                    2          3 02-NOV-23

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
rac_database         bacoredb                           2          3 01-NOV-23

12 rows selected.

SQL>

From OMS server,

$OMS_HOME/bin/emcli login -username=SYSMAN
$OMS_HOME/bin/emcli sync
$OMS_HOME/bin/emcli delete_target -name="bacoredb_STLBAS" -type="oracle_database" -delete_monitored_targets

[oracle@em13c ~]$ /u01/app/oem/bin/emcli login  -username=SYSMAN
Enter password :

Login successful
[oracle@em13c ~]$ /u01/app/oem/bin/emcli sync
Synchronized successfully
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_STLBAS" -type="oracle_database" -delete_monitored_targets
Error: Target does not exist
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_bacoredb2_1" -type="oracle_database" -delete_monitored_targets
Target "bacoredb_bacoredb2_1:oracle_database" deleted successfully
[oracle@em13c ~]$

SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database      bacoredb_bacoredb1                    2          3 01-NOV-23
oracle_pdb         bacoredb_CDBROOT                       2          3 01-NOV-23
oracle_pdb         bacoredb_EMOB                       2          3 01-NOV-23
oracle_pdb         bacoredb_PDB1                       2          3 01-NOV-23
oracle_pdb         bacoredb_STLBAS                       2          3 01-NOV-23
oracle_pdb         bacoredb_HIKMAH                       2          1 29-DEC-23
oracle_pdb         bacoredb_ETESORO                       2          1 02-JAN-24
oracle_dbsys         bacoredb_sys                       2          3 01-NOV-23
rac_database         bacoredb_1                        1          1 09-NOV-23
oracle_database      bacoredb_bacoredb2                    2          3 02-NOV-23
rac_database         bacoredb                           2          3 01-NOV-23

11 rows selected.

SQL>

[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_1" -type="rac_database" -delete_monitored_targets
Target "bacoredb_1:rac_database" deleted successfully
[oracle@em13c ~]$

SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database      bacoredb_bacoredb1                    2          3 01-NOV-23
oracle_pdb         bacoredb_CDBROOT                       2          3 01-NOV-23
oracle_pdb         bacoredb_EMOB                       2          3 01-NOV-23
oracle_pdb         bacoredb_PDB1                       2          3 01-NOV-23
oracle_pdb         bacoredb_STLBAS                       2          3 01-NOV-23
oracle_pdb         bacoredb_HIKMAH                       2          1 29-DEC-23
oracle_pdb         bacoredb_ETESORO                       2          1 02-JAN-24
oracle_dbsys         bacoredb_sys                       2          3 01-NOV-23
oracle_database      bacoredb_bacoredb2                    2          3 02-NOV-23
rac_database         bacoredb                           2          3 01-NOV-23

10 rows selected.

SQL>




Ref
EM12c, EM13c: How to Delete a Target in Enterprise Manager Cloud Control ( Doc ID 1905181.1 )