Oracle DBA Best Practices
Version 21c

Overview
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 attention 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. 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
3. Determine if there is block level corruption. Report new blocks corrupted and corrupted blocks 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.
4. 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
5. Look for newly invalidated objects and unusable indexes conn / as sysdba

SELECT con_id, owner, object_type, COUNT(*)
FROM cdb_objects_ae
WHERE status = 'INVALID'
GROUP BY con_id, owner, object_type
ORDER BY 1,2,3;

-- if invalid objects are found DROP them or run {$ORACLE_HOME}/rdbms/admin/utlrp.sql

SELECT con_id, owner, table_name, index_name
FROM cdb_indexes
WHERE status = 'UNUSABLE'
ORDER BY 1,2,3;

-- if unusable indexes are found ALTER or DROP them.
6. Are there any hung resumable sessions SELECT user_id, session_id, status, suspend_time, error_number
FROM dba_resumable;
7. 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;
8. Backup Control File to Trace ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/control_file.bkp';
9. 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-DEC-2020 04:42:50 OPEN         ALLOWED
mlm01p2          usml9001b    15-DEC-2020 12:52:17 OPEN         ALLOWED
10. 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.
set linesize 181
set pagesize 99
col MMDD format a4
col 00 format 999
col 01 format 999
col 02 format 999
col 03 format 999
col 04 format 999
col 05 format 999
col 06 format 999
col 07 format 999
col 08 format 999
col 09 format 999
col 10 format 999
col 11 format 999
col 12 format 999
col 13 format 999
col 14 format 999
col 15 format 999
col 16 format 999
col 17 format 999
col 18 format 999
col 19 format 999
col 20 format 999
col 21 format 999
col 22 format 999
col 23 format 999

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
---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
1209  16  11   9   8   8  10  12   8   8  10   8  10  14  10  11  15  15   8  12   8   7   6   9   7
1210  13  12   8   9   7   6  11   9   6   8   7   8  12   6   7   6   8   7  10   7   4   4   4   5
1211  12   8   5   9   9   7  11   7   6   7   8   5  12   9  10   8   9  12  12  10   6   6   9   8
1212  13  12   7   9   7   9  10  10   7   7   9   8  11   7   7   8   7   7  11   9   5   6   8   7
1213  12  11   7   8   8   7  13   7   9   7   8   7  13  10   9   8   8   8  11   8   7   5   7   6
1214  15  10   9   9   8   9  13   9   9   7  11  13  11   9   8   9  13   9  12   9   7   9   7   7
1215  15  10  10   8  10   9  12   8   9   8   9   7  13   6   8   7   7   7  15  10   7   7   7   5
1216  13   8   8   7   7   6  10   8  11   7   8   6  11   7  12  13  13  14  13   9   9   9   7   8
1217  15  13  10   9   8   9  16   8   8  10   9  10  16  11  10  10   8  11  13   8   9   9   7   9
1218  12  13  15  15  13  13  15  13   9  12   8  11  14   9  10   9   9   8  14   9   8   8   9   8
1219  16  11  10  11   9   9  13  12  10   9  12  12  17   8   9   9  11  11  14   9   9  11  10  12
1220  19  15  11  10  10  10  19  11   9   9   9   9  13   7  15  10  11  11  12  10   9  11  11  10
1221  13  16  11   9  10  13  16   8  14   9  11  12  17  10  10  11   8  11  14   8  11  14   8  11
1222  16  13  13  11  11   9  16   9   9  11  10  11  17  10   9  10  10  10  13  14   9  10  10   8
1223  19  13  12  13  13  11  16  12  11  11  11  11  16   9  10  13   2  14  14   8   9   8   8   8
1224  14   9   9   9   7   9  11   8   8   7   8   8  14   7   8   7   9   3   6   0   0   0   0   0
1225   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2   0   0   0   0
1226   0   1   0   0   0   0   0   0   0   4   0   0   0   2   2   3   2   7   5   6   1   0   0   0
1227   3  10   0   0   0   5   0   1  10   0   0   0   0   0   1   0   1   0   2   5   3   7   1   0
1229   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   4   0   4   0   0   6   7   6
1230   7   4  23  19   9  10   5   6   7  17  19  17  15  17  15  43  40  32  17  15  14  20  13  15
1231  15  12  14  12  13  12  13  17  15  17  20  20  18  18  17  15  14  13  10  10  15  15  13  19
0101  21  22  20  18  14  14  12  13  11  11  14  14  14  10   9  10   9  10  11   9  11   9  10  12
0102   9  13  10  17  14  17  15  17  23  20  19  20  17  19  16  17  15  17  15  15  15  16  16  18
0103  22  19  19  18  16  15  13  13  14  11  13  10  12  14  10  12  14  11   9  11  12  13  12   9
0104  14  13   9  11  10  12  13  11  11   8  10  10  11  11  11  12  10  10   9  10   8   9  12   7
0105  14  15  11  12   9  15  13  12  12   9  12  14  12  12  12  12  13  11   8   9  12  13   2   0
0106   0   0   1   0   3  15  10  10   7   8  10  11  12   8   6   9  13  12   9   8   9   8  10  10
0107  16   9   8  15  10  11   9   8   8  14   9  10  10   8   8  14  15  10   9   9   8   9  10  10
0108  13  12   9  10  10   9   9  10  11  11   8   9   9   8   9  13   8   9   6   9   9  11  10   9
0109  12  10   9  10   9  12   9   8   8  11   7  10  11   9   9  13  10   9   8   9  11  12  10  10
0110  15  12   9  13   9  12   8  10  11  13   9   8  10   9   8  12  11  12   9   9  10  11  10   8
0111  13  12  10  13  10  10   9   7  10  11   9  10  12  12  12  15  12   9   8   9  11  12  12  12
0112  14  12  12  11  10  10  12  12  12  15  10  11  11  10   4   5  15  14  10   9   8   8  13   6
0113  12  12   9   9  11  10  10   9  10   9  14   7   7   8   8   9  14   9   9  10  12   8  13  10
0114  10  10   9  14  12  15  12  14  13  15  10  11   9   4   8   6   8   7   6   7   8   8   8   8
0115  10  11   9   8   8   9   9   6   6   7   7  12   7   9  15  14  13  16  12  14  11   9   6   7
0116  10  10   9   9   9  10  12  14  11  10  12   9   8  12   7   3   0   0   0   0   0   0   0   0
11. 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             INPUT_TYPE STATUS
-------------------- -------------------- ---------- ---------
24-JAN-2021 17:34:47 24-JAN-2021 18:59:18 DB_FULL    COMPLETED
24-JAN-2021 23:40:21 25-JAN-2021 01:06:55 DB_FULL    COMPLETED
25-JAN-2021 05:40:21 25-JAN-2021 06:46:35 DB_FULL    COMPLETED
25-JAN-2021 08:39:03 25-JAN-2021 08:40:24 DB_FULL    COMPLETED
25-JAN-2021 11:47:12 25-JAN-2021 13:17:40 DB_FULL    COMPLETED
25-JAN-2021 17:43:16 25-JAN-2021 19:14:50 DB_FULL    COMPLETED
25-JAN-2021 23:46:11 26-JAN-2021 01:32:38 DB_FULL    COMPLETED
26-JAN-2021 05:47:34 26-JAN-2021 07:36:51 DB_FULL    COMPLETED
26-JAN-2021 11:47:27 26-JAN-2021 13:18:06 DB_FULL    COMPLETED
26-JAN-2021 17:48:53 26-JAN-2021 18:52:53 DB_FULL    COMPLETED
26-JAN-2021 23:48:43 27-JAN-2021 01:05:57 DB_FULL    COMPLETED
27-JAN-2021 05:48:08 27-JAN-2021 07:05:31 DB_FULL    COMPLETED
27-JAN-2021 08:38:42 27-JAN-2021 08:40:11 DB_FULL    COMPLETED
27-JAN-2021 11:54:09 27-JAN-2021 13:22:56 DB_FULL    COMPLETED
27-JAN-2021 17:50:09 27-JAN-2021 19:50:18 DB_FULL    COMPLETED
27-JAN-2021 23:50:20 28-JAN-2021 01:14:51 DB_FULL    COMPLETED
28-JAN-2021 05:50:20 28-JAN-2021 07:15:22 DB_FULL    COMPLETED
28-JAN-2021 11:50:59 28-JAN-2021 13:11:05 DB_FULL    COMPLETED
28-JAN-2021 17:51:01 28-JAN-2021 19:11:44 DB_FULL    COMPLETED
28-JAN-2021 23:54:16 29-JAN-2021 01:22:42 DB_FULL    COMPLETED
29-JAN-2021 05:56:23 29-JAN-2021 07:12:03 DB_FULL    COMPLETED
29-JAN-2021 08:35:47 29-JAN-2021 08:37:06 DB_FULL    COMPLETED
29-JAN-2021 12:00:03 29-JAN-2021 13:41:28 DB_FULL    COMPLETED
29-JAN-2021 17:55:18 29-JAN-2021 19:30:54 DB_FULL    COMPLETED
29-JAN-2021 23:55:47 30-JAN-2021 01:25:14 DB_FULL    COMPLETED
30-JAN-2021 05:56:34 30-JAN-2021 07:45:18 DB_FULL    COMPLETED
30-JAN-2021 11:58:41 30-JAN-2021 13:27:15 DB_FULL    COMPLETED
30-JAN-2021 18:02:45 30-JAN-2021 19:02:32 DB_FULL    COMPLETED
30-JAN-2021 23:56:36 31-JAN-2021 01:04:36 DB_FULL    COMPLETED
31-JAN-2021 06:02:03 31-JAN-2021 07:18:37 DB_FULL    COMPLETED
31-JAN-2021 11:59:26 31-JAN-2021 13:18:13 DB_FULL    COMPLETED
31-JAN-2021 17:58:57 31-JAN-2021 19:05:34 DB_FULL    COMPLETED
31-JAN-2021 23:59:34 01-FEB-2021 01:09:43 DB_FULL    COMPLETED
01-FEB-2021 05:59:51 01-FEB-2021 07:02:11 DB_FULL    COMPLETED
01-FEB-2021 08:39:17 01-FEB-2021 08:40:36 DB_FULL    COMPLETED
01-FEB-2021 12:06:08 01-FEB-2021 13:50:32 DB_FULL    COMPLETED
01-FEB-2021 17:59:48 01-FEB-2021 19:44:28 DB_FULL    COMPLETED
02-FEB-2021 00:00:41 02-FEB-2021 01:30:53 DB_FULL    COMPLETED
02-FEB-2021 06:02:12 02-FEB-2021 07:34:12 DB_FULL    COMPLETED
02-FEB-2021 12:02:02 02-FEB-2021 13:36:45 DB_FULL    COMPLETED
02-FEB-2021 18:02:13 02-FEB-2021 19:15:06 DB_FULL    COMPLETED
03-FEB-2021 00:04:59 03-FEB-2021 01:31:11 DB_FULL    COMPLETED
03-FEB-2021 06:04:10 03-FEB-2021 07:13:35 DB_FULL    COMPLETED
03-FEB-2021 08:37:19 03-FEB-2021 08:38:52 DB_FULL    COMPLETED
03-FEB-2021 12:15:05 03-FEB-2021 14:05:17 DB_FULL    COMPLETED
03-FEB-2021 18:04:46 03-FEB-2021 19:44:57 DB_FULL    COMPLETED
04-FEB-2021 00:05:56                      DB_FULL    FAILED
04-FEB-2021 02:00:54 04-FEB-2021 03:30:21 DB_FULL    COMPLETED
04-FEB-2021 08:01:03 04-FEB-2021 09:27:16 DB_FULL    COMPLETED
04-FEB-2021 14:09:03 04-FEB-2021 15:09:35 DB_FULL    COMPLETED
04-FEB-2021 20:03:17 05-FEB-2021 00:46:34 DB_FULL    COMPLETED WITH ERRORS
05-FEB-2021 02:01:54 05-FEB-2021 03:05:00 DB_FULL    COMPLETED
05-FEB-2021 08:01:49 05-FEB-2021 09:16:59 DB_FULL    COMPLETED
05-FEB-2021 08:36:15 05-FEB-2021 08:37:47 DB_FULL    COMPLETED
05-FEB-2021 14:01:35 05-FEB-2021 15:31:33 DB_FULL    COMPLETED
05-FEB-2021 20:01:23 05-FEB-2021 22:05:26 DB_FULL    COMPLETED WITH ERRORS
06-FEB-2021 02:03:05 06-FEB-2021 03:37:16 DB_FULL    COMPLETED
06-FEB-2021 08:02:56 06-FEB-2021 09:24:00 DB_FULL    COMPLETED
06-FEB-2021 14:07:53 06-FEB-2021 15:12:10 DB_FULL    COMPLETED
06-FEB-2021 20:01:50 06-FEB-2021 21:06:41 DB_FULL    FAILED
07-FEB-2021 02:01:42 07-FEB-2021 03:16:17 DB_FULL    COMPLETED
07-FEB-2021 08:02:35 07-FEB-2021 09:43:35 DB_FULL    COMPLETED
07-FEB-2021 14:03:12 07-FEB-2021 15:14:44 DB_FULL    COMPLETED
07-FEB-2021 20:03:56                      DB_FULL    FAILED
08-FEB-2021 02:04:27 08-FEB-2021 03:24:14 DB_FULL    COMPLETED
08-FEB-2021 08:04:43 08-FEB-2021 09:44:53 DB_FULL    COMPLETED
08-FEB-2021 08:37:11 08-FEB-2021 08:38:28 DB_FULL    COMPLETED
08-FEB-2021 14:05:53 08-FEB-2021 15:52:53 DB_FULL    COMPLETED
08-FEB-2021 20:04:20 08-FEB-2021 22:19:13 DB_FULL    FAILED
09-FEB-2021 02:05:12 09-FEB-2021 04:15:11 DB_FULL    COMPLETED
09-FEB-2021 08:07:40 09-FEB-2021 10:15:32 DB_FULL    COMPLETED
09-FEB-2021 14:06:40 09-FEB-2021 15:17:38 DB_FULL    COMPLETED
09-FEB-2021 20:05:35 09-FEB-2021 21:47:53 DB_FULL    FAILED
10-FEB-2021 02:06:13 10-FEB-2021 03:46:37 DB_FULL    COMPLETED
10-FEB-2021 08:06:56 10-FEB-2021 09:44:15 DB_FULL    COMPLETED
10-FEB-2021 08:44:16 10-FEB-2021 08:45:33 DB_FULL    COMPLETED
10-FEB-2021 14:06:14 10-FEB-2021 15:46:15 DB_FULL    COMPLETED
10-FEB-2021 20:07:30 10-FEB-2021 21:54:22 DB_FULL    FAILED
11-FEB-2021 00:53:58 11-FEB-2021 03:07:22 DB_FULL    COMPLETED
11-FEB-2021 06:53:31 11-FEB-2021 08:32:46 DB_FULL    COMPLETED
12. 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;
13. 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-JAN-2021 23:02:27
 2         ONLINE                             10 NO  01-JAN-2021 23:02:27
 3         ONLINE                             10 NO  01-JAN-2021 23:02:27
 4         ONLINE                             10 NO  01-JAN-2021 23:02:27
 5         ONLINE                             10 NO  01-JAN-2021 23:02:27
 6         ONLINE                             10 NO  01-JAN-2021 23:02:27
 7         ONLINE                             10 NO  01-JAN-2021 23:02:27
 8         ONLINE                             10 NO  01-JAN-2021 23:02:27
 9         ONLINE                             10 NO  01-JAN-2021 23:02:27
10         ONLINE                             10 NO  01-JAN-2021 23:02:27
11         ONLINE                             10 NO  01-JAN-2021 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-JAN-2021 17:25:24
 2         ONLINE                             10 NO  02-JAN-2021 17:25:24
 3         ONLINE                             10 NO  02-JAN-2021 17:25:24
 4         ONLINE                             10 NO  02-JAN-2021 17:25:24
 5         ONLINE                             10 NO  02-JAN-2021 17:25:24
 6         ONLINE                             10 NO  02-JAN-2021 17:25:24
 7         ONLINE                             10 NO  02-JAN-2021 17:25:24
 8         ONLINE                             10 NO  02-JAN-2021 17:25:24
 9         ONLINE                             10 NO  02-JAN-2021 17:25:24
10         ONLINE                             10 NO  02-JAN-2021 17:25:24
11         ONLINE                             10 NO  02-JAN-2021 17:25:24
14. 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;
15. 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 ... 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.
16. Look for invalid date constraints @?/rdbms/admin/utlconst.sql
17. Look for dependency timestamp errors @?/rdbms/admin/utldtchk.sql
18. 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)
19. What is the status of the database's containers? SELECT con_id, dbid, name, open_mode
FROM v$pdbs
ORDER BY 1;

 CON_ID    DBID       NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 3298821576 PDB$SEED                       READ ONLY
         3 1823093744 TEST21P1                       READ WRITE
20. 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;

no rows selected
21. Check undo tablespace size and resize 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;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
22. Increase undo tablespace retention time to 12-24 hours. ALTER SYSTEM SET undo_retention=43200 CONTAINER=ALL SID='*' SCOPE=BOTH;

System altered.
23. 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.
col sequence_owner format a30

SELECT sequence_owner, cache_size, COUNT(*)
FROM dba_sequences
WHERE sequence_owner NOT LIKE 'SYS%'
GROUP BY sequence_owner, cache_size
ORDER BY 1,2;

SEQUENCE_OWNER            CACHE_SIZE   COUNT(*)
------------------------- ---------- ----------
CTXSYS                            20          3
DBSNMP                            20          2
DVSYS                              0         22
GSMADMIN_INTERNAL                  0          4
GSMADMIN_INTERNAL                  9          1
GSMADMIN_INTERNAL                 20         12
LBACSYS                           20          3
MDSYS                              0         12
MDSYS                             10          1
MDSYS                             20          5
OJVMSYS                           20          2
OLAPSYS                           20          1
ORDDATA                            0         13
WMSYS                              0          5
WMSYS                             20          1
XDB                               10          1
XDB                               20          4
24. 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;

no rows selected
25. Disabled Constraints SELECT owner, constraint_name, constraint_type
FROM dba_constraints
WHERE status = 'DISABLED'
ORDER BY 1,2;

OWNER                     CONSTRAINT_NAME                C
------------------------- ------------------------------ -
SYS                       ATTRIBUTE_TRANSFORMATIONS_FK   R
SYS                       DAM_CONFIG_PARAM_FK1           R
SYS                       DEPENDENCIES_FK                R
SYS                       DEPENDENCIES_REQ_FK            R
SYS                       JAVA_DEV_DISABLED              C
...
SYSTEM                    LOGMNR_TABPART$_PK             P
SYSTEM                    LOGMNR_TABSUBPART$_PK          P
SYSTEM                    LOGMNR_TS$_PK                  P
SYSTEM                    LOGMNR_TYPE$_PK                P
SYSTEM                    LOGMNR_USER$_PK                P
26. Disabled Triggers col trigger_name format a30

SELECT owner, trigger_name, trigger_type
FROM dba_triggers
WHERE status = 'DISABLED'
ORDER BY 1,3,2;

OWNER                     TRIGGER_NAME                   TRIGGER_TYPE
------------------------- ------------------------------ ----------------
LBACSYS                   LBAC$AFTER_CREATE              AFTER EVENT
LBACSYS                   LBAC$AFTER_DROP                AFTER EVENT
LBACSYS                   LBAC$BEFORE_ALTER              BEFORE EVENT
MDSYS                     SDO_TOPO_DROP_FTBL             BEFORE EVENT
SYS                       SYSLSBY_EDS_DDL_TRIG           AFTER EVENT
SYS                       DBMS_JAVA_DEV_TRG              BEFORE EVENT
SYS                       LOGMNRGGC_TRIGGER              BEFORE EVENT
WMSYS                     NO_VM_DROP_A                   AFTER EVENT
WMSYS                     NO_VM_DDL                      BEFORE EVENT
27. 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';

NAME                           VALUE                UPDATE_COMMENT
------------------------------ -------------------- ------------------------------
plsql_warnings                 DISABLE:ALL


ALTER SYSTEM SET plsql_warnings = 'ENABLE:ALL'
COMMENT = 'Enabled in all containers 01-01-2021';
28. 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) STIME, status, oper_type, oper_mode, parameter
FROM v$sga_resize_ops
WHERE initial_size <> final_size;

sho parameter memory_max_target
sho parameter memory_target
sho parameter sga_target
sho parameter pga_aggregate_target

-- to convert from AMM back to ASMM
ALTER SYSTEM SET memory_max_target=0 SID='*' SCOPE=SPFILE;
ALTER SYSTEM SET memory_target=0 SID='*' SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=<value> SID='*' SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=<value> SID='*' SCOPE=SPFILE;
-- restart your database
29. 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)';
30. 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;
31. Identify Tables and Indexes with Stale Statistics SELECT owner, stale_stats, COUNT(*)
FROM dba_tab_statistics
GROUP BY owner, stale_stats
ORDER BY 1,2;

set timing on
BEGIN
  FOR srec IN (SELECT UNIQUE owner, table_name, partition_name
               FROM dba_tab_statistics
               WHERE stale_stats = 'YES') LOOP
    IF srec.partition_name IS NULL THEN
      dbms_stats.gather_table_stats('SYS', srec.table_name, CASCADE=>TRUE);
    ELSE
      dbms_stats.gather_table_stats('SYS', srec.table_name, srec.partition_name, CASCADE=>TRUE);
    END IF;
  END LOOP;
END;
/
set timing off

SELECT owner, partition_name, COUNT(*)
FROM dba_ind_statistics
WHERE stale_stats = 'YES'
GROUP BY owner, partition_name
ORDER BY 1,2;
32. 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 re-verify on a regular basis?
1. Is it a container database? conn / as sysdba

SELECT con_id, dbid, name, open_mode
FROM v$containers;
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, supplemental_log_data_pl
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 a55

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;
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.con_id, d.username, d.product
FROM cdb_users_with_defpwd d, cdb_users u
WHERE d.username = u.username
AND u.account_status = 'OPEN'
ORDER BY 1,2;

 CON_ID USERNAME
------- ------------------------------
      1 ORDSYS
      1 ORDSYS
      1 XDB
      1 XDB
      4 HR
      4 SCOTT


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 a Feature Usage Report (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 DATABASE FORCE LOGGING;
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. If resource management is in use 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. If a container database when were CDBVW stats last collected? -- my guess is never because DBAs likely are not even aware that they exist

SELECT dbms_pdb.update_cdbvw_stats;
29. 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;
30. 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;
31. 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
32. 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'
ORDER BY 1;
33. 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.
34. Is TCP/IP Stack Optimization in place? See Linux Oracle Installation and verify for your operating system version.
35. 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, num_rows
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY 1;
36. 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;
37. 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);
38. Do you have a copy of your current 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
39. 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]
40. Are you monitoring Space Utilization? WITH d AS (SELECT SUM(bytes/1024/1024) AS DATA_FILES_MB FROM dba_data_files),
     t AS (SELECT SUM(bytes/1024/1024) AS TEMP_FILES_MB FROM dba_temp_files)
SELECT data_files_mb, temp_files_mb
FROM d, t;

SELECT DISTINCT bytes/1024/1024 AS LOG_SIZE_MB
FROM v$log;

SELECT GROUP#, MEMBER
FROM v$logfile;

SELECT tablespace_name, SUM(BYTES) FREE_SIZE
FROM dba_free_space
GROUP BY tablespace_name;
41. Is NUMA enabled? You should know whether your server and operating system are supporting NUMA and if your database configuration matches that of your infrastructure resources: If not then reconfigure so that they match either by disabling server support or enabling database support.

Use the NUMA link at the bottom of this page to get the information you need to monitor your configuration
42. Is the database an Oracle Autonomous Database? -- Oracle OCI Database

SELECT cloud_identity FROM v$pdbs;

CLOUD_IDENTITY
--------------------------------------------



-- Oracle Autonomous Database

SELECT cloud_identity FROM v$pdbs;

CLOUD_IDENTITY
----------------------------------------------------------------------------------------------
{
  "DATABASE_NAME" : "DB202103231419",
  "REGION" : "us-ashburn-1",
  "TENANT_OCID" : "OCID1.TENANCY.OC1..AAAAAAAAC4Q677U6GLMPUD2VM2D5QAAQQEBJ4V56FDMTFULZIWQAJGS7NX4Q",
  "DATABASE_OCID" : "OCID1.AUTONOMOUSDATABASE.OC1.IAD.ABUWCLJRTLAJ5GRGPWCTA4ZNNJBZPEGRHD4QP6MQGJDEWQNJP62YL7WZY25A",
  "COMPARTMENT_OCID" : "ocid1.tenancy.oc1..aaaaaaaac4q677u6glmpud2vm2d5qaaqqebj4v56fdmtfulziwqajgs7nx4q",
  "OUTBOUND_IP_ADDRESS" :
  [
    "150.136.133.92"
  ]
}
43. Are Huge Pages in use and properly configured? -- For best performance your database should use Large Pages (aka Huge Pages). A page is being written for the library which will cover this topic. Until that page is posted here are a few notes to help you with the topic.

show parameter pages

NAME             TYPE    VALUE
---------------- ------- -----
use_large_pages  string  TRUE


Set to ONLY

Note: 1134002.1
Note: 401749.1
Kevin Closson's Blog
https://docs.oracle.com/database/121/REFRN/refrn10320.html#REFRN10320
44. Did you Revoke automatic inheritance from PUBLIC? -- if a non-container database

conn / as sysdba

-- connect to each PDB in turn and run the following queries

conn sys@pdbdev as sysdba

SELECT 'REVOKE INHERIT PRIVILEGES ON USER ' || username || ' FROM public'
FROM dba_users
WHERE username NOT IN ('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', 'GGSYS', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'LBACSYS', 'MDDATA', 'MDSYS', 'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGNS', 'ORDSYS', 'OUTLN', 'ORDPLUGINS', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SYS', 'SYSTEM', 'SYS$UMF', 'SYSBACKUP', 'SYSDG', 'SYSKM', 'SYSRAC', 'WMSYS', 'XDB', 'XS$NULL')
ORDER BY username;
45. What poorly written code is in the databases I am managing? conn / as sysdba

SELECT name, COUNT(*)
FROM user_errors
GROUP BY name
ORDER BY 1;

NAME                           COUNT(*)
------------------------------ --------
TKHCS_LOG_LISTAGGCLOB                 1
TKHCS_LOG_LISTAGGCLOBTYPE             9
TKHCS_LOG_PKG                         1
UTL_MAIL                             10
UTL_MAIL_INTERNAL                     4
UTL_PG                               27
UTL_SYS_COMPRESS                      1


set serveroutput on

DECLARE
 RetVal get_error$.myrcType;
 RetRec get_error$.myRec;
 x      INTEGER;

 TYPE array_t IS TABLE OF VARCHAR2(4000)INDEX BY BINARY_INTEGER;
 rec_array array_t;
BEGIN
  RetVal := get_error$.error_lines('UTL_MAIL');

  FETCH RetVal BULK COLLECT INTO rec_array;
  FOR i IN rec_array.FIRST .. rec_array.LAST LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(TO_CHAR(rec_array.COUNT));
END;
/
PLW-07203: parameter 'ADDR_LIST' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SMTP_SERVERS' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SMTP_PORTS' may benefit from use of the NOCOPY compiler hint
PLW-06010: keyword "DATA" used as a defined name
PLW-06010: keyword "DATA" used as a defined name
PLW-06010: keyword "DATA" used as a defined name
PLW-06009: procedure "SEND_I" OTHERS handler does not end in RAISE
PLW-06002: Unreachable code
PLW-06002: Unreachable code
PLW-06002: Unreachable code

Related Topics
ADR Command Interpreter
Auditing
Database Security
DBMS_FEATURE_USAGE_REPORT
DBMS_FGA
DBMS_RESUMABLE
DBMS_UTILITY
DBMS_WORKLOAD_REPOSITORY
Developer Best Practice Guidelines
Large Pages
NUMA
Oracle Best Practice Guidelines
Remote Diagnostic Agent (RDA)
System Statistics
Troubleshooting Best Practice Guidelines
What's New In 21c
What's New In 23c