More Than Four

Areas of Interest, as counted by my cat

Category: Oracle (Page 2 of 2)

Installing VirtualBox 4.3 on Windows 7-64 – Solved

February 10, 2015 / / 16 Comments

I was preparing for an Oracle online workshop on Database 12c multi-tenancy, and as part of the prep, you get to download a VM image with the lab environment. Of course, being Oracle, it is a Virtual Box vm.

Now, I like Virtual Box. It works great on my Mac at home, but for some reason I’ve had problems installing 4.x on my work laptop (Lenovo W520, getting on a bit now but still recommended).

The problem is Virtual Box almost completely installs – until it gets a certain point, then the progress bar runs backwards and it says “rolling back install”, followed by:

“Oracle VM VirtualBox 4.3.20 Setup Wizard ended prematurely” because of “an error”. This is followed up by “Installation failed! Fatal error during installation.”.

No other information provided. Running as Adminstrator after a cold reboot didn’t help.

Well… I really wanted to have the Workshop lab environment available, so despite the fact that last time I encountered this problem I didn’t find any useful help on the web, I tried again… and this time, I found something on the forums, a post by user Tronmech:

https://forums.virtualbox.org/viewtopic.php?f=6&t=61785

I’ll repeat the instructions here in case for some reason that post vanishes:

  • Go into the registry at: HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Network
  • Change “MaxNumFilters” from 8 to 20 (decimal)
  • Reboot
  • Install 4.3.x (run installer as administrator, just in case)

This time, the install was successful. Thanks, Tronmech!

After some more research, it seems that Windows 7 has a maximum hard-coded limit of 14 network filter drivers… and for some reason, this setting in the registry reduces it further to 8, by default.

So setting the MaxNumFilters key to 20 probably only lifts the artificial limit and allows 14 possible filters. Applications such as Virtual Machine managers and VPN clients need to add filters to the network stack, and increasing this limit in the registry seems to be the recommended fix.

In one KB article, I read that a Windows Service Pack could not be installed until some filters were removed!

To view currently deployed Network Filter Drivers, right-click on your connection widget in the Network Connections control panel applet, and view properties:

I believe that each of those checkboxed items is a “Network Filter Driver”. I have 9 of them, and I assume that the “VirtualBox Bridged Networking” driver was the 9th one added after I lifted the limit of 8.

Implementing a Proper() case function in PL/SQL

October 2, 2013 / / 1 Comment

I found that I needed to convert some raw column names (i.e. THIS_IS_MY_COLUMN) into a human-readable “Proper” or “Title” case form, e.g. This Is My Column.

Scouring the web, I didn’t find out I could steal, so here’s my implementation:

   function Proper(
      p_string varchar2
   ) return varchar2
   is
      l_string varchar2(200);
      l_proper varchar2(200);   
   begin
      l_proper := '';
      -- change any underscores to spaces:
      l_string := replace(lower(p_string), '_',' ' );

      for i in 1..length(l_string)
      loop
         -- obviously the first character is UPPER:
         if i =1 then
            l_proper := l_proper || upper( substr( l_string, i, 1 ) );
         else
         -- if the character is preceded by a space, UPPER it:
            if substr( l_string, i-1, 1 ) = ' ' then
               l_proper := l_proper || upper( substr( l_string, i, 1 ) );
            else
               l_proper := l_proper || substr( l_string, i, 1 );
            end if;            
         end if;   
      end loop;
      return l_proper;
   end;

Exploring AUTHID with an eye to partitioning client data

May 5, 2013 / / 0 Comments

Problem

Is it possible to design a multi-tenant architecture in an Oracle DB instance, such that different customers’ data is transparently partitioned whilst keeping common code and common tables in a single location?

Possible Strategy

Consider the following architecture:

Here we have two schemas in an Oracle database instance:

  • U_Common contains common tables and stored procedures, packages, etc.
  • U_Cust_1 contains customer-specific tables, with private data.
  • Client processes acting on behalf of Customer_1 are connecting to the U_Cust_1 schema.

(You may assume we also have U_Cust_2, etc.)

Assume that all access is performed through stored procedures or similar code objects.
In order for sessions connecting to U_Cust_1 to execute specific procedures in U_Common, they need:

  • EXECUTE permissions on the objects in U_Common;
  • Either:
    o explicit namespace prefixes, e.g. U_Common.DoSomething()
    o a synonym object referencing the code object in U_Common

In order to have common code objects that reference both common tables and customer tables, we need to create “template” versions of the tables in the U_Common schema:

However, when U_Cust_1 invokes the procedure in U_Common, the session can’t “see” the customer-specific version of the table, because the code is executing in the context of U_Common. It will see the template table data (or lack of it – presumably there are no rows in the template tables).

This can be addressed by adjusting the AUTHID pragma when the code objects are defined:

Default:

  create or replace package U_Common [authid definer] is ..

Change to:

  create or replace package U_Common authid current_user is ..

According to the documentation, this AUTHID clause instructs Oracle on whether to run the code with the invoker’s rights, or the owner’s rights. (the default is DEFINER, or owner.)

In practice, this means that when U_Cust_1 executes the code object defined as AUTHID CURRENT_USER, the customer tables in U_Cust_1 are in scope, and are accessed instead of the template tables in the U_Common schema.

Two interesting observations about executing code defined as AUTHID CURRENT_USER:

  • the code does NOT see the Common tables. Any table in U_Common is “out of scope”;
  • the code can see other procedures and packages in U_Common, including those defined as AUTHID DEFINER (default);
  • when the code makes a nested call to those other procedures, the tables in U_Common are now visible!

Here’s a sequence diagram of the call stack:

This means that everything will work just fine, so long as the code objects are classified into “programs that access common data” and “programs that access customer data”.

Concerning Views

Views don’t work so well.

You can define a view in U_Common that joins a Common table with a Customer table, but any U_Common procedure that is authid current_user will not be able to “see” the view from the context of U_Cust_1.

You can try this:

grant SELECT on View to U_Cust_1
create synonym in U_Cust_1 for the U_Common.View

But the result set will be entirely pulled from the U_Common schema tables.

So this technique probably isn’t going to be used to create a transparently customer-partitioned application architecture. But it is still worth understanding how AUTHID works.

Thank you Derek Crudginton

January 22, 2008 / / 0 Comments

This is exactly what I needed to know:

Installing Oracle 10g r2 on Solaris 5.11 is painless except before
you ./runInstaller you have to edit the ./install/oraparam.ini and
change line 42 to something like:

Solaris=5.8,5.9,5.10,5.11

Then it will pass the first check and continue with install.

Newer posts »

© 2024 More Than Four

Theme by Anders Noren — Up ↑