Re: (EXT) RE: Duplicating a PDB to an existing CDB at a point in time in the recent past

From: Franck Pachot <franck_at_pachot.net>
Date: Wed, 3 Jun 2020 07:07:06 +0200
Message-ID: <CAK6ito3dzqOsq_BfrWSAd4Q0RUCYo4Ki0H-Vn1FEVSYm0y29HQ_at_mail.gmail.com>

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_at_//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_at_gmail.com> 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_at_rit.edu>
> <srcdco_at_rit.edu> 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_at_rit.edu <srcdco_at_rit.edu>* | 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_at_freelists.org <oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org> *On Behalf Of *Steve Wales (AddOns)
> *Sent:* Monday, June 1, 2020 1:58 PM
> *To:* oracle-l_at_freelists.org
> *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
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 03 2020 - 07:07:06 CEST