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.

Oracle Trace/Log File Cleanup using ADRCI

October 16, 2014

Due to time constraints, I have not completed my intended entry on the ASM Filter Driver (ASMFD). In the interim, I have written a short post on trace/log file clean up using the latest Oracle utility, ADRCI.

I recently posted a blog about trace/log file clean up in Oracle. The methodology I gave would work for all Oracle versions. However, starting with Oracle 11gR1, Oracle has released a program called adrci that can be used to clean up the log and trace files much more easily. The usage is similar to the OS supplied log file rotation utility logrotate, except it also cleans up trace files. Adrci does not however clean up audit files or the listener log files.

ADRCI stands for the Automatic Diagnostic Repository Command Interpreter. As the name implies, there is evidently much more to this utility than just the ability to clean up old trace files and trim log files. That will be the subject of another post. This post discusses cleaning up log and trace files, which is a very common problem on Oracle servers.

Starting in Oracle 11gR1, the Oracle approved method of removing trace files and cleaning database alert logs is to use adrci, but OS commands must still be used for the listener logs and the audit files. I wrote a shell script using a combination of these methods for cleaning up 11gR2 environments.

The assumptions: the code assumes that the instances are up and running, and that there are entries for the instances in the oratab. In a clustered environment, it is expected that the instance (1, 2, etc) have an entry in the oratab as well as the database name. eg, if there are two cluster nodes for a database named orcl, in node 1 there would be entries for both orcl and orcl1 in the oratab on node one, and in the oratab on node two we would see orcl and orcl2.

Next, we write the script. The first segment is basically bookkeeping, but note that we have to translate the number of days supplied in the first command line argument to minutes in the script, as adrci will expect minutes as the argument on the retention time. The second command line argument is the retention time for the listener log files. The script could easily be modified to accept additional retention requirements based on your specific needs:

# Description
# Shell script to clean up oracle diagnostic files using ADRCI, and to remove log data.
# Allows 2 arguments, $1 – retention time for trace and audit data, $2 – retention time for listener
# log files. Could be enhanced for multiple retention periods. 
###
#set -x
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
export PATH
if [[ -n "$1" ]]; then
 if [ $1 -ne 0 -o $1 -eq 0 2>/dev/null ]
 then
   if [[ $1 -lt 0 ]]; then
     echo invalid input
     exit 1
   else
     days=$1
     minutes=$((1440 * $days))
    echo days=$days
     echo minutes=$minutes
   fi
 fi
else
 echo days=7
 days=7
 minutes=$((1440 * $days))
 echo days=$days
 echo minutes=$minutes
fi
if [[ -n "$2" ]]; then
 if [ $2 -ne 0 -o $2 -eq 0 2>/dev/null ]
 then
   if [[ $2 -lt 0 ]]; then
     echo invalid input
     exit 1
   else
     log_days=$1
     echo log_days=$days
   fi
 fi
else
 echo log_days=30
 log_days=30
 echo log_days=$log_days
fi
SERVER=`hostname -s`
FDATE=`date +%d_%m_%y`
# Check user is oracle
USERID=`/usr/bin/id -u -nr`
if [ $? -ne 0 ]
then
       echo "ERROR: unable to determine uid"
       exit 99
fi
if [ "${USERID}" != "oracle" ]
then
       echo "ERROR: This script must be run as oracle"
       exit 98
fi
echo "INFO: Purge started at `date`"

Next, we use the ps command to identify the running oracle instances and loop through them. We grep for the smon process for each running instance, and remove everything up to and including the underscore in the process name to get the database sid. After extracting the sid, we set the environment for that ORACLE_SID using oraenv:

for ORACLE_SID in `ps -e -o "cmd" | grep smon|grep -v grep| awk -F "_" '{print$3}'`
do
   # uncomment 2 lines below if RAC environment and individual sids are not in oratab
   #   SID=`echo $ORACLE_SID | sed -e 's/1//g'`
   #   ORACLE_HOME=`cat /etc/oratab|grep ^$SID:| head -n 1 | cut -f2 -d':'`
 ORAENV_ASK=NO
 export ORAENV_ASK
 export ORACLE_SID
 echo $ORACLE_SID
 . /usr/local/bin/oraenv
 echo SID=$ORACLE_SID
 echo "INFO: purge started at `date`"

Next, we identify the location of the audit files and clean them up. This section is identical to my previous clean up script as adrci will not clean up audit files. Note, it is possible that your environment requires that you move these elsewhere for auditing purposes rather than remove them. These are not the standard Oracle database auditing, these are the records of everything done on the instance using the sys privilege:

 
AUDIT_DEST=`$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<!
 set feedback off heading off verify off
 select value from v\\$parameter where name='audit_file_dest';
!`
 finaud=`echo $AUDIT_DEST | sed -e 's|\?|'"$ORACLE_HOME"'|'`
 /usr/bin/find $finaud -name \*.aud -mtime +$days
 /usr/bin/find $finaud -name *.aud -mtime +$days | xargs -i ksh -c "echo deleting {}; rm {}"
 echo $finaud
 # Purge ADR contents

Next we run the adrci command to delete the diagnostic information. In the current script, the retention is the same for all file types. It would be fairly easy to modify the script to accept multiple retention levels based on the type of data.

 echo "INFO: adrci purge started at `date`"
 adrci exec="show homes"|grep -v : | while read file_line
 do
   echo "INFO: adrci purging diagnostic destination" $file_line
   echo "INFO: purging ALERT older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type ALERT"
   echo "INFO: purging INCIDENT older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type INCIDENT"
   echo "INFO: purging TRACE older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type TRACE"
   echo "INFO: purging CDUMP older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type CDUMP"
   echo "INFO: purging HM older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type HM"
   echo ""
  echo ""
 done
done
echo
echo "INFO: adrci purge finished at `date`"

Finally, we trim the listener log since this not done properly through adrci:

# for whatever reason, adrci doesn't like to remove the listener trace
# log, so we need to get it manually
for listener_log in `find $ORACLE_BASE/diag/tnslsnr -name "listener.log"`
do
 listener_file=`echo "$listener_log" | awk -Ftrace/ '{print $2}'`
 echo $listener_log
 echo $listener_file
 fname="${listener_log}_`date '+%Y%m%d'`.gz"
 fname1="${listener_log}_`date '+%Y%m%d'`"
 echo $fname
 if [ -e $fname ]
 then
   echo "Already cleared $listener_log today"
 else
   cp $listener_log $fname1
   gzip $fname1
   /usr/bin/find $ORACLE_BASE/diag/tnslsnr -name ${listener_file}*.gz -mtime +$log_days | xargs -i ksh -c "echo deleting {}; rm {}"
   echo > $listener_log
 fi
done
echo "SUCC: Purge completed successfully at `date`"
exit 0

Thus we have purged oracle log, trace, and audit files. Below is the script unedited with the commentary removed:


# Description
# Shell script to clean up oracle diagnostic files using ADRCI, and to remove log data.
# Allows 2 arguments, $1 – retention time for trace and audit data, $2 – retention time for listener
# log files. Could be enhanced for multiple retention periods.
###
#set -x
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
export PATH
if [[ -n "$1" ]]; then
 if [ $1 -ne 0 -o $1 -eq 0 2>/dev/null ]
 then
   if [[ $1 -lt 0 ]]; then
     echo invalid input
     exit 1
   else
     days=$1
     minutes=$((1440 * $days))
     echo days=$days
     echo minutes=$minutes
   fi
 fi
else
 echo days=7
 days=7
 minutes=$((1440 * $days))
 echo days=$days
 echo minutes=$minutes
fi

if [[ -n "$2" ]]; then
 if [ $2 -ne 0 -o $2 -eq 0 2>/dev/null ]
 then
   if [[ $2 -lt 0 ]]; then
     echo invalid input
     exit 1
   else
     log_days=$1
     echo log_days=$days
   fi
 fi
else
 echo log_days=30
 log_days=30
 echo log_days=$log_days
fi
SERVER=`hostname -s`
FDATE=`date +%d_%m_%y`

# Check user is oracle
USERID=`/usr/bin/id -u -nr`
if [ $? -ne 0 ]
then
       echo "ERROR: unable to determine uid"
       exit 99
fi
if [ "${USERID}" != "oracle" ]
then
       echo "ERROR: This script must be run as oracle"
       exit 98
fi
echo "INFO: Purge started at `date`"
# Establish some oracle enviroment
for ORACLE_SID in `ps -e -o "cmd" | grep smon|grep -v grep| awk -F "_" '{print$3}'`
do
   # uncomment 2 lines below if RAC environment and individual sids are not in oratab
   #   SID=`echo $ORACLE_SID | sed -e 's/1//g'`
   #   ORACLE_HOME=`cat /etc/oratab|grep ^$SID:| head -n 1 | cut -f2 -d':'`
 ORAENV_ASK=NO
 export ORAENV_ASK
 export ORACLE_SID
 echo $ORACLE_SID
 . /usr/local/bin/oraenv
 echo SID=$ORACLE_SID
 AUDIT_DEST=`$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<!
 set feedback off heading off verify off
 select value from v\\$parameter where name='audit_file_dest';
!`
 finaud=`echo $AUDIT_DEST | sed -e 's|\?|'"$ORACLE_HOME"'|'`
 /usr/bin/find $finaud -name \*.aud -mtime +$days
 /usr/bin/find $finaud -name *.aud -mtime +$days | xargs -i ksh -c "echo deleting {}; rm {}"

 echo $finaud
 # Purge ADR contents
 echo "INFO: adrci purge started at `date`"
 adrci exec="show homes"|grep -v : | while read file_line
 do
   echo "INFO: adrci purging diagnostic destination" $file_line
   echo "INFO: purging ALERT older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type ALERT"
   echo "INFO: purging INCIDENT older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type INCIDENT"
  echo "INFO: purging TRACE older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type TRACE"
   echo "INFO: purging CDUMP older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type CDUMP"
   echo "INFO: purging HM older than $1 days."
   adrci exec="set homepath $file_line;purge -age $minutes -type HM"
   echo ""
   echo ""
 done
done
echo
echo "INFO: adrci purge finished at `date`"
# All completed
# for whatever reason, adrci doesn't like to remove the listener trace
# log, so we need to get it manually
for listener_log in `find $ORACLE_BASE/diag/tnslsnr -name "listener.log"`
do
 listener_file=`echo "$listener_log" | awk -Ftrace/ '{print $2}'`
 echo $listener_log
 echo $listener_file
 fname="${listener_log}_`date '+%Y%m%d'`.gz"
 fname1="${listener_log}_`date '+%Y%m%d'`"
 echo $fname
 if [ -e $fname ]
 then
   echo "Already cleared $listener_log today"
 else
   cp $listener_log $fname1
   gzip $fname1
   /usr/bin/find $ORACLE_BASE/diag/tnslsnr -name ${listener_file}*.gz -mtime +$log_days | xargs -i ksh -c "echo deleting {}; rm {}"
   echo > $listener_log
 fi
done
echo "SUCC: Purge completed successfully at `date`"
exit 0

ASMLIB in OEL 7

August 18, 2014

In my previous port I wrote about using UDEV rules for device name persistence in an Oracle RAC environment. In this post, I will discuss using ASMLIB to the same purpose. I had intended to also talk about using kmod-oracleasm, which is the equivalent pack to ASMLIB, but unique to RHEL. However, due to licensing issues I was not able to create a set of RHEL VMware workstations to this purpose

AMLIB is the Oracle preferred solution to device name persistence. ASMLIB will write an ASM header to each physical device, as opposed to UDEV, which uses the existing SCSI unique ID to map the device name. In my opinion, ASMLIB is superior to UDEV for device name persistence.

In this post, I will also include the steps to get the iSCSI devices mapped into the OEL operating system. They were not included in my previous posts for the purpose of brevity. These steps are identical to those required in the previous post on UDEV rules. These are primarily included for those who wish to set up their own home cluster. For a true commercial level cluster, use the licensed version of the iscsi target, or other commercial software. For instructions on how to set up the ISCSI target within Openfiler, there are several good sets of instructions available, one is here: http://techhead.co/how-to-configure-openfiler-v23-iscsi-storage-for-use-with-vmware-esx/.

The first step is to turn on the iSCSI initiator on the operating system. The initiator appears to be part of the default kernel installation. The commands to activate it are here:

service iscsid start

chkconfig iscsid on

 

In OEL 7, the commands will be translated to systemctl, so the commands you see will be this:

[root@oel7641 ~]# service iscsid start

Redirecting to /bin/systemctl start iscsid.service

[root@oel7641 ~]# chkconfig iscsid on

Note: Forwarding request to ‘systemctl enable iscsid.service’.

[root@oel7641 ~]#

 

In order to map the openfiler iSCSI devices to the Linux operating system, we use the sendtargets option of the iscsiadm command, then we use the login option to login to the devices. After this is done, the iscsi devices will appear as standard Linux scsi devices:

[root@oel7641-localdomain ~]# iscsiadm -m discovery -t sendtargets -p 192.168.213.100:3260

192.168.213.100:3260,1 iqn.2006-01.com.openfiler:tsn.70d7d9301f39

[root@oel7641-localdomain ~]# iscsiadm -m node -T iqn.2006-01.com.openfiler:tsn.70d7d9301f39 -p 192.168.213.100:3260 –login

Logging in to [iface: default, target: iqn.2006-01.com.openfiler:tsn.70d7d9301f39, portal: 192.168.213.100,3260] (multiple)

Login to [iface: default, target: iqn.2006-01.com.openfiler:tsn.70d7d9301f39, portal: 192.168.213.100,3260] successful.

 

In the output below, devices sdb through sdg are the iscsi devices:

 

[root@oel7641 ~]# fdisk -l

 

Disk /dev/sda: 161.1 GB, 161061273600 bytes, 314572800 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x0003b5aa

 

Device Boot Start End Blocks Id System

/dev/sda1 * 2048 1026047 512000 83 Linux

/dev/sda2 1026048 314572799 156773376 8e Linux LVM

 

Disk /dev/mapper/ol_oel764-swap: 16.9 GB, 16852713472 bytes, 32915456 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

 

Disk /dev/mapper/ol_oel764-root: 87.2 GB, 87241523200 bytes, 170393600 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

 

Disk /dev/mapper/ol_oel764-home: 56.4 GB, 56438554624 bytes, 110231552 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

 

Disk /dev/sdb: 8187 MB, 8187281408 bytes, 15990784 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x2adfadeb

 

Disk /dev/sdc: 8187 MB, 8187281408 bytes, 15990784 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x536c247a

 

Disk /dev/sdd: 8187 MB, 8187281408 bytes, 15990784 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0xcf43d9a8

 

Disk /dev/sde: 40.9 GB, 40936407040 bytes, 79953920 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x253975b8

 

Disk /dev/sdf: 40.9 GB, 40936407040 bytes, 79953920 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0xbdab400b

 

Disk /dev/sdg: 40.9 GB, 40936407040 bytes, 79953920 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x85ddc3a3

 

At this point, we want to make sure that the devices are also available on the second node. The simplest method to do this is to copy the subdirectories under /var/lib/iscsi to the second and additional nodes in the cluster, then run the commands to start iscsi on those nodes.

scp –r /var/lib/iscsi oel7642:/var/lib/scsi

(log into oel7642)

[root@oel7642 ~]# service iscsid start

Redirecting to /bin/systemctl start iscsid.service

[root@oel7642 ~]# chkconfig iscsid on

Note: Forwarding request to ‘systemctl enable iscsid.service’.

[root@oel7642 ~]#exit

 

Next, we need to partition the iscsi devices. Also, since these are VMware vmdk’s, we need to make sure that the partitions are block aligned for best performance. Here is a typical fdisk conversation to accomplish that purpose. Note that we only partition and run oracleasm on a single node. After the devices are marked for ASM, we just scan for them on the second node and additional nodes:

 

[root@oel7641 ~]# fdisk /dev/sdb

Welcome to fdisk (util-linux 2.23.2).

 

Changes will remain in memory only, until you decide to write them.

Be careful before using the write command.

 

Device does not contain a recognized partition table

Building a new DOS disklabel with disk identifier 0x2adfadeb.

 

Command (m for help): n

Partition type:

p primary (0 primary, 0 extended, 4 free)

e extended

Select (default p): p

Partition number (1-4, default 1): 1

First sector (2048-15990783, default 2048):

Using default value 2048

Last sector, +sectors or +size{K,M,G} (2048-15990783, default 15990783):

Using default value 15990783

Partition 1 of type Linux and of size 7.6 GiB is set

 

Command (m for help): t

Selected partition 1

Hex code (type L to list all codes): fb

Changed type of partition ‘Linux’ to ‘VMware VMFS’

 

Command (m for help): x

 

Expert command (m for help): b

Selected partition 1

New beginning of data (1-15990783, default 2048): 2048

 

Expert command (m for help): w

The partition table has been altered!

 

Calling ioctl() to re-read partition table.

Syncing disks.

 

Because of the number of devices, I created a very simple script to run the fdisk conversation on the remainder of the devices, I called it doit.sh. Don’t complain about the naming. This script assumes we want to use the entire device for ASM storage.

Here is the script:

[root@oel7641 ~]# cat doit.sh

#!/bin/sh
fdisk $1 <<EOF
n
p
1

 
 t
fb
x
b
2048
w
exit
EOF

 

The script is called with the script name followed by the device name:

[root@oel7641 ~]# ./doit.sh /dev/sdc

[root@oel7641 ~]# ./doit.sh /dev/sdd

[root@oel7641 ~]# ./doit.sh /dev/sde

[root@oel7641 ~]# ./doit.sh /dev/sdf

[root@oel7641 ~]# ./doit.sh /dev/sdg

 

At that point, all the device are partitioned and it is time to create the ASM devices.

 

So next, we need to install ASM. It is not installed by default (currently) in the OEL7 Kernel. We use yum to install ORACLEASM, two packages are required ORACLEASM and ORACLEASM-SUPPORT:

 

[root@oel7641-localdomain ~]# yum install oracleasm

Loaded plugins: langpacks, rhnplugin

This system is receiving updates from ULN.

ol7_x86_64_Dtrace_userspace | 1.2 kB 00:00:00

ol7_x86_64_UEKR3 | 1.2 kB 00:00:00

ol7_x86_64_addons | 1.2 kB 00:00:00

ol7_x86_64_latest | 1.4 kB 00:00:00

ol7_x86_64_latest/updateinfo | 33 kB 00:00:00

ol7_x86_64_latest/primary | 4.5 MB 00:00:02

ol7_x86_64_latest 6631/6631

ol7_x86_64_optional_latest | 1.2 kB 00:00:00

ol7_x86_64_optional_latest/updateinfo | 32 kB 00:00:00

ol7_x86_64_optional_latest/primary | 1.4 MB 00:00:00

ol7_x86_64_optional_latest 5022/5022

Resolving Dependencies

–> Running transaction check

—> Package kernel-uek.x86_64 0:3.8.13-35.3.4.el7uek will be installed

–> Processing Dependency: kernel-firmware = 3.8.13-35.3.4.el7uek for package: kernel-uek-3.8.13-35.3.4.el7uek.x86_64

–> Running transaction check

—> Package kernel-uek-firmware.noarch 0:3.8.13-35.3.4.el7uek will be installed

–> Finished Dependency Resolution

 

Dependencies Resolved

 

================================================================================================================================================================

Package Arch Version Repository Size

================================================================================================================================================================

Installing:

kernel-uek x86_64 3.8.13-35.3.4.el7uek ol7_x86_64_UEKR3 31 M

kernel-uek-firmware noarch 3.8.13-35.3.4.el7uek ol7_x86_64_UEKR3 1.8 M

 

Transaction Summary

================================================================================================================================================================

Install 2 Packages

 

Total size: 33 M

Total download size: 1.8 M

Installed size: 112 M

Is this ok [y/d/N]: y

Downloading packages:

No Presto metadata available for ol7_x86_64_UEKR3

kernel-uek-firmware-3.8.13-35.3.4.el7uek.noarch.rpm | 1.8 MB 00:00:01

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : kernel-uek-firmware-3.8.13-35.3.4.el7uek.noarch 1/2

Installing : kernel-uek-3.8.13-35.3.4.el7uek.x86_64 2/2

Verifying : kernel-uek-firmware-3.8.13-35.3.4.el7uek.noarch 1/2

Verifying : kernel-uek-3.8.13-35.3.4.el7uek.x86_64 2/2

 

Installed:

kernel-uek.x86_64 0:3.8.13-35.3.4.el7uek kernel-uek-firmware.noarch 0:3.8.13-35.3.4.el7uek

 

Complete!

 

[root@oel7641-localdomain ~]# yum install oracleasm-support

Loaded plugins: langpacks, rhnplugin

This system is receiving updates from ULN.

Resolving Dependencies

–> Running transaction check

—> Package oracleasm-support.x86_64 0:2.1.8-3.el7 will be installed

–> Finished Dependency Resolution

 

Dependencies Resolved

 

================================================================================================================================================================

Package Arch Version Repository Size

================================================================================================================================================================

Installing:

oracleasm-support x86_64 2.1.8-3.el7 ol7_x86_64_latest 79 k

 

Transaction Summary

================================================================================================================================================================

Install 1 Package

 

Total download size: 79 k

Installed size: 242 k

Is this ok [y/d/N]: y

Downloading packages:

oracleasm-support-2.1.8-3.el7.x86_64.rpm | 79 kB 00:00:00

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : oracleasm-support-2.1.8-3.el7.x86_64 1/1

Note: Forwarding request to ‘systemctl enable oracleasm.service’.

ln -s ‘/usr/lib/systemd/system/oracleasm.service’ ‘/etc/systemd/system/multi-user.target.wants/oracleasm.service’

Verifying : oracleasm-support-2.1.8-3.el7.x86_64 1/1

 

Installed:

oracleasm-support.x86_64 0:2.1.8-3.el7

 

Complete!

 

 

Once the ASMLIB is installed, it must be configured. This just amounts to telling Linux the owner of the ASM disks, and the location of the disks, along with some minor options.

 

[root@oel7641-localdomain ~]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

 

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets (‘[]’). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

 

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Scan for Oracle ASM disks on boot (y/n) [y]:

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [ OK ]

Scanning the system for Oracle ASMLib disks: [ OK ]

 

With ASM configured, we mark the disks for ASM. Note that unlike UDEV this puts an actual label on the disk, it does not use the existing UUID to identify the disk.

 

[root@oel7641 ~]# /etc/init.d/oracleasm createdisk asmdisk1 /dev/sdb1

Marking disk “asmdisk1″ as an ASM disk: [ OK ]

[root@oel7641 ~]# /etc/init.d/oracleasm createdisk asmdisk2 /dev/sdc1

Marking “asmdisk2″ as an ASM disk:

[root@oel7641 ~]# /etc/init.d/oracleasm createdisk asmdisk3 /dev/sdd1

Marking disk “asmdisk3″ as an ASM disk: [ OK ]

[root@oel7641 ~]# /etc/init.d/oracleasm createdisk asmdisk4 /dev/sde1

Marking disk “asmdisk4″ as an ASM disk: [ OK ]

[root@oel7641 ~]# /etc/init.d/oracleasm createdisk asmdisk5 /dev/sdf1

Marking disk “asmdisk5″ as an ASM disk: [ OK ]

[root@oel7641 ~]# /etc/init.d/oracleasm createdisk asmdisk6 /dev/sdg1

Marking disk “asmdisk6″ as an ASM disk: [ OK ]

[root@oel7641 ~]# /etc/init.d/oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASMDISK4

ASMDISK5

ASMDISK6

 

Next, we make sure that the other cluster nodes can see the ASM disks. We do not need to reboot the second node of the cluster, nor do we run the createdisk command on the other nodes in the cluster, we scan for the storage devices on the other nodes, to make oracleasm aware of the disks and we are done:

 

[root@oel7642 ~]# /etc/init.d/oracleasm scandisks

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@oel7642 ~]# /etc/init.d/oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASMDISK4

ASMDISK5

ASMDISK6

[root@oel7642 ~]#

 

If you want to verify that the disks appear properly reboot both servers and check.

 

At this point, the Oracle Grid Infrastructure for a cluster can be installed. The ASM_DISKSTRING for the installation will be /dev/oracleasm/disks/*.

 

According to Oracle documentation, starting with Oracle version 12, there is a piece of software called the ASM Filter Driver (ASMFD) that prevents non-oracle software from writing to Oracle data files. I assume that by this they mean software not owned by the Oracle software owner. In any case, the ASMFD replaces ASMLIB. In my next post, I will go over the steps to replace ASMLIB with ASMFD.

 

 

 

Udev Rules

August 8, 2014

Being a true computer nerd, periodically I feel the need to learn a new application or skill. Most recently, I had some discussions with other nerds about the advantages of uses Linux’ native udev rules rather than Oracle’s ASMLIB for device persistence on Linux/Unix servers.

First a quick history of ASMLIB. Originally available on RHEL 4 and 5, ASMLIB became unavailable on RHEL 6 because Oracle stopped maintaining it for RHEL. After much negative publicity about this, Oracle made the kmod-oracleasm package available for RHEL, which replaced Oracle ASMLIB with about the same functionality, and only required a couple of packages from the Public OEL Yum servers to make it work properly (oracleasm and oracleasm-support). Even so, there are still discussions, often tantamount to religious wars, with regard to the advantages of ASMLIB versus udev.

As a consequence, I felt it would be a good idea for me to learn how to use udev. Udev is designed to accomplish the same task that ASMLIB does, that is persist device names and privileges across system reboots on Linux servers. Linux has the unfortunate habit of renaming disk devices when you add new ones, eg, /dev/sdc might become /dev/sdd when a new device is added, and the new device becomes /dev/sdc. Udev uses the unique SCSI device id (UUID) to associate a specific storage device with a specific mount point on the Linux server, thus making the Linux name moot. Udev can be used on most, if not all devices on Linux servers, though this article only discusses it usage with storage devices. There are numerous examples of udev rules for use with the Oracle storage. In general, the format (in OEL/RHEL 6.5) is something like this:

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524856447a376e2d7871364a2d654b4272″, NAME+=”oracleasm/asm-disk1″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

Where KERNEL reperesents the device interface type (sd is SCSI, hd would be ide, there are others) SUBSYSTEM is the storage type (block) PROGRAM is the command to query the device, RESULT is the return value of the query, and the remainder of the fields show how to use the data returned. In the example above, the device with a name in the pattern SD?1 returning the result “14f504e46494c45524856447a376e2d7871364a2d654b4272″ gets named “/dev/oracleasm/asm-disk1″, with oracle as the owner, dba as the group, and protections are 660. Once the rule set works properly, the file can be duplicated for other nodes when using a cluster. The last two items are the key to the storage udev rules, the long ID number is unique to the storage device, and as a consequence will always be associated with the appropriate name. Thus, when the same set of rules is copied to other nodes on a cluster, each storage device is named correctly and as a consequence, software such as Oracle can find the storage where it is expected.

Udev rule files are also used to name network devices, cd and dvd drives, etc, and are stored in the directory /udev/rules.d. The rules files are always prefixed with a number indicating the order in which they will be executed followed by a name, followed b a suffix .rules. Eg, 99-oracleasm-devices.rules. This is very similar in design to the Linux runlevel startup scripts.

So, as you might have gathered udev rules are a fundamental piece of making Linux work. This really makes me wonder why such a fundamental piece of the Linux OS was changed in what is a fairly dramatic way in the next release of Redhat Linux, RHEL 7. Specifically, the location of the scsi_id program has changed, and the parameter used in the command have changed. The NAME option can no longer be used for Disk Devices, it only works for network devices. So the equivalent of the udev rule above, in RHEL 7 would be this:

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524856447a376e2d7871364a2d654b4272″, SYMLINK+=”oracleasm/asm-disk1″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

Note that it now creates a symbolic link for the device. I am not and do not claim to be a Linux System Administrator, but I would have expected to be able to search for ‘RHEL7 Udev rules’ and get some sort of explanation of this change, the expected rule change required, but to this point I have not found anything. The designers of this may have intended to use a different parameter than SYMLINK in the udev rules, but it appears to be the only option at this point.

To make the creation of udev rules a little quicker, I wrote a very simple script to generate the rules:

[root@oel7614 ~]# cat setudev.sh

#!/bin/sh
COUNTER=0
for id in `lsscsi -i 3 | awk -F ” ”  ‘{print$7}’`
do
COUNTER=$((COUNTER+1))
echo KERNEL==\”sd?1\”, SUBSYSTEM==\”block\”, PROGRAM==\”/usr/lib/udev/scsi_id -g -u -d /dev/\$parent\”, RESULT==\”${id}\”, SYMLINK+=\”oracleasm/asm-disk$COUNTER\”, OWNER=\”oracle\”, GROUP=\”dba\”, MODE=\”0660\”
done

 

Please note that the script above, while useful for me, makes some assumptions. One, it assumes that the disks of interest are on the third SCSI channel, which is probably not the case on the readers system. And two, it assumes that the UUID shows up in the seventh column of output. These will vary by device type and storage software. I am using openfiler and vmdk’s for my storage.

In order to see all SCSI devices on your server, run the lsscsi. The output should be similar to below (in larger print I hope), note this is merely an example from my VMware workstation:

[root@oel7614 ~]# lsscsi -i

[root@oel7614 ~]# lsscsi -i

[1:0:0:0] cd/dvd NECVMWar VMware IDE CDR10 1.00 /dev/sr0 –

[2:0:0:0] disk VMware, VMware Virtual S 1.0 /dev/sda 36000c29105108d894764376c6756ebf7

[3:0:0:0] disk OPNFILER VIRTUAL-DISK 0 /dev/sdb 14f504e46494c45524856447a376e2d7871364a2d654b4272

[3:0:0:1] disk OPNFILER VIRTUAL-DISK 0 /dev/sdc 14f504e46494c45524e78676a70372d556d43312d4f706379

[3:0:0:2] disk OPNFILER VIRTUAL-DISK 0 /dev/sdd 14f504e46494c455246305034744e2d786133422d79507066

[3:0:0:3] disk OPNFILER VIRTUAL-DISK 0 /dev/sde 14f504e46494c45524b46664d4c332d5863497a2d58764357

[3:0:0:4] disk OPNFILER VIRTUAL-DISK 0 /dev/sdf 14f504e46494c4552644b424254762d566468522d48355254

[3:0:0:5] disk OPNFILER VIRTUAL-DISK 0 /dev/sdg 14f504e46494c4552657172324e572d4e4676362d576c7944

[3:0:0:6] disk OPNFILER VIRTUAL-DISK 0 /dev/sdh 14f504e46494c45524e4b744332762d795862672d30377676

[3:0:0:7] disk OPNFILER VIRTUAL-DISK 0 /dev/sdi 14f504e46494c455231594d6c4d462d3456776f2d4d523552

[3:0:0:8] disk OPNFILER VIRTUAL-DISK 0 /dev/sdj 14f504e46494c45523452636e62632d4e4177692d51644665

[3:0:0:9] disk OPNFILER VIRTUAL-DISK 0 /dev/sdk 14f504e46494c455269314c68397a2d50646e352d316e4f33

[3:0:0:10] disk OPNFILER VIRTUAL-DISK 0 /dev/sdl 14f504e46494c455233686b36694e2d67514a632d58344e47

[3:0:0:11] disk OPNFILER VIRTUAL-DISK 0 /dev/sdm 14f504e46494c4552686b706e304b2d384e714f2d52586175

 

This is the output of the setudev script:

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524856447a376e2d7871364a2d654b4272″, SYMLINK+=”oracleasm/asm-disk1″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524e78676a70372d556d43312d4f706379″, SYMLINK+=”oracleasm/asm-disk2″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455246305034744e2d786133422d79507066″, SYMLINK+=”oracleasm/asm-disk3″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524b46664d4c332d5863497a2d58764357″, SYMLINK+=”oracleasm/asm-disk4″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c4552644b424254762d566468522d48355254″, SYMLINK+=”oracleasm/asm-disk5″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c4552657172324e572d4e4676362d576c7944″, SYMLINK+=”oracleasm/asm-disk6″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524e4b744332762d795862672d30377676″, SYMLINK+=”oracleasm/asm-disk7″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455231594d6c4d462d3456776f2d4d523552″, SYMLINK+=”oracleasm/asm-disk8″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45523452636e62632d4e4177692d51644665″, SYMLINK+=”oracleasm/asm-disk9″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455269314c68397a2d50646e352d316e4f33″, SYMLINK+=”oracleasm/asm-disk10″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455233686b36694e2d67514a632d58344e47″, SYMLINK+=”oracleasm/asm-disk11″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/usr/lib/udev/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c4552686b706e304b2d384e714f2d52586175″, SYMLINK+=”oracleasm/asm-disk12″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

The RHEL/OEL 6.5 version of the script and output are:

[root@oel7614 ~]# cat setudev65.sh

#!/bin/sh

COUNTER=0

for id in `lsscsi -i 3 | awk -F ” ” ‘{print$7}’`

do

COUNTER=$((COUNTER+1))

echo KERNEL==\”sd?1\”, SUBSYSTEM==\”block\”, PROGRAM==\”/sbin/scsi_id -g -u -d /dev/\$parent\”, RESULT==\”${id}\”, NAME+=\”oracleasm/asm-disk$COUNTER\”, OWNER=\”oracle\”, GROUP=\”dba\”, MODE=\”0660\”

done

 

[root@oel7614 ~]# ./setudev65.sh

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524856447a376e2d7871364a2d654b4272″, NAME+=”oracleasm/asm-disk1″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524e78676a70372d556d43312d4f706379″, NAME+=”oracleasm/asm-disk2″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455246305034744e2d786133422d79507066″, NAME+=”oracleasm/asm-disk3″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524b46664d4c332d5863497a2d58764357″, NAME+=”oracleasm/asm-disk4″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c4552644b424254762d566468522d48355254″, NAME+=”oracleasm/asm-disk5″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c4552657172324e572d4e4676362d576c7944″, NAME+=”oracleasm/asm-disk6″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45524e4b744332762d795862672d30377676″, NAME+=”oracleasm/asm-disk7″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455231594d6c4d462d3456776f2d4d523552″, NAME+=”oracleasm/asm-disk8″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c45523452636e62632d4e4177692d51644665″, NAME+=”oracleasm/asm-disk9″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455269314c68397a2d50646e352d316e4f33″, NAME+=”oracleasm/asm-disk10″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c455233686b36694e2d67514a632d58344e47″, NAME+=”oracleasm/asm-disk11″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?1″, SUBSYSTEM==”block”, PROGRAM==”/sbin/scsi_id -g -u -d /dev/$parent”, RESULT==”14f504e46494c4552686b706e304b2d384e714f2d52586175″, NAME+=”oracleasm/asm-disk12″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

 

Please note: The above information is not comprehensive, the intent here is to give a basic tutorial on the usage of udev for storage on Oracle servers, it will not make you an expert on the subject! If, for example, multipathed devices are used, the udev rules are different. There are many other cases where the rules will be different, this is just a basic introduction.

In my next blog, the subject will be ASMLIB on Redhat using kmod-oracleasm, compared to ASMLIB on OEL, and also I plan to have some information on the changes to ASM that come in with Oracle 12c (ASMFD). That may result in more than one more entry. We will see how it goes.


Follow

Get every new post delivered to your Inbox.