Simple Script to check alert.log file for errors in oracle

We have to check alert log files for errors, but its a time consuming process to read the file using less and check for errors. If you do a grep it will take the whole file and if the log rotation is done by month, you will get undesired output. Here is a script to check alert.log file in oracle for errors for today. We just have to copy this to a file and name it as alertcheck.sh and then from the OS prompt run
 "sh alertcheck.sh <alert log file with complete path>" without quotes.

######################################################################################################
#
#This script can be used to check any ORA- errors are there in the alertlog file for the present day #
#
######################################################################################################
#!/sbin/bash
if ! [ $1 ] ; then
echo Usage:
echo "sh alertcheck.sh 'alertlogFilename'"
else
 alrt=$1
 frm=$(grep -m 1 -n  "`date '+%a %b %d'`" $alrt |awk -F[:] '{print $1}')
 if ! [ $frm ]; then
  echo -e "\033[33mWe cannot find any entry for today."
  echo -e "Please check the file you have given is correct OR check with tail command manually\033[0m"
 else
  lst=$(wc -l $alrt | awk '{print $1}')
  lns=$(awk -v a="$lst" -v b="$frm" 'BEGIN{print a-b+1}')
  dt=$(date '+%a %b %d')
  echo -e "\033[34m..........Checking $lns lines writen today -$dt- out of $lst lines in the alert log.........\033[0m"
  err=$(tail -$lns $alrt | grep ORA-|tail -1|awk '{print $1}')
  if [ $err ]; then
    echo -e "\033[31mErrors found:\033[0m"
    echo ------------
    tail -$lns $alrt |  grep  ORA- |grep -n ORA-
    echo ""
    echo ""
    echo -e "\033[31mCheck the details of the errors below. \033[0m (Details means the surroundig lines of the error message only)"
    echo  "-------------------------------------"
    tail -$lns $alrt |  grep -A 5 -B 2 --color ORA-
  else
    echo -e "\033[32mNo Errors Found. Enjoy \033[33m:)\033[0m"
  fi
 fi
fi
#############################End of the script ##################################

If there are errors it will list you all errors first and then a few lines around the error message with a heading error details.

If no errors it will give you corresponding message

If the script can't find any entry for the date, it will give a message for it and will remind you to check the file.

Enjoy

Thank you,
ALI

DBMS_STATS consuming resource

Q: DBMS_STATS.GATHER_SCHEMA_STATS consuming much resources. How to optimize statistics collection using DBMS_STATS?

A: We DBAs used to collect object statistics to  improve performance of the database. But actually statistics collection will use much resources. You may see dbms_stats in top sql statements.

Collect statistics only in recovery window.

We do not need to collect statistics every day for all tables except those with a lot of changes. There is a way to collect statistics of only tables having outdated statistics. The method is to collect schema statistics only for stale statistics tables.

How does it works??

Our db will record all changes in a table in user_tab_modifications, When the modification reaches a threshold, the statistics will be marked as stale. And when you collect the statistics with stale option, It will collect statistics for only those tables with stale statistics.

How to do it?

Here is an example

SQL> BEGIN
2 DBMS_STATS.GATHER_SCHEMA_STATS (
3 ownname => 'SCOTT',
4 estimate_percent => 20,
5 block_sample => TRUE,
6 method_opt => 'FOR COLUMNS SIZE 10',
7 options => 'GATHER STALE',
8 cascade => TRUE);
9 END;
10 /

You can also give GATHER AUTO instead of GATHER STALE.
visit oracle documentation for more information

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461

dbms_space.object_growth_trend

Q. What does the following query do? It is one of the most expensive Queries.


insert into wri$_adv_objspace_trend_data select timepoint,
 space_usage, space_alloc, quality from
      table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
         NULL, 'FALSE', :5, 'FALSE'))

A:
It is used to collect data for space adviser for enterprise manager. You can disable the job if you don't use space adviser.
Execute dbms_scheduler.disable('sys.auto_space_advisor_job');

The real problem is solved if we go to 10.2.0.4.
And yes it has to do with bug 5376783.
All users on 10.2.0.2 have this performance problem !!!
















https://forums.oracle.com/forums/thread.jspa?threadID=1111378

Import and Export (links)



Thinking to do  a research on expdp and impdp, I have collected some links. I am sharing these with you hoping it would be helpful for you as well.

You will find billions of sites about the same, please share if you find some of them worth reading.

 I have just did a cross read, felt its worth reading and marked for further thorough read. 




This link may be much useful for us. But I felt it is a little bit difficult to understand.

Full oracle documentation:




see these issues, The same happened even for me.


A lot of  bugs related to this  topic are listed here,