Compress file dynamically using plsql and instant job.

This is very similar to the last function I demo’d, just this plsql app will take 3 arguments

1. Directory –> This is the directory of the current file that will be backed up.
2. FileName –> This is the current file name for the file we wish to compress and archive.
3. ArchiveDirectory –> This is the back up or archive directory where the file will sit when complete.

The application user running this plsql procedure will need to be able to schedule jobs since this is the method to run the os commands.
I’m opting to use os commands because there does not need to be impact on the database engine while this runs.
You may need to tweak this based on os since this is designed specifically for Linux os Testing on rh4, proofing on ol6

grant create job to <user>;
grant create any job to <user>;
grant CREATE EXTERNAL JOB to <user>;

-- Grant access to oracle home
chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

on os -- 391820.1

sudo su – root
cd <ORACLE_HOME>/rdbms/admin
chmod 640 externaljob.ora
vi externaljob.ora
run_user = oracle
run_group = dba

groups oracle
Verify oracle is in the group that owns the oracle home directory “maybe oinstall”

[/extjob]# ls -l <ORACLE_HOME>/rdbms/admin | grep externaljob.ora
-rw-r--r--  1 root   dba    1531 Aug  8 14:17 externaljob.ora

[/extjob]# ls -l /app/oracle/product/ | grep extjob
-rwsr-x---  1 root   dba    622819 Oct 21  2008 extjob
-rwxr-xr-x  1 oracle dba    622819 Oct 21  2008 extjobo


Example archive script, install this on os
mkdir /extjob/archive –p
chown oracle.dba /extjob –R
chmod 775 /extjob –R

#Note Oracle is fairly touchy about the syntak and I had to play with it a little to set it to get no errors, things that work simply on the os may not work as expected from the oracle user.
#Also remember the user is coming from the oracle home and all references are from oracle home so you may need to declare paths for logic

touch /extjob/stj_test3.log
tar -czf /extjob/stj4.tgz -C /extjob stj_test3.log
mv /extjob/stj4.tgz /extjob/archive/stj4.tgz

#Now you plsql application to call
# This job is scheduled to run immediately and in the same session if you want it to run in a different session you’ll need to add a run_job with a use_current_session=>false
    dbms_scheduler.drop_job(job_name        => 'archivefile2');
    when others then
  dbms_scheduler.create_job(job_name        => 'archivefile2',
                            job_type        => 'executable',
                            job_action      => '/extjob/',
                            enabled         => TRUE,
                            auto_drop       => TRUE
  when others then
    dbms_output.put_line('ERROR:' || sqlerrm);