Data Base Audit Queries

1)

SELECT 'SYSTEM AUDIT PERFOMED BY DB USER '||USER ||' AT ' || SYSTIMESTAMP AS "SYS AUDIT INFORMATION"
FROM dual

SELECT * FROM (
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'))
                                ) a
                                 where RECOMMEND_VALUE <> 'OK'  ;

2) NLS PARAMETER SETTINGS

SELECT parameter,current_value,recommend_value FROM (SELECT parameter,value current_value,

(CASE 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) ;

3) DB INFO

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 ;

4) DB VERSION

SELECT * FROM v$version ;

5) USER AND TABLESPACE INFORMATION

SELECT username,default_tablespace,temporary_tablespace FROM dba_users
WHERE username ='CONTRACTREPO' ORDER BY username ;

6) SGA Size :

SHOW SGA

7) Database Archival:

ARCHIVE LOG LIST

8) Database Instance Information

SELECT instance_name,host_name,TO_CHAR(startup_time, 'DD-MON-YYYY HH24:MI') startup_time,version,status FROM v$instance;

9) Database Log Information

SELECT * FROM v$log;

10) Database Log Files Information

SELECT * FROM v$logfile ;

11) Database Control Files Information

SELECT status,name FROM v$controlfile;

12) HARED POOL USAGE IN MB

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'  ;

13) MEMORY ALLOCATION DATA

SELECT * FROM(SELECT NAME, BYTES/(1024*1024) MB FROM V$SGASTAT WHERE POOL = 'shared pool'ORDER BY BYTES DESC)WHERE ROWNUM <= 10;

14) Database Tablespace Information

SELECT tablespace_name,status,block_size,contents,extent_management,allocation_type,segment_space_management

FROM dba_tablespaces ;

15) Datafile Information

SELECT tablespace_name,file_name FROM dba_data_files UNION SELECT tablespace_name,file_name FROM dba_temp_files ORDER BY tablespace_name ;

16) OBJECTS THAT NEED TO PINNED IN SHARED POOL

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 ;

17) DBA Jobs Information

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 ;

18) DBA Job Description

SELECT job,what FROM dba_jobs;

19) DBA SYS Privileges

SELECT grantee, privilege FROM dba_sys_privs WHERE grantee NOT IN (SELECT username FROM dba_users) ORDER by grantee;

20) DBA Roles

SELECT role FROM dba_roles ORDER BY role;

21) DBA Role Privileges

SELECT grantee, granted_role FROM dba_role_privs WHERE grantee NOT IN (SELECT username FROM dba_users) ORDER BY grantee ;