RAC Services

Not the lot you call down in a breakdown but  for Oracle Real Application Cluster.

Running two nodes, collabn1 and collabn2 as virtual machines (could be physical does not matter).

MADRID is a 12c Oracle database with a pluggable database BERLIN.

Added a service BERLIN_SVC with a preferred server instance of MADRID2 i.e. collabn2

Check the service...

[oracle@collabn2 admin]$ srvctl status database -db MADRID -v

Instance MADRID1 is running on node collabn1. Instance status: Open.

Instance MADRID2 is running on node collabn2. Instance status: Open.

[oracle@collabn2 admin]$ srvctl add service -d MADRID -service BERLIN_SVC.RACATTACK -pdb BERLIN -preferred MADRID2

Make sure to start the service and as if by magic, new service on second node.

[oracle@collabn2 admin]$ srvctl start service -service BERLIN_SVC.RACATTACK -db MADRID

[oracle@collabn2 admin]$ srvctl status database -db MADRID -v

Instance MADRID1 is running on node collabn1. Instance status: Open.

Instance MADRID2 is running on node collabn2 with online services BERLIN_SVC.RACATTACK. Instance status: Open.

[oracle@collabn2 admin]$ . oraenv

ORACLE_SID = [+ASM2] ? MADRID

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@collabn2 admin]$ export ORACLE_SID=MADRID2

[oracle@collabn2 admin]$ sqlplus system/racattack

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 8 14:26:13 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Wed Feb 08 2017 14:04:44 +00:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

Connect with the new service

SQL> connect system/racattack@collabn-cluster-scan.racattack:1521/BERLIN_SVC.RACATTACK

Connected.

Remember this is RAC so need to gv$ not the typical v$

SQL> select host_name from gv$instance where instance_number=userenv('instance');


HOST_NAME
----------------------------------------------------------------

collabn2.racattack

SQL> exit

Bitcoin - too crazy, too late?

This is sort of IT related, so thought I would share my thoughts on the virtual currency Bitcoin and crypto currency, which seems to be all over the news at the moment. I have been keeping an eye on Bitcoin from the early days and I nearly did buy some, but I kept thinking, is it all a bit crazy, am I too late or is it just another scam? To be honest, I wasn’t really interested in the money side (although a big plus mind - could have been a millionaire, money in pounds sterling thank you very much) it was more about building a massive server (years of IT could be put to some use) to so some mining, more on that further down.

Just a quick bit of background. At the moment, in the real world, if I was to say buy a television, the man in the shop scans my card and my bank comes back says I am good for the money and everyone is happy. It has been this way for a very long time. Now, if I want to spend Bitcoin there is no bank but there are lots and lots of "book keepers" all keeping tabs on my account. While, the man in the shop may not trust one individual bookkeeper he will trust all of them to tell him I am good for the money. What is in it for the book keepers, well every time a transaction happens one of them at random will earn some crypto currency for their work and in a very simplistic model this is what "mining" is all about. Now as virtual currencies become main stream, if I had a lot of virtual currency my concern is that the banks will start to fight back and in a fight (as much as I hate the "loads-a-money" bankers, I know who I would be betting on. 

While I can hardly say I am as green as a leprechaun's hat, I do like to think I do my bit for the environment.  So along came Bitcoin and all of a sudden people were buying high end machines with top end graphic cards to mine the currency (like I said nearly joined the "gold rush" but I was never really happy about the electricity usage for all of this, plus the kids wanted a big holiday and that won, kids eh?). Ever since then it seems to be getting ever more crazy -

https://99bitcoins.com/20-insane-bitcoin-mining-rigs/

Can you imagine living next door to someone with 50 or 60 high-end machines in their garage with fans to blow away an elephant? How long before someone burns their house down - maybe they already have. The problem is that even with the top end home rigs, they don't compare slightly to the massive server rooms being built across Iceland by the big players. In fact, more electricity is needed to power these server rooms than is needed to provide electricity to the locals. 

It isn't just Bitcoin, crypto currencies seem to be popping up all the time and every time a new currency pops up the miners turn their attention to the next big thing. At least now, you can buy servers on the cloud to do the mining for you. No doubt the criminals will get involved and somebody will hand over money to run 1000 servers when in fact they are getting a Pentium Pro (remember them). So while the bloke next door is trying to burn his house down or electrocute someone at least he knows what he is paying for and he may make a lot of money.

The whole crypto currency thing reminds me of the early days of social media. While I wish I had bought into Facebook, remember poor old ITV, who bought "Friend's Reunited" and the people who signed up for Bebo, there was always going to be winners and losers. While Bitcoin might go onto big things it's value can't just increase continually and people are talking of a bubble. We all know what happens to bubbles.

Anyway, back to why I did not buy Bitcoin, there's the bubble effect, the banks getting involved and I don't agree with the vast amounts of power being used. Plus as someone who works in IT, I am sure all those servers could be put to better use, even the search for aliens - not that ET has ever phoned back. More importantly, for those who remember the VW advert - "the man who bet a million on black when it came up red” the day I buy Bitcoin will be the day the whole thing will crash.

For more information :

https://www.theguardian.com/commentisfree/2017/dec/05/cryptocurrency-bitcoin-soul-damaged-faustian-pact-speculative-finance

https://www.bbc.co.uk/iplayer/episode/b09s3wbt/panorama-who-wants-to-be-a-bitcoin-millionaire

A bit of PLSQL - just for fun.

Always, looking for bits of PLSQL to use with my apprentice so that he might pick it up.

If you want to test your select knowledge.

http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

Not necessarily database related. So just for fun we did these.

• Simple maths question

I was asked a simple maths question by my daughter and wondered if I could use simple PL/SQL to work it out. It was the square root of a number plus 15 added to the square root of that number


i.e. sqrt(a+15) + sqrt(a) = 15 (knew it would be a low number so tried up to 100).

SQL>
1 DECLARE
2 c integer;
3 a integer := 0;
4 x number;
5 BEGIN
6 WHILE a < 100 LOOP
7 SELECT ((sqrt (a+15)) + (sqrt(a))) into x from dual;
8 dbms_output.put_line('loop ' || a ||' value '|| x);
9 IF (x = 15) then dbms_output.put_line('Success........');
10 END IF;
11 a := a+ 1;
12 END LOOP;
13* END;

SQL> /

set server output on

…..

loop 47 value 14.72966247441285514462090589789685682414

loop 48 value 14.865457163469280945614632626941270744

loop 49 value 15


Success........

PL/SQL procedure successfully completed.


So the square root of (49+15) added to the square root of 49 equals 15

• Lotto generator
  

As the lotto syndicate was doing terrible (does it do anything else) wrote a script to generate
100 numbers and we would pick six numbers from the output in the range we wanted.

There are thousands of examples out there to do this but at least now we can use our "Lucky Dips" haha

Using TRUNC or ROUND to alter the precision as required. For example, to produce random integer values between 1 and 59 truncate the output and add 1 to the upper boundary.

TRUNC(DBMS_RANDOM.value(1,60))

1 DECLARE
2 c integer;
3 a integer := 0;
4 TYPE ref_cursor IS REF CURSOR;
5 cur REF_CURSOR;
6 BEGIN
7 WHILE a < 100 LOOP
8 OPEN CUR for 'SELECT MOD(trunc(DBMS_RANDOM.Value(1, 60)) from dual';
9 FETCH CUR into c;
10 dbms_output.put_line('value of lotto: ' || c);
11 IF (c = 0) then dbms_output.put_line('value of lotto is Zero');
12 END IF;
13 a := a+ 1;
14 END LOOP;
15 CLOSE CUR;
16* END;

SQL> /

•  Sine wave generator

SQL> l

1 declare

2 d integer := 0;

3 e number(7,4);

4 f integer;

5 begin

6 dbms_output.put_line('Sine value');

7 loop

8 d := d + 1;

9 e := ((sin (d *(3.1412857/720))));

10 dbms_output.put_line(e);

11 exit when d >= 180;

12 end loop;

13* end;

Unfortunately, I had to put the output in Excel to see the Sine wave. I tried to use "tput" and asterisks in a shell script but it did not quite work as expected.

If anybody has any strange things that can be done in PLSQL please let me know.

Using the code from above you can easily use it as a template to do other stuff. If you wanted to show  character codes for example.

SQL> l

1 declare
2 a integer :=0;
3 x character;
4 begin
5 while a < 128 loop
6 select chr(a) into x from dual;
7 dbms_output.put_line('character ' || a ||' value '|| x);
8 a := a+1;
9 end loop;
10* end;


Variation to count rows from a table every hour for a week

  1  declare
  2                 x integer :=1;
  3                y integer;
  4                 z date;
  5                 begin
  6                while x < 168 loop
  7                 select count(*) into y from EC_MAIN.API_REQUEST_LOG where INSERTED_DTM < round(SYSDATE-(x/24),'hh');
  8                 select round(SYSDATE-(x/24),'hh') into z from dual;
  9                dbms_output.put_line( z ||     '       ' ||  y);
 10             x := x+1;
 11             end loop;
 12*         end;
 13  /


06-MAY-2019 10:00:00       4243025
06-MAY-2019 09:00:00       4229012
06-MAY-2019 08:00:00       4214438
...etc

e.g. so for 09-10am 4243025 - 4229012 = 14013 rows

Run a count for the whole days (should match the row sum for the 24 hours)  :

 1* select trunc(inserted_dtm), count(*) from ec_main.api_request_log group by trunc(inserted_dtm) order by 1
  2  /
...
06-MAY-2019 00:00:00     258371
...

Ran a double check to show hours :

 1  declare
  2      x integer :=1;
  3      y date;
  4      begin
  5      while x < 168 loop
  6      select round (SYSDATE-(x/24),'hh') into y from dual;
  7      dbms_output.put_line( x || '-- ' ||  y);
  8      x := x+1;
  9      end loop;
 10*   end;

SQL> /

1-- 02-MAY-2019 09:00:00
2-- 02-MAY-2019 08:00:00
3-- 02-MAY-2019 07:00:00
4-- 02-MAY-2019 06:00:00
5-- 02-MAY-2019 05:00:00
6-- 02-MAY-2019 04:00:00
7-- 02-MAY-2019 03:00:00
8-- 02-MAY-2019 02:00:00
9-- 02-MAY-2019 01:00:00
.....
....
164-- 25-APR-2019 14:00:00
165-- 25-APR-2019 13:00:00
166-- 25-APR-2019 12:00:00
167-- 25-APR-2019 11:00:00

PL/SQL procedure successfully completed.