Database Experts

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 [ archlog ]         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 rsync.sh #!...

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 Description. If Query Cache enabled 2. innodb_additional_mem_pool_size Values:50M Description: 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 Description: The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables....

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/shutnstart.sh 2.Here the contents of shutnstart.sh 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 & echo "MYSQL STARTED.." We use the utility of mysqladmin

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.  

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. #!/bin/bash #Go to bdump location cd /oracle1/oradata/pindb/bdump/ #SMS d...