How to FTP Archive Log Files to Standby / DR Server at Regular Intervals, Using Unix Script and Cron Job

How to FTP Archive Log Files to Standby / Disaster Recovery (DR) Server at Regular Intervals, Using Unix Script and Cron Job

 

The sample script will help to synchronize / transport archive log files from primary server to the DR / Standby Server

This sample copies all files since yesterday (estimated) to a temporary FTP directory and then starts FTPing to DR database. The process is very sensitive and could result in very serious consequences. Please be very careful while applying it on production database.

a. Create Unix script as listed following:

$ cd /data1/users/scripts/backup/

$ vi ftp_logs_to_dr.sh

#!/bin/sh

HOST='testHost.com'

USER='testuser'

PASSWD='testpassword'

## define actual archive log directory

TARGET_DIR1=/data/app/oracle/orcl/FTP_ARCLOGS/

## define temporary archive log directory to be used by ftp

TARGET_DIR2=/data/app/oracle/orcl/archlogs/

SUCCESS1='NOT OK'

SUCCESS2='NOT OK'

SUCCESS3='NOT OK'

#CREATE A LOG FILE

NEW_DATE1=`date +"%m_%d_%y"`

LOGFILE_NAME='FTP_ARCLOGS'$NEW_DATE1'.log'

touch /data1/users/ftplog/$LOGFILE_NAME

##Clear the temporary archive log directory first

cd /data/app/oracle/orcl/FTP_ARCLOGS/

CUR_DIR=`pwd`'/'

if [ "$CUR_DIR" = "$TARGET_DIR1" ]

then

rm -f *

SUCCESS1='OK'

echo $SUCCESS1

fi

## Go to actual archive log directory and copy files(until yesterday) to

## the ftp directory

rcp -p `find /data/app/oracle/orcl/archlogs/ -name '*arc' -mtime -1` /data/app/oracle/orcl/FTP_ARCLOGS/

##Update log file with the list of files to be FTPed

cd /data/app/oracle/orcl/FTP_ARCLOGS/

echo 'List of files to be copied...... date/time: ' `date` >> /data1/users/ftplog/$LOGFILE_NAME

ls -l >> /data1/users/ftplog/$LOGFILE_NAME

CUR_DIR=`pwd`'/'

if [ "$CUR_DIR" = "$TARGET_DIR1" ]

then

echo 'Connecting to FTP server and start sending files. Please be patient during this operation.... date/time: ' `date` >> /data1/users/ftplog/$LOGFILE_NAME

## Kick start FTP to send files to target server

ftp -n -i $HOST <<END_SCRIPT

quote USER $USER

quote PASS $PASSWD

bin

cd cd /dataDR/app/oracle/orcl/archlogsDR/

mput *.arc

quit

END_SCRIPT

echo 'FTP operation completed successfully. --  date/time: ' `date` >> /data1/users/ftplog/$LOGFILE_NAME

SUCCESS3='OK'

else

`date` >> /data1/users/ftplog/$LOGFILE_NAME

SUCCESS3='NOT OK'

fi

exit 0

b. Configure the script in crontab to run regularly at 10:00 pm (for example)

0 22 * * * /data1/users/scripts/backup/ftp_logs_to_dr.sh

How to Run Unix Script from within PL/SQL oracle 10g - Solaris 10

How to Run Unix Script from within PL/SQL oracle 10g - Solaris 10

This example will demonstrate how to run an a unix script from within PL/SQL. The example uses DBMS_SCHEDULER builtin oracle package.The example was tested on Solaris 10 with Oracle 10g instance.

$ sqlplus / as sysdba
SQL>
CREATE OR REPLACE
  PROCEDURE RUN_OS_SCRIPT( in_os_command IN VARCHAR2)
  IS
   v_prg          NUMBER;
   v_jobs         NUMBER;
   error_message  VARCHAR2(500);
   v_count99      NUMBER;
   v_job_action   VARCHAR2(100);
   v_arg          VARCHAR2(50);
  BEGIN
   v_job_action := in_os_command;
   v_prg         := 0;
   v_jobs        := 0;
   v_count99     := 0;
   BEGIN
      dBMS_SCHEDULER.stop_job (job_name => 'RUN_UNIX_SCRIPT_JOB' );
   EXCEPTION
   WHEN OTHERS THEN
      NULL;
   END;
   --=========================================================================
   -- This loop will make sure that the job is not already running. This seems to be a bug in 10g that
   -- stop and drop commands fail to stop or drop schedules / jobs immediately.
   -- To oversome this problem I have added this loop to make sure the next run should be successful.
   --=========================================================================
   LOOP
      IF v_count99 > 2000 THEN
         EXIT;
      END IF;
      v_count99 := v_count99+1;
      SELECT COUNT(*)
      INTO v_prg
      FROM sys.dba_scheduler_programs
      WHERE upper(PROGRAM_NAME) = upper('RUN_UNIX_SCRIPT_PRG') ;
      SELECT COUNT(*) INTO v_jobs
      FROM sys.dba_scheduler_jobs
      WHERE upper(JOB_NAME) = upper( 'RUN_UNIX_SCRIPT_JOB' ) ;
      IF v_prg > 0 OR v_jobs > 0
       THEN
         BEGIN
            DBMS_SCHEDULER.stop_job (job_name => 'RUN_UNIX_SCRIPT_JOB' );
            DBMS_SCHEDULER.drop_program  ( program_name => 'RUN_UNIX_SCRIPT_PRG' , force => TRUE);
            DBMS_SCHEDULER.drop_schedule ( schedule_name => 'RUN_UNIX_SCRIPT_SCHEDULE' , force => TRUE);
            DBMS_SCHEDULER.drop_job      ( job_name => 'RUN_UNIX_SCRIPT_JOB' , force => TRUE);
         EXCEPTION
         WHEN OTHERS THEN
            NULL;
         END;
      ELSE
         EXIT ;
      END IF;
   END LOOP;
   --========================================================================

   --========================================================================
   -- Create a new schedule and define a job to run
   --========================================================================
   DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'RUN_UNIX_SCRIPT_PRG' , program_type => 'EXECUTABLE',
   program_action => v_job_action, enabled         => FALSE, number_of_arguments => 0, comments => 'RUN RUN_UNIX_SCRIPT' );
   DBMS_SCHEDULER.enable (name                     => 'RUN_UNIX_SCRIPT_PRG' );
   DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name   => 'RUN_UNIX_SCRIPT_SCHEDULE', start_date => sysdate + .001,  repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'test schedule');
   DBMS_SCHEDULER.CREATE_JOB ( job_name            => 'RUN_UNIX_SCRIPT_JOB', program_name => 'RUN_UNIX_SCRIPT_PRG' ,   schedule_name => 'RUN_UNIX_SCRIPT_SCHEDULE', enabled=> true, auto_drop=> false);
   DBMS_SCHEDULER.run_job (job_name                => 'RUN_UNIX_SCRIPT_JOB' );
   error_message := 'job done';
   dbms_output.put_line(error_message);

   --===================================================================
   -- Drop schedule after the job is completed
   --===================================================================
   BEGIN
      DBMS_SCHEDULER.drop_program ( program_name     => 'RUN_UNIX_SCRIPT_PRG' , force => TRUE);
      DBMS_SCHEDULER.drop_schedule ( schedule_name   => 'RUN_UNIX_SCRIPT_SCHEDULE' , force => TRUE);
      DBMS_SCHEDULER.drop_job ( job_name             => 'RUN_UNIX_SCRIPT_JOB' , force => TRUE);
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
   END;
EXCEPTION
 WHEN OTHERS THEN
   error_message := v_count || '    ' ||SUBSTR(SQLERRM,1,100);
   dbms_output.put_line(error_message);
END;
/

Execute following code to run the operating system command / script.

  BEGIN
     RUN_OS_SCRIPT('/data1/users/scripts/copy_arch_logs.sh' );
  END;
/

How to extract / download / unpack a BLOB with large data into a CSV File using raw column

How to extract / download / unpack a BLOB with large data into a CSV File using raw column

This example will help to upload larger taxt into blobs and later download / extract into a csv file. Normally this can be achived using CLOBS but what if your CSV file goes upto 4 GB? I think this is the best way in a  situation like that.

Following procedure requires two parameters
 1. A reference key to read the blob from database table (The blob must already be stored in a
     database table).
2.  Directory where you want to create a CSV file.

The output will return 'OK' if the procedure ran successfully.

CREATE OR REPLACE PROCEDURE
                         CREATE_CSV_USING_BLOB(P_BLOB_ID in number,
                                                                             P_DIRECTORY IN VARCHAR2,
                                                                             P_RESULT OUT VARCHAR2) is
    V_BLOB BLOB;
    V_START NUMBER := 1;
    V_BLOB_CHUNK_LENGTH NUMBER;
    V_READ_BLOB_IN_RAW RAW(32000);
    V_BLOB_LENGTH NUMBER;
    V_FILENAME VARCHAR2(200);
    V_OUTPUT_FILENAME utl_file.file_type;
  BEGIN
    P_RESULT := 'OK';
    V_START := 1;
    V_BLOB_CHUNK_LENGTH := 32000;
    -- get length of blob
    SELECT dbms_lob.getlength(FILE_BLOB)
    INTO V_BLOB_LENGTH
    FROM UPLOAD_STREET_FILES
    WHERE  FILE_ID =  P_BLOB_ID;
   -- select blob into variable
    SELECT FILE_BLOB,
           FILE_NAME
    INTO   VBLOB,
           V_FILENAME
    FROM   UPLOAD_STREET_FILES
    WHERE  FILE_ID =  P_BLOB_ID;
   -- define output file which will store the csv data

    V_OUTPUT_FILENAME := utl_file.fopen(P_DIRECTORY , V_FILENAME,'wb', 32760);
    -- if small enough for a single write just do it
    IF V_BLOB_LENGTH < 32760 THEN
      utl_file.put_raw(V_OUTPUT_FILENAME,V_BLOB);
      utl_file.fflush(V_OUTPUT_FILENAME);
    ELSE -- write in small pieces and carry on until finished
      V_START := 1;
      WHILE V_START < V_BLOB_LENGTH and V_BLOB_CHUNK_LENGTH > 0
      LOOP
        dbms_lob.read(V_BLOB,V_BLOB_CHUNK_LENGTH,V_START,V_READ_BLOB_IN_RAW);
        utl_file.put_raw(V_OUTPUT_FILENAME,V_READ_BLOB_IN_RAW);
        utl_file.fflush(V_OUTPUT_FILENAME);
        -- set the start position for the next chunk to read
        V_START := V_START + V_BLOB_CHUNK_LENGTH;
        -- set the end position if less than 32000 bytes (could be reading the last line)
        V_BLOB_LENGTH := V_BLOB_LENGTH - V_BLOB_CHUNK_LENGTH;
        IF V_BLOB_LENGTH < 32000 THEN
           V_BLOB_CHUNK_LENGTH := V_BLOB_LENGTH;
        END IF;
     END LOOP;
    END IF;
    utl_file.fclose(V_OUTPUT_FILENAME);
    dbms_output.put_line( P_RESULT );
    EXCEPTION
     wheN OTHERS THEN
       dbms_output.put_line (SQLERRM );
       P_RESULT := SQLERRM ;
  END;
 /

SQL> EXECUTE CREATE_CSV_USING_BLOB(1001, 'UNIX_CSV_OUTPUT_DIRECTORY' );

Global / Package based cursors - pl/sql

This type of cursors are written to facilitate other languages to use oracle table in their queries. Widely used by Java develpers. Another advantage of writing cusrors at package level is that they can be used by any other procedures / functions and save from rewriting most commonly used cursors repeatedly.

To write a global / package level cursor we may need at least folloiwng 2 steps.

1. Defind a global cursor in package specs

create or replace package read_cursor_pkg is
    cursor c_read_employees is
    select * from employees;
    read_employees c_read_employees%ROWTYPE;
    procedure run_query;
end;
/

2.  Use package body to open and fetch the cursor

create or replace package body read_cursor_pkg is
    procedure run_query is
    begin
        open read_cursor_pkg.c_read_employees ;
        loop
            fetch read_cursor_pkg.c_read_employees into read_cursor_pkg.read_employees;
            exit when read_cursor_pkg.c_read_employees%NOTFOUND;
            DBMS_OUTPUT.put_line(
                                 read_cursor_pkg.read_employees.EMPLOYEE_ID ||' - ' ||
                                 read_cursor_pkg.read_employees.FIRST_NAME  ||' - ' ||
                                 read_cursor_pkg.read_employees.first_Name
                                );
        end loop;
        close read_cursor_pkg.c_read_employees;
     end;
end;
/


SQL>set serveroutput on
SQL>execute read_cursor_pkg.run_query ;
Package body created.
SQL>
SQL> set serveroutput on
SQL> execute read_cursor_pkg.run_query ;
198 - Donald - Donald
199 - Douglas - Douglas
200 - Jennifer - Jennifer
201 - Michael - Michael
202 - Pat - Pat
203 - Susan - Susan
204 - Hermann - Hermann
205 - Shelley - Shelley
206 - William - William
100 - Steven - Steven
101 - Neena - Neena
102 - Lex - Lex
103 - Alexander - Alexander

PL/SQL procedure successfully completed.
SQL>