2015-10-12

Installing Oracle 12.1.0.2 on Red Hat 7

This is not a complete guide how to do the installation. Just a note how to get a RPM-GPG-KEY-oracle file to Red Hat 7. Go to oracle-base for installation notes on oel7.

Installation documentation is mentioning: "Starting with Oracle Database 12c Release 1 (12.1.0.2), Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64 systems." So do not try to install 12.1.0.1 on Red Hat 7. The case when you have SE one licence and not yet buying SE2 license. Just to mention 11.2 is supported to be installed on Red Hat Enterprise Linux 7.

Download the information about Oracle public yum repo https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-downloading-yum-repo.html

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol7.repo
Try to install preinstall package and get an error.
yum install oracle-rdbms-server-12cR1-preinstall
...
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

GPG key retrieval failed: [Errno 14] curl#37 - "Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle"
You need the file mentioned here https://docs.oracle.com/cd/E37670_01/E39381/html/ol_import_gpg.html
wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol7 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
And you will succeed installing:
yum install oracle-rdbms-server-12cR1-preinstall

2015-07-24

SUM over time -Elements

A while ago wrote how to calculate sum over time. During overlapping time intervals the resulting sum is produced there. But how about if one needs the elements that the sum is generated from?
2015-07-25 2015-07-26 1
2015-07-26 2015-07-28 2
2015-07-27 2015-07-30 3
The testdata is generated in td view.
with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
), b as (
  select row_number()over(order by case r when 1 then validFrom else validTill end,r desc) rn
       , a
       , r
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   , sum(case r when 1 then 1 else -1 end) over (order by case r when 1 then validFrom else validTill end,r desc) cntover
   from td, (
  select rownum r from dual connect by level < 3
  )
),c (rn,a,r,validfrom,sumover,cntover,vals) as (
 select rn,a,r,validfrom,sumover,cntover,mdsys.SDO_NUMTAB(a) from b where rn = 1
union all
select b.rn,b.a,b.r,b.validFrom,b.sumover,b.cntover,case b.r when 1 then c.vals multiset union mdsys.SDO_NUMTAB(b.a) else c.vals multiset except mdsys.SDO_NUMTAB(b.a) end
  from b,c where b.rn = c.rn+1 
),d as (select max(cntover)over(partition by validFrom) mx,a,r,validFrom,sumover,cntover,(select count(*) from table(vals)) n,vals, count(*)over(partition by validFrom) cn 
  from c 
  where cntover=(select count(*) from table(vals))
) 
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumover
     , (select listagg(column_value,'+')within group(order by column_value) from table(vals)) vals
  from d
 where cntover=mx
order by validFrom;

2015-07-25 2015-07-26 1 1
2015-07-26 2015-07-27 2 2
2015-07-27 2015-07-28 5 2+3
2015-07-28 2015-07-30 3 3
2015-07-30 2200-01-01 0 

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.