Joan a 16:53, 21 feb 2022

2022-02-21T16:53:42Z

Joan a 17:38, 19 feb 2022

2022-02-19T17:38:58Z

Joan: Es crea la pàgina amb «

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

2022-02-19T17:35:26Z

Es crea la pàgina amb «<pre> https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase https://pazikas.com/5-steps-to-use-oracle-database-with-vagrant/ git clone https://github.co...».

Pàgina nova

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

</pre>

{{Autor}}, febrer 2022

← Versió més antiga		Revisió del 17:38, 19 feb 2022
Línia 1:		Línia 1:
		+	[[Fitxer:Oracle21c-xe-vagrant.png \| thumbnail]]
	<pre>		<pre>
	https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase		https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase

Vagrant Boxes. Base de dades Oracle - Historial de revisió

Joan a 16:53, 21 feb 2022

Joan a 17:38, 19 feb 2022

Joan: Es crea la pàgina amb «
https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase https://pazikas.com/5-steps-to-use-oracle-database-with-vagrant/ git clone https://github.co...».