Tuning Sort Area Size

We can check the amount of sorting in memory and on disk by V$SYSSTAT view:

Select name, value from v$sysstat where name in ('sorts (memory)', 'sorts (disk)');

And if a we find that there is a large number of sorts require I/O to disk, increase the initialisation parameter SORT_AREA_SIZE.

Happy Diwali

Hi Friends,

Wishes You and Your Family a Very Happy Diwali.....

Thanks,

ingress

Happy Diwali

Hi Friends,

Wishes You and Your Family a Very Happy Diwali.....

Thanks,

Bhavik

Index Monitoring, Status and Usage (Related to 10g)

SQL engine retain all indexes clear beside a table in spite of their usage. Normally Index maintenance can cause major amounts of CPU and disk I/O usage, which can be harmful to the performance.

Keeping this thing in mind it makes sense to identify and remove any indexes that are not in use. Index monitoring allows unused indexes to be recognized accurately, removing the risks allied with dropping useful indexes.

It is always important to make sure that index monitoring is performed over a period of time. If you only monitor indexes during specific time frames you may incorrectly show up indexes as being unused.

INDEX Monitoring

ALTER INDEX INDEX_IMERGE MONITORING USAGE;

ALTER INDEX INDEX_IMERGE NOMONITORING USAGE;

INDEX Status

SELECT TABLE_NAME, INDEX_NAME, MONITORING FROM V$OBJECT_USAGE WHERE TABLE_NAME = UPPER('&1') AND INDEX_NAME = DECODE(UPPER('&2'), 'ALL', INDEX_NAME, UPPER('&2'));

INDEX Usage

SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORIN FROM V$OBJECT_USAGE WHERE INDEX_NAME =’INDEX_IMERGE’ ORDER BY INDEX_NAME;

 

AWR (Automated Workload Repository) in 10g Rel 2

Let me share some thing with you on AWR

AWR Consist

-   Active session history (ASH)

-   High load SQL statements

-   Time model statistics at the database level as well as at the session level for CPU Usage and wait classification.

-   Object statistics that record usage as well as access counts for segments such as tables, indexes and other database objects.

-   Traditional V$SESSTAT, V$SYSSTAT, V$SYSTEM_EVENT AND V$SESSION_EVENT data.

Where does AWR stay?

-   Oracle kernel allocates a small but distinct portion of the SGA to buffers that are dedicated to holding session history and other AWR related information.

-   This in-memory buffer is updated by MMNL and MMON background processes.

All the AWR Objects stat with one of the following prefixes

-   WRH$ tables stores workload repository history details. Some tables are Range Partitioned by DB_ID & SNAP_ID Column

-   WRI$ tables stores workload repository internal details

-   WRM$ tables stores workload repository metadata

ORA-01103: database name 'PRIMARY' in controlfile is not 'STANDBY'

At the time of Creating Standby Database many DBA's receive ORA-01103 Error. 

There is a possibility that we can receive this error becuse of DB_NAME Parameter. 

When you receive ORA-01103 Error please make sure that DB_NAME PARAMETER is having same value on Primary as well as Standby Database.

Heartbeat failed to connect to standby 'STANDBY'. ORA 01031

Hi Friends,

As I am working on Creating a Standby Database Server with Data Guard. After creating a standby database server with Data Guard I found that my Primary Database alert.log file is writing the below mentioned error.

PING[ARCj]: Heartbeat failed to connect to standby 'STANDBY'. Error is 1031.

Mon Oct 20 20:12:29 2008

Error 1031 received logging on to the standby

Mon Oct 20 20:12:29 2008

Errors in file d:\oracle\product\10.2.0\admin\primary\bdump\primary_arcj_1052.trc:

ORA-01031: insufficient privileges

So I started working on this concern. If any of you are receiving this kind of error here is the possible solution that I got.

1. Removes the parameter db_allowed_logon_version which is replaced by the sqlnet.ora parameter called sqlnet_allowed_logon_version.

2. There is a possiblilty that we give a different passwords on Production and Standby. If so we need to re-create the password file with the same password.

I think this will help all the DBA's a lot.....

Keep Enjoying......

Error LRM-00123 Create SPFILE from PFILE

Hi Friends,

Yesterday night I have a plan to create a Physical Standby Database server with Data Guard feature. But I got hanged with LRM-00123 Error at the time of creating SPFILE from PFILE 

And later on I tired a lot to do googling but there is no proper solution provided by anyone.

SQL> create spfile from pfile='c:\primarypfile.ora';

create spfile from pfile='c:\primarypfile.ora' 

*

ERROR at line 1:

ORA-01078: failure in processing system parameters

LRM-00123: invalid character 146 found in the input file

Here when I have used Notepad or Wordpad Text editor I was not able to find any difference. But later on when I have installed TEXTPAD text editor. I found the difference that I have used "`" instead of using '.

I hope this will help you all in future.

Enjoy!!!!!

How to find a Lock on any Object?

Hi Friends,

Yesterday Arun was asking about how to find a lock on the objects.

When an object is locked by any other user oracle automatically udpates the view V$Locked_Objects in SYS Schema

For example:

1. Open Sql-prompt and connect it with Scott User.

2. Update EMP table.

SQL> update emp set sal=sal+100;

3. Open Sql-prompt and connect it with SYS User.

4. Query the view V$Locked_Objects

SQL> select * from v$locked_object;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME           PROCESS      LOCKED_MODE

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

        10          6        983      30139         10 SCOTT                          IMERGE\ingress              476:5612                   3

5. Here you can see Oracle_Username with its Session ID and other details.

Hope this will help you out. Please give comments.

How to find a Lock on any Object?

Hi Friends,

Yesterday Arun was asking about how to find a lock on the objects.

When an object is locked by any other user oracle automatically udpates the view V$Locked_Objects in SYS Schema

For example:

1. Open Sql-prompt and connect it with Scott User.

2. Update EMP table.

SQL> update emp set sal=sal+100;

3. Open Sql-prompt and connect it with SYS User.

4. Query the view V$Locked_Objects

SQL> select * from v$locked_object;

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME           PROCESS      LOCKED_MODE

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

        10          6        983      30139         10 SCOTT                          IMERGE\Bhavik              476:5612                   3

5. Here you can see Oracle_Username with its Session ID and other details.

Hope this will help you out. Please give comments.

Message :

Hi Friends,

Sorry for been away from the blog for last 2 weeks...

So now I am back and will be working again....

Hope you will enjoy some new stuffs coming to our blog this week.

Thanks for your support and visit.

Thanks,

IngressIT