Aug - 2023 - Archives - Bałtyk Blog

2023

August 4, 2024 2 min read Docs

Enable auditing 1ALTER SYSTEM SET audit_trail = DB, EXTENDED SCOPE = SPFILE; -- detailed user actions. 2SHOW PARAMETER audit_trail; -- default NONE → set it to EXTENDED where possible. 3SHOW PARAMETER audit; -- the full audit configuration. Audit users 1AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY hr BY SESSION; 2AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY hr BY SESSION WHENEVER SUCCESSFUL; 3AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY hr BY SESSION WHENEVER NOT SUCCESSFUL; 4AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY hr BY ACCESS; 5AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY hr BY ACCESS WHENEVER SUCCESSFUL; 6AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE BY hr BY ACCESS WHENEVER NOT SUCCESSFUL; 7 8AUDIT ALL BY ACCESS; -- alternatively, audit everything. Audit tables 1AUDIT SELECT, INSERT, UPDATE ON hr.employees BY SESSION; 2AUDIT SELECT, INSERT, UPDATE ON hr.employees BY SESSION WHENEVER SUCCESSFUL; 3AUDIT SELECT, INSERT, UPDATE ON hr.employees BY SESSION WHENEVER NOT SUCCESSFUL; 4AUDIT SELECT, INSERT, UPDATE ON hr.employees BY ACCESS; 5AUDIT SELECT, INSERT, UPDATE ON hr.employees BY ACCESS WHENEVER SUCCESSFUL; 6AUDIT SELECT, INSERT, UPDATE ON hr.employees BY ACCESS WHENEVER NOT SUCCESSFUL; View the audit trail 1SELECT * FROM dba_audit_trail WHERE username = 'HR'; -- the audited actions of a user. 2SELECT * FROM dba_stmt_audit_opts; -- the user-level audits enabled. 3SELECT * FROM dba_obj_audit_opts; -- the object-level audits enabled.

August 4, 2024 1 min read Docs

🔄 Data Guard

Memo Databases

Synchronisation mechanism between two databases in Active/Passive. Switchover 1dgmgrl sys@orcl 2DGMGRL> switchover to 'orcl'; Check primary / standby 1echo -e "set heading off;\n select database_role FROM v\$database;" | sqlplus -S / as sysdba 2# PHYSICAL STANDBY (or PRIMARY) 3 4echo -e "set heading off;\n select open_mode FROM v\$database;" | sqlplus -S / as sysdba 5# MOUNTED (a standby is mounted, not open) PRIMARY + READ WRITE → primary. PHYSICAL STANDBY + MOUNTED → standby.

August 3, 2024 4 min read Docs

🔁 Redo Log & Archivelog

Memo Databases Storage

Redo log principles The redo log files keep a trace of every data alteration, so that after a crash they can replay the changes. You need at least two, and they deserve careful attention for both backup and access optimisation. In ARCHIVELOG mode the redo logs are archived — keeping a full trace of all changes, not just what fits within the redo log file size. The redo buffer is flushed to disk when it is full, so the redo log files should be at least as large as the redo log buffer (log_buffer).

August 3, 2024 4 min read Docs

💾 Backup & Recovery (RMAN)

Memo Databases Storage

Connect 1rman 2RMAN> connect target 1rman target / With a recovery catalog: 1rman target sys/<pwd>@orcl catalog repo/<pwd>@rmancat 1RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; -- enables restoring the CONTROLFILE. 2RMAN> SHOW ALL; -- the whole RMAN configuration. Backup 1RMAN> BACKUP DATABASE; -- full backup. 2RMAN> BACKUP DATABASE PLUS ARCHIVELOG; -- full + archived logs. 3RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; -- level 0 = baseline. 4RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; -- level 1 = incremental. 5RMAN> BACKUP CUMULATIVE INCREMENTAL LEVEL 1 DATABASE; -- cumulative increments. 6RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE; -- compressed full backup. 7RMAN> BACKUP ARCHIVELOG UNTIL TIME 'sysdate - 1/24' ALL DELETE INPUT; Run a script:

August 2, 2024 2 min read Docs

🧪 Invalid Objects

Memo Databases

Find invalid objects 1COL owner FOR a20 2COL object_name FOR a50 3COL subobject_name FOR a30 4 5SELECT owner, object_name, subobject_name, object_type, created 6FROM dba_objects WHERE status <> 'VALID' ORDER BY 2; Recompile — good practice after an import 1SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID'; 2-- 61 3 4@?/rdbms/admin/utlrp 5 6SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID'; 7-- 40 Recompile with a PL/SQL cursor 1SET TERMOUT ON 2SET SERVEROUTPUT ON 3DECLARE 4 CURSOR cur_invalid_objects IS 5 SELECT object_name, object_type FROM user_objects 6 WHERE object_type IN ('PROCEDURE','FUNCTION','TRIGGER','SYNONYM','VIEW', 7 'MATERIALIZED VIEW','PACKAGE','PACKAGE BODY') 8 AND status = 'INVALID'; 9 rec_columns cur_invalid_objects%ROWTYPE; 10 err_status NUMBER; 11BEGIN 12 dbms_output.enable(10000); 13 OPEN cur_invalid_objects; 14 LOOP 15 FETCH cur_invalid_objects INTO rec_columns; 16 EXIT WHEN cur_invalid_objects%NOTFOUND; 17 BEGIN 18 IF rec_columns.object_type IN ('VIEW','SYNONYM','MATERIALIZED VIEW','PACKAGE') THEN 19 dbms_output.put_line('Recompiling ' || rec_columns.object_type || ' ' || rec_columns.object_name); 20 EXECUTE IMMEDIATE 'ALTER ' || rec_columns.object_type || ' "' || rec_columns.object_name || '" COMPILE'; 21 ELSIF rec_columns.object_type = 'PACKAGE BODY' THEN 22 dbms_output.put_line('Recompiling ' || rec_columns.object_type || ' ' || rec_columns.object_name); 23 EXECUTE IMMEDIATE 'ALTER PACKAGE "' || rec_columns.object_name || '" COMPILE BODY'; 24 ELSE 25 dbms_output.put_line('Recompiling ' || rec_columns.object_type || ' ' || rec_columns.object_name); 26 dbms_ddl.alter_compile(rec_columns.object_type, NULL, rec_columns.object_name); 27 END IF; 28 EXCEPTION WHEN OTHERS THEN 29 err_status := SQLCODE; 30 dbms_output.put_line('Recompilation failed: ' || SQLERRM(err_status)); 31 END; 32 END LOOP; 33 CLOSE cur_invalid_objects; 34END; 35/ Drop invalid objects 1SET SERVEROUTPUT ON 2DECLARE 3 CURSOR cur_invalid_objects IS 4 SELECT object_name, object_type FROM user_objects 5 WHERE object_type IN ('PROCEDURE','FUNCTION','TRIGGER','SYNONYM','VIEW', 6 'MATERIALIZED VIEW','PACKAGE','PACKAGE BODY') 7 AND status = 'INVALID'; 8 rec_columns cur_invalid_objects%ROWTYPE; 9BEGIN 10 dbms_output.enable(10000); 11 OPEN cur_invalid_objects; 12 LOOP 13 FETCH cur_invalid_objects INTO rec_columns; 14 EXIT WHEN cur_invalid_objects%NOTFOUND; 15 dbms_output.put_line('DROP ' || rec_columns.object_type || ' ' || rec_columns.object_name); 16 EXECUTE IMMEDIATE 'DROP ' || rec_columns.object_type || ' ' || rec_columns.object_name; 17 END LOOP; 18 CLOSE cur_invalid_objects; 19END; 20/ `DROP ` does not map one-to-one for every object type (e.g. there is no `DROP PACKAGE BODY`, and `SYNONYM`/`TRIGGER` need care). Prefer recompiling; drop only objects you are sure you no longer need.

August 2, 2024 2 min read Docs

🚚 Move / Clone a Database

Memo Databases

Copy the source database oraprd into a target test database oratest (created beforehand). The copy stops oratest and replaces its files with oraprd’s, then makes them take effect. 1. Generate the control-file script 1ALTER DATABASE BACKUP CONTROLFILE TO TRACE; This writes a trace file into user_dump_dest. The relevant part looks like: 1STARTUP NOMOUNT 2CREATE CONTROLFILE REUSE DATABASE "oraprd" NORESETLOGS ARCHIVELOG 3MAXLOGFILES 5 4MAXLOGMEMBERS 3 5MAXDATAFILES 100 6MAXINSTANCES 1 7MAXLOGHISTORY 908 8LOGFILE 9 GROUP 1 'G:\ORACLE\ORADATA\oraprd\REDO01.LOG' SIZE 10M, 10 GROUP 1 'G:\ORACLE\ORADATA\oraprd\REDO02.LOG' SIZE 10M, 11 GROUP 2 'F:\ORACLE\ORADATA\oraprd\REDO03.LOG' SIZE 10M, 12 GROUP 2 'F:\ORACLE\ORADATA\oraprd\REDO04.LOG' SIZE 10M 13DATAFILE 14 'F:\ORACLE\ORADATA\oraprd\SYSTEM01.DBF', 15 'F:\ORACLE\ORADATA\oraprd\CWMLITE01.DBF', 16 'F:\ORACLE\ORADATA\oraprd\DATA\DATPRD.DBF', 17 ... (the whole list of datafiles) 18CHARACTER SET WE8MSWIN1252 19; 20 21RECOVER DATABASE 22ALTER SYSTEM ARCHIVE LOG ALL; 23ALTER DATABASE OPEN; 24ALTER TABLESPACE TEMP ADD TEMPFILE 'G:\ORACLE\ORADATA\oraprd\TEMP02.DBF' SIZE 2000M REUSE AUTOEXTEND OFF; 2. Adapt the generated script The source database must be shut down so that all files are synchronized. Once oraprd’s files are copied over oratest, adapt the control-file script to the new paths (e.g. F:\ORACLE\ORADATA\oraprd and G:\... → D:\ORACLE\ORADATA\oratest), and change the database name:

August 2, 2024 2 min read Docs

⚙️ SPFILE & PFILE

Memo Databases

Configuration via init.ora (PFILE) init.<SID>.ora was the way to configure Oracle 8/9. It is the database parameter file — without it the database cannot start. Default location: $ORACLE_HOME/dbs (UNIX) or %ORACLE_HOME%\database (Windows). Sometimes a system stays on `init..ora` even on 11g/12c, because the instance was upgraded from an old version. Examples of parameters:

Read more about 🔧 Installation.

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 3 min read Docs

📝 Scripting

Memo Databases Scripting

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

📋 Procedures

Memo Databases

Basics find a procedures 1SELECT * 2 FROM USER_OBJECTS 3 WHERE object_type = 'PROCEDURE' 4 AND object_name = 'grant_RW' Example which give SELECT right on one schema to the role 1CREATE OR REPLACE PROCEDURE grant_RO_to_schema( 2 username VARCHAR2, 3 grantee VARCHAR2) 4AS 5BEGIN 6 FOR r IN ( 7 SELECT owner, table_name 8 FROM all_tables 9 WHERE owner = username 10 ) 11 LOOP 12 EXECUTE IMMEDIATE 13 'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee; 14 END LOOP; 15END; 16/ 17 18-- See if procedure is ok -- 19SHOW ERRORS 20 21CREATE ROLE '${ROLE_NAME}' NOT IDENTIFIED; 22GRANT CONNECT TO '${ROLE_NAME}'; 23GRANT SELECT ANY SEQUENCE TO '${ROLE_NAME}'; 24GRANT CREATE ANY TABLE TO '${ROLE_NAME}'; 25 26-- Play the Procedure -- 27EXEC grant_RO_to_schema('${SCHEMA}','${ROLE_NAME}') Procedure which give Read/Write right to one schema: 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; 9CREATE OR REPLACE PROCEDURE grant_RW_to_schema( 10 username VARCHAR2, 11 grantee VARCHAR2) 12AS 13BEGIN 14 FOR r IN ( 15 SELECT owner, table_name 16 FROM all_tables 17 WHERE owner = username 18 ) 19 LOOP 20 EXECUTE IMMEDIATE 21 '\''GRANT SELECT,DELETE,UPDATE,INSERT,ALTER ON '\''||r.owner||'\''.'\''||r.table_name||'\'' to '\'' || grantee; 22 END LOOP; 23END; 24/ 25CREATE ROLE '${ROLE_NAME}' NOT IDENTIFIED; 26GRANT CONNECT TO '${ROLE_NAME}'; 27GRANT SELECT ANY SEQUENCE TO '${ROLE_NAME}'; 28GRANT CREATE ANY TABLE TO '${ROLE_NAME}'; 29GRANT CREATE ANY INDEX TO '${ROLE_NAME}'; 30EXEC grant_RW_to_schema('\'''${SCHEMA}''\'','\'''${ROLE_NAME}''\'') 31exit; 32EOF2 33unset ORAENV_ASK; 34' 1-- This one is working better : 2CREATE OR REPLACE PROCEDURE grant_RW_to_schema( 3myschema VARCHAR2, 4myrole VARCHAR2) 5AS 6BEGIN 7for t in (select owner,object_name,object_type from all_objects where owner=myschema and object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) loop 8if t.object_type in ('TABLE','VIEW') then 9EXECUTE immediate 'GRANT SELECT, UPDATE, INSERT, DELETE ON '||t.owner||'.'||t.object_name||' TO '|| myrole; 10elsif t.object_type in ('PROCEDURE','FUNCTION','PACKAGE') then 11EXECUTE immediate 'GRANT EXECUTE ON '||t.owner||'.'||t.object_name||' TO '|| myrole; 12end if; 13end loop; 14end; 15/

August 1, 2024 8 min read Docs

💽 Disks ASM

Memo Databases Storage

Basics Start ASM - The old way: 1. oraenv # ora SID = +ASM1 (if second nodes +ASM2 ) 2sqlplus / as sysasm 3startup Start ASM - The new method: 1srvctl start asm -n ora-node1-hostname Check ASM volumes 1srvctl status asm 2asmcmd lsdsk 3asmcmd lsdsk -G DATA 4srvctl status diskgroup -g DATA Check clients connected to ASM volume 1# List clients 2asmcmd lsct 3 4DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group 5+ASM CONNECTED 19.0.0.0.0 19.0.0.0.0 +ASM DATA 6+ASM CONNECTED 19.0.0.0.0 19.0.0.0.0 +ASM FRA 7ORCL CONNECTED 12.2.0.1.0 12.2.0.0.0 ORCL DATA 8ORCL CONNECTED 12.2.0.1.0 12.2.0.0.0 ORCL FRA 9MYDB CONNECTED 12.2.0.1.0 12.2.0.0.0 MYDB DATA 10MYDB CONNECTED 12.2.0.1.0 12.2.0.0.0 MYDB FRA 11 12# Files Open 13asmcmd lsof 14 15DB_Name Instance_Name Path 16ORCL ORCL +DATA/ORCL/DATAFILE/blob.268.1045299983 17ORCL ORCL +DATA/ORCL/DATAFILE/data.270.1045299981 18ORCL ORCL +DATA/ORCL/DATAFILE/indx.269.1045299983 19ORCL ORCL +DATA/ORCL/control01.ctl 20ORCL ORCL +DATA/ORCL/redo01a.log 21ORCL ORCL +DATA/ORCL/redo02a.log 22ORCL ORCL +DATA/ORCL/redo03a.log 23ORCL ORCL +DATA/ORCL/redo04a.log 24ORCL ORCL +DATA/ORCL/sysaux01.dbf 25[...] Connect to ASM prompt 1. oraenv # ora SID = +ASM 2asmcmd ASMlib ASMlib - provide oracleasm command: 1# list 2oracleasm listdisks 3DATA2 4FRA1 5 6# check 7oracleasm status 8Checking if ASM is loaded: yes 9Checking if /dev/oracleasm is mounted: yes 10 11# check one ASM volume 12oracleasm querydisk -d DATA2 13Disk "DATA2" is a valid ASM disk on device [8,49] 14 15# scan 16oracleasm scandisks 17Reloading disk partitions: done 18Cleaning any stale ASM disks... 19Scanning system for ASM disks... 20Instantiating disk "DATA3" 21 22# Create, delete, rename 23oracleasm createdisk DATA3 /dev/sdf1 24oracleasm deletedisk 25oracleasm renamedisk custom script to list disks handle for ASM (not relevant anymore): 1cat asmliblist.sh 2#!/bin/bash 3for asmlibdisk in `ls /dev/oracleasm/disks/*` 4 do 5 echo "ASMLIB disk name: $asmlibdisk" 6 asmdisk=`kfed read $asmlibdisk | grep dskname | tr -s ' '| cut -f2 -d' '` 7 echo "ASM disk name: $asmdisk" 8 majorminor=`ls -l $asmlibdisk | tr -s ' ' | cut -f5,6 -d' '` 9 device=`ls -l /dev | tr -s ' ' | grep -w "$majorminor" | cut -f10 -d' '` 10 echo "Device path: /dev/$device" 11 done Disks Group Disk Group : all disks in teh same DG should have same size. Different type of DG, external means that LUN replication is on storage side. When a disk is added to DG wait for rebalancing before continuing operations.