Our PeopleSoft system has a couple of maintenance tasks which are kicked off from the database server. I am converting it to use Ansible and a management server, but in the meantime I need this to work.

We had been using Bitvise SSH server on Windows, but experienced problems with it locking up occasionally. Also we needed to create some new Windows VMs and wondered if there was a way to do the work without paying for more licenses. Also as we are upgrading to Windows server 2016, I am seeing if there is a way to automate this as part of my Ansible build.

Security Updates Not Included!

Oracle’s Deployment Package DPK is supposed to deliver all the software required and at the correct versions. However, it appears this isn’t true. PeopleSoft itself is at the current version in the DPK, but all the software it depends on only has the previous quarters security updates applied. This often leaves us vulnerable to some quite serious security flaws.

The solution is to apply the patches after applying the DPK. It is annoying that we have to do this, because the sales documentation at the time suggested everything would be at the correct version with the latest patches.

Here are some notes as to how to create test data from real data with SQL.

I created a package to do this. It generates SQL statements to mask columns which have personal data, so we don’t end up testing with real peoples data. Here are some things I made it do.

The procedure loops through a list of tables which is in a local table. In this case we are selecting across a database link. I use a SQL statement to discover the format of the remote table using dbms_sql.parse.

The Problem

Our release process means that we have to refresh test environments on a particular schedule around the date of the release. These dates were put into a confluence page, but we discovered that the problem with this is that if a release is moved we have to change all the refresh dates. Also sometimes projects mean particular environments aren’t refreshed or the schedule is altered slightly.

So it ends up that we have to:

Last time I showed how I set up a project in TaskJuggler. Now it is time to add in the tasks.

Thinking about what I want to achieve, the project goes something like this:

The Project

Update the operating system on the database server

1. Update the development database server during the patching window.
2. Run a test to make sure it works. This includes

• People using it during the day.
• An automated test running overnight.

1. Assuming it all works, update the test database server during the patching window.
2. Run the same tests as above
3. Update the UAT database server during the patching window
4. Run the tests
5. Update production during the patching window.

Patch the databases

This looks very similar to above

One thing I have found terribly dull is project management. This is something we all have to do at times. My example is the quarterly patching from Oracle. We also take the opportunity to do operating system updates at the same time.

Thinking about how to do the work, we have a patch window where systems are allowed to be shut down (Given the appropriate change control of course) for maintenance to be done. These are twice a week. We have a numer of systems, dev, test, UAT and production like everyone else, and the patches are moved through so any issues are discovered before we hit production.

We recently got access to Gitlab. It is rather more user friendly than our previous Gitolite install, and has lots more features.

One thing I thought might be useful is to connect SQL developer to it and use the IDE features.

The first thing I discovered is that running SQL Developer off a Windows share is a painful business. Also it is likely to be out of date. Installing it locally seems like a good move.

Imagine a situation where a production database is copied to test, and the data scrambled. Our testers want to access the database because they need to find some data to use for testing.

• The testing is automated.
• The refresh is automated
• The scrambling is automated.

How do we get the testers the password in a secure fashion? Also, how do we ensure testers don’t just use the test user normally? They should log in with their own user name and passwords. Also, the test user shouldn’t have the same password all the time, it should change periodically. This will ensure that if a tester has found out the password because they need it to develop the automated tests, they won’t know it after they leave, or be tempted to use it when they ought to be using their own user name.