Skill-guru has added Oracle DBA Interview questions to its repository of tests. This test can also help you in oracle DBA preperation

Skill-guru has added Oracle Pl SQL Interview questions to its repository of tests. The test contains conceptual questions on Pl/SQL.

This post gives an overview and understanding of  AWR and ADDM in Oracle 10g

AWR Architecture

Oracle 10g - awrArchitecture

Read more…

Cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon
Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

Crontab Restrictions:

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab.
The allow/deny files consist of one user name per line.
Options:

-l  List – display the current crontab entries.

-r  Remove the current crontab.

-e  Edit the current crontab using the editor specified by the VISUAL or EDITOR environment variables.
After you exit from the editor, the modified crontab will be installed automatically.

-v  Display the last time you edited your crontab file. (This option is only available on a few systems.)

Crontab syntax

*     *   *   *    *  command to be executed
-     -    -    -    -
|     |     |     |     |
|     |     |     |     +—– day of week (0 – 6) (Sunday=0)
|     |     |     +——- month (1 – 12)
|     |     +——— day of month (1 – 31)
|     +———– hour (0 – 23)
+————- min (0 – 59)

Field  Meaning
======  =========
1   Minute (0-59)
2   Hour (2-24)
3   Day of month (1-31)
4   Month (1-12, Jan, Feb, …)
5   Day of week (0-6) 0=Sunday, 1=Monday … or Sun, Mon, Tue, Wed, Thur, Fri
6   User that the command will run as
7   Command to execute
=> The comma (‘,’) operator specifies a list of values, for example: “1,3,4,7,8″
=> The dash (‘-’) operator specifies a range of values, for example: “1-6″, which is equivalent to “1,2,3,4,5,6″
=> The asterisk (‘*’) operator specifies all possible values for a field. e.g. every hour or every day.

What is the Cause of this error ?

Rollback segments work in a circular fashion to reuse the extents which have been released by committed transactions. Thus in case of long running queries if data is changed while the query is running, rollback segments created for that change are needed to make a read consistent image for the long running query. When these extents are reused while these were still need by the query, this error occurs, because oracle can no longer provide a read consistent image of the data. 

How to fix: 

• Properly sizes the rollback segment which will avoid overwrite rollback information.
• Reduce the number of commits
• Add additional rollback segments
• Run the processing against a range of data rather than the whole table
• Work with UNDO_RETENTION, OPTIMAL Parameter

 Notes: 

The Optimal parameter is used to specify the size to which a rollback segment will shrink after automatically extending itself. If the optimal parameter is not specified when a rollback segment is created, any additional extents dynamically allocated for the rollback segment will remain allocated. By setting the optimal parameter the database administrator can control the size of rollback segments. The optimal parameter is set in a number of bytes, kilobytes, or megabytes and is typically set based on the extent size and minimum number of extents. The minimum size for the optimal parameter is minextents * extent_size (plus some room for overhead information). 

Queries:

 SELECT segment_name,status

FROM dba_rollback_segs; 

SELECT segment_name, optsize “Optimal”

FROM v$rollstat, dba_rollback_segs

WHERE usn = segment_id;

 SET TRANSACTION USE ROLLBACK SEGMENT rb1;