Adding additional controlfiles (multiplexing) in Oracle using ASM

Posted by Jamie on May 12, 2009

When creating a database using ASM, the Oracle database creation assistant, ordbca, has an annoying tendency to only create one controlfile by default.  This is unequivocally a bad idea for any database that contains meaningful data, therefore it is necessary to add additional controlfiles.  Before ASM, you could shutdown the db, copy the controlfile on the OS level and modify the control_files parameter to add additional controlfiles.  If you use ASM, the operation is not that simple anymore, you need to use RMAN to perform the copy.  Here’s how I did it on my RAC database ( on ASM (Note: check your ASM locations – this is only an example) : Continue reading…

UPDATE! – Ruby ActiveRecord and Oracle: Auto Incrementing Primary Keys using Triggers

Posted by Jamie on July 11, 2008

Well, it looks like this has been fixed in the latest versions of the Oracle enhanced adapter for ActiveRecord. It looks like there was a reorganization of the different components of the ActiveRecord/Adapter relationship that had originally led to this problem, according to the documentation submitted with the fix. Thanks to whomever was able to provide the patch; You just made my life a little bit easier.

Ruby ActiveRecord and Oracle: Auto Incrementing Primary Keys using Triggers

Posted by Jamie on January 30, 2008

One of the most obvious limitations that I have faced when using ActiveRecord is it’s ability to adapt to legacy systems, especially Oracle databases. Many of the Oracle databases that I have used employ triggers to populate numeric primary key columns when data is inserted into the database tables. While this is a very nice and easy way to hide the mechanisms used to identify the data from the application developers when they are using straight DML, when you try to use ActiveRecord on these tables, ESPECIALLY when dealing with large object (LOB) fields, you get into some real hair-pulling situations.

ActiveRecord handles LOB inserts in two steps:

  1. The data is inserted into the database and the LOB field is initialized with an empty lob.
  2. The record is updated with the provided LOB data, replacing the empty lob.

As part of step 1 above, ActiveRecord pre-fetches the sequence that is used to populate the primary key field of the table. If you then have a trigger on that table that updates the incoming primary key column with the next sequence value, your Ruby object and the data inserted into the database are out of sync. Therefore, in step
2 of the LOB insert process described above, ActiveRecord is attempting to update a row with a primary key value that does not exist and no LOB values are ever populated.

To prevent this, I created the following code that alters ActiveRecord::Base and ActiveRecord::ConnectionAdapters::OracleAdapter to use the trigger-generated key (via the Oracle ‘RETURNING’ clause) in the AR object.

module ActiveRecord
module ConnectionAdapters #:nodoc:
class OracleAdapter < AbstractAdapter
def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
cursor = @connection.parse(sql)
log(sql, name) { cursor.exec(0) }
# return the primary key value for the record just inserted

class Base
# Creates a record with values matching those of the instance attributes and
# returns its id.
def create
if && connection.prefetch_primary_key?(self.class.table_name) = connection.next_sequence_value(self.class.sequence_name)

quoted_attributes = attributes_with_quotes

statement = if quoted_attributes.empty?
“INSERT INTO #{self.class.quoted_table_name} ” +
“(#{quoted_column_names.join(‘, ‘)}) ” +
“VALUES(#{quoted_attributes.values.join(‘, ‘)}) returning #{self.class.primary_key} into :1”
end = connection.insert(statement, “#{} Create”,
self.class.primary_key,, self.class.sequence_name)

@new_record = false

Now, this is really, really rough. I am using the 2.x version of ActiveRecord, and any updates to these sections in future releases will break my changes. It does, however, work for me right now. As I have time I will refactor this and increase its maintainablility.