Rest API Database Lock

[sprintell]

Reviewing the strategy to overcome Database Lock issue in the Rest API

[sprintell]

• Schedule a meeting with Mark Head in DBA
• Compare different options of what could be done to bring and end to the problem
• Implement the solution

[sprintell]

We had a meeting with Mark Head concerning theis problem. The root of this problem is that whenever SPOTPRO database refresh is executed in Delphix for any of the other Oracle database environments including the SPOTPUB Database (SPOTDV1, SPOTDV2, SPOTDV3, SPOTTST, SPOTPUB, SPOTPUBFALL, SPOTCTV, SPOTPREL), the SPOTPRO "gwas user" password overwrites the password of the given database.

The agreed solution is to create other "non gwas" users and password for each Oracle database in SPOTPRO. if this "non gwas" user is used to connect from the application to the Oracle Database, the password will still be working after SPOTPRO refresh, because this new users and passwords will be thesame after DB Refresh. With this, the lock will not occur again.

Ticket INC0060321 is used to work on this fix.

[sprintell]

Mark is now on holiday, when this was tested on the last data release, the changes Mark Head made did not work.

Waiting for Mark to fix the changes he made ...

[sprintell]

Mark has recreated all the users in upper case in SPOTPRO, sorry my mistake on creating them lower case.
Passwords remain the same

[sprintell]

• We have created "non gwas" users and password for each Oracle database.
• This was created in SPOTPRO and copied over when all the Databases are Refreshed including SPOTPUB
• This "non gwas" user will be used connect from the application to the Rest API Db
• This new users and passwords will be thesame after DB Refresh.
• With this, the lock will not occur again.
• The configurations will be updated in conflunce document:
  Confluence -> What Runs Where -> GWAS Project - Servers, Databases and Infrastructure Administration

I've tested these for the 7 databases, they are all now working perfectly.

While testing, I discovered that some Oracke View Tables that are accessible when we connect to the Oracle via the gwas user are not accessible when we connect with this new user.

We might just need to double check why this is the case.

[sprintell]

@sprintell test Mark head's final solution to be sure new user accounts can access the Views in Oracle

[sajo-ebi]

there were multiple issue due to which new DB pointing was not implemented

• the select query was not working on the tables for the new user , Mark fixed it
• the schema migration table data was not copied which was causing the migration scripts to get executed , need to replicate this in lower environment befpre trying in production

[sajo-ebi]

Still getting issues with running rest API locally with the new DB user , the sequence are not accessible by the API , Mark from DBA had applied some fix , still it didn't work, will continue to coordinate with DBA to fix