The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Changing database schema name

Highlighted
Cindy Ramberg
Regular Collector

Changing database schema name

Hi,

 

We are moving to a new database server and it is required of us that we change the database schema names.  For example:  We currently have names of 'ITGADM' and 'ITGRML'.  But, we need to change to 'ITGADMDEV' and 'ITGRMLDEV', etc for EACH PPM Instance!  (So, we'll need an 'ITGADMTST', 'ITGADMPRD', etc.)  We have updated the server.conf and the Environment in workbench.  But, there are a few HP-delivered database objects that seem to be using the old database schema name.  (2 triggers:  KCRT_REQ_RHT_AUDIT_30106_1 and KCRT_REQ_RHT_AUDIT_30196_1).  One of the triggers prevented us from submitting any requests.  If we manually update the trigger scripts, they work.  However, we don't feel comfortable updating HP-delivered.  Can you tell us where else the databgase schema names could be stored?

 

Thanks for your help!

cindy

5 REPLIES
Utkarsh_Mishra
Honored Contributor

Re: Changing database schema name

Its better to change the DB settings using kConfig.sh (this including Oracle Instance details)

 

The best approvach for such migration is to

 

  1. Create a new PPM DB user using CreateKintanaUser script
  2. Give the necessary grants
  3. The migrate the Database dump in this user

Seeing your post; it seem the tigger in ITGADMDEV schema might be create like this

 

ITGADM.KCRT_REQ_RHT_AUDIT_30106_1... so now you need to correct it like ITGADMDEV.KCRT_REQ_RHT_AUDIT_30106_1 

 

Also recompile all the invalid objects in the database and check whether any objects are still invalid or failing, is so then correct them.

 

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
AlexSavencu
Honored Contributor

Re: Changing database schema name

Hi,

 

@Cindy: you can safely modify the trigger code, since they are not actually delivered by HP. These are triggers created by the workbench when you select for specific fields to have audit trail.

 

@Utkarsh: the problem is in the code of the triggers. One specific user (ITGADMDEV) cannot hold another user's objects (ITGADM).

 

cheers

alex


--remember to kudos people who helped solve your problem
Cindy Ramberg
Regular Collector

Re: Changing database schema name

Thanks Utkarsh,
Our DBA ran these 2 scripts:  CreateKintanaUser.sql and CreatRMLUser.sql (which give the necessary db grants).  Then re-imported the 'database dump'.  However, we still have database objects that are invalid. 

 

Thanks Alex,
We also have an invalid function named 'NEW_CONTACT_ID' that is pointing to the old schema name.  Is it okay to also modify this object type?  We are just concerned that there are still 'behind-the-scenes' or 'internal' pointers to the OLD database schema name.

 

Thanks again to both of you!
cindy

AlexSavencu
Honored Contributor

Re: Changing database schema name

Hi,

This function looks like it is a custom function, so I think it's safe to change it accordingly.

You can post here the list of invalid objects so we can provide better assistance.

Cheers
Alex

--remember to kudos people who helped solve your problem
sonaliashah
Super Collector

Re: Changing database schema name

Hello Cindy,

Both the triggers - KCRT_REQ_RHT_AUDIT_30106_1 and KCRT_REQ_RHT_AUDIT_30196_1 are automatically created whenever the request header is created/updated and are related to different request header types. The following steps should help:

1. Rename or drop the triggers in question - this is absolutely safe for the rht_audit triggers.

2. Open the workbench and open the request header types that refer to both these triggers

3. Recompile KCRT_AUDIT package again.

4. Make a minor change in each request header type (adding a space or character to the description field and then again undoing it to activate the save button). Save the request header type. As soon as you save the request header type, these two triggers would be recreated once again, using the correct schema.

 

Alternatively, if you update the trigger script and recompile it using the new DB names, it would work too, but the above method is the recommended one, as per HP. We had a similar situation and was recommended by HP.

 

Hope this helps solve your issue.

Sonali S.


//Add this to "OnDomLoad" event