shell script for expdp and impdp

-

            SHELL SCRIPT FOR EXPDP AND IMPDP 


#!/bin/bash
echo " ============================================================ "
echo " === Generic Export/Import of an Oracle Dump 11g      === "
echo " === version 1.00                      === "
echo " ============================================================ "
# " DO NOT change these values below !!!!! "
# " Please ask the administrator before "
#imp
INSTALL_DP=/u01/app/oracle/gen_ImportExport/importDumpFiles
DATA_PUMP_I=DATA_PUMP_IDIR
#exp
EXPORT_DP=/u01/app/oracle/gen_ImportExport/exportDumpFiles
DATA_PUMP_E=DATA_PUMP_EDIR
#bkp
DATA_PUMP_B=DATA_PUMP_BDIR


# " You CAN change these values below"
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export DATAFILE=/applications/conslife/oradata
export ORACLE_SID=CONSLIFE
HERE=$PWD
SYS_PWD=password
DFLT_ORACLE_HOME=$ORACLE_HOME

#    ================== Set the Oracle Home otherwise take the default value
echo "1. Oracle Home [Default:$ORACLE_HOME]    "
read ORACLE_HOME
if [ "$ORACLE_HOME" = "" ]
then
    ORACLE_HOME=$DFLT_ORACLE_HOME
fi
export ORACLE_HOME

#    ==================  Flag Do you want Export or Import ?
echo "2. Do you want to export/import ? [Please enter 'e' or 'i']    "
read EXPIMP
if [ "$EXPIMP" != "e" ] && [ "$EXPIMP" != "i" ]
then
    echo "e = Export | i = Import\n\n"
    echo "This program will close"
    exit
fi

#    ================== Do you want execute the script or simulate the script
echo "3. Do you want to execute the script ? [Please enter 'y' or 'n' (default)]"
read EXECUTE_ME
if [ "$EXECUTE_ME" != "y" ] && [ "$EXECUTE_ME" != "n" ]
then
    EXECUTE_ME=n
fi

DFLT_SID=$ORACLE_SID

#    ==================  IMPORT ==================
#    4.1 Oracle import dump directory
#    4.2 Oracle SID
#    4.3 Import Type
#    If(u=User)    
#     4.4  Schema user (from)
#    4.5  Schema user (target)
#    4.6  password user (target)
#    4.7  tablespace
#    4.8  temporary tablespace
#    4.9  password of the system user
#     ==================  ====== ==================
if [ "$EXPIMP" = "i" ]
then
    echo "Import...."
    
    #    ================== Set the location where is stored the dump to import
    echo "4.1 Oracle Datapump directory (Import) "
    echo "[Default location:$INSTALL_DP (recommended)]    "
    echo "[Default Oracle directory:$DATA_PUMP_I (recommended)] "
    read DPI
    if [ "$DPI" = "" ]
    then
        DPI=$DATA_PUMP_I
    fi
    export DPI
    
    
#    ==================  Set the Oracle SID otherwise take the default value
    DFLT_SID=$ORACLE_SID
  echo "4.2 Oracle SID [Default:$DFLT_SID]    "
  read ORACLE_SID
  if [ "$ORACLE_SID" = "" ]
  then
        ORACLE_SID=$DFLT_SID
  fi
  export ORACLE_SID
  
#    ==================   Flag to import the full database or only a user schema
  DFLT_TYPE=u
    echo "4.3 What do you want to import : Full database (f) or Only user (u) ? [Default:$DFLT_TYPE]    "
  read IMP_TYPE
  if [ "$IMP_TYPE" = "" ]
  then
        IMP_TYPE=$DFLT_TYPE
  fi
    ls $INSTALL_DP/*.dump *.dmp
    DFLT_FILE=`ls $INSTALL_DP/*.dump *.dmp| head -1`
    echo "4.4 File to import [Default:$DFLT_FILE]    "
  read IMP_FILE    
  if [ "$IMP_FILE" = "" ]
      then
            IMP_FILE=$DFLT_FILE
      fi
    
#    ================== FULL IMPORT ==================
    if [ "$IMP_TYPE" = "f" ]
    then
        IMP_LOG=importFull_$ORACLE_SID.log
        LAUNCHFILE='importFull_'$ORACLE_SID'_'$IMP_FILE'.sh'
        echo "===================="
        echo "Import Full... File Dump $IMP_FILE / File log=$IMP_LOG"
        echo "Generation of the script '$LAUNCHFILE'"
        echo "===================="
        echo "ORACLE_HOME=$ORACLE_HOME" > $LAUNCHFILE
        echo "ORA_NLS33=$ORA_NLS33" >> $LAUNCHFILE
        echo "ORA_NLS10=$ORA_NLS33" >> $LAUNCHFILE
        echo "PATH=$ORACLE_HOME/bin:$PATH" >> $LAUNCHFILE
        echo "nohup $ORACLE_HOME/bin/impdp system/$SYS_PWD@$ORACLE_SID DIRECTORY=$DPI LOGFILE=$IMP_LOG DUMPFILE=$IMP_FILE &" >> $LAUNCHFILE
        chmod 0755 $LAUNCHFILE
        if [ "$EXECUTE_ME" = "y" ]
        then
            sh $LAUNCHFILE
        else 
            echo "launch $LAUNCHFILE"
        fi
    else
    
#    ================== USER IMPORT ==================
#    ==================  set the source user
        DFLT_OWNER=IAMOWNER
        echo "4.4 Schema user (FROM) [Default:$DFLT_OWNER]    "
        read IMP_FROM_OWNER
        if [ "$IMP_FROM_OWNER" = "" ]
        then
                IMP_FROM_OWNER=$DFLT_OWNER
        fi
#    ==================  set the target user    
        IMP_TO_OWNER=IAMTARGET
        echo "4.5 Schema user (TARGET) [Default:$IMP_TO_OWNER]    "
        read IMP_TO_OWNER
        if [ "$IMP_TO_OWNER" = "" ]
        then
                IMP_TO_OWNER=$IMP_TO_OWNER
        fi
#    ==================  configuration of target script
        DROPSQL=_$ORACLE_SID.sql
        DROPSQL=Drop$IMP_TO_OWNER$DROPSQL
        CREATORSQL=_$ORACLE_SID.sql
        CREATORSQL=Create$IMP_TO_OWNER$CREATORSQL
#    ==================  Password of the target user    
        echo "4.6 Password of the user (TARGET) [Default:$IMP_TO_OWNER]    "
        read IMP_TO_OWNER_PWD
        if [ "$IMP_TO_OWNER_PWD" = "" ]
        then
                IMP_TO_OWNER_PWD=$IMP_TO_OWNER
        fi
#    ==================  set the target tablespace
        DFLT_TBLSPC=$IMP_TO_OWNER'_TBS'
        echo "4.7 Tablespace of the user (TARGET) [Default:$DFLT_TBLSPC (recommended)]    "
        read IMP_TBLSPC
        if [ "$IMP_TBLSPC" = "" ]
        then
                IMP_TBLSPC=$DFLT_TBLSPC
        fi
#    ==================  set the temporary tablespace    
        DFLT_TMP_TBLSPC=TEMP
        echo "4.8 Temporary tablespace of the user (TARGET) [Default:$DFLT_TMP_TBLSPC (recommended)]    "
        read IMP_TMP_TBLSPC
        if [ "$IMP_TMP_TBLSPC" = "" ]
        then
                IMP_TMP_TBLSPC=$DFLT_TMP_TBLSPC
        fi
#    ==================  system password        
        DFLT_SYS_PWD=$SYS_PWD
        echo "4.9 Password of the system user [Default:$DFLT_SYS_PWD]    "
        read SYS_PWD
        if [ "$SYS_PWD" = "" ]
        then
                SYS_PWD=$DFLT_SYS_PWD
        fi

        IMP_LOG=$IMP_TO_OWNER'_'$ORACLE_SID.log
        echo "===================="
        echo "Import for User... $IMP_TO_OWNER / File log=$IMP_LOG"
        echo "Generation of the script '$DROPSQL'"
        echo "===================="
        if [ -f "$DROPSQL" ]
            then
                echo $DROPSQL exists, will not overwrite
            else
                echo "spool $IMP_LOG.drop;" > $DROPSQL
                echo "SET echo ON;" >> $DROPSQL
                echo "WHENEVER SQLERROR EXIT -1 ROLLBACK;" >> $DROPSQL
                echo "PROMPT you need TO be connected AS SYSTEM USER" >> $DROPSQL
                echo "CONNECT system/$SYS_PWD@$ORACLE_SID;" >> $DROPSQL
                echo "DROP USER $IMP_TO_OWNER CASCADE;" >> $DROPSQL
                echo "DROP TABLESPACE $IMP_TBLSPC INCLUDING CONTENTS AND DATAFILES;" >> $DROPSQL
                echo "EXIT 0;" >> $DROPSQL
                echo $DROPSQL created
        fi
        echo "===================="
        echo "Import for User... $IMP_TO_OWNER / File log=$IMP_LOG"
        echo "Generation of the script '$CREATORSQL'"
        echo "===================="    
        if [ -f "$CREATORSQL" ]
            then
                echo $CREATORSQL exists, will not overwrite
            else
                echo "Create user $IMP_TO_OWNER/$IMP_TO_OWNER_PWD on $ORACLE_SID..."; 
                echo "spool $IMP_LOG.create;" > $CREATORSQL
                echo "SET echo ON;" >> $CREATORSQL
                echo "WHENEVER SQLERROR EXIT -1 ROLLBACK;" >> $CREATORSQL
                echo "PROMPT you need TO be connected AS SYSTEM USER" >> $CREATORSQL
                echo "CONNECT system/$SYS_PWD@$ORACLE_SID;" >> $CREATORSQL
                echo "Create new tablespace  '$IMP_TBLSPC' ? [y|n]    "
                read CREATE_TBLSPC
                if [ "$CREATE_TBLSPC" = "y" ]
                then
                    echo "CREATE TABLESPACE $IMP_TBLSPC " >> $CREATORSQL
                    echo "DATAFILE " >> $CREATORSQL
                    TABLE_SPACE_FILE=$ORACLE_SID'_'$IMP_TBLSPC'_DATA_01.dbf'
                    echo "'$DATAFILE/$TABLE_SPACE_FILE' SIZE 500M REUSE AUTOEXTEND ON ONLINE; " >> $CREATORSQL        
                fi
                echo "CREATE USER $IMP_TO_OWNER IDENTIFIED BY $IMP_TO_OWNER_PWD DEFAULT TABLESPACE $IMP_TBLSPC TEMPORARY TABLESPACE $IMP_TMP_TBLSPC; ">>  $CREATORSQL
                echo "GRANT CONNECT TO $IMP_TO_OWNER; " >> $CREATORSQL
                echo "GRANT RESOURCE TO $IMP_TO_OWNER; " >> $CREATORSQL
                
                echo "COMMIT;" >> $CREATORSQL
                echo "EXIT 0;" >> $CREATORSQL
                echo $CREATORSQL created
        fi    
        LAUNCHFILE='importUser_'$ORACLE_SID'_'$IMP_TO_OWNER'.sh'
        if [ -f "$LAUNCHFILE" ]
        then
            echo $LAUNCHFILE exists, will not overwrite
        else
            echo "===================="
            echo "Import of User $IMP_FROM_OWNER to User $IMP_TO_OWNER... File DUMP=$IMP_FILE / File log=$IMP_LOG"
            echo "Generation of the script '$LAUNCHFILE'"
            echo "===================="    
            echo "ORACLE_HOME=$ORACLE_HOME" > $LAUNCHFILE
            echo "ORA_NLS33=$ORA_NLS33" >> $LAUNCHFILE
            echo "ORA_NLS10=$ORA_NLS33" >> $LAUNCHFILE
            echo "PATH=$ORACLE_HOME/bin:$PATH" >> $LAUNCHFILE
            echo "$ORACLE_HOME/bin/sqlplus /nolog @$HERE/$DROPSQL" >> $LAUNCHFILE
            echo "if [ \$? != 0 ]" >> $LAUNCHFILE
            echo "then" >> $LAUNCHFILE
            echo "echo 'Error during the suppression of the user'" >> $LAUNCHFILE
            echo "fi " >> $LAUNCHFILE
            echo "$ORACLE_HOME/bin/sqlplus /nolog @$HERE/$CREATORSQL" >> $LAUNCHFILE
            echo "if [ \$? != 0 ]" >> $LAUNCHFILE
            echo "then" >> $LAUNCHFILE
            echo "echo 'Error during the creation of the user'" >> $LAUNCHFILE
            echo "exit \$?" >> $LAUNCHFILE
            echo "fi " >> $LAUNCHFILE
            echo "nohup $ORACLE_HOME/bin/impdp system/$SYS_PWD@$ORACLE_SID SCHEMAS=$IMP_FROM_OWNER DIRECTORY=$DPI LOGFILE=$IMP_LOG DUMPFILE=$IMP_FILE REMAP_SCHEMA=$IMP_FROM_OWNER:$IMP_TO_OWNER REMAP_TABLESPACE=CONSLEG:$IMP_TBLSPC &" >> $LAUNCHFILE
            
            chmod 0755 $LAUNCHFILE
        fi
        
        if [ "$EXECUTE_ME" = "y" ]
        then
            sh $LAUNCHFILE
        else 
            echo "launch $LAUNCHFILE"
        fi
    fi
else
#    ==================  EXPORT ==================
#    4.1 Oracle export dump directory
#    4.2 Oracle SID
#    4.3 Export Type
#    4.4  password of the system user
#    If(u=User)    
#     4.5  Schema user to export
#     ==================  ====== ==================
    if [ "$EXPIMP" = "e" ]
    then
        echo "Export...."
#    ================== Set the location where is stored the dump to export
        echo "4.1 Oracle Datapump directory (Export) "
        echo "[Default location:$EXPORT_DP (recommended)]    "
        echo "[Default Oracle directory:$DATA_PUMP_E (recommended)] "
        read DPE
        if [ "$DPE" = "" ]
        then
            DPE=$DATA_PUMP_E
        fi
        export DPE    
        
#    ==================  Set the Oracle SID otherwise take the default value
        DFLT_SID=$ORACLE_SID
        echo "4.2 Oracle SID [Default:$DFLT_SID]    "
        read ORACLE_SID
        if [ "$ORACLE_SID" = "" ]
        then
            ORACLE_SID=$DFLT_SID
        fi
        export ORACLE_SID

#    ==================   Flag to export the full database or only a user schema
        DFLT_TYPE=u
        echo "4.3 What do you want to export : Full database (f) or Only user (u) ? [Default:$DFLT_TYPE]    "
        read EXP_TYPE
        if [ "$EXP_TYPE" = "" ]
        then
            EXP_TYPE=$DFLT_TYPE
        fi
        
#    ==================  system password        
        DFLT_SYS_PWD=$SYS_PWD
        echo "4.4 Password of the system user [Default:$DFLT_SYS_PWD]    "
        read EXP_SYS_PWD
        if [ "$EXP_SYS_PWD" = "" ]
        then
                EXP_SYS_PWD=$DFLT_SYS_PWD
        fi
#    ================== FULL EXPORT ==================
        if [ "$EXP_TYPE" = "f" ]
        then
            EXP_FILE=exportFull_$ORACLE_SID.dump
            EXP_LOG=exportFull_$ORACLE_SID.log        
            echo "===================="
            echo "Export Full... File Dump=$EXP_FILE / File log=$EXP_LOG"
            echo "===================="
            LAUNCHFILE='exportFull_'$ORACLE_SID'.sh'
            echo "ORACLE_HOME=$ORACLE_HOME" > $LAUNCHFILE
            echo "ORA_NLS33=$ORA_NLS33" >> $LAUNCHFILE
            echo "ORA_NLS10=$ORA_NLS33" >> $LAUNCHFILE
            echo "PATH=$ORACLE_HOME/bin:$PATH" >> $LAUNCHFILE
            echo "nohup $ORACLE_HOME/bin/expdp system/$EXP_SYS_PWD@$ORACLE_SID DIRECTORY=$DPE LOGFILE=$EXP_LOG DUMPFILE=$EXP_FILE &">> $LAUNCHFILE

            chmod 0755 $LAUNCHFILE
            
            if [ "$EXECUTE_ME" = "y" ]
            then
                sh $LAUNCHFILE
            else 
                echo "launch $LAUNCHFILE"
            fi
        else
#    ================== USER EXPORT ==================        
            DFLT_OWNER=CONSLEGOWNER
            echo "4.5 Schema user to export [Default:$DFLT_OWNER]    "
            read EXP_OWNER
            if [ "$EXP_OWNER" = "" ]
            then 
                    EXP_OWNER=$DFLT_OWNER
            fi    
          
            EXP_FILE=$EXP_OWNER'_'$ORACLE_SID.dump
            EXP_LOG=$EXP_OWNER'_'$ORACLE_SID.log
                
            echo "===================="
            echo "Export of the user $EXP_OWNER@$ORACLE_SID... File Dump=$EXP_FILE / File log=$EXP_LOG"
            echo "===================="
            
            LAUNCHFILE='exportUser_'$ORACLE_SID'_'$EXP_OWNER'.sh'
            echo "ORACLE_HOME=$ORACLE_HOME" > $LAUNCHFILE
            echo "ORA_NLS33=$ORA_NLS33" >> $LAUNCHFILE
            echo "ORA_NLS10=$ORA_NLS33" >> $LAUNCHFILE
            echo "PATH=$ORACLE_HOME/bin:$PATH" >> $LAUNCHFILE
            echo "nohup $ORACLE_HOME/bin/expdp system/$EXP_SYS_PWD@$ORACLE_SID DIRECTORY=$DPE SCHEMAS=$EXP_OWNER LOGFILE=$EXP_LOG DUMPFILE=$EXP_FILE &">> $LAUNCHFILE

            chmod 0755 $LAUNCHFILE
                
            if [ "$EXECUTE_ME" = "y" ]
            then
                sh $LAUNCHFILE
            else 
                echo "launch $LAUNCHFILE"
            fi
        fi
    fi
fi



THIS SCRIPT WILL HELP OUT MANY ONE !!
SPECIALLY FOR BEGINNERS

dont forget to comment !!


Comments

  1. SV7 February 2019 at 02:00

    with all due respect, please format your article properly, it is not at all readable.

    ReplyDelete

Post a Comment

Popular posts from this blog

Query for Tablespace usage with Autoextend

-
                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...
Read more

How to configure multiple Oracle listeners

-
How to configure multiple Oracle listeners It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment. 1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.    2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2 Modify listener.ora and tnsnames.ora as following: Here is a sample of   listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...
Read more

sheel script - automatic tablespace addition in oracle 11g

-
      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...
Read more