Home » Articles » 18c » Here

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

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.

• 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.

-- Primary
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FD2A7B8B56772FFE0536538A8C0ECC3
PDB1                           READ WRITE ENABLED  7FD2C1FCD1227929E0536538A8C0E1F1

SQL>


-- Standby
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FD2A7B8B56772FFE0536538A8C0ECC3
PDB1                           MOUNTED    ENABLED  7FD2C1FCD1227929E0536538A8C0E1F1

SQL>

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=cdb3
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Create link user.
sqlplus / as sysdba <<EOF

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

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

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Create database link.
sqlplus / as sysdba <<EOF
CREATE 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 <<EOF
CDB3 =
  (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 <<EOF
ALTER 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.

-- Standby
SELECT * 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=cdb3
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF
ALTER 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=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Primary
sqlplus / as sysdba <<EOF
CREATE 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 PDB2
Recovery 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.

-- Primary
ALTER PLUGGABLE DATABASE pdb2 OPEN;

-- Standby
ALTER 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=cdb3
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF
ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 OPEN;

EXIT;
EOF

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

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

-- Primary
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FFCBA15929572D9E0536538A8C055AE
PDB1                           READ WRITE ENABLED  7FFCD0D366F578BDE0536538A8C01566
PDB2                           READ WRITE ENABLED  7FFD420F711E187AE0536538A8C0D422

SQL>


-- Standby
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FFCBA15929572D9E0536538A8C055AE
PDB1                           MOUNTED    ENABLED  7FFCD0D366F578BDE0536538A8C01566
PDB2                           MOUNTED    ENABLED  7FFD420F711E187AE0536538A8C0D422

SQL>

Check the status of the Data Guard configuration.

dgmgrl sys/${SYS_PASSWORD}@${NODE2_DB_UNIQUE_NAME} <<EOF
SHOW 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 2019
Version 18.3.0.0.0

Copyright (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 database

Fast-Start Failover: DISABLED

Configuration 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.

-- Primary
ALTER 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.

-- Primary
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FFCBA15929572D9E0536538A8C055AE
PDB1                           READ WRITE ENABLED  7FFCD0D366F578BDE0536538A8C01566

SQL> 


-- Standby
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FFCBA15929572D9E0536538A8C055AE
PDB1                           MOUNTED    ENABLED  7FFCD0D366F578BDE0536538A8C01566

SQL>

We can also disable Active Data Guard (ADG).

# Standby - Active Data Guard (ADG).
sqlplus / as sysdba <<EOF
CONN / AS SYSDBA
SHUTDOWN 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.

-- Primary
CONN / AS SYSDBA

CREATE 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.

# Primary
rman target=/ <<EOF
BACKUP AS COPY PLUGGABLE DATABASE pdb5 FORMAT '/u01/files/%U';
EXIT;
EOF

Now it's safe to drop the pluggable database.

-- Primary
CONN / AS SYSDBA
DROP 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.

-- Standby
CONN / AS SYSDBA
ALTER SYSTEM SET standby_pdb_source_file_directory='/u01/files';

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

-- Primary
CONN / AS SYSDBA
CREATE 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 PDB3
Recovery 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.

-- Primary
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

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

-- Primary
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       READ ONLY  ENABLED  7FFCBA15929572D9E0536538A8C055AE
PDB1                           READ WRITE ENABLED  7FFCD0D366F578BDE0536538A8C01566
PDB3                           READ WRITE ENABLED  7FFE48F43BBD2A0CE0536538A8C04898

SQL> 


-- Standby
SET LINESIZE 100
COLUMN name FORMAT A30

SELECT name, open_mode, recovery_status, guid
FROM   v$pdbs
ORDER BY 1;

NAME                           OPEN_MODE  RECOVERY GUID
------------------------------ ---------- -------- --------------------------------
PDB$SEED                       MOUNTED    ENABLED  7FFCBA15929572D9E0536538A8C055AE
PDB1                           MOUNTED    ENABLED  7FFCD0D366F578BDE0536538A8C01566
PDB3                           MOUNTED    ENABLED  7FFE48F43BBD2A0CE0536538A8C04898

SQL>

Check the status of the Data Guard configuration.

dgmgrl sys/${SYS_PASSWORD}@${NODE2_DB_UNIQUE_NAME} <<EOF
SHOW 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 2019
Version 18.3.0.0.0

Copyright (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 database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 37 seconds ago)

DGMGRL>
$

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

-- Primary
ALTER 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 \
 -ignorePreReqs

export ORACLE_SID=cdb3
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES

# Set required parameters, create link user.
sqlplus / as sysdba <<EOF
alter 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 <<EOF
shutdown 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.

Created: 2019-01-23  Updated: 2020-06-30

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer