How to archive Oracle database report files?

[sherrysun]

Hi,

I've got a file space, say, /eprdio, that stores the daily Oracle report files. These files can built up pretty quick, so we have to delete all those that are 14 days older, in order to free up space.

But we want to save those files for legal purpose, for 7 years, so I need to archive them.

My question is: If I do once-a-week archive, it will be stored as each individual archive,

not only it will be big for database, but also it would be impractical to try to restore one day.

Do I have other option?

Also, another question regarding archive, if I archive, it goes to tape, and then copies to offsite for storage. it ends up with two copies of files. How do I just have one copy on tape, but instead of staying in the library, it goes to offsite? My current offsite command is " copy stg tapepool offsite", can I just move to offsite instead of copy?



Thanks for your input.



Sherry



[email protected]

 

[chad_small]

Sherry,



I work on a Fortune 500 account and something I have learned (and is really the TSM motto) is never trust data that is only on a single tape. In other words if you are required to keep data for 7 yrs. but only have it on one tape and the tape goes bad, is lost, or damaged what then? As for the archive job we do the same thing with the following PERL script (Written by Hari Patel -> [email protected]). It analyzes the files and archives all the files in the directory over 14 days old. Actually you can set how many days old it analyzes for, since the value is passed from by the schedule. What we do is create a COMMAND based client schedule and put the following in the objects line:



/usr/tivoli/tsm/client/ba/bin/TSMCliArchiver.pl 14 <Description> /my/directory/of/choice/



************Begin Cut After This Line**************

shift;

shift;



while ( @ARGV gt 0 ) {

$dir = $ARGV[0];

& log_stamp;

printf(SCR_LOG "$logstamp Processing $dir\n");



if ( "$dir" eq "/" ) {

& log_stamp;

printf(SCR_LOG "$logstamp No archiving from the root\($dir\) directory\n");

shift;

next;

}



if ( ! -d "$dir" ) {

& log_stamp;

printf(SCR_LOG "$logstamp Directory $dir does not exist\n");

shift;

next;

}



open(FILELST,"/usr/bin/find $dir -mtime +$days -print < /dev/null |")

|| die "Can't run find :$!\n";



while ( <FILELST> ) {

chomp;

next if (/^$/);

next if (-d "$_");



if ( $dsmfile eq 0 ) {

$tsmfile = "/.ARCHIVE/$prefix.$dept.$dtstamp.$count.ksh";

$tsmlog = "/.ARCHIVE/$prefix.$dept.$dtstamp.$count.dsm";

open(DSMC,">$tsmfile") || die "Can't open $tsmfile :$!\n";

print (DSMC "#!/bin/ksh\n\n");

print (DSMC "exec > $tsmlog 2>&1\n\n");

print (DSMC "/usr/bin/dsmc archive ");

$dsmfile=1;

$tarflag=1;

}



if ( $filecount <= 19 ) {

print (DSMC "\\\n$_ ");

$filecount=$filecount+1;

$totalcount=$totalcount+1;

$filecrt=1;

}

else {

print (DSMC "\\\n$_ ");

print (DSMC "-deletefiles \n");

print (DSMC "\n");

print (DSMC "exit 0\n");

close DSMC;

& run_archiver;

$dsmfile=0;

$filecount=1;

$filecrt=0;

$count = sprintf("%03d", $count + 1 );

$totalcount=$totalcount+1;

& log_stamp;

printf(SCR_LOG "$logstamp Processed $totalcount files\n");

}

}



if ( $filecrt eq 1 ) {

print (DSMC "-deletefiles \n");

print (DSMC "\n");

print (DSMC "exit 0\n");

close DSMC;

& run_archiver;

$dsmfile=0;

$filecount=1;

$filecrt=0;

& log_stamp;

printf(SCR_LOG "$logstamp Processed $totalcount files\n");

}

shift;

}



if ( $tarflag eq 1 ) {

& tar_files;

}



& log_stamp;

printf(SCR_LOG "$logstamp Ended log for script. \n");

printf(SCR_LOG " $0 . . . . . \n");



close SCR_LOG;

exit 0;





############################################################################

# Script Ended . . . . . . . . . #

# Sub-Routine Begin Here . . . . . . . . . #

############################################################################



sub usage {

if ( @ARGV le 2 ) {

print "Usage: $0 number_of day department directories_separted_by_name\n\n\n";

exit 0;

}

else {

open(BASENAME,"basename $0 < /dev/null |") || die "Can't run basename :$?\n";

$BASENAME=<BASENAME>;

chop($BASENAME);

@basename = split(/\./,$BASENAME);

$prefix = $basename[0];

$days = $ARGV[0];

$dept = $ARGV[1];

& date_stamp;

$scriptlog = "/.ARCHIVE/$prefix.$dept.$dtstamp.script.log";



if(-e $scriptlog) {

open SCR_LOG,">>$scriptlog";

& log_stamp;

printf(SCR_LOG "$logstamp Started log for script. \n");

printf(SCR_LOG " $0 . . . . . .\n");

}

else {

open SCR_LOG,">$scriptlog";

& log_stamp;

printf(SCR_LOG "$logstamp Started log for script. \n");

printf(SCR_LOG " $0 . . . . . .\n");

}



}

}



sub def_variable {

& log_stamp;

printf(SCR_LOG "$logstamp Defining pre-variable.\n");

$totalcount=0;

$dsmfile=0;

$tarflag=0;

$filecount=1;

$filecrt=0;

$count = sprintf("%03d", 1 );

}



sub create_log_dir {

if ( -d "/.ARCHIVE" ) {

printf(SCR_LOG "$logstamp Directory /.ARCHIVE is exist\n");

}

else {

$ret=system("/usr/bin/mkdir /.ARCHIVE");

if ( $ret ne 0 ) {

& log_stamp;

printf(SCR_LOG "$logstamp Unable to create \/.ARCHIVE\n");

}

else {

& log_stamp;

printf(SCR_LOG "$logstamp Successfully created directory \/.ARCHIVE\n");

}

}

}



sub run_archiver {

& log_stamp;

printf(SCR_LOG "$logstamp Processing TSM Archive\n");

if ( -e $tsmfile ) {

$ret = system("/usr/bin/chmod 500 $tsmfile");

if ( $ret ne 0 ) {

& log_stamp;

printf(SCR_LOG "$logstamp Unable to change mode for $tsmfile\n");

}

else {

$ret = system("$tsmfile");

if ( $ret ne 0 ) {

& log_stamp;

printf(SCR_LOG "$logstamp Archive failed.\n");

}

}

}

else {

& log_stamp;

printf(SCR_LOG "$logstamp File $tsmfile not exist\n");

}

}



sub tar_files {

$ret = system("/usr/bin/tar -cvf /.ARCHIVE/$prefix.$dept.$dtstamp.tar /.ARCHIVE/$prefix.$dept.$dtstamp.*.ksh /.ARCHIVE/$prefix.$dept.$dtstamp.*.dsm");

if ( $ret eq 0 ) {

& log_stamp;

printf(SCR_LOG "$logstamp Successfully tar log files\n");

& compress_tar_files;

}

else {

& log_stamp;

printf(SCR_LOG "$logstamp Failed to tar log files\n");

}

}



sub compress_tar_files {

$ret = system("/usr/bin/compress /.ARCHIVE/$prefix.$dept.$dtstamp.tar");

if ( $ret eq 0 ) {

& log_stamp;

printf(SCR_LOG "$logstamp Successfully compress tar file\n");

& rm_log_files;

}

else {

& log_stamp;

printf(SCR_LOG "$logstamp Failed to compress tar file\n");

}

}



sub rm_log_files {

$ret = system("/usr/bin/rm /.ARCHIVE/$prefix.$dept.$dtstamp.*.dsm /.ARCHIVE/$prefix.$dept.$dtstamp.*.ksh");

if ( $ret eq 0 ) {

& log_stamp;

printf(SCR_LOG "$logstamp Successfully remove log files\n");

}

else {

& log_stamp;

printf(SCR_LOG "$logstamp Failed to compress tar file\n");

}

}



sub date_stamp {

$now = time();

@dt = localtime($now);

$dt[5] += 1900;

$dt[4]++;

$dtstamp = sprintf("%04d%02d%02d.%02d%02d%02d",

$dt[5], $dt[4], $dt[3], $dt[2], $dt[1], $dt[0]);

}



sub log_stamp {

$lognow = time();

@logdt = localtime($lognow);

$logdt[5] += 1900;

$logdt[4]++;

$logstamp = sprintf("%04d/%02d/%02d %02d:%02d:%02d",

$logdt[5], $logdt[4], $logdt[3], $logdt[2], $logdt[1], $logdt[0]);

}

 

[sherrysun]

Thank you Chad, for your detailed answer.



I will remember the motto!



Currently, here is how I am doing to solve the archive issue, please tell me if this would work:



I created a management class, say, io_archive, and in there, I created a backup policy that has these attributes:

Versions Data Exists NOLIMIT

Versions Data Deleted 1

Retain Extra Versions 30

Retain Only Version 2555

Copy Mode MODIFIED



Since in this directory, most of files have only one version with time stamps in the filename. so by setting the Retain Only to 2555 would keep the files for 7 years. and I do incremental backup on the directory everyday. Once I backed up, the client box's cron job will delete all files older than 14 days.



Will this work?



Thanks again for your advice.



Sherry Sun