Hi Steve.
There is no way other than point-in-time duplicate to an auxiliary CDB and
unplug to put wherever you want. You can skip pluggable databases so that
you restore only CDB$ROOT and your PDB. This process is automated by RMAN
when restoring in-place (a temporary auxiliary database is created for
CDB$ROOT) but not for your case.
Here is a script I used to demo it once on a lab:
---## Create auxiliary instance
---# create an init.ora
cat > /tmp/CDB_TEMP.ora <<'CAT'
db_name=CDB_TEMP
# I set a temporary domain to avoid any conflict
db_domain=temp.dbi-services.com
enable_pluggable_database=true
compatible=19.0.0.0
db_block_size=8192
db_files=200
sga_target=1024M
processes=150
db_create_file_dest=/u02/oradata
db_recovery_file_dest=/u90/fast_recovery_area
db_recovery_file_dest_size=1G
#_clone_one_pdb_recovery=true # this is used by automated in-place
PDBPITR so it may be cool ;)
#_system_trig_enabled=false
CAT
---# Start the instance
ORACLE_SID=CDB_TEMP sqlplus / as sysdba <<<"startup force nomount
pfile='/tmp/CDB_TEMP.ora';"
---## RMAN duplicate
---# RMAN connect to target and auxiliary
ORACLE_SID=CDB_TEMP rman
set echo on
connect target sys/manager@//localhost:1521/CDB1.it.dbi-services.com
alter system archive log current
/
connect auxiliary /
---# list PDBs to exclude (remove the one you want to keep!)
select listagg(pdb_name,',')within group(order by pdb_name) from dba_pdbs
/
---# duplicate skip PDBs(use no-open just to show funny name)
duplicate database CDB1_SITE1 to CDB_TEMP skip pluggable database
CDB1PDB02,CDB1PDB03 until restore point 'DEMO_OOP_PDB_PITR' noopen;
quit;
---# Open it (because we did a noopen duplicate)
ORACLE_SID=CDB_TEMP sqlcl / as sysdba
show pdbs
alter database open resetlogs
/
show pdbs
Cleanup:
---# remove the temporary CDB
ORACLE_SID=CDB_TEMP rman target /
startup dba mount force;
drop database noprompt;
quit;
rm -rf /u??/?*/CDB_TEMP $ORACLE_BASE/diag/rdbms/cdb_temp
$ORACLE_HOME/rdbms/log/cdb_temp* $ORACLE_HOME/rdbms/audit/CDB_TEMP*
$ORACLE_HOME/dbs/?*CDB_TEMP* $ORACLE_BASE/admin/CDB_TEMP
I doubt there will ever be another solution because you need a CDB$ROOT at
same point-in-time in order to open the PDB and unplug it.
Note that, as an alternative, if you have a standby you may stop apply,
flashback the pdb, convert to snapshot standby, clone the pdb, and get all
back to physical standby.
Franck.
On Wed, Jun 3, 2020 at 6:16 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
Well, you should create an empty CDB and just clone the PDB over the
network. Tim Hall is helpful, as usual:
https://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1#cloning-remote-pdb
There is also Oracle documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/cloning-a-pdb.html#GUID-05702CEB-A43C-452C-8081-4CA68DDA8007
Regards
On 6/1/20 2:19 PM, Steve Wales (AddOns) wrote:
Scott,
Thanks for the reply but that’s to recover a PDB to a point in time
(within the same database).
I am wanting to duplicate prod (server 1) to test (server 2) - and only
restore one of the multiple PDB’s in there.
Steve
Sent from my iPad
On Jun 1, 2020, at 12:11 PM, Scott Canaan <srcdco@xxxxxxx>
<srcdco@xxxxxxx> wrote:
Check out this page for examples of PITR of a single pdb:
https://oracle-base.com/articles/12c/multitenant-rman-backup-recovery-cdb-and-pdb-12cr1#pdb-pitr-recovery
*Scott Canaan ‘88*
*Sr Database Administrator *Information & Technology Services
Finance & Administration
*Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
*srcdco@xxxxxxx <srcdco@xxxxxxx>* | c: (585) 339-8659
*CONFIDENTIALITY NOTE*: The information transmitted, including
attachments, is intended only for the person(s) or entity to which it is
addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any
action in reliance upon this information by persons or entities other than
the intended recipient is prohibited. If you received this in error, please
contact the sender and destroy any copies of this information.
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx> *On Behalf Of *Steve Wales (AddOns)
*Sent:* Monday, June 1, 2020 1:58 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Duplicating a PDB to an existing CDB at a point in time in the
recent past
<image001.gif>
Let's say I have a CDB with 3 PDB's.
Something happens yesterday, a bug, an erroneous update, whatever and I
need to restore just ONE of my PDB's to yesterday at noon to test or dev
for troubleshooting.
I am not sure how I restore just the ONE PDB to yesterday at 11:59 so I
can see the data as it existed at that time.
I am aware of several different means of transferring data from Prod to
Test but none of them go back 24 hours (that I’m aware of).
1) Export Prod / Import to Test (good for current data, not for yesterday)
2) Duplicate the PDB over a database link (this is current data, not
yesterday)
3) Restore the whole CDB to another database, unplug the PDB you want,
plug it into test (this blows my mind, that means I need to provision
double the disk space just for this process).
So, short of doubling disk allocation to the server to perform #3, is
there any other way to duplicate a PDB from Prod to test to a point in time
in the past.
I have been reading all I can find in the Documentation and notes at
Oracle Support but I’m not finding any ideas here with a clear example.
I have raised an SR with Support, but the engineer I’m working with isn’t
giving me a lot of information and what I am getting back are short one
sentence answers.
I have seen some pages on assorted sites like:
https://oracle-base.com/articles/12c/recovery-manager-rman-database-duplication-enhancements-12cr1
https://oracle-base.com/articles/18c/multitenant-duplicate-a-pdb-to-an-existing-cdb-18c
Oracle Documentation on this is here:
https://docs.oracle.com/en/database/oracle/oracle-database/18/bradv/rman-duplicating-databases.html#GUID-23205E75-CFA8-4BCE-AC50-3D39DF040512
Maybe I’m misunderstanding what “FROM ACTIVE DATABASE” is meaning here.
Can you combine UNTIL TIME or UNTIL SCN with FROM ACTIVE DATABASE to make
it go read the RMAN catalog info from Prod and do the appropriate things to
the new PDB in test ?
My ability to play with this is a little limited. The database I’m
copying is 600 or 700 GB so anything I try isn’t exactly a quick test.
Would appreciate insights from anyone running in this kind of
configuration, the whole PDB thing is relatively new to me, we’re just
starting to look at converting from a non-CDB architecture into the
multi-tenant world.
If it makes any difference, this is hosted in Oracle Cloud (but I don’t
have access to the cloud console).
Thanks
Steve
*Disclaimer*
The information contained in this communication from the sender is
confidential. It is intended solely for use by the recipient and others
authorized to receive it. If you are not the recipient, you are hereby
notified that any disclosure, copying, distribution or taking action in
relation of the contents of this information is strictly prohibited and may
be unlawful.
This email has been scanned for viruses and malware, and may have been
automatically archived.
*Disclaimer*
The information contained in this communication from the sender is
confidential. It is intended solely for use by the recipient and others
authorized to receive it. If you are not the recipient, you are hereby
notified that any disclosure, copying, distribution or taking action in
relation of the contents of this information is strictly prohibited and
may be unlawful.
This email has been scanned for viruses and malware, and may have been
automatically archived.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
