Sunday, March 22, 2015

Setting up SQL Developer with Instant Client on MacOSX

Since I started a new job I also wanted to try out MacBook as my new laptop. I've never been a Mac user, but there seems to be a large group of people who think Macs are the best, so I needed to find out the goodness of Macs myself :) A quick tip: Don't upgrade to Yosemite if you laptop is bound to Active Directory.

One part of setting up my new laptop was also setting up SQL Developer WITH Oracle Driver, because a year or two ago I rolled out radius authentication for all users who need to access the production databases directly and radius (and other non-default-password authentication schemes) do not work with thin JDBC driver. Being new to Macworld and not finding any guides for this task online, it was quite a struggle in the beginning. Here I'm trying to share my experiences if anybody else find themselves in the similar situation. If you find any mistakes here, please let me know - I'm new to the world of MacOS :)

Jan Karremans just today published a similar article about Mac and Instant client, but the goal of my blog post is to get SQL Developer connected through Instant Client.

JDK

First you need Java installed, I installed 64-bit JDK8, but it would be better to install 64-bit JDK7, since SQL Developer 4.0 is not supported on JDK8.

SQL Developer

When I'm writing this, SQL Developer 4.1 is in Early Adopter release and 4.0 is in production. I installed SQL Developer 4.0.3.

Oracle Instant Client

There is another reason to choose SQL Developer 4.0 over 4.1 - 4.1 requires version 12.1 of the Oracle Client and that is not yet released for MacOS. SQL Developer 4.0 requires Oracle Client 11.2.0.3 and that is available for MacOS. I downloaded Instant Client 11.2.0.3 (64-bit - since I installed 64-bit JDK): instantclient-basic-macos.x64-11.2.0.3.0.zip and instantclient-sqlplus-macos.x64-11.2.0.3.0.zip to be precise. I unzipped both these files under /opt so my Instant Client installation directory is /opt/instantclient_11_2. After unzipping, Instant Client installation instructions require creating the following symlinks:

Configuring SQL Developer

Open SQL Developer, open Preferences and go to Database > Advanced. Configure Oracle Client paths and check Use Oracle Client and Use OCI/Thick driver boxes. Here is the sample screenshot from my settings.

When you press the Configure button to set the Oracle client path, there is also a button to Test if the Client was loaded correctly. Currently this test should fail.

Setting the environment variables

We need to set two environment variables: DYLD_LIBRARY_PATH to make sure applications can find and load the instant client libraries and TNS_ADMIN so instant client could find sqlnet.ora and tnsnames.ora files. In my case sqlnet.ora is essential, since I need to use Radius authentication.

First I added these environment variables to ~/.profile:

After restarting terminal and verifying that these environment variables are indeed set, execute SQL Developer app directly from terminal by executing open /Applications/SQLDeveloper.app/.

Open preferences and press the Oracle Client Test button again, now the test should succeed and SQL Developer is connected through Instant Client.

All good! Great success! Guess again... Close SQL Developer and launch it from Docker/Launchpad and you will see the Oracle Client test failing again. This is because the environment variables under ~/.profile or ~/.bash_profile are loaded only inside Terminal.

Setting environment variables on login

This seems to be the most complex part of this story since it seems to change with every MacOS release. The method that is working for me under Yosemite (and in no way am I declaring that it is the best method, I am a Mac newbie after all), is this:

Open application Script Editor and paste the following AppleScript there:

Yes, it is also necessary to set TNS_ADMIN, since setting the TNS admin directory path under SQL Developer preferences does not work if you need to use specific settings in sqlnet.ora, for example authentication.

Save the script and in the save dialog set File format: Application. And finally System settings -> Users & Groups -> Login items press + sign and select the application you just saved. Now, log out from MacOS and log back in. Now SQL Developer should work with Instant Client even when you launch it from Launchpad.

I got this method from a Stackoverflow thread.

Thursday, January 1, 2015

Script to delete multipathed ASMLIB storage devices from Linux

Before storage device can be removed (or unassigned) from server, it must first be removed from Linux. This is even more necessary if the server is running Oracle ASMLib, because if there are any removed or read-only storage devices present when running service oracleasm scandisks, the command will just hang and server load will start to increase (maximum I've seen was 3500).

Here you find RedHat instructions on how to cleanly remove storage devices in RHEL6. A lot of steps, especially when running multipath and having multiple paths to each device.

Since we do database replication and cloning to test using storage replication, then assigning and unassigning storage devices from test servers is quite frequent and if not done properly and cleanly, we have to reboot the server. Here is a little script to help with that task. It takes ASMLib disk names to be removed as an argument, finds all paths to the device and removes them from Linux.

Tested in OEL5 (using RHEL kernel) and OEL6 (using UEK2 kernel). This script only deals with dm-* and sd* devices, so before using please check and modify the script for your environment. Before removing the storage devices from Linux, make sure that the disk groups are dismounted from ASM and they are not used.

Sunday, December 28, 2014

Sample code: Using the Oracle ZFS Storage Appliance REST API from Python

Most (all?) of the Oracle ZFS Storage Appliance (ZS3-4 in my case) management functions are also exposed through REST API so it is possible to script and automate the storage management tasks. No proprietary clients or GUI needed, just need to write a simple script that can send some REST commands over HTTPS to the appliance and parse the JSON output.

I had a task of automating test database refresh from production and part of that workflow is also cloning and attaching LUNs from ZFSSA. I did not find any sample code to access ZFSSA online, so here am I publishing my code. It is a Python script that makes a use of a few ZFSSA REST API calls and publishes them as Fabric tasks.

This script exposes four tasks:

  • clone - Creates a new clone from replication
  • attach_stage1 - Attaches all LUNs in a project to a single target server
  • attach_stage2 - Attaches all LUNs in a project to multiple target servers
  • drop - Deletes a project

ZFSSA REST API works fine if each LUN has only a single initiator group, but when assigning LUN to multiple initiator groups (to be used in a RAC setup), I observed the following errors (they must be ZFSSA REST API bugs):

  • When modifying LUN to be part of multiple initiator groups (PUT request to /api/storage/v1/pools/_poolname_/projects/_projectname_/luns/_lunname_), then the API responds with (argument numbers may be different): {"fault": {"message": "invalid input argument (assignednumber: invalid literal for int() with base 10: '0,0,0')", "code": 400, "name": "ERR_INVALID_ARG"}}. Nevertheless, ZFS GUI confirms that the operation was completed successfully. This script just ignores the returned error message, when assigning LUN to multiple initiator groups.
  • If any LUN in the project has multiple initiator groups defined, then API call to get the list of LUNs (GET request to /api/storage/v1/pools/_poolname_/projects/_projectname_/luns) in a project returns an error immediately: {"luns":[{"fault": {"message": "internal error", "code": 500, "name": "ERR_INTERNAL"}}]}. This script cannot reattach the LUNs in this case, since getting the list of LUNs fails.

Thursday, November 13, 2014

12c metadata only default values for NULL columns part 2

Yesterday I attended #cluboracle event in London (I highly recommend this event! ... but I don't recommend Thameslink train service :) ). In this event one interesting discussion popped up regarding 12c. I've already written about this feature, so in this post I'll clarify some points as I understand them and test out a few more things.

Here is a quote from my last post summing up my findings so far:

This new column seems to set a bit, whether the value in the column "should be trusted" or replaced with a default. So, all the rows that existed before the new column was added have NULL is this new column, but all the newly modified rows set a specific bit to 1. Looking at the original expression, then there is also a possibility that this bit is set to 0, maybe this newly added column can contain other "row status bits" also?

DECODE (TO_CHAR (sys_op_vecbit ("SYS_NC00003$", 0)),
    NULL, NVL ("DEF_W_NULL", 'THIS IS DEFAULT2'),
    '0', NVL ("DEF_W_NULL", 'THIS IS DEFAULT2'),
    '1', "DEF_W_NULL"
)

Therefore... Only the rows that existed previously don't have the default value written to the data blocks, all rows added after adding the column have the DEFAULT value written to the data block directly.. What happens if you update the old row?

SQL> create table default_values (
   id number primary key
  3  );

Table created.

SQL>  insert into default_values values (1);

1 row created.

SQL>  insert into default_values values (2);

1 row created.

SQL>  insert into default_values values (3);

1 row created.

SQL>  insert into default_values values (4);

1 row created.

SQL> alter table default_values add def_w_null varchar2(20) default 'THIS IS DEFAULT2';

Table altered.

SQL> insert into default_values (id) values (5);

1 row created.

SQL> commit;

Commit complete.

Here is the projection information for column DEF_W_NULL.

DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),
  NULL,NVL("DEF_W_NULL",'THIS IS DEFAULT2'),
  '0',NVL("DEF_W_NULL",'THIS IS DEFAULT2'),
  '1',"DEF_W_NULL")

The hidden column containing the bitmap in this test is SYS_NC00002$. Here is the current contents of this column and the interesting bit.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0)) bitval  FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00002$  BITVAL
---------- -------------------- ----------- ------------- --------
         1 THIS IS DEFAULT2     is null
         2 THIS IS DEFAULT2     is null
         3 THIS IS DEFAULT2     is null
         4 THIS IS DEFAULT2     is null
         5 THIS IS DEFAULT2     is not null 01            1

All as expected so far. ID 1-4 were added before adding the new column, so SYS_NC00002$ IS NULL for them and the NULL value in file is replaced with metadata default value. ID=5 was added after, so its BITVAL=1 and from the projection information we see, that this column is displayed how it is written to the file. What happens if I update ID=1 row but don't touch DEF_W_NULL column?

SQL> update default_values set id=10 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval  FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00002$  BITVAL
---------- -------------------- ----------- ------------- --------
        10 THIS IS DEFAULT2     is null
         2 THIS IS DEFAULT2     is null
         3 THIS IS DEFAULT2     is null
         4 THIS IS DEFAULT2     is null
         5 THIS IS DEFAULT2     is not null 01            1

Nothing happened with DEF_W_NULL column, Oracle only updated ID column. What if I update DEF_W_NULL column first with keyword DEFAULT and then with the same value?

SQL> update default_values set def_w_null=default, id=11 where id=2;

1 row updated.

SQL> update default_values set def_w_null='THIS IS DEFAULT2', id=12 where id=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval  FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00002$  BITVAL
---------- -------------------- ----------- ------------- --------
        10 THIS IS DEFAULT2     is null
        11 THIS IS DEFAULT2     is not null 01            1
        12 THIS IS DEFAULT2     is not null 01            1
         4 THIS IS DEFAULT2     is null
         5 THIS IS DEFAULT2     is not null 01            1

Oracle has updated the physically stored value, so keep an eye on row migration possibilities!

What happens if I now change the default value in table metadata? Looking at the projection information it must update the existing rows to store the old default value. Lets verify.

SQL> alter table default_values modify DEF_W_NULL varchar2(20) default 'NEW DEFAULT';

Table altered.

SQL> insert into default_values (id) values (6);

1 row created.

SQL> commit;

Commit complete.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval  FROM default_values;

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00002$  BITVAL
---------- -------------------- ----------- ------------- --------
        10 THIS IS DEFAULT2     is null
        11 THIS IS DEFAULT2     is not null 01            1
        12 THIS IS DEFAULT2     is not null 01            1
         4 THIS IS DEFAULT2     is null
         5 THIS IS DEFAULT2     is not null 01            1
         6 NEW DEFAULT          is not null 01            1

Whoa... really interesting. I didn't do anything! And I think it makes sense now also, it only has to store the initial DEFAULT value, when the column was not present in the table, rows added later already have the correct value written to the files. It must also mean, that Oracle stores two different default values in table metadata - the currently active default value and the initial value when column was added.

The last small thing I want to test, what if I have multiple columns with NULL default values in the same table, does it add one hidden column for each of them?

SQL> alter table default_values add def_w_null2 varchar2(20) default 'THIS IS DEFAULT 3';

Table altered.

SQL> insert into default_values (id) values (6);

1 row created.

SQL> commit;

Commit complete.

SQL> select id, DEF_W_NULL, nvl2(SYS_NC00002$,'is not null','is null') is_hidden_col_null, SYS_NC00002$, TO_CHAR (sys_op_vecbit(SYS_NC00002$,0))bitval1, TO_CHAR (sys_op_vecbit(SYS_NC00002$,1)) bitval2  FROM default_values where id in (6,7);

        ID DEF_W_NULL           IS_HIDDEN_C SYS_NC00002$  BITVAL1   BITVAL2
---------- -------------------- ----------- ------------- --------- ---------
         6 NEW DEFAULT          is not null 01            1         0
         7 NEW DEFAULT          is not null 03            1         1

The new column projection information:

DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",1)),
  NULL,NVL("DEF_W_NULL2",'THIS IS DEFAULT 3'),
  '0',NVL("DEF_W_NULL2",'THIS IS DEFAULT 3'),
  '1',"DEF_W_NULL2")

I'm pleasantly surprised again! It did not add a new column, just used another bit in the same column.

Tuesday, November 4, 2014

Slides from OUGE Meetup #3

I gave a talk about my highlights from Oracle Open World 2014 and how to use CloneDB in Oracle User Group Estonia Meetup #3. It was a really good meetup and thanks to everybody who attended.

Here you can find the slides