Vagrant Boxes. Base de dades Oracle

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

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

vagrant init
cd al directori que vull la versió, per ex:
$ cd 11.2.0.2/
$ cd 19.3.0
aquí hi ha un Vagrantfile

I ara hem d'anar a cercar
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


$ cd 21.3.0-XE/

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



Bringing machine 'oracle21c-xe-vagrant' up with 'virtualbox' provider...
==> oracle21c-xe-vagrant: Checking if box 'oraclelinux/8' version '8.5.320' is up to date...
==> 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: Version 21.3.0.0.0
    oracle21c-xe-vagrant: 
    oracle21c-xe-vagrant: Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    oracle21c-xe-vagrant: 
    oracle21c-xe-vagrant: Connected to:
    oracle21c-xe-vagrant: Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
    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: INSTALLER: Installation complete, database ready to use!



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@oracle11g-xe-vagrant ~]$

Canviem a l'usuari oracle:

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

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     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     39995   39963  0 17:35 pts/0    00:00:00 grep --color=auto -i pmon

I finalment ja podem entrar a la base de dades. L'aplicació CLI per comunicar-nos amb la base de dades és ''sqlplus'':

$ sqlplus / as sysdba

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:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> select version from v$instance;

VERSION
-----------------
21.0.0.0.0

$ 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.



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
  );

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:


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
...

---

creat per Joan Quintana Compte, febrer 2022