11 March 2015

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.  

 #!/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  

2 comments:

  1. Great Post Niall, Thank you. Would like to see you again in Tehran.

    ReplyDelete
  2. Dafabet Link - Top-Rated Online Casino with Amazing Bonuses
    Discover best casino games at ボンズ カジノ Dafabet dafabet link Link including slots, blackjack, video poker, roulette, keno, video keno, jackpot slots 인카지노 and even the latest

    ReplyDelete