This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
  << Previous Topic | Next Topic >>Return to Index  

EPM 11.1.2.1 Relational Database server change

April 23 2012 at 10:22 PM
No score for this post
Anonymous 
from IP address 70.138.75.150

Our IT dept is planning to go through a datacenter migration that requires changing the (SQL Server 2008) relational database name that our Oracle EPM 11.1.2.1. repositories are configured to.

Does anyone have a recommended structure of steps that are needed to cover all configuration changes needed in the EPM environment to work on the new server name? I plan on running the EPM System Configurator, however I understand this may not cover all areas that need to be updated with the new repository server name?


Currently we utilize the following Oracle EPM products across three servers in which I will be running the EPM System Configurator in this order:
-SERVER03: Shared Services (HSS), Hyperion Reports
-SERVER01: Essbase, Provider Services (APS), Administration Services (EAS), Essbase Studio
-SERVER02: Planning, Calc Manager, Oracle Data Integrator (ODI), EPMA


I've found the following Oracle Knowledge Base recommended steps for Shared Services.
=================================================================
How To Change Shared Services Database Repository in EPM 11 [ID 976279.1]
1. Navigate to EPM_ORACLE_INSTANCE/config/foundation/11.1.2.0.
2. Backup/copy the reg.properties file.
3. Delete the reg.properties file.
4. Restart EPM System Configurator.
5. Reconfigure the Shared Services database by selecting "Connect to a previously configured database."
=================================================================

Any helpful recommendations are greatly appreciated, thanks!


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Anonymous

64.213.167.3

Not pretty but EPM 11.1.2.1 reconfigured to new relational db!

No score for this post
April 25 2012, 11:44 AM 

Ok, after a gruesome 2 days of working with Oracle Support and several logged SR tickets later (seperate for each EPM product), I was able to get my EPM 11.1.2.1 system up and running on the new SQL Server Database.
Turns out it wasn't as simple as just re-running the EPM System Configurator as there are many more steps post configuration.

I don't mind sharing these steps if someone may find them useful. For the most part, Oracle Support has them broken out by their own respective modules but spread out across their knowledge base site it may get a bit difficult to find them all. So I put together a consolidated breakout (at least for my system) in order by each EPM module (Foundation/HSS, Planning/Workspace, EAS, Essbase, Financial Reports, etc.)

Credit goes to Oracle Support for their excellent service and effort helping walk me through working out all the issues!

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Jess

130.216.168.157

How to change database name EPM 11.1.2.0

No score for this post
May 7 2012, 9:08 PM 

I need to do the same thing now. I found the same KB article but it asked me to re-create epmsystem1. I'm keen to know what's your post action after performing the task mentioned in KB article.

Please could you share how you solve this issue?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Kurt Reece-Peeplez

68.98.54.24

would like info on changing the database

No score for this post
May 15 2012, 4:21 PM 

Could you send your findings to me at kurtrp@yahoo.com. We have an Oracle database upgrade planned for this weekend and we may need to change the host and port numbers. Thanks

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Anonymous

216.136.3.126

Re: Not pretty but EPM 11.1.2.1 reconfigured to new relational db!

No score for this post
May 29 2012, 12:03 PM 

Is simple documentation for the necessary steps to change relational db server online anywhere?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Franc

66.229.136.87

Pls email steps to reconfigure DB

No score for this post
September 15 2012, 4:01 PM 

Please email the document to reconfigure database.

Thanks much

Franc

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
TN

64.213.167.3

Re: EPM 11.1.2.1 Relational Database server change

No score for this post
May 30 2012, 4:34 PM 

***DISCLAIMER: This steps I am providing is solely for informational purposes only. I accept absolutely NO responsibility or liability for any damage caused by this information, so use at your own discretion!
If you are having ANY doubts of your ability to perform this, I highly recommend that you bring in an EPM expert to help you.***

A few things to point out:
- The steps below is just a straight copy/paste of my own set of instructions I put together for my own environment minus the screenshots. These are based on the experience I had with my own EPM system, so not everyone's is configured the same way.
- I left out the ODI 11.1.1.3 since it contains it's own extensive set of reconfiguration changes. I can post that too if anyone needs it.
- Currently my EPM 11.1.2.1 system is deployed based on the Oracle EPM Standard Deployment method. Our servers are running Windows 2008 EE R2, and the relational database is SQL Server 2008.

I have the following Oracle EPM products installed across four servers, therefore my steps will be covering each server seperately in their EPM system order below:
-EPMSYSTEM1: Shared Services (HSS), Hyperion Reporting
-EPMSYSTEM2: Essbase, Provider Services (APS), Administration Services (EAS), Essbase Studio
-EPMSYSTEM3: Planning, Calc Manager, Oracle Data Integrator (ODI)
-EPMSYSTEM4: Strategic Finance (HSF)


**You should make sure the following prerequisites are met**
-BACK UP your relational databases BEFORE attempting to make the changes just in case you need to restore them.
-You are only updating your relational (SQL) database connection settings, so when running the EPM Configurator DO NOT select any options to create a new database.
-Use only the original service account userid that was used to install and configure the EPM software.
-Have contact info to resources (SQL admin, server admin, Oracle support) readily available in the event you run into issues.



==================================================================================================================================
Updating Oracle EPM 11.1.2.1 Relation (SQL Server 2008) databases
==================================================================================================================================
**NOTE: Before beginning, STOP all EPM services. I have a vb script that automates this process so I just kick it off which usually takes about 10 minutes to run. However, you can manually STOP the services on each server.


==================================================================================================================================
I. How to reconfigure your Shared Services or EPM products database using the configuration tool
==================================================================================================================================
You cannot change a configured Shared Services database directly in EPM System Configurator without removing the file that contains the previously configured database information.
You will need to perform this on all three EPM servers (EPMSYSTEM4 HSF server does not apply).

***IMPORTANT NOTE: Since you are running the EPM Configurator to update the relational repositories this create a duplicate HAVA system id, which will likely cause issues with Workspace and Hyperion Reports being unable to
establish communication with each other. Therefore the HAVA id will be updated in a section VII below.***


1. Navigate to EPM_ORACLE_INSTANCE/config/foundation/11.1.2.0.

Example:
EPMSYSTEM1\D$\Oracle\Middleware\user_projects\EPMSYSTEM1\config\foundation\11.1.2.0
EPMSYSTEM2\D$\Oracle\Middleware\user_projects\EPMSYSTEM2\config\foundation\11.1.2.0
EPMSYSTEM3\D$\Oracle\Middleware\user_projects\EPMSYSTEM3\config\foundation\11.1.2.0

2. Backup/copy the reg.properties file.
3. Delete the reg.properties file.
4. Restart EPM System Configurator.
5. Reconfigure the Shared Services database by selecting "Connect to a previously configured database."


==================================================================================================================================
II. Reconfigure EPMSYSTEM1 - Foundation Services, FR
==================================================================================================================================
1. Login to the EPMSYSTEM1 server. Launch EPM System Configurator
2. Select "Create a new EPM Oracle Instance", it defaults to the current EPM directory and name 'EPMSYSTEM1'. Click Next
3. Select "Connect to a previously configured Shared Services database", input the new SQL server info. Click Next

Example:
Type: Connect to a previously configured Shared Services database
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <HSS db>
Username: hypadmin
Password:

4. Select only the checkboxes in the Configuration screen to Configure and Deploy:
- "Reporting and Analysis"
- "Configure Database"
- "Deploy to Application Server"
5. Leave Configure Common Settings default settings, click Next
6. For Reporting and Analysis, input the new SQL server info

Example:
Type: Connect to a previously configured database (*select old SQL WS db to reconfigure)
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <WS db>
Username: hypadmin
Password:

7. Click Next to continue, when prompted, select option to "Reuse the existing database"
8. Accept the default framework settings, click Next
9. Unselect "Wish to receive security updates…" (optional)
10. Confirm products, and click Next to begin installation
11. Process should take about 10 minutes to complete. Click Finish to close and proceed to the EPMSYSTEM2 server…


==================================================================================================================================
III. Reconfigure EPMSYSTEM2 - Essbase, EAS, APS
==================================================================================================================================
12. Login to the EPMSYSTEM2 server. Launch EPM System Configurator.
13. Select to "Modify Existing EPM Oracle instance", click Next
14. Enter info to connect to HSS, click Next

Example:
Type: Connect to a previously configured Shared Services Database
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <HSS db>
Username: hypadmin
Password:

15. Select only the checkboxes in the Configuration to Configure and Deploy, then click Next
- Essbase Studio
- "Configure Database"
- Essbase Administration Services
- "Deploy to Application Server"
- "Configure Database"

16. Enter updated info for Essbase Studio, then click Next

Example:
Type: Connect to a previously configured Database (*select old SQL STUDIO db to reconfigure)
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <STUD db>
Username: hypadmin
Password:

17. Go down the tree and select Essbase Administration Services (EAS) configuration and enter the following For EAS

Type: Connect to a previously configured Database (*select old SQL EAS db to reconfigure)
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <EAS db>
Username: hypadmin
Password:

18. Click Next when complete and select option "Reuse the existing database when prompted"
19. On the Deployment screen, leave default at "Deploy Web application to an existing domain". Verify the information is correct, click Next
20. Click the checkbox to select the ‘eas’ EAR/WAR file. Click Next
21. Keep default Studio directories and click Next
22. Review the tasks and click Next to begin reconfiguration
23. When completed, click Next to close and continue to the EPMSYSTEM3 server…


==================================================================================================================================
IV. EPMSYSTEM3 - Planning
==================================================================================================================================
24. Login to the EPMSYSTEM3 server. Launch EPM System Configurator
25. Select Create new EPM Oracle Instance
26. For HSS, select modify existing connection, enter info below and click Next

Example:
Type: Connect to a previously configured Shared Services Database
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <HSS db>
Username: hypadmin
Password:

27. Select only the checkboxes in the Configuration to Configure and Deploy, then click Next
- Calculation Manager
- Deploy to Application Server
- Configure Database
- Planning
- Deploy to Application Server
- Configure Database

28. Leave default Common settings and click Next
29. For Calc Manager, update info and click Next

Example:
Type: Connect to a previously configured Shared Services Database (*select old SQL CALCMGR db to reconfigure)
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <CALCMGR db>
Username: hypadmin
Password:

30. For Planning, toggle the dropdown then update the Planning info, click Next

Example:
Type: Connect to a previously configured Shared Services Database (*select old SQL PLN db to reconfigure)
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <PLN db>
Username: hypadmin
Password:

31. Leave the default and click Next
32. Review the tasks and click Next to continue the reconfiguration


**NOTE: If you do not have an HSF server, then skip to the next step to proceed with updating the Planning Data Source connections **


==================================================================================================================================
V. EPMSYSTEM4 - HSF
==================================================================================================================================
33. Login to the EPMSYSTEM4 server. Launch EPM System Configurator
34. Select Modify an existing EPM Oracle instance
35. For HSS, select modify existing connection, enter info below and click Next

Example:
Type: Connect to a previously configured Shared Services Database
Database Type: Oracle’s MS SQL Server Driver (Type 4)
Database Server: <NewSQLserver>
Port:1433
Database Name: <HSS db>
Username: hypadmin
Password:

36. Since you only need to configure the HSS connection, you can Uncheck all. Click Next to continue.
37. Check that their are no tasks listed, then Click Next to continue
38. Upon completion, click Finish to complete.


==================================================================================================================================
VI. Update Planning Data Sources
==================================================================================================================================
1. START all EPM services (or you can just start the following Foundation, Essbase, Planning services)
2. Launch IE browser and access the Planning Workspace page
3. Go to menu Navigate > Administer > Classic App Administration > Planning Administration
4. Select Upgrade Wizard
5. Update the Database connection with the new SQL server name <NewSQLserver>, enter a Password and click to Validate the Db Connection for both the Application and Essbase
6. Click Finish to save the changes

**NOTE: Planning Security filters do not get refreshed after changing the SQL server relational database name, so you will have to Refresh the Planning security after restarting the services

7. Return to the EPM servers and STOP all hyperion services, then START the Hyperion services again

8. Launch IE browser and access the Planning Workspace page again
9. Launch a Planning application (i.e. FORECAST application) and then go to Administration -> refresh only the User Security Filters
10. Return to the EPM servers and STOP all of the Hyperion services
11. Proceed to the next step to update the HAVA ID…


==================================================================================================================================
VII. Update EPM System HAVA ID
==================================================================================================================================
When you run the EPM configurator, it creates a duplicate HAVA application id therefore causes a communication issue with Workspace and HR. You must follow the steps below to update the HSS registry with the new HAVA
application id. **Unless you have access to SQL Server Manangement Studio, you may need to contact a SQL admin to run the following SQL query below.


1. On the EPMSYSTEM1 server, STOP all Hyperion services (Foundation, Reporting and Analysis services).
2. Now have a SQL admin execute the following query against the Shared Services Registry SQL database (i.e. <HSS db> to obtain the HAVA id.

select application_id from css_provisioning_info where lower(application_id) like 'hava%';

(example HAVA application_id output: HAVA:0000013047396ceb-0000-50c5-ab4a4b68)

**With MS SQL Server Management Studio access:
a. Launch MS SQL Server Management Studio
b. Login to the new SQL server with userid: hypadmin
c. Find SQL db <HSS db> right-click and select option ‘New Query Editor Window’
d. Update the sql query above and execute to obtain the APPLICATION_ID

3. Return to the EPMSYSTEM1 Shared Services server to edit the Shared Services Registry
4. Open a command prompt and go to the EPM_ORACLE_INSTANCE\user_projects\EPMSYSTEM1\bin (i.e. D:\Oracle\Middleware\user_projects\EPMSYSTEM1\bin)
5. Run the following command:

CMD> epmsys_registry updateproperty RA_FRAMEWORK/@applicationId HAVA:<insert new HAVA ID>

Example:
CMD> epmsys_registry updateproperty RA_FRAMEWORK/@applicationId HAVA:0000013047396ceb-0000-50c5-ab4a4b68

6. Restart ALL Hyperion services
7. Go to EPM_ORACLE_INSTANCE\user_projects\EPMSYSTEM1\bin (i.e. D:\Oracle\Middleware\user_projects\EPMSYSTEM1\bin\) and double-click the "epmsys_registry.bat"
8. Confirm the new HAVA ID is applied

Example:
===============================================
RA_FRAMEWORK (RA_FRAMEWORK)
Properties:
Property Name Property Value
version 11.1.2.0
instance_home D:\Oracle\Middleware\user_projects\EPMSYSTEM1
applicationId HAVA:0000013047396ceb-0000-50c5-ab4a4b68
===============================================


==================================================================================================================================
VIII. Validate SQL Server changes
==================================================================================================================================
A. Restart all Hyperion services
B. Run EPM Registry Report
1. Remote to EPMSYSTEM1 directory ‘D:\Oracle\Middleware\user_projects\EPMSYSTEM1\bin’ and double-click to execute the "epmsys_registry.bat" to run the EPM system report
2. Search the page for the old SQL Server (i.e. SQLSERVER08) and verify it IS NOT on the report
3. Search the page for the new SQL Server (i.e. SQLSERVERNEW08) and verify it IS on the report

C. Validate Workspace login with access to Applications, Webforms, Explore, Reports
D. Validate EAS connection
E. Validate FR Studio can connect to FR
F. Validate Essbase Studio connection
G. Validate HSF connection


Congratulations, you are now DONE! I hope this helps, good luck...

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Anonymous

200.230.226.125

Error EPM System Configurator

No score for this post
June 12 2012, 10:16 AM 

Hi,
I want to thank you for sharing your experience on the subject ¡¨EPM 11.1.2.1 Relational Database server change¡¨.

A few days ago I ran the Configurator EPMSystem to change the Database server. I wrong to configure 02 products together:

„» Foundation: Owner ¡§hyp_pma¡¨
„» Profitability: Owner ¡§hyp_pcm¡¨

The error is generated that have become a single Owner, ie:
By changing the Foundation, Profitability hyp_pma copies the Owner.
By changing the Profitability, the Foundation copies the Owner hyp_pcm.


Is there any way to fix this error?
A few things to point out:
EPM System 11.1.2.1.00 (Oracle EPM Standard Deployment method). Server Windows 2008 (64bit)

Relational database is Oracle Server Enterprise Edition, version 11.2.0.2, platform Linux

Epmsystem1:
Foundation
Calculation Manager
Reporting and Analysis
Essbase Studio e Essbase Administration Services
Planning
Profitability and Cost Management
FDM ERP Integration


Thanks

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
John

115.111.229.130

EPMA database migration to new server

No score for this post
November 30 2012, 1:53 AM 

Hi TN,

Do you know how to handle this Sever changes, if we have EPMA in this scenario? Are there any addtional steps, if we are using EPMA to update planning application?

Thanks,
John

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Lou

144.42.254.14

ODI upgrade

No score for this post
June 6 2013, 4:00 PM 

Hello,

I am going through a very similar type of upgrade, going from EPM 11.1.1.3 to 11.1.2.1.
Also using SQL Server 2008 for my repository, I have two windows 2008 R2 64-bit Servers, one for Essbase and ODI and the other for Planning, shared Services, R&A, EPMA, etc.

We are also moving to windows 7 client side PC's using MS office 2010 and IE8.

I am currently using ODI 10.1.3.5 and I was told that I should be moving to ODI 11gR1 version 11.1.1.6.0 and then to patch up to version 11.1.1.6.3. Does that sound correct?

Anyway if you could post your finding on ODI I think that would be very helpful to me, that is if you still remember what has done or you have it documented.

I do run some ODI processes that were developed prior to my arriving at my current company however I am not very strong when it comes to ODI.

Thank you.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
  << Previous Topic | Next Topic >>Return to Index  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.