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;