PeopleSoft – Move File Attachments (Database to SFTP)

This is the scenario, client needs to move from database stored file attachments to an actual file share, this allows them to remove 1/4 of their entire database storage needs out to a storage area.  Clearly this is a good thing. So, here is the quick run down on what we did:

1. Copy all the existing URL references that contained record:// for backup purposes.

2. Setup a SSH account with a SSH key pair for security

3. Install the SSH key pair into the digital certificates

4. Change all the existing URLs that point to the database storage to the new SFTP storage locations.

5. Run the Orphan cleanup process for File Attachments

6. Copy the file attachments from the OLDURL to the NEWURL

7. Setup the default file attachment server to use the new URL attachment server.

8. Generate a list of all the attachments moved into the file storage and compare that against what was actually in the attachment record

9. Purged the attachment record of all the migrated files.

10.  Updated attachment reference records when necessary if they referenced the old URL in any way.

This is where we ran into an interesting problem with unicode characters.  The process was relatively slow, and we found that to move approximately 200,000 files took about 40 hours.  So to get into production what we did was made a copy of production and run the process in a test environment.  Then we moved all the files that we copied to the production file share and than removed all the entries from the database attachment record that were already moved and then ran the tiny subset of new attachments out to the storage so we could minimize our downtime.


X11 Forwarding – SSH using XMING

After many years of working with PeopleSoft I try not to get to worried about the little things, because so much changes every day that you could lose your mind trying to keep track of it all.

I was doing an install of the Oracle Database software on a new RHEL 5.8 server the other day and I could not get my X11 forwarding working.  Now I don’t claim to be an expert in SSH and never will be, but I use the Bitvise Tunnelier and then use XMing for the SSH/X11 client software.  On this server I had setup the SSH Daemon to run X11 forward on using the default off-set 10.  This basically means that the communication for X11 will be on port 6010 instead of 6000. This means that your display variable will need to be set differently.  This is the export command I used:

export DISPLAY=localhost:10.0

In a lot of cases it will just show you 0.0, but because of the offset you need to change to 10.0 when using the off-set of 10. When setting up my SSH connection in Tunnelier, under the terminal tab, you need to enable X11 forwarding, and you will need to change your Display variable appropriately, in my case I was using the offset: 10, so I entered:

Lastly, I found that the XMing client defaults to NO offset, so when I launched XMING launch, on the first page it asks what the offset should be be.  Once I changed that to 10, everything started working great.

It should also be noted that if you are using a firewall (in my case iptables) you will want to make sure port 6010 is open correctly if using the ssh-offset 10.

The common error I got with this configuration being wrong as: Windows TCP error code 10061.