Oracle DBA cheat sheet: SQL*Plus, sessions, waits, space, RMAN, Data Guard and more

#Connect, start and stop

. oraenv                              # set ORACLE_SID / ORACLE_HOME
sqlplus / as sysdba
sqlplus system@//db01.corp.local:1521/SALESPDB
lsnrctl status
srvctl status database -db ORCL       # Grid / Oracle Restart / RAC

STARTUP;                       STARTUP MOUNT;          STARTUP NOMOUNT;
SHUTDOWN IMMEDIATE;            SHUTDOWN ABORT;  -- last resort
SELECT name, open_mode, database_role, log_mode, flashback_on FROM v$database;
SELECT instance_name, status, host_name, startup_time FROM gv$instance;
SELECT banner_full FROM v$version;

SQL*Plus formatting: SET LINESIZE 200 PAGESIZE 100, COLUMN name FORMAT a30, SET SERVEROUTPUT ON.

#Multitenant (CDB / PDB)

SHOW CON_NAME;
SHOW PDBS;
ALTER SESSION SET CONTAINER = SALESPDB;
ALTER PLUGGABLE DATABASE SALESPDB OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE SALESPDB SAVE STATE;          -- reopen after restart
SELECT con_id, name, open_mode, restricted FROM v$pdbs;

#Sessions

SELECT s.inst_id, s.sid, s.serial#, s.username, s.status, s.machine, s.program, s.sql_id,
       s.event, s.seconds_in_wait, s.blocking_session
FROM gv$session s WHERE s.type = 'USER' AND s.status = 'ACTIVE' ORDER BY s.seconds_in_wait DESC;

-- blocking tree
SELECT sid, serial#, username, blocking_session, event, seconds_in_wait
FROM v$session WHERE blocking_session IS NOT NULL;

ALTER SYSTEM KILL SESSION '123,45678,@1' IMMEDIATE;   -- sid,serial#,@inst_id
SELECT sql_id, sql_fulltext FROM v$sql WHERE sql_id = '7h35uxf5uhmm1';

#Waits and top SQL

-- what sessions are waiting on now
SELECT event, wait_class, COUNT(*) FROM v$session
WHERE type = 'USER' AND wait_class <> 'Idle' GROUP BY event, wait_class ORDER BY 3 DESC;

-- top SQL by elapsed time (cursor cache)
SELECT * FROM (
  SELECT sql_id, executions, ROUND(elapsed_time/1e6) AS elapsed_s, ROUND(cpu_time/1e6) AS cpu_s,
         buffer_gets, SUBSTR(sql_text, 1, 80) AS sql_text
  FROM v$sql ORDER BY elapsed_time DESC) WHERE ROWNUM <= 15;

-- actual plan of a cursor
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7h35uxf5uhmm1', NULL, 'ALLSTATS LAST'));

Licensed Diagnostics/Tuning packs: @?/rdbms/admin/awrrpt.sql, @?/rdbms/admin/ashrpt.sql, @?/rdbms/admin/addmrpt.sql, SQL Tuning Advisor. Without them, use Statspack.

#Tablespaces and space

SELECT df.tablespace_name,
       ROUND(df.bytes/1024/1024/1024, 1) AS size_gb,
       ROUND((df.bytes - NVL(fs.bytes,0))/1024/1024/1024, 1) AS used_gb,
       ROUND(100 * (df.bytes - NVL(fs.bytes,0)) / df.maxbytes, 1) AS pct_of_max
FROM (SELECT tablespace_name, SUM(bytes) bytes, SUM(GREATEST(maxbytes, bytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) fs
  ON df.tablespace_name = fs.tablespace_name ORDER BY pct_of_max DESC;

ALTER TABLESPACE users ADD DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf' RESIZE 20G;

-- temp and undo
SELECT tablespace_name, ROUND(SUM(bytes_used)/1024/1024) used_mb, ROUND(SUM(bytes_free)/1024/1024) free_mb FROM v$temp_space_header GROUP BY tablespace_name;

-- largest segments
SELECT * FROM (SELECT owner, segment_name, segment_type, ROUND(bytes/1024/1024/1024, 2) gb
               FROM dba_segments ORDER BY bytes DESC) WHERE ROWNUM <= 20;

#Redo, archivelogs and FRA

SELECT group#, thread#, bytes/1024/1024 AS mb, members, status FROM v$log;
SELECT * FROM v$recovery_file_dest;
SELECT file_type, percent_space_used, percent_space_reclaimable FROM v$recovery_area_usage;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SET db_recovery_file_dest_size = 500G;

-- log switches per hour (last day)
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hr, COUNT(*) FROM v$log_history
WHERE first_time > SYSDATE - 1 GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24') ORDER BY 1;

#RMAN

rman target /
RMAN> SHOW ALL;
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP DELETE INPUT;
RMAN> LIST BACKUP SUMMARY;
RMAN> REPORT NEED BACKUP;
RMAN> CROSSCHECK BACKUP;  DELETE NOPROMPT EXPIRED BACKUP;  DELETE NOPROMPT OBSOLETE;
RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE DATABASE PREVIEW;
RMAN> VALIDATE DATABASE;                  -- checks for corrupt blocks

SELECT session_key, input_type, status, start_time, end_time, output_bytes_display
FROM v$rman_backup_job_details ORDER BY start_time DESC FETCH FIRST 10 ROWS ONLY;
SELECT * FROM v$database_block_corruption;

#Data Guard (broker)

dgmgrl sys@ORCL
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE 'ORCL_DR';
DGMGRL> VALIDATE DATABASE 'ORCL_DR';
DGMGRL> SWITCHOVER TO 'ORCL_DR';
DGMGRL> FAILOVER TO 'ORCL_DR';
DGMGRL> EDIT DATABASE 'ORCL_DR' SET STATE='APPLY-OFF';   -- pause apply

SELECT name, value FROM v$dataguard_stats;               -- on the standby: transport/apply lag
SELECT name, role, action, thread#, sequence# FROM v$dataguard_process;   -- 12.2+ (replaces v$managed_standby)

#Users and privileges

CREATE USER app_ro IDENTIFIED BY "..." DEFAULT TABLESPACE users QUOTA 0 ON users;
GRANT CREATE SESSION TO app_ro;
GRANT SELECT ON sales.orders TO app_ro;
ALTER USER app_ro ACCOUNT UNLOCK;
ALTER USER app_ro IDENTIFIED BY "new-password";
SELECT username, account_status, expiry_date, profile FROM dba_users ORDER BY username;
SELECT * FROM dba_role_privs WHERE granted_role = 'DBA';
SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_name LIKE 'PASSWORD%';

#Invalid objects and statistics

SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID';
@?/rdbms/admin/utlrp.sql                                     -- recompile invalid objects
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SALES', degree => 4);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS', cascade => TRUE);
SELECT table_name, last_analyzed, stale_stats FROM dba_tab_statistics WHERE owner = 'SALES' AND stale_stats = 'YES';

#Alert log and diagnostics

adrci
adrci> show homes
adrci> set home diag/rdbms/orcl/ORCL
adrci> show alert -tail 100 -f
adrci> show problem
adrci> ips pack problem 1 in /tmp           # package for Oracle Support

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
SELECT originating_timestamp, message_text FROM v$diag_alert_ext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' HOUR AND message_text LIKE '%ORA-%';

#Patching level

$ORACLE_HOME/OPatch/opatch lspatches

SELECT patch_id, action, status, description, action_time FROM dba_registry_sqlpatch ORDER BY action_time DESC;

Warning

AWR, ASH, ADDM, SQL Tuning Advisor and DBA_HIST_* views are part of separately licensed packs. Querying them without a licence can create an audit finding. Check control_management_pack_access.