Shrinking Disk Space Online From Oracle.
Scenario
After a large reorganization such as moving the BI cube data and indexes to a new oracle tablespace, it may be advisable to reduce the size and number of database files. Benefits include performance improvements to backups and reducing disk storage requirements.
Reducing Datafiles using SAP brtools (brspace).
The focus of this article is on two options of brspace , dropping a datafile and resizing (shrinking) a datafile :
- Drop a Datafile: brspace -f dfalter -a drop -f filename; drop an empty datafile, if a data exists on a datafile then the command will fail. eg. brspace -c force -f dfalter -a drop -f /oracle/BIQ/sapdata1/sr3old_20/sr3old.data20
- Resize a Datafile: brspace -f dbalter -a resize -f filename -s new_size_in_MB; resize an existing datafile, key is that not only will this command increase the size of a datafile but it also to shrink it provided no data exists on the bit being deleted. eg. brspace -c force -f dfalter -a resize –f /oracle/BIQ/sapdata5/sr3old_23/sr3old.data23 -s 6914
The 2 Questions.
Before execution of these commands we need to identify the following:
1. Can a Datafile be Dropped ?: Whilst any attempt to drop a datafile with existing data will fail, the professional approach is to first identify which datafiles are actually empty before attempting a drop.
2. Can a Datafile be Resized Down?: If so what is the correct Resize Value: How do you know what value to specify for in the above brspace resize command. new_size_in_MB?
Question 1: Dropping a Datafile How to know if the datafile is empty and can be deleted.
- Simply SQL query to oracle views dba_extents and dba_data_files to find which datafiles exist have no data (extents).
sqlplus / as sysdba
select df.file_name, count(de.segment_name)
from dba_data_files df, dba_extents de
where df.file_id = de.file_id(+)
group by df.file_name
having count(de.segment_name) = 0
order by df.file_name
Any file with no segments can be dropped, except for the last datafile of a tablespace (a tablespace needs to have at least one datafile). To delete where appropriate use of brtools menus or direct command line.
Resize: What is the Correct Resize Value?
Oracle particularly with space controlled with locally managed tablespaces, the chunks of data (extents) are dispersed throughout the datafiles, however in principle Oracle tries to fill the datafiles from the bottom up.
Consider after a large mass deletion of tables or indexes, you will your data scattered/peppered throughout the datafile. For example imagine a hypothetical 10MB datafile with 10 1MB blocks, at one stage the datafile is 100% used, ie 10 blocks are occupied by data. Subsequently after a mast deletion of data, only 2 blocks are occupied and 8 blocks are completely empty. The problem is oracle doesn’t automatically relocate the occupied blocks back to the start of the datafile. Oracle just keeps them where they are written, and the gaps are marked into the register of free space. If we are lucky there will be a freespace gap at the tail of the datafile, it is this freespace that we can “cut” off and return to the filesystem. To get back the 8GB of freespace we would have to do a full reorganization of the tablespace, a topic of another blog. The focus of this blog is on removing the unused space at the tail of the datafile.
Thus our focus is to find the end block of the last chunk of data (extent) on each datafile. This sets the value of the brspace resize -s "new_size_in_MB" value the for our brspace. But how to find that "end block"?
The Smart Way: Reuse the Ideas of Smarter People.
Ask anyone who interacts with me for more that 5 minutes, they will tell you I'm lazy; imagine I’m given the choice between working for a ½ a day coming up with some kind of SQL statement to calculate end block of a datafile myself, or simply “reusing” from the work of others, the choice is clear. And whom better to borrow from than the genius(es) behind the scripts of my favourite SAP Oracle note: 1438410. The script in question is titled Space_Datafiles_ExtentsAtEndOfDatafiles.txt
(Tip: If you are responsible for an SAP Oracle database, spend 1 hour downloading that note take a look at the scripts…it will be the best 1 hour you have spent in a long time).
A look at Space_Datafiles_ExtentsAtEndOfDatafiles.txt and you will see it is creates resize datafile scripts with the correct calculation of the end block. ( Note it’s work of near genius, that takes 3 glasses of red to get your head around. )
My approach is to use brtools to make changes as another check and balance to any activity. A quick hack of the query results of Space_Datafiles_ExtentsAtEndOfDatafiles.txt and we can create the correct calls to both drop datafiles without any in-use data and resize the remaining datafiles to the minimum possible size.
cat resize_datafiles.sql
set pages 0
set verify off
set lines 300
set feedback off
column resize_command format a300
accept tablespace_name prompt "Enter the name of the tablespace or % for all : "
spool resize_datafile_command.sh
SELECT
decode(end_block_1,
null, 'brspace -c force -f dfalter -a drop -f ''' || FILE_NAME ||'''' ,
'brspace -c force -f dfalter -a resize -f ''' || FILE_NAME || ''' -s ' ||
ceil((NVL(END_BLOCK_1, 1) * 8192)/1024/1024)
)
RESIZE_COMMAND
FROM
( SELECT
FILE_NAME,
FILE_ID,
FILE_BLOCKS,
SEGMENT_NAME SEGMENT_NAME_1,
START_BLOCK START_BLOCK_1,
END_BLOCK END_BLOCK_1,
LEAD(SEGMENT_NAME, 1) OVER
(PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) SEGMENT_NAME_2,
LEAD(START_BLOCK, 1) OVER
(PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) START_BLOCK_2,
LEAD(END_BLOCK, 1) OVER
(PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) END_BLOCK_2,
LEAD(SEGMENT_NAME, 2) OVER
(PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) SEGMENT_NAME_3,
LEAD(START_BLOCK, 2) OVER
(PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) START_BLOCK_3,
LEAD(END_BLOCK, 2) OVER
(PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) END_BLOCK_3,
POSITION
FROM
( SELECT
FILE_NAME,
FILE_ID,
FILE_BLOCKS,
SEGMENT_NAME,
BLOCK_ID START_BLOCK,
BLOCK_ID + BLOCKS - 1 END_BLOCK,
POSITION
FROM
( SELECT DISTINCT
DDF.FILE_NAME,
DDF.FILE_ID,
DDF.BLOCKS FILE_BLOCKS,
DE.SEGMENT_NAME,
DE.BLOCK_ID,
DE.BLOCKS,
ROW_NUMBER() OVER (PARTITION BY DDF.FILE_NAME ORDER BY DE.BLOCK_ID DESC) POSITION
FROM
DBA_DATA_FILES DDF,
DBA_EXTENTS DE
WHERE
DDF.FILE_ID = DE.FILE_ID (+)
and DDF.TABLESPACE_NAME like '&&tablespace_name'
)
WHERE
POSITION<= 3
)
)
WHERE
POSITION = 1
ORDER BY
FILE_ID
;
spool off
prompt Check and then run command script resize_datafile_command.sh
exit
Just run this script with the command sqlplus / as sysdba @resize_datafile, at the prompt enterthe tablespace name or use the “%” for all.
The will create (if you run unix, adjust the script for windows as a guess simply change the .sh file created to a .bat file ) a script called resize_datafile_command.sh. Example reducing the size of 2 datafiles to approx 17.5GB and dropping another datafile completely.
brspace -c force -f dfalter -a resize -f '/oracle/BIQ/sapdata114/dods_98/dods.data98' -s 17386
brspace -c force -f dfalter -a resize -f '/oracle/BIQ/sapdata115/dods_99/dods.data99' -s 17416
brspace -c force -f dfalter -a drop -f '/oracle/BIQ/sapdata116/dods_100/dods.data100'
Tips
- If the tablespace is completely empty you wont be able to drop the last datafile, in that case consider dropping the tablespace completely.
- Take care not to try to shrink tablespaces that shouldnt be shunk. eg PSAPUNDO, PSAPTEMP, PSAPAUX, SYSTEM etc.
- If you are doing this after a full reorg then there may not be any freespace in the reduced datafiles for growth. In that case for a QA system it may make sense to add 5~10% extra to the size eg rather than -s 17386 maybe round the value up eg. -s 18000
- Naturally this script would come into most use for QA , Dev or proof of concept systems. Take care before performing mindless execution in production.
- Use the latest download of brtools. This is tested on an Oracle 10G 10.0.4.2 system only with the latest brtools 7.20 V7 release. Note you dont need to be running your sap system with kernel 7.20 or 7.10 to use brtools 7.20 it is downwardly compatible (reasonable due diligence as always eg read the note first).
