Oracle

12 Posts
1 Sections
1 Types

August 1, 2024 0 min read Docs

Administrations

Docs DBA Oracle Admin

August 1, 2024 3 min read Docs

Clusterware

Docs DBA Oracle HA

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:

Read more about Oracle Clients.

August 1, 2024 2 min read Docs

Procedures

Docs DBA Oracle

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

Scripting

Docs DBA Oracle Shell

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;

August 1, 2024 0 min read Docs

Scripting

Docs DBA Oracle Storage

August 1, 2023 2 min read Docs

📐 Storage

Docs SysAdmin Virtualisation Oracle Storage KVM

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.