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; |
