Temp file Recovery in CDB & PDB

Temp File loss and recovery

Temp file loss in CDB

SQL> select name from V$tempfile;

NAME

---------------------------------------------------------------------------------

/oracle/product/oradata/sec_cdb/temp01.dbf

/oracle/product/oradata/sec_cdb/pdbseed/pdbseed_temp012015-08-23_10-05-57-AM.dbf

/opt/oracle/oradata/pdb_purchase/temp01.dbf

/opt/oracle/oradata/pdb_hr/temp201.dbf

SQL> select con_id, name from V$tempfile;

CON_ID  NAME

------ ---------------------------------------------------------------------

1      /oracle/product/oradata/sec_cdb/temp01.dbf

2      /oracle/product/oradata/sec_cdb/pdbseed/pdbseed_temp012015-AM.dbf

3      /opt/oracle/oradata/pdb_purchase/temp01.dbf

4      /opt/oracle/oradata/pdb_hr/temp201.dbf

Manually removing the tempfile to mitigate failure

SQL> ! rm -f /oracle/product/oradata/sec_cdb/temp01.dbf

54  select * from dba_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13;

select * from dba_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13

                                                               *

ERROR at line 54:

ORA-01116: error in opening database file 201

ORA-01110: data file 201: '/oracle/product/oradata/sec_cdb/temp01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

If the db is open then we can add a tempfile to the the temp tablespace and drop the missing tempfile once it is not used by any of the process.

SQL> alter tablespace TEMP add tempfile                '/oracle/product/oradata/sec_cdb/temp02.dbf' size 1g;

Tablespace altered.

When the temp file is removed when the db is down and during the db is bought up, it will automatically creates the missing tempfile. Check the below snap from alert log.

Mon Aug 24 18:46:38 2015

Errors in file /u01/app/oraInventory/diag/rdbms/sec_cdb/seccdb/trace/seccdb_dbw0_14883.trc:

ORA-01186: file 201 failed verification tests

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/oracle/product/oradata/sec_cdb/temp01.dbf'

Mon Aug 24 18:46:38 2015

File 201 not verified due to error ORA-01157

Starting background process SMCO

Mon Aug 24 18:46:38 2015

Re-creating tempfile /oracle/product/oradata/sec_cdb/temp01.dbf

Mon Aug 24 18:46:38 2015

SMCO started with pid=24, OS id=15080

Temp file Loss in PDB

$ rm -f /oracle/product/oradata/sec_cdb/hrdept/pdbseed_temp01.dbf

Temp file will be re-created automatically when the pdb opens.

Or if the PDB if already open then manually add an tempfile to the temp tablespace and drop the missing tempfile.

Errors in file /u01/app/oraInventory/diag/rdbms/sec_cdb/seccdb/trace/seccdb_dbw0_14883.trc:

ORA-01186: file 203 failed verification tests

ORA-01157: cannot identify/lock data file 203 - see DBWR trace file

ORA-01110: data file 203: '/oracle/product/oradata/sec_cdb/hrdept/pdbseed_temp01.dbf'

Mon Aug 24 18:55:37 2015

File 203 not verified due to error ORA-01157

Mon Aug 24 18:55:37 2015

Re-creating tempfile /oracle/product/oradata/sec_cdb/hrdept/pdbseed_temp01.dbf