Geek Stuff Matters

2011-08-02T09:20:00.000-07:00

create or replace function get_token(
the_list varchar2,
the_index number,
delim varchar2 := ','
)
return varchar2
is
start_pos number;
end_pos number;
begin
if the_index = 1 then
start_pos := 1;
else
start_pos := instr(the_list, delim, 1, the_index - 1);
if start_pos = 0 then
return null;
else
start_pos := start_pos + length(delim);
end if;
end if;

end_pos := instr(the_list, delim, start_pos, 1);

if end_pos = 0 then
return substr(the_list, start_pos);
else
return substr(the_list, start_pos, end_pos - start_pos);
end if;

end get_token;
/

select
get_token('foo,bar,baz',1), -- 'foo'
get_token('foo,bar,baz',3), -- 'baz'
--
get_token('a,,b',2), -- '' (null)
get_token('a,,b',3), -- 'b'
--
get_token('a|b|c',2,'|'), -- 'b'
get_token('a|b|c',4,'|') -- '' (null)
from
dual
/

Oracle logging message to a table.

2011-02-09T12:08:00.000-08:00

create table log_test (message varchar2(255));
create table log_results (resultvalue number);

CREATE OR REPLACE PROCEDURE LOG_MESSAGE (MESSAGE_IN IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOG_TEST (MESSAGE) VALUES (MESSAGE_IN);
COMMIT;
END;
/

create or replace
PROCEDURE CREATE_ERROR (VALUE_IN IN NUMBER) IS
l_x NUMBER;
BEGIN
LOG_MESSAGE( 'Attempting to divide by ' || to_char(VALUE_IN) );
l_x := 10 / VALUE_IN;
INSERT INTO log_results (resultvalue) values (l_x);
LOG_MESSAGE( 'Finished to divide by ' || to_char(VALUE_IN) );
COMMIT;
END;
/

select * from log_test;

SQL> select * from log_test;

no rows selected

SQL> select * from log_results;

no rows selected

SQL> exec create_error(2);

PL/SQL procedure successfully completed.

SQL> exec create_error(1);

PL/SQL procedure successfully completed.

SQL> exec create_error(0);
BEGIN create_error(0); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TPM.CREATE_ERROR", line 5
ORA-06512: at line 1

SQL> exec create_error(4);

PL/SQL procedure successfully completed.

SQL> select * from log_results;

RESULTVALUE
-----------
5
10
2.5

SQL> select * from log_test;

MESSAGE
--------------------------------------------------------------------------------
Attempting to divide by 2
Finished to divide by 2
Attempting to divide by 1
Finished to divide by 1
Attempting to divide by 0
Attempting to divide by 4
Finished to divide by 4

7 rows selected.

2010-10-20T20:42:00.001-07:00

set your ORACLE_SID to an ASM instance
using asmcmd locate the directory that contains the archive log files. for example..

ASMCMD> cd +POC2FLASH/POC2DWH/ARCHIVELOG
using 'rm' command you can delete the directory.

But don't forget to remove the archive log details from the control file or the RMAN catalog
For this you need to connect to the target database using rman
rman target sys/ nocatalog
rman>crosscheck archivelog all
rman>delete expired archivelog all

DHCP hostname.

2010-10-18T11:09:00.000-07:00

Set the hostname: `# hostname `
Add ‘HOSTNAME=’ to ‘/etc/sysconfig/network’ (makes the change permanent).
Add ‘DHCP_HOSTNAME=’ to ‘/etc/sysconfig/network-scripts/ifcfg-eth0′
Restart the networking service: `# service network restart`

Mount entire driver image

2010-09-07T11:29:00.000-07:00

if you dump your entire Hard driver using DD. you can view the content of the Image using the following comment.

assume you have create a hd image in /home/backup/hd.dump

1). fdisk /home/backup/hd.dump
You must set cylinders.
You can do this from the extra functions menu.

Disk /mnt/storage/disk_image: 0 MB, 0 bytes
255 heads, 63 sectors/track, 0 cylinders, total 0 sectors
Units = sectors of 1 * 512 = 512 bytes
Disk identifier: 0x41172ba5

Device Boot Start End Blocks Id System
/home/backup/hd.dump1 63 64259 32098+ de Dell Utility
/home/backup/hd.dump2 * 64260 78108029 39021885 7 HPFS/NTFS
Partition 2 has different physical/logical endings:
phys=(1023, 254, 63) logical=(4861, 254, 63)

2). then take the start of the partition that you want to edit 64260 in this case and multiply it by 512 ie 512*64260=32901120

then mount like this:

3). mount -o loop,offset=32901120 -t auto /home/backup/hd.dump /mnt/images ( assume the /mnt/images exists).

change HP server boot order remotely using iLo

2010-08-31T12:11:00.000-07:00

1). SSH to HP server
2). cd system1/bootconfig1
3). show bootsource1 #please write down the bootorder number, I use 1 in this case
4). set bootsource1 bootorder=1 'this will give you the descriptive msg'
5). set bootsource$b bootorder=$p 'replace $b and $p to the device and boot order you want to set'

6). voila. reboot the machine.

add temp table space

2010-08-13T20:56:00.000-07:00

alter tablespace temp add tempfile size 128m autoextend on next 32m maxsize 8g;

Oracle turn on Archive mode

2010-08-07T23:48:00.000-07:00

his is a two-step process. email if you have questions: fayoubi@gmail.com

STEP ONE : Modify your initSID.ora

Modify your initSID.ora to include the following three lines:

log_archive_start = true
log_archive_dest = /mount_point/oradata/SID/ARCHIVE/arch
log_archive_format = _%s.arc

log_archive_start: This parameter specifies whether archiving should be started automatically when the database is started.

log_archive_dest: This value specifies the default location of the file to be used when archiving redo log files.

log_archive_format: This parameter is used to specify the default file name format for the archive log files, this value is appended to the log_archive_dest value ( %s is the log sequence number ).

STEP TWO : Starting archive logging

The final step to starting archive logging, involves an Oracle SQL*DBA command which is run when the database is mounted but not open.

With the database shut down, use one of the many DBA tools available to manually start the database ( I still prefer to use SQL*DBA ) and execute the following sequence of commands:

startup mount exclusive
connect internal
alter database archivelog;
alter database open;

Now check to ensure archive logging is running. Whilst still connected internal, execute the command archive log list as shown below:

SQLDBA> archive log list
Database log mode ARCHIVELOG
Automatic archival ENABLED
Archive destination /mount_point/oradata/SID/ARCHIVE/arch
Oldest online log sequence 2
Current log sequence 3
SQLDBA>

how to fix ORA-00600: internal error code, arguments: [kcratr1_lastbwr]

2010-07-07T15:21:00.000-07:00

if your Oracle gives you ORA-01033: ORACLE initialization or shutdown in progress when your app is trying to connect Oracle. (or DBA account with ORA-00600: internal error code, arguments: [kcratr1_lastbwr])

below is the commands to fix it.

login as sysdba;

SQL> shutdown;
SQL> startup mount;
SQL> recover database;
SQL> alter database open;

it should fix the problem.

How to fix 'No converter found for 'org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer"

2010-05-26T19:49:00.000-07:00

if you are seeing 'No converter found for 'org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer' in your log when you using the spring+hibernate+dwr combination,

to solve this problem,

change the converter='bean' to converter="hibernate3" in the convert tag.

for example.

to

launch java program with remote debugging capability.

2010-04-21T10:38:00.000-07:00

export javadg="java -Xdebug -Xrunjdwp:transport=dt_socket,address=8000,server=y,suspend=n"

Move a directory over the network

2010-03-08T14:02:00.000-08:00

tar -c -f - -p --numeric-owner $source_dir_1 | \
ssh $destination_host "cd $destination_location ; tar -x -p"

Oracle drop all tables.

2009-12-09T13:32:00.001-08:00

1). get a list of the tables and construct the drop statement.
select 'drop table '|| object_name || ' cascade constraint;' from user_objects where object_type = 'TABLE';

2). run the result.

set oracle date format

2009-11-24T09:54:00.000-08:00

for lazy people like me don't want to use to_date() function.
we can preset the dateformat for the session.

alter session set NLS_DATE_FORMAT='';

dblink link

2009-11-23T14:19:00.000-08:00

1) make sure you have create database link privileges.
login as dba and "grant create database link to $user;

2).
create database link
$dblinkname connect to $schemaname identified by $schemapassword using '$oracle_sid';
$dblinkname is the identifier for this dblink.
$schemaname is the schema you want to connect
$schemapassword is the normal password for the $schemaname.
$oracle_sid is the connection string. eg host/sid

to test.

select * from dual@$dblinkname;

use rman to delete archive log

2009-11-20T11:39:00.000-08:00

if you are getting

Enter password: ********
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

this error message when you login to your Oracle

you can use rman to delete some log.

oracle $ rman target /
rman> delete noprompt archivelog until time 'sysdate - 2' ;
rman> crosscheck backupset;

This will delete the archive log older than 2 days.

Liferay add user to a community programmtically

2009-10-06T15:35:00.001-07:00

UserLocalServiceUtil.addGroupUsers(groupId, new String[]{userId});

Web Design Stuff

2009-09-28T14:25:00.000-07:00

some CSS note:
to hide/show a div tag: display : hide/show;

to pop up a div tag
position : absolute;

Hi Speed copy Oracle data from one machine to another machine

2009-08-19T13:37:00.000-07:00

[PREREQUITE]
SSH SETUP to connect without password
[TARGET MACHINE]
mknod /tmp/inpipe.dmp p
imp username/password file=inpipe.dmp &

[SOURCE MACHINE]
mknod /tmp/outpipe p
ssh -C user@target 'cat > /tmp/inpipe.dmp' < /tmp/outpipe &
exp username/password file=/tmp/outpipe

use exp/imp to copy the data from one db to another

2009-08-06T14:53:00.000-07:00

To export Table(s)
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

To export whole schema
exp scott/tiger file=emp.dmp

To import
imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Spring + Quartz + Hibernate

2009-06-08T09:47:00.000-07:00

If you are getting no session error due to the hibernate session is not available to you.
here is the solution.

create a bean using thetranssactionProxyFactoryBean
[bean id="cpkMailingList" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"]
[property name="target"]
[bean class="com.company.manufacturing.cron.CpkMailingList"]
[property name="emailTemplate" value="cpkemail.vm" /]
[property name="sendHTMLMailService" ref="sendHTMLMailService" /]
[property name="mailingList" value="bli@email.com"/]
[property name="message" ref="mailMessage" /]
[property name="fpyManager" ref="fpyManager"/]
[property name="productManager" ref="productManager" /]
[property name="appMeasStatManager" ref="appMeasStatManager" /]
[/bean]
[/property]
[property name="transactionAttributes"]
[props]
[prop key="sendMail"]PROPAGATION_REQUIRED[/prop]
[/props]
[/property]
[/bean]

Caching in Java + Spring

2009-05-29T12:40:00.000-07:00

Caching is quite important to boost the application performance. There are many cache libraries in Java world, both commercial and open source. You should choose the existing library instead creating your own library. I see some guys in my previous companies develop their own cache, and it is not good for their project. Instead of spending time on project business features, guy spent much time to create the perfect library and maintain it through the project life cycle. Another case is using the Map and its relative classes for caching, it makes the application could not scalable and it does not provide the flexible caching mechanism the cache should have. Of course, we need to create the cache library if there is no good one in the market, but it is not true. There are many good cache libraries in Java world, one of them is Ehcache (http://ehcache.sourceforge.net/). Ehcache is the excellent library provides to community with high quality, good documentation, simple use and wide adoption by other popular frameworks. It takes development team 5-10 minutes to set up the caching (of course, if you are fresher you must spend more time to understand the caching mechanism), so why you do not use such library instead of creating your own library?

In our application, we use Spring as IoC container, though set up the Ehcache manager is the easy task but we would like to use it with our Spring beans and exposed it as Osgi service. Spring modules is the good library help us easy in configuration. It provides the consistent abstraction caching to our application, hence we can use our cache facade with other caching libraries such as JBoss cache, JCS, OScache without chaging our application code. You could download the Spring Modules at https://springmodules.dev.java.net/.

Here are the following steps to configure Ehcache in Spring application

Configuring caching via ehcache.xml file

[?xml version="1.0" encoding="UTF-8"?]

[ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="ehcache.xsd"]

[!--
Configure it if you have overflowToDisk or diskPersistent enabled for any cache.
java.io.tmpdir - Default temp file path
--]
[diskStore path="java.io.tmpdir" /]

[!--

Mandatory Default Cache configuration. These settings will be applied to caches
created programmtically using CacheManager.add(String cacheName).

The defaultCache has an implicit name "default" which is a reserved cache name.
--]
[defaultCache maxElementsInMemory="10000" eternal="false"
timeToIdleSeconds="120" timeToLiveSeconds="120" overflowToDisk="true"
diskSpoolBufferSizeMB="30" maxElementsOnDisk="10000000"
diskPersistent="false" diskExpiryThreadIntervalSeconds="120"
memoryStoreEvictionPolicy="LRU" /]

[!--

Sample cache named sampleCache1
This cache contains a maximum in memory of 10000 elements, and will expire
an element if it is idle for more than 5 minutes and lives for more than
10 minutes.

If there are more than 10000 elements it will overflow to the
disk cache, which in this configuration will go to wherever java.io.tmp is
defined on your system. On a standard Linux system this will be /tmp"

--]
[cache name="engroupCache" maxElementsInMemory="10000"
maxElementsOnDisk="10000000" eternal="false" overflowToDisk="true"
diskSpoolBufferSizeMB="20" timeToIdleSeconds="300"
timeToLiveSeconds="600" memoryStoreEvictionPolicy="LFU" /]
[/ehcache]

Declarating Ehcache in Spring context

[bean id="cacheManager"
class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean"]
[property name="configLocation"]
[value]classpath:ehcache.xml[/value]
[/property]
[/bean]
[/pre]
[pre lang="xml" xml:space="preserve" xml:lang="xml"]
[bean id="cacheProviderFacade"
class="org.springmodules.cache.provider.ehcache.EhCacheFacade"]
[property name="cacheManager" ref="cacheManager" /]
[/bean]

Programmatic use

Using ehcache is so simple, a unit test to make the test whether configuration is set up correctly and take the simple put/get could explain all.

@RunWith(EngroupClassRunner.class)
@ContextConfiguration(locations = { "/META-INF/spring/cache-context.xml" })
public class CacheTest {
@Autowired
protected CacheProviderFacade cacheProviderFacade;

@Test
public void testCacheConfiguration() {
Assert.assertNotNull(cacheProviderFacade);

EhCacheCachingModel model = new EhCacheCachingModel();
model.setCacheName("engroupCache");

cacheProviderFacade.putInCache("a", model, new Integer(1));
Integer fromCache = (Integer) cacheProviderFacade.getFromCache("a",
model);
Assert.assertEquals(new Integer(1), fromCache);
}
}

Besides of using ehcache to create the session like variable in our application, we would like to use ehcache to cache SQL statement in iBatis and integrate with Terracotta to complete our distributing caching solution in our future project. I will keep the post on further caching usage in future posts. Keep reading!

use maven to generate auto generate schema

2009-05-26T09:20:00.000-07:00

add the following to your pom.xml

change the content inside your srcFile tag and of course convert the [] to the diamond brakets.

[plugin]
[groupId]org.codehaus.mojo[/groupId]
[artifactId]sql-maven-plugin[/artifactId]
[version]1.0[/version]
[configuration]
[driver]${jdbc.driverClassName}[/driver]
[username]${jdbc.username}[/username]
[password]${jdbc.password}[/password]
[url]${jdbc.url}[/url]
[autocommit]true[/autocommit]
[skip]${maven.test.skip}[/skip]
[/configuration]
[executions]
[execution]
[id]create-schema[/id]
[phase]process-test-resources[/phase]
[goals]
[goal]execute[/goal]
[/goals]
[configuration]
[autocommit]true[/autocommit]
[srcFiles]
[srcFile]src/test/resources/${jdbc.groupId}-schema.sql[/srcFile]
[/srcFiles]
[/configuration]
[/execution]
[/executions]
[dependencies]
[dependency]
[groupId]${jdbc.groupId}[/groupId]
[artifactId]${jdbc.artifactId}[/artifactId]
[version]${jdbc.version}[/version]
[/dependency]
[/dependencies]
[/plugin]

Hibernate oneToMany lazy load correctly

2009-05-25T15:02:00.001-07:00

I found lots of programmers are scratching their heads and try to find out why their lazy loading fetch strategy is not working correctly. There aren't many documents or tutorials in the web to tell you how to solve it.

To properly configure the hibernate lazy initialize collection, just follow these steps.

1). POJO Bean
1). override equal() and hashCode(). This is very important. I have seen a lots of programmer think these functions are not useful. without override these function, the Hibernate lazy loading will not work correctly.

2). in your @OneToMany and @ManyToOne relation attribute. indicate these are lazy loading. To do that. add fetch=fetchType.lazy inside the bracket eg. @ManyToOne(fetch=fetchType.lazy)

3). in your web.xml file add the following entities
<filter-mapping >
<filter-name>lazyLoadingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
and
<filter>
<filter-name>lazyLoadingFilter</filter-name>
<filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class>
</filter>

Hibernate enable lazy loading in your web app

2009-05-25T14:51:00.000-07:00

In your web.xml file,

added the following

[filter-mapping]
[filter-name]lazyLoadingFilter[/filter-name>
[url-pattern]/*[/url-pattern]
[/filter-mapping]

and

[filter]
[filter-name]lazyLoadingFilter[/filter-name]
[filter-class]org.springframework.orm.hibernate3.support.OpenSessionInViewFilter[/filter-class]
[/filter]

replace [ and ] to > and <
damn blogspot editor

How to use OR to rewrite SQL Query

2009-05-20T22:38:00.000-07:00

how to optimize the following SQL

select * from sys_user

where user_code = 'bli'

or user_code in

(select grp_code

from sys_grp

where sys_grp.user_code = 'bli')

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 FILTER

2 1 TABLE ACCESS (FULL) OF 'SYS_USER'

3 1 INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE)

Statistics

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

14 recursive calls

4 db block gets

30590 consistent gets

0 physical reads

0 redo size

1723 bytes sent via SQL*Net to client

425 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

the result of this query normally contains one or two rows, but sys_user table contains a lots of entries. How can we drive sys_grp?

the table information

Sql > select count(*) from sys_grp;

count(*)

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

25130

sql> select count(*) from sys_user;

Count(*)

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

15190

optimize:

1) reduce the logical read is the base principle.

we will try to reduce the logical read to increase the sql query.

we can use or to rewrite this sql

select * from sys_user where user_code = 'bli'

union all

select * from sys_user where user_code <> 'bli'

and user_code in (select grp_code from sys_grp

where sys_grp.user_code = 'bli')

Statistics

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

0 recursive calls

0 db block gets

130 consistent gets

0 physical reads

0 redo size

1723 bytes sent via SQL*Net to client

425 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

3 rows processed

Execution Plan

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

0 SELECT STATEMENT Optimizer=RULE

1 0 UNION-ALL

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER'

3 2 INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE)

4 1 NESTED LOOPS

5 4 VIEW OF 'VW_NSO_1'

6 5 SORT (UNIQUE)

7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP'

8 7 INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE)

9 4 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER'

10 9 INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE)

with this query, the logically read reduces to 130. From 30590 to 130 is a huge performance jump

DBMS_STAT tables and backup the stat informaiton

2009-05-19T21:21:00.001-07:00

when we use DBMS_STAT to analyze the DB, we need to save previous analyze data in case of the stat data decrease the system performance.

to create a stat backup.
login to sqlplus

SQL> begin
2 dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'STAT_TABLE');
3 end;
4 /

analyze information
SQL> BEGIN
2 --DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 DBMS_STATS.gather_table_stats(ownname => 'TEST',tabname => 'A');
4 END;
5 /

export analyzed data to stat_table
SQL> BEGIN
2 dbms_stats.export_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /

check how many rows in a table
SQL> select count(*) from stat_table;

delete analyzed data
SQL> BEGIN
2 DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 END;
4 /

import analyzed data
SQL> BEGIN
2 DBMS_STATS.import_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /

Done

Oracle show parameters tips and tricks

2009-05-19T21:13:00.000-07:00

To check Oracle parameters, you can use "show Parameters". Show parameters can be run as fuzzy query.

for instance, if I want to check "db_" parameters, I can use

SQL> show parameter db_;

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_block_buffers integer 0

db_block_checking string FALSE

db_block_checksum string TRUE

db_block_size integer 8192

db_cache_advice string ON

db_cache_size big integer 0

NAME TYPE VALUE

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

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

db_domain string

db_file_multiblock_read_count integer 8

db_file_name_convert string

db_files integer 200

db_flashback_retention_target integer 1440

NAME TYPE VALUE

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

db_keep_cache_size big integer 0

db_name string Ora813

db_recovery_file_dest string /home/oracle/product/10.2.0/flash

_recovery_area

db_recovery_file_dest_size big integer 2G

db_recycle_cache_size big integer 0

db_unique_name string Ora813

db_writer_processes integer 1

dbwr_io_slaves integer 0

rdbms_server_dn string

standby_archive_dest string %ORACLE_HOME%/RDBMS

NAME TYPE VALUE

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

standby_file_management string MANUAL

SQL> spool off;

I have difficulty to remember all these parameters. Oracle have already thought about these and you can use fuzzy to query the parameters.

SQL> show parameter db_c;

NAME TYPE VALUE

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

db_cache_advice string ON

db_cache_size big integer 0

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> spool off;

Oracle lag and Lead

2009-05-19T21:05:00.001-07:00

LAG(EXPRESSION,[OFFSET],[DEFAULT])

SQL> with a as (select 1 id,'a' name from dual
2 union
3 select 2 id,'b' name from dual
4 union
5 select 3 id,'c' name from dual
6 union
7 select 4 id,'d' name from dual
8 union
9 select 5 id,'e' name from dual
10 )
11 select id,name,lag(id,1,'')over(order by name) from a
12 /

ID NAME LAG(ID,1,'')OVER(ORDERBYNAME)
---------- ---- -----------------------------
1 a
2 b 1
3 c 2
4 d 3
5 e 4

LEAD(EXPRESION,[OFFSET],[DEFAULT])

SQL> with a as (select 1 id,'a' name from dual
2 union
3 select 2 id,'b' name from dual
4 union
5 select 3 id,'c' name from dual
6 union
7 select 4 id,'d' name from dual
8 union
9 select 5 id,'e' name from dual
10 )
11 select id,name,lead(id,1,'')over(order by name) from a
12 /

ID NAME LEAD(ID,1,'')OVER(ORDERBYNAME)
---------- ---- ------------------------------
1 a 2
2 b 3
3 c 4
4 d 5
5 e

Use Maven to manage your Java Project

2009-05-18T20:54:00.000-07:00

Maven have lots of cool features. In this post, I am writing for how to craete single executable jar file that includes all the dependended libraries. This plug-in is called assembly.

for how to use this plugin. please visit http://maven.apache.org/plugins/maven-assembly-plugin

Oracle Analytic Fucntions

2009-05-18T18:16:00.000-07:00

Oracle Analytic Fucntions

The general syntax of analytic function is:

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

is like "ROW " or "RANK "

the usages are:

Types of Analytical Functions

Ranking Functions

This family consists of rank, dense_rank, row_number, ntile,percent_rank and cume_dist.

See also dense rank vs rank vs row_number.

Window Functions

Window Functions consist of all agregate functions (sum,max, avg...)

Reporting Functions

These are queries that ask something like: find the sales that contribute 10 % or more to total sales. The most prominent function for this is ratio_to_report.

Lag/Lead Functions

Lag/Lead functions make it possible to access values in other rows than the current one. The corresponding functions are lag and lead.

The partition clause

An important part in analytic functions is the Partition Claus

measuring Oracle cache hit rate

2009-05-13T23:36:00.001-07:00

rem-------------------------------------------

rem measuring cache hitrate

rem ------------------------------------------

-- Collecting initial cache hit rate...

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"

FROM v$sysstat cur, v$sysstat con, v$sysstat phy

WHERE cur.name = 'db block gets'

AND con.name = 'consistent gets'

AND phy.name = 'physical reads'

-- intervent to increase cache hit rate...

DECLARE

v_dummy dual.dummy%TYPE;

BEGIN

FOR I IN 1..1000 LOOP

SELECT dummy INTO v_dummy FROM dual;

END LOOP;

END;

-- collect again...

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"

FROM v$sysstat cur, v$sysstat con, v$sysstat phy

WHERE cur.name = 'db block gets'

AND con.name = 'consistent gets'

AND phy.name = 'physical reads'

Quick and Easy ways to copy Oracle Schema method 1

2009-05-13T22:53:00.000-07:00

exp/imp can be used to export/import data to or from schema

the syntax is

to export:

$exp username/password inctype=[complete||incremental] file=file.dmp

to import:

imp username/password inctype= RECTORE FULL=Y FILE=file.dmp

please note. to import, you need to create enough room for rollback.

next time, I will take about to use new tool called datapump to export/import schema. data pump only available for oracle 10+.

5 easy steps to create a PDF Document in Java

2009-05-12T23:34:00.000-07:00

Recently, my company has asked me to create some histograms in the pdf format.

The first thing comes to my mind is to use Jasper Report. After talking to one of my colleagues, I decided to use iText to generate the pdf document.

Generate a pdf document is not a rocket science. Here are the steps how I do it in Netbeans( I believe it should be same as in Eclipse). I assume you have installed Maven plugin for your Netbeans. If you haven't done so, please install it. Maven will make your programming life much easier.

Setup the environment first.

1). Create a maven project in NetBeans.

2). open the project description file pom.xml. this file is located inside your Project Files.

3). inside the dependencies tag, add an iText dependeny

<groupid>com.lowagie</groupid>

<artifactid>itext</artifactid>

</dependency>

4). right click on the Libraries folder and select download all libraries.

2. inside your main function.

Document doc = new Document();

PdfWriter.getInstance(doc, new FileOutputStream("/tmp/helloWorld.pdf"));

doc.open();

doc.add(new Paragraph("Hello from iText PDF"));

doc.close();

3. click on Run.

4. a file named helloWorld.pdf should located in /tmp directory.

5. there is no step 5.

Here is just the sample to illustruate how easy it is to create PDF file by using Netbean and Maven. If you have some questions, please let me know.

Difference between "in (...)" and "= any(...)" queries when using arrays

2009-05-12T11:13:00.000-07:00

I was playing with "in (...)" and "= any (...)" queries and found a
difference between them and I wonder:

why this works:

select * from table_of_integers where integer_column = any (array[5,6]);

and this doesn't:

select * from table_of_integers where integer_column in (array[5,6]);

Although documentation says:

9.20.4. ANY/SOME

[...]
SOME is a synonym for ANY. IN is equivalent to = ANY.
[...]

I thought that if IN is equivalent to = any, both queries above should work.

after "google is your friend", I am able to find my answers.

the answers are

select * from table_of_integers where integer_column = any (array[5,6]);

This checks if integer_column matches any value in the specified array.

select * from table_of_integers where integer_column in (array[5,6]);

This checks if integer_column IS an array[5,6] (which it isn't).

Converting an ext3 filesystem to ext4

2009-05-11T22:02:00.000-07:00

since ext4 is backwards compatible, you can mount an ext3 partition as ext4:

mount -t ext4 /dev/device /mnt/mountpoint

where device is the device you want to mount, and mountpoint is where you want to mount it.

The filesystem will not, however, use many of the new features of ext4. You can enable those with the following:

Warning: This will make the filesystem unable to be mounted as ext3

tune2fs -O extents,uninit_bg,dir_index /dev/device

It is important that you then run fsck, to make sure everything is ok:

fsck -pf /dev/device

Note: Using this method, new files will be created using the "extents" method, but old files will not be automatically converted

Appfuse experience

2009-05-11T21:35:00.000-07:00

appfuse is an opensource framework. it glues some of the best Java Open source frameworks out there.

Appfuse is build on top of Spring framework. it can be use JPA, Hibernate and iBatic for the data layer persistence.

you can download appfuse from www.appfuse.org

Poor man VPN using SSH Tunnelling

2009-05-11T19:26:00.000-07:00

Tips: Alter postgresql.conf to increase PostgreSQL performance

2009-05-11T19:03:00.000-07:00

The default configuration PostgreSQL ships with is a very solid configuration aimed at everyone's best guess as to how an "average" database on "average" hardware should be setup. This article aims to help PostgreSQL users of all levels better understand PostgreSQL performance tuning.

There are several postmaster options that can be set that drastically affect performance, below is a list of the most commonly used and how they effect performance:

max_connections = — This option sets the maximum number of database backend to have at any one time. Use this feature to ensure that you do not launch so many backends that you begin swapping to disk and kill the performance of all the children. Depending on your application it may be better to deny the connection entirely rather than degrade the performance of all of the other children.
shared_buffers = — Editing this option is the simplest way to improve the performance of your database server. The default is pretty low for most modern hardware. General wisdom says that this should be set to roughly 25% of available RAM on the system. Like most of the options I will outline here you will simply need to try them at different levels (both up and down ) and see how well it works on your particular system. Most people find that setting it larger than a third starts to degrade performance.
effective_cache_size = — This value tells PostgreSQL's optimizer how much memory PostgreSQL has available for caching data and helps in determing whether or not it use an index or not. The larger the value increases the likely hood of using an index. This should be set to the amount of memory allocated to shared_buffers plus the amount of OS cache available. Often this is more than 50% of the total system memory.
work_mem = — This option is used to control the amount of memory using in sort operations and hash tables. While you may need to increase the amount of memory if you do a ton of sorting in your application, care needs to be taken. This isn't a system wide parameter, but a per operation one. So if a complex query has several sort operations in it it will use multiple work_mem units of memory. Not to mention that multiple backends could be doing this at once. This query can often lead your database server to swap if the value is too large. This option was previously called sort_mem in older versions of PostgreSQL.
max_fsm_pages = — This option helps to control the free space map. When something is deleted from a table it isn't removed from the disk immediately, it is simply marked as "free" in the free space map. The space can then be reused for any new INSERTs that you do on the table. If your setup has a high rate of DELETEs and INSERTs it may be necessary increase this value to avoid table bloat.
fsync = — This option determines if all your WAL pages are fsync()'ed to disk before a transactions is committed. Having this on is safer, but can reduce write performance. If fsync is not enabled there is the chance of unrecoverable data corruption. Turn this off at your own risk.
commit_delay = and commit_siblings = — These options are used in concert to help improve performance by writing out multiple transactions that are committing at once. If there are commit_siblings number of backends active at the instant your transaction is committing then the server waiting commit_delay microseconds to try and commit multiple transactions at once.
random_page_cost = — random_page_cost controls the way PostgreSQL views non-sequential disk reads. A higher value makes it more likely that a sequential scan will be used over an index scan indicating that your server has very fast disks.

If this is still confusing to you, Revolution Systems does offer a PostgreSQL Tuning Service

Note that many of these options consume shared memory and it will probably be necessary to increase the amount of shared memory allowed on your system to get the most out of these options.

Hardware Issues

Obviously the type and quality of the hardware you use for your database server drastically impacts the performance of your database. Here are a few tips to use when purchasing hardware for your database server (in order of importance):

RAM — The more RAM you have the more disk cache you will have. This greatly impacts performance considering memory I/O is thousands of times faster than disk I/O.
Disk types — Obviously fast Ultra-320 SCSI disks are your best option, however high end SATA drives are also very good. With SATA each disk is substantially cheaper and with that you can afford more spindles than with SCSI on the same budget.
Disk configuration — The optimum configuration is RAID 1+0 with as many disks as possible and with your transaction log (pg_xlog) on a separate disk ( or stripe ) all by itself. RAID 5 is not a very good option for databases unless you have more than 6 disks in your volume. With newer versions of PostgreSQL you can also use the tablespaces option to put different tables, databases, and indexes on different disks to help optimize performance. Such as putting your often used tables on a fast SCSI disk and the less used ones slower IDE or SATA drives.
CPUs — The more CPUs the better, however if your database does not use many complex functions your money is best spent on more RAM or a better disk subsystem.

In general the more RAM and disk spindles you have in your system the better it will perform. This is because with the extra RAM you will access your disks less. And the extra spindles help spread the reads and writes over multiple disks to increase throughput and to reduce drive head congestion.

Another good idea is to separate your application code and your database server onto different hardware. Not only does this provide more hardware dedicated to the database server, but the operating system's disk cache will contain more PostgreSQL data and not other various application or system data this way.

For example, if you have one web server and one database server you can use a cross-over cable on a separate ethernet interface to handle just the web server to database network traffic to ensure you reduce any possible bottlenecks there. You can also obviously create an entirely different physical network for database traffic if you have multiple servers that access the same database server.

Writing your own Velocity macro

2009-05-09T11:44:00.000-07:00

Velocity is a Java Template Engine. Velocity can be generate Highlighted source code view, SQL, reports in variable forms. By using velocity, developers can seperate the code logic and the views.

these are the logics

#if #else #end -- conditional
#foreach #end -- Iteration

#set() assign new variable value

#stop stop execution the following
#evaluate() evaluate a function
#define

#macro

to define your own macro

#macro(invoke $__p_page)
   #if($__p_page.startsWith("/"))
       #parse($__p_page)
   #else
       #set($__uri = $resource.this_vm())
       #set($__path = $__uri.substring(0, $__uri.lastIndexOf("/")))
       #parse("$__path/$__p_page")
   #end
#end

to use it.

#invoke( hello.vm)

NC linux command

2009-05-08T16:45:00.001-07:00

Today, I discover a nice network utility tool call nc

you can use it for troubleshooting your network apps.

Ford Escape

2009-05-08T16:33:00.001-07:00

Yes, finally. I got my Ford Escape today.

I like this small suv very much. Compare to my 98 Acura Integra. This car is big and comfy.

will post some pictures when I have time

move/rename oracle datafiles

2009-04-22T12:53:00.000-07:00

To properly move the datafile around or rename the datafile, follow this guide:

Login to SQLPlus.
Connect as SYS DBA with CONNECT / AS SYSDBA command.
Shutdown the database instance with SHUTDOWN command.
Rename or/and move the datafiles at OS level.
Start Oracle database in mount state with STARTUP MOUNT command.
Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘’ TO ‘’;
Open Oracle database instance completely with ALTER DATABASE OPEN command.

If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.

Login to SQLPlus.
Connect as SYS DBA with CONNECT / AS SYSDBA command.
Make offline the affected tablespace with ALTER TABLESPACE OFFLINE; command.
Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER TABLESPACE RENAME DATAFILE ‘’ TO ‘’;
Bring the tablespace online again with ALTER TABLESPACE alter tablespace ONLINE; command.

wtf is Oracle Varchar2? What are the difference between varchar and varchar2

2009-04-21T10:19:00.001-07:00

I was kinda confused earlier on the differences but get a better explaination after hunting around. Below details each of their characteristics:

Char: Declare and used. If the size declared is 5 and assigned value is only length 2, the leftover will be stored with spaces. Some claimed this would be much faster, but some doesn't find any differences in performance compare to varchar.
VarChar: Declare in advance, size used only when assigning values. Depending on the values assigned, for those that have not been used or excessive size allocated will be freed. First few bites will store the length of the values assigned. ANSI SQL Standard.
VarChar2: It is an oracle's improve Varchar datatype version. Characteristics are mostly similar to varchar.

Do you have anything to addon?

Recycled from the Internet.

Use RMAN to copy a database to a different Server

2009-04-17T15:33:00.000-07:00

Question: I want to use RMAN to copy a database to a different server, in a different disk directory. Can I use RMAN to restore a database to another server?

Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.0.0

The goal is restoring a database using RMAN on a different node with different backup directory structures and different database directory structures .

- You have a database backed up on NODE 1.
- You need to restore the database on NODE 2.
- The directory structure is different on NODE 2.
- You need to put the backups in a new directory structure in NODE 2, unlike as they were in NODE 1.
- You need to restore the database files into a new directory structure in NODE 2, unlike as they were in NODE 1.

Solution

Below is the procedure with an example of using RMAN to copy a database to another directory:

1) Connect to the target database using rman and backup the database ---> NODE 1

$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:29:33
2007Copyright © 1982, 2005, Oracle. All rights reserved.connected to target database: ORA10G (DBID=3932056136)RMAN>
backup database plus archivelog;Starting backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel
ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1
sequence=143 recid=109 stamp=614392105channel ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece
1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp
tag=TAG20070213T002825 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting
backup at 13-FEB-07using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying
datafile(s) in backupsetinput datafile fno=00003 name=/u01/oracle/product/oradata/ora10g/data/sysaux01.dbfinput datafile
fno=00001 name=/u01/oracle/product/oradata/ora10g/data/system01.dbfinput datafile fno=00002 name=/u01/oracle/product/oradata/ora10g/data/undotbs01.dbfinput
datafile fno=00004 name=/u01/oracle/product/oradata/ora10g/data/users01.dbfinput datafile fno=00005 name=/home/oracle/1.dbfinput
datafile fno=00006 name=/u01/oracle/product/oradata/ora10g/data/sysaux02.dbfinput datafile fno=00007 name=/u01/oracle/product/oradata/ora10g/data/undotbs02.dbfchannel
ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp
tag=TAG20070213T002827 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55Finished backup at 13-FEB-07Starting
backup at 13-FEB-07current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel
ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=144 recid=110 stamp=614392165channel
ORA_DISK_1: starting piece 1 at 13-FEB-07channel ORA_DISK_1: finished piece 1 at 13-FEB-07piece handle=/u01/oracle/product/oradata/ora10g/fra/ORA10G/backupset/2007_02_13/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp
tag=TAG20070213T002925 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 13-FEB-07Starting
Control File and SPFILE Autobackup at 13-FEB-07piece handle=/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02 comment=NONEFinished Control File and SPFILE Autobackup at 13-FEB-07RMAN> exit

2) Move the following files to the NODE 2 :

+ The database backup pieces
+ Controlfile backup piece
+ The parameter file i.e init.ora file

3) Edit the PFILE on NODE 2 to change the environment specific parameters like .

user_dump_dest =
background_dump_dest =
control_files =

4) Once the PFILE is suitably modified invoke Rman on the NODE 2 after setting the Oracle environment variables and start the database in nomount mode:

[oracle@test-br test]$ export ORACLE_HOME=/u01/oracle/product/ora10g
[oracle@test-br test]$ export ORACLE_SID=ora10g
[oracle@test-br test]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@test-br test]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 13 00:36:55 2007
Copyright © 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 205520896 bytes
Fixed Size 1218508 bytes
Variable Size 75499572 bytes
Database Buffers 121634816 bytes
Redo Buffers 7168000 bytes

5) Restore the controlfile from the backup piece.

RMAN> restore controlfile from '/u01/oracle/product/ora10g/dbs/c-3932056136-20070213-02';
Starting restore at 13-FEB-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete,
elapsed time: 00:00:02
output filename=/u01/oracle/product/oradata/ora10g/cntrl/control01.ctl
Finished restore at 13-FEB-07

6) Mount the database.

RMAN> alter database mount ;

7) Now catalog the backup pieces that were shipped from NODE 1.

RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp';Starting
implicit crosscheck backup at 13-FEB-07allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKCrosschecked
3 objectsFinished implicit crosscheck backup at 13-FEB-07Starting implicit crosscheck copy at 13-FEB-07using channel
ORA_DISK_1Finished implicit crosscheck copy at 13-FEB-07searching for all files in the recovery areacataloging files...no
files catalogedcataloged backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002925_2x21m6ty_.bkp
recid=41 stamp=614393265RMAN> catalog backuppiece '/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp';cataloged
backuppiecebackup piece handle=/home/oracle/test/backup/o1_mf_annnn_TAG20070213T002825_2x21kbds_.bkp recid=42 stamp=614393292RMAN>
catalog backuppiece '/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp';cataloged backuppiecebackup
piece handle=/home/oracle/test/backup/o1_mf_nnndf_TAG20070213T002827_2x21kd12_.bkp recid=43 stamp=614393310

8) Get to know the last sequence available in the archivelog backup using the following command.

RMAN > list backup of archivelog all;

9) Rename the Redologfiles,so that they can be created in new locations when opened the database is opened in resetlogs.

SQL> alter database rename file '/u01/oracle/product/oradata/ora10g/log/redo01.log' to '/home/oracle/test/log/redo01.log';..................

10) Now restore the datafiles to new locations and recover.

RMAN> run { set until sequence set newname for datafile 1 to '/home/oracle/test/data/sys01.dbf';
set newname for datafile 2 to '/home/oracle/test/data/undotbs01.dbf'; set newname for datafile 3 to '/home/oracle/test/data/sysaux01.dbf';
set newname for datafile 4 to '/home/oracle/test/data/users01.dbf'; set newname for datafile 5 to '/home/oracle/test/data/1.dbf';
set newname for datafile 6 to '/home/oracle/test/data/sysaux02.dbf'; set newname for datafile 7 to '/home/oracle/test/data/undotbs02.dbf';
restore database; switch datafile all; recover database; alter database open resetlogs; }

This is just my note copy from Sorabh Harit. Thanks Sorabh.

brainstorming a new Document Management System

2009-04-15T14:18:00.000-07:00

I am thinking to make myself a Document Management System (DMS).

I don't like any of the DMS out there.

either they are not search friendly (KnowledgeTree).
hate the language it is written in (PHP)
too complicate to use. ( Alfresco)

I kind of like Docmgr DMS, it has a very clean interface and simply it just work.
the only think I don't like Docmgr is it is written in PHP.

I am not saying PHP is bad. but it just too troublesome to maintain the code and people will write bad code.

I am thinking to use Java and PostgreSQL as the main Technology components.

Java have been proven it is clean, well structure programming language.
PostgreSQL in my opinion, it is the best Open Source Database out there. (Don't convince me MySQL is good, to be honestly, I hate MySQL. Too many things can go wrong with MySQL).

The draft of the requirement is in http://docs.google.com/Doc?id=dsq74hw_55czc4zfd5&hl=en

Once I have time, I will finalize the requirements.

Purchase 2009 Ford Escape

2009-04-14T13:27:00.000-07:00

Yesterday is my happy day. I went to Ford Dealer yesterday to browse some new Car. I was hooked by 2009 Escape.

the stylish, roomy and functional widgets make us to bring out our Credit card and reserved one of them.

I have never drove a Ford in my life so far and I has always thought American Car sucks. These statements are not true anymore.

I am looking forward to April 30. that is when my car is coming.

I will take some pix and keep you guys posted.

Oracle Tips and Tricks

2009-04-12T23:07:00.000-07:00

oracle auto generated drop objects :)

select 'Drop '||object_type||' '||object_name||
case
when object_type = 'TABLE' then ' cascade constraints;'
else ';'
end
from user_objects;

This will generated all the drop table, sequence etc. statements for you.

Publish Post

Resume Mysql Replication

2009-04-12T22:31:00.000-07:00

When MySQL slave db doesn't shutdown correctly, the next time you start it, the error may occur and complaints one of the log files is not found in mysql directory.

here is the procedure to fix it.

1). MYSQL > show slave status\G;

the output as the following

Slave_IO_State:

Master_Host: 127.0.01

Master_User: remotereplicator

Master_Port: 33061

Connect_Retry: 60

Master_Log_File: HP2-bin.000016

Read_Master_Log_Pos: 819453275

Relay_Log_File: mysqld-relay-bin.000020

Relay_Log_Pos: 3311066

Relay_Master_Log_File: HP2-bin.000016

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 819453275

Relay_Log_Space: 0

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.02 sec)

write down the Relay_Maste_Log_File and Read_Master_Log_File_Pos value. in this case, they are HP2-bin.000016 and 819453275 respectly.

2). MYSQL> stop slave;

3). MYSQL>reset slave;

4). MYSQL>start slave;

5). MYSQL>show slave status\G;

the output should be look like this

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: remotereplicator

Master_Port: 33061

Connect_Retry: 60

Master_Log_File: HP2-bin.000003

Read_Master_Log_Pos: 3493

Relay_Log_File: mysqld-relay-bin.000006

Relay_Log_Pos: 233

Relay_Master_Log_File: HP2-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1007

Last_Error: Error 'Can't create database 'manufacture';

database exists' on query. Default database: 'manufacture'. Query: 'create

database manufacture'

Skip_Counter: 0

Exec_Master_Log_Pos: 98

Relay_Log_Space: 3628

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)

ERROR:

No query specified

-- it tells you the database has exists. this is a good sight. it means the

slave is working.

6). MYSQL> stop slave;

7). MYSQL> CHANGE MASTER TO MASTER_HOST='127.0.01',

MASTER_USER='$remotereplicator',

MASTER_PASSWORD='$pass',MASTER_LOG_FILE='$log_file',

MASTER_LOG_POS=$log_pos;

in here,

$remotereplicator is the remote replication user

$pass is the remote replication user's password

$log_file is the remote replication file. in this case. it is HP2-bin.000016

$log_pos is the remote position, in this case, it is 819453275

8). MYSQL> start slave;

9). to confirm it is working.

MYSQL> show slave status\G;

the values of the Slave_IO_Running and the Slave_SQL_Running should be

"Yes"

Database => oracle and router => cisco

2008-09-17T18:59:00.000-07:00

Now adays, when comes to decision of database, most people say Oracle without look at the requirements. There is no doubt that Oracle is a good Database System, but most of the time, it just overkilled.