export ORACLE_HOME=/star/app/oracle/product/11.1.0/db_1
DTE=`date +"%m%d%y"`
DIR=/work/BKP_DB
echo [.. starting Backup of NAVADEMO "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=NAVADEMO
dmpfname='expdp_DEMO_'$DTE'.dmp'
logfname='expdp_DEMO_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVADEMO directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
#/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@starDEMO directory=exp_job dumpfile=$dmpfname schemas=tiger logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
echo [.. starting Backup of NAVADEV "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=NAVADEV
dmpfname='expdp_DEV_'$DTE'.dmp'
logfname='expdp_DEV_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVADEV directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
echo [.. starting Backup of NAVATST "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=NAVATST
dmpfname='expdp_TST_'$DTE'.dmp'
logfname='expdp_TST_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVATST directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
echo [..starting Backup of NAVATMP "`date`"] >> $DIR/Bkp_all.txt
export ORACLE_SID=starTMP
dmpfname='expdp_TMP_'$DTE'.dmp'
logfname='expdp_TMP_'$DTE'.log'
/star/app/oracle/product/11.1.0/db_1/bin/expdp system/star@NAVATMP directory=exp_job dumpfile=$dmpfname full=y logfile=$logfname
echo [.zipping dumpfile "`date`"] >> $DIR/Bkp_all.txt
gzip $DIR/$dmpfname >> $DIR/Bkp_all.txt
Scheduler in Linux for DB backups of NAVADEV,NAVADEMO,NAVATST & NAVATMP
scheduled as per the below scripts:
0 1 * * 1,4 /work/BKP_DB/BKP_SCRIPT_DB.sh
So this backup are scheduled to happen at every Monday and Thursday at 1 am.
Hence NAVADEV,NAVADEMO,NAVATST & NAVATMP are backuped every Monday and Thursday.
Calling procedure to write tracing of default trace file in a file:
---------------------------------------------------------------------
/*
declare
v_sid number;
v_serial number;
v_file varchar2(2000);
begin
-- Call the procedure
select sid,serial# into v_sid,v_serial from v$session where osuser='501200I430' and status='ACTIVE';
sys.dbms_system.set_sql_trace_in_session(sid => v_sid,
serial# => v_serial,
sql_trace => TRUE);
SELECT value into v_file
FROM v$diag_info
WHERE name = 'Default Trace File';
dbms_output.put_line(v_file);
end;
*/
Tablespace critical message when it becomes 85% occupied:
----------------------------------------------------------
#!/bin/ksh
export PATH=/star/app/oracle/product/11.1.0/db_1/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/star/bin
export ORACLE_BASE=/star/app/oracle
export LD_LIBRARY_PATH=/star/app/oracle/product/11.1.0/db_1/lib:/lib:/usr/lib
export LD_LIBRARY_PATH=/ora11g/app/oracle/product/11.1.0.7/lib:/usr/lib:/usr/ccs/bin:/usr/dt/lib
export ORACLE_HOME=/star/app/oracle/product/11.1.0/db_1
MAILLIST=`cat /home/star/scripts/email.lst`
LOGFILE=/tmp/chk_ts_$1.log
echo $1
CHKUP=`ps -ef | grep pmon | grep $1 | wc -l`
if [ "${CHKUP}" -eq 1 ]
then
echo "The Pmon Process is running on host"
else
echo " ****************************************************************" >> $LOGFILE
echo " " `date` " Pmon Proces is not found" >> $LOGFILE
echo " ****************************************************************" >> $LOGFILE
exit 0
fi
export ORACLE_SID=$1
sqlplus -s "/ as sysdba"
set trimspool on
set trimout on
set verify off
set feedback off
column TABLESPACE_NAME format a18
column "%ocup" format a6
set lines 200
spool /tmp/chk_ts_$1.spl
SELECT total.tablespace_name tablespace_name,
ROUND (tot / 1024 / 1024) total,
ROUND ((tot - tot_l) / 1024 / 1024) occupied,
ROUND (tot_l / 1024 / 1024) remain,
ROUND (max_l / 1024 / 1024) max_extent,
ROUND ((tot - tot_l) * 100 / tot) || '%' AS "%ocup"
FROM (SELECT tablespace_name, SUM (BYTES) tot_l, MAX (BYTES) max_l
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT d.tablespace_name,
f.bytes_free + f.bytes_used - NVL (p.bytes_used, 0) tot_l,
0 max_l
FROM SYS.v_\$temp_space_header f,
dba_temp_files d,
SYS.v_\$temp_extent_pool p
WHERE f.tablespace_name(+) = d.tablespace_name AND f.file_id(+) = d.file_id
AND p.file_id(+) = d.file_id) libre,
(SELECT tablespace_name, SUM (BYTES) tot
FROM dba_data_files
GROUP BY tablespace_name
UNION
(SELECT tablespace_name, SUM (BYTES) tot
FROM dba_temp_files
GROUP BY tablespace_name)) total,
dba_tablespaces dba_t
WHERE total.tablespace_name = libre.tablespace_name(+)
AND total.tablespace_name = dba_t.tablespace_name
AND ROUND (tot_l / 1024 / 1024) < 1024
AND ROUND ((tot - tot_l) * 100 / tot) > 85
AND dba_t.tablespace_name NOT IN ('RBS')
ORDER BY ROUND ((tot - tot_l) * 100 / tot) DESC;
spo off
EOF
if [ -s /tmp/chk_ts_$1.spl ]; then
cat /tmp/chk_ts_$1.spl | mailx -s "Critical : ($1 on insiscdev02.sony.com.sg) Missing free space on tablespace of the database $1 -- Immediate Action" $MAILLIST
echo "---------------------------------------------------------------------------" >> $LOGFILE
echo `date` >> $LOGFILE
cat /tmp/chk_ts_$1.spl >> $LOGFILE
echo "---------------------------------------------------------------------------" >> $LOGFILE
rm -fr /tmp/chk_ts_$1.spl
fi
STATISTICS COLLECTED FOR NAVAREP SCHEMA:
Step 1:Execute the DBMS_STATS.gather_schema_stats package with the below options.
BEGIN
DBMS_STATS.gather_schema_stats (
ownname => 'NAVATXN',
estimate_percent => null,
block_sample => false,
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => null,
granularity => 'ALL',
cascade => true,
options => 'GATHER');
END;
/
Step 2:Check the statistics gathered with the help of DBA_TABLES dictionary view.
SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP';
SQL> SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP'; 2
STATISTICS COLLECTED FOR NAVAREP SCHEMA:
Step 1:Execute the DBMS_STATS.gather_schema_stats package with the below options.
BEGIN
DBMS_STATS.gather_schema_stats (
ownname => 'NAVATXN',
estimate_percent => null,
block_sample => false,
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => null,
granularity => 'ALL',
cascade => true,
options => 'GATHER');
END;
/
Step 2:Check the statistics gathered with the help of DBA_TABLES dictionary view.
SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP';
SQL> SELECT table_name, num_rows, blocks, avg_row_len
FROM DBA_tables where OWNER='NAVAREP'; 2
