| 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 |
| 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. |
SELECT start_time, end_time, input_type, input_type, status
FROM v$rman_backup_job_details
ORDER BY 1;
START_TIME END_TIME STATUS
-------------------- -------------------- -----------------------
24-JAN-2011 17:34:47 24-JAN-2011 18:59:18 COMPLETED
24-JAN-2011 23:40:21 25-JAN-2011 01:06:55 COMPLETED
25-JAN-2011 05:40:21 25-JAN-2011 06:46:35 COMPLETED
25-JAN-2011 08:39:03 25-JAN-2011 08:40:24 COMPLETED
25-JAN-2011 11:47:12 25-JAN-2011 13:17:40 COMPLETED
25-JAN-2011 17:43:16 25-JAN-2011 19:14:50 COMPLETED
25-JAN-2011 23:46:11 26-JAN-2011 01:32:38 COMPLETED
26-JAN-2011 05:47:34 26-JAN-2011 07:36:51 COMPLETED
26-JAN-2011 11:47:27 26-JAN-2011 13:18:06 COMPLETED
26-JAN-2011 17:48:53 26-JAN-2011 18:52:53 COMPLETED
26-JAN-2011 23:48:43 27-JAN-2011 01:05:57 COMPLETED
27-JAN-2011 05:48:08 27-JAN-2011 07:05:31 COMPLETED
27-JAN-2011 08:38:42 27-JAN-2011 08:40:11 COMPLETED
27-JAN-2011 11:54:09 27-JAN-2011 13:22:56 COMPLETED
27-JAN-2011 17:50:09 27-JAN-2011 19:50:18 COMPLETED
27-JAN-2011 23:50:20 28-JAN-2011 01:14:51 COMPLETED
28-JAN-2011 05:50:20 28-JAN-2011 07:15:22 COMPLETED
28-JAN-2011 11:50:59 28-JAN-2011 13:11:05 COMPLETED
28-JAN-2011 17:51:01 28-JAN-2011 19:11:44 COMPLETED
28-JAN-2011 23:54:16 29-JAN-2011 01:22:42 COMPLETED
29-JAN-2011 05:56:23 29-JAN-2011 07:12:03 COMPLETED
29-JAN-2011 08:35:47 29-JAN-2011 08:37:06 COMPLETED
29-JAN-2011 12:00:03 29-JAN-2011 13:41:28 COMPLETED
29-JAN-2011 17:55:18 29-JAN-2011 19:30:54 COMPLETED
29-JAN-2011 23:55:47 30-JAN-2011 01:25:14 COMPLETED
30-JAN-2011 05:56:34 30-JAN-2011 07:45:18 COMPLETED
30-JAN-2011 11:58:41 30-JAN-2011 13:27:15 COMPLETED
30-JAN-2011 18:02:45 30-JAN-2011 19:02:32 COMPLETED
30-JAN-2011 23:56:36 31-JAN-2011 01:04:36 COMPLETED
31-JAN-2011 06:02:03 31-JAN-2011 07:18:37 COMPLETED
31-JAN-2011 11:59:26 31-JAN-2011 13:18:13 COMPLETED
31-JAN-2011 17:58:57 31-JAN-2011 19:05:34 COMPLETED
31-JAN-2011 23:59:34 01-FEB-2011 01:09:43 COMPLETED
01-FEB-2011 05:59:51 01-FEB-2011 07:02:11 COMPLETED
01-FEB-2011 08:39:17 01-FEB-2011 08:40:36 COMPLETED
01-FEB-2011 12:06:08 01-FEB-2011 13:50:32 COMPLETED
01-FEB-2011 17:59:48 01-FEB-2011 19:44:28 COMPLETED
02-FEB-2011 00:00:41 02-FEB-2011 01:30:53 COMPLETED
02-FEB-2011 06:02:12 02-FEB-2011 07:34:12 COMPLETED
02-FEB-2011 12:02:02 02-FEB-2011 13:36:45 COMPLETED
02-FEB-2011 18:02:13 02-FEB-2011 19:15:06 COMPLETED
03-FEB-2011 00:04:59 03-FEB-2011 01:31:11 COMPLETED
03-FEB-2011 06:04:10 03-FEB-2011 07:13:35 COMPLETED
03-FEB-2011 08:37:19 03-FEB-2011 08:38:52 COMPLETED
03-FEB-2011 12:15:05 03-FEB-2011 14:05:17 COMPLETED
03-FEB-2011 18:04:46 03-FEB-2011 19:44:57 COMPLETED
04-FEB-2011 00:05:56 FAILED
04-FEB-2011 02:00:54 04-FEB-2011 03:30:21 COMPLETED
04-FEB-2011 08:01:03 04-FEB-2011 09:27:16 COMPLETED
04-FEB-2011 14:09:03 04-FEB-2011 15:09:35 COMPLETED
04-FEB-2011 20:03:17 05-FEB-2011 00:46:34 COMPLETED WITH ERRORS
05-FEB-2011 02:01:54 05-FEB-2011 03:05:00 COMPLETED
05-FEB-2011 08:01:49 05-FEB-2011 09:16:59 COMPLETED
05-FEB-2011 08:36:15 05-FEB-2011 08:37:47 COMPLETED
05-FEB-2011 14:01:35 05-FEB-2011 15:31:33 COMPLETED
05-FEB-2011 20:01:23 05-FEB-2011 22:05:26 COMPLETED WITH ERRORS
06-FEB-2011 02:03:05 06-FEB-2011 03:37:16 COMPLETED
06-FEB-2011 08:02:56 06-FEB-2011 09:24:00 COMPLETED
06-FEB-2011 14:07:53 06-FEB-2011 15:12:10 COMPLETED
06-FEB-2011 20:01:50 06-FEB-2011 21:06:41 FAILED
07-FEB-2011 02:01:42 07-FEB-2011 03:16:17 COMPLETED
07-FEB-2011 08:02:35 07-FEB-2011 09:43:35 COMPLETED
07-FEB-2011 14:03:12 07-FEB-2011 15:14:44 COMPLETED
07-FEB-2011 20:03:56 FAILED
08-FEB-2011 02:04:27 08-FEB-2011 03:24:14 COMPLETED
08-FEB-2011 08:04:43 08-FEB-2011 09:44:53 COMPLETED
08-FEB-2011 08:37:11 08-FEB-2011 08:38:28 COMPLETED
08-FEB-2011 14:05:53 08-FEB-2011 15:52:53 COMPLETED
08-FEB-2011 20:04:20 08-FEB-2011 22:19:13 FAILED
09-FEB-2011 02:05:12 09-FEB-2011 04:15:11 COMPLETED
09-FEB-2011 08:07:40 09-FEB-2011 10:15:32 COMPLETED
09-FEB-2011 14:06:40 09-FEB-2011 15:17:38 COMPLETED
09-FEB-2011 20:05:35 09-FEB-2011 21:47:53 FAILED
10-FEB-2011 02:06:13 10-FEB-2011 03:46:37 COMPLETED
10-FEB-2011 08:06:56 10-FEB-2011 09:44:15 COMPLETED
10-FEB-2011 08:44:16 10-FEB-2011 08:45:33 COMPLETED
10-FEB-2011 14:06:14 10-FEB-2011 15:46:15 COMPLETED
10-FEB-2011 20:07:30 10-FEB-2011 21:54:22 FAILED
11-FEB-2011 00:53:58 11-FEB-2011 03:07:22 COMPLETED
11-FEB-2011 06:53:31 11-FEB-2011 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
2 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; |
| |
| What should I do with 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; |
| 6. Are FLASHBACK LOGS being collected? |
conn / as sysdba
SELECT flashback_on FROM v$database; |
| 7. 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; |
| 8. Where are the control files? |
conn / as sysdba
SELECT name FROM v$controlfile; |
9. 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; |
| 10. What are the initialization parameters? |
conn / as sysdba
CREATE PFILE='/home/oracle/initparams.txt' FROM memory; |
11. 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; |
| 12. Capture instance information |
conn / as sysdba
SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state
FROM gv$instance; |
| 13. 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>;
|
| 14. Is BLOCK CHANGE TRACKING enabled? |
conn / as sysdba
col filename format a60
SELECT filename, status, bytes
FROM v$block_change_tracking; |
| 15. What features are being used? |
Run the demo code under DBMS_FEATURE_USAGE_REPORT |
| 16. 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; |
| 17. Are case sensitive passwords enabled? |
SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%sensitive%'); |
| 18. Is Advanced Queuing in use? |
SELECT owner, queue_table, type
FROM dba_queue_tables; |
| 19. 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; |
| 20. 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); |
| 21. Is FORCE LOGGING enabled? |
SELECT force_logging FROM v$database;
SELECT tablespace_name, force_logging
FROM dba_tablespaces
ORDER BY 2,1; |
| 22. Is ADVANCED REWRITE in use? |
SELECT owner, name FROM dba_rewrite_equivalences; |
| 23. 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. |
| 24. Examine enabled degree of parallelism |
SELECT degree, COUNT(*)
FROM dba_tables
GROUP BY degree;
SELECT degree, COUNT(*)
FROM dba_indexes
GROUP BY degree; |
| 25. 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; |
| 26. 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 |
| 27. Look for security compromised by public synonyms |
SELECT DISTINCT table_owner
FROM dba_synonyms
WHERE owner = 'PUBLIC'; |
| 28. 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. |
|
