Revisió de 16:53, 21 feb 2022

Contingut

1 Introducció
2 Instal·lació
3 Sessió de base de dades dins d'Oracle
4 Tasques i entrega

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:

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

Diferència entre revisions de la pàgina «Vagrant Boxes. Base de dades Oracle»

Revisió de 16:53, 21 feb 2022

Contingut

Introducció

Instal·lació

Sessió de base de dades dins d'Oracle

Tasques i entrega

Menú de navegació

Eines de l'usuari

Espais de noms

Variants

Vistes

Més

Cerca

Navegació

Institut Jaume Balmes

Màquines recreatives

CNC

Informàtica musical

joanillo.org Planet

Eines

@@ Línia 1: / Línia 1: @@
+=Introducció=
+[[Fitxer:Oracle21c-xe-vagrant.png | thumbnail]]
+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:
 <pre>
-https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase
+$ git clone https://github.com/oracle/vagrant-projects
-https://pazikas.com/5-steps-to-use-oracle-database-with-vagrant/
+</pre>
+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ó).
-git clone https://github.com/oracle/vagrant-projects
+<pre>
-cd vagrant-projects/OracleDatabase
+$ cd vagrant-projects/
+$ cd OracleDatabase/
-vagrant init
+$ cd 21.3.0-XE
-cd al directori que vull la versió, per ex:
+</pre>
-$ cd 11.2.0.2/
+Següent pas:
-$ cd 19.3.0
-aquí hi ha un Vagrantfile
-I ara hem d'anar a cercar
+Download the installation zip file(s) from OTN into this directory - first time only
-https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html
-Once we are in the directory we need to place the Oracle 19c database installation .zip files here so that our Vargant VM can pick them up and install an Oracle Database for us.
-LINUX.X64_193000_db_home.zip
-són 3GB
+La nostra versió la trobarem en el següent enllaç:
-$ cd 21.3.0-XE/
+*https://www.oracle.com/database/technologies/xe-downloads.html
-XE significa express edition
-https://www.oracle.com/database/technologies/xe-downloads.html
 Oracle Database 21c Express Edition for Linux x64 ( OL8 )
- 	(2,339,651,768 bytes - September 08, 2021)
-[Sha256sum: f8357b432de33478549a76557e8c5220ec243710ed86115c65b0c2bc00a848db]
-oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
-vagrant up
+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):
+<pre>
+$ mv /home/joan/Baixades/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm /home/joan/vagrant-projects/OracleDatabase/21.3.0-XE
+</pre>
+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):
+<pre>
+$ 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: 1521 (guest) => 1521 (host) (adapter 1)
-    oracle21c-xe-vagrant: 5500 (guest) => 5500 (host) (adapter 1)
-    oracle21c-xe-vagrant: 22 (guest) => 2222 (host) (adapter 1)
-==> oracle21c-xe-vagrant: Running 'pre-boot' VM customizations...
-==> oracle21c-xe-vagrant: Booting VM...
-...
-==> oracle21c-xe-vagrant: Mounting shared folders...
-    oracle21c-xe-vagrant: /vagrant => /home/joan/oracle2/vagrant-projects/OracleDatabase/21.3.0-XE
-==> oracle21c-xe-vagrant: Running provisioner: shell...
-    oracle21c-xe-vagrant: Running: /tmp/vagrant-shell20220219-5792-1j2x3bp.sh
-    oracle21c-xe-vagrant: INSTALLER: Started up
 ...
-     oracle21c-xe-vagrant: SQL*Plus: Release 21.0.0.0.0 - Production on Sat Feb 19 17:25:12 2022
+     oracle21c-xe-vagrant:   Installing       : oracle-database-xe-21c-1.0-1.x86_64                    1/1
-     oracle21c-xe-vagrant: Version 21.3.0.0.0
+    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: Copyright (c) 1982, 2021, Oracle.  All rights reserved.
+     oracle21c-xe-vagrant: Installed:
+    oracle21c-xe-vagrant:   oracle-database-xe-21c-1.0-1.x86_64
      oracle21c-xe-vagrant:
-     oracle21c-xe-vagrant: Connected to:
+     oracle21c-xe-vagrant: Complete!
-     oracle21c-xe-vagrant: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
+     oracle21c-xe-vagrant: INSTALLER: Oracle software installed
-    oracle21c-xe-vagrant: Version 21.3.0.0.0
+...
-    oracle21c-xe-vagrant: SQL>
-    oracle21c-xe-vagrant:
-    oracle21c-xe-vagrant: PL/SQL procedure successfully completed.
-    oracle21c-xe-vagrant:
-    oracle21c-xe-vagrant: SQL>
-    oracle21c-xe-vagrant: Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
-    oracle21c-xe-vagrant: Version 21.3.0.0.0
-    oracle21c-xe-vagrant: INSTALLER: Global EM Express port enabled
-    oracle21c-xe-vagrant: oracle-xe-21c.service is not a native service, redirecting to systemd-sysv-install.
-    oracle21c-xe-vagrant: Executing: /usr/lib/systemd/systemd-sysv-install enable oracle-xe-21c
-    oracle21c-xe-vagrant: INSTALLER: Created and enabled oracle-xe-21c systemd's service
-    oracle21c-xe-vagrant: INSTALLER: setPassword.sh file setup
      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: XkvDrsMflzM=1
+     oracle21c-xe-vagrant: ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: EyfKUWyL7JQ=1
      oracle21c-xe-vagrant: INSTALLER: Installation complete, database ready to use!
+</pre>
+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):
+<pre>
-I ara ja podem entrar a la caixa Vagrant on tenim una base de dades Oracle via ssh:
 $ vagrant ssh
-Welcome to Oracle Linux Server release 7.9 (GNU/Linux 5.4.17-2136.302.7.2.2.el7uek.x86_64)
+[vagrant@localhost ~]$
-...
+</pre>
+Canviem a l'usuari ''oracle'':
-[vagrant@oracle11g-xe-vagrant ~]$
+<pre>
+[vagrant@localhost ~]$ sudo su - oracle
-Canviem a l'usuari oracle:
+[oracle@localhost ~]
+</pre>
-$ sudo su - oracle
+Comprovem que els processos d'Oracle estan funcionant:
-[oracle@localhost ~]$
+<pre>
-Comprovem que el listener s'està executant:
 $ ps -ef |grep -i listener
-oracle     37468       1  0 17:07 ?        00:00:00 /opt/oracle/product/21c/dbhomeXE/bin/tnslsnr LISTENER -inherit
+oracle     37535       1  0 17:03 ?        00:00:00 /opt/oracle/product/21c/dbhomeXE/bin/tnslsnr LISTENER -inherit
-oracle     39989   39963  0 17:34 pts/0    00:00:00 grep --color=auto -i listener
 $ ps -ef |grep -i pmon
-oracle     39357       1  0 17:22 ?        00:00:00 xe_pmon_XE
+oracle     39510       1  0 17:19 ?        00:00:00 xe_pmon_XE
-oracle     39995   39963  0 17:35 pts/0    00:00:00 grep --color=auto -i pmon
+</pre>
+=Sessió de base de dades dins d'Oracle=
-I finalment ja podem entrar a la base de dades. L'aplicació CLI per comunicar-nos amb la base de dades és ''sqlplus'':
+I ara ja podem entrar dins de la base de dades amb l'aplicació client ''sqlplus'':
+<pre>
 $ sqlplus / as sysdba
+SQL*Plus: Release 21.0.0.0.0 - Production on Mon Feb 21 17:34:30 2022
-SQL*Plus: Release 21.0.0.0.0 - Production on Sat Feb 19 17:36:47 2022
 Version 21.3.0.0.0
 Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 Connected to:
@@ Línia 119: / Línia 101: @@
 SQL>
-SQL> select version from v$instance;
+</pre>
+Ara que ja estem a dins la base de dades, podem fer les operacions bàsiques:
-VERSION
+*crear tres taules
------------------
+*fer inserts sobre aquestes taules
-.0.0.0.0
+*fer una select per recuperar informació
-$ sqlplus system
-posem el password del system
-SQL> connect system/XkvDrsMflzM=1 as SYSDBA
-Connected.
-SELECT * FROM all_tables WHERE OWNER = 'system'
-ORDER BY table_name;
-====
-Sessió pràctica dins de l'Oracle:
-https://www.oracletutorial.com/getting-started/create-oracle-sample-database-for-practice
-https://www.oracletutorial.com/getting-started/oracle-sample-database
-https://www.oracletutorial.com/getting-started/create-oracle-sample-database-for-practice/
-http://saidulhaque.com/knowledgebase/article-30/how-to-create-new-pluggable-databases-in-oracle-19c#/
-$ mkdir /opt/oracle/oradata/XE/pdborcl/
-CREATE PLUGGABLE DATABASE pdborcl ADMIN USER pdborcl IDENTIFIED BY pdborcl
-FILE_NAME_CONVERT = ('/opt/oracle/oradata/XE/pdbseed/', '/opt/oracle/oradata/XE/pdborcl/');
-Pluggable database created
-SQL> alter pluggable database pdborcl OPEN;
-SQL> SHOW con_name;
-CON_NAME
-------------------------------
-CDB$ROOT
-SQL> ALTER SESSION SET CONTAINER = pdborcl;
-SQL> SHOW con_name;
-CON_NAME
-------------------------------
-PDBORCL
-SQL> CREATE USER alumne IDENTIFIED BY keiL2lai;
-SQL> GRANT CONNECT, RESOURCE, DBA TO alumne;
-SQL> CONNECT alumne@pdborcl
-Enter password:
-ERROR:
-ORA-12154: TNS:could not resolve the connect identifier specified
-===
-https://www.oracletutorial.com/getting-started/create-oracle-sample-database-for-practice/
-download the oracle sample database:
-Download Oracle Sample Database
-After downloading the file, you should extract it. The zip file contains the following *.sql files:
-     ot_create_user.sql is for creating OT user and grant privileges
-     ot_schema.sql is for creating database objects such as tables, constraints, etc.
-     ot_data.sql is for loading data into the tables.
-     ot_drop.sql is for removing all objects in the sample database.
+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'':
+<pre>
 CREATE TABLE regions
    (
@@ Línia 224: / Línia 146: @@
        ON DELETE CASCADE
    );
+</pre>
+Dins de ''ot_data.sql'' tenim les dades que interessen, els insert:
+<pre>
 Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe');
 Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas');
@@ Línia 283: / Línia 207: @@
 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');
+</pre>
+I ara ja podem fer una select:
-i ara ja podem fer una select:
+<pre>
+SQL> select region_name, country_name, city
-select region_name, country_name, city
 from regions r inner join countries using(region_id)
 inner join locations l using (country_id);
@@ Línia 301: / Línia 223: @@
 Oxford
 ...
+</pre>
+=Tasques i entrega=
+Has de reproduir el tutorial, i poder fer una sessió mínima de SQL amb la base de dades d'exemple.
-</pre>
+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.
 {{Autor}}, febrer 2022