Memo

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:

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:

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.

Managing users Four main concepts: USERS — with the granted PRIVILEGES. ROLES — a pack of privileges. PROFILES — a pack of limitations. Note: an unquoted SQL name is uppercase; a quoted name keeps its case as written. Creation / deletion: 1-- all users, with account status, expiry, profile, etc. 2SELECT username, profile, account_status, expiry_date, lock_date 3FROM dba_users WHERE oracle_maintained = 'N'; 4 5CREATE USER my_user IDENTIFIED BY my_password; -- create a user. 6DROP USER my_user; -- drop a user. 7DROP USER my_user CASCADE; -- drop a user and all its tables. Privileges 1SELECT * FROM dba_sys_privs; -- all possible privileges. 2SELECT * FROM dba_sys_privs WHERE grantee = 'MY_USER'; -- one user's privileges. 3 4GRANT create session, alter session, drop any index TO my_user; 5REVOKE alter session FROM my_user; Roles 1CREATE ROLE my_role; -- create a role. 2GRANT create session, alter session, drop tablespace, delete any table TO my_role; -- grant to a role. 3GRANT my_role TO my_user, hr; -- grant a role to users. 4REVOKE alter session FROM my_role; 5 6SELECT * FROM dba_roles; 7SELECT * FROM dba_role_privs WHERE grantee = 'MY_USER'; -- roles of one user. 8SELECT grantee, granted_role, admin_option, default_role FROM dba_role_privs ORDER BY 1,2; 9SELECT * FROM dba_sys_privs WHERE grantee = 'MY_ROLE'; 10SELECT * FROM dba_tab_privs WHERE grantee = 'MY_ROLE'; Profiles 1SELECT * FROM dba_profiles; -- all profiles. 2SELECT * FROM dba_profiles WHERE profile = 'MY_PROFILE'; -- one profile's limits. 3 4CREATE PROFILE my_profile LIMIT idle_time 15 connect_time 20 failed_login_attempts 50; 5ALTER USER my_user PROFILE my_profile; Account management 1ALTER USER my_user IDENTIFIED BY new_password; -- change the password. 2ALTER USER my_user ACCOUNT UNLOCK; -- unlock a user. sys / system 1ALTER USER sys IDENTIFIED BY '<password>'; 2ALTER USER system IDENTIFIED BY '<password>'; If the DB password is changed, you must also regenerate the password file (orapwd), which controls remote SYSDBA access:

Listener / Tnsname.ora 1# Check if listner is present 2ps -edf | grep lsn 3 4# Prompt Listner 5lsnrctl 6LSNRCTL> help 7The following operations are available 8An asterisk (*) denotes a modifier or extended command: 9 10start stop status services 11version reload save_config trace 12spawn quit exit set* 13show* 14 15lsnrctl status 16lsnrctl start 17 18# Logs 19less /opt/oracle/product/12c/db/network/admin/listener.ora Local Listner 1# in Oracle prompt 2show parameter listener; 3NAME TYPE VALUE 4------------------------------------ ----------- ------------------------------ 5listener_networks string 6local_listener string LISTENER_TOTO 7remote_listener string First LISTENER_TOTO must be defined in the tnsnames.ora. 1# in Oracle prompt 2alter system set local_listener='LISTENER_TOTO' scope=both; 3alter system register; 1lsnrctl status 2 3LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-APR-2021 18:58:48 4Copyright (c) 1991, 2016, Oracle. All rights reserved. 5Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 6STATUS of the LISTENER 7------------------------ 8Alias LISTENER 9Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production 10Start Date 29-APR-2021 18:11:13 11Uptime 0 days 0 hr. 47 min. 34 sec 12Trace Level off 13Security ON: Local OS Authentication 14SNMP OFF 15Listener Log File /u01/oracle/base/diag/tnslsnr/myhost/listener/alert/log.xml 16Listening Endpoints Summary... 17 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.example.com)(PORT=1521))) 18Services Summary... 19Service "+ASM" has 1 instance(s). 20 Instance "+ASM", status READY, has 1 handler(s) for this service... 21Service "+ASM_DATA" has 1 instance(s). 22 Instance "+ASM", status READY, has 1 handler(s) for this service... 23Service "+ASM_FRA" has 1 instance(s). 24 Instance "+ASM", status READY, has 1 handler(s) for this service... 25Service "ORCL" has 1 instance(s). 26 Instance "ORCL", status READY, has 1 handler(s) for this service... 27Service "ORCLXDB" has 1 instance(s). 28 Instance "ORCL", status READY, has 1 handler(s) for this service... 29The command completed successfully Static Listner: TNSnames.ORA Services have to be listed in tnsnames.ora of client hosts.

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.

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/

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;

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: