Baza: Oracle

Baza danych Oracle to zaawansowany system zarządzania relacyjnymi bazami danych, opracowany przez firmę Oracle Corporation. Jest powszechnie używany w przedsiębiorstwach i organizacjach na całym świecie ze względu na swoją niezawodność, wydajność i wszechstronność. Oracle oferuje pełne wsparcie dla standardów SQL, co ułatwia integrację z istniejącymi aplikacjami i narzędziami. Jego zaawansowane funkcje obejmują transakcyjność, zabezpieczenia, replikację danych, zarządzanie danymi w chmurze, oraz możliwość obsługi dużych ilości danych i zapytań równoległych. Dodatkowo, Oracle zapewnia szeroką gamę narzędzi administracyjnych i monitorowania, które ułatwiają zarządzanie bazą danych, w tym narzędzia do tworzenia kopii zapasowych, optymalizacji wydajności, zarządzania użytkownikami i kontrolowania dostępu. Dzięki swojej skalowalności i niezawodności, Oracle jest często wybierany do obsługi krytycznych aplikacji biznesowych, takich jak systemy zarządzania relacjami z klientami (CRM), systemy zarządzania zasobami przedsiębiorstwa (ERP) oraz systemy bankowości i finansów.

W niniejszym wpisie wykonamy instalację i konfigurację darmowej bazy danych oferowanej przez Oracle.

Założenia

Zakładam posiadanie podstawowej wiedzy o:
– docker
– cli
– client bazodanowy
– openssl

Akcja

Instalacja

1.Dodajemy do pliku /etc/hosts wpis kierujący nas do nowej domeny bazy danych.

127.0.0.1 oracle.e-strix.pl

2.Tworzymy strukturę katalogów wykorzystując docker compose

.
└── docker-compose.yml

3. Wypełniamy plik docker-compose.yml

version: '3.7'

services:
  oracle23:
    image: container-registry.oracle.com/database/free:latest
    container_name: oracle23
    expose:
      - 1521
      - 1532
    ports:
      - 1521:1521
      - 1532:1532

I uruchamiamy

docker-compose up

W rezultacie zobaczymy

oracle23    | Starting Oracle Net Listener.
oracle23    | Oracle Net Listener started.
oracle23    | Starting Oracle Database instance FREE.
oracle23    | Oracle Database instance FREE started.
oracle23    | 
oracle23    | The Oracle base remains unchanged with value /opt/oracle
oracle23    | #########################
oracle23    | DATABASE IS READY TO USE!
oracle23    | #########################
oracle23    | The following output is now a tail of the alert.log:
oracle23    | 2024-04-05T16:25:05.555839+00:00
oracle23    | ===========================================================
oracle23    | Dumping current patch information
oracle23    | ===========================================================
oracle23    | No patches have been applied
oracle23    | ===========================================================
oracle23    | 2024-04-05T16:25:06.759715+00:00
oracle23    | FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
oracle23    | Completed: Pluggable database FREEPDB1 opened read write 
oracle23    | Completed: ALTER DATABASE OPEN

Sprawdźmy teraz czy działa

telnet oracle.e-strix.pl 1521

W rezultacie zobaczymy

Trying 127.0.0.1...<br>
Connected to oracle.e-strix.pl.<br>
Escape character is '^]'.<br>
Connection closed by foreign host.

Konfiguracja

Logujemy się do kontenera docker

docker exec -it oracle23 bash

Tworzymy katalog w którym będziemy trzymać tzw. wallet.

mkdir -p /opt/oracle/wallet/

Tworzymy wallet

orapki wallet create -wallet "/opt/oracle/wallet" -pwd myWallet_321 -auto_login_local

Dodajemy certyfikat dla naszej domeny

orapki wallet add -wallet "/opt/oracle/wallet" -pwd myWallet_321 -dn "CN=oracle.e-strix.pl" -keysize 1024 -self_signed -validity 3650

Sprawdźmy teraz

orapki wallet display -wallet "/opt/oracle/wallet" -pwd myWallet_321

W wyniku czego powinniśmy zobaczyć

Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
User Certificates:
Subject:        CN=oracle.e-strix.pl
Trusted Certificates: 
Subject:        CN=oracle.e-strix.pl

Możemy teraz wyeksportować certyfikat z wallet’u

orapki wallet export -wallet "/opt/oracle/wallet" -pwd myWallet_321 -dn "CN=oracle.e-strix.pl" -cert /tmp/server-certificate.crt

Zacznijmy zmiany w plikach konfiguracyjnych

Plik /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora

# listener.ora Network Configuration File:


SSL_CLIENT_AUTHENTICATION = FALSE


WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/oracle/wallet)
    )
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/23c/dbhomeFree)
      (PROGRAM = extproc)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_FREE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 1532))
    )
  )


DEFAULT_SERVICE_LISTENER = (FREE)

Plik /opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora

Na
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
DISABLE_OOB=ON


WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /opt/oracle/wallet)
     )
   )


SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE

Plik /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File:

FREE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREE)
    )
  )

FREESSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 1532))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREE)
    )
  )

LISTENER_FREE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

FREEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

FREEPDB1SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 1532))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_FREE))
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
  )

Zrestartujmy serwis

lsnrctl stop
lsnrctl start

W efekcie zobaczymy

LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 05-APR-2024 16:58:23

Copyright (c) 1991, 2023, Oracle.  All rights reserved.

Starting /opt/oracle/product/23c/dbhomeFree/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 23.0.0.0.0 - Production
System parameter file is /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/7d1f9b0a99b8/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=0.0.0.0)(PORT=1532)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_FREE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date                05-APR-2024 16:58:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           FREE
Listener Parameter File   /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/7d1f9b0a99b8/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_FREE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=0.0.0.0)(PORT=1532)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Sprawdźmy czy słuchacze działają poprawinie

tnsping FREE

Co pokaże

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 05-APR-2024 17:02:22

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE)))
OK (0 msec)

I połączenia SSL

tnsping FREESSL

Co pokaże

TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 05-APR-2024 17:02:19

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/23c/dbhomeFree/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = 0.0.0.0)(PORT = 1532)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE)))
OK (0 msec)

Ok, wygląda dobrze. Teraz przygotujmy schemat bazy danych, który pozwoli nam na swobodną pracę.

Logujemy się do bazy danych jako administrator

sqlplus sys/oracle as SYSDBA

i ustawiamy kontekst. Co ważne w wersji darmowej możemy mieć tylko jeden ale nam to wystarczy.

alter session set container=FREEPDB1;

Tworzymy konto użytkownika/schemat

CREATE USER ESTRIX_SCHEMA IDENTIFIED BY password DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

I dodajemy niezbędne uprawnienia

GRANT CONNECT, RESOURCE TO ESTRIX_SCHEMA;

Teraz możemy wylogować z aplikacji sqlplus i sprawdzić czy użytkownik został poprawnie utworzony

sqlplus ESTRIX_SCHEMA/password@FREEPDB1

I sprawdźmy jaki protokół jest wykorzytywany

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

Powinien być

NETWORK_PROTOCOL
--------------------------------------------------------------------------------
tcp

Wylogujmy się i sprawdźmy połączenie SSL

sqlplus ESTRIX_SCHEMA/password@FREEPDB1SSL

I sprawdzamy protokół

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

Powinien być

NETWORK_PROTOCOL
--------------------------------------------------------------------------------
tcps

Sprawdzamy

Sprawdźmy pierw czy porty są otwarte i gotowe.

telnet oracle.e-strix.pl 1521

Widzimy

Trying 127.0.0.1...
Connected to oracle.e-strix.pl.
Escape character is '^]'.

I dla portu SSL

telnet oracle.e-strix.pl 1532

Widzimy

Trying 127.0.0.1...
Connected to oracle.e-strix.pl.
Escape character is '^]'.

Skorzystajmy teraz z programu openssl do sprawdzenia połączeń

openssl s_client -connect oracle.e-strix.pl:1521

W rezultacie widzimy

CONNECTED(00000005)
write:errno=0
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 319 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---

I dla połączenia SSL

openssl s_client -connect oracle.e-strix.pl:1532

Widzimy

CONNECTED(00000005)
depth=0 CN = oracle.e-strix.pl
verify error:num=18:self signed certificate
verify return:1
depth=0 CN = oracle.e-strix.pl
verify return:1
---
Certificate chain
 0 s:CN = oracle.e-strix.pl
   i:CN = oracle.e-strix.pl
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIBtjCCAR8CEAgwnvhW+8djMEvEBUqY5rowDQYJKoZIhvcNAQELBQAwHDEaMBgG
A1UEAxMRb3JhY2xlLmUtc3RyaXgucGwwHhcNMjQwNDA1MTY0MzI1WhcNMzQwNDAz
MTY0MzI1WjAcMRowGAYDVQQDExFvcmFjbGUuZS1zdHJpeC5wbDCBnzANBgkqhkiG
9w0BAQEFAAOBjQAwgYkCgYEAoMSdPU0x4L2FoHPRkOj09XH4+LHEBgF08bWPRX3Z
ChpEWAv7fUMvwe7rLRqkBG0AbXY4e6Bg3PX0GEYatgHOsbl7rHVNwWwuLGIRuaBJ
igxduwzm/8Vn7iWj/pJMA1HQy1yts+yPfr+5OWklo5UqkVCwuZXcQlYuuipYqRXE
mlECAwEAATANBgkqhkiG9w0BAQsFAAOBgQAZvuaaujfZ05/d1V2xY1gdWEH7IoJA
LqWahrM1aF264y877FiE96l+LiRIEQA6DBa66M5ldfOK+o1mVHLmibO0TVE95WfN
lZWF/VNXeFpxosvaVzGvXMBRS7Tf09uSWBVqLPHsaAlzv2zDmyD94SU9/uq/Xmio
sdo0l07m5BRLJQ==
-----END CERTIFICATE-----
subject=CN = oracle.e-strix.pl

issuer=CN = oracle.e-strix.pl

---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: ECDH, P-256, 256 bits
---
SSL handshake has read 996 bytes and written 751 bytes
Verification error: self signed certificate
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 1024 bit
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 18 (self signed certificate)
---
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: 4D6AF9FABF707D27C89103E282453083B169BFF7F4990063FE16919AF1339772
    Session-ID-ctx: 
    Resumption PSK: 3E86D872B4049557698DC8A4B942A3D0ABC15DC3D738755B4EE98A5075E0C0574FD1B97C7B517DECAE1EE0A276D7D402
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 3600 (seconds)
    TLS session ticket:
    0000 - a3 43 18 35 b2 06 15 9e-d5 38 8a 42 b7 00 35 a3   .C.5.....8.B..5.
    0010 - 5a 96 f6 37 f9 68 68 73-3f 7b 1e dc a6 a5 d6 d2   Z..7.hhs?{......

    Start Time: 1712337982
    Timeout   : 7200 (sec)
    Verify return code: 18 (self signed certificate)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: B4C0ACA8B210AF781A02CC1956F18CE00FA19918D8D9F25F576D738121222B6E
    Session-ID-ctx: 
    Resumption PSK: 98F8E74EDF8BD23B293CB770B6A40973FC2E6E6D9D50E89817E0BB169BDD6085DF8012E3868ACD3695EEB2D970B5D2C5
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 3600 (seconds)
    TLS session ticket:
    0000 - c4 ea 3a d4 02 ee 9c 54-10 7b bd ab 26 f7 58 97   ..:....T.{..&.X.
    0010 - b6 68 fe de 60 ec be 57-c1 3e 5d ae 7a af d0 a9   .h..`..W.>].z...

    Start Time: 1712337982
    Timeout   : 7200 (sec)
    Verify return code: 18 (self signed certificate)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK

Jest to certyfikat typu self-sign zatem żeby móc skorzystać z tego połączenia powinniśmy pobrać go i dodać do używanego truststor’a w naszych aplikacjach.

Aby to zrobić musimy pobrać certyfikat

openssl s_client -connect oracle.e-strix.pl:1532 </dev/null 2>/dev/null | sed -ne '/-BEGIN CERTIFICATE/,/END CERTIFICATE/p' > certificate.pem

Zmienić format na der

openssl x509 -outform der -in certificate.pem -out certificate.der

I dodajemy do truststore

keytool -import -alias oracle.e-strix.pl -keystore truststore.jks -file certificate.der

Teraz możemy dodać nasz trust store do ulubionej aplikacji developerskiej i połączyć się wykorzystując JDBC url

dla połączenia bezpośredniego

username: ESTRIX_SCHEMA
password: password
JDBC url: jdbc:oracle:thin:@//oracle.e-strix.pl:1521/FREEPDB1

oraz dla połączenia SSL

username: ESTRIX_SCHEMA
password: password
JDBC url: jdbc:oracle:thin:@(description=(address=(protocol=tcps)(host=oracle.e-strix.pl)(port=1532))(connect_data=(service_name=FREEPDB1)))

Pomocne

Konfiguracja trust store dla SQLDeveloper.

Edytujemy plik SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf

Dodajemy klucze

AddVMOption -Djavax.net.ssl.trustStore=[PATH]/truststore.jks
AddVMOption -Djavax.net.ssl.trustStoreType=JKS
AddVMOption -Djavax.net.ssl.trustStorePassword=password

Dodanie własnego table space

CREATE USER USER_KAMIL IDENTIFIED BY USER_PASSWORD;
GRANT CONNECT, RESOURCE TO USER_KAMIL;
CREATE TABLESPACE TS_UTILS_DATA DATAFILE 'TS_UTILS_DATA.DBF' SIZE 120M AUTOEXTEND ON NEXT 20M MAXSIZE 2048M;
CREATE TABLESPACE TS_UTILS_IDX DATAFILE 'TS_UTILS_IDX.DBF' SIZE 20M AUTOEXTEND ON NEXT 20M MAXSIZE 2048M;
ALTER USER USER_KAMIL DEFAULT TABLESPACE TS_UTILS_DATA;
ALTER USER USER_KAMIL QUOTA UNLIMITED ON TS_UTILS_DATA;
ALTER USER USER_KAMIL QUOTA UNLIMITED ON TS_UTILS_IDX;

GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO USER_KAMIL;
GRANT SELECT ON SYS.PENDING_TRANS$ TO USER_KAMIL;
GRANT SELECT ON SYS.DBA_2PC_PENDING TO USER_KAMIL;
GRANT EXECUTE ON SYS.DBMS_XA TO USER_KAMIL;
GRANT FORCE ANY TRANSACTION TO USER_KAMIL;

Mierzenie rozmiaru tabel

Poszczególne tabele

SELECT
   (BYTES)/1024/1024 as size_in_mega,
   segment_name
FROM
   user_segments
ORDER BY size_in_mega DESC;

Całość

SELECT
   SUM(BYTES)/1024/1024 as size_in_mega
FROM
   user_segments;

Eksport bazy

exp USER/password@//127.0.0.1:1521/XE FULL=y FILE=dump.dmp GRANTS=y ROWS=y

Import bazy

imp USER/password@//127.0.0.1:1521/XE FILE=dump.dmp

Paginacja

select * from TABLE OFFSET 1 ROWS FETCH NEXT 15 ROWS ONLY;