Saturday, October 23, 2010

crsstat –t detailed output

 crsstat –t shows truncated resource names like below
# /u01/app/crs/bin/crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    fire       
ora....RE.lsnr application    ONLINE    ONLINE    fire       
ora.fire.gsd   application    ONLINE    ONLINE    fire       
ora.fire.ons   application    ONLINE    ONLINE    fire       
ora.fire.vip   application    ONLINE    ONLINE    fire       
ora.racdb.db   application    ONLINE    ONLINE    water      
ora....b1.inst application    ONLINE    ONLINE    fire       
ora....b2.inst application    ONLINE    ONLINE    water      
ora....SM2.asm application    ONLINE    ONLINE    water      
ora....ER.lsnr application    ONLINE    ONLINE    water      
ora.water.gsd  application    ONLINE    ONLINE    water      
ora.water.ons  application    ONLINE    ONLINE    water      
ora.water.vip  application    ONLINE    ONLINE    water   

The resource name is truncated and dots are used in Name column.

To view complete names, here is a wrapper script, (saved as my_crs_stat.sh in my system)
command=/u01/app/crs/bin/crs_stat
format_string="%-35s %-15s %-10s %-20s \n"

printf "$format_string" "Name" "Type" "Target" "State"
printf "$format_string" | tr ' ' '-'

$command | cut -f 2 -d= | tr '\n' ',' | sed s/,,/:/g | tr ':' '\n' | while read line
do
    name=`echo $line | cut -f1 -d','`
    type=`echo $line | cut -f2 -d','`
  target=`echo $line | cut -f3 -d','`
   state=`echo $line | cut -f4 -d','`

  printf "$format_string" "$name" "$type" "$target" "$state"
done

Output of the script looks like below
# ./my_crs_stat.sh
Name                                Type            Target     State               
------------------------------------------------------------------------------------
ora.fire.ASM1.asm                   application     ONLINE     ONLINE on fire      
ora.fire.LISTENER_FIRE.lsnr         application     ONLINE     ONLINE on fire      
ora.fire.gsd                        application     ONLINE     ONLINE on fire      
ora.fire.ons                        application     ONLINE     ONLINE on fire      
ora.fire.vip                        application     ONLINE     ONLINE on fire      
ora.racdb.db                        application     ONLINE     ONLINE on water     
ora.racdb.racdb1.inst               application     ONLINE     ONLINE on fire      
ora.racdb.racdb2.inst               application     ONLINE     ONLINE on water     
ora.water.ASM2.asm                  application     ONLINE     ONLINE on water     
ora.water.LISTENER_WATER.lsnr       application     ONLINE     ONLINE on water     
ora.water.gsd                       application     ONLINE     ONLINE on water     
ora.water.ons                       application     ONLINE     ONLINE on water     
ora.water.vip                       application     ONLINE     ONLINE on water


Wednesday, October 20, 2010

Using "alter user" privilege, connecting to Oracle database as different user without knowing his/her password

Linux root user can use a command "su -" to work as different user without entering a password of that user. For example, being a root user, I can enter the command,

[root@myserver ~]# su - littleboy
[littleboy@myserver ~]$

I can work as normal user littleboy without knowing the password of user littleboy. It is just possible as I am root.

Now the question, is it possible to work as normal user if I have dba role without knowing normal user password on Oracle database? In simple terms how to su on Oracle database? Here is the way.

Consider the following setup, where user bigboy is dba, and littleboy is normal user.

$ sqlplus / as sysdba
SQL> create user littleboy identified by littleboy1;
User created.
SQL> grant connect, resource to littleboy;
Grant succeeded.
SQL> create user bigboy identified by bigboy1;
User created.
SQL> grant dba to bigboy;
Grant succeeded.

If bigboy wants to connect to database as littleboy without knowing password of littleboy, the steps are,

$ sqlplus bigboy/bigboy1
SQL> alter user littleboy grant connect through bigboy;
User altered.

Now bigboy can connect to database as littleboy user like this (as if bigboy switched user to littleboy), Note that user bigboy is entering his password, not littleboy's password.

$ sqlplus bigboy[littleboy]/bigboy1
SQL>

Here bigboy is a proxy user for littleboy. List of proxy users can be found from proxy_users view.

SQL> select proxy, client from proxy_users;
PROXY                          CLIENT
------------------------------ ------------------------------
BIGBOY                         LITTLEBOY

To revoke this connect through privilege for bigboy,

SQL> alter user littleboy revoke connect through bigboy;
User altered.

Actually to connect as different user, bigboy need not to have dba role, he just needs "alter user" privilege. Of course, "alter user" is a powerful privilege, because it allows bigboy to connect as any user who has dba privilege!

Monday, October 11, 2010

A script to make autoextend off for data files


To make sure that the policy of "no datafiles should be autoextend on", if there are any datafiles have autoextend on, I have make them autoextend off. To automate it, here is a simple script.

set serveroutput on;
declare
stmt varchar2(600);
begin
 for i in (select file_name from dba_data_files where AUTOEXTENSIBLE='YES')
 loop
  stmt := 'alter database datafile ''' || i.file_name || ''' autoextend off';
  dbms_output.put_line(stmt);
  execute immediate stmt;
 end loop;
end;
/

Friday, October 8, 2010

Fixing “Waiting for driver initialization” delay issue while booting RHEL 5

Recently I had a new installation of RHEL 5.4 on my home desktop. I observed that kernel/initrd boot time goes around 30 seconds (without including time to start init services and X). I already faced this using RHEL 5.4 on vmware and ignored it by thinking that it is vmware performance issue. I could not do same when it appears on actual desktop.So, I fixed it in this way.

I have removed “quiet” parameter from kernel line in grub.conf to see where it is taking more time. It is taking more than 15 seconds when it is doing “Waiting for driver initialization“. As it message is appearing after starting nash, it is the issue in initrd image, not with kernel. After extracting initrd image, and reviewing init script in that, got who is culprit. Here it is,

echo Waiting for driver initialization.
stabilized –hash –interval 1000 /proc/scsi/scsi

To know what is “stabilized”, checked manual of nash, it is not there. Did a google for it, and found there is a bug logged against it. Unfortunately this bug is not going to be fixed by RedHat :-( . Did not get any workaround in my search.

So, as a last resort downloaded its source code (actually, it is in mkinird-XX.src.rpm). After reviewing nash.c, stabilized has a checksum function and delay function. I guessed that this may be removed. So, experimented by removing it.

The init script in initrd image will be generated by mkinitrd script. I commented the line which contains,

emit “stabilized –hash –interval 1 /proc/scsi/scsi”

And generated initrd image again. Yes, as I guessed, with new initrd image there is no long “Waiting for driver initialization“.

Getting child tables, referencing columns and referred columns for a parent table


Few days ago during a task at my office, I needed to get child tables of few given tables, and which columns of child tables are referencing parent tables. Another thing I observed is, some parent tables contain composite primary and/or unique keys. Also, some child tables refer both primary key and unique key columns.

For example, if I have parent and child tables created like below,

create table parent_table(
  c1 number,
  c2 number,
  c3 number,
  constraint pk_parent_table
    primary key  (c1), 
  constraint u1_parent_table 
    unique (c2, c3)
);

create table child_table1( 
  cc1 number,
  constraint child_table1_fk1
    foreign key (cc1) references parent_table(c1)
);

create table child_table2(
  cc2 number,
  cc3 number,
  constraint child_table2_fk1
    foreign key (cc2, cc3) references parent_table(c2, c3)
);

create table child_table3(
  cc1 number,
  cc2 number,
  cc3 number,
  constraint child_table3_fk1
    foreign key (cc1) references parent_table(c1),
  constraint child_table3_fk2
    foreign key (cc3, cc2) references parent_table(c2, c3)
);

Observe the order of columns in foreign key constraint referencing composite unique key of parent
child_table2: foreign key (cc2, cc3) references parent_table(c2, c3)
child_table3: foreign key (cc3, cc2) references parent_table(c2, c3)

The task is to get all child tables of 'PARENT_TABLE' and which columns of child tables are referencing which columns of parent. The output should look like this,

TABLE_NAME     REFERENCING_COLUMNS  REFERENCED_COLUMNS
-------------- -------------------- -------------------
CHILD_TABLE1   CC1                  C1
CHILD_TABLE2   CC2,CC3              C2,C3
CHILD_TABLE3   CC1                  C1
CHILD_TABLE3   CC3,CC2              C2,C3

I could not get any ready-made SQL for this on net. So, started writing a new query. The query which gave almost the results I needed was

select  child_tab_cols.owner,
        child_tab_cols.table_name,
        child_tab_cols.column_name referencing_columns,
        parent_tab_cols.column_name referenced_columns
from    all_constraints   parent_tab_cons,
        all_cons_columns  parent_tab_cols,
        all_constraints   child_tab_cons,
        all_cons_columns  child_tab_cols
where    
-- Filters to get primary/unique constraint names
        parent_tab_cons.owner            = 'MURTY'
    and parent_tab_cons.table_name       = 'PARENT_TABLE'
    and parent_tab_cons.constraint_type in ('P', 'U')
-- Get columns of parent table using primary/unique constraint names
    and parent_tab_cols.constraint_name  = parent_tab_cons.constraint_name
-- Get child's foreign constraint names that refer parent's primary/unique constraints
    and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name
-- Get columns of child table using foreign constraint names
    and child_tab_cols.constraint_name   = child_tab_cons.constraint_name 
-- Now match columns of parent and child in case of composite primary/unique key
    and child_tab_cols.position          = parent_tab_cols.position
-- group to aggregate column names
order by  child_tab_cols.owner,
          child_tab_cols.table_name,
          child_tab_cols.constraint_name,
          child_tab_cols.position
;


Output of the query is
OWNER   TABLE_NAME    REFERENCING_COLUMNS  REFERENCED_COLUMNS
------- ------------- -------------------- -------------------
MURTY   CHILD_TABLE1  CC1                  C1
MURTY   CHILD_TABLE2  CC2                  C2
MURTY   CHILD_TABLE2  CC3                  C3
MURTY   CHILD_TABLE3  CC1                  C1
MURTY   CHILD_TABLE3  CC3                  C2
MURTY   CHILD_TABLE3  CC2                  C3

I have added owner name in the query as we had child tables even in different schema also. Though this query gives the required information, I wanted to aggregate composite primary/unique columns in output by separating column names with ','.

To use my favorite string aggregation technique, I have created required data type and function.

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION to_string (
                  nt_in        IN varchar2_ntt,
                  delimiter_in IN VARCHAR2 DEFAULT ','
                  ) RETURN VARCHAR2 IS
     v_idx PLS_INTEGER;
     v_str VARCHAR2(32767);
     v_dlm VARCHAR2(10);
BEGIN
     v_idx := nt_in.FIRST;
     WHILE v_idx IS NOT NULL LOOP
        v_str := v_str || v_dlm || nt_in(v_idx);
        v_dlm := delimiter_in;
        v_idx := nt_in.NEXT(v_idx);
     END LOOP;

     RETURN v_str;
END to_string;
/

Now using the above function, query which gives results exactly I need is

select  child_tab_cols.owner,
        child_tab_cols.table_name,
-- aggregate child table columns that refer composite primary/unique key
        to_string (cast
                    (collect (child_tab_cols.column_name order by child_tab_cols.position)
                   as varchar2_ntt )
                  ) referencing_columns,
-- aggregate parent table columns that have composite primary/unique key
        to_string (cast
                    (collect (parent_tab_cols.column_name order by parent_tab_cols.position)
                   as varchar2_ntt )
                  ) referenced_columns
from    all_constraints   parent_tab_cons,
        all_cons_columns  parent_tab_cols,
        all_constraints   child_tab_cons,
        all_cons_columns  child_tab_cols
where    
-- Filters to get primary/unique constraint names
          parent_tab_cons.owner            = 'MURTY'
      and parent_tab_cons.table_name       = 'PARENT_TABLE'
      and parent_tab_cons.constraint_type in ('P', 'U')
-- Get columns of parent table using primary/unique constraint names
      and parent_tab_cols.constraint_name  = parent_tab_cons.constraint_name
-- Get child's foreign constraint names that refer parent's primary/unique constraints
      and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name
-- Get columns of child table using foreign constraint names
      and child_tab_cols.constraint_name   = child_tab_cons.constraint_name 
-- Now match columns of parent and child in case of composite primary/unique key
      and child_tab_cols.position          = parent_tab_cols.position
-- group to aggregate column names
group by  child_tab_cols.owner,
          child_tab_cols.table_name,
          child_tab_cols.constraint_name
;

BTW, in 11gR2 we can write above query using "listagg" for aggregation like below

select  child_tab_cols.owner,
        child_tab_cols.table_name,
-- aggregate child table columns that refer composite primary/unique key
        listagg(child_tab_cols.column_name, ',')
          within group (order by child_tab_cols.position) referencing_columns,
-- aggregate parent table columns that have composite primary/unique key
        listagg(parent_tab_cols.column_name, ',')
          within group (order by parent_tab_cols.position) referenced_columns
from    all_constraints   parent_tab_cons,
        all_cons_columns  parent_tab_cols,
        all_constraints   child_tab_cons,
        all_cons_columns  child_tab_cols
where    
-- Filters to get primary/unique constraint names
          parent_tab_cons.owner            = 'MURTY'
      and parent_tab_cons.table_name       = 'PARENT_TABLE'
      and parent_tab_cons.constraint_type in ('P', 'U')
-- Get columns of parent table using primary/unique constraint names
      and parent_tab_cols.constraint_name  = parent_tab_cons.constraint_name
-- Get child's foreign constraint names that refer parent's primary/unique constraints
      and child_tab_cons.r_constraint_name = parent_tab_cons.constraint_name
-- Get columns of child table using foreign constraint names
      and child_tab_cols.constraint_name   = child_tab_cons.constraint_name 
-- Now match columns of parent and child in case of composite primary/unique key
      and child_tab_cols.position          = parent_tab_cols.position
-- group to aggregate column names
group by  child_tab_cols.owner,
          child_tab_cols.table_name,
          child_tab_cols.constraint_name
;