Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

How to Restore Dropped PDB

Mustafa, 2022-02-04

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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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:

Oracle PL/SQL
1
2
3
4
5
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”)

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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:

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

3- startup the instance

Oracle PL/SQL
1
2
3
4
5
6
7
export ORACLE_SID=dup
 
sqlplus / as sysdba
 
startup nomount
 
exit

4- use rman and create duplicate database

Oracle PL/SQL
1
2
3
4
5
6
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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.

12c 18c 19c 21c Administration _###_UNKNOWN_PDB_#_dropped pdbduplicate databasepdb point in time recoveryrecover dropped pdb

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes