Inside a Shell script One line command: 1# Set the SID 2ORAENV_ASK=NO 3export ORACLE_SID=HANA 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;

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/

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 "IANA" has 1 instance(s). 26 Instance "IANA", status READY, has 1 handler(s) for this service... 27Service "IANAXDB" has 1 instance(s). 28 Instance "IANA", 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.

Sources & Docs Oracle-base Some good-practices and standards

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 7MANA CONNECTED 12.2.0.1.0 12.2.0.0.0 MANA DATA 8MANA CONNECTED 12.2.0.1.0 12.2.0.0.0 MANA FRA 9MREPORT CONNECTED 12.2.0.1.0 12.2.0.0.0 MREPORT DATA 10MREPORT CONNECTED 12.2.0.1.0 12.2.0.0.0 MREPORT FRA 11 12# Files Open 13asmcmd lsof 14 15DB_Name Instance_Name Path 16MANA MANA +DATA/MANA/DATAFILE/blob.268.1045299983 17MANA MANA +DATA/MANA/DATAFILE/data.270.1045299981 18MANA MANA +DATA/MANA/DATAFILE/indx.269.1045299983 19MANA MANA +DATA/MANA/control01.ctl 20MANA MANA +DATA/MANA/redo01a.log 21MANA MANA +DATA/MANA/redo02a.log 22MANA MANA +DATA/MANA/redo03a.log 23MANA MANA +DATA/MANA/redo04a.log 24MANA MANA +DATA/MANA/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.

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: 172.16.228.0/255.255.255.0/ens192, static 6Subnet IPv6: 7SCAN 1 IPv4 VIP: 172.16.228.33 8SCAN VIP is enabled. 9SCAN VIP is individually enabled on nodes: 10SCAN VIP is individually disabled on nodes: 11SCAN 2 IPv4 VIP: 172.16.228.35 12SCAN VIP is enabled. 13SCAN VIP is individually enabled on nodes: 14SCAN VIP is individually disabled on nodes: 15SCAN 3 IPv4 VIP: 172.16.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: