Tuesday, February 14, 2012

Database administration or DBA daily activities



Make sure the database is available.  Log into each instance and run daily reports or test scripts.  Some sites may wish to automate this.
Optional implementation: use Oracle Enterprise Manager's 'probe' event.

   Look for any new alert log entries

·         Connect to each managed system.
·         Use 'telnet' or comparable program.
·         For each managed instance, go to the background dump destination, usually $ORACLE_BASE/<SID>/bdump. Make sure to look under each managed database's SID.
·         At the prompt, use the Unix ‘tail’ command to see the  alert_<SID>.log, or otherwise examine the most recent entries in the file.
·         If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in <file>.

Monitor the status of DB Listener


To check the DB Listener, give lsnrctl status at the $ prompt
$lsnrctl status.

Check the disk space in both Production and Development box (if necessary remove unwanted trace files).
Login to Unix box and give bdf or df –k this command will give you all the
Details about the disk space availability. Check and remove Unwanted trace files from udump, and cdump.   Check the Table space in all the Instances ( Increase Tablespace if necessary).

column per_avail format 99.99
select free_space. tablespace_name,free_bytes,used_bytes,free_bytes+used_bytes     total_bytes,free_bytes/(free_bytes+used_bytes)*100 per_avail from (select table space,sum(bytes)free_bytes from dba_free_space group by table space_name) free_space, (select table space,sum(bytes)used_bytes from dba_extents group by tablespace_name) used_space. where free_space. tablespace_name (+)=used_space. table space
order by 5

 Select file_name,bytes from dba_data_files
 where tablespace_name=<’TABLESPACE NAME’>;
            
         This select statement will give the complete path of the tablespace where it is 
         Located and also the total bytes. To increase the Table-space give this statement
 Alter database datafile ‘ABOVE PATH’ resize <VALUE YOU WANT TO INCREASE…… INCREASE ONLY        30% OF THE EXISTING SIZE.
   This statement will increase Table space.

Check the Extents in all the three Instances (Increase if necessary).        
Steps to Check the Extents. Login as oracle and
 select rpad(a.tablespace_name,10) ts_name,rpad(a.segment_name,30) object_name, rpad(b.segment_type,5) type,rpad(min_extents,7)min_ext,rpad(max_extents,12)max_ext,max(rpad(b.extent_id+1,4)) Ext from dba_segments a,dba_extents b
where a.tablespace_name = b.tablespace_name and   a.segment_name = b.segment_name and   a.segment_type = b.segment_type and a.segment_type in  ('TABLE','INDEX') group by a.tablespace_name,a.segment_name,b.segment_type,a.min_extents,a.max_extents

   This will give status of all the extents. 
Check for the invalid objects on all the three instances (Any invalid objects
 should be compiled ).
  Steps to Check the Invalid Objects. Login as oracle and
               
      select rpad(object_name,20)obj_name,object_type,status from dba_objects
      where status='INVALID'

      This will give all the invalid objects available
      To compile them do the following changing the object name where required.
    
     Alter <OBJET_TYPE> <OBJ_NAME> compile <DEPENDS ON THE OBJECT  TYPE i.e. BODY FOR            PACKAGE BODY / VIEW FOR VIEW AND SO ON>.
   
The above statement will compile the invalid object.

Reactions:

0 comments:

Post a Comment