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