Databases sections in docs
Documentation regarding all Databases.
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)
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:
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
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
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
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
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:
1. oraenv # ora SID = +ASM1 (if second nodes +ASM2 )
2sqlplus / as sysasm
3startup
1srvctl start asm -n ora-node1-hostname
1srvctl status asm
2asmcmd lsdsk
3asmcmd lsdsk -G DATA
4srvctl status diskgroup -g DATA
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[...]
1. oraenv # ora SID = +ASM
2asmcmd
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
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
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.
---
config:
theme: forest
layout: elk
---
flowchart TD
subgraph s1["Instance DB"]
style s1 fill:#E8F5E9,stroke:#388E3C,stroke-width:2px
subgraph s1a["Background Processes"]
style s1a fill:#FFF9C4,stroke:#FBC02D,stroke-width:1px
n5["PMON (Process Monitor)"]
n6["SMON (System Monitor)"]
n10["RECO (Recoverer Process)"]
end
subgraph s1b["PGA (Process Global Area)"]
style s1b fill:#E3F2FD,stroke:#1976D2,stroke-width:1px
n1["Processes"]
end
subgraph s1c["SGA (System Global Area)"]
style s1c fill:#FFEBEE,stroke:#D32F2F,stroke-width:1px
subgraph n7["Shared Pool (SP)"]
style n7 fill:#F3E5F5,stroke:#7B1FA2,stroke-width:1px
n7a["DC (Dictionary Cache)"]
n7b["LC (Library Cache)"]
n7c["RC (Result Cache)"]
end
n8["DB Cache (DBC)"]
n9["Redo Buffer"]
n3["DBWR (DB Writer)"]
n4["LGWR (Log Writer)"]
n5["PMON (Process Monitor)"]
n6["SMON (System Monitor)"]
n10["RECO (Recoverer Process)"]
end
end
subgraph s2["Database: Physical Files"]
style s2 fill:#FFF3E0,stroke:#F57C00,stroke-width:2px
n11["TBS (Tablespaces, files in .DBF)"]
n12["Redo Log Files"]
n13["Control Files"]
n14["SPFILE (Binary Authentication File)"]
n15["ArchiveLog files"]
end
subgraph s3["Operating System"]
style s3 fill:#E0F7FA,stroke:#00796B,stroke-width:2px
n16["Listener (Port 1521)"]
end
n3 --> n11
n3 --> n7c
n4 --> n12
n6 --> n7a
s3 --> s1
s1c <--> n12
s1c <--> n13
s1c <--> n14
n7b <--> n7c
classDef Aqua stroke-width:1px, stroke-dasharray:none, stroke:#0288D1, fill:#B3E5FC, color:#01579B
classDef Yellow stroke-width:1px, stroke-dasharray:none, stroke:#FBC02D, fill:#FFF9C4, color:#F57F17
classDef Green stroke-width:1px, stroke-dasharray:none, stroke:#388E3C, fill:#C8E6C9, color:#1B5E20
classDef Red stroke-width:1px, stroke-dasharray:none, stroke:#D32F2F, fill:#FFCDD2, color:#B71C1C
class n11,n12,n13,n14,n15 Aqua
class n5,n6,n10 Yellow
class n1 Green
class n7,n8,n9,n3,n4 Red
An Oracle server includes an Oracle Instance and an Oracle Database.
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
1# in Oracle prompt
2show parameter listener;
3NAME TYPE VALUE
4------------------------------------ ----------- ------------------------------
5listener_networks string
6local_listener string LISTENER_TOTO
7remote_listener string
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
Services have to be listed in tnsnames.ora of client hosts.
1SELECT *
2 FROM USER_OBJECTS
3 WHERE object_type = 'PROCEDURE'
4 AND object_name = 'grant_RW'
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}')
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/
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
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'
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
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;
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
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
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
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
1SQL> Create table emp as select * from employees;
2SQL> UPDATE emp SET LAST_NAME='ABC';
3SQL> commit;