ORA-1555 after switchover to standby

This week started with diagnosing an interesting situation that didn’t seem to be talked about much (maybe because it is a relatively “old” problem–explanation in a minute), so I thought I’d share the experience for everyone’s benefit.

The situation involves a 10.2.0.1 (<gasp>) database on Linux 32-bit RHEL 4. The database is in flashback mode and has a single physical standby database with Data Guard configured in maximum availability mode. The standby was configured relatively recently and had never been tested with a switchover. The switchover was scheduled and conducted without much issue. Continue reading “ORA-1555 after switchover to standby”

Corrupt download caused installation to hang, unzip lessons learned

I’m posting this because apparently it hasn’t happened to anyone else except me (heavens knows I searched for help!). The root cause certainly wasn’t obvious to me, so hopefully, this will help someone (maybe me, once I forget about it).

All I needed to do was install a default Oracle Database 11g R1 environment with a starter database for some experimentation. Seems simple.

I had archived a copy of linux_11gR1_database.zip from OTN, so I copied that to my Oracle Enterprise Linux 5 U1 virtual machine and unzipped it (no errors or warnings, mind you). I proceeded to launch the installer as the oracle user and it ran me through the 4-6 screens of questions, prerequisite checks, etc, then the summary screen and started installing. The progress bar on the first task (copying files or similar) quickly progressed to 38% and then stopped…forever (I waited an hour). Continue reading “Corrupt download caused installation to hang, unzip lessons learned”

Upcoming presentations: OOW, UKOUG

Every year it seems like I get part way through the Oracle OpenWorld event and comment that next year, I need to be less busy so I can enjoy the event a little more and attend others’ presentations more than giving my own. And yet it seems that somewhere between then and the following year, I manage to volunteer myself for a full agenda of participation via the RAC SIG, IOUG, and my own personal/professional interests.

This year’s conference, coming up September 21-25, will be no different and I’ll be there all week to meet as many new friends as possible (that’s you), see some old friends (that might be you too), and share a few of my technical and community bits of knowledge with anyone that shows up at my sessions (hopefully you). I’ve already glanced through the schedule builder and picked out a lot of sessions that interest me, so pulling myself away from those to perform other presentations or fulfill other duties will be challenging, as usual. First, let me list where you’re certain to find me (you should add these sessions to your schedule using the Schedule Builder to ensure your seat in the sessions): Continue reading “Upcoming presentations: OOW, UKOUG”

IOUG RAC Attack!, Event Summary

As one of the organizers of the IOUG RAC Attack! event, I may have a slightly partial viewpoint, but I think the event overall went very well. The hands-on lab was especially popular and I think participants in the hands-on lab all got to learn at least a few new things. If you’ve been to Oracle Education training classes like I have, you know how it seems like you sometimes have to spend 5 days in training to get 1.5 days’ worth of material. With the hands-on lab at RAC Attack!, we provided guided exercises to demonstrate certain features and/or processes, but the whole day was more like “structured playtime” than particular labs that had to be completed. Continue reading “IOUG RAC Attack!, Event Summary”

IOUG RAC Attack!, Day 1 complete

Many of you have (hopefully) heard of the IOUG RAC Attack! event taking place yesterday and today in Chicago. We had a great first day yesterday with many great technical sessions and the first-day lab guinea pigs didn’t find many bugs or issues in completing the lab exercises and tests. Overall, the format is working out pretty well, though I think many people had a tough time choosing what technical sessions they were willing to miss in order to attend the hands on lab.

For any of the RAC Attack! attendees, please (really, please–like right now before you forget) drop me a line with your feedback and/or leave it here in comments. I am growing more and more confident that we’ll repeat this event at some point in the future. Plus, it’s highly likely that the hands on lab portion will be repeated at the Collaborate09 conference in Orlando in May, 2009 as well (sorry, no website up for it yet). Stay tuned for more details! So, if you didn’t get enough hands on lab time or have coworkers, peers or buddies that couldn’t make it to Chicago, there will be more opportunities for them to participate in the future. I hope Tuesday goes as well as Monday or better and I think it will!

My train is about to arrive in Chicago, so I’ll look forward to seeing today’s lab victims participants shortly!

Security can be basic

Sometimes I think that people think of security, especially database security, as a domain for the highly-skilled consultant. However, sometimes it is the most basic little things that need attention and it doesn’t require a high-priced, highly skilled consultant to figure it out.

Case in point: I recently arrived at a new customer site to help them with some database issues. They have a development environment, test environment, production database, and a clone of production they use for reporting. To get started, they sent me the TNS entries for each of these four databases. I didn’t have any usernames or passwords, so I was still in a holding pattern. Since I was using instantclient, I didn’t have tnsping, but I still wanted to verify that the TNS entries were created properly and that I had connectivity. So, I thought I’d just use scott/tiger to test and expected the ORA-01017 (invalid username/password) error.

I tried development, ORA-01017 which confirmed that the TNS entry was correct, but there was no scott/tiger account (or at least the password wasn’t tiger). Tried the test database, same result. The reporting database, same result. (You can see the punchline coming, right?) I tried the production database and, wouldn’t you know it, I got connected using the scott/tiger account! I was so shocked I think I let out a little yelp of disbelief.

So, for all the DBAs tuned in: here’s a quick and easy way to make things better (maybe still insufficient, but at least safER than now). Lock all the accounts that are not in use or that you can’t confirm are in use. If you need a hint: alter user scott account lock;. If you still don’t get it, then prepare your resume :). If you can’t confirm that the account is needed, lock it. When someone complains, unlock it (yes, it’s that easy). If they go to your boss to complain, explain that you did what you did in the name of database and data security (which is true) and you’ll generally avoid punishment.

If you aren’t sure whether the account is one of Oracle’s built-in, default accounts, consult Pete Finnigan’s lists. For more information, check out Project Lockdown, Oracle 11g Database Security Guide, and Pete Finnigan’s list of whitepapers and presentations. You can’t mention Oracle Security without a link to Mary Ann Davidson’s blog which is both informative and often entertaining.

Feel free to submit your horror story in comments. This same scenario happens all the time, but this time just seemed too silly to keep it a secret. No, I won’t tell you who the customer was :).

IOUG RAC Attack! Register Now!

The Oracle RAC SIG and the IOUG are co-sponsoring the IOUG “RAC Attack” event and if you haven’t yet heard about it, you might want to check it out. The event runs for 2 days, August 4-5, in downtown Chicago and will bring together some excellent presenters as well as the opportunity for hands-on experiences via the hands-on labs that run throughout the event. See the RAC Attack web page for more details on the event.

As one of the RAC SIG board members, I’ve been involved with the planning and development of this event over the last 9 months and I think it’s going to be one of the best opportunities to get focused, high-quality education on RAC available today. Plus, you’ll get the chance to network with a group of people that are focused on RAC and it’s uses.

Of course, I’ll be there, so if you do make it to the event, please be sure to say hello. I’ll likely spend much of my time in the hands-on lab (when I’m not presenting my technical session) helping those that need it to build their cluster, test backup and recovery or exercise some new features.

Hope to see you there!

TNS Listener Configuration for Oracle RAC

I’ve been to more than one RAC customer site and seen several different ways to misconfigure the listeners for a RAC cluster. This post describes how I usually configure the listeners and their associated instance parameters normally. This really has nothing to do with TAF or connection load balancing, those those features may not work as expected unless the underlying configuration is performed properly.

First, some background on how these items work is necessary. The TNS listener (tnslsnr process on *nix) process listens on a specific network address for connection requests to one of the services from one of the database instances that it services. When requested, it either spawns a server process (dedicated server environment) and connects the user to that process or forwards the connection request to a dispatcher (shared server environment) for service to the database service requested. Alternatively, if the listener knows of more than one instance providing the requested service, it may direct the client to an alternate listener (usually on a different node) that will service the request. Continue reading “TNS Listener Configuration for Oracle RAC”

PIPELINED PL/SQL function performance

I recently had an opportunity to do a deeper dive into a performance issue related to the performance of a PIPELINED function. The results weren’t quite what I had expected, so I thought they may be of general interest. As with many performance issues, the issues encountered here are likely data- and database-specific. This was testing performed on 10.2.0.3 64-bit non-RAC on Solaris 9.

The PIPELINED function was called like this:

select * from TABLE(pkg1.f1(arg1,arg2,arg3));

And pkg1 is defined like this:

CREATE OR REPLACE PACKAGE pkg1 AS
TYPE coll_table IS TABLE OF custom_persistent_obj_type;
FUNCTION f1 (
p1        IN   NUMBER,
p2             IN   NUMBER,
p3        IN   NUMBER
) RETURN coll_table PIPELINED;
END;
/

The function pkg1.f1 returns a collection of object types, is a little more than 1400 lines long, and contains more than 10 distinct, separate queries, each of them significantly complex involving at least 2 tables, many of them with multiple levels of subqueries. Anyhoo, my chore was to open the idea bin and see if there were some possible enhancements that hadn’t been considered yet.

While reviewing overall performance metrics for the instance, I noted that the DBMS_PICKLER package appeared in the top 10 resource-intensive SQL statements on a regular basis. More digging identified this package as being the primary workhorse behind the TABLE function. So, then the test was clear. Let’s see what happens if we remove PIPELINED, remove TABLE and call the function in a more conventional way. So, my version of the pkg.f would create a collection, add records to it throughout the function’s execution, then return the whole collection at the end with a RETURN statement. The calling function would then need to process this collection into some form to make it more usable by the calling application (similar to the functionality that TABLE provides). For this task, I wrote a stand alone function called DAN.

The new package, pkg2, is defined like this:

CREATE OR REPLACE PACKAGE pkg2 AS
  TYPE coll_table IS TABLE OF custom_persistent_obj_type;
  FUNCTION f2 (
    p1        IN   NUMBER,
    p2             IN   NUMBER,
    p3        IN   NUMBER
  )  RETURN coll_table;
END;
/

DAN is defined as:

CREATE OR REPLACE FUNCTION dan (p_b_tab  IN  pkg2.coll_table,
    p_delimiter     IN  VARCHAR2 DEFAULT ',')
  RETURN VARCHAR2 IS
    l_string     VARCHAR2(32767);
  BEGIN
    for i in p_b_tab.FIRST .. p_b_tab.LAST LOOP
      l_string := l_string || to_char(p_b_tab(i).id1)  ||p_delimiter
                           ||         p_b_tab(i).id2   ||p_delimiter
                           || to_char(p_b_tab(i).array_id)   ||p_delimiter
                           ||         p_b_tab(i).value
      ;
    END LOOP;
  RETURN l_string;
END dan;
/

So, my new units would be called like this:

DECLARE
  l_string      VARCHAR2(32767);
BEGIN
  l_string := dan(pkg2.f2(arg1,arg2,arg3));
  DBMS_OUTPUT.PUT_LINE(l_string);
END;
/

Of course, performance is our primary objective in this case, so timing these two examples side-by-side is the primary chore. I was satisfied that they performed equivalent work and provided equivalent results using different methods. The calling program (custom java, in this case) would have to be modified to handle the changes required, but that may be worth it depending on the time difference.

So, I devised a script to do some very rough testing. The script looked like this:

set timing on echo on termout on pages 0 lines 450 feedb on serveroutput on size 100000
select * from table(pkg1.f1(1095482041,60983,1));
DECLARE
  l_string      VARCHAR2(32767);
BEGIN
  l_string := dan(pkg2.f2(1095482041,60983,1));
  DBMS_OUTPUT.PUT_LINE(l_string);
END;
/

The results were somewhat surprising. Keep in mind that all I’m changing is the way in which data is returned and processed from the function. The data returned, though in slightly different formats, is exactly the same and is not filtered further after it is returned (so there’s no benefit in having it available to run SQL against it).

SELECT (sec) PL/SQL (sec) Difference (sec) Percent Improvement
0.15 0.09 0.06 40.00%
0.14 0.09 0.05 35.71%
0.14 0.10 0.04 28.57%
0.16 0.09 0.07 43.75%
0.15 0.09 0.06 40.00%
0.14 0.12 0.02 14.29%
0.18 0.12 0.06 33.33%
0.18 0.10 0.08 44.44%
0.25 0.12 0.13 52.00%
0.14 0.10 0.04 28.57%
0.18 0.10 0.08 44.44%
0.15 0.10 0.05 33.33%
0.17 0.10 0.07 41.18%
0.18 0.10 0.08 44.44%
0.14 0.10 0.04 28.57%
0.17 0.11 0.06 35.29%
0.17 0.10 0.07 41.18%
0.16 0.10 0.06 37.01% Average
0.14 0.09 0.02 14.29% Min
0.25 0.12 0.13 52.00% Max
8.84% Standard Deviation

As you can see, the results are pretty clear that there is a performance improvement in this case when we avoid using PIPELINED functions. I will also say that the result set from the function body is only about 30 rows. From my review of common uses for PIPELINED functions, it appears that larger collections (result sets) may benefit more from PIPELINED functions as they can likely take more advantage of the parallelism allowed with PIPELINED functions.

2:30am will never happen on Sunday morning

For those that didn’t bother to install the OS, Oracle or other DST patches (to handle the adjusted DST change schedule) last year and elected to just change the time on your servers by hand twice a year, get ready to do that again this weekend. Personally, I’d rather burn two whole regular work days figuring out and installing the patches than get up to modify the time on a server at 2am on Sunday morning, but that’s just me I guess.

For those that forgot the crontab rule about not scheduling things to occur between 1am and 3am (because they either get skipped or run twice once a year), prepare to be confused as 2:30am doesn’t ever happen on clocks in most US states/regions (here are the exceptions).

I’ll look forward to a little more daylight during the hours that count and one hour less sleep on Saturday night.

On a side note, let’s hope that this little posting will get me started on regular posting here…at least for a while!