script for auto rman and logmnr-优快云博客

#!/bin/bash
export ORACLE_HOME=/home/oracle/ora10g/product/10.2.0/db_1
export ORACLE_SID=rac
export PATH=$ORACLE_HOME/bin:$PATH
###############JUDGE SPFLE OR INIT.ORA START DATABASE##########
$ORACLE_HOME/bin/sqlplus '/as sysdba' << EOF
spool /home/oracle/script/status.txt
show parameter spfile
spool off
EOF
PARAMETER=`cat status.txt|grep spfile${ORACLE_SID}.ora`

###############AUTO SETUP LOGMNR LOG#############################
function setup_logmnr_spfile {
echo -en "INPUT LOGMNR_FILENAME FOR BUILD DICTIONARY FILE: "
read LOGMNR_FILENAME
sqlplus '/as sysdba' <    alter system set UTL_FILE_DIR='$UTL_FILE_DIR' scope = spfile;
    shutdown immediate;
    startup ;
    @$ORACLE_HOME/rdbms/admin/dbmslm.sql;
    @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
    @$ORACLE_HOME/rdbms/admin/prvtlm.plb;
    exec dbms_logmnr_d.build ('$LOGMNR_FILENAME','$UTL_FILE_DIR');
EOF
echo "SETUP LOGMNR COMPLETE......"
}

function setup_logmnr_pfile {
echo -en "INPUT LOGMNR_FILENAME FOR BUILD DICTIONARY FILE: "
read LOGMNR_FILENAME
sqlplus '/as sysdba' <    startup ;
    @$ORACLE_HOME/rdbms/admin/dbmslm.sql;
    @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
    @$ORACLE_HOME/rdbms/admin/prvtlm.plb;
    exec dbms_logmnr_d.build ('$LOGMNR_FILENAME','$UTL_FILE_DIR');
EOF
echo "SETUP LOGMNR COMPLETE......"
}

function logmnr {
echo -en "SETUP UTL_FILE_DIR DESTINATION FOR LOGMNR: "
read UTL_FILE_DIR
      if [ -d $UTL_FILE_DIR ]
             then
               if [ "$PARAMETER" > 0 ]
                 then
                   if [ "$STATUS" = "MOUNTED" ]
                        then
                        sqlplus '/as sysdba' <                        alter database open;
EOF
                       setup_logmnr_spfile
                    elif [ "$STATUS" = "ORACLE not available" ]
                       then
                       sqlplus '/as sysdba' <                       startup;
EOF
                       setup_logmnr_spfile
                    elif [ "$STATUS" = "database not mounted" ]
                       then
                       sqlplus '/as sysdba' <                       alter database mount;
                       alter database open;
EOF
                       setup_logmnr_spfile
                    else
                       setup_logmnr_spfile
                    fi
               elif [ "$PARAMETER" < 0 ]
                 then
                    if [ "$STATUS" = "ORACLE not available" ]
                       then
                       echo "UTL_FILE_DIR#'$UTL_FILE_DIR'" >>$HOME_ORACLE/dbs/init${ORACLE_SID}.ora
                       setup_logmnr_pfile
                    else
                       sqlplus '/as sysdba' <                       shutdown immediate;
EOF
                       setup_logmnr_pfile
                    fi
               fi
      else
         echo "$UTL_FILE_DIR IS NOT DESTINATION,SETUP LOGMNR NOT COMPLETE"
      fi
}

################AUTO RMAN BACKUP SCRIPT#########################
function rman_bak {
rman target / nocatalog log='/home/oracle/script/rman.log' <crosscheck archivelog all;
delete expired archivelog all;
yes
run
{
allocate channel disk_d1 type disk;
sql "alter system archive log current";
backup tag "fullback" format '/home/oracle/rmanbak/%d_%p_%s_%t' database include current controlfil;
backup spfile tag "spfile" format '/home/oracle/rmanbak/spfile_%t';
release channel disk_d1;
}
report obsolete;
delete obsolete;
yes
EOF
}

###############AUTO LOGMNR LOGFILE SCRIPT#######################
function recover {
       if [ "$STATUS" = "MOUNTED" ]
         then
rman target / nocatalog log='/home/oracle/script/rman.log' <list backup;
restore database;
recover database;
ALTER DATABASE OPEN;
EOF
       elif [ "$STATUS" = "ORACLE not available" ]
          then
rman target / nocatalog log='/home/oracle/script/rman.log' <startup mount;
restore database;
recover database;
ALTER DATABASE OPEN;
EOF
      elif [ "$STATUS" = "database not mounted" ]
          then
rman target / nocatalog log='/home/oracle/script/rman.log' <alter database mount;
restore database;
recover database;
ALTER DATABASE OPEN;
EOF

else
rman target / nocatalog log='/home/oracle/script/rman.log' <list backup;
shutdown immediate;
startup mount;
restore database;
recover database;
ALTER DATABASE OPEN;
EOF
fi
}

############################MONITOR LOGMNR LOGFILE##################################
function add_logfile_new {
         echo -en "PLEASE INPUT NEED LOGMNR ARCHIVELOG FILE AND DESTINATION: "
         read ARCHIVE_LOG
   if [ -f $ARCHIVE_LOG ]
         then
         echo "BUILD LOGMNR LIST: "
         sqlplus '/as sysdba' <         exec dbms_logmnr.add_logfile(logfilename=>'$ARCHIVE_LOG',options=>'dbms_logmnr.new');
EOF
   else
         echo "$ARCHIVE_LOG IS NOT EXISTS,LOGMRN NOT COMPLETE"

fi
}

function add_logfile {
         echo -en "PLEASE INPUT NEED LOGMNR ARCHIVELOG FILE AND DESTINATION: "
         read ARCHIVE_LOG
   if [ -f $ARCHIVE_LOG ]
         then
         echo "BUILD LOGMNR LIST: "
         sqlplus '/as sysdba' <         exec dbms_logmnr.add_logfile(logfilename=>'$ARCHIVE_LOG',options=>'dbms_logmnr.addfile');
EOF
   else
         echo "$ARCHIVE_LOG IS NOT EXISTS,LOGMRN NOT COMPLETE"

fi
}

function start_logmnr {
         sqlplus '/as sysdba' <         exec dbms_logmnr.start_logmnr (options => dbms_logmnr.dict_from_online_catalog);
EOF
}
function end_logmnr {
         sqlplus '/as sysdba' <         exec dbms_logmnr.end_logmnr;
EOF
}
function remove_logfile {
         echo -en "PLEASE INPUT NEED REMOVE LOGMNR ARCHIVELOG FILE: "
         read REMOVE_LOGFILE
         sqlplus '/as sysdba' <         exec dbms_logmnr.remove_logfile(logfilename => '$REMOVE_LOGFILE');
EOF
}
function monitor_logmnr {
PS3="Enter option: "
select choose in "dbms_lomnr.add_logfile.new" "dbms_logmnr.add_logfile" "dbms_logmnr.remove_logfile"
do
case $choose in
      "dbms_lomnr.add_logfile.new")
          add_logfile_new;;
      "dbms_logmnr.add_logfile")
          add_logfile;;
      "dbms_logmnr.remove_logfile")
          remove_logfile;;
      "dbms_logmnr.end_logmnr")
          end_logmnr;;
      "dbms_logmnr.start_logmnr")
          start_logmnr;;
      "exit logmnr" )
          exit ;;
esac
done
}
###########################MENU SCRIPT#############################################
PS3="Enter option: "
select menu in "rman back" "recover database" "setup logmnr" "logmnr log" "exit menu"
do
case $menu in
    "rman back")
      rman_bak ;;
    "recover database")
      recover ;;
    "setup logmnr")
      logmnr ;;
    "logmnr log")
      monitor_logmnr ;;
    "exit menu")
     break ;;
    *)
       clear
       echo "input you choose [1|2|3|4|5]:";;
esac
done