Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to do a Remote Hot Restore / Duplicate of PDB setting a UNTIL TIME timestamp

User_AJ5PPDec 22 2022

Question with a case stury example
CDBSOURCE containing one PDB (pdbprod)
CDBDEST containing 2 PDB (pdbtesta, pdbtestb)
I would like to find a way to duplicate/restore/clone the pdbprod@CDBSOURCE in CDBDEST but with an older timestamp than the current one.
So if today we are the 21-DEC-22 11.00am, I would like to have to copy pdbprod in the CDBDEST container set UNTIL TIME 21-DEC-22 08:00am.

With a non-cdb, I was using the following command with RMAN.

   set until time "to_date('${TIMESTAMP}','${NLS_DATE_FORMAT}')"
   duplicate database SOURCEDB to TESTDB backup location '${BKPDIR}';

I would like to do the same for a PDB. MY Big problem: I would like to clone the source PDB (pdbprod) in a container (CDBDEST), which has already two other pdbs (pdbtesta, pdbtestb) that have to stay available. The source PDB (pdbprod) also has to stay available.
I know these 3 ways but any of them correspond to what i want to do.

    1) SQL> create pluggable database pdbprod_cpy FROM pdbprod@cdbsource_link
    2) RMAN> duplicate pluggable database pdbprod as pdbprod_copy to CBDDEST from     active database
    3) RMAN> duplicate database to cdbdest pluggable database pdbprod;
       --for this solution I need a new & empty CDB

 I repeat that CDBDEST and its pdbs has to stay available. The CDBDEST is not empty !
Is it possible to do it?
Thank you for your help.
Kind regards
C.R.

Comments

Julian Frey-CITE

Hi
I think the only way currently is via an auxiliary CDB.
You restore your CDBSOURCE to the testserver with point in time restore
Unplug the PDBPROD
Plug the PDBPROD as pdbprod_copy (with nocopy)
You drop the CDBAUX
There is currently no way of PITR of a PDB.

Marius Catalin

Hi Julian,

You can perform a Remote Hot Restore / Duplicate of a PDB to another CDB while using an UNTIL TIME timestamp with RMAN, but it requires a Point-in-Time Recovery (PITR) approach.

Since you want to clone PDBPROD from CDBSOURCE to CDBDEST but recover it to an earlier timestamp (21-DEC-22 08:00 AM) while keeping existing PDBs (pdbtesta, pdbtestb) intact, you will need TSPITR (Tablespace Point-in-Time Recovery) for the PDB.

Solution Approach

  1. Take full RMAN backup of PDBPROD from CDBSOURCE.
  2. Restore the PDB into CDBDEST with UNTIL TIME recovery.
  3. Open the PDB in CDBDEST.

1. Run this on CDBSOURCE to create a backup:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Now, identify the SCN or timestamp corresponding to 21-DEC-22 08:00 AM:

SELECT SCN, TIME FROM V$ARCHIVED_LOG WHERE TIME < TO_DATE('21-DEC-22 08:00:00', 'DD-MON-YY HH24:MI:SS') ORDER BY TIME DESC;

Use the highest SCN or timestamp found.

2. Restore and Recover PDBPROD to CDBDEST

On CDBDEST, perform the following:

a) Restore PDB from RMAN Backup

RMAN> CONNECT TARGET sys@CDBDEST; RMAN> CONNECT AUXILIARY sys@CDBSOURCE; RMAN> RUN { SET UNTIL TIME "TO_DATE('21-DEC-22 08:00:00','DD-MON-YY HH24:MI:SS')"; DUPLICATE PLUGGABLE DATABASE pdbprod TO pdbprod_copy FROM ACTIVE DATABASE USING BACKUPSET SPFILE NOFILENAMECHECK; }

This will:

  • Clone pdbprod from CDBSOURCE to CDBDEST.
  • Restore it to the UNTIL TIME you specified.
  • Keep existing PDBs (pdbtesta, pdbtestb) intact.

3. Open the Cloned PDB in CDBDEST

ALTER PLUGGABLE DATABASE pdbprod_copy OPEN RESETLOGS;

Alternative: Using RMAN with Backup Instead of Active Database

If you prefer to restore from backup files instead of an active connection, do this:

1. Backup PDBPROD on CDBSOURCE:

RMAN> BACKUP PLUGGABLE DATABASE pdbprod FORMAT '/backup_path/%U' PLUS ARCHIVELOG;

2. Transfer Backup to CDBDEST:

2a. Run this on CDBSOURCE to identify backup files:

RMAN> LIST BACKUP OF PLUGGABLE DATABASE pdbprod;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup_path/%U';

Your backup files will be in /backup_path/.

Copy Backup Files to CDBDEST

On CDBSOURCE, run:

scp /backup_path/* oracle@CDBDEST:/backup_dest/

This copies the backup files to CDBDEST.

Note**:** Ensure SSH access is enabled between servers. If prompted for a password, enter it. You can also set up passwordless SSH for automation.

2b. If both databases are connected via a database link, you can use RMAN cross-transport.

Create DB Link on CDBDEST:

CREATE DATABASE LINK cdbsource_link CONNECT TO rman_user IDENTIFIED BY 'password' USING 'CDBSOURCE_TNS';

Copy Backup Using RMAN:

RMAN> CONNECT TARGET sys@CDBDEST; RMAN> CONNECT AUXILIARY sys@CDBSOURCE; RMAN> CATALOG START WITH '/backup_dest/';

Next Step: Catalog and Restore on CDBDEST

Once the files are transferred, catalog them on CDBDEST:

RMAN> CATALOG START WITH '/backup_dest/';

Then, proceed with the restore and recovery.

3. Restore PDB to CDBDEST:

RMAN> CATALOG START WITH '/backup_path/'; RMAN> RUN { SET UNTIL TIME "TO_DATE('21-DEC-22 08:00:00','DD-MON-YY HH24:MI:SS')"; RESTORE PLUGGABLE DATABASE pdbprod; RECOVER PLUGGABLE DATABASE pdbprod; }

4. Rename and Open the PDB in CDBDEST:

ALTER PLUGGABLE DATABASE pdbprod_copy OPEN RESETLOGS;

Final Notes

  • This method preserves existing PDBs (pdbtesta, pdbtestb).
  • Uses UNTIL TIME for Point-in-Time Recovery of PDBPROD.
  • Works without needing an empty CDB.
  • You can automate this via RMAN scripts.
1 - 2

Post Details

Added on Dec 22 2022
#database, #database-key-features, #multitenant, #rman
2 comments
932 views