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/10.2.0.4/bin | 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
vi archive.sh
#!/bin/bash
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 archive.sh
# 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
PROCEDURE ARCHIVE_FILE
AS
BEGIN
begin
dbms_scheduler.drop_job(job_name => 'archivefile2');
exception
when others then
null;
end;
dbms_scheduler.create_job(job_name => 'archivefile2',
job_type => 'executable',
job_action => '/extjob/archive.sh',
enabled => TRUE,
auto_drop => TRUE
);
commit;
exception
when others then
dbms_output.put_line('ERROR:' || sqlerrm);
END ARCHIVE_FILE;