Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Choosing a password scheme for the database

In the Security Guide there is a section to assist you with the decisions about what rules you might want to have in place when users choose passwords, namely attributes like the minimum length of a password, the types of characters it must (and must not) contain, re-use of old passwords etc etc. The documentation refers to a number of pre-supplied routines that are now available in 12c to assist administrators.  This is just a quick blog post to let you know that there is no “smoke and mirrors” going on here in terms of these functions. We’re implementing them in the same way that you might choose to build them yourself. In fact, you can readily take a look at exactly what the routines do because they are just simple PL/SQL code:


SQL> select text
  2   from dba_source
  3  where name in (
  4    'ORA12C_STRONG_VERIFY_FUNCTION'
  5    'ORA12C_VERIFY_FUNCTION',
  6    'ORA_COMPLEXITY_CHECK',
  7    'ORA_STRING_DISTANCE')
  8  order by name, line;

TEXT
----------------------------------------------------------------------------------------------------------------------------------
function ora12c_strong_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
return boolean IS
   differ integer;
begin
   if not ora_complexity_check(password, chars => 9, upper => 2, lower => 2,
                           digit => 2, special => 2) then
      return(false);
   end if;

   -- Check if the password differs from the previous password by at least
   -- 4 characters
   if old_password is not null then
      differ := ora_string_distance(old_password, password);
      if differ < 4 then
         raise_application_error(-20032, 'Password should differ from previous '
                                 || 'password by at least 4 characters');
      end if;
   end if;

   return(true);
end;

FUNCTION ora12c_verify_function
(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
   differ integer;
   db_name varchar2(40);
   i integer;
   reverse_user dbms_id;
   canon_username dbms_id := username;
BEGIN
   -- Bug 22369990: Dbms_Utility may not be available at this point, so switch
   -- to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN
     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
   END IF;
   IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
                               special => 1) THEN
      RETURN(FALSE);
   END IF;

   -- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20002, 'Password contains the username');
   END IF;

   -- Check if the password contains the username reversed
   FOR i in REVERSE 1..length(canon_username) LOOP
     reverse_user := reverse_user || substr(canon_username, i, 1);
   END LOOP;
   IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20003, 'Password contains the username ' ||
                                     'reversed');
   END IF;

   -- Check if the password contains the server name
   select name into db_name from sys.v$database;
   IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
      raise_application_error(-20004, 'Password contains the server name');
   END IF;

   -- Check if the password contains 'oracle'
   IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
        raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password differs from the previous password by at least
   -- 3 characters
   IF old_password IS NOT NULL THEN
     differ := ora_string_distance(old_password, password);
     IF differ < 3 THEN
        raise_application_error(-20010, 'Password should differ from the '
                                || 'old password by at least 3 characters');
     END IF;
   END IF ;

   RETURN(TRUE);
END;

function ora_complexity_check
(password varchar2,
 chars integer := null,
 letter integer := null,
 upper integer := null,
 lower integer := null,
 digit integer := null,
 special integer := null)
return boolean is
   digit_array varchar2(10) := '0123456789';
   alpha_array varchar2(26) := 'abcdefghijklmnopqrstuvwxyz';
   cnt_letter integer := 0;
   cnt_upper integer := 0;
   cnt_lower integer := 0;
   cnt_digit integer := 0;
   cnt_special integer := 0;
   delimiter boolean := false;
   len integer := nvl (length(password), 0);
   i integer ;
   ch char(1);
begin
   -- Check that the password length does not exceed 2 * (max DB pwd len)
   -- The maximum length of any DB User password is 128 bytes.
   -- This limit improves the performance of the Edit Distance calculation
   -- between old and new passwords.
   if len > 256 then
      raise_application_error(-20020, 'Password length more than 256 characters');
   end if;

   -- Classify each character in the password.
   for i in 1..len loop
      ch := substr(password, i, 1);
      if ch = '"' then
         delimiter := true;
      elsif instr(digit_array, ch) > 0 then
         cnt_digit := cnt_digit + 1;
      elsif instr(alpha_array, nls_lower(ch)) > 0 then
         cnt_letter := cnt_letter + 1;
         if ch = nls_lower(ch) then
            cnt_lower := cnt_lower + 1;
         else
            cnt_upper := cnt_upper + 1;
         end if;
      else
         cnt_special := cnt_special + 1;
      end if;
   end loop;

   if delimiter = true then
      raise_application_error(-20012, 'password must NOT contain a '
                               || 'double-quotation mark which is '
                               || 'reserved as a password delimiter');
   end if;
   if chars is not null and len < chars then
      raise_application_error(-20001, 'Password length less than ' ||
                              chars);
   end if;

   if letter is not null and cnt_letter < letter then
      raise_application_error(-20022, 'Password must contain at least ' ||
                                      letter || ' letter(s)');
   end if;
   if upper is not null and cnt_upper < upper then
      raise_application_error(-20023, 'Password must contain at least ' ||
                                      upper || ' uppercase character(s)');
   end if;
   if lower is not null and cnt_lower < lower then
      raise_application_error(-20024, 'Password must contain at least ' ||
                                      lower || ' lowercase character(s)');
   end if;
   if digit is not null and cnt_digit < digit then
      raise_application_error(-20025, 'Password must contain at least ' ||
                                      digit || ' digit(s)');
   end if;
   if special is not null and cnt_special < special then
      raise_application_error(-20026, 'Password must contain at least ' ||
                                      special || ' special character(s)');
   end if;

   return(true);
end;

function ora_string_distance
(s varchar2,
 t varchar2)
return integer is
   s_len    integer := nvl (length(s), 0);
   t_len    integer := nvl (length(t), 0);
   type arr_type is table of number index by binary_integer;
   d_col    arr_type ;
   dist     integer := 0;
begin
   if s_len = 0 then
      dist := t_len;
   elsif t_len = 0 then
      dist := s_len;
   -- Bug 18237713 : If source or target length exceeds max DB password length
   -- that is 128 bytes, then raise exception.
   elsif t_len > 128 or s_len > 128 then
     raise_application_error(-20027,'Password length more than 128 bytes');
   elsif s = t then
     return(0);
   else
      for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
          d_col(j) := 0 ;
      end loop;
      for i in 0 .. s_len loop
          d_col(i) := i;
      end loop;
      for j IN 1 .. t_len loop
          d_col(j * (s_len + 1)) := j;
      end loop;

      for i in 1.. s_len loop
        for j IN 1 .. t_len loop
          if substr(s, i, 1) = substr(t, j, 1)
          then
             d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
          else
             d_col(j * (s_len + 1) + i) := LEAST (
                       d_col( j * (s_len+1) + (i-1)) + 1,      -- Deletion
                       d_col((j-1) * (s_len+1) + i) + 1,       -- Insertion
                       d_col((j-1) * (s_len+1) + i-1) + 1 ) ;  -- Substitution
          end if ;
        end loop;
      end loop;
      dist :=  d_col(t_len * (s_len+1) + s_len);
   end if;

   return (dist);
end;

The good thing about this is that:

  • the routines are transparent, which is how all good security models should be presented. Because then they are open to scrutiny and not reliant on any kind of obfuscation to be secure, and 
  • if the routines do not exactly meet your requirements, then you now have a well established starting point from which to build your own custom routines.

Of course, if you are just after a string distance function, you might be better off using the pre-supplied UTL_MATCH package.

New Video of Oracle Security Vulnerability Scanning

I have just made a new video of a sample session using PFCLScan our vulnerability / security scanner for the Oracle database. In the video I show how easy it is to get started with PFCLScan and scan an Oracle....[Read More]

Posted by Pete On 17/08/17 At 01:50 PM

SQL Server 2012 and Changes to the Backup Operator Permissions

style="display:inline-block;width:320px;height:100px"
data-ad-client="ca-pub-5103295461547706"
data-ad-slot="5182487270">

I’m off to Columbus, Ohio tomorrow for a full day of sessions on Friday for the Ohio Oracle User Group.  The wonderful Mary E. Brown and her group has set up a great venue and a fantastic schedule.  Next week, I’m off to SQL Saturday Vancouver to present on DevOps for the DBA to a lovely group of SQL Server attendees.  It’s my first time to Vancouver, British Columbia and as it’s one of the cities on our list of potential future locations to live, I’m very excited to visit.

Speaking of SQL Server-  Delphix‘s own SQL Server COE, (Center of Excellence) meets twice a month to discuss various topics surrounding our much-loved Microsoft offering.  This week, one of the topics discussed a previous change made to permissions to the Backup Operator role from SQL Server 2008R2 to SQL Server 2012. This feature, referred to as “File Share Scoping” was unique to 2008R2 clusters and no longer exists.

Now many may say, “but this is such an old version.  We’ve got SQL Server 2017, right?”  The challenge is, there are folks out there with 2008 instances and it’s good to know about these little changes that can make big impacts to your dependent products.  This change impacted products with shared backups file systems and as we know, having access to a backup can offload a lot of potential load on a system.

Now, for my product, Delphix, we are dependent on read access to backup files for the initial creation of our “golden copy” that we source everything from.  The change in SQL Server 2012 from the previous File Share Scoping in 2008R2 was only made to Microsoft Failover Clusters, to then offering access to only those with Administrator, where previously, anyone with Backup Operator role could attain access, too.

Our documentation clearly states during configuration of a Delphix engine for the validated sync, (creation of the golden copy) the customer must grant read access for the backup shares to the Delphix OS user and doesn’t state to grant Backup Operator.  As with everything, routine can spell failure, as the Backup Operator role previously offered this access with 2008R2 and it was easy to assume the configuration complete upon database level role grants.

Using Powershell from the command line, note that you can’t view the root of the shared drive with the file server role, Backup Operator in the newer release.

PS C:\Users\user> Get-SmbShareAccess -name "E$" | ft -AutoSize

Name ScopeName AccountName AccessControlType AccessRight
---- --------- ----------- ----------------- -----------
E$ USER1-SHARE BUILTIN\Administrators Allow Full
E$ * BUILTIN\Administrators Allow Full
E$ * BUILTIN\Backup Operators Allow Full
E$ * NT AUTHORITY\INTERACTIVE Allow Full

If you’d like to read more details on backup and recovery changes from SQL Server 2008R2 to 2012, check out the documentation from Microsoft here.

 



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [SQL Server 2012 and Changes to the Backup Operator Permissions], All Right Reserved. 2017.

The post SQL Server 2012 and Changes to the Backup Operator Permissions appeared first on DBA Kevlar.

AskTOM–more experts to help you!

I’m thrilled to announce the “formal” addition of globalization and characterset guru Sergiusz Wolicki to the AskTOM team. I say “formal” addition because the team was already getting guidance from Sergiusz whenever we had tough question on charactersets, but just like his enthusiasm to help customers on the forums, Sergiusz was keen to help our AskTOM visitors as well.

Sergiusz is a 20+ year veteran of Oracle Corporation, with over half that time specializing in globalization, internationalization of Oracle products. It only takes a quick glance at the community space for Globalization to gauge the contribution he makes there !

image

 

And it didn’t take long before his knowledge came to good use on AskTOM !

 

image

Welcome Sergiusz !

Oracle Code … Not for database people ?

imageJump over to the Oracle Code home page and you will see the “mission statement” of the Oracle Code conference series:

“Learn from technical experts in sessions for developing software in Java, Node.js, and other languages and frameworks.”

You might hence be thinking that “old school” stuff like (relational) database technology has no place at such a conference, and certainly the agenda looks slanted away from database technologies.  But I think you’re wrong Smile and here is why I think that.  I did a talk in Bangalore last week at the Oracle Code event there (which by the way was a wonderful event, so thanks to all that came along) on some SQL language techniques.  After the talk, one of attendees came up to me, thanked me for the talk and said this (I’m paraphrasing):

“It was really interesting to see all the stuff that you could do in SQL.  I’m a Java person, and whenever I have complicated data requirements, I have always simply retrieved the data from the database and then done the complex part of the operations in Java.  But your session has convinced me to explore doing some of that in SQL”

It is so easy to have a bias for the technology(s) that you are most capable with.  I am just as guilty of that as anyone. I’ll generally look for a SQL or PL/SQL means to solve a business problem before considering other options that may actually be more appropriate.  But here we had an attendee who was happy to consider looking outside his sphere of expertise to focus on optimal solutions to problems rather than just solutions that sat inside his “comfort zone”.  That really struck a chord with me, and made me feel like the entire trip was worthwhile. Because when we have a bias toward a particular technology, it is easy to lulled into an argument that other technologies are inappropriate for any usage.  And then suddenly we’re into a shouting match about why technology “X” is the best and that anything that is not technology “X” is junk.  We all lose when that’s the case.

So there’s an argument to made that Oracle Code is indeed not for database developers, but in the same way, it is not for middle tier developers, and not for front end developers.  Oracle Code is about creating the balanced developer – a developer that has expertise in one (or more) areas but more importantly, can understand the whole stack and have an impartial, unclouded (no pun intended) view of the benefits of all layers in the application stack.  Because that balance ultimately leads to a better development community, and better opportunities to maximize the benefits of each of the components in the array of technologies that now permeate our development careers.

So whatever your area of expertise, Oracle Code has something for you, and and perhaps the best thing you can do at an Oracle Code event, is attend something outside your current area of expertise.

Words I Don’t Use, Part 5: “Wait”

The fifth “word I do not use” is the Oracle technical term wait.

The Oracle Wait Interface

In 1991, Oracle Corporation released some of the most important software instrumentation of all time: the wait statistics that were implemented in Oracle 7.0. Here’s part of the story, in Juan Loaiza’s words, as told in Nørgaard et. al (2004), Oracle Insights: Tales of the Oak Table.

This stuff was developed because we were running a benchmark that we could not get to perform. We had spent several weeks trying to figure out what was happening with no success. The symptoms were clear—the system was mostly idle—we just couldn’t figure out why.

We looked at the statistics and ratios and kept coming up with theories, the trouble was that none of them were right. So we wasted weeks tuning and fixing things that were not the problem. Finally we ran out of ideas and were forced to go back and instrument the code to figure out what the problem was.

Once the waits were instrumented the problem was diagnosed in minutes. We were having “free buffer” waits because the DBWR was not writing blocks fast enough. It’s amazing how hard that was to figure out with statistics, and how easy it was to figure out once the waits were instrumented.

...In retrospect a lot of the names could be greatly improved. The wait interface was added after the freeze date as a “stealth” project so it did not get as well thought through as it should have. Like I said, we were just trying to solve a problem in the course of a benchmark. The trouble is that so many people use this stuff now that if you change the names it will break all sorts of thing tools, so we have to leave them alone.

Before Juan’s team added this code, the Oracle kernel would show you only how much time its user calls (like parse, exec, and fetch) were taking. The new instrumentation, which included a set of new fixed views like v$session_wait and new WAIT lines in our trace files, showed how much time Oracle’s system calls (like reads, writes, and semops) were taking.

The Working-Waiting Model

The wait interface begat a whole new mental model about Oracle performance, based on the principle of working versus waiting:

Response Time = Service Time + Wait Time

In this formula, Oracle defines service time as the duration of the CPU used by your Oracle session (the duration Oracle spent working), and wait time as the sum of the durations of your Oracle wait events (the duration that Oracle spent waiting). Of course, response time in this formula means the duration spent inside the Oracle Database kernel.

Why I Don’t Say Wait, Part 1

There are two reasons I don’t use the word wait. The first is simply that it’s ambiguous.

The Oracle formula is okay for talking about database time, but the scope of my attention is almost never just Oracle’s response time—I’m interested in the business’s response time. And when you think about the whole stack (which, of course you do; see holistic), there are events we could call wait events all the way up and down:

  • The customer waits for an answer from a user.
  • The user waits for a screen from the browser.
  • The browser waits for an HTML page from the application server.
  • The application server waits for a database call from the Oracle kernel.
  • The Oracle kernel waits for a system call from the operating system.
  • The operating system’s I/O request waits to clear the device’s queue before receiving service.
  • ...

If I say waits, the users in the room will think I’m talking about application response time, the Oracle people will think I’m talking about Oracle system calls, and the hardware people will think I’m talking about device queueing delays. Even when I’m not.

Why I Don’t Say Wait, Part 2

There is a deeper problem with wait than just ambiguity, though. The word wait invites a mental model that actually obscures your thinking about performance.

Here’s the problem: waiting sounds like something you’d want to avoid, and working sounds like something you’d want more of. Your program is waiting?! Unacceptable. You want it to be working. The connotations of the words working and waiting are unavoidable. It sounds like, if a program is waiting a lot, then you need to fix it; but if it’s working a lot, then it is probably okay. Right?

Actually, no.

The connotations “work is virtuous” and “waits are abhorrent” are false connotations in Oracle. One is not inherently better or worse than the other. Working and waiting are not accurate value judgments about Oracle software. On the contrary, they’re not even meaningful; they’re just arbitrary labels. We could just as well have been taught to say that an Oracle program is “working on disk I/O” and “waiting to finish its CPU instructions.”

The terms working and waiting really just refer to different subroutine call types:

“Oracle is working means “your Oracle kernel process is executing a user call”
“Oracle is waiting means “your Oracle kernel process is executing a system call”

The working-waiting model implies a distinction that does not exist, because these two call types have equal footing. One is no worse than the other, except by virtue of how much time it consumes. It doesn’t matter whether a program is working or waiting; it only matters how long it takes.

Working-Waiting Is a Flawed Analogy

The working-waiting paradigm is a flawed analogy. I’ll illustrate. Imagine two programs that consume 100 seconds apiece when you run them:

Program A Program B
Duration Call type Duration Call type
98 system calls (waiting) 98 user calls (working)
2 user calls (working) 2 system calls (waiting)
100 Total 100 Total

To improve program A, you should seek to eliminate unnecessary system calls, because that’s where most of A’s time has gone. To improve B, you should seek to eliminate unnecessary user calls, because that’s where most of B’s time has gone. That’s it. Your diagnostic priority shouldn’t be based on your calls’ names; it should be based solely on your calls’ contributions to total duration. Specifically, conclusions like, “Program B is okay because it doesn’t spend much time waiting,” are false.

A Better Model

I find that discarding the working-waiting model helps people optimize better. Here’s how you can do it. First, understand the substitute phrasing: working means executing a user call; and waiting means executing a system call. Second, understand that the excellent ideas people use to optimize other software are excellent ideas for optimizing Oracle, too:

  1. Any program’s duration is a function of all of its subroutine call durations (both user calls and system calls), and
  2. A program is running as fast as possible only when (1) its unnecessary calls have been eliminated, and (2) its necessary calls are running at hardware speed.

Oracle’s wait interface is vital because it helps us measure an Oracle program’s complete execution duration—not just Oracle’s user calls, but its system calls as well. But I avoid saying wait to help people steer clear of the incorrect bias introduced by the working-waiting analogy.

ODA X6 Small Medium Large and High Availability

There are 4 models of Oracle Database Appliance with the new ODA X6 which is for the moment the latest ODA hardware version. One is similar to the previous X5-2 one, and 3 smaller ones known as ODA Lite. They are 1 year old already, here is a small recap of the differences and links to more detail.

System

The ODA X6 are composed with Oracle Server X6-2:

  • ODA X6-2S has one server
  • ODA X6-2M has one server
  • ODA X6-2L has one server
  • ODA X6-2HA is a cluster of two servers with one shared storage shelf (DE3-24C). It can be expanded with one or two additional storage shelf.

Those servers have 2 USB 2.0 ports accessible (2 in front, 2 in back) and 2 internal USB ports.
They have one serial console (SER MGT/RJ-45 connector) port. And One VGA DB-15 connector, easier to plug before you put the cable rails. Resolution: 1,600 x1,200×16 MB @ 60 Hz. Note that the resolution is 1,024 x 768 when viewed remotely via Oracle ILOM).
Each server has 2 redundant power supplies (hot swappable).

Rack Unit

  • ODA X6-2S is 2U
  • ODA X6-2M is 2U
  • ODA X6-2L is 4U
  • ODA X6-2HA is 6U (up to 10U with storage expansions)

CPU

The processor of X6 servers is an Intel 2.2GHz 10-Core Xeon E5-2630 v4, 85W
Cache:
Level 1: 32 KB instruction and 32 KB data L1 cache per core
Level 2: 256 KB shared data and instruction L2 cache per core
Level 3: 25 MB shared inclusive L3 cache per processor

  • ODA X6-2S has 1 processor: 10 cores
  • ODA X6-2M has 2 processors: 20 cores
  • ODA X6-2L has 2 processors: 20 cores
  • ODA X6-2 HA has two servers with 2 processors each: 40 cores

The core factor of the processor is 0.5 and you can license the full capacity for Enterprise Edition: 5 licenses for one ODA X6-2S, 10 licenses for one ODA X6-2M or 2L, 20 licenses for ODA X6-2 HA. You can also run NUP licenses with a minimum of 125 NUP for one ODA X6-2S, 250 NUP for one ODA X6-2M or 2L, 500 NUP for ODA X6-2 HA.
Of course, you can do Capacity on Demand on all models, so the minimum processor license is 1 license for ODA Lite (when 2 cores only are enabled) or 2 licenses for ODA HA because the nodes must by symmetric (except if you run only one node). In NUP, it means 25 NUP minimum for ODA Lite or 50 NUP for ODA HA.

On ODA Lite (2S, 2M, 2L) you can choose to run Standard Edition. Then you count the number of processors: 1 license for ODA X6-2S, 2 licenses for one ODA X6-2M or 2L. When in Standard Edition 2 you can license in NUP with the minimum of 10 NUP per server.

RAM

The X6 servers have 14 DIMM slots and are partially populated with 32GB DIMMs (DDR4-2400).

  • ODA X6-2S has 4 slots populated: 128GB RAM. It can be expanded with additional 8 DIMMs for 384GB
  • ODA X6-2M has 8 slots populated: 256GB RAM. It can be expanded with additional 16 DIMMs for 512GB or 768GB which populates all the slots.
  • ODA X6-2L has 8 slots populated: 256GB RAM. It can be expanded with additional 16 DIMMs for 512GB or 768GB which populates all the slots.
  • ODA X6-2 HA has 8 slots populated per server: 256GB RAM. It can be expanded with additional 16 DIMMs for 512GB or 768GB which populates all the slots.

Network

ODA Lite:
2x 10GbE SFP+ (fiber) and 2x 10GBase-T (copper) ports for ODAX6-2S (4x 10GBase-T for 2M and 2L)
ODA HA:
InfiniBand interconnect, 4x 10GBase-T (copper) ports (bonded to provide two public interfaces) or optional 2x 10GbE SFP+ (fiber) per server and one pair of 10GBase-T will be used for interconnect.

Storage

The servers have two 2.5″ 480GB SAS-3 SDDs disks in front, mirrored for the Operating System and the Oracle Database Software

ODA X6-2S and 2M have additional two 2.5″ 3.2TB NVMe PCIe 3.0 SSD disks for DATA and RECO disk groups (6.4TB raw capacity, 2.4TB double-mirrored, 1.6TB triple-mirrored)
They are expandable to 12.8 TB with two additional disks.

ODA X6-2L has six 3.5-inch disk bays are populated with 3.2TB SSDs (19.2TB raw capacity, 9.6TB double-mirrored, 6.4TB triple-mirrored)
They are expandable to 28.8 TB with two additional disks.

ODA X6-HA has a storage shelf, as previous X5 model (similar but not compatible), but with SSD and lower capacity for DATA: 10x SAS SSD flash 1.2TB (1.6 formatted to 1.2, over-provisioned for write performance because of garbage collection) which means 12 TB (12TB raw capacity, 6TB double-mirrored, 4TB triple-mirrored) expandable to 24 TB in the same chassis. You can add another storage shelf with additional 24TB.

Note that latest ODA X5 had 16x 8TB disks, so 128TB so X6-HA has higher performance but decreased capacity.

Virtualization, High Availability

Virtualization with OVM is only for ODA X6-HA. Only one VM per node has the resources to run Oracle Databases. You can add additional ‘Guest VMs’ for your applications (not for databases as the I/O is optimal only in the ODA Base VM). You can define VLANs.

The ODA Lite (2S/2M/2L) cannot run applications on guest VMs as they are bare metal only. If you want to run applications on ODA Lite, you can now use KVM, but remember that you still need to license all activated cores for each product because KVM is only ‘soft partitioning’ for Oracle LMS.

High Availability (with RAC) is only for ODA X6-HA.
A standby database to another ODA is possible for all models: Data Guard when in Enterprise Edition, or manual standby (we recommend Dbvisit standby – see one of our customer case study http://www.dbvisit.com/customers/case-study-das-protection-juridique/) when in Standard Edition.

Price

According to the Price List (http://www.oracle.com/us/corporate/pricing/exadata-pricelist-070598.pdf)

  • ODA X6-2S costs USD 18000
  • ODA X6-2M costs USD 24000
  • ODA X6-2L costs USD 40000
  • ODA X6-2 HA costs USD 72000

Full specifications

The full specification is available in the Oracle System Handbook:

A simple presentation is in the 3D view:

 

Cet article ODA X6 Small Medium Large and High Availability est apparu en premier sur Blog dbi services.

Join Elimination Bug

A few years ago a bug relating to join elimination showed up in a comment to a post I’d done about the need to keep on testing and learining. The bug was visible in version 11.2.0.2 and, with a script to replay it, I’d found that it had disappeared by 11.2.0.4.

Today I had a reason to rediscover the script, and decided to test it against 12.2.0.1 – and found that the bug was still present.

Here’s the model:


rem     Script:         join_eliminate_bug_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2012

drop table child purge;
drop table parent purge;

create table parent (
        id      number(4),
        name    varchar2(10),
        constraint par_pk primary key (id)
        deferrable initially immediate
)
;

create table child(
        id_p    number(4)       
                constraint chi_fk_par
                references parent,
        id      number(4),
        name    varchar2(10),
        constraint chi_pk primary key (id_p, id)
)
;

insert into parent values (1,'Smith');
insert into parent values (2,'Jones');

insert into child values(1,1,'Simon');
insert into child values(1,2,'Sally');

insert into child values(2,1,'Jack');
insert into child values(2,2,'Jill');

commit;

begin
        dbms_stats.gather_table_stats(user,'child');
        dbms_stats.gather_table_stats(user,'parent');
end;
/

set serveroutput off

select
        chi.*
from
        child   chi,
        parent  par
where
        par.id = chi.id_p
;

select * from table(dbms_xplan.display_cursor);

The setup is just to show you the correct results with join elimination taking place. Here’s the output from the query and the actual execution plan:

      ID_P         ID NAME
---------- ---------- ------------
         1          1 Simon
         1          2 Sally
         2          1 Jack
         2          2 Jill

4 rows selected.


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1whubydgj8w0s, child number 0
-------------------------------------
select  chi.* from  child chi,  parent par where  par.id = chi.id_p

Plan hash value: 2406669797

-----------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    11 |
|   1 |  TABLE ACCESS FULL| CHILD |     4 |    48 |    11 |
-----------------------------------------------------------

On a single column join, with referential integrity in place, and no columns other than the primary key involved, the optimizer eliminates table parent from the query. But if I now defer the primary key constraint on parent and duplicate every row (which ought to duplicate the query result), watch what happens with the query:


set constraint par_pk deferred;

insert into parent (id,name) values (1,'Smith');
insert into parent (id,name) values (2,'Jones');

alter system flush shared_pool;

select
        chi.*
from
        child   chi,
        parent  par
where
        par.id = chi.id_p
;

select * from table(dbms_xplan.display_cursor);


      ID_P         ID NAME
---------- ---------- ------------
         1          1 Simon
         1          2 Sally
         2          1 Jack
         2          2 Jill

4 rows selected.


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1whubydgj8w0s, child number 0
-------------------------------------
select  chi.* from  child chi,  parent par where  par.id = chi.id_p

Plan hash value: 2406669797

-----------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |    11 |
|   1 |  TABLE ACCESS FULL| CHILD |     4 |    48 |    11 |
-----------------------------------------------------------

I get the same plan, so I get the same results – and notice that I flushed the shared pool before repeating the query so I haven’t fooled Oracle into reusing the wrong plan by accident – it’s a whole new freshly optimized plan.

Just to show what ought to happen here’s the last bit of the test case:


select  /*+ no_eliminate_join(@sel$1 par@sel$1) */
        chi.*
from
        child   chi,
        parent  par
where
        par.id = chi.id_p
;

select * from table(dbms_xplan.display_cursor);


      ID_P         ID NAME
---------- ---------- ------------
         1          1 Simon
         1          2 Sally
         1          1 Simon
         1          2 Sally
         2          1 Jack
         2          2 Jill
         2          1 Jack
         2          2 Jill

8 rows selected.


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5p8sp7k8b0fgq, child number 0
-------------------------------------
select /*+ no_eliminate_join(@sel$1 par@sel$1) */  chi.* from  child
chi,  parent par where  par.id = chi.id_p

Plan hash value: 65982890

-----------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |       |       |     5 |
|   1 |  NESTED LOOPS                |        |     4 |    60 |     5 |
|   2 |   NESTED LOOPS               |        |     4 |    60 |     5 |
|   3 |    INDEX FULL SCAN           | PAR_PK |     2 |     6 |     1 |
|*  4 |    INDEX RANGE SCAN          | CHI_PK |     2 |       |     1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CHILD  |     2 |    24 |     2 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("PAR"."ID"="CHI"."ID_P")


I ran this test on 11.2.0.4 – and then repeated it on 12.2.0.1: the bug is still present (although I thought I’d seen a MoS note saying it had been fixed in 12.1).

It’s always a little dangerous playing around with deferrable constraints – my view is that you should keep the interval of deferment as short as possible and don’t try to use it for doing anything other than correcting known data errors. At present if you have code that defers constraints and runs non-trivial queries afterwards you might want that code to start with an “alter session” to set the hidden parameter _optimizer_join_elimination_enabled to false (after checking with Oracle support, of course).

Goodbye Oracle Corporation (Modern Love)

  It’s with a mixture of excitement, trepidation and a touch of sorrow that I today handed in my resignation after 6 years at Oracle Corporation. My plans at this stage are to take a bit of time-off initially, before going back to providing Oracle Database consulting and training services as I’ve done in the […]

Friday Philosophy – Are Leaving Presentations A Quaint British Tradition?

A few days ago a friend, Robert Lockard, started a discussion on Facebook about bad bosses and the strange things they did. I mentioned how one of my first bosses had refused to do my leaving presentation, arguing with his boss (very loudly so all could hear, despite it being in “an office” – a plastic box in the corner) that he did not want to be nice to me and do the presentation. Neither did his boss!

https://mwidlake.files.wordpress.com/2017/08/screenhunter_172-aug-10-21-... 920w, https://mwidlake.files.wordpress.com/2017/08/screenhunter_172-aug-10-21-... 150w, https://mwidlake.files.wordpress.com/2017/08/screenhunter_172-aug-10-21-... 300w, https://mwidlake.files.wordpress.com/2017/08/screenhunter_172-aug-10-21-... 768w" sizes="(max-width: 460px) 100vw, 460px" />

Another friend of mine, Jeff Smith (oh he of SQL*Developer fame) replied to my comment saying “what is a ‘leaving presentation’ – they let you get up in front of everyone and invite them to kiss your a$$ goodbye? Because, that sounds pretty amazing”.
That took me by surprise, it had never occurred to me that “leaving presentations” were not universal. That’s one of the great things about the global reach of social media, it helps you realise that so many things you thing are normal are, in fact, local to your region and are seen as bizarre by others in other cultures.

So that made me wonder how common “leaving presentations” are? I could have asked on Twitter or mailed a few friends, but I have this blog thing I can use…

I suppose I should describe what a “leaving presentation” is, in case other cultures do the same but call it something else (or just do it without a title). So:-

For most of my working life in the UK, if you are a permanent member of staff and it is known you are leaving (so it’s not a case of you being sacked) and it is only you (so it’s not a case of the company slashing the workforce) then “Shirley from Accounts” will take charge and will buy a card. I don’t know why, but it is nearly always a woman who gets the card and the same woman organises most people’s leaving card. The card is sent around the office in an envelope and people sign it (maybe adding some words like “begone foul demon”) and, depending on the organisations, there may be a collection made at the same time. It is beholden on you, the leaver, to pretend to never notice the card going around (or checking how the collection is going. I did know one guy who put money into his own collection to make himself seem more popular!).

Back in the 1980’s/90’s, the places I worked at did not have email – not even internal systems on the mainframe, so lots of envelopes would be going around with company memos or things you had to read and sign you had read. So the odd card going around was easy to ignore. These days of course everything is email so the last few times I’ve noticed a card going around, it stuck out like a sore thumb and you knew it was a leaving (or “congratulations” or “get well soon”) card.

Then on your last day your boss gets the team around, (s)he says you did not steal much and you did not piss off all the users, they give you the card and pretend to care what happens to you in the future. People then clap politely. This not the US, there is no whooping or saying it is the saddest day of their lives. If a collection had been made they will have bought you something with the collection. It is always almost, but not quite totally, useless. You might have to do a small speech and then, the best bit, you take them down the pub and buy everyone a drink (it used to happen at lunch time but now it tends to be more at the end of the day). The round generally costs you more than the collection they gathered for you. Sadly the last bit seems to be dying out.

As a contractor/external consultant you tend to avoid the mild discomfort of it all as you are not around long enough to become part of the team and, well, it’s just not done for over-paid contractors.

Personally, I have always found the whole thing a bit weird and, if I am the leaver, mildly uncomfortable. I try to avoid the whole thing by keeping my exit quiet or stealing enough stationary so that management do not feel I deserve a leaving presentation. Of course, in the case I cite above, I nearly avoided it just by making my bosses hate me. ho Hum.

But I do still try and do one bit, the “taking people to the pub” at the end of the day, even when I am a contractor and we are not supposed to get leaving presentations.

So what if anything do they do where you are? Is the leaver expected to do something (bring in cake, kiss everyone, do a dance)? Do you have a tradition that is eminently sensible and common in your country but, not you you come to think of it, maybe it’s a touch strange? Or do people just leave quietly and no one notices much – except for the scramble for the chair or your higher-res screen?