8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

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.

Related articles.

Assumptions

We have two databases running in archivelog mode.

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.

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 \
 -automaticMemoryManagement false \
 -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 \
  -automaticMemoryManagement false \
  -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:

Hope this helps. Regards Tim...

Back to the Top.