Regular Tasks Of a DBA....

1.Regular Monitoring of The free space in Database.

2.Taking logical bakups of important table.

3.Checking the locks on the Database.

4.Checking the long running queries on ur database

5.Analysing the performance of ur Database

6.Investigate Wait Statistics

7.Tablespace Usage

8.Ensure Connectivity to Oracle

9.Lock Contention

10.Extent Failure

11.Alert Logs

12.Redo Logs

13.Check if all the instances are up and running ps -ef | grep ora -

14.check if all the listeners are up and running ps -ef | grep litener

15.check log file for any new errors

16.verify success of backup

17.verify succss of archiving to tape and to standby db

procedure for Calculating Database Growth and scheduling in DBMS JOBS....

1.

Create a Table By the Name db_growth...with following details...

Name Null? Type

----------------------------------------- -------- ----------------------------

DAY DATE

DATABASE_SIZE_MB NUMBER

DAILY_GROWTH_MB NUMBER

2.

create or replace PROCEDURE database_growth

AS

today_size NUMBER;

yesterday_size NUMBER;

growth_size NUMBER;

cnt NUMBER;

BEGIN

SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;

SELECT COUNT(1) INTO cnt FROM db_growth ;

IF cnt > 0

THEN

SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d

ay,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');

ELSE

yesterday_size:=today_size;

END IF;

growth_size := today_size - yesterday_size;

INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);

EXCEPTION

WHEN no_data_found THEN

INSERT INTO db_growth VALUES(sysdate,today_size,0);

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

3.Submit in DBMS_JOBS

variable jobno number;

begin

dbms_job.submit(

:jobno,

'database_growth ;',

trunc(sysdate+1) + 4/24,

'trunc(sysdate+1) + 4/24'

);

commit;

end;

/

print :jobno

10 Scripts Every DBA Should Have

I. Display the Current Archivelog Status :


ARCHIVE LOG LIST;


II. Creating a Control File Trace File


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


III. Tablespace Free Extents and Free Space


column Tablespace_Name format A20
column Pct_Free format 999.99

select

Sql Scripts for knowing the transaction status

select * from v$transaction;

--All tranasactions + sid and username
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;

--All tranasactions + sid and username + first 64 bytes of SQL
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

any-longer-getting-ddl

--1 Get DDL of the object:
declare
clb CLOB; pos INTEGER:=1; amt INTEGER; len INTEGER; txt VARCHAR2(4000);
begin
clb := dbms_metadata.get_ddl ('$OBJ_TYPE','$OBJ_NAME','$OWNER');
len := LENGTH(clb);
LOOP
amt := nvl(INSTR (clb, chr(10), pos),len) - pos;
IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;
pos := pos + amt + 1;
DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));
EXIT WHEN pos>=len;
END LOOP;
end;
/

--2 Get DDL of all $OBJ_TYPE objects in schema:
declare
clb CLOB; pos INTEGER; amt INTEGER; len INTEGER; txt VARCHAR2(4000);
handle NUMBER; transhandle NUMBER; cnt NUMBER;
begin
handle := dbms_metadata.open ('$OBJ_TYPE');
--dbms_metadata.set_filter (handle, 'NAME_EXPR','like ''%''');
dbms_metadata.set_filter (handle, 'SCHEMA', '$OWNER');
dbms_metadata.set_count (handle, 10);
transhandle := dbms_metadata.add_transform (handle, 'DDL');
dbms_metadata.set_transform_param (transhandle, 'SQLTERMINATOR', TRUE);
LOOP
clb := dbms_metadata.fetch_clob (handle);
EXIT WHEN clb is null;
pos := 1;
len := LENGTH(clb);
txt := '';
LOOP
amt := nvl(INSTR (clb, chr(10), pos),len) - pos;
IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;
pos := pos + amt + 1;
DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));
EXIT WHEN pos>=len;
END LOOP;
END LOOP;
end;
/

moving-schema-tablesindexes

-1 Move group of TABLE segments (check for unusable indexes after that)
select 'alter table '||owner||'.'||segment_name||' move '||
decode(segment_type,
'TABLE PARTITION','partition '||partition_name,
'TABLE SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--2 Move group of INDEX segments
select 'alter index '||owner||'.'||segment_name||' rebuild '||
decode(segment_type,
'INDEX PARTITION','partition '||partition_name,
'INDEX SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'INDEX%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--3 List segments that will fail to expand
select /*+ all_rows */ segs.*
from
dba_segments segs,
sys.seg& s,
(select ts#,max(length) m from sys.fet& group by ts#) f
where s.ts#=f.ts# and extsize>m
and segs.header_file=s.file# and segs.header_block=s.block#
;

--4 List of fragmented segments
select segs.*
from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
;

simple-log-management

select * from v&log; --status of logfile groups
select * from v&logfile order by group#; --status of logfiles
select * from v&instance; --status of the archiver
alter system archive log start; --restart the archiver
alter system switch logfile; --switch online log
alter system set log_archive_max_processes=4;

--Add logfile group
alter database add logfile group 4
('&logfilename1',
'&logfilename2') size 64M;

--Drop logfile group and all members in it
alter database drop logfile group &N;

--Add logfile member
alter database add logfile member '&logfilename' reuse to group 4;

--Drop logfile member
alter database drop logfile member '&logfilename';



--Checking archivelog mode
select dbid, name, resetlogs_time, log_mode from v&database;

alter system archive log start; -- restarts the archiver
select * from v&archive_dest; -- archiver destinations

--Altering destination
alter system set log_archive_dest_1='location=&path';
alter system set log_archive_dest_state_1='enable';

--Archived log info from the control file
select * from v&archived_log;

--The sequence# of last backed up log
select thread#, max(sequence#) from v&archived_log
where BACKUP_COUNT>0 group by thread#;

--Redo size (MB) per day, last 30 days
select trunc(first_time) arc_date, sum(blocks * block_size)/1048576 arc_size
from v&archived_log
where first_time >= (trunc(sysdate)-30)
group by trunc(first_time);

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here are ways to get both while you dont have statspack snapshot you can use them as alternate.



QUERY(cusomizable,according to your workload and) and Sample Output from SPOOL

1 SELECT ses.sid
2 , DECODE(ses.action,NULL,'online','batch') "User"
3 , MAX(DECODE(sta.statistic#,9,sta.value,0))
4 /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
5 , MAX(DECODE(sta.statistic#,40,sta.value,0))
6 /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
7 , 60*24*(sysdate-ses.logon_time) "Minutes"
8 FROM V$SESSION ses
9 , V$SESSTAT sta
10 WHERE ses.status = 'ACTIVE'
11 AND sta.sid = ses.sid
12 AND sta.statistic# IN (9,40)
13 GROUP BY ses.sid, ses.action, ses.logon_time
14 ORDER BY
15 SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
16* / greatest(3600*24*(sysdate-ses.logon_time),1) DESC
17
SQL> /

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
9 online 12913.0075 12912.9963 26.7166667
5 online 98.9491051 98.9362416 29.8
14 online 4578.26388 .012476606 26.7166667
19 online 3170.5866 .00270636 24.6333333
20 online 1328.76316 .035087719 1.9
18 online .111731844 .026536313 11.9333333
7 online .749860101 0 29.7833333
21 online .2 0 .5
6 online .016219239 0 29.8
1 online 0 0 29.8166667
2 online 0 0 29.8166667

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
3 online 0 0 29.8166667
4 online 0 0 29.8

13 rows selected.



1 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
2 FROM V$SQLAREA
3 WHERE buffer_gets > 1000
4 OR disk_reads > 100
5* ORDER BY buffer_gets + 100*disk_reads DESC
SQL>
SQL> /

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
---------- ---------- ----------- ---------- -----------
2626326413 1 572 384 1
690085868 1 2575 134 1
2963598673 349 1095 55 6
657604649 1 1172 18 1

USING CRON WITH ORACLE

USING CRON WITH ORACLE

by Raghu Viswanathan
Oracle Support - Unix Group

The objective of this bulletin is to acquaint the reader with the use of cron
and to provide some examples on how it is used with ORACLE. We will use the
Unix att universe for the purpose of discussion. Differences between att and
bsd are documented at the end of this report.

For simplicity, let us assume that the database is owned
by the account 'oracle' and that the demo account is called 'scott'.
Let us also assume that $ORACLE_HOME is located at /usr/oracle and
the SID is S

What is cron?. Cron is a clock daemon utility provided by
unix. This utility, which is located in /etc/cron executes commands
specified in the file /usr/lib/crontab at times specified in crontab
by the user. cron checks this file EVERY MINUTE.

Not all users are allowed to use cron. In order to use it,
your userid must be listed in a file called /usr/lib/cron/cron.allow
which is owned by root. Contact your system administrator to get your
userid in this file. From now on, let us assume that the file
cron.allow only contains userids root and oracle.

Every user listed in cron.allow will have his/her personal
crontab file. Each user can only have one crontab file. This crontab
will list all the commands that the user wants to execute and what
times thereof.

How to set up your own crontab file:
------------------------------------

All crontab file contain lines in the following format:

* * * * *

where the *'s (often called the five time fields) reference digits
that collectively make up the time, and reference the
command to be executed. The five time fields in successive order
are:

Field Range
------------------------

minute 0-59
hour 0-23
day of the month 1-31
month of the year 1-12
day of the week 0-6 (0 is Sunday)

You may directly use * for any one of these values which means that
all possible values are accepted. For example, to execute an echo
command everyday at 7:29 in the evening you can set up the following
line:

29 19 * * * echo its dinner time.

You can enter more requests on consecutive lines. Once
you have entered all these into a file (say, mycron) using your
favorite editor, you can now submit this file as YOUR CRONTAB file
to the system. To do this type:

crontab mycron

You can always add more requests in your file mycron and
then resubmit it using the above command. The system will only retain
your most recent submission.

To view your current submitted file type:

crontab -l

To delete your current submitted file type:

crontab -r


Your Results:
-------------

Now that you have submitted your crontab file, the echo
command will execute everyday at 7:30 in the evening. The results are
sent to you via unix mail.


Uses of Cron:
-------------

As you can see, cron is very useful. System administrators
use cron often to perform administrative chores automatically.
Typically, such activities are performed at night when the system load
is low. From a database standpoint, we can use cron to perform backups
of the database every night or we can run SQL reports that will spool
output files every night to keep track of system usage.

Scenario:
---------

Let us set up a very simple example - let us say that we wish
to know the contents of the emp and dept tables in the scott account
everyday at 11:30 at night and save the results in a file called
scottout. In order to do this, we will need 3 files:

sql script containing the queries - call this scottcron.sql
a shell script to execute scottcron.sql - call this scottshell
a crontab file to run the shell script - call this mycron
(this last file has to be submitted to cron)

Let us also say that these files and the results will be
located in the directory /usr/oracle/personal.

Step 1 - Writing the SQL script:
--------------------------------

spool /usr/oracle/personal/scottout
select * from emp;
select * from dept;
exit;

Now we need to set up a shell script that will enter SQL*Plus
as scott/tiger and execute scottcron.sql. We also need to write the
crontab file. Let us approach this from two angles:

Approach A - Executing as ROOT:
-------------------------------

In this approach we will let the ROOT execute the commands
and save it in /usr/oracle/personal. We can set up the shell file
scottshell as follows:

sqlplus scott/tiger @/usr/oracle/personal/scottcron

and the crontab file as:

30 23 * * * su - oracle -c "/usr/oracle/personal/scottshell" >
"/usr/oracle/personall/scotterror"


Essentially, root is the super user and can thus impersonate oracle
without a password. su oracle will enable root to become oracle.
The -c means 'execute the following command as oracle'. The extra '-'
between su and oracle is crucial - because of this, the script is being
executed as if the user oracle had logged in himself. In other words
all environment variables like $ORACLE_HOME,$ORACLE_SID and $PATH
are set correctly.

This is the approach we use at Oracle Support when maintaining our own online
support system. Our crontab file looks like the following:

<5 time fields> su - oracle -c "shell1"
<5 time fields> su - oracle -c "shell2"
<5 time fields> su - oracle -c "shell3"
<5 time fields> su - oracle -c "shell4"
<5 time fields> su - oracle -c "shell5"


Approach B - Executing as oracle:
---------------------------------

In this approach, oracle will submit the crontab file
instead of root. The shell file scottshell will now look like:

ORACLE_HOME=/usr/oracle; export ORACLE_HOME;
ORACLE_SID=S; export ORACLE_SID;
/usr/oracle/bin/sqlplus scott/tiger @/usr/oracle/personal/scottcron
and the crontab file will look like:

30 23 * * * "/usr/oracle/personal/scottshell" >
"/usr/oracle/personall/scotterror"

A common mistake is to forget to set ORACLE_HOME and
ORACLE_SID - this usually yields to a ORA 2700 error. This is not a
bug - when cron runs the script it uses the environment similar to
the one used by root when it does an 'su' to oracle WITHOUT
executing the login scripts of oracle. Thus the ORACLE variables are
not set. As a matter of fact, you can also set the PATH explicitly to
avoid using full path names.

We can do an export or run a report in the same way.


Differences with BSD Environment:
---------------------------------

In the ATT environment, we could never edit the crontab
file directly - we had to keep resubmitting our personal cronfiles.
In BSD versions, there is only one configuration file called
/usr/lib/crontab. Version 4.3 also has a crontab.local. You may
either use one or the other, not both.

Also, on some systems, the crontab files are set up in a
seven-field format. The first 5 fields are the time fields, the sixth
is the userid and seventh is the command to be executed.

If you are not using cron, be sure to remove your crontab
file - otherwise the system will continually execute cron!

HOW TO RUN A SQL PLUS SCRIPT FROM A CRON JOB

PURPOSE:
========
HOW TO RUN A SQL PLUS SCRIPT FROM A CRON JOB.

SCOPE & APPLICATION:
=====================
This article is intended for an audience having knowledge about unix and sql.

EXPLANATION:
============
Cron is a mechanism for planning and scheduling batch jobs. The daemon "crond" is started automatically on system boot up. It runs one check per minute to see if there are any jobs to execute. The list of jobs to execute is created by the program "crontab".

Entering the command "crontab -l" will display a list of current entries. By default, only entries owned by the logged in user will be displayed.
Existing lists can be removed/deleted with command "crontab -r".

To create a new list, it is recommended to read the entries from a file using the command

"crontab filename".

To run a sql script, create a file (for ex: script1.ksh) containing the sql commands. Now use the following crontab entry.

#The following entry will execute the script 1.ksh every hour.
0 12 * * * $ORACLE_BASE/admin/scripts/snap.ksh

This entry consists of six parameters. The first five parameters define the time schedule, whereas the sixth parameter contains the command for executing the job.
This command consists of:

- the full path and file name of the script - an argument

Parameters defining the time schedule are:

minute(0-59)
hour(0-23)
day of month(1- 31)
month(1-12)
day of week(0-6) 0 = Sun

Hence, the above sample entry: 0 12 * * * can be translated as:

If Minute = 0 and Hour = 12, the script will be executed.

Because the last three scheduling parms are defined by the wildcard character
"*", the job will be executed every day.

REFERENCES:
===========
For further explanations under Unix enter the following commands:

man crontab
man 5 crontab
man cron

Contention on SYS.DUAL table-- - - From Vivek Sharma

Contention on sys.dual

SYS owned dual table was introduced by Oracle long back and since then the Application
Development team across the globe has been using this table for some internal processing.
Some of the common queries on DUAL table used by the application team are :

select sysdate from dual;
select sequence.nextval from dual;
select sys_context('USERENV','SESSION_USER') from dual;

DUAL being a magic table contains only one row and hence returns a single row for a given
sql statement. Any inputs passed to dual table returns the desired output and hence is widely
used by the applications.

Originally, this table was introduced by Oracle for its internal processing, for example, RMAN
uses this table to fetch the current date for a backup. Contention on this table can occur, if
application uses this table frequently and concurrently, with common waits like Buffer Busy Wait,
Latch Free (Cache Buffer Chain / Cache Buffer LRU chain). The behaviour of this table is changed in
Oracle database version 10g and hence, this note is only applicable for database versions 9i and below.


This article focusses on eliminating contention on sys owned dual table and an alternative to this table
without altering the original table. Oracle strongly recommends that sys owned dual table should not be
altered in any way.

In this Article, we will create our own dual table and will force the application to use our table rather
than using sys owned dual table. This will be done without making any change in the application which was a
big challenge when it comes to Customized applications developed by third party.

Any single call to a sys owned dual table does 3-4 logical i/o’s, thus concurrent and multiple calls on this
table will increase these I/O’s thus causing contention.
As an Example :

SQL> column user_info format a30
SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
------------------------------
VIVEK


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now, if I create my own table in my production schema, the same query used above
can be forced to use our table without making change in the code. The process would
be :

SQL> create table my_dual (x number primary key) organization index;

Table created.

SQL> insert into my_dual values (1);

1 row created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'VIVEK',tabname=>'MY_DUAL',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> create view dual as select * from my_dual;

View created.

SQL> set autot on
SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
------------------------------
VIVEK


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_32339' (UNIQUE) (Cost=1
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

1 Logical I/O against 4 Logical I/O’s which is 75% performance improvement. The improvement is
clearly visible when this table is accessed concurrently and will eliminate the contention,
thus improving buffer cache efficiency.

Thus, if statspack shows high logical I/O’s or disk reads on SYS.Dual table, this solution can be
implemented to reduce these I/O’s. Less the number of I/O’s, less is the contention for latches.
Implementing this solution does not alter the original dual table and Oracle internally can use its
own table whenever required while our application (without any modification) uses our own table
efficiently.

As mentioned earlier, the behaviour of dual table is changed in Oracle 10g. Oracle 10g introduces
FAST DUAL concept and does not visits data blocks to get the desired output. Hence, the recommendations
suggested in this note is not required in all versions of 10g databases. For example,


Orcl10g> SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
-------------
VIVEK


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
415 bytes sent via SQL*Net to client

Handy SQL

Top 10 sql commands in the system that are using most Memory



select * from (select sql_text, sharable_mem + persistent_mem from v$sql order by 2 desc) where rownum < 11 order by 2

/

Top 10 sql commands in the system that are using most CPU




select * from (select sql_text, cpu_time/1000000000 cpu_time, elapsed_time/1000000000 elapsed_time from v$sqlarea order by cpu_time desc, disk_reads desc ) where rownum < 11

/

Top 10 sql commands in the system that are using most I/O




select * from (select sql_text, disk_reads, rows_processed from v$sqlarea order by cpu_time desc, disk_reads desc
) where rownum < 11

/


Transaction Pers Second



Transaction in oracle means all DMLs done between a commit/rollback and the next commit/rollback. Oracle generates a new SCN at commit time. So, I would would calculate number of SCNs generated per second and that will roughly give me transactions per second. v$log_history can be used to calculate number of SCNs generated.

col FIRST_CHANGE# for 999,999,999,999
col NEXT_CHANGE# for 999,999,999,999
alter session set nls_date_format='mm/dd/yy hh24:mi:ss';
select * from (
select FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# , FIRST_TIME
from v$log_history order by 4 desc)
where rownum < 5
order by 4;

FIRST_CHANGE# NEXT_CHANGE# SEQUENCE# FIRST_TIME
---------------- ---------------- ---------- -----------------
18,501,481,600 18,501,516,335 13815 09/12/07 12:01:31
18,501,516,335 18,501,544,326 13816 09/12/07 12:21:32
18,501,544,326 18,501,572,873 13817 09/12/07 12:41:30
18,501,572,873 18,501,604,213 13818 09/12/07 13:01:31

You can see that between 12:01 and 12:21 number of SCNs generated = 18,501,516,335 - 18,501,481,600 = 34,935

34,735 transactions in 20 minutes = 28.94 transactions per second.

Note that this is an approximation because all SCNs are not generated due to commit, but most of the SCNs are generated at commit time.