When reading the headline, the todays topic sounds not very complicated or special at all. But be warned, it definitely is. During a Migration-to-Multitenant project at a customers site we walked through quite a couple of restore scenarios. The most obvious scenario is dropping a PDB by accident. Of cause we have all the backups in place, that we will need to restore and recover the lost PDB.
Let’s start with what I did to the PDB:
[oracle@vm160 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 23 13:54:28 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.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 ORCL19PDB3 READ WRITE NO
5 ORCL19PDB2 READ WRITE NO
SQL> alter session set container=pdb1;
Session altered.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
108
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.
That was the easiest part of the story. You see, there were 3 PDBs, the dropped PDB had a table “EMPLOYEES” with 108 records. Now the simple goal ist to restore that PDB to right before the drop. To get that timestamp, we simply look it up in the alert.log:
[oracle@vm160 ~]$ tail /u01/app/oracle/diag/rdbms/orcl19/orcl19/trace/alert_orcl19.log
2023-02-23T13:55:01.355396+01:00
alter pluggable database pdb1 close immediate
2023-02-23T13:55:01.357622+01:00
PDB1(3):Pluggable database PDB1 closing
PDB1(3):JIT: pid 9707 requesting stop
PDB1(3):Closing sequence subsystem (3457133901505).
PDB1(3):Buffer Cache flush started: 3
2023-02-23T13:55:02.353510+01:00
PDB1(3):Buffer Cache flush finished: 3
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close immediate
2023-02-23T13:55:14.816955+01:00
drop pluggable database pdb1 including datafiles
2023-02-23T13:55:17.338832+01:00
Deleted Oracle managed file /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_ksfwjspy_.dbf
Deleted Oracle managed file /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_temp_kq6nz8kv_.dbf
Deleted Oracle managed file /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_kq6nz8kv_.dbf
Deleted Oracle managed file /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_kq6nz8kt_.dbf
Deleted Oracle managed file /u01/app/oracle/oradata/ORCL19/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_kq6nz8kh_.dbf
2023-02-23T13:55:17.616908+01:00
Stopped service pdb1
Completed: drop pluggable database pdb1 including datafiles
Now that we identified the propper timestamp which is roughly 13:55, let’s start with RMAN:
[oracle@vm160 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 23 13:57:26 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL19 (DBID=422142402)
RMAN> run {
2> set until time "to_date('23.02.2023 13:55:00', 'dd.mm.yyyy hh24:mi:ss')";
3> restore pluggable database pdb1;
4> recover pluggable database pdb1;
5> }
executing command: SET until clause
Starting restore at 2023-02-23 13:58:23
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/23/2023 13:58:24
ORA-01403: no data found
RMAN> restore pluggable database pdb1;
Starting restore at 2023-02-23 13:58:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=277 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/23/2023 13:58:31
RMAN-06813: could not translate pluggable database PDB1
Ok, neither can we do a point-in-time restore nor can we do a complete and current restore of the dropped PDB. This is because the controlfile is our single point of truth and it simply forgot everything about the PDB when I dropped it.
What we need to restore the PDB is an auxiliary instance to restore and recover the lost PDB. MOS Note “How to Restore – Dropped Pluggable database (PDB) in Multitenant (Doc ID 2034953.1)” is using DUPPLICATE for that, but this requires to have all neccessary backups in one location. So I chose a different approach. My auxiliary instance will be based on the original one, so I create a Pfile from it, that I will change according to my needs:
- choose a new db_unique_name
- remove “control_files” parameter
- leave create-file-destinations unchanged
- change memory parameters to minimum values
SQL> create pfile='/tmp/initaux.ora' from spfile;
File created.
*.audit_file_dest='/u01/app/oracle/admin/orcl19/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.container_data='ALL'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain='support.robotron.de'
*.db_name='orcl19'
*.db_unique_name='orcl19aux'
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.db_recovery_file_dest_size=10494m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl19XDB)'
*.enable_pluggable_database=true
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=364m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE
*.sec_return_server_release_banner=TRUE
*.sga_target=2048m
*.undo_tablespace='UNDOTBS1'
Keeping the values for file destinations will restore the datafiles in their original paths in case of user-defined filenames. In case of OMF we will get new subdirectories according to the new db_unique_name. Either way the files will be created in the filesystem the original database is using.
With this Pfile I can now start my auxiliary instance and create a SPfile. Having a SPfile is handy because RMAN will update the “control_files” parameter accordingly when restoring the controlfiles. Of cause I have to set the ORACLE_SID to some value other than the original CDB.
[oracle@vm160 ~]$ export ORACLE_SID=aux
[oracle@vm160 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 23 14:22:46 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/tmp/initaux.ora
ORACLE instance started.
Total System Global Area 2147482320 bytes
Fixed Size 9165520 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7610368 bytes
SQL> create spfile from pfile='/tmp/initaux.ora';
File created.
SQL> startup nomount force
ORACLE instance started.
Total System Global Area 2147482320 bytes
Fixed Size 9165520 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7610368 bytes
Having that, I can now restore and recover my PDB using RMAN. Note, since we only restore the one PDB, we have to skip all the other PDBs and the PDB$SEED during recovery.
[oracle@vm160 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 23 14:27:46 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL19 (not mounted)
RMAN> set DBID 422142402
executing command: SET DBID
RMAN> run {
1 set until time "to_date('23.02.2023 13:55:00', 'dd.mm.yyyy hh24;mi:ss')";
2 set newname for database to new;
3 restore controlfile from '/u01/app/oracle/fra/ORCL19/autobackup/2023_02_23/o1_mf_s_1129557030_kzgr8qj7_.bkp';
4 alter database mount;
5 restore database root pluggable database pdb1;
6 switch datafile all;
7 switch tempfile all;
8 recover database skip forever tablespace
9 "ORCL19PDB3":"SYSTEM","ORCL19PDB3":"SYSAUX","ORCL19PDB3":"UNDOTBS1","ORCL19PDB2":"SYSTEM","ORCL19PDB2":"SYSAUX","ORCL19PDB2":"UNDOTBS1","PDB$SEED":"SYSTEM","PDB$SEED":"SYSAUX","PDB$SEED":"UNDOTBS1";
10 }
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2023-02-23 14:51:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=256 device type=DISK
[...]
Starting recover at 2023-02-23 15:08:47
using channel ORA_DISK_1
Executing: alter database datafile 5, 6, 8 offline drop
Executing: alter database datafile 12, 13, 14 offline drop
Executing: alter database datafile 15, 16, 17 offline drop
starting media recovery
archived log for thread 1 with sequence 164 is already on disk as file /u01/app/oracle/fra/ORCL19/archivelog/2023_02_23/o1_mf_1_164_kzgr5rdp_.arc
archived log for thread 1 with sequence 165 is already on disk as file /u01/app/oracle/fra/ORCL19/onlinelog/o1_mf_3_kq6lk1ty_.log
archived log file name=/u01/app/oracle/fra/ORCL19/archivelog/2023_02_23/o1_mf_1_164_kzgr5rdp_.arc thread=1 sequence=164
archived log file name=/u01/app/oracle/fra/ORCL19/onlinelog/o1_mf_3_kq6lk1ty_.log thread=1 sequence=165
media recovery complete, elapsed time: 00:00:06
Finished recover at 2023-02-23 15:08:58
In case you have several PDBs to skip or if you do not like to type all the tablespaces by hand, you can use this handy SQL on the original database to generate the list of tablespaces to skip:
SQL> select listagg('"'||p.name||'":"'||t.tablespace_name||'"', ',')
2 from v$pdbs p, cdb_tablespaces t
3 where p.con_id=t.con_id
4 and t.contents <> 'TEMPORARY'
SQL> /
LISTAGG('"'||P.NAME||'":"'||T.TABLESPACE_NAME||'"',',')
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"ORCL19PDB3":"SYSTEM","ORCL19PDB3":"SYSAUX","ORCL19PDB3":"UNDOTBS1","ORCL19PDB2":"SYSTEM","ORCL19PDB2":"SYSAUX","ORCL19PDB2":"UNDOTBS1","PDB$SEED":"SYSTEM","PDB$SEED":"SYSAUX","PDB$SEED":"UNDOTBS1"
At this point we could open the auxiliary instance using the “open resetlogs” clause. But wait, we restored the controlfile from the original database so we have the same filenames for the redo members. And we are on the same host. Clearing those redo files might not be the best option. It would simply overwrite the redo files of our original database and we would end up doing another incomplete recovery… My solution is to create a new controlfile with modified redo members. As a basis I backup the controlfile to trace, change the db_name right away and restart my auxiliary instance to nomount.
SQL> alter database backup controlfile to trace as '/tmp/create_ctrl_aux.sql' resetlogs;
Database altered.
SQL> alter system set db_name="AUX" scope=spfile;
System altered.
SQL> startup nomount force
ORACLE instance started.
Total System Global Area 2147482320 bytes
Fixed Size 9165520 bytes
Variable Size 486539264 bytes
Database Buffers 1644167168 bytes
Redo Buffers 7610368 bytes
Then I modified the “create controlfile” statement
- set the new database name
- remove all the files, that I didn’t restore
- remove redo file names (OMF) – or – change redo file names
The final statement is this:
CREATE CONTROLFILE REUSE set DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 200M BLOCKSIZE 512,
GROUP 2 SIZE 200M BLOCKSIZE 512,
GROUP 3 SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/ORCL19AUX/datafile/o1_mf_system_kzgvw29p_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/datafile/o1_mf_sysaux_kzgvw291_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/datafile/o1_mf_undotbs1_kzgvw2b9_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/datafile/o1_mf_users_kzgvw2f8_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_system_kzgw69jf_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_sysaux_kzgw69jz_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_undotbs1_kzgw69kb_.dbf',
'/u01/app/oracle/oradata/ORCL19AUX/ED7F830814134AADE053A024100A5D08/datafile/o1_mf_users_kzgw69h9_.dbf'
CHARACTER SET AL32UTF8
;
Finally, I create a new controlfile, open the auxiliary database and unplug the PDB keeping their datafiles.
SQL> CREATE CONTROLFILE REUSE set DATABASE "AUX" RESETLOGS ARCHIVELOG
[...]
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB1 MOUNTED
4 ORCL19PDB3 MOUNTED
5 ORCL19PDB2 MOUNTED
SQL> alter pluggable database PDB1 unplug into '/tmp/pdb1.xml';
Pluggable database altered.
SQL> drop pluggable database PDB1 keep datafiles;
Pluggable database dropped.
At this point we can transfer the restored PDB back to its original container. You can decide, if you want to use the restored datafiles right away or if you create another copy of them. Either way it is a simple one-liner.
SQL> create pluggable database PDB1 using '/tmp/pdb1.xml' copy;
Pluggable database created.
Or
SQL> create pluggable database PDB1 using '/tmp/pdb1.xml' nocopy tempfile reuse;
Pluggable database created.
Last step is to open the PDB again and check it’s contents.
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> alter pluggable database PDB1 save state;
Pluggable database altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
108
Et voila, missing accomplished. The very last step now is removing the auxiliary instance.
SQL> startup mount restrict exclusive force
SQL> drop database;
Whoever said, Multitenant will make DBA’s life easier did not do such a restore 🙂 … But to be fair, there are lot of scenarios where Multitenant is very handy, I must admit.
Marco's DBA Blog 