Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Notify alert_SID.log of Oracle DB to DBA 1

Status
Not open for further replies.

cts123

Technical User
Feb 28, 2007
108
IN
Hi,

I am very new to Oracle DBA activity. We had a issue with Oracle database and was trying to find out the root cause. The following error I found in alert_SID.log, due to this All transaction associated with that Tablespace got hung.

Mon May 21 13:24:21 2007

ORA-1654: unable to extend index SAPR3.M_ZSAR____0 by 7500 in tablespace PSAPPOOLI

Mon May 21 14:35:31 2007

I am using this query to findout the space %free for all Tablespace on daily basis.

select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;

And for PSAPPOOLI, I am getting

Tablespace Used MB Free MB Total MB Pct. Free

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

PSAPPOOLI 11337 89 11426 1

89 MB free it is showing for PSAPPOOLI Tablespace. And the same figure I was getting more than a month. How can I solve this problem? Do I need to change the maxm. extent to higher value? How can I do that? It is production database. So want to confirm this before doing any activity.

And also I want a UNIX script, which will check the error periodically in alert_SID.log and will notify me for such alert.

Any help on this issue will be highly appreciated.

Thanks agian.

Regards,

-Sam

NB: I am using following query to find the free space.

select tablespace_name, sum(bytes)/1024/1024 MB from dba_free_space

where TABLESPACE_NAME='PSAPPOOLI' group by TABLESPACE_NAME;



 

You need to allocate more space to the tablespace data files and/or add a new datafile to the tablespace. [3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Hello,

a few thoughts:

1) Oracle is trying to increase the space for your index SAPR3.M_ZSAR____0 by 7500 blocks. I don't know what the block size in you database is, but let's assume it is 8K, a common size, afaik. So 7500 blocks are around 60 MB. You may think that they should fit into your 89 MB free space. But keep in mind that the 60 MB must be available as contiguous space in tablespace PSAPPOOLI. And it seems they are not available.

2) Because of that kind of problems it is good practice to have a bit more than 1 percent of freespace available. 5-10 percent seem reasonable. A quick solution is to increase your tablespace. You might also consider to reduce the next extent value of your index, to a value less than 7500 blocks.

3)From your schema name SAPR3 and your tablespace name PSAPPOOLI I gather that your Oracle database is used for an SAP R/3 system.
So you might use SAP's methods of database checking, instead of reinventing the wheel.
Of course a DBA should be familiar with dba_free_space and the like, but SAP has a lot of tools for doing checks, e.g. the freespace percentage is availabe in transaction DB02. Or you may run a program every day that will do a lot of predefined checks for you, and produce results in categories Error, Warning, Info.
By the way, are you the new SAP Basis administrator as well?

hope this helps
 
You mean ..I have to increase the size of DBF? If I don't want to add more data file, resize command will work?

like, alter database datafile
'/oracle/PRD/sapdata5/pooli_2/pooli.data2' resize 600M

 
Something like that, but the resize value should be what you already have plus the additional 600Mb.

I want to be good, is that not enough?
 
hoinz,

Thanks a lot on your detail explanation. I am not a SAP Basis guy. Yes you are right. I am basically AIX guy. There is a SAP server which is running on AIX 4.3.3. And this SAP version is pretty old. This error came all of a sudden. And just of the way was checking the error log. Saw that error.

I am not sure about any SAP DB check method. If there is any such thing available it will be great.

At present I did resize option for the time being and it is working. :)

By the way is there any script available which I can use to notify me when suxh error comes in alert_SID.log ?

TIA ..

Regards,

-Sam
 
I got the script while googling.
Thanks a lot.

Regards,

-Sam
 
Glad you got it working.
And thanks for the star. [smile]

Yesterday I had to leave shortly after my first answer, and so I couldn't reply immediately.

regards
 
Sam, be careful when meddling with SAP-tablespaces!
Use the supplied tools (like dbatools or brtools) to modify them. SAP holds an own data dictionary describing it's tablespaces, which is not guaranteed to be up to date if you use the oracle-tools (sqlplus).
Resizing may not cause problems - but adding a datafile will.

Stefan
 
Stefan,

I agree in general and disagree in detail.
I did add datafiles to SAP tablespaces quite a number of times. And I remember having read somewhere that this way is supported by SAP.
Or am I missing something?

However I would never ever dare add a column to an SAP table using sqlplus.

regards
 
Oh,
of course this should have read:
I did add datafiles to SAP tablespaces by sqlplus ...
 
I did too when I started working with SAP. The application works well with those tablespaces, but if you - or in my case a SAP-basis guy who took over, tries to add a datafile using brtools there are problems.
 
Stefan,
Quite interesting.
In my case, I was able to add other datafiles using sapdba or brspace later on.
So maybe this was just a problem with older versions of SAP?

Or maybe there is a trap I avoided on instinct: I used standard SAP naming conventions for datafiles. Not sure what will happen if one don't ....

Ok, I think you are right, we should urgently advice Sam to use standard SAP tools !
[thumbsup2]
 
Thanks,

I was reading all the information which hoinz and stefanhi addressed. Thanks a lot.

So far "resize" worked out for me. I will address this thing certainly to my Oracle DBA team to look into.

Regards,

-Sam
 
@hoinz
interesting indeed. In my case it was r3/4.6c running on aix 4.3 and oracle 8.1.7 - and I used the naming conventions too. One problem was that sapdba tried to add a file with exactly the same name...
 
Stefan,
In my case it was r3/4.7 on Tru64 Unix V5.x and Oracle 9.2.x
[ponder]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top