Systems Engineering and RDBMS

System Audit Script for Oracle

Posted by decipherinfosys on October 22, 2008

Here is a system audit script for Oracle environments. You can modify the values and settings as per your needs. Idea is to make sure that all the environments are audited on a regular basis to ensure that there are no discrepancies. There are similar scripts for the user (Schema) level as well. And similar audit set of scripts for SQL Server, DB2 LUW, MySQL environments as well. We will post those in the days to come.

The script takes the data set that it collects and prepares a HTML report.

rem
rem sys_audit.sql
rem
rem NOTE:
rem Run this script as a user with SYS/SYSTEM privileges
rem Script supports up-to Oracle 10g.

ALTER SESSION SET NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’
/

PROMPT Generating sys audit report …..
SET PAGES 2000 LINES 120 VERIFY OFF ECHO OFF TRIMSPOOL ON TRIMOUT ON FEED OFF TERMOUT OFF
SET MARKUP HTML ON SPOOL ON HEAD ‘<TITLE> Database Audit Report </TITLE>’
SPOOL sys_audit.html
PROMPT
PROMPT
SELECT ‘SYSTEM AUDIT PERFOMED BY DB USER ‘||USER ||’ AT ‘ || SYSTIMESTAMP AS “SYS AUDIT INFORMATION”
FROM dual
/
PROMPT Database Init Parameters Information
PROMPT ====================================
COLUMN name HEADING ‘Parameter|Name’ FORMAT a35
COLUMN current_value HEADING ‘Current|Value’ FORMAT a35 wrap
COLUMN recommend_value HEADING ‘Recommended|Value’ FORMAT a35 wrap
SELECT name,
current_value,
recommend_value
FROM
(SELECT name,
value current_value,
(CASE
/* General*/
WHEN LOWER(name) = ‘open_cursors’ AND TO_NUMBER(value) <> 512 THEN
‘Recommended setting is 512’
WHEN LOWER(name) = ‘cursor_sharing’ AND LOWER(value) <> ‘exact’ THEN
‘Not OK. Set the value to EXACT’
WHEN LOWER(name) = ‘session_cached_cursors’ AND TO_NUMBER(value) < 400 THEN
‘Not OK. Set the parameter to 400’
WHEN LOWER(name) = ‘db_block_checksum’ AND LOWER(value) <> ‘true’ THEN
‘Not OK. Set the parameter to TRUE’
WHEN LOWER(name) = ‘db_files’ AND TO_NUMBER(value) < 500 THEN
‘Set the parameter to 500’
WHEN LOWER(name) = ‘query_rewrite_enabled’ AND LOWER(value) <> ‘true’ THEN
‘Not OK. Set the parameter to TRUE’
WHEN LOWER(name) = ‘query_rewrite_integrity’ AND LOWER(value) <> ‘trusted’ THEN
‘Not OK. Set the parameter to TRUSTED’
/*Optimizer*/
WHEN LOWER(name) = ‘optimizer_mode’ AND LOWER(value) <> ‘first_rows’ THEN
‘Not OK. Set the parameter to FIRST_ROWS’
WHEN LOWER(name) = ‘optimizer_index_caching’ AND TO_NUMBER(value) <> 90 THEN
‘Recommended setting is 90’
WHEN LOWER(name) = ‘optimizer_index_cost_adj’ AND TO_NUMBER(value) <> 25 THEN
‘Recommended setting is 25’
WHEN LOWER(name) = ‘_sort_elimination_cost_ratio’ AND TO_NUMBER(value) <> 4 THEN
‘Recommended setting is 4’
WHEN LOWER(name) = ‘_b_tree_bitmap_plans’ AND LOWER(value) <> ‘false’ THEN
‘Not OK. Set the parameter to FALSE’
WHEN LOWER(name) = ‘optimizer_dynamic_sampling’ AND TO_NUMBER(VALUE) <= 1 THEN
‘Set it to atleast 2. This will help in accessing data from global temporary tables as upto date statistics may not be available for temporary tables’
WHEN LOWER(name) = ‘db_file_multiblock_read_count’ AND TO_NUMBER(value) > 8 THEN
‘Recommended setting is 8 for a OLTP system’
/*Auto Management*/
WHEN LOWER(name) = ‘workarea_size_policy’ AND LOWER(value) <> ‘auto’ THEN
‘Not OK. Set the parameter to AUTO’
WHEN LOWER(name) = ‘pga_aggregate_target’ AND TO_NUMBER(value) < 1073741824 THEN
‘Not OK. Recommended setting is 1073741824 (1g)’
WHEN LOWER(name) = ‘sort_area_size’ AND TO_NUMBER(value) < 1048576 THEN
‘Increase to 1048576. (Ok, if pga_aggregate_target/work_area_policy are used)’
WHEN LOWER(name) = ‘sort_area_retained_size’ AND TO_NUMBER(value) < 16384 THEN
‘Increase to 16384. (Ok, if pga_aggregate_target/work_area_policy are used)’
/*Auto UNDO management*/
WHEN LOWER(name) = ‘undo_management’ AND LOWER(value) <> ‘auto’ THEN
‘Not OK. Set the parameter to AUTO’
WHEN LOWER(name) = ‘undo_retention’ AND TO_NUMBER(value) <> 900 THEN
‘Not OK. Recommended setting is 900’
/*Memory Management*/
WHEN LOWER(name) = ‘db_cache_size’ AND TO_NUMBER(value) < 1610612736 THEN
‘Not OK. Increase to 1610612736 (1.5g) minimum. Recommended is 2147483648 (2g)’
WHEN LOWER(name) = ‘db_cache_advice’ AND LOWER(value) <> ‘off’ THEN
‘Not OK. Set the parameter to OFF’
WHEN LOWER(name) = ‘db_block_size’ AND TO_NUMBER(value) <> 8192 THEN
‘Not OK. Set the parameter to 8192 (8 kb)’
WHEN LOWER(name) = ‘db_block_buffers’ AND TO_NUMBER(value) <> 0 THEN
‘Not OK. Set the parameter to 0 (zero)’
WHEN LOWER(name) = ‘sga_max_size’ AND TO_NUMBER(value) < 3221225472 THEN
‘Not OK. Increase to 3221225472 (3g) (recommended)’
WHEN LOWER(name) = ‘sga_target’ AND TO_NUMBER(value) < 3221225472 THEN
‘Not OK. Increase to 3221225472 (3g) (recommended)’
WHEN LOWER(name) = ‘log_checkpoint_interval’ AND TO_NUMBER(value) <> 0 THEN
‘Not OK. Set the parameter to 0 (zero)’
WHEN LOWER(name) = ‘shared_pool_size’ AND TO_NUMBER(value) < 419430400 THEN
‘Not OK. Increase to 419430400 (400m) minimum (recommended)’
WHEN LOWER(name) = ‘shared_pool_reserved_size’ AND TO_NUMBER(value) < 16777216 THEN
‘Not OK. Increase to 16777216 (16m) mininum (recommended)’
WHEN LOWER(name) = ‘log_buffer’ AND TO_NUMBER(value) < 1048576 THEN
‘Not OK. Increase to 1048576 (1m)’
WHEN LOWER(name) = ‘processes’ AND TO_NUMBER(value) <> 500 THEN
‘Recommended setting is 500’
/*Recovery*/
WHEN LOWER(name) = ‘fast_start_mttr_target’ AND TO_NUMBER(value) <> 900 THEN
‘Recommended setting is 900’
WHEN LOWER(name) = ‘log_archive_start’ AND LOWER(value) <> ‘true’ THEN
‘Not Archiving?. Set the parameter to TRUE. Production databases should run under ARCHIVELOG mode’
/*Shared Server*/
WHEN LOWER(name) = ‘shared_servers’ AND TO_NUMBER(value) <> 0 THEN
‘Not OK. Set the parameter to 0 (zero)’
WHEN LOWER(name) = ‘dispatchers’ AND UPPER(value) NOT LIKE ‘(PROTOCOL=TCP)%’ THEN
‘Not OK. Set the parameter to default’
/*Other*/
WHEN LOWER(name) = ‘parallel_automatic_tuning’ AND LOWER(value) <> ‘false’ THEN
‘Not OK. Set the parameter to FALSE’
WHEN LOWER(name) = ‘parallel_adaptive_multi_user’ AND LOWER(value) <> ‘false’ THEN
‘Not OK. Set the parameter to FALSE’
WHEN LOWER(name) = ‘parallel_max_servers’ AND TO_NUMBER(value) > 5 THEN
‘Not OK. Set the parameter value to 5 (default)’
WHEN LOWER(name) = ‘skip_unusable_indexes’ AND LOWER(value) <> ‘true’ THEN
‘Not OK. Set the parameter to TRUE’
WHEN LOWER(name) = ‘plsql_optimize_level’ AND TO_NUMBER(value) <> 1 THEN
‘Not OK. Set the parameter value to 1’
WHEN LOWER(name) = ‘audit_trail’ AND (LOWER(value) not in (‘none’,’false’)) THEN
‘Not OK. Audit is not recommended due to performance overhead’
/*Information*/
WHEN LOWER(name) = ‘cluster_database’ AND LOWER(value) = ‘true’ THEN
‘OK. Database setup with RAC’
ELSE
‘OK’
END) recommend_value
FROM v$parameter
UNION
SELECT ‘_b_tree_bitmap_plans’, ‘Not Set’, ‘Add this parameter in your init.ora and set to FALSE’
FROM DUAL
WHERE NOT EXISTS (SELECT 1
FROM v$parameter
WHERE name = ‘_b_tree_bitmap_plans’)
UNION
SELECT ‘_sort_elimination_cost_ratio’, ‘Not Set’, ‘Add this parameter in your init.ora and set it to 4’
FROM DUAL
WHERE NOT EXISTS (SELECT 1
FROM v$parameter
WHERE name = ‘_sort_elimination_cost_ratio’))

/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT NLS Parameter Settings
PROMPT ======================
COLUMN parameter HEADING ‘Parameter|Name’ FORMAT a35
COLUMN current_value HEADING ‘Current|Value’ FORMAT a35 wrap
COLUMN recommend_value HEADING ‘Recommended|Value’ FORMAT a35 wrap
SELECT parameter,
current_value,
recommend_value
FROM
(SELECT parameter,
value current_value,
(CASE
— General
WHEN LOWER(parameter) = ‘nls_length_semantics’ AND LOWER(value) <> ‘char’ THEN
‘Change the semantics to CHAR’
WHEN LOWER(parameter) = ‘nls_characterset’ AND LOWER(value) NOT IN (‘utf8’, ‘al32utf8’) THEN
‘Database CharacterSet should be atleast UTF8 (Unicode)’
ELSE
NULL
END) recommend_value
FROM v$nls_parameters)
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Database Information
PROMPT ====================
COLUMN name HEADING ‘Database|Name’ FORMAT a9
COLUMN created HEADING ‘Created|Date’ FORMAT a17
COLUMN resetlogs_time HEADING ‘ResetLogs|Time’ FORMAT a17
COLUMN log_mode HEADING ‘Log|Mode’ FORMAT a12
COLUMN open_mode HEADING ‘Open|Mode’ FORMAT a10
COLUMN database_role HEADING ‘Database|Role’ FORMAT a16
COLUMN protection_mode HEADING ‘Protection|Mode’ FORMAT a20
SELECT name,
TO_CHAR(created, ‘DD-MON-YYYY HH24:MI’) AS created,
TO_CHAR(resetlogs_time, ‘DD-MON-YYYY HH24:MI’) AS resetlogs_time,
log_mode,
open_mode,
database_role,
protection_mode
FROM v$database
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Database Version Information
PROMPT ============================
COLUMN banner HEADING ‘Version|Information’ FORMAT a70
SELECT *
FROM v$version
/
CLEAR COLUMNS
PROMPT
PROMPT
PROMPT SGA Size
PROMPT ========
PROMPT
SHOW SGA

PROMPT
PROMPT
PROMPT Database Archival
PROMPT =================
ARCHIVE LOG LIST

PROMPT
PROMPT
PROMPT User and Tablespace Information
PROMPT ===============================
COLUMN username HEADING ‘User|Name’ FORMAT a30
COLUMN default_tablespace HEADING ‘Default|Tablespace’ FORMAT a30
COLUMN temporary_tablespace HEADING ‘Temp|Tablespace’ FORMAT a30
SELECT username,
default_tablespace,
temporary_tablespace
FROM dba_users
ORDER BY username
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Database Instance Information
PROMPT =============================
COLUMN instance_name HEADING ‘Instance|Name’ FORMAT a16
COLUMN host_name HEADING ‘Host|Name’ FORMAT a20
COLUMN startup_time HEADING ‘Startup|Time’ FORMAT a17
COLUMN version HEADING ‘Version’ FORMAT a12
COLUMN status HEADING ‘Status’ FORMAT a12
SELECT instance_name,
host_name,
TO_CHAR(startup_time, ‘DD-MON-YYYY HH24:MI’) startup_time,
version,
status
FROM v$instance
/
CLEAR COLUMNS
PROMPT
PROMPT
PROMPT Database Log Information
PROMPT ========================
SELECT *
FROM v$log
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Database Log Files Information
PROMPT ==============================
COLUMN member HEADING ‘Log|Filename’ FORMAT a60
SELECT *
FROM v$logfile
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Database Control Files Information
PROMPT ==================================
COLUMN status HEADING ‘Status’
COLUMN name HEADING ‘ControlFile|Name’ FORMAT a60
SELECT status,
name
FROM v$controlfile
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Shared Pool Usage in (MB)
PROMPT =========================
COLUMN Shared_Pool_Used Heading ‘Shared Pool|Used’ Format 999,999.99
COLUMN Shared_Pool_Size Heading ‘Shared Pool|Size’ Format 999,999.99
COLUMN Shared_Pool_Avail Heading ‘Shared Pool|Available’ Format 999,999.99
COLUMN Shared_Pool_Pct Heading ‘Shared Pool|Percentage’ Format 999.99
SELECT SUM(a.Bytes)/1048576 AS Shared_Pool_Used,
MAX(b.Value)/1048576 AS Shared_Pool_Size,
(MAX(b.Value)/1048576)-(SUM(a.Bytes)/1048576) AS Shared_Pool_Avail,
(SUM(a.Bytes)/MAX(b.Value))*100 AS Shared_Pool_Pct
FROM v$SgaStat a,
v$Parameter b
WHERE a.Pool = ‘shared pool’
AND a.Name != ‘free memory’
AND b.Name = ‘shared_pool_size’
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Database Tablespace Information
PROMPT ===============================
COLUMN tablespace_name HEADING ‘Tablespace|Name’ FORMAT a30
COLUMN status HEADING ‘Status’ FORMAT a7
COLUMN block_size HEADING ‘Block|Size’ FORMAT 99999 JUSTIFY RIGHT
COLUMN contents HEADING ‘Contents’ FORMAT a10 WRAP
COLUMN extent_management HEADING ‘Extent|Management’ FORMAT a10 WRAP
COLUMN allocation_type HEADING ‘Allocation|Type’ FORMAT a10 WRAP
COLUMN segment_space_management HEADING ‘Segment|Space|Management’ FORMAT a10
SELECT tablespace_name,
status,
block_size,
contents,
extent_management,
allocation_type,
segment_space_management
FROM dba_tablespaces
/

PROMPT
PROMPT
PROMPT Datafile Information
PROMPT ====================
COLUMN tablespace_name HEADING ‘Tablespace|Name’ FORMAT a30
COLUMN file_name HEADING ‘File|Name’ FORMAT a60
SELECT tablespace_name,
file_name
FROM dba_data_files
UNION
SELECT tablespace_name,
file_name
FROM dba_temp_files
ORDER BY tablespace_name
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT Objects that need to pinned in Shared Pool
PROMPT ==========================================
COLUMN object_name HEADING ‘Object|Name’ format a50
COLUMN object_type HEADING ‘Object|Type’ format a25
COLUMN pin_command HEADING ‘Statement|to Pin’ format a80
SELECT owner||’.’||name as object_name,
type as object_type,
‘EXEC DBMS_SHARED_POOL.Keep(‘||chr(39)||OWNER||’.’||name||chr(39)||’,’||
chr(39)||decode(type,’PACKAGE’,’P’,’PROCEDURE’,’P’,’FUNCTION’,’P’,’SEQUENCE’,’Q’,’TRIGGER’,’R’)||chr(39)||’)’ as pin_command
FROM v$db_object_cache
WHERE type NOT IN (‘NOT LOADED’,’NON-EXISTENT’,’VIEW’,’TABLE’,’INVALID TYPE’,’CURSOR’,’PACKAGE BODY’)
AND executions > loads
AND executions > 1
AND kept=’NO’
AND OWNER <> ‘SYS’
ORDER BY owner, namespace, type, executions DESC
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT DBA Jobs Information
PROMPT ====================
COLUMN job HEADING ‘Job#’ FORMAT 99999
COLUMN log_user HEADING ‘Job|Owner’ FORMAT a20 WRAP
COLUMN schema_user HEADING ‘Under|Schema’ FORMAT a20 WRAP
COLUMN last_date HEADING ‘Last|Run|Date’ FORMAT a17
COLUMN next_date HEADING ‘Next|Run|Date’ FORMAT a17
COLUMN total_time_min HEADING ‘Total|Time|(min)’ FORMAT 99999
COLUMN failures HEADING ‘Times|Faild’ FORMAT 99999
SELECT job,
log_user,
schema_user,
TO_CHAR(last_date, ‘DD-MON-YYYY HH24:MI’) AS last_date,
TO_CHAR(next_date, ‘DD-MON-YYYY HH24:MI’) AS next_date,
(total_time/60) AS total_time_min,
failures
FROM dba_jobs
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT DBA Job Description
PROMPT ===================
COLUMN job HEADING ‘Job#’ FORMAT 99999
COLUMN what HEADING ‘Description’ FORMAT a100 WRAP
SELECT job,
what
FROM dba_jobs
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT DBA SYS Privileges
PROMPT ==================
COLUMN grantee HEADING ‘Role|Name’ FORMAT a30
COLUMN privilege HEADING ‘Privilege|Name’ FORMAT a40
SELECT grantee,
privilege
FROM dba_sys_privs
WHERE grantee NOT IN (SELECT username FROM dba_users)
ORDER by grantee
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT DBA Roles
PROMPT =========
COLUMN role HEADING ‘Role Name’
SELECT role
FROM dba_roles
ORDER BY role
/
CLEAR COLUMNS

PROMPT
PROMPT
PROMPT DBA Role Privileges
PROMPT ===================
COLUMN grantee HEADING ‘Role|Name’ FORMAT a30
COLUMN granted_role HEADING ‘Role|Granted’ FORMAT a30
SELECT grantee,
granted_role
FROM dba_role_privs
WHERE grantee NOT IN (SELECT username FROM dba_users)
ORDER BY grantee
/
CLEAR COLUMNS

SPOOL OFF
EXIT

One Response to “System Audit Script for Oracle”

  1. […] System Audit Script for Oracle […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: