BO4.1 CMS doesn't up due to corrupt object in CMS DB

[Ganesha]

Hello Everyone

we are using BO4.1 SP5 Patch 3 platform in windows,  we have faced CMS DB issue two times within one month.

The problem is CMS doesn't up and it shows error message in Event Logs that due to corrupted object exist in CMS DB won't possible to up CMS server.

We don't know which object exactly corrupted and why it is corrupted. Until we manually delete the corrupted object from CMS DB we will not be possible restart CMS server

we will take the backup of corrupted object and deleted from CMS DB and then it restarted.

Now we want to find which object and its type, unfortunately I dont know the exact query to retrieve these information from CMS DB, anybody faced this problem before.

we are using oracle 11 CMS database.

Regards,

Gansha

[CdnConnection]

Ganesha,

      Did you check the database size to see if you have enough free space and also check TEMP space.

Ajay

[Ganesha]

Hi Ajay,

yes, we have enough space.

Regards,

Ganesh

[CdnConnection]

Ganesha,

   If have allocated enough extra space for your CMS and FRS then your best option is to run the REPOSCAN.exe tool.  This tool will help recover any lost indexes and resolve corruption between CMS & FRS.

• First run the tool in SCAN mode to see all issues
• Then run in REPAIR mode to resolve issues.

Ajay

[Former Member]

Dear Gansha,

Did you try to use the Repository Diagnostic Tool against repository database?

Location

<INSTALLDIR>\SAP BusinessObjects Enterprise XI 4.0\win64_x64\reposcan.exe

Example Command

reposcan -dbdriver oracledatabasesubsystem -connect "UID=BO_REPOSITORY_DB_USER;PWD=PASSWORD;DSN=TNS_ENTRY;PORT=NETWORK_PORT" -inputfrsdir %DefaultInputFRSDir% -outputfrsdir %DefaultOutputFRSDir% -dbkey "CLUSTER_KEY"

Note: You need to modify the values in Italic. If you want to automatically repair any inconsistencies add -repair switch on the end of the aforementioned command. I suggest you to run the command without -repair the first time in order to have a look at the results.

More Information

Document: Business Intelligence Platform Administrator Guide

Chapter 28 - Repository Diagnostic Tool

Regards,

ilias

[Ganesha]

Hi ilias,

thanks for your input.

we regularly do Reposcan, this object corruption happen suddenly and we will not be perform any action until we delete that object, so we deleted this corrupted object blindly after taken backup.

I just want see the object name and it type from CMS backup table, as object name is encrypted in CMS_INFOOBJECTS table.  Anybody Know the script/SQL query to decrypt this object name and object type.

Regards,

Ganesh

[Former Member]

Hi Ganesh,

If you know the encrypted name of the corrupted object, you can get its ID by running the following query on CMS DB:

SELECT OBJECTID

FROM CMS_INFOOBJECTS7

WHERE OBJNAME='OBJECT_NAME'

Note: You may get more than one results here.

Afterwards, you can use 'Query Builder'.

Link

http://<YOUR_BO_SERVER>:<TOMCAT_PORT>/AdminTools

Example Query

select SI_NAME, SI_KIND

from  CI_INFOOBJECTS

where SI_ID=OBJECTID_FROM_PREVIOUS_QUERY

Thanks,

ilias

[Ganesha]

Hi ilias,

we know the OBJECT ID and we want to know the object name  which is in encrypted form and object type, as we have deleted this object from CMS_INFOOBJECTS7 table and just copied into another dummy table, so we will not be possible to see the details from CI_INFOOBJECTS via Query builder.

Regards,

Ganesh

[Former Member]

Dear Ganesh,

Unfortunately we cannot extract any other useful information from the dummy table, that will lead us to the object name & type.

Thanks,

ilias

[Ganesha]

dummy table is exactly the copy of CMS_INFOOBJECTS7 which contains only the corrupted record, what I am looking here is if we would have script to decrypt the object name then we will able to see the object name and also if we know type name of the corresponding type id, that is enough

Regards,

Ganesh

[Former Member]

Try this?

select

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

replace(

translate(replace(objname,’BE’,’.’),’)+-/13579;=?ACEGIKMOQSUWY]!”{JP’,’ABCDEFGHIJKLMNOPQRSTUVWXYZ’),

‘`|>’,’1′),

‘`|@’,’2′),

‘`|B’,’3′),

‘`|D’,’4′),

‘`|F’,’5′),

‘`|H’,’6′),

‘`|J’,’7′),

‘`|L’,’8′),

‘`|N’,’9′),

‘M|N’,’_’),

‘M|Z’,’-’),

‘@’,’ ‘),

‘`|<','0'),

'B~P','/'),

'M}Z',':'),

'B|C','('),

'B|D',')'),

'B~K','@') objnm,objname, objectid from cms_infoobjects7 where objectid = 12345;

[Ganesha]

Hi Arby Teh,

i tried this query, it doesnt helps, result is displyed like this, object name is still not readable format.

if we look at the object type 41, seems to be this corupted object is not  'Folder','WebI', 'DeskI','Universe','Connection', 'Word', 'PDF', 'Text', 'Excel', 'PPT', 'Group', 'Service','License'

OBJNM       OBJNAME           OBJECTID    OBJTYPE

OCD1BUJD    E-/!`|>"+Q!;D     31307489    41

BFA3RTJD    +3)!`|B"KO!;D     29323364    41

 

So it might be session element or something system related, But still we doesn't have idea why it is corrupted , if it corrupted then why it doesn't delete automatically while restarting the CMS.      

[Former Member]

Hello Ganesha,

Yes, ObjectID 41 should be translated into "Logon Sessions", by filtering it

     'where objectid = 41'

Did you try to remove the entry after you backup them?

[Ganesha]

Yes, we deleted corrupted object and and taken backup before.

This is happened three of time in 4 months, we have to delete this corrupted object to make the system available.

We could not able get clear root cause why session is corrupted, Basically info-object property column CLOB data type which inserted with NULL, that cause the problem, But question is why it inserted with NULL, at the time it inserted NULL doesn't cause any issue, But when we restart the CMS that time is system wont be up, NULL value insertion may be due to network issue...but  no clue.

[Former Member]

Hi Ganesha,

Try below SQL statement:

select distinct info.ObjectID, info.ParentID, info.OwnerID,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(ObjName

                                               AS varchar(2000)), 'S', 'v'), 'M', 's'), 'A', 'm'), ')', 'a'), '+', 'b'), 'C', 'n'), '-', 'c'), '/', 'd'), 'O', 't'), 'E', 'o'), '1', 'e'), '3', 'f'), 'G', 'p'), '5', 'g'), '7', 'h'),

                                              'W', 'x'), 'U', 'w'), 'Q', 'u'), 'I', 'q'), '9', 'i'), ':', 'i'), ';', 'j'), 'K', 'r'), '=', 'k'), '?', 'l'), '[', 'y'), ']', 'z'), '!@', ' '), 'B~S', '&'), '!BO', '.'), 'B|C"', '('), '!B|D',

                                              ')'), 'M|Z', '-'), 'M}L', ','), 'M|N', '_'), 'M}Z', ':'), '!B{B', ''''), '`|<', '0'), '`|>', '1'), '`|@', '2'), '`|B', '3'), '`|D', '4'), '`|F', '5'), '`|H', '6'), '`|J', '7'), '`|L',

                                              '8'), '`|N', '9'), '{', ''), '!', ''), '"', ''), '@', '') AS ObjectName,

                                              CASE WHEN info.TypeID = 262 THEN 'Webi Report' WHEN info.TypeID = 314 THEN 'Deski Report' WHEN info.TypeID = 283 THEN 'PDF' WHEN info.TypeID = 267

                                               THEN 'Text' WHEN info.TypeID = 323 THEN 'Excel' WHEN info.TypeID = 266 THEN 'Universe' WHEN info.TypeID = 278 THEN 'Publication' WHEN info.TypeID

                                               = 299 THEN 'Connection' WHEN info.TypeID = 19 THEN 'User type 19' WHEN info.TypeID = 18 THEN 'User type 18' WHEN info.TypeID = 47 THEN 'User type 47'

                                               WHEN info.TypeID = 48 THEN 'User type 48' WHEN info.TypeID = 8 THEN 'Shortcut' WHEN info.TypeID = 1 THEN 'Folder' WHEN info.TypeID = 20 THEN 'Groups'

                                               WHEN info.TypeID = 13 THEN 'Server' WHEN info.TypeID = 16 THEN 'BO Server' WHEN info.TypeID = 21 THEN 'Event' WHEN info.TypeID = 24 THEN 'License Key' WHEN info.TypeID = 57 THEN 'Access Level'

                                               ELSE 'Other' END AS Type, info.TypeID

                                               from dbo.CMS_InfoObjects6 Info with (nolock)

[Former Member]

How do you identify that the records in the CMS DB (CMS_InfoObjects7 table) as corrupted record?

By the column "Properties" having null value?

[Ganesha]

Hello

In CMS log we could able to get the object which got corrupted and that stops CMS up.