Check for empty file in Unix systems

Often, there arises a need to check if a particular file exists and/or is it empty ?, this usually is the scenario when developing shell scripts to automate stuff and a file is created by the script to hold intermediate results. So when the script is scheduled to run next time, it has to execute a sequence of commands if the file exists or it would execute a different set of commands when the file is no longer available.

Here is a simple code snippet which checks for the existence of a file and that it is NOT empty to return TRUE and it returns FALSE otherwise.

if [ -s test_file ]  ### if the test_file exists and that it is not empty
                     ### i.e., file has contents
then
echo "TRUE"          ### returns 'true' ONLY if the file exists and it is NOT empty,
                     ### i.e., file has contents - file is not of 0 bytes in size
else
echo "FALSE"         ### returns 'false' if the file exists and it is empty
                     ### (0 bytes in size).
                     ### returns 'false' also if the file does not exist
fi

For different results to appear, try creating a file 'test_file' and by changing its contents to make it a sized file and/or to make it an empty file.

Funny Oracle Error....

I always have a habit of looking up for the errors returned by Oracle in the server itself using the 'oerr' utility before googling it out since 'oerr' gives you a suggestion of a quick fix saving you quite some time that is spent on rooting through the innumerable hits from Google for a workaround or a fix.

I stumbled upon an error when trying to pull the DDL of a materialized view using the dbms_metadata.get_ddl function, upon trying to find the cause/action of each of the errors in the returned error stack I was told to "Please contact someone who can fix the problem.", but who would that SOMEONE be....Hey Larry !!! Mr. Ellison, are you listening ;-)

SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','JUNK_MVIEW','BOGUS_SCHEMA') from dual;

ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 246
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7511
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

SQL> ! oerr ora 6502
06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause:
// *Action:

SQL> ! oerr ora 31605
31605, 00000, "the following was returned from %s in routine %s:\nLPX-%d: %s"
// *Cause:  An LPX routine (XML/XSL processing) returned an internal
//          error number to its PL/SQL wrapper routine in facility KUX which
//          provides the implementation for package UTL_XML.
// *Action: Look up the LPX error number and follow its corrective action.

SQL> ! oerr lpx 1
00001, 00000, "NULL pointer"
// *Cause:  A NULL pointer was detected as an internal error condition.
// *Action: This is a programming error by the caller of the XML parser.
//          Please contact someone who can fix the problem.

Windows Oracle database administration scripts for hot backup and export backup

Automated conventional hot backup, export backup and their clean up script on a windows environment are pasted below - it is advised to go through the script / test on a Dev-Test environment before moving over to Prod since a couple of locations are to be hardcoded based on the environment. Most of the work is being carried out by pl/sql scripts, this gave me little chance to improvise my pl/sql skills ;-).


The .sql files in the .bat files create runtime sql files that will place the backup files in a folder named after the date string of the current day. The scripts are located in 'D:\oracle\backups\scripts' and the hot backup is taken in 'D:\oracle\backups\hot\DD-MON-YYYY' directory while the export backup is taken in 'D:\oracle\backups\exp\DD-MON-YYYY'. Based on your environment please change Drive letter and other locations, which has to be reflected in all the scripts and batch files.

Due to some limitations in the way this webpage is built some lines of the scripts may have been cropped, please click on the name of the script to open it in a new window which you can copy from.

###############################
# # #   H O T   B K U P   # # #
###############################

################################
### hot_backup_on_disk.bat  ####
################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-bkup_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-hot_bkup.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\03-arch_switch.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\04-ctrl_file.sql

###########################
####  01-bkup_dest.sql  ###
###########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\bkup_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\hot\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\bkup_dest_create.sql
exit

##########################
###  02-hot_bkup.sql  ####
##########################


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\hot_bkup_copy.sql
declare
cursor ts is select distinct(tablespace_name) 
from dba_data_files;
cursor fn (ts_name in VARCHAR2) is select file_name 
from dba_data_files
where tablespace_name=ts_name order by 1;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Datafiles --');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
for c in ts
loop
dbms_output.put_line('alter tablespace '||c.tablespace_name - 
||' begin backup;');
for d in fn (c.tablespace_name)
loop
dbms_output.put_line('host copy '||d.file_name|| - 
' D:\oracle\backups\hot\'||dir||'\.');
end loop;
dbms_output.put_line('alter tablespace '|| - 
c.tablespace_name||' end backup;');
dbms_output.put_line(chr(0));
end loop;
end;
/
spool off
@D:\oracle\backups\scripts\hot_bkup_copy.sql
exit

#############################
####  03-arch_switch.sql ####
#############################

###
### The LOG_ARCHIVE_FORMAT parameter
### plays a significant role in here
### the format of archive files that 
### is used here would be
### ${ORACLE_SID}_ARCH_*_*001, which
### has to be changed appropriately
### based on your environment
###


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\arch_switch_copy.sql
declare
prev_seq number;
i number;
j number;
k number;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
select sequence# into i from v$log where status='CURRENT';
prev_seq:=i-1;
execute immediate ('alter system switch logfile');
select sequence# into j from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
select sequence# into k from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Logfiles ---');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
dbms_output.put_line('host copy - 
D:\oracle\oradata\arch\PROD_ARCH_*'||prev_seq||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||i||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||j||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||k||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\arch_switch_copy.sql
exit

##########################
###  04-ctrl_file.sql  ###
##########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\ctrl_file_copy.sql
declare
dir varchar2(20);
 
begin
select to_char(sysdate,'DD-MON-YYYY') into 
dir from dual;
execute immediate 'alter session set tracefile_identifier='||''''||dir||'''';
execute immediate ('alter database backup controlfile to trace');
execute immediate 'alter database backup controlfile to'||''''||'D:\oracle\backups\hot\'||dir||'\controlfile_'||dir||'_bin.bkup'||'''';
dbms_output.put_line('host move D:\oracle\admin\PROD\udump\*'-
||dir||'*.trc D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\ctrl_file_copy.sql
exit

###############################
# # #   E X P   B K U P   # # #
###############################

####################################
###   export_backup_on_disk.bat  ###
####################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-exp_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-exp_bkup.sql

#########################
###  01-exp_dest.sql  ###
#########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\exp_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\exp\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\exp_dest_create.sql
exit

#########################
###  02-exp_bkup.sql  ###
#########################

set head off
set feed off
set line 300
set serveroutput on
spool D:\oracle\backups\scripts\exp_bkup_full.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host exp system/manager file=D:\oracle\backups\exp\'||dir||'\PROD_full.dmp log=D:\oracle\backups\exp\'||dir||'\PROD_full.log full=y buffer=10485760 consistent=y compress=n');
end;
/
spool off
@D:\oracle\backups\scripts\exp_bkup_full.sql
exit

#######################################
# # #   C L E A N U P   J O B S   # # #
#######################################

###########################
### delete_hot_bkup.bat ###
###########################

###
### Keeps the last 5 days of hot backups  
### on disk while deleting anything older
### than that
###

forfiles /p "D:\oracle\backups\hot" /s /m *.* /d -05 /c "cmd /c del @path"

###########################
### delete_exp_bkup.bat ###
###########################

###
### keeps the last 5 days of export backups
### on disk while deleting anything older than
### that
###

forfiles /p "D:\oracle\backups\exp" /s /m *.* /d -05 /c "cmd /c del @path"