| 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 |
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; |
| 7. Backup Control File to Trace |
ALTER DATABASE BACKUP CONTROLFILE
TO TRACE AS '/home/oracle/control_file.bkp'; |
| |
| 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
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 gv$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 log files?
Thank you Nikunj Gupta for the recommended changes to this and other items. |
conn / as sysdba
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 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; |
| 20. 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. |
