Thursday, September 3, 2009

DBA Activities

DBA BEST PRACTICES

One question that comes up frequently for new DBAs or under trainee DBAs are

1. what is my job?

2. when I take over a new system what should I do first?


This page gives you a slight review of dba best practices, advice on what to do in certain situations. Actions are on the left, commands on the right.

What should I do today?


1. Review the alert log

Check for any errors.

2. Determine if there is block level corruption

conn / as sysdba

SELECT * FROM v$database_block_corruption;

3. Verify the status of last night's backup

conn / as sysdba

SELECT * FROM v$backup_corruption;


4. Look for newly invalidated objects

conn / as sysdba

SELECT owner, object_type, COUNT(*)
FROM dba_objects
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;

What should I do in a new environment/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 are any invalid objects in the DB

conn / as sysdba

SELECT owner, object_type, COUNT(*)
FROM dba_objects
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


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;


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 log files?

conn / as sysdba

SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
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

conn / as sysdba

SELECT dbid, name, open_mode, database_role, platform_name,
FROM v$instance;


12. Capture instance information

conn / as sysdba

SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state
FROM v$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 either lock the accounts for change them:

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 FORCE LOGGING enabled?

SELECT force_logging FROM v$database;

19. Is ADVANCED REWRITE in use?

SELECT owner, name FROM dba_rewrite_equivalences;

1 comment: