Archives

2023

August 1, 2024 3 min read Docs

Inside a Shell script One line command: 1# Set the SID 2ORAENV_ASK=NO 3export ORACLE_SID=orcl 4. oraenv 5 6# Trigger oneline command 7echo -e "select inst_id, instance_name, host_name, database_status from gv\$instance;" | sqlplus -S / as sysdba In bash script: 1su - oracle -c ' 2export SQLPLUS="sqlplus -S / as sysdba" 3export ORAENV_ASK=NO; 4export ORACLE_SID='${SID}'; 5. oraenv | grep -v "remains"; 6 7${SQLPLUS} <<EOF2 8set lines 200 pages 2000; 9select inst_id, instance_name, host_name, database_status from gv\$instance; 10exit; 11EOF2 12 13unset ORAENV_ASK; 14' Inside SQL Prompt 1-- with an absolute path 2@C:\Users\Matthieu\test.sql 3 4-- or trigger from director on which sqlplus was launched 5@test.sql 6 7-- START syntax possible as well 8START test.sql Variables usages 1-- User variable (if not define, oracle will prompt) 2SELECT * FROM &my_table; 3 4-- Prompt user to set a variable 5ACCEPT my_table PROMPT "Which table would you like to interrogate ? " 6SELECT * FROM $my_table; Some Examples Example of Shell script to launch sqlplus command: 1export ORACLE_SID=SQM2DWH3 2 3echo "connect ODS/ODS 4BEGIN 5ODS.PURGE_ODS.PURGE_LOG(); 6ODS.PURGE_ODS.PURGE_DATA(); 7END; 8/" | sqlplus /nolog 9 10echo "connect DSA/DSA 11BEGIN 12DSA.PURGE_DSA.PURGE_LOG(); 13DSA.PURGE_DSA.PURGE_DATA(); 14END; 15/" | sqlplus /nolog Example of script to check tablespaces.sh 1#!/bin/ksh 2 3sqlplus -s system/manager <<! 4SET HEADING off; 5SET PAGESIZE 0; 6SET TERMOUT OFF; 7SET FEEDBACK OFF; 8SELECT df.tablespace_name||','|| 9 df.bytes / (1024 * 1024)||','|| 10 SUM(fs.bytes) / (1024 * 1024)||','|| 11 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1)||','|| 12 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) 13 FROM dba_free_space fs, 14 (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df 15 WHERE fs.tablespace_name (+) = df.tablespace_name 16 GROUP BY df.tablespace_name,df.bytes 17 ORDER BY 1 ASC; 18quit 19! 20 21exit 0 1#!/bin/ksh 2 3sqlplus -s system/manager <<! 4 5set pagesize 60 linesize 132 verify off 6break on file_id skip 1 7 8column file_id heading "File|Id" 9column tablespace_name for a15 10column object for a15 11column owner for a15 12column MBytes for 999,999 13 14select tablespace_name, 15'free space' owner, /*"owner" of free space */ 16' ' object, /*blank object name */ 17file_id, /*file id for the extent header*/ 18block_id, /*block id for the extent header*/ 19CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 20from dba_free_space 21where tablespace_name like '%TEMP%' 22union 23select tablespace_name, 24substr(owner, 1, 20), /*owner name (first 20 chars)*/ 25substr(segment_name, 1, 32), /*segment name */ 26file_id, /*file id for extent header */ 27block_id, /*block id for extent header */ 28CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 29from dba_extents 30where tablespace_name like '%TEMP%' 31order by 1, 4, 5 32/ 33 34quit 35! 36 37exit 0 SPOOL to write on system from sqlplus: 1SQL> SET TRIMSPOOL on 2SQL> SET LINESIZE 1000 3SQL> SPOOL /root/output.txt 4SQL> select RULEID as RuleID, RULENAME as ruleName,to_char(DBMS_LOB.SUBSTR(EPLRULESTATEMENT,4000,1() as ruleStmt from gep_rules; 5SQL> SPOOL OFF from script.sql: 1SET TRIMSPOOL on 2SET LINESIZE 10000 3SPOOL resultat.txt 4ACCEPT var PROMPT "Which table do you want to get ? " 5SELECT * FROM &var; 6SPOOL OFF Generate DATA Duplicate table to fill up tablespace or generate fake data: 1SQL> Create table emp as select * from employees; 2SQL> UPDATE emp SET LAST_NAME='ABC'; 3SQL> commit;

August 1, 2024 2 min read Docs

📦 Export / Import (Data Pump)

Memo Databases

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.

August 1, 2024 5 min read Docs

🔧 Installation

Memo Databases

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.