topic Re: How can we release the unused allocated blocks to the openvms file ? in Operating System - OpenVMS

How can we release the unused allocated blocks to the openvms file ?

Chinraj Rajasekaran — Tue, 27 Jun 2006 09:35:36 GMT

Hi,

we are running AlphaServer 4100 5/400 4MB running OpenVMS V7.1-2 clustering 2 alpha nodes with common storage.

Currently we have problems with one of the oracle database rollback segment DBS file allocated with large unused blocks.

***********

TS_ROLLBACK.DBS;1 204800/1024012

*******************

i already tried

*****************************
$ set file/trun TS_ROLLBACK.DBS

%SET-E-READERR, error reading DKC2:[ORACLE.DB_P73LAB]TS_ROLLBACK.DBS;1
-SYSTEM-W-ACCONFLICT, file access conflict
**********************************

we also tried with database complete shutdown but still i get the same error as above.

how can we release the unused allocated blocks?

regards
Raj

Re: How can we release the unused allocated blocks to the openvms file ?

Karl Rohwedder — Tue, 27 Jun 2006 09:40:36 GMT

If the file is not locked by another process the SET FILE/TRUNCATE should do it.
But I do not know why Oracle allocates such an amount of blocks, may be preformance reasons to avoid costly extension when needing the blocks.

regards Kalle

Re: How can we release the unused allocated blocks to the openvms file ?

Hein van den Heuvel — Tue, 27 Jun 2006 10:18:44 GMT

Hello Chinraj,

Welcome to the HP OpenVMS ITRC forum

I would STRONGLY recommend against manipulating Oracle controlled files outside Oracle.

The EOF marker is a STRICTILY OPTIONAL piece of information. It may or might not be honored by Oracle.

Someone told Oracle it could use those blocks, so don't go taking them away behind its back. Your first task is to talk to the DBA to find out how much space it believed to be needed.

Now if it is indeed overallocated, then there is hope. Since this appears to be a ROLLBACK segment (Or a very silly/funny DBA), you can just tell Oracle to
- create a new rollback tablespace
- disable the rollback segments in the current tablespace
- DROP the whole oversized tablespace and delete the files.
(or change the INIT.ORA to select different automatic undo segments).

Met vriendelijk groetjes,
Hein.

Re: How can we release the unused allocated blocks to the openvms file ?

Ian Miller. — Tue, 27 Jun 2006 10:19:01 GMT

SET FILE/TRUNC should work but appears something was accessing the file.

SHOW DEV/FILES DKC2 will tell you which process has the file open (will have to do this on each node in the cluster).

Re: How can we release the unused allocated blocks to the openvms file ?

Robert Gezelter — Tue, 27 Jun 2006 10:28:12 GMT

Raj,

So long as the file is being accessed for write/append in some way, the EOF block is also not reliable information.

- Bob Gezelter, http://www.rlgs.com

Re: How can we release the unused allocated blocks to the openvms file ?

Peter Zeiszler — Tue, 27 Jun 2006 10:42:13 GMT

On both systems run:
show dev/files DKC2:

This will show you the processes that have the file locked so you can not access it.
Rarely will you see someone install this in memory and then the PID will be 000000.

Since this looks like an Oracle database file I would suggest NOT making that change. The oracle table is normally preallocated to a specific size. Talk to your Orace Database Admin to get details on table size and requirements.

Re: How can we release the unused allocated blocks to the openvms file ?

Chinraj Rajasekaran — Tue, 27 Jun 2006 15:33:24 GMT

Hi,

Thanks all of you for your replies.

today we had some problems with our oracle database and we were getting "ORA-1562 Unable to extend roll back segments Error"

So DBA tried to increase the size roll back segments size. But it dont help. finally we had shutdown and restart the database.

Then DBA reverted back the changes - set the old file size for roll back segments.

So now DBA can see the size is less now.

But the free space is not released yet.

I already tried after we shutdown the database one more time and i checked for

show dev dkc2 /file

but no process was using the file.

SO i think the only option left out is as HEIN said, we have to try to create new roll back segment file and delete the existing one.

regards
Raj

Re: How can we release the unused allocated blocks to the openvms file ?

Chinraj Rajasekaran — Thu, 27 Jul 2006 04:36:58 GMT

set file /trun worked just after reboot before database was started. Thanks all of your for your help.