8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Usable Backups of Non-CDBs and Relocated PDBs in Oracle Database 18c
In previous releases, conversion of a non-CDB instance to a PDB meant losing access to all the previous backups. If a restore was necessary, it meant restoring the non-CDB instance, then re-converting to a PDB. Oracle 18c allows backups prior to the non-CDB to PDB conversion to be used as preplugin backups by making the backup metadata available to the destination CDB instance.
The situation is similar when performing an unplug/plugin of a PDB to a new location, but the database takes care of copying the metadata automatically, so all you need to do is make sure the backups are available to the destination CDB instance.
- Assumptions
- Create Backup
- Convert Non-CDB to PDB
- Check PrePlugin Backups
- Recover From PrePlugin Backup
- Appendix
Related articles.
- Multitenant : All Articles
- Multitenant : Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
Assumptions
We have two databases running in archivelog mode.
- db18c : A non-CDB instance. This is the source databases that will be converted to a PDB.
- cdb1 : Root container. This is the destination for the new PDB.
You can see how these instances were created in the appendix.
It is assumed the backups and archived redo logs will be visible from the destination CDB. This might mean copying them to the destination server, or having them on shared storage.
Create Backup
We need a backup to test with, so let's creation one.
export ORACLE_SID=db18c export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES rman target=/ <<EOF BACKUP DATABASE PLUS ARCHIVELOG; EXIT; EOF
We need to make sure the metadata for the existing backups is captured and made available to the destination CDB. This is done using the DBMS_PDB.EXPORTRMANBACKUP
procedure, which we run in the source database. You can read about what this procedure does here.
sqlplus / as sysdba <<EOF EXECUTE DBMS_PDB.exportrmanbackup(); exit; EOF
If this were an unplug and plugin of a PDB, we could so a similar thing, but include the name of the PDB we were about to unplug. This should not be necessary as the unplug already includes the metadata information.
sqlplus / as sysdba <<EOF EXECUTE DBMS_PDB.exportrmanbackup('MYPDB'); exit; EOF
Convert Non-CDB to PDB
With the backup metadata captured, we can now convert the the non-CDB instance to a PDB.
We open the non-CDB instance in read-only mode, describe it using the DBMS_PDB.DESCRIBE
procedure, then shut it down.
export ORACLE_SID=db18c export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF -- Open in read-only mode. SHUTDOWN IMMEDIATE; STARTUP OPEN READ ONLY; -- Describe the non-CDB instance. BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/tmp/db18c.xml'); END; / -- Shutdown the database. SHUTDOWN IMMEDIATE; exit; EOF
We switch to the CDB instance and create the new pluggable database using the non-CDB description file. This is similar to a plugin operation, but this time we are plugging in a non-CDB instance to create a PDB, so we have to run the "noncdb_to_pdb.sql" script. In this example we plugin using a COPY
, so the old non-CDB instance is left intact. You'll see why that is important later.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF CREATE PLUGGABLE DATABASE db18cpdb USING '/tmp/db18c.xml' COPY; ALTER SESSION SET CONTAINER=db18cpdb; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ALTER PLUGGABLE DATABASE OPEN; ALTER PLUGGABLE DATABASE SAVE STATE; exit; EOF
Check PrePlugin Backups
Connect to the new instance using RMAN.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES rman target=/
We can see the preplugin backups are available to the CDB instance.
RMAN> LIST PREPLUGIN BACKUP OF PLUGGABLE DATABASE db18cpdb; using target database control file instead of recovery catalog switched to preplugin container DB18CPDB List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 26 Full 1.12G DISK 00:00:03 23-APR-19 BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190423T095519 Piece Name: /u01/app/oracle/fast_recovery_area/DB18C/backupset/2019_04_23/o1_mf_nnndf_TAG20190423T095519_gcxr882k_.bkp List of Datafiles in backup set 26 Container ID: 4, PDB Name: DB18CPDB File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 163 Full 2838908 23-APR-19 NO /u02/oradata/CDB1/871ED24F4C0325E8E055000000000001/datafile/o1_mf_system_gcxrg5m3_.dbf 164 Full 2838908 23-APR-19 NO /u02/oradata/CDB1/871ED24F4C0325E8E055000000000001/datafile/o1_mf_sysaux_gcxrg5m8_.dbf 165 Full 2838908 23-APR-19 NO /u02/oradata/CDB1/871ED24F4C0325E8E055000000000001/datafile/o1_mf_undotbs1_gcxrg5m9_.dbf 166 Full 2838908 23-APR-19 NO /u02/oradata/CDB1/871ED24F4C0325E8E055000000000001/datafile/o1_mf_users_gcxrg5m9_.dbf RMAN>
Recover From PrePlugin Backup
We could do any of the typical restore and recover operations, but in this example we will restore the whole database from the preplugin backups.
Connect to the CDB instance using RMAN.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES rman target=/
Close the PDB.
ALTER PLUGGABLE DATABASE db18cpdb CLOSE IMMEDIATE;
Catalog any archived redo logs created by the non-CDB instance after the backups were taken.
SET PREPLUGIN CONTAINER=db18cpdb; CATALOG PREPLUGIN ARCHIVELOG '/u01/app/oracle/fast_recovery_area/DB18C/archivelog/2019_04_23/o1_mf_1_20_gcxr8656_.arc'; CATALOG PREPLUGIN ARCHIVELOG '/u01/app/oracle/fast_recovery_area/DB18C/archivelog/2019_04_23/o1_mf_1_21_gcxr8hco_.arc'; CATALOG PREPLUGIN ARCHIVELOG '/u01/app/oracle/fast_recovery_area/DB18C/archivelog/2019_04_23/o1_mf_1_22_gcxr8vk5_.arc';
Restore and recover the PDB from the preplugin backups.
RESTORE PLUGGABLE DATABASE db18cpdb FROM PREPLUGIN; RECOVER PLUGGABLE DATABASE db18cpdb FROM PREPLUGIN;
The recovery of the PDB always complains about an additional archivelog being needed. In this case with the following message.
unable to find archived log archived log thread=1 sequence=23 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 04/23/2019 10:07:14 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 23 and starting SCN of 2839529
Since this archived redo log doesn't exist, the only solution seems to be to open the non-CDB instance again, after the plugin operation, switch logfile, then catalog the new archived redo log. From there the recovery can proceed. I assume there is a better solution for this, but I don't know what it is.
export ORACLE_SID=db18c export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF STARTUP; ALTER SYSTEM SWITCH LOGFILE; SHUTDOWN IMMEDIATE; exit; EOF
With the new archived redo log created, we can switch back to the CDB instance, and use RMAN to catalog it and run the recovery again.
SET PREPLUGIN CONTAINER=db18cpdb; CATALOG PREPLUGIN ARCHIVELOG '/u01/app/oracle/fast_recovery_area/DB18C/archivelog/2019_04_23/o1_mf_1_23_gcxttm7v_.arc'; RECOVER PLUGGABLE DATABASE db18cpdb FROM PREPLUGIN;
This time it completes as expected.
The final step is to perform a normal recovery of the PDB and open it. We also include an additional restore step with the SKIP PREPLUGIN
clause. This is to restore any datafiles that were added since the PDB was created. It's not necessary here, but does no harm including it as a reminder.
RESTORE PLUGGABLE DATABASE db18cpdb SKIP PREPLUGIN; RECOVER PLUGGABLE DATABASE db18cpdb; ALTER PLUGGABLE DATABASE db18cpdb OPEN;
The PDB is now fully recovered from the preplugin backup.
Appendix
The examples in this article use the following instances.
mkdir -p /u01/app/oracle/fast_recovery_area # Container (cdb1) with PDB (pdb1). dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword SysPassword1 \ -systemPassword SysPassword1 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbName pdb1 \ -pdbAdminPassword SysPassword1 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/u01/oradata/" \ -redoLogFileSize 50 \ -emConfiguration NONE \ -ignorePreReqs export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES # Set required parameters. sqlplus / as sysdba <<EOF alter pluggable database pdb1 save state; alter system set db_create_file_dest = '/u02/oradata'; alter system set local_listener='LISTENER'; alter system set db_recovery_file_dest_size=20G; alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; shutdown immediate; startup mount; alter database archivelog; alter database open; exit; EOF # Non-CDB instance (db18c). dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname db18c -sid db18c -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword SysPassword1 \ -systemPassword SysPassword1 \ -createAsContainerDatabase false \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/u02/oradata/" \ -redoLogFileSize 50 \ -initParams encrypt_new_tablespaces=DDL \ -emConfiguration NONE \ -ignorePreReqs export ORACLE_SID=db18c export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES # Set required parameters. sqlplus / as sysdba <<EOF alter system set db_create_file_dest = '/u02/oradata'; alter system set local_listener='LISTENER'; alter system set db_recovery_file_dest_size=20G; alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; shutdown immediate; startup mount; alter database archivelog; alter database open; exit; EOF # Delete the instances. #dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1 #dbca -silent -deleteDatabase -sourceDB db18c -sysDBAUserName sys -sysDBAPassword SysPassword1
For more information see:
- Performing Complete Recovery Using Preplugin Backups
- About Preplugin Backups
- Multitenant : All Articles
- Multitenant : Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...