In this post I will go over the steps to take to duplicate a pluggable database to a point in time using RMAN. This can be useful to restore certain data from the past while the PDB is running. The global steps to take are:

1. Create an auxiliary instance
2. Restore backups from the source into the auxiliary instance
3. Coffee

At this moment it is (as far as I’m concerned) not possible to restore a pluggable database point in time in the same CDB with a different name. The steps described in the article are needed to be taken to accomplish this goal. Make sure to have the RMAN backup files ready from your source database. In my case I will do the restore on the same system as the source CDB, so I can directly use its backup files.

Create the audit destination (all about auditing here):

$ mkdir -p /u01/app/oracle/admin/auxdb/audit

Create a pfile:

$ touch $ORACLE_HOME/rdbms/admin/AUXDB.ora

Then add the following content to the pfile:

*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/auxdb/audit'
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='+DATA/AUXDB/initAUXDB.dbf' 
*.db_block_checking='false'
*.db_block_checksum='typical'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_create_online_log_dest_1='+DATA1' 
*.db_domain=''
*.db_files=1024
*.db_lost_write_protect='typical'
*.db_name='auxdb'
*.db_recovery_file_dest='+RECO1'
*.db_recovery_file_dest_size=2048g
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=FALSE
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_buffer=134217728
*.open_cursors=300
*.os_authent_prefix=''
*.parallel_adaptive_multi_user=FALSE
*.parallel_execution_message_size=16384
*.parallel_max_servers=1280
*.parallel_min_servers=0
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=2048m
*.processes=300
*.recyclebin='on'
*.remote_login_passwordfile='exclusive'
*.sga_target=6144m
*.sql92_security=TRUE
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='ONLY'

Then set your environment to start the instance

$ . oraenv
ORACLE_SID = AUXDB
ORACLE_HOME=<your oracle_home>

$ sqlplus / as sysdba

SQL> startup nomount pfile='<your oracle_home>/rdbms/admin/AUXDB.ora';

SQL> exit;

Your instance will startup. When its started, startup RMAN to the instance as an auxiliary.

rman auxiliary /

Then edit the following run block to match your situation.

RMAN> run {
set UNTIL TIME  "to_date('Aug 28 2023 08:17:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel dupchan1 type disk;
allocate auxiliary channel dupchan2 type disk;
allocate auxiliary channel dupchan3 type disk;
allocate auxiliary channel dupchan4 type disk;
duplicate database to AUXDB pluggable database <pdbname>, root backup location '/mnt/backups/<location to your source CDB RMAN files>';
}

Once executed, RMAN does its magic and restores the CDB with the selected PDB into the auxiliary instance. This can take a while. When its done you can sqlplus into the database.

Now you have multiple choices. You could either export your data or do whatever it is you want to do. Or you can unplug this PDB. The first option you have to do on your own. The second option would look like this:

SQL> alter pluggable database <pdbname> close;
SQL> alter pluggable database <pdbname> unplug into '/tmp/<pdbname>.xml';

You could then use this xml file to load the PDB. You can either choose to COPY the files and use them in your own CDB file structure or use the files in the file structure of the auxiliary instance.

Copying files:

SQL> create pluggable database <new name> using '</tmp/<pdbname.xml>' tempfile reuse copy;

Not copying files

SQL> create pluggable database <new name> using '</tmp/<pdbname>' tempfile reuse nocopy;

Phew, that was all to create a duplicate of a pluggable database to a point in time. RMAN did it again, saved the day. If you have any questions I’m available for those. Reach me through the contact section of the website.

For now, welcome the PDB back from the future and spend some time together.