Infrastructure Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

oracleasm-support-2.1.5-1.el6.x86_64 doesnt work on OL 6.0 UEK

801534Feb 9 2011 — edited Feb 10 2011

ASMlib keeps return "Instantiating disk: failed" while trying to create ASM disks. It happens on both Clariion and Symmetrix devices.

[root@sgelxha198 ~]# uname -a
Linux sgelxha198 2.6.32-100.28.5.el6.x86_64 #1 SMP Wed Feb 2 18:40:23 EST 2011
x86_64 x86_64 x86_64 GNU/Linux
[root@sgelxha198 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.0 (Santiago)
[root@sgelxha198 ~]# rpm -aq |grep oracleasm
oracleasm-support-2.1.5-1.el6.x86_64

[root@sgelxha198 ~]# syminq |grep mpath
/dev/mapper/mpatha DGC RAID 5 0531 160018CC 5242880
/dev/mapper/mpathb DGC RAID 3 0531 170018CD 5242880
/dev/mapper/mpathc DGC RAID 1 0531 180018CE 5242880
/dev/mapper/mpathd DGC RAID 10 0531 190018CF 5242880
/dev/mapper/mpathe DGC VRAID 0531 1A4CE9B7 104857600
/dev/mapper/mpathf DGC VRAID 0531 1B4CE9B7 104857600
/dev/mapper/mpathah EMC SYMMETRIX 5875 1600050000 N/A
/dev/mapper/mpathai EMC SYMMETRIX 5875 1600E44000 5280000
/dev/mapper/mpathaj EMC SYMMETRIX 5875 1600E45000 5280000
/dev/mapper/mpathak EMC SYMMETRIX 5875 1600E46000 5280000
/dev/mapper/mpathal EMC SYMMETRIX 5875 1600E47000 5280000
/dev/mapper/mpatham EMC SYMMETRIX 5875 1600E48000 5280000

[root@sgelxha198 ~]# ls -l /dev/mapper/*p1
lrwxrwxrwx. 1 root root 8 Feb 9 03:13
/dev/mapper/360060160f6102900507cc7b2c3f5df11p1 -> ../dm-16
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathahp1 -> ../dm-18
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathaip1 -> ../dm-23
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathajp1 -> ../dm-24
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathakp1 -> ../dm-25
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathalp1 -> ../dm-26
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathamp1 -> ../dm-27
lrwxrwxrwx. 1 root root 7 Feb 9 03:30 /dev/mapper/mpathap1 -> ../dm-8
lrwxrwxrwx. 1 root root 7 Feb 9 03:30 /dev/mapper/mpathbp1 -> ../dm-9
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathcp1 -> ../dm-10
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathdp1 -> ../dm-12
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathep1 -> ../dm-11
lrwxrwxrwx. 1 root root 8 Feb 9 03:30 /dev/mapper/mpathfp1 -> ../dm-13

[root@sgelxha198 log]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@sgelxha198 log]#

[root@sgelxha198 ~]# oracleasm createdisk cxdisk /dev/mapper/mpathap1
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done
[root@sgelxha198 ~]# oracleasm createdisk vmaxdisk /dev/mapper/mpathaip1
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done

mpatha (360060160f6102900505745e1c3f5df11) dm-3 DGC,RAID 5
size=5.0G features='0' hwhandler='1 alua' wp=rw
|-+- policy='round-robin 0' prio=50 status=active
| `- 3:0:0:1 sdj 8:144 active ready running
`-+- policy='round-robin 0' prio=10 status=enabled
`- 2:0:0:1 sdb 8:16 active ready running
[root@sgelxha198 ~]# multipath -ll mpathai
mpathai (360000970000192602316533030453434) dm-17 EMC,SYMMETRIX
size=5.0G features='0' hwhandler='0' wp=rw
`-+- policy='round-robin 0' prio=1 status=active
|- 2:0:1:1 sdr 65:16 active ready running
`- 3:0:1:1 sdq 65:0 active ready running

This post has been answered by Avi Miller-Oracle on Feb 9 2011

Jump to Answer

Frank Kulash

Answer

Hi,

858747 wrote:
... Sample data:
------------------

Customer             product type
--------------------------------------------
AAA	                      DVD
AAA	                      Tape
BBB	                      Tape
CCC                                 DVD
CCC	                      Bluray
DDD	                      DVD
DDD	                      Tape
DDD	                      Bluray

...
Final Output
No. of customers who purchased the products across all product types

`	DVD	Tape	Bluray			
DVD		2	1			
Tape	2	1	1			
Bluray	1	1

Sorry, I don't follow the logic. Try explaining in words, with specific examples.
Didn't 2 distinct customers, 'CCC' and 'DDD', buy both 'DVD' and 'Bluray'? Why aren't the results

`	DVD	Tape	Bluray			
DVD		2	2
Tape	2	1	1			
Bluray	2	1

with 2's in the corners?

Assuming that was just mistake, you can do something like this:

WITH	got_pairs	AS
(
	SELECT	  a.product_type			AS product_a
	,	  NVL (b.product_type, a.product_type)	AS product_b
	FROM	  	cust_product	a
	LEFT OUTER JOIN	cust_product	b  ON  a.customer	= b.customer
					   AND	a.product_type != b.product_type
)
SELECT	  *
FROM	  got_pairs
PIVOT	  (	COUNT (*)
	  FOR	product_b
	  IN	( 'DVD'
		, 'Tape'
		, 'Blueray'
		)
	  )
ORDER BY  CASE  product_a
		WHEN  'DVD'	THEN  1
		WHEN  'Tape'	THEN  2
		WHEN  'Bluray'	THEN  3
	  END
;

This assumes that the combination (customer, product_type) is unique, as it is in your sample data., and that the only product_types are the 3 in your sample data.
if those assumptions are not true, then post some revised sample data and results, that show what you want in those cases.

Sorry, I don't have an Oracle 11 database right now to test this.
I can (and did) test the outer join, that combines a product_type with itself only if it can't be combined with anything else.

Using a pre-Oracle 11 pivot technique, the following works:

WITH	got_pairs	AS
(
	SELECT	  a.product_type			AS product_a
	,	  NVL (b.product_type, a.product_type)	AS product_b
	FROM	  	cust_product	a
	LEFT OUTER JOIN	cust_product	b  ON  a.customer	= b.customer
					   AND	a.product_type != b.product_type
)
SELECT	  product_a	AS product_type
,	  COUNT (CASE WHEN product_b = 'DVD'    THEN 1 END)	AS dvd
,	  COUNT (CASE WHEN product_b = 'Tape'   THEN 1 END)	AS dvd
,	  COUNT (CASE WHEN product_b = 'Bluray' THEN 1 END)	AS dvd
FROM	  got_pairs
GROUP BY  product_a
ORDER BY  CASE  product_a
		WHEN  'DVD'	THEN  1
		WHEN  'Tape'	THEN  2
		WHEN  'Bluray'	THEN  3
	  END
;

Notice that the sub-query got_pairs is the same in both queries.

Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!

Edited by: Frank Kulash on May 29, 2011 4:05 PM
Added Oracle 9 query.

Marked as Answer by 861750 · Sep 27 2020

861750

Hi Kulash,

Thanks for responding. I will try to explain the logic to give you a clear picture of it(hopefully). Pls let me know in case you need any further information. Thanks once again.

1. There is a customer table wherein we can find the list of customers along with the products they purchase in their life time. It is as mentioned below.

Customer             product type
--------------------------------------------
AAA	                      DVD
AAA	                      Tape
BBB	                      Tape
CCC                                 DVD
CCC	                      Bluray
DDD	                      DVD
DDD	                      Tape
DDD	                      Bluray



2. Now the report should the results - who are all the customers who have purchased different products across all the combinations.

Now lets consider customer AAA, this customer has purchased 2 products DVD and Tape in his life time. Now considering the final report only for customer AAA will look like as below:

	AAA		

	DVD	Tape	Bluray				
DVD		 1				
Tape	 1					
Bluray		

As we know, though the customer AAA has purchased only 2 products DVD and Tape, this combination exists in 2 places like - 1. Tape as row, DVD as column and 2. DVD as row and Tape as column. So this whole combination - DVD and Tape for the customer AAA should appear at both the places

Now consider BBB, this customer has purchased only 1 product - Tape. so the report for this individual customer will look like - 


	BBB	

	DVD	Tape	Bluray				
DVD		 			
Tape	 	 1				
Bluray	


The report for customer CCC will be like below (similar logic to AAA)

	CCC

	DVD	Tape	Bluray				
DVD		 	1		
Tape	 	 			
Bluray	1

Now considering customer DDD, this customer has purchased 3 products in his life time - DVD, Tape, Bluray

Now figure out all the possible combinations of these products - 

DVD, Tape
Tape, Bluray
Bluray, DVD

The report for the customer DDD, will look like - (considering both row - column and column - row values)

	DDD

	DVD	Tape	Bluray				
DVD		 1	1		
Tape	 1	 	1		
Bluray	 1                1


Now combine all the four individual reports to get the desired result as mentioned below:

	DVD	Tape	Bluray			
DVD	0	2                 2	
Tape	2              1                 1		
Bluray	2              1                 0

Edited by: 858747 on 30-May-2011 11:35

JonWat

I think you need to get your logic straighter before you worry about the SQL.....

Why does B, who bought only tape, get a 1 in the cross of tape and tape, but D, who bought all three, gets all zeroes along the diagonal?

Assuming you were interested in figuring out how likely it is that someone who buys a DVD might also by a tape, I think you always need to document the diagonal elements, not only when they bought nothing else.

Jon

Aketi Jyuuzou

I like scalar subQuery 8-)

with cust_product(customer,product_type) as(
select 'AAA','DVD'    from dual union all
select 'AAA','Tape'   from dual union all
select 'BBB','Tape'   from dual union all
select 'CCC','DVD'    from dual union all
select 'CCC','Bluray' from dual union all
select 'DDD','DVD'    from dual union all
select 'DDD','Tape'   from dual union all
select 'DDD','Bluray' from dual)
select product_type,
(select count(count(*)) from cust_product b
 group by b.customer
 having max(decode(b.product_type,a.product_type,1))
      * max(decode(b.product_type,'DVD',1)) = 1
    and (a.product_type='DVD' and count(*) = 1
     or a.product_type!='DVD')) as "DVD",
(select count(count(*)) from cust_product b
 group by b.customer
 having max(decode(b.product_type,a.product_type,1))
      * max(decode(b.product_type,'Tape',1)) = 1
    and (a.product_type='Tape' and count(*) = 1
     or a.product_type!='Tape')) as "Tape",
(select count(count(*)) from cust_product b
 group by b.customer
 having max(decode(b.product_type,a.product_type,1))
      * max(decode(b.product_type,'Bluray',1)) = 1
    and (a.product_type='Bluray' and count(*) = 1
     or a.product_type!='Bluray')) as "Bluray"
  from cust_product a
group by product_type
order by case product_type
         when 'DVD'    then 1
         when 'Tape'   then 2
         when 'Bluray' then 3 end;

PRODUC  DVD  Tape  Bluray
------  ---  ----  ------
DVD       0     2       2
Tape      2     1       1
Bluray    2     1       0

My SQL articles of OTN-Japan :-)
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image4-1-365357-ja.html

1 person found this helpful

861750

A perfect solution !!! Thank you so much Kulash.

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 10 2011

Added on Feb 9 2011

#linux, #oracle-linux

7 comments

1,424 views

Infrastructure Software

oracleasm-support-2.1.5-1.el6.x86_64 doesnt work on OL 6.0 UEK

Comments

Post Details