If flashback of CDB complete, there are chances to previously dropped PDB datafiles goes offline. There might be PDB dropped in past and now dropped PDB related datafiles becomes offline during flashback database.
Example
Backup of this database taken around scn 4700000. PDB3 was dropped at SCN 4800000. Flashback for CDB was complete until scn 4810629. Find the files those are offline (those are related to Dropped PDB i.e pdb3)
sql> select file#,name from v$datafile;
For this test, we have two files related to dropped PDB, after flashback was performed both files became offline and file names are UNNAMED00017 & UNNAMED00018.
/u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00017 /u01/app/oracle/product/12.1.0.2/db_1/dbs/UNNAMED00018
from alert logfile:
Flashback Restore Start Flashback: Added pluggable database #5: 'PDB3' to the control file. Flashback: created tablespace #0: 'SYSTEM' of pdb #5 in the controlfile. Flashback: created OFFLINE file 'UNNAMED00017' for tablespace #0 of pdb #5 in the controlfile. Filename was: '/u01/app/oracle/oradata/CDBPRODS/4F88781C447A496AE0533B20B80AAF00/datafile/o1_mf_system_dkl7nplc_.dbf' when dropped. File will have to be restored from a backup and recovered. Flashback: created tablespace #1: 'SYSAUX' of pdb #5 in the controlfile. Flashback: created OFFLINE file 'UNNAMED00018' for tablespace #1 of pdb #5 in the controlfile. Filename was: '/u01/app/oracle/oradata/CDBPRODS/4F88781C447A496AE0533B20B80AAF00/datafile/o1_mf_sysaux_dkl7npld_.dbf' when dropped. File will have to be restored from a backup and recovered. Flashback: created tablespace #2: 'TEMP' of pdb #5 in the controlfile. Flashback Restore Complete Flashback Media Recovery Start
How to recover those files from backup
1. Restore the datafiles with until scn option:
RMAN> run {
set until scn 4810629;
set newname for datafile 17 to '/u01/app/oracle/oradata/CDBPRODS/datafile/datafile171.dbf';
set newname for datafile 18 to '/u01/app/oracle/oradata/CDBPRODS/datafile/datafile181.dbf';
restore pluggable database pdb3;
}
2. switch datafiles to copy (rename the datafiles those where with UNNAMED000xx) (in this example, datafile 17 and 18 were restored):
RMAN> switch datafile 17 to copy; RMAN> switch datafile 18 to copy;
You can check the PDBs:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB3 MOUNTED
3. Online the datafiles. Connect to the pdb and make it online, by default flashback would keep datafiles in offline status:
SQL> alter session set container=pdb3; SQL> alter database datafile '/u01/app/oracle/oradata/CDBPRODS/datafile/datafile171.dbf' online; SQL> alter database datafile '/u01/app/oracle/oradata/CDBPRODS/datafile/datafile181.dbf' online;
4. Recover your PDB files. Below recovery will create auxiliary instance and go for recovery of those datafiles:
RMAN> run {
set until scn 4810629;
recover pluggable database pdb3;
}
5. Open the pluggable database in resetlogs:
RMAN> alter pluggable database pdb3 open resetlogs;