• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Recover DROPPED PDB After Flashback of CDB

by admin

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;

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. What Is Oracle Key Vault
  2. How to Create a Physical Standby from ASM Primary
  3. DNS and DHCP Setup Example for Oracle Grid Infrastructure GNS
  4. Cron Script does not Execute as Expected from crontab – Troubleshoot
  5. Difference between SQL and SQL*Plus Statements
  6. XA and NON-XA
  7. How to Disable Oracle Net Tracing without stopping server process
  8. How To Resize An ACFS Filesystem/ASM Volume (ADVM)
  9. Oracle Database 12c New Feature: SYSRAC administrative privilege
  10. How to Disable AUTOEXTEND Mode on a datafile in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • diffstat: Create a histogram from the output of the diff command
  • diffoscope: Compare files, archives, and directories
  • diff-pdf: Tool for comparing two PDFs
  • dict: Command line dictionary using the DICT protocol

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright