Oracle MySQL Linux

configure: error: Package requirements (gtk+-2.0 >= 2.0.0) were not met

2011-11-01T02:46:00.000-07:00

I'm trying to install plascolin but when I type ./configure in the directory into which I unpacked the package, I get:

configure: error: Package requirements (gtk+-2.0 >= 2.0.0) were not met.

Now, as I understand it, GTK is installed as part Ubuntu. I don't see anything specifically called "gtk+-2.0" in synaptic so I'm not sure what to do here.

Dev packages are not installed by default, you can install it with:

sudo apt-get install libgtk2.0-dev

Adding extra Repositories on Ubuntu

2011-10-30T03:27:00.001-07:00

Repositories on Ubuntu are the locations that you can download software from. As a general rule, the default repositories don’t contain the right locations for most software packages that you’ll want to install. You will want to open up the /etc/apt/sources.list file, find and uncomment the following lines

deb http://us.archive.ubuntu.com/ubuntu dapper universe main restricted universe

deb http://security.ubuntu.com/ubuntu dapper-security universe

Note that if you are using a different version than Dapper Drake (6.06), you will probably see a different name there, something like breezy or edgy.

Once you are done adding the repositories, you’ll need to run this command:

sudo apt-get update

(Thanks to Gregg in the comments for catching that)

SSH Public Key Based Authentication – Howto

2011-10-05T02:21:00.001-07:00

How to do it

Generate the authentication key
On the client machine, the user must generate a public / private keys pair that will identify himself on the servers. One can choose to protect it with password or not.

Letting it with no password, means that anyone with access to the key files (eg. root on the client’s machine) will have the same level of access of the user and no password will be asked when the client tries to connect to the servers.

Protecting the keys with password means that every time the user tries to connect to a server using those keys , the password for decrypting it will be asked. This is surely more secure, since anyone who can read the key files, will only see an encrypted version.

To generate the key pair do:

fabio@morpheus:~$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/fabio/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/fabio/.ssh/id_rsa.
Your public key has been saved in /home/fabio/.ssh/id_rsa.pub.
The key fingerprint is:
44:3e:ef:58:94:15:52:c2:88:ca:ab:21:43:53:3d:42 fabio@morpheus
fabio@morpheus:~$
Just let the default file (~/.ssh/id_rsa). Enter the password at choice, as explained before. If you need to change the password or add one, do:

fabio@morpheus:~$ ssh-keygen -p
Enter file in which the key is (/home/fabio/.ssh/id_rsa):
Key has comment '/home/fabio/.ssh/id_rsa'
Enter new passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved with the new passphrase.
fabio@morpheus:~$
In this case, a new password was added. Note that this operation does not change the public / private key pair. It only changes its encryption.

Install the public key on the servers
Once the public key is installed on the server, access will be granted with no password question. SSH usually comes with an utility called ssh-copy-id that simply adds the contents of client’s ~/.ssh/id_rsa.pub to the server’s ~/.ssh/authorized_keys:

fabio@morpheus:~$ ssh-copy-id -i .ssh/id_rsa.pub ornellas@apanela.com
15
ornellas@apanela.com's password:
Now try logging into the machine, with "ssh 'ornellas@apanela.com'", and check in:

.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

fabio@morpheus:~$
Note that at this point password access is needed. This procedure can be done by any other way you wish. For example, the server’s administrator himself can add the public key to allow a user access, instead of giving him a password.

SQL Server, Error 18542: Login failed for user ''. The user is not associated with a trusted SQL Server Connection

2011-09-08T19:49:00.000-07:00

How to reset MSSQL Server sa password

Depending on what mode SQL Server was installed (Windows Authentication or Mixed Mode), you may have to do a few things. If you installed in mixed mode, you can log in as sa and add the administrator user as a Login under the Security section. Make sure you assign sysadmin role to the user, which can be found under Server Roles.

If Windows Authentication mode was chosen during install then there are a couple of things you can do, but I think the easiest is just to recreate the user sadegh, login into SQL Server as that user, and follow the previous step to add the administrator user as a login.

UPDATE: If all else fails, you can run SQL Server in Single User Mode, which allows a single connection to the server, and enable/change the sa password. To do this you can:

Open the command prompt (Right-Click on and select "Run As Administrator")
From the command prompt type net stop MSSQLSERVER
Next type net start MSSQLSERVER /m
Open SQL Server Management Studio. Do not login, cancel the login dialog.
From the file Menu select New->Database engine query, and login (Make sure you use the host name and not localhost).
Execute the query ALTER LOGIN sa WITH PASSWORD = ''; to reset the password (if the sa is not enabled then type ALTER LOGIN sa ENABLE to do so)
Login with the sa user and add the Administrator user.
Tested on Windows 7 Ultimate with SQL Server 2008 Standard. Hope this helps.

Installing Software in GNU/Linux

2011-04-11T05:48:00.000-07:00

Introduction
For the benefit of people new to Linux, I have written a generic explanation of howto install software in Linux. Note that some software may have specific installation procedures, this HOWTO is not a substitute for reading the official installation documentation

This HOWTO covers the following topics:

Command-line process:
Compiling and Installing software from source
Installing RPM's using the Redhat Package Manager

If you have just installed GNU/Linux and would like to know how to install software on your new OS, I would recommend you read the section specifically for your GNU/Linux Distribution.

Note: for new users, it is generally easiest/best to install any software using the default package tool that is included with your particular distribution.

Do not worry if you don't understand or remember everything written here straight away, book mark this page and use it as a reference page later on.

Compiling and Installing software from source

NOTE - Installing from source code is the most difficult method for obtaining software on Linux and in most cases is not necessary. Most popular software can be found and installed quite easily using your distribution's package manager (see sections on "apt-get" and "yum"). Installing from source is recommended only for experienced Linux users and/or those who aren't afraid to break something for the purpose of learning.

Some software is distributed in "Source form". This means you download a file containing all the source code for the application you want to install, unpack it, and compile it on your system. Compiling is the process of turning the source code into an executable binary. The common myth and newbie assumption is that this is very hard todo, or it is only for programmers. Wrong. It is a fairly straight forward process, and you will find that a lot of software you install will need to be built from source.

Typically applications you must compile from source will come as a ".tar.gz", ".tar.bz2", or ".zip" file.

You'll probably want to operate from inside your home directory. If your user is (for example) username, your home directory will be /home/username/. For the rest of this section we will assume you have downloaded your zip file to /home/username/src. If you do not have a src directory, you can create it with the following "mkdir" (make directory) command:

Code:
mkdir /home/username/src/
So, we have our source package in /home/username/src/.

Change to the /home/username/src/ directory with the "cd" (change directory) command like so:

Code:
cd /home/username/src/
Use the "ls" (list directory contents) command, to see the file is present:

Code:
ls

We now need to unzip the zipped file, this is done differently depending on the file extension.

for files ending in .tar.gz, use:
Code:
tar -zxvf
(replacing with the name of the file).

for files ending in .tar.bz2, use:
Code:
tar -jxvf
for files ending in .zip, use:
Code:
unzip
You should now have a new directory, containing all of the source files. To confirm it exists, and to get its name, use the "ls" command again.
Code:
ls
we now need to go into the new directory, so use the cd command:
Code:
cd

This is where things will differ. Some packages will have an INSTALL or README file which will contain installation instructions. use "ls" to see if the software has an install or readme file. If it does have one, you can use the "more" command to read it, like so:
Code:
more INSTALL
Generally, the final 3 stages are as follows:
- Configure the installation
- Compile the software
- Install the binaries

The pre-installation configuration is done by executing ./configure:
Code:
./configure
This will perform some requirements testing on your system, and create a "Makefile" which will explain to the "make" utility how the software should be compiled. If you receive any error messages during this stage, you may wish to search the forums to see if they have been found and resolved by someone else already, if not, feel free to post a question on the forums - Please include all of the output including any error messages, and some details about your system - what distro you are using, what are you trying to install etc etc

The next stage is to compile the software, this is done using "make". When you run "make" it will read the instructions in the Makefile and build the application binaries.
Code:
make

The final stage is to install these binaries, ie, copy them to a more permanent location. Typically only the "root" user can do this, so you will need to swich to the root user with the "su" command:
Code:
su
Once you are root, install the binaries using the "make" command, followed by "install", like so:
Code:
make install
That is it! Check the user documentation of the software you installed for details of how to run the application.

Remember that if you have any problems, please post in the most relevant section of the forums. - When posting, remember to include as much info as possible, including all output and error messages.

Installing RPM's using the Redhat Package Manager
Redhat RPM's offer a flexable and easy method to install new software. Software installed from an RPM package differs from compiling from source in a few ways, but the most important one of all is the software is already compiled for you. Essentially all you are doing is extracting the pre-built binaries and copying them to their pre-selected destination. RPM's are files that have a ".rpm" extenstion. The good point about RPM's is installation of new software, and maintaining the software currently installed is much easier than doing so for individual packages compiled from source. The downside to RPM's is that you dont have as much choice about where software is installed on your system, how it is compiled, and how it is configured.

Using the RPM system is fairly straight forward. To install a package, you can use the following command:
Code:
rpm -i
When using rpm, you must be logged in as the root user. The "-i" flag in the above command means "install".

Un installing a package is just as easy:
Code:
rpm -e
The "-e" switch used here means "erase" (un install).
Note that is different from used when installing.

For example, if you are installing an application called "mysoftware", you may use a command like "rpm -i mysoftware-1.0.2-i386.rpm" to install "mysoftware", when removing we dont follow the filename for installation, but rather the name of the software itself.

For further uses of RPM, please use "rpm --help" and "man rpm". Also see this page, which has some fairly useful information.

If you need to find & download the RPM file for a piece of software, I recommend using
RPM Find and RPM Pbone Search Note that not all applications are available as RPM's, in these cases you will need to compile the software from source. (see above).
__________________

Script to kill process

2011-04-09T20:22:00.001-07:00

kill -9 `ps -aef | grep memcache | grep -v grep | awk '{print $2}'`

HOWTO Install dig on Windows 7

2011-03-23T08:50:00.001-07:00

by Dominic on March 3rd, 2011
The short version:

1.Create a folder for the dig application (I used E:\apps\dig)
2.Download the latest version of BIND from the Internet Systems Consortium website.
3.Open the .zip archive and extract dig.* and *.dll into your application folder
4.Add the application folder to your path
5.All of these steps might need a little more explanation for an inexperienced person. But then if you’re an inexperienced person, why do you need dig? Happy to expand on any of the above steps if you wish – just add a comment here.

How to Mail server (Postfix/Dovecot/SMTP auth with sasl/squirrelmail/clamav/spamassassin/amavis/dkim/Postfixadmin & virtual users using mysql)

2011-03-09T05:55:00.000-08:00

How to Mail Server

Postfix/Dovecot/SMTP auth with sasl/squirrelmail/clamav/spamassassin/amavis/dkim/Postfixadmin & virtual users using mysql

Prerequisites:

CentOS 5.3
Intermediate knowledge of Linux
apache
mysql
saslauthd
Centos/Base repository

Purpose:

I have created this how to produce all these integrated howtos from one place because I have to make hard struggle to find out all these things & off course to configure with integration. I have followed how to from different sites & this how to is also derived from those I have adjusted it according to my requirements you can also do it

Installing & configuring Postfix SMTP server with mysql support

The version of Postfix that comes with Red Hat Enterprise Linux 5 or CentOS 5 does not support MySQL lookups. You can build your own custom Postfix RPM or get a prebuilt RPM from the CentOS Plus repository. You can use the CentOS repository in RHEL since CentOS is binary compatible with RHEL. This article describes how to install Postfix with MySQL lookup table support from the CentOS Plus repository.

login to your server open terminal & type the following command

style="font-family:";">

vi /etc/yum.repos.d/CentOS-Base.repo

edit the lines below

[updates]

exclude=postfix

[centosplus]

enabled=1

includepkgs=postfix

If you have already installed postfix first remove it

yum remove postfix

Now install the version of Postfix found in the CentOS Plus repository

yum install postfix

To verify MySQL support, type following command and check if mysql is found in the output

postconf -m

[root@cu config]# postconf -m

btree

cidr

environ

hash

ldap

mysql

nis

pcre

pgsql

proxy

regexp

static

unix

Switch to Postfix from Sendmail

By default, Sendmail is the active SMTP server. Here’s how to change it.

alternatives --config mta

now choose postfix by entering number

Configuring Postfix

vi /etc/postfix/main.cf

Find the following keys and change its values as follows

inet_interfaces = all

mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain

home_mailbox = Maildir/

Note:Make sure that all mail_spool_directory lines are commented out. Otherwise, it will override the setting in the home_mailbox line above.

Now restart postfix or start if it first time

service postfix restart

chkconfig postfix on

Checking Postfix/ SMTP

you can verify this by doing telnet on smtp port

hint:If you encounter any problems, check the log file at

/var/log/maillog

Dovecot POP3/IMAP Server Setup

First install dovecot

yum install dovecot

After that

vi /etc/dovecot.conf

Find the following keys and change its values as follows

protocols = pop3 pop3s imap imaps

mail_location = maildir:~/Maildir/

pop3_uidl_format = %08Xu%08Xv

imap_client_workarounds = delay-newmail outlook-idle netscape-eoh

pop3_client_workarounds = outlook-no-nuls oe-ns-eoh

Now start service

service dovecot start

chkconfig dovecot on

Checking Dovecot

you can verify this by doing telnet on pop/imap port

Hint:If you encounter any problems, check the log file at

Postfix SMTP Authentication and Dovecot SASL

First dovecot

Edit the file “/etc/dovecot.conf” and make sure your auth default section has the lines below

vi /etc/dovecot.conf

auth default {

socket listen {

client {

path = /var/spool/postfix/private/auth

mode = 0660

user = postfix

group = postfix

}

}

mechanisms = plain login

}

Now Postfix/SMTP

mynetworks = 127.0.0.0/8

smtpd_sasl_type = dovecot

smtpd_sasl_path = private/auth

smtpd_sasl_auth_enable = yes

smtpd_recipient_restrictions = permit_mynetworks,

permit_sasl_authenticated, reject_unauth_destination

broken_sasl_auth_clients = yes

Edit /etc/postfix/main.cf, find the keys below and change its values as follows or add it at the bottom of the file if the key (the word before the = sign) cannot be found

Hint:The first line says trust only localhost, meaning only localhost can send email outside the network (relay). The last line is there to support old clients like Microsoft Outlook Express 4.0 and Microsoft Exchange 5.0 just in case someone is still using it

Now restart postfix & dovecot

service postfix restart

service dovecot restart

now you can verify both services by telnet if you face any problem check above mentioned log file

Note: make sure that “saslauthd” is started

SquirrelMail Webmail Setup

yum install squirrelmail

you can now access it

http://servername/webmail

Configuring Squirrelmail

cd /usr/share/squirrelmail/config/

./conf.pl

2

1

Enter your domain name

abc.com

After entring dns name now press

3

smtp

After saving information quit from menue

This is the minimum configuration for squirrel webmail they are lot of other option like organization setting, logo change now it your to-do to explore this further

Note:Now at this point your minimum mail server is configured with system username & password u can logon by

http://servername/webmail

but prior to this start apache

service httpd start

chkconfig httpd on

How to Install Postfix Admin

Install the Postfix Admin requirements using the command below

yum install php-mysql php-imap

Download the latest stable version of Postfix Admin in .tar.gz format here. Assuming you got the file postfixadmin-2.2.1.1.tar.gz, type in the commands below to extract and to put it into its proper directory

tar -xvzf postfixadmin-2.2.1.1.tar.gz

mv postfixadmin-2.2.1.1 /usr/share/postfixadmin

Create the Postfix Admin MySQL database using the commands below. The default root password of MySQL is a blank password

Start mysql service if it is not strated

service mysqld strat

chkconfig mysql on

mysql -u root -p -e "CREATE DATABASE postfix;"

mysql -u root -p -e "CREATE USER postfix IDENTIFIED BY 'password';"

mysql -u root -p -e "GRANT ALL PRIVILEGES ON postfix.* TO postfix;"

Edit the file /usr/share/postfixadmin/config.inc.php and update the following lines below

$CONF['configured'] = true;

$CONF['postfix_admin_url'] = '/postfixadmin';

$CONF['database_type'] = 'mysqli';

$CONF['database_host'] = 'localhost';

$CONF['database_user'] = 'postfix';

$CONF['database_password'] = 'your_password';

$CONF['database_name'] = 'postfix';

$CONF['domain_path'] = 'YES';

$CONF['domain_in_mailbox'] = 'NO';

$CONF['encrypt'] = 'cleartext';

Configuring the Postfix Admin Web Application

Create the file /etc/httpd/conf.d/postfixadmin.conf containing the line below

vi /etc/httpd/conf.d/postfixadmin.conf

Alias /postfixadmin /usr/share/postfixadmin

Restart httpd

service httpd restart

Go to the Postfix Admin setup page a

http://servername/postfixadmin/setup.php

Ccreate a new admin user. The admin user name should be a valid email address. Delete setup.php when you are done

Go to the Postfix Admin login page at http://servername/postfixadmin/ and login using your newly created admin account

Note: You must create mysql root passowrd

Postfix/dovecot virtual users using mysql

Now we configure Postfix and Dovecot to handle multiple domains stored in MySQL. Postfix Admin is used to manage the domains, mailboxes and aliases in MySQL.

creating the Virtual Mail User Account
useradd vmail

passwd vmail

vi /etc/passwd

just change after the home directory path

vmail:x:501:501::/home/vmail:/bin/nologin

Create the file “/etc/postfix/mysql-domains.cf”

vi /etc/postfix/mysql-domains.cf

Add following lines

host = localhost

user = postfix

password = your_password

dbname = postfix

table = domain

select_field = domain

where_field = domain

additional_conditions = and backupmx = '0' and active = '1'

Save&exit

Create the file “/etc/postfix/mysql-users.cf “

vi /etc/postfix/mysql-users.cf

Add following lines

host = localhost

user = postfix

password = your_password

dbname = postfix

table = mailbox

select_field = maildir

where_field = username

result_format = %sMaildir/

Save&exit

Create the file “/etc/postfix/mysql-aliases.cf”

vi /etc/postfix/mysql-aliases.cf

Add following lines

host = localhost

user = postfix

password = your_password

dbname = postfix

table = alias

select_field = goto

where_field = address

Edit the postfix configuration file

vi /etc/postfix/main.cf

mydestination = $myhostname, localhost.$mydomain, localhost

and add the lines below

virtual_mailbox_domains = mysql:/etc/postfix/mysql-domains.cf

virtual_mailbox_maps = mysql:/etc/postfix/mysql-users.cf

virtual_alias_maps = mysql:/etc/postfix/mysql-aliases.cf

virtual_mailbox_base = /home/vmail

virtual_uid_maps = static:501

virtual_gid_maps = static:501

Note:virtual_mailbox_base, virtual_uid_maps and virtual_gid_maps should contain the home directory, user id and group id of vmail respectively

Note:Make sure $mydomain in mydestination has been removed, otherwise the lookup will not work and you will get a “User unknown in local recipient table” error in some cases myhost to.

Configuring Dovecot
Create the file “/etc/dovecot-mysql.conf”

vi /etc/dovecot-mysql.conf

driver = mysql

connect = host=localhost dbname=postfix user=postfix password=password

default_pass_scheme = PLAIN

password_query = SELECT password FROM mailbox WHERE username = '%u'

svae&exit

Edit the file “/etc/dovecot.conf”

vi /etc/dovecot.conf

passdb sql {

args = /etc/dovecot-mysql.conf

}

userdb static {

args = uid=501 gid=501 home=/home/vmail/%d/%n

}

Note:uid, gid and home should contain the user id, group id and home directory respectively of the vmail user account.

Note:If you encounter any problems, check the log file at /var/log/maillog

Install the SquirrelMail Change Password Plugin

cd /usr/share/squirrelmail/plugins

Connect to your SquirrelMail server and note down its version.

Download the Change Passwd and Compatibility plugins

wget http://squirrelmail.org/countdl.php?fileurl=http%3A%2F%2Fwww.squirrelmail.org%2Fplugins%2Fcompatibility-2.0.14-1.0.tar.gz

wget http://www.squirrelmail.org/countdl.php?fileurl=http%3A%2F%2Fwww.squirrelmail.org%2Fplugins%2Fchange_sqlpass-3.3-1.2.tar.gz

tar xvfz compatibility-2.0.14-1.0.tar.gz

tar xvfz change_sqlpass-3.3-1.2.tar.gz

Extract the downloaded files and put them into the /usr/share/squirrelmail/plugins directory.

If your version of SquirrelMail is not 1.4.13 and higher or 1.5.1 and higher, you need to patch your SquirrelMail source

cd compatibility/

Compatibility patch by typing in the command below

patch -p0 <>

cd ..

cd change_sqlpass

cp config.php.sample config.php

vi vi config.php

Note:Change the following required parameters your parameters may differ but if you have follow this howto from top to till this setting will work, you only have to give postfix database password

$csp_dsn = 'mysql://postfix:your_password@localhost/postfix';

$lookup_password_query = 'SELECT count(*) FROM mailbox WHERE username = "%1" AND password = "%5"';

$password_update_queries = array(

'UPDATE mailbox SET password = "%5" WHERE username = "%1"',

$force_change_password_check_query = '';

$password_encryption = 'NONE';

$csp_salt_query = '';

save&exit

They are other options you can also play it

Note:if you are experiencing blank screen problem after logging in squirellmail the mostlikly your data base setting is not correct & mak double check on config .php in changpass module directory

/usr/share/squirrelmail/config/conf.pl

you'll see the following menu. Navigate through it as indicated:

SquirrelMail Configuration Read config.php(1.4.0)
---------------------------------------------------------
MainMenu--
1.Organization Preferences
2.Server Settings
3. Folder Defaults
4. General Options
5. Themes
6. Address Books
7. Message of the Day(MOTD)
8. Plugins
9. Database
10. Languages

Command >> ← 8

SquirrelMail Configuration : Read: config.php (1.4.0)

---------------------------------------------------------

Plugins

Installed Plugins

1. delete_move_next

2. squirrelspell

3. newmail

4. filters

Available Plugins:

5. translate

6. compatibility

7. administrator

8. bug_report

9. abook_take

10. listcommands

11. message_details

12. fortune

13. mail_fetch

14. calendar

15. spamcop

16. change_sqlpass

17. sent_subfolders

18. info

R Return to Main Menu

C Turn color on

S Save data

Q Quit

Command >>

6

SquirrelMail Configuration : Read: config.php (1.4.0)

---------------------------------------------------------

Plugins

Installed Plugins

1. delete_move_next

2. squirrelspell

3. newmail

4. filters

5. compatibility

Available Plugins:

6. translate

7. administrator

8. bug_report

9. abook_take

10. listcommands

11. message_details

12. fortune

13. mail_fetch

14. calendar

15. spamcop

16. change_sqlpass

17. sent_subfolders

18. info

R Return to Main Menu

C Turn color on

S Save data

Q Quit

Command >>

16

SquirrelMail Configuration : Read: config.php (1.4.0)

---------------------------------------------------------

Plugins

Installed Plugins

1. delete_move_next

2. squirrelspell

3. newmail

4. filters

5. compatibility

6. change_sqlpass

Available Plugins:

7. translate

8. administrator

9. bug_report

10. abook_take

11. listcommands

12. message_details

13. fortune

14. mail_fetch

15. calendar

16. spamcop

17. sent_subfolders

18. info

R Return to Main Menu

C Turn color on

S Save data

Q Quit

Command >>

s

Command >>

q

clamav/spamassassin/amavis

Adding DAG repo
To install amavis and clamav, we need to add dag repositories since the packages mentioned are not available on the centos base repositories.To do this, we have to create a file named “/etc/yum.repos.d/dag.repo “

vi /etc/yum.repos.d/dag.repo

Add the following lines

[dag]
name=Dag-RHEL-Yum
baseurl=http://dag.linux.iastate.edu/dag/redhat/el$releasever/en/$basearch/dag
http://www.mirrorservice.org/sites/apt.sw.be/redhat/el$releasever/en/$basearch/dag
http://mirrors.ircam.fr/pub/dag/redhat/el$releasever/en/$basearch/dag
http://apt.sw.be/redhat/el$releasever/en/$basearch/dag
enabled=1
gpgcheck=1

save & exit

After doing that, we need to import dag rpm-gpg-key with the command below:

rpm --import http://dag.wieers.com/packages/RPM-GPG-KEY.dag.txt

if you are unable to imort just wget these keys & import localy

Now we are ready to install, do it as root with the command below.

yum install amavisd-new clamav clamd

yum install perl-Archive-Tar.noarch

touch /var/log/amavis.log && chown amavis.amavis /var/log/amavis.log

Edit/etc/amavis.conf, then set the $domain and $hostname to our own value and then uncomment the following:

vi /etc/amavis.conf
$forward_method = 'smtp:127.0.0.1:10025'; # where to forward checked mail
$notify_method = $forward_method; # where to submit notifications

Disable virus quarantines: change the value of $QUARANTINEDIR the line:

$QUARANTINEDIR = undef;

If we dont want our system to notify those senders that we did not accept their email attachment or we found out that their email has virus, uncomment the ff: below.

# $final_virus_destiny = D_DISCARD;
# $final_banned_destiny = D_BOUNCE;
# $final_spam_destiny = D_BOUNCE;
# $final_bad_header_destiny = D_PASS;

Next, find and uncomment the Clam AV section. Comment out all virus scanners you are not using:

### http://www.clamav.net/
['Clam Antivirus-clamd',
\&ask_daemon, ["CONTSCAN { }\n", "/var/run/clamav/clamd.ctl"],
qr/\bOK$/, qr/\bFOUND$/,
qr/^.*?: (?!Infected Archive)(.*) FOUND$/ ],

Make sure amavis is not currently running, do the command:

/etc/init.d/amavis stop

then do the command

amavis debug

( if you found error, then troubleshoot)

Start amavis now:

/etc/init.d/amavis start

chkconfig amavis on

Try to

telnet 127.0.0.1 10024

You should get the response below showing that amavis has been running.

Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
220 [127.0.0.1] ESMTP amavisd-new service ready

Now we have to configure Postfix to pipe incoming email through amavisd-new:

postconf -e 'content_filter = amavis:[127.0.0.1]:10024'
postconf -e 'receive_override_options = no_address_mappings

Next, configure Postfix to use Amavisd-new, which will now function as an SMTP proxy server. Add this to the end of /etc/postfix/master.cf:

vi /etc/postfix/master.cf
amavis unix - - - - 2 smtp

-o smtp_data_done_timeout=1200

-o smtp_send_xforward_command=yes

127.0.0.1:10025 inet n - - - - smtpd

-o content_filter=

-o local_recipient_maps=

-o relay_recipient_maps=

-o smtpd_restriction_classes=

-o smtpd_client_restrictions=

-o smtpd_helo_restrictions=

-o smtpd_sender_restrictions=

-o smtpd_recipient_restrictions=permit_mynetworks,reject

-o mynetworks=127.0.0.0/8

-o strict_rfc821_envelopes=yes

-o receive_override_options=no_unknown_recipient_checks,no_header_body_checks

-o smtpd_bind_address=127.0.0.1

Then add this line to /etc/postfix/main.cf:

content_filter = smtp-amavis:[127.0.0.1]:10024

Then restart postfix by the command below:

/etc/init.d/postfix restart

Now open /etc/clamav.conf and /etc/amavisd.conf, and make sure that amavisd.conf references the LocalSocket file:

vi /etc/clamav/clamav.conf
LocalSocket /var/run/clamav/clamd.ctl

Save&exit

vi /etc/amavis/amavisd.conf
### http://www.clamav.net/
['Clam Antivirus-clamd',
\&ask_daemon, ["CONTSCAN { }\n", "/var/run/clamav/clamd.ctl"],
qr/\bOK$/, qr/\bFOUND$/,
qr/^.*?: (?!Infected Archive)(.*) FOUND$/ ],

Note:It might happen that /var/run/clamav/clamd.ctl does not exist and when you will try to start clamd, it won't start since it did'nt find that file or does not have the right to create or use the file so we can do the command:

touch /var/run/clamav/clamd.ctl

and then

chown amavis.amavis /var/run/clamav/clamd.ctl

Now we need to change some lines on clamd.conf and freshclam.conf..see below:

vi /etc/clamd.conf

User amavis #orig user is clamav

Also change some default settings below

# Path to a local socket file the daemon will listen on.
# Default: disabled
LocalSocket /var/run/clamav/clamd.sock
#note, the default is that LocaSocket was commented so have to uncomment it
#Below, the default is that TCPSocket has no comment so you have to comment it
#because if you forget to comment that line, when you restart Clamav, the error would be
#Starting Clam AntiVirus Daemon: ERROR: You can select one mode only (local/TCP).
# TCP port address.
# Default: disabled
#TCPSocket 3310

vi /etc/freshclam
DatabaseOwner amavis #orig is clamav

Now try to chown some clamav folder to amavis.amavis

LogFile /var/log/clamav/clamav.log
PidFile /var/run/clamav/clamd.pid
DatabaseDirectory /var/clamav/

Finally, hunt down any files belonging to Clam AV in /etc/logrotate.d/.

For example:

/etc/logrotate.d/clamav
/etc/logrotate.d/freshclam

The filenames may vary. Inside each file, find this line:

Change it to:

create 640 clamav amavis

Save the changes and reload clamav and that's it! You can now check the maillog of by the command:

tail -f /var/log/maillog

You can also try to check if CLAMV anti-virus really functioning. Try to create a file in you name test.com ( do this inside your linux box ) paste the line of characters below without the quotes.

"X5O!P%@AP[4\PZX54(P^)7CC)7}$EICAR-STANDARD-ANTIVIRUS-TEST-FILE!$H+H* "

This is the eicar anti-virus test file. Email this file as attachement to the user on that box or from that box to outside and you will see messages something like below, saying that it did detected this file as a sort of infected file.

log file below:

Feb 20 10:24:22 pacland amavis[3786]: (03786-06) Blocked INFECTED
(Eicar-Test-Signature), <> -> , Message-ID:
<20060220022421.ga25880@test.net>, mail_id: qvSy9GYSCarX,
Hits: -, 614 ms
Feb 20 10:24:22 pacland postfix/smtp[25890]: C564C4A665:
to=, relay=127.0.0.1[127.0.0.1], delay=1,
status=sent (250 2.7.1 Ok, discarded, id=03786-06 - VIRUS: Eicar-Test-Signature)

If you find this in your log files, it means that the installation has been functioning well basically.

Installing and configuring spamassassin

On Centos, "yum install spamassassin or check if might be already installed. After the installation,

rpm -q spamassassin

if not

yum intsall spamassassin

Edit “/etc/amavis.conf”

vi /etc/amavis.conf

Uncomment and set

$final_spam_destiny = D_PASS; # (defaults to D_REJECT)

D_PASS allow the message to be delivered to the recipient and let the recipient decide what to do.
D_DISCARD will drop the message at smtp level, avoiding extra usage of bandwidth. Set the following:

$sa_tag_level_deflt = -999; # add spam info headers if at, or above that level
$sa_tag2_level_deflt = 5.0; # add 'spam detected' headers at that level
$sa_kill_level_deflt = -999; # triggers spam evasive actions
# string to prepend to Subject header field when message exceeds tag2 level
$sa_spam_subject_tag = '***SPAM*** ';

Then chown the spamassassin folders and files, as root, do the command.

chown -R amavis:amavis /usr/share/spamassassin

Now, to test your system if it really filters spam, at least basically, try to email a certain account on the host that has been installed with spamassassin with a message that contains "penis enlargement, viagra" and I'm sure it will be block as spam.

Then restart amavis-new

/etc/init.d/amavisd restart

Check this out -/spamassassin autoconfigurator/-

Installing & configuring DKIM

Installation
Install the rpm, ignore dependencies as csh is a dependency but it does not affect dkim-milter; it's only required for some sample scripts that are shipped with the rpm.

rpm -Uvh http://www.c-corp.net/linux/centos/5/general/RPMS/i386/dkim-milter-2.2.1-1.i386.rpm --nodeps
mkdir /etc/dkim-milter
chown dkim-milt.dkim-milt /etc/dkim-milter
chmod 700 /etc/dkim-milter
chgrp postfix /var/run/dkim-milter
chmod 770 /var/run/dkim-milter

Generate The Keys
Download this script that you can use to easily generate the keys for signing the mail:

./dkim-genkey.sh -d

Replace with the domain name you will be signing mail for. This will create two files default.txt and default.private, default.txt is the line you need to add to your zone file - a sample is below

default._domainkey IN TXT "v=DKIM1; g=*; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDG81CNNVOlWwfhENOZEnJKNlikTB3Dnb5kUC8/zvht/S8SQnx+YgZ/KG7KOus0By8cIDDvwn3ElVRVQ6Jhz/HcvPU5DXCAC5owLBf/gX5tvAnjF1vSL8ZBetxquVHyJQpMFH3VW37m/mxPTGmDL+zJVW+CKpUcI8BJD03iW2l1CwIDAQAB"

; ----- DKIM default for topdog-software.com

Default.private contains your private key. Move this file into /etc/dkim-milter and rename it

_default.key.pem:

mv default.private /etc/dkim-milter/_default.key.pem

Edit the file /etc/sysconfig/dkim-milter and set the variables:

USER="dkim-milt"

PORT=local:/var/run/dkim-milter/dkim.sock

SIGNING_DOMAIN=""

SELECTOR_NAME="default"

KEYFILE="/etc/dkim-milter/${SIGNING_DOMAIN}_${SELECTOR_NAME}.key.pem"

SIGNER=yes

VERIFIER=yes

CANON=simple

SIGALG=rsa-sha1

REJECTION="bad=r,dns=t,int=t,no=a,miss=r"

EXTRA_ARGS="-h -l -D"

Init Script Fix
Install my modified init script as the one that is supplied with the rpm has a bug.

wget http://www.topdog-software.com/files/dkim-milter -O /etc/init.d/dkim-milter
chkconfig --level 345 dkim-milter on
service dkim-milter start

chkconfig dkim-milter on

Configure Postfix
Add this to the postfix configuration file /etc/postfix/main.cf:

smtpd_milters = unix:/var/run/dkim-milter/dkim.sock

non_smtpd_milters = unix:/var/run/dkim-milter/dkim.sock

Append to the existing milters if you have other milters already configured.

Start dkim-milter and restart Postfix:

service dkim-milter start

service postfix restart

Note: now check mail it is working or not in case of problem check /var/log/maillog.

Possible Error

if your postfix fail to start due to error “/var/run/dkim-milter/dkim.sock” not found then

vi /etc/sysconfig/dkim-milter

uncomment the following lines

PORT="inet:10035@localhost"

Testing
Send a message to autorespond+dkim@dk.elandsys.com; the system will return a response to let you know if DKIM is working.

References

http://www.howtoforge.com

http://www.linuxmail.info/

http://squirrelmail.org/

http://aioshin.blogspot.com/2006/02/quick-how-to-on-installing-amavis.html

http://www.topdog.za.net/howtos

Free xml sitemap generator

Opensource load balancing Software

2011-03-08T21:18:00.000-08:00

Posted by Nikesh Jauhari
Linux Virtual Server
The Linux Virtual Server Project is a project to cluster many real servers together into a highly available, high-performance virtual server. The LVS load balancer handles connections from clients and passes them on the the real servers (so-called Layer 4 switching) and can virtualize almost any TCP or UDP service, like HTTP, HTTPS, NNTP, FTP, DNS, ssh, POP3, IMAP4, SMTP, etc. It is fully transparent to the client accessing the virtual service.
Homepage: http://www.LinuxVirtualServer.org/

BalanceNG
BalanceNG is a modern software IP load balancing solution. It is small, fast, and easy to use and setup. It offers session persistence, different distribution methods (Round Robin, Random, Weighted Random, Least Session, Least Bandwidth, Hash, Agent, and Randomized Agent) and a customizable UDP health check agent in source code. It supports VRRP to set up high availability configurations on multiple nodes. It supports SNMP, integrating the BALANCENG-MIB with Net-SNMPD. It implements a very fast in-memory IP-to-location database, allowing powerful location-based server load-balancing.
Homepage:http://www.inlab.de/balanceng/

HAproxy
HAproxy is a high-performance and highly-robust TCP and HTTP load balancer which provides cookie-based persistence, content-based switching, advanced traffic regulation with surge protection, automatic failover, run-time regex-based header control, Web-based reporting, advanced logging to help trouble-shooting buggy applications and/or networks, and a few other features. Its own event-driven state machine achieves 20,000 hits per second and surpasses GigaEthernet on modern hardware, even with tens of thousands of simultaneous connections.
Homepage:http://haproxy.1wt.eu/

Pen
Pen is a load balancer for "simple" TCP-based protocols such as HTTP or SMTP. It allows several servers to appear as one to the outside. It automatically detects servers that are down and distributes clients among the available servers. This gives high availability and scalable performance.
Homepage:http://siag.nu/pen/

Crossroads Load Balancer
Crossroads is a daemon running in user space, and features extensive configurability, polling of back ends using wake up calls, status reporting, many algorithms to select the 'right' back end for a request (and user-defined algorithms for very special cases), and much more. Crossroads is service-independent: it is usable for any TCP service, such as HTTP(S), SSH, SMTP, and database connections. In the case of HTTP balancing, Crossroads can provide session stickiness for back end processes that need sessions, but aren't session-aware of other back ends. Crossroads can be run as a stand-alone daemon or via inetd.
Homepage:http://crossroads.e-tunity.com/

balance
Balance is a simple but powerful generic TCP proxy with round-robin load balancing and failover mechanisms. Its behavior can be controlled at runtime using a simple command line syntax. Balance supports IPv6 on the listening side, which makes it a very useful tool for IPv6 migration of IPv4 only services and servers.
Homepage:http://www.inlab.de/balance.html

Distributor load balancer
Distributor is a software TCP load balancer. Like other load balancers, it accepts connections and distributes them to an array of back end servers. It is compatible with any standard TCP protocol (HTTP, LDAP, IMAP, etc.) and is also IPv6 compatible. It has many unique and advanced features and a high-performance architecture.
Homepage:http://distributor.sourceforge.net/

Pure Load Balancer
Pure Load Balancer is a high-performance software load balancer for the HTTP and SMTP protocols. It uses an asynchronous non-forking/non-blocking model, and provides fail-over abilities. When a backend server goes down, it automatically removes it from the server pool, and tries to bring it back to life later. Pure Load Balancer has full IPv6 support and works on OpenBSD, NetBSD, FreeBSD and Linux.
Homepage:http://plb.sunsite.dk/

Load Balancer Project
The Load Balancer Project is a tool that allows you to balance requests using clusters of servers. The goal is to achieve high availability load balancing with a simple configuration for the load balancer and the network topology. It leaves the servers untouched so the configuration only resides on the load balancer, and it allows you to manage any type of service via a plugin model design and a transparent proxy feature.
Homepage:http://www.jmcresearch.com/projects/loadbalancer/

mod_athena
mod_athena is an Apache-based application load balancer for large systems. It allows the HTTP server to act as a load balancer either internally to Apache's own mod_proxy (for reverse proxying), or externally to machines querying it. Arbitrary statistics are sent to the engine via a simple GET plus query-string interface, from which it will then make decisions based on chosen algorithms.
Homepage:http://ath.sourceforge.net/

udpbalancer
Udpbalancer is a reverse proxy that sorts UDP requests from your clients to your servers. It may operate in round-robin, volume balance, and load balance modes.
Homepage:http://dev.acts.hu/udpbalancer/

MultiLoad
MultiLoad is a load balancer that redirects HTTP requests to pre-defined servers/locations. It gives the provider a way to balance the traffic and hides the real download location. It allows you to manage different version of each download. It is also a load balancing server extension. You can distribute files on some servers so that a downloaded file can be loaded form different servers. These servers can have different priorities to control the active traffic.
Homepage:http://download.laukien.com

Creating, detaching, re-attaching, and fixing a suspect database

2011-02-15T01:55:00.001-08:00

Creating, detaching, re-attaching, and fixing a suspect database

This is one I've been trying to get to since I started blogging a couple of years ago: how to re-attach a detached suspect database. This is a pretty common scenario I see on the forums - a database goes suspect so the DBA tries to detach/attach, which fails. I wrote a demo for my corruption session at TechEd this year that shows how to create a suspect database with hex editor, then detaches it and shows how to re-attach and fix it. It's going to be a long blog post, but I've got everything wrapped up before we fly out to the UK tomorrow so I've got a bit of spare time.

Creating a Suspect Database

First off I'm going to create a simple database to use, called DemoSuspect with a table and some random data.

USE MASTER
GO
CREATE DATABASE DemoSuspect
GO
USE DemoSuspect;
GO

CREATE TABLE Employees (FirstName VARCHAR (20), LastName VARCHAR (20), YearlyBonus INT);
GO
INSERT INTO Employees VALUES ('Paul', 'Randal', 10000);
INSERT INTO Employees VALUES ('Kimberly', 'Tripp', 10000);
GO

Now I'll perform an update in an explicit transaction and force it to be written out to disk with a CHECKPOINT. I've accidentally deleted Kimberly's bonus!

-- Simulate an in-flight transaction
BEGIN TRAN;
UPDATE Employees SET YearlyBonus = 0 WHERE LastName = 'Tripp';
GO

CHECKPOINT;
GO

Then in another window, I'll simulate a crash using:

SHUTDOWN WITH NOWAIT;
GO

Now that SQL Server is shutdown, I'm going to simulate an I/O failure that corrupts the log file. I'm going to use a hex editor to do this - my editor of choice is the immensely popular and useful XVI32, written by Christian Maas. I opened the log file, filled the first section with zeroes, and then saved it again. See the screenshot below.

When I startup SQL Server again, it will try to run recovery on the DemoSuspect database and fail. This will put the database into the SUSPECT state.

So I restarted SQL Server, let's try getting in to the DemoSuspect database.

USE DemoSuspect;
GO

Msg 945, Level 14, State 2, Line 1
Database 'DemoSuspect' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Now let's check the database status:

SELECT DATABASEPROPERTYEX ('DemoSuspect', 'STATUS') AS 'Status';
GO
Status
--------
SUSPECT

Now at this point, the correct procedure is to restore from backups. If there are no backups available, then the next best thing is to get the database into EMERGENCY mode and extract as much data as possible, or run emergency-mode repair. However, I'm going to try the detach/attach route instead.

Detaching the Database

I'll try detaching the database using sp_detach_db:

EXEC sp_detach_db 'DemoSuspect';
GO

Msg 947, Level 16, State 1, Line 1
Error while closing database 'DemoSuspect'. Check for previous additional errors and retry the operation.

Hmm - did it work or didn't it?

SELECT * FROM sys.databases WHERE NAME = 'DemoSuspect';
GO

And there are no results, so the detach must have succeeded.

Note: from SQL Server 2008 you cannot detach a suspect database unless you jump through a few more hoops (e.g. put the database offline, then copy off the files, then drop the database).

Re-attaching the Database

Let's try the obvious sp_attach_db:

EXEC sp_attach_db @dbname = N'DemoSuspect',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf';
GO

Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf' is not a valid database file header. The PageAudit property is incorrect.

Hmm. How about using the ATTACH_REBUILD_LOG option on CREATE DATABASE? That should create a new log file for me:

CREATE DATABASE DemoSuspect ON
(NAME = DemoSuspect, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf')
FOR ATTACH_REBUILD_LOG;
GO

Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf' is not a valid database file header. The PageAudit property is incorrect.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DemoSuspect'. CREATE DATABASE is aborted.

Hmm. The database knows that there was an active transaction. Using the ATTACH_REBUILD_LOG command only works if the database was cleanly shut down and the log is missing. What about if I actually remove the log file and try that - maybe I could fool it? I took a copy of the data and log file, and then deleted the original log file (this is getting more and more dodgy...). Let's try again:

CREATE DATABASE DemoSuspect ON
(NAME = DemoSuspect, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf')
FOR ATTACH_REBUILD_LOG;
GO

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect_LOG.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DemoSuspect'. CREATE DATABASE is aborted.

Well, that was a long-shot but SQL Server's smarter.

Basically the problem is that the database wasn't cleanly shutdown, which means that recovery HAS to run and complete before the database can be attached again. Given that our log file is corrupt, that's impossible.

So, never detach a suspect database.

The only way to get the database back into SQL Server is to use a hack. I'm going to create a new dummy database with the exact same file layout and as close as possible to the file sizes of the detached database. Then I'm going to shutdown SQL Server, swap in the corrupt database files, and re-start SQL Server. If all goes well, the corrupt, suspect database will be attached again.

The one major downside of this is that if the SQL Server instance doesn't have instant initialization enabled (see How to tell if you have instant initialization enabled?), then creating the dummy database could take a long time if the data files are very big. This means that your application is offline while the files are created and zero'd out.

So, I've already got a copy of the corrupt database files, so now I need to delete the data file too. Before doing this you want to make absolutely sure you've got multiple copies of the corrupt database files... just in case. After deleting the data file, I can create my dummy database.

CREATE DATABASE DemoSuspect
GO

If you forget to delete the existing corrupt files first, you'll get the following error:

Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Line 1
Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoSuspect.mdf' because it already exists. Change the file path or the file name, and retry the operation.

Ok - delete the data file and try again. Now I need to check the dummy database's files are there (at this point I'm totally paranoid), shutdown SQL Server, delete the dummy databases' files (this just seems wrong!), and swap in the corrupt files. I took another copy of the corrupt files before swapping them back in, just in case something goes wrong.

After restarting SQL Server, I can check the database state:

SELECT DATABASEPROPERTYEX ('DemoSuspect', 'STATUS') AS 'Status';
GO

Status
--------
SUSPECT

Woo-hoo - I'm back to having a suspect database attached again - after having to shutdown the server and mess about deleting and copying files around. Not good. Now I can actually fix it.

Fixing the Database

If you don't have any backups, then the only way to get into the database is to use EMERGENCY mode. This lets you into the database but you need to be aware that recovery has not completed so the contents of the database are transactionally (and possibly structurally) inconsistent. I'm going to choose to repair the database using emergency-mode repair. See CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort for a detailed description of this tool.

ALTER DATABASE DemoSuspect SET EMERGENCY;
GO
ALTER DATABASE DemoSuspect SET SINGLE_USER;
GO
DBCC CHECKDB (DemoSuspect, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoSuspect_log.LDF' is not a valid database file header. The PageAudit property is incorrect.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoSuspect_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
The Service Broker in database "DemoSuspect" will be disabled because the Service Broker GUID in the database (9E879BFC-B742-4A69-AB14-4D6BD6F99E02) does not match the one in sys.databases (B4568D23-7018-40CF-B189-9C29DE697C09).
Warning: The log for database 'DemoSuspect' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

First off it tries to do the regular ATTACH_REBUILD_LOG. When that fails, DBCC CHECKDB takes over and forces the log to be rebuilt, after trying to force as much of the damaged log to be processed for recovery as it can. It then runs a full repair, in case there's anything corrupt in the database - in this case there isn't so there are no corruption messages in the output.

Notice the line about the Service Broker GUID being wrong. I had to use the hack method to get the database attached again, but when I created the dummy database, it created a Service Broker GUID for the DemoSuspect database in master.sys.databases. When I swapped in the corrupt database, it has a different GUID - so now I can't use Service Broker! And all because I detached the suspect database instead of repairing it.

So what's the state of the data?

USE DemoSuspect;
GO

SELECT * FROM Employees;
GO

FirstName LastName YearlyBonus
---------- --------- ------------
Paul Randal 10000
Kimberly Tripp 0

Kimberly doesn't get a bonus this year - she won't be happy! This is contrived and flippant, of course, but it illustrates the point that after doing an emergency-mode repair, transactions that were active at the time the log was damaged will not get a chance to roll-back, most likely. In this case, I know what was going on when the crash occured, but what about on a busy OLTP system with hundreds or thousands of active transactions? What state will the data be in?

Summary

Yes, you can recover from a detached suspect database, but it's not pretty and you have to be very careful. The best course of action is always to have a comprehensive backup strategy that allows you to restore as quickly as possible. If you do have a suspect database and no backups, use EMERGENCY mode to access and/or repair the database. Hopefully this article will help people that find themselves in these situations.

How to prevent duplicate entry error in MySQL

2011-01-09T21:02:00.000-08:00

Errors should be ignored because they are not necessary at all. In MySQL especially for replication, the general architecture is to have a host for write and other hosts for read. As a result, response time of the whole system should be more scalable. If something goes wrong at the master node where all write operations must be performed, the error may propagate to other replications. One of common error oftenly occurs during replication is duplicate entry. It is possible to prevent this error which may lead the replication to stop.

This problem was reported for so long and it it supposed to be fixed already. However, I still got them sometimes. The solution is to specify slave-skip-erros at client side as follows.

[mysqld]
slave-skip-errors = 1062
1062 means to duplicate entry error. For more information, read below links

manually create a standby database

2011-01-03T20:55:00.000-08:00

By Nicolas de Fontenay 4 Comments
Categories: High Availability
Tags: availability, High Availability, Oracle, standby, synchronization
This post talks only about manually creating a standby database.

Configuring Dataguard CLI (DGMGRL) and dataguard through the Grid Control will be discussed later on in other subjects.

Since this blog is under oracle 10g, I will consider the databases to use spfiles.

To ease the difficulty, I will also consider that the folder structure on the primary database and the standby database are the same.

I will also try to describe the expected error when something is not done properly

The creation of a standby database consists of 3 parts:

1) Creation of standby spfile, controlfile, password file

2) Connectivity configuration (Listener, tnsnames)

3) Standby restoration, recovery and management

——

Preliminary:

The databases’ instance names are: THTEST10

The primary database unique name is: THTEST10

The standby database unique name is: THTESTDG

Creation of standby spfile, controlfile, password file

1) Create the SPFILE for production and standby

On the production database:

Connect to sqlplus:

C:\> set oracle_sid=thtest10

C:\>sqlplus “/ as sysdba”

SQL>create pfile=’initthtest10.ora’ from spfile=’spfilethtest10.ora’;

From the file generated, initthtest10.ora, we are going to modify a bunch of parameters before converting it into spfile again.

If they don’t exist, add:

*.db_name=’THTEST10′
*.db_unique_name=’THTEST10′

*.log_archive_config=’dg_config=(THTEST10,THTESTDG)’

*.log_archive_dest_2=’SERVICE=thtestdg.world VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=THTE
STDG’

*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’

*.standby_archive_dest=’g:\oradata\THTEST10\arch’
*.standby_file_management=’AUTO’
*.dg_broker_start=TRUE
*.fal_client=’THTEST10.WORLD’
*.fal_server=’THTESTDG.WORLD’

modify the archiving parameter:

*.log_archive_dest_1=’location=g:\oradata\THTEST10\arch valid_for=(ONLINE_LOGFILE,ALL_ROLES) db_unique_name=THTEST10′

The point is to add the optional values VALID_FOR and DB_UNIQUE_NAME. If they are missing, they might result into errors at the DGMGRL configuration time.

Make sure the host is specified in the parameter below:

*.local_listener=’(ADDRESS=(PROTOCOL=tcp)(HOST=testserver)(PORT=1910))’

Most of these parameters can be modified using the command when using a spfile.

SQL>alter system set = ‘value’ scope=both;

But some parameters, such as db_unique_name will require to modify the parameter file before hand, hence a shutdown of the DB is required.

It’s not possible to modify it while the DB is on.

On the standby database

Copy the modified init file and modify it again to fit standby requirements.

*.db_unique_name=’THTESTDG’
*.local_listener=’(ADDRESS=(PROTOCOL=tcp)(HOST=standbyserver)(PORT=1911))’
*.log_archive_config=’dg_config=(THTEST10,THTESTDG)’
*.LOG_ARCHIVE_DEST_1=’location=g:\oradata\THTEST10\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=THTESTDG’
*.log_archive_dest_2=’SERVICE=thtest10.world VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=THTEST10′
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.dg_broker_start=TRUE
*.standby_file_management=’AUTO’
*.fal_server=’THTEST10.WORLD’
*.fal_client=’THTESTDG.WORLD’

Finally, don’t forget to convert the pfile to spfile again and make sure both your primary and standby starts with a spfile.

2) Create the control file for the standby

On the production server, type the following command:

C:\>sqlplus connect / as sysdba
SQL>alter database create standby controlfile as ‘d:\somewhere\control01.ctl’;

Move the control file to the location specified in your spfile. If there’s more than one, just make a copy of the freshly generated one, and rename it.

2) Create the password file for the standby

Simply copy it from production to standby.

Connectivity configuration (Listener, tnsnames)

Notice the green bold service names in the pfile in the step above.

These will be specified in the tnsnames.ora and through it, our databases will communicate.

It is important to have both databases specified on both servers.

The tnsnames.ora file should look like this:

THTEST10.WORLD =

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = primaryserver)(PORT=1521)
)
(CONNECT_DATA =
(SERVICE_NAME = THTEST10.WORLD)
)
)
THTESTDG.WORLD =

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = standbyserver)(PORT=1521)
)
(CONNECT_DATA =
(SERVICE_NAME = THTEST10.WORLD)
(UR = A)
)
)
Notice the value (UR=A) in the CONNECT_DATA of the tns entry THTESTDG.WORLD.

This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.

Don’t forget to create the listeners on both servers.

Standby restoration, recovery and management

Finally the interesting part.

Before starting to work on the standby, take a hot backup of your primary database:

C:\>set oracle_sid=thtest10

C:\>RMAN target /

RMAN> backup database plus archive log not backed up 1 times;

Copy the backups to the standby. The folder structure for your backups should be the same as well to simplify.

On the standby server

Create an oracle service for your standby database with the oradim command

C:\>oradim -NEW -SID THTEST10 -startmode manual -shutmode n

Since the spfile is already located in ORACLE_HOME\database it doesn’t need to be mentioned.
Same for the password file which should be here already from the 1st step.

Restore and recover your database using RMAN

C:\ set oracle_sid=thtest10

RMAN target /

RMAN> restore database;

… (a log of things happens here)

RMAN> recover database;

… (archive logs are applied)

Now that the database is recovered, we will need to copy the redo logs from production to the standby.
That is required because this standby database will not be open with the resetlogs command therefore the redo logs will not be generated.
So, go back to production and copy the redo logs.
Make sure they are not accessed in order to copy it. To check the status of your redo log groups, connect as sysdba and use the following query:

SQL> select group#, status from v$log;

GROUP# STATUS
————————–
1 INACTIVE
2 INACTIVE
3 CURRENT

You can copy the groups with status INACTIVE. The group with a status CURRENT can’t be copied right now but we will force switch to another group:

SQL>alter system switch logfile;

SQL> select group#, status from v$log;

GROUP# STATUS
————————–
1 CURRENT
2 INACTIVE
3 INACTIVE

Now it’s ok to copy group# 3 to the standby server

Switching to automatic recovery

Previously, to recover the database with RMAN we had to set the database to mount mode. We must now shutdown and go into mount standby mode. That’s how it works:

SQL> startup nomount;

SQL>alter database mount standby database;

SQL>recover automatic standby database;

You should get ORA- messages: ORA-00279 ORA-00289 ORA-00280 ORA-0027. This is a normal behavior. The OS message saying that it’s not finding a missing archive log is normal too since at a certain point it will reach the latest archive and the next one is not generated yet. Result: it fails but we like it.

Cancel the operation by simply typing cancel. When the media recovery is canceled, you should get an error message as follow:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘D:\ORACLE\ORADATA\OE\SYSTEM01.DBF’
ORA-01112: media recovery not started

This is normal too. There will be no open resetlogs anyway.

We can now move to:

Switching the standby into managed recovery mode

SQL>recover managed standby database;

This command will not return the hand to you.

Any further sql query will have to be run from another command line.

Testing your newly created standby database

Go to the primary database and type the command:

SQL>alter system switch logfile;

This command will generate a new archive log file.

You should see it’s generation in the alert log.

You should be able to see the standby database fetching it and applying it to the standby database.

You should also see the newly moved archive log in the archive folder.

If it’s not working

Investigate the following:

Are the listeners working?
Are the tnsnames.ora configured to reflect the parameters specified in log_archive_dest_2?
Is the parameter *.service_names=’THTEST10.WORLD’ reflecting the service_name specified in tnsnames.ora (in connect_data)?

These should be a good starter. This process is very rigorous. Read the error message carefully and comply to what it requires.

We will see next how to configure dataguard to manage the primary and standby database.

50 New Features of SQL Server 2008

2010-12-30T19:31:00.000-08:00

This article explains you 50 new features of Microsoft SQL Server 2008. You can also read other popular article 22 New Features of Visual Studio 2008 for .NET Professionals

Transparent Data Encryption

Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.

Extensible Key Management

SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.

Auditing

Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"

Enhanced Database Mirroring

SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.

Automatic Recovery of Data Pages

SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.

Log Stream Compression

Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.

Resource Governor

Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.

Predictable Query Performance

Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Data Compression

Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.

Hot Add CPU

Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.

Policy-Based Management

Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.

Streamlined Installation

SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.

Performance Data Collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.

Language Integrated Query (LINQ)

Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.

ADO.NET Data Services

The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.

DATE/TIME

SQL Server 2008 introduces new date and time data types:

DATE—A date-only type

TIME—A time-only type

DATETIMEOFFSET—A time-zone-aware datetime type

DATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type

The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.

HIERARCHY ID

Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.

FILESTREAM Data

Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.

Integrated Full Text Search

Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.

Sparse Columns

NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.

Large User-Defined Types

SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.

Spatial Data Types

Build spatial capabilities into your applications by using the support for spatial data.

Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.

Implement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.

Backup Compression

Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.

Partitioned Table Parallelism

Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

Star Join Query Optimizations

SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.

Grouping Sets

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

Change Data Capture

With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.

MERGE SQL Statement

With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.

SQL Server Integration Services (SSIS) Pipeline Improvements

Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.

SQL Server Integration Services (SSIS) Persistent Lookups

The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.

Analysis Scale and Performance

SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.

Block Computations

Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.

Writeback

New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.

Enterprise Reporting Engine

Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.

Internet Report Deployment

Customers and suppliers can effortlessly be reached by deploying reports over the Internet.

Manage Reporting Infrastructure

Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.

Report Builder Enhancements

Easily build ad-hoc and author reports with any structure through Report Designer.

Forms Authentication Support

Support for Forms authentication enables users to choose between Windows and Forms authentication.

Report Server Application Embedding

Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.

Microsoft Office Integration

SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.

Predictive Analysis

SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop.

Log Miner

2010-12-27T20:09:00.000-08:00

Using Log Miner utility, you can query the contents of online redo log files and archived log files. Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

LogMiner Configuration

There are three basic objects in a LogMiner configuration that you should be familiar with: the source database, the LogMiner dictionary, and the redo log files containing the data of interest:

The source database is the database that produces all the redo log files that you want LogMiner to analyze.
The LogMiner dictionary allows LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request.
LogMiner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.

For example, consider the following the SQL statement:

INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES('IT_WT','Technical Writer', 4000, 11000);

Without the dictionary, LogMiner will display:

insert into "UNKNOWN"."OBJ# 45522"("COL 1","COL 2","COL 3","COL 4") values

(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),

HEXTORAW('c229'),HEXTORAW('c3020b'));

· The redo log files contain the changes made to the database or database dictionary.

LogMiner Dictionary Options

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you. LogMiner gives you three options for supplying the dictionary:

Using the Online Catalog
Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.

Extracting a LogMiner Dictionary to the Redo Log Files
Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.

Extracting the LogMiner Dictionary to a Flat File
This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.

Using the Online Catalog

To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner, as follows:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Extracting a LogMiner Dictionary to the Redo Log Files

To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled. While the dictionary is being extracted to the redo log stream, no DDL statements can be executed. Therefore, the dictionary extracted to the redo log files is guaranteed to be consistent (whereas the dictionary extracted to a flat file is not).

To extract dictionary information to the redo log files, use the DBMS_LOGMNR_D.BUILD procedure with the STORE_IN_REDO_LOGS option. Do not specify a filename or location.

SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Extracting the LogMiner Dictionary to a Flat File

When the LogMiner dictionary is in a flat file, fewer system resources are used than when it is contained in the redo log files. Oracle recommends that you regularly back up the dictionary extract to ensure correct analysis of older redo log files.

Set the initialization parameter, UTL_FILE_DIR, in the initialization parameter file. For example, to set UTL_FILE_DIR to use /oracle/database as the directory where the dictionary file is placed, enter the following in the initialization parameter file:
UTL_FILE_DIR = /oracle/database

Start the Database
SQL> startup

Execute the PL/SQL procedure DBMS_LOGMNR_D.BUILD. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file. For example, enter the following to create the file dictionary.ora in /oracle/database:
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/oracle/database/',

DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

Redo Log File Options

To mine data in the redo log files, LogMiner needs information about which redo log files to mine.

You can direct LogMiner to automatically and dynamically create a list of redo log files to analyze, or you can explicitly specify a list of redo log files for LogMiner to analyze, as follows:

Automatically

If LogMiner is being used on the source database, then you can direct LogMiner to find and create a list of redo log files for analysis automatically. Use the CONTINUOUS_MINE option when you start LogMiner.

Manually

Use the DBMS_LOGMNR.ADD_LOGFILE procedure to manually create a list of redo log files before you start LogMiner. After the first redo log file has been added to the list, each subsequently added redo log file must be from the same database and associated with the same database RESETLOGS SCN. When using this method, LogMiner need not be connected to the source database.

Example: Finding All Modifications in the Current Redo Log File

The easiest way to examine the modification history of a database is to mine at the source database and use the online catalog to translate the redo log files. This example shows how to do the simplest analysis using LogMiner.

Step 1 Specify the list of redo log files to be analyzed.

Specify the redo log files which you want to analyze.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => '/usr/oracle/ica/log1.ora',

OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -

LOGFILENAME => '/u01/oracle/ica/log2.ora',

OPTIONS => DBMS_LOGMNR.ADDFILE);

Step 2 Start LogMiner.

Start LogMiner and specify the dictionary to use.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Step 3 Query the V$LOGMNR_CONTENTS view.

Note that there are four transactions (two of them were committed within the redo log file being analyzed, and two were not). The output shows the DML statements in the order in which they were executed; thus transactions interleave among themselves.

SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');

USR XID SQL_REDO SQL_UNDO
---- --------- ----------------------------------------------------
HR 1.11.1476 set transaction read write;

HR 1.11.1476 insert into "HR"."EMPLOYEES"( delete from "HR"."EMPLOYEES"
"EMPLOYEE_ID","FIRST_NAME", where "EMPLOYEE_ID" = '306'
"LAST_NAME","EMAIL", and "FIRST_NAME" = 'Mohammed'
"PHONE_NUMBER","HIRE_DATE", and "LAST_NAME" = 'Sami'
"JOB_ID","SALARY", and "EMAIL" = 'MDSAMI'
"COMMISSION_PCT","MANAGER_ID", and "PHONE_NUMBER" = '1234567890'
"DEPARTMENT_ID") values and "HIRE_DATE" = TO_DATE('10-JAN-2003
('306','Mohammed','Sami', 13:34:43', 'dd-mon-yyyy hh24:mi:ss')
'MDSAMI', '1234567890', and "JOB_ID" = 'HR_REP' and
TO_DATE('10-jan-2003 13:34:43', "SALARY" = '120000' and
'dd-mon-yyyy hh24:mi:ss'), "COMMISSION_PCT" = '.05' and
'HR_REP','120000', '.05', "DEPARTMENT_ID" = '10' and
'105','10'); ROWID = 'AAAHSkAABAAAY6rAAO';

OE 1.1.1484 set transaction read write;

OE 1.1.1484 update "OE"."PRODUCT_INFORMATION" update "OE"."PRODUCT_INFORMATION"
set "WARRANTY_PERIOD" = set "WARRANTY_PERIOD" =
TO_YMINTERVAL('+05-00') where TO_YMINTERVAL('+01-00') where
"PRODUCT_ID" = '1799' and "PRODUCT_ID" = '1799' and
"WARRANTY_PERIOD" = "WARRANTY_PERIOD" =
TO_YMINTERVAL('+01-00') and TO_YMINTERVAL('+05-00') and
ROWID = 'AAAHTKAABAAAY9mAAB'; ROWID = 'AAAHTKAABAAAY9mAAB';

OE 1.1.1484 update "OE"."PRODUCT_INFORMATION" update "OE"."PRODUCT_INFORMATION"
set "WARRANTY_PERIOD" = set "WARRANTY_PERIOD" =
TO_YMINTERVAL('+05-00') where TO_YMINTERVAL('+01-00') where
"PRODUCT_ID" = '1801' and "PRODUCT_ID" = '1801' and
"WARRANTY_PERIOD" = "WARRANTY_PERIOD" =
TO_YMINTERVAL('+01-00') and TO_YMINTERVAL('+05-00') and
ROWID = 'AAAHTKAABAAAY9mAAC'; ROWID ='AAAHTKAABAAAY9mAAC';

HR 1.11.1476 insert into "HR"."EMPLOYEES"( delete from "HR"."EMPLOYEES"
"EMPLOYEE_ID","FIRST_NAME", "EMPLOYEE_ID" = '307' and
"LAST_NAME","EMAIL", "FIRST_NAME" = 'John' and
"PHONE_NUMBER","HIRE_DATE", "LAST_NAME" = 'Silver' and
"JOB_ID","SALARY", "EMAIL" = 'JSILVER' and
"COMMISSION_PCT","MANAGER_ID", "PHONE_NUMBER" = '5551112222'
"DEPARTMENT_ID") values and "HIRE_DATE" = TO_DATE('10-jan- 2003
('307','John','Silver', 13:41:03', 'dd-mon-yyyy hh24:mi:ss')
'JSILVER', '5551112222', and "JOB_ID" ='105' and
"DEPARTMENT_ID"
TO_DATE('10-jan-2003 13:41:03', = '50' and ROWID =
'AAAHSkAABAAAY6rAAP';
'dd-mon-yyyy hh24:mi:ss'),
'SH_CLERK','110000', '.05',
'105','50');

OE 1.1.1484 commit;

HR 1.15.1481 set transaction read write;

HR 1.15.1481 delete from "HR"."EMPLOYEES" insert into "HR"."EMPLOYEES"(
where "EMPLOYEE_ID" = '205' and "EMPLOYEE_ID","FIRST_NAME",
"FIRST_NAME" = 'Shelley' and "LAST_NAME","EMAIL","PHONE_NUMBER",
"LAST_NAME" = 'Higgins' and "HIRE_DATE", "JOB_ID","SALARY",
"EMAIL" = 'SHIGGINS' and "COMMISSION_PCT","MANAGER_ID",
"PHONE_NUMBER" = '515.123.8080' "DEPARTMENT_ID") values
and "HIRE_DATE" = TO_DATE( ('205','Shelley','Higgins',
'07-jun-1994 10:05:01', and 'SHIGGINS','515.123.8080',
'dd-mon-yyyy hh24:mi:ss') TO_DATE('07-jun-1994 10:05:01',
and "JOB_ID" = 'AC_MGR' 'dd-mon-yyyy hh24:mi:ss'),
and "SALARY"= '12000' 'AC_MGR','12000',NULL,'101','110');
and "COMMISSION_PCT" IS NULL
and "MANAGER_ID"
= '101' and "DEPARTMENT_ID" =
'110' and ROWID =
'AAAHSkAABAAAY6rAAM';

OE 1.8.1484 set transaction read write;

OE 1.8.1484 update "OE"."PRODUCT_INFORMATION" update "OE"."PRODUCT_INFORMATION"
set "WARRANTY_PERIOD" = set "WARRANTY_PERIOD" =
TO_YMINTERVAL('+12-06') where TO_YMINTERVAL('+20-00') where
"PRODUCT_ID" = '2350' and "PRODUCT_ID" = '2350' and
"WARRANTY_PERIOD" = "WARRANTY_PERIOD" =
TO_YMINTERVAL('+20-00') and TO_YMINTERVAL('+20-00') and
ROWID = 'AAAHTKAABAAAY9tAAD'; ROWID ='AAAHTKAABAAAY9tAAD';

HR 1.11.1476 commit;
Step 4 End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

Example of Mining Without Specifying the List of Redo Log Files Explicitly

The previous example explicitly specified the redo log file or files to be mined. However, if you are mining in the same database that generated the redo log files, then you can mine the appropriate list of redo log files by just specifying the time (or SCN) range of interest. To mine a set of redo log files without explicitly specifying them, use the DBMS_LOGMNR.CONTINUOUS_MINE option to the DBMS_LOGMNR.START_LOGMNR procedure, and specify either a time range or an SCN range of interest.

Example : Mining Redo Log Files in a Given Time Range

This example assumes that you want to use the data dictionary extracted to the redo log files.

Step 1 Determine the timestamp of the redo log file that contains the start of the data dictionary.

SQL> SELECT NAME, FIRST_TIME FROM V$ARCHIVED_LOG

WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE DICTIONARY_BEGIN = 'YES');

NAME FIRST_TIME

-------------------------------------------- --------------------

/usr/oracle/data/db1arch_1_207_482701534.dbf 10-jan-2003 12:01:34

Step 2 Display all the redo log files that have been generated so far.

This step is not required, but is included to demonstrate that the CONTINUOUS_MINE option works as expected, as will be shown in Step 4.

SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS

WHERE LOW_TIME > '10-jan-2003 12:01:34';

NAME

----------------------------------------------

/usr/oracle/data/db1arch_1_207_482701534.dbf

/usr/oracle/data/db1arch_1_208_482701534.dbf

/usr/oracle/data/db1arch_1_209_482701534.dbf

/usr/oracle/data/db1arch_1_210_482701534.dbf

Step 3 Start LogMiner.

Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY, PRINT_PRETTY_SQL, and CONTINUOUS_MINE options.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-

STARTTIME => '10-jan-2003 12:01:34', -

ENDTIME => SYSDATE, -

OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -

DBMS_LOGMNR.COMMITTED_DATA_ONLY + -

DBMS_LOGMNR.PRINT_PRETTY_SQL + -

DBMS_LOGMNR.CONTINUOUS_MINE);

Step 4 Query the V$LOGMNR_LOGS view.

This step shows that the DBMS_LOGMNR.START_LOGMNR procedure with the CONTINUOUS_MINE option includes all of the redo log files that have been generated so far, as expected. (Compare the output in this step to the output in Step 2.)

SQL> SELECT FILENAME name FROM V$LOGMNR_LOGS;

NAME

------------------------------------------------------

/usr/oracle/data/db1arch_1_207_482701534.dbf

/usr/oracle/data/db1arch_1_208_482701534.dbf

/usr/oracle/data/db1arch_1_209_482701534.dbf

/usr/oracle/data/db1arch_1_210_482701534.dbf

Step 5 Query the V$LOGMNR_CONTENTS view.

To reduce the number of rows returned by the query, exclude all DML statements done in the sys or system schema. (This query specifies a timestamp to exclude transactions that were involved in the dictionary extraction.)

Note that all reconstructed SQL statements returned by the query are correctly translated.

SQL> SELECT USERNAME AS usr,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID,

SQL_REDO FROM V$LOGMNR_CONTENTS

WHERE SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM') AND

TIMESTAMP > '10-jan-2003 15:59:53';

USR XID SQL_REDO

----------- -------- -----------------------------------

SYS 1.2.1594 set transaction read write;

SYS 1.2.1594 create table oe.product_tracking (product_id number not null,

modified_time date,

old_list_price number(8,2),

old_warranty_period interval year(2) to month);

SYS 1.2.1594 commit;

SYS 1.18.1602 set transaction read write;

SYS 1.18.1602 create or replace trigger oe.product_tracking_trigger

before update on oe.product_information

for each row

when (new.list_price <> old.list_price or

new.warranty_period <> old.warranty_period)

declare

begin

insert into oe.product_tracking values

(:old.product_id, sysdate,

:old.list_price, :old.warranty_period);

end;

SYS 1.18.1602 commit;

OE 1.9.1598 update "OE"."PRODUCT_INFORMATION"

set

"WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),

"LIST_PRICE" = 100

where

"PRODUCT_ID" = 1729 and

"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and

"LIST_PRICE" = 80 and

ROWID = 'AAAHTKAABAAAY9yAAA';

OE 1.9.1598 insert into "OE"."PRODUCT_TRACKING"

values

"PRODUCT_ID" = 1729,

"MODIFIED_TIME" = TO_DATE('13-jan-2003 16:07:03',

'dd-mon-yyyy hh24:mi:ss'),

"OLD_LIST_PRICE" = 80,

"OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00');

OE 1.9.1598 update "OE"."PRODUCT_INFORMATION"

set

"WARRANTY_PERIOD" = TO_YMINTERVAL('+08-00'),

"LIST_PRICE" = 92

where

"PRODUCT_ID" = 2340 and

"WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') and

"LIST_PRICE" = 72 and

ROWID = 'AAAHTKAABAAAY9zAAA';

OE 1.9.1598 insert into "OE"."PRODUCT_TRACKING"

values

"PRODUCT_ID" = 2340,

"MODIFIED_TIME" = TO_DATE('13-jan-2003 16:07:07',

'dd-mon-yyyy hh24:mi:ss'),

"OLD_LIST_PRICE" = 72,

"OLD_WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00');

OE 1.9.1598 commit;

Step 6 End the LogMiner session.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

MySQL : Dynamic

2010-12-05T19:57:00.000-08:00

You CAN execute Dynamic SQL from a mysql stored procedure using prepared statements (from correspondence with MySQL this might not 'officially' be supported but it works with the release candidate).

Here's a couple ways to handle a stored procedure which uses an input parameter in a prepared statement.

-- Concatenate SQL statement and input parameters before PREPARE

DELIMITER \\

DROP PROCEDURE IF EXISTS `sp_test_dynamic_sql`\\
CREATE PROCEDURE `sp_test_dynamic_sql` (_min int, _max int)
BEGIN

SET @s = CONCAT('SELECT * FROM authors WHERE id BETWEEN ', _min, ' AND ', _max);

PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

END\\

DELIMITER ;

Swap memory increase with swap file (How to create)

2010-12-01T19:37:00.000-08:00

Swap Memory is a space in the Hard Disk of your computer that Operating Systems (Linux in our case) will use to put the info that is actually on the RAM to free it for another application.

This should be done when the system needs memory for a new process and there is none, so we can see that if our system has plenty of RAM it will maybe need no SWAP memory.

How much Swap Memory do I need?

As a rule of thumb if you have 512 MB RAM put 1 GByte Swap, but this stops being true when the limit at least in x386 PCs is achieved (2 GB or swap is the maximum I could ever allocate to a System).

How do I create Swap.

Usually when you install Linux you reserve a partition to be used as swap memory, and the rest of the disk for your files, but what happens if I need more swap memory?. How to create more swap memory?

If you disk is full you can try to shrink the partitions to make room for another swap partition but an easier way is to make a swap file, so now the question is.

How to create a swap file?

dd if=/dev/zero of=/swapfile bs=1024 count=100000

This will create file (swapfile) of size 100 MB (round)

mkswap /swapfile

add this file to your swap pool

swapon /swapfile

This will do the job, now you have 100 more Mbytes of swap memory.

SQL Server Peer to Peer replicate

2010-11-24T01:08:00.000-08:00

Peer-to-Peer Replication in SQL Server 2008 – Configure a two-node topology

By Yan Pan

International companies with offices and customers around the globe often face a problem. Data specific to a location is updated at that location, but the updated data still need to be available to other locations.

Take an international sales website for example. Customers from different countries go to a landing page, select the login links written in their native languages, and are redirected to the login pages for their countries.

Behind the scene, a customer is actually directed to the application server hosting the login page that he/she selects.
In this scenario, the site traffic for the international sales company is distributed across application servers.

As you know, the application servers need a database backend to store customer and order information. Therefore, the database workload also needs to be distributed among database servers. Peer-to-Peer replication provides a very suitable solution for this kind of scenario.

First of all, the data is partitioned naturally by country. Therefore, data records specific to a country is only updated at the data servers residing at that country.

Although conflict detection is introduced in Peer-to-Peer replication in SQL Server 2008, the conflict resolution is simply based on the peer ID.
If a row identified by the primary key value is inserted, updated, and/or deleted at more than one node, the Distribution Agent will fail to deliver commands and remain in a stalled state until the conflict is resolved manually.

You will see an example in this article series.
Second, Peer-to-Peer replication builds on the foundation of transactional replication, and can be viewed as a multiple-node transactional replication with each node published to other nodes, and at the same time, subscribed to other nodes.
Therefore, writes on one server are propagated to other servers in near real-time, thus making data changes available to other locations quickly.
Peer-to-Peer replication also provides data redundancy and read scalability as each server has a copy of the replicated data.

INSERT INTO table EXEC (stored_procedure)

2010-07-21T20:52:00.000-07:00

Hi Buddy,
This script is for insert value into a table.
But the value is from an stored procedure return.

use tempdb
go

select
a.*
into
MyTemp
from
openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;',
'
SET FMTONLY OFF;
EXEC SP_HELPDB
') a

select * from MyTemp

drop table MyTemp

Maybe you will meet problem with Ad hoc querries.
Just use Administrator account and set using sp_configure to change the value to 1.
All will be okey
The script is here for reference

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

MySQL as a Data Warehousing Platform

2010-07-21T00:44:00.000-07:00

In the next post, we'll start doing some hands on. But before that we need to select a Data Warehouse Platform. Don't worry, I am not going to nudge you towards some commercial "Data Warehouse Solution" here! As promised, it will be open source and free.

Since a Data Warehouse is essentially a database, we could use any standard database for the purpose. But since we aren't making just a toy to impress people with, let's stop for a moment and look at what we need. So lets go over that...

Unlike an OLTP system that needs to write very fast, we would be better off with a database that can read lots of data faster because that is what we would be doing most of the time. (See previous post Database vs Data Warehouse).

We would also like the system to be production grade, robust, 24x7, and common enough so that finding expertise is not impossible. Do note that 24x7 availability may not be as critical for a DW as it would be for the OLTP servicing your bank's ATM or rerouting your flights.

Demonstrated ability to handle huge (really HUGE) amounts of data is a must as well, and it should allow high-speed bulk loading in batch jobs. And it would be great if there was a way to split very huge tables into smaller ones (like Oracle partitioning).

And then you would need transaction support. Right? Wrong.

Huh? Well, look at it this way. If your OLTP system did not have transactions, we'd be dead. But a DW is not a transaction oriented system. It would be more efficient to use a non-transactional database as you do away with the overheads required for enforcing transactions. This is a debatable point, but a non-transactional database would be a lot faster than a transactional database in simple read write tasks.

MySQL, configured right, makes an obvious and affordable choice for Data Warehousing.

MySQL's non-transactional MyISAM db engine is one of the fastest around when it comes to querying large amounts of data. You don't get transaction support, and that's alright because you don't need transactions (and the huge overheads they bring). And its bulk insert features (LOAD DATA INFILE…) are extemely fast as well!

In case you didn't know it, MyISAM tables can be merged into larger tables for querying. So if your data is split into 4 tables (say 1 for each quarter of year named SALES_2005_Q1, SALES_2005_Q2, etc.), you can define a merge table (say SALES_2005) on top of them. Then you can directly query SALES_2005 merge table using SQL if you want data for the entire year instead of running 4 queries on 4 tables. And since merge tables use indexes on the underlying tables, these queries can be very fast for well-designed indexing mechanisms. Sounds similar to Oracle's partitioning? Well, it gives you similar benefits free of cost.

MySQL is simple to install and maintain, can be easily setup and optimized for reporting, and is open source licensed under GPL.

The only thing you have to keep in mind is the "configuration". By default MySQL comes configured to be used as an OLTP system with InnoDB transactional engine. MyISAM engine is also there, just that it is not the default one. And since default MySQL has to run on very minimal platforms as well, it tries to take less resources than it should sometimes. So you may also need to tell it that you have installed it on a more powerful machine where it can use more RAM, etc.

A good design for a reporting system based on MySQL is to keep the fact tables as MyISAM (fastest disk based tables) and keep the smaller dimension tables in memory. You'll be surprised to see how your reports fly.

(From Internet)

TRUNCATE SQL SERVER LOG

2010-03-24T09:14:00.001-07:00

Problem
We have been running SQL Server 2000 for a few years and the transaction log file has become very large for some of our databases. In some circumstances, the transaction log is a more than 5 times larger than our database. How can I reduce the size of this file?

Solution
Most likely, the root cause for the continuous transaction log growth is related to the database recovery model being set to 'full' for your user defined databases without issuing regularly scheduled transaction log backups. The full recovery model configuration is maintaining all of the before and after records in the transaction log until the transaction log is backed up. Most likely, you are not backing up the transaction log on a regular basis i.e. hourly, daily, etc. to support a high availability solution such as log shipping.

With these items in mind, it is time to make some changes to your SQL Server user defined databases and get your transaction logs to a manageable size. Let's walk through each of these steps one at a time. Keep in mind that since you are not performing transaction log backups that this process could be completed at an as needed time, but the preference would be during a maintenance window.

Note - These commands can be run in SQL Server management tool of your choice and replace the '[YourDatabaseNameHere]' text with the needed user defined database name.

First, review the transaction log size prior to the shrinking process.

USE [YourDatabaseNameHere]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

Second, set the database recovery model to 'simple'.

USE [YourDatabaseNameHere]
GO
ALTER DATABASE [YourDatabaseNameHere] SET RECOVERY SIMPLE
GO

Third, issue a checkpoint against the database to write the records from the transaction log to the database.

USE [YourDatabaseNameHere]
GO
CHECKPOINT
GO

Fourth, truncate the transaction log.

USE [YourDatabaseNameHere]
GO
BACKUP LOG [YourDatabaseNameHere] WITH NO_LOG
GO

Fifth, record the logical file name for the transaction log to use in the next step.

USE [YourDatabaseNameHere]
GO
SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

Sixth, to free the unused space in your transaction log and return the space back to the operating system, shrink the transaction log file.

USE [YourDatabaseNameHere]
GO
DBCC SHRINKFILE ([FileNameFromPreviousStep], [NeededFileSize])
GO

Seven, review the database transaction log size to verify it has been reduced.

USE [YourDatabaseNameHere]
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

Next Steps

* Review your key SQL Server databases to determine if the transaction log growth is out of control.
* Review this code and modify it for one of your databases.
* Once the scripts are modified, test the scripts in a test environment to ensure they meet your needs.
* Schedule time to shrink your databases and communicate the configuration changes.
* Continue to monitor the database sizes and the available disk space on your servers.

TutorialCachingStory

2010-01-27T06:05:00.001-08:00

This is a story of Caching

ed note: this is an overview of basic memcached use case, and how memcached clients work

Two plucky adventurers, Programmer and Sysadmin, set out on a journey. Together they make websites. Websites with webservers and databases. Users from all over the Internet talk to the webservers and ask them to make pages for them. The webservers ask the databases for junk they need to make the pages. Programmer codes, Sysadmin adds webservers and database servers.

One day the Sysadmin realizes that their database is sick! It's spewing bile and red stuff all over! Sysadmin declares it has a fever, a load average of 20! Programmer asks Sysadmin, "well, what can we do?" Sysadmin says, "I heard about this great thing called memcached. It really helped livejournal!" "Okay, let's try it!" says the Programmer.

Our plucky Sysadmin eyes his webservers, of which he has six. He decides to use three of them to run the 'memcached' server. Sysadmin adds a gigabyte of ram to each webserver, and starts up memcached with a limit of 1 gigabyte each. So he has three memcached instances, each can hold up to 1 gigabyte of data. So the Programmer and the Sysadmin step back and behold their glorious memcached!

"So now what?" they say, "it's not DOING anything!" The memcacheds aren't talking to anything and they certainly don't have any data. And NOW their database has a load of 25!

Our adventurous Programmer grabs the pecl/memcache client library manual, which the plucky Sysadmin has helpfully installed on all SIX webservers. "Never fear!" he says. "I've got an idea!" He takes the IP addresses and port numbers of the THREE memcacheds and adds them to an array in php.

$MEMCACHE_SERVERS = array(
"10.1.1.1", //web1
"10.1.1.2", //web2
"10.1.1.3", //web3
);

Then he makes an object, which he cleverly calls '$memcache'.

$memcache = new Memcache();
foreach($MEMCACHE_SERVERS as $server){
$memcache->addServer ( $server );
}

Now Programmer thinks. He thinks and thinks and thinks. "I know!" he says. "There's this thing on the front page that runs SELECT * FROM hugetable WHERE timestamp > lastweek ORDER BY timestamp ASC LIMIT 50000; and it takes five seconds!" "Let's put it in memcached," he says. So he wraps his code for the SELECT and uses his $memcache object. His code asks:

Are the results of this select in memcache? If not, run the query, take the results, and PUT it in memcache! Like so:

$huge_data_for_frong_page = $memcache->get("huge_data_for_frong_page");
if($huge_data_for_frong_page === false){
$huge_data_for_frong_page = array();
$sql = "SELECT * FROM hugetable WHERE timestamp > lastweek ORDER BY timestamp ASC LIMIT 50000";
$res = mysql_query($sql, $mysql_connection);
while($rec = mysql_fetch_assoc($res)){
$huge_data_for_frong_page[] = $rec;
}
// cache for 5 minutes
$memcache->set("huge_data_for_frong_page", $huge_data_for_frong_page, 600);
}

// use $huge_data_for_frong_page how you please

Programmer pushes code. Sysadmin sweats. BAM! DB load is down to 10! The website is pretty fast now. So now, the Sysadmin puzzles, "What the HELL just happened!?" "I put graphs on my memcacheds! I used cacti, and this is what I see! I see traffic to one memcached, but I made three :(." So, the Sysadmin quickly learns the ascii protocol and telnets to port 11211 on each memcached and asks it:

Hey, 'get huge_data_for_front_page' are you there?

The first memcached does not answer...

The second memcached does not answer...

The third memcached, however, spits back a huge glob of crap into his telnet session! There's the data! Only once memcached has the key that the Programmer cached!

Puzzled, he asks on the mailing list. They all respond in unison, "It's a distributed cache! That's what it does!" But what does that mean? Still confused, and a little scared for his life, the Sysadmin asks the Programmer to cache a few more things. "Let's see what happens. We're curious folk. We can figure this one out," says the Sysadmin.

"Well, there is another query that is not slow, but is run 100 times per second. Maybe that would help," says the Programmer. So he wraps that up like he did before. Sure enough, the server loads drops to 8!

So the Programmer codes more and more things get cached. He uses new techniques. "I found them on the list and the faq! What nice blokes," he says. The DB load drops; 7, 5, 3, 2, 1!

"Okay," says the Sysadmin, "let's try again." Now he looks at the graphs. ALL of the memcacheds are running! All of them are getting requests! This is great! They're all used!

So again, he takes keys that the Programmer uses and looks for them on his memcached servers. 'get this_key' 'get that_key' But each time he does this, he only finds each key on one memcached! Now WHY would you do this, he thinks? And he puzzles all night. That's silly! Don't you want the keys to be on all memcacheds?

"But wait", he thinks "I gave each memcached 1 gigabyte of memory, and that means, in total, I can cache three gigabytes of my database, instead of just ONE! Oh man, this is great," he thinks. "This'll save me a ton of cash. Brad Fitzpatrick, I love your ass!"

"But hmm, the next problem, and this one's a puzzler, this webserver right here, this one runing memcached it's old, it's sick and needs to be upgraded. But in order to do that I have to take it offline! What will happen to my poor memcache cluster? Eh, let's find out," he says, and he shuts down the box. Now he looks at his graphs. "Oh noes, the DB load, it's gone up in stride! The load isn't one, it's now two. Hmm, but still tolerable. All of the other memcacheds are still getting traffic. This ain't so bad. Just a few cache misses, and I'm almost done with my work. So he turns the machine back on, and puts memcached back to work. After a few minutes, the DB load drops again back down to 1, where it should always be.

"The cache restored itself! I get it now. If it's not available it just means a few of my requests get missed. But it's not enough to kill me. That's pretty sweet."

So, the Programmer and Sysadmin continue to build websites. They continue to cache. When they have questions, they ask the mailing list or read the faq again. They watch their graphs. And all live happily ever after.

Author: Dormando via IRC. Edited by Brian Moon for fun. Further fun editing by Emufarmers.

Reset Identity Column Value in SQL Server

2010-01-24T22:17:00.001-08:00

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.

It would be wise to first check what the current identify value is. We can use this command to do so:

DBCC CHECKIDENT (’tablename’, NORESEED)

For instance, if I wanted to check the next ID value of my orders table, I could use this command:

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, I can use this command:

DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.

Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)

DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999)

Red Hat AS 5 Howto: Gyachi - Yahoo! messenger, Webcam, room voice chat, photo sharing

2009-12-09T01:05:00.000-08:00

One day, I find it so bored with Pdigin , so try another chat program on Linux ,
Red Hat Linux AS 5.4
I use Gyache.

Source is downloaded here
But when you complie with this.
I'm sure you will meet many and many error.
So in that case
Try to install these packages first. that will be more easier later.
libtool / gettext-tools / gtk-devel / libgtkhtml / alsa / alsa-devel / alsa-tools / alsa-utils / gpgme-devel / mcrypt (or libmcrypt) / mcrypt-devel or (libmcrypt-devel) / xmms / xmms-devel / lib-devel / openssl / openssl-devel / jasper / jasper-devel / libjpg-devel / v4l-conf / v4l-tools/libnotify-devel/libnotify

How to install and configure are very easy, just open INSTALL.txt file and step by step.
Trick to get it out.

How to create a partition for an existing table and index?

2009-11-29T23:45:00.001-08:00

ake the following steps to create a partition for an existing table and index:

1. Create a partition function: A partition function can be created by using the CREATE PARTITION FUNCTION STATEMENT. An example is given below:

CREATE PARTITION FUNCTION myfunc
RANGE LEFT FOR VALUES(100, 200, 300)
2. Create a partition scheme: A partition scheme can be created by using the CREATE PARTITION SCHEME statement. An example is given below:

CREATE PARTITION SCHEME myps AS myfunc TO (mv1), (mv2), (mv3)
3. Drop the clustered index: The clustered index can be dropped by using the DROP INDEX statement. The statement is as follows:

DROP INDEX index_name ON table_name.column_name
4. Recreate the clustered index on the partition scheme: The index can be created on a partitioned scheme as follows:

CREATE CLUSTERED INDEX ON employee.empid ON myps(empid)

The table is automatically partitioned according to the partition scheme of the clustered index.

On ROWNUM and Limiting Results

2009-11-26T01:13:00.001-08:00

On ROWNUM and Limiting Results
By Tom Kyte

Our technologist explains how ROWNUM works and how to make it work for you.

This issue's Ask Tom column is a little different from the typical column. I receive many questions about how to perform top-N and pagination queries in Oracle Database, so I decided to provide an excerpt from the book Effective Oracle by Design (Oracle Press, 2003) in hopes of answering a lot of these questions with this one column. Note that the content here has been modified from the original to fit the space and format.

Limiting Result Sets

ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:

* To perform top-N processing. This is similar to using the LIMIT clause, available in some other databases.
* To paginate through a query, typically in a stateless environment such as the Web. I use this technique on the asktom.oracle.com Web site.

I'll take a look at each of these uses after I review how ROWNUM works.

How ROWNUM Works

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N, where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select *
from t
where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

select ..., ROWNUM
from t
where
group by
having
order by ;

Think of it as being processed in this order:

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

That is why a query in the following form is almost certainly an error:

select *
from emp
where ROWNUM <= 5
order by sal desc;

The intention was most likely to get the five highest-paid people—a top-N query. What the query will return is five random records (the first five the query happens to hit), sorted by salary. The procedural pseudocode for this query is as follows:

ROWNUM = 1
for x in
( select * from emp )
loop
exit when NOT(ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP

It gets the first five records and then sorts them. A query with WHERE ROWNUM = 5 or WHERE ROWNUM > 5 doesn't make sense. This is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause.

Here is the correct version of this query:

select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;

This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records). As you'll see in the top-N discussion coming up shortly, Oracle Database doesn't really sort the entire result set—it is smarter than that—but conceptually that is what takes place.

SQL SELECT TOP N equivalent in ORACLE and MySQL

2009-11-26T01:11:00.000-08:00

Very easy select from get top

SQL Server:
SELECT TOP 10 product, descr, email
FROM products

ORACLE:
SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10

MySQL:
SELECT product, descr, email
FROM products
LIMIT 10

SQL Server .Script for Monitor Replication Status .

2009-11-05T00:39:00.000-08:00

After make Repliacte on SQL Server, You can monitor it using Replication Monitor.
For security problem, thats not a good issue to work with.
Here are the scritps from google (^^) for monitor the error and latecy of Replicate.

1. Script for monitor error
use distribution;
go
select top 100 * from msrepl_errors order by [time] desc;

2.Script for monitor latency

F OBJECT_ID('dbo.dba_replicationMonitor') IS Null
BEGIN
CREATE TABLE dbo.dba_replicationMonitor
(
monitor_id INT IDENTITY(1,1) Not Null
, monitorDate SMALLDATETIME Not Null
, publicationName sysname Not Null
, publicationDB sysname Not Null
, iteration INT Null
, tracer_id INT Null
, distributor_latency INT Null
, subscriber VARCHAR(1000) Null
, subscriber_db VARCHAR(1000) Null
, subscriber_latency INT Null
, overall_latency INT Null
);
END;

IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_replicationLatencyMonitor_sp'), N'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.dba_replicationLatencyMonitor_sp;
PRINT 'Procedure dba_replicationLatencyMonitor_sp dropped';
END;
Go

SET Quoted_Identifier ON
Go
SET ANSI_Nulls ON
Go

CREATE PROCEDURE dbo.dba_replicationLatencyMonitor_sp

/* Declare Parameters */
@publicationToTest sysname = N'yourPublicationName'
, @publicationDB sysname = N'yourPublicationDB'
, @replicationDelay VARCHAR(10) = N'00:00:30'
, @iterations INT = 5
, @iterationDelay VARCHAR(10) = N'00:00:30'
, @displayResults BIT = 0
, @deleteTokens BIT = 1
AS
/*********************************************************************************
Name: dba_replicationLatencyMonitor_sp

Author: Michelle F. Ufford

Purpose: Retrieves the amount of replication latency in seconds

Notes: Default settings will run 1 test every minute for 5 minutes.

@publicationToTest = defaults to yourPublicationName publication

@publicationDB = the database that is the source for the publication.
The tracer procs are found in the publishing DB.

@replicationDelay = how long to wait for the token to replicate;
probably should not set to anything less than 10 (in seconds)

@iterations = how many tokens you want to test

@iterationDelay = how long to wait between sending test tokens
(in seconds)

@displayResults = print results to screen when complete

@deleteTokens = whether you want to retain tokens when done

Called by: DBA

Date Initials Description
----------------------------------------------------------------------------
2008-11-20 MFU Initial Release
2008-11-24 ILK Tweaked to allow for centralized execution
Replaced temp table with permanent table.
2008-11-25 MFU More tweaking, added publication data to
dba_replicationMonitor, fixed NULL latency data,
moved dba_replicationMonitor creation out of proc
*********************************************************************************
Exec dbo.dba_replicationLatencyMonitor_sp
@publicationToTest = N'myTestPublication'
, @publicationDB = N'sandbox_publisher'
, @replicationDelay = N'00:00:05'
, @iterations = 1
, @iterationDelay = N'00:00:05'
, @displayResults = 1
, @deleteTokens = 1;
*********************************************************************************/

SET NOCOUNT ON;
SET XACT_Abort ON;

BEGIN

/* Declare Variables */
DECLARE @currentIteration INT
, @tokenID BIGINT
, @currentDateTime SMALLDATETIME
, @sqlStatement NVARCHAR(200)
, @parmDefinition NVARCHAR(500);

DECLARE @tokenResults TABLE
(
iteration INT Null
, tracer_id INT Null
, distributor_latency INT Null
, subscriber VARCHAR(1000) Null
, subscriber_db VARCHAR(1000) Null
, subscriber_latency INT Null
, overall_latency INT Null
);

/* Initialize our variables */
SELECT @currentIteration = 0
, @currentDateTime = GETDATE();

WHILE @currentIteration < @iterations
BEGIN

/* Prepare the stored procedure execution string */
SET @sqlStatement = N'Execute ' + @publicationDB + N'.sys.sp_postTracerToken ' +
N'@publication = @VARpublicationToTest , ' +
N'@tracer_token_id = @VARtokenID OutPut;'

/* Define the parameters used by the sp_ExecuteSQL later */
SET @parmDefinition = N'@VARpublicationToTest sysname, ' +
N'@VARtokenID bigint OutPut';

/* Insert a new tracer token in the publication database */
EXECUTE SP_EXECUTESQL
@sqlStatement
, @parmDefinition
, @VARpublicationToTest = @publicationToTest
, @VARtokenID = @TokenID OUTPUT;

/* Give a few seconds to allow the record to reach the subscriber */
WAITFOR Delay @replicationDelay;

/* Prepare our statement to retrieve tracer token data */
SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_helpTracerTokenHistory ' +
N'@publication = @VARpublicationToTest , ' +
N'@tracer_id = @VARtokenID'
, @parmDefinition = N'@VARpublicationToTest sysname, ' +
N'@VARtokenID bigint';

/* Store our results for retrieval later */
INSERT INTO @tokenResults
(
distributor_latency
, subscriber
, subscriber_db
, subscriber_latency
, overall_latency
)
EXECUTE SP_EXECUTESQL
@sqlStatement
, @parmDefinition
, @VARpublicationToTest = @publicationToTest
, @VARtokenID = @TokenID;

/* Assign the iteration and token id to the results for easier investigation */
UPDATE @tokenResults
SET iteration = @currentIteration + 1
, tracer_id = @tokenID
WHERE iteration IS Null;

/* Wait for the specified time period before creating another token */
WAITFOR Delay @iterationDelay;

/* Avoid endless looping... :) */
SET @currentIteration = @currentIteration + 1;

END;

/* Display our results */
IF @displayResults = 1
BEGIN
SELECT
iteration
, tracer_id
, IsNull(distributor_latency, 0) AS 'distributor_latency'
, subscriber
, subscriber_db
, IsNull(subscriber_latency, 0) AS 'subscriber_latency'
, IsNull(overall_latency,
IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
AS 'overall_latency'
FROM @tokenResults;
END;

/* Store our results */
INSERT INTO dbo.dba_replicationMonitor
(
monitorDate
, publicationName
, publicationDB
, iteration
, tracer_id
, distributor_latency
, subscriber
, subscriber_db
, subscriber_latency
, overall_latency
)
SELECT
@currentDateTime
, @publicationToTest
, @publicationDB
, iteration
, tracer_id
, IsNull(distributor_latency, 0)
, subscriber
, subscriber_db
, IsNull(subscriber_latency, 0)
, IsNull(overall_latency,
IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0))
FROM @tokenResults;

/* Delete the tracer tokens if requested */
IF @deleteTokens = 1
BEGIN

SELECT @sqlStatement = 'Execute ' + @publicationDB + '.sys.sp_deleteTracerTokenHistory ' +
N'@publication = @VARpublicationToTest , ' +
N'@cutoff_date = @VARcurrentDateTime'
, @parmDefinition = N'@VARpublicationToTest sysname, ' +
N'@VARcurrentDateTime datetime';

EXECUTE SP_EXECUTESQL
@sqlStatement
, @parmDefinition
, @VARpublicationToTest = @publicationToTest
, @VARcurrentDateTime = @currentDateTime;

END;

SET NOCOUNT OFF;
RETURN 0;
END
Go

SET Quoted_Identifier OFF;
Go
SET ANSI_Nulls ON;
Go

MySQL Storage Engine MYISAM , INNODB

2009-11-03T00:24:00.000-08:00

When you create a table, you can choose what strorage engine to use.
The most popular usage in MySQL is MYISAM and INNODB.Base on the purpose and
the feature of each storage engine ,you can get a right decision.

I will make here a comparision about two kind.
1. On disk
* MYISAM table using three files
+ a format file that stores the definition of the table structure ( mytable.frm)
+ a data file that stores the contents of table rows (mytable.MYD)
+ an index file that stores any indexes on the table (mytable.MYI)
* Each INNODB table is represented on disk by an .frm format file (like MYISAM).
As well as data and index storage in the INNODB tablespace.(the default name is ibdata1 in
data directory ).
2. Lock mechanism
* MyISAM : Mysql manages contention between queries for MYISAM table access using table-level
lock . Query performance is very fast for retrievals.Multiple tables can read the table
simultaneously.For a write query , an exlusie lock is used to prevent use of the table by
other read or write queries.So very clearly that will lead to reduce performance in
environments with a mix of read and write queries.
Deadlock canot occur with table-level lock .
* INNODB : Mysql manages query contention for INNODB using multi versioning and row-level
locking.Multi-versioning give each transaction it own view of the databases.This, combined
with row-level locking keep contention to a minumun.The result is good query concurrency
even if clients are performing a mix of reads and writes.
Deadlock can occur
3. Data file .
Both MYISAM and INNODBA file on disk are portable, so can be copied directy to another host
and used by server here.

Conclusion :
If your table are updated rarely and for purpose to read, try MYISAM .Other way, INNODB is the
best solution storage engine for an enviroment mix of reads and writes.INNODB support transactions with commit and rollback(with rollback, they get an rollback segment on Tablespace ).It provides full ACID(atomicity,consistency,isolation,durability).INNODB storage engine have all the features like on Oracle database.

Vmware On Linux

2009-10-30T02:14:00.000-07:00

On Linux, xend is the best solution for virtualization with para and full supported.
I'm using Centos 5.2 with Kernel get Xend support.
But unfortunately, I cannot manage using Xen to install windown.
Seem so dummy for install windown on linux, but I think that there are many people have same need like me.
Try another way, I use VMWare Server2.0 for linux ( go to VMware site and make a download).
When rpm -ivh VMware-server-2.0.2-203138.i386.rpm,
I get an error :

 You cannot install VMware Server on a system running a xen kernel

How to fix this,

To do this, you first need to install the kernel. yum makes this easy:

yum install kernel

Then remove xen and the kernel-xen packages:

yum remove xen kernel-xen

Then you have to specify which kernel you want to run in /boot/grub/grub.conf. In that file there’s a list of kernels available to boot from:

#boot=/dev/sda default=1 timeout=5 splashimage=(hd0,0)/boot/grub/splash.xpm.gz hiddenmenu title CentOS (2.6.18-92.1.10.el5) root (hd0,0) kernel /boot/vmlinuz-2.6.18-92.1.10.el5 ro root=LABEL=/1 rhgb quiet initrd /boot/initrd-2.6.18-92.1.10.el5.img title CentOS (2.6.18-92.1.10.el5xen) root (hd0,0) kernel /boot/xen.gz-2.6.18-92.1.10.el5 module /boot/vmlinuz-2.6.18-92.1.10.el5xen ro root=LABEL=/1 rhgb quiet module /boot/initrd-2.6.18-92.1.10.el5xen.img

The line that says default= specifies which kernel to load, where 0 is the first one listed in the file. In my case, I had to change the line that says default=1 to default=0 to specify the non-xen kernel.

Reboot, and you’re good to go – the VMware RPM will install, and VMware server runs just fine.

After , you can get to Kernel with Xen, please remember to make a
"yum update" for VMware config running smooth.

An important node.

You can meet a hard error like this

What is the location of the directory of C header files that match your running
kernel? [/usr/src/linux/include]

The path "/usr/src/linux/include" is not an existing directory.

To fix it

Do the following step

yum install kernel-devel

After that link the directory :

ls -d /usr/src/kernels/$(uname -r)*/include

That will work for you.

If meet some errors, Just post here.

AntiVirus Software On Linux

2009-10-28T21:21:00.001-07:00

Hi all,
Virus attached dont' care what you are using, Linux,Windown or Unix.So proctect
yourself from virus is a very important job.
With Linux, I get an advise from an experienced Linux master, using ClamAV.
Try to fingure it out.

1. Download the source from http://sourceforge.net/projects/clamav/files/clamav/0.95.3/clamav-0.95.3.tar.gz/download
2. Unzip it and make it running
* cd clamav-0.95.3
* ./configure
* make
* make install
3. Scan it
[root@alpha VIRUS]clamscan -r --move=VIRUS -l Logfile.txt
4.Update virus database
[root@alpha VIRUS]freshman
Maybe you will get a dummy error
ERROR: Please edit the example config file /usr/local/etc/freshclam.conf
ERROR: Can't open/parse the config file /usr/local/etc/freshclam.conf

To fix it, just go to this file freshclam.conf and change like this
Original

# Comment or remove the line below.
Example

Change
# Comment or remove the line below.
#Example

Very dummy,it running.

5. Add a cronjob for schedule scan or update as you want.
More information just look it this file freshclam.conf.

The official MySQL Optimization Tips

2009-10-26T09:00:00.000-07:00

(From Nova Blog)

From the basic conditions, costs, advantages and disadvantages and applications in terms of five kinds of MySQL reliability of the program are analyzed and compared to the MySQL development and management role in a certain reference.

1． 1. MySQL Clustering(ndb-cluster stogare) MySQL Clustering (ndb-cluster stogare)

Description:
MySQL storage engine company to provide high-reliability programs, is a transaction-safe, and real-time replication of data that can be used that require high reliability and load balancing occasions. The program requires at least three nodes in the server to achieve better results.

Cost:
Node server RAM in great demand, with the database size of the linear ratio;
Better to use the Gigabit Ethernet network;
Also need to use the Dolphin's SCI cards to provide expensive.

Benefits:
Can be used for load balancing occasions;
Can be used for high-reliability occasions;
High scalability;
The real database redundancy;
Easy to maintain.

Disadvantages:
With a database larger, the demand for RAM to become bigger, so the cost is high;

Speed:
Almost more than the typical stand-alone server (no Gigabit Ethernet, no SCI cards, storage engine-related restrictions less) slow 10 times.

Applications:
Redundancy, high reliability, load balancing

2. MySQL / GFS-GNBD/ HA (Active/Passive)

Description:
If multiple MySQL servers using a shared disk as a data storage, how this program?
GFS / GNBD can provide the necessary shared disk.
。 GFS is a transaction-safe file system The same time you can make a MySQL using the shared data.

Cost:
N up to the cost of high-performance servers, one active, the other as a backup server.

Benefits:
High reliability, a certain degree of redundancy in accordance with the high reliability of the telescopic

Disadvantages:
No load balancing does not guarantee that the redundancy can not write to stretch

Speed:
A separate server 2 times. Operational support to the reading better.

Applications
That require high reliability, and read-intensive applications

3. MySQL / DRBD / HA (Active/Passive)

Description:
If multiple MySQL servers using a shared disk as a data storage, how this program?
DRBD can provide such a shared hard drive. DRBD can be set to transaction security.
The same time you can make a MySQL using the shared data.

Cost:
N up to the cost of high-performance servers, one active, while others as a backup server.

Benefits:
High reliability;
A certain degree of redundancy;
Look at the name of a high reliability is scalable.

Disadvantages:
Load balancing does not guarantee that there is no redundancy in the load there is no flexibility in writing

Speed:
The equivalent of reading and writing in a separate server

Applications
That require high reliability, read-intensive applications

4. MySQL Write Master / Multiple MySQL Read Slaves (Active/Active) 4. MySQL Write Master / Multiple MySQL Read Slaves (Active / Active)

Description:
Consider different reading, writing and DB database connection situation. You can use a single master server for the write operation, while the use of n units from the server for read operations.

Cost:
To write up a high-performance servers, n Taiwan to study the cost of the server

Benefits:
Read the high reliability;
Load balance read operations;
In the read load balancing aspect is scalable.
Disadvantages:
No write operation of high reliability;
No write operation of the load-balancing;
In the write-free scalability;

Speed:
With a separate server; in the read operation to support very good

Applications

Read-intensive and require high reliability and load balancing applications.

5. Standalone MySQL Servers(Functionally separated) (Active) 5. Standalone MySQL Servers (Functionally separated) (Active)

Functional separation of multiple separate servers, there is no high-reliability, load balancing, obviously too many shortcomings, not be considered.

Summary:
。 MySQL official website of the program is a combination of HA recommended DRBD (This article Option 3) and Replication (program 4). We add the Linux Heartbeat can be implemented with Auto-failover feature, so that machine down time is greatly reduced.

ORACLE : AUTOMATICALLY STARTUP,ASM(Part 3)

2009-10-26T08:11:00.000-07:00

With the part 1 and part 2.We have known how to make scripts for automatic startup
Oracle database , listener and Application Server.When database have the feature ASM ,
we have make some changes, startup css before startup database.(If you are working with RAC,you will see css is very familiar)

1. Change in /etc/inittab

[root@igisdb rc.d]# cd /etc/
[root@igisdb etc]# vi inittab

#
# inittab This file describes how the INIT process should set up
# the system in a certain run-level.
#
# Author: Miquel van Smoorenburg,
# Modified for RHS Linux by Marc Ewing and Donnie Barnes
#

# Default runlevel. The runlevels used by RHS are:
# 0 - halt (Do NOT set initdefault to this)
# 1 - Single user mode
# 2 - Multiuser, without NFS (The same as 3, if you do not have networking)
# 3 - Full multiuser mode
# 4 - unused
# 5 - X11
# 6 - reboot (Do NOT set initdefault to this)
#
id:5:initdefault:

# System initialization.
si::sysinit:/etc/rc.d/rc.sysinit

l0:0:wait:/etc/rc.d/rc 0
l1:1:wait:/etc/rc.d/rc 1
l2:2:wait:/etc/rc.d/rc 2
h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1
l3:3:wait:/etc/rc.d/rc 3
l4:4:wait:/etc/rc.d/rc 4
l5:5:wait:/etc/rc.d/rc 5
l6:6:wait:/etc/rc.d/rc 6

# Trap CTRL-ALT-DELETE
ca::ctrlaltdel:/sbin/shutdown -t3 -r now

# When our UPS tells us power has failed, assume we have a few minutes
# of power left. Schedule a shutdown for 2 minutes from now.
# This does, of course, assume you have powerd installed and your
# UPS connected and working correctly.
pf::powerfail:/sbin/shutdown -f -h +2 "Power Failure; System Shutting Down"

# If power was restored before the shutdown kicked in, cancel it.
pr:12345:powerokwait:/sbin/shutdown -c "Power Restored; Shutdown Cancelled"

# Run gettys in standard runlevels
1:2345:respawn:/sbin/mingetty tty1
2:2345:respawn:/sbin/mingetty tty2
3:2345:respawn:/sbin/mingetty tty3
4:2345:respawn:/sbin/mingetty tty4
5:2345:respawn:/sbin/mingetty tty5
6:2345:respawn:/sbin/mingetty tty6

# Run xdm in runlevel 5
x:5:respawn:/etc/X11/prefdm -nodaemon
#h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 ~
~
~
~
~
~
~
~
~
~
~
"inittab" 55L, 1801C

We add h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1
2. Change in /etc/oratab
+ASM:/u01/app/oracle/product/10.1.0/db_1:Y
igis:/u01/app/oracle/product/10.1.0/db_1:Y

3. Create file dbora for automatic startup

[root@igisdb etc]# cd /etc/init.d/
[root@igisdb init.d]# vi dbora

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

SLEEP_TIME=180
ORACLE_OWNER=oracle

case "$1" in
start)
sleep $SLEEP_TIME
su - $ORACLE_OWNER -c "/backup/script/dbStart"
touch /var/lock/subsys/dbora
;;

stop)
su - $ORACLE_OWNER -c "/backup/script/dbStop"
rm -f /var/lock/sysbsys/dbora
;;

*)
echo $"Usage: $prog {start|stop}"
exit 1
esac
exit

4.Create file dbStart and dbStop in /backup/script
[root@igisdb script]# vi dbStart

export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
lsnrctl start
$ORACLE_HOME/bin/dbstart
~

[root@igisdb script]# vi dbStop

export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
$ORACLE_HOME/bin/dbshut
lsnrctl stop
~

ORACLE : AUTOMATICALLY STARTUP,Application Server (Part 2)

2009-10-26T08:00:00.000-07:00

• STARTUP ORACLE APPLICATION SERVER
Some action are worked as Part1 ,
/etc/oratab
*:/home/oracle/product/10.1.3.1/OracleAS_1:N
We made a script called
[root@igisdev init.d]# vi appora
#!/bin/bash
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/home/oracle/product/10.1.3.1/OracleAS_2
ORA_OWNER=root
case "$1" in
'start')
# Start HTTP Server and OC4J instance
if [ -f $ORA_HOME/opmn/bin/opmnctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/opmn/bin/opmnctl startall"
fi
;;

'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/opmn/bin/opmnctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/opmn/bin/opmnctl stopall"
fi
;;
esac
~

ORACLE : AUTOMATICALLY STARTUP,Database And Listener (Part 1)

2009-10-26T07:51:00.000-07:00

· NORMAL DATABASE AND LISTENER

1. Add in /etc/oratab

igis:/u01/app/oracle/product/10.2.0/db_1:Y

( Explain : ORACLE_SID:ORACLE_HOME:Y)

2. [root@igisdev etc]# cd /etc/init.d/

Create dbora as

[root@igisdev init.d]# vi dbora

#!/bin/sh

# chkconfig: 345 99 10

# descripion: Oracle auto start-stop script.

# Set ORA_HOME to be equivalent to the $ORACLE_HOME

# from wich you wish to execute dbstart and dbshut;

# Set ORA_OWNER to the user id of the owner of the

# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/10.2.0/db_1

ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]

then

echo "Oracle startup: cannot start"

exit

case "$1" in 'start')

# Start the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"

su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"

;;

'stop')

# Stop the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"

;;

esac

3. Change permission on file va add startup at level 3 and 5

[root@igisdev init.d]# chmod 777 dbora

[root@igisdev init.d]# chkconfig --level 35 dbora on

HowTo Yum on Centos 5.2

2009-10-23T21:04:00.001-07:00

First using Yum, I cannot manage to install, update software from Internet.
That's so difficult to work with the software that have many dependencies.
Try to fix this is a small tip.

1. Try to yum local from ISO file.
2. Enable yum repository.

Let go details .
1. Yum local from ISO file
[root@alpha yum.repos.d]#cd /etc/yum.repos.d
[root@alpha yum.repos.d]#vi Centos-Media.repo
[c5-media]
name=CentOS-$releasever - Media
baseurl=file:///media/CentOS/
file:///media/cdrom/
file:///media/cdrecorder/
file:///media/ISOFILE ( add mount device for ISOFILE ) .
[root@alpha yum.repos.d] yum --disablerepo=\* --enablerepo=c5-media install vlc

2. Enable yum repository
Look in file /etc/yum.repos.d/CentOS-Base.repo
When enabled =0, change it to enabled = 1 .
Make a check and enjoy your jobs.
[root@alpha yum.repos.d]# yum repolist
repo id repo name status
addons CentOS-5 - Addons enabled
base CentOS-5 - Base enabled
centosplus CentOS-5 - Plus enabled
extras CentOS-5 - Extras enabled
rpmforge Red Hat Enterprise 5 - RPMforge.net - da enabled
updates CentOS-5 - Updates enabled
[root@alpha yum.repos.d]# yum install vlc

Some small tips on Linux

2009-10-23T20:47:00.000-07:00

Working with Linux like a server is a great jobs, but when use it in normal life for daily task, I'm sure we meet a lot issues, not supported, not found package, howto...etc.Some small tips when I try use Linux in daily life.Hope that help us a little.
1. Mount ISO file
[root@alpha ~]# mount -o loop /ISO_File/CentOS-5.2-i386-bin-DVD.iso /media/ISOFILE/
Put it in /etc/fstab
[root@alpha ~]vi /etc/fstab
/ISO_File/CentOS-5.2-i386-bin-DVD.iso /media/ISOFILE iso9660 rw,loop=/dev/loop0

2. Music on Linux.
* There are many player program for listen music, what video.
I do like MPlayer and VLC.
Install MPlayer :
- Download MPlayer-1.0rc2.tar.bz2
- Install from source
[root@isc-alpha Linux]tar -xvfz MPlayer-1.0rc2.tar.bz2
[root@isc-alpha Linux]cd MPlayer-1.0rc2
[root@isc-alpha Linux]./configure
[root@isc-alpha Linux]make
[root@isc-alpha Linux]make install
[root@isc-alpha Linux]mplayer
- After finish install, When using this you will meet a problem about default skin.
To fix is a little dummy and easy, try to download one skin on google.
I use Blue-1.7.tar.bz2
* Extract this file
* Copy all the file in the directory to folder /usr/local/share/mplayer/skins /default.
* Change the file skinb.png to skin.png
Then it works.

If the sound is a little small, dont try to install driver, find sound card , fix more software.I do a small job, unplug the headphone and plug in the back of the case.
So suprised, the sound like on the windown, big and bigger.

Install VLC:
Install VLC without Internet is a hard jobs.If you get internet , just easy yum install vlc.
If not you install from source or rpm packages , it requires you such a lot dependencies .
So howto fix in this case, very easily , change to MPlayer

3. View PDF file
[root@alpha ~]kpdf filename

4. View Graphic file
[root@alpha ~]kview filename

HOWTO Read .CHM File On Linux

2009-10-23T20:34:00.000-07:00

There are many ways and software to read .CHM file on linux.
1. You can use wine application, then install MU-Commander.
With Mu-Commander, you can easily read .CHM file.
But the view is not so good, you cannot change anything, the font also small.
I really hate this kind of usage.

2. Using another software support this reading.
After google a lot , I find out gnochm is a good software to use.
Download from this site
http://sourceforge.net/projects/gnochm/files/
The packages need to download and install in sequences.
* rpm -ivh chmlib-0.40-1.el5.rf.i386.rpm
* rpm -ivh chmlib-devel-0.40-1.el5.rf.i386.rpm
* rpm -ivh pychm-0.8.4-2fc5.i386.rpm
* gnochm-0.9.11-1fc5.src.rpm

After finish, feel it easily using to read the ebook
[root@alpha Linux]# gnochm

Lock screen when Using Root User

2009-10-23T20:21:00.000-07:00

With all who use Linux, root is such a so powerful user . Using it careless can make many disaster problem.But with a desktop enviroment , we dont need so much high
avalible and must use another different account, what we need is security with all the document , the email ..etc.
There is one usefull software that can support us do this. Lock Screen in Root user, that's is xlockmore.

Easily Install : yum install xlockmore.
For package : Just download via Google search. hehe.

Using .
[root@alpha ~]# xlock
[root@alpha ~]# xlock -mode ball

Try man xlock for more mode and beautiful screen..

Setup Replicate with Security

2009-10-06T08:00:00.000-07:00

SQL 2005 : Setup Replicate with Security

Today, I summarize some essential steps to setup replicate with Security.

The main idea for that security setup is to divide smaller and smaller privilleges

of the user.

Let have a look at this .

We do on two server

SERVER1 : Publisher, Distributor.

SERVER2 : Subscriber

Database Publisher : Pub_Test

Database Subscriber : Sub_Test

Privleges needed when configure Replicate

Enable Distributor : SysAdmin ( Using Administrator)

Enable database for Publisher : SysAdmin ( Using Administrator to setup )

Create publication : db_owner of Pub_Test ( Using PublisherUser)

Subscribe to a publication : db_owner of Pub_Test, db_owner of Sub_Test (Using SubscriberUser)

Step 1 : Disable Distributor, Delete all the existing Publisher, Subscriber

Step 2 : Create Windowns Account on each node with the same UserName, Password.

SERVER1 :

ReplSnapAgent : For snapshot agent.

ReplDistAgent : For distributor agent.

PublisherUser : Publishing Tasks

SubscriberUser: Publishing Tasks

SERVER2

ReplSnapAgent : For snapshot agent.

ReplDistAgent : For distributor agent.

PublisherUser : Publishing Tasks

SubscriberUser: Publishing Tasks

Step 3 : Grant privleges on the share folder

Create directory : C:\Repl

Administrators : Allow-Full Control

PublisherUser : Allow-Read

SubscriberUser : Allow-Read

ReplSnapAgent : Allow-Change

ReplDistAgent : Allow-Read

Step 4 : Configure Distributor

SERVER1

· Use Administrator account

· Configure follow the wizard

· Remember to choose : Folder for snapshot like this : \\SERVER1\Repl

Step 5 : Running the script to enable Publisher

SERVER1

· Use Administrator account

exec sp_replicationdboption

@dbname = N'Pub_Test' ,

@optname = N'publish' ,

@value = N'true'

Step 6: Assign Database Roles

· Use Administrator Account

· Open Security on each SERVER, map Server and grant role like below

ReplDistAgent :(Carefully on Server1 and Server2)

SERVER1 : Distribution : db_owner

SEREVR2 : Sub_Test : db_owner

ReplSnapAgent :

SERVER1 : Distribution : db_owner

SERVER1: Pub_test : db_owner

PublisherUser

SERVER1 : Pub_Test : db_owner

SubscriberUser

SERVER1: Pub_Test : db_owner

SERVER2: Sub_Test : db_owner

Step 7 : Configure Publisher

SERVER1 :

* Connect to as PublisherUser

* Follow the wizards

* Snapshot Agent Security : SERVER1\ReplSnapAgent

* Open PAL, add 2 account ReplSnapAgent, SubScriberUser in.

Step 8 : Create a Subscription

SERVER2 :

* Connect to as Subscriber User

* Follow the wizards

* Choose SERVER1 for Pubisher.(Windown authenticate)

* Push subscription

* Distibution Agent Security SERVER1\ReplDistAgent

VietNamese Fonts & Keyboard input on Linux

2009-10-02T03:06:00.000-07:00

I'm using Centos 5.2 .Install OpenOffice 5.2 and few difficult howto use VietNamese on this
application.
Search on google, bing and many resource they guide me to do this.

1. First : Install fonts
Download source : utf8.tar.gz
Unzip : tar -xvfz utf8.tar.gz
Install : ./configre
make
make install
Cache : fc-cache

2. Second : Install an VietNamese Input keyboard.
Download source : http://xvnkb.sourceforge.net/xvnkb/xvnkb-0.2.8a.tar.bz2
Unzip :tar xjvf xvnkb-0.2.8a.tar.bz2
Install
./configure
make && make install

And now the problem happen, everytime I start up OpenOffice 3.1 ,
I meet a so terrible error, my application hangs up forever and forever.
I have tried many many way and finally find out that.
You dont have to install the font, just install the VietNam input keyboard
and enjoy VietNamese words.

So the way to fix OpenOffice3.1 hang on,
Just make uninstall the fonts.
Uninstall OpenOffice
Uninstall xvnkb.
Remove folder $home/.openoffice
Make a reboot.

And finally install xvnkb, OpenOfiice that's enough.

Hope that help you

Adding Network Adapter Attansic L2 100M

2009-10-02T02:19:00.000-07:00

Centos 5.2 doesnot recognize the Network Adapter naming Attansic Technolog Corp .L2 100
So we have to install the package support for this kind of NIC.
That's so terrible problem.Imagie what we are doing with computer without Net.

After google 1 day , do many way.I found out 2 solutions that can help me overcome this.

1. Using rpm package.
* Download 2 rpm package here
+ atl2-1.0.40.4-el5.ceeentos.1.noarch.rpm
+ kmod-atl2-1.0.40.4-el5.ceeentos.1.i686
* Install them
+rpm -ivh atl2-1.0.40.4-el5.ceeentos.1.noarch.rpm
+rpm -ivh kmod-atl2-1.0.40.4-el5.ceeentos.1.i686
* After that, using system-config-network to add a new profile
+[root@newserver home]#system-config-network
Or you can use the Network in the grapphic GNOME

2. Install from source
* Download the source L2-linux-driver-new.rar
* Unzip this file
* Step& Step Compile this source
[root@newserver]#cd L2_linux-driver-new/src
[root@newserver]#make install
Here , maybe you will meet some errors like this

In file included from /usr/local/src/l2-linux-v1.0.40.4
from /usr/local/src/l2-linux-v1.0.40.4/src/at_main.c:28:
/usr/local/src/l2-linux-v1.0.40.4/src/kcompat.h:1084:
error: redefinition of typedef 'irq_handler_t'
include/linux/interrupt.h:67: error: previous declaration
of 'irq_handler_t' was here

Howto overcome this dummy error.
Simple comment the code in kcompat.h like
/*
typedef irqreturn_t (*irq_handler_t)(int, void*, struct pt_regs *);
*/

Then that will be okey.

I prefer the first way , install from package .
It seems easier and not stuck in so many troubles.
Now I can use internet.And I decide to make this topic,
just to share my google results.
Hope that help you.

How to mount ntfs driver on Linux

2009-09-30T06:13:00.000-07:00

Although there are so many guides on Google about this topic.
"How to mount ntfs driver on Linux " .
I find it rather difficult for new comer to work with it.
This is the easiet way to get your purpose , just a small tip.

There are two ways you can do this jobs.
1. Using yum.
You can see http://wiki.centos.org/TipsAndTricks/NTFS
2. Install package.( I prefer it better)
* Get the package here.
http://sourceforge.net/projects/linux-ntfs/files/
Choose the package suitable for your Linux version .
[root@server home]#uname -a
2.6.18-92.el5xen.
So I get this file kernel-module-ntfs-2.6.18-92.el5-2.1.27-0.rr.10.11.i686.rpm
* Install this package rpm
[root@serverhome]rpm -ivh kernel-module-ntfs-2.6.18-92.el5-2.1.27-0.rr.10.11.i686.rpm
* Easily mount .
[root@serverhome]mount -t ntfs /dev/sda5 /media/WORKING
* After that , you can put it in /etc/fstab for automatically mount when startup
/dev/sda5 /media/WORKING ntfs rw 0 0

Hope you love this tip.