Multitenant : Copying a PDB in a Data Guard Environment in Oracle Database 18c (2023)

Home » Articles » 18c » Here

The multitenant architecture brings some complications where Data Guard environments are concerned. New pluggable databases (PDBs) created from the seed, or as a clone of an existing read-only PDB in the local root container are handled automatically by Data Guard. New PDBs created by a remote clone or a plugin operation are not managed automatically. The STANDBY_PDB_SOURCE_FILE_DBLINK and STANDBY_PDB_SOURCE_FILE_DIRECTORY initialisation parameters make remote clones and plugin operations much simpler in Oracle 18c.

This feature is available from 12.1.0.2 when patched appropriately, as described in MOS Doc ID 2274735.1, but 18c is the first time it has been documented in the official documentation.

• Assumptions
• STANDBY_PDB_SOURCE_FILE_DBLINK : Remote Clone a PDB
• STANDBY_PDB_SOURCE_FILE_DIRECTORY : Plugin a PDB
• Appendix

Related articles.

• Data Guard Quick Links : 11gR2, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
• Multitenant : Controlling PDB Replication in Data Guard Environments
• Multitenant : All Articles

Assumptions

Before you start trying these features for yourself, it's important you understand a few things.

You understand that making mistakes on a standby database can leave you in a position where you have to rebuild the entire standby database. Don't attempt these commands on a system you care about without practising it somewhere first. If you don't have a convenient test bed, then build yourself one. The examples here use an environment built using VirtualBox and Vagrant, as described here.

In addition to the primary and standby database, the primary node has another CDB used as the source of a remote clone, so this is the starting point.

• cdb1 (cdb1) : Primary database with a single PDB called "pdb1".
• cdb1 (cdb1_stby) : Standby database protecting the PDB called "pdb1" from the primary database.
• cdb3 : Standalone instance used as source for a remote clone test of a PDB called "pdb5".

The additional "cdb3" instance can be created created using the commands in the appendix.

The output below shows the starting status of the pluggable databases in the primary and standby databases.

-- PrimarySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FD2A7B8B56772FFE0536538A8C0ECC3PDB1 READ WRITE ENABLED 7FD2C1FCD1227929E0536538A8C0E1F1SQL>-- StandbySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FD2A7B8B56772FFE0536538A8C0ECC3PDB1 MOUNTED ENABLED 7FD2C1FCD1227929E0536538A8C0E1F1SQL>

STANDBY_PDB_SOURCE_FILE_DBLINK : Remote Clone a PDB

The STANDBY_PDB_SOURCE_FILE_DBLINK parameter is set in the standby database, and identifies the database link used by primary database to perform a remote clone. When the standby database receives the redo associated with the PDB clone operation on the primary database, it uses the database link to perform the clone operation on the standby database. There are some constraints associated with this.

• The standby database must be in Active Data Guard (ADG) mode.
• The source PDB must be in read-only mode. This functionality doesn't work with hot cloning introduced in 12.2.

Let's see it in action.

Create the link user in the source container database "cdb3".

export ORACLE_SID=cdb3export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YES# Create link user.sqlplus / as sysdba <<EOFDROP USER c##remote_clone_user CASCADE;CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;EXIT;EOF

Create a database link on the primary database "cdb1" to the source database "cdb3" using the link user.

export ORACLE_SID=cdb1export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YES# Create database link.sqlplus / as sysdba <<EOFCREATE DATABASE LINK clone_link CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user USING 'cdb3';exit;EOF

On the standby, make sure it can connect to the source database "cdb3" also. You will need something similar to this entry in the "tnsnames.ora" file on each server.

cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOFCDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-183-dg1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb3) ) )EOF

Switch the standby database into Active Data Guard (ADG) mode.

# Standby - Active Data Guard (ADG).sqlplus / as sysdba <<EOFALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;ALTER DATABASE OPEN READ ONLY;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;EXIT;EOF

Test the database link, which should now have been replicated to the standby database. Assuming it works, reference it in the STANDBY_PDB_SOURCE_FILE_DBLINK parameter.

-- StandbySELECT * FROM dual@clone_link;ALTER SYSTEM SET STANDBY_PDB_SOURCE_FILE_DBLINK='clone_link';

Connect to the source database "cdb3" and make sure the "pdb5" pluggable database is opened in read-only mode.

export ORACLE_SID=cdb3export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YESsqlplus / as sysdba <<EOFALTER PLUGGABLE DATABASE pdb5 CLOSE;ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;EXIT;EOF

On the primary database "cdb1" create a new pluugable database called "pdb2" as a clone of the remote pluggable database "pdb5".

export ORACLE_SID=cdb1export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YES# Primarysqlplus / as sysdba <<EOFCREATE PLUGGABLE DATABASE pdb2 FROM pdb5@clone_link;EXIT;EOF

Wait for all files to copy on standby. You can check this by checking the alert log on the standby.

tail -f /u01/app/oracle/diag/rdbms/cdb1_stby/cdb1/trace/alert_cdb1.log

You will see the datafiles copied after the following type of message.

Recovery created pluggable database PDB2Recovery attempting to copy datafiles for pdb-PDB2 from source pdb-PDB5 at dblink-clone_link

Once the files are copied to the standby, you are safe to open the PDB on the primary database "cdb1". You should also reset the parameter on the standby database.

-- PrimaryALTER PLUGGABLE DATABASE pdb2 OPEN;-- StandbyALTER SYSTEM RESET standby_pdb_source_file_dblink;

At this point it's safe to turn the source pluggable database "pdb5" back to read-write mode.

export ORACLE_SID=cdb3export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YESsqlplus / as sysdba <<EOFALTER PLUGGABLE DATABASE pdb5 CLOSE;ALTER PLUGGABLE DATABASE pdb5 OPEN;EXIT;EOFexport ORACLE_SID=cdb1export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YES

Check the current status of the PDBs on the primary and standby databases.

-- PrimarySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FFCBA15929572D9E0536538A8C055AEPDB1 READ WRITE ENABLED 7FFCD0D366F578BDE0536538A8C01566PDB2 READ WRITE ENABLED 7FFD420F711E187AE0536538A8C0D422SQL>-- StandbySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FFCBA15929572D9E0536538A8C055AEPDB1 MOUNTED ENABLED 7FFCD0D366F578BDE0536538A8C01566PDB2 MOUNTED ENABLED 7FFD420F711E187AE0536538A8C0D422SQL>

Check the status of the Data Guard configuration.

dgmgrl sys/${SYS_PASSWORD}@${NODE2_DB_UNIQUE_NAME} <<EOFSHOW CONFIGURATION;EXIT;EOF

This gives us the following output.

DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Jan 21 18:54:14 2019Version 18.3.0.0.0Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1"Connected as SYSDG.DGMGRL>Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESS (status updated 36 seconds ago)DGMGRL>$

Let's clean up ready for the next section by dropping the "pdb2" pluggable database from the primary database.

-- PrimaryALTER PLUGGABLE DATABASE pdb2 CLOSE;DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;

Not surprisingly, we now see the "pdb2" pluggable database has been removed from the primary and standby database in the normal way.

-- PrimarySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FFCBA15929572D9E0536538A8C055AEPDB1 READ WRITE ENABLED 7FFCD0D366F578BDE0536538A8C01566SQL> -- StandbySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FFCBA15929572D9E0536538A8C055AEPDB1 MOUNTED ENABLED 7FFCD0D366F578BDE0536538A8C01566SQL>

We can also disable Active Data Guard (ADG).

# Standby - Active Data Guard (ADG).sqlplus / as sysdba <<EOFCONN / AS SYSDBASHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;EXIT;EOF

STANDBY_PDB_SOURCE_FILE_DIRECTORY : Plugin a PDB

The STANDBY_PDB_SOURCE_FILE_DIRECTORY parameter allows us to tell the standby database where to find the datafile copies to complete a plugin operation initiated by the primary database. These datafiles can't be part of a PDB archive.

Create a directory on the primary and secondary servers to hold some backup files.

mkdir -p /u01/files

We want to plugin a database, so let's create a PDB which we will unplug and use as our starting point.

-- PrimaryCONN / AS SYSDBACREATE PLUGGABLE DATABASE pdb5 ADMIN USER pdb_admin IDENTIFIED BY Password1;ALTER PLUGGABLE DATABASE pdb5 OPEN;ALTER PLUGGABLE DATABASE pdb5 CLOSE INSTANCES=ALL;ALTER PLUGGABLE DATABASE pdb5 UNPLUG INTO '/u01/files/pdb5.xml';

Now backup the files associated with the PDB.

# Primaryrman target=/ <<EOFBACKUP AS COPY PLUGGABLE DATABASE pdb5 FORMAT '/u01/files/%U';EXIT;EOF

Now it's safe to drop the pluggable database.

-- PrimaryCONN / AS SYSDBADROP PLUGGABLE DATABASE pdb5 INCLUDING DATAFILES;

Copy the datafile copies to the standby server and place them in a directory called "/u01/files".

Set the STANDBY_PDB_SOURCE_FILE_DIRECTORY on the standby to the location of the data file copies.

-- StandbyCONN / AS SYSDBAALTER SYSTEM SET standby_pdb_source_file_directory='/u01/files';

Create a new pluggable database "pdb3" based on the unplugged PDB in the primary database.

-- PrimaryCONN / AS SYSDBACREATE PLUGGABLE DATABASE pdb3 as clone USING '/u01/files/pdb5.xml' COPY SOURCE_FILE_DIRECTORY='/u01/files';

Wait for all files to copy on standby.

tail -f /u01/app/oracle/diag/rdbms/cdb1_stby/cdb1/trace/alert_cdb1.log

You will see the datafiles copied after the following type of message.

Recovery created pluggable database PDB3Recovery attempting to copy datafiles for pdb-PDB3 from source dir-/u01/files

Once the PDB creation is complete on standby we can open the PDB in the primary database.

-- PrimaryALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

We now see the "pdb3" pluggable database has been created on the primary and standby database.

-- PrimarySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED READ ONLY ENABLED 7FFCBA15929572D9E0536538A8C055AEPDB1 READ WRITE ENABLED 7FFCD0D366F578BDE0536538A8C01566PDB3 READ WRITE ENABLED 7FFE48F43BBD2A0CE0536538A8C04898SQL> -- StandbySET LINESIZE 100COLUMN name FORMAT A30SELECT name, open_mode, recovery_status, guidFROM v$pdbsORDER BY 1;NAME OPEN_MODE RECOVERY GUID------------------------------ ---------- -------- --------------------------------PDB$SEED MOUNTED ENABLED 7FFCBA15929572D9E0536538A8C055AEPDB1 MOUNTED ENABLED 7FFCD0D366F578BDE0536538A8C01566PDB3 MOUNTED ENABLED 7FFE48F43BBD2A0CE0536538A8C04898SQL>

Check the status of the Data Guard configuration.

dgmgrl sys/${SYS_PASSWORD}@${NODE2_DB_UNIQUE_NAME} <<EOFSHOW CONFIGURATION;EXIT;EOF

This gives us the following output.

DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Jan 21 20:00:53 2019Version 18.3.0.0.0Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1_stby"Connected as SYSDBA.DGMGRL>Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby databaseFast-Start Failover: DISABLEDConfiguration Status:SUCCESS (status updated 37 seconds ago)DGMGRL>$

Let's clean up by dropping the "pdb3" pluggable database from the primary database.

-- PrimaryALTER PLUGGABLE DATABASE pdb3 CLOSE;DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

Appendix

The examples here use an environment built using VirtualBox and Vagrant, as described here.

The additional "cdb3" instance can be created created using the following commands on the primary node.

# Create FRA location.mkdir -p /u01/app/oracle/fast_recovery_area# Container (cdb3) with PDB (pdb5).dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \ -characterSet AL32UTF8 \ -sysPassword SysPassword1 \ -systemPassword SysPassword1 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbName pdb5 \ -pdbAdminPassword SysPassword1 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 1500 \ -storageType FS \ -datafileDestination "/u01/oradata/" \ -redoLogFileSize 50 \ -emConfiguration NONE \ -ignorePreReqsexport ORACLE_SID=cdb3export ORAENV_ASK=NO. oraenvexport ORAENV_ASK=YES# Set required parameters, create link user.sqlplus / as sysdba <<EOFalter pluggable database pdb5 save state;alter system set db_create_file_dest = '/u01/oradata';alter system set db_recovery_file_dest_size = 10G;alter system set db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area';drop user c##remote_clone_user cascade;create user c##remote_clone_user identified by remote_clone_user container=all;grant create session, create pluggable database to c##remote_clone_user container=all;exit;EOF# Enable ARCHIVELOG mode.sqlplus / as sysdba <<EOFshutdown immediate;startup mount;alter database archivelog;alter database open;exit;EOF# Delete the instances.#dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1

For more information see:

• Using standby_pdb_source_file_dblink and standby_pdb_source_file_directory to Maintain Standby Databases when Performing PDB Remote Clones or Plugins (Doc ID 2274735.1)
• STANDBY_PDB_SOURCE_FILE_DBLINK
• STANDBY_PDB_SOURCE_FILE_DIRECTORY
• Data Guard Quick Links : 11gR2, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
• Multitenant : Controlling PDB Replication in Data Guard Environments
• Multitenant : All Articles

Hope this helps. Regards Tim...

Back to the Top.