Restoring a PDB Backup into a New CDB

By -
According to Oracle Backup and Recovery documentation, restoring a Pluggable Database (PDB) normally requires access to the backup of the Container Database (CDB) that originally hosted the PDB. Without the source CDB metadata, restoring a standalone PDB backup is generally not possible. However, there is an alternative approach that can sometimes be used to recover a PDB backup and plug it into a different CDB. Although this method is not guaranteed to work in every scenario, it is a useful technique to be aware of when designing backup and recovery strategies. In this example, we will back up a PDB named RMANPDB, transfer the backup to another server, and restore it into a completely new CDB. Backing Up the PDB First, verify that the PDB exists and is open: Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 5 PDB1 READ WRITE NO 4 RMANPDB READ WRITE NO Create a RMAN backup of the PDB and include the current control file: RMAN> backup pluggable database RmanPDB format '/oracle21c/bkp_pdb/%U' current controlfile format '/oracle21c/bkp_pdb/control0000.bkp'; Starting backup at 16-OCT-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=266 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00068 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo62lhz_.dbf input datafile file number=00067 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo62lhv_.dbf input datafile file number=00069 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo62lj1_.dbf input datafile file number=00070 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo66vjx_.dbf channel ORA_DISK_1: starting piece 1 at 16-OCT-21 channel ORA_DISK_1: finished piece 1 at 16-OCT-21 piece handle=/oracle21c/bkp_pdb/4p0bo4mu_153_1_1 tag=TAG20211016T052342 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 16-OCT-21 channel ORA_DISK_1: finished piece 1 at 16-OCT-21 piece handle=/oracle21c/bkp_pdb/control0000.bkp tag=TAG20211016T052342 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 16-OCT-21 Transferring the Backup Copy the backup pieces to the target server: [oracle@RAC2 ~]$ mkdir -p /oracle21c/bkp_pdb [oracle@oLinux7 ~]$ scp /oracle21c/bkp_pdb/* 192.168.1.20:/oracle21c/bkp_pdb/ 4p0bo4mu_153_1_1 100% 518MB 68.8MB/s 00:07 control0000.bkp 100% 30MB 81.1MB/s 00:00 Creating a Minimal Instance On the destination server, create a minimal initialization parameter file: [oracle@RAC2 ~]$ vi p_1400_07_24.ora *.db_name=db21c *.db_create_file_dest='/oracle21c/recoverdb' Start the instance in NOMOUNT mode: SQL> startup nomount pfile='/home/oracle/p_1400_07_24.ora'; Restoring the Control File Restore the control file from the backup: RMAN> restore controlfile from '/oracle21c/bkp_pdb/control0000.bkp'; Starting restore at 16-OCT-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=4262 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oracle21c/recoverdb/DB21C/controlfile/o1_mf_jpo703jv_.ctl Finished restore at 16-OCT-21 Mount the database: RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed At this point, the metadata required to locate the PDB backup is available. Restoring the PDB Restore the PDB datafiles: RMAN> RESTORE PLUGGABLE DATABASE RMANPDB; Starting restore at 16-OCT-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=571 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00067 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo62lhv_.dbf channel ORA_DISK_1: restoring datafile 00068 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo62lhz_.dbf channel ORA_DISK_1: restoring datafile 00069 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo62lj1_.dbf channel ORA_DISK_1: restoring datafile 00070 to /oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo66vjx_.dbf channel ORA_DISK_1: reading from backup piece /oracle21c/bkp_pdb/4p0bo4mu_153_1_1 channel ORA_DISK_1: piece handle=/oracle21c/bkp_pdb/4p0bo4mu_153_1_1 tag=TAG20211016T052342 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 16-OCT-21 RMAN restores all datafiles belonging to the PDB. Become a Medium member It is important to understand that only the datafiles have been restored. The PDB itself is not yet usable because it is not associated with an operational CDB. Identifying the Restored Datafiles Determine the location of the restored datafiles: SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 4 RMANPDB MOUNTED 5 PDB1 MOUNTED SQL> select name from v$datafile where con_id=4; /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo72qsh_.dbf /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo72ppx_.dbf /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo72s8q_.dbf /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo72ssg_.dbf Generating the XML To plug the restored PDB into another CDB, an XML file must be created. Connect to an open CDB and use the DBMS_PDB.RECOVER procedure: [oracle@RAC2 ~]$ srvctl start database -db db21c [oracle@RAC2 ~]$ sqlplus "/as sysdba" SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 16 13:23:36 2021 Version 21.3.0.0.0 BEGIN DBMS_PDB.RECOVER ( pdb_descr_file => '/home/oracle/RMANPDB.xml', pdb_name => 'RMANPDB', filenames => ' /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo72qsh_.dbf, /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo72ppx_.dbf, /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo72s8q_.dbf, /oracle21c/recoverdb/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo72ssg_.dbf ' ); END; / PL/SQL procedure successfully completed. The procedure creates an XML manifest describing the PDB. A portion of the generated file looks like this: [oracle@RAC2 ~]$ less /home/oracle/RMANPDB.xml 1 RMANPDB 4 0 0 0.0.0.0.0 0.0.0.0.0 21.0.0.0.0 0.0.0.0.0 0 0.0.0.0.0 3948388369 0 246486353 CE75C2880CCD2A3FE0530488200A29C8 Plugging the PDB into a New CDB Create the PDB using the XML : SQL> CREATE PLUGGABLE DATABASE RMANPDB USING '/home/oracle/RMANPDB.xml' NOCOPY; Pluggable database created. Verify that the PDB has been created: SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 RMANPDB MOUNTED Opening the PDB The PDB may initially open in migration mode and generate warnings. SQL> ALTER DATABASE UPGRADE SYNC OFF; Database altered. SQL> alter pluggable database RMANPDB open ; Warning: PDB altered with errors. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 RMANPDB READ WRITE YES Validating the Recovery Connect to the restored PDB and verify that application objects are accessible: [oracle@RAC2 ~]$ sqlplus "usef/a@192.168.1.20:1521/RMANPDB" Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> select count(*) from usef.tbl1; COUNT(*) ---------- 91 SQL> create table usef.tbl2 as select * from tbl1; Table created.

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 21c — Setting Compression Attribute for Indexes During Import

By -
  In Oracle 12cR1, a new feature was introduced that allowed us to enable or disable the   compression   property for tables during a dump import. This was done using the   TRANSFORM   parameter in the   impdp   command: SQL * Plus: Release 12.2 .0 .1 .0 Production SQL > select compression from dba_tables where table_name = 'TBL1' ; COMPRESS -------- DISABLED SQL > drop table usef.tbl1; Table dropped. [oracle @oshost ~ ]$ impdp usef / a directory = drm dumpfile = test.dmp tables = usef.tbl1 TRANSFORM = TABLE_COMPRESSION_CLAUSE:\"ROW STORE COMPRESS ADVANCED\" SQL > select compression,COMPRESS_FOR from dba_tables where table_name = 'TBL1' ; COMPRESS COMPRESS_FOR -------- ------------------------------ ENABLED ADVANCED This feature was only available at the  table level , and there was no way to control index compression during import (in Oracle 12c). However, in  Oracle 21c , as a new feature, you can now enable (...
Read more

Automatic Service Failback to Preferred Instance in Oracle RAC 19c

By -
  As you know, in a RAC environment when creating a service, you can define   preferred instances   and   available instances . In this case, the service will by default run on the preferred instance. If for any reason the preferred instance fails, or the entire node becomes unavailable, the service will be moved to the available instance. Even after restarting the preferred instance, the service will not automatically fail back; it will continue running on the available instance. To move it back to the preferred instance, you need to manually perform a  relocate  operation. Example: In this example, we create a service named  mysrv  in Oracle 11g: [ grid@node1 ~ ]$ crsctl query crs releaseversion Oracle High Availability Services release version on the local node is [ 11.2 .0 .4 .0 ] [ oracle@node1 ~ ]$ srvctl add service -d mydb -s mysrv -r instance1 -a instance2 After creating the service, start it. You will see that the service is runnin...
Read more