Oracle Tales

Recently I was tasked to build out an OEM 13c instance to manage a few Oracle Database servers and their associated instances. As a part of the setup, I needed to generate a daily email report to confirm the backup status of all databases in the OEM inventory. Unfortunately, it seems Oracle has overlooked this in the default reports, so I had to create a custom report. Here's how this was accomplished. The EM Repository database keeps track of the last database backup timestamp for each database. This is shown on the home page for any database. We can use this information to create a daily report and confirm that each database has been backed up in the last 24 hours. Login the BI publisher site https://oemserver.yourdomain:9851/xmlpserver with your account or sysman. Near the top right corner click “catalog”. In the tree on the left create a new folder under “Shared Folders” called custom reports. Within the custom reports folder create a new folder called “custom d...

Just a simple post today. If you ever get the error " User does not have permission to perform Compare from File " when applying a PeopleSoft Update Manager (PUM) update, the user account you are using to apply the update in change assistant needs some new permissions added to it. It's almost guaranteed anyone doing their first PUM update will run into this. The PeopleSoft Enterprise Support blog has a great post covering this. https://community.oracle.com/community/support/support-blogs/peoplesoft-enterprise-support-blog/blog/2015/02/12/perplexing-problem-plaguing-pum-permissions

       Last year one of our DBA's came to us and said "We need to delete some databases, we're running out of space on the Exadata", to which we replied is there another option? It turns out there was. By default the Oracle ASM storage management software on Exadata uses HIGH redundancy which means triple mirroring or up to 3 disk failures. In development we don't necessarily need this level of protection so we investigated switching to NORMAL redundancy which means double mirroring or up to 2 disk failures and gives us some additional capacity. The catch..... you cannot directly convert. You need to create a new ASM storage group, add disks to it and then migrate the databases from the old to new storage group. Additionally, we had no available disks. However... there was a way to make this all happen. ASM allows you to live remove disks from existing disk groups as long as there is enough space on the other disks to handle the capacity, so we... Added s...

At my office, we refresh our PeopleSoft development databases quite regularly. The developers came to our team with a request to make sure we retain all PeopleSoft Test Framework objects in between refreshes. Additionally, it needs to be fully automated (no manual export/import). I did some digging and discovered that Application Designer command line allows you to create a project and insert specified definition types into the project. A read of the PeopleTools 8.54: Application Designer Developer's Guide  showed that the PTF objects numbers are: 104=Test Definitions 105=Test Framework Test Cases Armed with this info, I have put together simple Windows batch file that will create a project with all PTF tests and then export it to file. After the refresh job is complete, you can use the Application Designer cli to import the project back into the refreshed database. Script is in the "gist" below.

       Here is simple fun script I came up with some time ago when I needed to confirm that a user account was locked in all of our non-production databases. This one requires a Windows workstation with the Oracle client installed and PowerShell v3 or newer. It will go through the array of databases and return the SQL result for each database. This can be handy for comparing settings across multiple databases. Output looks something like this:

If you are unfamiliar with  Rundeck , get ready to have your life as a System Admin made a bit easier. Rundeck allows you to take your scripts and put a web interface in front of them so you can easily set parameters and then run then with just a few mouse clicks. The only real limitation is what you can come up with in your preferred scripting language (bash, power shell, python, emcli, sqlplus... etc). Given the lack of admin tools delivered by Oracle for managing PeopleSoft, I find this software to be a must for any PeopleSoft Administrator. Installing the software is a breeze which can be done with either a package manager or simply by running the jar file with java. ( instructions on rundeck website ) Once you've done this just point your browser at http://localhost:4440 and sign in with admin/admin. Let's go through the setup. In this case I am going to install it on an Oracle Linux 7 VM with Campus Solutions PUM image#2 already installed. 1. Install RunDeck ...

This is a 3rd in a series of 3 posts on deploying PeopleSoft Domains from a script. In this post we will be creating a PeopleSoft PIA(Weblogic) Domain. This one is probably the most complex of the 3 domain types to automate. A few prerequisites: Requires Linux with Bash (although it may work with Bash on Windows.. not sure) PS_HOME, PS_CFG_HOME, PS_APP_HOME, and PS_CUST_HOME must be set It includes some text file manipulation to configure your ibgateway settings and imports any certificates need by your weblogic keystore which you may prefer to comment out. Finally, if you're not familiar Rundeck makes running these jobs and any other bash script a breeze! Perhaps I'll talk more about that another time.

In my last post I talked about how creating PeopleSoft domains by hand was rather inefficient and prone to misconfiguration and provided a script that will create a process scheduler domain. Here is the equivalent script for creating an appserver domain. Obviously this one is a bit more complex. A few prerequisites: Requires Linux with Bash (although it may work with Bash on Windows.. not sure) PS_HOME, PS_CFG_HOME, PS_APP_HOME, and PS_CUST_HOME must be set You'll need to create a custom cfx template in $PS_HOME/appserv/small_custom.cfx. Just copy the small.cfx file in this folder and then set any custom properties you want. i.e SMTPSERVER=mail.domain.com Your Linux installation should have "lsof" installed. I will post a script for the PIA domain (Weblogic) in my next post.

After going through a PeopleSoft upgrade some time ago, I found that creating PeopleSoft domains by hand was rather inefficient and prone to misconfiguration. So I put together a bash script (since we run on Linux) that will let you supply the domain creation variables and perform a few checks to ensure the domain is created as expected. Script posted as a " GIST " on GitHub and can be download from the embedded link below. A few prerequisites: Requires Linux with Bash (although it may work with Bash on Windows.. not sure) PS_HOME, PS_CFG_HOME, PS_APP_HOME, and PS_CUST_HOME must be set You'll need to create a custom cfx template in $PS_HOME/appserv/prcs/unix_custom.cfx. Just copy the unix.cfx file in this folder and then set any custom properties you want. i.e SMTPSERVER=mail.domain.com. I will post a script for the appserver in my next post.

PeopleSoft Change assistant 8.55+ gives us the ability to upload customizations in to the PUM appliance. This allows us to generate reports to let us know which customizations will be affected by any bug fixes. One issue I have come across is that Oracle expects you to load all of your customizations into a single project (for example UPGCUST), export to file and then upload. What if you want to collect all of your customizations each within their own project and then upload them individually ? Well, here's my trick... To upload the customizations first we need to export them from an existing copy of production . Get a list of all of the project names for your customizations. Typically your customizations project names should all share a common prefix, This SQL script will generate a list of all customizations and update the project flags if they are set incorrectly. --dump list of project names that use your customizations prefix select projectname from psprojectdefn whe...

Wouldn’t it be nice to not have to manage user passwords in your Oracle Databases? Setting up Active Directory aka Kerberos Authentication is a way to avoid having to manage passwords and allows users to authenticate against your Active Directory. Below is a list of useful links and My Oracle references that were used when setting up this example: EDIT - This post gets a lot of hits so I will include some updated information. If you're on 12c you need to add kerberos5pre to your list of services. e.g. SQLNET.AUTHENTICATION_SERVICES= (beq,kerberos5pre,kerberos5) If you are an Oracle Support Customer check out How To Configure Kerberos Authentication In A 12c Database ( Doc ID 1996329.1 ). If not, here is a good 3rd party write up  https://wiki.loopback.org/display/KB/Oracle+Database+12c+Kerberos+Configuration   Master Note For Kerberos Authentication (Doc ID 1375853.1) Configuring ASO Kerberos Authentication with a Microsoft Windows 2008 R2 Active Directory KDC (Doc ...