Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 1 September 2014

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:\oracle\Archive
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:\ORACLE\ORADATA\SHAAN\SYSTEM01.DBF
SYSTEM
3942645760
3.671875
2
D:\ORACLE\ORADATA\SHAAN\UNDOTBS01.DBF
UNDOTBS1
7948206080
7.402344
3
D:\ORACLE\ORADATA\SHAAN\EXAMPLE01.DBF
EXAMPLE
125829120
0.117188
4
D:\ORACLE\ORADATA\SHAAN\INDX01.DBF
INDX
3145728000
2.929688
5
D:\ORACLE\ORADATA\SHAAN\TOOLS01.DBF
TOOLS
1267138560
1.180115
6
D:\ORACLE\ORADATA\SHAAN\USERS01.DBF
USERS
26214400
0.024414
7
D:\ORACLE\ORADATA\SHAAN\SDH_TIMS01.DBF
SDH_TIMS_DBF
20971520000
19.53125
8
D:\ORACLE\ORADATA\SHAAN\SDH_TIMS02.DBF
SDH_TIMS_DBF
20971520000
19.53125
9
G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
SDH_HRMS_DBF
5242880000
4.882813
10
F:\ORACLE\SHAAN\SDH_EDSS01.DBF
SDH_EDSS_DBF
13421772800
12.5
11
G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
SDH_SHTR_DBF
5242880000
4.882813
12
D:\ORACLE\ORADATA\SHAAN\SDH_FIN01.DBF
SDH_FIN_DBF
12582912000
11.71875
13
F:\ORACLE\SHAAN\SDH_EDSS02.DBF
SDH_EDSS_DBF
2831155200
2.636719
14
D:\ORACLE\ORADATA\SHAAN\INDX02.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:\ORACLE\ORADATA\SHAAN\TEMP01.DBF
TEMP
16392388608
15.2666

SELECT * FROM V$LOGFILE;

GROUP#
STATUS
TYPE
MEMBER
1

ONLINE
D:\ORACLE\ORADATA\SHAAN\REDO01.LOG
2

ONLINE
D:\ORACLE\ORADATA\SHAAN\REDO02.LOG
3

ONLINE
D:\ORACLE\ORADATA\SHAAN\REDO03.LOG
1

ONLINE
E:\ORACLE\ORADATA\SHAAN\REDO01B.LOG
2

ONLINE
E:\ORACLE\ORADATA\SHAAN\REDO02B.LOG
3

ONLINE
D:\ORACLE\ORADATA\SHAAN\REDO03B.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:\ORACLE\ORADATA\SHAAN\CONTROL01.CTL

D:\ORACLE\ORADATA\SHAAN\CONTROL02.CTL

D:\ORACLE\ORADATA\SHAAN\CONTROL03.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

2 comments:

  1. eToro is the #1 forex trading platform for novice and pro traders.

    ReplyDelete