Temp file movement in Oracle database

ZB1 System was frequently facing ORA-01114

ORA-01114:
IO error writing block to file string (block # string)

Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.

and ORA-12805: parallel query server died unexpectedly, errors frequently. So it was decided to move temp files to separate mount point. It included moving temp files from different locations to separate mount point only dedicated for temp files:

Please find the steps for the same :

Check whether listner is down Or Down Listener First

Take Name and open mode of database
SQL ) Select name,open_mode from v$database;

Take screen shot of location + status of tempfile

SQL ) set pagesize 0
SQL ) spool filetemp.txt
SQL ) select TS#,NAME,STATUS from v$tempfile;
SQL ) spool off

Now shut down the database and copy the files to new location

sqlplus / as sysdba
shutdown immediate

Note :: During shutdown of the database it hung for about 45-50 minutes, checked alert file nothing was happening, killed the ora_lgwr process with "kill -9" command then started the database with "startup" and then "shutdown immediate" it was smooth then started copying the files.

copy files from existing location to new mount point :

cp -pr /oracle/ZB1/sapdata1/temp_1/temp.data1 /oracle/ZB1/sapdata_temp1/temp_1 cp -pr /oracle/ZB1/sapdata8/temp_2/temp.data2 oracle/ZB1/sapdata_temp1/temp_2
cp -pr /oracle/ZB1/sapdata8/temp_3/temp.data3 oracle/ZB1/sapdata_temp1/temp_3


SQL ) startup mount

SQL ) alter database rename file '/oracle/ZB1/sapdata1/temp_1/temp.data1' to '/oracle/ZB1/sapdata_temp1/temp_1/temp.data1';
SQL ) alter database rename file '/oracle/ZB1/sapdata8/temp_2/temp.data2' to '/oracle/ZB1/sapdata_temp1/temp_2/temp.data2';
SQL ) alter database rename file '/oracle/ZB1/sapdata8/temp_3/temp.data3' to '/oracle/ZB1/sapdata_temp1/temp_3/temp.data3';


SQL ) alter database open;


Check the current status of updated temp files
SQL ) select TS#,NAME,STATUS from v$tempfile;

Query to ascertain database size

select (select sum(bytes)/1024/1024 from dba_data_files)+ (select sum(bytes)/1024/1024 from dba_temp_files) "Size in MB" from dual;

Table Reorg - Pre-requisites

1. No backup should be running in the system

select * from v$backup.

2. No archiving job should be running in the system, check from SM37


3. The size of oraarch (saparch) - should be free at least to the size of Table to be reorganized. Normally 30-40 GB free

4. Size of Table space PSAPSR3 should by 120% of the size of table to be reorganized.

5. Run the stats and check the table size before and after Reorg.

Table Reorg - How to do - commands

1. archive destination (sufficient space) - 20GB - 22GB (screen shot)
bdf | grep saparch (SID)


2. Tablespace PSAPEVO - object - SPACE should be same as table size - (20%). (screen shot)

select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name
, sum(bytes_used + bytes_free)/1024/1024 used_mb
from v$temp_space_header group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name union all
select tablespace_name,sum(bytes_free)/1024/1024 free_mb from v$temp_space_header group by tablespace_name ) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;

3. Collect table statistics

brconnect -c -u / -f stats -t /BIC/B0000032000 -f collect -p 2

4. Checking TOP 20 Fregmented tables.

SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *
(1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE /
100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE 'SAP%' AND
PARTITIONED = 'NO' AND Table_name in ('/BIC/B0000034000
','/BIC/B0000582000',
',
(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;

5. Getting Table & DB size

select sum(bytes)/1024/1024/1024 from dba_segments;

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=();