Wednesday, September 18, 2013

Oracle/Linux:RSYNC:Transfer archives from one server to another through rsync utility

RSYNC for Copy files

This is one of the finest method to copy archives from primary server to backup/standby server.

For this, rsync daemon running on primary server.

For Primary Server.

1.Configure rsync.conf

[oracle@Bhavu etc]$ cat /etc/rsyncd.conf

        path = /oracle/archive/bhavudb
        comment = For bhavu database archive log backup
        uid = root
        gid = root
2.Check rsync daemon running.

[oracle@Bhavu etc]$ ps -ef|grep rsync
oracle   13287  3699  0 14:44 pts/1    00:00:00 grep rsync
root     29022     1  0 Aug28 ?        00:00:00 /usr/bin/rsync --daemon

For Backup/Standby Server.

1.Create sh file for  rsync commands.filename is


pro_check=`ps -ef | grep rsync | grep -v grep | grep -v rsync-port`

if [[ -n $pro_check ]] ; then
                echo "Rsync Already Running...."
                echo "Starting Rsync...."
                rsync -avzpt primarydatabase_ip::archlog Bachkp_server_location

2.For more info read man rsync.

Tuesday, September 17, 2013

MYSQL:Tuning MYSQL Parameters

Tuning MYSQL Parameters

These are the list of variables need to tune to better performance of MYSQL Database.

This is in alphabetical orders.

1. have_query_cache     
Values YES
If Query Cache enabled

2. innodb_additional_mem_pool_size
As per MySQL documentation, this pool is used to store data dictionary information and other internal data structures. If InnoDB runs out of memory on this pool, it starts allocating from OS.
Most of the additional memory pool usage goes to tables in the data dictionary and connections. Normally, with increasing number of tables you may need to allocate more memory to this pool. But as modern OS'es have good and fast memory allocating functions, this variable does not hit performance.

3. innodb_buffer_pool_size 
Values 50-80% of Installed RAM
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, the time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds..

4.  innodb_log_buffer_size   
The MySQL InnoDB log buffer allows transactions to run without having to write the log to disk before the transactions commit. The size of this buffer is configured with the innodb_log_buffer_size variable.

5. innodb_log_file_size 
Values:25% of  innodb_buffer_pool_size 
Transaction Logs File size

6. innodb_thread_concurrency       
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.

7. join_buffer_size               
Values:Better to do at session level for large queries
The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary. There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it. 

8. key_buffer_size    
Index blocks for MyISAM tables are buffered and are shared by all threads.

9. log_slow_queries       
The slow query log consists of SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined. The default value of long_query_time is 10. Beginning with MySQL 5.1.21, the minimum is 0, and the value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored. Prior to MySQL 5.1.21, the minimum value is 1, and the value for this variable must be an integer. 

10. long_query_time         
If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The default value of long_query_time is 10. Beginning with MySQL 5.1.21, the minimum is 0, and the value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored. Prior to MySQL 5.1.21, the minimum value is 1, and the value for this variable must be an integer. 

11. max_connections     
The maximum permitted number of simultaneous client connections. By default, this is 100.Increasing this value increases the number of file descriptors that mysqld requires.

12. max_heap_table_size    
Values: 314572800   
This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. 

13. query_cache_limit 
This is the maximum size query (in bytes) that will be cached.

14. query_cache_min_res_unit        
The minimum size (in bytes) for blocks allocated by the query cache. 

15. query_cache_size         
The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. The permissible values are multiples of 1024; other values are rounded down to the nearest multiple. 

16. read_buffer_size        
Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB. 

17. read_rnd_buffer_size        
When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.  Setting the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries. 

18. sort_buffer_size    
Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization. See Section, “ORDER BY Optimization”, for example.
If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload.

19. table_cache      
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. 

20. thread_cache_size               
How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. 

21. wait_timeout 
MySQL would close any connection that was idle for more than  wait_timeout seconds.

MYSQL:Restart MYSQL services in solaris using shell script

MYSQL Shut and start service

This script is mainly developed for restarting mysql services in solaris machine.

1.Make one sh file and execute it.

/usr/local/mysql/.profile; sh /usr/local/mysql/

2.Here the contents of script.

/usr/local/mysql/bin/mysqladmin -u root -ppass shutdown
echo "STOPPING...."

sleep 4
echo "STARTING...."
/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --user=mysql &

We use the utility of mysqladmin

MYSQL:Scripts for MYSQL Databases hotbackuup

MYSQL Databases backup

MYSQL provide the utility for hot backup that can be done by mysqldump

1.For Backup of single database in shell script

/usr/local/mysql/bin/mysqldump  -uroot -ppass --log-error=/mysql2/mysqlbackup/db_name_`date +"%d-%m-%y"`.log DBNAME --routines >/mysql2/mysqlbackup/DBNAME_`date +"%d-%m-%y"`.sql

2.For All MYSQL Databases backup in one single file.

/usr/local/mysql/bin/mysqldump -u root -p --log-error=/mysql2/mysqlbackup/ALL_DB_ERROR_`date+"%d-%m-%y"`.log  --all-databases --routines >/mysql2/mysqlbackup/ALL_MYSQL_DB_`date +"%d-%m-%y"`.sql

Use mysqlimport to restore backup.

Monday, September 16, 2013

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

SMS Alert logs ORA- errors

This is something new in database administration part.Suppose DBA was out of office and got call from company manager to come to office.There is some problem in database.Generally DBA connect database and check connection possible or not then check any alert log entry.It is better if DBA and their company people got messages whenever any ORA- errors came in certain period of time.

For this,I found some solution to read alert log and send sms to respective people.
It is mandatory if we put database in archive log and where frequent archives generated.This is require because of the timestamp of archives used to get the start and end time of alertlogs entry.

This is all done through SHELL script and on linux machine.

Here it is the magical script can help to get the info of SMS by mail ans SMS.

This shell designed to read alerts of last 30 minutes.

#Go to bdump location
cd /oracle1/oradata/pindb/bdump/

#SMS duration
sms_first=`perl -e "use POSIX qw/strftime/; print strftime('%I:%M %p',localtime(time()-1800))"`
sms_last=`perl -e "use POSIX qw/strftime/; print strftime('%I:%M %p',localtime(time()))"`

#Current and previos time of alert
current_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()-1800))"`
future_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()))"`
#search the current time
first=`cat -n alert_pindb.log | grep -i "$current_time" | awk '{print $1}' | head -1`
#last=`cat -n test_kavya.log | grep -i "$future_time" | awk '{print $1}' | tail -1`
#1800(30 min)-60(1 min)
#Log the all activities of this shell
echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
echo "Current Time Is $future_time" >> /oracle1/oradata/pindb/ora_alert_activity.log
echo "Time To Check Is $current_time" >> /oracle1/oradata/pindb/ora_alert_activity.log

while [ -z "$first" ]
        current_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()+"$i"))"`
        first=`cat -n alert_pindb.log | grep -i "$current_time" | awk '{print $1}' | head -1`
        i=`expr $i + 60`;
        count_min=`expr $count_min + 1`;
        if [ "$count_min" -ge "31" ] ; then
            echo "Min is $count_min" >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo "We have reached an Impasse $count_min" >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo "Goodbye" >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
            echo "-------------------------------------------------------------------------" >> /oracle1/oradata/pindb/ora_alert_activity.log
        echo "In Loop Time Is $current_time" >> /oracle1/oradata/pindb/ora_alert_activity.log

#while [ -z "$last" ]
#        future_time=`perl -e "use POSIX qw/strftime/; print strftime('%a %b %e %H:%M.*%Y',localtime(time()+"$j"))"`
#        last=`cat -n test_kavya.log | grep -i "$future_time" | awk '{print $1}' | tail -1`
#        j=`expr $j+60`
#        #echo "Future In Loop $future_time"

#echo "Current $current_time"
#echo "Future $future_time"

echo "Found Timestamp On Line Number -->  $first" >> /oracle1/oradata/pindb/ora_alert_activity.log
#echo "Last $last"

list1=`cat alert_pindb.log | awk '{if(NR>='$first'){print $0}}' | grep -i ORA- | grep -v ORA-27091 | grep -v ORA-27069 | grep -v ORA-19883 | grep -v ORA-01115 | grep -v 17281 | grep -v ORA-01001`
list2=`cat alert_pindb.log | awk '{if(NR>='$first'){print $0}}' | grep -i ALTER | grep -v BACKUP | grep -v "ARCHIVE LOG"`

cd /oracle1/oradata/pindb/

echo $list1 > /oracle1/oradata/pindb/ora_alert.log
echo $list2 >> /oracle1/oradata/pindb/ora_alert.log

#Remove duplicate errors from list1,list2

cat /oracle1/oradata/pindb/ora_alert.log | sort | uniq > /oracle1/oradata/pindb/ora_alert1.log
cat /oracle1/oradata/pindb/ora_alert1.log > /oracle1/oradata/pindb/ora_alert.log
rm /oracle1/oradata/pindb/ora_alert1.log
#Re-assign values to list1,list2
list1=`cat /oracle1/oradata/pindb/ora_alert.log | grep -i ORA- `
list2=`cat alert_pindb.log | grep -i ALTER `

sms_list1=`echo $list1 | cut -c-160`
sms_list2=`echo $list2 | cut -c-160`
#Mail by mailx and SMS by perl script that developed individually or you can use other way to send sms
if [[ -n $list1 || -n $list2 ]] ; then
    mailx -s "DATABASE Alert", < /oracle1/oradata/pindb/ora_alert.log
    ssh XXX.XXX.XXX.XX -l itadmin "cd /itapps/apps/NodeMonitor/;perl \"$sms_first to $sms_last $sms_list1 $sms_list2\""
    echo "Mail & Message Sent for -- $sms_list1 -- $sms_list2" >> /oracle1/oradata/pindb/ora_alert_activity.log
    echo "I Can't Believe ORA- Error --> $list1 Is Blank" >> /oracle1/oradata/pindb/ora_alert_activity.log
    echo "I Can't Believe ALTER Error --> $list2 Is Blank" >> /oracle1/oradata/pindb/ora_alert_activity.log

echo "Goodbye" >> /oracle1/oradata/pindb/ora_alert_activity.log
echo " " >> /oracle1/oradata/pindb/ora_alert_activity.log
echo "-------------------------------------------------------------------------" >> /oracle1/oradata/pindb/ora_alert_activity.log