How to Restore Dropped PDB

Hi,

it seems easy, right? Well, not that much but not that hard either. if you drop a pdb in a cdb, you cannot restore it using current database because when you drop the pdb, all information about the pdb is lost. So, what is the solution? you need a point in time recovered duplicated database.

I have a cdb named CDB and had a pluggable database named PDB1. this database is a 21c database and installation of CDB is pretty standard.

db_create_file_dest = /u01/app/oracle/oradata
db_recovery_file_dest = /u01/app/oracle/fast_recovery_area

Let’s say I dropped it by mistake and make a recover:

create pluggable database pdb1 admin user admin identified by admin; -- what a security

alter pluggable database pdb1 open;

alter session set container=pdb1;

create table tmp_pitr(d number);

insert into tmp_pitr select level from dual connect by level < 11;
commit;

select sysdate from dual;
2022-02-04 13:15:06

create pluggable database pdb1 admin user admin identified by admin; -- what a security

alter pluggable database pdb1 open;

alter session set container=pdb1;

create table tmp_pitr(d number);

insert into tmp_pitr select level from dual connect by level < 11;

commit;

select sysdate from dual;

2022-02-04 13:15:06

I created the pdb and create a table in it. I also get the time because it will be required while restoring pdb. now, let’s drop it by mistake:

alter session set container=cdb$root;

alter pluggable database pdb1 close immediate;

drop pluggable database pdb1 including datafiles;

alter session set container=cdb$root;

alter pluggable database pdb1 close immediate;

drop pluggable database pdb1 including datafiles;

I don’t add outputs for now. Okay, now I lost my pdb. to restore it, we must create a duplicate database, recovered until a specific time. step by step:

1- create a new pfile for duplicate database (let’s called out db name as “dup”)

vim /u01/app/oracle/dbs/initDUP.ora

audit_file_dest='/tmp/dup/'
diagnostic_dest='/tmp/dup/'
audit_trail='db'
compatible='21.0.0' 
control_files='/tmp/dup/dup.ctl'
db_name='dup'
instance_name='dup'
db_unique_name='dup'
enable_pluggable_database=true
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
db_create_file_dest='/tmp/dup/'               
db_create_online_log_dest_1='/tmp/dup/'

vim /u01/app/oracle/dbs/initDUP.ora

audit_file_dest='/tmp/dup/'

diagnostic_dest='/tmp/dup/'

audit_trail='db'

compatible='21.0.0'

control_files='/tmp/dup/dup.ctl'

db_name='dup'

instance_name='dup'

db_unique_name='dup'

enable_pluggable_database=true

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

db_create_file_dest='/tmp/dup/'

db_create_online_log_dest_1='/tmp/dup/'

2- create password file:

orapwd password=Orcl_1234 format=12.2 file=/u01/app/oracle/dbs/orapwdup.ora

1	orapwd password=Orcl_1234 format=12.2 file=/u01/app/oracle/dbs/orapwdup.ora

3- startup the instance

export ORACLE_SID=dup

sqlplus / as sysdba

startup nomount

exit

export ORACLE_SID=dup

sqlplus / as sysdba

startup nomount

exit

4- use rman and create duplicate database

rman auxiliary /

run {
set until time "to_date('2022-02-04 13:15:06', 'yyyy-mm-dd hh24:mi:ss')";
duplicate database to dup noopen backup location '/u01/app/oracle/fast_recovery_area/CDB/';
}

rman auxiliary /

run {

set until time "to_date('2022-02-04 13:15:06', 'yyyy-mm-dd hh24:mi:ss')";

duplicate database to dup noopen backup location '/u01/app/oracle/fast_recovery_area/CDB/';

}

you can get different error messages at this point but they all have enough information to solve the problem. if you get this error:

RMAN-05501: aborting duplication of target database
RMAN-05657: There are multiple database IDs present.

then you must set dbid before run the duplicate database command.

export ORACLE_SID=cdb

sqlplus / as sysdba

select dbid from v$container where name = 'CDB$ROOT';
2211841890

export ORACLE_SID=dup

rman auxiliary /

set dbid 2211841890;

run{
...

export ORACLE_SID=cdb

sqlplus / as sysdba

select dbid from v$container where name = 'CDB$ROOT';

2211841890

export ORACLE_SID=dup

rman auxiliary /

set dbid 2211841890;

run{

...

5- after duplicate database command completed (which will take for a while) your new database called “dup” will be ready and in mount mode.

export ORACLE_SID=dup

sqlplus / as sysdba
select name from v$containers;

NAME                                                                                                                            
--------------
CDB$ROOT
PDB$SEED
_###_UNKNOWN_PDB_#_4

export ORACLE_SID=dup

sqlplus / as sysdba

select name from v$containers;

NAME

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

CDB$ROOT

PDB$SEED

_###_UNKNOWN_PDB_#_4

at this point if you query pdbs (containers), a strange name will appear “_###_UNKNOWN_PDB_#_4” (4 is pdb number so it might be different on your systems). after Opening your database with resetlogs (this is point in time recovery) it will be converted to correct name.

export ORACLE_SID=dup

sqlplus / as sysdba

alter database open resetlogs;

select name from v$containers;

NAME                                                                                                                            
--------------
CDB$ROOT
PDB$SEED
pdb1

export ORACLE_SID=dup

sqlplus / as sysdba

alter database open resetlogs;

select name from v$containers;

NAME

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

CDB$ROOT

PDB$SEED

pdb1

here it is. after this point we have 2 container databases on the server, cdb and dup. so, we can simply unplug and plug pdb1 into database CDB again.

export ORACLE_SID=dup
sqlplus / as sysdba

alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';

exit;

export ORACLE_SID=cdb
sqlplus / as sysdba

create pluggable database pdb1 using '/tmp/pdb1.xml' move;

exit;

export ORACLE_SID=dup

sqlplus / as sysdba

alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';

exit;

export ORACLE_SID=cdb

sqlplus / as sysdba

create pluggable database pdb1 using '/tmp/pdb1.xml' move;

exit;

I use “move” while plugging it into CDB database because I want its datafiles move to db_create_file_dest locations of CDB database.

that’s it. I found this solution is a little bit tiresome and I believe next releases will have easier solutions but still we can complete what we need in a few commands.

thanks for reading, I hope this helps.

wish you all healthy days.

Leave a Reply Cancel reply