Kevin Kempf's Blog

July 8, 2009

RDBMS 11g Cloning “Gotcha”

Filed under: 11g, Oracle — Tags: , , , , — kkempf @ 3:08 pm

As you might be able to tell, I’ve been doing some cloning (and cloning streamlining) lately and hit another obscure bug which required some attention. Toward the end of my cloning process I used the FNDCPASS utility to change the apps and non-apps but related (GL, AR, AP, etc) passwords so they differ from production. I’m going about my normal business of running the script which does this for me, and next thing I know, I can’t log in as apps with either the old or the new password. Bugger.

After making this mistake once, I re-cloned the environment (which is a VM) and took a snapshot before I started messing with the offending script. Two or three tries (and snapshot reverts) later, while following 159244.1, I’m out of ideas. I’m doing it verbatim from their doc. I even opened an SR with Oracle. I told the analyst I was running this in a VM. Thus it was of no value to me to try to troubleshoot the “broken” post-FNDCPASS condition, since I could just revert the snapshot. I think her head exploded when she read this. She didn’t know what a VM was, or if FNDCPASS could possibly even work in one. At this point, I (once again) gave up on support.

After trying to relink the binary without a change in behavior, I came to the sudden realization that I could not cite a case where I had successfully used this utility since going to RDBMS 11g. All my non-production environments were upgraded in place, and thus had never had this utility run against them. That’s when I found a note on Metalink which ultimately fixed the issue (751868.1). Curiously, it is worded to imply that this condition came up only in cases where you were migrated from 11i to R12, and also 10.2 to 11.1 RDBMS. Upon reading it in detail, however, you can see that R12 is irrelevant and this did fix my issue.

Reverted my snapshots, started RDBMS with sec_case_sensitive_logon=false in my init file, and voila, suddenly FNDCPASS correctly changes the password.

Advertisements

June 16, 2009

Locked Statistics

Filed under: Oracle — Tags: , , , — kkempf @ 8:12 am

I run the concurrent request gather schema statistics weekly with default settings, against the schema ALL. The past two weeks (since shortly after the 11g RDBMS upgrade) the job failed; I called the first one a fluke but by the second failure I needed to investigate. When I got in on Monday morning, the job was still running with large “concurrent program” spikes and the process was tied up on dictionary lock waits. Did a little digging on Metalink, and found Note 732082.1 which basically says, for whatever reason, you may need to unlock schema stats before your run the gather job. In the end, I just scheduled a pl/sql procedure to run before the stats job. I suppose I could have made it a request group for running statistics gathering; that may have been the “best” answer, but I didn’t want to pollute canned functionality in the apps. I also have to admit I don’t know if it will ever surface again, or why it happened this time. The truth is I just don’t want to have to think about it. 10-20 lines of code later and I’m covered

procedure unlock_schema_stats
is
cursor c1 is
select
distinct(owner) ownername
from
dba_tab_statistics
where
stattype_locked is not null
and
owner not in (‘SYSTEM’,’APPLSYS’,’SYS’)
;
begin
for c1_rec in c1 loop
dbms_stats.unlock_schema_stats(c1_rec.ownername);
end loop
;
end unlock_schema_stats
;

Create a free website or blog at WordPress.com.