MDB Registration error, when creating or updating a new CI

Document ID : KB000045505
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction: 

CORACleanup is a small tool to fix inconsistencies in the MDB.

It is a tool used to clean up inconsistencies in asset fields that may have occurred during registration or re-registration of the assets. Usually these can occur if multiple products are sharing and updating these assets together.

Coracleanup tool does not come out of box from ServiceDesk. It has to be requested for to CA support

Background:  

This technical document will address best practices to use coracleanup.

Environment:  

ServiceDesk 12.x, 14.x

*In order to provide right Coracleanup tool, please provide the cora version by running the command “coraver”

Please review the following document that talks about coraver: TEC508407

Instructions: 

Coracleanup is an NT command line utility that helps apply the latest changes to CORA to the existing and registered assets in the Database

 

CORACLEANUP <switches>

/SERVER <server>     : The name of the server hosting the database ***to specify a non-default port /SERVER <server:xxxx> or see /INSTANCE
/SCOPE <scope>        : USD, NSM, DSM, SPM, ALL
/METHOD <method>  : Reinit, Delete, Test
/DATABASE <name>  : Name of database, if not provided defaults to "mdb"
/TYPE <type>             : Type of database, set to [2 (MSSQL) |3 (Oracle) ]
/INSTANCE <name>    : Database instance, if not provided default instance is used ***to specify a non-default port /INSTANCE <name>,<port>
/USERNAME <name>   : Username for the database
/PASSWORD <password> : Password for the user specified by /USERNAME
/LOG <option>        : Set to either ERRORS or FULL defaults to errors

NB: If username and password is omitted a trusted connection will be used on MS SQL Server

Example:
CORACLEANUP /SERVER myserver.com /SCOPE all /METHOD reinit /DATABASE mdb /TYPE 2 /USERNAME admin /PASSWORD Adm1n /LOG full

or

CORACLEANUP /DATABASE mdb /SERVER myserver.com /INSTANCE myserver,5002 /SCOPE all /METHOD reinit /TYPE 2 /USERNAME uapmadmin /PASSWORD xxxxx /LOG full

or

CORACLEANUP /SERVER myserver.com:1234 /SCOPE all /METHOD reinit /DATABASE mdb /TYPE 2 /USERNAME admin /PASSWORD Adm1n /LOG full

 

Supported scopes: 

USD - Apply the method only to USD 
NSM - Apply the method only to NSM 
DSM - Apply the method only to DSM 
SPM - Apply the method only to SPM 
ALL - Apply the method too all (USD, NSM, DSM, SPM) products 
SUBCHEMA_# - Apply the method only to a specific subschema #. It's recommended to always run this first and then the scope all. Use this only if the subschema is one of the following 12, 20, 21, 22,23. 

 

Supported methods: 

Reinit - this will delete all the assets from CORA that belong to the product specified in the scope and re-register based on the information from the product's table all the assets. 

Delete - this will delete all the assets from CORA that belong to the product specified in the scope 

Test - this will test CORA against known bugs. This method is not really used. - Not Maintained Anymore 

DEREG - NOT DOCUMENTED TO THE CUSTOMERS, THIS IS MOSTLY FOR INTERNAL USE ONLY. Supported only on version newer than 11.2.0.200965121547. This will facilitate the process of cleaning up CORA. There are situations then an asset is still registered in CORA but the asset does not exist in the product anymore. This method will get a list of asset_source_uuids that are found in CORA but there is no link to that asset from ca_owned_resource or ca_discovered_hardware tables. This is supported for now only on MS SQL in conjunction with scopes USD, DSM and ALL. The ALL scope will not do anything to assets that belong to NSM or SPM. - Available starting with test fix T5ET177. 

REGINV - NOT DOCUMENTED TO THE CUSTOMERS, THIS IS MOSTLY FOR INTERNAL USE ONLY. Supported only on version newer than 11.2.0.200965121547. This will facilitate the process of registering the assets that failed to register because of various reasons when a full coracleanup was ran. This method will go through ca_owned_resource or ca_discovered_hardware tables and get all the assets that have an asset_source_uuid set to null and try to register them. - Available starting with test fix T5ET177. 

NOTE: DEREG and REGINV methods were not designed to replace Reinit method but to speed up the process of cleaning up a BAD database. After successfully running the DEREG and REGINV methods it's highly recommended to run a coracleanup with the reinit method. 

 

In coracleanup r12 a new parameter was added which is not documented. It was added just to test available DB connections for CORA. The parameter is "/PROVIDER" with valid values: SQLOLEDB, SQLNCLI, SQLNCLI10. 

 

Usage sample: 

1) Run "coracleanup /server XXXX /scope ALL /method reinit /database XXXX /type XXXX /username XXXX /password XXXX /log full" - this will use SQLOLEDB. The connection string in coracleanup.log should read: 

"~ConnectionString~Provider=SQLOLEDB;Server=....." 

2) Run "coracleanup /server XXXX /scope ALL /method reinit /database XXXX /type XXXX /username XXXX /password XXXX /log full /provider sqloledb" - this will use SQLOLEDB.The connection string in coracleanup.log should read: 

"~ConnectionString~Provider=sqloledb;Server=....." 

3) Run "coracleanup /server XXXX /scope ALL /method reinit /database XXXX /type XXXX /username XXXX /password XXXX /log full /provider sqlncli" - this will use SQLNCLI.The connection string in coracleanup.log should read: 

"~ConnectionString~Provider=sqlncli;Server=....." 

4) Run "coracleanup /server XXXX /scope ALL /method reinit /database XXXX /type XXXX /username XXXX /password XXXX /log full /provider sqlncli10" - this will use SQLNCLI10.The connection string in coracleanup.log should read: 

"~ConnectionString~Provider=sqlncli10;Server=....." 

Additional Information:

Known SQL Server 2008 Error:
If the DMBS is SQL Server 2008 coralceanup will not be able to connect to the database. To bypass this problem please review the following document: TEC541954

Q1. Considering a reasonably powerful machine, how long should it take to go through 50K Assets? Does it improve performance by using the /scope switch?
A1. With the logging on CORA turned on about 100 assets per minute, without the logging turned on somewhere around 300 assets per minute, depending on the existing data it CORA could process data at even 600 assets per minute. The scope switch just limits the number of assets that will be processed, will not affect the overall time.

Q2. Is there any way we can run coracleanup without stopping all services?
A2. Not really. Because coracleanup deletes all the assets from CORA there might be problems when a product is trying to access the asset info during the cleanup process.

Q3. How does coracleanup identify a duplicate?
A3. Coracleanup does not delete the assets from the product's table but only from CORA and then it re-registers them by sending a request to CORA to register the assets using the info that it got from the product's tables. If there is a duplicate and CORA can't register the asset then an error will be logged and the asset will not be registered.

Q4. What does coracleanup do under the hood?
A4. In order to cleanup the CORA assets with the reinit method it deletes only the assets that belong to the product passed in the scope in the CORA tables (ca_asset, ca_asset_source, ca_logical_asset, ca_logical_asset_property) and sets asset_source_uuid to null on the needed product tables (ca_owned_resource, ca_discovered_hardware, tng_managedobject, pd_machine). Then it goes through the product tables to re-registers with CORA the assets that were found in the product table and updates the asset_source_uuid column with the current asset_source_uuid.

Q5. Is there a need to backup the database before running coracleanup?
A5. Not really. You might get errors during coracleanup but no information will be lost. We delete only the information that we ca restore. If there are any errors they might be errors generated by the product table. Example: Because of the changes in CORA now there should not be created a new asset but because of a bug there was creates a new one now, after fixing that bug, you might get an error telling you that the asset you are trying to register is a duplicate. In this case you will have to manually investigate the product table to confirm if this is a error on CORA side or a duplicate in the product table because the old version of CORA did not detect a duplicate.

Q6. Does coracleanup work on SD localized versions?
A6. We are not aware of any problems when running coracleanup on a localized version of SD. CORA in general is not language dependent.