Feed aggregator

Setting Environment Variables in Application Server/Process Scheduler Tuxedo Domains

David Kurtz - Thu, 2016-07-07 10:54
The topic of how to manage environment variables was mentioned recently on the PeopleSoft Administrator Podcast
Recently I have built a pair of PeopleSoft environments for a proof-of-concept and have faced into exactly this problem.  I have two PS_APP_HOMEs (application homes) share the same PS_HOME (PeopleTools home).  The environment variables need to be correct before I open psadmin to administer the application server and process scheduler domains.
On Unix, I would usually recommend running domains for different PS_APP_HOMEs in different Unix user accounts.  Thus environmental variables can be set up in the .profile or .bashrc scripts.  The processes then run as different Unix users which makes them easier to monitor.  The Unix users should be in the same Unix group and have group permissions to the PS_HOME directory.  This approach is not possible on Windows.
The alternative is to have a scripts or a menu that sets up those variables before you enter psadmin.
I noticed that there is a new menu in the domain configuration menu in psadmin which permits you to set environmental variables that are then built into the Tuxedo domain configuration.  In fact this has always been possible by editing the psappsrv.ubx and psprcs.ubx files directly to cause variables to be created in the psappsrv.env and psprcs.env files, but now you just have to enter the variables through the menu in psadmin.
When we start psadmin we can see the relevant environmental parameters.   PS_APP_HOME points to my HR installation.
PSADMIN -- PeopleTools Release: 8.54.16
Copyright (c) 1996, 2014, Oracle. All rights reserved.

--------------------------------
PeopleSoft Server Administration
--------------------------------

PS_CFG_HOME /home/psadm1/psft/pt/8.54
PS_HOME /opt/oracle/psft/pt/tools
PS_APP_HOME /opt/oracle/psft/pt/hr91
PS_CUST_HOME /opt/oracle/psft/pt/hr91/pscust

1) Application Server
...
But what if I have a Financials installation under the same PS_APP_HOME?  Option 16 of the configuration menu lets me define environmental settings.  Something very similar happens in the Process Scheduler configuration.
----------------------------------------------
Quick-configure menu -- domain: RASC3K
----------------------------------------------
Features Settings
========== ==========
1) Pub/Sub Servers : Yes 17) DBNAME :[XXXXXX]
...
Actions
=========
14) Load config as shown
15) Custom configuration
16) Edit environment settings
h) Help for this menu
q) Return to previous menu

HINT: Enter 17 to edit DBNAME, then 14 to load
So, I have added PS_APP_HOME, PS_CUST_HOME and INTERFACE_HOME and they have become a part of the configuration of the Tuxedo domain.
--------------------------------------
PeopleSoft Domain Environment Settings
--------------------------------------
Domain Name: RASC3K

TEMP :[{LOGDIR}{FS}tmp]
TMP :[{LOGDIR}{FS}tmp]
TM_BOOTTIMEOUT :[120]
TM_RESTARTSRVTIMEOUT :[120]
TM_BOOTPRESUMEDFAIL :[Y]
FLDTBLDIR32 :[{$TUXDIR}{FS}udataobj]
FIELDTBLS32 :[jrep.f32,tpadm]
ALOGPFX :[{LOGDIR}{FS}TUXACCESSLOG]
INFORMIXSERVER :[{$Startup\ServerName}]
COBPATH :[{$PS_APP_HOME}/cblbin:{$PS_HOME}/cblbin]
PATH :[{$PATH}:{$Domain Settings\Add to PATH}]
PS_APP_HOME :[/opt/oracle/psft/pt/fin91]
PS_CUST_HOME :[/opt/oracle/psft/pt/fin91/pscust]
INTERFACE_HOME :[/opt/oracle/psft/pt/fin91/pscust/interfaces]

1) Edit environment variable
2) Add environment variable
3) Remove environment variable
4) Comment / uncomment environment variable
5) Show resolved environment variables
6) Save
h) Help for this menu
q) Return to previous menu

Command to execute (1-6, h or q) :
What is going on here?  These variables have been added to the PS_ENVFILE section of psappsrv.ubx.
# ----------------------------------------------------------------------
*PS_ENVFILE
TEMP={LOGDIR}{FS}tmp
TMP={LOGDIR}{FS}tmp
TM_BOOTTIMEOUT=120
TM_RESTARTSRVTIMEOUT=120
TM_BOOTPRESUMEDFAIL=Y
FLDTBLDIR32={$TUXDIR}{FS}udataobj
FIELDTBLS32=jrep.f32,tpadm
ALOGPFX={LOGDIR}{FS}TUXACCESSLOG
{WINDOWS}
COBPATH={$PS_HOME}\CBLBIN%PS_COBOLTYPE%
INFORMIXSERVER={$Startup\ServerName}
# Set IPC_EXIT_PROCESS=1 to use ExitProcess to terminate server process.
# Set IPC_TERMINATE_PROCESS=1 to use TerminateProcess to terminate server process.
# If both are set, TerminateProcess will be used to terminate server process.
#IPC_EXIT_PROCESS=1
IPC_TERMINATE_PROCESS=1
PATH={$PS_HOME}\verity\{VERITY_OS}\{VERITY_PLATFORM}\bin;{$PATH};{$Domain Settings\Add to PATH}
{WINDOWS}
{UNIX}
INFORMIXSERVER={$Startup\ServerName}
COBPATH={$PS_APP_HOME}/cblbin:{$PS_HOME}/cblbin
PATH={$PATH}:{$Domain Settings\Add to PATH}
{UNIX}
PS_APP_HOME=/opt/oracle/psft/pt/fin91
PS_CUST_HOME=/opt/oracle/psft/pt/fin91/pscust
INTERFACE_HOME=/opt/oracle/psft/pt/fin91/pscust/interfaces
They then appear in the psappsrv.env file that is generated when the domain is configured.  This file contains fully resolved values of environmental variables that are set by every tuxedo application server process when it starts.
TEMP=/home/psadm1/psft/pt/8.54/appserv/XXXXXX/LOGS/tmp
TMP=/home/psadm1/psft/pt/8.54/appserv/XXXXXX/LOGS/tmp
TM_BOOTTIMEOUT=120
TM_RESTARTSRVTIMEOUT=120
TM_BOOTPRESUMEDFAIL=Y
FLDTBLDIR32=/opt/oracle/psft/pt/bea/tuxedo/udataobj
FIELDTBLS32=jrep.f32,tpadm
ALOGPFX=/home/psadm1/psft/pt/8.54/appserv/XXXXXX/LOGS/TUXACCESSLOG
# Set IPC_EXIT_PROCESS=1 to use ExitProcess to terminate server process.
# Set IPC_TERMINATE_PROCESS=1 to use TerminateProcess to terminate server process.
# If both are set, TerminateProcess will be used to terminate server process.
#IPC_EXIT_PROCESS=1
IPC_TERMINATE_PROCESS=1
IPC_TERMINATE_PROCESS=1
INFORMIXSERVER=
COBPATH=/opt/oracle/psft/pt/fin91/cblbin:/opt/oracle/psft/pt/tools/cblbin
PATH=/opt/oracle/psft/pt/fin91/bin:/opt/oracle/psft/pt/fin91/bin/interfacedrivers:/opt/oracle/psft/pt/tools/jre/bin:/opt/oracle/psft/pt/tools/appserv:/opt/oracle/psft/pt/tools/setup:/opt/oracle/psft/pt/bea/tuxedo/bin:.:/opt/oracle/psft/pt/oracle-client/12.1.0.1/bin:/opt/oracle/psft/pt/oracle-client/12.1.0.1/OPatch:/opt/oracle/psft/pt/oracle-client/12.1.0.1/perl/bin:/opt/mf/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/opt/oracle/psft/pt/tools/bin:/opt/oracle/psft/pt/tools/bin/sqr/ORA/bin:/opt/oracle/psft/pt/tools/verity/linux/_ilnx21/bin:/home/psadm1/bin::.
PS_APP_HOME=/opt/oracle/psft/pt/fin91
PS_CUST_HOME=/opt/oracle/psft/pt/fin91/pscust
INTERFACE_HOME=/opt/oracle/psft/pt/fin91/pscust/interfaces
WarningI found that I had to specify fully resolved paths for the variables I defined.  I do try setting variables in terms of other variables,

PS_APP_HOME :[/opt/oracle/psft/pt/fin91]
PS_CUST_HOME :[{$PS_APP_HOME}{FS}pscust]
INTERFACE_HOME :[{$PS_CUST_HOME}{FS}interfaces]

but I started to get errors.
==============ERROR!================
Value for PS_CUST_HOME: {$PS_APP_HOME}{FS}pscust, is invalid. Your environment
may not work as expected.
==============ERROR!================
And some variables were not fully resolved in the psappsrv.env file.
PS_APP_HOME=/opt/oracle/psft/pt/fin91
PS_CUST_HOME=/opt/oracle/psft/pt/fin91/pscust
INTERFACE_HOME={$PS_APP_HOME}{FS}pscust/interfaces
Configuration Settings in the .ubx –v- .cfgMy only reservation is that there is now environment specific configuration in the psappsrv.ubx file, rather than the psappsrv.cfg file.   When I have done this in the past I would create additional variables in psappsrv.cfg that were referenced from the psappsrv.ubx file.  Thus the psappsrv.ubx was consistent across environments, and all the configuration is in the main configuration file psappsrv.cfg.
Although, you can add additional variables in psappsrv.cfg, thus
[Domain Settings]
;=========================================================================
; General settings for this Application Server.
;=========================================================================

Application Home=/opt/oracle/psft/pt/fin91
Custom Directory=pscust
Interface Directory=pscust/interfaces

and then reference them in the variables, and they will resolve correctly in the psappsrv.env.
PS_APP_HOME={$Domain Settings\Application Home}
PS_CUST_HOME={$Domain Settings\PS_APP_HOME}{FS}{$Domain Settings\Custom Directory}
INTERFACE_HOME={$Domain Settings\PS_APP_HOME}{FS}{$Domain Settings\Interface Directory}
You may experience errors in psadmin
==============ERROR!================
Value for PS_APP_HOME: {$Domain Settings\Application Home}, is invalid. Your
environment may not work as expected.
==============ERROR!================

==============ERROR!================
Value for PS_CUST_HOME: {$Domain Settings\Application Home}{FS}{$Domain
Settings\Custom Directory}, is invalid. Your environment may not work as
expected.
==============ERROR!================
ConclusionUsing this technique, it does not matter how environment variables are set when you go into psadmin to start the application server, the correct setting is defined in the Tuxedo domain and overrides that.
You have always been able to do this in Tuxedo, but you would have had to edit the psappsrv.ubx file yourself, now the menu allows you to administer this.
There is no way to view the psappsrv.ubx and psappsrv.env files from within psadmin, only psappsrv.cfg can be opened.  If you want to check your settings have reached the psappsrv.env file, you will need to leave psadmin and look in the files for yourself.

IOUG WebCenter Special Interest Group Survey

WebCenter Team - Thu, 2016-07-07 10:07

The WebCenter Special Interest Group (SIG) is interested in hearing from you! We are conducting a brief four question survey to get a better understanding of where Oracle WebCenter customers are at today with their current usage of WebCenter solutions and to gauge your interest in learning more about WebCenter topics.

Please take a moment to answer this short survey. Your input is important to us! Understanding where WebCenter customers are at now is critical to helping you move forward in the future.

Regards,
The WebCenter SIG

Website validation using regexp_like.

Tom Kyte - Thu, 2016-07-07 09:06
Hi, Could you please help to get website validation query using regexp_like. e.g. www.google.com https//:www.google.com both should get validated. Thanks in advance.
Categories: DBA Blogs

Datatype issue.

Tom Kyte - Thu, 2016-07-07 09:06
Hi all, I have an issue with column datatype.Can you please look at this issue. I have two tables emp1 and emp2 with empno column in both tables in table emp1 column empno is nvarchar2 and in table emp2 column empno is number datatype I am tryin...
Categories: DBA Blogs

how to debug the SQL logic execution ?

Tom Kyte - Thu, 2016-07-07 09:06
AskTom, I have a question about the 'SQL logic execution': Can Oracle Database log the logic of the SQL execution ? (which condition of the WHERE clause failed ?) example: select 1 from Dual WHERE 1 = 1 AND 1 = 1 + 1 --line 5 ; C...
Categories: DBA Blogs

Fuzzy Name Match Stored Procedure Optimization

Tom Kyte - Thu, 2016-07-07 09:06
Hello, Have written PL/SQL stored proc 'FuzzyNameMatch' that interrogates first, middle, last names from a single column in two distinct tables, ie source and compare columns. The algo parses shorter strings through longer and increments counter v...
Categories: DBA Blogs

Need to generate numbers between a given range for each record.

Tom Kyte - Thu, 2016-07-07 09:06
Hi Team, I have below sample table as input : ID MIN MAX 1 5 10 2 3 5 And I want output as follows: ID value 1 5 1 6 1 7 1 8 1 9 2 3 2 4 Id column is a p...
Categories: DBA Blogs

Performance & features of OCCI vs OCI

Tom Kyte - Thu, 2016-07-07 09:06
I'm going to develop an application that needs max performance. I will need: - batch inserts of BLOB - batch updates of BLOB - batch select of BLOB I consider to use OCCI but I'm not sure if it supports all optimizations that are done in OCI. ...
Categories: DBA Blogs

Materialized view logs and unique indexes

Tom Kyte - Thu, 2016-07-07 09:06
I have a table with moderate DML activity, a materialized view log on that table, and one materialized view on a remote database. After some activity in the source app, I can see multiple rows in the MV log with the same m_row$$, with a DMLTYPE$ v...
Categories: DBA Blogs

Best Data Type To use

Tom Kyte - Thu, 2016-07-07 09:06
I've a Table with columns EMPCODE,DAY1,DAY2...DAY31 I've to loop from Day1 to Day31 so what is the best way of doing this,If I should use collections then how should I assign Day1--31 to a Collection and what Collection Should I use. Can anyone p...
Categories: DBA Blogs

Privacy - InMobi Pays $1M In Penalties

Abhinav Agarwal - Thu, 2016-07-07 08:40
image credit: WDnet Agency, pexels.com
In 2015 I had written a series of articles on the e-commerce battle between Flipkart and Amazon, one of which focused on why companies are so obsessed with apps Mobile Apps: There’s Something (Profitable) About Your Privacy.
Now it turns out that InMobi has agreed to pay a US$950,000 in civil penalties to "settle charges it violated federal law." InMobi is described by the US Federal Trade Commission complaint thus: "describes itself as the “world’s largest independent mobile advertising company.” In February 2015, Defendant reported its advertising network had reached over one billion unique mobile devices, with 19% of those devices located in North America, and had served 6 billion ad requests per day."
According to the FTC complaint [bold emphasis mine], "Even if the consumer had restricted an application’s access to the location API, until December 2015, Defendant still tracked the consumer’s location and, in many instances, served geo-targeted ads, by collecting information about the WiFi networks that the consumer’s device connected to or that were in-range of the consumer’s device. "


Worse, since the InMobi SDK was used by third-party app developers to integrate within their apps and serve targeted ads to children, the FTC charged that InMobi had also violated the Children's Online Privacy Protection Act Rule (COPPA) of 1998.
In my 2015 article I had written about other notable privacy violations:
In 2012, before its IPO, JustDial’s app was removed from the Google Play Store. It was alleged that the updated version of the JustDial app had “started retrieving and storing the user’s entire phone book, without a warning or disclaimer.” Thereafter, JustDial’s mobile “Terms and Conditions” were updated to include the following line: “You hereby give your express consent to Justdial to access your contact list and/or address book for mobile phone numbers in order to provide and use the Service.”
In 2013, US-based social networking app Path was caught as it “secretly copied all its users’ iPhone address books to its private servers.” Action was swift. The FTC investigated and reached a settlement with Path, which required “Path, Inc. to establish a comprehensive privacy program and to obtain independent privacy assessments every other year for the next 20 years. The company also will pay $800,000 to settle charges that it illegally collected personal information from children without their parents’ consent.” In the US, a person’s address book “is protected under the First Amendment.” When the controversy erupted, it was also reported that “A person’s contacts are so sensitive that Alec Ross, a senior adviser on innovation to Secretary of State Hillary Rodham Clinton, said the State Department was supporting the development of an application that would act as a “panic button” on a smartphone, enabling people to erase all contacts with one click if they are arrested during a protest.” Of course, politics is not without its dose of de-rigueur dose of irony. That dose was delivered in 2015 when it emerged that Hillary Clinton had maintained a private email account even as she was Secretary of State in the Barack Obama presidency and refused to turn over those emails.
Privacy protection is an area that needs urgent attention from India's regulatory authorities. What the Indian telecom regulator, the Telecom Regulatory Authority of India, is doing remains a matter of speculation, unfortunately. Its flip-flops over the last one year on Net Neutrality do not inspire much confidence either.

References:
This post first appeared in LinkedIn Pulse on June 23, 2016.
Screenshot of article on LinkedIn Pulse


Remote DBA Benefits Comparison Series- Part 3- Collective Knowledge & Expertise

Chris Foot - Thu, 2016-07-07 08:00

This is Part 3 of a series that compares in-house personnel, consultants and remote DBA service providers.  In Part 1, we talked about the cost reduction benefits that remote DBA service providers offer customers.  In Part 2 we discussed service continuity and quality as well as the flexibility benefits customers receive when utilizing a remote DBA service provider.

In this installment of the series, we’ll focus on the collective knowledge benefits and subject matter expertise remote DBA service providers offer clients.  I use my organization, RDX, as an example.  

Oracle HEXTORAW Function with Examples

Complete IT Professional - Thu, 2016-07-07 06:00
The Oracle HEXTORAW function is a basic conversion function used in Oracle SQL. Learn how to use it and see some examples in this article. Purpose of the Oracle HEXTORAW Function The purpose of the HEXTORAW function is to convert a hexadecimal value into a raw value.   Syntax The syntax for the Oracle HEXTORAW […]
Categories: Development

Create Weblogic users for ServiceBus

Darwin IT - Thu, 2016-07-07 03:57
At one of my customers there was the question of creating users that needed to log on to EM (Fusion MiddleWare Control) and Service bus console to be able  to deploy SB en WLS Artefacts (queues, datasources etc.) on the Dev and Test environment. And to be able to do some monitoring on Acceptance. Further more they still used weblogic as the only, shared, administrator user. For the latter Oracle recommends to create at least one other user, to prevent you from an accidental admin-lockout. So I'd create named admin-users for each administrator.

Since there were several users to provision on several systems, it would be convenient to have a script to create the users and the particular groups.

As for the Roles there we need to differentiate to WebLogic default roles and and groups, and Service Bus roles.

To be able to log on to the EM, WLS Console and/or Servicebus the user at least needs the WLS Monitor role, which is provided using the Monitors group. But to be able to start and stop servers (that the developers need/want on dev and at this customer also test) you need the Operators group. To be able to create WLS Artefacts, the user need to be in the Deployers group. More on that you can read in chapter '6 Users, Groups, And Security Roles' in the document 'Securing Resources Using Roles and Policies for Oracle WebLogic Server' (12.2.1).

The Servicebus Application Specific Roles are described in the paragraph '11.1.3 Roles' within chapter '11.1 Understanding Oracle Service Bus Application Security' of the document 'Administering Oracle Service Bus'. How to add those to weblogic users and groups is quite clearly described in paragraph '19.1.3.3 Adding a Product-Specific Administration Role to the Enterprise Deployment Administration Group' of the 'Enterprise Deployment Guide for Oracle SOA Suite' (12.2.1), so I'm not going to copy that.

In the following script first administrator-users are created and added to the administrators group.
For every user group that is created (Administrators, OSB Developers  and OSB Testers), the property file (example is provided later) contains three comma seperated lists for the usernames, passwors and the user descriptions. So you can provide as many as you want, provided that the order in the passwords and description lists match the order in the user list. Of course if you have many users, you should consider implementing an LDAP Directory with WebLogic.
For each list of users the script loops over the list and fetches based on an incremented idx-variable the accompanying password and description. It creates the user and adds it as a member to the particular group.

After the administrator-users, the OSB Developers group is created, and for the Dev environment it is added to the WebLogic groups Operators and Deployers. It is done in a loop, based on a list of WebLogic groups this group should be a member of. In EM the group is added to the OSB Application Specific role 'Developer'. I haven't searched for a wls example to do that, but it is a environment specific small activity so I kept that manual.
After the OSB Developers group, the OSB Developer users are created.

Lastly in the same way the OSB Tester group and users are created. The Tester group is only added to the Weblogic Monitors group, and in EM added to the OSB Application Specific role 'Tester'.

So here's the script:
#############################################################################
# Create WebLogic Users and groups
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.1, 2016-07-06
#
#############################################################################
# Modify these values as necessary
import sys, traceback
scriptName = sys.argv[0]
#
#
lineSeperator='__________________________________________________________________________________'
#
#
def usage():
print 'Call script as: '
print 'Windows: wlst.cmd '+scriptName+' -loadProperties localhost.properties'
print 'Linux: wlst.sh '+scriptName+' -loadProperties environment.properties'
print 'Property file should contain the following properties: '
print "adminUrl=localhost:7001"
print "adminUser=weblogic"
print "adminPwd=welcome1"
#
#
def connectToadminServer(adminUrl, adminServerName):
try:
print(lineSeperator)
print('Try to connect to the AdminServer')
try:
connect(userConfigFile=usrCfgFile, userKeyFile=usrKeyFile, url=adminUrl)
except NameError, e:
print('Apparently user config properties usrCfgFile and usrKeyFile not set.')
print('Try to connect to the AdminServer adminUser and adminPwd properties')
connect(adminUser, adminPwd, adminUrl)
except WLSTException:
message='Apparently AdminServer not Started!'
print (message)
raise Exception(message)
#
#
def getRealm(name=None):
cd('/')
if name == None:
realm = cmo.getSecurityConfiguration().getDefaultRealm()
else:
realm = cmo.getSecurityConfiguration().lookupRealm(name)
return realm
#
#
def getAuthenticator(realm, name=None):
if name == None:
authenticator = realm.lookupAuthenticationProvider('DefaultAuthenticator')
else:
authenticator = realm.lookupAuthenticationProvider(name)
return authenticator
#
#
def createUser(authenticator, userName, password, description):
print ("Creating user " + userName)
if authenticator.userExists(userName):
print ("User "+userName+" already exists.")
else:
print ("User "+userName+" does not exist.")
authenticator.createUser(userName, password, description)
print("User "+userName+" created with password "+password+".")
#
#
def createGroup(authenticator, groupName, description):
print ("Creating group " + groupName)
if authenticator.groupExists(groupName):
print ("Group "+groupName+" already exists.")
else:
print ("Group "+groupName+" does not exist.")
authenticator.createGroup(groupName, description)
print("Group "+groupName+" created.")
#
#
def addMember2Group(authenticator, groupName, memberName):
print ("Adding member "+memberName+" to group " + groupName)
if authenticator.isMember(groupName,memberName,true) == 0:
print ("Member "+memberName+" not yet member of the group "+groupName+".")
authenticator.addMemberToGroup(groupName, memberName)
print ("Member "+memberName+" added to the group "+groupName+".")
else:
print ("Member "+memberName+" already member of the group "+groupName+".")
#
#
def main():
try:
print (lineSeperator)
print ('Start Osb Cluster')
print (lineSeperator)
print('\nConnect to AdminServer ')
connectToadminServer(adminUrl, adminServerName)
#
#Create Users
# Get Realm and Authenticator
realm = getRealm()
authenticator = getAuthenticator(realm)
# Administrators
print('\nCreate Administrator users')
administratorList=administrators.split(',')
administratorDescrList=administratorsDesc.split(',')
administratorPasswordList=administratorPasswords.split(',')
#
idx=0
for administrator in administratorList:
administratorDesc=administratorDescrList[idx]
administratorPassword=administratorPasswordList[idx]
print(str(idx)+': Process administrator user '+administrator+' with description '+administratorDesc)
createUser(authenticator, administrator, administratorPassword, administratorDesc)
addMember2Group(authenticator, 'administrators', administrator)
idx=idx+1
#
# OSB Developers
print('\nCreate group '+grpOsbDevName)
createGroup(authenticator, grpOsbDevName, grpOsbDevDesc)
grpOsbDevWlsGrpList=grpOsbDevWlsGrps.split(',')
idx=0
for wlsGroup in grpOsbDevWlsGrpList:
print(str(idx)+': Add '+grpOsbDevName+' to '+wlsGroup)
addMember2Group(authenticator, wlsGroup, grpOsbDevName)
idx=idx+1
#
osbDeveloperList=osbDevelopers.split(',')
osbDeveloperDescrList=osbDeveloperDescriptions.split(',')
osbDeveloperPasswordList=osbDeveloperPasswords.split(',')
#
print('Create OSB Developer users')
idx=0
for osbDeveloper in osbDeveloperList:
osbDeveloperDesc=osbDeveloperDescrList[idx]
osbDeveloperPassword=osbDeveloperPasswordList[idx]
print(str(idx)+': Process OSB Developer user '+osbDeveloper+' with description '+osbDeveloperDesc)
createUser(authenticator, osbDeveloper, osbDeveloperPassword, osbDeveloperDesc)
addMember2Group(authenticator, grpOsbDevName, osbDeveloper)
idx=idx+1
#
# OSB Testers
print('\nCreate group '+grpOsbTestName)
createGroup(authenticator, grpOsbTestName, grpOsbTestDesc)
grpOsbTestWlsGrpList=grpOsbTestWlsGrps.split(',')
idx=0
for wlsGroup in grpOsbTestWlsGrpList:
print(str(idx)+': Add '+grpOsbTestName+' to '+wlsGroup)
addMember2Group(authenticator, wlsGroup, grpOsbTestName)
idx=idx+1
#
osbTesterList=osbTesters.split(',')
osbTesterDescrList=osbTesterDescriptions.split(',')
osbTesterPasswordList=osbTesterPasswords.split(',')
#
print('Create OSB Tester users')
idx=0
for osbTester in osbTesterList:
osbTesterDesc=osbTesterDescrList[idx]
osbTesterPassword=osbTesterPasswordList[idx]
print(str(idx)+': Process OSB Tester user '+osbTester+' with description '+osbTesterDesc)
createUser(authenticator, osbTester, osbTesterPassword, osbTesterDesc)
addMember2Group(authenticator, grpOsbTestName, osbTester)
idx=idx+1
#
print('\nExiting...')
exit()
except NameError, e:
print('Apparently properties not set.')
print "Please check the property: ", sys.exc_info()[0], sys.exc_info()[1]
usage()
except:
apply(traceback.print_exception, sys.exc_info())
exit(exitcode=1)
#call main()
main()
exit()

And here an example of the property file:
#############################################################################
# Properties for Creating WLS Users
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.0, 2016-07-06
#
#############################################################################
#
# Properties for AdminServer
adminServerName=Adminserver
adminUrl=lxoosb301:7001
# AdminUser
adminUser=weblogic
adminPwd=wlsadmin_O3
#
defaultPassword=welkom123
#
grpOsbDevName=OSBDevelopers
grpOsbDevDesc=OSB Developers
grpOsbDevWlsGrps=Deployers,Operators
#
osbDevelopers=sjaak,maarten
osbDeveloperDescriptions=Sjaak with a Lastname - OSB Developer,Maarten from another father - OSB Developer
osbDeveloperPasswords=sjaak123,maarten456
#
grpOsbTestName=OSBTesters
grpOsbTestDesc=OSB Testers
grpOsbTestWlsGrps=Monitors
#
osbTesters=wim
osbTesterDescriptions=Wim You know - OSBTester
osbTesterPasswords=wimrt789
#
administrators=hans,martien
administratorsDesc=Hans the Admin,Martien van den Akker (extern)
administratorPasswords=hans123,martien456

As you can see the descriptions may have spaces and dashes and other characters in it. But certainly no comma's (','), since that breaks the list. Often the description is used to provide a full name.
Also it contains a references to the admin server and the admin username/passsword. So you need to adapt this part of the property file for each environment.
In a later past I'll show how to create user key files to use encrypted administrator passwords. So after creating the users, remove this file from your system.
To start it you can use the following shell script, or get the running wlst command from:
#!/bin/bash
#############################################################################
# Create users
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 2.1, 2016-07-06
#
#############################################################################
#
. fmw12c_env.sh
echo
echo Create Users
wlst.sh ./createUsers.py -loadProperties fmw.properties

Rename the fmw.properties reference to the file you named the property file created based on the property file example above.

That (as my other scripts) needs a fmw12c_env.sh script like:
#!/bin/bash
echo set Fusion MiddleWare 12cR2 environment
export FMW_HOME=/ontw/u01/app/oracle/product/fmw12c2
#
echo call setWLSEnv.sh
. $FMW_HOME/wlserver/server/bin/setWLSEnv.sh
export PATH=$FMW_HOME/oracle_common/common/bin:$WL_HOME/common/bin/:$WL_HOME/server/bin:$PATH


Create a SQL Profile to let the Optimizer ignore hints in #Oracle

The Oracle Instructor - Thu, 2016-07-07 03:36

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:04.92
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
--------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1767991108

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |   139K(100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES     |    17M|   131M|   139K  (1)| 00:00:06 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | SALES_BIX |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CHANNEL_ID"=3)

The index hint directs the optimizer here to use a bad plan that wouldn’t be used otherwise:

SQL> select max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.06
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  ahw4npmjpnu1k, child number 0
--------------------------------------
select max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHANNEL_ID"=3)

Now the remedy:

begin
 dbms_sqltune.import_sql_profile(
 name => 'MYPROFILE1',
 category => 'DEFAULT',
 sql_text => 'select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3',
 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')
                                 );
end;
/

PL/SQL procedure successfully completed.

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.05

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
-------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHANNEL_ID"=3)

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------
   - SQL profile MYPROFILE1 used for this statement

This works for that SQL statement only without having to modify the application. The SQL profile can be removed like this:

SQL> exec dbms_sqltune.drop_sql_profile('MYPROFILE1')
PL/SQL procedure successfully completed.

All the above is not new, but still I think it might be worthwhile to mention it here for your reference, should you encounter some nasty hints once:-)


Tagged: optimizer, Performance Tuning
Categories: DBA Blogs

Modified IO CPU+IO Elapsed Graph (sigscpuio)

Bobby Durrett's DBA Blog - Wed, 2016-07-06 18:16

Still tweaking my Python based Oracle database performance tuning graphs.

I kind of like this new version of my “sigscpuio” graph:

blogiopluscp2u

The earlier version plotted IO, CPU, and Elapsed time summed over a group of force matching signatures. It showed the components of the time spent by the SQL statements represented by those signatures. But the IO and CPU lines overlapped and you really could not tell how the elapsed time related to IO and CPU.  I thought of changing to a stacked graph where the graph layered all three on top of each other but that would not work. Elapsed time is a separate measure of the total wall clock time and could be more or less than the total IO and CPU time. So, I got the idea of tweaking the chart to show IO time on the bottom, CPU+IO time in the middle, and let the line for elapsed time go wherever it falls. It could be above the CPU+IO line if there was time spent that was neither CPU or IO. It could fall below the line if CPU+IO added up to more than the elapsed time.

So, this version of sigscpuio kind of stacks CPU and IO and just plots elapsed time wherever it falls.  Might come in handy.

Bobby

Categories: DBA Blogs

Don’t Miss Hands-On Learning with Oracle Product Experts, Oracle ACE’s, and Java Champions!

OTN TechBlog - Wed, 2016-07-06 17:43

The Oracle Technology Network team invites you to attend our latest OTN Summit series. Gain first hand access to highly technical demonstrations, presentations, and hands-on labs. View the full agenda and abstracts.

The Summit opens with Oracle Cloud Insights – A Conversation with Inderjeet Singh, Executive Vice President, Fusion Middleware Development. Then, in just over three hours, you can explore 12 different sessions among four tracks.

  • Database track: Three sessions focused on understanding the scope of your cloud implementation across basic Database operations, Business Analytics and Big Data management so you can evaluate your current database architecture in the context of Oracle Cloud services.
  • Java track: Learn best practices and platforms for improved development and deployment of Java applications, how to optimize your DevOps practices with practical and proven tactics, and create JavaScript applications leveraging Java EE as the backend.
  • Middleware track: This track will focus on Integration in the Cloud, with three sessions presenting three different angles on Oracle’s Integration Cloud Service (OICS) and learn about integration options, examine best practices and integration patterns, and watch a demo involving OICS, Peoplesoft, and Oracle's LinkedIn Adapter.
  • Systems track: For our attendees in this track will introduce the Oracle Public Cloud Machine, examine SQL in Silicon and Security in Silicon features, and a technical overview and best practices of the Oracle Exadata Cloud Service.

View the full agenda and abstracts.

Oracle doubts

Tom Kyte - Wed, 2016-07-06 14:46
c d f ---- ---- ---- 1 a 100 1 b 200 Output: 1 a 100 b 200
Categories: DBA Blogs

cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Tom Kyte - Wed, 2016-07-06 14:46
I am using PL/SQL in front end and Oracle 11gR2 in back end to developing a db app. In the middle of time, I am going to check hard parsed execution plan of one sql statment. What make me surprised is that the PL/SQL Developer seems has no capability...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator