Recently, I decided to automate the regular backup of APEX applications on my servers. To achieve this I scheduled a regular PL/SQL job using crontab.
Basically I just loop through all of the applications, then export them to a clob and write this clob to a directory. I use the application ID, its name and the date as the output file name.
I have an optional part at the end where I push the directory to Amazon's S3 as off-site storage.
Basically I just loop through all of the applications, then export them to a clob and write this clob to a directory. I use the application ID, its name and the date as the output file name.
I have an optional part at the end where I push the directory to Amazon's S3 as off-site storage.
#!/bin/bash
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/dbhome_1
export PATH=${PATH}:${ORACLE_HOME}/bin
export ORACLE_SID=MYSID -- replace this with your SID
export ORAENV_ASK=NO
export OUTPUT_DIR=/u01/exports -- same as oracle dir DATA_PUMP_DIR
. ${ORACLE_HOME}/bin/oraenv
date
echo "Oracle Home is " ${ORACLE_HOME}
${ORACLE_HOME}/bin/sqlplus system/mysystempassword << EOC
SET ECHO ON
SET FEEDBACK ON
SET TIMING ON
SET TIME ON
SET SERVEROUTPUT ON
DECLARE
v_clob CLOB;
BEGIN
FOR rec IN (SELECT application_id, application_name
FROM apex_applications
ORDER BY application_id)
LOOP
v_clob := wwv_flow_utilities.export_application_to_clob(trim(to_char(rec.application_id)));
dbms_xslprocessor.clob2file
(cl => v_clob,
flocation => 'DATA_PUMP_DIR', -- oracle dir points to the same as $OUTPUT_DIR
fname => trim(to_char(rec.application_id,'99900000'))||'_'
||replace(rec.application_name,' ','_')
||'.'||to_char(sysdate,'YYYYMMDD')||'.sql');
dbms_output.put_line(rec.application_name||':'||length(v_clob));
END LOOP;
END;
/
EOC
date
#optional copy to Amazon S3 - you don't necessarily need this
TODAY=`date "+%Y%m%d"`
echo $TODAY
s3cmd put $OUTPUT_DIR/*$TODAY* s3://DBExports/APEXapplications/$TODAY/
date
Great Post Niall, Thank you. Would like to see you again in Tehran.
ReplyDeleteDafabet Link - Top-Rated Online Casino with Amazing Bonuses
ReplyDeleteDiscover best casino games at ボンズ カジノ Dafabet dafabet link Link including slots, blackjack, video poker, roulette, keno, video keno, jackpot slots 인카지노 and even the latest