tag:blogger.com,1999:blog-6174080633027694882024-03-04T23:59:04.651-08:00Adventures in OBIEEPostings about scripts, tips, and tricks I have picked up with OBIEE 11g.Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-617408063302769488.post-56683386257786776742013-10-30T16:04:00.000-07:002013-12-10T09:25:46.820-08:00Configuring Agents with OBIA and EBS Cookie Integration<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhI7n_cHTJ_pu1hVAZvA6_q00Bh1UyARQ4wPW4vtBizxdRCYq2o4Nxw8ozYEoEJjPCren-6bymOliRhYcQApXttExnFo8Q_I60wTwSvRDVjGKHZnw-SciVtPHP-7ATwi6KEsZwYbiXY3o/s1600/Agent.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhI7n_cHTJ_pu1hVAZvA6_q00Bh1UyARQ4wPW4vtBizxdRCYq2o4Nxw8ozYEoEJjPCren-6bymOliRhYcQApXttExnFo8Q_I60wTwSvRDVjGKHZnw-SciVtPHP-7ATwi6KEsZwYbiXY3o/s1600/Agent.png" /></a></div>
Where I am currently working, we are using EBS and OBIEE in a setup with EBS Cookie Authentication (blog post on this soon). This has some draw backs. One draw back is Agents can not be used. When trying to run an agent under this setup, as a user authenticated via EBS Cookie, you will get the following error:<br />
<br />
[OBI-SEC-00015] Unable to find user in identity store<br />
<br />
I opened an SR at the time, and the response back was a reference to an existing Bug (Bug 10632223: AGENTS NOT WORKING IN EBS SSO ENVIRONMENT WITH OBIEE 11G) and an enhancement (Bug 8326835: INTRODUCE SUPPORT FOR DELIVERS WITH EBS ICX_COOKIE).<br />
<br />
According to the Oracle Support person, neither has been implemented. Apparently, Agents were never intended to be used with EBS cookie users, but only with users contained directly in usable authentication systems (ie OID/OAM/LDAP, etc).<br />
<br />
Obviously, this is not acceptable, so I went to work to find a solution to get Agents up and running. We are pretty lucky here, in that every user whom can access OBIEE can access all subject areas in OBIEE. This might be different for you, so the end result might require you to do additional work.<br />
<br />
Continue on to read for an explanation of what I did to get Agents up and running.<br />
<a href="http://www.blogger.com/blogger.g?blogID=617408063302769488" name="more"></a><br />
<a name='more'></a><div>
<div>
<br /></div>
<div>
Steps to resolve Agents with EBS Cookie Authentication:<br />
<br />
1. Create a new session initialization block called 'EBS Initialize Session Cookies'<br />
2. Click on 'Edit Data Source' and input the default Initialization String as:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>select '-1', '0' from dual;</code></pre>
<br />
3. Select the Oracle EBS OLTP Connection Pool<br />
4. Press 'Edit Target Data' button and create 2 variables: ICX_SESSION_COOKIE and ACF<br />
5. Define session variable: ICX_SESSION_COOKIE:<br />
6. Check the "Enable any user to set the value" checkbox<br />
7. Check the 'Security Sensitive" checkbox<br />
8. Set the Default Initializer to:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>'-1'</code></pre>
<br />
9. Define session variable: ACF<br />
10. Check the "Enable any user to set the value" checkbox<br />
11. Check the "Security Sensitive" checkbox<br />
12. Set the Default Initializer to:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>'0'</code></pre>
<br />
13. Redefine the 'Execute on connect' query for the Oracle EBS OLTP Connection Pools as follows:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>Declare /* valueof (NQ_SESSION.ACF) */ v_icx_session_cookie varchar2(100) := 'valueof(NQ_SESSION.ICX_SESSION_COOKIE)'; begin if v_icx_session_cookie <> '-1' then APP_SESSION.validate_icx_session(v_icx_session_cookie); end if; end;</code></pre>
<br />
14. Update the session variable initialization block 'EBS Security Context' query to be:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>select FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID,
FND_GLOBAL.SECURITY_GROUP_ID,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.USER_ID,
FND_GLOBAL.EMPLOYEE_ID,
NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') from dual
where NVL(FND_GLOBAL.USER_NAME,'IMPERSONATOR') != 'GUEST'
NQS_PASSWORD_CLAUSE(and FND_GLOBAL.USER_NAME != 'GUEST')NQS_PASSWORD_CLAUSE</code></pre>
<br /></div>
</div>
<blockquote style="border: currentColor; margin: 0px 0px 0px 40px; padding: 0px;">
<div>
<div>
What this does is a couple of things:</div>
</div>
</blockquote>
<div>
<ol><ol>
<li>If you attempt to login directly via the OBIEE login screen, and you do not exist in the internal weblogic LDAP, you will be authenticated against this SQL. Since we set up the new Execute on connect, as you have no cookie it will bypass that on-connect SQL. The FND_GLOBAL functions will return null/-1 for all values.</li>
<li>When the system is not logging in as an impersonated user, it will require the NQS_PASSWORD_CLAUSE to succeed, which is this case the value of USER_NAME is null and thus != GUEST. Hence the block will fail, and the user will not be allowed to login.</li>
<li>When an iBot/Agent runs this query, the contents of NQS_PASSOWRD_CLAUSE is ignored. Thus the query will return and allow the iBot/Agent to continue.</li>
</ol>
</ol>
15. Log on to your server (via Putty or similar tool) and open the instanceconfig.xml file. For me this is located at:<br />
<br />
${FMWHOME}/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml<br />
<br />
16. Scroll down to the <b></Alerts></b> tag, and just above, add the following lines:
<br />
<div class="MsoListParagraphCxSpMiddle" style="margin: 0in 0in 0pt 1.5in; mso-add-space: auto;">
</div>
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code><!-- Custom Settings -->
<ignorewebcatdeliveryprofiles>false</ignorewebcatdeliveryprofiles>
<uppercaserecipientnames>false</uppercaserecipientnames>
<!-- Custom Settings End --></code></pre>
<br />
17. At the bottom of the instanceconfig.xml file, right before the </ServerInstance> tag, place the following:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code><!-- Custom Settings -->
<SubjectAreaMetadata>
<SystemSubjectArea>true</SystemSubjectArea>
</SubjectAreaMetadata>
<!-- Custom Settings End --></code></pre>
<br />
<strong>Important Note:</strong> In order to not have to populate a physical system subject area table with all of my users, and then manually keep it up-to-date, the SA_SUBECT area was setup to pull from the W_EMPLOYEE_D dimension. This allows the ETL to automatically populate the users and email address, so new users will automatically appear in the SA Subject area, and will automatically have their delivery profile setup with their email address as it appears in EBS.<br />
<br />
This subject area is simply a new logic table, Users, under the Core model.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-TYDvHMVOBgpO8gdMmX1VeH2dLOJPkCqaYcddr5Ax5CgTb4xuN_7GqQ_nDDuYx5q7Vv5urYv2w09l5g_hkmh06A5FVDW3RBrSbDWG_jlLl71UzrLIGkAOiIbEuGPLKYyuOhfjqH1CEmw/s1600/logical_model.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-TYDvHMVOBgpO8gdMmX1VeH2dLOJPkCqaYcddr5Ax5CgTb4xuN_7GqQ_nDDuYx5q7Vv5urYv2w09l5g_hkmh06A5FVDW3RBrSbDWG_jlLl71UzrLIGkAOiIbEuGPLKYyuOhfjqH1CEmw/s320/logical_model.png" width="320" /></a></div>
<br />
As most of the columns are not required (see <a href="http://docs.oracle.com/cd/E23549_01/bi.1111/e10541/sa_system.htm" target="_blank">Oracle Note</a> on SA Subject Area) they have been defaulted to null:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgH20-AVpJxw8x6hUgaILqYWuLnxh-w2tDPW8yY3suEH0smRlg727SiFdkJeSX2ob9ZDLEnemsDDY8E5Sj_DYQND1fpgRb07mL2qDM_tljf8ZtE2WBmW8rtw0qnOUoP-EWGlTZ15owvFk/s1600/LTS_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="174" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgH20-AVpJxw8x6hUgaILqYWuLnxh-w2tDPW8yY3suEH0smRlg727SiFdkJeSX2ob9ZDLEnemsDDY8E5Sj_DYQND1fpgRb07mL2qDM_tljf8ZtE2WBmW8rtw0qnOUoP-EWGlTZ15owvFk/s320/LTS_1.png" width="320" /></a></div>
<br />
Finally, because the Employee Dimension is type 2, and we really only care about the current information for email address, we added a Content Filter:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQojYSKOlXRK4tYDkaIrBPTQr8R0xUIp_02zjuveMRl9ELrc4Fdn0_6tV9s51k7m1g1KOIEPoRlmxYxr_p8nfmIsi2BFIzjhWVOn1dliNPj90Z6eUlGi9Kdc-sgq06UY3OWaQiGad14V0/s1600/LTS_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQojYSKOlXRK4tYDkaIrBPTQr8R0xUIp_02zjuveMRl9ELrc4Fdn0_6tV9s51k7m1g1KOIEPoRlmxYxr_p8nfmIsi2BFIzjhWVOn1dliNPj90Z6eUlGi9Kdc-sgq06UY3OWaQiGad14V0/s320/LTS_2.png" width="320" /></a></div>
<br />
<br />
18. For each subject area you want to allow Agents to access, they need to have the Authenticated User permissions applied. If not, then when the agent runs it will not be able to apply any filters, as it won't have access to those columns.<br />
<br />
<strong>Note:</strong> Because of my circumstances, this was acceptable. It might not be for you. With some work, you might be able to force the Agent to authenticate as BI Author, or some other Application Role, in which case this step is not required.<br />
<br />
19. Deploy the RPD file.<br />
<br />
Agents will now work for all users.<br />
<br />
One caveat. Agents can not be stored in a users My Folder, as the impersonated user that logs in will not have read access to that folder. Therefore, all Agents will need to be stored in the Shared Folders. Not ideal, but better than not having access to Agents at all.</div>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com10tag:blogger.com,1999:blog-617408063302769488.post-46990271878877790342013-09-26T10:23:00.001-07:002013-09-26T10:28:57.394-07:00Configure Weblogic SSL to deny SSLv2<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLWrg8nplUMaEBpq5hPvN1CsuqSczpQ4W3F-KPO4VKc_huS1i7FZwRBtrDEc_iY7_7bq5suMLsfa6P7axSjOV95WE8jJ3qUxBdc-tFGLZNXX7Hz01_iTPHNxuZGnfpGtw3m54RbqI00QI/s1600/Oracle-Weblogic.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="100" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLWrg8nplUMaEBpq5hPvN1CsuqSczpQ4W3F-KPO4VKc_huS1i7FZwRBtrDEc_iY7_7bq5suMLsfa6P7axSjOV95WE8jJ3qUxBdc-tFGLZNXX7Hz01_iTPHNxuZGnfpGtw3m54RbqI00QI/s200/Oracle-Weblogic.jpg" width="100" /></a></div>
Quick post, with a quick solution.<br />
<br />
We are going through some security audits, and one of the things that we had failed on was an SSL test on our OBIEE server. The issue was that by default, Weblogic was allowing SSLv2 and weak ciphers, something out security team doesn't like.<br />
<br />
The solution took me some <a href="http://docs.oracle.com/cd/E13222_01/wls/docs92/secmanage/ssl.html#wp1194608">googleing</a>, but I found the change and it was quite simple. Essentially we had to add a simple command line argument to our bi_server startup script:<br />
<br />
-Dweblogic.security.SSL.protocolVersion=SSL3 -Dweblogic.security.disableNullCipher=true<br />
<br />
That resolved it, and now we are passing the SSL check.<br />
<br />
For reference on how we <a href="http://adventuresinobiee.blogspot.com/2013/05/setting-up-ssl-for-weblogic.html">setup SSL for OBIEE</a> in Weblogic on the appserver, check out the previous blog post.Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com0tag:blogger.com,1999:blog-617408063302769488.post-84057784308544767522013-09-25T12:52:00.002-07:002013-10-30T15:44:59.330-07:00How to Unit Test OBIA Informatica Changes<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivO7LzdNKS2j2qP-ubUbd_zAsnlgKD7W7ctGjhA8XA0WkMWIUOA46ZS53YtYFBwHwd7S0wNInL8T9KQF1lzaMYIP-EFtaruQ_HX7SON8vKE66mil94uZ_lEU3yHdaSN3DH4W8pb7jjs3k/s1600/Informatica.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivO7LzdNKS2j2qP-ubUbd_zAsnlgKD7W7ctGjhA8XA0WkMWIUOA46ZS53YtYFBwHwd7S0wNInL8T9KQF1lzaMYIP-EFtaruQ_HX7SON8vKE66mil94uZ_lEU3yHdaSN3DH4W8pb7jjs3k/s1600/Informatica.png" /></a></div>
I've started getting into doing a bit of development with Informatica on our OBIA install (for reference, we are using OBIA 7.9.6.3 + Extension pack). Part of the "best practices" setup by our Oracle Consultant was to create a separate sandbox folder anytime we were making a change to an Infomratica mapping. The difficulty I had was how to test the workflow after I made my changes, as I can't run my change via DAC, without creating a separate execution plan, and I can't test directly in Infomratica, as it references a parameter file that exists on the server.<br />
<br />
The solution was to extract the command that DAC would run (pmcmd), create a temporary parameter file, and then run my new mapping from my sandbox folder. And as always, I've created a helpful script to automate this!<br />
<br />
Continue on to read for an explanation of what my script does, and a copy of said script.<br />
<a href="http://www.blogger.com/blogger.g?blogID=617408063302769488" name="more"></a><br />
<a name='more'></a><div>
<div>
<br /></div>
<div>
Note, this is probably fairly specific to our OBIA setup, but should be able to be tweaked to work under your setup.</div>
<div>
<br /></div>
<div>
First, the script, infaUnitTest.sh</div>
<div>
<br /></div>
<a href="javascript:void(0);" onclick="javascript:showinfaUnitTestMessage()">Click me to view infaUnitTest.sh</a>
<script language="JavaScript">function showinfaUnitTestMessage(){if(document.getElementById('infaUnitTest').style.display == "block"){document.getElementById('infaUnitTest').style.display = "none";}else{document.getElementById('infaUnitTest').style.display = "block";}}</script>
<br />
<pre id="infaUnitTest" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#! /bin/bash
######
# infaUnitTest.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
#
######
TRUNCATE=0
INFA_DB_USER=OBIINFA
INFA_DB_PASS=INFA_PASS
OBI_DB_USER=OBIBAW
OBI_DB_PASS=INFA_PASS
DB_NAME=OBID
# If there are less than 4 parameters, then show help and exit
if [ $# -lt 8 ]; then
echo -e "\nUsage: infaUnitTest.sh -u infa_user -p infa_pass -s sandbox_folder -w workflow_name [-t]\n\t-u\tInfa user name that owns the sandbox folder.\n\t-p\tPassword for the infa user that owns the sa
ndbox folder.\n\t-s\tName of the Informatica sandbox folder.\n\t-w\tName of the Informatica Workflow.\n\t-t\tTruncate the target table(s) specified in the workflow\n"
exit -1
fi
set -o pipefail
while getopts "s:w:tu:p:" opt; do
case $opt in
s) SANDBOX=${OPTARG} ;;
w) WORKFLOW_NAME=${OPTARG} ;;
t) TRUNCATE=1 ;;
u) USERNAME=${OPTARG} ;;
p) PASSWORD=${OPTARG} ;;
\?) echo "Invalid option: -$OPTARG"; exit ;;
:) echo "Option -$OPTARG requires an argument."; exit ;;
esac
done
# Create custom Param file
echo -e "Creating temp parameter file."
cd /apps/applinfa/infa_910/server/infa_shared/DacParameters
for file in $(find * -type f -print0 2>/dev/null | xargs -0 grep -l "$WORKFLOW_NAME" | grep -Ev 'sandbox')
do
echo -e "\nFound parameter file $file."
NEWFILE=$SANDBOX.$WORKFLOW_NAME.txt
cp $file $NEWFILE
echo -e "\nCreating clone of $file as $NEWFILE."
sed -e 's/ORA_R1213.DATAWAREHOUSE.CUSTOM_SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/ORA_R1213.DATAWAREHOUSE.SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/CUSTOM_SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/SDE_ORAR1213_Adaptor/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/DATAWAREHOUSE.DATAWAREHOUSE.CUSTOM_SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/DATAWAREHOUSE.DATAWAREHOUSE.SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/CUSTOM_SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
sed -e 's/SILOS/'${SANDBOX}'/g' $NEWFILE > /tmp/$NEWFILE
mv /tmp/$NEWFILE $NEWFILE
done
cd ~/bmackin
if [ "$TRUNCATE" != "0" ]; then
TARGET_TABLE=$(${ORACLE_HOME}/bin/sqlplus -s ${INFA_DB_USER}/${INFA_DB_PASS}@${DB_NAME} << END
set feedback off
set head off
set pagesize 0
set heading off
set LINE 200
set DEFINE OFF
set SCAN OFF
select distinct opb_targ.target_name
from opb_subject join opb_task on opb_subject.subj_id = opb_task.subject_id
join (
select workflow_id, instance_id, task_id, task_type, instance_name, max (version_number)
from opb_task_inst sess
where sess.task_type = 68
group by workflow_id, instance_id, task_id, task_type, instance_name
) tasks on opb_task.task_id = tasks.workflow_id
join (
select session_id, mapping_id, max (version_number)
from opb_session
group by session_id, mapping_id
) sessions on tasks.task_id = sessions.session_id
join opb_mapping on sessions.mapping_id = opb_mapping.mapping_id
join opb_widget_inst on opb_mapping.mapping_id = opb_widget_inst.mapping_id
join opb_targ on opb_widget_inst.widget_id = opb_targ.target_id
where opb_task.is_visible = 1
and opb_task.task_type = 71
and opb_mapping.is_visible = 1
and opb_targ.is_visible = 1
and opb_widget_inst.version_number = (select max(owi.version_number) from opb_widget_inst owi where owi.mapping_id = opb_mapping.mapping_id)
and opb_subject.subj_name = '$SANDBOX'
and opb_task.task_name = '$WORKFLOW_NAME';
exit
END)
echo -e "\nTarget Table(s) found:\n$TARGET_TABLE"
for TABLE in $TARGET_TABLE
do
echo -e "\nTruncate table $TABLE"
${ORACLE_HOME}/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} << END
truncate table $TABLE;
exit
END
done
echo -e "\nDetermine if there are any indexs on the table(s):\n$TARGET_TABLE"
echo -e "set verify off\nset feedback off\nset show off\nset echo off\nset tab off" > tmp_index_ddl.sql
for TABLE in $TARGET_TABLE
do
echo -e "\nExport index DDL for table $TABLE"
${ORACLE_HOME}/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} << END >> tmp_index_ddl.sql
set feedback off
set head off
set pagesize 0
set heading off
set LINE 200
set DEFINE OFF
set SCAN OFF
select dbms_lob.substr(DBMS_METADATA.GET_DDL('INDEX',index_name),4000,1) || ';'
from user_indexes
where table_name = UPPER('$TABLE');
END
done
echo -e "SHOW ERROR;\nSET FEEDBACK ON\nEXIT\n/" >> tmp_index_ddl.sql
echo -e "\nDrop all indxes on the table(s):\n$TARGET_TABLE"
echo -e "set verify off\nset feedback off\nset show off\nset echo off\nset tab off" > tmp_index_drop.sql
for TABLE in $TARGET_TABLE
do
echo -e "\nCreate drop index syntax for table $TABLE"
${ORACLE_HOME}/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} << END >> tmp_index_drop.sql
set feedback off
set head off
set pagesize 0
set heading off
set LINE 200
set DEFINE OFF
set SCAN OFF
select 'drop index ' || index_name || ';' from user_indexes where table_name = UPPER('$TABLE');
END
done
echo -e "SHOW ERROR;\nSET FEEDBACK ON\nEXIT\n/" >> tmp_index_drop.sql
echo -e "\nRun the drop index DDL script."
/apps/oracle/product/11.2.0/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} @tmp_index_drop.sql
rm tmp_index_drop.sql
fi
echo -e "\nRun the workflow $WORKFLOW_NAME out of folder $SANDBOX."
pmcmd startworkflow -sv OBI_IS_D -d Domain_sappobid02v -u $USERNAME -p $PASSWORD -f $SANDBOX -lpf /apps/applinfa/infa_910/server/infa_shared/DacParameters//$SANDBOX.$WORKFLOW_NAME.txt -wait $WORKFLOW_NAME
if [ "$TRUNCATE" != "0" ]; then
echo -e "\nRestore any dropped indexes."
/apps/oracle/product/11.2.0/bin/sqlplus -s ${OBI_DB_USER}/${OBI_DB_PASS}@${DB_NAME} @tmp_index_ddl.sql
rm tmp_index_ddl.sql
fi
echo -e "\n\n**********\n**********\nAll Done. Please check your results.\n**********\n**********\n"
</pre>
<br />
<div>
Now for the explanation.</div>
<div>
<br /></div>
<div>
The script takes the following parameters:<br />
<br />
-u Infa user name that owns the sandbox folder.<br />
-p Password for the infa user that owns the sandbox folder.<br />
-s Name of the Informatica sandbox folder where your files are stored.<br />
-w Name of the Informatica Workflow to run.<br />
-t Truncate the target table(s) specified in the workflow.<br />
<br />
<h2>
Parameter File Creation</h2>
<br />
The script will then go into the DacParameters directory, in my case this is:<br />
<br />
/apps/applinfa/infa_910/server/infa_shared/DacParameters<br />
<br />
It will then do a search file any parameter files that exist with your workflow name mentioned within the file.<br />
If it finds one, it copies it into a new file (prefixed with your sandbox folder name). It then updates the custom parameter file to replace the existing ORAR1213 references to our sandbox folder.<br />
<br />
<h2>
Truncate Tables</h2>
If you had supplied the truncate table option, the script will do some additional steps.<br />
<br />
First it will determine the target tables by querying the Informatica repository for your workflow. It does this with the following SQL:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>select distinct opb_targ.target_name
from opb_subject join opb_task on opb_subject.subj_id = opb_task.subject_id
join (
select workflow_id, instance_id, task_id, task_type, instance_name, max (version_number)
from opb_task_inst sess
where sess.task_type = 68
group by workflow_id, instance_id, task_id, task_type, instance_name
) tasks on opb_task.task_id = tasks.workflow_id
join (
select session_id, mapping_id, max (version_number)
from opb_session
group by session_id, mapping_id
) sessions on tasks.task_id = sessions.session_id
join opb_mapping on sessions.mapping_id = opb_mapping.mapping_id
join opb_widget_inst on opb_mapping.mapping_id = opb_widget_inst.mapping_id
join opb_targ on opb_widget_inst.widget_id = opb_targ.target_id
where opb_task.is_visible = 1
and opb_task.task_type = 71
and opb_mapping.is_visible = 1
and opb_targ.is_visible = 1
and opb_widget_inst.version_number = (select max(owi.version_number) from opb_widget_inst owi where owi.mapping_id = opb_mapping.mapping_id)
and opb_subject.subj_name = '$SANDBOX'
and opb_task.task_name = '$WORKFLOW_NAME';</code></pre>
<br />
The script then uses this target table list to:<br />
<br />
<ul>
<li>Truncate the list of tables.</li>
<li>Determine if there are any indexes on the list of tables, and if there are:</li>
<ul>
<li>Extract the DDL for said indxes into a script</li>
<li>Drop the said indexes</li>
</ul>
</ul>
<div>
Next, the script executes pmcmd to run the workflow. It will then pause and wait until the workflow completes.</div>
<div>
<br /></div>
<div>
Finally, if you had previously truncated the table, it will run the DDL to reapply indexes.</div>
<div>
<br /></div>
<div>
You should be able to monitor the run using Workflow Monitor, and then query the tables when it completes to do data validation.</div>
</div>
</div>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com0tag:blogger.com,1999:blog-617408063302769488.post-10364284676210637102013-05-24T13:59:00.002-07:002013-09-26T10:28:26.014-07:00Setting up SSL for Weblogic<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE_7obwKHL9EPFsP0_sPLTq5S3t9mxk_awX-SvwJZ2t0esGdp19_YiHC98LwKjnqJSBu3P6D7Uw_o1G-0ZL26xpwdCFSO5bflQHR9zVmS4cBNtsi7chx8m1tYl1Pxnk_zhh55ZnIEi3G4/s1600/Oracle-Weblogic.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="100" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE_7obwKHL9EPFsP0_sPLTq5S3t9mxk_awX-SvwJZ2t0esGdp19_YiHC98LwKjnqJSBu3P6D7Uw_o1G-0ZL26xpwdCFSO5bflQHR9zVmS4cBNtsi7chx8m1tYl1Pxnk_zhh55ZnIEi3G4/s200/Oracle-Weblogic.jpg" width="100" /></a></div>
We recently needed to setup SSL for OBIEE so that we could accept the EBS cookie for single sign on between EBS and OBIEE (post to come soon on how to get that all wired up). Unfortunatly I don't have all of the steps, as someone else on the team handles the making of the actual cert, but I can provide all steps around the process.<br />
<br />
Continue on to read what I did to create a private key, create a certificate request (CSR), and configure WebLogic to enable the SSL port for OBIEE.<br />
<a href="http://www.blogger.com/blogger.g?blogID=617408063302769488" name="more"></a><br />
<a name='more'></a><div>
<ol>
<li>Log into the server you want to setup SSL on with Putty and run the following commands:</li>
<br />
<ol>
<li>openssl genrsa -des3 -rand file1:file2:file3:file4:file5 -out <serverhost>.key 2048</li>
<ul>
<li>Replace file1, file2, etc with random files on the server.</li>
<li>Set passphrase to: <b>welcome1 </b>(or any value you like)</li>
</ul>
<ul>
</ul>
<li>openssl req -new -key <serverhost>.key -out <serverhost>.csr</li>
<ol>
<li>Enter passphrase: <b>welcome1</b></li>
<li>Answers to the Questions:</li>
<ul>
<li>Country Name (2 letter code) [GB]: <b>US</b></li>
<li>State or Province Name (full name) [Berkshire]: <b>California</b></li>
<li>Locality Name (eg, city) [Newbury]: <b>Sacramento</b></li>
<li>Organization Name (eg, company) [My Company Ltd]: <b>My Corp</b></li>
<li>Organizational Unit Name (eg, section) []: <b>OBIEE</b></li>
<li>Common Name (eg, your name or your server's hostname) []:<b><serverhost>.blah.com</b></li>
<li>Email Address []: <b>something@aol.com</b></li>
<br />
<li>Please enter the following 'extra' attributes to be sent with your certificate request</li>
<li>A challenge password []: <b>welcome1</b></li>
<li>An optional company name []: <b>My Corp</b></li>
</ul>
</ol>
</ol>
<br />
<li>Send your CSR to whomever creates certificates for your company. They will return to you a CRT file. This, along with the key you created earlier, will be used to create a java keystore which is what you will configure in WebLogic.</li>
<li>Back in Putty, enter the following commands:</li>
<ol>
<li>cd <OBIEE_HOME>/wlserver_10.3/server/lib</li>
<li>openssl pkcs12 -export -in ~/path/to/<serverhost>.cer -inkey ~/path/to/<serverhost>.key > ~/path/to/<serverhost>.p12</li>
<ul>
<li>Enter the following for the passphrase and export password: <b>welcome1</b></li>
</ul>
<li><OBIEE_HOME>/Oracle_BI1/jdk/bin/keytool -importkeystore -srckeystore ~/path/to/<serverhost>.p12 -destkeystore <serverhost>Trust.jks -srcstoretype pkcs12</li>
<ul>
<li>Enter the following for the destination keystore password: <b>mykeystore</b></li>
<li>Enter the following for the source keystore password: <b>welcome1</b></li>
</ul>
</ol>
<li>Log into Weblogic Console</li>
<ul>
<li>http://<serverhost>:7001/console</li>
</ul>
<li>Navigate to Environment->Servers->bi_server1</li>
<li>Click the Lock and Edit button.</li>
<li>Check the SSL Listen Port Enabled box.</li>
<li>Set the port to 9804.</li>
<li>Click Save.</li>
<li>Click on the tab for Keystores</li>
<li>Click the Change button next to Demo Identity and Demo Trust</li>
<li>Select Custom Identity and Java Standard Trust</li>
<li>Click Save.</li>
<li>For Custom Identity Keystore enter:</li>
<ul>
<li><OBIEE_HOME>/wlserver_10.3/server/lib/<serverhost>Trust.jks</li>
</ul>
<li>For Custom Identity Keystore Type enter:</li>
<ul>
<li>jks</li>
</ul>
<li>For Custom Identity Keystore Passphrase and Confirm Custom Identity Keystore Passphrase enter:</li>
<ul>
<li><b>mykeystore</b></li>
</ul>
<li>For Java Standard Trust Keystore Passphrase and Confirm Java Standard Trust Keystore Passphrase enter:</li>
<ul>
<li><b>changeit</b></li>
<li><b>NOTE: This is the default password for the Java Standard Trust Keystore. If you have changed it, which you should, update this passphrase to your new Java Standard Trust Keystore password.</b></li>
</ul>
<li>Click Save.</li>
<li>Click the SSL tab.</li>
<li>For Private Key Alias enter</li>
<ul>
<li><b>1</b></li>
</ul>
<li>For Private Key Passphrase and Confirm Private Key Passphrase enter:</li>
<ul>
<li><b>welcome1</b></li>
</ul>
<li>Click Save.</li>
<li>Click Activate Changes.</li>
<li>The process will take a few moments, but will let you know once it has completed.</li>
<li>Now go back to Putty to restart all services (Weblogic, EM, AdminServer, etc).</li>
</ol>
</div>
<div>
<br />
You should now be able to connect to https://<serverhost>:9804/analytics<br />
<br />
SSL Websites for reference<br />
<br />
<ul>
<li>http://slacksite.com/apache/certificate.php</li>
<li>http://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html</li>
<li>http://www.sslshopper.com/article-most-common-openssl-commands.html</li>
</ul>
</div>
<ul></ul>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com0tag:blogger.com,1999:blog-617408063302769488.post-25358070799784822302013-05-23T15:42:00.002-07:002013-05-24T13:38:38.759-07:00Missing "Treat Text As" options in OBIEE 11gSomething I used to use back in OBIEE 10g was the Override Default Data Format for text to do things with HTML in my Answers Requests. I had assumed this would be available the same way out of the box with 11g, but alas it is not.<br />
<br />
<table><tbody>
<tr>
<td>What I get:</td>
<td>What I want:</td>
</tr>
<tr>
<td><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-5EuOG-WOPgTzzb5mAnozoApCF459jWNp_po0PDAk9bV4D6DcNY2RVNvrTAgFTxAGWxf1t5pdrXiJCj4x52sVGSythWGkj8xjcjlf7HH2yh2PXr5QxB7hh_fT4nttE7q4kmcAgRRiWus/s1600/fewchoices.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="247" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-5EuOG-WOPgTzzb5mAnozoApCF459jWNp_po0PDAk9bV4D6DcNY2RVNvrTAgFTxAGWxf1t5pdrXiJCj4x52sVGSythWGkj8xjcjlf7HH2yh2PXr5QxB7hh_fT4nttE7q4kmcAgRRiWus/s320/fewchoices.PNG" width="290" /></a></div>
</td>
<td><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCtxeKFyHISK7tZmaBaLYu6CVly5jo_Z9IwLpwFrK9_biJ4QXFpnfFJD1a9qJdJ4DAVpSeZEtKIuzsrCsbH_ATWHRctH5PQSdMWYDByH93KpHZsYJcSQsf3TIcUFSCsxv1ee95MaxzVqw/s1600/allchoices.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="247" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCtxeKFyHISK7tZmaBaLYu6CVly5jo_Z9IwLpwFrK9_biJ4QXFpnfFJD1a9qJdJ4DAVpSeZEtKIuzsrCsbH_ATWHRctH5PQSdMWYDByH93KpHZsYJcSQsf3TIcUFSCsxv1ee95MaxzVqw/s320/allchoices.jpg" width="290" /></a></div>
</td>
</tr>
</tbody></table>
<br />
<br />
So as always, I did a little digging, and found that in order to have all options show, you need to have the privilege: <b>Save System-Wide Column Formats</b>. What's even better is that out of the box, this privilege is assigned to no one, so you can't even set it!<br />
<br />
Continue on to read what I did to allow me to enable the setting for BI Administrators, and finally gain access to all the treat text as options.<br />
<a href="http://www.blogger.com/blogger.g?blogID=617408063302769488" name="more"></a><br />
<a name='more'></a><br />
<div>
First things first, a word of warning, Save System-Wide Column Formats can be very powerful. You more than likely do not want to grant this privilege to most users, as they could potential make system level changes.<br />
<br />
With that said, here are the steps I took to enable the option. Note that you might see some other blogs or posts that state you need to add a HardenXSS tag to your instanceconfig file. This is not the case, as this tag was deprecated by Oracle with 11.1.1.5. If you add this tag, your OBIEE Presentation Services will fail to start.<br />
<br />
<ol>
<li>Open the Web Catalog in offline mode.</li>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfheePG2mcBY_pTgE0D-IVY7c5ISdXLkTOfbDchS-eCLRRDbi0oy5pPP0UnHKaaPmFORIEGqsIvr0x1qvHRdLpu_nWF6Ark5vfq6waAVZEURB5fVToKJ_lCgY8CURpAWvpKXc6Mo-eKW0/s1600/openwebcat.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfheePG2mcBY_pTgE0D-IVY7c5ISdXLkTOfbDchS-eCLRRDbi0oy5pPP0UnHKaaPmFORIEGqsIvr0x1qvHRdLpu_nWF6Ark5vfq6waAVZEURB5fVToKJ_lCgY8CURpAWvpKXc6Mo-eKW0/s320/openwebcat.PNG" width="320" /></a></div>
<br />
<li>Navigate to the following folder:</li>
<br />System -> Privs ->
FormatSystemPrivs<br /><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTaiW3U-2MbhSrfpLcbr8yBFAtRkXT_O4zv9vL9KpS7NLBScXymnt_Rx8_YtDEypLywp3cPGm62WwWI8Oetsjbg9C5LXU84MdY-MFG5PIAtFfFBL1WzO7gO1VMlrVxUeWP6ERqQ7WJN2Y/s1600/formatsystemprivs.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTaiW3U-2MbhSrfpLcbr8yBFAtRkXT_O4zv9vL9KpS7NLBScXymnt_Rx8_YtDEypLywp3cPGm62WwWI8Oetsjbg9C5LXU84MdY-MFG5PIAtFfFBL1WzO7gO1VMlrVxUeWP6ERqQ7WJN2Y/s320/formatsystemprivs.PNG" width="320" /></a></div>
<br />
<li>Double click on the item 'Edit Formats'</li>
<li>Click on 'Edit Privs'</li>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkk0MLZE0NUbTwRKWpuEv8-v5BWgbNzfJv3tbQd7pf9CKHK3XgAGvaXlzihg95YC4H6FYpbG5hlk5u_zLSitUihtFHsFFVPvOpTrwQTRkyGO1EaB2dQM7zWzVZiEiSkIEPyL8ljWPpWEs/s1600/properties.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkk0MLZE0NUbTwRKWpuEv8-v5BWgbNzfJv3tbQd7pf9CKHK3XgAGvaXlzihg95YC4H6FYpbG5hlk5u_zLSitUihtFHsFFVPvOpTrwQTRkyGO1EaB2dQM7zWzVZiEiSkIEPyL8ljWPpWEs/s320/properties.PNG" width="292" /></a></div>
<li>Choose the roles that should have this privilege (ex: biadministrator)</li>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4w3KqySQXGxtzemIzrf2N2c3rQk-QMj2GWK03AtvEcV2EZqABSDYS1ycMKz3jxRcJVeMjgrr2xbGhivlK-DVWR86jAI2Da_Pgln_mKf03ZW_jEisqEw7zb3l4iKna8UMP_7YvNEMCPkM/s1600/addpriv.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="152" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4w3KqySQXGxtzemIzrf2N2c3rQk-QMj2GWK03AtvEcV2EZqABSDYS1ycMKz3jxRcJVeMjgrr2xbGhivlK-DVWR86jAI2Da_Pgln_mKf03ZW_jEisqEw7zb3l4iKna8UMP_7YvNEMCPkM/s320/addpriv.PNG" width="320" /></a></div>
<br />
<li>Click Ok.</li>
<li>Click Ok again.</li>
<li>Redeploy the modified Web Catalog, and restart the presentation services.</li>
</ol>
Now, when logging into OBIEE as a user with BI Administrator role, you will see the menu now has all options.<br />
<br />
Have fun!</div>
<ul></ul>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com0tag:blogger.com,1999:blog-617408063302769488.post-36310128222114240052013-03-21T22:21:00.003-07:002013-08-14T21:22:49.310-07:00Fix "Configuration Error" on 64-bit OBIEE 11.1.1.6.5 Client ToolsEver since upgrading to 11.1.1.6.5, I was unable to use the 64bit version of the newly installed client tools. The program would always error out on launch. Oddly enough, if I installed the 32bit version, things would work *mostly* fine, well at least no errors on start-up. But then I had to update the 32bit ODBC entries, and it overall was just a hassle to manage.<br />
<br />
So I did a little digging, and found that because it installs into Program Files, it creates it's own bi_init.bat file, which sets up it's own ORACLE_HOME, ORACLE_INSTANCE, etc.<br />
<br />
And guess what, this doesn't work!<br />
<br />
Continue on to read what I did to change both the bi_init.bat and adjust some of my environment variables.<br />
<a href="http://www.blogger.com/blogger.g?blogID=617408063302769488" name="more"></a><br />
<a name='more'></a><br />
<div>
First things first, navigate to where you installed the 64 bit client tools. For me this was:<br />
<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><span style="font-family: 'Times New Roman'; white-space: normal;">C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client</span></pre>
<div>
<br /></div>
<br />
Now, navigate to where the bi_init.bat file is stored:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><span style="font-family: 'Times New Roman'; white-space: normal;">oraclebi\orahome\bifoundation\server\bin</span></pre>
<br />
Open up the file bi_init.bat in TextPad, Notpad++, or your favorite editor. Replace the contents with:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>set ORACLE_HOME=C:\ofmw\obiee11g\Oracle_BI1
set ORACLE_INSTANCE=C:\ofmw\obiee11g\instances\instance1
set ORACLE_BI_APPLICATION=coreapplication
set JAVA_HOME=C:\ofmw\obiee11g\Oracle_BI1\jdk
set ESSBASEPATH=%ORACLE_HOME%\clients\epm\Essbase\EssbaseRTC
call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd
if NOT "%1" == "" </code>set COMPONENT_NAME=%1
set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\jre\bin;%windir%;%windir%\system32;%PATH%
if NOT "%2" == "" %2</pre>
</div>
<br />
<b><i>Note:</i></b> You will need to change the location to your fusion middleware directory. For me this is:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>C:\ofmw\obiee11g</code></pre>
<br />
<div>
Change this to where ever your fusion middleware directory is.<br />
<br />
At this point, you should be able to launch the new 64-bit 11.1.1.6.5 client tools from the shortcut in your Start menu.<br />
<br />
If you want to be able to launch when double clicking on an RPD, without having to first open the Admin Tool, all you need to do is set your environment variables as they are configured in the script. If you don't already have the following, add them as environment variables:<br />
<br />
ORACLE_HOME<br />
ORACLE_INSTANCE<br />
ORACLE_BI_APPLICATION<br />
<br />
and set them to the same values that you have referenced in the script above.<br />
<br />
<b><i>Note</i></b>: By changing the environment variables, you are impacting all programs on your system. Part of the reason they are locally defined in a script before the launch of the Admin Tool is that those variables are only set for the program launched after the script. <b><i>Always be cautious while changing environment variables.</i></b></div>
<ul></ul>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com0tag:blogger.com,1999:blog-617408063302769488.post-88797004683131260892013-03-07T12:56:00.001-08:002013-03-22T21:52:01.656-07:00Quickly open OBIEE Admin Client in Online ModeSomething I have found myself doing quite often is checking who is connected in OBIEE prior to doing a deployment. The quickest/easiest means I have found is to fire up the Admin Client, and look at the sessions. This allows me to know if I need to notify anyone (and who), prior to taking down the server.<br />
<br />
What was always a pain was to go through the multiple steps to open the client, choose Open in Online mode, and select my connection, across multiple environments. Luckily this can be scripted!<br />
<br />
Continue on to read the steps to quickly script opening the Admin Client directly in Online mode.<br />
<a name='more'></a><div>
The Admin Client has a command line interface, but it is undocumented. Essentially what I did was create a Windows BAT file with the following:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>start C:\ofmw\obiee11g\Oracle_BI1\bifoundation\server\bin\admintool.exe /command C:\ofmw\scripts\dev.txt</code></pre>
<br />
All you need to change is the path to your Admin Client and the path to the script. <b><i>Note </i></b>that you have to specify a script with the <b>/command</b> argument, you can't just type in the below code into the BAT file.<br />
<br />
The script it calls just looks like:<br />
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>OpenOnline "DEV OBIEE" biadmin oracle01 Admin123</code></pre>
<br />
There are 5 parameters:<br />
<br />
<ul>
<li>OpenOnline - This tells the Admin Client to open a connection in Online Mode.</li>
<li>"DEV OBIEE" - This is the ODBC name of the server I want to connect to.</li>
<li>biadmin - This is the weblogic admin username.</li>
<li>oracle01 - This is the weblogic admin password.</li>
<li>Admin123 - This is the repository password.</li>
</ul>
<div>
<br /></div>
</div>
<ul>
</ul>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com1tag:blogger.com,1999:blog-617408063302769488.post-43996172552138010702013-02-24T16:23:00.001-08:002013-08-14T21:22:49.312-07:00How to Enable Usage Tracking on OBIEE 11g (11.1.1.6.5)A usual requirement is to enable usage tracking to gather statistics about usage of OBIEE, things like performance, which Dashboards/Reports are being utilized, who is logging in, etc. Out of the box, Oracle includes a robust usage tracking capability with OBIEE, and in fact includes a sample RPD with the Usage Tracking Subject area during install.<br />
<br />
There are three main components:<br />
<br />
<ul>
<li>Changes/configuration in the RPD</li>
<li>Configuration in Enterprise Manager</li>
<li>SQL to run to setup Usage Tracking schema.</li>
</ul>
<br />
Continue on to read the steps to enable Usage Tracking.<br />
<a name='more'></a><div>
<br />
<h2>
RPD Changes</h2>
The sample RPD can be found at:<br />
<br />
{OBIEE_11G_Instance}/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking<br />
<br />
You will need to copy the Physical, BMM, and Presentation components from the sample UsageTracking.rpd to your development/test/production RPD. Note that I had to make a few changes to the included RPD. If the UsageTracking subject area is not as OBIEE is expecting, then OBIEE will fail to update the table with usage details, and give an error message in the nqserver.log.<br />
<ul>
<li>Open the RPD with the Admin Tool.</li>
<li>Expand the S_NQ_ACCT table in the physical layer and check that all of the following fields exist with the correct data type.</li>
<table style="width: 100%px;"><tbody>
<tr><td align="left"><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPYyZHTp4WBAH2CKd0CMZTGIJ2mktLimG5O6esan3qXVp8SEChkKosQ7utddaRYPRL1SXpjhkqpdjcCJVtnkBG6BfGAbSQOa-T0UVyAsQapjtk_aMWsCWQCV2R5ElSsUmhDZsPk_Qg2go/s1600/aStep-1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPYyZHTp4WBAH2CKd0CMZTGIJ2mktLimG5O6esan3qXVp8SEChkKosQ7utddaRYPRL1SXpjhkqpdjcCJVtnkBG6BfGAbSQOa-T0UVyAsQapjtk_aMWsCWQCV2R5ElSsUmhDZsPk_Qg2go/s320/aStep-1.PNG" /></a></div>
</td><td align="right"><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaz5t_-_xF82vV78NcQij1WP7y8CXD2RZ9J97BvcWKehA0yaWCqUbQIumjYJ-6iR25Z8IMxSAc5Bu6-YLzAfXvw_IM4hUQmeRpjcd628oKh7F6Ta-k6w4SAhM7GgAAARD8GWM5NvgCRB4/s1600/aStep-2.PNG" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaz5t_-_xF82vV78NcQij1WP7y8CXD2RZ9J97BvcWKehA0yaWCqUbQIumjYJ-6iR25Z8IMxSAc5Bu6-YLzAfXvw_IM4hUQmeRpjcd628oKh7F6Ta-k6w4SAhM7GgAAARD8GWM5NvgCRB4/s320/aStep-2.PNG" /></a></div>
</td></tr>
</tbody></table>
<br />
<li>Rename RUNAS_USER_NAME to IMPERSONATOR_USER_NAME</li>
<li>Add the following missing fields (right click on S_NQ_ACCT, select <b>New Object</b> -> <b>Physical Column…</b>)</li>
<ul>
<li>ID</li>
<ul>
<li>Type: VARCHAR</li>
<li>Length: 50</li>
<li>Nullable: Unchecked</li>
</ul>
<li>QUERY_BLOB</li>
<ul>
<li>Type: LONGVARCHAR</li>
<li>Length: 4000</li>
<li>Nullable: Checked</li>
</ul>
<li>QUERY_KEY</li>
<ul>
<li>Type: VARCHAR</li>
<li>Length: 128</li>
<li>Nullable: Checked</li>
</ul>
</ul>
<li>Below is a list of all of the required fields, data type, length, and nullable flag:</li>
</ul>
<center>
<table style="border-style: groove;">
<tbody>
<tr><td><b>Field Name</b></td><td><b>Data Type</b></td><td><b>Length</b></td><td><b>Nullable?</b></td></tr>
<tr><td>CACHE_IND_FLG</td><td>CHAR</td><td>1</td><td>No</td></tr>
<tr><td>COMPILE_TIME_SEC</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>CUM_DB_TIME_SEC</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>CUM_NUM_DB_ROW</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>END_DT</td><td>DATETIME</td><td></td><td>Yes</td></tr>
<tr><td>END_HOUR_MIN</td><td>CHAR</td><td>5</td><td>Yes</td></tr>
<tr><td>END_TS</td><td>DATETIME</td><td></td><td>Yes</td></tr>
<tr><td>ERROR_TEXT</td><td>VARCHAR</td><td>250</td><td>Yes</td></tr>
<tr><td>ID</td><td>VARCHAR</td><td>50</td><td>No</td></tr>
<tr><td>IMPERSONATOR_USER_NAME</td><td>VARCHAR</td><td>128</td><td>Yes</td></tr>
<tr><td>NODE_ID</td><td>VARCHAR</td><td>15</td><td>Yes</td></tr>
<tr><td>NUM_CACHE_HITS</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>NUM_CACHE_INSERTED</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>NUM_DB_QUERY</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>PRESENTATION_NAME</td><td>VARCHAR</td><td>128</td><td>Yes</td></tr>
<tr><td>QUERY_BLOB</td><td>LONGVARCHAR</td><td>4000</td><td>Yes</td></tr>
<tr><td>QUERY_KEY</td><td>VARCHAR</td><td>128</td><td>Yes</td></tr>
<tr><td>QUERY_SRC_CD</td><td>VARCHAR</td><td>30</td><td>Yes</td></tr>
<tr><td>QUERY_TEXT</td><td>VARCHAR</td><td>1024</td><td>Yes</td></tr>
<tr><td>REPOSITORY_NAME</td><td>VARCHAR</td><td>128</td><td>Yes</td></tr>
<tr><td>ROW_COUNT</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>SAW_DASHBOARD</td><td>VARCHAR</td><td>150</td><td>Yes</td></tr>
<tr><td>SAW_DASHBOARD_PG</td><td>VARCHAR</td><td>150</td><td>Yes</td></tr>
<tr><td>SAW_SRC_PATH</td><td>VARCHAR</td><td>250</td><td>Yes</td></tr>
<tr><td>START_DT</td><td>DATETIME</td><td></td><td>Yes</td></tr>
<tr><td>START_HOUR_MIN</td><td>CHAR</td><td>5</td><td>Yes</td></tr>
<tr><td>START_TS</td><td>DATETIME</td><td></td><td>Yes</td></tr>
<tr><td>SUBJECT_AREA_NAME</td><td>VARCHAR</td><td>128</td><td>Yes</td></tr>
<tr><td>SUCCESS_FLG</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>TOTAL_TIME_SEC</td><td>DOUBLE</td><td></td><td>Yes</td></tr>
<tr><td>USER_NAME</td><td>VARCHAR</td><td>128</td><td>Yes</td></tr>
</tbody></table>
</center>
</div>
<ul>
<li>The join between S_NQ_ACCT and NQ_LOGIN_GROUP is broken. Select both tables, right click and choose <b>Phyiscal Diagram</b> -> <b>Selcted Object(s) Only.</b></li>
<li>Select the red arrow, and click Delete to remove the broken join.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje1Gu-K6sjUhGudDP-hlc138O-eT5UkSAWaoDSDIAjgk0ILWo5ZuDL5BvO8J4L4ba9XEvLbgInw54lth07eAQWtn0DiGFLp-cv8K2RQ5hvqssEI75oC_q8pchy13U2FKTOsdd9jcuJsME/s1600/aStep-3.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje1Gu-K6sjUhGudDP-hlc138O-eT5UkSAWaoDSDIAjgk0ILWo5ZuDL5BvO8J4L4ba9XEvLbgInw54lth07eAQWtn0DiGFLp-cv8K2RQ5hvqssEI75oC_q8pchy13U2FKTOsdd9jcuJsME/s320/aStep-3.PNG" /></a></div>
<br />
<li>Now click on the new join icon</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguci-lmF7MJb_0DkpTQX3qUKL7sRJeknkOs32_b95iKAElu1taRkpBqJXCOnVtZ4f-GhGZk4A4ZrHDv6UGAcLpiYXZbCUhhcV87CkPd2sb8Q4shQty3BoSS1XAM0GiPCsBRBd1gTwVWgw/s1600/aStep-4.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguci-lmF7MJb_0DkpTQX3qUKL7sRJeknkOs32_b95iKAElu1taRkpBqJXCOnVtZ4f-GhGZk4A4ZrHDv6UGAcLpiYXZbCUhhcV87CkPd2sb8Q4shQty3BoSS1XAM0GiPCsBRBd1gTwVWgw/s320/aStep-4.PNG" /></a></div>
<br />
<li>Draw a new join from S_NQ_ACCT to NQ_LOGIN_GROUP and change the join condition to:</li>
<li>"OBI Usage Tracking"."dbo"."NQ_LOGIN_GROUP"."LOGIN" = "OBI Usage Tracking"."dbo"."S_NQ_ACCT"."USER_NAME"</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdizEIJ5ITB1u7zjNVfPrQL6EZe5xr05ItRxIeWcPjbpYJ8vcPzKYiXYD9wAg2mu1U5oQhb92-WC-QRYZ_KYrezbVgQEQ5kzCxnfFTQa-aF1bnEhFB7JIE8EWEb95Puqp7ejZ_ZqptsFs/s1600/aStep-5.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdizEIJ5ITB1u7zjNVfPrQL6EZe5xr05ItRxIeWcPjbpYJ8vcPzKYiXYD9wAg2mu1U5oQhb92-WC-QRYZ_KYrezbVgQEQ5kzCxnfFTQa-aF1bnEhFB7JIE8EWEb95Puqp7ejZ_ZqptsFs/s320/aStep-5.PNG" /></a></div>
<br />
<li>Click <b>Ok</b>.</li>
<li>Close the physical diagram window.</li>
<li>Now right click on the table S_NQ_ACCT and select properties.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4Uzf-xQ0ojtkBHLTP5veyH9BUZkgHV9Y-S-Wb0HlH_uHoEV9yn12D0dRC2GkH3riW3QNJSWLNq8GO-0lhmMH4OQ0lfe1Ws_3A56lgC0Q3meaersBrXwKph-5kSZYfwxE87AOHCMQhgFA/s1600/aStep-6.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4Uzf-xQ0ojtkBHLTP5veyH9BUZkgHV9Y-S-Wb0HlH_uHoEV9yn12D0dRC2GkH3riW3QNJSWLNq8GO-0lhmMH4OQ0lfe1Ws_3A56lgC0Q3meaersBrXwKph-5kSZYfwxE87AOHCMQhgFA/s320/aStep-6.PNG" /></a></div>
<br />
<li>Select the <b>Keys </b>tab.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJjibSm6Fz0-LVkrTecH8Rbm3fdd2TdqCxK6VrCFwT4qMkfmky3p03FPgJGzwf2l4lDP8cFMSrcAhY6EpWYbV3Rph8d1U9cYJT21Rk1Dc1eYE-bTNYRkAUIQJDnsqWznRo8I93kSO3vxo/s1600/aStep-7.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJjibSm6Fz0-LVkrTecH8Rbm3fdd2TdqCxK6VrCFwT4qMkfmky3p03FPgJGzwf2l4lDP8cFMSrcAhY6EpWYbV3Rph8d1U9cYJT21Rk1Dc1eYE-bTNYRkAUIQJDnsqWznRo8I93kSO3vxo/s320/aStep-7.PNG" /></a></div>
<br />
<li>If there is no key defined, enter <b>ID </b>in Key name and select the <b>ID </b>field from the drop down in the Columns field.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix9xmhulox_DxjtqYpCWJ9yNj0N-f7Ba2tfTL9euMV1kPHDaX1e4tl4cnf8hvy666BMxvyFnfREqluuhv4VWUOlqXQFvQzWjEY4AtdDszfWDB6ejmSktyzX58wbc9lt1mL9WjbzJvd62U/s1600/aStep-8.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix9xmhulox_DxjtqYpCWJ9yNj0N-f7Ba2tfTL9euMV1kPHDaX1e4tl4cnf8hvy666BMxvyFnfREqluuhv4VWUOlqXQFvQzWjEY4AtdDszfWDB6ejmSktyzX58wbc9lt1mL9WjbzJvd62U/s320/aStep-8.PNG" /></a></div>
<br />
<li>Click <b>Ok</b>.</li>
<li>Don't forget to setup proper Connection Pool information for both connection pools:</li>
<ul>
<li>Usage Tracking Connection Pool</li>
<li>Usage Tracking Writer Connection Pool</li>
</ul>
</ul>
<br />
<h2>
Enterprise Manager Configuration</h2>
<div>
The following outlines the steps needed within Enterprise Manager to configure and start Usage Tracking.</div>
<ul>
<li>Navigate to the Enterprise Manager (EM) of the host you are deploying to.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRMdsq0s9RDN4TUzJS9SvDo0EE_iuOCNaeWyALX9NCpiVvS8Vim79zG_O_Okqp1a1wpHOKygiJ-233DvPFYTPtw9ONcqX3_HeYCO0LuzFVCVI2KhLoWIEI-9lfPTHQFZxEdd1Ej8KMvEI/s1600/Step1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="178" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRMdsq0s9RDN4TUzJS9SvDo0EE_iuOCNaeWyALX9NCpiVvS8Vim79zG_O_Okqp1a1wpHOKygiJ-233DvPFYTPtw9ONcqX3_HeYCO0LuzFVCVI2KhLoWIEI-9lfPTHQFZxEdd1Ej8KMvEI/s320/Step1.PNG" width="320" /></a></div>
<br />
<li>Login with the admin credentials (biadmin/oracle01)</li>
<li>Navigate to the Mbeans by expanding WebLogic Domain, selecting bifoundation_domain, and then from the drop down chooses System MBean Browser.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZX4Xvn-P4mtlqpjrIGR8lBm7ozrboDVh7OWLSsfrApMorRQ_ZS0tOgJg8WFShacFphzJ0jjqH83nOepEAvyW3U1TpcPBHDtxhA1ER5DmmfxbRs7wOfTgX5Y1d3YBca0CkFEtqGXDVBDY/s1600/Step2.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZX4Xvn-P4mtlqpjrIGR8lBm7ozrboDVh7OWLSsfrApMorRQ_ZS0tOgJg8WFShacFphzJ0jjqH83nOepEAvyW3U1TpcPBHDtxhA1ER5DmmfxbRs7wOfTgX5Y1d3YBca0CkFEtqGXDVBDY/s320/Step2.PNG" /></a></div>
<br />
<li>Lock the Mbeans by navigating to:</li>
<ul>
<li>Application Defined MBeans->oracle.biee.admin->Domain: bifoundation_domain->BIDomain->BIDomain (the second one of the two)</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijiFVb8T6_01OSHGrFDLDae32Uzi5dHkTZIhCh1Yjh5HEdvoaj0o5xLcLTPJszEIruItPehcQ2HQnrCa95lWFmZaQ5qj8EeaKrMjjjeAXjo1OwkwLMsKQzkHs2p3ORTlO_-xdJPtMuJqA/s1600/Step3.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijiFVb8T6_01OSHGrFDLDae32Uzi5dHkTZIhCh1Yjh5HEdvoaj0o5xLcLTPJszEIruItPehcQ2HQnrCa95lWFmZaQ5qj8EeaKrMjjjeAXjo1OwkwLMsKQzkHs2p3ORTlO_-xdJPtMuJqA/s320/Step3.PNG" /></a></div>
<br />
</ul>
<li>Click on the Operations tab.</li>
<li>Click on lock.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNmvglfCKU8fpeoANLFYUhK4GhsofM0nIJVDJ_cl82A81jGWE0WCcycTY6cs3uM9ggAOLMYyzjzE22_H0rq55OncavM5UG9oqGVJc9FZ1PGVT9UUOQPSxTUE1DnL6xxVPYsBiHQCMFmME/s1600/Step4.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNmvglfCKU8fpeoANLFYUhK4GhsofM0nIJVDJ_cl82A81jGWE0WCcycTY6cs3uM9ggAOLMYyzjzE22_H0rq55OncavM5UG9oqGVJc9FZ1PGVT9UUOQPSxTUE1DnL6xxVPYsBiHQCMFmME/s320/Step4.PNG" /></a></div>
<br />
<li>Click on Invoke.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2BAdPunIdQEgnehK4D-w6HfDxbxZJbifuLLHinaDYBq_GIiRbmz64WKbUU9Ud6sj5-XLxvrd5cGm-os4r0TRequj-1jXA70XCiTJ8q_0wcbv_LiBWRg5DGwdZPh9GFiR4Pucc8ZzsLDI/s1600/Step5.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2BAdPunIdQEgnehK4D-w6HfDxbxZJbifuLLHinaDYBq_GIiRbmz64WKbUU9Ud6sj5-XLxvrd5cGm-os4r0TRequj-1jXA70XCiTJ8q_0wcbv_LiBWRg5DGwdZPh9GFiR4Pucc8ZzsLDI/s320/Step5.PNG" /></a></div>
<br />
<li>Click on Return.</li>
<li>Navigate to the Usage Tracking mbeans:</li>
<ul>
<li>Application Defined MBeans->oracle.biee.admin->Domain: bifoundation_domain->BIDomain.BIInstance.ServerConfiguration->BIDomain.BIInstance.ServerConfiguration</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikObh3wIeR7oDOAgO9N_voOQlkccAu9rW38U0zdf8XEI24VuC-y_P0pFla8RLjvNPMY0KhIty70dhNflymuR7NWWmNK-AtqFPqYofrgkQ1rOAclVbQmeRNRJPQeb-jDFCv2Ek8h70q7TA/s1600/Step6.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikObh3wIeR7oDOAgO9N_voOQlkccAu9rW38U0zdf8XEI24VuC-y_P0pFla8RLjvNPMY0KhIty70dhNflymuR7NWWmNK-AtqFPqYofrgkQ1rOAclVbQmeRNRJPQeb-jDFCv2Ek8h70q7TA/s320/Step6.PNG" /></a></div>
<br />
</ul>
<li>Click on Attributes.</li>
<li>Set the following Attributes:</li>
<ul>
<li>UsageTrackingConnectionPool = "Oracle Analytics Usage"."Usage Tracking Writer Connection Pool"</li>
<li>UsageTrackingEnabled = True</li>
<li>UsageTrackingPhysicalTableName = "Oracle Analytics Usage"."dbo"."S_NQ_ACCT"</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpqJCNnVAIMaZOFDAWREfVEcS8KNk5904BAvM4G5zZpcxp0Rc6jOorp4JrU37K_rtPa94JAC6Ub25K8oRa57dKJWpnpZfn3NrOgFcofmeGavmwHVt4gAM_qDfD9y8v5yk8OcZ1PMpi-LU/s1600/Step7.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpqJCNnVAIMaZOFDAWREfVEcS8KNk5904BAvM4G5zZpcxp0Rc6jOorp4JrU37K_rtPa94JAC6Ub25K8oRa57dKJWpnpZfn3NrOgFcofmeGavmwHVt4gAM_qDfD9y8v5yk8OcZ1PMpi-LU/s320/Step7.PNG" /></a></div>
<br />
</ul>
<li>Click on Apply.</li>
<li>Commit the MBeans by navigating to:</li>
<ul>
<li>Application Defined MBeans->oracle.biee.admin->Domain: bifoundation_domain->BIDomain->BIDomain</li>
</ul>
<li>Click on the Operations tab.</li>
<li>Click on commit (the first option).</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpRheX9kq79gOrYwzS_q_Gj0oYsC-H8Wbv9HDPg_-PjXQL3eAtubqGxRBENqo1qEQy4HGMxOumQIAQv2HvBZZqKi3uIMHvcgiP7QkSYlbQQdxfx7iEYAySclgKojnKokPM-EFOdiB_Z0o/s1600/Step8.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpRheX9kq79gOrYwzS_q_Gj0oYsC-H8Wbv9HDPg_-PjXQL3eAtubqGxRBENqo1qEQy4HGMxOumQIAQv2HvBZZqKi3uIMHvcgiP7QkSYlbQQdxfx7iEYAySclgKojnKokPM-EFOdiB_Z0o/s320/Step8.PNG" /></a></div>
<br />
<li>Click on Invoke.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhy3i0zxw9-OJz7dV2hqVypsukpP8PMAlzf9DpdhgA2PCeYH-XrcnmNZ8RzUm9uon0bYb7faRTgwe8Ic4X3dSmR8_xZpRXQdVP0eZyqhCsflVm9hsGCfhu4Y7EWFmgE5P28R6zhuubtZx4/s1600/Step9.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhy3i0zxw9-OJz7dV2hqVypsukpP8PMAlzf9DpdhgA2PCeYH-XrcnmNZ8RzUm9uon0bYb7faRTgwe8Ic4X3dSmR8_xZpRXQdVP0eZyqhCsflVm9hsGCfhu4Y7EWFmgE5P28R6zhuubtZx4/s320/Step9.PNG" /></a></div>
<br />
<li>Click on Return.</li>
<li>Navigate back to the main EM page.</li>
<li>Under Business Intelligence, select coreapplicaiton.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZGHgDHZxJI89nMEZw6o4aVmjanTEN847eCAcGr79-j9sNENRmWjal8WFFYZUwYf7k3Hzv0b3lAvxjkUNmf8o86u5Chu3seTiWJNP8IA16vktXbsGiB7PLB8reSmEiZv5cLWR6VeUhwQQ/s1600/Step10.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZGHgDHZxJI89nMEZw6o4aVmjanTEN847eCAcGr79-j9sNENRmWjal8WFFYZUwYf7k3Hzv0b3lAvxjkUNmf8o86u5Chu3seTiWJNP8IA16vktXbsGiB7PLB8reSmEiZv5cLWR6VeUhwQQ/s320/Step10.PNG" /></a></div>
<br />
<li>Click on the Overview tab, if you are not already there.</li>
<li>Click on the Restart button.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwVGBsq2ENCxL712C3n93LDFlBHj5Mj-7TiFNz4KmyPHt0qUE3rKaTtQ7LE_Z-L0qGq0wbG5mQ797TJtlVrTfXm06IR-TY7_YZGKqsNqB6faynmSYz1BI2S5T_V8zUrxVUNiGRhd0T-8Q/s1600/Step11.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwVGBsq2ENCxL712C3n93LDFlBHj5Mj-7TiFNz4KmyPHt0qUE3rKaTtQ7LE_Z-L0qGq0wbG5mQ797TJtlVrTfXm06IR-TY7_YZGKqsNqB6faynmSYz1BI2S5T_V8zUrxVUNiGRhd0T-8Q/s320/Step11.PNG" /></a></div>
<br />
<li>When prompted to restart all components, click on Yes.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAtRA2U8sadsZbEwrLGrpu3CJmOif2y8Kvz0zalWUGaJ4TweUPvk2VgDFjFHh5RYODIFYo7nH2Uqmdb75ggn9EB66sBrSxwhyVVGXmSTXEPbTEIhKVaJSFL7UgZG6Sg1n22FM_av1wu2E/s1600/Step12.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAtRA2U8sadsZbEwrLGrpu3CJmOif2y8Kvz0zalWUGaJ4TweUPvk2VgDFjFHh5RYODIFYo7nH2Uqmdb75ggn9EB66sBrSxwhyVVGXmSTXEPbTEIhKVaJSFL7UgZG6Sg1n22FM_av1wu2E/s320/Step12.PNG" /></a></div>
<br />
<li>The process will take a few moments, but will let you know once it has completed.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUKyFstHkwRgFCBRyUrSVHih7awFJ9BTEj20HVt0WbPCzIXXuUeXPD_b_27mu2do_Cw0SXSNcsyP9AqL98niBEx9hyphenhyphenLPZpftuo8WM4yMou0mfCQFR6TzXg0lKY0Sj0T6xRz-ktC1-c4fI/s1600/Step13.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUKyFstHkwRgFCBRyUrSVHih7awFJ9BTEj20HVt0WbPCzIXXuUeXPD_b_27mu2do_Cw0SXSNcsyP9AqL98niBEx9hyphenhyphenLPZpftuo8WM4yMou0mfCQFR6TzXg0lKY0Sj0T6xRz-ktC1-c4fI/s320/Step13.PNG" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtzMoP4gLs6U0enxZkH5FJYKXMWBlZtZZHiz6huu2CHqs8rnN71i-8Purql3R80n2ZEr58rR3Re0XbLHGsLn21jSmi3MVTLfbrbRPI8Jj9Dc9f2FSsfLck7VJF2BK33oKr1zqr9GDDZTo/s1600/Step14.PNG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtzMoP4gLs6U0enxZkH5FJYKXMWBlZtZZHiz6huu2CHqs8rnN71i-8Purql3R80n2ZEr58rR3Re0XbLHGsLn21jSmi3MVTLfbrbRPI8Jj9Dc9f2FSsfLck7VJF2BK33oKr1zqr9GDDZTo/s320/Step14.PNG" /></a></div>
<br />
</ul>
<h2>
Schema Setup</h2>
<ul>
<li>Using TOAD (or SQLPLUS) connect to the BIPLATFORM schema (DEV_BIPLATFORM, or whatever you called it during installation) on the database which you configured for this OBIEE 11g instance.</li>
<li>Run the four Oracle scripts to setup Usage Tracking (found in SQL_Server_Time folder, where the sample RPD is included, see top of post):</li>
<ul>
<li><span style="background-color: white; color: #292929; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Oracle_create_nQ_Calendar.sql</span></li>
<li><span style="background-color: white; color: #292929; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Oracle_create_nQ_Clock.sql</span></li>
<li><span style="background-color: white; color: #292929; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Oracle_nQ_Calendar.sql</span></li>
<li><span style="background-color: white; color: #292929; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 15px; line-height: 20px;">Oracle_nQ_Clock.sql</span></li>
</ul>
</ul>
<ul>
<li>Remember to Commit!</li>
</ul>
<div>
At this point you should have everything wired up. If you log into OBIEE, and navigate a few dashboards, statistics should be gathered and recorded into the usage tracking tables within the BIPLATFORM schema.</div>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com6tag:blogger.com,1999:blog-617408063302769488.post-22726017511874668362013-02-24T16:05:00.005-08:002013-03-22T21:52:01.649-07:00Migrate Enterprise Manager Application Roles and Weblogic Users and Groups between environmentsManaging users across environments, when using the built-in Weblogic LDAP was a tedious process. What I have found to work well is create the users once, and then have a script which caries them across to the rest of the environments. This has been very useful in our Development and Test environments when getting the users seeded.<br />
<br />
The Enterprise Manager (EM) migration script utilizes a very cool java program that ArtofBI developed and posted to <a href="https://github.com/artofbi/Oracle-FMW-Amelia">Github</a>. I've tweaked the code a bit for my needs, so attached to this post also includes the modified javasource and complied JAR.<br />
<br />
Continue after the break for links to the files, and example scripts for both Enterprise Manager and Weblogic.<br />
<br />
<a name='more'></a><br /><br />
<table><tbody>
<tr>
<td><a href="https://docs.google.com/file/d/0B_shk9i3DvyhVWZzVXptUTUtb1U/edit?usp=sharing" target="_blank"><img border="0" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVNDFdI56RlcQqd2a1vq14X1B8aJmXndZWp8L9C47ox8MHddDRT2BirHuMFzL9pczwd4ph5-U7YbLzZLv9uwOEw_7Kv3BZhNWLY2Vr8ZhbjBLdPxv9DxFlL6n64YjyHRbcDiAyl_4BVnY/s320/jar.png" width="32" /></a></td>
<td><a href="https://docs.google.com/file/d/0B_shk9i3DvyhVWZzVXptUTUtb1U/edit?usp=sharing" target="_blank">OBIEE11g_Amelia.jar</a></td>
</tr>
<tr>
<td><a href="https://docs.google.com/file/d/0B_shk9i3Dvyhc2VMeXg0NUYwaFU/edit?usp=sharing"><img border="0" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7CrgqO8gX4oghEQxs_Z80WNr_hkIG3yy1PpqWHLKgKiDh_9ZyUbYtrAdE9GbO3lHrEpvgl4kK8hK1zucGxc-NkV8vTM_ysWR7A26LB-Ud8wGw5_q9qaLDRq5KgVuufhT1FWhRtyRkrFg/s320/java.png" width="32" /></a></td>
<td><a href="https://docs.google.com/file/d/0B_shk9i3Dvyhc2VMeXg0NUYwaFU/edit?usp=sharing">XMLSecurityParserManager.java</a></td>
</tr>
<tr>
<td><a href="https://docs.google.com/file/d/0B_shk9i3DvyhM3o1SlFxNXBBRkk/edit?usp=sharing"><img border="0" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7CrgqO8gX4oghEQxs_Z80WNr_hkIG3yy1PpqWHLKgKiDh_9ZyUbYtrAdE9GbO3lHrEpvgl4kK8hK1zucGxc-NkV8vTM_ysWR7A26LB-Ud8wGw5_q9qaLDRq5KgVuufhT1FWhRtyRkrFg/s320/java.png" width="32" /></a></td>
<td><a href="https://docs.google.com/file/d/0B_shk9i3DvyhM3o1SlFxNXBBRkk/edit?usp=sharing">HelperFunctions.java</a></td>
</tr>
<tr>
<td><a href="https://docs.google.com/file/d/0B_shk9i3Dvyhc1hHQUlIanZXcGs/edit?usp=sharing"><img border="0" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7CrgqO8gX4oghEQxs_Z80WNr_hkIG3yy1PpqWHLKgKiDh_9ZyUbYtrAdE9GbO3lHrEpvgl4kK8hK1zucGxc-NkV8vTM_ysWR7A26LB-Ud8wGw5_q9qaLDRq5KgVuufhT1FWhRtyRkrFg/s320/java.png" width="32" /></a></td>
<td><a href="https://docs.google.com/file/d/0B_shk9i3Dvyhc1hHQUlIanZXcGs/edit?usp=sharing">XQuerySecurityManager.java</a></td>
</tr>
<tr>
<td><a href="https://docs.google.com/file/d/0B_shk9i3DvyhbXZuOUpwNzlPZlk/edit?usp=sharing"><img border="0" height="32" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7CrgqO8gX4oghEQxs_Z80WNr_hkIG3yy1PpqWHLKgKiDh_9ZyUbYtrAdE9GbO3lHrEpvgl4kK8hK1zucGxc-NkV8vTM_ysWR7A26LB-Ud8wGw5_q9qaLDRq5KgVuufhT1FWhRtyRkrFg/s320/java.png" width="32" /></a></td>
<td><a href="https://docs.google.com/file/d/0B_shk9i3DvyhbXZuOUpwNzlPZlk/edit?usp=sharing">OBIEE11g_Amelia.java</a></td>
</tr>
</tbody></table>
<br />
At a high level, the migrateWeblogicUsers.sh script will
<br />
<ul>
<li>Create an import and export python script</li>
<li>Runs the python scripts with WLST to export and then import the Weblogic Users and Groups.</li>
</ul>
<br />
At a high level, the migrateAppRoles.sh script will
<br />
<ul>
<li>Copy (scp) over the system-jazn-data.xml from the destination to the source</li>
<li>Process the XML file with OBIEE11g_Amelia.jar, which produces a python script</li>
<li>Runs the python script with WLST to import the new Application Roles.</li>
</ul>
<a href="javascript:void(0);" onclick="javascript:showMigrateAppRoles()">Click me to view migrateAppRoles.sh</a>
<script language="JavaScript">function showMigrateAppRoles(){if(document.getElementById('migrateAppRoles').style.display == "block"){document.getElementById('migrateAppRoles').style.display = "none";}else{document.getElementById('migrateAppRoles').style.display = "block";}}</script>
<br />
<pre id="migrateAppRoles" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#! /bin/bash
######
# migrateAppRoles.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
# This script will facilitate migrating of Application Roles between environments.
######
set -o pipefail
checkStatus ()
{
if [ $1 -ne 0 ]; then
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The migration failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
return 0
}
if [ $# -lt 8 ]; then
echo -e "\nUsage: migrateAppRoles.sh -h toHost -u user -p password -f fromHost\n\t-h\tWeblogic host you are migrating to.\n\t-u\tUsername of
admin Weblogic user.\n\t-p\tPassword of admin Weblogic user.\n\t-f\tWeblogic host where you migrating from.\n"
exit -1
fi
while getopts ":h:u:p:f:" opt; do
case $opt in
h) MIGTOHOST=${OPTARG} ;;
u) MIGTOUSER=${OPTARG} ;;
p) MIGTOPASS=${OPTARG} ;;
f) MIGFROMHOST=${OPTARG} ;;
\?) echo "Invalid option: -$OPTARG" | tee -a $LOGFILE; exit ;;
:) echo "Option -$OPTARG requires an argument." | tee -a $LOGFILE; exit ;;
esac
done
SECURITY_FILE_LOC=/apps/applobi/OBIEE11g/user_projects/domains/bifoundation_domain/config/fmwconfig/system-jazn-data.xml
LOGFILE=~/logs/${MIGTOHOST}_migrateapproles_$(date +%y%m%d_%H%M%S).log
EMAIL=null@null.com
SUBJECT="Migrate Application Roles to $MIGTOHOST - `date +%m/%d/%Y` @ `date +%H:%M:%S`"
echo -e "`date +[%H:%M:%S]` ## Doing application role deployment to $MIGTOHOST from $MIGFROMHOST\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Copy the applicaiton roles file from $MIGFROMHOST\n" | tee -a $LOGFILE
scp -q $MIGFROMHOST:$SECURITY_FILE_LOC system-jazn-data.xml 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: scp -q $MIGFROMHOST:$SECURITY_FILE_LOC system-jazn-data.xml"
echo -e "`date +[%H:%M:%S]` ## Run the Java program to build the WLST python script\n" | tee -a $LOGFILE
/apps/applobi/OBIEE11g/Oracle_BI1/jdk/bin/java -jar support/OBIEE11g_Amelia.jar system-jazn-data.xml ./ $MIGTOUSER $MIGTOPASS $MIGTOHOST 2>&1 |
tee -a $LOGFILE
checkStatus "$?" "The deployment failed: java -jar support/OBIEE11g_Amelia.jar system-jazn-data.xml ./ $MIGTOUSER $MIGTOPASS $MIGTOHOST"
rm system-jazn-data.xml
echo -e "`date +[%H:%M:%S]` ## Run the WLST script to load the Application Roles to $MIGTOHOST\n" | tee -a $LOGFILE
/apps/applobi/OBIEE11g/Oracle_BI1/common/bin/wlst.sh OBI11gSecurityMigration.py 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: wlst.cmd OBI11gSecurityMigration.py"
echo "`date +[%H:%M:%S]` ## Migration of Appliction Roles from $MIGFROMHOST to $MIGTOHOST compelted." | tee -a $LOGFILE
rm OBI11gSecurityMigration.py
rm OBI11gSecurityMigration_delete.py
echo "`date +[%H:%M:%S]` ## Migration of Appliction Roles from $MIGFROMHOST to $MIGTOHOST compelted." | mutt -a $LOGFILE -s "$SUBJECT - SUCCESS
FULL" $EMAIL
</pre>
<br />
<a href="javascript:void(0);" onclick="javascript:showMigrateWeblogicUsers()">Click me to view migrateWeblogicUsers.sh</a>
<script language="JavaScript">function showMigrateWeblogicUsers(){if(document.getElementById('migrateWeblogicUsers').style.display == "block"){document.getElementById('migrateWeblogicUsers').style.display = "none";}else{document.getElementById('migrateWeblogicUsers').style.display = "block";}}</script>
<br />
<pre id="migrateWeblogicUsers" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#! /bin/bash
######
# migrateWeblogicUsers.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
# This script will facilitate migrating of Weblogic security between environments.
######
set -o pipefail
checkStatus ()
{
if [ $1 -ne 0 ]; then
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The migration failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
return 0
}
# If there are less than 4 parameters, then show help and exit
if [ $# -lt 8 ]; then
echo -e "\nUsage: migrateWeblogicUsers.sh -h toHost -u user -p password -f fromHost -w fromPassword\n\t-h\tWeblogic host you are migrating t
o.\n\t-u\tUsername of admin Weblogic user.\n\t-p\tPassword of admin Weblogic user.\n\t-f\tWeblogic host where you migrating from.\n\t-w\tPasswo
rd of admin Weblogic user (optional).\n"
exit -1
fi
while getopts ":h:u:p:f:w:" opt; do
case $opt in
h) MIGTOHOST=${OPTARG} ;;
u) MIGTOUSER=${OPTARG} ;;
p) MIGTOPASS=${OPTARG} ;;
f) MIGFROMHOST=${OPTARG} ;;
w) MIGFROMPASS=${OPTARG} ;;
\?) echo "Invalid option: -$OPTARG" | tee -a $LOGFILE; exit ;;
:) echo "Option -$OPTARG requires an argument." | tee -a $LOGFILE; exit ;;
esac
done
# If the from password is not set, assume the hosts share the same password
if [ -z "$MIGFROMPASS" ]; then
MIGFROMPASS=$MIGTOPASS
fi
LOGFILE=~/logs/${MIGTOHOST}_migrateweblogicsec_$(date +%y%m%d_%H%M%S).log
EMAIL=null@null.com
SUBJECT="Migrate Weblogic Users to $MIGTOHOST - `date +%m/%d/%Y` @ `date +%H:%M:%S`"
FILEPATH=/tmp
DATE=`date +%y%m%d_%H%M%S`
FAIL=0
echo -e "`date +[%H:%M:%S]` ## Doing weblogic security migration from $MIGFROMHOST to $MIGTOHOST\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Build the WLST export script\n" | tee -a $LOGFILE
echo "connect('$MIGTOUSER','$MIGFROMPASS','$MIGFROMHOST:7001')" > weblogicExport.py
echo "domainRuntime()" >> weblogicExport.py
echo "cd('/DomainServices/DomainRuntimeService/DomainConfiguration/bifoundation_domain/SecurityConfiguration/bifoundation_domain/DefaultRealm/m
yrealm/AuthenticationProviders/DefaultAuthenticator')" >> weblogicExport.py
echo "cmo.exportData('DefaultAtn','/tmp/DefaultAuthenticator_$DATE.dat', Properties())" >> weblogicExport.py
echo "cd('../../CredentialMappers/DefaultCredentialMapper')" >> weblogicExport.py
echo "cmo.exportData('DefaultCreds','/tmp/DefaultCredentialMapper_$DATE.dat', Properties())" >> weblogicExport.py
echo "cd('../../Authorizers/XACMLAuthorizer')" >> weblogicExport.py
echo "cmo.exportData('XACML','/tmp/XACMLAuthorizer_$DATE.dat', Properties())" >> weblogicExport.py
echo "cd('../../RoleMappers/XACMLRoleMapper')" >> weblogicExport.py
echo "cmo.exportData('XACML','/tmp/XACMLRoleMapper_$DATE.dat', Properties())" >> weblogicExport.py
echo "disconnect()" >> weblogicExport.py
echo "exit()" >> weblogicExport.py
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Build the WLST import script\n" | tee -a $LOGFILE
echo "connect('$MIGTOUSER','$MIGTOPASS','$MIGTOHOST:7001')" > weblogicImport.py
echo "domainRuntime()" >> weblogicImport.py
echo "cd('/DomainServices/DomainRuntimeService/DomainConfiguration/bifoundation_domain/SecurityConfiguration/bifoundation_domain/DefaultRealm/m
yrealm/AuthenticationProviders/DefaultAuthenticator')" >> weblogicImport.py
echo "cmo.importData('DefaultAtn','$FILEPATH/DefaultAuthenticator.dat', Properties())" >> weblogicImport.py
echo "cd('../../CredentialMappers/DefaultCredentialMapper')" >> weblogicImport.py
echo "cmo.importData('DefaultCreds','$FILEPATH/DefaultCredentialMapper.dat', Properties())" >> weblogicImport.py
echo "cd('../../Authorizers/XACMLAuthorizer')" >> weblogicImport.py
echo "cmo.importData('XACML','$FILEPATH/XACMLAuthorizer.dat', Properties())" >> weblogicImport.py
echo "cd('../../RoleMappers/XACMLRoleMapper')" >> weblogicImport.py
echo "cmo.importData('XACML','$FILEPATH/XACMLRoleMapper.dat', Properties())" >> weblogicImport.py
echo "disconnect()" >> weblogicImport.py
echo "exit()" >> weblogicImport.py
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Run the WLST script to export the weblogic users from $MIGFROMHOST\n" | tee -a $LOGFILE
/apps/applobi/OBIEE11g/Oracle_BI1/common/bin/wlst.sh weblogicExport.py 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: wlst.cmd weblogicExport.py"
echo -e "`date +[%H:%M:%S]` ## SCP the security files from $MIGFROMHOST\n" | tee -a $LOGFILE
scp -q $MIGFROMHOST:/tmp/DefaultAuthenticator_$DATE.dat $MIGTOHOST:$FILEPATH/DefaultAuthenticator.dat 2>&1 | tee -a $LOGFILE
if [ $? -ne 0 ]; then
FAIL=1
fi
scp -q $MIGFROMHOST:/tmp/DefaultCredentialMapper_$DATE.dat $MIGTOHOST:$FILEPATH/DefaultCredentialMapper.dat 2>&1 | tee -a $LOGFILE
if [ $? -ne 0 ]; then
FAIL=1
fi
scp -q $MIGFROMHOST:/tmp/XACMLAuthorizer_$DATE.dat $MIGTOHOST:$FILEPATH/XACMLAuthorizer.dat 2>&1 | tee -a $LOGFILE
if [ $? -ne 0 ]; then
FAIL=1
fi
scp -q $MIGFROMHOST:/tmp/XACMLRoleMapper_$DATE.dat $MIGTOHOST:$FILEPATH/XACMLRoleMapper.dat 2>&1 | tee -a $LOGFILE
if [ $? -ne 0 ]; then
FAIL=1
fi
ssh $MIGFROMHOST "rm /tmp/DefaultAuthenticator_$DATE.dat" | tee -a $LOGFILE
ssh $MIGFROMHOST "rm /tmp/DefaultCredentialMapper_$DATE.dat" | tee -a $LOGFILE
ssh $MIGFROMHOST "rm /tmp/XACMLAuthorizer_$DATE.dat" | tee -a $LOGFILE
ssh $MIGFROMHOST "rm /tmp/XACMLRoleMapper_$DATE.dat" | tee -a $LOGFILE
# Check if any of the previous steps failed
checkStatus "$FAIL" "The deployment failed: scp -q $MIGFROMHOST:/tmp/DefaultAuthenticator_$DATE.dat $FILEPATH/DefaultAuthenticator.dat"
echo -e "`date +[%H:%M:%S]` ## Run the WLST script to import the weblogic users from $MIGFROMHOST\n" | tee -a $LOGFILE
/apps/applobi/OBIEE11g/Oracle_BI1/common/bin/wlst.sh weblogicImport.py 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: wlst.cmd weblogicImport.py"
echo "`date +[%H:%M:%S]` ## Migration of Weblogic Security from $MIGFROMHOST to $MIGTOHOST compelted." | tee -a $LOGFILE
rm weblogicImport.py
rm weblogicExport.py
ssh $MIGTOHOST "rm $FILEPATH/DefaultAuthenticator.dat" 2>&1 | tee -a $LOGFILE
ssh $MIGTOHOST "rm $FILEPATH/DefaultCredentialMapper.dat" 2>&1 | tee -a $LOGFILE
ssh $MIGTOHOST "rm $FILEPATH/XACMLAuthorizer.dat" 2>&1 | tee -a $LOGFILE
ssh $MIGTOHOST "rm $FILEPATH/XACMLRoleMapper.dat" 2>&1 | tee -a $LOGFILE
echo "`date +[%H:%M:%S]` ## Migration of Weblogic Security from $MIGFROMHOST to $MIGTOHOST compelted." | mutt -a $LOGFILE -s "$SUBJECT - SUCCES
SFULL" $EMAIL
</pre>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com1tag:blogger.com,1999:blog-617408063302769488.post-57955202008975611942013-02-23T22:17:00.001-08:002013-11-14T11:44:47.386-08:00Configure Change Password for Weblogic users in OBIEE 11gInitially at my current engagement, we were using the inbuilt WebLogic LDAP for controlling user access to OBIEE 11g. We have since switched to using the EBS-ICX cookie SSO method (blog post on how to set this up coming soon), and are currently researching OID integration for SSO.<br />
<i><br /></i>
<i>Side note, the "EBS-ICX" cookie method does not allow for use of Agents or BI Publisher within OBIEE. These are known oracle bugs, but according to the SR I had opened, they are not high on the list to be fixed.</i><br />
<br />
[<b><i>Update - 10/30/2013:</i></b> I found a solution to this. Read my <a href="http://adventuresinobiee.blogspot.com/2013/10/configuring-agents-with-obia-and-ebs.html">post here</a>.]<br />
<br />
One of the draw backs to using Weblogic for your Identity Store is that if a user wants to change their password, there is no means of doing so from within OBIEE. I searched the web, and found articles that covered parts, but not the whole thing. Continue on to read step by step instructions to get setup.<br />
<br />
<a name='more'></a><br />
The scope of this post is to cover basic Jdeveloper tasks and OBIEE tasks. It also includes general setup instructions to customize the placement of the change password link within the OBIEE Global Header.<br />
<h2>
<b>JDeveloper Steps</b></h2>
I used JDeveloper to build and deploy the webservice, so this blog post outlines the steps using that tool.
<br />
<ul>
<li>Open <b>JDeveloper</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBWR5KPIgMut8Ji2-15XwQFiJLT0SpFNt5zzaiCoSvGBP3-DMHUAltvtYcj54uB8TAIeCLfjzKuQFLD3l_l8Zp3Zqyx4_QC-1efvyZerT3uabtFk9ZVYZao_R3PPvKDafR0eEH8LQjPsg/s1600/image003.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBWR5KPIgMut8Ji2-15XwQFiJLT0SpFNt5zzaiCoSvGBP3-DMHUAltvtYcj54uB8TAIeCLfjzKuQFLD3l_l8Zp3Zqyx4_QC-1efvyZerT3uabtFk9ZVYZao_R3PPvKDafR0eEH8LQjPsg/s320/image003.png" width="320" /></a></div>
<br />
<li>Create a New Application called <b>Changepwd</b>, and select<b> Generic Application. </b>Click <b>Next</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsunkzcQMG8Jwp60meK2abw_8IR2TcCyCYd-nbgH7pHHCn2hd6RTMx6IGJ8jOOoNTDOnnEWJTUCy1jSJQmdh8PaL6g5Cg-4qxy5xtJWAn6TJeUDeampRFOBs3baegRreVvpkaPu2V-q0Q/s1600/image004.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsunkzcQMG8Jwp60meK2abw_8IR2TcCyCYd-nbgH7pHHCn2hd6RTMx6IGJ8jOOoNTDOnnEWJTUCy1jSJQmdh8PaL6g5Cg-4qxy5xtJWAn6TJeUDeampRFOBs3baegRreVvpkaPu2V-q0Q/s320/image004.png" width="320" /></a></div>
<br />
<li>Name the project <b>PasswordChange</b>, select <b>Java</b> as a Project Technology. Click <b>Next</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMydUj0-PEH_THyOZPQhxQ1w7bYdYpsnhDmf_X4ICW3gcMSEENgl4vW7pb4H4KoZXxXN1z_9pxjHzl5wfBqA6NSJykq8Em3dgZwXrF2VGmPZa-u_rCc4CpNLh_T0LwxCJfu9q1G9x5bvA/s1600/image005.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMydUj0-PEH_THyOZPQhxQ1w7bYdYpsnhDmf_X4ICW3gcMSEENgl4vW7pb4H4KoZXxXN1z_9pxjHzl5wfBqA6NSJykq8Em3dgZwXrF2VGmPZa-u_rCc4CpNLh_T0LwxCJfu9q1G9x5bvA/s320/image005.png" width="320" /></a></div>
<br />
<li>Name the Default Package as <b>Changepwd</b>. Click <b>Finish</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi63ky8axWMPL-0IyyChh4WzuQ5Rv6_VVnYa0BOvwjWLGfYoJxzRPKw1bpYxKcKpsJckd5C8-420npaG050njucCtnmTSI8xNOJwkITcghM08SRcDvv4poOfdqaEtkR6Upmdm9s-60u14M/s1600/image006.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi63ky8axWMPL-0IyyChh4WzuQ5Rv6_VVnYa0BOvwjWLGfYoJxzRPKw1bpYxKcKpsJckd5C8-420npaG050njucCtnmTSI8xNOJwkITcghM08SRcDvv4poOfdqaEtkR6Upmdm9s-60u14M/s320/image006.png" width="320" /></a></div>
<br />
<li>Create a new Java class file called <b>Changepwd</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvl8smudzRphmDh11lTmJfWGUFlM17V0wqzKmL3pdB-xuxTDoheSAq_OM7_U5f3NMdgUmXrKiXmkXRD0_XpPegEdhFpPO8CEi4xGOYYiO2IDvPSyzmNNsW8PIb27DzO3wG20orZnX10LY/s1600/image007.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvl8smudzRphmDh11lTmJfWGUFlM17V0wqzKmL3pdB-xuxTDoheSAq_OM7_U5f3NMdgUmXrKiXmkXRD0_XpPegEdhFpPO8CEi4xGOYYiO2IDvPSyzmNNsW8PIb27DzO3wG20orZnX10LY/s320/image007.png" width="263" /></a></div>
<br />
<li>Paste in the contents of <b>Changepwd.java</b> to your new class file in JDeveloper.</li>
<ul>
<li><a href="https://docs.google.com/file/d/0B_shk9i3DvyhVmhOX0dvQkl6cTg/edit?usp=sharing" target="_blank">Changepwd.java</a></li>
<li>Note: You will need to update the variables for weblogic hostname, port, username, and password.</li>
</ul>
<br />
<li>Right Click on your main class and select <b>Webservice Properties</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7PivQrByPhlgtsOG2cyDGzVpqlTknw8IEWdgEnM7KINk_3evrv0V4WRvyuNZ1Po0ir-RGwWLJ2ps-Wh9Jgnnzc1JlbfH8BMZDdRw2wqdZFq07P-0Kdr6GGIgB64o2lvUxhbhbbKVb5hY/s1600/image009.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7PivQrByPhlgtsOG2cyDGzVpqlTknw8IEWdgEnM7KINk_3evrv0V4WRvyuNZ1Po0ir-RGwWLJ2ps-Wh9Jgnnzc1JlbfH8BMZDdRw2wqdZFq07P-0Kdr6GGIgB64o2lvUxhbhbbKVb5hY/s320/image009.png" width="288" /></a></div>
<br />
<li>Select the default (Java EE 1.5, with support for JAX-WS Annotations), and click on <b>Ok</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnl3opXR4VnWLAZ3DbG5p1ls6dz-wlVyNwH3AQ31E86jLjya_64v-7lhfilXM_Cyr3WVhD4JJE_SeRPoEnjAgcwK0WKefHebucWf1tt0cw0fO8jMtV2XmuXyBE2BEx6r2c_-Sca-q6CVc/s1600/image010.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnl3opXR4VnWLAZ3DbG5p1ls6dz-wlVyNwH3AQ31E86jLjya_64v-7lhfilXM_Cyr3WVhD4JJE_SeRPoEnjAgcwK0WKefHebucWf1tt0cw0fO8jMtV2XmuXyBE2BEx6r2c_-Sca-q6CVc/s320/image010.png" width="320" /></a></div>
<br />
<li>Select <b>Methods</b> and confirm that the method <b>passwordChange</b> is selected and click <b>Ok</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4Npgmg5E5Eckt4Lc0dWiEbjQ48w5FXn8iBsluo46Nro1UyZ7DUU2PQQPva20qrjOO9Fo4a5AefSxdl2lHIdfQ9WoYQFgPyr4QxWHu-D-1it0T2isCOVKJXOaaAMsGDIC-9a1Ny45TkJw/s1600/image011.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4Npgmg5E5Eckt4Lc0dWiEbjQ48w5FXn8iBsluo46Nro1UyZ7DUU2PQQPva20qrjOO9Fo4a5AefSxdl2lHIdfQ9WoYQFgPyr4QxWHu-D-1it0T2isCOVKJXOaaAMsGDIC-9a1Ny45TkJw/s320/image011.png" width="320" /></a></div>
<br />
<li>Let’s now deploy the web service to the Weblogic server. Right Click on the project and click <b>Deploy –> Webservices</b></li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgP2Z6Z4TYF0-YIQ10zM01VmoeSp8feSQl2fnTm87O8Ko3KCGBly-PcGAxxIg1HbGMWBWgIjg-_oc3gAqbqCJjM-tUQ10nKJ4ltemh1MEGTaTtr28yKlCSgUpKCWv5Wft5-wFLG8IKcEJ0/s1600/image012.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="255" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgP2Z6Z4TYF0-YIQ10zM01VmoeSp8feSQl2fnTm87O8Ko3KCGBly-PcGAxxIg1HbGMWBWgIjg-_oc3gAqbqCJjM-tUQ10nKJ4ltemh1MEGTaTtr28yKlCSgUpKCWv5Wft5-wFLG8IKcEJ0/s320/image012.png" width="320" /></a></div>
<br />
<li>Select <b>Deploy to Application Server</b>. Click Next.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg368R24elVWHOG-PX2X1UTzt8yClviEnTYl_QfYzWjg2oYHmccYjaCkQrWrFZN71huMLX0Ao7Fcblk6qwY5E-FVZf-HGpxPQ8o9BkZZZlgGGh-1uuzAJb4ibJwMwi5ZIKVjeDn9Op2rEc/s1600/image013.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg368R24elVWHOG-PX2X1UTzt8yClviEnTYl_QfYzWjg2oYHmccYjaCkQrWrFZN71huMLX0Ao7Fcblk6qwY5E-FVZf-HGpxPQ8o9BkZZZlgGGh-1uuzAJb4ibJwMwi5ZIKVjeDn9Op2rEc/s320/image013.png" /></a></div>
<br />
<li>Click <img align="middle" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg95Gm4man6E5VDGL7EDETFWHY0w_1p5XjFSsRrPov9ZsA_UtTcHFymrdGsJPiQZx3PkJjCYml-9g4yCYLyqSJNKL9A41MJh1OJuFjhcqS0GHDNubwEwNUCtd9C6pzEItHAhXY_5_YImtw/s320/image014.png" /> to add a connection to the Weblogic server. Name it <b>LocalWeblogic</b>.</li>
<ul>
<li>Note: This guide walks through setting up your local weblogic server, but you can easily replace your local info with the main server info you want the WAR deployed to.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiux8oJcubxmrBH3JgHHhyMrRZb1LDqf7UL11f74Ql3lC3E92IQjdy_YH6NkAYZ3hbUzC2J6r08cC7LAB-bHjbnLmy3ovEQfWLC9-CaGoVFTxgf8ve8Yh-2zzI6LpZcEGCqvX6460VaOOM/s1600/image015.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiux8oJcubxmrBH3JgHHhyMrRZb1LDqf7UL11f74Ql3lC3E92IQjdy_YH6NkAYZ3hbUzC2J6r08cC7LAB-bHjbnLmy3ovEQfWLC9-CaGoVFTxgf8ve8Yh-2zzI6LpZcEGCqvX6460VaOOM/s320/image015.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgSFDmi5YPoGTi0xM7OB26YofewSRiWJJV7rWEHEpA6zfthynowHgRNYs2acQzzXoAXZLfCT27ddAMXiCzIiJMtpCIZvCQjEdWLloQo5x3VpGhjvVP2nNi0maQmHaBPxuQpeBWrqEQ1tQ/s1600/image016.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgSFDmi5YPoGTi0xM7OB26YofewSRiWJJV7rWEHEpA6zfthynowHgRNYs2acQzzXoAXZLfCT27ddAMXiCzIiJMtpCIZvCQjEdWLloQo5x3VpGhjvVP2nNi0maQmHaBPxuQpeBWrqEQ1tQ/s320/image016.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit33kKcvl-_HVQWx1hhNOcvfm4g-hVuS-Ug5CEyDhTG86QwSFKeraTIGX_ND7RZUYaQDZB9xSiSt7a0OAthwa05yhETKCpt9wrxCdEs9-6JMZrHHimX_fUh0IIZfckI926xUm5OOqME3g/s1600/image017.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEit33kKcvl-_HVQWx1hhNOcvfm4g-hVuS-Ug5CEyDhTG86QwSFKeraTIGX_ND7RZUYaQDZB9xSiSt7a0OAthwa05yhETKCpt9wrxCdEs9-6JMZrHHimX_fUh0IIZfckI926xUm5OOqME3g/s320/image017.png" /></a></div>
<br />
</ul>
<li>Select the Application Server you just added, in our case <b>LocalWeblogic</b>. Click <b>Next</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfHDN0cMyeoFZcbX0fkTsYuipYPJ8MGFsU3YunM7tpa9fudfaJK_HRb1UvVAepvY3uLUZgEzxy6ou9QPib3OCjV9Lej1TcWo7YzcdfsEtF7kOgBb47v9kIjzj_twW89z_G-7x_-BTzcSY/s1600/image018.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfHDN0cMyeoFZcbX0fkTsYuipYPJ8MGFsU3YunM7tpa9fudfaJK_HRb1UvVAepvY3uLUZgEzxy6ou9QPib3OCjV9Lej1TcWo7YzcdfsEtF7kOgBb47v9kIjzj_twW89z_G-7x_-BTzcSY/s320/image018.png" /></a></div>
<br />
<li>Select the second radio button, <b>Deploy to selected instances in the domain</b>. Make sure to check only the BI_cluster, not the AdminServer. Leave <b>Deploy as a standalone Application</b> selected. Click <b>Next</b>. Then click <b>Finish</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgo44KxhN9RU8jUggdKwSNWibpLi9vpnF69yihFSpVyQrTkxIVo7Ccfkudt0Lw0ykA7IFApheW6KCd8Gf-k1h2blPgy3eSYLVB_mHg3OOI3cOqK7ihuU_Y4B0fDwwo2Rd9oX7y7eindFM0/s1600/image019.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgo44KxhN9RU8jUggdKwSNWibpLi9vpnF69yihFSpVyQrTkxIVo7Ccfkudt0Lw0ykA7IFApheW6KCd8Gf-k1h2blPgy3eSYLVB_mHg3OOI3cOqK7ihuU_Y4B0fDwwo2Rd9oX7y7eindFM0/s320/image019.png" /></a></div>
<br />
<li>A new tab in Jdeveloper should appear at the bottom of the screen called <b>Deployment</b>. Monitor here for a successful deployment message. This can take a few minutes.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijHa5394bK3Qj96JJhIZqYf_qazDXREXjkPg0Bfq3aJKx8yhzJzHUb8fSI5n_tT92x3QCG1dJQCufhq1RBmJ4EyYVmdMRL2dpsR2YVeWdeiU5CAZUDjBOqeEIrf_lQ5irCG0HoMqgnuJs/s1600/image020.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijHa5394bK3Qj96JJhIZqYf_qazDXREXjkPg0Bfq3aJKx8yhzJzHUb8fSI5n_tT92x3QCG1dJQCufhq1RBmJ4EyYVmdMRL2dpsR2YVeWdeiU5CAZUDjBOqeEIrf_lQ5irCG0HoMqgnuJs/s320/image020.png" /></a></div>
</ul>
<b><br /></b>
<br />
<h2>
<b>OBIEE 11g Steps</b></h2>
<br />
The following steps take place from within OBIEE. They will create a simple Agent to invoke the webservice deployed above, and then create a hidden dashboard page to be a launch point for the agent.<br />
<ul>
<li>Login to the OBIEE portal (<a href="http://localhost:9704/analytics">http://localhost:9704/analytics</a>)</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAszz3Xck5PoDDKWlWYGJrM3cAzN3D0PHfn0Rt58cI5hTPvSA8ko8GBDEmRQO67fgu8KyHESGRD8RE0p2dTsEQKYGJAh183CgksmFeTP1QiasHL5UzDO7i-hc0unoTH81WUIvea1ObAo8/s1600/image021.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAszz3Xck5PoDDKWlWYGJrM3cAzN3D0PHfn0Rt58cI5hTPvSA8ko8GBDEmRQO67fgu8KyHESGRD8RE0p2dTsEQKYGJAh183CgksmFeTP1QiasHL5UzDO7i-hc0unoTH81WUIvea1ObAo8/s320/image021.png" /></a></div>
<br />
<li>Create a new Dashboard,called <b>User Settings</b>.</li>
<li>Under Location, select <b>Browser…</b></li>
<li>From the <b>Shared Folders</b> folder, select <b>New Folder</b>. Name it <b>User Account</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFvUKJcLHr6G0U6ZcDmiHI3iARxyJDqKsk6WgnDNOxq1VMHhAPLbgWmbhMnZRr3iJCnTV2hbSQpDZH_ME_gTZ66onAl-aYFeIURBqMNjIzkmSDde7rhqZP2ZOOZL0Mgk3zGdz3in6gSCg/s1600/image022.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFvUKJcLHr6G0U6ZcDmiHI3iARxyJDqKsk6WgnDNOxq1VMHhAPLbgWmbhMnZRr3iJCnTV2hbSQpDZH_ME_gTZ66onAl-aYFeIURBqMNjIzkmSDde7rhqZP2ZOOZL0Mgk3zGdz3in6gSCg/s320/image022.png" /></a></div>
<br />
<li>Select the radio button for <b>Add content later (Create empty dashboard)</b>. Click <b>Ok</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiss7RywvIrNbEolzZso-YUFagEqPULOcZzYvyG5xt2gXto2731XU08Hw0wknP8Znv4e133qC95FUZAIpd0ailISpDXWnpp3Sd2eAsWqV-nSWVeYAeZbdd_o6VFDl9ikKfS-6R9yf3tpes/s1600/image023.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiss7RywvIrNbEolzZso-YUFagEqPULOcZzYvyG5xt2gXto2731XU08Hw0wknP8Znv4e133qC95FUZAIpd0ailISpDXWnpp3Sd2eAsWqV-nSWVeYAeZbdd_o6VFDl9ikKfS-6R9yf3tpes/s320/image023.png" /></a></div>
<br />
<li>Click on the Catalog link, and navigate to the newly created folder, <b>User Account</b>.</li>
<li>Select <b>Permissions</b> to set the appropriate permissions on the new folder, BI Administrator should have <b>Full Control</b>. BI Consumer should have <b>Open</b>.</li>
<ul>
<li>Make sure that <b>Apply permissions to sub-folders </b>and<b> Apply permissions to items within folder</b>
are both selected.</li>
</ul>
<li>Click <b>Ok</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgILbM3JTJLfjScVJ7Ifwe6hNb-aYtMZ9yEDiRr6mW30jDpe8L4JeA56yYwg37QDGKlKuuLdUr0SQs0byZdPQbJC131oy9hiLvyFmdSBSYHH1orlOIRspFtNmil1OAio4FGY0WhK6oqNZ4/s1600/image024.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgILbM3JTJLfjScVJ7Ifwe6hNb-aYtMZ9yEDiRr6mW30jDpe8L4JeA56yYwg37QDGKlKuuLdUr0SQs0byZdPQbJC131oy9hiLvyFmdSBSYHH1orlOIRspFtNmil1OAio4FGY0WhK6oqNZ4/s320/image024.png" /></a></div>
<br />
<li>From the <b>New</b> menu in the OBIEE global navigation, select <b>Action</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaSVPGY_pb5bTsvw_zhrZm5IFHBEtB26SA93vc8w4GjsPuGZE4wt6iXaRjpZ6TqaMxAceL0Xd_TK3-Cw6C24AjV6b2rX7PXe1-Bqz7lMN_OyMkc7XGvxmOQw7ia3fBenh8xYQ8BvCGKLs/s1600/image025.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaSVPGY_pb5bTsvw_zhrZm5IFHBEtB26SA93vc8w4GjsPuGZE4wt6iXaRjpZ6TqaMxAceL0Xd_TK3-Cw6C24AjV6b2rX7PXe1-Bqz7lMN_OyMkc7XGvxmOQw7ia3fBenh8xYQ8BvCGKLs/s320/image025.png" /></a></div>
<br />
<li>Choose <b>Invoke Web Service</b> from the popup menu.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK2sv7uLovKEAEpAT7eJ_ZJIvJPDB8KyN0qHq3fQFzPPPwDWF9qf24gVco4oOkjZ9JhEn17AVXbFh73r8-jCPVIhSGTyVLY_zWbTT2WAh5mE_tVxiRt88HyhHqlQKua2oNrotB8jNEHQA/s1600/image026.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK2sv7uLovKEAEpAT7eJ_ZJIvJPDB8KyN0qHq3fQFzPPPwDWF9qf24gVco4oOkjZ9JhEn17AVXbFh73r8-jCPVIhSGTyVLY_zWbTT2WAh5mE_tVxiRt88HyhHqlQKua2oNrotB8jNEHQA/s320/image026.png" /></a></div>
<br />
<li>Plug in the WSDL and click Open.</li>
<ul>
<li>The URL would look like: http://<weblogic host>:<obiee port>/<package name>-<project name>-context-root/<package name>Port?WSDL</li>
<li>On my local, it is: <b>http://localhost:9704/Changepwd-PasswordChange-context-root/ChangepwdPort?WSDL</b></li>
</ul>
<li>Drill down until you can select <b>passwordChange</b>, and select it. Click <b>Ok.</b></li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVNab5-C4N9evFtZz4pOqZtWMHFp6tDNin1vU_ppTBWczB-A9AkYzwK9yobGV5SgwJUFhnxYKQ9rEc4-LriUFSMUFA2mmQIZDsW5zeBEcEkRZWtfeMBwlyvZTPwNvWR8IvZlmDfDnUIrI/s1600/image027.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVNab5-C4N9evFtZz4pOqZtWMHFp6tDNin1vU_ppTBWczB-A9AkYzwK9yobGV5SgwJUFhnxYKQ9rEc4-LriUFSMUFA2mmQIZDsW5zeBEcEkRZWtfeMBwlyvZTPwNvWR8IvZlmDfDnUIrI/s320/image027.png" /></a></div>
<br />
<li>This opens up a new dialog box, enter the following values for the prompts (no quotes):</li>
<ul>
<li>passwordChange_arg0 = "Username:"</li>
<li>passwordChange_arg1 = "Old Password:"</li>
<li>passwordChange_arg2 = "New Password:"</li>
<li>passwordChange_arg3 = "Confirm Password:"</li>
</ul>
<li>For the <b>Username</b> argument, for <b>Value</b> select <b>Session Variable</b>, then type in <b>USER</b> and mark it as <b>fixed</b> and <b>hidden</b>.</li>
<ul>
<li>This will force the password change to only work for the current logged in user.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiDDaabskbE1o8UEMllRT64G_8-UHMAIoFRTQYaL3D1CQbV2bmu2bsco4iXglr5oTYqoy_HwZJsC2nm5i94c88bMV6Pr59Jz6Hh3mxLe44wvJkL6XuAiBv4eEqnChZ49xn37Gp5bjKiMA/s1600/image028.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiDDaabskbE1o8UEMllRT64G_8-UHMAIoFRTQYaL3D1CQbV2bmu2bsco4iXglr5oTYqoy_HwZJsC2nm5i94c88bMV6Pr59Jz6Hh3mxLe44wvJkL6XuAiBv4eEqnChZ49xn37Gp5bjKiMA/s320/image028.png" /></a></div>
<br />
</ul>
<li>Click the <b>Options</b> button to personalize the messages (again, no quotes):</li>
<ul>
<li>Set<b> Dialog Title</b> to "Change Password"</li>
<li>Set <b>Action Help Text</b> to "Please enter your existing password and new password below."</li>
<li>Set <b>Execute Button Text</b> to "Change Password"</li>
<li>Leave all other options blank.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimV-KxWeOGS7rN1IUT_SMVGQbvp5GPZciWtUc6ogcmy2gzvqd4ceLeGXNV2mDKw7Dnm_eiQIdTwcLagi1ECHbUVz3T5iHkosGh1YHpxqmwzkjFGLgQfhSuqXbY6IDAGFAzuHQDqSOXq2g/s1600/image029.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimV-KxWeOGS7rN1IUT_SMVGQbvp5GPZciWtUc6ogcmy2gzvqd4ceLeGXNV2mDKw7Dnm_eiQIdTwcLagi1ECHbUVz3T5iHkosGh1YHpxqmwzkjFGLgQfhSuqXbY6IDAGFAzuHQDqSOXq2g/s320/image029.png" /></a></div>
<br />
</ul>
<li>Click on the <b>Action Results</b> tab and enter the parameters for the return messages (again, no quotes):</li>
<ul>
<li>Set <b>Dialog Text</b> to "@{passwordChangeResponse}"</li>
<li>Create one XPath variable:</li>
<ul>
<li><b>Name</b>: passwordChangeResponse</li>
<li><b>XPath Expression</b>: Body/passwordChangeResponse/return</li>
</ul>
<li>Set <b>Dialog Title</b> to "Result"</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFwJFzJiyAdouYkMkCROxjwf9kk6r2WbuAY4PTOCLeG3YJ7g20wB3d-vGEDexMKn4nMYV7VrM4U0EJ0_leDStgcnBkqK2gz41Uxs-vlDOfjLdrO8qjcBSoJccjPxm03wYPDZBfhrcN2ig/s1600/image030.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFwJFzJiyAdouYkMkCROxjwf9kk6r2WbuAY4PTOCLeG3YJ7g20wB3d-vGEDexMKn4nMYV7VrM4U0EJ0_leDStgcnBkqK2gz41Uxs-vlDOfjLdrO8qjcBSoJccjPxm03wYPDZBfhrcN2ig/s320/image030.png" /></a></div>
<br />
</ul>
<li>Click <b>Ok</b>. Click <b>Save Action</b>.</li>
<li>Save the action as <b>Change Password</b> into the <b>User Account</b> Shared Folder you created above.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSobfhxhD4nJSa6civVA9AZ9MK_ZiUS-8UrV8lH2i26rJVEyxA8cOjxJrtFiY9VH8ByZRnaDUWcTjCi4eUWIj1-ScLJNh1dcFcMwyuGxCZ3s7JvrsNfR3e-GuqJEl27XBZgPBICMGXh2Y/s1600/image031.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSobfhxhD4nJSa6civVA9AZ9MK_ZiUS-8UrV8lH2i26rJVEyxA8cOjxJrtFiY9VH8ByZRnaDUWcTjCi4eUWIj1-ScLJNh1dcFcMwyuGxCZ3s7JvrsNfR3e-GuqJEl27XBZgPBICMGXh2Y/s320/image031.png" /></a></div>
<br />
<li>Navigate to your newly created Dashboard, <b>User Settings</b>. Edit the dashboard by placing a new section. In that section, add a text item. Place the following into the text item (making sure to check the <b>Contains HTML Markup</b> box):</li>
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>
<script type="text/javascript">
document.getElementById("idPageOptions").style.display = "none";
</script>
Welcome to the change password page. This page will allow you to change your password for access to the Business Intelligence reporting application.<br><br>
You are currently logged in as <b>@{biServer.variables['NQ_SESSION.USER']}</b>.<br><br>
Please click the link below to begin the password reset process.
</code></pre>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsqn2gqxXWZcMUuLaWXGLpb0NDwV5HuK5vY_v8KnC3jC-cYIvMEmqQMqKAl_SKBWPhE5IvpG1u83EYHu-8K9jJMnCo4zfPNWMVSnf0mAJ6gMFDMKw41UjbAfSO1-YwqDWJiaETQSr_K_A/s1600/image032.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsqn2gqxXWZcMUuLaWXGLpb0NDwV5HuK5vY_v8KnC3jC-cYIvMEmqQMqKAl_SKBWPhE5IvpG1u83EYHu-8K9jJMnCo4zfPNWMVSnf0mAJ6gMFDMKw41UjbAfSO1-YwqDWJiaETQSr_K_A/s320/image032.png" /></a></div>
<br />
<li>Click <b>Ok</b>. Edit the Column Properties. Click on <b>Custom CSS Style Options (HTML Only)</b> and add the following to the <b>Use Custom CSS Style</b>:</li>
<br />
<code style="background-color: #e4e4e4; border-style: solid; border-width: 1px;">
position:absolute;height:120px; margin-top:40px;
</code>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipnl1drHBSGWJe99EG3A23-MVsUme6v3gvKvLgsHHdPXvaMJGySILo6tCUJK08AvhrGBlEwyn6oE8g68DtqLQWXBOONL3St9wLRn8cHTUgv7y1McgKv5GopICGLaBGeeZppbUtb5-k-rc/s1600/image033.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipnl1drHBSGWJe99EG3A23-MVsUme6v3gvKvLgsHHdPXvaMJGySILo6tCUJK08AvhrGBlEwyn6oE8g68DtqLQWXBOONL3St9wLRn8cHTUgv7y1McgKv5GopICGLaBGeeZppbUtb5-k-rc/s320/image033.png" /></a></div>
<br />
<li>Click <b>Ok</b>.</li>
<li>Drag an Action Link below your text item in that same section.
Name the link <b>Change Password</b>, and
navigate to the saved Action Link you saved to the Shared Folder. Don’t define
any values, just click on the <b>Ok</b>
button.</li>
<li>Click <b>Ok</b> to close the Action
Link Properties window.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0NS93ELgsaemAbAL_tmIk5u9S4QiLs9sJ6CP88kVND0QXb4vJq-F9vSGySa_qJ1G8yzRpZkjx7xw_AcIrq7EQo2q4s71Kz0RKBkbg9z-F5fMpW6kB3BokSQZwNdrKXeFquaga-h3wcls/s1600/image034.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0NS93ELgsaemAbAL_tmIk5u9S4QiLs9sJ6CP88kVND0QXb4vJq-F9vSGySa_qJ1G8yzRpZkjx7xw_AcIrq7EQo2q4s71Kz0RKBkbg9z-F5fMpW6kB3BokSQZwNdrKXeFquaga-h3wcls/s320/image034.png" /></a></div>
<br />
<li>On the main section
properties, unselect <b>Collapsible</b>.
Additionally, from the Section Properties, set the border position to <b>None</b>.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje2wHqXIX1pzNIu_B4t0gNEDKOQjXfxjdh_Z_xNK95ZTmoNYNoSDv_8aFe08eo2wTe89zVU7h2E0kfJTKpxAlnzum2l29AEeoGoVq3TRn-b8S_vZTLGSYpBG0e-e6EC8NxfKs0_-O-1Yg/s1600/image035.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEje2wHqXIX1pzNIu_B4t0gNEDKOQjXfxjdh_Z_xNK95ZTmoNYNoSDv_8aFe08eo2wTe89zVU7h2E0kfJTKpxAlnzum2l29AEeoGoVq3TRn-b8S_vZTLGSYpBG0e-e6EC8NxfKs0_-O-1Yg/s320/image035.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiebwRRElpjij2DEeOE2a8shhFtOg3wSUBh8jj8OGpPejFkuOLFJIeG7WE2A8u5hMcizMsmJRRemDuxqsDxUUShIV1kK7p5A9DkzIF8CJoqfHk6S4ldLgg6hdKzh53sS9D2-WO1Fh2R_dY/s1600/image036.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiebwRRElpjij2DEeOE2a8shhFtOg3wSUBh8jj8OGpPejFkuOLFJIeG7WE2A8u5hMcizMsmJRRemDuxqsDxUUShIV1kK7p5A9DkzIF8CJoqfHk6S4ldLgg6hdKzh53sS9D2-WO1Fh2R_dY/s320/image036.png" /></a></div>
<br />
<li>Click the <img align="middle" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4ONgMyZn4L7tVRcF58EwthErREmSNg5ajxFeynECjMTaFpmytK_PbIVjN4NwlnFgN3cLEi4TAanICkOGNNfaWislbvYd-YJaKzFnJniYxQtFSkccXnTSE-1NoQ7pcEY-z5h6SHO0-LjU/s320/image037.png" /> icon to save the dashboard. Click the <img align="middle" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmhHDRZf99t8McQL4i5bM_2fWgquLk7XIiAIRiyqP36shjfjP1BMxuizM7n4rlKRY5yFllzOypYh6z7oOGNSoPIpHmVnOw3mYrOQWyJ_tx4d0nfhrGvr1DMG6yNoMblk4e5x7seTmU-Sc/s320/image038.png" /> icon to view the results.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjdT9b0rjCeJkpQZZJke_KsS53A3-0WyoZCQP4IL4XkwQm9Zi4sw0UjR_2bhaiMw_k9k0SNYWv3v55iRMrcRHdxxE89RUMKNPEZK2-Shh041HQjldRHnIahXptLdYgd_OKtjb8g88SvhI/s1600/image039.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjdT9b0rjCeJkpQZZJke_KsS53A3-0WyoZCQP4IL4XkwQm9Zi4sw0UjR_2bhaiMw_k9k0SNYWv3v55iRMrcRHdxxE89RUMKNPEZK2-Shh041HQjldRHnIahXptLdYgd_OKtjb8g88SvhI/s320/image039.png" /></a></div>
<br />
<li>Click on Catalog. Navigate
back to the new <b>User Account</b> folder
you created. Select the folder, and then select Properties. Select <b>Hidden</b>, then click <b>Ok</b>.</li>
</ul>
<h2>
<div style="font-size: medium;">
<h2>
OBIEE Server Steps</h2>
</div>
<div style="font-size: medium; font-weight: normal;">
The following steps take place on the OBIEE server. All files references are for a Linux/Solaris install, but file locations will be similar for Windows.</div>
</h2>
<ul>
<li>Update the following files,
to place a link to the Change Password in the header bar:</li>
<ul>
<li>/apps/applobi/OBIEE11g/Oracle_BI1/bifoundation/web/app/res/b_mozilla/header.js</li>
<li>/apps/applobi/OBIEE11g/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/b_mozilla/header.js</li>
</ul>
<li>For these two files, find the line:</li>
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>
b.push(new obips.ContextMenu.MenuOption(saw.header.getLocalizedString("kmsgHeaderMyAccount"),"",null,new obips.Callback(this,this.onMyAccount)));
</code>
</pre>
<br />
<br />
<li>and replace it with:</li>
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>
b.push(new obips.ContextMenu.MenuOption(saw.header.getLocalizedString("kmsgHeaderMyAccount"),"",null,new obips.Callback(this,this.onMyAccount)));b.push(new obips.ContextMenu.MenuOption(saw.header.getLocalizedString("kmsgHeaderCustomURL"),"",null,new obips.Callback(this,this.onCustomURL)));
</code>
</pre>
<br />
<br />
<li>then find the line:</li>
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>
saw.header.NavBar.prototype.onMyAccount=function(){saw.header.Menubar.getManager().hidePopupPanel();var a=new saw.ondemandload.FuncProxy("obips.AccountInfo.launchDialog",{messageTemplate:"kuiMyAccountDialogHead"});a.exec()};
</code>
</pre>
<br />
<br />
<li>and replace it with:</li>
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>
saw.header.NavBar.prototype.onMyAccount=function(){saw.header.Menubar.getManager().hidePopupPanel();var a=new saw.ondemandload.FuncProxy("obips.AccountInfo.launchDialog", {messageTemplate:"kuiMyAccountDialogHead"});a.exec()};saw.header.NavBar.prototype.onCustomURL=function(){var w = 1920, h = 1080; if (document.all) { w = document.body.clientWidth;h = document.body.clientHeight;x = window.screenTop;y = window.screenLeft;} else if (document.layers) { w = window.innerWidth;h = window.innerHeight;x = window.screenX;y = window.screenY;} var popW = 450, popH = 276;var leftPos = ((w-popW)/2)+y, topPos = ((h-popH)/2)+x;window.open('saw.dll?PortalPages&PortalPath <b>%2Fshared%2FUser%20Account%2F_portal%2FUser%20Settings</b>','passwordresetwindow','toolbar=no, menubar=no, scrollbars=no, resizable=no,location=no, directories=no, status=no, width='+popW+',height='+popH+',top='+topPos+',left='+leftPos);};
</code>
</pre>
<br />
<br />
<br />
<ul>
<li>Note: Replace the bold part with the path to the location you saved the
Action Link in OBIEE above.</li>
</ul>
<li>Update the following file to add the resource id</li>
<ul>
<li>/apps/applobi/OBIEE11g/Oracle_BI1/bifoundation/web/msgdb/common/saw.header.xml</li>
</ul>
<li>For this file, add the following line: </li>
<br />
<code style="background-color: #e4e4e4; border-style: solid; border-width: 1px;">
<resource id="kmsgHeaderCustomURL" />
</code>
<br />
<br />
<li>The next file will not exist out of the box, and needs to be created, along with any folders along the way.</li>
<ul>
<li>/apps/applobi/OBIEE11g/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/msgdb/l_en/customMessages/uicmsgs/saw.header.xml</li>
</ul>
<li>For this file, you will be creating it, so make the
contents:</li>
<br />
<pre style="background-color: #e4e4e4; border-style: solid; border-width: 1px;"><code>
<?xml version="1.0" encoding="utf-8"?>
<webmessagetables xmlns:sawm="com.siebel.analytics.web/message/v1">
<webmessagetable lang="en-us" system="saw.header" table="Messages">
<webmessage name="kmsgHeaderCustomURL"><text>Change Password</text></webmessage>
</webmessagetable>
</webmessagetables>
</code>
</pre>
<br />
<li>Restart the OBIEE services.
You should now see a link under the User menu called <b>Change Password</b>. This will open a new window providing the user
with simple instructions to change their password.</li>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXE2aY3A4isZoOGz2r18OCiPknoQgPfgJ9rY8GIzwXNa06s5H8lgrjioyoZA0jT4k3221Px1SS_N4mkVx-xAqn6kr1fpjc_vwALhL6HF6wb1A2a0s2IElhjBYAcDGyq9UAoEeM__e6xFk/s1600/image040.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXE2aY3A4isZoOGz2r18OCiPknoQgPfgJ9rY8GIzwXNa06s5H8lgrjioyoZA0jT4k3221Px1SS_N4mkVx-xAqn6kr1fpjc_vwALhL6HF6wb1A2a0s2IElhjBYAcDGyq9UAoEeM__e6xFk/s320/image040.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMQrGww362tkFMaortJpElC2dhICB2XyQbMuEa-QbBJiT9EUY61G1kc-B9EFdy9OOB8Il1TyZlI100yIcEqVsxkaAorEQqz_p_H2gxFgi8lpwE9kBxPL4G2rndrg7QwyGHO25a1JZjyl4/s1600/image041.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMQrGww362tkFMaortJpElC2dhICB2XyQbMuEa-QbBJiT9EUY61G1kc-B9EFdy9OOB8Il1TyZlI100yIcEqVsxkaAorEQqz_p_H2gxFgi8lpwE9kBxPL4G2rndrg7QwyGHO25a1JZjyl4/s320/image041.png" /></a></div>
</ul>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com32tag:blogger.com,1999:blog-617408063302769488.post-83981469402076475192013-02-22T19:26:00.001-08:002013-04-13T17:40:26.050-07:00Changing password of the MDS and BIPLATFORM schemas used by OBIEE 11gAt my current engagement, we are required to change the passwords for everything every 60 days. After doing some research I found a blog post by someone who showed how to do this from Enterprise Manager, but they only covered three of the four locations to change the password. As I have mentioned previously, I like automation, so I build a script which takes care of all the tasks.<br />
<br />
After the jump is an example script, along with the python for WLST, to perform this task.<br />
<br />
<a name='more'></a><br />
Using what I've picked up with WLST I created a script to automate this process.
At a high level, the script will
<br />
<ul>
<li>Log into a database and change the password for the MDS and BIPLATFORM schemas</li>
<li>Log into Enterprise Manager, and update the password for MDS in the JDBC data sources</li>
<li>Log into Enterprise Manager, and update the password for BIPLATFORM in the JDBC data sources</li>
<li>Log into Enterprise Manager, and update the password for EPMSystemRegistry in the JDBC data sources</li>
<li>Log into Enterprise Manager, and update the password for BIPLATFORM in the Deployments section.</li>
<li>Restart the OBIEE Services</li>
</ul>
<div>
You will need to manually restart all of the processes (AdminServer and biserver) after this completes.</div>
<div>
<br /></div>
<a href="javascript:void(0);" onclick="javascript:showMDSChangePwd()">Click me to view resetMDSBIPlatformPwd.sh</a>
<script language="JavaScript">function showMDSChangePwd(){if(document.getElementById('changemdspwd').style.display == "block"){document.getElementById('changemdspwd').style.display = "none";}else{document.getElementById('changemdspwd').style.display = "block";}}</script>
<br />
<pre id="changemdspwd" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#!/bin/bash
######
# resetMDSBIPlatformPwd.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
# This script will change the MDS and BIPLATFORM passwords.
######
set -o pipefail
checkStatus ()
{
if [ $1 -eq 0 ]; then
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The password reset failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
return 0
}
checkStatus2 ()
{
if [ $1 -ne 0 ]; then
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The password reset failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
return 0
}
# If there are less than 5 parameters, then show help and exit
if [ $# -lt 10 ]; then
echo -e "\nUsage: resetMDSBIPlatformPwd.sh -h host -u WLuser -p WLpassword -o oldpassword -n newpassword\n\t-h\tWeblogic host you are updating the password on.\n\t-u\tUsername of admin Weblogic user.\n\t-p\tPassword of admin Weblogic user.\n\t-o\tOld password for MDS and BIPLATFORM schemas\n\t-n\tNew password for MDS and BIPLATFORM schemas\n"
exit -1
fi
while getopts ":h:u:p:o:n:" opt; do
case $opt in
h) SERVERHOST=${OPTARG} ;;
u) WLUSER=${OPTARG} ;;
p) WLPASS=${OPTARG} ;;
o) OLDPASS=${OPTARG} ;;
n) NEWPASS=${OPTARG} ;;
\?) echo "Invalid option: -$OPTARG" | tee -a $LOGFILE; exit ;;
:) echo "Option -$OPTARG requires an argument." | tee -a $LOGFILE; exit ;;
esac
done
if [ "$SERVERHOST" = "appobid" ]; then
DB_NAME=OBID
elif [ "$SERVERHOST" = "appobisit" ]; then
DB_NAME=OBIBAWD
elif [ "$SERVERHOST" = "appobipat" ]; then
DB_NAME=OBIT
elif [ "$SERVERHOST" = "appobiprod" ]; then
DB_NAME=OBIP
elif [ "$SERVERHOST" = "appobiqa" ]; then
DB_NAME=OBIQ
else
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n"
echo "The password change failed: $SERVERHOST is undefined."
exit 1
fi
FMWHOME=/apps/applobi/OBIEE11g # change this to the fusion middleware home
SUPPORT_LOC=~/support
LOGFILE=~/logs/${SERVERHOST}_changeschemapasswords_$(date +%y%m%d_%H%M%S).log
EMAIL=null@null.com
DATE=`date +%y%m%d_%H%M%S`
SUBJECT="OBIEE Repository Password Change on $SERVERHOST - `date +%m/%d/%Y` @ `date +%H:%M:%S`"
echo -e "`date +[%H:%M:%S]` ## Doing MDS and BIPLATFORM repository password change on $HOST\n" | tee -a $LOGFILE
DB_CHECK=`${ORACLE_HOME}/bin/sqlplus -s DEV_MDS/${OLDPASS}@${DB_NAME} << END
alter user MDS identified by ${NEWPASS} replace ${OLDPASS};
exit
END`
echo $DB_CHECK | grep "ORA-"
checkStatus "$?" "The script failed: sqlplus -s DEV_MDS/${OLDPASS}@${DB_NAME}"
DB_CHECK=`${ORACLE_HOME}/bin/sqlplus -s DEV_BIPLATFORM/${OLDPASS}@${DB_NAME} << END
alter user BIPLATFORM identified by ${NEWPASS} replace ${OLDPASS};
exit
END`
echo $DB_CHECK | grep "ORA-"
checkStatus "$?" "The script failed: sqlplus -s DEV_MDS/${OLDPASS}@${DB_NAME}"
echo -e "`date +[%H:%M:%S]` ## Do weblogic scripting to change password\n" | tee -a $LOGFILE
${FMWHOME}/Oracle_BI1/common/bin/wlst.sh $SUPPORT_LOC/wlstMDSBIPlatformPwd.py -u $WLUSER -p $WLPASS -h $SERVERHOST -o 7001 -n $NEWPASS 2>&1 | tee -a $LOGFILE
checkStatus2 "$?" "The deployment failed: /apps/applobi/OBIEE11g/Oracle_BI1/common/bin/wlst.sh $SUPPORT_LOC/wlstMDSBIPlatformPwd.py -u $WLUSER -p $WLPASS -h $SERVERHOST -o 7001 -n $NEWPASS"
##
# Check status and send email.
##
echo -e "`date +[%H:%M:%S]` ## Deployment to $SERVERHOST was successfull." > obi_status.tmp
ssh $SERVERHOST '/apps/applobi/OBIEE11g/instances/instance1/bin/opmnctl status' >> obi_status.tmp
cat obi_status.tmp | tee -a $LOGFILE
cat obi_status.tmp | mutt -a $LOGFILE -s "$SUBJECT - SUCCESSFULL" $EMAIL
rm obi_status.tmp
</pre>
<br />
<a href="javascript:void(0);" onclick="javascript:showWLSTMDSChangePwd()">Click me to view wlstMDSBIPlatformPwd.py</a>
<script language="JavaScript">function showWLSTMDSChangePwd(){if(document.getElementById('WLSTMDS').style.display == "block"){document.getElementById('WLSTMDS').style.display = "none";}else{document.getElementById('WLSTMDS').style.display = "block";}}</script>
<br />
<pre id="WLSTMDS" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">import sys
import os
from java.lang import System
import getopt
def usage():
print "Usage:"
print "wlstMDSBIPlatformPwd.py -u user -p password -h host -o port -n newpassword"
try:
opts, args = getopt.getopt(sys.argv[1:], "u:p:h:o:n:",
["user=", "password=", "host=", "port=", "newpassword="])
except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)
user = ''
password = ''
host = ''
port = ''
newpassword = ''
for opt, arg in opts:
if opt == "-u":
user = arg
elif opt == "-p":
password = arg
elif opt == "-h":
host = arg
elif opt == "-o":
port = arg
elif opt == "-n":
newpassword = arg
if user == "":
print "Missing \"-u user\" parameter."
usage()
sys.exit(2)
elif password == "":
print "Missing \"-p password\" parameter."
usage()
sys.exit(2)
elif host == "":
print "Missing \"-h host\" parameter."
usage()
sys.exit(2)
elif port == "":
print "Missing \"-o port\" parameter."
usage()
sys.exit(2)
elif newpassword == "":
print "Missing \"-n newpassword\" parameter."
usage()
sys.exit(2)
connect(user, password, host+":"+port)
cd("/Servers/AdminServer")
edit()
startEdit()
cd("JDBCSystemResources")
print(host + ": Changing Password for DataSource mds-owsm")
cd("/JDBCSystemResources/mds-owsm/JDBCResource/mds-owsm/JDBCDriverParams/mds-owsm")
set("Password", newpassword)
print(host + ": Password has been Changed for mds-owsm)
print(host + ": Changing Password for DataSource bip_datasource")
cd("/JDBCSystemResources/bip_datasource/JDBCResource/bip_datasource/JDBCDriverParams/bip_datasource")
set("Password", newpassword)
print(host + ": Password has been Changed for bip_datasource)
print(host + ": Changing Password for DataSource EPMSystemRegistry")
cd('/JDBCSystemResources/EPMSystemRegistry/JDBCResource/EPMSystemRegistry/JDBCDriverParams/EPMSystemRegistry)
set('Password', newpassword)
print(host + ": Password has been Changed for DataSource: EPMSystemRegistry")
save()
activate()
print(host + ": Changing Scheduler Password for BIPlatform Schema")
print(host + ": Connecting to Domain ...")
try:
domainCustom()
except:
print(host + ": Already in domainCustom")
print(host + ": Go to biee admin domain")
cd("oracle.biee.admin")
# go to the server configuration
print(host + ": Go to BIDomain.BIInstance.ServerConfiguration MBean")
cd("oracle.biee.admin:type=BIDomain.BIInstance.ServerConfiguration,biInstance=coreapplication,group=Service")
# Lock the System
print(host + ": Calling lock ...")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("lock", objs, strs)
except:
print(host + ": System already locked")
cd("..")
# go to the server configuration
cd("oracle.biee.admin:type=BIDomain.BIInstance.SchedulerDatabaseConfiguration,biInstance=coreapplication,group=Service")
print(host + ": Changing BIPLATFORM Schema password")
# Set the parameters --- Change DEV_BIPLATFORM to the name of the BIPLATFORM schema on your system
params = jarray.array(["DEV_BIPLATFORM",newpassword],java.lang.Object)
# Set the parameters Signs
sign = jarray.array(["java.lang.String", "java.lang.String"],java.lang.String)
# Invoke the procedure
invoke( "setCredentials", params, sign)
# Commit the system
print(host + ": Commiting Changes")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("commit", objs, strs)
except:
print(host + ": System not locked")
# Restart the system
print(host + ": Restarting OBIEE processes")
cd("..\..")
cd("oracle.biee.admin")
cd("oracle.biee.admin:type=BIDomain.BIInstance,biInstance=coreapplication,group=Service")
print(host + ": Stopping the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("stop", params, signs)
BIServiceStatus = get("ServiceStatus")
print(host + ": BI ServiceStatus " + BIServiceStatus)
print(host + ": Starting the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("start", params, signs)
BIServerStatus = get("ServiceStatus")
print(host + ": BI ServerStatus " + BIServerStatus)
</pre>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com0tag:blogger.com,1999:blog-617408063302769488.post-54920474369821758422013-02-05T14:23:00.003-08:002013-03-22T21:52:01.653-07:00Scripted OBIEE 11g Web Catalog deployments using runcat.sh and WLSTI had never looked deeply into automating the OBIEE Web Catalog, as on previous engagements I had a technical architecture team which handled all deployments. They were used to doing a manual process utilizing the Catalog Manager. On my latest engagement, I am the technical architecture team, and I like automation.<br />
<br />
After poking around, I found that you can call the catalog manager form the command line, and along with WLST scripting, you can do a fully automated web catalog deployment.<br />
<br />
After the jump is a script which can be used to do OBIEE 11g web catalog deployments. This script assumes you have setup ssh keys on the various servers so you can login without typing a password (<a href="http://www.linuxproblem.org/art_9.html">http://www.linuxproblem.org/art_9.html</a>).<br />
<br />
<a name='more'></a><br />
At a high level, the script will
<br />
<ul>
<li>Perform either a full deployment or an incremental deployment</li>
<li>Remotely connects to the destination, and SCPs the web catalog</li>
<li>When doing an incremental deployment</li>
<ul>
<li>it skips all user folders (and any other folders you specify)</li>
<li>it creates a diff of the new catalog and the currently deployed catalog</li>
<li>it creates a patch file based on the diff</li>
<li>it applies the patch to the currently deployed catalog</li>
</ul>
<li>When doing a full deployment</li>
<ul>
<li>Call WLST with a custom script to lock, update the web catalog path, commit the changes, and restart the BI server</li>
</ul>
<li>Performs a resync of user GUIDs after the deployment</li>
</ul>
<a href="javascript:void(0);" onclick="javascript:showWebCatMessage()">Click me to view deployWebCat.sh</a>
<script language="JavaScript">function showWebCatMessage(){if(document.getElementById('deploywebcat').style.display == "block"){document.getElementById('deploywebcat').style.display = "none";}else{document.getElementById('deploywebcat').style.display = "block";}}</script>
<br />
<pre id="deploywebcat" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#!/bin/bash
######
# deployWebCat.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
# This script will check the diffs on the currently deployed webcat and a new web cat and deploy only the changes.
# Note: This script ignores the user folders, and doesn't attempt to migrate any user reports. That has to be done manually.
######
set -o pipefail
checkStatus ()
{
if [ $1 -ne 0 ]; then
if [ "${3-zz}" != "zz" ]; then
exec $3
fi
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The deploy failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
return 0
}
# If there are less than 1 parameters, then show help and exit
if [ $# -lt 1 ]; then
echo -e "\nUsage: deployWebCat.sh deploymentLoc\n\t Where deploymentLoc is DEV|SIT|PATCH|QA|PROD\n"
exit -1
fi
FULL_DEPLOY=0
if [ "$2" = "FULL" ]; then
FULL_DEPLOY=1
echo -e "`date +[%H:%M:%S]` ## Performing a Full Deploy\n" | tee -a $LOGFILE
fi
if [ "$1" = "DEV" ]; then
SERVERHOST=appobid
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "SIT" ]; then
SERVERHOST=appobisit
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "PATCH" ]; then
SERVERHOST=appobipat
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "PROD" ]; then
SERVERHOST=appobiprod
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "QA" ]; then
SERVERHOST=appobiqa
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
else
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n"
echo "The deployment failed: $1 is undefined."
exit 1
fi
SUPPORT_LOC=~/support
SVN_LOC=~/svnget # change this to the location where the webcat to deploy is
PRE_DEPLOY_LOC=/tmp/bmackin/deploy
FMWHOME=/apps/applobi/OBIEE11g # change this to the fusion middleware home
OBI_INSTANCE=${FMWHOME}/instances/instance1
WEB_CAT_NAME=EnterpriseBusinessAnalytics7963_Extn # change this to the name of the web catalog folder
PRE_DEPLOY_LOC=/tmp/predeploy # this folder gets created below
WEB_CAT_PARENT_DIR=${OBI_INSTANCE}/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1
LOGFILE=~/logs/${SERVERHOST}_webcatdeploy_$(date +%y%m%d_%H%M%S).log
EMAIL=null@null.com
DATE=`date +%y%m%d_%H%M%S`
SUBJECT="OBIEE Web Cat Deployment on $SERVERHOST - `date +%m/%d/%Y` @ `date +%H:%M:%S`"
echo -e "`date +[%H:%M:%S]` ## Doing Web Catalog deployment to $SERVERHOST\n" | tee -a $LOGFILE
##
# Use SVN to get the web cat
##
##
# Stop services and backup the deployed web cat
##
if [ $FULL_DEPLOY = 0 ]; then
echo -e "`date +[%H:%M:%S]` ## Stop services and backup the currently deployed web cat \n" | tee -a $LOGFILE
ssh $SERVERHOST "'$OBI_INSTANCE'/bin/opmnctl stopall" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: ssh $SERVERHOST \"'$OBI_INSTANCE'/bin/opmnctl stopall\""
ssh $SERVERHOST "rm '$WEB_CAT_PARENT_DIR'/catalog/'$WEB_CAT_NAME'.zip" 2>&1 | tee -a $LOGFILE
ssh $SERVERHOST "cd '$WEB_CAT_PARENT_DIR'/catalog; zip -q -r '$WEB_CAT_NAME' '$WEB_CAT_NAME'; mv '$WEB_CAT_NAME'.zip '$WEB_CAT_NAME'_'$DATE'.zip" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: ssh $SERVERHOST \"cd '$WEB_CAT_PARENT_DIR'/catalog; zip -q -r '$WEB_CAT_NAME' '$WEB_CAT_NAME'; mv '$WEB_CAT_NAME'.zip '$WEB_CAT_NAME'_'$DATE'.zip\""
echo -e "`date +[%H:%M:%S]` ## Move the file to the pre-deployment directory\n" | tee -a $LOGFILE
ssh $SERVERHOST "rm -rf '$PRE_DEPLOY_LOC'" 2>&1 | tee -a $LOGFILE
ssh $SERVERHOST "mkdir -p '$PRE_DEPLOY_LOC'" 2>&1 | tee -a $LOGFILE
scp $SVN_LOC/$WEB_CAT_NAME.zip $SERVERHOST:$PRE_DEPLOY_LOC/$WEB_CAT_NAME.zip 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: scp $SVN_LOC/$WEB_CAT_NAME.zip $SERVERHOST:$PRE_DEPLOY_LOC/$WEB_CAT_NAME.zip"
echo -e "`date +[%H:%M:%S]` ## Unzip the new catalog\n" | tee -a $LOGFILE
ssh $SERVERHOST "cd '$PRE_DEPLOY_LOC'; unzip -q '$WEB_CAT_NAME'.zip" 2>&1 | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Create a DIFF file for two webcats\n" | tee -a $LOGFILE
### IMPORTANT: Any folders you want skipped when doing the comapre must be specified.
### Otherwise, if the folder doesn't exist in the development but in the destination it will be removed.
ssh $SERVERHOST "'$WEB_CAT_PARENT_DIR'/catalogmanager/runcat.sh -cmd diff -baseline '$WEB_CAT_PARENT_DIR'/catalog/'$WEB_CAT_NAME' -latest '$PRE_DEPLOY_LOC'/'$WEB_CAT_NAME' -outputFile /tmp/web_cat_diff_'$DATE'.txt -verbosity detail -skipFolder /shared/\!TROUBLESHOOTING:/shared/Project Shared" 2>> $LOGFILE 1>/dev/null
checkStatus "$?" "The deployment failed: $WEB_CAT_PARENT_DIR/catalogmanager/runcat.sh -cmd diff -baseline $WEB_CAT_PARENT_DIR/catalog/'$WEB_CAT_NAME' -latest '$PRE_DEPLOY_LOC'/'$WEB_CAT_NAME' -outputFile /tmp/web_cat_diff_$DATE.txt -verbosity detail -skipFolder /shared/\!TROUBLESHOOTING:/shared/Project Shared" "ssh $SERVERHOST \"rm /tmp/web_cat_diff_'$DATE'.txt\""
scp -q $SERVERHOST:/tmp/web_cat_diff_$DATE.txt /tmp/$1_web_cat_diff1_$DATE.txt
checkStatus "$?" "The deployment failed: scp -q $SERVERHOST:/tmp/web_cat_diff_$DATE.txt /tmp/$1_web_cat_diff1_$DATE.txt"
echo -e "##### Results" | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'Total objects reported' | tee -a $LOGFILE
echo "#" | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'Num of objects NOT in BASE' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'Num of objects NOT in LATE' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'Num of objects NOT in PROD' | tee -a $LOGFILE
echo "#" | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'Num of objects changed' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'TYPE : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'ATTRIBUTES : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'OWNER : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'ACL : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'PROPERTIES : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'CREATOR : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'MODIFIER : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'CONTENT : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_diff1_$DATE.txt | grep 'MSG : ' | tee -a $LOGFILE
echo -e "#####\n" | tee -a $LOGFILE
####### Currently if a folder exists in PROD but not in Latest, the folder is being removed.
echo -e "`date +[%H:%M:%S]` ## Create a patch file based on DIFF\n" | tee -a $LOGFILE
ssh $SERVERHOST "'$WEB_CAT_PARENT_DIR'/catalogmanager/runcat.sh -cmd createPatch -inputFile /tmp/web_cat_diff_'$DATE'.txt -outputFile /tmp/web_cat_patch_'$DATE'.txt -production '$WEB_CAT_PARENT_DIR'/catalog/'$WEB_CAT_NAME' -winsConflict latest" 2>> $LOGFILE 1>/dev/null
# latest
# production
# blendThenLatest
# blendThenProduction
checkStatus "$?" "The deployment failed: $WEB_CAT_PARENT_DIR/catalogmanager/runcat.sh -cmd createPatch -inputFile /tmp/web_cat_diff_$DATE.txt -outputFile /tmp/web_cat_patch_$DATE.txt -production $WEB_CAT_PARENT_DIR/catalog/'$WEB_CAT_NAME' -winsConflict latest" "ssh $SERVERHOST \"rm /tmp/web_cat_patch_'$DATE'.txt\""
scp -q $SERVERHOST:/tmp/web_cat_patch_$DATE.txt /tmp/$1_web_cat_patch1_$DATE.txt
checkStatus "$?" "The deployment failed: scp -q $SERVERHOST:/tmp/web_cat_patch_$DATE.txt /tmp/$1_web_cat_patch1_$DATE.txt"
echo -e "##### Results" | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'Total objects reported' | tee -a $LOGFILE
echo "#" | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'Num of objects NOT in BASE' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'Num of objects NOT in LATE' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'Num of objects NOT in PROD' | tee -a $LOGFILE
echo "#" | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'Num of objects changed' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'TYPE : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'ATTRIBUTES : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'OWNER : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'ACL : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'PROPERTIES : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'CREATOR : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'MODIFIER : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'CONTENT : ' | tee -a $LOGFILE
cat /tmp/$1_web_cat_patch1_$DATE.txt | grep 'MSG : ' | tee -a $LOGFILE
echo -e "#####\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Apply the patch file\n" | tee -a $LOGFILE
ssh $SERVERHOST "'$WEB_CAT_PARENT_DIR'/catalogmanager/runcat.sh -cmd applyPatch -inputFile /tmp/web_cat_patch_'$DATE'.txt -outputFile /tmp/web_cat_patch_'$DATE'.log" 2>> $LOGFILE 1>/dev/null
checkStatus "$?" "The deployment failed: $WEB_CAT_PARENT_DIR/catalogmanager/runcat.sh -cmd applyPatch -inputFile /tmp/web_cat_patch_$DATE.txt -outputFile /tmp/web_cat_patch_$DATE.log" "ssh $SERVERHOST \"rm /tmp/web_cat_patch_$DATE.log\""
echo -e "`date +[%H:%M:%S]` ## Web Cat Patch Log:\n" | tee -a $LOGFILE
scp -q $SERVERHOST:/tmp/web_cat_patch_$DATE.log /tmp/$1_web_cat_patch1_$DATE.log
cat /tmp/$1_web_cat_patch1_$DATE.log | tee -a $LOGFILE
echo -e "\n`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
##
# Cleanup and email success
##
ssh $SERVERHOST "rm /tmp/web_cat_diff*"
ssh $SERVERHOST "rm /tmp/web_cat_patch*"
rm /tmp/$1_web_cat_patch*
rm /tmp/$1_web_cat_diff*
echo -e "`date +[%H:%M:%S]` ## Cleanup the pre-deploy folder\n" | tee -a $LOGFILE
ssh $SERVERHOST "rm -rf '$PRE_DEPLOY_LOC'" 2>&1 | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## Restart the OBIEE processes\n" | tee -a $LOGFILE
ssh $SERVERHOST "'$OBI_INSTANCE'/bin/opmnctl startall" 2>&1 | tee -a $LOGFILE
############################################################################################
## This ends the incremental migration. Below the else statement is the FULL migration tasks
else
## We are doing a full deploy
############################################################################################
echo -e "`date +[%H:%M:%S]` ## We are about to do a full catalog replacement/deploy. If this is not" | tee -a $LOGFILE
echo -e "`date +[%H:%M:%S]` ## what you intended, kill this session." | tee -a $LOGFILE
read -p "Pausing until [Enter] is hit..."
echo -e "`date +[%H:%M:%S]` ## Delete the current $WEB_CAT_NAME if it exists in the deployment directory on $SERVERHOST\n" | tee -a $LOGFILE
ssh $SERVERHOST "rm -rf '$WEB_CAT_PARENT_DIR'/catalog/'$WEB_CAT_NAME'" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: ssh $SERVERHOST \"rm -rf '$WEB_CAT_PARENT_DIR'/catalog/'$WEB_CAT_NAME'\""
echo -e "`date +[%H:%M:%S]` ## Move the catalog to the deployment directory on $SERVERHOST\n" | tee -a $LOGFILE
scp $SVN_LOC/$WEB_CAT_NAME.zip $SERVERHOST:$WEB_CAT_PARENT_DIR/catalog/$WEB_CAT_NAME.zip 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: scp $SVN_LOC/$WEB_CAT_NAME.zip $SERVERHOST:$WEB_CAT_PARENT_DIR/catalog/$WEB_CAT_NAME.zip"
echo -e "`date +[%H:%M:%S]` ## Unzip the new catalog\n" | tee -a $LOGFILE
ssh $SERVERHOST "cd '$WEB_CAT_PARENT_DIR'/catalog; rm -rf '$WEB_CAT_NAME'; unzip -q '$WEB_CAT_NAME'.zip; rm '$WEB_CAT_NAME'.zip" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: Unzipping the new catalog"
echo -e "`date +[%H:%M:%S]` ## Do weblogic scripting to deploy the file\n" | tee -a $LOGFILE
/apps/applobi/OBIEE11g/Oracle_BI1/common/bin/wlst.sh $SUPPORT_LOC/wlstDeployCatalog.py -u $WEBLOGIC_USER -p $WEBLOGIC_PASS -h $SERVERHOST -o 7001 -w $WEB_CAT_NAME 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: /apps/applobi/OBIEE11g/Oracle_BI1/common/bin/wlst.sh $SUPPORT_LOC/wlstDeployCatalog.py -u $WEBLOGIC_USER -p $WEBLOGIC_PASS -h $SERVERHOST -o 7001 -w $WEB_CAT_NAME"
fi
##
# Correct the user GUIDs.
##
./resyncUserGUIDs.sh $1
##
# Check status and send email.
##
echo -e "`date +[%H:%M:%S]` ## Deployment to $SERVERHOST was successfull." > obi_status.tmp
ssh $SERVERHOST '/apps/applobi/OBIEE11g/instances/instance1/bin/opmnctl status' >> obi_status.tmp
cat obi_status.tmp | tee -a $LOGFILE
cat obi_status.tmp | mutt -a $LOGFILE -s "$SUBJECT - SUCCESSFULL" $EMAIL
rm obi_status.tmp
</pre>
<br />
<a href="javascript:void(0);" onclick="javascript:showWLSTWebCatMessage()">Click me to view wlstDeployCatalog.py</a>
<script language="JavaScript">function showWLSTWebCatMessage(){if(document.getElementById('WLST').style.display == "block"){document.getElementById('WLST').style.display = "none";}else{document.getElementById('WLST').style.display = "block";}}</script>
<br />
<pre id="WLST" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">import sys
import os
from java.lang import System
import getopt
def usage():
print "Usage:"
print "wlstDeployCatalog.py -u user -p password -h host -o port -w webcatname"
try:
opts, args = getopt.getopt(sys.argv[1:], "u:p:h:o:w:",
["user=", "password=", "host=", "port=", "webcatname="])
except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)
user = ''
password = ''
host = ''
port = ''
webcatname = ''
for opt, arg in opts:
if opt == "-u":
user = arg
elif opt == "-p":
password = arg
elif opt == "-h":
host = arg
elif opt == "-o":
port = arg
elif opt == "-w":
webcatname = arg
if user == "":
print "Missing \"-u user\" parameter."
usage()
sys.exit(2)
elif password == "":
print "Missing \"-p password\" parameter."
usage()
sys.exit(2)
elif host == "":
print "Missing \"-h host\" parameter."
usage()
sys.exit(2)
elif port == "":
print "Missing \"-o port\" parameter."
usage()
sys.exit(2)
elif webcatname == "":
print "Missing \"-w webcatname\" parameter."
usage()
sys.exit(2)
connect(user, password, host+":"+port)
# Be sure we are in the root
cd("..\..")
print(host + ": Connecting to Domain ...")
try:
domainCustom()
except:
print(host + ": Already in domainCustom")
print(host + ": Go to biee admin domain")
cd("oracle.biee.admin")
# go to the server configuration
print(host + ": Go to BIDomain.BIInstance.ServerConfiguration MBean")
cd("oracle.biee.admin:type=BIDomain.BIInstance.PresentationServerConfiguration,biInstance=coreapplication,group=Service")
# Lock the System
print(host + ": Calling lock ...")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("lock", objs, strs)
except:
print(host + ": System already locked")
cd("..")
# go to the server configuration
cd("oracle.biee.admin:type=BIDomain.BIInstance.PresentationServerConfiguration,biInstance=coreapplication,group=Service")
print(host + ": Configuring WebCatalog Path")
WebCatalogSharedLocation = get("WebCatalogSharedLocation")
print(host + ": old WebCatalogSharedLocation = " + WebCatalogSharedLocation)
newWebCatalogSharedLocation = "$ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/$COMPONENT_NAME/catalog/" + webcatname
set("WebCatalogSharedLocation",newWebCatalogSharedLocation)
WebCatalogSharedLocation = get("WebCatalogSharedLocation")
print(host + ": new WebCatalogSharedLocation = " + WebCatalogSharedLocation)
# Commit the system
print(host + ": Commiting Changes")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("commit", objs, strs)
except:
print(host + ": System not locked")
# Restart the system
print(host + ": Restarting OBIEE processes")
cd("..\..")
cd("oracle.biee.admin")
cd("oracle.biee.admin:type=BIDomain.BIInstance,biInstance=coreapplication,group=Service")
print(host + ": Stopping the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("stop", params, signs)
BIServiceStatus = get("ServiceStatus")
print(host + ": BI ServiceStatus " + BIServiceStatus)
print(host + ": Starting the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("start", params, signs)
BIServerStatus = get("ServiceStatus")
print(host + ": BI ServerStatus " + BIServerStatus)
</pre>
<br />
<a href="javascript:void(0);" onclick="javascript:showResyncMessage()">Click me to view resyncUserGUIDs.sh</a>
<script language="JavaScript">function showResyncMessage(){if(document.getElementById('resyncUserGUIDs').style.display == "block"){document.getElementById('resyncUserGUIDs').style.display = "none";}else{document.getElementById('resyncUserGUIDs').style.display = "block";}}</script>
<br />
<pre id="resyncUserGUIDs" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#!/bin/bash
######
# resyncUserGUIDs.sh
# Created by Ben Mackin (bmackin@gmail.com)
#
# This script will resync the user GUIDs in the OBIEE web catalog
######
set -o pipefail
checkStatus ()
{
if [ $1 -ne 0 ]; then
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The resync failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
echo -e "`date +[%H:%M:%S]` ## Success\n" | tee -a $LOGFILE
return 0
}
if [ $# -ne 1 ]; then
echo -e "\nUsage: resyncUserGUIDs.sh host\n\t Where host is DEV|SIT|PATCH|QA|PROD\n"
exit -1
fi
if [ "$1" = "DEV" ]; then
SERVERHOST=appobid
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "SIT" ]; then
SERVERHOST=appobisit
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "PATCH" ]; then
SERVERHOST=appobipat
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "PROD" ]; then
SERVERHOST=appobiprod
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
elif [ "$1" = "QA" ]; then
SERVERHOST=appobiqa
WEBLOGIC_USER=biadmin
WEBLOGIC_PASS=oracle01
else
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n"
echo "The resync failed: $1 is undefined."
exit 1
fi
DATE=`date +%y%m%d_%H%M%S`
FMWHOME=/apps/applobi/OBIEE11g # change this to the fusion middleware home
OBI_INSTANCE=${FMWHOME}/instances/instance1
LOGFILE=~/logs/${SERVERHOST}_resyncguids_$(date +%y%m%d_%H%M%S).log
EMAIL=null@null.com
SUBJECT="Resync User GUIDs on $SERVERHOST - `date +%m/%d/%Y` @ `date +%H:%M:%S`"
echo -e "`date +[%H:%M:%S]` ## Resyncing User GUIDs on $SERVERHOST\n" | tee -a $LOGFILE
echo -e "\n`date +[%H:%M:%S]` ## Stop the BI Presentation Server and the BI Server\n" | tee -a $LOGFILE
ssh $SERVERHOST "$OBI_INSTANCE/bin/opmnctl stopproc ias-component=coreapplication_obips1" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl stopproc ias-component=coreapplication_obips1"
sleep 5
ssh $SERVERHOST "$OBI_INSTANCE/bin/opmnctl stopproc ias-component=coreapplication_obis1" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl stopproc ias-component=coreapplication_obis1"
sleep 5
ssh $SERVERHOST "$OBI_INSTANCE/bin/opmnctl stopproc ias-component=coreapplication_obis1" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl stopproc ias-component=coreapplication_obis1"
sleep 5
echo -e "\n`date +[%H:%M:%S]` ## Update the NQSConfig.INI file to set FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;\n" | tee -a $LOGFILE
ssh $SERVERHOST "cat '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI | sed -e 's/FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = NO;/FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;/' > /tmp/NQSConfig.INI" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: Failed on NQSConfig.INI update"
ssh $SERVERHOST "mv '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI_'$DATE'" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: Failed on NQSConfig.INI update"
ssh $SERVERHOST "mv /tmp/NQSConfig.INI '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: Failed on NQSConfig.INI update"
echo -e "\n`date +[%H:%M:%S]` ## Update the instanceconfig.xml file to set the UpdateAccountGUIDs\n" | tee -a $LOGFILE
ssh $SERVERHOST "cat '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml | sed -e 's/<\/UpgradeAndExit>/<\/UpgradeAndExit>\n <updateaccountguids>UpdateAndExit<\/UpdateAccountGUIDs>/' > /tmp/instanceconfig.xml" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: Failed on instanceconfig.xml update"
ssh $SERVERHOST "mv '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml_'$DATE'" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: Failed on instanceconfig.xml update"
ssh $SERVERHOST "mv /tmp/instanceconfig.xml '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: Failed on instanceconfig.xml update"
echo -e "\n`date +[%H:%M:%S]` ## Start the BI Presentation Server and the BI Server\n" | tee -a $LOGFILE
ssh $SERVERHOST "'$OBI_INSTANCE'/bin/opmnctl startproc ias-component=coreapplication_obis1" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obis1"
sleep 20
ssh $SERVERHOST "'$OBI_INSTANCE'/bin/opmnctl startproc ias-component=coreapplication_obips1" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obips1"
sleep 20
echo -e "\n`date +[%H:%M:%S]` ## Revert the NQSConfig.INI and instanceconfig.xml files\n" | tee -a $LOGFILE
ssh $SERVERHOST "rm '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: rm $OBI_INSTANCE/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI"
ssh $SERVERHOST "mv '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI_'$DATE' '$OBI_INSTANCE'/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: mv $OBI_INSTANCE/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI_$DATE $OBI_INSTANCE/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI"
ssh $SERVERHOST "rm '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: rm $OBI_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml"
ssh $SERVERHOST "mv '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml_'$DATE' '$OBI_INSTANCE'/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: mv $OBI_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml_$DATE $OBI_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml"
echo -e "\n`date +[%H:%M:%S]` ## Restart the BI Services\n" | tee -a $LOGFILE
ssh $SERVERHOST "'$OBI_INSTANCE'/bin/opmnctl stopall" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl stopall"
ssh $SERVERHOST "'$OBI_INSTANCE'/bin/opmnctl startall" 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The resync failed: $OBI_INSTANCE/bin/opmnctl startall"
echo -e "`date +[%H:%M:%S]` ## Resync of User GUIDs on $SERVERHOST was successfull." > obi_status.tmp
ssh $SERVERHOST '/apps/applobi/OBIEE11g/instances/instance1/bin/opmnctl status' >> obi_status.tmp
cat obi_status.tmp | tee -a $LOGFILE
cat obi_status.tmp | mutt -a $LOGFILE -s "$SUBJECT - SUCCESSFULL" $EMAIL
rm obi_status.tmp
</updateaccountguids></pre>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com5tag:blogger.com,1999:blog-617408063302769488.post-40743330548467102312013-02-05T14:22:00.004-08:002013-03-22T21:52:01.650-07:00Scripted RPD deployments using UDML and WLSTAutomating the RPD deployment in OBIEE 10g was as simple as doing an SCP of the RPD to the OBIEE server, and then bounce the processes. With OBIEE 11g, RPD deployments are now handled through Weblogic, so we need to take some additional steps. Luckily these can be easily scripted thanks to WLST.<br />
<br />
After the jump is a script which can be used to do OBIEE 11g RPD deployments. This script assumes you have setup ssh keys on the various servers so you can login without typing a password (<a href="http://www.linuxproblem.org/art_9.html">http://www.linuxproblem.org/art_9.html</a>).<br />
<br />
<a name='more'></a><br />
At a high level, the script will:
<br />
<ul>
<li>Apply a custom UDML file to your RPD.</li>
<ul>
<li>See <a href="http://adventuresinobiee.blogspot.com/2013/02/creating-udml-file-for-obiee-11g-rpd.html">previous post</a> on how/why to create UDML.</li>
</ul>
<li>Remotely connect to the destination, and SCP the RPD file.</li>
<li>Clean up the destination repository directory to archive all but the last three RPD files</li>
<ul>
<li>This is important, as each deployment creates a new RPD (numbering from 001, 002, 003, etc). The directory can fill up quickly with lots of deployments.</li>
</ul>
<li>Call WLST with a custom python script to:</li>
<ul>
<li>Perform a lock</li>
<li>Deploy the RPD</li>
<li>Commit the changes</li>
<li>Restart the BI server</li>
</ul>
</ul>
We had an additional requirement to change the RPD password in an automated fashion for our QA server. So that is what is going on in the if [ "$1" = "QA" ] section of code. Right now, the new password is hardcoded in the script, but this can easily be changed to be an optional parameter of the script itself.
<br />
<br />
<a href="javascript:void(0);" onclick="javascript:showRPDMessage()">Click me to view deployRPD.sh</a>
<script language="JavaScript">function showRPDMessage(){if(document.getElementById('deployrpd').style.display == "block"){document.getElementById('deployrpd').style.display = "none";}else{document.getElementById('deployrpd').style.display = "block";}}</script>
<br />
<pre id="deployrpd" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">#!/bin/bash
######
# deployRPD.sh
# Created by Ben Mackin (bmackin@mac.com)
#
# This script will deploy an RPD
######
set -o pipefail
checkStatus ()
{
if [ $1 -ne 0 ]; then
if [ "${3-zz}" != "zz" ]; then
exec $3
fi
echo -e "\n`date +[%H:%M:%S]` ###### FAILED ######\n\n" | tee -a $LOGFILE
echo "The deployment failed: $2" | mutt -a $LOGFILE -s "$SUBJECT - FAILED" $EMAIL
exit 1
fi
logOutput "Success\n"
return 0
}
logOutput ()
{
echo -e "`date +[%H:%M:%S]` - ${SERVERHOST} ## $1" | tee -a $LOGFILE
return 0
}
# If there are less than 2 parameters, then show help and exit
if [ $# -lt 4 ]; then
echo -e "\nUsage: deployRPD.sh -h host -u user [-n -a]\n\t-h\tWeblogic host you are migrating to.\n\t-u\tUsername of admin Weblogic user.\n\t-n\tTurn off logging at the RPD level\n\t-a\tChange the RPD password to match the destination weblogic password.\n"
exit -1
fi
set -o pipefail
while getopts "h:u:na" opt; do
case $opt in
h) SERVERHOST=${OPTARG} ;;
u) WEBLOGIC_USER=${OPTARG} ;;
n) NOLOG=1 ;;
a) CHANGERPDPASS=1 ;;
\?) echo "Invalid option: -$OPTARG" | tee -a $LOGFILE; exit ;;
:) echo "Option -$OPTARG requires an argument." | tee -a $LOGFILE; exit ;;
esac
done
# We stored the weblogic password on the destination server for some other scripts, so just go grab it.
# Otherwise, you will want to replace this with your weblogic password
WEBLOGIC_PASS=`ssh ${SERVERHOST} 'tail -n 1 ${FMWHOME}/user_projects/domains/bifoundation_domain/servers/AdminServer/security/custom.sec.properties'`
ADMINPWD=Admin123 # change this to the admin password of the 11g RPD file
SUPPORT_LOC=~/support # folder for support scripts
SVN_LOC=~/svnget # change this to the path of where the RPD to deploy is
UDML_LOC=~/udml/${SERVERHOST}.udml # change this to the path of the udml scripts
RPD_NAME=EnterpriseBusinessAnalytics7963.rpd # change this to the name of the RPD file
LOGFILE=~/logs/${SERVERHOST}_rpddeploy_$(date +%y%m%d_%H%M%S).log
FMWHOME=/apps/applobi/OBIEE11g # change this to the fusion middleware home
DEPLOY_DIR=${FMWHOME}/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository
SUBJECT="OBIEE RPD Deployment on $SERVERHOST - `date +%m/%d/%Y` @ `date +%H:%M:%S`"
EMAIL=null@null.com
logOutput "Doing RPD deployment to $SERVERHOST\n"
##
# Apply the appropriate UDML to the RPD from SVN
##
# Check for lock file. Two nqudmlexec can't process the same .rpd, or the command locks up.
# If there is a lock file, wait 2 seconds and check again. Otherwise, process away!
while [ -f "$SVN_LOC/lockfile" ]
do
sleep `expr $RANDOM % 10`
done
touch $SVN_LOC/lockfile
# Source the bi-init which sets up the environment variables for OBIEE commands
. ${FMWHOME}/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh
logOutput "Apply the UDML to the file from SVN\n"
# Apply a custom UDML which set the LOGLEVEL variable to 0 to disable logging
if [ ${NOLOG-0} -eq 1 ]; then
logOutput "Apply the NOLOG option to the UDML and RPD\n"
cat $UDML_LOC ~/bmackin/udml/NOLOG.udml > /tmp/${SERVERHOST}_COMBINE.udml
${FMWHOME}/Oracle_BI1/bifoundation/server/bin/nqudmlexec -P $ADMINPWD -I /tmp/${SERVERHOST}_COMBINE.udml -B $SVN_LOC/$RPD_NAME -O $SVN_LOC/${SERVERHOST}_$RPD_NAME.new 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: nqudmlexec.sh -P $ADMINPWD -I /tmp/${SERVERHOST}_COMBINE.udml -B $SVN_LOC/$RPD_NAME -O $SVN_LOC/${SERVERHOST}_$RPD_NAME.new" "rm $SVN_LOC/lockfile"
rm /tmp/${SERVERHOST}_COMBINE.udml
else
${FMWHOME}/Oracle_BI1/bifoundation/server/bin/nqudmlexec -P $ADMINPWD -I $UDML_LOC -B $SVN_LOC/$RPD_NAME -O $SVN_LOC/${SERVERHOST}_$RPD_NAME.new 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: nqudmlexec.sh -P $ADMINPWD -I $UDML_LOC -B $SVN_LOC/$RPD_NAME -O $SVN_LOC/${SERVERHOST}_$RPD_NAME.new" "rm $SVN_LOC/lockfile"
fi
# Cleanup the temp files which the nqudmlexec command generates
rm $SVN_LOC/lockfile
rm $SVN_LOC/*.Log
rm -rf ${FMWHOME}/instances/instance1/bifoundation/OracleBIServerComponent/$SERVERHOST
# Check if the change password parameter was set
if [ ${CHANGERPDPASS-0} -eq 1 ]; then
OLDADMINPWD=$ADMINPWD
ADMINPWD=$WEBLOGIC_PASS # We are just setting the RPD password to the weblogic password
logOutput "Change the RPD password for $SERVERHOST.\n"
echo -e "${OLDADMINPWD}\n${ADMINPWD}" | ${FMWHOME}/Oracle_BI1/bifoundation/server/bin/obieerpdpwdchg -I $SVN_LOC/${SERVERHOST}_$RPD_NAME.new -O $SVN_LOC/${SERVERHOST}_$RPD_NAME.new2
checkStatus "$?" "The deployment failed: obieerpdpwdchg -I $SVN_LOC/${SERVERHOST}_$RPD_NAME.new -O $SVN_LOC/${SERVERHOST}_$RPD_NAME.new2"
rm $SVN_LOC/*.Log
rm $SVN_LOC/${SERVERHOST}_$RPD_NAME.new
mv $SVN_LOC/${SERVERHOST}_$RPD_NAME.new2 $SVN_LOC/${SERVERHOST}_$RPD_NAME.new
fi
#SSH to remote machine, clean up old files, deploy the newly gotten file.
logOutput "Clear out the .Log and .Sav files on $SERVERHOST\n"
ssh $SERVERHOST "rm '$DEPLOY_DIR'/*.Log" 2>&1 | tee -a $LOGFILE
ssh $SERVERHOST "rm '$DEPLOY_DIR'/*.Sav" 2>&1 | tee -a $LOGFILE
ssh $SERVERHOST "mkdir $DEPLOY_DIR/archive" 2>&1 | tee -a $LOGFILE
logOutput "Success\n"
# Each RPD deployment creates a new numbered file. Move all but the last 3 to the archive folder
logOutput "Save only the last 3 rpd file, move everything else to the archive folder\n"
ssh $SERVERHOST 'bash -s' < $SUPPORT_LOC/cleanuprpd.sh 2>&1 | tee -a $LOGFILE
logOutput "Success\n"
# Move the RPD to the deployment directory
logOutput "Move the file to the deployment directory\n"
scp $SVN_LOC/${SERVERHOST}_$RPD_NAME.new $SERVERHOST:$DEPLOY_DIR/$RPD_NAME 2>&1 | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: scp $SVN_LOC/${SERVERHOST}_$RPD_NAME.new $SERVERHOST:$DEPLOY_DIR/$RPD_NAME"
# Cleanup the temp files
rm $SVN_LOC/${SERVERHOST}_$RPD_NAME.new
# Run WLST with the custom deployment script
logOutput "Do weblogic scripting to deploy the file\n"
${FMWHOME}/Oracle_BI1/common/bin/wlst.sh $SUPPORT_LOC/wlstDeployRPD.py -u $WEBLOGIC_USER -p $WEBLOGIC_PASS -h $SERVERHOST -o 7001 -r $ADMINPWD | tee -a $LOGFILE
checkStatus "$?" "The deployment failed: wlst.sh $SUPPORT_LOC/wlstDeployRPD.py -u $WEBLOGIC_USER -p $WEBLOGIC_PASS -h $SERVERHOST -o 7001 -r $ADMINPWD"
# Remove the pre-deployed file from the remove server
logOutput "Remove the pre-deployed file\n"
ssh $SERVERHOST "rm '$DEPLOY_DIR'/'$RPD_NAME'" 2>&1 | tee -a $LOGFILE
logOutput "Success\n"
# Clear the OBIEE cache on the remove server
logOutput "Clear the remote OBIEE cache\n"
ssh $SERVERHOST "cd $SUPPORT_LOC/..; ./clearCache.sh" 2>&1 | tee -a $LOGFILE
echo -e "\n`date +[%H:%M:%S]` - ${SERVERHOST} ## Deployment to $SERVERHOST was successfull." > ${SERVERHOST}_obi_status.tmp
ssh $SERVERHOST '${FMWHOME}/instances/instance1/bin/opmnctl status' >> ${SERVERHOST}_obi_status.tmp
cat ${SERVERHOST}_obi_status.tmp | tee -a $LOGFILE
cat ${SERVERHOST}_obi_status.tmp | mutt -a $LOGFILE -s "$SUBJECT - SUCCESSFULL" $EMAIL
rm ${SERVERHOST}_obi_status.tmp
</pre>
<br />
<a href="javascript:void(0);" onclick="javascript:showWLSTMessage()">Click me to view the wlstDeployRPD.py file</a><br />
<script language="JavaScript">function showWLSTMessage(){if(document.getElementById('wlstdeployrpd').style.display == "block"){document.getElementById('wlstdeployrpd').style.display = "none";}else{document.getElementById('wlstdeployrpd').style.display = "block";}}</script>
<br />
<pre id="wlstdeployrpd" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">import sys
import os
from java.lang import System
import getopt
def usage():
print "Usage:"
print "genWlstDeployRPD -u user -p password -h host -o port -r adminpwd"
try:
opts, args = getopt.getopt(sys.argv[1:], "u:p:h:o:r:",
["user=", "password=", "host=", "port=", "adminpwd="])
except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)
user = ''
password = ''
host = ''
port = ''
adminpwd = ''
for opt, arg in opts:
if opt == "-u":
user = arg
elif opt == "-p":
password = arg
elif opt == "-h":
host = arg
elif opt == "-o":
port = arg
elif opt == "-r":
adminpwd = arg
if user == "":
print "Missing \"-u user\" parameter."
usage()
sys.exit(2)
elif password == "":
print "Missing \"-p password\" parameter."
usage()
sys.exit(2)
elif host == "":
print "Missing \"-h host\" parameter."
usage()
sys.exit(2)
elif port == "":
print "Missing \"-o port\" parameter."
usage()
sys.exit(2)
elif adminpwd == "":
print "Missing \"-r adminpwd\" parameter."
usage()
sys.exit(2)
connect(user, password, host+":"+port)
# Be sure we are in the root
cd('..\..')
print(host + ': Connecting to Domain ...')
try:
domainCustom()
except:
print(host + ': Already in domainCustom')
print(host + ': Go to biee admin domain')
cd('oracle.biee.admin')
# go to the server configuration
print host + ': Go to BIDomain.BIInstance.ServerConfiguration MBean'
cd('oracle.biee.admin:type=BIDomain.BIInstance.ServerConfiguration,biInstance=coreapplication,group=Service')
# Lock the System
print(host + ': Calling lock ...')
cd('..')
cd('oracle.biee.admin:type=BIDomain,group=Service')
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke('lock', objs, strs)
except:
print(host + ': System already locked')
cd('..')
# go to the server configuration
cd('oracle.biee.admin:type=BIDomain.BIInstance.ServerConfiguration,biInstance=coreapplication,group=Service')
print(host + ': Uploading RPD')
params = jarray.array(['/apps/applobi/OBIEE11g/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository/AJ_EnterpriseBusinessAnalytics7963.rpd',adminpwd],java.lang.Object)
sign = jarray.array(['java.lang.String', 'java.lang.String'],java.lang.String)
invoke( 'uploadRepository', params, sign)
# Commit the system
print(host + ': Commiting Changes')
cd('..')
cd('oracle.biee.admin:type=BIDomain,group=Service')
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke('commit', objs, strs)
except:
print(host + ': System not locked')
# Restart the system
print(host + ': Restarting OBIEE processes')
cd('..\..')
cd('oracle.biee.admin')
cd('oracle.biee.admin:type=BIDomain.BIInstance,biInstance=coreapplication,group=Service')
print(host + ': Stopping the BI instance')
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke('stop', params, signs)
BIServiceStatus = get('ServiceStatus')
print(host + ': BI ServiceStatus ' + BIServiceStatus)
print(host + ': Starting the BI instance')
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke('start', params, signs)
BIServerStatus = get('ServiceStatus')
print(host + ': BI ServerStatus ' + BIServerStatus)
</pre>Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com1tag:blogger.com,1999:blog-617408063302769488.post-15497602331151344672013-02-05T14:22:00.001-08:002013-03-22T21:52:01.648-07:00Creating a UDML file for OBIEE 11g RPDThe ability to use UDML for scripted OBIEE RPD changes has been around since OBIEE 10g. UDML files are very useful in order to do migrations, as they can contain the connection pool information and any variables which are environment specific. I am currently using UDML within our enivonrments to allow for migration of an RPD from Development through to Produciton.<br />
<br />
Take a jump to view the steps to create UDML files for your environments, along with a sample UDML file.<br />
<br />
<a name='more'></a><br />
If you are on Linux/Solaris then source the bi-init which sets up the environment variables for OBIEE commands
<br />
<br />
. {OBIEE11GInstance}/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh<br />
<br />
Run the nqUDMLgen command located in the Oracle_BI1 folder:
<br />
<br />
{OBIEE11GFMHome}/Oracle_BI1/bifoundation/server/bin/
<br />
<br />
The command to run, with parameters:
<br />
<br />
nqudmlgen –P <rpd Admin Password><repositorypassword> -R <Path to RPD File> <path file="" rpd="" to=""> -O <path file="" ourput="" to="" udml="">
<Path to Output UDML file></path></path></repositorypassword><br />
<repositorypassword><path file="" rpd="" to=""><path file="" ourput="" to="" udml=""></path></path></repositorypassword><br />
<repositorypassword><path file="" rpd="" to=""><path file="" ourput="" to="" udml="">This will create a large UDML file of your entire repository. You then need to fish out just the Connection Pools and Variables you care about which will change form environment to environment. Paste those components into a new file, and call it <ENVIRONMENT><something>.udml</something></path></path></repositorypassword><br />
<repositorypassword><path file="" rpd="" to=""><path file="" ourput="" to="" udml=""><something></something></path></path></repositorypassword><br />
<repositorypassword><path file="" rpd="" to=""><path file="" ourput="" to="" udml=""><something>Note: You can change most things from the UDML directly, like database schemas/sids, or variable values. The one thing you can is the connection pool passwords. Those you will need to first change in the RPD, generate the UDML, and then paste in.
Once you have the UDML files, migrations are a snap, as you just apply the UDML file of the environment you are going to.</something></path></path></repositorypassword>
<br />
<br />
Here is a sample UDML file I created for a development environment which is using OBIA. Notice I only captured the Connection Pools and Variables I care about changing from environment to environment.
<br />
<br />
<a href="javascript:void(0);" onclick="javascript:showUDMLMessage()">Click me to view DEV.UDML</a>
<br />
<script language="JavaScript">function showUDMLMessage(){if(document.getElementById('udml').style.display == "block"){document.getElementById('udml').style.display = "none";}else{document.getElementById('udml').style.display = "block";}}</script>
<pre id="udml" style="background-color: #e4e4e4; border-style: solid; border-width: 1px; display: none; height: 300px; overflow: auto; white-space: pre;">DECLARE CONNECTION POOL "Oracle EBS OLTP"."Oracle EBS OLTP Connection Pool" AS "Oracle EBS OLTP Connection Pool" UPGRADE ID '80cefa95-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(ORA_EBS_OLTP_DSN)}
TIME OUT 300
MAX CONNECTIONS 64
TYPE 'Default'
USER 'VALUEOF(ORA_EBS_OLTP_USER)'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E82367C0723D9D9723721C4CB50D15EA005874D6559DF134D9F444104976710755A370704A09A9E272FE056558D48CA5B F6F02A8BE79927CC999036C463525CBDD5975D0E0EF0895CF1E6D1C4BA4262B9'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
POST CONNECT ('call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session(''valueof(NQ_SESSION.ICX_SESSION_COOKIE)'')')
DESCRIPTION {User Name is VALUEOF(ORACLE_OLTP_USER)
Data source name: VALUEOF(ORA_EBS_OLTP_DSN)}
PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle EBS OLTP"."Oracle EBS OLTP InitBlocks Connection Pool" AS "Oracle EBS OLTP InitBlocks Connection Pool" UPGRADE ID '80b43a95-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(ORA_EBS_OLTP_DSN)}
TIME OUT 300
MAX CONNECTIONS 64
TYPE 'Default'
USER 'VALUEOF(ORA_EBS_OLTP_USER)'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E82367C0723D9D9723721C4CB50D15EA005874D6559DF134D9F444104976710755A370704A09A9E272FE056558D48CA5B F6F02A8BE79927CC999036C463525CBDD5975D0E0EF0895CF1E6D1C4BA4262B9'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
POST CONNECT ('call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session(''valueof(NQ_SESSION.ICX_SESSION_COOKIE)'')')
DESCRIPTION {User Name is VALUEOF(ORACLE_OLTP_USER)
Data source name: VALUEOF(ORA_EBS_OLTP_DSN)}
PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle EBS OLTP"."Oracle EBS OLTP DbAuth Connection Pool" AS "Oracle EBS OLTP DbAuth Connection Pool" UPGRADE ID '80b43a97-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(ORA_EBS_OLTP_DSN)}
TIME OUT 300
MAX CONNECTIONS 80
TYPE 'Default'
USER ':USER'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E82367C0723D9D9723721C4CB50D15EA005874D6559DF134D9F444104976710755A370704A09A9E272FE056558D48CA5B F6F02A8BE79927CC999036C463525CBDD5975D0E0EF0895CF1E6D1C4BA4262B9'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
POST CONNECT ('call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session(''valueof(NQ_SESSION.ICX_SESSION_COOKIE)'')')
DESCRIPTION {User name is :USER
Password field should contain :PASSWORD}
PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Connection Pool" AS "Oracle Data Warehouse Connection Pool" UPGRADE ID '00238434-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 300
MAX CONNECTIONS 100
TYPE 'Default'
USER 'VALUEOF(OLAP_USER)'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912EA392739D8ABA5B5D77C02AA523C79850FA383C28CBE406264CBEADCF063BBA55 BB9F4B07EB3F9DB1F091E66E88ABFFEAF117D1D661F8060EF6214536FEBFC704'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is VALUEOF(OLAP_USER)
Data source name is VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Repository Initblocks Connection Pool" AS "Oracle Data Warehouse Repository Initblocks Connection Pool" UPGRADE ID '8058fb0a-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 300
MAX CONNECTIONS 10
TYPE 'Default'
USER 'VALUEOF(OLAP_USER)'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912EA392739D8ABA5B5D77C02AA523C79850FA383C28CBE406264CBEADCF063BBA55 BB9F4B07EB3F9DB1F091E66E88ABFFEAF117D1D661F8060EF6214536FEBFC704'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is VALUEOF(OLAP_USER)
Data source name is VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle Analytics Usage"."Usage Tracking Connection Pool" AS "Usage Tracking Connection Pool" UPGRADE ID '00238436-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 300
MAX CONNECTIONS 100
TYPE 'OCI10G'
USER 'DEV_BIPLATFORM'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E6FE385B517924A7854D8ADE26FFF384EF7C27CB462973FCBE472088DA0566674A6EF3442FE326E7D98406D5E4DE2C90D 9A32196C9984E27780843E928862E888427B6059D88AE69E9CE6849A02B18340'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is DEV_BIPLATFORM
Data source name: VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
DECLARE CONNECTION POOL "Oracle Analytics Usage"."Usage Tracking Writer Connection Pool" AS "Usage Tracking Writer Connection Pool" UPGRADE ID '002b2498-0bd5-0000-714b-e31d00000000'
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 300
MAX CONNECTIONS 100
TYPE 'OCI10G'
USER 'DEV_BIPLATFORM'
AES_PASSWORD '6C6F0BE01FC4111F3AC2236A364D912E6FE385B517924A7854D8ADE26FFF384EF7C27CB462973FCBE472088DA0566674A6EF3442FE326E7D98406D5E4DE2C90D 9A32196C9984E27780843E928862E888427B6059D88AE69E9CE6849A02B18340'
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is DEV_BIPLATFORM
Data source name: VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLAPTBO" AS "OLAPTBO" UPGRADE ID '00238487-0bd7-0000-714b-e31d00000000' EXPRESSION {'OBIBAW'}
DESCRIPTION {Data Warehouse table owner}
PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLAP_USER" AS "OLAP_USER" UPGRADE ID '0023844e-0bd7-0000-714b-e31d00000000' EXPRESSION {'OBIBAW'}
DESCRIPTION {Database user used to connect to the data warehouse}
PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLAP_DSN" AS "OLAP_DSN" UPGRADE ID '0023844f-0bd7-0000-714b-e31d00000000' EXPRESSION {'OBID'}
DESCRIPTION {Data Source Name of the Data Warehouse. It is used in the Connection pool definitions, to normalize this information in the repository.}
PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLTP_USER" AS "OLTP_USER" UPGRADE ID '0023844d-0bd7-0000-714b-e31d00000000' EXPRESSION {'APPS'}
DESCRIPTION {Database user used to connect to the transaction database. }
PRIVILEGES ( READ);
DECLARE RP VARIABLE "OLTP_DSN" AS "OLTP_DSN" UPGRADE ID '00238450-0bd7-0000-714b-e31d00000000' EXPRESSION {'DEV2'}
DESCRIPTION {Data Source Name of the Transaction Database.}
PRIVILEGES ( READ);
DECLARE RP VARIABLE "ORA_EBS_OLTP_USER" AS "ORA_EBS_OLTP_USER" UPGRADE ID '80b43a91-0bd7-0000-714b-e31d00000000' EXPRESSION {'APPS'}
PRIVILEGES ( READ);
DECLARE RP VARIABLE "ORA_EBS_OLTP_DSN" AS "ORA_EBS_OLTP_DSN" UPGRADE ID '80b43a9c-0bd7-0000-714b-e31d00000000' EXPRESSION {'DEV2'}
PRIVILEGES ( READ);
</pre>
Ben Mackinhttp://www.blogger.com/profile/05702474266807766612noreply@blogger.com1