Daily Health Checks of DBA

  Daily Health Checks of DBA:

1. Monitoring Alert log

Not applicable/cannot be done on SQE as we do not have access to server.

2. Tablespace Management

Sql> SELECT a.tablespace_name, ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) percent_used,(100 - ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2)) percent_free FROM (SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_data_files GROUP BY tablespace_name) a,(SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

3. Checking for Locks

Sql> select s.sid, s.serial#,s.machine,p.pid, p.spid vprocess_spid,to_char(logon_time,'MM/DD/YY HH24:MI:SS') time,s.status sstatus, s.username susername,d.object_name from   dba_objects d, v$locked_object l, v$session s,v$process p where  (l.session_id=s.sid) and (p.addr = s.paddr) and (d.object_id = l.object_id) and s.sid in (select sid from v$lock) order by s.username;

4. Checking for blocking sessions

Sql> select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

5. No. of Active and Inactive Sessions

Sql> select status,count(*) from v$session where username is not null group by status order by status;

6. No. of Sessions idle for more than 60min’s.

Sql> select sid,serial#,username,osuser,machine,status,program,module,event,action,round (seconds_in_wait/60,2) as idle_mins,wait_class,last_call_et from v$session where seconds_in_wait>3600 and username is not null and status='INACTIVE';

Sql>Set heading off

Sql> Set feedback off

Sql> Set lines 120

Sql> Set pages 100

Sql>spool sess_kill.sql

Sql> select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username is not null and status='INACTIVE' and seconds_in_wait>3600;

Sql>spool off

Sql>set heading on

Sql> Set feedback on

Sql>@sess_kill.sql

7. Check for status of objects (Valid/Invalid).

Sql> select status,count(*) from dba_objects where owner=‘SQE_RE_ENG’ group by status order by status;

·         In the case of finding invalid objects, fire below query to check the objects which are invalid.

Sql> select object_name,object_type,status from dba_objects where owner=‘SQE_RE_ENG’ and status='INVALID' order by object_type,object_name;

è  Needs to compile them to make VALID.

8. Refreshing MV’s if they are not getting refreshed automatically.

 Cannot be done on SQE as we do not have MV’s

9. Generating AWR, ASH, ADDM reports.
   Sql>  Need to run respective scripts to generate the above reports
10. Analyzing the queries which are taking more time by using Explain Plan.
    Sql> Need to run script from rdbms/admin/.
11.  Monitoring Cluster Ready Services.
    It cannot be done/not applicable for SQE as we do not have access to server.
12. Monitoring Listener logs.

Not applicable/cannot be done on SQE as we do not have access to server.

Weekly/Monthly Activities:

1. Collecting  Statistics

Sql> select table_name,last_analyzed,num_rows from dba_tables where owner=‘SQE_RE_ENG’ order by last_analyzed,table_name;

2. Defragmentation of tables

Sql> select table_name,blocks C_size,trunc(num_rows*avg_row_len/8192) A_Size,trunc((((blocks)-trunc(num_rows*avg_row_len/8192))*100/blocks),2) "%freed"from dba_tables where owner=‘SQE_RE_ENG’ and blocks>=1 order by "%freed" desc,table_name;

3. Re-Organization of Tablespaces.

Step1:

Sql> select file_id,maxbytes,maxblocks from dba_data_files where tablespace_name='DATA' order by file_id;

Sql> select owner,segment_name,segment_type from dba_segments where tablespace_name='DATA' order by segment_type;

                                                                                or

Sql> select owner,segment_name,segment_type from dba_segments where tablespace_name='DATA' order by owner;

Sql> select count(segment_type),segment_type from dba_segments where tablespace_name='DATA' group by segment_type order by segment_type;

Step2:

Sql>create tablespace data_test;

Sql> Set heading off

Sql> Set feedback off

Sql> Set lines 120

Sql> Set pages 100

Sql>spool reorg_ts.sql

Sql> select 'alter table '||owner||'.'||segment_name||' move tablespace data_test;' from dba_segments where tablespace_name='DATA' and segment_type='TABLE' order by owner;

Sql> select 'alter index '||owner||'.'||segment_name||' rebuild tablespace index1;' from dba_segments where tablespace_name='DATA' and segment_type='INDEX' order by owner;

Sql>spool off

Sql>set heading on

Sql> Set feedback on

Sql>@reorg_ts.sql

Sql> select file_id,maxbytes,maxblocks from dba_data_files where tablespace_name='DATA' order by file_id;

Sql> select owner,segment_name,segment_type from dba_segments where tablespace_name='DATA' order by segment_type;

                                                                                or

Sql> select owner,segment_name,segment_type from dba_segments where tablespace_name='DATA' order by owner;

Sql> select count(segment_type),segment_type from dba_segments where tablespace_name='DATA' group by segment_type order by segment_type;

Step3:

Sql> drop tablespace <tablespace name (DATA)>;

Sql> alter tablespace DATA_TEST rename to DATA;

Sql> select file_id,maxbytes,maxblocks from dba_data_files where tablespace_name='DATA' order by file_id;

Sql> select owner,segment_name,segment_type from dba_segments where tablespace_name='DATA' order by segment_type;

                                                                                or

Sql> select owner,segment_name,segment_type from dba_segments where tablespace_name='DATA' order by owner;

Sql> select count(segment_type),segment_type from dba_segments where tablespace_name='DATA' group by segment_type order by segment_type;

4. Rebuilding Indexes.

Sql>Set heading off

Sql> Set feedback off

Sql> Set lines 120

Sql> Set pages 100

Sql>spool index_rebuild.sql

Sql> select 'alter index '||OWNER||'.'||index_name||' rebuild tablespace index1;' from dba_indexes where owner in (SQE_RE_ENG','SQE_USER','SQE_BULK','SQE_JMS','SQE_LITMUS_JMS','EXP_DBLINK_ USER');

Sql>spool off

Sql>set heading on

Sql> Set feedback on

Sql>@index_rebuild.sql

5. Backing up of OCR and Voting Disk files

Maintained by GS ENV/BT DBA’s.

6. Performing Backups depends on the Backup Strategies

Performed by GS ENV/BT DBA’s.

Activities Performed for Every Quarter:

1. Applying CPU, PSU patches for Security purposes.

Will be done by BT DBA’s as per the Database maintenance rules

Activities performed on Client/End Users/Prj Requirements:

1. Creating Schema’s.

Will be done by SQE DBA’s on the request of Dev/Application Teams

2. Replication of Schema’s, Tables.

Will be done by SQE DBA’s on the request of Dev/Application Teams

3. Granting Roles and Privileges to Objects, Users.

Will be done by SQE DBA’s on the request of Dev/Application Teams

4. Creating Database Links Materialized Views.

Will be done by SQE DBA’s to reduce the load on servers based on load

5. Creation of Tablespaces and adding datafiles.

Will be done by SQE DBA’s when tablespace usage reaches threshold value.

6. Applying Interim Patches to fix the bugs.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

7. Installation of Oracle, Database.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

8. Upgrading Cluster, Oracle Home, Database.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

9. Applying Patches to the Oracle Home, Cluster Home.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

10. Cloning of production Database for Test purposes.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

11. Migration of Databases to New environments.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

12. Creation of ASM instances, Disk groups to manage the Data.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

13. Installing Recovery Catalog to manage the Backups.

Performed by GS ENV/BT DBA’s as per the backup strategies.

14. Configuration of OEM, Grid agents and Registering Servers, Databases.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

15. Monitoring Databases using Centralized controls (OEM, RMAN-Catalog and Grid Agent).

Monitoring is done by SQE DBA’s to maintain/manage databases efficiently

16. Configuration of Cluster, RAC databases.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

17. Configuration of Data Guard.

Done by GS ENV/BT DBA’s based on the request raised by SQE DBA’s.

18.