| 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
SQL> col corruption_change# format 99999999999999999999
SQL> SELECT * FROM v$database_block_corruption where rownum = 1;
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
SQL> SELECT inst_id, set_stamp, piece#, file#, block#, blocks, marked_corrupt, corruption_type
2 FROM gv$backup_corruption;
INST_ID SET_STAMP PIECE# FILE# BLOCK# BLOCKS MAR CORRUPTIO
---------- ---------- ---------- ---------- ---------- ---------- --- ---------
6 711749250 1 22 103631 3 NO LOGICAL
SQL> SELECT inst_id, recid, stamp, copy_recid, file#, block#, blocks, marked_corrupt, corruption_type
2 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
|
conn / as sysdba
SELECT owner, object_type, COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID'
GROUP BY owner, object_type; |
| 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 |
SQL> col host_name format a15
SQL> select instance_name, host_name, startup_time, status, logins
2 from gv$instance
3 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 |
| |
| 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, object_type, COUNT(*)
FROM dba_objects_ae
WHERE status = 'INVALID'
GROUP BY owner, 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
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
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 trigger$ a, 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; |
| 22. Is ADVANCED REWRITE in use? |
SELECT owner, name FROM dba_rewrite_equivalences; |
| 23. 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. |
|
