As part of my post go-live procedures I was trying to do some comparisons from the pre-upgrade environment to the actual production environment.  Usually this is very straight forward and can be done using the Linked Server.  As it turned out the linked server was already configured however when I tried to do the sql lookup on the PSOPRDEFN table I got an error.

Msg 7411, Level 16, State 1, Line 1

Server ‘mvDBSERVER? is not configured for RPC

To re-enable the RCP commands for the linked server you can do it in the options GUI or from the query window:

exec sp_serveroption @server=’myDBSERVER?, @optname=’rpc’, @optvalue=’true’

exec sp_serveroption @server=’myDBSERVER?, @optname=’rpc out’, @optvalue=’true’

Now when I run [myDBSERVER].[PREUPGDB].[dbo].[PSOPRDEFN] I get the results I am looking for!

Okay, this is a little embarrassing but it points out an important concept.  Sometimes reviewing the basics can save you a lot of time and frustration.

During my last go-live weekend we cut over to a whole new infrastructure for the new HCM 9.2 environment.  This included an external web server for the e-recruiting module that is in use.  The new web server was configured and up and running and the SSL certificates were in place and for testing purposes I put an entry in the hosts file to verify everything worked.  On go-live we had to change the external DNS and NATs for the new servers so that it could work in the DMZ.  According to the network guys everything was done and the only thing they could tell me was that my Weblogic Web Server was not running or not accepting requests.

Now clearly, I was on the actual server and had tested everything so, I know that the Weblogic was working and it was communicating to the application servers.  I could RDC to the server and even map UNC paths to the other servers to get files to and from the old servers.  Clearly communications were working.  It just simply wouldn’t allow http and https traffic.

Turns out that this server was created from a completely different VM template than all my other servers.  When I looked closely I found that the Windows Firewall was turned on and was blocking the web traffic.   Unfortunately this was after about 5 hours of having the network guys looking into the problem.  Now they had to do some work but the problem clearly could have been resolved VERY quickly if I had just thought to look at the settings on the server.  Oh Windows Firewall how you frustrate me.

I have a little test site setup for Oracle PeopleSoft and I ran into an issue the other day where my sysadm account was expired. I typically have never ran across Oracle Database expiring my administrative user accounts. A little google search later and I found Amit Rath’s blog on fixing this problem.  It turned out that I had a default profile that had a time limit of 180 days for the password.

SQL> select profile from dba_users;

All of my system administrative users had the profile:  DEFAULT

SQL> select * from dba_profiles where profile=’DEFAULT’ and RESOURCE_NAME=’PASSWORD_LIFE_TIME’;

Here is a nice clean way to clone an Oracle database.

STEP 1:

Generate a control file trace which will allow you to create a new database. On the current database server, go into SQL*Plus as sysdba: enter the command:

alter database backup controlfile to trace as ‘/filepath/filename.sql’;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDDB” RESETLOGS NOARCHIVELOG
MAXLOGFILES 8
MAXLOGMEMBERS 4
MAXDATAFILES 1021
MAXINSTANCES 1
MAXLOGHISTORY 584
LOGFILE
GROUP 1 ‘/oracle/db/olddb/redo/redo01.log’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/oracle/db/olddb/redo/redo02.log’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/oracle/db/olddb/redo/redo03.log’ SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/oracle/db/olddb/data/system01.dbf’,
‘/oracle/db/olddb/data/sysaux01.dbf’,
‘/oracle/db/olddb/data/psundots01.dbf’,
‘/oracle/db/olddb/data/psdefault.dbf’,
‘/oracle/db/olddb/data/aaapp.dbf’,
…..All the PeopleSoft Datafiles will be listed here…….
‘/oracle/db/olddb/data/aalarge.dbf’,
‘/oracle/db/olddb/data/psimage2.dbf’
CHARACTER SET WE8ISO8859P15
;

STEP 2:

Shutdown the OLDDB database

STEP 3:

Copy all of the associated files with the database into a new directory or to a new server. Make sure you end up with the same structure as before just with the NEWDB name. So in my case I did: cp -rf /oracle/db/olddb /oracle/db/newdb, make sure you create the bdump, udump and cdump directories

STEP 4:

Using the script from Step 1, change the controlfile creation script:

Original: CREATE CONTROLFILE REUSE DATABASE “OLDDB” NORESETLOGS
Modified: CREATE CONTROLFILE SET DATABASE “NEWDB” RESETLOGS

STEP 4a:

Identify the TEMPFILE’s reuse statements in the script and make sure to keep them as part of the script (Should be at the end of the trace file)

STEP 5:

Remove any other instructions from the trace file and save it.

STEP 6:

Change the olddb references in the datafile and controlfile sections to the NEWDB location.

Original: DATAFILE ‘/oracle/db/olddb/data/system01.dbf’,
Modified: DATAFILE ‘/oracle/db/newdb/data/system01.dbf’,

STEP 6a:

Change the paths for the TEMPFILE to the new database location:

ALTER tablespace {TableSpaceName} add TEMPFILE ‘/oracle/db/{newdb}/data/psgtt01.dbf’ reuse;

STEP 7:

Save as newdb_create.sql.

STEP 8:

Copy the old initOLDDB.ora file to initNEWDB.ora
Modify the initNEWDB.ora and make the changes from the OLDDB to the NEWDB.

STEP 9:

Add entries in the tnsnames.ora, listener.ora and oratab for the NEWDB.

STEP 10:

Change to the NEWDB and STARTUP NOMOUNT and run the @newdb_create.sql

STEP 11:

ALTER DATABASE OPEN RESETLOGS;

STEP 12:

For a PeopleSoft Environment, you will want to update the PS.PSDBOWNER table to reflect the NEWDB.

Smart Thinking Newsletter

9 Tips for Selecting and Implementing an ERP System

Taking the first step towards a new ERP system is no easy task – but by looking for the light at the end of the tunnel, as well as implementing a few key tips on selecting the right system for your company. Making a list, garnering upper management support, evaluating your options are all great ways to start off your ERP project, but there are more ways to make the project run smoothly.