More HW Contention tuning

There are three other lob columns that need to be moved to non-assm tablespaces to eliminate hw contention issues. We talked about cube_scope before, now we need to recreate tables audit_details, sync_store and xml_document with non-assm blob storage as well. Here is the sql script to do this:

DROP TABLE orabpel.AUDIT_DETAILS;
DROP TABLE orabpel.SYNC_STORE;
DROP TABLE orabpel.XML_DOCUMENT;

CREATE TABLESPACE AUDIT_DETAIL_LOB DATAFILE
'+DATA/soa/datafile/adlob01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE 5120M,
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE TABLESPACE SYNC_STORE_LOB DATAFILE
'+DATA/soa/datafile/stlob01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE 5120M,
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE TABLESPACE XML_DOC_LOB DATAFILE
'+DATA/soa/datafile/xdlob01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE 5120M,
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

CREATE TABLE orabpel.AUDIT_DETAILS
(
CIKEY INTEGER,
DOMAIN_REF INTEGER,
DETAIL_ID INTEGER,
BIN_CSIZE INTEGER,
BIN_USIZE INTEGER,
DOC_REF VARCHAR2(300 BYTE),
BIN BLOB
)
TABLESPACE ORABPEL
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BIN) STORE AS
( TABLESPACE AUDIT_DETAIL_LOB
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 6
FREELIST GROUPS 5
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX orabpel.AD_PK ON AUDIT_DETAILS
(CIKEY, DETAIL_ID)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE orabpel.AUDIT_DETAILS ADD (
CONSTRAINT AD_PK
PRIMARY KEY
(CIKEY, DETAIL_ID)
USING INDEX
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));


CREATE TABLE orabpel.SYNC_STORE
(
CIKEY INTEGER,
DOMAIN_REF INTEGER,
BIN_CSIZE INTEGER,
BIN_USIZE INTEGER,
BIN BLOB
)
TABLESPACE ORABPEL
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BIN) STORE AS
( TABLESPACE SYNC_STORE_LOB
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
CACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 6
FREELIST GROUPS 5
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX orabpel.SS_FK ON orabpel.SYNC_STORE
(CIKEY)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE TABLE orabpel.XML_DOCUMENT
(
DOCKEY VARCHAR2(200 BYTE),
DOMAIN_REF INTEGER,
BIN_CSIZE INTEGER,
BIN_USIZE INTEGER,
BIN BLOB,
MODIFY_DATE TIMESTAMP(6),
BIN_FORMAT INTEGER
)
TABLESPACE BPELDOC
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BIN) STORE AS
( TABLESPACE xml_doc_lob
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
CACHE
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 6
FREELIST GROUPS 5
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX orabpel.XML_DOC_PK ON XML_DOCUMENT
(DOCKEY)
LOGGING
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
REVERSE;


ALTER TABLE orabpel.XML_DOCUMENT ADD (
CONSTRAINT XML_DOC_PK
PRIMARY KEY
(DOCKEY)
USING INDEX
TABLESPACE BPELIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

B2B Patching Sequence

Current patching sequence for B2B (10.1.2.0.2):

4505133 - 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
4869010 - OC4J FAILS TO GUARANTEE VALID CONNECTIONS IN POOL TO RAC NODES
6083645 - MLR ON TOP OF 10.1.2.0.2/10.1.2.1 FOR CPUJUL2007

6488268 - B2B Patchset 5

SOA Best Practices Guide (10.1.3.x)

You would think this would be easier to find. I'm linking it here so that I don't have to go digging for it again.