• You keep using $_, but the file name is in $file.

• readdir returns file names, not paths. You'll need to prepend the file names you pass to -e and rename with /cdw/home_dir/s006258/MarketingDB/.

• Why do you hide your actions after your error messages? Instead of
  die(...) unless (action)
  use
  action or die(...)

• You never write to your log.

• foreach my $file (readdir(DIR))
  loads the entire list into memory, whereas
  while (defined(my $file = readdir(DIR)))
  reads one at a time.

• You're very random in your use of parenthesises. Be consistent.

Cleaned up code:

#!/usr/bin/perl

use strict;
use warnings;

use File::Spec ();

my $dir = '/cdw/home_dir/s006258/MarketingDB';
my $log = 'List_of_files_changed.txt';

opendir(local *DIR, $dir)
    or die("Cannot open $dir.\n");
    
open(local *FILES, '>', $log)
    or die("Cannot open output file $log.\n");

while (defined(my $file = $filereaddir(DIR))) {
    next unless $file =~ /^append/;
    my $old = my $new = $file;

    $new =~ s/^append/aPPend/;
    
    $old = File::Spec->catfile($dir, $old);
    $new = File::Spec->catfile($dir, $new);

    die("Unable to rename $old: $new already exists.\n")
        if -e $new;

    rename($old, $new)
       or die("Unable to rename $old: $!\n");

    print FILES ("$old renamed to $new\n");
}
[download]

Updated.

Update: 3rd point was backwards.