『AskWan』

“革命尚未成功同志仍需努力”

Sun, 14 Mar 2010 14:07:08 +0000

今年的“两会”就要开完了，再听听国父的讲话……

“革命尚未成功同志仍需努力”
“Revolution does not succeed yet, comrades still need to struggle！”
------国父孙中山

configuration oracle data guard for testing

Thu, 11 Mar 2010 06:39:41 +0000

OS: CentOS 5.4 x86_64
Oracle: Oracle 10.2.0.1 64bit for linux

题外话：有些东西长时间不接触，而理解又不深刻的话，慢慢就淡忘了。。。
这个笔记是边参考文档oracle 10g《Data Guard Concepts and Administration》，边验证记录下的
仅供参考

单机环境配置oracle 10g dataguard (physical standby)
IP：192.168.0.21
预期规划如下：

主库：
db_name=dg1
sid=dg1
db_unique_name=primary
network_service_name=primary_21

ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
数据文件路径=/u01/oracle/oradata/dg1
归档日志路径=/u01/oracle/oradata/dg1/archive
警告跟踪等日志文件路径=/u01/oracle/admin/dg1/

从库：
dg_name=dg1
sid=standby
db_unique_name=standby
network_service_name=standby_21

ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
数据文件路径=/u01/oracle/oradata/standby
归档日志路径=/u01/oracle/oradata/standby/archive
警告跟踪等日志文件路径=/u01/oracle/admin/standby

1 准备工作

      安装完oracle软件后，创建主库

1.1 登录主库，将主库置于归档模式下：

SQL> select * from v$version;

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

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/dg1/archive
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

打开force logging

SQL>alter database force logging;

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

DBID LOG_MODE FOR
---------- ------------ ---
1694483490 ARCHIVELOG YES

1.2 建立密码文件

$ orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwdg1 password=askwan.com entries=8
$ orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwstandby password=askwan.com entries=8

1.3 为从库建立相应目录

$ mkdir /u01/oracle/oradata/standby
$ mkdir /u01/oracle/admin/standby/
$ mkdir /u01/oracle/admin/standby/{a,b,c,dp,u}dump
$ mkdir /u01/oracle/admin/standby/pfile

1.4 配置standby redo log

SQL> alter database add standby logfile
        group 4 ('/u01/oracle/oradata/dg1/standby_redo04.log') size 50m,
        group 5 ('/u01/oracle/oradata/dg1/standby_redo05.log') size 50m,
        group 6 ('/u01/oracle/oradata/dg1/standby_redo06.log') size 50m,
        group 7 ('/u01/oracle/oradata/dg1/standby_redo07.log') size 50m;

Database altered.

2 配置监听LISTENER和网络服务名NETWORK SERVICE NAME

2.1 配置 listener.ora

#Network Configuration File: #/u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = dg1)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
    )
    (SID_DESC =
      (SID_NAME = standby)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

2.2 配置网络服务器名

# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY_21 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = primary)
    )
  )

STANDBY_21 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

2.3 启动监听

$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2010 11:10:27

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.21)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                10-MAR-2010 11:10:27
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1" has 1 instance(s).
  Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3 配置主库初始化参数文件

SQL> create pfile from spfile;

File created.

在/u01/oracle/product/10.2.0/db_1/dbs路径下编辑主库刚生成的pfile文件initdg1.ora

3.1 配置主库参数文件initdg1.ora
未编辑前：

编辑后：

dg1.__db_cache_size=134217728
dg1.__java_pool_size=4194304
dg1.__large_pool_size=4194304
dg1.__shared_pool_size=67108864
dg1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/dg1/adump'
*.background_dump_dest='/u01/oracle/admin/dg1/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/dg1/control01.ctl','/u01/oracle/oradata/dg1/control02.ctl','/u01/oracle/oradata/dg1/control03.c
tl'
*.core_dump_dest='/u01/oracle/admin/dg1/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/dg1/udump'
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
DB_FILE_NAME_CONVERT='standby','dg1'
LOG_FILE_NAME_CONVERT='standby','dg1'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/dg1/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2='SERVICE=standby_21 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=standby_21
FAL_CLIENT=primary_21

3.2 配置从库初始化参数文件initstandby.ora
从库文件则依据主库文件修改，修改后：

standby.__db_cache_size=134217728
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__shared_pool_size=67108864
standby.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/standby/adump'
*.background_dump_dest='/u01/oracle/admin/standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/standby/control01.ctl','/u01/oracle/oradata/standby/control02.ctl','/u01/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/standby/udump'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
DB_FILE_NAME_CONVERT='dg1','standby'
LOG_FILE_NAME_CONVERT='dg1','standby'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/standby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary_21 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_CLIENT=standby_21
FAL_SERVER=primary_21

4 备份主库数据文件到从库

SQL> alter database begin backup;

Database altered.

$ cp -frp /u01/oracle/oradata/dg1/* /u01/oracle/oradata/standby/
$ rm -fr /u01/oracle/oradata/standby/control0*.ctl

建立从库控制文件

SQL> alter database create standby controlfile as '/u01/oracle/oradata/standby/control01.ctl';

Database altered.

SQL> alter database end backup;

Database altered.

$ cp /u01/oracle/oradata/standby/control01.ctl /u01/oracle/oradata/standby/control02.ctl

$ cp /u01/oracle/oradata/standby/control01.ctl /u01/oracle/oradata/standby/control03.ctl

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

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers                2170880 bytes
Database mounted.
Database opened.

5 数据库启动
5.1 从库打开redo apply

$ export ORACLE_SID=standby

SQL>startup pfile='/u01/oracle/product/10.2.0/db_1/dbs/initstandby.ora' nomount
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers                2170880 bytes
Database mounted.

SQL>alter database mount standby database

SQL> alter database recover managed standby database disconnect from session;

主库：

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
4

从库：

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
4

切换操作不做了

参考文档:《Data Guard Concepts and Administration》
Tags - oracle , dataguard

A friend companys Linux server ext3 filesystem becomes read only

Mon, 08 Mar 2010 03:12:35 +0000

System has been restarted everything seemed normal, the application starts normally! View the system boot log, there is no exception, the specific reasons for this situation also unknown now! It seems need to continue to observe.

However, open system, found that swap partition 0

Swap: 0k total, 0k used, 0k free, 300496k cached

Is it did not installed the system swap partition?

[root@youke2 log]# cat /proc/swaps

output Nothing, there is no swap partition indeed !
this System has 4G mem, so additional 8G swap space manually

[root@youke2 log]# dd if=/dev/zero of=/tmp/swapfree bs=1024K count=8192
8192+0 records in 8192+0 records out 8589934592 bytes (8.6 GB) copied, 111.247 seconds, 77.2 MB/s

[root@youke2 log]# mkswap /tmp/swapfree
Setting up swapspace version 1, size = 8589930 kB

[root@youke2 log]# swapon /tmp/swapfree

Re-examine swap partition:

[root@youke2 log]# cat /proc/swaps
Filename Type Size Used Priority
/tmp/swapfree file 8388600 0 -1

USE top TO CHECK

Tasks: 145 total,   1 running, 144 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.2%sy,  0.0%ni, 94.4%id,  5.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4138172k total,  3834908k used,   303264k free,    28220k buffers
Swap:  8388600k total,        0k used,  8388600k free,  3605544k cached

OK! write this to fstab file,so it can valid at system start time !

/tmp/swapfree swap swap defaults 0 0

a simple oracle rman backup script

Fri, 05 Mar 2010 08:17:22 +0000

Note:this backup script is suitable for db size < 200G,
and for backup file safety ,may be you should use an independent disk to store backup files

1 configuration target database to archive log mode

2 configuration or check log_archive_dest and db_recovery_file_dest or log_archive_dest_n

3 write a rman script

oracle@dbtest:~$ vim backup.rman

run{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/orabak/ctfile_%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/opt/oracle/orabak/DBFULL_%U';
BACKUP DATABASE SKIP INACCESSIBLE PLUS ARCHIVELOG FILESPERSET 20 DELETE ALL INPUT;
RELEASE CHANNEL C1;
}

4 write backup shell script

oracle@dbtest:~$ vim backup.sh

#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=capitalv
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/bin:/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH
rman TARGET / log='/opt/oracle/orabak/rman.log' cmdfile='/opt/oracle/backup.rman'

5 set cronjob for user oracle

30 02 * * 0,3 bash /opt/oracle/backup.sh

《蚁族》

Thu, 04 Mar 2010 06:27:07 +0000

how to create a rman catalog

Tue, 02 Mar 2010 04:07:06 +0000

SQL> create tablespace rmantest datafile '/u01/oracle/oradata/askwan/rmantest01.dbf' size 100M autoextend on ;

Tablespace created.

SQL> create user rman identified by rman;

User created.

SQL> alter user rman default tablespace rmantest temporary tablespace temp;

User altered.

SQL> alter user rman quota unlimited on rmantest;

User altered.

SQL> grant recovery_catalog_owner,connect,resource to rman;

Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@oracle ~]$ rman catalog rman/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 2 12:10:44 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

RMAN>
RMAN> create catalog tablespace rmantest;

recovery catalog created

create rman catalog done!
Tags - rman , catalog

log_archive_dest and db_recovery_file_dest

Tue, 02 Mar 2010 03:45:34 +0000

默认安装情况下，oracle归档路径可能只有一个，即$ORACLE_HOME/flash_recovery_area这个位置：

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/oracle/product/10.
                                                 2.0/db_1/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

而其他归档日志参数默认值都为空：
SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0

使用参数 log_archive_dest 修改归档日志路径
SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;
alter system set log_archive_dest='/opt/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

报db_recovery_file_dest参数不能和LOG_ARCHIVE_DEST还有 LOG_ARCHIVE_DEST_N同时使用，先将
db_recovery_file_dest参数置空：

SQL> alter system set db_recovery_file_dest='' scope=both;

System altered.

SQL> alter system set log_archive_dest='/opt/oracle/archive' scope=both;

System altered.

归档日志参数的格式：
SQL> show parameter log_archive_format;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf

SQL> alter system switch logfile;

System altered.

查看归档日志：
SQL> host ls -al /opt/oracle/archive/
total 1156
drwxr-xr-x  2 oracle oinstall    4096 2010-03-02 11:28 .
drwxrwxr-x 25 oracle oinstall    4096 2010-03-02 11:14 ..
-rw-r-----  1 oracle oinstall 1170432 2010-03-02 11:28 1_34_708277817.dbf

oracle建议归档日志使用log_archive_dest_n代替log_archive_dest,故一般将log_archive_dest和db_recovery_file_dest置空，然后设置log_archive_dest_n .

恭祝2010年新春快乐

Tue, 09 Feb 2010 01:40:46 +0000

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

configuration oracle 10g archive log mode

Fri, 15 Jan 2010 07:18:45 +0000

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

Tags - oracle , archive , archivelog , mode

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

Mon, 28 Dec 2009 04:26:27 +0000

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 01:42:15 +0000

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 03:24:12 +0000

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 01:55:55 +0000

"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 00:48:08 +0000

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 02:01:50 +0000

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

『AskWan』

“革命尚未成功 同志仍需努力”

configuration oracle data guard for testing

A friend companys Linux server ext3 filesystem becomes read only

a simple oracle rman backup script

《蚁族》

how to create a rman catalog

log_archive_dest and db_recovery_file_dest

恭祝2010年新春快乐

configuration oracle 10g archive log mode

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

use Pgpool-II and HeartBeat realize high availability of postgresql

Postgresql Warm Standby Testing

use pgFouine analyse postgresql slow queries

use perl to get timestamp

use DBD::Pg to connect PostgreSQL database

“革命尚未成功同志仍需努力”