ASM: Adding disk “_DROPPED%” FORCE

Ok doke,
First let I make it clear: Adding a disk with force should be avoided, mainly by all the rebalance involved. The best choice, if you has “time”, is to just put disks online, like:

1) ALTER DISKGROUP ONLINE DISK ; or
2) ALTER DISKGROUP ONLINE DISKS IN FAILGROUP ; or
3) ALTER DISKGROUP ONLINE ALL;

But, the post is about adding back to DG the dropped disks.
Let’s imagine, to undestand my situation, you lost the contact with one of your two site storage… In this example, represented by failgroup FGAUX. You would see the disks like this:

SQL> select name,failgroup,state from v$asm_disk a where state <> 'NORMAL';

NAME FAILGROUP STATE
------------------------------ ------------------------------ --------
_DROPPED_0000_DGDATA FGAUX FORCING
_DROPPED_0001_DGDATA FGAUX FORCING
_DROPPED_0002_DGDATA FGAUX FORCING

So, you know your disks by the name pattern (0 are FGMAIN and 1 are FGAUX, the problematic). You can do something like:

[root@database-host ~]# /etc/init.d/oracleasm listdisks |grep DGDATA
DGDATA001
DGDATA002
DGDATA003
DGDATA101
DGDATA102
DGDATA103

Now, make the simple… 🙂

SQL> ALTER DISKGROUP DGDATA ADD
FAILGROUP FGAUX
DISK
'ORCL:DGDATA101' name DGDATA101 FORCE,
'ORCL:DGDATA102' name DGDATA102 FORCE,
'ORCL:DGDATA103' name DGDATA103 FORCE;

Diskgroup altered.

SQL> ALTER DISKGROUP DGDATA rebalance power 8;

Diskgroup altered.

While waiting the reball, let’s see the disks in DG:

SQL> select * from v$asm_operation where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
3 REBAL WAIT 8
SQL> select name,failgroup,state from v$asm_disk a where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

NAME FAILGROUP STATE
------------------------------ ------------------------------ --------
_DROPPED_0000_DGDATA FGAUX FORCING
_DROPPED_0001_DGDATA FGAUX FORCING
_DROPPED_0002_DGDATA FGAUX FORCING
DGDATA101 FGAUX NORMAL
DGDATA102 FGAUX NORMAL
DGDATA103 FGAUX NORMAL
DGDATA001 FGMAIN NORMAL
DGDATA002 FGMAIN NORMAL
DGDATA003 FGMAIN NORMAL

And, when the rebalance end, the situation will be OK:

SQL> select * from v$asm_operation where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
3 REBAL RUN 8 8 629 19087 10143 1

SQL> select * from v$asm_operation where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

no rows selected

SQL> select name,failgroup,state from v$asm_disk a where group_number=(select group_number from v$asm_diskgroup where name='DGDATA');

NAME FAILGROUP STATE
------------------------------ ------------------------------ --------
DGDATA101 FGAUX NORMAL
DGDATA102 FGAUX NORMAL
DGDATA103 FGAUX NORMAL
DGDATA001 FGMAIN NORMAL
DGDATA002 FGMAIN NORMAL
DGDATA003 FGMAIN NORMAL

OK? Easy! 😀

Matheus.

Grepping Entries from Alert.log

Hey hey,
One more McGayver by me! Haha
Again to find some information in alert. This time, I’m looking to count and list all occurrences of an action in alert. To archive this, I made the script below.

grep-swiss-knife-590x295

The functionality is just a little bit more complex than the script of the last post, but stills quite simple. Take a look:

Parameters:
PAR1: name of alert (the main alert.log)
PAR2: Searched token
PAR3: Start day you want to, in the format “Mon dd” or just “Mon”. Below an example.
PAR4: Start Year (4 digits)
PAR5: [optional]End day you want to, in the format “Mon dd” or just “Mon”. The default value is “until now”.
PAR6: [optional]End Year (4 digits). The default value is “until now”. If you use the PAR5, you have to use PAR6.
PAR7: [optional] List All entries and when?. If you want to use this PAR, you must to use PAR5 and PAR6.

Examples (Looking for service reconfigurations):
Ex1: sh grep_entries_alert.sh alert_xxdb_1.log “services=” “Apr 12” 2015
(Seach between April 12 and now and count entries).
Ex2: sh grep_entries_alert.sh alert_xxdb_1.log “services=” “Apr 01” 2015 “May 30” 2015
(Seach between April 01 and May 30 and count the entries).
Ex3: sh grep_entries_alert.sh alert_xxdb_1.log “services=” “Apr 01” 2015 “May 30” 2015 LIST
(Seach between April 01 and May 30 and count the entries and list them all…)

# Script grep_entries_alert.sh
if [ $# -lt 6 ]; then
FIN=`cat $1 |wc -l`
else FIN=`cat $1 |grep -n $5 |grep $6$ |head -n 1 |cut -d':' -f1`
fi
BEG=`cat $1 |grep -n "$3" |grep $4$ |head -n 1 |cut -d':' -f1`
NMB=`expr $FIN - $BEG`
ENTR=`cat $1 |head -n $FIN |tail -$NMB| grep $2|wc -l`
echo Number of Entries: $ENTR >log.log
if [ $# -lt 7 ]; then
echo ------- Complete List Of Entries and When ---------- >> log.log
for line in `cat $1 |head -n $FIN |tail -$NMB| grep -n $2|cut -d':' -f1`;do
LR=`expr $line + $BEG` # To get "real line", without the displacement
DAT=`expr $LR - 1`     # To get line date of entry
echo awk \'NR==$DAT\' $1 >>aux.sh # Printing the lines just calculted
echo awk \'NR==$LR\' $1 >>aux.sh  # with aux.sh
done;
sh aux.sh >>log.log
fi
cat log.log

It’s not beautiful. But it works! 🙂

After that, there is the new blog sponsor:
MacGyver-macgyver-880400_200_228
(Hahahaha)

Matheus.

Grepping Alert by Day

Hi all,
For that moment when your alert is very big and some OS doesn’t “work very well with it” (in my case was using AIX), I jerry-ringged the shellscript bellow. It puts in a new log just the log entries of a selected day.

24 7 365

The call can be made with two or three parameters, this way:

Parameters:
PAR1:
name of alert (the main alert.log)
PAR2: Day you want to, in the format “Mon dd”. Below an example.
PAR3: [optional] desired year. The default is the current year. But is useful specially on the “new year” period…

Examples:
Ex1: sh grep_day.sh alert_xxdb_1.log “Apr 12”
Ex2: sh grep_day.sh alert_xxdb_1.log “Apr 12” 2014

Generated files:
dalert_2015Apr12.log
dalert_2014Apr12.log

# Script grep_day.sh
if [ $# -lt 3 ]; then
YEAR=`date +"%Y"`
else YEAR=$3
fi
DATEFORMAT=`echo $2|cut -d' ' –f1`""`echo $2|cut -d' ' –f2`
BEG=`cat $1 |grep -n "$2" |grep $YEAR |head -1 |cut -d':' -f1`
FIN=`cat $1 |grep -n "$2" | grep $YEAR |tail -1 |cut -d':' -f1`
NMB=`expr $FIN - $BEG`
cat $1 |head -$FIN |tail -$NMB > dalert_$YEAR$DATEFORMAT.log

Belive me! It can be useful…. haha

See ya!

Matheus.

Oracle Convert Number into Days, Hours, Minutes

There’s a little trick…
Today I had to convert a “number” of minutes into hours:minutes format. Something like convert 570 minutes in format hour:minutes. As you know, 570/60 is “9,5” and should be “9:30”.

Lets use 86399 seconds (23:59:59) as example:

I began testing “to_char(to_date)” functions:
boesing@db>select to_char(to_date(86399,'sssss'),'hh24:mi:ss') formated from dual;

FORMATED
——–
23:59:59

Ok, it works. But using “seconds past midnight” (sssss). By the way, it works between 0 and 86399 only:

boesing@db> select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual;
select to_char(to_date(86400,'sssss'),'hh24:mi:ss') from dual
*
ERROR at line 1:
ORA-01853: seconds in day must be between 0 and 86399

The problem remains. How to use minutes in 3 digits (570 minutes -> 9:30), for example?
The best way I solve was:

--- Seconds in hours:minutes:seconds
--- If you comment the first "TO_CHAR" line, can be minutes in hours:minutes too..
select
TO_CHAR(TRUNC(vlr/3600),'FM9900') || ':' || -- hours
TO_CHAR(TRUNC(MOD(vlr,3600)/60),'FM00') || ':' || -- minutes
TO_CHAR(MOD(vlr,60),'FM00') -- second
from dual;

It always works. 🙂

boesing@db>select
2 TO_CHAR(TRUNC(86399/3600),'FM9900') || ':' || -- hours
3 TO_CHAR(TRUNC(MOD(86399,3600)/60),'FM00') || ':' || -- minutes
4 TO_CHAR(MOD(86399,60),'FM00') -- second
5 from dual;

TO_CHAR(TRUNC
————-
23:59:59

boesing@db>select
2 TO_CHAR(TRUNC(570/3600),’FM9900′) || ‘:’ || — hours
3 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — minutes
4 TO_CHAR(MOD(570,60),’FM00′) — second
5 from dual;

TO_CHAR(TRUNC
————-
00:09:30

boesing@db>select
2 TO_CHAR(TRUNC(MOD(570,3600)/60),’FM00′) || ‘:’ || — hours
3 TO_CHAR(MOD(570,60),’FM00′) — minutes
4 from dual;

TO_CHAR
——-
09:30

Any better way? Leave a comment. Thanks!

Matheus.

How to list all my Oracle Products from Application park?

YES!
I knew you would like the last post!

So, remains a doubt. What about my Oracle Application park?
Be soft. I’m glad to help. At real, Dieison Santos and me. As I said in the last post, it was his problem theese days… 😛

Here is a query to list your Oracle Application Products (including Oracle SOA Suite, of course) from OEM.

Use wisely:

select distinct * from (
select (LBL_HOSTNAME) "Host",
(CASE
when LBL_PRODUCTNAME like 'WebLogic Server' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%WebTier and Utilities%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%EM Platform (OMS)%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%Web Services Manager%' then 'Diagnostics Pack for Internet Application Server'
when LBL_PRODUCTNAME like '%Application Server 10g%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%' then 'Oracle Enterprise Single Sign-On Suite'
when LBL_PRODUCTNAME like '%Business Intelligence%' then 'Oracle Business Intelligence Suite Enterprise Edition Plus'
when LBL_PRODUCTNAME like '%Oracle SOA Suite%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%Oracle BAM%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g%' then 'Oracle WebCenter Portal'
when LBL_PRODUCTNAME like '%Oracle Business Process Management%' then 'Unified Business Process Management Suite'
when LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%' then 'Oracle WebCenter Content'
when LBL_PRODUCTNAME like '%Oracle Application Server Guard%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Configuration%' then 'Configuration Management Pack for Internet Application Server'
else
LBL_PRODUCTNAME
end)
"Produto",
LBL_BASEVERSION "Versao",
LBL_PROCESSOR "Processador",
lbl_virtual "VIrtual",
DECODE(LBL_CPUS,null,1,LBL_CPUS) "CPUS"
from (SELECT M.EXTERNAL_NAME LBL_PRODUCTNAME,
M.NAME LBL_COMPONENTNAME,
M.BASE_VERSION LBL_BASEVERSION,
M.HOST_NAME LBL_HOSTNAME,
p.virtual lbl_Virtual,
p.system_config || nvl2(p.freq, p.freq || ' MHz FSB ', '') LBL_PROCESSOR,
p.cpu_count LBL_CPUS
FROM (MGMT$SOFTWARE_COMPONENTS M INNER JOIN mgmt$os_hw_summary p ON
M.HOST_NAME = P.HOST_NAME))
where   (
LBL_PRODUCTNAME like 'WebLogic Server'
or LBL_PRODUCTNAME like '%WebTier and Utilities%'
or LBL_PRODUCTNAME like '%EM Platform (OMS)%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
or LBL_PRODUCTNAME like '%Application Server 10g%'
or LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%'
or LBL_PRODUCTNAME like '%Business Intelligence%'
or LBL_PRODUCTNAME like '%Oracle SOA Suite%'
or LBL_PRODUCTNAME like '%Oracle BAM%'
or LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g'
or LBL_PRODUCTNAME like '%Oracle Business Process Management%'
or LBL_PRODUCTNAME like '%Application Server Configuration%'
or LBL_PRODUCTNAME like '%Oracle Application Server Guard%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
) order by "Produto");

Matheus.

How to list all my Oracle Products from Database park?

This is part of DBA role: know and prospect the use of Oracle Products for Oracle contract periodical review, isn’t?
It usually represent a huge problem, or, at least, demands a long time to refresh your spread sheet…

Well, If you use OEM, we offer you a better option! 😀
(I said ‘we’, because Dieison Santos came to me with this problem theese days…. So we talked about, I gave some directives and he mainly solved the problem. This way, great part of ‘we’ should be ‘he’… haha)

Without further, here’s a query that can map your environment (at least your Oracle database products):
You can use it to automate a report and set thresholds. Be creative… 😉

PS: From now, I’ll post all in english. Just for fun.

select
distinct(ddi.host_name) "Host",
(case
when opt.name like '%Active Data Guard%' then 'Oracle Active Data Guard'
when opt.name like '%Advanced Compression%' then 'Oracle Advanced Compression'
when opt.name like '%Audit Vault%' then 'Oracle Audit Vault'
when opt.name like '%Database Vault%' then 'Oracle Database Vault'
when opt.name like '%Partitioning (User)%' then 'Oracle Partitioning'
when opt.name like '%Real Application Clusters%' then 'Oracle Real Application Clusters'
when opt.name like '%Real Application Testing%' then 'Oracle Real Application Testing'
when (opt.name like '%ADDM%' or
opt.name like '%Automatic Database Diagnostic Monitor%' or
opt.name like '%Automatic Workload Repository%' or
opt.name like '%AWR%' or
opt.name like '%Baseline%' or
opt.name like '%Diagnostic Pack%' ) then 'Oracle Diagnostic Pack'
when (opt.name like '%SQL Monitoring%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Profile%' or
opt.name like '%SQL Tuning%' or
opt.name like '%SQL Access Advisor%' or
opt.name like '%Tuning Pack%') then 'Oracle Tuning Pack'
when opt.name like '%Change Management Pack%' then 'Oracle Change Management Pack'
when ddi.edition like 'Enterprise Edition' then 'Oracle Database Enterprise Edition'
else opt.name
end) "Produto Oracle",
hcd.num_cores "Cores",
ohs.virtual "Virtual",
hcd.impl "Processador",
ddi.dbversion "Versao"
from
mgmt$hw_cpu_details hcd,
mgmt$os_hw_summary ohs,
mgmt$db_dbninstanceinfo ddi,
(select
h.host_name as host,
h.target_name as database_name,
i.instance_name as instance_name,
h.target_type   as target_type,
h.target_guid as target_guid,
f.DBID,
f.NAME,
f.CURRENTLY_USED,
f.DETECTED_USAGES,
f.FIRST_USAGE_DATE,
f.LAST_USAGE_DATE,
f.VERSION,
f.LAST_SAMPLE_DATE,
f.LAST_SAMPLE_PERIOD,
f.TOTAL_SAMPLES,
f.AUX_COUNT,
f.DESCRIPTION
from
mgmt_db_featureusage f,
mgmt_targets h,
mgmt_db_dbninstanceinfo_ecm i,
gc$ecm_gen_snapshot s
where
s.is_current = 'Y' and
s.snapshot_guid = i.ecm_snapshot_id and
s.target_guid = f.target_guid and
h.target_type in ('oracle_database','rac_database') and
s.target_type = h.target_type and
s.snapshot_type in ('oracle_dbconfig','oracle_racconfig') and
f.DETECTED_USAGES>0
) opt
where
hcd.target_guid=ohs.target_guid
and ohs.host_name=ddi.host_name
and ddi.target_guid=opt.target_guid
and (    opt.name like '%Active Data Guard%' -- Active Data Guard
or opt.name like '%Advanced Compression%' -- Advanced Compression
or opt.name like '%Audit Vault%' -- Audit Vault
or opt.name like '%Database Vault%' -- DB Vault
or opt.name like '%Partitioning (user)%' -- Partitioning
or opt.name like '%Real Application Clusters%' --RAC
or opt.name like '%Real Application Testing%' -- RAT
or opt.name like '%ADDM%' -- Diagnostic Pack
or opt.name like '%Automatic Database Diagnostic Monitor%' -- Diagnostic Pack
or opt.name like '%Automatic Workload Repository%' -- Diagnostic Pack
or opt.name like '%AWR%' -- Diagnostic Pack
or opt.name like '%Baseline%' --  Diagnostic Pack
or opt.name like '%Diagnostic Pack%' -- Diagnostic Pack
or opt.name like '%SQL Monitoring%' -- Tuning Pack
or opt.name like '%SQL Performance%' -- Tuning Pack
or opt.name like '%SQL Profile%' -- Tuning Pack
or opt.name like '%SQL Tuning%' -- Tuning Pack
or opt.name like '%SQL Access%' -- Tuning Pack
or opt.name like '%Tuning Pack%' -- Tuning Pack
or opt.name like '%Change Management Pack%' -- Change Management Pack
or ddi.edition like 'Enterprise Edition')
order by ddi.host_name;

Matheus.