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;