ORA-01187
Found ORA-01187 and ORA-01110 after activating a standby database.
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 2003: '+DATA/ORCLCDB/ORCLPDB/temp01.dbf'
ORA-01187 means that the tempfile you want to read cannot be verified by server processes, most likely, the file is in another incarnation. Therefore, the database cannot re-create tempfile after you open the database.
Meanwhile, we saw some errors in the alert log.
ORA-01186: file 2003 failed verification tests
ORA-01122: database file 2003 failed verification check
ORA-01110: data file 2003: '+DATA/ORCLCDB/ORCLPDB/temp01.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
ORCLPDB(3):Cannot re-create tempfile +DATA/ORCLCDB/ORCLPDB/temp01.dbf, the same name file exists
It seems that we can no longer use the tempfile which is not in the same incarnation as the rest of data files in the database. Moreover, Oracle tried to re-create the tempfile but no luck, because the file is physically existing.
Solution
Since the tempfile is no longer usable, we take the following steps to make all tempfiles get back to work.
Delete All TempFiles
The very first step is to remove all tempfiles. Since it's a RAC database, we remove it by grid.
[grid@standby01 grid]$ asmcmd rm +DATA/ORCLCDB/ORCLPDB/temp01.dbf
Next, we have 2 options to re-create all tempfiles back.
The first way is to restart the database, no matter it's a normal database (non-CDB) or a pluggable database (PDB). Oracle will automatically re-create all missing tempfiles back after opening the database, and we can skip the rest of steps.
The second way is to re-create tempfiles manually by ourselves.
Although the first way is preferable, but it needs a restart. If you can't restart the database at this moment, we take the following procedure to re-create them when the database is online.
Let's see how we re-create them back manually.
Create New Temporary Tablespace
To exempt TEMP from the default temporary tablespace of the database before we drop it, we need another temporary tablespace. In this case, we use TEMP1 tablespace.
If you don't have one, please create a new temporary tablespace for the next step.
SQL> create temporary tablespace temp1 tempfile '+DATA/ORCLCDB/ORCLPDB/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.
Switch Over Default Temporary Tablespace
We temporarily switch the default temp tablespace to TEMP1.
SQL> alter database default temporary tablespace temp1;
Database altered.
You may stop here, if you do like TEMP1 to be your default temporary tablespace.
Recreate TEMP Tablespace
We drop the tablespace then create the original temporary tablespace back.
SQL> drop tablespace temp;
Tablespace dropped.
SQL> create temporary tablespace temp tempfile '+DATA/ORCLCDB/ORCLPDB/temp01.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.
5. Switch Back Default Temp Tablespace
The last step to solve ORA-01187 is to switch the default temporary tablespace back to TEMP.
SQL> alter database default temporary tablespace temp;
Database altered.
It's working now.