Error Saving User Preferences in Planning

While performing an upgrade to the planning application, the new application is facing an error saving the user preferences in planning. 

When trying to save the user preferences the following end user error is displayed:

"The SQL operation failed. Check logs for details.

User Preferences have been saved."

Diving into the Planning logs identifies the actual error message:

java.sql.SQLSyntaxErrorException: ORA-00904: "ENABLE_MRU_SCREEN": invalid identifier


This error was found searching the knowledge base, 
Unable to Save Form Preference in Planning After Upgrade [ID 889744.1]


According to Oracle:
This issue has been caused by migrating the application and a column must be added to HSP_USER_PREF table.

One would think that when you upgrade the application, the schema update it performs when logging in for the first time would add this column, nevertheless...

The following SQL was executed against the planning application database to add the appropriate column.

ALTER TABLE HSP_USER_PREFS ADD ENABLE_MRU_SCREEN INTEGER DEFAULT 1 NOT NULL

epmsys_registry Command Line Options

The epmsys_registry command is the key to manipulating the insides of the EPM System. However, it is not well documented. Here are a few ways to further explore the tool...

A command that is useful is the host report. This report can show you what hosts are stored inside the epm registry and what those hosts are being resolved to on each node in the environment. This is useful if you have DNS names or virtual names inside the registry and how those names are being resolved on the host itself. Over time, host resolution may change based on host file entries, DNS changes, failover scenarios, load balancing...etc. The report can identify host related issues buried inside the registry.

epmsys_registry viewhosts

Hosts in EPM Registry

HOST NAME IN EPM REGISTRY           HOST NAME AS RESOLVED ON THIS MACHINE

*************************                            *************************************

1       host1.full                                      host1.full

2       VirtualIP                                       host2.full

Generate a deployment report

  - Creates a report enumerating your web deployments and a history of deployment activities.

epmsys_registry report deployment

Components of the report:

• Logical Web Addresses
• Application Tier Components
• EPM Deployment Topology Report 
• EPM Deployment History Report
• User Directories - SSO Not Enabled
• Database Connections
• Data Directories

Sample Command Line Usage:

epmsys_registry deletecomponent #8dafd4d781a01633a4a713b43baa1b1S6c71
    The component has been removed. However the linked components remain.

epmsys_registry view FOUNDATION_SERVICES_PRODUCT

Change the database password in the EPM Registry:
epmsys_registry updateencryptedproperty #64f85ee85aef2a62eccaf7d13c02cb3445S76a3/@dbPassword testpw
    The new value of the property has been encrypted and updated on the component.

Hierarchical changes:

epmsys_registry createlink #18349408fd0bc34c2226d26dafds88d019S7a87 #834940894494fdcS6fe8c90313b3e28c59dS6d7f

-- first argument is parent component , second is the child link that will be removed. epmsys_registry removelink #18349408fd0bc34dfda33b3e28c59dS6d7f #8349408fd0bc34addd26213aad88d019S7a87

Full List of Command Line Options

 view

 viewencryptedproperty (and yes this does display the unencrypted password)

 viewhierarchy

 addproperty

 updateproperty

 removeproperty

 addencryptedproperty

 updateencryptedproperty

 addfile

 updatefile

 removefile

 createlink

 removelink

 createcomponenthierarchy

 deletecomponent

 updatehost

I'm presenting at ODTUG's Kscope 13 in New Orleans

I'm presenting at ODTUG's Kscope 13 in New Orleans! Join me @ the best conference for developers put on by developers http://kscope13.com

How to Turn New Recruits into Oracle EPM Support Gurus
When: Jun 26, 2013, Session 16, 4:15 pm - 5:15 pm
Oracle EPM requires a knowledgeable team to provide production support due to its criticality as a service. Typically skill levels vary in the team as resources are pulled from other areas or are required to support multiple services. Consequently, the need for infrastructure training is a recurring theme in an organization. This presentation covers how to explain Hyperion and its architecture in a way to fully engage new support staff. It includes getting started with EPM modules, logs, and troubleshooting.

EPM Log Files Revisited

The topic of log files can be a bit mundane, yet during troubleshooting I find myself constantly pursuing log files. The ability to navigate quickly to the correct log file is crucial for successful administration of the EPM system.

For example, a quick review of the log files on a typical EPM system might include over 100 log files:



\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Admin.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FinancialReporting*\logs\FRLogging.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FinancialReporting*\logs\FinancialReporting*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\CalcMgr*\logs\CalcManager.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\CalcMgr*\logs\CalcMgr*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\FoundationServices*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Admin.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Audit.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Bpmui.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Bpmui_Config.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Bpmui_Sec.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_CMSClient.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Hub.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_LCM.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Registry.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_Security.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\SharedServices_TaskFlow.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\Workspace.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\WorkspaceConfig.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices*\logs\WorkspaceConfig.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\RaFramework*\logs\RaFramework*.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\hfm.odl.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\HsvEventLog.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\InteropJava.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\registry\registry.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\stdout_console_servlets.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9CALC-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9CALC-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FinancialManagementWebSvcs-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FinancialManagementWebSvcs-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FoundationServices-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FoundationServices-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FRReports-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FRReports-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9RaFramework-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9RaFramework-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\diagnostics\logs\OHS\ohs_component\access_log
\Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\diagnostics\logs\OHS\ohs_component\console~OHS~*.log
\Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\diagnostics\logs\OHS\ohs_component\ohs_component.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9aifWeb-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9aifWeb-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FinancialManagementWebSvcs-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9FinancialManagementWebSvcs-sysout.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\ErpIntegrator*\logs\ErpIntegrator*.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\stdout_console_default.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\agent.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\configuration_messages_default.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\eiengine.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_AdministrationServlet.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_AnalyticBridgeService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_AuthenticationService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_AuthorizationService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_BrowseServlet.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_CommonServices.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_CSSSynchronizer.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_DataAccessServlet.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_EventService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_GSM.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_HarvesterService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_iHTMLServlet.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_JobManagerServlet.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_JobService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_LoggingService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_LSM.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_PersonalPagesServlet.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_PublisherService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_RepositoryService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_SearchIndexing.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_SearchKeywordProvider.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_SearchMonitor.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_ServiceBroker.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_ServiceBroker.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_SessionManager.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_TransformerService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\server_messages_UsageService.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\stdout_console_agent.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\FinancialReporting\FRPrintLogging.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\epma\datasync.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\epma\dimensionServer.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\epma\NetJNIBridge.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9EPMADataSynchronizer-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9EPMADataSynchronizer-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9EPMAWebTier-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9EPMAWebTier-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\FRPrintService.Err.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\FRPrintService.Out.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9RaFrameworkAgentErr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9RaFrameworkAgentOut.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\EpmaDataSync*\logs\EpmaDataSync*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\EpmaWebReports*\logs\EpmaWebReports*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\AnalyticProviderServices*\logs\AnalyticProviderServices*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\EssbaseAdminServices*\logs\easserver.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\EssbaseAdminServices*\logs\EssbaseAdminServices*.log
\Oracle\Middleware\user_projects\domains\EPMSystem\servers\Planning*\logs\Planning*.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9aps-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9aps-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9eas-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9eas-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9Planning-syserr.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\services\HyS9Planning-sysout.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\HsvEventLog.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\hfm.odl.log
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\hfm\InteropJava.log'

Let's focus on how to sift through the logs more quickly: 

Types of logs: 

1. WebLogic Application Logs
   These logs come from a web deployment and are located in the domains folder:
   Oracle\Middleware\user_projects\domains\EPMSystem\servers
2. EPM Application Logs
   Once the service or application server is started properly, its internal logging takes over using the Oracle ODL logging framework. These log files are typically in the diagnostics folder, but may also reside in the domains folder or elsewhere based on the ODL configuration.
   \Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs

WebLogic Service Not Starting
For web application based services check out the specific WebLogic application log under the domains folder. If the web app is not able to start correctly, chances are it is not writing the EPM application logs because the logger was not initialized or the underlying service never came up. In the Application server log you will see the server go through different states like STATE_PREPARED, etc. During these phases the application does things like setup JDBC connections to the database and tries to initialize its services based on configuration details within the HSS registry. The server is available once you see a message like "server started in PRODUCTION mode".


Workspace Errors After Logging On
Workspace has two components required to log on, the Framework Services and the Framework/Foundation Web Services. For the Web components check the domain logs for Framework and Foundation.
However, the Agent services must start correctly, Check for the message "Giving control to the ORB" in the log:
\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\ReportingAnalysis\stdout_console_default.log
Typically, if BI will not start - some type of exception is generated during the AGENT start up in stdout_contsole*.log


Errors in a Running Service
Usually once a web service has been started the domain logs become less important because the EPM application logging starts to take over. Assuming the service has started check for the EPM Applicaiton logs in the diagnostics folder for the particular product as the WebLogic Application log usually stops giving useful messages after "server started in PRODUCTION mode".


HFM Logs
HFM has historically been an outlier with respect to log setup. The best place to look for HFM specific errors is to use the HFMErrorLogViewer utility. This utility accumulates and formats the somewhat cryptic messages left in the Windows Registry and the HFM Database for human readable display. Remember, if you have the HFM Web service on a different node than the HFM Application node - the web server HFMErrorLogViewer might have different information.

In 11.1.2.2 HFM has a WebLogic component so look in the domain logs.


FDM Logs
FDM is an IIS deployed application. It logs generic errors to the Windows Registry. During run-time specific operations like loads may generate specific log messages. The log files are viewable by selecting the log file in the FDM web menu. Additionally this log file can be found by searching for *.log within the FDM Application's share directory, prefixed with the username of the user running the operation. Additional logging regarding adapter messages can be coaxed out by setting logging levels with the adapter settings for things like ERPI.

Additional information regarding troubleshooting using the logs can be found in my other posts:
Oracle-EPM-Troubleshooting-and-Debugging-Guide-(Part-1-of-2)
Oracle-EPM-Troubleshooting-and-Debugging-Guide-(Part-2-of-2)

resetConfigTask.bat

Sometimes it is necessary to reset a configuration task in order to redo some specific actions which the config tool does during the initial configuration. Meaning there are some tasks that are only performed one time during the initial configuration. Subsequent runs on the config tool task do not select these tasks, such as the pre-configuration tasks.

Since this task is already green indicating it is configured, the next step only has 1 task:

However, there are additional pre-configuration tasks that can be run by setting the status of the task back from Configured to Pending:

Now this shows the Pre-Configuration Tasks

What to Do?

In the past this was done by changing the xml files associated with the product. For instance, epma would be located in the directory

Oracle\Middleware\user_projects\epmsystem1\config\foundation\11.1.2.0\product\bpma\11.1.2.0

And the file  bpma_1_config.xml  would contain the status of each deployment task.

<property name="applicationServerDeployment">Configured</property> 

<property name="bpmaInterfaceDatasourceConfiguration">Configured</property> 

<property name="bpmaVirtualDirectoryConfiguration">Configured</property>

<property name="hubRegistration">Configured</property>

<property name="preConfiguration">Configured</property> 

In 11.1.2.2 the deployment tasks have been moved into the database so as to become a central location for editing this metadata. Now, to change the metadata you should use the resetConfigTask.bat utility located in the following directory:

D:\Oracle\Middleware\user_projects\epmsystem1\bin

It takes two arguments, the –task and the –product. The product name can be found by looking in a registry report, search for the word “Configured”.

For instance, let’s modify the preConfiguration task for RA Framework. First, check the registry report:

We wish to change the preConfiguration to Pending in order to rerun the pre-configuration scripts for reporting and analysis

D:\Oracle\Middleware\user_projects\epmsystem1\bin>resetConfigTask.bat -product r

aframework -task preConfiguration

D:\Oracle\Middleware\user_projects\epmsystem1\bin>"D:\Oracle\Middleware\EPMSyste

m11R1\common\config\11.1.2.0\resetConfigTask.bat" -eoi "D:\Oracle\Middleware\use

r_projects\epmsystem1" -product raframework -task preConfiguration

Task [preConfiguration] for product [raframework] has been found under [INSTANCE

_TASKS_CONFIGURATION] component with id [18349408fd0bc34c6a4ad8e913aade7a519S4de

c]

Now to check the instance configuration in the epm system registry report:

Now the task is set to pending as shown in the above screenshot.