Problem solve Get help with specific problems with your technologies, process and projects.

Script to run large daily schema exports

This is a script to use on AIX servers to run very large daily schema exports.

This is a script I use on AIX servers to run very large daily schema exports. I have to use the mknode method to trick the export utility to generate a dump file > 2 GB.

In earlier versions I referenced ../oratab for instance information, but I wanted to control which schemas were exported and to which file systems so I created (and must maintain) exporttab (an acceptable tradeoff I think). I use par files to standardize options and to "hide" the system or schema passwords so they do not appear in the process string.

Here is a sample exporttab line:

instance_name:schema_name:directory_name

Here is the script:

 
#!/bin/ksh
# ---------------------------------
# R.Dow 03/22/02

. ~/.profile

DATE=`date +'%Y%m%d_%H%M'`
ORACLE_HOME=/sprd/app/oracle/product/8.1.7
export ORACLE_HOME=${ORACLE_HOME}
ORATAB=/home/oracle/admin/script/exporttab

cat $ORATAB | while read LINE
do
    case $LINE in
        #*)
        ;;        #comment-line in oratab
        *)
         ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
         OSCHEMA=`echo $LINE | awk -F: '{print $2}' -`
         ARCH=`echo $LINE | awk -F: '{print $3}' -`
         export ORACLE_SID
         export OSCHEMA
         UNIQUE=$$
         NODE=/${ARCH}/${ORACLE_SID}/${UNIQUE}.dmp
         ORAFLAGS="parfile=/home/oracle/admin/script/${ORACLE_SID}_${OSCHEMA}.par file=${NODE} 
               log=/${ARCH}/${ORACLE_SID}/${ORACLE_SID}_${OSCHEMA}_${DATE}.log"
         mknod ${NODE} p
         gzip < ${NODE} > /${ARCH}/${ORACLE_SID}/${ORACLE_SID}_${OSCHEMA}_${DATE}.dmp.gz &
         ${ORACLE_HOME}/bin/exp ${ORAFLAGS}
                  sleep 5
           rm ${NODE}
       ;;
      esac
done

For More Information

Brian Peasland writes: An interesting take on things! The only comment that I have is that most Oracle versions now support dump files larger than 2GB. So I rarely use the mknod utility to pipe the export contents into a compression routine. It just slows things down (export and import).

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close