| Overview |
One question that comes up frequently during DBA Boot Camps is ... "what is my job?"
Another is "when I take over a new system what should I do first?"
With that in mind this page is dedicated to collected so-called "best practice" advice on what to do in these situations. Actions are on the left ... code on the right. |
| |
| What should I do today? |
| 1. Review the alert log |
See the demos under ADR Command Interpreter and External Tables |
|
2. Determine if there is block level corruption. Report new blocks corrupted and corrupted remaining from the previous day's report |
conn / as sysdba
col corruption_change# format 99999999999999999999
SELECT * FROM v$database_block_corruption ORDER BY 1, 3;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- --------------------- ---------
22 162844 4 10594075667556 CORRUPT
-- if corruption is found use the following SQL to identify the corrupt segment(s)
SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = 22
AND 162844 BETWEEN block_id AND (block_id+(blocks-1));
-- then consider using DBMS_REPAIR to repair the corruption. |
| 3. Verify the status of last night's backup. Report new backup corruption and previous unmitigated backup corruption events |
conn / as sysdba
SELECT inst_id, set_stamp, piece#, file#, block#, blocks, marked_corrupt, corruption_type
FROM gv$backup_corruption;
INST_ID SET_STAMP PIECE# FILE# BLOCK# BLOCKS MAR CORRUPTIO
---------- ---------- ---------- ---------- ---------- ---------- --- ---------
6 711749250 1 22 103631 3 NO LOGICAL
SELECT inst_id, recid, stamp, copy_recid, file#, block#, blocks, marked_corrupt, corruption_type
FROM gv$copy_corruption where rownum = 1;
INST_ID RECID STAMP COPY_RECID FILE# BLOCK# BLOCKS MAR CORRUPTIO
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------
6 1 705195114 9 22 39918 1 NO LOGICAL |
| 4. Look for newly invalidated objects and unusable indexes |
conn / as sysdba
SELECT owner, object_type, COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID'
GROUP BY owner, object_type;
SELECT owner, table_name, index_name
FROM dba_indexes
WHERE status = 'UNUSABLE'; |
| 5. Are there any hung resumable sessions |
SELECT user_id, session_id, status, suspend_time, error_number
FROM dba_resumable; |
| 6. Are there any blocked sessions |
SELECT (
SELECT username
FROM gv$session
WHERE sid=a.sid) blocker,
a.sid, ' is blocking ', (
SELECT username
FROM gv$session
WHERE sid=b.sid) blockee, b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2; |
| 7. Backup Control File to Trace |
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/control_file.bkp'; |
| 8. Verify the system did not restart without your knowledge |
col host_name format a15
SELECT instance_name, host_name, startup_time, status, logins
FROM gv$instance
ORDER BY 1;
INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS LOGINS
---------------- ------------ -------------------- ------------ ----------
mlm01p1 usml9001a 07-JUL-2010 04:42:50 OPEN ALLOWED
mlm01p2 usml9001b 15-JUL-2010 12:52:17 OPEN ALLOWED |
9. Look for anomalies in log switch frequency and switch frequencies greater than 12 per hour.
For example there seem to be a couple of patterns visible in the data to the right and some obvious outages.
In this real production data you can see clear evidence that if the DBA had not been asleep at the wheel the problems might have been caught and dealt with before the outages. And do you get the impression that some things have been timed for 00, 06, 12, and 18 hrs? How predictable. |
SELECT TO_CHAR(first_time,'MMDD') MMDD,
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') "21",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22",
TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
GROUP BY TO_CHAR(first_time,'MMDD')
ORDER BY 1;
MMDD 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
0609 16 11 9 8 8 10 12 8 8 10 8 10 14 10 11 15 15 8 12 8 7 6 9 7
0610 13 12 8 9 7 6 11 9 6 8 7 8 12 6 7 6 8 7 10 7 4 4 4 5
0611 12 8 5 9 9 7 11 7 6 7 8 5 12 9 10 8 9 12 12 10 6 6 9 8
0612 13 12 7 9 7 9 10 10 7 7 9 8 11 7 7 8 7 7 11 9 5 6 8 7
0613 12 11 7 8 8 7 13 7 9 7 8 7 13 10 9 8 8 8 11 8 7 5 7 6
0614 15 10 9 9 8 9 13 9 9 7 11 13 11 9 8 9 13 9 12 9 7 9 7 7
0615 15 10 10 8 10 9 12 8 9 8 9 7 13 6 8 7 7 7 15 10 7 7 7 5
0616 13 8 8 7 7 6 10 8 11 7 8 6 11 7 12 13 13 14 13 9 9 9 7 8
0617 15 13 10 9 8 9 16 8 8 10 9 10 16 11 10 10 8 11 13 8 9 9 7 9
0618 12 13 15 15 13 13 15 13 9 12 8 11 14 9 10 9 9 8 14 9 8 8 9 8
0619 16 11 10 11 9 9 13 12 10 9 12 12 17 8 9 9 11 11 14 9 9 11 10 12
0620 19 15 11 10 10 10 19 11 9 9 9 9 13 7 15 10 11 11 12 10 9 11 11 10
0621 13 16 11 9 10 13 16 8 14 9 11 12 17 10 10 11 8 11 14 8 11 14 8 11
0622 16 13 13 11 11 9 16 9 9 11 10 11 17 10 9 10 10 10 13 14 9 10 10 8
0623 19 13 12 13 13 11 16 12 11 11 11 11 16 9 10 13 2 14 14 8 9 8 8 8
0624 14 9 9 9 7 9 11 8 8 7 8 8 14 7 8 7 9 3 6 0 0 0 0 0
0625 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
0626 0 1 0 0 0 0 0 0 0 4 0 0 0 2 2 3 2 7 5 6 1 0 0 0
0627 3 10 0 0 0 5 0 1 10 0 0 0 0 0 1 0 1 0 2 5 3 7 1 0
0629 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 4 0 0 6 7 6
0630 7 4 23 19 9 10 5 6 7 17 19 17 15 17 15 43 40 32 17 15 14 20 13 15
0701 15 12 14 12 13 12 13 17 15 17 20 20 18 18 17 15 14 13 10 10 15 15 13 19
0702 21 22 20 18 14 14 12 13 11 11 14 14 14 10 9 10 9 10 11 9 11 9 10 12
0703 9 13 10 17 14 17 15 17 23 20 19 20 17 19 16 17 15 17 15 15 15 16 16 18
0704 22 19 19 18 16 15 13 13 14 11 13 10 12 14 10 12 14 11 9 11 12 13 12 9
0705 14 13 9 11 10 12 13 11 11 8 10 10 11 11 11 12 10 10 9 10 8 9 12 7
0706 14 15 11 12 9 15 13 12 12 9 12 14 12 12 12 12 13 11 8 9 12 13 2 0
0707 0 0 1 0 3 15 10 10 7 8 10 11 12 8 6 9 13 12 9 8 9 8 10 10
0708 16 9 8 15 10 11 9 8 8 14 9 10 10 8 8 14 15 10 9 9 8 9 10 10
0709 13 12 9 10 10 9 9 10 11 11 8 9 9 8 9 13 8 9 6 9 9 11 10 9
0710 12 10 9 10 9 12 9 8 8 11 7 10 11 9 9 13 10 9 8 9 11 12 10 10
0711 15 12 9 13 9 12 8 10 11 13 9 8 10 9 8 12 11 12 9 9 10 11 10 8
0712 13 12 10 13 10 10 9 7 10 11 9 10 12 12 12 15 12 9 8 9 11 12 12 12
0713 14 12 12 11 10 10 12 12 12 15 10 11 11 10 4 5 15 14 10 9 8 8 13 6
0714 12 12 9 9 11 10 10 9 10 9 14 7 7 8 8 9 14 9 9 10 12 8 13 10
0715 10 10 9 14 12 15 12 14 13 15 10 11 9 4 8 6 8 7 6 7 8 8 8 8
0716 10 11 9 8 8 9 9 6 6 7 7 12 7 9 15 14 13 16 12 14 11 9 6 7
0717 10 10 9 9 9 10 12 14 11 10 12 9 8 12 7 3 0 0 0 0 0 0 0 0 |
10. Verify that your backup was successful
The example, at right, demonstrates real-world failures that require follow-up by reading the corresponding RMAN log files.
Also be alert to repeated failures that occur at the same time of day as occurs in the output. |
-- this query modified slightly from the listing below
SELECT start_time, end_time, input_type, status
FROM v$rman_backup_job_details
ORDER BY 1;
START_TIME END_TIME STATUS
-------------------- -------------------- -----------------------
24-JAN-2012 17:34:47 24-JAN-2012 18:59:18 COMPLETED
24-JAN-2012 23:40:21 25-JAN-2012 01:06:55 COMPLETED
25-JAN-2012 05:40:21 25-JAN-2012 06:46:35 COMPLETED
25-JAN-2012 08:39:03 25-JAN-2012 08:40:24 COMPLETED
25-JAN-2012 11:47:12 25-JAN-2012 13:17:40 COMPLETED
25-JAN-2012 17:43:16 25-JAN-2012 19:14:50 COMPLETED
25-JAN-2012 23:46:11 26-JAN-2012 01:32:38 COMPLETED
26-JAN-2012 05:47:34 26-JAN-2012 07:36:51 COMPLETED
26-JAN-2012 11:47:27 26-JAN-2012 13:18:06 COMPLETED
26-JAN-2012 17:48:53 26-JAN-2012 18:52:53 COMPLETED
26-JAN-2012 23:48:43 27-JAN-2012 01:05:57 COMPLETED
27-JAN-2012 05:48:08 27-JAN-2012 07:05:31 COMPLETED
27-JAN-2012 08:38:42 27-JAN-2012 08:40:11 COMPLETED
27-JAN-2012 11:54:09 27-JAN-2012 13:22:56 COMPLETED
27-JAN-2012 17:50:09 27-JAN-2012 19:50:18 COMPLETED
27-JAN-2012 23:50:20 28-JAN-2012 01:14:51 COMPLETED
28-JAN-2012 05:50:20 28-JAN-2012 07:15:22 COMPLETED
28-JAN-2012 11:50:59 28-JAN-2012 13:11:05 COMPLETED
28-JAN-2012 17:51:01 28-JAN-2012 19:11:44 COMPLETED
28-JAN-2012 23:54:16 29-JAN-2012 01:22:42 COMPLETED
29-JAN-2012 05:56:23 29-JAN-2012 07:12:03 COMPLETED
29-JAN-2012 08:35:47 29-JAN-2012 08:37:06 COMPLETED
29-JAN-2012 12:00:03 29-JAN-2012 13:41:28 COMPLETED
29-JAN-2012 17:55:18 29-JAN-2012 19:30:54 COMPLETED
29-JAN-2012 23:55:47 30-JAN-2012 01:25:14 COMPLETED
30-JAN-2012 05:56:34 30-JAN-2012 07:45:18 COMPLETED
30-JAN-2012 11:58:41 30-JAN-2012 13:27:15 COMPLETED
30-JAN-2012 18:02:45 30-JAN-2012 19:02:32 COMPLETED
30-JAN-2012 23:56:36 31-JAN-2012 01:04:36 COMPLETED
31-JAN-2012 06:02:03 31-JAN-2012 07:18:37 COMPLETED
31-JAN-2012 11:59:26 31-JAN-2012 13:18:13 COMPLETED
31-JAN-2012 17:58:57 31-JAN-2012 19:05:34 COMPLETED
31-JAN-2012 23:59:34 01-FEB-2012 01:09:43 COMPLETED
01-FEB-2012 05:59:51 01-FEB-2012 07:02:11 COMPLETED
01-FEB-2012 08:39:17 01-FEB-2012 08:40:36 COMPLETED
01-FEB-2012 12:06:08 01-FEB-2012 13:50:32 COMPLETED
01-FEB-2012 17:59:48 01-FEB-2012 19:44:28 COMPLETED
02-FEB-2012 00:00:41 02-FEB-2012 01:30:53 COMPLETED
02-FEB-2012 06:02:12 02-FEB-2012 07:34:12 COMPLETED
02-FEB-2012 12:02:02 02-FEB-2012 13:36:45 COMPLETED
02-FEB-2012 18:02:13 02-FEB-2012 19:15:06 COMPLETED
03-FEB-2012 00:04:59 03-FEB-2012 01:31:11 COMPLETED
03-FEB-2012 06:04:10 03-FEB-2012 07:13:35 COMPLETED
03-FEB-2012 08:37:19 03-FEB-2012 08:38:52 COMPLETED
03-FEB-2012 12:15:05 03-FEB-2012 14:05:17 COMPLETED
03-FEB-2012 18:04:46 03-FEB-2012 19:44:57 COMPLETED
04-FEB-2012 00:05:56 FAILED
04-FEB-2012 02:00:54 04-FEB-2012 03:30:21 COMPLETED
04-FEB-2012 08:01:03 04-FEB-2012 09:27:16 COMPLETED
04-FEB-2012 14:09:03 04-FEB-2012 15:09:35 COMPLETED
04-FEB-2012 20:03:17 05-FEB-2012 00:46:34 COMPLETED WITH ERRORS
05-FEB-2012 02:01:54 05-FEB-2012 03:05:00 COMPLETED
05-FEB-2012 08:01:49 05-FEB-2012 09:16:59 COMPLETED
05-FEB-2012 08:36:15 05-FEB-2012 08:37:47 COMPLETED
05-FEB-2012 14:01:35 05-FEB-2012 15:31:33 COMPLETED
05-FEB-2012 20:01:23 05-FEB-2012 22:05:26 COMPLETED WITH ERRORS
06-FEB-2012 02:03:05 06-FEB-2012 03:37:16 COMPLETED
06-FEB-2012 08:02:56 06-FEB-2012 09:24:00 COMPLETED
06-FEB-2012 14:07:53 06-FEB-2012 15:12:10 COMPLETED
06-FEB-2012 20:01:50 06-FEB-2012 21:06:41 FAILED
07-FEB-2012 02:01:42 07-FEB-2012 03:16:17 COMPLETED
07-FEB-2012 08:02:35 07-FEB-2012 09:43:35 COMPLETED
07-FEB-2012 14:03:12 07-FEB-2012 15:14:44 COMPLETED
07-FEB-2012 20:03:56 FAILED
08-FEB-2012 02:04:27 08-FEB-2012 03:24:14 COMPLETED
08-FEB-2012 08:04:43 08-FEB-2012 09:44:53 COMPLETED
08-FEB-2012 08:37:11 08-FEB-2012 08:38:28 COMPLETED
08-FEB-2012 14:05:53 08-FEB-2012 15:52:53 COMPLETED
08-FEB-2012 20:04:20 08-FEB-2012 22:19:13 FAILED
09-FEB-2012 02:05:12 09-FEB-2012 04:15:11 COMPLETED
09-FEB-2012 08:07:40 09-FEB-2012 10:15:32 COMPLETED
09-FEB-2012 14:06:40 09-FEB-2012 15:17:38 COMPLETED
09-FEB-2012 20:05:35 09-FEB-2012 21:47:53 FAILED
10-FEB-2012 02:06:13 10-FEB-2012 03:46:37 COMPLETED
10-FEB-2012 08:06:56 10-FEB-2012 09:44:15 COMPLETED
10-FEB-2012 08:44:16 10-FEB-2012 08:45:33 COMPLETED
10-FEB-2012 14:06:14 10-FEB-2012 15:46:15 COMPLETED
10-FEB-2012 20:07:30 10-FEB-2012 21:54:22 FAILED
11-FEB-2012 00:53:58 11-FEB-2012 03:07:22 COMPLETED
11-FEB-2012 06:53:31 11-FEB-2012 08:32:46 COMPLETED |
| 11. View incremental backups to verify Level 0 vs Level 1 metrics |
SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status
FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd
WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time
AND vrbjd.input_type <> 'ARCHIVELOG'
ORDER BY 2,1; |
| 12. Verify datafile headers are consistent and current |
SELECT file#, status, error, format, recover, checkpoint_time
FROM gv$datafile_header;
FILE# STATUS ERROR FORMAT REC CHECKPOINT_TIME
---------- ------- ------------------ ---------- --- --------------------
1 ONLINE 10 NO 01-JUN-2011 23:02:27
2 ONLINE 10 NO 01-JUN-2011 23:02:27
3 ONLINE 10 NO 01-JUN-2011 23:02:27
4 ONLINE 10 NO 01-JUN-2011 23:02:27
5 ONLINE 10 NO 01-JUN-2011 23:02:27
6 ONLINE 10 NO 01-JUN-2011 23:02:27
7 ONLINE 10 NO 01-JUN-2011 23:02:27
8 ONLINE 10 NO 01-JUN-2011 23:02:27
9 ONLINE 10 NO 01-JUN-2011 23:02:27
10 ONLINE 10 NO 01-JUN-2011 23:02:27
11 ONLINE 10 NO 01-JUN-2011 23:02:27
ALTER SYSTEM CHECKPOINT;
SELECT file#, status, error, format, recover, checkpoint_time
FROM gv$datafile_header;
FILE# STATUS ERROR FORMAT REC CHECKPOINT_TIME
---------- ------- ------------------ ---------- --- --------------------
1 ONLINE 10 NO 02-JUN-2011 17:25:24
2 ONLINE 10 NO 02-JUN-2011 17:25:24
3 ONLINE 10 NO 02-JUN-2011 17:25:24
4 ONLINE 10 NO 02-JUN-2011 17:25:24
5 ONLINE 10 NO 02-JUN-2011 17:25:24
6 ONLINE 10 NO 02-JUN-2011 17:25:24
7 ONLINE 10 NO 02-JUN-2011 17:25:24
8 ONLINE 10 NO 02-JUN-2011 17:25:24
9 ONLINE 10 NO 02-JUN-2011 17:25:24
10 ONLINE 10 NO 02-JUN-2011 17:25:24
11 ONLINE 10 NO 02-JUN-2011 17:25:24 |
| 13. Verify that no one compiled anything in debug mode |
SELECT owner, name, type
FROM dba_plsql_object_settings
WHERE plsql_debug='TRUE'
ORDER BY 1,3,2; |
| 14. Look at the audit trail |
I have been doing this for a very long time and only one thing amazes me more than DBAs that don't
turn on auditing on their databases ... that is those that have turned on auditing and have never, not once, actually reviewed the audit
trail to see if there are issues.
No day should pass without reviewing AUD$ and FGA_LOG$ for issues. |
| 15. Look for invalid date constraints |
@?/rdbms/admin/utlconst.sql |
| 16. Look for dependency timestamp errors |
@?/rdbms/admin/utldtchk.sql |
| 17. Look for memory leaks and related issues |
col osuser format a15
col pid format 9999
col program format a20
col sid format 99999
col spid format a6
col username format a12
SELECT p.spid,p.pid,s.sid,s.serial#,s.status,p.pga_alloc_mem,p.PGA_USED_MEM,s.username,s.osuser,s.program
FROM v$process p,v$session s
WHERE s.paddr ( + ) = p.addr
AND p.background IS NULL -- remove if need to monitor background processes
ORDER BY p.pga_alloc_mem DESC;
SPID PID SID SERIAL# STATUS PGA_ALLOC_MEM PGA_USED_MEM USERNAME OSUSER PROGRAM
---- --- --- ------- ------ ------------- ------------- -------- ------------ -------
8480 29 134 7 ACTIVE 3,837,058,284 1,915,366,348 SYSTEM ORACLE.EXE
(CJQ0)
8108 44 113 1 ACTIVE 381,410,540 27,305,246 SYSTEM
ORACLE.EXE (AS03)
8716 25 140 10 ACTIVE 59,497,708 54,748,654 PUBLIC NT
AUTHORITY ORACLE.EXE\SYSTEM
7692 38 127 127 ACTIVE 49,470,700 1,198,750 SYSTEM
ORACLE.EXE (MS00)
8316 41 119 1 ACTIVE 47,308,012 21,730,766 SYSTEM
ORACLE.EXE (AS00)
6676 37 125 35 ACTIVE 40,737,356 1,719,020 SYSTEM
ORACLE.EXE (LSP0)
7856 46 109 1 ACTIVE 35,511,532 28,834,942 SYSTEM
ORACLE.EXE (AS05)
6020 43 115 1 ACTIVE 34,659,564 27,762,294 SYSTEM
ORACLE.EXE (AS02)
7888 45 112 1 ACTIVE 33,479,916 28,959,710 SYSTEM
ORACLE.EXE (AS04)
7204 42 117 1 ACTIVE 32,300,268 27,629,622 SYSTEM
ORACLE.EXE (AS01)
7232 14 157 1 ACTIVE 19,962,444 14,842,372 SYSTEM
ORACLE.EXE (MMON)
8300 21 151 1 ACTIVE 12,377,324 4,945,804 SYSTEM
ORACLE.EXE (ARC2)
6572 18 154 5 ACTIVE 12,377,324 4,912,644 SYSTEM
ORACLE.EXE (ARC0)
896 22 150 1 ACTIVE 10,149,100 4,920,036 SYSTEM
ORACLE.EXE (LNS1)
8712 10 161 1 ACTIVE 10,018,028 4,964,308 SYSTEM
ORACLE.EXE (LGWR)
7592 20 152 2 ACTIVE 10,018,028 4,954,988 SYSTEM
ORACLE.EXE (ARC1)
8828 39 123 1 ACTIVE 6,872,300 5,263,606 SYSTEM
ORACLE.EXE (MS01) |
| |
| What should I know about this system? |
| 1. Determine the actual version of Oracle |
conn / as sysdba
SELECT * FROM v$version;
Also run the demos under DBMS_UTILITY.DB_VERSION and DBMS_UTILITY.PORT_STRING |
| 2. Determine what components are installed |
conn / as sysdba
col comp_name format a40
SELECT comp_name, version, status
FROM dba_registry; |
| 3. Determine if there invalid objects in the DB |
conn / as sysdba
SELECT owner, edition_name, object_type, COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID'
GROUP BY owner, edition_name, object_type; |
| 4. Is the instance part of a RAC cluster |
Run the demo code under DBMS_UTILITY.ACTIVE_INSTANCES
(or)
SELECT *
FROM v$active_instances; |
| 5. Is there Data Guard replication running? |
conn / as sysdba
SELECT protection_mode, protection_level, remote_archive, database_role, dataguard_broker,
guard_status
FROM v$database; |
| 6. Is the database in ARCHIVE LOG mode? |
conn / as sysdba
SELECT log_mode FROM v$database;
(or)
SQL> archive log list; |
| 7. Are FLASHBACK LOGS being collected? |
conn / as sysdba
SELECT flashback_on FROM v$database; |
| 8. Is there supplemental logging in place? |
conn / as sysdba
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui,
supplemental_log_data_fk, supplemental_log_data_all
FROM v$database; |
| 9. Where are the control files? |
conn / as sysdba
SELECT name FROM v$controlfile; |
10. Where are the redo log files? Are there at least two members in each group?
Thank you Nikunj Gupta for the recommended changes to this and other items. |
conn / as sysdba
col member format a45
SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS
FROM gv$log l, gv$logfile lf
WHERE l.group# = lf.group#
AND l.inst_id = lf.inst_id
ORDER BY 1,3; |
| 11. What are the initialization parameters? |
conn / as sysdba
CREATE PFILE='/home/oracle/initparams.txt' FROM memory; |
12. Capture database information
Thanks to Nikunj Gupta for the correction |
conn / as sysdba
col platform_name format a30
SELECT dbid, name, open_mode, database_role, platform_name
FROM v$database; |
| 13. Capture instance information |
conn / as sysdba
SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state
FROM gv$instance; |
| 14. Are default passwords in use? |
conn / as sysdba
SELECT d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
AND account_status = 'OPEN'
ORDER BY 2,1;
If default passwords are in use either lock the accounts or change the passwords:
ALTER USER <user_name> ACCOUNT LOCK;
ALTER USER <user_name> IDENTTIFIED BY <new_password>;
|
| 15. Is BLOCK CHANGE TRACKING enabled? |
conn / as sysdba
col filename format a60
SELECT filename, status, bytes
FROM v$block_change_tracking; |
| 16. What features are being used? |
Run the demo code under DBMS_FEATURE_USAGE_REPORT |
| 17. What profiles exist and are in use? |
conn / as sysdba
SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';
SELECT profile, resource_name, limit
FROM dba_profiles
ORDER BY 1,2;
SELECT username, profile
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY 1; |
| 18. Are case sensitive passwords enabled? |
SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%sensitive%'); |
| 19. Is Advanced Queuing in use? |
SELECT owner, queue_table, type
FROM dba_queue_tables; |
| 20. Are Streams, CDC or other capture and apply processes in use? |
SELECT capture_name, queue_name, status
FROM dba_capture;
SELECT apply_name, queue_name, status
FROM dba_apply; |
| 21. Are event triggers in use? |
SELECT a.obj#, a.sys_evts, b.name
FROM sys.trigger$ a, sys.obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject IN (0, 88); |
| 22. Is FORCE LOGGING enabled? |
SELECT force_logging FROM v$database;
SELECT tablespace_name, force_logging
FROM dba_tablespaces
ORDER BY 2,1; |
| 23. Is ADVANCED REWRITE in use? |
SELECT owner, name FROM dba_rewrite_equivalences; |
| 24. Were system statistics collected? |
SELECT pname, pval1
FROM sys.aux_stats$;
If the query result only shows values for FLAGS, CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED and the later two have the values 10 and 4096 you need to collect system statistics.
Follow the link at the bottom of the page to do so. |
| 25. Examine enabled degree of parallelism |
SELECT degree, COUNT(*)
FROM dba_tables
GROUP BY degree;
SELECT degree, COUNT(*)
FROM dba_indexes
GROUP BY degree; |
| 26. Determine who has access to the SYSTEM and SYSAUX tablespaces. Remove quota and move objects found in violation of Oracle's advice. |
SELECT username, tablespace_name
FROM dba_ts_quotas
WHERE tablespace_name IN ('SYSTEM', 'SYSAUX')
ORDER BY 1;
SELECT DISTINCT owner
FROM dba_segments
WHERE tablespace_name IN ('SYSTEM', 'SYSAUX')
ORDER BY 1;
-- and needless to say set the quota to 0 for any user other than those installed by Oracle
ALTER USER <username> QUOTA 0 ON SYSTEM;
ALTER USER <username> QUOTA 0 ON SYSAUX; |
| 27. Set an appropriate FAST_START_MTTR_TARGET |
sho parameter fast_start_mttr_target
Read the Oracle docs and understand how to set an appropriate value for your system: For example:
-- these must not be set
SQL> show parameter checkpoint
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
---------------------------------- -------- -------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
SQL> show parameter io_target
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
---------------------------------- -------- -------------------------
fast_start_io_target integer 0
-- then set the value
ALTER SYSTEM RESET log_checkpoint_timeout SCOPE=SPFILE;
ALTER SYSTEM SET FAST_START_MTTR_TARGET=15 SCOPE=SPFILE SID='*';
-- and restart the system |
| 28. Look for security compromised by public synonyms |
SELECT DISTINCT table_owner
FROM dba_synonyms
WHERE owner = 'PUBLIC'; |
| 29. Do you have an RDA |
If your answer is "what is an RDA?" you've much to learn. There is a link at page bottom under
related topics. Download the tool from metalink and create an RDA. You should have a current RDA for every Oracle database for which you are
responsible available at all times. |
| 30. Is TCP/IP Stack Optimization in place |
See Linux Oracle Installation and verify for your operating system version. |
| 31. Is auditing enabled? |
SQL> show parameter audit
SELECT MAX(logoff$time)
FROM sys.aud$;
SELECT MAX(timestamp#), MAX(ntimestamp#)
FROM sys.fga_log$;
/* if auditing is not enabled, including auditing of SYS, what are you waiting for?
An engraved invitation with gold leaf from your friends at anonymous? And if they are in use have the tables been moved to their own tablespace or are they still in SYSTEM? */
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY 1; |
|
