Oracle-Datenbank

Know-How:

  • /etc/oratab: Enthält eine Liste der Oracle-Datenbankinstanzen auf einem bestimmten Host, in der Form <INSTANZNAME>:<ORACLE_HOME>:<AUTOSTART_FLAG> (Name der Oracle-Datenbankinstanz:Pfad zum Oracle Home-Verzeichnis:Instanz automatisch beim Systemstart starten).

  • /etc/tnsnames.ora: Enthält Informationen über die Standorte von Oracle-Datenbankinstanzen, die über das Oracle Net Services-Protokoll erreichbar sind. Diese Datei wird normalerweise auf dem Client-Rechner (nicht auf dem Datenbankserver) eingerichtet und ermöglicht es Clients, Verbindungen zu Oracle-Datenbanken herzustellen.

  • ASM: Automatic Storage Management.

  • CDB: Multi-Tenant Container Database. Kann aus mehreren PDBs bestehen.

  • DBCA: Database Configuration Assistant.

  • DBW: Database Writer Process.

  • FRA: Fast Recovery Area.

  • LGWR: Log Writer.

  • PDB: Pluggable Database (Default-Datenbank bei XE: „XEPDB1“), seit Oracle Database 12c. Die Pluggable Database (PDB) ist eine Möglichkeit, mehrere Datenbanken in einer gemeinsamen Datenbankinstanz zu hosten, wodurch Ressourcen effizienter genutzt und die Verwaltung vereinfacht wird. Jede PDB verhält sich wie eine eigenständige Datenbank mit eigenen Benutzern und Schemas, aber sie teilen sich die gleiche Datenbankinstanz.

  • PGA: Process Global Area.

  • RAC: Real Application Clusters.

  • SAME: Stripe and Mirror Everything.

  • SCN: System Change Number.

  • SGA: System Global Area. Prozessübergreifende Speicherbereiche. Dort stecken beispielsweise der Database Buffer Cache und der Redo Log Buffer.

  • SID: System Identifier. Der SID ist ein eindeutiger Name oder Schlüssel, der eine bestimmte Oracle-Datenbankinstanz identifiziert. Jede Oracle-Datenbankinstanz auf einem Server muss einen eindeutigen SID haben, damit sie korrekt unterschieden und angesprochen werden kann.
    Neben dem SID gibt es auch den sogenannten „Service Name“. Der Service Name ist ein weiterer Identifikator für eine Oracle-Datenbankinstanz. Der Service Name wird in moderneren Oracle-Umgebungen häufiger empfohlen, da er flexibler ist.
  • XE: Die „Express-Edition“ ist eine kostenlose, limitierte Version der Oracle Database. Datenbank-Speicherlimit von 11 GB, nutzt nur 1x CPU-Core, verwendet bis max. 2 GB RAM, nur eine Instanz möglich, max. 20 concurrent User, und weniger Funktionen.

Installation Oracle XE auf RHEL 8

Der Server sollte über mindestens 15 GB freien Plattenplatz verfügen. Zusätzlich muss der Server beim Auflösen seines FQDNs eine „gültige“ IP-Adresse erhalten (127.0.0.1 genügt nicht). Wer dafür keinen DNS konfigurieren kann, kann die /etc/hosts anpassen. Beispiel:

/etc/hosts
192.0.2.74 orcl.example.com

Oracle Database 21c Express Edition (XE) für Linux x64 (RHEL 8):

VER=21c-1.0-1
dnf -y install https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-$VER.el8.x86_64.rpm
dnf -y install https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-$VER.ol8.x86_64.rpm

Die Installation erfolgt in /opt/oracle/product/21c/dbhomeXE. Datenbank-Instanz konfigurieren lassen und Geduld mitbringen:

/etc/init.d/oracle-xe-21c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Enter SYS user password:
********
Enter SYSTEM user password:
*******
Enter PDBADMIN User Password:
*********
Prepare for db operation
Copying database files
Creating and starting Oracle instance
Completing Database Creation
Creating Pluggable Databases
Executing Post Configuration Actions
Running Custom Scripts
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/XE.
Database Information:
Global Database Name:XE
System Identifier(SID):XE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details.

Connect to Oracle Database using one of the connect strings:
     Pluggable database: walle.linuxfabrik.loc:1539/XEPDB1
     Multitenant container database: walle.linuxfabrik.loc:1539
Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE

Der Oracle TNS Listener tnslsnr-Service (Transparent Network Substrate Listener) hört anschliessend auf Port 1539/tcp, die Datenbank selbst ist auf orcl.example.com:1539/XEPDB1 erreichbar. Auf https://localhost:5500/em läuft der Oracle Enterprise Manager for Oracle Database XE.

Datei /etc/oratab kontrollieren/anpassen.

Oracle XE beim Reboot starten lassen:

systemctl enable --now oracle-xe-21c.service

Oracle TNS Listener manuell starten/stoppen: /etc/init.d/oracle-xe-21c start etc.

SQLPlus so ändern, dass unter RHEL die Cursor-Tasten für die Suche in der History sauber unterstützt werden:

dnf -y install rlwrap

cp /opt/oracle/product/21c/dbhomeXE/bin/sqlplus /opt/oracle/product/21c/dbhomeXE/bin/sqlplusplus
chown oracle:oinstall /opt/oracle/product/21c/dbhomeXE/bin/sqlplusplus
echo 'rlwrap sqlplusplus $@' > /opt/oracle/product/21c/dbhomeXE/bin/sqlplus

Umgebungsvariablen setzen:

su - oracle
~/.bash_profile
# mandatory
export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
export ORACLE_SID=XE
export PATH=$PATH:/opt/oracle/product/21c/dbhomeXE/bin/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/oracle/product/21c/dbhomeXE/lib/

# optional
export NLS_DATE_FORMAT="yyyy-mm-dd:hh24:mi:ss"
export NLS_LANG=american_america.al32utf8
source ~/.bash_profile

Status des Oracle Listeners prüfen:

lsnrctl status

Tipp

Der Connect in Oracle SQL Developer klappt im Beispiel mit folgenden Angaben:

  • Username: SYS, Role: SYSDBA

  • Password: linuxfabrik

  • Hostname: orcl.example.com

  • Port: 1539

  • SID: XE

Verbinden mit SQLPlus:

sqlplus / as sysdba

Test: Liste der Pluggable Databases in der XE-Container-Datenbank auslesen:

select pdb_name from dba_pdbs;
exit

Datenbank auf der Kommandozeile erzeugen

Im Beispiel wird eine SID / Datenbank namens „BULK“ mit dem Database Configuration Assistant Tool dbca erzeugt. Die dafür notwendigen Datenbank-Templates sind unter /opt/oracle/product/*/dbhomeXE/assistants/dbca/templates/ gespeichert. Die zugehörigen Oracle-Instanzen werden ebenfalls erzeugt (was einige Minuten Zeit und etwas mehr als 1 GB Plattenplatz in Anspruch nehmen kann), inkl. Eintrag in die /etc/oratab.

su - oracle

DBNAME=BULK
dbca \
    -characterSet AL32UTF8 \
    -createDatabase \
    -emConfiguration LOCAL
    -gdbname $DBNAME \
    -memoryPercentage 30 \
    -responseFile NO_VALUE \
    -sid $DBNAME \
    -silent \
    -templateName General_Purpose.dbc

Datenbank bei Hochfahren des Servers starten (Eintrag von „N“ auf „Y“ ändern):

/etc/oratab
BULK:/opt/oracle/product/21c/dbhomeXE:Y

/etc/tnsnames.ora ergänzen, damit Client-Tools die Datenbank erreichen können:

Variante 1: /etc/tnsnames.ora per SID
BULK  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = server.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SID = BULK)
    )
    (HS = OK)
  )
Variante 2: /etc/tnsnames.ora per Service Name (hier steht „BULK“ sowohl für den Service Name als auch für die SID)
BULK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = server.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bulk)
    )
  )

Nun noch einen Benutzer für die Datenbank anlegen, der DBA-Rechte hat und somit die DB administrieren darf:

su - oracle

sqlplus /nolog
connect sys@BULK as SYSDBA

-- USER
create user bulk identified by "linuxfabrik" default tablespace "users" temporary tablespace "temp";

-- ROLES
grant "dba" to bulk;
alter user bulk default role "dba";

-- SYSTEM PRIVILEGES
grant unlimited tablespace to bulk;

quit

sqlplus

Um sqlplus nutzen zu können, auf der Kommandozeile in den „oracle“-Benutzer wechseln:

su - oracle

Verbindung zur Datenbank - Variante 1:

export ORACLE_SID=mydb
sqlplus / as sysdba

Verbindung zur Datenbank - Variante 1:

sqlplus /nolog
connect sys@mydb as SYSDBA

Benutzer mit sqlplus auf der Kommandozeile anlegen:

create user myuser identified by "linuxfabrik";

Benutzer-Passwort ändern:

alter user myuser identified by "myNEWpass";

Tipp

Ablauf von Kennwörtern: Damit Kennwörter der Datenbank-Benutzer nicht ablaufen, passt man die Standardeinstellung von Oracle an (im Beispiel für den DB-Benutzer „BULK“):

alter profile default limit failed_login_attempts unlimited password_life_time unlimited;

Rollen zuteilen und entfernen (Beispiel-Rollen sind „create session“, „connect“, „select_catalog_role“ usw.):

revoke create session from myuser;
grant connect to myuser;

Test des Benutzer-Logins:

connect myuser/linuxfabrik

SQL-Plus und dessen Verwendung in einem Bash-Script:

#!/usr/bin/env bash

export ORACLE_SID=mydb

sqlplus / as sysdba <<EOF
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
EOF

Tipp

Die History wird in $HOME/.sqlplus_history gespeichert.

Log-Dateien

Verbindungsversuche zum TNS-Listener:

tail -f /opt/oracle/diag/tnslsnr/*/listener/trace/listener.log

Datenbank in ARCHIVELOG-Modus setzen

sqlplus
archive log list

shutdown immediate
startup mount

alter database archivelog;
archive log list
alter database open;

Anschliessend Datenbank sichern.

Redo-Logs

sqlplus
select group#, status, member from v$logfile;
alter database add logfile member '/u01/app/oracle/fast_recovery_area/mydb/redo03.log' to group 7;

# repeat until all are ACTIVE
alter system switch logfile;
select * from v$log;

Backup Know-How

Begriffe:

  • Whole database backup: Alle Data-Files plus mindestens 1 Control-File

  • Partial database backup: 0 oder mehr Tablespaces und 0 oder mehr Data-Files

  • Full Backup: Kopie jedes Data Blocks erstellen

  • Incremental Backup:

    • Level 0: Full Backup, das als „Level 0“ gekennzeichnet wurde. Die Basis für nachfolgende inkrementelle Backups.

    • Cumulative Level 1: Inkrementelles Backup - enthält alle Änderungen seit dem letzten Level 0-Backup.

    • Differential Level 1: Inkrementelles Backup - enthält alle Änderungen seit dem letzten inkrementellen Backup (egal was für eins). Default in RMAN.

  • Offline backup: Datenbank ist zum Zeitpunkt geschlossen

  • Online backup: Das Gegenteil. Gefahr von Inkonsistenzen.

Typen:

  • Image copy: Data- oder Log-Files wurden kopiert.

  • Backup set: RMAN-spezifische Sammlung von Dateien wie Data-Files, Control-Files usw.; leere Blöcke werden nicht gespeichert. Ist auch ein Full Backup.

  • Proxy copies

Disk-Backups sollten immer in der Fast Recovery Area (FRA) liegen.

Backup mit dem Recovery Manager (RMAN)

RMAN wird für physische Sicherung eingesetzt, und ist die empfohlene Art, um Datenbanken zu sichern. Die RTO liegt bei Stunden oder Tagen. RMAN stellt folgende Dateitypen aus dem Backup wieder her:

  • Alle Data-Dateien (die Datenbanken)

  • Tablespaces

  • Control files

  • Archived redo logs

  • Server parameter files

Mit SYSDBA-Privilegien zur lokalen DB verbinden:

export ORACLE_SID=mydb
rman target "'/ as sysbackup'"  # individual PDB
BACKUP AS BACKUPSET FORMAT '/backup/df_%d_%s_%p.bus' TABLESPACE mydb1; # creating backup sets
BACKUP AS COPY DATAFILE '/u01/mydb.dbf';
BACKUP AS COPY ARCHIVELOG LIKE '/u01/arch%';
BACKUP COPY OF DATABASE;  # image copy
BACKUP DATABASE; # PDB: backup data files; CDB: all PDBs, controlfile, SPFILE
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP TABLESPACE mydb1, mydb2:mytablspace;
BACKUP PLUGGABLE DATABASE mydb1, mydb2;
BACKUP PLUGGABLE DATABASE "CDB$ROOT";

# runs for six hours max., run it until it is finished to get a full backup
BACKUP DATABASE NOT BACKED UP SINCE `SYSDATE-3` DURATION 06:00 PARTIAL MINIMIZE TIME;

# incremental backup at level 0:
BACKUP INCREMENTAL LEVEL 0 DATABASE;

# differential incremental backup:
BACKUP INCREMENTAL LEVEL 1 DATABASE;

# cumulative incremental backup:
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
CONFIGURE DEFAULT DEVICE TYPE DISK BACKUP TYPE TO COPY;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE <device> PARALLELISM 3;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE RETENTION POLICY TO NONE;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF <days> DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY <copies>;

LIST BACKUP;

# use data files and redo-logs:
RECOVER DATABASE;
RECOVER PLUGGABLE DATABASE mydb1;
RECOVER TABLESPACE system;

# use files from backup:
RESTORE DATABASE;
RESTORE TABLESPACE system;

SHOW ALL;
SHOW CONTROLFILE AUTOBACKUP;
SHOW RETENTION POLICY;

SPOOL LOG OFF;
SPOOL LOG TO <file>;

Job Commands:

RUN {
    ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT "/disk2/%U";
    BACKUP AS BACKUPSET DATABASE;
    SQL 'alter system archive log current';
}

RMAN und dessen Verwendung in einem Bash-Script:

export ORACLE_SID=mydb

rman target "'/ as sysbackup'" <<EOF
BACKUP DATABASE;
delete noprompt obsolete;
exit;
EOF

Die Fast Recovery Area Backup Location findet man per show parameter db_recovery_file_de;.

Siehe auch https://www.oracle.com/webfolder/technetwork/de/community/dbadmin/tipps/rman_i_backup/index.html

Backup mit exp

Alternativ mit einem einfachen Shell-Script und dem älteren exp (welches seit Oracle 10g durch das leistungsfähigere expdp, dem „Data Pump Export“ ersetzt werden kann):

#!/usr/bin/env bash

# we need to run our .profile to ensure all the environment variables are set
# jobs submitted through cron do not run our .profile by default (needed by oracle's exp)
. /root/.bash_profile

log() { logger -p "user.debug" -t "$(basename $0)" "$@"; echo "$@"; }

DBs=( system/user@DB01 system/user@DB02 )
OWNERs=( "owner1,owner_group1" "owner2,owner_group2" )
FILEs=( DB01 DB02 )

BACKUP_DIR=/backup/oracle-dump
LOG_DIR=/backup/oracle-dump

mkdir -p $BACKUP_DIR

# app paths
ORACLEDUMP=/opt/oracle/product/21c/dbhomeXE/bin/exp

cnt_dbs=${#DBs[@]}
for (( i = 0 ; i < $cnt_dbs ; i++ ))
do
    $ORACLEDUMP "${DBs[$i]}" owner="${OWNERs[$i]}" consistent=y file=$BACKUP_DIR/"${FILEs[$i]}".dmp log=$LOG_DIR/"${FILEs[$i]}".log
    log "$(hostname --short)::$(basename $0) - Oracle Database ${FILEs[$i]} dumped to $BACKUP_DIR/${FILEs[$i]}.dmp"
done

SQL

SELECT
    sysdate "Date"
FROM
    dual;

SELECT
    round(col * 1.155) - col "New Col",
FROM
    table
WHERE
    name LIKE '&user_input';

SELECT
    col, CASE col
    WHEN 'value1' THEN 'output1'
    WHEN 'value2' THEN 'output2'
    ELSE 'value3' END
FROM
    table;

# alternativ (und kompatibel zu MySQL/MariaDB):
SELECT
    col, CASE
    WHEN col = 'value1' THEN 'output1'
    WHEN col = 'value2' THEN 'output2'
    ELSE 'value3' END
FROM
    table;

# alternativ:
SELECT
    col, decode(col,
    'value1', 'output1',
    'value2', 'output2',
    'value3')
FROM
    table;

SAVEPOINT mysp;
ROLLBACK TO mysp;

Funktionen:

  • add_months(col, 6)

  • initcap(col)

  • length(col)

  • lower(col)

  • lpad(col, length, char)

  • months_between(sysdate, mydatecol)

  • next_day(col, ‚monday‘)

  • nvl(col, ‚string‘) - replace null value (returned as a blank) with a string

  • round(col, stellen)

  • substr(col, start, length)

  • sysdate - mydatecol

  • to_char(col,

    • ‚fm$99,999.00‘

    • ‚fmDay‘ = Monday

    • ‚Dsdp‘ = ergint ausgeschriebene Nummer

    • ‚YYYY‘

  • trunc(col, length)

  • upper(col)

Clients

Oracle SQL Developer

RPM findet sich auf https://www.oracle.com/database/sqldeveloper/technologies/download/ (Login mit Oracle-Account nötig).

Startup unter RHEL 6

Dieses generische init-Skript startet und stoppt mit Hilfe von dbstart und dbshut alle in /etc/oratab definierten Datenbanken unter dem „oracle“-Benutzer.

/etc/init.d/oracle
#!/bin/sh
# chkconfig: 3 99 10
# description: Oracle auto start-stop script.

ORA_OWNER=oracle

case $1 in
start)
    su - $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
    touch /var/lock/subsys/oracle
    ;;
stop)
    su - $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
    rm -f /var/lock/subsys/oracle
    ;;
restart)
    su - $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
    rm -f /var/lock/subsys/oracle
    su - $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
    touch /var/lock/subsys/oracle
    ;;
esac
chmod 750 /etc/init.d/oracle
chkconfig --level 3 oracle on
su - oracle
mkdir -p /home/oracle/scripts
/home/oracle/scripts/startup.sh
#!/bin/bash

ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Start Listener
lsnrctl start

$ORACLE_HOME/bin/dbstart $ORACLE_HOME
/home/oracle/scripts/shutdown.sh
#!/bin/bash

ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

$ORACLE_HOME/bin/dbshut $ORACLE_HOME

# Stop Listener
lsnrctl stop
chown -R oracle.oinstall /home/oracle/scripts
chmod u+x /home/oracle/scripts/*.sh

Built on 2024-02-28