Oracle admin tasks

Oracle admin tasks – here are some basic queries and script examples I have gathered and adapted from various sources – the Internet, colleagues etc.

GitHub repo: https://github.com/DonaldSimpson/oracle_scripts

My main interest in this is in doing both day to day maintenance tasks to support environments, and in scripting monitoring and preventative Jenkins jobs that report on various aspects of Oracle Database servers – these automated database monitors have proved very worthwhile, and often identify upcoming issues before they cause problems (e.g. expiring users, table spaces filling up, disabled constraints and triggers, etc etc).

 

Find and kill sessions:

Connect:
sqlplus / as sysdba

Set the line size so things look better:
set linesize 999

Then run a query to show active users:
SELECT s.osuser, s.status, s.process, s.machine, s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != ‘BACKGROUND’;

If you want to kill one, use the SID and SERIAL from the above:

ALTER SYSTEM KILL SESSION ‘{SID},{SERIAL}’;

———————–

Tablespaces; finding, resizing and autoextending:

sqlplus / as sysdba
set linesize 999

List Oracle Database tablespace files:

SELECT FILE_NAME as FNAME, TABLESPACE_NAME as TSPACE,BYTES, AUTOEXTENSIBLE as AUTOEX, MAXBYTES as MAXB,INCREMENT_BY as INC FROM DBA_DATA_FILES;

From the above, get the file name for the Table Space that needs altered, and do something like this:

ALTER DATABASE DATAFILE ‘{/path to above TS file, eg /ora/path/undotbs_0001.dbf}’ AUTOEXTEND ON NEXT 64m MAXSIZE 2G;

———————–

Start and Stop things:

Listeners…

lsnrctl start listener_Name
lsnrctl stop listener_Name

databases…
sqlplus / as sysdba
startup
shutdown immediate

———————–

Find invalid objects:

Optionally filtered by owner(s) and without synonyms…

select owner || ‘.’ || object_name || ‘[‘ || object_type || ‘]’
from dba_objects
where status = ‘INVALID’
and object_type != ‘SYNONYM’
and owner in (‘SYSTEM’,’SYS’,’TOOLS’,’DEVUSER’);

———————–

Check Constraints and Triggers:

SELECT * FROM all_constraints WHERE status <> ‘ENABLED’;

or filter by users:

SELECT * FROM all_constraints WHERE owner = ‘ARBOR’ and status <> ‘ENABLED’;

Triggers are similar:

select * from all_triggers where status <> ‘ENABLED’;

———————–

Check for locked/locking users:

those already locked:

select * from dba_users where username in (‘SYSTEM’,’SYS’,’TOOLS’,’DEVUSER’) and lock_date is not null;

or those about to be locked (I add this to my Jenkins Database monitoring jobs so you get some warning…):

select * from dba_users where expiry_date < (trunc(SYSDATE) +7) and lock_date = null;

———————–

Check the Oracle Wallet:

Check to see if encrytion is present:

select * from dba_encrypted_columns;

if that brings something back, then you can check the state of the Oracle Wallet:

SELECT status from v$encryption_wallet where status not like ‘OPEN’;

———————–

Running SQL scripts from Shell scripts:

This can be done in various ways, but I tend to either use this approach to simply run a file and exit:

echo “About to run ${SCRIPT_NAME} on ${SERVER}…”
echo exit | sqlplus ${DB_USER}/${DB_PASSWORD}@${SERVER} @/path/to/sql/scripts/${SCRIPT_NAME}.sql
echo “Script ${SCRIPT_NAME} complete.” # now check the return code etc…

or sometimes a HEREDOC is more suitable, something like this example for checking database links work:

echo “Checking ${DBLINK} link for user ${DB_USER}…”
DBLINK_CHECK=$(sqlplus -s -l ${DB_USER}/${DB_PASS}@${ADM_DBASE}<<EOF
set echo off heading off feedback off
SELECT ‘Link works’ from dual@${DBLINK};
exit;
EOF)
if [ $? -ne 0 ]
then
echo “ERROR: Checking link ${DBLINK} as ${DB_USER} FAILED”
fi

———————–

If you find any of these useful or would like to suggest additions or changes please let me know.

Cheers,

Don

Leave a Reply