流年飘飘『AskWan』

恭祝2010年新春快乐

Tue, 09 Feb 2010 09:40:46 +0800

春节即将到来，提前预祝大家
春节好！

configuration oracle 10g archive log mode

Fri, 15 Jan 2010 15:18:45 +0800

VERSION:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Check current database archive log mode

SQL> select dbid,name,log_mode from v$database;

      DBID NAME      LOG_MODE
---------- --------- ------------
2406511032 CAPITALV  NOARCHIVELOG

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Current log sequence           34

shutdown database clear

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

startup database to mount status:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             142607832 bytes
Database Buffers          134217728 bytes
Redo Buffers                6303744 bytes
Database mounted.

open database archive log mode :

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34

stop archive log mode :

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             142607832 bytes
Database Buffers          134217728 bytes
Redo Buffers                6303744 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Current log sequence           34

oracle 10g em and isqlplus unrecognizable chinese characters on debian lenny

Mon, 28 Dec 2009 12:26:27 +0800

OS:Debian Lenny 503 amd64
Oracle 10g R2

emctl stop dbconsole
isqlplusctl stop

Quotation

cd $ORACLE_HOME/jdk/jre/lib
mv font.properties font.properties_bak20091229
cp -frp font.properties.zh_CN.Redhat font.properties

vim font.properties
in the last line :
change

Quotation

filename.-misc-zysong18030-medium-r-normal--*-%d-*-*-c-*-iso10646-1=/usr/share/fonts/zh_CN/TrueType/zysong.ttf

Quotation

filename.-misc-zysong18030-medium-r-normal--*-%d-*-*-c-*-iso10646-1=/usr/share/fonts/zh_CN/TrueType/simhei.ttf

simhei.ttf copied from windows (C:\WINDOWS\Fonts )

Quotation

cp -f font.properties $ORACLE_HOME/jre/1.4.2/lib
cp -f font.properties $ORACLE_HOME/javavm/lib/ojvmfonts

Quotation

rm -fr $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/zhs/*.gif
rm -fr $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/isqlplus/isqlplus/cabo/images/cache/*.gif

emctl start dbconsole
isqlplusctl start

-------EOF--------

use Pgpool-II and HeartBeat realize high availability of postgresql

Fri, 04 Dec 2009 09:42:15 +0800

Pgpool-II:
pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client.
http://pgpool.projects.postgresql.org/
it can used for Connection Pooling,Replication,Load Balance,Limiting Exceeding Connections and Parallel Query

that Heartbeat is a daemon that provides cluster infrastructure (communication and membership) services to its clients.
http://www.linux-ha.org/

I have archived this :

May be I have to do more testing :)
Tags - pgpool-ii , heartbeat , realize , high , availability , postgresql , pgpool2

Postgresql Warm Standby Testing

Thu, 03 Dec 2009 11:24:12 +0800

OS: Debian5.0  lenny x86_64
Postgresql:8.3.8
Master :192.168.0.121
Warm  Standby:192.168.0.122

1.  install postgresql on both master and warm standby server.

#cd /usr/local/src/postgresql-8.3.8/
#./configure –prefix=/usr/local/pgsql
#make
#make install
#mkdir /usr/local/pgsql/data
#chown postgres /usr/local/pgsql/data
#su - postgres
$/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

2. Config passwordless ssh authentication use user postgres
Please refer to http://www.petefreitag.com/item/532.cfm

3. Install pg_standby on both both master and warm standby server.

#cd /usr/local/src/postgresql-8.3.8/contrib/pg_standby
#make
#make install

4. Config postgresql

In master server :

Quotation

archive_mode = on
archive_command = 'rsync -arv %p postgres@192.168.0.122:/wal_archives/%f'
archive_timeout = 1200

in warm standby server :

#mkdir /wal_archives
#chown postgres /wal_archives/

start master server :

pg_ctl -D /usr/local/pgsql/data/ start -l /usr/local/pgsql/data/logfile

5.  Dump all production data to master server  .

You can check the warm standby server ‘s /wal_archives directory , whether there is archive logs  from master server located on .

6.  Login to master  server as postgres user.
Create a script to do a base backup:

Quotation

#!/bin/bash
PGDATA=/usr/local/pgsql/data
psql -c "CHECKPOINT;"
psql -c "SELECT pg_start_backup('BASEBACKUP');"
tar -C /usr/local/pgsql/ -zcf - data |ssh 192.168.0.122 "tar -C /usr/local/pgsql/ -zxf -"
psql -c "select pg_stop_backup();"
echo "BASEBACKUP compete!"

7. Login to warm standby server as postgresql user.
Create a script to do some clean and configuration

Quotation

#!/bin/bash
PG_HOME=/usr/local/pgsql
PGDATA=/usr/local/pgsql/data
trigger_file=/tmp/pgsql.trigger
pg_standby=$PG_HOME/bin/pg_standby
PG_ARCHIVES=/wal_archives
  rm -f $PGDATA/recovery.*
  rm -f $PGDATA/logfile
  rm -f $PGDATA/postmaster.pid
  rm -f $PGDATA/pg_xlog/0*
  rm -f $PGDATA/pg_xlog/archive_status/0*
  sed -i '/^archive_/s/^/#/g' $PGDATA/postgresql.conf
  echo "restore_command = '$pg_standby -l -d -s 2 -t $trigger_file $PG_ARCHIVES %f %p %r 2>>/tmp/standby.log'" > $PGDATA/recovery.co
nf
  chown postgres.postgres $PGDATA/recovery.conf
  echo "Init completed! now start warm standby server."

Note:PostgreSQL 8.4 provides the recovery_end_command option.so,in recovery.conf add recovery_end_command=’rm –f /tmp/pgsql.trigger ’

pg_standby supports creation of a "warm standby" database server. It is designed to be a production-ready program, as well as a customizable template should you require specific modifications.
More about pg_standby,refer to http://www.postgresql.org/docs/current/static/pgstandby.html
http://www.enterprisedb.com/docs/en/8.4/pg/pgstandby.html

8. Start warm standby server

pg_ctl -D /usr/local/pgsql/data start -l /usr/local/pgsql/data/logfile

9. check warm standby postgresql logfile:

Quotation

LOG:  database system was interrupted; last known up at 2009-12-03 11:44:37 CST
LOG:  starting archive recovery
LOG:  restore_command = '/usr/local/pgsql/bin/pg_standby -l -d -s 2 -t /tmp/pgsql.trigger /wal_archives %f %p %r 2>>/tmp/standby.log
'
LOG:  restored log file "000000010000000000000014.00000020.backup" from archive
LOG:  restored log file "000000010000000000000014" from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 0/14000068
FATAL:  the database system is starting up
LOG:  restored log file "000000010000000000000015" from archive
LOG:  restored log file "000000010000000000000016" from archive
LOG:  restored log file "000000010000000000000017" from archive
LOG:  restored log file "000000010000000000000018" from archive
LOG:  restored log file "000000010000000000000019" from archive
LOG:  restored log file "00000001000000000000001A" from archive
LOG:  restored log file "00000001000000000000001B" from archive
LOG:  restored log file "00000001000000000000001C" from archive
LOG:  restored log file "00000001000000000000001D" from archive
LOG:  restored log file "00000001000000000000001E" from archive
LOG:  restored log file "00000001000000000000001F" from archive
LOG:  restored log file "000000010000000000000020" from archive
LOG:  restored log file "000000010000000000000021" from archive
……

Check standby logfile:

Quotation

Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 00000001.history
WAL file path           : /wal_archives/00000001.history
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/00000001.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000014.00000020.backup
WAL file path           : /wal_archives/000000010000000000000014.00000020.backup
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000014.00000020.backup" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000014
WAL file path           : /wal_archives/000000010000000000000014
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000014" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK

Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000015
WAL file path           : /wal_archives/000000010000000000000015
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000015" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000010000000000000014 and later
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000016
WAL file path           : /wal_archives/000000010000000000000016
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds

Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000016" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000010000000000000014 and later
running restore         : OK
......
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
......

10. failover
In warm standby server
touch /tmp/ pgsql.trigger
Tags - postgresql , warm , standby , pitr

use pgFouine analyse postgresql slow queries

Wed, 25 Nov 2009 09:55:55 +0800

"pgFouine is a PostgreSQL log analyzer used to generate detailed reports from a PostgreSQL log file. pgFouine can help you to determine which queries you should optimize to speed up your PostgreSQL based application."

you can use syslog or stderr

for example :use syslog

1. edit your /etc/syslog.conf

Quotation

local0.* -/var/log/pgsql

Quotation

*.info;mail.none;authpriv.none;cron.none;local0.none /var/log/messages

2. edit postgresl.conf

Quotation

log_destination = 'syslog'
silent_mode = on

log_min_duration_statement = 0
#set to 0 log all
#set to -1 ,disable query logging
#set to X(positive integral) ,means to log queries slower than X milliseconds:

log_duration = off
log_statement = 'all'

3.download pgFouine
http://pgfouine.projects.postgresql.org/index.html

than restart syslogd and postgresql

4. analyse log
./pgfouine.php -file /var/log/postgresql/pgsql > askwan.com.html

use stderr please refer to http://gkoenig.wordpress.com/2009/02/19/pgfouine-dive-into-postgres-log/
Tags - use , pgfouine , analyse , postgresql , slow , queries

use perl to get timestamp

Fri, 30 Oct 2009 08:48:08 +0800

in different  context have different value
eg:
saclar context
print scalar (localtime);

then output :
Thu Oct 29 18:50:01 2009

but in list context ;
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime(time);
$sec    0 ~ 59
$min    0 ~ 59
$hour  0 ~ 23
$mday   1~31
$mon     0 ~ 11
$year   after 1900,it passed years
$wday    0 ~ 6
$yday    0 ~365
$isdst   insignificant for me

can use this to get timestamp

output:
2009-10-29 19:2:40

have some defect ablove,use this

output:
2009-10-29 19:02:45

you can also use POSIX functions:
eg

output:
2009-10-29 11:10:44
2009-10-29 19:10:44

use DBD::Pg to connect PostgreSQL database

Tue, 20 Oct 2009 10:01:50 +0800

first, install DBI and DBD::Pg module

come next two example I have writen for testing:

example one:

example two:
output :

datname             blks_read           blks_hit            
------------------------------------------------------------
template1           0                   0                   
template0           0                   0                   
postgres            136                 5955                
askwan              124                 7396

Tags - ' , dbd:pg'postgresql

compile net-snmp on debian lenny

Wed, 23 Sep 2009 16:16:08 +0800

net-snmp
下载地址：http://ncu.dl.sourceforge.net/project/net-snmp/net-snmp/5.4.2.1/net-snmp-5.4.2.1.tar.gz
解压然后编译，编译参数

./configure --prefix=/usr/local/net-snmp/ --with-sys-contact="askwan@askwan.com" --with-sys-location="pudong shanghai China" --enable-shared --with-default-snmp-version=2 --enable-mfd-rewrites --with-mib-modules="misc/ipfwacc mibII host disman/event-mib ucd_snmp agent_mibs agentx notification target utilities ucd-snmp/diskio tunnel mibII/mta_sendmail" --with-sysconfdir="/usr/local/net-snmp/etc/"

如编译报错：

Quotation

/usr/bin/ld: cannot find -lperl
collect2: ld returned 1 exit status

这是缺少libperl-dev开发库,安装上

apt-get install libperl-dev

然后make ;make install
可以用snmpconf配置snmpd.conf，也可用例子文件

cp EXAMPLE.conf /usr/local/net-snmp/etc/snmpd.conf

然后根据需要修改此文件

com2sec local     localhost       askwan
com2sec mynetwork 192.168.0.0/24      askwan

group MyRWGroup v1         local
group MyRWGroup v2c        local
group MyRWGroup usm        local
group MyROGroup v1         mynetwork
group MyROGroup v2c        mynetwork
group MyROGroup usm        mynetwork

view all    included  .1                               80
access MyROGroup ""      any       noauth    exact  all    none   none
access MyRWGroup ""      any       noauth    exact  all    all    none

syslocation shanghai pu dong
syscontact Me askwan@askwan.com

启动

/usr/local/net-snmp/sbin/snmpd -Lsd -Lf /dev/null -p /var/run/snmpd.pid -a -c /usr/local/net-snmp/etc/snmpd.conf

测试

snmpwalk -c askwan -v2c 192.168.0.5 if

Quotation

IF-MIB::ifIndex.1 = INTEGER: 1
IF-MIB::ifIndex.2 = INTEGER: 2
IF-MIB::ifDescr.1 = STRING: lo
IF-MIB::ifDescr.2 = STRING: eth0
IF-MIB::ifType.1 = INTEGER: softwareLoopback(24)
IF-MIB::ifType.2 = INTEGER: ethernetCsmacd(6)
....

---------END------------

Tags - debian , net-snmp

Mysql replication Error Slave_SQL_Running:No

Tue, 15 Sep 2009 13:20:23 +0800

mysql> show slave status\G
......
Slave_IO_Running: Yes
Slave_SQL_Running: No
......

首先定位同步不成功的原因
查数据库日志

若是从主机重启，事物回滚，则

mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

其他网络故障，则
go to master :


mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000004 | 244274056 | cada_w       |                  | 
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

go to slave ，manual replication

mysql>salve stop
mysql> change master to
> master_host='192.168.0.50',
> master_user='askwan',
> master_password='askwan.com',
> master_port=3306,
> master_log_file=localhost-bin.000004',
> master_log_pos=244274056 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)

then done !

mysql> show slave status\G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

another example:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.100.88
                Master_User: slave
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000097
        Read_Master_Log_Pos: 84519125
             Relay_Log_File: HK1-relay-bin.001040
              Relay_Log_Pos: 8880267
      Relay_Master_Log_File: mysql-bin.000097
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: askwan.com
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
               Skip_Counter: 0
      Exec_Master_Log_Pos: 67663733
            Relay_Log_Space: 30399576
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

first stop slave replication:

mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)

change master host info:

mysql> change master to master_host='192.168.100.88',master_user='slave',master_password='askwan.com',master_port=3306,master_log_file='mysql-bin.000097',master_log_pos=67663733;

last step ,start replication:

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

check all right or not :

mysql> show slave status\G

.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......

Tags - mysql , replication , error