Vagrant Boxes. Base de dades Oracle

Introducció

En la pràctica anterior vas instal·lar una màquina Linux d'Oracle, però no era la base de dades Oracle. Aquesta vegada sí que entrarem en la base de dades relacional Oracle, àmpliament utilitzada en les grans empreses.

Seguirem els següents enllaços:

• https://pazikas.com/5-steps-to-use-oracle-database-with-vagrant/
• https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase

Instal·lació

Descarreguem el projecte:

$ git clone https://github.com/oracle/vagrant-projects

i ja podem entrar dins del projecte. Farem servir la base de dades 21 (versió última) XE (express edition, no necessites registrar-te a Oracle per descarregar aquesta versió).

$ cd vagrant-projects/
$ cd OracleDatabase/
$ cd 21.3.0-XE

Següent pas:

Download the installation zip file(s) from OTN into this directory - first time only

La nostra versió la trobarem en el següent enllaç:

• https://www.oracle.com/database/technologies/xe-downloads.html

Oracle Database 21c Express Edition for Linux x64 ( OL8 )

que es correspon al fitxer: oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm (uns 3GB, és normal)

aquest fitxer l'hem de copiar al nostre directori de treball on es farà la instal·lació (i on hi ha el Vagrantfile):

$ mv /home/joan/Baixades/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm /home/joan/vagrant-projects/OracleDatabase/21.3.0-XE

I ara ja podem arrencar la màquina vagrant, que llegirà la informació del fitxer de configuració Vagrantfile: (a la màquina del professor ha trigat una mitja hora):

$ vagrant up 

Bringing machine 'oracle21c-xe-vagrant' up with 'virtualbox' provider...
==> oracle21c-xe-vagrant: Importing base box 'oraclelinux/8'...
==> oracle21c-xe-vagrant: Matching MAC address for NAT networking...
==> oracle21c-xe-vagrant: Checking if box 'oraclelinux/8' version '8.5.320' is up to date...
==> oracle21c-xe-vagrant: Setting the name of the VM: oracle21c-xe-vagrant
==> oracle21c-xe-vagrant: Clearing any previously set network interfaces...
==> oracle21c-xe-vagrant: Preparing network interfaces based on configuration...
    oracle21c-xe-vagrant: Adapter 1: nat
==> oracle21c-xe-vagrant: Forwarding ports...
...
    oracle21c-xe-vagrant:   Installing       : oracle-database-xe-21c-1.0-1.x86_64                    1/1
    oracle21c-xe-vagrant:  
    oracle21c-xe-vagrant:   Running scriptlet: oracle-database-xe-21c-1.0-1.x86_64                    1/1
    oracle21c-xe-vagrant:  
    oracle21c-xe-vagrant: [INFO] Executing post installation scripts...
    oracle21c-xe-vagrant: [INFO] Oracle home installed successfully and ready to be configured.
    oracle21c-xe-vagrant: To configure Oracle Database XE, optionally modify the parameters in '/etc/sysconfig/oracle-xe-21c.conf' and then execute '/etc/init.d/oracle-xe-21c configure' as root.
    oracle21c-xe-vagrant:   Verifying        : oracle-database-xe-21c-1.0-1.x86_64                    1/1
    oracle21c-xe-vagrant:  
    oracle21c-xe-vagrant: 
    oracle21c-xe-vagrant: Installed:
    oracle21c-xe-vagrant:   oracle-database-xe-21c-1.0-1.x86_64                                           
    oracle21c-xe-vagrant: 
    oracle21c-xe-vagrant: Complete!
    oracle21c-xe-vagrant: INSTALLER: Oracle software installed
...
    oracle21c-xe-vagrant: INSTALLER: Running user-defined post-setup scripts
    oracle21c-xe-vagrant: INSTALLER: Done running user-defined post-setup scripts
    oracle21c-xe-vagrant: ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: EyfKUWyL7JQ=1
    oracle21c-xe-vagrant: INSTALLER: Installation complete, database ready to use!

Fixem-nos que en les últimes línies tenim el password per als usuaris sys, system i pdbadmin.

Entrem dins de la màquina vagrant amb SSH (fixar-se que en cap moment cal tenir obert el client d'escriptori del VirtualBox):

$ vagrant ssh
[vagrant@localhost ~]$

Canviem a l'usuari oracle:

[vagrant@localhost ~]$ sudo su - oracle
[oracle@localhost ~]

Comprovem que els processos d'Oracle estan funcionant:

$ ps -ef |grep -i listener
oracle     37535       1  0 17:03 ?        00:00:00 /opt/oracle/product/21c/dbhomeXE/bin/tnslsnr LISTENER -inherit

$ ps -ef |grep -i pmon
oracle     39510       1  0 17:19 ?        00:00:00 xe_pmon_XE

Sessió de base de dades dins d'Oracle

I ara ja podem entrar dins de la base de dades amb l'aplicació client sqlplus:

$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Feb 21 17:34:30 2022
Version 21.3.0.0.0

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

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>

Ara que ja estem a dins la base de dades, podem fer les operacions bàsiques:

• crear tres taules
• fer inserts sobre aquestes taules
• fer una select per recuperar informació

Per practicar farem servir una base de dades d'exemple:

• https://www.oracletutorial.com/getting-started/oracle-sample-database

Dins d'aquest enllaç podràs descarregar-te la base de dades: oracle-sample-database.zip

Te la descarregues, i la descomprimeixes. Dins del fitxer ot-schema.sql trobarem les sentències de creació de les taules regions, countries i locations:

CREATE TABLE regions
  (
    region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
    START WITH 5 PRIMARY KEY,
    region_name VARCHAR2( 50 ) NOT NULL
  );
-- countries table
CREATE TABLE countries
  (
    country_id   CHAR( 2 ) PRIMARY KEY  ,
    country_name VARCHAR2( 40 ) NOT NULL,
    region_id    NUMBER                 , -- fk
    CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )
      REFERENCES regions( region_id ) 
      ON DELETE CASCADE
  );

-- location
CREATE TABLE locations
  (
    location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24 
                PRIMARY KEY       ,
    address     VARCHAR2( 255 ) NOT NULL,
    postal_code VARCHAR2( 20 )          ,
    city        VARCHAR2( 50 )          ,
    state       VARCHAR2( 50 )          ,
    country_id  CHAR( 2 )               , -- fk
    CONSTRAINT fk_locations_countries 
      FOREIGN KEY( country_id )
      REFERENCES countries( country_id ) 
      ON DELETE CASCADE
  );

Dins de ot_data.sql tenim les dades que interessen, els insert:

Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe');
Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas');
Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia');
Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa');

REM INSERTING into OT.COUNTRIES
SET DEFINE OFF;
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('AR','Argentina',2);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('AU','Australia',3);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('BE','Belgium',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('BR','Brazil',2);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('CA','Canada',2);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('CH','Switzerland',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('CN','China',3);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('DE','Germany',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('DK','Denmark',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('EG','Egypt',4);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('FR','France',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('IL','Israel',4);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('IN','India',3);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('IT','Italy',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('JP','Japan',3);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('KW','Kuwait',4);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('ML','Malaysia',3);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('MX','Mexico',2);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('NG','Nigeria',4);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('NL','Netherlands',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('SG','Singapore',3);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('UK','United Kingdom',1);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('US','United States of America',2);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('ZM','Zambia',4);
Insert into COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('ZW','Zimbabwe',4);

REM INSERTING into OT.LOCATIONS
SET DEFINE OFF;
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (1,'1297 Via Cola di Rie','00989','Roma',null,'IT');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (2,'93091 Calle della Testa','10934','Venice',null,'IT');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (3,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (4,'9450 Kamiya-cho','6823','Hiroshima',null,'JP');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (5,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (6,'2011 Interiors Blvd','99236','South San Francisco','California','US');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (7,'2007 Zagora St','50090','South Brunswick','New Jersey','US');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (8,'2004 Charade Rd','98199','Seattle','Washington','US');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (9,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (10,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (11,'40-5-12 Laogianggen','190518','Beijing',null,'CN');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (12,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (13,'12-98 Victoria Street','2901','Sydney','New South Wales','AU');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (14,'198 Clementi North','540198','Singapore',null,'SG');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (15,'8204 Arthur St',null,'London',null,'UK');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (16,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (17,'9702 Chester Road','09629850293','Stretford','Manchester','UK');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (18,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (19,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (20,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (21,'Murtenstrasse 921','3095','Bern','BE','CH');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (22,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL');
Insert into LOCATIONS (LOCATION_ID,ADDRESS,POSTAL_CODE,CITY,STATE,COUNTRY_ID) values (23,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');

I ara ja podem fer una select:

SQL> select region_name, country_name, city
from regions r inner join countries using(region_id)
inner join locations l using (country_id);

...
Europe
United Kingdom
London

Europe
United Kingdom
Oxford
...

Tasques i entrega

Has de reproduir el tutorial, i poder fer una sessió mínima de SQL amb la base de dades d'exemple.

Entregaràs les evidències de què has instal·lat correctament una de les màquines oficials de Vagrant de la base de dades Oracle. Com a mínim ha d'haver-hi:

• captura de pantalla del Virtual Box on es vegi que la màquina està arrencada
• captura de pantalla on es vegi el password de l'usuari system
• captura de pantalla on es vegi una consulta SQL sobre les taules REGIONS, COUNTRIES, LOCATIONS.

---

creat per Joan Quintana Compte, febrer 2022