Oracle DBA by Example. Code samples, Scripts, Reference.: June 2016

========================
General
========================
Send files by ftp with perl.
Scheduled Task is activating bat file.
bat file is calling perl program.
perl program is setting customer speific details and calling a generic program.

ftp parameters - are read from database
files to send - all files under source directory.
In case of an success - move file to backup directory.
In case of an error - send an email.

========================
File List
========================
main_send_cdrs.bat
main_send_cdrs.pl
gen_send_cdrs.pl
sendMail.bat
mailBody.txt

========================
File Code
========================
main_send_cdrs.bat
perl main_send_cdrs.pl

main_send_cdrs.pl
#! /usr/bin/perl

use DBI;
use Time::gmtime;
use File::Copy;
use Net::FTP;
use strict;
use warnings;

#-------------------------------------------------------------------
# Global Variables - these variables need to be known in routines
#-------------------------------------------------------------------
my $dbh;

############################################################################
#####A subroutine to get the current date ##################################
############################################################################
sub getDate
{
use Time::gmtime;
my $tm=gmtime;
my $day;
my $month;
my $year;

($day,$month,$year) = (gmtime) [3,4,5];
my $locDate=sprintf("%04d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday);
return $locDate;
}

############################################################################
#####A subroutine to get the current time ##################################
############################################################################
sub getTime
{
use Time::gmtime;
my $lt=gmtime;
my $hour;
my $minute;
my $seconds;

($hour,$minute,$seconds)=(gmtime) [1,2];
my $LocTime = sprintf ("%02d%02d%02d",$lt->hour,$lt->min,$lt->sec);
return $LocTime
}
#---------------------------------------
# Open Database Connection
#---------------------------------------
sub OpenDatabaseConnection
{
my $db_user=shift;
my $db_pass=shift;
my $db_connect_str=shift;

print "----I---- Opening Database Connection ".$db_user."/".$db_pass."@".$db_connect_str."\n";
$dbh=DBI->connect("DBI:Oracle:".$db_connect_str,$db_user,$db_pass,{RaiseError =>1,AutoCommit=>0})||die "$DBI::errstr";
print "----I---- Database Connection Opened"."\n";
}
sub CloseDatabaseConnection
{
$dbh->disconnect;
}

#--------------------------------------------------------
sub GetFtpData
{
my $gate_id=shift;

my $IPAddress;
my $FTPUser;
my $FtpPass;
my $ftpDir;

my $sql;
my $sth;

$sql = qq{SELECT server_ip_address, ftp_user, ftp_password, remote_cdr_dir FROM XFER_FTP_PARAMS WHERE customer_id=?};
$sth=$dbh->prepare($sql);
$sth->execute($gate_id);
$sth->bind_columns(undef,\$IPAddress,\$FTPUser,\$FtpPass,\$ftpDir);
$sth->fetch();
$sth->finish();
return ($IPAddress,$FTPUser,$FtpPass,$ftpDir);
}
#--------------------------------------------------------
#===================================================
#sub routines End
#===================================================

#==========================================
# main()
#==========================================
my $myGate = "291";
my $IPAddress= "444.333.222.111";
my $FTPUsr="ftpuser";
my $FTPPass="ftppass";
my $RemoteDir="/Starhome/";
#-------------------------------------------------------------------------
# Local
#-------------------------------------------------------------------------
my $rootDir="D:\\Data\\CdrXfer";
my $customerRootDir="EMTEL_Inbound";
my $localFilesDir=$rootDir."\\".$customerRootDir."\\"."Files";
my $oldFilesDir=$rootDir."\\".$customerRootDir."\\"."Oldfiles";

#-------------------------------------------------------------------------
# CGW Database Connection
#-------------------------------------------------------------------------
my $db_user='db_user';
my $db_pass='db_pass';
my $connection_str='orainst';

#-------------------------------------------------------------------------
# Other
#-------------------------------------------------------------------------
my $primaryExtension="dat";
my $oldExtension="old";
my $myScript='main_send_cdrs.pl';

my $runMode;
my $retCode;
my $runDate;
my $runTime;
my $mailScript='';
my $mailBatScript='';
my $oldName;
my $logFile;
#-------------------------------------------------------------------------
# Start execution
#-------------------------------------------------------------------------
$runMode='PROD';

#-------------------------------------------------------------------------
# For TEST mode - override the PROD settings
#-------------------------------------------------------------------------
#$runMode='TEST';

if ($runMode eq 'TEST'){
$db_user='db_user_test';
$db_pass='db_pass_test';
$connection_str='orainst';
$myGate = "999";
$IPAddress= "666.555.444.333";
$FTPUsr="ftp_user_test";
$FTPPass="ftp_pass_test";
$RemoteDir="/starhome/iu/XFER_FILES/TEST/";
$customerRootDir="TEST";
$localFilesDir=$rootDir."\\".$customerRootDir."\\"."Files";
$oldFilesDir=$rootDir."\\".$customerRootDir."\\"."Oldfiles";
}

#-------------------------------------------------------------------------
# Set parameters before calling gen_send_cdrs.pl
#-------------------------------------------------------------------------
$runDate=getDate;
$runTime=getTime;
$logFile=$rootDir."\\".$customerRootDir."\\Logs\\runLog_".$runDate."_".$runTime.".txt";
$mailBatScript='sendMail.bat';
$mailScript=$rootDir."\\".$customerRootDir."\\".$mailBatScript;
print "----I---- ---------------------------------"."\n";
OpenDatabaseConnection($db_user,$db_pass,$connection_str);
($IPAddress,$FTPUsr,$FTPPass,$RemoteDir)=GetFtpData($myGate);
print "----I---- ---------------------------------"."\n";
print "----I---- FTP Details"."\n";
print "----I---- Customer Remote Server Details. Server: ".$IPAddress." User: ".$FTPUsr." Remote Dir: ".$RemoteDir."\n";
print "----I---- ---------------------------------"."\n";
CloseDatabaseConnection();

#-------------------------------------------------------------------------
# Call gen_send_cdrs.pl
#-------------------------------------------------------------------------

#my $result=system($^X,"gen_send_cdrs.pl",
my $result=system($^X,"D:\\CDR_XFER\\GENERIC_CODE\\gen_send_cdrs.pl",
#00
$myGate,
$IPAddress,
$FTPUsr,
$FTPPass,
$RemoteDir,
#05
$rootDir,
$customerRootDir,
$localFilesDir,
$oldFilesDir,
$primaryExtension,
#10
$oldExtension,
$logFile,
$runDate,
$runTime,
$mailScript,
#15
$db_user,
$db_pass,
$connection_str
#20
);
print $myScript." Finished with Status: ".$result."\n";
exit;

gen_send_cdrs.pl
#! /usr/bin/perl

use DBI;
use Net::FTP;
use Time::gmtime;
use File::Copy;
use strict;
use warnings;

#================================
# Input Parameters
#================================
#00
my $myGate=shift;
my $IPAddress=shift;
my $FTPUsr=shift;
my $FTPPass=shift;
my $RemoteDir=shift;
#05
my $rootDir=shift;
my $customerRootDir=shift;
my $localFilesDir=shift;
my $oldFilesDir=shift;
my $primaryExtension=shift;
#10
my $oldExtension=shift;
my $logFile=shift;
my $runDate=shift;
my $runTime=shift;
my $mailScript=shift;
#15
my $db_user=shift;
my $db_pass=shift;
my $connection_str=shift;
#20

#================================
# Global Parameters
#================================
my $retCode;
my $dbh;

#===================================================
#sub routines Start
#===================================================
sub FTPFile
{
my $ipAddress=shift;
my $FTPuser=shift;
my $FTPPass=shift;
my $FTPdir=shift;
my $localFilesDir=shift;
my $fileName=shift;

my $newerr=0;
my $ftp;

$ftp=Net::FTP->new($ipAddress,Passive => 1,Timeout=>240) or $newerr=1 ;
if ($newerr==1){
print MyLog "----E---- Cannot connect to server: ".$ipAddress."\n";
#$ftp->quit;
return 0;
}
print MyLog "----I---- Connected to Remote server: ".$ipAddress."\n";
$ftp->login($FTPuser,$FTPPass) or $newerr=1 ;
if ($newerr==1){
print MyLog "----E---- Cannot login with credentials: ".$FTPuser."/".$FTPPass."\n";
#$ftp->quit;
return 0;
}
print MyLog "----I---- Logged with user: ".$FTPuser."\n";

$ftp->cwd($FTPdir) or $newerr=1 ;
if ($newerr==1){
print MyLog "----E---- Cannot Change Directory to ".$FTPdir."\n";
#$ftp->quit;
return 0;
}
print MyLog "----I---- Directory changed to ".$FTPdir."\n";

$ftp->binary;

$ftp->put($localFilesDir."/".$fileName) or $newerr=1 ;
if ($newerr==1){
print MyLog "----W---- Cannot put file ".$fileName." from local Directory: ".$localFilesDir."\n";
sleep 5;
$newerr=0;
print MyLog "----I---- Going to sleep 5 seconds before second attempt";
$ftp->put($localFilesDir."/".$fileName) or $newerr=1 ;
if ($newerr==1){
print MyLog "----W---- Cannot put file ".$fileName." from local Directory: ".$localFilesDir."\n";
#$ftp->quit;
return 0;
}
}
print MyLog "----I---- Successfully sent file ".$fileName." to remote Directory: ".$FTPdir."\n";

$ftp-> quit;
return 1;
}

sub updateXFERFilesTable
{

my $p_db_user=shift;
my $p_db_pass=shift;
my $p_connection_str=shift;

my $p_sent_ind=shift;
my $p_customer_id=shift;
my $p_file_name=shift;

my $newerr;
my $ora_conn_str='dbi:Oracle:'.$p_connection_str;

#---------------------------------------
# Open Database Connection
#---------------------------------------
print MyLog "----I---- Opening Database Connection ".$ora_conn_str."\n";
$dbh=DBI->connect($ora_conn_str,$p_db_user,$p_db_pass,{RaiseError =>1,AutoCommit=>0})||die "$DBI::errstr";
print MyLog "----I---- Database Connection Opened"."\n";
print MyLog "----I---- Running SQL: UPDATE XFER_FILES SET sent_ind=".$p_sent_ind." WHERE customer_id=".$p_customer_id." AND file_name=".$p_file_name."\n";
my $sql = qq{UPDATE XFER_FILES SET sent_ind=? WHERE customer_id=? AND file_name=?};
my $sth=$dbh->prepare($sql);
$sth->execute($p_sent_ind,$p_customer_id,$p_file_name);
$dbh->commit;
$sth->finish();
$dbh->disconnect;
print MyLog "----I---- SQL Completed Successfully."."\n";
return 1;
}

sub sendMail{
my $sendMailbat=shift;
my $logFile=shift;

my @mailArg;
@mailArg=($sendMailbat,$logFile);
sleep 1;
system(@mailArg);
}
#===================================================
#sub routines End
#===================================================

#==========================================
# main()
#==========================================
print "using log file: ".$logFile."\n";
unlink $logFile;
open (MyLog,">>".$logFile);

print MyLog "### Strating CDR XFER process for date: ".$runDate." ".$runTime." ###"."\n";
print MyLog "----I---- Sending files to server: ".$IPAddress." with login user: ".$FTPUsr." Remote Folder: ".$RemoteDir."\n";

$retCode=1;
my $f;
my $oldName;
opendir(IMD,$localFilesDir)||die ("cannot open dir".$localFilesDir);
print MyLog "----I---- Processing files under Local Directory ".$localFilesDir."\n";
while ($f=readdir(IMD))
{
if ($f=~/\.$primaryExtension$/)
{
print MyLog "\n";
print MyLog "----I---- Currently processing file: ".$f."\n";
# Remove extension from file name
$oldName=$f;
$oldName=~s/\.$primaryExtension$/\.$oldExtension/;

$retCode=FTPFile($IPAddress,$FTPUsr,$FTPPass,$RemoteDir,$localFilesDir,$f);
if ($retCode !=1 )
{
print MyLog "----E---- FATAL ERROR. Cannot FTP ".$f." to Customer."."\n";
close(MyLog);

sendMail($mailScript,$logFile);
die;
}
else
{
## Move the file into the oldFile directory
move($localFilesDir."/".$f,$oldFilesDir."/".$oldName);

# Mark the file as SENT_IND=1 in the XFER_FILES table
$retCode=0;
$retCode=updateXFERFilesTable($db_user, $db_pass,$connection_str,"1",$myGate,$f);
if ($retCode!=1)
{
print "----E---- FATAL ERROR. Cannot update XFER_FILES table"."\n";
print MyLog "----E---- FATAL ERROR. Cannot update XFER_FILES table"."\n";
close(MyLog);
sendMail($mailScript,$logFile);
$dbh->disconnect;
die;
}
else
{
print MyLog "File ".$f." in XFER_FILES table was successfully updated."."\n";
}
}
}
}
print MyLog "----I---- CDR XFER process has finished successfully"."\n";
print MyLog "### End CDR XFER process ###"."\n";
close(MyLog);

sendMail.bat
blat mailBody.txt -to alec.kaplan@starhomemach.com -i mis@starhome.com -subject "CDR XFER Failure for EMTEL" -attacht %1

mailBody.txt
Hi All,

A Fatal Error Occurred during Files Transfer.

Thanks,
MIS Department
StarhomeMACH

There are many tablespaces, that actually do not hold any segments.
These are historical tablespaces.
The segments were dropped, but tablespaces, and related datafiles, were not.

Step 1.
Fetch all empty tablespaces

SELECT tablespace_name, file_name
FROM DBA_DATA_FILES
WHERE tablespace_name IN (
SELECT tablespace_name FROM (
SELECT tablespace_name FROM DBA_TABLESPACES
MINUS
SELECT tablespace_name FROM DBA_SEGMENTS

)

around 100 entries where returned!!

Here is an example for a single tablespace

Step 2.
Checks before dropping the tablespace and datafile.
Shrink the datafile.
This is a good practice before dropping a datafile, because if the datafile indded contain data, and an attampt is made to shring the datafile beyond the data limit, an error would be raised.

Before resize:
-rw-r----- 1 oracle dba 10493952 Jun 1 09:13 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 2M;
oracle@isr-sdc-1-cgw-1:~>% ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r----- 1 oracle dba 2105344 Jun 1 09:15 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 1M;

oracle@isr-sdc-1-cgw-1:~>% ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r----- 1 oracle dba 1056768 Jun 1 09:26 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 512K;
Database altered

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 256K;
Database altered

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 128K;

Database altered

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 64K

ORA-03214: File Size specified is smaller than minimum required

oracle@isr-sdc-1-cgw-1:~>% ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r----- 1 oracle dba 139264 Jun 1 09:28 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

Although the Tablespace is empty, the datafile cannot be shrinked below one extent size.

SQL> SELECT COUNT(*) FROM DBA_SEGMENTS WHERE tablespace_name = 'GATES_DB_TABLE';
COUNT(*)
----------
0

SQL> SELECT ROUND(bytes/1024) As Kb,
ROUND(user_bytes/1024) AS USER_KB,
tablespace_name
FROM DBA_DATA_FILES
WHERE file_name = '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' ;

KB USER_KB TABLESPACE_NAME
---------- ---------- ------------------------------
128 64 GATES_DB_TABLE

SQL> SELECT tablespace_name,
block_size/1024 AS block_size_kb,
initial_extent/1024 AS initial_extent_kb
FROM DBA_TABLESPACES
WHERE tablespace_name = 'GATES_DB_TABLE';

TABLESPACE_NAME BLOCK_SIZE_KB INITIAL_EXTENT_KB
------------------------------ ------------- -----------------
GATES_DB_TABLE 8 64

Step 3.
Drop tablespace and datafile
Option A.
Drop the datafile using ALTER TABLESPACE DROP DATAFILE Syntax.

ALTER TABLESPACE GATES_DB_TABLE DROP DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf'

ORA-02142: missing or invalid ALTER TABLESPACE option

It is not allowed to drop the first or the only datafile in tablespace.

SELECT COUNT(*) FROM DBA_TABLESPACES WHERE tablespace_name = 'GATES_DB_TABLE';

COUNT(*)
----------
1

Option B.
Drop datafile using DROP TABLESPACE INCLUDING CONTENTS Syntax.
This would drop the tablespace, and related datafile(s) from Oracle dictionary but not the physical file on the host.
To do so, need to run OS command.

DROP TABLESPACE GATES_DB_TABLE INCLUDING CONTENTS;

Tablespace dropped

ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r----- 1 oracle dba 139264 Jun 1 11:15 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

SELECT ROUND(bytes/1024) As Kb, ROUND(user_bytes/1024) AS USER_KB, tablespace_name
FROM DBA_DATA_FILES
WHERE file_name = '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' ;

KB USER_KB TABLESPACE_NAME
---------- ---------- ------------------------------

rm /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Monday, June 27, 2016

Perl by Example: Send files by ftp with perl

Wednesday, June 1, 2016

Code by Example: Drop Datafile