A DBA Must Keep Record of following information

During the stress of a recovery situation, it is important that you have all necessary information at your disposal. This is especially true if for some reason you need to contact Oracle Support because you run into a problem that you do not understand. You should have the following documentation about the database & hardware configuration:

You should keep this information both in electronic and hard copy form. For example, if you save this information in a text file on the network or in an email message, then if the entire system goes down, you may not have this data available.

The name of the node that host the database:

SELECT DBID, DATABASE_NAME FROM V$DATABASE;

63198034        SHAAN.WORLD

SELECT * FROM GLOBAL_NAME;

SHAAN.WORLD

The version and Patch Release of the Oracle Database:
9.2.0.7.0
The Version and Patch Release of the Networking Software:
TCP/IP
The database/instance/host information:
SELECT INSTANCE_NAME FROM V$INSTANCE;

SHAAN

SELECT UTL_INADDR.GET_HOST_ADDRESS FROM DUAL;

192.168.13.31

SELECT UTL_INADDR.GET_HOST_NAME FROM DUAL;

DBSERVER14

SELECT LOG_MODE FROM V$DATABASE;

ARCHIVELOG

The database properties and status information:

SELECT DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

LOG_ARCHIVE_DEST_1      VALID            E:oracleArchive

LOG_ARCHIVE_DEST_2      INACTIVE

LOG_ARCHIVE_DEST_3      INACTIVE

LOG_ARCHIVE_DEST_4      INACTIVE

SELECT * FROM DATABASE_PROPERTIES;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DBTIMEZONE +03:00 DB time zone
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET AR8MSWIN1256 Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set
NLS_RDBMS_VERSION 9.2.0.7.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME SHAAN.WORLD Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #

The database file & storage information:

Datafile Storage:

SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES, bytes/(1024*1024*1024) “in GB” FROM DBA_DATA_FILES;

FILE_ID FILE_NAME TABLESPACE_NAME BYTES In GB
1 D:ORACLEORADATASHAANSYSTEM01.DBF SYSTEM 3942645760 3.671875
2 D:ORACLEORADATASHAANUNDOTBS01.DBF UNDOTBS1 7948206080 7.402344
3 D:ORACLEORADATASHAANEXAMPLE01.DBF EXAMPLE 125829120 0.117188
4 D:ORACLEORADATASHAANINDX01.DBF INDX 3145728000 2.929688
5 D:ORACLEORADATASHAANTOOLS01.DBF TOOLS 1267138560 1.180115
6 D:ORACLEORADATASHAANUSERS01.DBF USERS 26214400 0.024414
7 D:ORACLEORADATASHAANSDH_TIMS01.DBF SDH_TIMS_DBF 20971520000 19.53125
8 D:ORACLEORADATASHAANSDH_TIMS02.DBF SDH_TIMS_DBF 20971520000 19.53125
9 G:ORA_DBF_EXTENDEDSDH_HRMS01.DBF SDH_HRMS_DBF 5242880000 4.882813
10 F:ORACLESHAANSDH_EDSS01.DBF SDH_EDSS_DBF 13421772800 12.5
11 G:ORA_DBF_EXTENDEDSDH_SHTR01.DBF SDH_SHTR_DBF 5242880000 4.882813
12 D:ORACLEORADATASHAANSDH_FIN01.DBF SDH_FIN_DBF 12582912000 11.71875
13 F:ORACLESHAANSDH_EDSS02.DBF SDH_EDSS_DBF 2831155200 2.636719
14 D:ORACLEORADATASHAANINDX02.DBF INDX 3145728000 2.929688

Tempfile Storage:

SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES, bytes/(1024*1024*1024) “in GB”  FROM DBA_TEMP_FILES

FILE_ID FILE_NAME TABLESPACE_NAME BYTES In GB
1 D:ORACLEORADATASHAANTEMP01.DBF TEMP 16392388608 15.2666

SELECT * FROM V$LOGFILE;

GROUP# STATUS TYPE MEMBER
1 ONLINE D:ORACLEORADATASHAANREDO01.LOG
2 ONLINE D:ORACLEORADATASHAANREDO02.LOG
3 ONLINE D:ORACLEORADATASHAANREDO03.LOG
1 ONLINE E:ORACLEORADATASHAANREDO01B.LOG
2 ONLINE E:ORACLEORADATASHAANREDO02B.LOG
3 ONLINE D:ORACLEORADATASHAANREDO03B.LOG

SELECT * FROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS STATUS FIRST_CHANGE# FIRST_TIME
1 1 3446 104857600 2 INACTIVE 366576348 15/01/2012 6:30:10 AM
2 1 3448 104857600 2 CURRENT 366718626 16/01/2012 4:48:15 PM
3 1 3447 104857600 2 INACTIVE 366612142 15/01/2012 10:52:42 AM

SELECT * FROM V$CONTROLFILE;

STATUS NAME
D:ORACLEORADATASHAANCONTROL01.CTL
D:ORACLEORADATASHAANCONTROL02.CTL
D:ORACLEORADATASHAANCONTROL03.CTL

Datafile Storage in GB:

SELECT SUM(BYTES)/(1024*1024*1024) “SIZE IN GB” FROM DBA_DATA_FILES;

93.9389038085938 GB

or

select name, bytes from (select name, bytes from v$datafile

union all select name, bytes from v$tempfile

union all select if.member “name”, l.bytes from v$logfile if, v$log l

where if.group#=l.group#) used, (select sum(bytes) as poo from dba_free_space) free;

Freespace in Tablespace

SELECT TABLESPACE_NAME, BYTES, BYTES/(1024*1024*1024) “Free in GB” FROM SM$TS_FREE;

TABLESPACE_NAME BYTES Free in GB
EXAMPLE 120258560 0.111999512
INDX 4424400896 4.120544434
SDH_EDSS_DBF 3769696256 3.510803223
SDH_FIN_DBF 6773932032 6.30871582
SDH_HRMS_DBF 4665769984 4.345336914
SDH_SHTR_DBF 5232590848 4.87322998
SDH_TIMS_DBF 17347117056 16.15576172
SYSTEM 3683647488 3.430664063
TOOLS 1267073024 1.180053711
UNDOTBS1 7910064128 7.366821289
USERS 26148864 0.024353027
Total _Free_Space 55220699136 51.42828369

Tablespace used/free space in MB

select t.tablespace, t.totalspace as ” Totalspace(MB)”, round((t.totalspace-fs.freespace),2) as “Used Space(MB)”, fs.freespace as “Freespace(MB)”, round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as “% Used”, round((fs.freespace/t.totalspace)*100,2) as “% Free” from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

TABLESPACE  Totalspace(MB) Used Space(MB) Freespace(MB) % Used % Free
EXAMPLE 120 5 115 4.17 95.83
INDX 6000 1781 4219 29.68 70.32
SDH_EDSS_DBF 15500 11905 3595 76.81 23.19
SDH_FIN_DBF 12000 5540 6460 46.17 53.83
SDH_HRMS_DBF 5000 550 4450 11 89
SDH_SHTR_DBF 5000 10 4990 0.2 99.8
SDH_TIMS_DBF 40000 23456 16544 58.64 41.36
SYSTEM 3760 247 3513 6.57 93.43
TOOLS 1208 0 1208 0 100
UNDOTBS1 7580 35 7545 0.46 99.54
USERS 25 0 25 0 100
Total_Tablespace 96193 43529 52664 45.25% 54.75%

Apart from database information a DBA also keeping record of hardware & software configuration of server
The make and model of the production machine:
Dell Power EDGE R310 (Dell Power EDGE 1800)
The Version and Patch of the Operating system:
Windows 2003 service pack 2
The Disk Capacity of the Host:
1 TB
The number of Disk and Disk Controllers:
1 Controller 2 Hard Disk.
The Disk type, Capacity & Free space
RAID1
Disk 1: 1TB
Disk2: 1 TB
The Media Management Vendor (if you use a third party Vendor)
NIL
The Type & Number of Media Management Device:
NIL
The Method and Frequency of database Backup:
RMAN full Commulative & Incremental Backup
Import/Export
The Method of Restore & Recovery:
RMAN
Import/Export
The datafiles Mount Point:
1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s