Export EXP (legacy): the old export utility — produces a binary dump (superseded by Data Pump). EXPDP (Data Pump): produces binary dump files, used with DIRECTORY objects. 1exp user/password@host FULL=Y # full legacy (binary) export. 2expdp user/password@host FULL=Y DIRECTORY=DUMP DUMPFILE=full.dmp 1# full DB, excluding statistics 2nohup expdp 'system/<password>'@orcl FULL=Y DIRECTORY=DUMP \ 3 DUMPFILE=expdp_orcl_full_$(date +%Y-%m-%d).dmp \ 4 LOGFILE=expdp_orcl_$(date +%Y-%m-%d).log EXCLUDE=statistics 5 6# one schema 7nohup expdp system/<password>@orcl SCHEMAS=my_schema DIRECTORY=DUMP \ 8 DUMPFILE=my_schema_$(date +%Y-%m-%d)_%U.dmp \ 9 LOGFILE=my_schema.log EXCLUDE=statistics Directories & rights 1SET LINES 200 PAGES 2000 2SELECT * FROM dba_directories; -- the paths defined for Oracle. 1CREATE DIRECTORY my_dir AS '/backup/dump'; 2GRANT READ, WRITE ON DIRECTORY my_dir TO my_user; 3DROP DIRECTORY my_dir; Then use it in an export: ... DIRECTORY=my_dir DUMPFILE=my_export.dmp.
Sources & Docs Oracle-Base: DB 19c RAC installation on Oracle Linux 8 (VirtualBox)
Standards Keep every Oracle installation as uniform as possible (easier automation). The points below are all required.
Example migration: previous install RAC ONE NODE SE (Grid 19.0.0 + udev/ASM, DB 12.2.0.1) → new RAC Active/Active EE (Grid 19.3 + AFD/ASM, DB 19.10).
Users & groups 1grep oracle /etc/passwd # oracle:x:1521:1521:Oracle User For Database Binaries:/home/oracle:/bin/bash 2grep oinstall /etc/group # oinstall:x:1521:oracle 3grep dba /etc/group # dba:x:1522:oracle Filesystems & diskgroups /u01 — a dedicated 100G filesystem (binaries ~25G + full install ~15G). /tmp — minimum 4G. DATA — ~60G raw devices / disks. FRA — minimum 4 disks × 20G (or 40G), raw devices / disks. VOT — minimum one 5G disk for the voting disk. Network Single instance: minimum two interfaces (public + backup/NFS). RAC: three interfaces: 1DEVICE TYPE CONNECTION 2ens192 ethernet Admin 3ens224 ethernet Interconnect 4ens256 ethernet Backup One network interface for backups is required to mount an NFS share.
Concepts A tablespace (TBS) is a logical group of storage for data; each tablespace is made of one or more datafiles (.dbf), created on a disk (TBS = 1.dbf + 2.dbf + …). One datafile belongs to exactly one tablespace; a tablespace can have many datafiles. To grow a database you grow the datafiles of the required tablespace (which needs free space on the filesystem or ASM disk).
View tablespaces & datafiles 1SELECT * FROM dba_tablespaces; -- the tablespaces. 2SELECT * FROM dba_data_files; -- the datafiles. 3SELECT * FROM dba_temp_files; -- the temporary files. 1SELECT tablespace_name FROM dba_tablespaces; Size & max size of a tablespace (interactive):
Identify the instance 1echo $ORACLE_SID # the Oracle instance used before a SQL connection. 2echo $ORACLE_HOME # the Oracle home directory. Load the instance environment:
1. oraenv /etc/oratab:
1+ASM:/u01/oracle/base/product/12.2.0/grid:N 2ORCL:/u01/oracle/base/product/12.2.0/dbhome_1:N All running instances:
1ps -ef | grep pmon 2oracle 2201 1 0 12:02 ? 00:00:00 ora_pmon_ORCL 3oracle 30513 1 0 Feb12 ? 00:01:11 asm_pmon_+ASM 1ps -ef | grep ora_pmon | grep -v grep | awk '{print $NF}' | cut -d"_" -f3 With a Clusterware layer Check whether an Oracle Clusterware layer is present:
1ps -ef | grep d.bin 2# /u01/oracle/base/product/12.2.0/grid/bin/[ohasd|oraagent|evmd|ocssd].bin ... 1srvctl config database Listeners and processes:
Grid The grid is the component responsable for Clustering in oracle.
Grid (couche clusterware) -> ASM -> Disk Group - Oracle Restart = Single instance = 1 Grid (with or without ASM) - Oracle RAC OneNode = 2 instances Oracle in Actif/Passif with shared storage - Oracle RAC (Actif/Actif)
SCAN 1# As oracle user: 2srvctl config scan 3 4SCAN name: host-env-datad1-scan.domain, Network: 1 5Subnet IPv4: 192.168.228.0/255.255.255.0/ens192, static 6Subnet IPv6: 7SCAN 1 IPv4 VIP: 192.168.228.33 8SCAN VIP is enabled. 9SCAN VIP is individually enabled on nodes: 10SCAN VIP is individually disabled on nodes: 11SCAN 2 IPv4 VIP: 192.168.228.35 12SCAN VIP is enabled. 13SCAN VIP is individually enabled on nodes: 14SCAN VIP is individually disabled on nodes: 15SCAN 3 IPv4 VIP: 192.168.228.34 16SCAN VIP is enabled. 17SCAN VIP is individually enabled on nodes: 18SCAN VIP is individually disabled on nodes: Oracle Instance resources: 1# As oracle user 2srvctl config database 3srvctl config database -d <SID> 4srvctl status database -d <SID> 5srvctl status nodeapps -n host-env-datad1n1 6srvctl config nodeapps -n host-env-datad1n1 7# ============ 8srvctl stop database -d DB_NAME 9srvctl stop database -d DB_NAME -o normal 10srvctl stop database -d DB_NAME -o immediate 11srvctl stop database -d DB_NAME -o transactional 12srvctl stop database -d DB_NAME -o abort 13srvctl stop instance -d DB_NAME -i INSTANCE_NAME 14# ============= 15srvctl start database -d DB_NAME -n host-env-datad1n1 16srvctl start database -d DB_NAME -o nomount 17srvctl start database -d DB_NAME -o mount 18srvctl start database -d DB_NAME -o open 19# ============ 20srvctl relocate database -db DB_NAME -node host-env-datad1n1 21srvctl modify database -d DB_NAME -instance DB_NAME 22srvctl restart database -d DB_NAME 23# === Do not do it 24srvctl modify instance -db DB_NAME -instance DB_NAME_2 -node host-env-datad1n2 25srvctl modify database -d DB_NAME -instance DB_NAME 26srvctl modify database -d oraclath -instance oraclath Cluster resources 1crs_stat 2crsctl status res 3crsctl status res -t 4crsctl check cluster -all 5 6# Example how it should look: 7/opt/oracle/grid/12.2.0.1/bin/crsctl check cluster -all 8************************************************************** 9host-env-datad1n1: 10CRS-4535: Cannot communicate with Cluster Ready Services 11CRS-4529: Cluster Synchronization Services is online 12CRS-4534: Cannot communicate with Event Manager 13************************************************************** 14host-env-datad1n2: 15CRS-4537: Cluster Ready Services is online 16CRS-4529: Cluster Synchronization Services is online 17CRS-4533: Event Manager is online 18************************************************************** 1show parameter cluster 2 3NAME TYPE VALUE 4------------------------------------ ----------- ------------------------------ 5cdb_cluster boolean FALSE 6cdb_cluster_name string DB_NAME 7cluster_database boolean TRUE 8cluster_database_instances integer 2 9cluster_interconnects string Stop/start secondary node: 1-- Prevent Database to switch over 2ALTER database cluster_database=FALSE; 1# as root 2/u01/oracle/base/product/19.0.0/grid/bin/crsctl stop crs -f 3/u01/oracle/base/product/19.0.0/grid/bin/crsctl disable crs 4 5# Shutdown/startup VM or other actions 6 7# as root 8/u01/oracle/base/product/19.0.0/grid/bin/crsctl enable crs 9/u01/oracle/base/product/19.0.0/grid/bin/crsctl start crs Stop/Start properly DB on both nodes: 1# as oracle user 2srvctl stop database -d oraclath 3 4# As root user, on both nodes: 5/opt/oracle/grid/12.2.0.1/bin/crsctl stop crs -f 6/opt/oracle/grid/12.2.0.1/bin/crsctl disable crs 7 8# As root user, on both nodes: 9/opt/oracle/grid/12.2.0.1/bin/crsctl enable crs 10/opt/oracle/grid/12.2.0.1/bin/crsctl start crs 11 12# checks after restart 13ps -ef | grep asm_pmon | grep -v "grep" 14 15# if ASM is up and running 16srvctl start database -d oraclath -node host1-env-data1n1.domain Listner issue 1# As oracle user 2srvctl status scan_listener 3 4PRCR-1068 : Failed to query resources 5CRS-0184 : Cannot communicate with the CRS daemon. the solution:
General concern If you want to move VMs to an another Storage Domain, you need to copy the template from it as well!
Remove a disk:
1# IF RHV does not use anymore disk those should appear empty in lsblk: 2lsblk -a 3sdf 8:80 0 4T 0 disk 4└─36001405893b456536be4d67a7f6716e3 253:38 0 4T 0 mpath 5sdg 8:96 0 4T 0 disk 6└─36001405893b456536be4d67a7f6716e3 253:38 0 4T 0 mpath 7sdh 8:112 0 4T 0 disk 8└─36001405893b456536be4d67a7f6716e3 253:38 0 4T 0 mpath 9sdi 8:128 0 0 disk 10└─360014052ab23b1cee074fe38059d7c94 253:39 0 100G 0 mpath 11sdj 8:144 0 0 disk 12└─360014052ab23b1cee074fe38059d7c94 253:39 0 100G 0 mpath 13sdk 8:160 0 0 disk 14└─360014052ab23b1cee074fe38059d7c94 253:39 0 100G 0 mpath 15 16# find all disks from LUN ID 17LUN_ID="360014054ce7e566a01d44c1a4758b092" 18list_disk=$(dmsetup deps -o devname ${LUN_ID}| cut -f 2 |cut -c 3- |tr -d "()" | tr " " "\n") 19echo ${list_disk} 20 21# Remove from multipath 22multipath -f "${LUN_ID}" 23 24# remove disk 25for i in ${list_disk}; do echo ${i}; blockdev --flushbufs /dev/${i}; echo 1 > /sys/block/${i}/device/delete; done 26 27# You can which disk link with which LUN on CEPH side 28ls -l /dev/disk/by-* NFS for OLVM/oVirt Since oVirt need a shared stockage, we can create a local NFS to bypass this point if no Storage bay.
Oracle DB Diagram --- config: theme: forest layout: elk --- flowchart TD subgraph s1["Instance DB"] style s1 fill:#E8F5E9,stroke:#388E3C,stroke-width:2px subgraph s1a["Background Processes"] style s1a fill:#FFF9C4,stroke:#FBC02D,stroke-width:1px n5["PMON (Process Monitor)"] n6["SMON (System Monitor)"] n10["RECO (Recoverer Process)"] end subgraph s1b["PGA (Process Global Area)"] style s1b fill:#E3F2FD,stroke:#1976D2,stroke-width:1px n1["Processes"] end subgraph s1c["SGA (System Global Area)"] style s1c fill:#FFEBEE,stroke:#D32F2F,stroke-width:1px subgraph n7["Shared Pool (SP)"] style n7 fill:#F3E5F5,stroke:#7B1FA2,stroke-width:1px n7a["DC (Dictionary Cache)"] n7b["LC (Library Cache)"] n7c["RC (Result Cache)"] end n8["DB Cache (DBC)"] n9["Redo Buffer"] n3["DBWR (DB Writer)"] n4["LGWR (Log Writer)"] n5["PMON (Process Monitor)"] n6["SMON (System Monitor)"] n10["RECO (Recoverer Process)"] end end subgraph s2["Database: Physical Files"] style s2 fill:#FFF3E0,stroke:#F57C00,stroke-width:2px n11["TBS (Tablespaces, files in .DBF)"] n12["Redo Log Files"] n13["Control Files"] n14["SPFILE (Binary Authentication File)"] n15["ArchiveLog files"] end subgraph s3["Operating System"] style s3 fill:#E0F7FA,stroke:#00796B,stroke-width:2px n16["Listener (Port 1521)"] end n3 --> n11 n3 --> n7c n4 --> n12 n6 --> n7a s3 --> s1 s1c <--> n12 s1c <--> n13 s1c <--> n14 n7b <--> n7c classDef Aqua stroke-width:1px, stroke-dasharray:none, stroke:#0288D1, fill:#B3E5FC, color:#01579B classDef Yellow stroke-width:1px, stroke-dasharray:none, stroke:#FBC02D, fill:#FFF9C4, color:#F57F17 classDef Green stroke-width:1px, stroke-dasharray:none, stroke:#388E3C, fill:#C8E6C9, color:#1B5E20 classDef Red stroke-width:1px, stroke-dasharray:none, stroke:#D32F2F, fill:#FFCDD2, color:#B71C1C class n11,n12,n13,n14,n15 Aqua class n5,n6,n10 Yellow class n1 Green class n7,n8,n9,n3,n4 Red Explanation An Oracle server includes an Oracle Instance and an Oracle Database.
Hosted-engine Administration Connect to VM hosted-engine with root and password setup during the install: 1# Generate a backup 2engine-backup --scope=all --mode=backup --file=/root/backup --log=/root/backuplog 3 4# Restore from a backup on Fresh install 5engine-backup --mode=restore --file=file_name --log=log_file_name --provision-db --restore-permissions 6engine-setup 7 8# Restore a backup on existing install 9engine-cleanup 10engine-backup --mode=restore --file=file_name --log=log_file_name --restore-permissions 11engine-setup host Administration Connect in ssh to the Host: 1# Pass a host in maintenance mode manually 2hosted-engine --vm-status 3hosted-engine --set-maintenance --mode=global 4hosted-engine --vm-status 5 6# Remove maintenance mode 7hosted-engine --set-maintenance --mode=none 8hosted-engine --vm-status 9 10# upgrade hosted-engine 11hosted-engine --set-maintenance --mode=none 12hosted-engine --vm-status 13engine-upgrade-check 14dnf update ovirt\*setup\* # update the setup package 15engine-setup # launch it to update the engine /!\ Connect individually to KVM Virtmanager does not work OVirt use libvirt but not like KVM do…
Prerequisistes Check Compatibilty hardware: Oracle Linux Hardware Certification List (HCL)
A minimum of two (2) KVM hosts and no more than seven (7).
A fully-qualified domain name for your engine and host with forward and reverse lookup records set in the DNS.
/var/tmp 10 GB space at least
Prepared a shared-storage (nfs or iscsi) of at least 74 GB to be used as a data storage domain dedicated to the engine virtual machine. ISCSI need to be discovered before oVirt install.
Example 1# Import values with connection details 2. .\values.ps1 3 4$scriptFilePath ="$MyPath\Install\MysqlBase\Script.sql" 5 6# Load the required DLL file (depend on your connector) 7[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.23\Assemblies\v4.5.2\MySql.Data.dll") 8 9# Load in var the SQL script file 10$scriptContent = Get-Content -Path $scriptFilePath -Raw 11 12# Execute the modified SQL script 13$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ 14 ConnectionString = "server=$MysqlIP;uid=$MysqlUser;Port=3306;user id=$MysqlUser;pwd=$MysqlPassword;database=$MysqlDatabase;pooling=false;CharSet=utf8;SslMode=none" 15 } 16 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand 17 $sql.Connection = $Connection 18 $sql.CommandText = $scriptContent 19 write-host $sql.CommandText 20 $Connection.Open() 21 $sql.ExecuteNonQuery() 22 $Connection.Close()
