Add/Remove ASM disks and asm_power_limit

Before going live on production our storage team decided to reconfigure their storage and raidsets for better performance. So i gave the raw devices back from asm and of course used the opportunity to test the asm_power_limit parameter.

From the following listing you can find how to list asm disks, adding and removing disks from a disk group and watch the rebalance operation from v$asm_operation view.

By changing the init parameter "asm_power_limit", asm rebalance operations are significantly getting faster. The difference can obviously be seen by setting the parameter to 10 (instead of default value 1) and then giving the same operations that need rebalance. Of course this was not an online system so we may want asm to exhaust all the IO for rebalancing. But in case of production systems there can be unwanted IO performance degradation of the application by this kind of aggressive setting.

SQL> select group_number,state,name,total_mb from v$asm_disk;

GROUP_NUMBER STATE    NAME                   TOTAL_MB
------------ -------- -------------------- ----------
0 NORMAL                               924
0 NORMAL                               924
0 NORMAL                               924
0 NORMAL                               924
1 NORMAL   DATA_0009                614300
1 NORMAL   DATA_0008                614290
1 NORMAL   DATA_0002                614290
1 NORMAL   DATA_0001                614290
1 NORMAL   DATA_0000                614290
1 NORMAL   DATA_0007                614290
1 NORMAL   DATA_0006                614290
1 NORMAL   DATA_0005                614290
1 NORMAL   DATA_0004                614290
1 NORMAL   DATA_0003                614290

14 rows selected.

SQL> alter diskgroup DATA drop disk DATA_0000;

Diskgroup altered.

SQL> alter diskgroup DATA drop disk DATA_0001;

Diskgroup altered.

SQL> select group_number,state,name,total_mb,label,path from v$asm_disk;

GROUP_NUMBER STATE    NAME              TOTAL_MB LABEL PATH
------------ -------- --------------- ---------- ----- --------------------------------------------------
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A7833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
1 NORMAL   DATA_0009           614300       /dev/rdsk/c6t600601607D731F00D994060263E8DD11d0s0
1 NORMAL   DATA_0008           614290       /dev/rdsk/c6t600601607D731F00D894060263E8DD11d0s0
1 NORMAL   DATA_0002           614290       /dev/rdsk/c6t600601607D731F006A37A4E0A0E7DD11d0s0
1 DROPPING DATA_0001           614290       /dev/rdsk/c6t600601607D731F006937A4E0A0E7DD11d0s0
1 DROPPING DATA_0000           614290       /dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0
1 NORMAL   DATA_0007           614290       /dev/rdsk/c6t600601607D731F007608DB3DA0E7DD11d0s0
1 NORMAL   DATA_0006           614290       /dev/rdsk/c6t600601607D731F007508DB3DA0E7DD11d0s0
1 NORMAL   DATA_0005           614290       /dev/rdsk/c6t600601607D731F007408DB3DA0E7DD11d0s0
1 NORMAL   DATA_0004           614290       /dev/rdsk/c6t600601607D731F007308DB3DA0E7DD11d0s0
1 NORMAL   DATA_0003           614290       /dev/rdsk/c6t600601607D731F007208DB3DA0E7DD11d0s0


SQL> select * from v$asm_operation;

OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES
----- ---- ---------- ---------- ---------- ---------- ---------- -----------
REBAL RUN          10         10       7198      12996        260          22
REBAL RUN          10         10       7679      12979        242          21
REBAL RUN          10         10       9286      12930        201          18
REBAL RUN          10         10      11647      12899        237           5

SQL> select * from v$asm_operation;

no rows selected

SQL> select group_number,state,name,total_mb,label,path from v$asm_disk;

GROUP_NUMBER STATE    NAME              TOTAL_MB LABEL PATH
------------ -------- --------------- ---------- ----- --------------------------------------------------
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A7833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0
0 NORMAL                       614290       /dev/rdsk/c6t600601607D731F006937A4E0A0E7DD11d0s0
0 NORMAL                       614290       /dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
1 NORMAL   DATA_0009           614300       /dev/rdsk/c6t600601607D731F00D994060263E8DD11d0s0
1 NORMAL   DATA_0008           614290       /dev/rdsk/c6t600601607D731F00D894060263E8DD11d0s0
1 NORMAL   DATA_0002           614290       /dev/rdsk/c6t600601607D731F006A37A4E0A0E7DD11d0s0
1 NORMAL   DATA_0007           614290       /dev/rdsk/c6t600601607D731F007608DB3DA0E7DD11d0s0
1 NORMAL   DATA_0006           614290       /dev/rdsk/c6t600601607D731F007508DB3DA0E7DD11d0s0
1 NORMAL   DATA_0005           614290       /dev/rdsk/c6t600601607D731F007408DB3DA0E7DD11d0s0
1 NORMAL   DATA_0004           614290       /dev/rdsk/c6t600601607D731F007308DB3DA0E7DD11d0s0
1 NORMAL   DATA_0003           614290       /dev/rdsk/c6t600601607D731F007208DB3DA0E7DD11d0s0


SQL> alter diskgroup DATA add disk '/dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0';

Diskgroup altered.

SQL> select group_number,state,name,total_mb,label,path from v$asm_disk;

GROUP_NUMBER STATE    NAME              TOTAL_MB LABEL PATH
------------ -------- --------------- ---------- ----- --------------------------------------------------
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A7833C0377FEDD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0
0 NORMAL                       614290       /dev/rdsk/c6t600601607D731F006937A4E0A0E7DD11d0s0
0 NORMAL                          924       /dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
1 NORMAL   DATA_0009           614300       /dev/rdsk/c6t600601607D731F00D994060263E8DD11d0s0
1 NORMAL   DATA_0008           614290       /dev/rdsk/c6t600601607D731F00D894060263E8DD11d0s0
1 NORMAL   DATA_0002           614290       /dev/rdsk/c6t600601607D731F006A37A4E0A0E7DD11d0s0
1 NORMAL   DATA_0007           614290       /dev/rdsk/c6t600601607D731F007608DB3DA0E7DD11d0s0
1 NORMAL   DATA_0006           614290       /dev/rdsk/c6t600601607D731F007508DB3DA0E7DD11d0s0
1 NORMAL   DATA_0005           614290       /dev/rdsk/c6t600601607D731F007408DB3DA0E7DD11d0s0
1 NORMAL   DATA_0004           614290       /dev/rdsk/c6t600601607D731F007308DB3DA0E7DD11d0s0
1 NORMAL   DATA_0003           614290       /dev/rdsk/c6t600601607D731F007208DB3DA0E7DD11d0s0
1 NORMAL   DATA_0000           614290       /dev/rdsk/c6t600601607D731F006837A4E0A0E7DD11d0s0


SQL> show parameter asm_power_limit;

NAME                            TYPE        VALUE
------------------------------- ----------- -------------------------
asm_power_limit                 integer     1

SQL> alter system set asm_power_limit=10;

System altered.

SQL> show parameter asm_power_limit;

NAME                            TYPE        VALUE
------------------------------- ----------- -------------------------
asm_power_limit                 integer     10

SQL> 

why auditing

In general it seems that security is one of the least important routines of the dba. But in my opinion this issue should be one of the most important issues. Not only be able to supply audit records to firm-wide audit documentations but also knowing what is going on our databases to be sure everything goes straight. Auditing is not only blaming someone for a wrong action. This routine is for early clearing up for the wrong events.
I start auditing with "db, extended" AUDIT_TRAIL parameter to track the sql statements with the actions by altering the system, restarting the instance is a must as this parameter cannot be changed online:

SQL> alter system set audit_trail="db, extended" scope=spfile;
SQL> shutdown immediate;
SQL> startup;

After enabling the auditing, i decided what actions should be audited. Here i was not very selective, i simply enabled all DML actions for the individual users.

AUDIT 
  INSERT ANY TABLE, 
  DELETE ANY TABLE, 
  UPDATE ANY TABLE 
BY "username" ;

AUDIT 
  INSERT TABLE, 
  DELETE TABLE, 
  UPDATE TABLE 
BY "username" ;

AUDIT 
  DROP ANY TABLE, 
  DROP ANY INDEX, 
  DROP ANY VIEW, 
  DROP ANY SYNONYM, 
  DROP ANY PROCEDURE, 
  DROP ANY SEQUENCE, 
  DROP ANY TRIGGER, 
  DROP ANY TYPE 
BY "username" ;

AUDIT 
  ALTER ANY TABLE, 
  ALTER ANY INDEX, 
  ALTER ANY PROCEDURE, 
  ALTER ANY SEQUENCE, 
  ALTER ANY TRIGGER, 
  ALTER ANY TYPE 
BY "username" ;

AUDIT 
  CREATE ANY TABLE, 
  DROP ANY INDEX, 
  DROP ANY VIEW, 
  DROP ANY SYNONYM, 
  DROP ANY PROCEDURE, 
  DROP ANY SEQUENCE, 
  DROP ANY TRIGGER, 
  DROP ANY TYPE 
BY "username";

Move sys.aud$ table to data tablespace: We dont want to oversize the SYSTEM tablespace. As far as i read from oracle documentation moving AUD$ table to another tablespace is not supported. Instead of this action writing some control and maintenance scripts are suggested to be used.

select * from sys.AUD$;
alter table sys.AUD$ move tablespace PQ_DATA;

List the auditing privileges and users: To check what is being audited by my database.

select * from dba_priv_audit_opts;

After two weeks of auditing, i nearly captured more than three commonly wrong routines especially using wrong tablespaces and deleting routines.

creating awr reports manually

When working with the third parties on the same database there happens some confusion. The operational dba 's are responsible from the database performance and other standard operational duties like backup, security, architecture .. and so on. Vendor dba 's are also responsible to achieve SLA contract requirements and promised performance values. Both side should be causious not to overlap the other side 's duties. Here i tried to answer one of the requests of our vendor dba by supplying hourly AWR reports directly in their directories on the UNIX environment. I think they can follow database activitiy better from now on.


### creating the awr report ###
ORACLE_HOME=/oracle/product/10.2.0; export ORACLE_HOME
ORACLE_SID=montc1; export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin; export PATH

$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF
declare
i_begin_snap_id number;
i_end_snap_id number;
i_db_id number;
i_inst_num number;
begin

--select the db_id and the instance_number of the instance,
--these values are going to be used to create the awr_report
select DBID, INSTANCE_NUMBER into i_db_id, i_inst_num from dba_hist_database_instance where rownum=1;

--find the snap_id of the time being
select SNAP_ID into i_end_snap_id from dba_hist_snapshot where trunc(end_interval_time,'HH24') = trunc(sysdate,'HH24') order by SNAP_ID desc;

--set the begin snap_id of the previous hour as we are generation hourly awr reports
i_begin_snap_id := i_end_snap_id - 1;

--generate and write the report to a table
insert into avea_sysmon.temp_awr_output select output from table (SYS.DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(i_db_id, i_inst_num, i_begin_snap_id, i_end_snap_id));
commit;

end;
/
EOF

### creating report name ###
report_name=/moneta_home/avea_data/awr/awr_report_`date | awk '{print $3"_"$2"_"$6"_"$4}'`.html
export report_name

### spooling previously created report to the file specified ###
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" << EOF
set linesize 1500;
set echo off;
set heading off;
set termout on;

spool $report_name;
select s_output from avea_sysmon.temp_awr_output;
truncate table avea_sysmon.temp_awr_output;
spool off;
EOF

### delete the older reports ###
ctl=`ls -l /moneta_home/avea_data/awr | grep .html | wc -l`
if [ $ctl -gt 110 ]
then
ls -l /moneta_home/avea_data/awr | grep .html | head -10 | awk '{print "/moneta_home/avea_data/awr/"$9}' | xargs rm
#else
#echo "dont delete anything"
fi


After i tested the script activity of the script, i used crontab to schedule this script as a job.

$ crontab -l
00,10,20,30,40,50 * * * * /oracle/epeker/check_arch.sh > /oracle/epeker/check_arch.log 2>&1
20 * * * * /oracle/awr_reports/get_awr.sh > /oracle/awr_reports/get_awr.log 2>&1

The awr reports of the specified time are now available on application directories.

$ ls -lrt /moneta_home/avea_data/awr
total 1053328
-rw-r--r--   1 oracle   dba      5229793 Nov  6 14:20 awr_report_6_Nov_2008_14:20:45.html
-rw-r--r--   1 oracle   dba      4911565 Nov  6 15:20 awr_report_6_Nov_2008_15:20:08.html
-rw-r--r--   1 oracle   dba      4865032 Nov  6 16:20 awr_report_6_Nov_2008_16:20:12.html
-rw-r--r--   1 oracle   dba      6112427 Nov  6 17:20 awr_report_6_Nov_2008_17:20:06.html
-rw-r--r--   1 oracle   dba      5057170 Nov  6 18:20 awr_report_6_Nov_2008_18:20:03.html
-rw-r--r--   1 oracle   dba      5031651 Nov  6 19:20 awr_report_6_Nov_2008_19:20:02.html
-rw-r--r--   1 oracle   dba      4798984 Nov  6 20:20 awr_report_6_Nov_2008_20:20:03.html
-rw-r--r--   1 oracle   dba      5016641 Nov  6 21:20 awr_report_6_Nov_2008_21:20:02.html
-rw-r--r--   1 oracle   dba      4895053 Nov  6 22:20 awr_report_6_Nov_2008_22:20:05.html
-rw-r--r--   1 oracle   dba      5228292 Nov  6 23:20 awr_report_6_Nov_2008_23:20:09.html
-rw-r--r--   1 oracle   dba      5018142 Nov  7 00:20 awr_report_7_Nov_2008_00:20:03.html
-rw-r--r--   1 oracle   dba      5072180 Nov  7 01:20 awr_report_7_Nov_2008_01:20:10.html