When your oracle temporary table space get corrupted.
So, guys its after few weeks here again with new post. š® Itās my early days with Oracle Database. After playing with Mysql, Mssql and Orient DB(Graph Db) for nearly 4ā5 years, now itās time to start something new. Itās Oracle. š
Still new to Oracle/Oracle DB Administration , but hope this is going to be more interesting than i thought. So what iām going to post today is something related to common Oracle error.
āORA-01187: cannot read from file because it failed verification testsā
This is mainly due to Oracle Temporary tablespace Data file Corruption. So i got the correct way of resolving this error.
1. Letās first check our v$tempfile.
This sql query will list down all the tempfiles and their status whether online or offline.
2. Then we should drop the corrupted tempfile from the DB. If not, this error continues. as an example letās say my error comes from temp1.dbf file which is related to TEMP1 temporary tablespace. So we need to drop this corrupted tempfile(temp1.dbf) from DB.
** Not the table space , itās only tempfile.
This will drop the corrupted tempfile from DB.
3. Check V$TEMPFILE again. Now you can see that temp1.dbf file was gone from the list. šš
4. Now we should create new tempfile for the TEMP1 table space.
**Do not separately create new temp file and add it to TEMP1 tablespace. Because it is not going to work. Therefore, use below query.
This query will create new temp4.dbf temp file and add it to the TEMP1 tablespace which is the default tablespace for this Oracle DB.
Game Over! šŖšŖšŖšŖ
Now you are clean with your oracle temporary tablespace & tempfile.
Hope this will be helpful to someone. See you soon. šš
Originally published at http://printfnimesh.wordpress.com on December 1, 2017.