Dave Welch at House of Brick Technologies Writes a Serious Takedown of a Gartner Article

July 13, 2015

I don’t normally discuss Oracle licensing on my blog. This is primarily because it is not an area I have spent a lot of time on. However, the licensing experts (note: they are not lawyers) over at House of Brick technologies are very good at explaining licensing issues with Oracle on VMware.

In this paper, Dave Welch at House of Brick writes a fairly severe takedown of a Gartner article on Oracle licensing. And while I did not read the Gartner article ($195 seems a little pricey to me), Welch makes a point repeatedly that is good to remember when dealing with Oracle: Oracle statements and blogs and other similar publications are not legally binding, only what is written in your contract or other supporting legal documents are legally binding. This is especially important when it comes to partitioning your Oracle installation.

Here is the article. http://houseofbrick.com/review-of-gartners-nov-2014-oracle-licensing-paper/

If you are looking for further information on Oracle licensing on VMware, there are several applicable articles on the House of Brick web site: http://houseofbrick.com/blog/

Running Jobs in Parallel using DBMS_SCHEDULER (part 2)

April 28, 2015

My second example of using DBMS_SCHEDULER to run jobs in parallel involves another common DBA task, rebuilding indices.

Under certain circumstances, deteriorating performance on a database can be traced to fragmented indices. In addition, direct path loads and certain other operations can invalidate indices, thus requiring them to be rebuilt.

In this example, indices that are invalid are identified and jobs submitted to rebuild the index. Not only can the individual rebuild processes run in parallel, but multiple rebuild jobs can be run at the same time by using DBMS_SCHEDULER.

This example does not use a driver table, but rather looks at the Oracle data dictionary tables to identify candidates for rebuild. The changes required to use a driver table containing the indices to rebuild are fairly simple, and I leave that to the reader.

In this example, we create a program that calls a stored procedure, and then individual jobs that call the program.  This allows us to use the arguments of owner and index name to specify each index to be rebuilt.

Below is the driving script, that finds each index needing to be rebuilt, and creates the job to rebuild them.  Typically we will rebuild two indexes at the same time, each with degree of parallelism set to four.


CREATE OR REPLACE
procedure maint_enable_rebuild(num_simul in integer default 2,par_degree in integer default 4) authid current_user
is
owner varchar2(30);
index_name varchar2(30);
type index_record is record
( owner varchar2(30),
index_name varchar2(30));
type index_array_type is varray(10000) of index_record;
index_count integer :=0;
lcounter integer :=0;
sql_cmd varchar2(2000);
index_array index_array_type;
num_running integer :=0;
continue_next varchar2(5) :='FALSE';
exit_now varchar2(5) :='FALSE';
num_ran integer :=0;
num_not_ran integer :=0;
num_short_count integer := num_simul;
begin
begin
maint_drop_rebuild_jobs;
exception
when others then null;
end;
dbms_scheduler.create_program
(program_name=>'REBUILD_INDEX_PROGRAM',
program_type=>'STORED_PROCEDURE',
program_action=>'MAINT_RUN_REBUILD',
number_of_arguments=>3,
enabled=>false,
comments=>'Stored procedure to rebuild unusable indexes asynchronously'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'owner',
argument_position=>1,
argument_type=>'varchar2'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'index_name',
argument_position=>2,
argument_type=>'varchar2'
);
dbms_scheduler.define_program_argument (
program_name=>'REBUILD_INDEX_PROGRAM',
argument_name=>'par_degree',
argument_position=>3,
argument_type=>'integer',
default_value=>4
);
dbms_scheduler.enable('REBUILD_INDEX_PROGRAM');
dbms_output.enable(100000);
index_array:=index_array_type();
for icursor in
(select owner, index_name from all_indexes where status in ('INVALID','UNUSABLE') and index_type='NORMAL' and owner !='SYS')
--/* testing only */ (select owner, index_name from all_indexes where owner='SCOTT' and table_name like 'E%')
loop
if icursor.owner is not null
then
index_count:=index_count+1;
index_array.extend();
index_array(index_count).owner:=icursor.owner;
index_array(index_count).index_name:=icursor.index_name;
-- dbms_output.put_line(to_char(index_count)||' '||icursor.owner||' '||icursor.index_name);
end if;
end loop;
for lcounter in 1..index_count
-- for lcounter in 1..52
loop
while continue_next = 'FALSE'
loop
if num_short_count <= 0
then
sleep(1);
select count(1) into num_running from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and state='RUNNING';
num_short_count:=num_simul-num_running;
-- dbms_output.put_line(to_char(num_running));
end if;
if num_running <= num_simul
then
if trim(index_array(lcounter).owner) is not null
then
-- dbms_output.put_line(index_array(lcounter).owner||'.'||index_array(lcounter).index_name);
dbms_scheduler.create_job(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
program_name=>'REBUILD_INDEX_PROGRAM',
comments=>'Unusable index asynch job',
start_date=>sysdate+1000,
enabled=>false,
auto_drop=>false);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>1,
argument_value=>index_array(lcounter).owner
);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>2,
argument_value=>index_array(lcounter).index_name
);
dbms_scheduler.set_job_argument_value(
job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter),
argument_position=>3,
argument_value=>par_degree
);
dbms_scheduler.enable('REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter));
dbms_scheduler.run_job(job_name=>'REBUILD_UNUSUABLE_INDEXES'||to_char(lcounter), use_current_session=>false);
end if;
num_short_count:=num_short_count-1;
continue_next:='TRUE';
else
sleep(5);
continue_next:='FALSE';
end if;
end loop;
continue_next:='FALSE';
end loop;
while exit_now='FALSE'
loop
select count(1) into num_not_ran from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and run_count=0;
if num_not_ran >0
then
sleep(5);
else
exit_now:='TRUE';
select count(1) into num_ran from all_scheduler_jobs where job_Name like 'REBUILD_UNUSUABLE_INDEXES%' and run_count>0;
dbms_output.put_line('Total Indexes rebuilt: '||to_char(num_ran));
end if;
end loop;
end;
/

Note that in this example, we cannot create a job to run immediately because we need to set the job arguments.  Because of this, the procedure DBMS_SCHEDULER.RUN_JOB is used to execute the job.  It may also be possible to use the DBMS_SCHEDULER.ENABLE procedure to execute them.

At the start of the driving procedure, the procedure below is called to remove any old index rebuild jobs that may exist:


CREATE OR REPLACE
procedure maint_drop_rebuild_jobs  authid current_user
is
begin
for drop_job_cursor in
(select job_name from all_scheduler_jobs where job_name like 'REBUILD_UNUSUABLE_INDEXES%')
loop
begin
dbms_scheduler.drop_job(job_name=>drop_job_cursor.job_name,force=>true);
exception
when others then null;
end;
end loop;
begin
dbms_scheduler.drop_program('REBUILD_INDEX_PROGRAM');
exception
when others then null;
end;
end;
/

Finally, as you review the initial script you will see the stored procedure that is called to rebuild the index, It is a very simple stored procedure:


CREATE OR REPLACE
procedure maint_run_rebuild(owner in varchar2, index_name in varchar2, par_degree in integer default 4) authid current_user is
sql_command varchar2(4000);
begin
sql_command:='alter index '||owner||'.'||index_name||' rebuild online parallel '||to_char(par_degree);
--  dbms_output.put_line(sql_command);
execute immediate sql_command;
end;
/

I definitely recommend keeping the number of indices rebuilt at any one time low until you have determined what kind of load your system can handle.  Needless to say they are highly IO intensive.  An index rebuild will generate a lot of redo. The procedures in this blog do not use the nologging option as we have a physical standby database configured. When a database has a physical standby, the force logging option is set so any nologging options are ignored.

If you have many smaller indices to rebuild, set parallel to 1 or 2, and rebuild more indices at the same time.  If you have fewer, larger, indices rebuild fewer indices simultaneously at a higher degree of parallelism.  If you have many large indices to rebuild, well, just figure out what works best.

Running Jobs in Parallel Using DBMS_SCHEDULER (part 1)

April 24, 2015

DBMS_SCHEDULER is a fairly nice feature of the Oracle database that allows Oracle to schedule and run jobs in the background. This also allows the clever DBA to run multiple procedures at the same time.

The method I describe here can be used for most types of database processing. In the examples this post and for part two, I will be demonstrating methods for running some standard database tasks in parallel: index rebuilds and manual statistics jobs.

We have run into certain very large tables in our database that require more attention than is typical when it comes to maintaining stats. Rather than spend the time to tweak the percentage required to mark the stats as stale, we determined that is simpler to just add those tables requiring special attention to our special stats job, which runs weekly to gather statistics on those tables. Below is the table layout:

OWNER		VARCHAR2(60)
TABLE_NAME		VARCHAR2(60)
GROUP_NUMBER		NUMBER
PARALLEL_DEGREE		NUMBER
ESTIMATE_PERCENT		NUMBER
LAST_RUN_DATE		DATE
RUNNING_NOW		CHAR(1)

The intent of this task is to allow the prioritization of the tables by groups, with group one being the most important. Tables in group one are analyzed by themselves, and with a high degree of parallelization to ensure that these will always finish.  After completing all the tables in group one in real time, the code then proceeds through the other groups and analyzes the tables in those groups.

Group numbers larger than one allow the program to work through the individual groups until all are done. Right now, we only have two groups, but the configuration allows for as many groups as desired. Next we have the driver program, this is a stored procedure with a single argument, the number of simultaneous detached jobs that can be run at once.

The procedure ANALYZE_BY_GROUP, below, is the driving procedure for this process.

procedure analyze_by_group(num_simul in number) authid current_user is
/* This Stored procedure is used to analyze only those tables whose statistics need special attention outside the normal
gather_stats_job. Do not place all tables in the stats_groups table, use this only for tables requiring special attention*/
num_running number;
job_exists number;
newstatid varchar2(30):=to_char(sysdate,'yymmddhh24mi');
begin
 dbms_output.enable(100000);
 for analyze_cursor in
 (select owner, table_name, parallel_degree, estimate_percent from stats_groups where group_number=1 and owner=(select user from dual))
 loop
 dbms_stats.export_table_stats(statown=>analyze_cursor.owner,stattab=>'SPECIAL_TAB_STATS',ownname=>analyze_cursor.owner,tabname=>analyze_cursor.table_name,statid=>'SPECIAL'||to_char(sysdate,'yyyymmddhh24mi'));
 if analyze_cursor.estimate_percent=0
 then
 update stats_groups set running_now='T' where owner=analyze_cursor.owner and table_name=analyze_cursor.table_name;
 commit;
-- dbms_output.put_line(analyze_cursor.owner||'.'||analyze_cursor.table_name);
 dbms_stats.gather_table_stats(ownname=>analyze_cursor.owner,tabname=>analyze_cursor.table_name,degree=>analyze_cursor.parallel_degree,
 estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
 else
 update stats_groups set running_now='T' where owner=analyze_cursor.owner and table_name=analyze_cursor.table_name;
 commit;
-- dbms_output.put_line(analyze_cursor.owner||'.'||analyze_cursor.table_name);
 dbms_stats.gather_table_stats(ownname=>analyze_cursor.owner,tabname=>analyze_cursor.table_name,degree=>analyze_cursor.parallel_degree,
 estimate_percent=>analyze_cursor.estimate_percent,cascade=>true);
 end if;
 update stats_groups set running_now='F', last_run_date=sysdate where owner=analyze_cursor.owner and table_name=analyze_cursor.table_name;
 commit;
 end loop;
 for analyze_cursor2 in
 (select distinct group_number from stats_groups where group_number >1 and owner=(select user from dual))
 loop
-- sleep(10);
 select count(1) into num_running from all_scheduler_jobs where job_name like 'ASYNC_ANALYZE%';
-- dbms_output.put_line(to_char(num_running));
 if (num_running <num_simul)
 then
-- dbms_output.put_line('submitting job '||to_Char(analyze_Cursor2.group_number));
 select count(1) into job_exists from all_scheduler_jobs where job_name='ASYNC_ANALYZE_'||to_char(analyze_cursor2.group_number);
 if job_exists >0
 then
 dbms_scheduler.drop_job(job_name=>'ASYNC_ANALYZE_'||to_char(analyze_cursor2.group_number),force=>true);
 end if;
 dbms_scheduler.create_job(
 job_name=>'ASYNC_ANALYZE_'||to_char(analyze_cursor2.group_number),
 job_type=>'PLSQL_BLOCK',
 job_action=>'BEGIN async_analyze_group('||analyze_cursor2.group_number||'); END;',
 comments=>'Asynch analyze job for tables requiring special attention. Argument is the group number from the stats_group table',
 auto_drop=>true, --set this to false for debugging purposes, job will remain on dba_scheduler_jobs view until manually removed
 enabled=>true);
 end if;
 end loop;
end;

Note that the code above simply executes the stored procedure ASYNC_ANALYZE_GROUP using DBMS_SCHEDULER, which allows us to run multiple detached jobs. Below is the ASYNC_ANALYZE_GROUP stored procedure:

procedure async_analyze_group(in_group_number in number)authid current_user
is
  newstatid varchar2(30):=to_char(sysdate,'yyyy-mm-dd hh24mi');
begin
  dbms_output.enable(100000);
  for analyze_cursor in
  (select owner, table_name, parallel_degree, estimate_percent from stats_groups where group_number=in_group_number and owner=(select user from dual))
  loop
    update stats_groups set running_now='T' where owner=analyze_cursor.owner and table_name=analyze_cursor.table_name;
    commit;
    dbms_stats.export_table_stats(statown=>analyze_cursor.owner,stattab=>'SPECIAL_TAB_STATS',ownname=>analyze_cursor.owner,tabname=>analyze_cursor.table_name,statid=>'SPECIAL'||to_char(sysdate,'yymmddhh24mi'));
    if analyze_cursor.estimate_percent=0
    then
      dbms_stats.gather_table_stats(ownname=>analyze_cursor.owner,tabname=>analyze_cursor.table_name,degree=>analyze_cursor.parallel_degree,
        estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
    dbms_output.put_line(analyze_cursor.owner||'.'||analyze_cursor.table_name||'    0');
    else
       dbms_stats.gather_table_stats(ownname=>analyze_cursor.owner,tabname=>analyze_cursor.table_name,degree=>analyze_cursor.parallel_degree,
        estimate_percent=>analyze_cursor.estimate_percent,cascade=>true);
    end if;
    update stats_groups set running_now='F',last_run_date=sysdate where owner=analyze_cursor.owner and table_name=analyze_cursor.table_name;
    commit;
  end loop;
  update stats_groups set running_now='F' where group_number=in_group_number;
  commit;
end;

The async analyze job is run asynchronously through the Oracle job scheduler.  Because it is asynchronous, we can start additional jobs to run at the same time. Note that the logic of this code divides various tables into groups, and then gathers stats group by group.  Because of this layout, we can also put each table in its own group and then run multiple tables simultaneously.  Either method is legitimate, and the code was written to allow for both possibilites.

The procedures above are a fairly simplistic method for running large processes in parallel. Your comments are appreciated as always.

Update on Hugepages (rewrite to fix formatting issues)

March 11, 2015

Update on Hugepages Post

** UPDATED on Feb 14, 2014 **As an update on the hugepages post, I have researched the requirements for enabling the equivalent of Hugepages in AIX.

AIX uses the term largepages when referring to the closest equivalent of lInux hugepages. AIX large pages are 16mb in size, and are implemented differently than Linux hugepages. In fact, my research showed that of all operating systems I generally work with (Linux, Windows, AIX, and sun-sparc) Linux is the most difficult to implement.

Solaris will implement large pages automatically using ism (intimate shared memory). Nothing special needs to be done to do this.

Enabling Large Pages in AIX

In AIX, large pages are implemented as described below. In AIX and Solaris, as in Linux, largepages are not swappable and the full amount is allocated when the instance starts:

  1. Calculate the total size of all SGA’s you will need on the AIX server. This will be the SGA max size, since all memory will be allocated on startup. So if you have 3 instances, with SGA max size of 32G, 8G, and 4G, the total size is 44G. Add in a fudge factor of 2G to be safe, for a total of 46G.
  2. Divide the total SGA in bytes by 16777216 (the AIX large page size) to get the total number of large page segments and add 1:

    (46*1024*1024*1024)/(16777216)=2944+1=2945. So 2945 is the total number of large pages required.

  3. Use vmo to allocate the largepages. You will need to allocate them to take effect immediately, and to persist after reboot, so the vmo command is run twice:

    vmo –o lgpg_regions=2945 –o lgpg_size=16777216

    vmo –r –o lgpg_regions=2945 –o lgpg_size=16777216

  4. Grant the database installation owner (typically oracle) the required privileges to use large pages and to pin memory:
    # Show pinshm settings:
    vmo -o v_pinshm
    v_pinshm = 0# set pinshm to 1 to allow shared memory to be pinned
    vmo -o v_pinshm=1
    vmo -r -o v_pinshm #persist change

    chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
    NOTE: after setting these, the oracle user will have to log out and back in for them to take effect.

  5. In the Oracle database, set sga_max_size and sga_target to the same value, set lock_sga=true, and restart the instance eg:

    Alter system set sga_max_size=16g scope=spfile sid=’*';
    Alter system set sga_target=16g scope=spfile sid=’*';
    Alter system set lock_sga=true scope=spfile sid=’*';
    Shutdown immediate;
    Startup;

To check the status of huge usage, run this command:

vmstat -P all
For 16M hugepages, look at the 16m row
System configuration: mem=61440MB

pgsz memory page
—– ————————– ————————————
siz avm fre re pi po fr sr cy
4K 5986176 1095680 61570 0 0 0 92 153 0
64K 35208 34938 270 0 0 0 0 0 0
16M 2241 2079 162 0 0 0 0 0 0

Addendum to Hugepages in Linux

Oracle has added a new ‘feature’ to OEL 6, called transparent hugepages. This is also in RHEL6 and SLES11 (Suse Linux). Oracle note 1557478.1 says that transparent hugepages should be disabled on servers running Oracle database. According to the note, the ‘feature’ can cause node reboots and performance problems. Transparent hugepages are allocated and deallocated like normal memory, so most likely the performance issues come from excessive CPU usage while managing the hugepages. In order to disable transparent hugepages, do the following:

  1. Verify that transparent hugepages are enabled.

    cat /sys/kernel/mm/transparent_hugepages/enabled

    [always] never

    If the always is bracketed above, hugepages are enabled.

    Alternate method:

    # grep /AnonHugePages /proc/meminfo

    If this returns a value > 0kB, the kernel is uses Transparent HugePages

  2. Preferred method to disable hugepages:

    Add transparent_hugepage=never to /etc/grub.conf, and reboot the server, eg:

    title Oracle Linux Server (2.6.32-300.25.1.el6uek.x86_64)
    root (hd0,0)
    kernel /vmlinuz-2.6.32-300.25.1.el6uek.x86_64 ro root=LABEL=/ transparent_hugepage=never
    initrd /initramfs-2.6.32-300.25.1.el6uek.x86_64.img

  3. Alternate method, disable in rc.local and reboot the server. Add the following section to rc.local and reboot:

    if test -f /sys/kernel/mm/transparent_hugepage/enabled; then

    echo never > /sys/kernel/mm/transparent_hugepage/enabled

    fi

    if test -f /sys/kernel/mm/transparent_hugepage/defrag; then

    echo never > /sys/kernel/mm/transparent_hugepage/defrag

    fi

 

Below is a link to spreadsheets containing common settings for hugepages (Linux) and largepages (AIX).

 

common settings

DBMS_REDEFINITION Revisited

March 3, 2015

It has come to my attention that my last post on dbms_redefinition was really too simplistic to be useful. Therefore, I have set up a new example to show some of the true capabilities of dbms_redefinition. So for this example, we will change column data types instead of just the column sizes. Below are the layouts of our source table (CUSTDATA) and our interim table (CUSTDATA_INT):


SQL> desc custdata
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 CRDATTIM                                  NOT NULL CHAR(26)
 CASE_NUMBER                                        CHAR(10)
 LAST_NAME                                          CHAR(30)
 FIRST_NAME                                         CHAR(30)


SQL> desc custdata_int
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 CRDATTIM                                  NOT NULL TIMESTAMP(6)
 CASE_NUMBER                                        VARCHAR2(30)
 LAST_NAME                                          VARCHAR2(30)
 FIRST_NAME                                         VARCHAR2(30)

Note that the custdata table layout is not particularly good, but it is an example of an actual case, this is a table of legacy data that we are unable to get rid of.  I am working on getting our application team to redesign it to a more sensible layout, but that will take time. As can be seen, there is an extensive amount of data changes to go through.  As in our previous example, first we have to verify that the table can be redefined use DBMS_REDEFINITION:

<pre>
SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  tname=>'CUSTDATA',
  5  options_flag=>DBMS_REDEFINITION.cons_use_rowid);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Now that we have verified that the source table can be redefined using dbms_redefinition, we begin the process:


SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT',
  6  col_mapping=>'to_timestamp(crdattim,'||''''||'yyyy-mm-dd-hh24.mi.ss.ff'||''
''||') crdattim,
  7  rtrim(ltrim(case_number)) case_number,
  8  rtrim(ltrim(first_name)) first_name,
  9  rtrim(ltrim(last_name)) last_name',
 10  options_flag=>DBMS_REDEFINITION.cons_use_rowid);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT');
  6  end;
  7  /

PL/SQL procedure successfully completed.

So, everything is going well.  Let’s move on to the next step:

 
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_num_errors PLS_INTEGER;
  3  begin
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5  uname=>'SCOTT',
  6  orig_table=>'CUSTDATA',
  7  int_table=>'CUSTDATA_INT',
  8  copy_indexes => DBMS_REDEFINITION.cons_orig_params,
  9  copy_triggers => TRUE,
 10  copy_constraints => TRUE,
 11  copy_privileges => TRUE,
 12  ignore_errors => FALSE,
 13  num_errors => l_num_errors);
 14  end;
 15  /
DECLARE
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1015
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1907
ORA-06512: at line 4

At this point, I abort the process and try and track down the problem.  To abort a dbms_Redefinition process, the command is abort_redef_table, like this:


SQL> begin
  2  DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=>'SCOTT',orig_table=>'CUSTDATA',int_table=>'CUSTDATA_INT');
  3  End;
  4  /

PL/SQL procedure successfully completed.

The error message does look like a possible Oracle bug.  Let’s see what the Metalink (My Oracle Support) says: I searched on ORA-01442 DBMS_REDEFINITION, and the first result is Document ID 1116785.1, ORA-1442 Error During Online Redefinition – DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.  Oracle doesn’t actually say it is a bug, and it may not be.  It probably stems from the fact that I created my interim table using a create table as select, then made changes to it.  It looks like I just need to drop the not null constraint on CUSTDATA_INT and start over, so let’s try that. I ran this query:

SQL> select * from dba_constraints where table_name ='CUSTDATA' and owner='SCOTT';
OWNER
-------------------------------------
CONSTRAINT_NAME                C
------------------------------ -
SCOTT
SYS_C0038648                   C

So there is only one constraint, a check constraint.  I will drop that, and see if that fixes the problem.

SQL> alter table custdata_int drop constraint SYS_C0038648;
Table altered.

Sure enough, that fixed it (NOTE: We have to restart at the beginning since we used the ABORT_REDEF_TABLE procedure.)

SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT',
  6  col_mapping=>'to_timestamp(crdattim,'||''''||'yyyy-mm-dd-hh24.mi.ss.ff'||''
''||') crdattim,
  7  rtrim(ltrim(case_number)) case_number,
  8  rtrim(ltrim(first_name)) first_name,
  9  rtrim(ltrim(last_name)) last_name',
 10  options_flag=>DBMS_REDEFINITION.cons_use_rowid);
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  l_num_errors PLS_INTEGER;
  3  begin
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  5  uname=>'SCOTT',
  6  orig_table=>'CUSTDATA',
  7  int_table=>'CUSTDATA_INT',
  8  copy_indexes => DBMS_REDEFINITION.cons_orig_params,
  9  copy_triggers => TRUE,
 10  copy_constraints => TRUE,
 11  copy_privileges => TRUE,
 12  ignore_errors => FALSE,
 13  num_errors => l_num_errors);
 14  end;
 15  /

PL/SQL procedure successfully completed.

So, lets finish the process:

 
SQL> SET SERVEROUTPUT ON
SQL> begin
  2  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
  3  uname=>'SCOTT',
  4  orig_table=>'CUSTDATA',
  5  int_table=>'CUSTDATA_INT');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> alter table custdata drop unused columns;
Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'CUSTDATA', ca
scade=>true);

PL/SQL procedure successfully completed.

SQL>

SQL> desc custdata;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 CRDATTIM                                           TIMESTAMP(6)
 CASE_NUMBER                                        VARCHAR2(30)
 LAST_NAME                                          VARCHAR2(30)
 FIRST_NAME                                         VARCHAR2(30)

Note a couple of steps I left off in my earlier example: drop unused columns and gathering stats.  The unused column was used in the redefinition process because I was using the ROWID option (cons_use_rowid).  Oracle added at the start of the process to keep track of rows, and Oracle automatically marks it as unused once the process is complete, but it is a best practice to drop the column when the process is complete.

DBMS_REDEFINITION Package

December 23, 2014

Very few operations in the Oracle database require a full table lock for any length of time. One of those very few operations is an ‘alter table’ statement. Because of this issue, Oracle has created the DBMS_REDEFINITION package for making online table changes. What follows is an example of how to use the DBMS_REDEFINITION package to expand a column in a table. In our example, we will be modifying TESTTAB1 by expanding a column. Here is the current layout of TESTTAB1:

SQL> desc testtab1
Name Null? Type
—————————————– ——– ——————-
RENCOL13 NOT NULL CHAR(26)
RENCOL12 NOT NULL CHAR(1)
RENCOL11 NOT NULL CHAR(2)
RENCOL10 NOT NULL CHAR(2)
RENCOL9 NOT NULL CHAR(8)
RENCOL8 NOT NULL CHAR(8)
RENCOL7 NOT NULL CHAR(10)
RENCOL6 NOT NULL CHAR(10)
RENCOL5 NOT NULL CHAR(8)
RENCOL4 NOT NULL CHAR(10)
RENCOL3 NOT NULL CHAR(21)
RENCOL2 NOT NULL CHAR(25)
RENCOL1 NOT NULL CHAR(30)

SQL> select index_name from dba_indexes where table_name=’TESTTAB1′;

INDEX_NAME
——————————
TESTTAB1INDEX01
TESTTAB1INDEX02
TESTTAB1INDEX03
TESTTAB1INDEX04
TESTTAB1INDEX05

These are the steps involved when using DBMS_REDEFINITION to change a table layout.

1. Create a table containing the identical layout of the original table. Do not include constraints or indexes, only the column definitions. Make sure that any added or changed columns are included. This will be referred to as the interim table.

Below is the layout of our interim table which will have the changed column definition. Right now it is identical to TESTTAB1, except that it is empty, and has no constraints or indices defined:

SQL> desc testtab2
Name Null? Type
—————————————– ——– ——————

RENCOL13 NOT NULL CHAR(26)
RENCOL12 NOT NULL CHAR(1)
RENCOL11 NOT NULL CHAR(2)
RENCOL10 NOT NULL CHAR(2)
RENCOL9 NOT NULL CHAR(8)
RENCOL8 NOT NULL CHAR(8)
RENCOL7 NOT NULL CHAR(10)
RENCOL6 NOT NULL CHAR(10)
RENCOL5 NOT NULL CHAR(8)
RENCOL4 NOT NULL CHAR(10)
RENCOL3 NOT NULL CHAR(21)
RENCOL2 NOT NULL CHAR(25)
RENCOL1 NOT NULL CHAR(30)

2. Modify the interim table to the intended layout of the original table.

SQL> alter table testtab2 modify (rencol9 char(20));

Table altered.

3. Verify that it is possible to redefine this table online. You will get an error if you cannot:

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(‘OWNER’,’TESTTAB1′);

PL/SQL procedure successfully completed.

4. Next, determine if Oracle will use the rowid or the primary key for this redefine operation. This is the setting for the ‘options_flag’ in the START_REDEF_TABLE procedure. If the table has a primary key defined, use the primary key option (DBMS_REDEFINITION.cons_use_pk). If not, the rowid option is used (DBMS_REDEFINITION.cons_use_rowid).

5. Create the DBMS_REDEFINITION commands that will be required. Generally there are four procedures used after the check with CAN_REDEF_TABLE. Those are START_REDEF_TABLE, COPY_TABLE_DEPENDENTS, SYNC_INTERIM_TABLE, and FINISH_REDEF_TABLE. It is useful to create the commands first so that they can just be copy/pasted into SQLPlus or SQL Developer. Here are the commands that will be used:

SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.START_REDEF_TABLE(
uname=>’OWNER’,
orig_table=>’TESTTAB1′,
int_table=>‘TESTTAB2’,
options_flag=>DBMS_REDEFINITION.cons_use_rowid);
end;
/

SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => ‘OWNER’,
orig_table => ‘TESTTAB1′,
int_table => ‘TESTTAB1′,
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
copy_triggers => TRUE, — Default
copy_constraints => TRUE, — Default
copy_privileges => TRUE, — Default
ignore_errors => FALSE, — Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line(‘l_num_errors=’ || l_num_errors);
END;
/

SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>’OWNER’,
orig_table=>’TESTTAB1′,
int_table=>‘TESTTAB2’);
end;
/

SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>’OWNER’,
orig_table=>’TESTTAB1′,
int_table=>‘TESTTAB2’);
end;
/

6. Start the operation by running the ‘START_REDEF_TABLE’ procedure. The START_REDEF_TABLE procedure initiates the table redefinition:

SQL> SET SERVEROUTPUT ON
SQL> begin
2 DBMS_REDEFINITION.START_REDEF_TABLE(uname=>’OWNER’,
3 orig_table=>’TESTTAB1′,
4 int_table=>‘TESTTAB2’,
5 options_flag=>DBMS_REDEFINITION.cons_use_rowid);
6 end;
7 /

PL/SQL procedure successfully completed.

7. After the start operation completes, run the copy table dependents option. Note that if this operation runs for any significant length of time, the SYNC_INTERIM_TABLE command can be run in a separate session (see step 8) to minimize the time required on the finish command:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => ‘OWNER’,
6 orig_table => ‘TESTTAB1′,
7 int_table => ‘TESTTAB1′,
8 copy_indexes => DBMS_REDEFINITION.cons_orig_params,
9 copy_triggers => TRUE, — Default
10 copy_constraints => TRUE, — Default
11 copy_privileges => TRUE, — Default
12 ignore_errors => FALSE, — Default
13 num_errors => l_num_errors);
14 DBMS_OUTPUT.put_line(‘l_num_errors=’ || l_num_errors);
15 END;
16 /
l_num_errors=0

PL/SQL procedure successfully completed.

8. If the COPY_TABLE_DEPENDENTS procedure runs long (over 30 seconds), run this command periodically to keep the data in sync and minimize time on the final step:
SQL> begin
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>’OWNER’,
3 orig_table=>’TESTTAB1′,
4 int_table=>‘TESTTAB2’);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> /
PL/SQL procedure successfully completed.

SQL> /
PL/SQL procedure successfully completed.

9. After the COPY_TABLE_DEPENDENTS procedure finishes, you can add any additional indices and constraints that may be needed. They are applied to the interim table, and must be done before running the ‘FINISH_REDEF_TABLE’ procedure. Run the ‘SYNC_INTERIM_TABLE’ procedure if needed after adding any constraints or indices.

10. Run the ‘FINISH_REDEF_TABLE’ procedure to finish the procedure. Note that at this point a very brief table lock is required. It may be necessary, though it is unlikely, to kill other user processes that are running. The lock is required as Oracle locks out changes as the data dictionary switches from the old table to the new table.
SQL> begin
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>’OWNER’,
3 orig_table=>’TESTTAB1′,
4 int_table=>‘TESTTAB2’);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> desc testtab1
Name Null? Type
—————————————– ——– ——————-
RENCOL13 NOT NULL CHAR(26)
RENCOL12 NOT NULL CHAR(1)
RENCOL11 NOT NULL CHAR(2)
RENCOL10 NOT NULL CHAR(2)
RENCOL9 NOT NULL CHAR(20)
RENCOL8 NOT NULL CHAR(8)
RENCOL7 NOT NULL CHAR(10)
RENCOL6 NOT NULL CHAR(10)
RENCOL5 NOT NULL CHAR(8)
RENCOL4 NOT NULL CHAR(10)
RENCOL3 NOT NULL CHAR(21)
RENCOL2 NOT NULL CHAR(25)
RENCOL1 NOT NULL CHAR(30)

SQL> select index_name from dba_indexes where table_name=’TESTTAB1′;

INDEX_NAME
——————————
TESTTAB1INDEX01
TESTTAB1INDEX02
TESTTAB1INDEX03
TESTTAB1INDEX04
TESTTAB1INDEX05

11. Drop the interim table that was created.

SQL> drop table testtab1;

Table dropped.

SQL>

12. The process is complete, be sure and verify that everything looks correct.

I have provided one example of the use of the DBMS_REDEFINITION package. It is also commonly used to partition unpartitioned tables, and there are numerous examples of that usage available with a simple google search.

Timestamp Comparison

December 19, 2014

Several weeks ago I determined a need to do some arithmetic based on a timestamp value on the database.  I needed to perform certain actions if the difference in the two timestamps was five minutes or greater.  This was really a fairly trivial problem, until I discovered that for some reason Oracle does not have an easy to use function to get the difference between two timestamp values.

This seemed really strange to me, so I searched thoroughly again determined that really, no such function exists.  Eventually I decided to write one of my own.  The particular problem I had only required resolution down to the minutes, but it seemed likely I might need a higher resolution in the future. Thus, I decided to write the function to allow the user to set the required resolution.  I have not done a lot of work with Oracle time and date functionality, and I ended up with the function below as my first iteration:


create or replace function tsdiff(ts1 in timestamp, ts2 in timestamp, units
in varchar2)
/* units - l=millisecond s=second, m=minute, h=hour, d=day */
return number
is
diffval number;
unitsin char(1);
begin
unitsin:=lower(units);
if unitsin='l'
then
select
extract(day from (ts1-ts2))*24*60*60*1000
+ extract(hour from (ts1-ts2))*60*60*1000
+ extract(minute from (ts1-ts2))*60*1000
+ extract(second from (ts1-ts2))*1000
into diffval
from dual;
elsif unitsin='s'
then
select
extract(day from (ts1-ts2))*24*60*60
+ extract(hour from (ts1-ts2))*60*60
+ extract(minute from (ts1-ts2))*60
+ extract(second from (ts1-ts2))
into diffval
from dual;
elsif unitsin='m'
then
select
extract(day from (ts1-ts2))*24*60
+ extract(hour from (ts1-ts2))*60
+ extract(minute from (ts1-ts2))
+ extract(second from (ts1-ts2))/60
into diffval
from dual;
elsif unitsin='h'
then
select
extract(day from (ts1-ts2))*24
+ extract(hour from (ts1-ts2))
+ extract(minute from (ts1-ts2))/60
+ extract(second from (ts1-ts2))/60/60
into diffval
from dual;
elsif unitsin='d'
then
select
extract(day from (ts1-ts2))
+ extract(hour from (ts1-ts2))/24
+ extract(minute from (ts1-ts2))/24/60
+ extract(second from (ts1-ts2))/24/60/60
into diffval
from dual;
end if;
return diffval;
end;

Note that this is fairly long and repetitive.  It is fairly fast, and got the job done.  However, it did seem too repetitive so I decided to ask around.  I mailed this up to the oracle-l mailing list, asking for input on how to improve it.  Kim Berg Hansen (http://dspsd.blogspot.com/ ) came up with a much neater version:


create or replace function tsdiff(ts1 in timestamp, ts2 in timestamp, units
in varchar2)
/* units - l=millisecond s=second, m=minute, h=hour, d=day */
return number
is
diff interval day(9) to second(9) := ts1 - ts2;
begin
return (
extract(day from diff)*24*60*60
+ extract(hour from diff)*60*60
+ extract(minute from diff)*60
+ extract(second from diff)
) / case (lower(units))
when 'l' then 1/1000
when 's' then 1
when 'm' then 60
when 'h' then 60*60
when 'd' then 24*60*60
else null
end;
end;
/

This version is very nice, neat, and short.  And that is what I decided to go with.  I made it generally available on our databases.  Calling it is very simple:

select tsdiff(ts1,ts2,units) from dual;

SQL> select tsdiff(systimestamp+1/24, systimestamp,’m’) from dual;
TSDIFF(SYSTIMESTAMP+1/24,SYSTIMESTAMP,’M’)
——————————————
59.9891503
SQL>

I thought this was a very nice solution, and while I admit it is short and easy to write yourself, it seems to me that this function is one that oracle should supply themselves.

Testing the ASM Filter Driver (AFD)

November 25, 2014

Testing the Oracle ASM Filter Driver (AFD)
In my previous post I converted from standard Oracle ASM to the ASM Filter Driver (AFD). According to Oracle, the ASM Filter Driver will prevent non-oracle software from updating the AFD devices. Let’s test that out.
First we try this as the owner of the GI installation grid:
[grid@vmrh65node1 ~]$ dd if=/dev/zero of=/dev/sdf1
dd: opening `/dev/sdf1′: Permission denied

Ok, that’s pretty straight forward. Grid does not have permission to modify the device. Let’s see what happens when we connect as root and do it:

[grid@vmrh65node1 ~]$ asmcmd afd_lsdsk
——————————————————————————–
Label Filtering Path
================================================================================
OCR1 ENABLED /dev/sdc1
OCR2 ENABLED /dev/sdd1
OCR3 ENABLED /dev/sde1
DATA ENABLED /dev/sdf1
RECO ENABLED /dev/sdg1

[root@vmrh65node1 ~]# cp oracleasmlib-2.0.4-1.el6.x86_64.rpm /dev/oracleafd/disks/DATA
cp: overwrite `/dev/oracleafd/disks/DATA’? y

Hmm. That’s not so good. What does the OS say?

[root@vmrh65node1 ~]# ls -la /dev/oracleafd/disks
total 32
drwxrwx— 2 grid dba 140 Nov 24 20:41 .
drwxrwx— 3 grid dba 80 Nov 24 20:41 ..
-rw-r–r– 1 root root 13300 Nov 25 12:52 DATA
-rw-r–r– 1 root root 10 Nov 24 20:41 OCR1
-rw-r–r– 1 root root 10 Nov 24 20:41 OCR2
-rw-r–r– 1 root root 10 Nov 24 20:41 OCR3
-rw-r–r– 1 root root 10 Nov 24 20:41 RECO

That doesnt look good. What does Oracle say?

[grid@vmrh65node1 asm]$ asmcmd afd_lsdsk
——————————————————————————–
Label Filtering Path
================================================================================
OCR1 ENABLED /dev/sdc1
OCR2 ENABLED /dev/sdd1
OCR3 ENABLED /dev/sde1
DATA ENABLED
RECO ENABLED /dev/sdg1

Ok. That’s not promising. But maybe there is something going on that I don’t know about. Is the database still up?

[root@vmrh65node1 ~]# ps -ef | grep pmon
grid 3288 1 0 Nov24 ? 00:00:06 asm_pmon_+ASM1
oracle 3825 1 0 Nov24 ? 00:00:13 ora_pmon_o11rhawd1
root 8379 7214 0 12:52 pts/0 00:00:00 grep pmon

Well, that’s interesting. It does look like the database is still up. Lets try a log in.

[root@vmrh65node1 ~]# su – oracle
[oracle@vmrh65node1 ~]$ . oraenv
ORACLE_SID = [oracle] ? o11rhawd1
The Oracle base has been set to /u01/app/oracle
[oracle@vmrh65node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 25 12:53:14 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 939525000 bytes
Database Buffers 3321888768 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> show parameter background

NAME TYPE VALUE
———————————— ———– ——————————
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/o11
rhawd/o11rhawd1/trace
SQL> exit

That looks very good. As we know, Oracle won’t start if it has problems with the system tablespace, and on this instance the system tablespace is in the DATA disk group.

Let’s reboot and see what Oracle says.

[root@vmrh65node1 ~]# reboot

Broadcast message from oracle@vmrh65node1.awddev.dstcorp.net
(/dev/pts/0) at 18:26 …

The system is going down for reboot NOW!
[root@vmrh65node1 ~]#
login as: oracle
oracle@10.193.204.10’s password:
Last login: Mon Nov 24 20:45:20 2014 from 10.201.232.32
[oracle@vmrh65node1 ~]$ ls
adrci_cleanup.sh backup dgdemo02pmmkvl_1_1.bak grid initdgdemo.ora mapper_behind.sh mapper_checks.sh oradiag_oracle patch working1.sh
[oracle@vmrh65node1 ~]$ ps -ef | grep pmon
grid 2992 1 0 18:28 ? 00:00:00 asm_pmon_+ASM1
oracle 3453 3410 0 18:28 pts/0 00:00:00 grep pmon
[oracle@vmrh65node1 ~]$ ls
adrci_cleanup.sh backup dgdemo02pmmkvl_1_1.bak grid initdgdemo.ora mapper_behind.sh mapper_checks.sh oradiag_oracle patch working1.sh
[oracle@vmrh65node1 ~]$ ps -ef | grep pmon
grid 2992 1 0 18:28 ? 00:00:00 asm_pmon_+ASM1
oracle 3484 3410 0 18:28 pts/0 00:00:00 grep pmon
[oracle@vmrh65node1 ~]$ ps -ef | grep pmon
grid 2992 1 0 18:28 ? 00:00:00 asm_pmon_+ASM1
oracle 3486 3410 0 18:28 pts/0 00:00:00 grep pmon
[oracle@vmrh65node1 ~]$ ps -ef | grep pmon
grid 2992 1 0 18:28 ? 00:00:00 asm_pmon_+ASM1
oracle 3488 3410 0 18:28 pts/0 00:00:00 grep pmon
[oracle@vmrh65node1 ~]$ ps -ef | grep pmon
grid 2992 1 0 18:28 ? 00:00:00 asm_pmon_+ASM1
oracle 3502 3410 0 18:28 pts/0 00:00:00 grep pmon
[oracle@vmrh65node1 diag]$ ps -ef | grep pmon
grid 2992 1 0 18:28 ? 00:00:00 asm_pmon_+ASM1
oracle 3504 1 0 18:29 ? 00:00:00 ora_pmon_o11rhawd1
oracle 3618 3410 0 18:29 pts/0 00:00:00 grep pmon

Ok, so the database came back up. What does the file system look like now?

[oracle@vmrh65node1 diag]$ ls -la /dev/oracleafd/disks
total 20
drwxrwx— 2 grid dba 140 Nov 25 18:27 .
drwxrwx— 3 grid dba 80 Nov 25 18:27 ..
-rw-r–r– 1 root root 10 Nov 25 18:27 DATA
-rw-r–r– 1 root root 10 Nov 25 18:27 OCR1
-rw-r–r– 1 root root 10 Nov 25 18:27 OCR2
-rw-r–r– 1 root root 10 Nov 25 18:27 OCR3
-rw-r–r– 1 root root 10 Nov 25 18:27 RECO

[oracle@vmrh65node1 diag]$ su – grid
Password:
[grid@vmrh65node1 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/oracle

[grid@vmrh65node1 ~]$ asmcmd afd_lsdsk
——————————————————————————–
Label Filtering Path
================================================================================
OCR1 ENABLED /dev/sdc1
OCR2 ENABLED /dev/sdd1
OCR3 ENABLED /dev/sde1
DATA ENABLED /dev/sdf1
RECO ENABLED /dev/sdg1

Ok, so now everything is back the way it was. AFD is living up to its claim of protecting the data files. This is actually very nice, I have definitely seen times when the ASM disks were accidently overwritten by inexperienced users with too much access.

UPDATE: I rebooted both cluster nodes again, and when I did the entire /dev/sdf1 partition was missing, which meant my DATA diskgroup was missing. While it is possible that this is merely coincidence, I consider it very unlikely. My working theory is that the data on the DATA diskgroup was somehow protected until the server was bounced, at which point the protection was lost and the write to the partition happened. This means that writes to the diskgroup are not protected from the super user by the ASM Filter Driver.

UPDATE 2:  I was able to get the data back in the data diskgroup just by partitioning the device again.  Perhaps ASMFD allowed the device header to get overwritten without overwriting the data?  Im not sure, but I know for a fact that without some protection I should not have been able to restore the data that easily.

Enabling ASM Filter Driver

November 25, 2014

As promised, this entry documents my experience in enabling the ASM Filter Driver (ASMFD). The ASMFD is the replace for ASM that according to Oracle prevents non-oracle software from making modifications to Oracle database files. Upgrading from standard ASM was surprisingly difficult, and required much more time than expected. Most of the problems arose from the difficulty in ensuring that all of the original ASM software was disabled during the process of moving to ASMFD. Also causing issues was the requirement to periodically run commands as root or grid, depending on the command. It is very important to run the commands as the correct user id.

Note that the example here is the more complex ASM case of the OCR and Voting Disks being stored in ASM.

So, here is the process.
1. Install the patch for Oracle bug 19035573. This fixes several issues with the upgrade process. Note, the readme for this patch installation is incomplete. Review the Oracle notes for patch installation using opatch prior to installing the patch. I required several tries to get this to work right. In particular, you must run the unlock perl script.
in the Grid Infrastructure home prior to patching, and the post patch script when complete.

2. Identify the OS path of all of the Oracle ASM disks. Oracle Support supplied me with this script which extracted the path for my Oracle disks. Note that this example was on a VM, and I was using shared VMDK’s for this process. Also note that ASM was already properly configured, and the cluster was up and running when this process was started (root user).

/etc/init.d/oracleasm querydisk -d `/etc/init.d/oracleasm listdisks -d` | 
cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' | 
while read v_asmdisk v_minor v_major 
do 
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'` 
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor, $v_major]" 
done

Output from above:
ASM disk DATA based on /dev/sdf1 [8, 81]
ASM disk OCR1 based on /dev/sdc1 [8, 33]
ASM disk OCR2 based on /dev/sdd1 [8, 49]
ASM disk OCR3 based on /dev/sde1 [8, 65]
ASM disk RECO based on /dev/sdg1 [8, 97]

3. Add AFD:* to the asm_diskstring so that the asm_diskstring contains both the old and new path NOTE: This is run as the GI home owner (GRID):

$ORACLE_HOME/bin/asmcmd dsset ‘ORCL:*’,’AFD:*’

4. Stop the cluster (run as root):
crsctl stop crs –f (both nodes)

5. Stop asm (as root):
/etc/init.d/oracleasm stop
/etc/init.d/oracleasm disable
Move the file /opt/oracle/extapi/64/asm/orcl/1/libasm.so to a new location so it is out of your path.

6. Stop ACFS (as root):
$ORACLE_HOME/bin/acfsload stop

7. At this point, if not already installed, oracle 12.1 GI should be installed.

8. On both nodes, configure the afd driver:
$ORACLE_HOME/bin/asmcmd afd_configure

9. Label the ASM disks for AFD (as root).
afd_label OCR1 /dev/sdc1 –migrate
afd_label OCR2 /dev/sdd1 –migrate
afd_label OCR3 /dev/sde1 –migrate
afd_label DATA /dev/sdf1 –migrate
afd_label RECO /dev/sdg1 –migrate

10. Scan for the new disks (as GRID):
$ORACLE_HOME/bin/asmcmd dsget
$ORACLE_HOME/bin/asmcmd afd_scan

11. Check the directory /dev/oracleafd/disks/* and verify that the ASMFD disks are listed there.

12. Start the cluster. If possible, boot the server to verify that everything starts properly after boot.

A couple of notes on this process: It took me several days to get this right, and I had to open an SR with Oracle support along the way. Evidently it is common for the ASM drivers to remain loaded, as well as the ACFS drivers. When you get error messages about ACFS or ASM being loaded, you may need to take additional steps to make sure that they are unloaded.
Also, the instructions from Oracle support said to set the afd diskstring to /dev/sd* as the last step before the scan. This may or may not be necessary, I did not run the command, and the upgrade process ran properly. I suspect when using the ASM_DISKSTRING as ORCL:* and the AFD_DISKSTRING as AFD:* this is not needed.


Follow

Get every new post delivered to your Inbox.