Oracle 12cR1 RAC Installation on Windows 2012 Using VirtualBox…

After having a play with Oracle 12c on Windows 8, I decided to give Windows Server 2012 a go. Here is the resulting virtual RAC installation.

As you would expect, much of the process is pretty similar to the 11gR2 RAC installation on Windows 2008.

Windows Server 2012 is a strange beast. The interface is quite similar to Windows 8, which seems strange for a server OS. I’m gradually coming to terms with the Windows 8, so I am not so repulsed any more. That’s not to say I think it is the correct thing for Microsoft to do, but the thought of supporting my family on it is not filling me with quite so much dread now.

I really should get round to upgrading my desktop to Fedora 19, but time has been short. 🙂



UKOUG Speaker Evaluations…

This is a bit of a vanity post, so excuse me blowing my own trumpet.

I just took a look at the speaker evaluation feedback from UKOUG 2012 and it was pretty good. Only 4 of the people left “wordy” feedback:

  • Could have done with more time. Just the sort of presentation I came to conference for v.good.
  • Impressive.
  • Brilliant presenter and useful content, could have been twice as long.
  • Very nice presentation.

As far as the rating feedback went, the scores in each area were out of 6. I got just under 5 for the slides and the rest (content, presentation skills and value of presentation) were all above 5, which I think is pretty good. I was particularly happy with the presentation skills result.

I don’t take this stuff too seriously as these ratings are very subjective. Some people are always mega-critical and some people would give you 6/6 if you tripped and fell of the stage, but it is a nice little ego boost.



PS. It kinda makes up for the terrible job I did last year, so I guess my average rating for UKOUG presentations is now about “meh’… 🙂

UKOUG 2012

The UKOUG 2012 conference was a rather short affair for me this year as I was only there for 1 day of it.

During the Sunday I was at my nephew’s birthday party. In the evening I returned to Birmingham in time to make it to the Oracle ACE dinner. Big thanks to Debra Lilley for organizing it and to the Oracle ACE program for sponsoring the event. I used the opportunity to ask a number of questions about ADF and WebLogic. I’m still a newbie and since I seem unable to distance myself from the subject, I figured I need to know a bit more. 🙂

Monday was my first and only day at the conference. The lineup was like this:

My presentation was a quick romp through a number of the big performance mistakes people make in PL/SQL. The presentation felt like it went well. I guess the evaluation results will reveal all. I’ve already got some good feedback from it and I spent the best part of an hour answering questions after it, so those people must have thought my opinions were worth something. 🙂 The link above points to a summary article of the presentation, with internal links to the details on all the sections I spoke about, plus a few more. There seems little point repeating the contents of those articles, so having this article as effectively a links page to them seems the most obvious solution.

In addition to the presentations, I got to hook up with lots of friends from the Oracle circuit. You sometimes feel like a social butterfly at these things, but that’s kinda nice. I also got to introduce some of my work colleagues to a number of influential people, which kinda made me feel important for a few minutes. 🙂

Then it was back home to shave off my Mo and cut my hair, then out to the Oak Table dinner. I got to the restaurant about 11 minutes late, but early compared to the rest of the Oakies. 🙂 During dinner we discussed a number of things including load balancing WebLogic, movies, novels and annoying people who push into queues. It was all dead technical. 🙂

And that was that. Short and sweet.



Oracle OpenWorld 2012 : Day 5

Day 5 was a presentations day for me.

I tried to make as many notes as I could, but you will see the quality and accuracy of the notes tail off as the day went along… 🙂

Tom’s Top 12 Things About the Latest Generation of Database Technology

My bullets don’t quite match Tom’s, which is why I have more than 12 things listed. 🙂

  • Functions (and procedures used within those functions) can be defined in the WITH clause. Performance boost compared to regular unit defintion. Pragma to allow regular functions to benefit from these performance benefits.
  • Default value of column can use a sequence.nextval.
  • Identity columns : Multiple levels of control of how it is used. Can use simple or more complex syntax.
  • Metadata only default of optional columns. Previous versions this was possible only for mandatory columns.
  • VARCHAR2(32767) in the database. Less than 4K is stored inline. More than 4K is stored out of line, similar to LOB, but simpler. Not available by default.
  • Top-N now using Row limiting clause eg. “OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY”. Similar to mySQL syntax.
  • Row pattern matching. Quite a lot of new analytic syntax here.
  • Partitioning Improvements:
    – Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
    – Cascade for TRUNCATE and EXCHANGE partition.
    – Multiple partition operations in a single DDL
    – Online move of a partition(without DBMS_REDEFINTIION).
    – Interval  + Reference Partitioning.
  • Adaptive Execution Plans:
    – If the optimizer notices the cardinality is not what is expected, so the current plan is not optimal, it can alter subsequent plan operations to take allow for the differences between the estimated and actual cardinalities.
    – The stats gathered during this process are persisted as Adaptive Statistics, so future decisions can benefit from this.
    – You will see STATISTICS COLLECTOR steps in the SQL Trace. Can make the trace harder to read as it can contain information about the expected plan and the actual plan.
  • Enhanced Statistics:
    –  Some dynamic sampling operations are persistent, so they are not lost when the SQL is aged out.
    – Hybrid histograms. When the number of distinct values is greater than 254, “almost popular” values can get “lost” in the mix. A single bucket can now store the popularity of than value, effectively increasing the number of buckets, without actually increasing it.
    – Possible the max number of buckets can be increased based on a parameter. (demo grounds)
    – Statistics gathered during loads. CTAS and INSERT … SELECT automatically compute stats.
    – Global temporary tables can have “session private statistics”. Previously, we had one-size-fits-all.
  • Temporary Undo (ALTER SESSION SET temp_undo_enabled=true):
    – UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace.
    – Reduces contents of regular UNDO, allowing better flashback operations.
    – Reduces the size of redo associated with recovering the regular UNDO tablespace.
  • Data Optimization:
    – Information Lifecycle Management: Uses heat map. Colder data is compressed and moved to lower tier storage. Controlled by declarative DDL policy.
  • Transaction Guard:
    – If a failure happens, your application may not know the actual status of a transaction. If it was successful, issuing it again could cause a duplication transaction.
    – In these cases, you can mark a transaction with an “unknown” state (as far as the application is concerned) as failed, so even though they may have been successful, it will never be considered, or recovered. You’ve guaranteed the outcome.
  • Pluggable database:
    – Oracle provided metadata and data is kept in the container database (CDB).
    – User metadata and data is kept in the plugable database (PDB) .
    – One container can have multiple plugable databases.
    – No namespace clashes. Allows public synonyms and database links at the PDB level, rather than the CBD level.
    – Cloning is quick and simple as only user metadata and data needs to be cloned.
    – Upgrades have the potential to just unplug from old version (12cR1) to new version (2cR2).
    – Reduce total resource usage is reduced on lower use databases.

Oracle Database Optimizer: An Insider’s View of How the Optimizer Works

Oracle database 12c is the first step on the way to making an adaptive, or self-learning optimiser.

Alternative subplans are precomputed and stored in the cursor, so no new hard parsing will be needed as part of the adaption of an already executing plan. Statistics collectors are included in the plan execution. If the collectors cross a threshold, the plan might switch during execution from a nested loops to a hash join.

You can see information about the adaptive actions that have occurred using the DBMS_XPLAN package, with the format of “+all_dyn_plan +adaptive”. If a plan has been adapted, you will see it indicated in the  v$sql.is_resolved_dynamic_plan column.

If this functionality scares you, you can turn it off using the OPTIMIZER_APADPTIVE_REPORTING_ONLY parameter. Same work is done, but no actual adaptive action is taken.

During parallel execution, collectors can influence the distribution method (HASH > Distribution). Shown in the plan as the HYBRID HASH operations.

Dynamic statistics replace dynamic sampling. The resulting stats are cached as SHARED DYNAMIC STATS specific for the statement, including the bind values. This information is used for any session using the same statement.

Cardinality feedback can be used to re-optimize subsequent operations. Join statistics are monitored. Works with adaptive cursor sharing. Persisted on disk. New column v$sql.is_reoptimizable shows that a subsequent run will take this into consideration. Collectors are kept, even if the SQL statement is killed part way through. The plan shows that cardinality feedback is used.

SQL Plan Directives are based on a SQL phrase (a specific join) rather than the whole statement. Cached in the directive cache, but persisted in the SYSAUX tablespace. Managed using the DBMS_SPD package.

Information gathered by the optimizer, may prompt automatic creation of column groups, so next time stats are gathered, the extended stats will be gathered.

What’s New in Security in the Latest Generation of Database Technology

  • Privilege Analysis:
    – Track direct privileges and privileges via roles being used, so you can determine the least privileges needed.
    – Monitoring controlled using DBMS_PRIVILEGE_CAPTURE.
    – Report what is used and what is not used.
  • Data Redaction: A variation in column masking of VPD, but it doesn’t just blank the value and still allows queries against the column in the WHERE clause.
  • Enhanced Security of Audit Trail:
    – Single unified audit trail.
    – Extension of the audit management package.
    – Multiple audit management privileges.
  • Encryption Enhancements:
    – Allow SQL creation and management of wallets, rather than command line utilities. Allows easier remote management.
    – Export and import wallets/keys between plugable databases.
    – Storage of wallets in ASM.
    – Much more…
  • Code-Based Access Control (CBAC):
    – A PL/SQL unit can have roles granted to it.
    – When the unit runs, any dynamic SQL running can have the privileges granted via the role.
    – Doesn’t affect compile time, so focussing very much on dynamic SQL.
    – Useful on invoker rights, since now the PL/SQL can run with user privileges and explicitly granted roles for the unit.
  • Invoker Rights:
    – INHERITED RIGHTS : Control accidental privilege escalation when a privileged user calls an invoker rights unit containing malicious code.
    – Invokers rights for views.
  • Separation of Duties:
    – SYSDBA – God
    – SYSOPER – More limited than SYSDBA, but still very powerful.
    – SYSBACKUP – Just enough to do a backup.
    – SYSDG – Just enough for data guard administration.
    – SYSKM – Just enough to perform basic key management tasks.
    – Roles for audit management.

The Hives

The wrap up party was probably the highlight of the week, thanks to The Hives. They were freakin’ awesome. The front man is a scream. Very funny when he interacts with the audience. Makes me want to be in a band again!

I’ll follow this series up with a wrap-up post.



Oracle OpenWorld 2012 : Day 4

My RAC Attack session was pretty early, so I headed straight there for the morning.

The OCP lounge always has drinks and food, so regular pitstops there are the norm for me at OOW. If you have OCP or above, it’s really worth dropping by on the first day and getting your ribbons, so you can do regular refreshment stops during the week. 🙂

After RAC Attack it is was back to the demo grounds to do another quick tour. Basically, I was looking for new faces to speak to and quizzing people further about stuff I had discussed on previous days. Sometimes you have to let this stuff stew a while before you can think of more questions. 🙂

I bumped into Tom Kyte before his SQL Tuning Experts panel session. He said something like, “Ah, Mr Hall”, to which I replied, “Mr? I didn’t do 5 years at evil medical school to be called Mr!” That comment came back to bite me during the panel session when I put my hand up to ask a question and he said something like, “A question from DR Hall…” The shame…

SQL Tuning Experts Panel

Some of the take-home points from this include:

  • The optimizer’s short term memory has become longer, as some of the dynamic statistics and cardinality feedback information can now be persisted to the SYSAUX tablespace.
  • SQL Monitor is the the best tool in 11g to troubleshoot performance problems.
  • SQL Performance Analyzer is a great way to check the impact of changes on SQL performance.
  • Stats have to be representative, not necessarily new.
  • Pending stats save you from screwing up all your plans every night. 🙂
  • In 12c, Enhanced Real-Time ADDM runs every 3 seconds to give you up to date advice on things that are happening on your system.

Five Things about SQL and PL/SQL you might not have known.

This included a mix of new stuff in 12c and existing stuff. My laptop battery was dead at this point, so I was making notes on my Nexus 7, so I only recorded the 12c stuff.

  • Implicit return of result sets:
    – The DBMS_SQL package new contains overloaded RETURN_RESULT procedures to push cursor variables back to the calling client code.
    – This is similar to how SQL Server can return result sets using SELECT without defining out parameters.
    – SQL*Plus automatically shows the implicit output.
    – PL/SQL can manipulate them using the GET_NEXT_RESULT function to return the next cursor variable.
    – This is a migration feature, so there is no need to replace existing procedures and functions that return cursor variables to return record sets.
  • Global Temporary Table Statistics can be session specific. It is controlled using a statistics preference, allowing them to be set to global or session-specific.
  • The cardinality of pipelined table functions was discussed. These is a subtle change in 12c as the cardinality feedback introduced in 11g is persisted in 12c.

Evening Social Events

In the evening it was off to the bloggers meetup. Every year I get to put more real faces to the names and twitter handles I’ve got used to seeing. It’s really good to meet everyone properly and have a little chat.

After that it was off to the appreciation event, or the everlasting buss ride as it turned out to be. I ended up seeing the last couple of songs by Kings of Leon, who sounded OK. I saw most of Perl Jam, but was a little dissapointed. The sound quality was a little poor and they weren’t as tight as I would have expected. That’s not to say they were bad, just not as good as I wanted them to be. I think maybe I should play Ten and pretend I’m twenty again and convince myself it was the best gig ever…

All in all a pretty crazy day…



Oracle OpenWorld 2012 : Day 3

Day 3 was similar to day 2 for me. I spent a lot of time hanging around the RAC Attack sessions. A lot of people pass through, so in addition to helping people set up virtualized RAC, there are always interesting people to talk to.

I did some serious demo grounds cruising again, including:

  • Data Pump Enhancements in 12c.
    – You can now do all the transportable tablespace stuff in a single hit using NETWORK_LINK.
  • PL/SQL Enhancements in 12c.
    – I’ll talk more about these in a summary of a Tom Kyte presentation in a later post.
  • Global Data Services in 12c.
    – Define a single service that can load balance across multiple replicated databases. This is independent of replication method.
  • Oracle Linux. I went back and spent some time chatting with Lenz Grimmer again. He’s really good value!
  • Oracle Text in 12c (Rodger Ford).

I finally got to meet Jeff Smith and David Haimes in person. It’s always a bit odd meeting people for the first time when you feel you already know them…

I went back to the hotel to dump my bag before heading out and accidentally went to bed, so that’s a second night of no social events for me… 🙂



Oracle OpenWorld 2012 : Day 2

The cold continues…

I decided than rather than trying to sit in sessions and struggle to cope I would do my stint at RAC Attack and spend the day on the demo grounds…

The demo grounds are a really awesome way to get information because you are often speaking to the developers and product specialists working on code itself. In my opinion, you get a much better fix on what is going on that sitting in a presentation.

I got to speak to a bunch of people for a few hours about:

  • Oracle Linux (Lenz Grimmer).
  • Unstructured Data and XML DB (Mark Drake).
    – XML DB is mandatory in 12c.
    – The new Enterprise Manager Express runs from within the database using the XML DB infrastructure,. This replaces the deprecated Database Control.
  • Optimizer enhancements in 12c (Alison Lee and Maria Colgan).
    – Adaptive Plans : The optimizer can change the plan during execution if it determines the cardinality estimates it made are not accurate.
    – Adaptive Statistics : The optimizer can store information about adaptive plans to allow it to make better estimates in future.
    – Existing information, like cardinality feedback can now be persisted, rather than being aged out and lost.
  • DBFS Enhancements in 12c.
    – Addition of WebDav support, so other platforms can use it as a real filesystem, without the Linux FUSE project.
  • Edition-Based Redefinition Enhancements in 12c.
    – Marking objects as non-editionalble.
  • Pluggable Databases in 12c.
    – Separating the internal Oracle metadata (container database – CDB) from the user metadata (plugable database – PDB)
    – Easier upgrades, clones and migrations.
    – Reduced namespace clashes. Public synonyms and DB links for each plugable database within the same instance.
  • Compression Enhancements in 12c (Graham Wood).
  • Far Sync Server (Graham Wood).
  • SQL Developer.
  • Data Redaction in 12c.
  • Data masking and data subsetting in Cloud Control 12cR2.

Sorry if I missed anyone or anything out of the list.

I think it may have been the Blogger meetup last night, but I was too tired so I went back to the hotel and dropped into a coma for a few hours. 🙂



Oracle OpenWorld 2012 : Day 1

Day 1 turned out to be a little awkward for me because I was feeling pretty under the weather. I’ve been nursing a very minor cold for a couple of weeks and I think the lack of sleep has made it kick up a gear.

I went to a DB tuning session and an ADF deployment session, then I felt out for the count. At lunch time I went and watched a movie (review to come), then I went back to my room and vegetated for the rest of the day. It felt like a bit of a waste of a day, but I really wasn’t able to cope with anything more.

In the evening it was the ACE Dinner. I ate loads of food and talked until my throat was dead. It was fun, but I was glad to get back to my bed.



Oracle OpenWorld 2012 : Day Minus 1

After another night with little sleep, Saturday was a chance to recover. I had no formal plans for the morning. I registered for OOW, then went out for breakfast with Marcelle, who then had to shoot to MySQL Connect.

From there I went back to my room for a little rest, then it was out to take Connor McDonald‘s advice and get a $15 T-Mobile SIM to get 7 day data access from my hotel room. Much cheaper than the hotel internet!

During the day I managed to hassle Marcus Eisele, Simon Haslam and Jakko Landlust about WebLogic stuff. It’s the classic case of “The more you know, the more you realise you don’t know”. Perhaps I should stop asking questions, so I can convince myself I’m a WebLogic God!

In the evening it was out to the OakTable dinner, which as now been renamed to “The Anual Doug Burns OakTable Party”. It was a pretty good effort considering it was his first one. I was a little bit disappointed at the lack of balloons, party streamers, jelly & icecream etc. Next years I’m sure he’ll be able to rectify these oversights!

After the walk home it was off to bed to get 3 hours sleep before “Oracle OpenWorld 2012 : Day 1” starts.

So far I’ve managed to get to the gym every day, but I’ve also managed to stuff myself with food. As a result I’ve lowered my fitness expectations for the week somewhat. My goal is to make it to the end of the week without getting stretch-marks on my face…



Oracle OpenWorld 2012 : Day Minus 2

Day 2 of the ACE Director briefing went pretty much to plan. My recent adventures in WebLogic meant I had to pay special attention to a few areas I would normally let wash over me. 🙂 I also learned a few things about the WebLogic roadmap that made me kick myself for some of the recent decisions I had made. I guess that’s what happens when you get forced into a position of doing a role you have no background in. I confess I’m a WebLogic noob! 🙂

Thanks for Victoria and Lillian for organising the ACED briefing again. It’s good to get face time with the movers and shakers in the Oracle tech world…

At the end of the day we were taken from the Sofitel in Redwood Shores across to the Hilton in San Francisco. That’s my bas for the next few days. Sleep still alludes me…