Database Experts

Backup archives on hourly basis To get full recovery of database,We backup archives on hourly basis. For this, Use this script for backup. Check comments for better understanding... #!/usr/bin/ksh # Go to backup location. cd /rmanbck/arch ddy=`date +%a` dt=`date +%Y%m%d` #last archive was backed up sequence lastredono=`ls -rt *arch*. log|tail -1|sed 's/^.*arch_//;s/[.]log//'|awk -F_ '{print $2}'` fno=`expr ${lastredono} \- 2` #From Archive destination, Find the max sequence generations tno=`find /oracle/arch -name "*.arch" -print| sort|tail -1|sed 's@/oracle/arch/@@'|awk -F_ '{print $1}'` FNAME=${dt}_${ddy}_arch_${fno}_${tno} echo $FNAME rman target / trace /rmanbck/arch/$FNAME.log << EOF run { ALLOCATE CHANNEL disk3 DEVICE TYPE DISK; backup as compressed backupset FORMAT '/rmanbck/arch/%T_${ddy}_arch_${fno}_${tno}_%U.set' archivelog from sequence ${fno} unt...

SQL Tuning Advisor Dear User, Read More about SQL Tuning Advisor from Oracle site http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#CHDJDFGE ***Tuning SQL Queries by Oracle SQL Tuning Advisor*** A)SQL Query tuning by SQL_TEXT. 1.Get the Query info from AWR Report like  Top CPU Consuming Queries or SQL Queries by Elapsed time. SQL>select SQL_TEXT,sql_id from v$sqlarea where sql_id='&sql_id'; 2.Get bind variable details from V$SQL_BIND_CAPTURE or DBA_HIST_SQLBIND. SQL>select address, hash_value,name,last_captured,DATATYPE_STRING,VALUE_STRING from  V$SQL_BIND_CAPTURE where sql_id= '&sql_id';  order by last_captured; SQL>select sql_id,name,last_captured,DATATYPE_STRING,VALUE_STRING from DBA_HIST_SQLBIND where sql_id= '&sql_id'   and trunc(last_captured)=trunc(sysdate); 3.Prepare Query and add bind variable values(VALUE_STRING) into SQL Queries.Values sho...

Oracle 8i:Standby Database for Disaster recovery solution. Here is the simple steps for creating standby database in oracle 8i. This is testing purpose,So do the all steps from creating primary to real time sync of standby database. 1.Create/Configure Primary Database. a)Create primary database pfile. db_name = "primary" instance_name = primary service_names = primary control_files = ("/oracle/primary/stdby1.ctl","/oracle/primary/stdby2.ctl") control_file_record_keep_time=30 open_cursors = 100 max_enabled_roles = 30 db_block_buffers = 40000 db_block_lru_latches=4 cursor_space_for_time=true session_cached_cursors=40 shared_pool_size = 71680000 shared_pool_reserved_size=9216000 large_pool_size = 10485760 java_pool_size = 10485760 log_checkpoint_interval = 1048576 processes = 300 log_buffer = 163840 l og_archive_start = true log_archive_dest_1 = "location=/oracle/primary/arc/"...

Automate Automatic Workload Repository Report in a mail Every time go to database and collect reports is old fashioned way.Now, Lets do some automated work for oracle workload reports. This is required when number of databases and also we all lazy to avoid see reports everyday.So it is better to make one schedule who will send us yesterdays all working day awr report in one single mail where just need to see if anything problematic in off -office hours. Simple and easy way creating one single procedure and schedule by scheduler. lets do some coding. 1.Create directory for parameters file for awrpt input parameters. --Report Type, --Num days --Begin snapshot id --End snapshot id --Reporrt Name This is the mandatory report parameter needs to generate awr report. 2.Create stored procedure. --Change directory name  --Get host_command from one of blog post. --set report path CREATE OR REPLACE PROCEDURE SYSTEM.USP_AUTOMATE...

Oracle 10g Upgrade 10.2.0.1.0 to 10.2.0.4.0 into solaris. 1.Download file p6810189_10204_Solaris-64.zip for upgrade patch. 2.Copy file to one location and unzip. mkdir -p /oracle1/10204 unzip p6810189_10204_Solaris-64.zip 3.Create one response.rsp file for input parameter. Sample file, Copyright (c) 1999, 2004 Oracle. All rights reserved.          ## ##                                                                ## ## Specify values for the variables listed below to customize     ## ## your installation.                                             ## ##                        ...

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....