I’m sure this is well-known and documented behavior to RecoverPoint appliance experts, but we recently found what we think to be a very nice feature in EMC’s RecoverPoint behavior that perhaps some folks new to the technology are unaware of …

Today we did a large (1Tb) backup of an assortment of Oracle database files on HP-UX file-systems to a set of ASM disk groups.  The ASM disk groups were the source LUNs in a RecoverPoint remote replication consistency group.  Our RMAN backup to ASM disk groups was done using 6 channels, and the source ASM LUNs resided in a RAID5 Enterprise Flash Drive Raid Group.

As the RMAN backup job allocated channels and wrote backup copies to the target LUNs, we noticed that the RecoverPoint appliances essentially “suspended” transmission of data with a helpful message about the source volumes being too busy.  As channels de-allocated and the RMAN job switched from tablespace to tablespace (it was a tablespace at a time RMAN “backup as copy” operation), the RecoverPoint data transfer would kick back in, then suspend itself again as the drives became busy.

Granted, we configured our consistency groups with pretty much default settings, didn’t set any non-standard or overly aggressive RPOs, and essentially were “out of the box” with a 100Gb-sized RecoverPoint journal size.  The nice implication of this behavior is this - in the event we need to do “large” data migration or storage-related activities on our source RP volumes, it looks like we’ll never have to worry about suspending our data transfer manually (through the RP administrative web interface or otherwise) - it all just happens by itself.

One less thing to worry about …

Many are familiar with the steps required to clone Oracle database using “rman duplicate” or “hot backup” cloning .  Many are also familiar with steps required to create EMC SnapView Clones or SnapView Snapshots, either with the Navisphere web interface or CLI.  In this post, I’ll outline steps required to build consistent, usable Oracle database “clones” within the framework of the following environment/architecture:

• Oracle 11.1.0.7, HP-UX 11iV2
• EMC CLARiiON CX4 storage arrays at production and DR site
• EMC RecoverPoint appliances at production and DR site
• Source database (production) uses Oracle ASM for its storage
• Requirement is to replicate production data from production storage array to remote “DR” array.
• Requirement is to use this replicated data at the DR site as the source for both SnapView Clones or SnapView Snapshots
• Requirement is to Clone or Snap from DR Replica LUNs, re-create an Oracle control file to build a new database, recover this target database, open with resetlogs, and use it

Goals:

• Move Oracle storage from HP XP platform to EMC CLARiiON storage array
• Utilize EMC Enterprise Flash Drives (EFDs) for most database files, FC drives for application/Oracle software, Oracle redo logs, archive log destination, and UNDO tablespace files
• Transition from HP-UX 11.23 file-system based storage to Oracle ASM (on combination of EFDs and FC drives)

Environment Details:

• HP RP8420 PA-RISC, HP-UX 11.23
• New storage array: EMC CX4-480 with a large collection of FC drives and 5 Enterprise Flash Drives
• Old storage array: HP XP-128 with many multiple FC drives
• EMC PowerPath 5.1

After installing and powering on the new CLARiiON storage array, the first step is to use NaviSphere to configure Raid Groups, LUNs, and a single Storage Group.

EMC CLARiiON Array Configuration

After installing the CLARiiON CX4-480 and installing NaviSphere, login to NaviSphere.  In this configuration, we did the following:

Created four (4) Raid Groups:

• RG0 = 5-disk RAID5, Vault (CLARiiON O/S), cached enabled with default values
• RG1 = 4-disk RAID5, EFDs, cache disabled
• RG2 = 5-disk RAID5, cache enabled with defaults
• RG3 = 10-disk RAID 1/0 on FC drives, cache enabled with defaults

Create LUNs:

• LUN0: EFD drives in RG1, intended use is database files, 1.2Tb in size
• LUN1: FC drives from RG3, 200Gb in size
• LUN2: FC drives from RG3, 200Gb in size
• LUN3: FC drives from RG2, 250Gb in size
• LUN4: FC drives from RG2, 400Gb in size

Storage group:

• We only have one host we’ll zone to any/alll of these LUNs
• Storage group = detux4 (the host name)

SAN Zoning and Host Configuration

At this point, we zone the newly created LUNs to DETUX4.  First, install Navisphere Agent on the host (detux4 in this case), assign via the Storage Group in NaviSphere, and scan (discover) disks. From the host:

• # /sbin/init.d/agent stop
• # ioscan -fnCdisk
• # insf -e
• # /sbin/init.d/agent start
• # /sbin/powermt check
• # /sbin/powermt config
• # /sbin/powermt save

Configure PowerPath and Document Devices

After installing and licensing EMC PowerPath 5.1, we’ll do a “powermt config” and assign PowerPath devices to the raw devices discovered in the previous step.   At the completion of this step, it’s important to understand and document the raw device names and how they map to CLARiiON LUNs.  In our case:

/dev/rdsk/c57t0d0 == raw device mapped to LUN0 (EFD LUN)

/dev/rdsk/c57t0d1 == raw device mapped to LUN1 (FC)

/dev/rdsk/c57t0d2 == raw device mapped to LUN2 (FC)

(the three raw devices above will represent the 3 disks we’ll use for our ASM diskgroups)

/dev/rdsk/c57t0d3  == device for file-system based storage for non-ERP databases.  For purposes of this document, we don’t plan on using these

/dev/rdsk/c57t0d4 == device on which we’ll build an HP-UX physical device, volume group, and our logical volumes and file-systems for Applications/Oracle binaries and data.

A note about PowerPath: When deciding which raw device to use for your ASM diskgroup, you can just pick one of the ones listed.  So in a sample “powermt display dev=all” output, you may see something like this:

CLARiiON ID=APM00101902811 [SG_detux4.aam.net]
Logical device ID=60060160ED532400CC44C4D9E16FDF11 [LUN 0]
state=alive; policy=CLAROpt; priority=0; queued-IOs=0;
Owner: default=SP A, current=SP B       Array failover mode: 1
==============================================================================
————— Host —————   - Stor -   — I/O Path –  — Stats —
###  HW Path               I/O Paths    Interf.   Mode    State   Q-IOs Errors
==============================================================================
57 0/0/2/1/0.11.57.0.0.0.0  c57t0d0   SP A1     active  alive       0      0
58 0/0/2/1/0.11.59.0.0.0.0  c58t0d0   SP B0     active  alive       0      0
59 1/0/2/1/1.11.57.0.0.0.0  c59t0d0   SP A1     active  alive       0      0
60 1/0/2/1/1.11.59.0.0.0.0  c60t0d0   SP B0     active  alive       0      0
I chose to pick /dev/rdsk/c57t0d0, but could just as easily picked /dev/rdsk/c58t0d0 or either of the other two.  If one of the paths fails, PowerPath will automatically handle redirection.  In subsequent sections of this post you’ll notice we’re symbolically linking /asm/disk1 to /dev/rdsk/c57t0d0 and building our ASM diskgroup on it.  Should the HBA on the server fail down this path, or if SP A1 fails on the CX4, PowerPath will handle the alternate path and no changes should be required inside ASM.  But this should be tested of course.

Build HP-UX Volumes/Filesystems

The goal here is to present 4 file-systems to HP-UX, /m001_emc, /m002_emc, /m003_emc, and /m004_emc. The steps to make this work are:

• # pvcreate /dev/rdsk/c57t0d4
• # vgcreate -s 16 /dev/vg53 /dev/dsk/c57t0d4
• # lvcreate -L 102384 /dev/vg53
• # newfs -o largefiles /dev/vg53/rlvol1
• # newfs -o largefiles /dev/vg53/rlvol2
• # newfs -o largefiles /dev/vg53/rlvol3
• # newfs -o largefiles /dev/vg53/rlvol4
• # mkdir /m001_emc; mkdir /m002_emc; mkdir /m002_emc; mkdir /m004_emc
• # mount -o delaylog /dev/vg53/lvol1/ /m001_emc
• # mount -o delaylog /dev/vg53/lvol2/ /m002_emc
• # mount -o delaylog /dev/vg53/lvol3/ /m003_emc
• # mount -o delaylog /dev/vg53/lvol4/ /m004_emc
• Add entries in /etc/fstab for new filesystems

Create Symbolic LInks for ASM Disks

I like to create symbolic links for ASM disk to enable a clear convention for asm_diskstring settings

• # mkdir /asm
• # ln -s /dev/rdsk/c57t0d0 /asm/disk1
• # ln -s /dev/rdsk/c57t0d1 /asm/disk2
• # ln -s /dev/rdsk/c57t0d2 /asm/disk3
• # chown -h oraasm:dba /asm/disk*
• # chown oraasm:dba /asm/disk*
• # chmod 660 /asm/disk*

When we build ASM diskgroups, we’ll do it based on these /asm/diskX names

Install ASM 11.1.0.7

• Download Oracle 11.1.0.7 software and install.  I like to install using a different Unix account (oraasm in this case) but with the same default group (dba)
• Create ASM instance
• Patch to PSU3 (9352179).  This is required when we migrate using RMAN from existing file-systems to ASM diskgroups; if not done, we’ll get errors trying to copy controlfile to ASM diskgroup.
• NOTE: Before patching an ASM instance, you need to shut it down and stop /etc/init.d/init.cssd (/etc/init.d/init.cssd stop), and when complete you’ll need to do “/etc/init.d/init.cssd start”

Configure Oracle Environment

As part of this initiative, we’re moving not just the database and its files to new storage on the CLARiiON, but also applications files/binaries from HP-UX mount points on the old storage array to the new mount points on the CLARiiON.  In order for this to work, we need to ensure that all environment variables (ORACLE_HOME, etc) are pointing to the new mount point, the init.ora is placed in $ORACLE_HOME/dbs and has its path-dependent settings changed to new mount point locations, etc.  In addition, login as oracle, source the environment, and run:

$ relink all

Ensure that binaries are relinked correctly.

Build ASM Disk Groups

• Login as oraasm
• Set init.ora parameter asm_diskstring=’/asm/disk*’ and bounce instance
• Create ERP5_DG1

SQL> create diskgroup ERP5_DG1 external redundancy disk ‘/asm/disk1′;
SQL> alter diskgroup ERP5_DG1 add directory ‘+ERP5_DG1/controlfile’;
SQL> alter diskgroup ERP5_DG1 add directory ‘+ERP5_DG1/controlfile/ERP5′;
SQL> alter diskgroup ERP5_DG1 set attribute ‘compatible.rdbms’='11.1′;
SQL> alter diskgroup ERP5_DG1 set attribute ‘compatible.asm’='11.1′;

• Create ERP5_DG2

SQL> create diskgroup ERP5_DG2 external redundancy disk ‘/asm/disk2′;
SQL> alter diskgroup ERP5_DG2 add directory ‘+ERP5_DG2/controlfile’;
SQL> alter diskgroup ERP5_DG2 add directory ‘+ERP5_DG2/controlfile/ERP5′;
SQL> alter diskgroup ERP5_DG2 set attribute ‘compatible.rdbms’='11.1′;
SQL> alter diskgroup ERP5_DG2 set attribute ‘compatible.asm’='11.1′;

• Create ERP5_DG3

SQL> create diskgroup ERP5_DG3 external redundancy disk ‘/asm/disk3′;
SQL> alter diskgroup ERP5_DG3 add directory ‘+ERP5_DG3/controlfile’;
SQL> alter diskgroup ERP5_DG3 add directory ‘+ERP5_DG3/controlfile/ERP5′;
SQL> alter diskgroup ERP5_DG3 set attribute ‘compatible.rdbms’='11.1′;
SQL> alter diskgroup ERP5_DG3 set attribute ‘compatible.asm’='11.1′;
For each of the three diskgroups, I’m leaving the allocation unit (AU) defaulted to 1Mb, as this is an OLTP database

• Bounce the ASM instance and ensure the 3 new diskgroups are mounted by checking the alert log and querying v$asm_diskgroup

Prepare ERP5 to be Moved to ASM

• SQL> create pfile=’/home/oracle/cx4mig/initERP5.ora’ from spfile
• Note file# of current temporary tablespaces - this can be done afterwards as well
• Note old controlfile location - in our case, one of them was on /r030/oradata/ERP5/control01.ctl
• Edit /home/oracle/cx4mig/initERP5.ora and changed control_files:

control_files = (’+ERP5_DG1/ERP5/control01.ctl’,
‘+ERP5_DG2/ERP5/control02.ctl’,
‘+ERP5_DG3/ERP5/control03.ctl’)

• Adjust any other paths that may refer to old ORACLE_BASE or ORACLE_HOME locations to use the new /m00X_emc ones
• Shutdown ERP5 (shutdown immediate)
• SQL> startup nomount pfile=’/home/oracle/cx4mig/initERP5.ora

Migrate to ASM: Control Files

The first step in the migration is to move the controlfiles to the ASM diskgroups. We’ll triplex these (based on the settings listed in the previous section.

• Launch RMAN and connect:
• $ rman
• RMAN> connect target /
• RMAN> restore controlfile from ‘/r030/oradata/ERP5/control01.ctl’

You should see a successful controlfile restore to ASM.  It will automatically copy /r030/oradata/ERP5/control01.ctl to each of the three ASM locations specified in the initERP5.ora

• RMAN> alter database mount

At this point, the database will be mounted against the ASM controlfiles

Migrate to ASM: Backup Database to ASM

Now we use RMAN to create backup copies of all files to the ASM diskgroups.  In simple cases in which all files will be copied to the same ASM diskgroup, a single RMAN “backup as copy database …” would suffice.  But in this case, when we’ll be restoring UNDO tablespaces to a different ASM diskgroup (comprised of FC drives), we’ll need to tackle using a series of “backup as copy tablespace …” commands to ensure things are done right.

The first step is to do something like this:

select ‘backup as copy tablespace ‘||tablespace_name||’ format ”+ERP5_DG1”;’
from dba_tablespaces
where contents not in (’TEMPORARY’,'UNDO’)
union
select ‘backup as copy tablespace ‘||tablespace_name||’ format ”+ERP5_DG3”;’
from dba_tablespaces
where contents in (’UNDO’)
/

Spool this to an output file and put the contents in an RMAN script - for purposes of example let’s save the script is backup_copy.rcv.  The contents should look like this:

configure device type disk parallelism 4;
run {
backup as copy tablespace SYSTEM format ‘+ERP5_DG1′;
backup as copy tablespace TOOLS format ‘+ERP5_DG1′;
backup as copy tablespace USERS format ‘+ERP5_DG1′;
backup as copy tablespace CARX format ‘+ERP5_DG1′;
… lots more , lines inserted from output of above query

}

• Launch RMAN and “connect target /”
• RMAN> @backup_copy.rcv
• Allow all tablespace files to restore to either ERP5_DG1 or ERP5_DG3.  NOTE: We chose a degree of parallelism to 4 to allow multiple RMAN copu threads for tablespaces that contained multiple files.  Had we chose to follow a “backup as copy datafile X” approach, we would not have been able to parallelize the work
• Let all the database files to be copied to the ASM diskgroup and resolve all errors along the way
• RMAN> switch database to copy;
• RMAN> alter database open;

At this point, your controlfiles and all database files will reside on ASM.  I copied /home/oracle/cx4mig/initERP5.ora to $ORACLE_HOME/dbs at this point; built an spfile from it, and bounced again.

Migrate to ASM: Move TEMPFILES to ASM

To handle moving TEMP tablespace files to ASM:

• RMAN> connect target /
• RMAN> run {
  set newname for tempfile 1 to ‘+ERP5_DG1′;
  switch tempfile all;}
• RMAN> shutdown immediate
• RMAN> startup

Now, our temporary tablespace file lives on ASM in ERP5_DG1 (ASM LUN)

Migrate to ASM: Build Redo Log Group Members on ASM

As things stand, the only parts of the database residing on file-systems are our online redo logs.  We have 2 approaches to get them to ASM: create new redo log groups on ASM and drop old groups, or add members on ASM diskgroups to existing redo log groups and drop old members.  I like the second approach:

• Find your groups

set lines 120
col group# format 99
col member format a70
col status format a20;SELECT a.group#, b.member, a.status FROM v$log a, v$logfile bWHERE a.group#=b.group#

• SQL> alter database add logfile member ‘+ERP5_DG2′ to group 10;
• SQL> alter database add logfile member ‘+ERP5_DG3′ to group 10;
• SQL> alter database add logfile member ‘+ERP5_DG2′ to group 11;
• SQL> alter database add logfile member ‘+ERP5_DG3′ to group 11;
• SQL> alter database add logfile member ‘+ERP5_DG2′ to group 12;
  etc …
• Re-run query from first bullet and validate that each of your redo log groups has a member on both ERP5_DG2 and ERP5_DG3
• Do “alter sytem switch logfile” enough times to cycle through each group, and re-run query above
• Do “alter database drop logfile member ‘/<file system redo log path’; for each redo log group member still residing on file-systems

Migrate to ASM: Setting Archive Destination to ASM

• SQL> alter system set log_archive_dest_1=’LOCATION=+ERP5_DG2′;

Test It All!

• Bounce your database, ensure things come up cleanly, switch logfiles
• Run full-scans against large tables and compare I/O times against non-EFD storage (look for another blog on this …)
• Do failure testing by unplugging HBAs one-at-a time on the HP server and/or SAN switch to ensure PowerPath correctly keeps connections established and no loss-of-service is experienced
• Do a load test

Many DBAs are familiar with the SGA size limitations on 32-bit platforms.  This post shows how to allocate a 3Gb buffer cache on 32-bit RHAS 3.

• Step 1: Mount /dev/shm to as type ramfs.  Edit /etc/fstab and add an entry like this:

none                    /dev/shm                ramfs defaults,size=4G 0 0

• Step 2: do a “mount -a” to mount /dev/shm.  I use ramfs instead of tmpfs because it doesn’t use swap; tmpfs does.  With ramfs memory allocation will also grow dynamically, whereas when using tmpfs it will not.
• Step 3: As root, assuming your database is owned by the Linux account oracle, whose primary group is “dba”, do this:

# chown oracle:dba /dev/shm

• Step 4: Add the following to /etc/security/limits.conf to increase maximum memory lock parameters.  Ensure “oracle” user has his environment sourced to establish these settings.

oracle            soft    memlock         3145728
oracle            hard    memlock         3145728

• Step 5: Login as oracle and do “ulimit -a”; validate that memlock is set to the above values
• Step 6: Edit /etc/sysctl.conf and add/change the following.  When complete, do “sysctl -w” to activate changes into the Linux kernel.  The “vm.hugetbl_pool” setting below is set based on the output of a script provided here (http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b32009/appi_vlm.htm) - run this AFTER Oracle is started at the completion of this document to get a realistic value for vm.hugetbl_pool and adjust accordingly

kernel.sem = 1000 32000 100 150
kernel.shmmax = 4294967295
kernel.shmall = 4194304
net.ipv4.ip_local_port_range = 1024 65000
vm.pagecache = 10 20 30
kernel.shmmni=4096
vm.hugetlb_pool=4096

• Step 7: Unset db_cache_size, db_xk_cache_size, sga_target, sga_max_size, memory_target init.ora parameters and manually set shared_pool_size to appropriate value.  You can use “show sga” to determine this
• Step 8: Set use_indirect_data_buffers=true
• Step 9: Set db_block_buffers such that the product of db_block_buffers and db_block_size = 3G
• Step 10: In oracle’s .profile/.bash_profile, set DISABLE_MAP_LOCK=1.  This is required to avoid unnecessarily long connect times for databases that are connected to frequently

export DISABLE_MAP_LOCK=1

• Step 11: Stop oracle, source environment, ensure O/S limits are correct (ulimit -a), ensure /dev/shm is owned by oracle (ls -al /dev/shm), ensure DISABLE_MAP_LOCK=1, and then start Oracle
• Step 12: Add the following to /etc/rc2.d/S99local:

mount /dev/shm

chown oracle:dba /dev/shm

Test.  Enjoy the benefits of a large cache.  Test across reboots to ensure /dev/shm is mounted correctly.

In R12 the formatting of payments (Checks, ACH, wires) and the output of related documents (positive pay, Separate Remittance Advice) is done by a Java concurrent programs that generate XML output which is then formatted by BI Publisher.

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments. The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively. These files are located in the $IBY_TOP/patch/115/sql directory.

The package allows custom elements to be created at five levels within the payment XML extract. You cannot customize the package specification, but the package body contains stubbed functions that you can customize.  The five functions are as follows:

Instruction - FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at instruction level and run only once for the instruction.

Payment - FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

Document Payable - FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

Document Payable Line - FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

Payment Process Request - FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each payment process request.

Here are two examples where I utilized this extension:

1) Last Day of Period for Positive Pay file:

The Positive Pay File only provides the tree <PositivePayDataExtract> with <OutboundPayment> for each payment that it captures. In order to add data to the positive pay file XML  you must use the function IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg.

2) Count of ACH Transmissions per day:

This count was needed once per payment instruction so I utilized IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg to insert the new xml element at the bottom of </OutboundPaymentInstruction> (the root of the XML for payment batches). This funciton also appears in the XML stream for separate remittance advice, checks, etc.

Here is a screen shot of how we coded the Get_Ins_Ext_Agg:

IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg

I recently received a requirement to account for the entire deferred revenue amount of a sale, then recognize the revenue over time, based on consumption or clicks from a customer. Oracle Receivables has two “commitment” transaction classes to support this type of requirement.

The two commitment transaction classes are:

Guarantees: as you associate invoice transactions the amount due “moves” from the guarantee to the invoice associated to it.

Deposits: as you associate invoice transactions the amount due on the invoice is reduced or wiped out and all cash, collections, and aging can be tracked on the single deposit.

In my situation Deposits is the way to go for the following reasons:

One bill to the customer is required; we are billing the entire amount of the “deal” using installment terms (example: 25% of invoice every 30 days). Opposed to guarantees where there is an amount due on each invoice associated to the guarantee (not acceptable in my case).

Also, cash can be applied to the deposit, guarantees do not allow cash applications.

Finally, aging can be “centralized” as the deposit and installment payments are managed and reported under a single transaction. The impression invoice transactions are simply recognizing revenue in my model, in a supported, auditable way.

Accounting Flow for my setup:

1. Create the Deposit with installment terms
Example: $400K, $100K due at 30, 60, 90, 120 days.

Note: Detailed aging would show 4 installments of $100k due every 30 days.

2. Create a cash receipt applied to first installment.
Example: $100K paid for first amount due.

3. Create the impressions Invoice to recognize revenue - Linking it to the Deposit commitment Invoice.
Example: $90K in impressions delivered

Balance goes to $0 once associated to the deposit which has enough remaining dollars to cover entire invoice.

As a DBA, have you ever had an issue you suspected may be a bug and have been asked to generate “test data” for Oracle development?  We ran into this at a client recently and found a cool 11g utility (at least I *think* it’s new :)) to get Oracle Support the data they need to reproduce the issue in-house.

The situation had to do with optimizer_features_enable being set to 11.1.0.7 in a database recently upgraded form 9.2.0.5 in an Oracle eBusiness Suite environment (11.5.10.2) that utilized Oracle Reports heavily.  Our problem was specific to a custom Report that had optimizer hints embedded in the main query.  With optimizer_features_enable set to 11.1.0.7, the report retrieved no rows.  With it set to 9.2.0.x or 10.2.0.x, it returned the proper number of rows.  Based on this data discrepancy, we felt it safe to set optimizer_features_enable < 11.1.0.7 across the board, as we didn’t know the scope of the issue.

During our work on an SR with Oracle, they asked for us to send the query, all the versions and optimizer settings, as well as an export of all the tables involved in the query with exported optimizer statistics.  Our problem was several-fold; first, the query was complex and had many embedded views, so we didn’t really want to spend time deconstructing it to get a comprehensive list of tables to export.  Second, the underlying tables were very large (hundreds of millions of rows for a few of them) and we didn’t have disk space or quite frankly, time, to export all the tables in their entirety.

Enter DBMS_SQLDIAG …

Using DBMS_SQLDIAG and 11g Data Pump features, we were able to quickly generate a complete test case to export a subset of the rows from all the impacted tables, very quickly and with minimal disk space requirements.  Here’s what we did:

1. Grabbed the offending query from a TKPROF’d trace file
2. Used this syntax to generate a test case:

declare
tc_out clob;
begin
dbms_sqldiag.export_sql_testcase(directory=>’<directory>’,
sql_text=>’<SQL Text>’,
testcase => tc_out,
exportdata=>TRUE,
samplingpercent=>1);
end;
/

In the above example, note the following:

• <directory> is a valid directory - check DBA_DIRECTORIES
• <SQL Text> is the SQL statement from the TKPROF output
• exportdata=>TRUE tells DBMS_SQLDIAG to export the data from the base tables
• samplingpercent=>1 tells Data Pump to use a 1% sampling size.  This was important to limit the number of rows

After executing, a number of XML, log, and Data Pump export dumps are generated to <directory> and available to upload to the SR!

One additional step we took was to export table statistics for all the tables involved in the query.  For this, we looked in the log file for all tables export and used DBMS_STATS.EXPORT_TABLE_STATS to export segment statistics.

The obvious benefit here is that it enabled us to continue working on the SR, but some other possible applications of DBMS_SQLDIAG could be for internal testing purposes, testing functionality/performance across versions of Oracle without a complicated upgrade, regression testing, and so forth.

On a recent project we had a business and technology reason to connect Oracle EBS running 11g to a DB2 database.  After a few weeks of researching for the best practice and approach we decided on the following implementation steps.

1. Download a DB2 driver, such as from DataTek, and download to your application server in any storage directory.
2. Create a directory called “YM” under your custom application top.  example: $CUSTOM_TOP/java/YM.  (Assumes you have already created a $CUSTOM_TOP and it has a directory called “java”.
3. Copy all files from the storage directory to $CUSTOM_TOP/java/YM
4. Add 3 entries to s_adovar_classpath and s_adovar_afclasspath to point to the files in $CUSTOM_TOP/java/YM.
5. Make sure you run auto-config on your updated environment.
6. Bounce the application and database tier.
7. Perform any select, insert, update and or delete from the Oracle EBS application via java program to the DB2 database.  The java program will reference the DB2 connection from a jdbc connect string such as: “jdbc:oracle:db2://servername.com:port;databasename=xxxxxx; User=xxxx; Password=xxxx”

Most organizations leave their mission critical systems vulnerable to security threats and performance degradation. Recent studies show that organizational constraints are at the core of this issue. These constraints range from organizational policies, budgets and staff availability and even competency.

26 percent of organizations apply critical security and performance patches as they are released, 20 percent will eventually apply 6 to 9 months later while another 20 percent have no requirement at all to apply them. That leaves many companies data unprotected.

There are several ways to combat these constraints. One way is gain support from your organization. This can come in the form of support from executives, security / audit team and other critical policy makers. By establishing new and or updating existing security policies, and as a critical component, patching, these guidelines and timeliness of patching removes some of these constraints. A second way to protect and improve data integrity is to use technology. By keeping current on latest software, technology can help you reduce time and budget constraints of getting current applications and databases tested and allow your team to proactively assess the impact to your enterprise.

Centroid believes in 2 degrees of continuous change instead of wide arcs of re-activeness. These small degrees of change allows for continuous improvement across the organization. Whether it be through technology or process slight proactive adjustments make the enterprise perform at a higher degree then you can ever accomplish by large scale reactive measures.

Centroid is in the business of Future Proofing Oracle environments. In fact, Centroid is the leader in Future Proofing your Business and Technology and no one does it better. No one.

We have been using jdeveloper 10g to create new OA Framework forms for our R12 Oracle EBS applications 12.0.6 with database version of 10.2.0.4 without issues.

We then upgraded our database to 11.1.0.7.0 and EBS to 12.1.1.  That is when we encountered an issue with prior custom OA forms in EBS.  oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.IllegalAccessError, msg=oracle/jdbc/driver/OracleCallableStatement.

This issue/error is specifically related to the OracleCallableStatement used in 11g.  With 11g certain features/libraries are not available.  The Oracle Database 10g Release 2 (10.2) will be the last major release to offer the following feature/libraries: classes12.jar, oracle.jdbc.drivers.* and OracleConnectionCacheImpl. In other words these features/libraries will not be available in the next major database release (for example 11g).

Since our java code was using an OracleCallableStatement and also using the oracle.jdbc.drivers on the 11g database we encountered the error above.  This means that public Oracle JDBC driver classes that were referenced by oracle.jdbc.driver package must be referenced by using the oracle.jdbc package . Therefore, Java programs must not import public classes that  have belonged to the oracle.jdbc.driver package - but import  those classes from the  oracle.jdbc package.

Since our code was using “import oracle.jdbc.driver*” we had to replace it with import oracle.jdbc.*

Once we replaced the above statement; the OracleCallableStatement executed as expected.