Sunday, July 13, 2014

Oracle 11g:Daily export metadata of databases.

Oracle 11g:Daily export metadata of databases.





Sometime we almost forgot about to take backup of sources like PL/SQL.table DDL before updating.

We already have fully sync DR Database and we lost when we had done mistake on our work.Fully sync will also will make big trouble.

So its better to keep backup of metadata of whole database atleast twice a day.

some prerequisite to do setup automate it.

a) Create mount point with sufficient space for one month of db backup.its almost one gb require for one day.SO Mount point should have sufficient space like 30Gb.

b)DB Directory created in db.Full access of SYSTEM User to access directory in read, write mode.

1.Use below script.FULL_METADATA_EXPORT.sh

#!/usr/bin/ksh
######## For Daily Database FULL  metadata export####
set -a

export ORACLE_SID=DBEXPERT
export ORACLE_HOME=/oracle11g/11.2.0.4/products/apps/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH

ddy=`date +%a`
dt=`date +%Y%m%d`
echo $dt
cd /metadata/
expdp system/**** CONTENT=METADATA_ONLY COMPRESSION=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=metadata_$dt.dmp logfile=metadata_$dt.log full=y





status=`tail -10 /metadata/FULL_METADATA_EXPORT.log|grep "successfully"`
echo $status
len=${#status}
echo $len

if [[ $len -eq 0  ]];
then
uuencode /metadata/FULL_METADATA_EXPORT.log DBEXPERT_METADATA.log | mailx -s "DBEXPERT:Metadata export failed" dba@de.com
else
uuencode /metadata/FULL_METADATA_EXPORT.log DBEXPERT_METADATA.log | mailx -s "DBEXPERT:Metadata export successfully completed" dba@de.com
fi;  


Oracle AIX 11g:Automate archive destination notification in mail

Oracle AIX 11g:Automate archive destination notification in mail


This post to get the notification when filesystem archive destination reach at threshold values.



1.Use below script.automate_arc.sh


#!/usr/bin/ksh
######## For Automate disk untilization Mail####
set -a

export ORACLE_SID=DBEXPERT
export ORACLE_HOME=/oracle11g/11.2.0.4/products/apps/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH

cd /oracle11g/products/apps/11.2/scripts

sqlplus -s   / as sysdba <<EOF
set lines 150;
set pages 100;
spool /oracle11g/products/apps/11.2/scripts/loc.log
archive log list
spool off

EOF

arc_loc=`cat /oracle11g/products/apps/11.2/scripts/loc.log|grep "Archive destination"`
arc_dest=${arc_loc:21}


echo $arc_dest

df -gt  $arc_dest > res.log
capacity=`awk  -F" "  '{ print $5 }' res.log`
echo $capacity
perc=${capacity:5}
perc1=`echo $perc| tr -d '%'`
echo $perc1
if [[ $perc1 -gt 90 ]]; then
echo "Dear TEAM," >/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log

echo "Kindly log a call with Oracle DB Team."   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Call Type            SR  Call"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Service Type / Domain        Oracle DBA"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Classification            Automated Mail:DB Archive destionation almost 90% full"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Priority                High"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Engineer Name            By default Auto Alert Staff"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Call source            Email"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Title                Automated Mail:DB Archive destionation almost 90% full"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Call Description    "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Dear Team,"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Please do disk clean up on archive destination."   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Its is almost 90%."   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "**This is automated mail generated**"   >>/oracle11g/products/apps/11.2/scripts/ticket.log

mailx -s "Automated Mail:DB Archive destionation almost 90% full" dba@de.com dba2 de.com< /oracle11g/products/apps/11.2/scripts/ticket.log
fi

rm  /oracle11g/products/apps/11.2/scripts/loc.log
rm /oracle11g/products/apps/11.2/scripts/ticket.log
rm /oracle11g/products/apps/11.2/scripts/res.log

 2.Run the script.

bash automate_arc.sh

Oracle 11g:RMAN Backup Database in NOARCHIVELOG MODE:Mount state

Oracle 11g:RMAN Backup Database in NOARCHIVELOG MODE




Dear User,

Today we'll see how to take rman compressed backup when database in noarchivelog mode.

For that ,Database should be in mount state because database needs to be in consistent state for taking compressed full backup where no changes happened during backup.


1.Use below script.

vi RMAN_DBEXPERT_COMPRESSED_LO.sh

export ORATAB=/etc/oratab

export ORACLE_BASE=/orahome/11.2.0.4

export ORACLE_HOME=/orahome/11.2.0.4/product/11.2.0.4/

export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=dbexport

touch /rman/Offline/dbexpert_rman/bckstart.time

sleep 60

ddy=`date +%a`

dt=`date +%Y%m%d`

echo "Backup Started on `date`" > /rman/Offline/dbexpert_rman/${dt}_${ddy}_dbexpert_lvl0.log



rman target / trace /rman/Offline/dbexpert_rman/${dt}_${ddy}_dbexpert_lvl0.log  << EOF

RUN {

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk4 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk5 DEVICE TYPE DISK;

SHUTDOWN IMMEDIATE

STARTUP MOUNT


backup as compressed backupset incremental level 0 FORMAT '/rman/Offline/dbexpert_rman/%T_${ddy}_lvl0_%d_%U.%p' database maxsetsize 30G;

release channel disk1;

release channel disk2;

release channel disk3;

release channel disk4;

release channel disk5;

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;

backup format '/rman/Offline/dbexpert_rman/%T_${ddy}_lvl0_Controlfile_%s.ctl' current controlfile;

backup format '/rman/Offline/dbexpert_rman/%T_${ddy}_lvl0_Spfile_%s.ora' spfile;

report schema;

}

EOF

touch /rman/Offline/dbexpert_rman/ftp.time

exit;


2. Run in background.Check db is running.

nohup sh RMAN_DBEXPERT_COMPRESSED_LO.sh &