OBIEE Repository Upload Issue - DESC_CODE_SERVER_EXCEPTION

Applicable to OBIEE 12c version(s)

Issue -
OBIEE 12c: Downloadrpd/Uploadrpd Failed: Failure in trying to acquire lock. Check bi-lcm-logs or diagnostics. Error Desc Code: DESC_CODE_SERVER_EXCEPTION

Cause - 
There can be any causes of the listed below:

• Empty  "service.xml" file  in  DOMAIN_HOME/bidata/service_instances/ssi/service.xml  (If file empty, replace it from working environment)
• Checked "Disallow RPD updates" will cause the issue. (If fine ignore)
• Corrupt "bi-lcm-rest.log.0.lck" file under DOMAIN_HOME/servers/bi_server1/logs  (rename it as backup, new file will auto generate once associated managed "bi_server1" server restarts).

Make necessary changes from any of the above issues & restart server components. Issue will be resolved.

How to Decrypt Weblogic Passwords

How to know lost weblogic password ?

or

How to know encrypted key-store or database Schema password for Oracle Application ?

We may face issues if we forget weblogic passwords or else set incorrect of different password.
So there may be need of time to recall or decrypt those passwords for use. This document will guide
on how you can decrypt weblogic password or any AES algorithm based password for weblogic supported oracle application. It can be for Trust Store Key or DB-Schema for the application mentioned in config.xml or weblogic encrypted password in boot.properties for the component.

Prerequisites:
Access to the server (Unix / Windows Box)

I will cover steps from unix server for OBIEE environment (Which may be applied to any
middleware environment in any OS, with small changes or differences)

Step-1: Login to server with read / write & execute permission to server.

Step-2: Navigate to wlst.sh script path & execute it to start working in wlst prompt (offline)
##############
12c - /Middleware/oracle_common/common/bin
11g - /Middleware/oracle_common/common/bin
##############

Execute ./wlst.sh

You will go to wlst offline prompt like below:
wls:/offline>

Step-3: Set the values for  domain ,service and encryption variables where the value for domain is the path to your weblogic domain:

[For 11g Environment]

wls:/offline> domain="/Middleware/user_projects/domains/bifoundation_domain"
wls:/offline> service=weblogic.security.internal.SerializedSystemIni.getEncryptionService(domain)
wls:/offline> encryption=weblogic.security.internal.encryption.ClearOrEncryptedService(service)

[For 12c Environment domain path differs, due to different directory structure & domain home rest is same]

[For 12c wls:/offline> domain="/Middleware/user_projects/domains/bi"]

Step-4: Open boot.properties file to get encrypted password

boot.properties file path

###
12c - /Middleware/user_projects/domains/bi/servers/AdminServer/data/nodemanager
11g - /Middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/security
###

Step-5: Copy encrypted password in other file but replace second last special character which is \
This AES Encrypted code will end with \=, you only need to remove \ & keep all other characters as it is

Step-6: Now execute print command with encrypted code as below:

print "Weblogic server Admin password : %s" %encryption.decrypt("{AES}vcTrVGvE0MbzzLmh3PGq5imw4RMf3B0sOvUBFxB5lok=")
Weblogic server Admin password : Admin321

Well you got the password now.

The good thing about this decryption is, you can only decrypt password of this AES algorithm weblogic password for
only the environment you are working for, so its secure & only user with admin privilege to access server can decrypt lost weblogic password.


Now the question is:

Is this AES code only limited to decrypting weblogic password or user name or we can decrypt many more passwords which are to establish connection ?

The Answer is Yes, we can also decrypt any code of the server with same steps as mentioned above with only change in last print command code.
We can decrypt key-store passwords encrypted in config.xml files in the path:

/Middleware/user_projects/domains/bi/config

[Command to print only line with AES encrypted password: grep -n "{AES}" config.xml]

or we can also decrypt all associated database schema passwords from any Schema_Datasource-jdbc.xml file under the path:

/Middleware/user_projects/domains/bi/config/jdbc

[Note - Backslash followed by equal to sign as suffix in the code (\=) only appears in weblogic password, for other AES encrypted passwords for environment (key-store or db schema) remains without backslash. So you do not need to make any changes in encrypted code of any password other than weblogic admin password.]

Important Note - Same steps are applicable to all middleware applications (SOA/ Forms & Reports, etc.) with only difference is there domain home and directory structure.

How to know RPD Password of any Repository deployed in server

Hi Friends,

Some way or other we overcome with multiple issues, where we are in need to know if by any means rpd password is set wrong in OBIEE 11g.

For 10g it was easy and we can easily bypass security by modifying NQSConfig.INI file. But this is not applicable for OBIEE 11g.

However we had the solution till OBIEE version 11.1.1.7 to get rep password through process as below: ( Only applicable till versions 11.1.1.7)

Pre-Requisite - You must have Admin Rights to access weblogic & OS to find it. You can not bypass security. :)

Step-1 Navigate to your ORACLE_HOME, then go to common/bin path under ORACLE_HOME, where you can get wlst.sh / wlst.cmd command to execute WLST.

wls:/offline>
(Note - You may ignore warning for insecure protocol)

Step-2 - Connect online with connect command with your weblogic admin credentials.

Format -
connect(“<weblogic_AdminUser>”,”<weblogic_AdminUser_Password>”,”<Adminserver_Hostname>:<Admin_Portno>”)

eg.
connect("weblogic","weblogic123","t3://HOST_NAME:7001")

You are now connected to wlst online :
wls:/bifoundation_domain/serverConfig>

Step-3 Execute listCred command online

Format -
wls:/bifoundation_domain/serverConfig> listCred(map="mapName", key="keyName")

wls:/bifoundation_domain/serverConfig> listCred(map="oracle.bi.enterprise", key="repository.SampleAppLiteBI0003")


But as mentioned earlier this is applicable till obiee 11.1.1.7 versions. What if we have version greater than 11.1.1.7.

While running ListCred command we get error as below:

Traceback (innermost last):
File "<console>", line 1, in ?
NameError: listCred
wls:/bifoundation_domain/serverConfig>

Reason:
Oracle has removed listCred wlst command and is no longer available

So what is the alternative ???

It is available Folkes... :)

Solution:

1) Log into to EM console, navigate to Weblogic Domain ->Domain Name ->System Mbean Browser
Under Application Defined Mbean go to com.oracle.jps ->Domain:<domain_name> -> JpsCredentialStore ->JpsCredentialStore
As shown in figure below:

2) Click Operation tab and select getPortableCredential






3) Enter both the parameters as shown
Enter map value in first text box & key value in second text box


4) Click on Invoke to get the password

You will get your password under Return Value Panel password field.

:)

Merry Christmas & Happy New Year to all.

:)

Weblogic console & OEM Fusion Middlewere Not opening in OBIEE 11g

Hi Friends,

Some days back i faced a weird issue in OBIEE 11g server, where all opmn components were up & analytics was logining fine. But as an admin i had to do manual repository migration through Oracle Enterprise Manager Fusion Middleware. Considering that you might be knowing the process of rpd migration through OEM Fusion middleware, i am mentioning here for the solution to the issue i faced.


Issue - Weblogic console & OEM Fusion Middlewere Not opening.

Major Identification - While validating Admin Server Logs from
$DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log

[Note - To save time always search logs with respect to timestamp, there can be many other techniques]

On Validating the AdminServer.log, i get the error as below:
-------------------------------------------------------------------------------------------------------------------------------
####<Apr 19, 2015 1:21:09 PM EDT> <Critical> <WebLogicServer> <$HOSTNAME> <AdminServer> <main> <<WLS Kernel>> <> <> <1429464069767> <BEA-000386> <Server subsystem failed. Reason: java.lang.NumberFormatException: null
java.lang.NumberFormatException: null
at java.lang.Integer.parseInt(Integer.java:417)
at java.lang.Integer.parseInt(Integer.java:499)
at weblogic.ldap.EmbeddedLDAP.validateVDEDirectories(EmbeddedLDAP.java:1104)
at weblogic.ldap.EmbeddedLDAP.start(EmbeddedLDAP.java:242)
at weblogic.t3.srvr.SubsystemRequest.run(SubsystemRequest.java:64)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)

-------------------------------------------------------------------------------------------------------------------------

Root Cause –
Server subsystem failed. Reason: java.lang.NumberFormatException: null

Error points to Embedded LDAP for Weblogic

Embedded LDAP file (From Path - $DOMAIN_HOME/servers/AdminServer/data/ldap/conf) - replicas.prop size was 0 byte.

Action Taken –
Renamed replicas.prop to replicas.prop_old & restarted admin services.

Result - Success :) ...
Admin Services started & new replicas.prop ( embedded ldap server file with 14 bytes in my case) file created. Weblogic Console & Oracle Fusion Middleware also started working fine.

Hope it helps all in need :)

Enjoy

Write Back Setting in OBIEE 11G

Write Back is the ability to enter values directly into a report and have those values written in the database & used in calculations and charts in the report.
Please follow the steps listed below to configure Write Back in OBIEE 11g.

A- Repository Level Changes in all 3 layers to set Write Back Column :

Step 1:Physical Layer - From Physical Table Properties Go to-> General tab dialog box. Uncheck Cacheable

Step 2: Business Model & Mapping Layer -  Logical Column Properties Go to-> General tab. Check Writable option.

Step 3: Presentation Layer - Presentation Column Properties Go to-> General tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for WriteBack feature.

Step 4: Presentation Layer - Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named "Execute Direct Database Requests".

Now we are done with Repository level changes for WriteBack column. Let us proceed with other changes.

B- Changes in the File Level :

Step 1: Enable Write Back. Add the LightWriteback tag within  the serverinstance tag of instanceconfig.xml file  ( file path - $ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1 )

<LightWriteback>true</LightWriteback>

If this entry already exists then no modifications required, else restart OracleBIPresentationServicesComponent for this change to be effective.

Step 2: WriteBack Template. You may give any name for the writeback.xml file. Here for example i am using the file name as writeback_sample.xml

Things to do before you start the Template:

• Identify the columns that are to be referenced. 
• We can use the column position or by column id in the XML definition. 
• We must include both insert and update statements in the template.

Template to be placed in the path:
$ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

Write-Back Template example: Name - eg. writeback_sample.xml
=================================================
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="WriteBack_Template_Name">
      <XML>
         <writeBack connectionPool="Connection_pool_name">
            <insert>INSERT INTO Customer VALUES('@1','@2',@3,'@4',@5)</insert>
            <update>UPDATE Customer set Address='@2' where Name='@1'</update>
<!-- Identify the columns that are to be referenced. We can use the column position like @1 for first column, or by column id as you see them in the XML definition. -->
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="Template_Name">
      <XML>
         <writeBack connectionPool="ConnectionPool_Name">
            <insert></insert>
            <update>UPDATE Product set Prod_Description='@2' where Name='@1'</update>
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="Template_Name">
      <XML>
         <writeBack connectionPool="ConnectionPool_Purchase">
            <insert>INSERT INTO Supplier VALUES('@1','@2',@3,@4)</insert>
            <update></update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>
<!-- We may also use multiple insert / update commands for same or different connection pools as well based on our need -->
==================================================
[ Note - We need to mention insert & update statements based on our requirement. Above insert & update code is only for sample purpose & content within <!-- #### --> are comments. If we do not want to include SQL commands within the elements, then we must insert a blank space between the opening and closing tags. eg. <insert> </insert> rather than <insert></insert>]

In the above xml file...
Important Tags -

• WebMessage name - The name here will be used as WriteBack template name in analysis -> Table Properties -> Write Back.
• Write Back connectionPool - Connection pool name for Write Back.
• insert - Based on your requirement or leave blank ( if not required)
• update - Based on your requirement or leave blank ( if not required)

Now we are done with changes in the files. Lets proceed further & know what changes we need to make in analysis for the writeback report...

C- Changes in Analytics :

Step 1: Give Privilege to the Role for Write Back ( Administration -> Manage Privilege -> Write Back & add the role for writeback privilege). 

Step 2: Now you need to edit Column Properties -> writeback -> Check 'Enable Write Back' of the field needed for writeback, from criteria tab.

Step 3: Now edit Table Properties view -> writeback & check 'Enable Write Back' & mention Template Name as mentioned in writeback file ( WebMessage name="WriteBack_Template_Name" ). You may also rename Apply, Revert & Done Button & change Button Position if required.

You are now done with all WriteBack settings. You may test the report for set writeback features.

OBIEE enterprise manager adf faces 60097

Today I faced one issue in newly built server while uploading rpd for migration through EM.

I noticed error from coreapplication as :

Then Navigated to MBean to try my luck for rpd uploading from there, but all parameters were showing unavailable Value.

I explored the solution to it & identified as file - <OBIEE_HOME>/user_projects/domains/bifoundation_domain/sysman/mds/partition1/ai/bi/fragments/mdssys/cust/user/weblogic/deployment.jspx.xml was causing the issue. I deleted it & proceeded with full bounce. 

[ Note - deployment.jspx.xml file will get created automatically after you navigate to coreapplication in enterprise manager. But take the backup of the file before deleting it. ]

The issue resolved then & coreapplication & MBean both working fine thereafter...

& deployment.jspx.xml got created automatically after navigating to coreapplication from Enterprise Manager.

Math Functions in OBIEE

There are many science, research and development functions which are not much explored or used in business intelligence. Most of such functions belong to Math Functions. I am listing below all Math Functions.

Math Functions

Abs, Acos, Asin, Atan, Atan2, Ceiling, Cos, Cot, Degrees, Exp, Floor, Log, Log10, Mod, Pi, Power, Radians, Rand, RandFromSeed, Round, Sign, Sin, Sqrt, Tan, Truncate

As I am not working in any scientific or research & development project, so I decided to manually create such functions, to describe math functions. I then thought to randomly select some of trigonometric functions & test result through their graph, so I choose sin, cos and tan. But for trigonometric function I had to further make use of pi (you may use degrees as well).

The functions applied in my case for different functions is as below:

Sin - SIN((RCOUNT(1)-1)*PI()/4)

Cos - COS((RCOUNT(1)-1)*PI()/4)

Tan - TAN((RCOUNT(1)-1)*PI()/4)

Angle in Degree - CEILING(DEGREES((RCOUNT(1)-1)*PI()/4))

Where RCOUNT(1)-1 gives numeric value starting from zero & 1 in RCOUNT(1) is numeric symbolizing first column with distinct data & PI()/4 is used to multiply each count to pi/4 ( quarter of semicircle).

Math Function Test
Test Maths
Calculated Attributes
Angle in Degree	Sine Value	Cos Value	Tan Value
0	0	1	0
45	0.707	0.707	1
90	1	0	6.18986E+14
135	0.707	-0.707	-1
180	0	-1	0
225	-0.707	-0.707	1
270	-1	0	2.16235E+14
315	-0.707	0.707	-1
360	0	1	0
405	0.707	0.707	1
450	1	0	1.20485E+14
495	0.707	-0.707	-1
540	0	-1	0
585	-0.707	-0.707	1
630	-1	0	9.01976E+13
675	-0.707	0.707	-1
720	0	1	0
765	0.707	0.707	1
810	1	0	6.77417E+13
855	0.707	-0.707	-1

So as verifying from the graph & tabular value, it is clear to make use of them, leaving tan pi/2 as an exception of giving a finite value. However still aviating, some more scientific calculus functions, which will make obiee full package to be used in scientific research & development projects.

J