Create PDB from Standby

To clone a PDB from one CDB to another requires that the source PDB be read-only. This may be unacceptable to the business, especially if the source PDB contains multiple schemas and you only want to extract a few of them to form a new PDB in the target CDB (so the other schemas in the source PDB should not be changed to read-only at any time). Other than using data pump to move the data, you may create a standby for the source CDB and put the standby in read-only. If you have licenses for active data guard, your standby's can be read-only and also have redo applied to it. But it requires special attention to not corrupt the target standby.

The procedure to create a PDB from a standby is as follows. All sqlplus commands are run as sys. Both our source and target databases have standby's, which are in read-only apply mode.

1. On Source (example cp16):
grant create pluggable database to system; <-- User system will be the receiving end of the DB link; user sys won't work

2. On Target (example cp17):
create database link source connect to system identified by "xxxxxx" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cp16sbhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cp16sb)))'; <-- note "connect to system"; "connect to sys" would not work; also note the host is where Source standby is
select * from dual@source;

3. On Target standby (example cp17sb):
alter system set standby_pdb_source_file_dblink='source';
select * from dual@source;

4. On Source standby (example cp16sb):
alter database recover managed standby database cancel; <-- assume this is active data guard

5. On Target (cp17), preferably done in tmux to prevent network loss:
create pluggable database targetpdb from sourcepdb@source; <-- you can of course not rename the PDB
This step takes 10 min for this 920 GB PDB database (sourcepdb) in our network.
After this is done, do not start recovery of the Source standby (cp16sb)!

6. On Target (cp17):

alter pluggable database targetpdb close immediate instances=all;
alter pluggable database targetpdb open instances=all;
alter pluggable database targetpdb save state instances=all;

If needed, create a service for targetpdb, especially if this is a RAC database. (Otherwise clients will use the internal service.)

If you only need certain schemas in targetpdb, clean up the new PDB (targetpdb) by dropping all schemas/tablespaces unrelated to the schemas/tablespaces you want.

7. On Target standby (cp17sb):

By this time, all datafiles probably have been created on the Target standby, then
alter pluggable database targetpdb open instances=all;
If it fails with "ORA-65104: operation not allowed on an inactive pluggable database", wait till all datafiles are created and retry. Check alert.log.

Add tempfiles to the PDB in Target standby (cp17sb) as they're not automatically created:
alter session set container=targetpdb;
select file#, creation_time, ts#, bytes, create_bytes from v$tempfile;
alter tablespace temp add tempfile '+DATA' size 1g autoextend on next 1g; <-- if there're other temp tablespaces, add their tempfiles, too
select file#, creation_time, ts#, bytes, create_bytes from v$tempfile;

8. On Source standby (cp16sb):
alter database recover managed standby database disconnect;
You *must* wait till all datafiles are created on the Target standby (cp17sb) to do this step, i.e. Step 7 being able to open the new PDB on the Target standby.
If you start recovery on the Source standby before the Target standby successfully opens the new PDB, you'll get error like

Errors in file /u01/app/oracle/diag/rdbms/cp17sb/cp171/trace/cp171_pr00_337731.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CP17/4632E39EA1F1AB6BE0635AE7460A27CD/DATAFILE/tm_indx_l.499.1220133917'
ORA-01565: error in identifying file '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form

in Target standby alert.log, because datafiles are created on the Target standby in an asynchronous manner, and some datafiles (like in the above error message) are still being streamed from the Source standby and you should not modify that source by applying redo to it in the middle of streaming. Otherwise, the only way to fix this ORA-01274 error, as far as we know, is to rebuild the entire Target standby; it's not possible to just fix the new PDB in the Target standby.