Oracle DBA Best Practices
Version 11.2.0.3
 
Overview
One question that comes up frequently during DBA Boot Camps is ... "what is my job?" Another is "when I take over a new system what should I do first?"

With that in mind this page is dedicated to collected so-called "best practice" advice on what to do in these situations. Actions are on the left ... code on the right.
 
What should I do today?
1. Review the alert log See the demos under ADR Command Interpreter and External Tables
2. Determine if there is block level corruption.  Report new blocks corrupted and corrupted remaining from the previous day's report conn / as sysdba

col corruption_change# format 99999999999999999999

SELECT * FROM v$database_block_corruption ORDER BY 1, 3;

     FILE#     BLOCK#     BLOCKS    CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- --------------------- ---------
        22     162844          4        10594075667556 CORRUPT

-- if corruption is found use the following SQL to identify the corrupt segment(s)

SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = 22
AND 162844 BETWEEN block_id AND (block_id+(blocks-1));

-- then consider using DBMS_REPAIR to repair the corruption.
3. Verify the status of last night's backup.  Report new backup corruption and previous unmitigated backup corruption events conn / as sysdba

SELECT inst_id, set_stamp, piece#, file#, block#, blocks, marked_corrupt, corruption_type
FROM gv$backup_corruption;

   INST_ID SET_STAMP      PIECE#      FILE#     BLOCK#     BLOCKS MAR CORRUPTIO
---------- ---------- ---------- ---------- ---------- ---------- --- ---------
         6  711749250          1         22     103631          3 NO  LOGICAL

SELECT inst_id, recid, stamp, copy_recid, file#, block#, blocks, marked_corrupt, corruption_type
FROM gv$copy_corruption where rownum = 1;

   INST_ID      RECID      STAMP COPY_RECID      FILE#     BLOCK#     BLOCKS MAR CORRUPTIO
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------
         6          1  705195114          9         22      39918          1 NO  LOGICAL
4. Look for newly invalidated objects and unusable indexes conn / as sysdba

SELECT owner, object_type, COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID'
GROUP BY owner, object_type;

SELECT owner, table_name, index_name
FROM dba_indexes
WHERE status = 'UNUSABLE';
5. Are there any hung resumable sessions SELECT user_id, session_id, status, suspend_time, error_number
FROM dba_resumable;
6. Are there any blocked sessions SELECT (
  SELECT username
  FROM gv$session
  WHERE sid=a.sid) blocker,
  a.sid, ' is blocking ', (
  SELECT username
  FROM gv$session
  WHERE sid=b.sid) blockee, b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;
7. Backup Control File to Trace ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/control_file.bkp';
8. Verify the system did not restart without your knowledge col host_name format a15

SELECT instance_name, host_name, startup_time, status, logins
FROM gv$instance
ORDER BY 1;

INSTANCE_NAME    HOST_NAME    STARTUP_TIME         STATUS       LOGINS
---------------- ------------ -------------------- ------------ ----------
mlm01p1          usml9001a    07-JUL-2010 04:42:50 OPEN         ALLOWED
mlm01p2          usml9001b    15-JUL-2010 12:52:17 OPEN         ALLOWED
9. Look for anomalies in log switch frequency and switch frequencies greater than 12 per hour.

For example there seem to be a couple of patterns visible in the data to the right and some obvious outages.

In this real production data you can see clear evidence that if the DBA had not been asleep at the wheel the problems might have been caught and dealt with before the outages. And do you get the impression that some things have been timed for 00, 06, 12, and 18 hrs? How predictable.
SELECT TO_CHAR(first_time,'MMDD') MMDD,
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') "21",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
GROUP BY TO_CHAR(first_time,'MMDD')
ORDER BY 1;

MMDD 00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
0609  16  11   9   8   8  10  12   8   8  10   8  10  14  10  11  15  15   8  12   8   7   6   9   7
0610  13  12   8   9   7   6  11   9   6   8   7   8  12   6   7   6   8   7  10   7   4   4   4   5
0611  12   8   5   9   9   7  11   7   6   7   8   5  12   9  10   8   9  12  12  10   6   6   9   8
0612  13  12   7   9   7   9  10  10   7   7   9   8  11   7   7   8   7   7  11   9   5   6   8   7
0613  12  11   7   8   8   7  13   7   9   7   8   7  13  10   9   8   8   8  11   8   7   5   7   6
0614  15  10   9   9   8   9  13   9   9   7  11  13  11   9   8   9  13   9  12   9   7   9   7   7
0615  15  10  10   8  10   9  12   8   9   8   9   7  13   6   8   7   7   7  15  10   7   7   7   5
0616  13   8   8   7   7   6  10   8  11   7   8   6  11   7  12  13  13  14  13   9   9   9   7   8
0617  15  13  10   9   8   9  16   8   8  10   9  10  16  11  10  10   8  11  13   8   9   9   7   9
0618  12  13  15  15  13  13  15  13   9  12   8  11  14   9  10   9   9   8  14   9   8   8   9   8
0619  16  11  10  11   9   9  13  12  10   9  12  12  17   8   9   9  11  11  14   9   9  11  10  12
0620  19  15  11  10  10  10  19  11   9   9   9   9  13   7  15  10  11  11  12  10   9  11  11  10
0621  13  16  11   9  10  13  16   8  14   9  11  12  17  10  10  11   8  11  14   8  11  14   8  11
0622  16  13  13  11  11   9  16   9   9  11  10  11  17  10   9  10  10  10  13  14   9  10  10   8
0623  19  13  12  13  13  11  16  12  11  11  11  11  16   9  10  13   2  14  14   8   9   8   8   8
0624  14   9   9   9   7   9  11   8   8   7   8   8  14   7   8   7   9   3   6   0   0   0   0   0
0625   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2   0   0   0   0
0626   0   1   0   0   0   0   0   0   0   4   0   0   0   2   2   3   2   7   5   6   1   0   0   0
0627   3  10   0   0   0   5   0   1  10   0   0   0   0   0   1   0   1   0   2   5   3   7   1   0
0629   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   4   0   4   0   0   6   7   6
0630   7   4  23  19   9  10   5   6   7  17  19  17  15  17  15  43  40  32  17  15  14  20  13  15
0701  15  12  14  12  13  12  13  17  15  17  20  20  18  18  17  15  14  13  10  10  15  15  13  19
0702  21  22  20  18  14  14  12  13  11  11  14  14  14  10   9  10   9  10  11   9  11   9  10  12
0703   9  13  10  17  14  17  15  17  23  20  19  20  17  19  16  17  15  17  15  15  15  16  16  18
0704  22  19  19  18  16  15  13  13  14  11  13  10  12  14  10  12  14  11   9  11  12  13  12   9
0705  14  13   9  11  10  12  13  11  11   8  10  10  11  11  11  12  10  10   9  10   8   9  12   7
0706  14  15  11  12   9  15  13  12  12   9  12  14  12  12  12  12  13  11   8   9  12  13   2   0
0707   0   0   1   0   3  15  10  10   7   8  10  11  12   8   6   9  13  12   9   8   9   8  10  10
0708  16   9   8  15  10  11   9   8   8  14   9  10  10   8   8  14  15  10   9   9   8   9  10  10
0709  13  12   9  10  10   9   9  10  11  11   8   9   9   8   9  13   8   9   6   9   9  11  10   9
0710  12  10   9  10   9  12   9   8   8  11   7  10  11   9   9  13  10   9   8   9  11  12  10  10
0711  15  12   9  13   9  12   8  10  11  13   9   8  10   9   8  12  11  12   9   9  10  11  10   8
0712  13  12  10  13  10  10   9   7  10  11   9  10  12  12  12  15  12   9   8   9  11  12  12  12
0713  14  12  12  11  10  10  12  12  12  15  10  11  11  10   4   5  15  14  10   9   8   8  13   6
0714  12  12   9   9  11  10  10   9  10   9  14   7   7   8   8   9  14   9   9  10  12   8  13  10
0715  10  10   9  14  12  15  12  14  13  15  10  11   9   4   8   6   8   7   6   7   8   8   8   8
0716  10  11   9   8   8   9   9   6   6   7   7  12   7   9  15  14  13  16  12  14  11   9   6   7
0717  10  10   9   9   9  10  12  14  11  10  12   9   8  12   7   3   0   0   0   0   0   0   0   0
10. Verify that your backup was successful

The example, at right, demonstrates real-world failures that require follow-up by reading the corresponding RMAN log files. Also be alert to repeated failures that occur at the same time of day as occurs in the output.
-- this query modified slightly from the listing below
SELECT start_time, end_time, input_type, status
FROM v$rman_backup_job_details
ORDER BY 1;

START_TIME END_TIME STATUS
-------------------- -------------------- -----------------------
24-JAN-2012 17:34:47 24-JAN-2012 18:59:18 COMPLETED
24-JAN-2012 23:40:21 25-JAN-2012 01:06:55 COMPLETED
25-JAN-2012 05:40:21 25-JAN-2012 06:46:35 COMPLETED
25-JAN-2012 08:39:03 25-JAN-2012 08:40:24 COMPLETED
25-JAN-2012 11:47:12 25-JAN-2012 13:17:40 COMPLETED
25-JAN-2012 17:43:16 25-JAN-2012 19:14:50 COMPLETED
25-JAN-2012 23:46:11 26-JAN-2012 01:32:38 COMPLETED
26-JAN-2012 05:47:34 26-JAN-2012 07:36:51 COMPLETED
26-JAN-2012 11:47:27 26-JAN-2012 13:18:06 COMPLETED
26-JAN-2012 17:48:53 26-JAN-2012 18:52:53 COMPLETED
26-JAN-2012 23:48:43 27-JAN-2012 01:05:57 COMPLETED
27-JAN-2012 05:48:08 27-JAN-2012 07:05:31 COMPLETED
27-JAN-2012 08:38:42 27-JAN-2012 08:40:11 COMPLETED
27-JAN-2012 11:54:09 27-JAN-2012 13:22:56 COMPLETED
27-JAN-2012 17:50:09 27-JAN-2012 19:50:18 COMPLETED
27-JAN-2012 23:50:20 28-JAN-2012 01:14:51 COMPLETED
28-JAN-2012 05:50:20 28-JAN-2012 07:15:22 COMPLETED
28-JAN-2012 11:50:59 28-JAN-2012 13:11:05 COMPLETED
28-JAN-2012 17:51:01 28-JAN-2012 19:11:44 COMPLETED
28-JAN-2012 23:54:16 29-JAN-2012 01:22:42 COMPLETED
29-JAN-2012 05:56:23 29-JAN-2012 07:12:03 COMPLETED
29-JAN-2012 08:35:47 29-JAN-2012 08:37:06 COMPLETED
29-JAN-2012 12:00:03 29-JAN-2012 13:41:28 COMPLETED
29-JAN-2012 17:55:18 29-JAN-2012 19:30:54 COMPLETED
29-JAN-2012 23:55:47 30-JAN-2012 01:25:14 COMPLETED
30-JAN-2012 05:56:34 30-JAN-2012 07:45:18 COMPLETED
30-JAN-2012 11:58:41 30-JAN-2012 13:27:15 COMPLETED
30-JAN-2012 18:02:45 30-JAN-2012 19:02:32 COMPLETED
30-JAN-2012 23:56:36 31-JAN-2012 01:04:36 COMPLETED
31-JAN-2012 06:02:03 31-JAN-2012 07:18:37 COMPLETED
31-JAN-2012 11:59:26 31-JAN-2012 13:18:13 COMPLETED
31-JAN-2012 17:58:57 31-JAN-2012 19:05:34 COMPLETED
31-JAN-2012 23:59:34 01-FEB-2012 01:09:43 COMPLETED
01-FEB-2012 05:59:51 01-FEB-2012 07:02:11 COMPLETED
01-FEB-2012 08:39:17 01-FEB-2012 08:40:36 COMPLETED
01-FEB-2012 12:06:08 01-FEB-2012 13:50:32 COMPLETED
01-FEB-2012 17:59:48 01-FEB-2012 19:44:28 COMPLETED
02-FEB-2012 00:00:41 02-FEB-2012 01:30:53 COMPLETED
02-FEB-2012 06:02:12 02-FEB-2012 07:34:12 COMPLETED
02-FEB-2012 12:02:02 02-FEB-2012 13:36:45 COMPLETED
02-FEB-2012 18:02:13 02-FEB-2012 19:15:06 COMPLETED
03-FEB-2012 00:04:59 03-FEB-2012 01:31:11 COMPLETED
03-FEB-2012 06:04:10 03-FEB-2012 07:13:35 COMPLETED
03-FEB-2012 08:37:19 03-FEB-2012 08:38:52 COMPLETED
03-FEB-2012 12:15:05 03-FEB-2012 14:05:17 COMPLETED
03-FEB-2012 18:04:46 03-FEB-2012 19:44:57 COMPLETED
04-FEB-2012 00:05:56 FAILED
04-FEB-2012 02:00:54 04-FEB-2012 03:30:21 COMPLETED
04-FEB-2012 08:01:03 04-FEB-2012 09:27:16 COMPLETED
04-FEB-2012 14:09:03 04-FEB-2012 15:09:35 COMPLETED
04-FEB-2012 20:03:17 05-FEB-2012 00:46:34 COMPLETED WITH ERRORS
05-FEB-2012 02:01:54 05-FEB-2012 03:05:00 COMPLETED
05-FEB-2012 08:01:49 05-FEB-2012 09:16:59 COMPLETED
05-FEB-2012 08:36:15 05-FEB-2012 08:37:47 COMPLETED
05-FEB-2012 14:01:35 05-FEB-2012 15:31:33 COMPLETED
05-FEB-2012 20:01:23 05-FEB-2012 22:05:26 COMPLETED WITH ERRORS
06-FEB-2012 02:03:05 06-FEB-2012 03:37:16 COMPLETED
06-FEB-2012 08:02:56 06-FEB-2012 09:24:00 COMPLETED
06-FEB-2012 14:07:53 06-FEB-2012 15:12:10 COMPLETED
06-FEB-2012 20:01:50 06-FEB-2012 21:06:41 FAILED
07-FEB-2012 02:01:42 07-FEB-2012 03:16:17 COMPLETED
07-FEB-2012 08:02:35 07-FEB-2012 09:43:35 COMPLETED
07-FEB-2012 14:03:12 07-FEB-2012 15:14:44 COMPLETED
07-FEB-2012 20:03:56 FAILED
08-FEB-2012 02:04:27 08-FEB-2012 03:24:14 COMPLETED
08-FEB-2012 08:04:43 08-FEB-2012 09:44:53 COMPLETED
08-FEB-2012 08:37:11 08-FEB-2012 08:38:28 COMPLETED
08-FEB-2012 14:05:53 08-FEB-2012 15:52:53 COMPLETED
08-FEB-2012 20:04:20 08-FEB-2012 22:19:13 FAILED
09-FEB-2012 02:05:12 09-FEB-2012 04:15:11 COMPLETED
09-FEB-2012 08:07:40 09-FEB-2012 10:15:32 COMPLETED
09-FEB-2012 14:06:40 09-FEB-2012 15:17:38 COMPLETED
09-FEB-2012 20:05:35 09-FEB-2012 21:47:53 FAILED
10-FEB-2012 02:06:13 10-FEB-2012 03:46:37 COMPLETED
10-FEB-2012 08:06:56 10-FEB-2012 09:44:15 COMPLETED
10-FEB-2012 08:44:16 10-FEB-2012 08:45:33 COMPLETED
10-FEB-2012 14:06:14 10-FEB-2012 15:46:15 COMPLETED
10-FEB-2012 20:07:30 10-FEB-2012 21:54:22 FAILED
11-FEB-2012 00:53:58 11-FEB-2012 03:07:22 COMPLETED
11-FEB-2012 06:53:31 11-FEB-2012 08:32:46 COMPLETED
11. View incremental backups to verify Level 0 vs Level 1 metrics SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status
FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd
WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time
AND vrbjd.input_type <> 'ARCHIVELOG'
ORDER BY 2,1;
12. Verify datafile headers are consistent and current SELECT file#, status, error, format, recover, checkpoint_time
FROM gv$datafile_header;

     FILE#  STATUS              ERROR     FORMAT REC CHECKPOINT_TIME
---------- ------- ------------------ ---------- --- --------------------
 1         ONLINE                             10 NO  01-JUN-2011 23:02:27
 2         ONLINE                             10 NO  01-JUN-2011 23:02:27
 3         ONLINE                             10 NO  01-JUN-2011 23:02:27
 4         ONLINE                             10 NO  01-JUN-2011 23:02:27
 5         ONLINE                             10 NO  01-JUN-2011 23:02:27
 6         ONLINE                             10 NO  01-JUN-2011 23:02:27
 7         ONLINE                             10 NO  01-JUN-2011 23:02:27
 8         ONLINE                             10 NO  01-JUN-2011 23:02:27
 9         ONLINE                             10 NO  01-JUN-2011 23:02:27
10         ONLINE                             10 NO  01-JUN-2011 23:02:27
11         ONLINE                             10 NO  01-JUN-2011 23:02:27

ALTER SYSTEM CHECKPOINT;

SELECT file#, status, error, format, recover, checkpoint_time
FROM gv$datafile_header;

FILE#       STATUS              ERROR     FORMAT REC CHECKPOINT_TIME
---------- ------- ------------------ ---------- --- --------------------
 1         ONLINE                             10 NO  02-JUN-2011 17:25:24
 2         ONLINE                             10 NO  02-JUN-2011 17:25:24
 3         ONLINE                             10 NO  02-JUN-2011 17:25:24
 4         ONLINE                             10 NO  02-JUN-2011 17:25:24
 5         ONLINE                             10 NO  02-JUN-2011 17:25:24
 6         ONLINE                             10 NO  02-JUN-2011 17:25:24
 7         ONLINE                             10 NO  02-JUN-2011 17:25:24
 8         ONLINE                             10 NO  02-JUN-2011 17:25:24
 9         ONLINE                             10 NO  02-JUN-2011 17:25:24
10         ONLINE                             10 NO  02-JUN-2011 17:25:24
11         ONLINE                             10 NO  02-JUN-2011 17:25:24
13. Verify that no one compiled anything in debug mode SELECT owner, name, type
FROM dba_plsql_object_settings
WHERE plsql_debug='TRUE'
ORDER BY 1,3,2;
14. Look at the audit trail I have been doing this for a very long time and only one thing amazes me more than DBAs that don't turn on auditing on their databases ... that is those that have turned on auditing and have never, not once, actually reviewed the audit trail to see if there are issues.

No day should pass without reviewing AUD$ and FGA_LOG$ for issues.
15. Look for invalid date constraints @?/rdbms/admin/utlconst.sql
16. Look for dependency timestamp errors @?/rdbms/admin/utldtchk.sql
17. Look for memory leaks and related issues col osuser format a15
col pid format 9999
col program format a20
col sid format 99999
col spid format a6
col username format a12

SELECT p.spid,p.pid,s.sid,s.serial#,s.status,p.pga_alloc_mem,p.PGA_USED_MEM,s.username,s.osuser,s.program
FROM v$process p,v$session s
WHERE s.paddr ( + ) = p.addr
AND p.background IS NULL      -- remove if need to monitor background processes
ORDER BY p.pga_alloc_mem DESC;

SPID PID SID SERIAL# STATUS PGA_ALLOC_MEM PGA_USED_MEM  USERNAME OSUSER       PROGRAM
---- --- --- ------- ------ ------------- ------------- -------- ------------ -------
8480  29 134       7 ACTIVE 3,837,058,284 1,915,366,348 SYSTEM   ORACLE.EXE   (CJQ0)
8108  44 113       1 ACTIVE   381,410,540    27,305,246 SYSTEM   ORACLE.EXE   (AS03)
8716  25 140      10 ACTIVE    59,497,708    54,748,654 PUBLIC   NT AUTHORITY ORACLE.EXE\SYSTEM
7692  38 127     127 ACTIVE    49,470,700     1,198,750 SYSTEM   ORACLE.EXE   (MS00)
8316  41 119       1 ACTIVE    47,308,012    21,730,766 SYSTEM   ORACLE.EXE   (AS00)
6676  37 125      35 ACTIVE    40,737,356     1,719,020 SYSTEM   ORACLE.EXE   (LSP0)
7856  46 109       1 ACTIVE    35,511,532    28,834,942 SYSTEM   ORACLE.EXE   (AS05)
6020  43 115       1 ACTIVE    34,659,564    27,762,294 SYSTEM   ORACLE.EXE   (AS02)
7888  45 112       1 ACTIVE    33,479,916    28,959,710 SYSTEM   ORACLE.EXE   (AS04)
7204  42 117       1 ACTIVE    32,300,268    27,629,622 SYSTEM   ORACLE.EXE   (AS01)
7232  14 157       1 ACTIVE    19,962,444    14,842,372 SYSTEM   ORACLE.EXE   (MMON)
8300  21 151       1 ACTIVE    12,377,324     4,945,804 SYSTEM   ORACLE.EXE   (ARC2)
6572  18 154       5 ACTIVE    12,377,324     4,912,644 SYSTEM   ORACLE.EXE   (ARC0)
 896  22 150       1 ACTIVE    10,149,100     4,920,036 SYSTEM   ORACLE.EXE   (LNS1)
8712  10 161       1 ACTIVE    10,018,028     4,964,308 SYSTEM   ORACLE.EXE   (LGWR)
7592  20 152       2 ACTIVE    10,018,028     4,954,988 SYSTEM   ORACLE.EXE   (ARC1)
8828  39 123       1 ACTIVE     6,872,300     5,263,606 SYSTEM   ORACLE.EXE   (MS01)
 
What should I know about this system?
1. Determine the actual version of Oracle conn / as sysdba

SELECT * FROM v$version;

Also run the demos under DBMS_UTILITY.DB_VERSION and DBMS_UTILITY.PORT_STRING
2. Determine what components are installed conn / as sysdba

col comp_name format a40

SELECT comp_name, version, status
FROM dba_registry;
3. Determine if there invalid objects in the DB conn / as sysdba

SELECT owner, edition_name, object_type, COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID'
GROUP BY owner, edition_name, object_type;
4. Is the instance part of a RAC cluster Run the demo code under DBMS_UTILITY.ACTIVE_INSTANCES

(or)

SELECT *
FROM v$active_instances;
5. Is there Data Guard replication running? conn / as sysdba

SELECT protection_mode, protection_level, remote_archive, database_role, dataguard_broker, guard_status
FROM v$database;
6. Is the database in ARCHIVE LOG mode? conn / as sysdba

SELECT log_mode FROM v$database;

(or)

SQL> archive log list;
7. Are FLASHBACK LOGS being collected? conn / as sysdba

SELECT flashback_on FROM v$database;
8. Is there supplemental logging in place? conn / as sysdba

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all
FROM v$database;
9. Where are the control files? conn / as sysdba

SELECT name FROM v$controlfile;
10. Where are the redo log files? Are there at least two members in each group?

Thank you Nikunj Gupta for the recommended changes to this and other items.
conn / as sysdba

col member format a45

SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS
FROM gv$log l, gv$logfile lf
WHERE l.group# = lf.group#
AND l.inst_id = lf.inst_id
ORDER BY 1,3;
11. What are the initialization parameters? conn / as sysdba

CREATE PFILE='/home/oracle/initparams.txt' FROM memory;
12. Capture database information

Thanks to Nikunj Gupta for the correction
conn / as sysdba

col platform_name format a30

SELECT dbid, name, open_mode, database_role, platform_name
FROM v$database;
13. Capture instance information conn / as sysdba

SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state
FROM gv$instance;
14. Are default passwords in use? conn / as sysdba

SELECT d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
AND account_status = 'OPEN'
ORDER BY 2,1;

If default passwords are in use either lock the accounts or change the passwords:

ALTER USER <user_name> ACCOUNT LOCK;

ALTER USER <user_name> IDENTTIFIED BY <new_password>;
15. Is BLOCK CHANGE TRACKING enabled? conn / as sysdba

col filename format a60

SELECT filename, status, bytes
FROM v$block_change_tracking;
16. What features are being used? Run the demo code under DBMS_FEATURE_USAGE_REPORT
17. What profiles exist and are in use? conn / as sysdba

SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';

SELECT profile, resource_name, limit
FROM dba_profiles
ORDER BY 1,2;

SELECT username, profile
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY 1;
18. Are case sensitive passwords enabled? SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%sensitive%');
19. Is Advanced Queuing in use? SELECT owner, queue_table, type
FROM dba_queue_tables;
20. Are Streams, CDC or other capture and apply processes in use? SELECT capture_name, queue_name, status
FROM dba_capture;

SELECT apply_name, queue_name, status
FROM dba_apply;
21. Are event triggers in use? SELECT a.obj#, a.sys_evts, b.name
FROM sys.trigger$ a, sys.obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject IN (0, 88);
22. Is FORCE LOGGING enabled? SELECT force_logging FROM v$database;

SELECT tablespace_name, force_logging
FROM dba_tablespaces
ORDER BY 2,1;
23. Is ADVANCED REWRITE in use? SELECT owner, name FROM dba_rewrite_equivalences;
24. Were system statistics collected? SELECT pname, pval1
FROM sys.aux_stats$;

If the query result only shows values for FLAGS, CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED and the later two have the values 10 and 4096 you need to collect system statistics. Follow the link at the bottom of the page to do so.
25. Examine enabled degree of parallelism SELECT degree, COUNT(*)
FROM dba_tables
GROUP BY degree;

SELECT degree, COUNT(*)
FROM dba_indexes
GROUP BY degree;
26. Determine who has access to the SYSTEM and SYSAUX tablespaces. Remove quota and move objects found in violation of Oracle's advice. SELECT username, tablespace_name
FROM dba_ts_quotas
WHERE tablespace_name IN ('SYSTEM', 'SYSAUX')
ORDER BY 1;

SELECT DISTINCT owner
FROM dba_segments
WHERE tablespace_name IN ('SYSTEM', 'SYSAUX')
ORDER BY 1;

-- and needless to say set the quota to 0 for any user other than those installed by Oracle

ALTER USER <username> QUOTA 0 ON SYSTEM;
ALTER USER <username> QUOTA 0 ON SYSAUX;
27. Set an appropriate FAST_START_MTTR_TARGET sho parameter fast_start_mttr_target

Read the Oracle docs and understand how to set an appropriate value for your system: For example:

-- these must not be set
SQL> show parameter checkpoint

NAME_COL_PLUS_SHOW_PARAM           TYPE    VALUE_COL_PLUS_SHOW_PARAM
---------------------------------- -------- -------------------------
log_checkpoint_interval            integer  0
log_checkpoint_timeout             integer  1800
log_checkpoints_to_alert           boolean  FALSE

SQL> show parameter io_target

NAME_COL_PLUS_SHOW_PARAM           TYPE     VALUE_COL_PLUS_SHOW_PARAM
---------------------------------- -------- -------------------------
fast_start_io_target               integer  0

-- then set the value
ALTER SYSTEM RESET log_checkpoint_timeout SCOPE=SPFILE;
ALTER SYSTEM SET FAST_START_MTTR_TARGET=15 SCOPE=SPFILE SID='*';
-- and restart the system
28. Look for security compromised by public synonyms SELECT DISTINCT table_owner
FROM dba_synonyms
WHERE owner = 'PUBLIC';
29. Do you have an RDA If your answer is "what is an RDA?" you've much to learn. There is a link at page bottom under related topics. Download the tool from metalink and create an RDA. You should have a current RDA for every Oracle database for which you are responsible available at all times.
30. Is TCP/IP Stack Optimization in place See Linux Oracle Installation and verify for your operating system version.
31. Is auditing enabled? SQL> show parameter audit

SELECT MAX(logoff$time)
FROM sys.aud$;

SELECT MAX(timestamp#), MAX(ntimestamp#)
FROM sys.fga_log$;

/* if auditing is not enabled, including auditing of SYS, what are you waiting for? An engraved invitation with gold leaf from your friends at anonymous? And if they are in use have the tables been moved to their own tablespace or are they still in SYSTEM? */

SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY 1;
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved