ORACLE 백업 펄스크립트

==> 본인의 글 입니다. 이 글을 다른곳에 게재하는 경우 본문의 출처를 밝혀주시기 바람니다.

거래처 솔라리스인 관계로 리눅스에서 작업했던 bash쉘 기능이 전부 먹히지 않아
그냥 Perl 스크립트로 만들어 보았습니다..

file: ora_backup.pl

#!/usr/bin/perl

use DBI;
use Time::Local;
use Error qw(:try);
use Archive::Zip qw(:ERROR_CODES :CONSTANTS);

BEGIN
{
	$ENV{ORACLE_OWNER}		= "oracle9";
	$ENV{ORACLE_BASE}		= "/home/$ENV{ORACLE_OWNER}";
	$ENV{ORACLE_HOME}		= "$ENV{ORACLE_BASE}/product/9i";
	$ENV{TNS_ADMIN}			= "$ENV{ORACLE_HOME}/network/admin";
	$ENV{ORA_NLS33}			= "$ENV{ORACLE_HOME}/ocommon/nls/admin/data";
	$ENV{ORACLE_SID}		= "NEXTBSC";
	$ENV{ORACLE_TNSNAME}	= "NEXTBSC";
	$ENV{NLS_LANG}			= "KOREAN_KOREA.KO16MSWIN949";
	$ENV{NLS_NCHAR}			= "KOREAN_KOREA.UTF8";
	$ENV{LD_LIBRARY_PATH}	= "$ENV{ORACLE_HOME}/lib:$ENV{ORACLE_HOME}/lib32:$ENV{ORACLE_HOME}/rdbms/demo:$ENV{ORACLE_HOME}/oracm/lib:/lib:/usr/lib:/usr/local/lib";
	$ENV{LD_RUN_PATH}		= "$ENV{ORACLE_HOME}/lib";
	$ENV{NLS_DATE_FORMAT}	= "YYYYMMDD";
	$ENV{TEMPDIR}			= "/tmp";
	$ENV{THREADS_FLAG}		= "native";
}

=comment
foreach my $key(keys %ENV)
{
	print $key."=". $ENV{$key}."\n";
}
=cut

##-------------------------------------------------------------------------------------------------------
## 삭제할 파일 일자 정의(3일전)
##-------------------------------------------------------------------------------------------------------
($year, $month, $day) = (localtime(time - (60*60*24*3)))[5,4,3];
my $rmdate			  = sprintf("%04d%02d%02d", $year+1900, $month+1, $day);


##-------------------------------------------------------------------------------------------------------
##백업할 파일 일자 정의(당일)
##-------------------------------------------------------------------------------------------------------
($year, $month, $day) = (localtime(time))[5,4,3];
my $date			  = sprintf("%04d%02d%02d", $year+1900, $month+1, $day);
#my $date			  = sprintf("%02d", $day);


##-------------------------------------------------------------------------------------------------------
## 기본 변수 선언. 
##-------------------------------------------------------------------------------------------------------
my $user		= "qes_bsc";
my $bakdir		= "/data1/oracle9i_dmp_files/";
my $fullpath	= $bakdir.$date		."_". $user ."_full.zip";
my $onepath		= $bakdir.$date		."_". $user ."_one.zip";
my $rmfullpath	= $bakdir.$rmdate	."_". $user ."_full.zip";
my $rmonepath	= $bakdir.$rmdate	."_". $user ."_one.zip";
my @nums		= (0..30);


#-------------------------------------------------------------------------------------------------------
# FULL 백업 ....
#-------------------------------------------------------------------------------------------------------
print "\nFULL 백업중.....\n";
my	$command  = "$ENV{ORACLE_HOME}/bin/exp $user/$user\@$ENV{ORACLE_TNSNAME} grants=y ";
	$command .= " file=(";
	for(@nums)
	{
		$command .= $bakdir . $user ."_full_". sprintf("%02s", $_) .".dmp,";
	}
	$command  = substr($command, 0, length($command)-1);
	$command .= " )";
	$command .= " filesize=2G";
	$command .= " log=". $bakdir . $user .".log";

system $command;


##-------------------------------------------------------------------------------------------------------
## 테이블 단위 백업....
##-------------------------------------------------------------------------------------------------------
try
{
	print "\n테이블 단위 백업중.....\n";
	my $conn	= DBI->connect("DBI:Oracle:NEXTBSC", $user, $user, {AutoCommit => 0, RaiseError => 1, PrintError => 0 }) || die "Error :$DBI::errstr";
	my $SQL		= "SELECT tname FROM tab WHERE tname NOT LIKE 'BIN%' ";
	my $stmt	= $conn->prepare($SQL);
	$stmt->execute();

	while(@row = $stmt->fetchrow_array)
	{
		my $command = "";

		if(@row["TNAME"] == "NG01_REPORT_INFO")
		{
			$command  = "$ENV{ORACLE_HOME}/bin/exp qes_bsc/qes_bsc\@$ENV{ORACLE_TNSNAME} grants=y ";
			$command .= " tables=". @row["TNAME"];
			$command .= " file=(";
			for(@nums)
			{
				$command .= $bakdir . @row["TNAME"] ."_one_". sprintf("%02s", $_) .".dmp,";
			}
			$command  = substr($command, 0, length($command)-1);
			$command .= " )";
			$command .= " filesize=2G";
		}
		else
		{
			$command  = "$ENV{ORACLE_HOME}/bin/exp qes_bsc/qes_bsc\@$ENV{ORACLE_TNSNAME} grants=y ";
			$command .= " tables=". @row["TNAME"];
			$command .= " file=". $bakdir . @row["TNAME"] ."_one_". ".dmp";
		}
		system $command;
	}

	$stmt->finish;
	$conn->disconnect;
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## FULL 백업 압축....
##-------------------------------------------------------------------------------------------------------
try
{
	opendir(DIR, $bakdir);
	@files = grep(/_full_\d{2}/, readdir(DIR));
	print $bakdir.@files;
	closedir(DIR);

	if(@files > 0)
	{
		my $zip		= Archive::Zip->new();
		my $comp	= $zip->addDirectory($date);
		$comp->desiredCompressionMethod(COMPRESSION_DEFLATED);
		$comp->desiredCompressionLevel(COMPRESSION_LEVEL_BEST_COMPRESSION);
		print "FULL 백업을 압축중입니다.....!!!\n";

		foreach my $file(sort @files)
		{
			print $file. "\n";
			$comp	= $zip->addFile($bakdir . $file, $date ."/". $file) || die 'write error';
			$check	= true;
		}
		$zip->writeToFileNamed($fullpath) == AZ_OK;
		system "split -b 680m ". $fullpath ." ". $fullpath.".";
	}
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## 테이블 단위 압축....
##-------------------------------------------------------------------------------------------------------
try
{
	opendir(DIR, $bakdir);
	@files = grep(/_one_(\d{2})?/, readdir(DIR));
	closedir(DIR);
	print $bakdir.@files;

	if(@files > 0)
	{
		my $zip		= Archive::Zip->new();
		my $zip		= Archive::Zip->new();
		my $comp	= $zip->addDirectory($date);
		$comp->desiredCompressionMethod(COMPRESSION_DEFLATED);
		$comp->desiredCompressionLevel(COMPRESSION_LEVEL_BEST_COMPRESSION);
		print "\n테이블 단위 백업을 압축중입니다.....!!!\n";

		foreach my $file(sort @files)
		{
			print $file. "\n";
			$comp	= $zip->addFile($bakdir . $file, $date ."/". $file) || die 'write error';
		}
		$zip->writeToFileNamed($onepath) == AZ_OK;
		system "split -b 680m ". $onepath ." ". $onepath.".";
	}
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## 삭제할 파일
##-------------------------------------------------------------------------------------------------------
system "rm -f ". $rmfullpath;
system "rm -f ". $rmonepath;
system "rm -f ". $bakdir ."*.dmp";


__END__
##-------------------------------------------------------------------------------------------------------
## 실행전에 필용한 팩키지 목록들....
##-------------------------------------------------------------------------------------------------------
##perl -e "use Archive::Zip"
##perl -MCPAN -e "install DBD::Oracle"
##perl -MCPAN -e "install Archive::Zip"
##perl -MCPAN -e shell
##cpan>i /archive::zip/
##cpan>install Archive::Zip
##cpan>quit
##windows --> copy /b XXXX_full_zip.aa + XXXX_full_zip.ab  XXXX_full.zip
##solaris --> cat XXXX_full_zip.?? >  XXXX_full.zip
##linux   --> XXXX_full_zip.* >  XXXX_full.zip

##__author__  = "park17@gmail.com"
##__version__ = "0.1"
##__date__    = "2007-09-29"
##__file__    = "ora_backup.pl"

2G가 이상되면 unzip 명령어가 먹지 않네요..
64비트 옵션을 주고 컴파일 하면 된다고 하는데 안되더라구요..
그래서 귀찮아서 그냥 이것도 Perl 스크립트로 만들었습니다.

file: unzip.pl

#!/usr/bin/perl

use strict;
use Archive::Zip qw(:ERROR_CODES);

my $zip = Archive::Zip->new();
my $zipName = shift(@ARGV);

if($zip->read($zipName) != AZ_OK)
{
    die "Read of $zipName failed\n";
}
else
{
    foreach my $member($zip->members())
    {
        print $member->fileName(), ", ", $member->uncompressedSize(), ":", $member->compressedSize(), "\n";
        $zip->extractMember($member->fileName());
    }
}