Oracle sections in docs
---
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.
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
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:
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).
Examples of parameters:
Oracle-Base: DB 19c RAC installation on Oracle Linux 8 (VirtualBox)
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).
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
/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.1DEVICE TYPE CONNECTION
2ens192 ethernet Admin
3ens224 ethernet Interconnect
4ens256 ethernet Backup
One network interface for backups is required to mount an NFS share.
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: 192.168.228.0/255.255.255.0/ens192, static
6Subnet IPv6:
7SCAN 1 IPv4 VIP: 192.168.228.33
8SCAN VIP is enabled.
9SCAN VIP is individually enabled on nodes:
10SCAN VIP is individually disabled on nodes:
11SCAN 2 IPv4 VIP: 192.168.228.35
12SCAN VIP is enabled.
13SCAN VIP is individually enabled on nodes:
14SCAN VIP is individually disabled on nodes:
15SCAN 3 IPv4 VIP: 192.168.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:
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).
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):
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
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[...]
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.
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).
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.
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:
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
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.
Synchronisation mechanism between two databases in Active/Passive.
1dgmgrl sys@orcl
2DGMGRL> switchover to 'orcl';
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.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.
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;
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:
Four main concepts:
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.
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;
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';
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;
1ALTER USER my_user IDENTIFIED BY new_password; -- change the password.
2ALTER USER my_user ACCOUNT UNLOCK; -- unlock a user.
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 remoteSYSDBAaccess:
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.
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.
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;
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.
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;
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
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/
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/
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 "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
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=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
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;