Changing password of the MDS and BIPLATFORM schemas used by OBIEE 11g

At 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.

After the jump is an example script, along with the python for WLST, to perform this task.


Using what I've picked up with WLST I created a script to automate this process. At a high level, the script will

• Log into a database and change the password for the MDS and BIPLATFORM schemas
• Log into Enterprise Manager, and update the password for MDS in the JDBC data sources
• Log into Enterprise Manager, and update the password for BIPLATFORM in the JDBC data sources
• Log into Enterprise Manager, and update the password for EPMSystemRegistry in the JDBC data sources
• Log into Enterprise Manager, and update the password for BIPLATFORM in the Deployments section.
• Restart the OBIEE Services

You will need to manually restart all of the processes (AdminServer and biserver) after this completes.

Click me to view resetMDSBIPlatformPwd.sh

#!/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

Click me to view wlstMDSBIPlatformPwd.py

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)