Tomcat 6 with JAX-WS 2.1

Foremost get the latest JDK (Anything after JDK 1.6 update 4 or later) as this would not require to configure/create additional "endorsed" directory (since rt.jar contains jax-ws 2.1 api).

To use JAX-WS 2.1 with Tomcat
1. Downloadthe JAX-WS libraries
2. Install (or Extract) the downloaded file with

java -jar JAXWS2.1.2-20070917.jar

3. Assuming the libraries were installed on $CATALINA_HOME/jaxws-ri, edit shared.loader property in $CATALINA_HOME/conf/catalina.properties as follows

shared.loader=$CATALINA_HOME/jaxws-ri/lib/*.jar

If $CATALINA_HOME is not defined on the environment edit catalina.properties as

shared.loader=${catalina.home}/jaxws-ri/lib/*.jar

That's all!

Be sure to have the web.xml and sun-jaxws.xml files in the WS deployment .war, unlike glassfish and weblogic, this tomcat configuration doesn't auto create them and wsdl won't be exposed.

Exporting from 11.2 to 11.1 and 10.2 causes error when tables are in the keep pool

Sometimes it is needed to export from a higher oracle db version to a lower version (from 11.2 to 11.1 or 11.2 to 10.2). But there appear to be a bug in the 11.2 expdp which causes export to fail for tables and indexes which have been moved to keep pool in the source data source. But this does not appear in 11.1 version.

This is the test case. All databases used have been patched to PSU April 2010.

Below code is used to create a table, populate it with some data and then move it to the keep pool. Databases all have keep pool configured on them.

create table abc (a number, b varchar2(10), c number primary key);

begin
 for i in 1 .. 1000
 loop
   insert into abc values (i+10,'abc'||i,i);
 end loop;
end;
/   

show parameter keep
NAME                 TYPE        VALUE
-------------------- ----------- ------
db_keep_cache_size   big integer 16M

alter table abc storage (buffer_pool keep);
alter index SYS_C0084932 storage (buffer_pool keep);

First exporting from 11.1.0.7 to 10.2.0.4

expdp asanga/*** directory=dumpdir dumpfile=asanga11g1.dmp logfile=11g1.log version="10.2.0.4" schemas=asanga
Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 20 May, 2010 14:04:40

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=asanga11g1.dmp logfile=11g1.log version=10.2.0.4 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."ABC"                              24.03 KB    1000 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
/ssdoracle/dpdumps/asanga11g1.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:05:17

Now import to 10.2.0.4

impdp asanga/asa directory=dumpdir dumpfile=asanga11g1.dmp logfile=10gimp.log full=y version="10.2.0.4"

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 20 May, 2010 14:15:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=asanga11g1.dmp logfile=10gimp.log full=y version=10.2.0.4
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ASANGA"."ABC"                              24.03 KB    1000 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 14:15:31

No errors there, import completes successfully. So the erroneous behavior didn't start with 11.1

Now exporting from 11.2.0 to 11.1.0.7
Create, populate and move the source table to keep pool using the aforementioned code. Afterwards do the export on 11.2

expdp asanga/*** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=11g1.log version="11.1.0.7" schemas=asanga

Export: Release 11.2.0.1.0 - Production on Thu May 20 14:27:52 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=11g1.log version=11.1.0.7 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."ABC"                              24.03 KB    1000 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
 /ssdoracle/dpdumps/asanga11g2_11107.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:28:19

Do the import on 11.1.0.7

impdp asanga/*** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=10gimp.log full=y version="11.1.0.7"

Import: Release 11.1.0.7.0 - 64bit Production on Thursday, 20 May, 2010 14:29:55

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_11107.dmp logfile=10gimp.log full=y version=11.1.0.7
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-25126: Invalid name specified for BUFFER_POOL
Failing sql is:
CREATE TABLE "ASANGA"."ABC" ("A" NUMBER, "B" VARCHAR2(10 BYTE), "C" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL ) TABLESPACE "TRAVELBOX"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"ASANGA"."SYS_C0034668" skipped, base object type TABLE:"ASANGA"."ABC" creation failed
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 14:30:02

As it could be seen from the above output, import fails due to invalid name specified on the buffer pool.

Now export 11.2 to 10.2.0.4

expdp asanga/*** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=11g1.log version="10.2.0.4" schemas=asanga

Export: Release 11.2.0.1.0 - Production on Thu May 20 14:22:57 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=11g1.log version=10.2.0.4 schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."ABC"                              23.84 KB    1000 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ASANGA.SYS_EXPORT_SCHEMA_01 is:
  /ssdoracle/dpdumps/asanga11g2_10204.dmp
Job "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:23:42

And now the import on 10.2.0.4

impdp asanga/*** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=10gimp.log full=y version="10.2.0.4"

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 20 May, 2010 14:27:04

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=asanga11g2_10204.dmp logfile=10gimp.log full=y version=10.2.0.4
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-25126: Invalid name specified for BUFFER_POOL
Failing sql is:
CREATE TABLE "ASANGA"."ABC" ("A" NUMBER, "B" VARCHAR2(10), "C" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL ) TABLESPACE "TRAVELBOX"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"ASANGA"."SYS_C0034668" skipped, base object type TABLE:"ASANGA"."ABC" creation failed
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 14:27:05

Again the export failes for the same reason as in 11.1.
Although here creation of index is skipped due to the fact table was not created but a similar test could be carried out by moving only the index to keep pool.
Error could be avoided if the source tables and indexes were moved to default pool before the export (how practical is this?) or recreate them beforehand (if there are only few) and use if table exists option in the impdp



Update 26 March 2013
Issue is mentioned in the following Oracle metalink notes.
Data Pump Import Fails With ORA-25126 If Data Pump Export From 11.2.0.1 With VERSION=10.2 [ID 1051792.1]
Bug 9160088 - 11.2 EXPDP to lower version errors on import (ORA-25126) [ID 9160088.8]
Problem seem to be fixed in 11.2.0.3 (checked with PSU 11.2.0.3.5 applied).

Effects of compress option on exp and imp

This is the official description of the compress option from Oracle docs

The default, COMPRESS=y, causes Export to flag table data for consolidation into
one initial extent upon import
If you specify COMPRESS=n, Export uses the current storage parameters, including
the values of initial extent size and next extent size.

What is basically says is that if you set compress=y when imported the table size will be same as the exported size. If you set compress=n then things are different as you can see from below.

create table test1 (a char(2000));

begin
    for i in 1 .. 20000
    loop
       insert into test1 values('a'||i);
    end loop;
end;
/   
    
SQL> select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA      BYTES
---------- ----------
TEST1        55574528   

SQL> delete from test1;

20000 rows deleted.

SQL> select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA      BYTES
---------- ----------
TEST1        55574528   

Now take two exports one with compress=y and one with compress=n

exp asanga/*** file=test.dmp tables=test1 compress=y
exp asanga/*** file=test_n.dmp tables=test1 compress=n

SQL>drop table test1 purge;

Table dropped.

imp asanga/*** file=test.dmp full=y
Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ASANGA's objects into ASANGA
. importing ASANGA's objects into ASANGA
. . importing table                        "TEST1"          0 rows imported
Import terminated successfully without warnings.                                

SQL>select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA      BYTES
---------- ----------
TEST1        55574528

SQL>drop table test1 purge;

Table dropped.

imp asanga/asa file=test_n.dmp full=y

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing ASANGA's objects into ASANGA
. importing ASANGA's objects into ASANGA
. . importing table                        "TEST1"          0 rows imported
Import terminated successfully without warnings.    

SQL>select segment_name,bytes from user_segments where segment_name='TEST1';

SEGMENT_NA      BYTES
---------- ----------
TEST1           65536

As it could seen from above even if an empty table is imported with the default options, the size of the table doesn't shrink. If compress=n is used the imported table is redcued in size (or number of segments it uses).

Basic Replication made simple

Basic replication is the only type of replication supported in Standard Edition One and Standard Edition. In basic replication only data is replicated not other data structures (eg indexes) or SP (procedures,functions etc. Replication is one-way from master site to replication site unlike advance replication which is capable of handling multi-master replication. The replication site uses materialized view to refresh the data and these are read-only.

Following is a test implementation to try out

create a oracle db user on both DBs. Call this replication user

On Master Site create the table and materialized view logs on that table and insert some data

create table res_bkg (booking_id number primary key, name varchar2(100), city varchar2(100), cost number);

create materialized view log on res_bkg with primary key;

insert into res_bkg values (1,'asanga','cmb',101);
insert into res_bkg values (2,'pradeep','lhr',201);

On Replication Site:
1. Edit the tnsnames.ora file and add an entry to the master site DB (assume it's std11g1)
2. As System user

create public database link std11g1.domain.ext using 'std11g1';

3. As replication user

create database link std11g1.domain.ext connect to asanga identified by asa;

create materialized view res_bkg refresh fast with primary key start with sysdate next sysdate + 1/(24*60) as (select * from res_bkg@std11g1.domain.ext);

select * from res_bkg;

This will show all the data currently in the master site table.

Insert another row to the master site

insert into res_bkg values (3,'alvin','alx',1001);

and do a select * from res_bkg on the replication site. After the refresh interval has passed the row will appear on the replication site. Can try a delete as well

delete from res_bkg where cost=1001;

Eventually the row will be deleted from the replication site.

The replication works between standard (primary) and enterprise (replicate) editions as well.