Creating Pluggable Database on Standby or Dataguard

As you might be aware of fact that we can create Standby Database on a Container Database (CDB) of Multitenant instance, thus it implies that we cannot create a standby database only for a PDB.

A Container Database (CDB) shares its resources like instance, control files, online redo logfiles and standby redo logfiles with PDBs.

Thus when a new pluggable database (PDB) is created on PRIMARY database, the changes are shipped and it is created automatically on STANDBY database as well. An important thing to keep in mind is having standby_file_management set to AUTO, for automatic management of datafiles.

When creating a PDB in dataguard environment, it is important to consider STANDBYS clause in CREATE PLUGGABLE DATABASE statement. This clause accepts two values:

1. ALL - [default] This will create new PDB on all STANDBY databases
2. NONE - This will not create new PDB on any STANDBY databases

Another clause commonly used is PATH_PREFIX. I will discuss this later in this post.

Steps to Create Pluggable Database in Dataguard Environment

1. Prepare directory for keeping datafiles

My environment had same directory structure on primary and standby databases, thus I have created below directory on both:

$ mkdir -p /u01/app/oracle/oradata/data/rackdb1/pdb2

2. Run CREATE PLUGGABLE DATABASE statement on PRIMARY database

SQL> select db_unique_name, database_role from v$database;

DB_UNIQUE_NAME  DATABASE_ROLE
--------------- -------------
rackdb1         PRIMARY

SQL> CREATE PLUGGABLE DATABASE PDB2 
ADMIN USER PDB2ADMIN IDENTIFIED BY "<pass>"
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/data/rackdb1/pdbseed/','/u01/app/oracle/oradata/data/rackdb1/pdb2/')
/

================ALERT LOG (PRIMARY)====================
Thu Mar 02 01:27:06 2017
CREATE PLUGGABLE DATABASE PDB2 ADMIN USER PDB2ADMIN IDENTIFIED BY *FILE_NAME_CONVERT=('/u01/app/oracle/oradata/data/rackdb1/pdbseed/','/u01/app/oracle/oradata/data/rackdb1/pdb2/')
Thu Mar 02 01:27:10 2017
****************************************************************
Pluggable Database PDB2 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#42 to file$(old file#5)
Adding new file#43 to file$(old file#7)
Successfully created internal service pdb2 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB2 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB2 ADMIN USER PDB2ADMIN IDENTIFIED BY *FILE_NAME_CONVERT=('/u01/app/oracle/oradata/data/rackdb1/pdbseed/','/u01/app/oracle/oradata/data/rackdb1/pdb2/')
=======================================================

================ALERT LOG (STANDBY)====================
Thu Mar 02 06:27:11 2017
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /u01/app/oracle/oradata/data/rackdb1/pdb2/system01.dbf from /u01/app/oracle/oradata/data/rackdb1/pdbseed/system01.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /u01/app/oracle/oradata/data/rackdb1/pdb2/system01.dbf
Successfully added datafile 42 to media recovery
Datafile #42: '/u01/app/oracle/oradata/data/rackdb1/pdb2/system01.dbf'
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /u01/app/oracle/oradata/data/rackdb1/pdb2/sysaux01.dbf from /u01/app/oracle/oradata/data/rackdb1/pdbseed/sysaux01.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /u01/app/oracle/oradata/data/rackdb1/pdb2/sysaux01.dbf
Successfully added datafile 43 to media recovery
Datafile #43: '/u01/app/oracle/oradata/data/rackdb1/pdb2/sysaux01.dbf'
=======================================================

 



3. Check status of newly created PDB2

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name

NAME     OPEN_MODE
-------- ----------
PDB$SEED READ ONLY
PDB1     READ WRITE
PDB2     MOUNTED








The database is created in MOUNTED state, this can be opened in read write mode using below ALTER statement.

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

Pluggable database altered.

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

NAME      OPEN_MODE
--------- ----------
PDB$SEED  READ ONLY
PDB1      READ WRITE
PDB2      READ WRITE

4. Check status of PDB2 on STANDBY database

SQL> select db_unique_name, database_role from v$database;

DB_UNIQUE_NAME  DATABASE_ROLE
--------------- ----------------
rackdb1dg       PHYSICAL STANDBY

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

NAME     OPEN_MODE
-------- ---------
PDB$SEED READ ONLY
PDB1     READ ONLY
PDB2     MOUNTED





The new pluggable database is in MOUNTED mode on standby database. Since this is an Active Dataguard instance, I can open it in read only.

SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ ONLY;

SQL> SELECT name, open_mode
FROM v$pdbs
ORDER BY name;

NAME     OPEN_MODE
-------- ---------
PDB$SEED READ ONLY
PDB1     READ ONLY
PDB2     READ ONLY

Cheers!
Anurag