Clarity

Expand all | Collapse all

Issues in dev environment after DB refresh

  • 1.  Issues in dev environment after DB refresh

    Posted Mar 20, 2015 06:10 AM

    Hi All,

     

    We are working in Clarity 12.1.We have refreshed the development environment with production DB.

     

    After the refresh we are not able to run the process reports and jobs, with the error "Contact System administrator".

     

    When we checked the app log and we are getting the error as

     

    java.sql.batchupdateException: ORA-00001: unique constraint (CLARITY.PRJ_BLB_SLICES_U2) violated



    We have tried the steps mentioned in the discussion but did not resolved our issue.

     

    NJS-0401: Execution of job failed.

     

    Help will be highly appreciated.

     

    Thanks in advance.

     

    Hari



  • 2.  Re: Issues in dev environment after DB refresh

    Posted Mar 20, 2015 07:22 AM

    Hi Hari,

     

    You can try with delete the slice table data and rerun the slices job.



  • 3.  Re: Issues in dev environment after DB refresh

    Posted Mar 20, 2015 10:16 AM

    Your problem is caused by taking a database backup when the Clarity services were still running.

     

    Whilst this might be necessary on a production environment for the purposes of disaster recovery planning, this is not an adequate method of migrating data between environments for other reasons.

     

    In those other reason situations, you must take a backup of the database during a maintenance window when the services are not running.

     

    At the very least, if you cannot find such a period, stop and wait for all jobs to finish, and use a method of database backup that is more likely to retain data consistency whilst the backup is running (e.g. do use 'datapump', do not use regular 'imp/exp' methods), and it will help mitigate the majority of these issues.

     

    It is possible to try and fix the consistency issues you're facing by 'bumping' the affected Oracle sequences being used to govern the new primary key values for records to a value that is higher than the current max(primarykeyfield) value (my 'rule of thumb' is to usually bump the amount by 1000 or 10000 above the current value to create clear headroom).

     

    The problem you will face is that as you fix one issue, you will run into the same thing again and again getting uncovered elsewhere.  And that is assuming it's only the sequences that are out of sync and nothing else.  So the 'fastest' option usually is also the 'best' one, and that is to retake the database backup and restore it again, using the guidelines above.



  • 4.  Re: Issues in dev environment after DB refresh

    Posted Mar 20, 2015 10:32 AM

    I misread the constraint you were having this issue with, so I would just like to clarify that sequences would most likely be an issue if the constraint that failed on this table was PRJ_BLB_SLICES_U1 but since yours is ...U2 it may not be enough to look for sequences to fix.  It could still easily be an issue with your backup/restore process for moving data from one environment to the other though, as I said, there are usually multiple sync problems that occur from this not just sequences.



  • 5.  Re: Issues in dev environment after DB refresh

    Posted Nov 08, 2016 04:57 AM

    Hi Nick, 

     

    May I ask if you can guide me how can we fix this issue? I know from your previous post the best in this situation would be to create completely new dump from PROD but instead of this I'd like to at least try to fix it.... 

    (this happened when moved DB dump from PROD to TEST, but didn't stop services at PROD (didn't know that....))

     

    Error message:

     

    ERROR 2016-11-07 15:25:33,725 [Dispatch pool-5-thread-11 : bg@dehensvh46.henkelgroup.net (tenant=clarity)] niku.blobcrack (clarity:admin:85760627__FB266B75-50D7-402F-90D5-2097F90A1107:Time Slicing) Exception during blobcrack process
    com.niku.union.persistence.PersistenceException:
    SQL error code: 1
    Error message: ORA-00001: unique constraint (NIKU.PRJ_BLB_SLICES_U2) violated

    Executed:
    INSERT INTO PRJ_BLB_SLICES ( SLICE_REQUEST_ID, PRJ_OBJECT_ID, SLICE_DATE, SLICE, CREATED_DATE )
    VALUES (?, ?, ?, ?, ?)
    Derived from statement:
    <?xml version="1.0" encoding="UTF-8"?>
    <statement location="blobcrackPMD.xml" id="blobcrack.insertSlice_stmt" batchable="true" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="none" xmlns="http://schemas.niku.com/2002/pmd">
    <sql dbVendor="all">
    <text>
    INSERT INTO PRJ_BLB_SLICES ( SLICE_REQUEST_ID, PRJ_OBJECT_ID, SLICE_DATE, SLICE, CREATED_DATE )
    VALUES (?, ?, ?, ?, ?)
    </text>
    <param name="requestID" type="int" direction="IN" expressionListDelimiter=","/>
    <param name="objectID" type="int" direction="IN" expressionListDelimiter=","/>
    <param name="sliceDate" type="date" direction="IN" expressionListDelimiter=","/>
    <param name="slice" type="double" direction="IN" expressionListDelimiter=","/>
    <param replace="timestamp" direction="IN" expressionListDelimiter=","/>
    </sql>
    </statement>

    Referenced by:
    <?xml version="1.0" encoding="UTF-8"?>
    <statementRef id="blobcrack.insertSlice_stmt" inputSource="map" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" xmlns="http://schemas.niku.com/2002/pmd"/>

    Using input:
    {slice=0.0, objectID=5714869, requestID=5001010, sliceDate=2017-05-06T00:00:00}

     

    at com.niku.union.persistence.PersistenceController.createException(PersistenceController.java:2048)
    at com.niku.union.persistence.PersistenceController.handleSQLException(PersistenceController.java:2152)
    at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2779)
    at com.niku.union.persistence.PersistenceController.processStatement(PersistenceController.java:868)
    at com.niku.union.persistence.PersistenceController.processStatements(PersistenceController.java:768)
    at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:576)
    at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:306)
    at com.niku.blobcrack.SliceInserter.finish(SliceInserter.java:87)
    at com.niku.blobcrack.BlobCrackDB.commit(BlobCrackDB.java:1131)
    at com.niku.blobcrack.BlobCrackDB.updateTimes(BlobCrackDB.java:401)
    at com.niku.blobcrack.BlobCrackDB.createSlices(BlobCrackDB.java:266)
    at com.niku.blobcrack.xql2.handlers.BlobCracker.processNewRequests(BlobCracker.java:134)
    at com.niku.blobcrack.xql2.handlers.BlobCracker.postProcess(BlobCracker.java:43)
    at com.niku.xql2.XQLVisitor.postProcess(XQLVisitor.java:1411)
    at com.niku.union.xml.dom.DOMWalker.postProcess(DOMWalker.java:210)
    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:94)
    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)
    at com.niku.union.xml.dom.DOMWalker.traverseIntern(DOMWalker.java:92)
    at com.niku.union.xml.dom.DOMWalker.traverse(DOMWalker.java:36)
    at com.niku.xql2.XQLProcessor.process(XQLProcessor.java:257)
    at com.niku.njs.xbl.XBLService.process(XBLService.java:127)
    at com.niku.njs.xbl.XBLService.process(XBLService.java:75)
    at com.niku.njs.listeners.XBLJob.scheduledEventFired(XBLJob.java:44)
    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:362)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (NIKU.PRJ_BLB_SLICES_U2) violated

    at com.ca.clarity.jdbc.oracle.ddn.b(Unknown Source)
    at com.ca.clarity.jdbc.oracle.ddn.s(Unknown Source)
    at com.ca.clarity.jdbc.oracle.ddn.r(Unknown Source)
    at com.ca.clarity.jdbc.oraclebase.ddes.v(Unknown Source)
    at com.ca.clarity.jdbc.oraclebase.dddm.executeBatch(Unknown Source)
    at com.niku.union.persistence.PersistenceController.processSql(PersistenceController.java:2701)
    ... 24 more



  • 6.  Re: Issues in dev environment after DB refresh

    Posted Nov 08, 2016 11:13 AM

    Hi Matej,


    {slice=0.0, objectID=5714869, requestID=5001010, sliceDate=2017-05-06T00:00:00}

     

    The items marked in bold are causing the conflict (on the basis a record in prj_blb_slices already exists with the same values).

     

    Possible causes I suspect may include:

     

    You somehow have a duplicate request, make sure this returns only 1 record:  select * from prj_blb_slicerequests where id = 5001010

     

    You have a duplicate source record.  Since Slice Request ID 5001010 is custom, I don't know what it points to.  Can you check?  Time entries, assignments, actuals, etc.  Once we know what that is, we can then: select * from <src table> where prid = 5714869

     

    We want to make sure that too only returns 1 record.  If the slice request is for time entries, then make <src table> prtimeentry and if it's estimates/actuals make it prassignment.  If it's allocation, make it prteam, and finally if it's availability, then make it prj_resources.  If your slice request is on anything else, we'll need to check further.

     

    If both of those only contain 1 record then possibly an issue with your prj_blb_slices table (or its indexes) are having a bad reference in them, as the records should be getting deleted from there before the slice job inserts replacements.

     

    You could try deleting this range of records as per the index definition explicitly (actually I'd probably do all records for the slice request id and object id, regardless of date), like this:  delete from prj_blb_slices where slice_request_id = 5001010 and prj_object_id = 5714869

     

    Make sure to COMMIT afterward.

     

    And if that doesn't work, then I'd suggest a full re-slice of that request, which you can effect by going to the time slice definition in the UI and making a change (e.g. change the number of periods), save it, then make another change to put it back again.  The Last Run Date for the slice request should then clear and a full reslice will take place next time.

     

    If after this, there are still errors, it may be required to do something about getting a new backup exported/imported, but check via a support ticket first if possible and reference these steps/actions that you already undertook.

     

    Hope that helps you out.



  • 7.  Re: Issues in dev environment after DB refresh

    Posted Nov 08, 2016 02:57 PM

    More drastically you can execute following actions for rebuilding all your slices

     

    1. Stop bg service
    2.  Truncate the table (especially if you have a lot of records to remove

    TRUNCATE table prj_blb_slices;

    1.       check number of records - must be 0

    select count(*) from prj_blb_slices;

    1.      Mark slices request for calculation again

    Update prj_blb_slicerequests

    set request_completed_date = null

    where table_name ='PRJ_BLB_SLICES';

    commit;

    1.      Rebuild indexes on prj_blb_slices

    ALTER INDEX prj_blb_slices_N2 REBUILD;

    ALTER INDEX prj_blb_slices_U2 REBUILD;

     

    1.      Start bg service


  • 8.  Re: Issues in dev environment after DB refresh

    Posted Nov 09, 2016 02:04 AM

    Hi Nick, Jeanliv

     

    Thanks for your great "manuals"  

     

    I've checked your tips and those are my results:

    select * from prj_blb_slicerequests where id = 5001010 : Returned 1 record

    select * from prtimeentry where prid = 5714869: Returned 1 record

     

    That's change of periods you mentioned I've already done before, but it didn't help. I've changed Timeentries and Daily availability periods which were the hugest (cca 1400 periods) from "From date" 1/1/2014 to 1/6/2015. Maybe a later from date would help e.g. some 2016....

     

    However as you advised to delete those double/harmed records, wouldn't it be easiest to follow Jeanliv's procedure? Simply rebuild all indexes and prj_blb_slices table? I can make a copy before this operation. 

     

    EDIT: When I've created copy of prj_blb_slices table I've realized there are much more prj_blb_slices_something in our DB. It is not needed to truncate also all these? I guess they are there because we have a lot of time slices set up in the UI, probably 30-40:

     

    Thanks to both

     

    Matej



  • 9.  Re: Issues in dev environment after DB refresh

    Posted Nov 09, 2016 09:34 AM

    However as you advised to delete those double/harmed records, wouldn't it be easiest to follow Jeanliv's procedure? Simply rebuild all indexes and prj_blb_slices table? I can make a copy before this operation.

     

    Not necessarily, hence I wouldn't publish that generally.  There are a good number of customers where the effort or impact of a full time slice rebuild could make their reports or other incompatible jobs unavailable for a significant period of time (extreme cases, up to 48 hours).

     

    So it's not necessarily the right or wrong approach to take as a rule, but I stopped short of saying such things because first I would say work it out with support via a ticket to see if that is applicable on a case by case basis.  That might be OK for you or even them with a Dev environment as you're stating now, for example, but future readership may take up this advice in other situations that would be detrimental for them.



  • 10.  Re: Issues in dev environment after DB refresh

    Posted Nov 09, 2016 10:54 AM

    The PRJ_BLB_SLICES_* are linked to the internal timeslicing of CA PPM. don't touch them



  • 11.  Re: Issues in dev environment after DB refresh

    Posted Nov 10, 2016 11:36 AM

    Hi Jeanliv, Nick, 

     

    So, I've done all steps according to Jeanliv's procedure. However after Time Slicing job finished I've checked "Last Run" columns in Time Slicing UI and there were no updated dates. I've found out the reason - error at bg logs:

     

    We are still receiving this error - also after 5x of extension that tablespace. So the question is if this Time Slicing is runnable or need more extra DB resources for its execution...

     

    ERROR 2016-11-10 15:06:22,371niku.blobcrack (clarity:admin:85796629__1EBACC30-4C5C-4BBC-A345-35AB6D523A08:Time Slicing 10.11 - TEST2) Exception during blobcrack process
    com.niku.union.persistence.PersistenceException:
    SQL error code: 1536
    Error message: ORA-01536: space quota exceeded for tablespace 'USERS_LARGE'

    Thanks

     

    Matej