Cloning a single PDB isn't always easy to do, especially if you are trying to use an existing backup rather copying from an existing database. In this blog post I will walk through how to restore a PDB from an existing Multi-tenant backup to another host, and plug it into another CDB.
My environment is:
DBCS database FASTDB
db_name = fastdb
db_unique_name = fastdb_67s_iad
DB Version = 19.19
TDE = Using local wallet
Backup = Object Storage using the Tooling
RMAN catalog = Using RMAN catalog to emulate ZDLRA
PDB name = fastdb_pdb1
Step #1 - Prepare destination
The first step is to copy over all the necessary pieces for restoring the database using the object store library.
- TDE wallet
- Tape Library
- Tape Library config file
- SEPS wallet used by backup connection
- SPFILE contents to build a pfile
NOTE: When using a ZDLRA as a source you need to copy over the following pieces.
- TDE wallet
- ZDLRA library (or use the library in the $ORACLE_HOME)
- SEPS wallet used by the channel allocation to connect to the ZDLRA
- SPFILE contents to build a pfile
Also create any directories needed (like audit file location).
- mkdir /u01/app/oracle/admin/fastdb_67s_iad/adump
I added the entry to the /etc/oratab file and changed my environment to point to this database name.
In my case I copied the following directories and subdirectories to the same destination on the host.
- scp /opt/oracle/dcs/commonstore/wallets/fastdb_67s_iad/*
- scp /opt/oracle/dcs/commonstore/oss/fastdb_67s_iad/*
Finally, I copied some of the contents in the spfile. Below are the critical entries.
audit_file_dest='/u01/app/oracle/admin/fastdb_67s_iad/adump'
*.compatible='19.0.0.0'
*.control_files='+RECO/FASTDB_67S_IAD/CONTROLFILE/current.256.1143303659'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_domain='subnet.vcn.oraclevcn.com'
*.db_files=1024
*.db_name='fastdb'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=8191g
*.db_unique_name='fastdb_67s_iad'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.global_names=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.processes=4000
*.sga_target=4g
*.tde_configuration='keystore_configuration=FILE'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.wallet_root='/opt/oracle/dcs/commonstore/wallets/fastdb_67s_iad'
Step #2 - Restore controlfile
The next step is to restore the controlfile to my destination host
I grabbed 2 pieces of information from the source database
- DBID - This is needed to restore the controlfile from the backup.
- Channel configuration.
With this I executed the following to restore the controlfile.
startup nomount;
set dbid=1292000107;
run
{
allocate CHANNEL sbt1 DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%I_%U_%T_%t' PARMS 'SBT_LIBRARY=/opt/oracle/dcs/commonstore/oss/fastdb_67s_iad/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/oss/fastdb_67s_iad/acefbba5-65ad-454c-b1fe-467dec1abde4/opc_fastdb_67s_iad.ora)';
restore controlfile ;
}
and below is my output.
RMAN> run
{
allocate CHANNEL sbt1 DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%I_%U_%T_%t' PARMS 'SBT_LIBRARY=/opt/oracle/dcs/commonstore/oss/fastdb_67s_iad/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/oss/fastdb_67s_iad/acefbba5-65ad-454c-b1fe-467dec1abde4/opc_fastdb_67s_iad.ora)';
restore controlfile ;
}2> 3> 4> 5>
allocated channel: sbt1
channel sbt1: SID=1513 device type=SBT_TAPE
channel sbt1: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 08-AUG-23
channel sbt1: starting datafile backup set restore
channel sbt1: restoring control file
channel sbt1: reading from backup piece c-1292000107-20230808-04
channel sbt1: piece handle=c-1292000107-20230808-04 tag=TAG20230808T122731
channel sbt1: restored backup piece 1
channel sbt1: restore complete, elapsed time: 00:00:01
output file name=+RECO/FASTDB_67S_IAD/CONTROLFILE/current.2393.1144350823
Finished restore at 08-AUG-23
Step #3 - Restore Datafiles for CDB and my PDB
Below is the commands I am going to execute to restore the datafiles for my CDB , my PDB and the PDB$SEED.
First I'm going to mount the database, and I am going to spool the output to a logfile.
alter database mount;
SPOOL LOG TO '/tmp/restore.log';
set echo on;
run {
restore database root ;
restore database FASTDB_PDB1;
restore database "PDB$SEED";
}I went through the output, and I can see that it only restored the CDB , my PDB, and the PDB$SEED.
Step #4 - execute report schema and review file locations
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1040 SYSTEM YES +DATA/FASTDB_67S_IAD/DATAFILE/system.283.1144351313
3 970 SYSAUX NO +DATA/FASTDB_67S_IAD/DATAFILE/sysaux.284.1144351305
4 95 UNDOTBS1 YES +DATA/FASTDB_67S_IAD/DATAFILE/undotbs1.280.1144351303
5 410 PDB$SEED:SYSTEM NO +DATA/FASTDB_67S_IAD/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/system.264.1143303695
6 390 PDB$SEED:SYSAUX NO +DATA/FASTDB_67S_IAD/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/sysaux.265.1143303695
7 50 PDB$SEED:UNDOTBS1 NO +DATA/FASTDB_67S_IAD/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/undotbs1.266.1143303695
8 410 FASTDB_PDB1:SYSTEM YES +DATA/FASTDB_67S_IAD/017B7B0563F0410FE063A100000A1C63/DATAFILE/system.291.1144351333
9 410 FASTDB_PDB1:SYSAUX NO +DATA/FASTDB_67S_IAD/017B7B0563F0410FE063A100000A1C63/DATAFILE/sysaux.292.1144351331
10 70 FASTDB_PDB1:UNDOTBS1 YES +DATA/FASTDB_67S_IAD/017B7B0563F0410FE063A100000A1C63/DATAFILE/undotbs1.281.1144351329
11 5 USERS NO +DATA/FASTDB_67S_IAD/DATAFILE/users.285.1144351303
12 5 FASTDB_PDB1:USERS NO +DATA/FASTDB_67S_IAD/017B7B0563F0410FE063A100000A1C63/DATAFILE/users.295.1144351329
13 420 RMANPDB:SYSTEM YES +DATA/FASTDB_67S_IAD/021D506D8C7ADC01E063A100000A8702/DATAFILE/system.285.1143999311
14 420 RMANPDB:SYSAUX NO +DATA/FASTDB_67S_IAD/021D506D8C7ADC01E063A100000A8702/DATAFILE/sysaux.282.1143999317
15 50 RMANPDB:UNDOTBS1 YES +DATA/FASTDB_67S_IAD/021D506D8C7ADC01E063A100000A8702/DATAFILE/undotbs1.281.1143999323
16 5 RMANPDB:USERS NO +DATA/FASTDB_67S_IAD/021D506D8C7ADC01E063A100000A8702/DATAFILE/users.284.1143999309
17 100 RMANPDB:RMANDATA NO +DATA/FASTDB_67S_IAD/021D506D8C7ADC01E063A100000A8702/DATAFILE/rmandata.280.1144001911
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/FASTDB_67S_IAD/TEMPFILE/temp.263.1143304005
2 131 PDB$SEED:TEMP 32767 +DATA/FASTDB_67S_IAD/017B5DDEB84167ACE063A100000AD816/TEMPFILE/temp.267.1143303733
4 224 FASTDB_PDB1:TEMP 4095 +DATA/FASTDB_67S_IAD/017B7B0563F0410FE063A100000A1C63/TEMPFILE/temp.272.1143304235
6 224 RMANPDB:TEMP 4095 +DATA/FASTDB_67S_IAD/021D506D8C7ADC01E063A100000A8702/TEMPFILE/temp.283.1143999305
Step #5 - Determine tablespaces to skip during recovery
I ran this on my primary database, and used it to build the RMAN command. This command will get the names of the tablespaces that are not part of this PDB so that I can ignore them.
select '''' ||pdb_name||''':'||tablespace_name ||','
from cdb_tablespaces a,
dba_pdbs b
where a.con_id=b.con_id(+)
and b.pdb_name not in ('FASTDB_PDB1')
order by 1;
From the above, I built the script below that skips the tablespaces for the PDB "RMANPDB".
recover database skip forever tablespace
'RMANPDB':RMANDATA,
'RMANPDB':SYSAUX,
'RMANPDB':SYSTEM,
'RMANPDB':TEMP,
'RMANPDB':UNDOTBS1,
'RMANPDB':USERS;
And then ran my RMAN script to recover my datafiles that were restored.
NOTE: the datafiles for my second PDB were "offline dropped"
Starting recover at 08-AUG-23
RMAN-06908: warning: operation will not run in parallel on the allocated channels
RMAN-06909: warning: parallelism require Enterprise Edition
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3771 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=4523 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/FASTDB_67S_IAD/DATAFILE/system.283.1144351313
...
Executing: alter database datafile 13, 14, 15, 16, 17 offline drop
starting media recovery
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_SBT_TAPE_1: reading from backup piece FASTDB_1292000107_5m23a29f_182_1_1_20230808_1144326447
channel ORA_SBT_TAPE_1: piece handle=FASTDB_1292000107_5m23a29f_182_1_1_20230808_1144326447 tag=TAG20230808T122727
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/FASTDB_67S_IAD/ARCHIVELOG/2023_08_08/thread_1_seq_26.2389.1144352807 thread=1 sequence=26
channel default: deleting archived log(s)
archived log file name=+RECO/FASTDB_67S_IAD/ARCHIVELOG/2023_08_08/thread_1_seq_26.2389.1144352807 RECID=1 STAMP=1144352806
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-AUG-23
Step #6 - Open database
I opened the database and the PDB
SQL> alter database open;
Database altered.
SQL> alter pluggable database fastdb_pdb1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FASTDB_PDB1 READ ONLY NO
4 RMANPDB MOUNTED
I also went and updated my init{sid}.ora to point to the controlfile that I restored.
Step #8 - Create shell PDB in the tooling
I created a new PDB that is going to be the name of the PDB I am going to plug in. This is optional.
Step #7 - Switch my restored database to be a primary database
I found that the database was considered a standby database, and I needed to make it a primary to unplug my pdb
SQL> RECOVER MANAGED STANDBY DATABASE FINISH;
Media recovery complete.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
Step #8 - unplug my PDB
I opened the database and unplugged my PDB.
SQL> alter database open;
Database altered.
SQL> alter pluggable database fastdb_pdb1 unplug into '/tmp/fastdb_pdb1.xml' ENCRYPT USING transport_secret;
Pluggable database altered.
SQL>
drop pluggable database fastdb_pdb1 keep datafiles;SQL>
Pluggable database dropped.
Step #9 - Drop the placeholder PDB from the new CDB
Now I am unplugging, and dropping the placeholder PDB.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 LAST21C_PDB1 READ WRITE NO
4 CLONED_FASTDB READ WRITE NO
SQL> alter pluggable database CLONED_FASTDB close;
Pluggable database altered.
SQL> alter pluggable database CLONED_FASTDB unplug into '/tmp/CLONED_FASTDB.xml' ENCRYPT USING transport_secret;
Pluggable database altered.
SQL> drop pluggable database CLONED_FASTDB keep datafiles;
Pluggable database dropped.
Step #10 - Plug in the PDB and open it up
create pluggable database CLONED_FASTDB USING '/tmp/fastdb_pdb1.xml' keystore identified by W3lCom3#123#123 decrypt using transport_secret
NOCOPY
TEMPFILE REUSE;
SQL> 2 3
Pluggable database created.
SQL> SQL>alter pluggable database cloned_fastdb open;
That's it. it took a bit to track down the instructions, but this all seemed to work.
Step #11 - Clone the PDB to ensure that the tooling worked
I next cloned the PDB to make sure the tooling properly recognized my PDB and it all worked fine. You can that I know have a second copy of the PDB (test_clone).
