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;
-- 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;
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
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;
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
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;
-- 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;
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.
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.
-- 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?
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;
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