How to drop and recreate a temp tablespace online and resolve ORA-01187: cannot read from file because it failed verification tests

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 1001: '/u01/oradata/pdbdbqpf1/temp_01.tpf'


SQL>
SQL>
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';
SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP'
                                              *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 1001: '/u01/oradata/pdbdbqpf1/temp_01.tpf'






SQL>
SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp2_01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL>  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;
CREATE TEMPORARY TABLESPACE temp
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists


SQL> drop tablespace temp including datafiles;
drop tablespace temp including datafiles
                               *
ERROR at line 1:
ORA-01911: CONTENTS keyword expected


SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL>
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL>
SQL>
SQL> drop tablespace temp2 including contents;

Tablespace dropped.

SQL>
SQL>
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';
ERROR:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 1002: '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf'



no rows selected



oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 32505864192 Jun 29 22:20 pdbtemp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:19 temp01.dbf
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> CREATE TEMPORARY TABLESPACE temp3
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp3_01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.


oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 32505864192 Jun 29 22:25 pdbtemp_01.tpf
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:24 temp3_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop;

Database altered.

SQL> !

oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 32505864192 Jun 29 22:25 pdbtemp_01.tpf
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:24 temp3_01.dbf


SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including tempfiles;
alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including tempfiles
                                                                                 *
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including datafiles;
alter database tempfile '/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf' drop including datafiles
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/pdbdbqpf1/pdbtemp_01.tpf"




SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/temp_01.tpf' drop;
alter database tempfile '/u01/oradata/pdbdbqpf1/temp_01.tpf' drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/pdbdbqpf1/temp_01.tpf"



oracle@pprfg67db800.ie.cgban.net:/u01/app/oracle/product/11.2.0.3/bin->cd /u01/oradata/pdbdbqpf1
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba 21474844672 Jun 15 00:13 temp_01.tpf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:30 temp3_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->rm -rf temp_01.tpf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> alter database tempfile '/u01/oradata/pdbdbqpf1/temp2_01.dbf' drop;
alter database tempfile '/u01/oradata/pdbdbqpf1/temp2_01.dbf' drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/oradata/pdbdbqpf1/temp2_01.dbf"




oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->ls -l | grep temp
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:18 temp2_01.dbf
-rw-r----- 1 oracle dba  1073750016 Jun 29 22:30 temp3_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->rm -rf temp2_01.dbf
oracle@pprfg67db800.ie.cgban.net:/u01/oradata/pdbdbqpf1->exit
exit

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u01/oradata/pdbdbqpf1/temp01.dbf' SIZE 1024M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> drop tablespace temp3 including contents and datafiles;

Tablespace dropped.

SQL>
SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/pdbdbqpf1/temp01.dbf

SQL>
SQL>
SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/pdbdbqpf1/temp01.dbf

ALTER TABLESPACE TEMP add tempfile '/u01/oradata/pdbdbqpf1/temp04.dbf' size 4096M reuse;

ALTER TABLESPACE TEMP add tempfile '/u01/oradata/pdbdbqpf1/temp04.dbf' size 4096M reuse;