Thursday, September 17, 2015

Being a DBA

Being a DBA
Now that I have been a DBA myself and looking for a new opportunitues, I have some thoughts about
what makes a good DBA and a good DBA manager.
This is purely what I think and all suggestions are welcome.
You are accountable for company database, and that includes.
Database Availability
Backup/Recovery
Database Performance
Database Security
Database Maintainability
Database Scalability / Capacity planning.
And you can always add more………………
That said being a manager you have additional responsibilities.
1. Keeping everyone busy, each person in a team should always have all fingers dirty, means atleast 5 things to do at one time.
They can multitask and even do more than 5 easily and learn to priortize their work based on importance of work.
I have seen sometimes there is no work for days and this is really poor management.
2. Always have good communication and tools to support it too.
Like we have Lync in our office and we can chat and call anyone anytime.
3. Use yourself as the minimum acceptable standard for your team.
For example try to hire people better than you.
4. How to keep team current with technology changes.
Encourage the sharing of new knowledge, we can always train each other, send emails for anything new achieved
and taking it forward we can manage and keep an updated wiki page.
5. Always give feedback, everyone loves to get a pat on the back now and then and also taking little feedback yourself will always help.
6. Establish a productivity baseline, so that you will know when you make it better or worse.
At the end of the day Service is the king, keep the system running and available.
Will be updating this as I go along.
Vijay Tarun
Posted by at No comments:

Oracle Trace Utility (10053 Trace and 10046 Trace). 

conn hr/hr

-- Set these initialization parameters for your trace session to guarantee the integrity of the trace file

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set “_rowsource_execution_statistics” = true

-- in order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = SQL_Trace ;

-- also if you are after 10g you can produce session level ASH reports by the help of CLIENT_ID filter
-- call dbms_session.set_identifier('an_identifier') and use this identifier name as a filter

-- start tracing from this session
Alter session set SQL_Trace = true ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

-- also these can be used for tracing
-- execute DBMS_SESSION.SET_SQL_TRACE ({true|false});
-- execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id, serial_id, {true|false});
-- execute DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, '');
-- for more information; http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html

-- Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;

-- stop tracing
Alter session set SQL_Trace = false ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

-- Than go to USER_DUMP_DEST to pick up the trace file. A query that gives your trace file name generated from your session
-- in windows without the tracefile_identifier would be as follows
--
-- select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace_file_is_here
-- from v$process a, v$session b, v$parameter c, v$instance d
-- where a.addr = b.paddr
-- and b.audsid = userenv('sessionid')
-- and c.name = 'user_dump_dest' ;
--
-- Format the trace file with TKPROF and interpret the output.
-- $ tkprof tracefile.trc output.txt [set the options as you like]
-- tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_2220_bind.trc D:\temp\xe_ora_2220_bind.txt explain=hr/hr sys=yes waits=yes sort=prscpu
Posted by at No comments:

To find the TOP PGA USER

REM  Locate the top PGA user

set lines 75
set pages 999
set serveroutput on

spool topuser.out

declare a1 number;
            a2 number;
            a3 varchar2(30);
            a4 varchar2(30);
            a5 number;
            a6 number;
            a7 number;
            a8 number;
            blankline varchar2(70);

cursor code is select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program",
PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');

begin
  blankline:=chr(13);
  open code;
  fetch code into a1, a2, a3, a4, a5, a6, a7, a8;
   
  dbms_output.put_line(blankline);
  dbms_output.put_line('               Top PGA User');
  dbms_output.put_line(blankline);

  dbms_output.put_line('PID:   '||a1||'             '||'SPID:   '||a2);
  dbms_output.put_line('User Info:           '||a3);
  dbms_output.put_line('Program:            '||a4);
  dbms_output.put_line('PGA Used:            '||a5);
  dbms_output.put_line('PGA Allocated:        '||a6);
  dbms_output.put_line('PGA Freeable:             '||a7);
  dbms_output.put_line('Maximum PGA:            '||a8);

end;
/

set lines 132
col value format 999,999,999,999,999

select * from v$pgastat;

spool off

Posted by at No comments:

Helpful daily use commands Part 1.

1. Killing Oracle database user sessions manually.

    Login to the database.

     select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username =    '<your_schema>'

     Run the generated SQL.

     In multi instance RAC - execute it on all instances or use gv$sessions.

2. SQL To find if your database is using pfile for spfile.

    select decode(count(*), 1, 'spfile', 'pfile' ) from v$spparameter where rownum=1 and              isspecified='TRUE' ;

3. SQL to find size of a schema.

    select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments where owner like 'schema_name' group by owner order by owner;

4. SQL to check if supplemental logging is enabled and enable if not.

    SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,    SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUUPLEME SUP SUP
---------------- ----- ------
NO NO NO
SUUPLEME must be YES

To Update:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUUPLEME SUP SUP
---------------- ----- ------
YES NO NO

5. SQL to get the SCN number in database.

  SQL > set num 24
  SQL > select dbms_flashback.get_system_change_number from dual;

    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
             17987351734

6. Sequence details

   select SEQUENCE_OWNER,SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from    dba_sequences where SEQUENCE_OWNER in ('Schema_name');

7.To generate the DDL

 set pagesize 0
set long 90000
set feedback off
set echo off
spool ddl_object.sql
SELECT DBMS_METADATA.GET_DDL('SEQUENCE',a.SEQ_TP_REGION_ID) from user_objects a where a.OBJECT_TYPE ='SEQUENCE' order by a.OBJECT_NAME;
spool off;

exit

8. Find the long running operations (like backup or export).

  select opname, sofar,totalwork from v$session_longops where  sofar/totalwork <> 1 and totalwork<>0;

9. Active database sessions/connections connections in RAC database.

  select inst_id, username, status, service_name, count(*) from gv$session
group by inst_id, username, status, service_name order by inst_id, service_name, status;

select username, status, machine, service_name, count(*)
      from v$session
group by username, status, machine, service_name
order by  service_name, status;

10.  Query for Encryption Wallet open and close.

    select * from gv$encryption_wallet;

    go to specifis instance and then execute
   ALTER SYSTEM SET ENCRYPTION WALLET CLOSE ;
   ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";

Some useful RAC 11G commands are documented below.

 STOP SEQUENCE

START SEQUENCE


OTHER USEFUL COMMANDS
11gR2 Clusterware and Grid Home – What you need to know [ID 1053147.1] [ POST VIEWS : 9871 ]























Posted by at No comments:
Subscribe to: Posts (Atom)