I'm a DBA but not well aquainted with Informix.
A new database we are monitoring needs to have dbspace added and this system is being replicated to manually each night to a standby server.

We will be adding logical volumes and creating dbspaces that span more than one. We are worried about the standby server, can this be done while the primary is up and running, etc.

What steps do I need to perform to add the dbspace? What part of these steps do I need to replicate to the standby server so when the copy is done at night, it will work?

This is an Informix db on an AIX server. Thank you in advance for any help you can offer.
K. Tjarks

If the replication is doen using HDR (thus a primary and a secondary server) then it is quite easy.

1st: define/create a new logical volume on both the primary and the secondary server. (The path and device volume name must be the same)

2nd: Create the dbspace(s) on the primary server. Informix will automatically create the dbspace(s) on the secondary, so you won't have to worry about that.

Most important/fotgotten step is to define the devices on both machines before specifying the onspaces command.

Hope this helps,


Rob Prop

I personally don't think of this as replication, but the company seems to think it is...
The database is actually copied with perl scripts at night and duplicated. This is not a "true" replicated or standby server, as most DBA's know of.

Our concern is, we can add the dbspace on the production server with no problem. What do we have to prepare on the secondary server so when the files are copied over, there is a match for it waiting.

Thank you again,
K. Tjarks

Can you please specify what you copy exactly at night:

For example:
- logical logs from disk
- the dbsspaces/chunks itself
- dbexport/import
- or something else

Sorry for the confusion....

Rob Prop

If the replication 'script' is not too large you can paste in the topic so there is no uncertainty anymore....

This is the beginning 1/2 of the script. It shows that the author is sending the backup file, zipped up, along with the logs to the secondary server.
Thank you again!
K. TJarks

#####
# Transfer script to find backup files and
# transfer them to farringdon when they are
# complete.
#####

#####
# User-defined variables
#
# $backup_log can be overridded with the -f logfile flag
my $backup_log = '/opt/informix/backup/backup.log';
my $transfer_log = '/opt/informix/backup/transfer.log';
my $remote_host = 'kyle';
my $remote_dest = '/opt/informix/backup';
my $remote_user = 'informix';
my $scp = '/usr/bin/scp';
my $ssh = '/usr/bin/ssh';
# Define sleep intervals (may change based on tapesize)
my $sleep_long = 5;

# OK, find out whether we're looking for a level 0 or 1
getopts('l:f:');

if ( ! ( defined $opt_l ) ) {
print "USAGE: transfer.pl -l (level of backup)\n";
print "Ex: transfer.pl -l 0 \n";
exit 1;
}

if ( defined $opt_f ) {
if ( -f $opt_f ) {
$backup_log = $opt_f;
} else {
print "Log file $opt_f does NOT exist! EXITING!\n";
exit 1;
}
}

# Print out the options
print "=============================\n";
print "--------\n";
print "Remote host: $remote_host\n";
print "Remote dest: $remote_dest\n";
print "Remote user: $remote_user\n";
print "=============================\n";

# First off, verify that the backup.log file exists.
# If it does not, then sleep for a total of 1 hour
# (in 1-minute increments). If it doesn't exist
# after then, exit the program.

if ( ! ( -f $backup_log ) ) {
my $countdown = 60;
my $file_found = 'FALSE';
while ( $countdown > 0 ) {
print "The backup log ($backup_log) does not exist...sleeping...\n";
sleep $sleep_long;
$countdown--;
if ( -f $backup_log ) {
# OK, backup has started...let's move on
$file_found = 'TRUE';
$countdown = 0;
}
if ( $file_found eq 'FALSE' ) {
print "The backup has still not kicked off...exiting.\n";
exit 1;
}
}

my $level = $opt_l;

my $tape_count = 1000;
my $continue = 'TRUE';
my $file_counter = 1;
my $list_is_final = 'FALSE';

while ( $continue eq 'TRUE' ) {
# Check for first file in backup set.
# (First file is guaranteed)
my $filename = "backup_L" . $level . "_" . $file_counter . ".bak.Z";
my $last_filename = "backup_L" . $level . "_" . $file_counter . "_last.bak.Z";
# Check to see if we're on the last file...if so, change the name.
if ( ( -f $filename ) || ( -f $last_filename ) ) {
# Change $filename to the last filename (indicates last backup file)
if ( -f $last_filename ) {
$filename = $last_filename;
$continue = 'FALSE';
}

# Wait for the file to finish compressing
WaitForCompress($filename);

print "Transferring $filename to $remote_host\n";

# Now transfer the file to farringdon
my $retry = 'TRUE';
my $command = $scp . ' ' . $filename . ' ' . $remote_user .
'@' . $remote_host . ':' . $remote_dest . '/' . $filename . '.tmp 2>&1';
print "Command: $command\n";

# Now loop through the transfer until the md5 checksums match
while ( $retry eq 'TRUE' ) {

$results = system($command);
";

print "Checking MD5 checksum\n";
my $command = "md5sum $filename";
my $orig_md5 = `$command`;
chomp($orig_md5);
($orig_md5) = (split / /, $orig_md5)[0];
$command = $ssh . " " . $remote_user . "\@" . $remote_host . ' "md5sum ' . $remote_dest . '/' . $filename . '.tmp"';
my $copy_md5 = `$command`;
chomp($copy_md5);
($copy_md5) = (split / /, $copy_md5)[0];

if ($orig_md5 eq $copy_md5) {
print "$filename transferred successfully!\n";
$command = $ssh . " " . $remote_user . "\@" . $remote_host . ' "mv ' . $remote_dest . '/' . $filename . '.tmp '
. $remote_dest . '/' . $filename . '"';
$results = `$command`;
$retry = 'FALSE';
} else {
print "$filename was corrupted during transfer! Retry
}
}
if ( $filename !~ /last.bak.Z$/ ) {
$file_counter++;
$filename = "backup_L" . $level . "_" . $file_counter . ".bak.Z";
$last_filename = "backup_L" . $level . "_" . $file_counter . "_last.bak.Z";
}
}

if ( $list_is_final eq 'FALSE' ) {
# Now check the backup log and determine if we know how many
# files need copied (how many tapes the backup used)
my @list = `grep "^Please mount" $backup_log`;
if (scalar @list > 0 ) {
my $count = scalar @list;
my $line = $list[$count - 1];
my @line = split/ /, $line;
$tape_count = $line[3];
print "\tDetermined there are at least $tape_count files to transfer\n";
}

OK, thanks for the info.....now we know what we're talking about

Define logical volumes (same paths/name) on the secondary before restoring a backup. After that you must start with a level 0 restore on the secondary/replicated server. With all other restores you will miss some information defined in the rootdbs about available spaces and chunks.
Thus in short:
- Do everything on the primary
- Create volumes on the secondary
- Start restore with Level 0

Greetz,

Rob Prop

The assist is appreciated...
K. TJarks