Oracle DBA Best Practices
Version 12.1.0.1

Overview
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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?" Lots of people are running around with the title "DBA" but nowhere is the job, itself, clearly defined. With that in mind this page is dedicated to collecting so-called "best practice" advice on what to do in these situations. Actions are on the left ... code and descriptions on the right.
 
How should I work with Oracle Support?
It is amazing how few Oracle DBAs have had formal training on how to work effectively with Oracle Support. Here is your guide to getting the support you need. Link. Simply put the most effective way to work with Oracle Support is to learn their internal rules and make them follow those rules. Also never have this phone number any more than a mouse click away: (800) 223-1711 and escalate as soon as you feel you are not getting the value you are paying for. That said ... Oracle Support is not a training organization so don't abuse them or expect them to teach you your job.
 
What should I do today?
1. Review the alert log Every day, at least twice, you should look at the alert log. I know a lot of people of monitoring systems, often internally developed that should do this. I don't trust any of them.

Also 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 de.owner, de.segment_name, de.segment_type
FROM dba_extents de, v$database_block_corruption vdbc
WHERE de.file_id = vdbc.file#
AND vdbc.block# BETWEEN de.block_id AND (de.block_id+(de.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 set_stamp, piece#, file#, block#, blocks, marked_corrupt, corruption_type
FROM v$backup_corruption;

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

SELECT recid, stamp, copy_recid, file#, block#, blocks, marked_corrupt, corruption_type
FROM v$copy_corruption;

     RECID      STAMP COPY_RECID      FILE#     BLOCK#     BLOCKS MAR CORRUPTIO
---------- ---------- ---------- ---------- ---------- ---------- --- ---------
         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';

-- if a CDB log into every PDB and rerun the same SQL statement
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-2013 04:42:50 OPEN         ALLOWED
mlm01p2          usml9001b    15-JUL-2013 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-2013 17:34:47 24-JAN-2013 18:59:18 COMPLETED
24-JAN-2013 23:40:21 25-JAN-2013 01:06:55 COMPLETED
25-JAN-2013 05:40:21 25-JAN-2013 06:46:35 COMPLETED
25-JAN-2013 08:39:03 25-JAN-2013 08:40:24 COMPLETED
25-JAN-2013 11:47:12 25-JAN-2013 13:17:40 COMPLETED
25-JAN-2013 17:43:16 25-JAN-2013 19:14:50 COMPLETED
25-JAN-2013 23:46:11 26-JAN-2013 01:32:38 COMPLETED
26-JAN-2013 05:47:34 26-JAN-2013 07:36:51 COMPLETED
26-JAN-2013 11:47:27 26-JAN-2013 13:18:06 COMPLETED
26-JAN-2013 17:48:53 26-JAN-2013 18:52:53 COMPLETED
26-JAN-2013 23:48:43 27-JAN-2013 01:05:57 COMPLETED
27-JAN-2013 05:48:08 27-JAN-2013 07:05:31 COMPLETED
27-JAN-2013 08:38:42 27-JAN-2013 08:40:11 COMPLETED
27-JAN-2013 11:54:09 27-JAN-2013 13:22:56 COMPLETED
27-JAN-2013 17:50:09 27-JAN-2013 19:50:18 COMPLETED
27-JAN-2013 23:50:20 28-JAN-2013 01:14:51 COMPLETED
28-JAN-2013 05:50:20 28-JAN-2013 07:15:22 COMPLETED
28-JAN-2013 11:50:59 28-JAN-2013 13:11:05 COMPLETED
28-JAN-2013 17:51:01 28-JAN-2013 19:11:44 COMPLETED
28-JAN-2013 23:54:16 29-JAN-2013 01:22:42 COMPLETED
29-JAN-2013 05:56:23 29-JAN-2013 07:12:03 COMPLETED
29-JAN-2013 08:35:47 29-JAN-2013 08:37:06 COMPLETED
29-JAN-2013 12:00:03 29-JAN-2013 13:41:28 COMPLETED
29-JAN-2013 17:55:18 29-JAN-2013 19:30:54 COMPLETED
29-JAN-2013 23:55:47 30-JAN-2013 01:25:14 COMPLETED
30-JAN-2013 05:56:34 30-JAN-2013 07:45:18 COMPLETED
30-JAN-2013 11:58:41 30-JAN-2013 13:27:15 COMPLETED
30-JAN-2013 18:02:45 30-JAN-2013 19:02:32 COMPLETED
30-JAN-2013 23:56:36 31-JAN-2013 01:04:36 COMPLETED
31-JAN-2013 06:02:03 31-JAN-2013 07:18:37 COMPLETED
31-JAN-2013 11:59:26 31-JAN-2013 13:18:13 COMPLETED
31-JAN-2013 17:58:57 31-JAN-2013 19:05:34 COMPLETED
31-JAN-2013 23:59:34 01-FEB-2013 01:09:43 COMPLETED
01-FEB-2013 05:59:51 01-FEB-2013 07:02:11 COMPLETED
01-FEB-2013 08:39:17 01-FEB-2013 08:40:36 COMPLETED
01-FEB-2013 12:06:08 01-FEB-2013 13:50:32 COMPLETED
01-FEB-2013 17:59:48 01-FEB-2013 19:44:28 COMPLETED
02-FEB-2013 00:00:41 02-FEB-2013 01:30:53 COMPLETED
02-FEB-2013 06:02:12 02-FEB-2013 07:34:12 COMPLETED
02-FEB-2013 12:02:02 02-FEB-2013 13:36:45 COMPLETED
02-FEB-2013 18:02:13 02-FEB-2013 19:15:06 COMPLETED
03-FEB-2013 00:04:59 03-FEB-2013 01:31:11 COMPLETED
03-FEB-2013 06:04:10 03-FEB-2013 07:13:35 COMPLETED
03-FEB-2013 08:37:19 03-FEB-2013 08:38:52 COMPLETED
03-FEB-2013 12:15:05 03-FEB-2013 14:05:17 COMPLETED
03-FEB-2013 18:04:46 03-FEB-2013 19:44:57 COMPLETED
04-FEB-2013 00:05:56 FAILED
04-FEB-2013 02:00:54 04-FEB-2013 03:30:21 COMPLETED
04-FEB-2013 08:01:03 04-FEB-2013 09:27:16 COMPLETED
04-FEB-2013 14:09:03 04-FEB-2013 15:09:35 COMPLETED
04-FEB-2013 20:03:17 05-FEB-2013 00:46:34 COMPLETED WITH ERRORS
05-FEB-2013 02:01:54 05-FEB-2013 03:05:00 COMPLETED
05-FEB-2013 08:01:49 05-FEB-2013 09:16:59 COMPLETED
05-FEB-2013 08:36:15 05-FEB-2013 08:37:47 COMPLETED
05-FEB-2013 14:01:35 05-FEB-2013 15:31:33 COMPLETED
05-FEB-2013 20:01:23 05-FEB-2013 22:05:26 COMPLETED WITH ERRORS
06-FEB-2013 02:03:05 06-FEB-2013 03:37:16 COMPLETED
06-FEB-2013 08:02:56 06-FEB-2013 09:24:00 COMPLETED
06-FEB-2013 14:07:53 06-FEB-2013 15:12:10 COMPLETED
06-FEB-2013 20:01:50 06-FEB-2013 21:06:41 FAILED
07-FEB-2013 02:01:42 07-FEB-2013 03:16:17 COMPLETED
07-FEB-2013 08:02:35 07-FEB-2013 09:43:35 COMPLETED
07-FEB-2013 14:03:12 07-FEB-2013 15:14:44 COMPLETED
07-FEB-2013 20:03:56 FAILED
08-FEB-2013 02:04:27 08-FEB-2013 03:24:14 COMPLETED
08-FEB-2013 08:04:43 08-FEB-2013 09:44:53 COMPLETED
08-FEB-2013 08:37:11 08-FEB-2013 08:38:28 COMPLETED
08-FEB-2013 14:05:53 08-FEB-2013 15:52:53 COMPLETED
08-FEB-2013 20:04:20 08-FEB-2013 22:19:13 FAILED
09-FEB-2013 02:05:12 09-FEB-2013 04:15:11 COMPLETED
09-FEB-2013 08:07:40 09-FEB-2013 10:15:32 COMPLETED
09-FEB-2013 14:06:40 09-FEB-2013 15:17:38 COMPLETED
09-FEB-2013 20:05:35 09-FEB-2013 21:47:53 FAILED
10-FEB-2013 02:06:13 10-FEB-2013 03:46:37 COMPLETED
10-FEB-2013 08:06:56 10-FEB-2013 09:44:15 COMPLETED
10-FEB-2013 08:44:16 10-FEB-2013 08:45:33 COMPLETED
10-FEB-2013 14:06:14 10-FEB-2013 15:46:15 COMPLETED
10-FEB-2013 20:07:30 10-FEB-2013 21:54:22 FAILED
11-FEB-2013 00:53:58 11-FEB-2013 03:07:22 COMPLETED
11-FEB-2013 06:53:31 11-FEB-2013 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 v$datafile_header;

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

ALTER SYSTEM CHECKPOINT;

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

FILE#       STATUS              ERROR     FORMAT REC CHECKPOINT_TIME
---------- ------- ------------------ ---------- --- --------------------
 1         ONLINE                             10 NO  02-JUN-2013 17:25:24
 2         ONLINE                             10 NO  02-JUN-2013 17:25:24
 3         ONLINE                             10 NO  02-JUN-2013 17:25:24
 4         ONLINE                             10 NO  02-JUN-2013 17:25:24
 5         ONLINE                             10 NO  02-JUN-2013 17:25:24
 6         ONLINE                             10 NO  02-JUN-2013 17:25:24
 7         ONLINE                             10 NO  02-JUN-2013 17:25:24
 8         ONLINE                             10 NO  02-JUN-2013 17:25:24
 9         ONLINE                             10 NO  02-JUN-2013 17:25:24
10         ONLINE                             10 NO  02-JUN-2013 17:25:24
11         ONLINE                             10 NO  02-JUN-2013 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)
18. What is the status of the database's containers? SELECT con_id, dbid, name, open_mode
FROM v$pdbs
ORDER BY 1;
19. Check for orphaned "FAKE" NOSEGMENT indexes SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'INDEX'
MINUS
SELECT owner, index_name
FROM dba_indexes;
20. Check undo tablespace size and redize in accordance with any advisory set serveroutput on

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/
21. Sequence cache size

Far too many sequences have the default size of 20 which results in lock handle waits with RAC and interferes with good performance.
SELECT sequence_owner, cache_size, COUNT(*)
FROM dba_sequences
WHERE sequence_owner NOT LIKE 'SYS%'
GROUP BY sequence_owner, cache_size;
22. Failed Scheduler Jobs SELECT owner, job_name, job_type, state, TRUNC(start_date) SDATE, TRUNC(next_run_date) NXTRUN, failure_count
FROM dba_scheduler_jobs
WHERE failure_count <> 0;
23. Disabled Constraints SELECT owner, constraint_name, constraint_type
FROM dba_constraints
WHERE status = 'DISABLED'
ORDER BY 1,2;
24. Disabled Triggers SELECT owner, trigger_name, trigger_type
FROM dba_triggers
WHERE status = 'DISABLED'
ORDER BY 1,3,2;
25. Have startup parameter changes been documented.

If not research the reason for the change and update it with a comment
col update_comment format a50

SELECT name, value, update_comment
FROM v$parameter
WHERE isadjusted = 'TRUE';

ALTER SYSTEM SET plsql_warnings = 'ENABLE:ALL'
COMMENT = 'Enabled in all containers 01-01-2014';
26. Is the system being thrashed by SGA resize operations

If so time to check whether AMM has been deployed and go to ASMM
SELECT trunc(start_time), status, oper_type, oper_mode, parameter
FROM v$sga_resize_ops
WHERE initial_size <> final_size;
27. Where are sorts taking place: Memory or disk? SELECT a.value "Disk Sorts", b.value "Memory Sorts",
ROUND((100*b.value)/DECODE((a.value+b.value), 0,1,(a.value+b.value)),2) "Pct Memory Sorts"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)';
28. Count the number of times a SQL statement used in a package has been executed as an indication of heavily used packages SELECT o.kglnaown||'.'||o.kglnaobj stored_object, sum(c.kglhdexc) sql_executions
FROM sys.x$kglob o, sys.x$kglrd d, sys.x$kglcursor c
WHERE o.inst_id = userenv('Instance')
AND d.inst_id = userenv('Instance')
AND c.inst_id = userenv('Instance')
AND o.kglobtyp in (7, 8, 9, 11, 12)
AND d.kglhdcdr = o.kglhdadr
AND c.kglhdpar = d.kglrdhdl
GROUP BY o.kglnaown, o.kglnaobj
ORDER BY 2;
28. Are there skipped AWR snapshots DECLARE
 x INTERVAL DAY TO SECOND;
 y NUMBER;
 s DATE;
 p POSITIVE;
 z VARCHAR2(40);

 CURSOR intcur IS
 SELECT dhs.end_interval_time - dhs.begin_interval_time AS zz
 FROM dba_hist_snapshot dhs
 WHERE dhs.startup_time = s
 ORDER BY dhs.begin_interval_time;
BEGIN
  SELECT snap_interval
  INTO x
  FROM wrm$_wr_control
  WHERE dbid = (SELECT dbid FROM v$database);

  y := EXTRACT(MINUTE FROM x) + (EXTRACT(HOUR FROM x)*60);

  SELECT MAX(s.startup_time)
  INTO s
  FROM dba_hist_snapshot s, dba_hist_database_instance di
  WHERE di.dbid = s.dbid
  AND di.instance_number = s.instance_number
  AND di.startup_time = s.startup_time;

  SELECT COUNT(*)
  INTO p
  FROM dba_hist_snapshot s, dba_hist_database_instance di
  WHERE di.dbid = s.dbid
  AND di.instance_number = s.instance_number
  AND di.startup_time = s.startup_time
  AND s.startup_time = s;

  FOR intrec IN intcur LOOP
    dbms_output.put_line(intrec.zz);
  END LOOP;
END;
/
 
What should I know about this system and reverify on a regular basis?
1. Is it a container database? conn / as sysdba

SELECT con_id, dbid, name, open_mode
FROM v$pdbs;
2. 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
4. Determine what components are installed conn / as sysdba

col comp_name format a40

SELECT comp_name, version, status
FROM dba_registry;
5. Is the instance part of a RAC cluster conn / as sysdba

-- run the demo code under DBMS_UTILITY.ACTIVE_INSTANCES

(or)


SELECT *
FROM v$active_instances;

-- if it is you need to know the following:
1. Is it using ASM or a CFS?
2. Are all nodes current in time-sync with an NTP server?
3. If ASM what is the number of ASM processes and does it correspond with the number of DB files?
6. Is Data Guard replication running? conn / as sysdba

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

SELECT log_mode FROM v$database;

(or)

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

SELECT flashback_on FROM v$database;
9. Is SUPPLEMENTAL LOGGING active? 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;
10. Where are the control files? conn / as sysdba

SELECT name FROM v$controlfile;
11. 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 v$log l, v$logfile lf
WHERE l.group# = lf.group#
AND l.inst_id = lf.inst_id
ORDER BY 1,3;
12. What are the initialization parameters? conn / as sysdba

CREATE PFILE='/home/oracle/initparams.txt' FROM memory;
13. 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;
14. Capture instance information conn / as sysdba

SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state
FROM gv$instance;
15. 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 u.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>;
16. Is BLOCK CHANGE TRACKING enabled? conn / as sysdba

col filename format a60

SELECT filename, status, bytes
FROM v$block_change_tracking;
17. What features are being used? Run the demo code under DBMS_FEATURE_USAGE_REPORT
18. 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;
19. Are case sensitive passwords enabled? conn / as sysdba

SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%sensitive%');
20. Is Advanced Queuing in use? conn / as sysdba

SELECT owner, queue_table, type
FROM dba_queue_tables;
21. Are Streams, CDC or other capture and apply processes in use? -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

SELECT capture_name, queue_name, status
FROM dba_capture;

SELECT apply_name, queue_name, status
FROM dba_apply;
22. Are event triggers in use? -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

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);
23. Is FORCE LOGGING enabled? conn / as sysdba

SELECT force_logging FROM v$database;

SELECT tablespace_name, force_logging
FROM dba_tablespaces
ORDER BY 2,1;

-- if not enabled
ALTER SYSTEM
24. Is ADVANCED REWRITE in use? -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

SELECT owner, name FROM dba_rewrite_equivalences;
25. Were system statistics collected? conn / as sysdba

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.

exec dbms_stats.gather_system_stats('INTERVAL', 15);
26. When was the last time dictionary and fixed object stats were collected -- if you don't know the answer to that question do so now

exec dbms_stats.gather_dictionary_stats;

exec dbms_stats.gather_fixed_objects_stats;
27. When was the last time processing rate stats were collected -- my guess is never because processing rate stats are new to 12c

exec dbms_stats.gather_processing_rate('START', 20);
28. Examine enabled degree of parallelism -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

SELECT degree, COUNT(*)
FROM dba_tables
GROUP BY degree;

SELECT degree, COUNT(*)
FROM dba_indexes
GROUP BY degree;
29. Determine who has access to the SYSTEM and SYSAUX tablespaces. Remove quota and move objects found in violation of Oracle's advice. -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

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;
30. Set an appropriate FAST_START_MTTR_TARGET conn / as sysdba

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
31. Look for security compromised by public synonyms -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

SELECT DISTINCT table_owner
FROM dba_synonyms
WHERE owner = 'PUBLIC';
32. 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.
33. Is TCP/IP Stack Optimization in place See Linux Oracle Installation and verify for your operating system version.
34. Is auditing enabled? -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

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;
35. Is Network ACL too open? -- if a non-container database
conn / as sysdba
-- connect to each PDB in turn and run the following queries

SELECT *
FROM dba_network_acl_privileges
WHERE end_date < SYSDATE
ORDER BY start_date;
36. What are the AWR parameters /* recommend collecting every 15 to 20 minutes and retention to a minimum of 31 days. use the DBMS_WORKLOAD_REPOSITORY link at page bottom to do so. */

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);
37. Patch Inventory -- verify the ORACLE_HOME environment variable has been set
echo $ORACLE_HOME
-- if it has not been then set it

cd $ORACLE_HOME/OPatch

opatch lsinventory -all

opatch lsinventory -details

-- every time you apply a patch rerun these and keep the output for future reference
38. Is OS Watcher installed? If it isn't ... download from MyOracleSupport and set it up today.

Support Document Reference: OSWatcher Black Box (Includes: Video) [ID 301137.1]

Related Topics
ADR Command Interpreter
Auditing
DBMS_FEATURE_USAGE_REPORT
DBMS_FGA
DBMS_RESUMABLE
DBMS_UTILITY
DBMS_WORKLOAD_REPOSITORY
Developer Best Practice Guidelines
Oracle Best Practice Guidelines
Remote Diagnostic Agent (RDA)
Security
System Statistics
Troubleshooting Best Practice Guidelines