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.

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.

Recently “The Business Week” June 15, 2009 ran an article titled ‘Taking AIM at outsourcers on US Soil’ If passed, the bill would bar companies with more than 50 employees in the U.S. from landing any additional work visas if more than half their US workforce is made up of H-1B or L-1 visa holders.  It definately appears there will be some controversy up on the hill.  If acted upon it would put a stop to the addition of H-1B resources coming to the US.  Senators Dick Durbin (D-Ill.) and Chuck Grassley (R-Iowa) are the two authoring the bill.  It would be good to hear your opinion on the subject…..I have mine!

Change Management, the mere mention of these two words make people cringe.

To paraphrase Gordon Gekko,

Change is Good…Change Works.

In this bloggers’ opinion, Change Management is a grossly misused, and misunderstood phrase.

At it’s core, Change Management should around change for the positive:

• Does change Increase Revenue?
• Does change Reduce Cost?
• Does change Improve Efficiency?
• Does it provide an environment for growth?
• Etc.

Often during Oracle EBS implementations, we see change is touted along with best practice, as a mantra for the project. People get nervous, raise their defenses, and the project suffers. Change is not something to enforce upon a business, most businesses have gotten to where they are working the way they’ve worked – “if it ain’t broke, don’t fix it”.

Change Management needs to be viewed not as an enforcer of change, but rather an enabler of change. Change allows businesses to move away from inefficient processes, to streamline practices, explore new avenues of growth. Change does not mean that everything you’ve been doing until now is wrong, just means there may be a better way.

Therein lies the art that is Change Management, helping business adopt change as a philosophy, a state of mind.

Why customize an application when you can rework the process.

Working with Oracle Enterprise Applications for over 18 years, I’ve seen many changes. Most changes are for the better, improved functionality, streamlined processes etc. However although the applications are growing rich in functionality, there’s an underlying trend that raises a concern. In today’s market it seems more and more, the path of least resistance is to customize applications vs. reviewing and re-engineering business processes.

A technical solution to a business problem often seems a cheaper path. This is often a misconception. Whilst a technical fix can provide a quick solution, the long term issues tend to be overlooked.

Often customizations are designed around facilitating a current business requirements. As business requirements change and evolve, companies find themselves in a constant state of changing their customization to facilitate the new requirements.

Business Process Re-Engineering or BPR was a buzz phrase in the 80’s and 90’s. It conjured up images of massive change in Organization processes, and practices, forced upon corporations by men in power suits, and aluminum briefcases.

I say it’s time to bring back BPR, minus the power suits and aluminum briefcases. BPR should be treated as a harmonization of business requirements and software functionality, change is good, change works….