In this blog, I would like to share how to create a new PDB in one CDB in a Data Guard environment on the ExaCC.

Read more: Create new PDB in a Data Guard environment on ExaCC

Naming Convention

Following is the naming convention:

  • CDB Name is MYCDB
  • ExaCC Primary site cluster is ExaCC-chz1-cl01
  • ExaCC Standby site cluster is ExaCC-chz2-cl01

Create new PDB

I will create a new PDB named NEWPDB in the CDB named MYCDB using dbaascli. This will be run on the primary site.

oracle@ExaCC-chz1-cl01n1:~/ [MYCDB1 (CDB$ROOT)] dbaascli pdb create --pdbName NEWPDB --dbName MYCDB
DBAAS CLI version 24.1.2.0.0
Executing command pdb create --pdbName NEWPDB --dbName MYCDB
Job id: e5eaa683-e31d-4c5a-962e-a568221653d9
Session log: /var/opt/oracle/log/MYCDB/pdb/create/dbaastools_2024-05-31_09-52-40-AM_96261.log
Loading PILOT...
Session ID of the current execution is: 7852
Log file location: /var/opt/oracle/log/MYCDB/pdb/create/pilot_2024-05-31_09-52-53-AM_101724
-----------------
Running Plugin_initialization job
Enter TDE_PASSWORD:                                                                                                                                                                                            *********************
Completed Plugin_initialization job
-----------------
Running Validate_input_params job
Completed Validate_input_params job
-----------------
Running Validate_target_pdb_service_name job
Completed Validate_target_pdb_service_name job
-----------------
Running Perform_dbca_prechecks job
Completed Perform_dbca_prechecks job
Acquiring read lock: _u02_app_oracle_product_19.0.0.0_dbhome_2
Acquiring write lock: mycdb
Acquiring write lock: newpdb
-----------------
Running PDB_creation job
Completed PDB_creation job
-----------------
Running Load_pdb_details job
Completed Load_pdb_details job
-----------------
Running Configure_pdb_service job
Completed Configure_pdb_service job
-----------------
Running Configure_tnsnames_ora job
Completed Configure_tnsnames_ora job
-----------------
Running Set_pdb_admin_user_profile job
Completed Set_pdb_admin_user_profile job
-----------------
Running Lock_pdb_admin_user job
Completed Lock_pdb_admin_user job
-----------------
Running Register_ocids job
Skipping. Job is detected as not applicable.
-----------------
Running Prepare_blob_for_standby_in_primary job
Completed Prepare_blob_for_standby_in_primary job
Releasing lock: newpdb
Releasing lock: mycdb
Releasing lock: _u02_app_oracle_product_19.0.0.0_dbhome_2
-----------------
Running Generate_dbsystem_details job
Acquiring native write lock: global_dbsystem_details_generation
Releasing native lock: global_dbsystem_details_generation
Completed Generate_dbsystem_details job
---------- PLUGIN NOTES ----------
Note: Pluggable database operation is performed on the primary database. In order to successfully complete the operation, the file /var/opt/oracle/log/reg_tmp_files/MYCDB_2024-05-31_09-52-53-AM_101724.tar needs to be copied to the standby database node and additional steps need to be run on all standby databases. Refer to the Cloud Service documentation for the complete steps.
---------- END OF PLUGIN NOTES ----------
dbaascli execution completed

Pay attention to the PLUGIN NOTES about the BLOB file to be copied on the standby site.

oracle@ExaCC-chz1-cl01n1:~/ [MYCDB1 (CDB$ROOT)] ls -ltrh /var/opt/oracle/log/reg_tmp_files/MYCDB_2024-05-31_09-52-53-AM_101724.tar
-rw------- 1 oracle oinstall 130K May 31 09:55 /var/opt/oracle/log/reg_tmp_files/MYCDB_2024-05-31_09-52-53-AM_101724.tar

Checks

Let’s check PDB on primary side:

oracle@ExaCC-chz1-cl01n1:~/ [MYCDB1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 31 09:56:30 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 NEWPDB                         READ WRITE NO

Let’s check PDB on standby side:

oracle@ExaCC-chz2-cl01n1:~/ [MYCDB1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 31 09:56:47 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO
         4 PDB2                           READ ONLY  NO
         5 PDB3                           READ ONLY  NO
         6 NEWPDB                         MOUNTED

The PDB is not started on the standby side.

Let’s check Data Guard:

oracle@ExaCC-chz1-cl01n1:~/ [MYCDB1 (CDB$ROOT)] dgh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri May 31 09:57:44 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect /
Connected to "MYCDB_CHZ1"
Connected as SYSDG.

DGMGRL> show configuration lag

Configuration - fsc

  Protection Mode: MaxAvailability
  Members:
  MYCDB_CHZ1 - Primary database
    MYCDB_CHZ2 - Physical standby database
                    Transport Lag:      0 seconds (computed 0 seconds ago)
                    Apply Lag:          0 seconds (computed 0 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 26 seconds ago)

The Data Guard between both Primary CHZ1 and Standby CHZ2 CDB is synchronized.

Try to open the PDB on standby side in READ ONLY?

Let’s try to open the PDB in READ ONLY mode on the standby side.

SQL> alter pluggable database NEWPDB open read only instances=all;
alter pluggable database NEWPDB open read only instances=all
*
ERROR at line 1:
ORA-65107: Error encountered when processing the current task on instance:1
ORA-01111: name for data file 68 is unknown - rename to correct file

As we can see, this is not possible. But why?

Let’s check the size of the new PDB on the standby side.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO
         4 PDB2                           READ ONLY  NO
         5 PDB3                           READ ONLY  NO
         6 NEWPDB                         MOUNTED

SQL> select name, total_size/1024/1024/1024 GB from v$pdbs;

NAME                                   GB
------------------------------ ----------
PDB$SEED                       1.85546875
PDB1                           2308.65039
PDB2                           1534.89966
PDB3                           1095.55273
NEWPDB                          0

SQL>

So the new PDB has been created on the standby side through Data Guard but is empty. This is why we are getting the BLOB file from dbaascli.

Copy the BLOB file to the standby side

We will copy and make the BLOB file available on the standby side. This has been done with scp command.

[opc@ExaCC-chz2-cl01n1 ~]$ sudo mv /tmp/MYCDB_2024-05-31_09-52-53-AM_101724.tar /var/opt/oracle/log/reg_tmp_files/
[opc@ExaCC-chz2-cl01n1 ~]$ sudo chown oracle: /var/opt/oracle/log/reg_tmp_files/MYCDB_2024-05-31_09-52-53-AM_101724.tar

Create the PDB on the standby using the BLOB file

We will need now to run on the standby the same exact command we ran on the primary, adding the option --standbyBlobFromPrimary.

oracle@ExaCC-chz2-cl01n1:~/ [MYCDB1 (CDB$ROOT)] dbaascli pdb create --pdbName NEWPDB --dbName MYCDB --standbyBlobFromPrimary /var/opt/oracle/log/reg_tmp_files/MYCDB_2024-05-31_09-52-53-AM_101724.tar
DBAAS CLI version 24.1.2.0.0
Executing command pdb create --pdbName NEWPDB --dbName MYCDB --standbyBlobFromPrimary /var/opt/oracle/log/reg_tmp_files/MYCDB_2024-05-31_09-52-53-AM_101724.tar
Job id: 7c3c0a4c-d7c2-4920-b453-0aee12547af1
Session log: /var/opt/oracle/log/MYCDB/pdb/create/dbaastools_2024-05-31_10-40-26-AM_149952.log
Loading PILOT...
Session ID of the current execution is: 5623
Log file location: /var/opt/oracle/log/MYCDB/pdb/create/pilot_2024-05-31_10-40-39-AM_153628
-----------------
Running Plugin_initialization job
Enter SYS_PASSWORD:                                                                                                                                                                                                    ***********************
Enter TDE_PASSWORD:                                                                                                                                                                                                     **********************
Completed Plugin_initialization job
-----------------
Running Validate_input_params job
Completed Validate_input_params job
-----------------
Running Validate_target_pdb_service_name job
Completed Validate_target_pdb_service_name job
-----------------
Running Perform_dbca_prechecks job
Completed Perform_dbca_prechecks job
Acquiring read lock: _u02_app_oracle_product_19.0.0.0_dbhome_2
Acquiring write lock: mycdb
Acquiring write lock: newpdb
-----------------
Running PDB_creation job
Completed PDB_creation job
-----------------
Running Load_pdb_details job
Completed Load_pdb_details job
-----------------
Running Configure_pdb_service job
Completed Configure_pdb_service job
-----------------
Running Configure_tnsnames_ora job
Completed Configure_tnsnames_ora job
-----------------
Running Set_pdb_admin_user_profile job
Skipping. Job is detected as not applicable.
-----------------
Running Lock_pdb_admin_user job
Skipping. Job is detected as not applicable.
-----------------
Running Register_ocids job
Skipping. Job is detected as not applicable.
-----------------
Running Prepare_blob_for_standby_in_primary job
Skipping. Job is detected as not applicable.
Releasing lock: newpdb
Releasing lock: mycdb
Releasing lock: _u02_app_oracle_product_19.0.0.0_dbhome_2
-----------------
Running Generate_dbsystem_details job
Acquiring native write lock: global_dbsystem_details_generation
Releasing native lock: global_dbsystem_details_generation
Completed Generate_dbsystem_details job
dbaascli execution completed

Checks

Now the PDB is open in READ ONLY mode on the standby side and having data.

oracle@ExaCC-chz2-cl01n1:~/ [MYCDB1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 31 10:50:00 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO
         4 PDB2                           READ ONLY  NO
         5 PDB3                           READ ONLY  NO
         6 NEWPDB                         READ ONLY  NO

SQL> select name, total_size/1024/1024/1024 GB from v$pdbs;

NAME                         GB
-------------------- ----------
PDB$SEED             1.85546875
PDB1                 2308.65039
PDB2                 1534.89966
PDB3                 1095.55273
NEWPDB               4.34375

SQL>

Let’s check Data Guard status.

DGMGRL> show configuration lag

Configuration - fsc

  Protection Mode: MaxAvailability
  Members:
  MYCDB_CHZ1 - Primary database
    MYCDB_CHZ2 - Physical standby database
                    Transport Lag:      0 seconds (computed 0 seconds ago)
                    Apply Lag:          0 seconds (computed 0 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

And we can that the standby is in sync with the primary.

To wrap up

Creating a new PDB in a multitenant environment with Data guard need to run creation of the PDB on the standby side. This is quite easy with dbaascli.