How to recovery PDB when PDB database is dropped :)
[oracle@rac01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 12:20:24 2025
Version 21.13.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
SQL>
SQL>
SQL> alter pluggable database PDB1 close;
Pluggable database altered.
SQL>
SQL>
SQL> drop pluggable database PDB1 including datafiles;
Pluggable database dropped.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB2 READ WRITE NO
============Alert log file contents======
2025-01-11T12:20:48.610884+01:00
alter pluggable database PDB1 close
2025-01-11T12:20:48.621225+01:00
PDB1(3):Pluggable database PDB1 closing
PDB1(3):JIT: pid 92739 requesting stop
PDB1(3):Closing sequence subsystem (204729984896).
PDB1(3):Buffer Cache flush started: 3
PDB1(3):Buffer Cache flush finished: 3
Pluggable database PDB1 closed
Completed: alter pluggable database PDB1 close
2025-01-11T12:21:10.490974+01:00
drop pluggable database PDB1 including datafiles
2025-01-11T12:21:10.596417+01:00
Deleted file +DATA/CDBAM/PDB1/undo_seed01.dbf
Deleted file +DATA/CDBAM/PDB1/users01.dbf
Deleted file +DATA/CDBAM/PDB1/temp01.dbf
Deleted file +DATA/CDBAM/PDB1/sysaux01.dbf
Deleted file +DATA/CDBAM/PDB1/system01.dbf
2025-01-11T12:21:10.640983+01:00
Stopped service pdb1
Completed: drop pluggable database PDB1 including datafiles======Execute the below script to recover PDB1 ======
Note: Never use RESTORE command as per Oracle support suggestion
run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')"
auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}log for recovery :-
[oracle@rac01 AUX]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 11 14:07:44 2025
Version 19.23.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDBAM (DBID=1604254345)
RMAN>
RMAN> run
{
set newname for datafile 20 to '+DATA/CDBAM/PDB1/system01.dbf';
set newname for datafile 21 to '+DATA/CDBAM/PDB1/sysaux01.dbf';
set newname for datafile 22 to '+DATA/CDBAM/PDB1/users01.dbf';
set newname for datafile 23 to '+DATA/CDBAM/PDB1/undo_seed01.dbf';
2> recover pluggable database pdb1 until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/applications/oracle/d19.3.0/CDBAM_BKP/AUX/';
}3> 4> 5> 6> 7> 8>
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting recover at 11-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='aiAi'
initialization parameters used for automatic instance:
db_name=CDBAM
db_unique_name=aiAi_pitr_pdb1_CDBAM
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/applications/oracle/d19.3.0/oracle_base
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=6144M
processes=200
db_create_file_dest=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/
log_archive_dest_1='location=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance CDBAM
Oracle instance started
Total System Global Area 6442447480 bytes
Fixed Size 9192056 bytes
Variable Size 1124073472 bytes
Database Buffers 5301600256 bytes
Redo Buffers 7581696 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 11-JAN-25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/c-1604254345-20250111-01 tag=TAG20250111T121656
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl
Finished restore at 11-JAN-25
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 6 to new;
set newname for datafile 20 to new;
set newname for datafile 21 to new;
set newname for datafile 22 to new;
set newname for datafile 23 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 6, 20, 21, 22, 23;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 11-JAN-25
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0i3evgde_18_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00020 to +DATA/CDBAM/PDB1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to +DATA/CDBAM/PDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to +DATA/CDBAM/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to +DATA/CDBAM/PDB1/undo_seed01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1
channel ORA_AUX_DISK_1: piece handle=/applications/oracle/d19.3.0/CDBAM_BKP/0k3evgdm_20_1_1 tag=TAG20250111T121646
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-JAN-25
datafile 1 switched to datafile copy
input datafile copy RECID=29 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_system_mr4v9vv6_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=30 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_undotbs1_mr4v9vvc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=31 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=32 STAMP=1190124510 file name=/applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_users_mr4v9vvg_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('11-JAN-2025 12:21:00','DD-MON-YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 6 online";
sql clone 'PDB1' "alter database datafile
20 online";
sql clone 'PDB1' "alter database datafile
21 online";
sql clone 'PDB1' "alter database datafile
22 online";
sql clone 'PDB1' "alter database datafile
23 online";
#recover pdb
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database
'PDB1' delete archivelog;
#open in read write mode
sql clone 'alter database open resetlogs';
#unplug dropped pdb into temp file
sql clone "alter pluggable database PDB1 unplug into ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''";
#create pdb using temp file of recovered pdb
sql "create pluggable database PDB1 using ''
/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse";
alter pluggable database PDB1 open;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 20 online
sql statement: alter database datafile 21 online
sql statement: alter database datafile 22 online
sql statement: alter database datafile 23 online
Starting recover at 11-JAN-25
using channel ORA_AUX_DISK_1
Executing: alter database datafile 2, 4, 7, 15 offline
Executing: alter database datafile 16, 17, 18, 19 offline
starting media recovery
archived log for thread 1 with sequence 269 is already on disk as file +ARCH/cdbam/arch/1_269_1189847369.dbf
archived log for thread 1 with sequence 270 is already on disk as file +ARCH/cdbam/arch/1_270_1189847369.dbf
archived log file name=+ARCH/cdbam/arch/1_269_1189847369.dbf thread=1 sequence=269
archived log file name=+ARCH/cdbam/arch/1_270_1189847369.dbf thread=1 sequence=270
media recovery complete, elapsed time: 00:00:01
Finished recover at 11-JAN-25
sql statement: alter database open resetlogs
sql statement: alter pluggable database PDB1 unplug into ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml''
sql statement: create pluggable database PDB1 using ''/applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml'' nocopy tempfile reuse
Statement processed
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /applications/oracle/d19.3.0/oracle_home/dbs/_rm_pdb_pitr_9_aiAi.xml deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/datafile/o1_mf_sysaux_mr4v9vv9_.dbf deleted
auxiliary instance file /applications/oracle/d19.3.0/CDBAM_BKP/AUX/CDBAM/controlfile/o1_mf_mr4v9ltv_.ctl deleted
Finished recover at 11-JAN-25
RMAN>
PDB1 recovered successfully.
[oracle@rac01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Jan 11 13:00:24 2025
Version 21.13.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0
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
