Monday, September 30, 2013

Thirteen hundred

That may not seem like a lot to some people, but that is an insanely huge number of hits for my little ol' blog to have pulled in for the past month.  I wanted to start a new blog spotlight tonight, but I just felt so happy and appreciative of all my readers that I had to take a moment to thank you!!  I have quite a ways to go, to build this into a technological terror which I can manage a little more effectively as I've slipped behind reading the blogs on my list as Twitter has substituted some of that time, yet I shall return to my ways of mining blogs and sharing with you the nuggets and kernels that are interesting to those in Support!

I've posted every day for a full three months, and it has been a wild ride the entire time since being able to present in Pittsburgh at the end of July, up until this past week seeing a lot of people I respect and follow on Twitter enjoying the fruits of attending Open World.  I know that for me the next three months will hold a lot of surprises with us implementing R12, and I hope you enjoy the ride with me!

Sunday, September 29, 2013

Disconnected Concurrent Manager

Due to a network issue the other night, I got to see interesting error messages, starting with from the Oracle Alert product:

APP-FND-01564: ORACLE error 12203 in AFPCOA
Cause:  AFPCOA failed due to ORA-12203: TNS:unable to connect to destination
.
The SQL statement being executed at the time of the error was:  and was executed from the file .
/s_u01/<directories>/applmgr/<servername>/appl/alr/11.5.0/bin/ALECDC
Program exited with status 1

So what really was unable to get to the destination?  Well on several concurrent manager nodes we were receiving these error messages:

Invalid option.
Usage: CONNECT <username> [AS SYSDBA|SYSOPER]
Invalid option.
Usage: CONNECT <username> [AS SYSDBA|SYSOPER]
unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Failed to get arguments
ORACLE error 1012 in FDPCLS
Cause:  FDPCLS failed due to ORA-01012: not logged on
The main COMPLETION_TEXT being returned was "Concurrent program returned no reason for failure." or "Program exited with status 1" or "Concurrent Manager encountered an error while running SQL*Plus for your concurrent request <ID>.
Review your concurrent request log and/or report output file for more detailed information."

Additionally, our Workflow Mailer went down with this error:

[Sep 24, 2013 7:12:24 PM MST]:1380075144128:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:<CM node 1>:<IP>:-1:-1:UNEXPECTED:[SVC-GSM-WFMLRSVC-1893136-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.open()]:Unable to connect to tranport
ERROR:
ORA-12203: TNS:unable to connect to destination

On a whole, most anything that needed to have a stable network connection from one point to another was disrupted so this aftermath made us take down the Concurrent Manager on both nodes and restart them to make sure everything was clean.

Saturday, September 28, 2013

Weekend Mystery: Primary Key Indexes

A few months ago I read this article from Arup Nanda, but it wasn't until re-reading it tonight that the lesson he was teaching us really sunk in for me.  If a table has a primary key, you'd naturally think that duplicate values can't exist as the primary key right?  WRONG!  Arup shows us exactly why, as well as explaining why this is actually desired in some situations which taught me even more.  Explore the mystery this weekend!

Friday, September 27, 2013

My first R12 bug!

After our last dress rehearsal for applying R12, we encountered a problem with all of our Oracle Alerts giving us this error:

/s_u01/app/applmgr/<instance>/apps/apps_st/appl/alr/12.0.0/bin/ALECDC

Program was terminated by signal 11

This was really interesting since we were under the impression that nothing changed from our prior mock run, other than maybe some setup but nothing we knew would cause this.  Eventually we started asking questions while the DBAs were opening an SR with Oracle, and found out that we had applied the following patches:

13855823 - 1OFF:12373900:12.1.3:12.1.3:QREP1220.2:FND.RLNK:RELINK FAILED FOR MODULE FNDCPAS

14532468 - HIGH ENQ: TX - ROW LOCK CONTENTION ON FND_CONCURRENT_PROCESSES

10152652 - OPATCH to be applied to 10.1.2ORACLE HOME

Oracle came back and told us that they weren't sure of the reason, but we had to apply these patches to get our problem resolved:

Patch 13728376 - Alert fails with signal 11 error after password case sensitivity fix.

Patch 9908378 - RELEASE 12 BUG FOR SIGNAL 11 WITH ALPPWF
What is interesting in this case is our PROD password for the ALERT FNDassuming that the patches we applied directly affected this.  Additional testing showed that in another R12 instance without these patches applied, Oracle Alerts were working without issue and only after patch 10152652 was applied (with Alerts still working) then had 13855823 applied did Oracle Alerts get broken!

Thursday, September 26, 2013

Thursday Twitter - 9/25/13

You may notice that some of these are from a few days ago, and that is because I haven't had a chance to catch back up from everybody sending out Tweets from OOW about everything going on there!

https://twitter.com/tanelpoder/status/382617585352589313 - Great advice from Tanel, and I get the Hot Topics email from MOS but I need to revisit my settings to see about this option.


https://twitter.com/jloracle/status/382387261574111232 - Jonathan Lewis hits it on the head, and it is surprising how often people have this misperception!

https://twitter.com/kylehhailey/status/382256835513708544 - I would've LOVED to have been a fly on the wall for this discussion, and this just makes me more dedicated to get to OOW and OTW next year!

https://twitter.com/fritshoogland/status/382250670482411520 - Fritz shares an update that a new Exadata platform announcement won't be coming for a few months at least.

BONUS:  Here are a few extra Tweets that were fun!

https://twitter.com/mc_ux/status/382177511452340224 - There is a new phrase that I'll keep in mind the next time I see something like this happening!

https://twitter.com/jloracle/status/381813865647837184 - Again, Jonathan Lewis comes through with an awesome insight on "the process" and why you can't get something for nothing.

Wednesday, September 25, 2013

In Depth: Data Integrity

Have you ever wondered about the technical nature of data and the associated integrity?  How about some additional insights into constraint enforcement or global and distributed transactions?  Well then, you're in luck because Lucas Jellema does an amazing job here, found via ArchBeat, of not only writing up a paper that is easy to understand but uses several visual aids which seem simple but do a really good job of explaining some complex ideas to us.

Tuesday, September 24, 2013

What happens when your Concurrent Manager goes crazy?

To answer the question I posed, I'll share with you a scenario we ran into a few months ago.

On a day like any other, at 11 AM we received an alert that we had created to tell us when users had queued up reports and their FND_USER records had become end dated.  This was created as a result of the issue detailed here that we encountered after upgrading our database to 11.2.0.2 when we went to Exadata:

“Scheduled Requests Running for END_DATED Users 100% CPU and Giving ORA-01002 [ID 1080047.1]”

but the alert showed that the reports were Pending and the bug is obviously about Running reports right?  So later in the day when the DBAs asked why Concurrency was so high, we really didn't think about these reports or the alert we received on them since the alert came in at 11 AM and the graph shows 5 AM.


At the time, I wanted to say that it was a problem maybe with the manager itself, with this nasty SQL behind the sql_id taking up the Concurrency time:

SELECT R.Conc_Login_Id, R.Request_Id, R.Phase_Code, R.Status_Code, P.Application_ID, P.Concurrent_Program_ID, P.Concurrent_Program_Name, R.Enable_Trace, R.Restart, DECODE(R.Increment_Dates, 'Y', 'Y', 'N'), R.NLS_Compliant, R.OUTPUT_FILE_TYPE, E.Executable_Name, E.Execution_File_Name, A2.Basepath, DECODE(R.Stale, 'Y', 'C', P.Execution_Method_Code), P.Print_Flag, P.Execution_Options, DECODE(P.Srs_Flag, 'Y',  'Y', 'Q', 'Y', 'N'), P.Argument_Method_Code, R.Print_Style, R.Argument_Input_Method_Code, R.Queue_Method_Code, R.Responsibility_ID, R.Responsibility_Application_ID, R.Requested_By, R.Number_Of_Copies, R.Save_Output_Flag, R.Printer, R.Print_Group, R.Priority, U.User_Name, O.Oracle_Username, O.Encrypted_Oracle_Password, R.Cd_Id, A.Basepath, A.Application_Short_Name, TO_CHAR(R.Requested_Start_Date,'YYYY/MM/DD HH24:MI:SS'), R.Nls_Language, R.Nls_Territory, DECODE(R.Parent_Request_ID, NULL, 0, R.Parent_Request_ID), R.Priority_Request_ID, R.Single_Thread_Flag, R.Has_Sub_Request, R.Is_Sub_Request, R.Req_Information, R.Description, R.Resubmit_Time, TO_CHAR(R.Resubmit_Interval), R.Resubmit_Interval_Type_Code, R.Resubmit_Interval_Unit_Code, TO_CHAR(R.Resubmit_End_Date,'YYYY/MM/DD HH24:MI:SS'), Decode(E.Execution_File_Name, NULL, 'N', Decode(E.Subroutine_Name, NULL, Decode(E.Execution_Method_Code, 'I', 'Y', 'J', 'Y', 'N'), 'Y')), R.Argument1,  R.Argument2,  R.Argument3,  R.Argument4,  R.Argument5, R.Argument6,  R.Argument7,  R.Argument8,  R.Argument9,  R.Argument10, R.Argument11, R.Argument12, R.Argument13, R.Argument14, R.Argument15, R.Argument16, R.Argument17, R.Argument18, R.Argument19, R.Argument20, R.Argument21, R.Argument22, R.Argument23, R.Argument24, R.Argument25, X.Argument26, X.Argument27, X.Argument28, X.Argument29, X.Argument30, X.Argument31, X.Argument32, X.Argument33, X.Argument34, X.Argument35, X.Argument36, X.Argument37, X.Argument38, X.Argument39, X.Argument40, X.Argument41, X.Argument42, X.Argument43, X.Argument44, X.Argument45, X.Argument46, X.Argument47, X.Argument48, X.Argument49, X.Argument50, X.Argument51, X.Argument52, X.Argument53, X.Argument54, X.Argument55, X.Argument56, X.Argument57, X.Argument58, X.Argument59, X.Argument60, X.Argument61, X.Argument62, X.Argument63, X.Argument64, X.Argument65, X.Argument66, X.Argument67, X.Argument68, X.Argument69, X.Argument70, X.Argument71, X.Argument72, X.Argument73, X.Argument74, X.Argument75, X.Argument76, X.Argument77, X.Argument78, X.Argument79, X.Argument80, X.Argument81, X.Argument82, X.Argument83, X.Argument84, X.Argument85, X.Argument86, X.Argument87, X.Argument88, X.Argument89, X.Argument90, X.Argument91, X.Argument92, X.Argument93, X.Argument94, X.Argument95, X.Argument96, X.Argument97, X.Argument98, X.Argument99, X.Argument100, R.number_of_arguments, C.CD_Name, NVL(R.Security_Group_ID, 0) FROM fnd_concurrent_requests R, fnd_concurrent_programs P, fnd_application A, fnd_user U, fnd_oracle_userid O, fnd_conflicts_domain C, fnd_concurrent_queues Q, fnd_application A2, fnd_executables E, fnd_conc_request_arguments X WHERE R.Status_code = 'I' And ((R.OPS_INSTANCE is null) or (R.OPS_INSTANCE = -1) or   (R.OPS_INSTANCE =     decode(:dcp_on,1,FND_CONC_GLOBAL.OPS_INST_NUM,R.OPS_INSTANCE))) And R.Request_ID = X.Request_ID(+) And R.Program_Application_Id = P.Application_Id(+) And R.Concurrent_Program_Id  = P.Concurrent_Program_Id(+) And R.Program_Application_Id = A.Application_Id(+) And P.Executable_Application_Id = E.Application_Id(+) And P.Executable_Id             = E.Executable_Id(+) And P.Executable_Application_Id = A2.Application_Id(+) And R.Requested_By   = U.User_Id(+) And R.Cd_Id = C.Cd_Id(+) And R.Oracle_Id = O.Oracle_Id(+) And Q.Application_Id = :q_applid And Q.Concurrent_Queue_Id = :queue_id And (P.Enabled_Flag is NULL OR P.Enabled_Flag = 'Y') And R.Hold_Flag = 'N' And R.Requested_Start_Date <= Sysdate And ( R.Enforce_Seriality_Flag = 'N' OR  ( C.RunAlone_Flag = P.Run_Alone_Flag  And (P.Run_Alone_Flag = 'N'  OR Not Exists  (Select Null From Fnd_Concurrent_Requests Sr  Where Sr.Status_Code In ('R', 'T')  And Sr.Enforce_Seriality_Flag = 'Y'  And Sr.CD_id = C.CD_Id))))  And Q.Running_Processes <= Q.Max_Processes  And R.Rowid = :reqname  And ((P.Execution_Method_Code != 'S' OR (R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID) IN ((0,98),(0,100),(0,31721),(0,31722),(0,31757))) AND ((R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID) NOT IN ((0,36888),(111,31715),(140,20445),(140,20810),(140,38315),(200,20532),(200,36067),(200,36068),(201,32439),(201,32460),(201,32469),(201,32844),(222,20865),(222,20893),(222,31738),(222,31893),(222,38640),(510,43959),(510,43960),(510,43966),(510,43967),(530,49102),(530,49106),(535,43927),(535,43928),(535,43929),(20004,31959),(20004,31961),(20004,31971),(20004,32058),(20004,33549),(20004,33625),(20004,41677),(20009,31993),(20009,32032),(20009,32164),(20009,32243),(20009,32250),(20009,32349),(20009,33564),(20009,33605),(20009,33679),(20009,33689),(20009,34482),(20009,34924),(20009,35325),(20009,35385),(20009,37049),(20009,43079),(20009,46390),(20009,56086),(20033,33685),(20033,39049),(20033,48498),(20033,48499),(20033,48518),(20033,48519),(20033,48539),(20033,48540),(20033,48558),(20033,48560),(20154,37533),(20154,37538),(20154,37909),(20154,39109),(20154,39169),(20154,39189),(20154,39457),(20154,39497),(20154,39517),(20154,39677),(20154,40377),(20154,40517),(20154,40537),(20154,40737),(20154,40818),(20154,41798),(20154,41857),(20154,41858),(20154,41897),(20154,42171),(20154,42173),(20154,42417),(20154,42697),(20154,42838),(20154,43039),(20154,43077),(20154,43297),(20154,43558),(20154,43777),(20154,43797),(20154,43877),(20154,45870),(20154,45872),(20154,45990),(20154,46070),(20154,46694),(20154,46695),(20154,46710),(20154,47030),(20154,47211),(20154,48418),(20154,50610),(20154,50611),(20154,50651),(20154,50880),(20154,50901),(20154,51141),(20154,51183),(20154,51242),(20154,51244),(20154,51323),(20154,51361),(20154,51463),(20154,51481),(20154,51801),(20154,51946),(20154,52383),(20154,52384),(20154,52385),(20154,52386),(20154,52388),(20154,53643),(20154,54063),(20154,54104),(20154,55482),(20154,55500),(20154,55881),(20154,55900),(20154,56360),(20154,56521),(20154,56930),(20154,56931),(20154,56932),(20154,56933),(20154,56934),(20154,56935),(20154,56936),(20154,56937),(20154,56938),(20154,56939),(20154,56940),(20154,56941),(20154,56942),(20154,56943),(20154,56944),(20154,56962),(20154,56963),(20154,57158),(20154,57159),(20154,57248),(20154,57284),(20154,57333),(20154,57334),(20154,57507),(20214,39804),(20214,39838),(20214,40937)) AND R.REQUESTED_BY NOT IN (20829,21465,192077))) FOR UPDATE OF R.status_code NoWait

Which looks like it is the actual manager locking (FOR UPDATE) the records when they were coming in ready to run the reports.  Yet when I looked at the sessions, new sessions were popping in and out with recent logon_time values, all from applmgr on Concurrent Manager node 2 and many of the events are listed as “library cache: mutex X” (references here and here for articles I DID find that had some relevance).  The next day, I decide to look at this from a different view point since what I had been doing was to query the DB to try and find what was holding a lock (with no success).  That's when I decided to look closer at the OS on CM node 2, which was the only CM server showing as having locked processes, by doing a top command, and found that 4 different threads were trying to take 100% of the CPU below:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
3165 applmgr   25   0 3069m 3.0g 2904 R 100.1  4.7 496:35.38 FNDLIBR
19021 applmgr   25   0 3069m 3.0g 2840 R 100.1  4.7 496:35.03 FNDLIBR
21783 applmgr   25   0 3068m 3.0g 2912 R 100.1  4.7 493:46.01 FNDLIBR
29837 applmgr   25   0 3068m 3.0g 2828 R 100.1  4.7 493:28.17 FNDLIBR

Using those PIDs, I then ran this command:

select * from fnd_concurrent_requests
where os_process_id = &OS_PID

What was odd is that each PID was giving me somewhere around 25-50 results when I was expecting a single row, until I realized that this corresponded to a dedicated concurrent manager process running in Financials.  Going into the application, I went to the Administer Concurrent Manager form (FNDCPVCM) and looked for what CMs were being run by CM node 2 and found that Standard Manager was Active on that node.  Clicking on this entry and then Processes presented me with all the processes (Active, Deactivated, or Terminated) where I noticed that the values under System looked an awful lot like our OS PIDs so I queried on one and low and behold a. it returned an Active process and b. the log file of the process showed that it was thrashing on a user with inactive responsibilities just like in the My Oracle Support note above.  The error message was initially:

Cause:  UPDATE_STATUS failed due to ORA-01002: fetch out of sequence.

and knowing it was related to an end_dated user I tried to extend the end date for the responsibilities by an extra day, but instead of making everything happy in CM land it just changed the error message being produced to:

Cause:  UPDATE_STATUS failed due to ORA-01008: not all variables bound

with the SQL statement changing from FND_CONCURRENT_REQUESTS (which was in exclusive mode due to the FOR UPDATE part of the statement) to FND_USER.  I don't know if this is because all 4 threads were still trying to hold all the server resources, and it would have returned normally after burning itself out on the statements it was going through, but this is where I deactivated the Standard Manager when there were no long running requests in the CM.  Monitoring for a few minutes with no change, after all jobs Running had completed, I had to Terminate the CM because those threads were still showing as Active and were still taking up 100% of the CPU on the server.  Once Terminated those 4 processes no longer showed as being Active and the thrashing which the DBA alerted us to, ceased to be.  With the processes gone, I could now put the user requests which caused all of this On Hold so that the CM wouldn't find itself in the same old situation once we turned everything back on.  I then Activated the Standard Manager, verified that no threads were hitting 100% on the CM node, and called it a job well done after I made sure the user still needed their reports after transitioning to another position in the company (which is why their account was end dated in the first place).

The moral of the story is that even though all the processes were Pending, so I thought there wasn't a problem, it appears the bug which we created this alert for affects the system not only when the request is Running and it can't finish but it also hits us when the system TRIES to run something and the user/responsibilities are end dated!

Monday, September 23, 2013

Application refusing to let users in

On Saturday morning, our non-Exadata 11.5.10.2 EBS instance running on 10.2.0.4 would prompt users trying to log in with the error message "Oracle error &ERRNO: &REASON has been detected in &ROUTINE."  Obviously that's not right, and it forced the DBAs to bounce the Apache web services to clear the error but every week or two the error message would return requiring that Apache bounce to clear it.  Oracle suggested that our solution was applying patch 6823287 for the bug that we started encountering, or a workaround is to set the initialization parameter session_cached_cursors=0.

I'll give you an update on if this resolves our issue, as the bug doesn't say it has been reproduced in our DB version, but I wanted to put this out into the community now in case others start to experience this error.  As an aside, I say "start to experience" for a reason since we've made few changes to this instance, none of them major, but years after being upgraded from 11.5.9 this started to creep up out of the blue for no reasons we can determine.

Sunday, September 22, 2013

BREAKING NEWS: OOW13 News

We interrupt your regularly scheduled blog --

Larry Ellison has just announced a new Database In-Memory Option which can make queries 100 times faster, but better than that is the faster transaction rates such as 3 to 4 times faster row insertion.  How is this being done?  Oracle 12c stores data in rows and columns simultaneously with this new option so the columns (introduction of "Columnar technology") are being kept "in-memory" and transactional integrity is kept due to the fact that there are no transactions being saved back on the columns, just the rows.  Cores will be able to scan billions of rows a second per vector instruction, and report results will be returned sub-second because the current OLTP architecture is being slowed down by analytic indexes which the column views will actually take advantage of.

This option makes the database easier to tune, run faster, just as secure as today but once the switch is flipped you get better performance even though nothing structurally changes when you flip the switch.  A live demo of a 3 billion row table (courtesy of Wikipedia data) showed how it was more than 1000 times faster when the In-Memory Option is turned on!  This allows companies to Scale-Out and Scale-Up with different types of hardware.  Speaking of hardware, Larry introduced several new components:

M6-32 Big Memory Machine - This has 32 Terabytes of DRAM memory, 32 SPARC M6 chips (twice as many cores as the M5), and as he casually mentioned is available right now.  Today.  Go buy one!  Then he elaborated on the Interconnect it uses to process 3 TeraBYTES, not TeraBITS, per second and on top of everything else the machine costs $3M which is less than a third of the cost of IBMs biggest machine.  He noted that their IBM partners in the room weren't clapping.  Awesome.

M6-32 SuperCluster - Just like it's brother the Big Memory Machine, it is the fastest database machine out there but the plus for this is that it connects to Exadata as integrated storage to speed things up by a factor of 10 via the Infiniband I/O Interconnect.

Oracle Database Backup, Logging, Recovery Appliance - Quite a mouthful, but Larry says he named it so I guess that's that.  This doesn't act like a file backup utility that loses data, slows down the business, and isn't scalable; instead this platform which is "like a Exadata machine" ships all of the logs so it results in the ability to restore as of any point in time.  Going a step further, this can all be delivered as a Cloud Service!
He closed with a snippet of what is the "Datacenter of the Future", which I think is a sneak-peak of things to come, where he details out a data center that has specialized servers for the core, backup, analytic and database functions to increase what customers can get out of their data centers.
-- We now return you to your regularly scheduled blog.

Saturday, September 21, 2013

Weekend Reading: Oracle BPM performance

I'm filing this one under "some day when I get access and learn more about our web services infrastructure", but if you have access and run the Oracle Business Process Management (BPM) Suite this is one document you need to read.  Even if you aren't running BPM, I think there are several "generic" suggestions especially early on in the paper to help learn more about what your goal is and better behaviors around performance tuning.

Friday, September 20, 2013

Breaking News: EBS R12.2 Announced!

We interrupt your regularly scheduled blog --

Normally this isn't the type of thing I'm really up on when announced, but now that I'm getting a bit more connected to the community, you just might find more of these "Breaking News" type of posts on the site!  Thanks to Yury, I heard that Steven Chan's blog has announced the availability of Oracle EBS R12.2 with all the features I was introduced to during the OAUG Connection Point - AppsTech conference (and which I shared with you in this post and this one too).  Go visit Steven's blog for the final edits to the R12.2 plan, read the full Press Release, and be the first to get your hands on the R12.2 Technical Planning Guide.

-- We now return you to your regularly scheduled blog.

Thursday, September 19, 2013

Thursday Twitter - 9/19/13

Continuing my new feature, I bring you 5 tweets which share important information or ideas!

Richard Foote has a great quote that he applies to his mentality towards tuning and troubleshooting.  Anybody else feel like that too?  I know I do, and that's why I'm challenging myself to keep learning and knowing more about what's under the hood of the Oracle stack.

David Kadavy is offering a free 12-week e-mail course to teach design, sign up by September 30th.  I'm debating on joining this, because I could probably stand to know more about design and you never know when you'll learn something valuable or lasting if you engage outside your space.

Oracle Press has a FREE eBook Securing Oracle Database 12c: A Technical Primer that you can register for.

Giuseppe Maxia lets us know that there is a Twitter list for Oracle ACEs and I've subscribed.

THAT Jeff Smith shares the top 10 popular Oracle SQL Developer posts of 2012.

Wednesday, September 18, 2013

CMCLEAN: Zombie Hunter?

The other day I was tracking down a mystery (more on that later) when one of our Exadata database nodes hard crashed and we had to pick up the pieces.  While I was tracking down that mystery, I happened to be on our concurrent manager nodes and saw that neither node had more than 3 processes at the OS layer marked as zombie processes.  What do you think I found after the crash?

We now had 38 zombie processes "alive" on one of the nodes!  As we were cleaning up the system, I kept asking "what about the zombie processes?" to which the DBA told me "don't worry, CMCLEAN will take care of it" but he didn't exactly tell me how or if he really knew for a fact that CMCLEAN would take care of the zombies.  After we took down the concurrent manager nodes and ran CMCLEAN, guess what: all the zombie processes were gone.

So this leaves me to wonder if they disappeared when the nodes were taken down, or if CMCLEAN actually did the job.  Next time, I'll have to watch a bit closer to find out for sure but if you know the answer share in the comments!

EDIT:  For some reason the Blogger iOS app doesn't include changing the "Published On" date, and Safari on my phone wouldn't load the site right/completely, so this showed up yesterday on the blog as created on 9/12/13 instead of when I posted it 9/18/13.  Sorry for burying it on the site!

Tuesday, September 17, 2013

RMAN: To connect, or not to connect?

For once I'm not going to focus on 12c; instead, I'm going to introduce you to a new feature in 11gR2 that really blew my mind.  RMAN does not need to connect to a target database in order to duplicate the target database.  Let that sink in for a minute or two.  Now if you are experiencing some type of corruption on your live system, you can grab your backup tapes and not even have to mount or open the database at all.  Amazing!!

Monday, September 16, 2013

Oracle Linux checks your data

Here you can see that Oracle is stepping up their efforts to combat data errors with data integrity protection around not only the whole system, but during the entire trip that the data takes it is being checked for validation.  Read the linked article to see a picture of this, as well as learn what creating integrity metadata is all about and how it relates to data integrity protection in an end-to-end disk-to-application solution.

Very interesting subject and a great visual presented, but I wonder why this solution only has EMC onboard as the first enterprise storage array vendor to join this effort by Emulex and Oracle.

Sunday, September 15, 2013

12c September - Atul Kumar

Usually I give you homework for the weekend, but this time I'm giving you a boat load of articles from Atul Kumar of onlineappsdba.com to go through during the upcoming week so you can digest them some!

Here he shares some issues encountered while using the OEM in 12c to manage Oracle HTTP Server, which includes links to several other articles on how to configure the monitoring of Oracle Fusion Middleware with OEM like this one about some lessons learned.

Another plug (yes, pun intended) for reading Oracle's whitepaper on Oracle Multitenant.

A good listing of the changes and new features for WebLogic with 12c.

Finally, Atul shows you how to monitor the Oracle Identity Connector Server with OEM in 12c which isn't exactly something we use or would be relevant except for the fact that he shows some very generic information that you can use to monitor other services as well.

Saturday, September 14, 2013

12c September - Kyte, Osborne & Poder

Sounds like a law firm right?  No, it's just three of the smartest individuals around if you have Oracle issues (one of which will be featured soon) so it makes sense I'd put together their posts today on 12c features.

I'm going to start with Tom Kyte sharing the CBAC (CODE Based Access Control) feature introduced with 12c which pretty much just blew my mind.  I loved the conversation that took place in the comments too!

Yesterday I did share what Tim Hall talked about, but Tom goes over the Implicit Results sets with some additional insights into why it was implemented as well as showing examples too.

Do you know what new bells and whistles that have been added in 12c?  Tom does, and now so can you.  Oh and you might learn something about invisible columns as well!

Another one of the game changer type of features being introduced with 12 is the concept of white listing your code.  You seriously need to see what Tom shares about this!

Here we are introduced to a new bug with using SQL Translation Framework by Kerry Osborne, and to the actual SQL Translation Framework feature itself which appears to have been created to allow us to address performance issues without changing the code!

Then Kerry helps us understand how to control the dial for automatic degree of parallelism.

Last, but not least, is Tanel Poder showing us how the CBO in 12c transforms scalar subqueries that are being used in the SELECT statement.

Friday, September 13, 2013

12c September - Tim Hall

When he's not traipsing around Latin America, Tim Hall has had some really great 12c related posts on his blog recently.

He has created some articles on Implicit Statement Results and Extended Data Types with 12c, even if he wasn't excited about them at all!

Then he goes over Identity Columns and DEFAULT Values in his post here, and also has to update an old post on AutoNumber too.

Even more new features are covered in several articles on row limiting for Top-N queries and Multithreaded Model using THREADED_EXECUTION which also required him to re-write other articles as well.  You really need to read some of the articles on this page about killing sessions/threads if you are going to 12c, as I've learned some new tricks for our environment and some are absolute killers (pun intended) for 12c!

Tim even helps us understand the WITH clause and how it has been enhanced with 12c, so if you are excited about that then finding out what the new tricks the DBFS is up to in 12c will probably knock your socks off!

Other than some good links to certification for 12c, this post outlines some "issues" he has found with the upcoming exams as well as a good general article about Oracle certification.

Thursday, September 12, 2013

Thursday Twitter!

Interrupting our 12c weeklong coverage, I introduce a new feature where I share 5 tweets that I've favorited:

https://twitter.com/oakies_blog/status/376434736019038208 - Karen Morton will have a new webinar on the 17th!

https://twitter.com/thatjeffsmith/status/376695554723438592 - Jeff Smith (yes, THAT Jeff Smith) shares how to organize connections into folders for SQL Developer.

https://twitter.com/martindba/status/375915850198298625 - Martin Bach helps show us some scripts from Enkitec.

https://twitter.com/dbakevlar/status/374971089530523648 - Kellyn Pot'Vin reminds us to think about how our SQL profiles on a regular basis, and makes me think about how to alert based on it.

https://twitter.com/oraclepdb/status/375693383399776256 - Tom Kyte in a video talking about Oracle Multitenant. 

Wednesday, September 11, 2013

12c September - Jonathan Lewis

Jonathan Lewis deserves his own week-long blog spotlight, but until I do that, he gets a mini-spotlight today on several 12c related articles he's written lately!

Here he investigates the differences between 11g and 12c where a new type called "Top-N" is being introduced, in part two he compares simple frequency histograms and Top-N histograms (and goes quite a bit above my head), then he compares some previous histogram issues to explore if they have been resolved with 12c.

In 12c there are several changes to subquery factoring that he shares, and also details the changes to join views too!

While even though the title says "Manuals", this article has some interesting discussion in the comments about what the true maximum services per instance are versus what Oracle has documented.

Tuesday, September 10, 2013

12c September - Features

Yesterday I promised a week of 12c content, and today is a focus on new features of 12c!

SQL for Pattern Matching is an extension of the SELECT command using MATCH_RECOGNIZE which allows you to look for patterns across your results.

A whole slew of data mining functions are being introduced or updated with 12c!

The parameter RedoRoutes has been introduced for Data Guard in 12c, among other additional enhancements, with a great example that was found via the Log Buffer.
Charles Hooper lets us know that deadlock changes are ahead in 12c with an interesting walkthrough.

Did you know that network recovery with RMAN is now possible with 12c?

Some resources to make your installation of 12c on Oracle Linux easier.

The Flashback feature in 12c is now available for all versions of the database and gets more enhancements as well via ArchBeat!

The tables DBA_USERS and DBA_ROLES have the new field ORACLE_MAINTAINED added in 12c, found via Log Buffer.

Monday, September 9, 2013

12c September catch up

Last week I promised some content on 12c, but over the few months I had gathered SO much information that it deserves it's own week with over 50 different articles reviewed and linked for you!  Today I start with content about the announcement, or other general "new" type of information which didn't fall under any other categories I put the posts in for this week.

Why should you be running 12c on Oracle Solaris?  Find out with an article that has several links to whitepapers, videos and deep dives!

Maria Colgan, via her Oracle Optimizer blog, announces new details that 12c brings to the Optimizer.

Ric Van Dyke helps announce the new version of the database too!

The full press release is linked here.

A glowing report on 12c from Ovum, which Oracle has hosted for us.

Tanel Poder helps to announce 12c as well, his article with a link to the documentation in case you haven't found it yet.

Pythian's Rene Antunez shares their first five minutes with 12c, which is from a DBA perspective about some of the new features/terminology.

Since 12c was available for Windows, Tim Hall decided to install it on Windows Blue 8.1!

Sunday, September 8, 2013

Exadata IORM analysis

Carlos Sierra shared an excellent article from a co-worker of his on tracking down a big problem involving Exadata, multiple databases, I/O throttling, and several wait events like "cell smart table scan" and "cell single block physical read".  It's a great read not only because it shares how to walk through a problem like this, but it introduced several new tools starting with the My Oracle Support note that is included in the article, and has me more knowledgeable about what IORM is.

Saturday, September 7, 2013

Keeping Exadata nodes maintained

From an interesting article I ran across on LinkedIn, Mike Chafin gives a step by step for how to create disk monitoring for Exadata Compute node disks with Enterprise Manager.  I need to get this over to our DBAs as they are the primary users of EM in our shop, since we could always use more alerting and Mike's article includes that as well for us!

Friday, September 6, 2013

Weekend Reading: Exadata & DB Storage

The weekend has just begun, but that doesn't mean you can escape without getting homework assigned!  I think these go together well, and soon I'll be devouring them when I get a chance to sit down for a few hours and catch up on my reading.  First, is Database Storage for Dummies; no seriously I can't make this up!  Then next up is the first entry in the Exadata Workbook series that covers planning, installing, operating and maintaining an Exadata platform so I want to go through this looking for opportunities to enhance the support of the platform.

As an extra bonus this weekend, you should also read the article at My Oracle Support 1274322.1 on Exadata High Availability best practices too!

Thursday, September 5, 2013

Oracle Advanced Compression highlights

Did you know that Oracle Database version 11g brings with it a host of new features, one of which is Oracle Advanced Compression?  If you didn't, you may want to browse this article which has a testimonial on how this feature allowed a customer to reduce their footprint by 50 percent.  Or, you can skip to the next article to see how another client reduced their database resources by 400 percent and you can find tools that let you self-assess your savings with and without compression.

Wednesday, September 4, 2013

Statspack bug(s) and fix(es)

This article highlights a bug (and fix) with the STATSPACK package, which reminds me of another bug we have encountered and are going to be fixing if it still exists after our upgrade to R12.  Currently in our 11.2.0.3 environment, and the bug exists in 11.2.0.2 as well, we encounter the following entry in the database log file every week when a job tries to run:

ORA-12012: error on auto execute of job "PERFSTAT"."STATSPACK_PURGE_61"
ORA-04063: package body "PERFSTAT.STATSPACK" has errors
ORA-06508: PL/SQL: could not find program unit being called: "PERFSTAT.STATSPACK"
ORA-06512: at line 2

You can see with this script, that several objects are marked as INVALID:

select object_name, object_type, status from dba_objects where owner = 'PERFSTAT' and status = 'INVALID'

The document you want to see to fix this on My Oracle Support is 1337034.1, and if you want to apply the patch and fix it I'd be interested to hear from you!

Tuesday, September 3, 2013

An old emperor in new clothes

As many of us are returning to work today, I wanted to share an interesting situation from last week to re-energize you for the short week!  I've mentioned that we've hit a nasty database bug which affected our 11.2.0.2 environment, and still affects the database even after upgrading to 11.2.0.3, so we've created an alert for it and know how to handle those situations.  The other day, we had another situation where it appeared that the inactive transaction branch bug was now affecting internal database transactions which did not go out over a database link at all.  Very interesting, and very scary prospect!

I did some poking around but couldn't really come up with anything proving that this session wasn't going out over a database link, so I talked with one of our top developers about it since he usually has some really great assistance and guidance on issues like these, and he said he'd take a look.  A little while later, he came back to me and just about knocked my socks off; the session was not internal, and it was going over a database link.  We had saw the following script in gv$sqlarea:

select sequenceX.nextval from dual

which doesn't appear to be using any database links, right?  Well the developer had found that the object had a synonym created, with the entry in DBA_SYNONYMS pointed across the database link so when we looked into the gv$sqlarea on that database with the same sql_id we found the same SQL being run!  This proves out that what appeared to be a local session was in fact NOT a local session after all, and that is why it was possible for the session to be stuck in the "inactive transaction branch" wait event class.

Monday, September 2, 2013

About 30 times a day

Since today is a day off for many of us, it's a time to reflect on the things that are going on in our lives and that is what this blog post is about.  Last month, I had the pleasure of seeing the blog having the highest traffic volume in total and hits per day, so that the site fell just a single post away from having 930 hits for the month and exactly 30 times a day for the month of August.

Seeing the site take off like this is really an amazing experience and I couldn't do it without all of you out there, so thank you!!  This coming week should be an exciting series of posts as I'll be highlighting Exadata, 12c, and other "advanced" technology items which just may include some bugs.  :}

Sunday, September 1, 2013

EBS Workflow

In closing our spotlight for the EBS Support blog, are some articles about the Oracle Workflow module which as you know is something I've spent some time with recently!  First, is an article on a subject that I addressed in my presentation and whitepaper about what happens when data is left in your tables after purging but from a new angle with PO Purge Workflow script that I need to try out at work.  Next up is a post about the Workflow Product Information Center and some of the common information you can find out at this PIC which is a great reminder about some other sources of information that Oracle has and I need to tap into more often!