Since the 21c was public available the Data Guard per Pluggable Database – DG PDB – was intended to be there, but Oracle needed more time to make things work and some weeks ago released the feature with the 21.7 version. Here in this post, I will show to configure it and also how to troubleshoot, and the pitfalls of using it. As usual, all the steps, logs, and outputs are covered here and I hope that it helps you understand the whole DG PDB process.
My environment
The environment that I am using here is:
- Two databases running in RAC mode (two nodes in each cluster).
- ASM: same DATA and RECO diskgroups names in each cluster.
About the databases I have:
- ORADBDC1, that have the pdb PDBDC1. So, they represent the DC1.
- ORADBDC2, that have the pdb PDBDC2. So, they represent the DC2.
Each of these clusters is in a separate environment, this means that both are primary databases inside each datacenter. So, they have no DG configured between them.
The main target for this post is to have the pdb from DC2 protected by the ORADBDC1 at the DC1. I used RAC and ASM because this is usually the normal configuration for the MAA (following the recommended architectures baseline) when using DG. This increases the protection and reduces the SPOF of your environment.
DG PDB
The idea of DG PDB differs a little from what we see commonly for Data Guard, here each container have own life. This means that only the pdb is protected and not the entire cdb. This puts the DG PDB close to Cloud than On-Prem because it fit perfectly at the OCI structure since you can create your pdb in one region and choose another region to protect it. And even closer if you think for Autonomous Database that your ownership is pdb only. I will not say that is good or not, but is linked to how Oracle works with OCI. Personally, I prefer to have normal DG configured to protect my databases and I choose where I want to open my pdb (maybe they add this feature in the future).
Another detail is that DG PDG (from now) works only in MaxPerformance mode, so, there is no SYNC mode for the archive destinations. There are more limitations for the DG PDB and you can check it in the topic DG PDB Configuration Restrictions from official documentation (I recommend that you read it).
Please read my new blog post about the new changes to the process. You can see how the process evolved and it is better. Read it here.
Preparing databases
Starting the process, let’s check what I have configured now. Basically, what is configured is the image below.
The first thing to do is to prepare both databases to meet the requirements before starting the broker configuration.
Current view
The current view for DC1 is:
[oracle@oel8n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 19:50:44 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBDC1 READ WRITE NO
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string oradbdc1
SQL>
And for DC2:
[oracle@oel8sn1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 19:51:29 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBDC2 READ WRITE NO
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string oradbdc2
SQL>
TNSNAMES
The first is step is preparing the connections between databases. So, you need to have both entries (for each database) at your tnsnames.ora file. Bellow, you can see that both nodes (for each cluster) resolve the same entries names (this is important that each site resolve the same name):
[oracle@oel8n1-21c ~]$ tnsping ORADBDC1 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 31-JUL-2022 19:53:27 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel8-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbdc1))) OK (10 msec) [oracle@oel8n1-21c ~]$ [oracle@oel8n1-21c ~]$ tnsping ORADBDC2 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 31-JUL-2022 19:53:32 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel8s-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbdc2))) OK (0 msec) [oracle@oel8n1-21c ~]$ [oracle@oel8n1-21c ~]$ scp /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora oel8n2-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora tnsnames.ora 100% 893 549.1KB/s 00:00 [oracle@oel8n1-21c ~]$ ############################## # # At DC2 # ############################## [oracle@oel8sn1-21c ~]$ tnsping ORADBDC1 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 31-JUL-2022 19:53:59 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel8-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbdc1))) OK (10 msec) [oracle@oel8sn1-21c ~]$ [oracle@oel8sn1-21c ~]$ tnsping ORADBDC2 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 31-JUL-2022 19:54:01 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel8s-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbdc2))) OK (0 msec) [oracle@oel8sn1-21c ~]$ [oracle@oel8sn1-21c ~]$ scp /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora oel8sn2-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora tnsnames.ora 100% 893 791.1KB/s 00:00 [oracle@oel8sn1-21c ~]$
Basic database parameters and adjusts
To start to configure DG PDB you don’t need too much, you just need to guarantee in each database the following:
- dg_broker_start and dg_broker_config_file* parameters configured and set.
- standby_file_management defined as AUTO.
- Standby redo log files for CDB level set.
You can see that I made that for databases at the database at DC1:
[oracle@oel8n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 19:57:44 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr1oradbdc1.dat
dg_broker_config_file2 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr2oradbdc1.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
SQL>
SQL>
SQL> alter system set dg_broker_config_file1 = '+DATA/oradbdc1/dr1oradbdc1.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_config_file2 = '+RECO/oradbdc1/dr2oradbdc1.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_start=TRUE scope = both sid = '*' ;
System altered.
SQL>
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management = AUTO scope = both sid = '*' ;
System altered.
SQL>
SQL> select group#,thread#,bytes from v$standby_log;
no rows selected
SQL> select group#,thread#,bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 209715200
2 1 209715200
3 2 209715200
4 2 209715200
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ---------- ----------
5 1 209715200
6 1 209715200
7 1 209715200
8 2 209715200
9 2 209715200
10 2 209715200
6 rows selected.
SQL>
And for databases at DC2 as well:
[oracle@oel8sn1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 19:59:25 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr1oradbdc2.dat
dg_broker_config_file2 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr2oradbdc2.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
SQL> alter system set dg_broker_config_file1 = '+DATA/oradbdc2/dr1oradbdc2.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_config_file2 = '+RECO/oradbdc2dr2oradbdc2.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_start=TRUE scope = both sid = '*' ;
System altered.
SQL>
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management = AUTO scope = both sid = '*' ;
System altered.
SQL>
SQL> select group#,thread#,bytes from v$standby_log;
no rows selected
SQL> select group#,thread#,bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 209715200
2 1 209715200
3 2 209715200
4 2 209715200
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+RECO', '+DATA') size 209715200;
Database altered.
SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ---------- ----------
5 1 209715200
6 1 209715200
7 1 209715200
8 2 209715200
9 2 209715200
10 2 209715200
6 rows selected.
SQL>
Remote connection and Wallet
To allow one database to register another for DG, you need to allow remote connection (user with SYSDBA/SYSDG permissions). And here we have the first pitfall.
The only way that I made it work was using the wallet and this is due to the way that the configuration is made inside the broker. In the end, I have the ISSUE #1 topic that describes with more information.
What you need to know and understand is that the databases will connect using a defined “connect identifier” and this needs to be one tns entry or ezconnect. And when using a wallet, the credential name inside of the wallet needs to have the same name as your tns entry for each database. So, the way to prepare this is exactly like this:
[oracle@oel8n1-21c ~]$ mkdir -p /u01/app/oracle/dbs/dgwallet_oradbdc [oracle@oel8n1-21c ~]$ [oracle@oel8n1-21c ~]$ mkstore -wrl /u01/app/oracle/dbs/dgwallet_oradbdc -createALO Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. [oracle@oel8n1-21c ~]$ mkstore -wrl /u01/app/oracle/dbs/dgwallet_oradbdc -createCredential ORADBDC1 sys oracle Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. [oracle@oel8n1-21c ~]$ mkstore -wrl /u01/app/oracle/dbs/dgwallet_oradbdc -createCredential ORADBDC2 sys oracle Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. [oracle@oel8n1-21c ~]$ mkstore -wrl /u01/app/oracle/dbs/dgwallet_oradbdc -listCredential Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved. List credential (index: connect_string username) 2: ORADBDC2 sys 1: ORADBDC1 sys [oracle@oel8n1-21c ~]$ [oracle@oel8n1-21c ~]$ ls -l /u01/app/oracle/dbs/dgwallet_oradbdc total 4 -rw------- 1 oracle oinstall 941 Aug 2 21:33 cwallet.sso -rw------- 1 oracle oinstall 0 Aug 2 21:32 cwallet.sso.lck [oracle@oel8n1-21c ~]$
Above you can see that I made the following (in order):
- Created the one autologin (ALO) wallet.
- Added the credentials (for the sys users).
After I need to configure the sqlnet.ora file (because the RFS processes – and others) will use this wallet to send the redo:
[oracle@oel8n1-21c ~]$ ls -l /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
ls: cannot access '/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora': No such file or directory
[oracle@oel8n1-21c ~]$
[oracle@oel8n1-21c ~]$ vi /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
[oracle@oel8n1-21c ~]$
[oracle@oel8n1-21c ~]$ cat /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = true
WALLET_LOCATION =
(
SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/dbs/dgwallet_oradbdc)
)
)
[oracle@oel8n1-21c ~]$
I recommend you to test the wallet usage with sqlplus connections before you continue. Below are some examples of how to do that, you can see that I do not specify username and password, just the tns entry (and this entry is the credential name inside the wallet).
[oracle@oel8n1-21c ~]$ sqlplus /@oradbdc2 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 2 21:37:33 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 [oracle@oel8n1-21c ~]$ sqlplus /@oradbdc1 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 2 21:37:39 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 [oracle@oel8n1-21c ~]$
After that I copy the wallet and sqlnet to the other node of DC1 and to all nodes for the DC2 database:
[oracle@oel8n2-21c ~]$ mkdir /u01/app/oracle/dbs/dgwallet_oradbdc [oracle@oel8n2-21c ~]$ scp oel8n1-21c:/u01/app/oracle/dbs/dgwallet_oradbdc/* /u01/app/oracle/dbs/dgwallet_oradbdc cwallet.sso 100% 941 58.2KB/s 00:00 cwallet.sso.lck 100% 0 0.0KB/s 00:00 [oracle@oel8n2-21c ~]$ [oracle@oel8n2-21c ~]$ scp oel8n1-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora sqlnet.ora 100% 221 42.9KB/s 00:00 [oracle@oel8n2-21c ~]$ ############################## # # At DC2 # ############################## [oracle@oel8sn1-21c ~]$ mkdir -p /u01/app/oracle/dbs/dgwallet_oradbdc [oracle@oel8sn1-21c ~]$ [oracle@oel8sn1-21c ~]$ scp oel8n1-21c:/u01/app/oracle/dbs/dgwallet_oradbdc/* /u01/app/oracle/dbs/dgwallet_oradbdc oracle@oel8n1-21c's password: cwallet.sso 100% 941 582.1KB/s 00:00 cwallet.sso.lck 100% 0 0.0KB/s 00:00 [oracle@oel8sn1-21c ~]$ scp oel8n1-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora oracle@oel8n1-21c's password: sqlnet.ora 100% 221 63.3KB/s 00:00 [oracle@oel8sn1-21c ~]$ [oracle@oel8sn1-21c ~]$ ssh oel8sn2-21c.oralocal Activate the web console with: systemctl enable --now cockpit.socket Last login: Wed Aug 3 10:02:21 2022 [oracle@oel8sn2-21c ~]$ mkdir -p /u01/app/oracle/dbs/dgwallet_oradbdc [oracle@oel8sn2-21c ~]$ scp oel8n1-21c:/u01/app/oracle/dbs/dgwallet_oradbdc/* /u01/app/oracle/dbs/dgwallet_oradbdc oracle@oel8n1-21c's password: cwallet.sso 100% 941 253.4KB/s 00:00 cwallet.sso.lck 100% 0 0.0KB/s 00:00 [oracle@oel8sn2-21c ~]$ scp oel8n1-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora oracle@oel8n1-21c's password: sqlnet.ora 100% 221 173.8KB/s 00:00 [oracle@oel8sn2-21c ~]$
As you can see above, I set tnsnames.ora, sqlnet.ora, and wallet outside the Oracle Home. This is because of the ROOH (Read Only Oracle Home) for 21c (as I explained in the previous post).
Unfortunately, after you configure the wallet and sqlnet entry you need to restart the databases (both instances). This is needed because the current database processes started before you configure and they don’t know the entries that you defined.
Broker and DGMGRL
Creating configuration
After adjusting the basic requirements we can start to configure the Broker. So, connecting at the first node of my cluster database ORADBDC1 at DC1 I created the configuration for the local database:
[oracle@oel8n1-21c ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Wed Aug 3 23:10:15 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "oradbdc1"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
ORA-16596: member not part of the Oracle Data Guard broker configuration
Configuration details cannot be determined by DGMGRL
DGMGRL>
DGMGRL>
DGMGRL> create configuration dc1 primary database is oradbdc1 connect identifier is oradbdc1;
Connected to "oradbdc1"
Configuration "dc1" created with primary database "oradbdc1"
DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;
Configuration - dc1
Protection Mode: MaxPerformance
Members:
oradbdc1 - Primary database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
ObserverPingInterval = '0'
ObserverPingRetry = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'oradbdc1_CFG'
ConfigurationSimpleName = 'dc1'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
As you saw, the basic configuration informs that this database is a primary database and the current connect identifier. Is the same that you do for a normal DG.
The same needs to be done for the other site. So, connecting at the first node of my cluster database ORADBDC2 at DC2 I created the configuration for the local database as well:
[oracle@oel8sn1-21c ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Wed Aug 3 23:11:17 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "oradbdc2"
Connected as SYSDBA.
DGMGRL>
DGMGRL> create configuration dc2 primary database is oradbdc2 connect identifier is oradbdc2;
Connected to "oradbdc2"
Configuration "dc2" created with primary database "oradbdc2"
DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;
Configuration - dc2
Protection Mode: MaxPerformance
Members:
oradbdc2 - Primary database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
ObserverPingInterval = '0'
ObserverPingRetry = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'oradbdc2_CFG'
ConfigurationSimpleName = 'dc2'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
So, now we have both sites with their own configuration. As you can see above, both configurations DC1 and DC2 appear as primary.
Add configuration
After that, we can use the new command for 21.7 and add one site to the other configuration. Here I am at DC1 and would add the configuration defined at DC2:
DGMGRL> add configuration dc2 connect identifier is oradbdc2; Configuration dc2 added. DGMGRL>
This is the new command for DG PDB. It allows you to add the configuration (from a remote site) to the current broker config. And here is the reason that you need to have a wallet (or somehow remote connection without requesting a password). At the moment the password is not possible to be specified (but supposed to connect using the current used to connect at dgmgrl). You can see at ISSUE #1 at the end of the page that I tried several ways and nothing worked.
As explained before, the “connect identifier” refers to tns entry and needs to be the same that exists in your wallet. With that, the connection can be done and the configuration can be added. After that you can see that I have both configurations identified as Primary Databases:
DGMGRL> show configuration verbose;
Configuration - dc1
Protection Mode: MaxPerformance
Members:
oradbdc1 - Primary database
oradbdc2 - Primary database in dc2 configuration
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
ObserverPingInterval = '0'
ObserverPingRetry = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'oradbdc1_CFG'
ConfigurationSimpleName = 'dc1'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
DGMGRL> show configuration verbose dc2
Configuration - dc2
Primary Database: oradbdc2
Properties:
DGConnectIdentifier = ''
LogShipping = 'ON'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
Configuration dc2 Status:
SUCCESS
DGMGRL>
After that we can enable the configuration (if you do not do that you can’t add the pdb and will receive the error DGM-17381: Configuration XXXX is disabled):
DGMGRL> enable configuration all; Succeeded. DGMGRL>
Using the ALL, the configuration is enabled on both sites.
Add Pluggable Database
After enabling we can use the next new command for 21.7. It is the ADD PLUGGABLE DATABASE, and (as the name suggests) it adds the pdb to your broker configuration:
DGMGRL> ADD PLUGGABLE DATABASE pdbdc2 AT oradbdc1 SOURCE IS pdbdc2 AT oradbdc2 PDBFILENAMECONVERT IS "'oradbdc2','oradbdc1'"; Connected to "oradbdc2" Enter password for DGPDB@oradbdc2: Connected to "oradbdc1" Enter password for DGPDB@oradbdc1: ORA-17628: Oracle error 11402 returned by remote Oracle server ORA-11402: Oracle Data Guard site entry not found Failed. DGMGRL> ADD PLUGGABLE DATABASE pdbdc2 AT oradbdc1 SOURCE IS pdbdc2 AT oradbdc2 PDBFILENAMECONVERT IS "'oradbdc2','oradbdc1'"; Connected to "oradbdc2" Connected to "oradbdc1" Pluggable Database "PDBDC2" added DGMGRL>
First, for the “failed”, I explain ISSUE #2 at the end of the post. But I just run the same command and the pdb is added correctly. Check that the password is not asked because the first execution already configured everything at pdb level (DGPDB_INT user).
So, the command has some details:
- The pdb name: As you saw above, I added the PDBDC2 with the same name, but you can specify any name that you want. One detail is that if you change the name of pdb and do not use OFA (like using at filesystem), remember to add this name change at the pdbfilenameconvert too.
- AT: The first “AT” indicates the target (where) the pdb will be added. In my scenario means that I am adding it to oradbdc1 database.
- SOURCE IS: This means the source of the PDB, here it is the PDBDC2 from the oradbdc2 database.
- PDBFILENAMECONVER: It defines the transcription for datafile conversion. It works the same way as db_file_name_convert. Here I just converted the names of the databases since both sites use ASM with the same diskgroup name. But if needed you can add the “couples” that you need.
When you execute the command it will request you to define the password for the DGPDB user. Please set the same password for both. This is a new used to sync and made the connections between sites. When you add the broker check if the user exists and it is unlocked. If not, it creates or unlocks it if needed. More info at the official documentation.
While after adding the pdb you can use the command to show the details of pdb inside of broker:
DGMGRL> show pluggable database pdbdc2 at oradbdc1; Pluggable database 'pdbdc2' at database 'oradbdc1' Data Guard Role: Physical Standby Con_ID: 5 Source: con_id 3 at oradbdc2 Transport Lag: 0 seconds (computed 43 seconds ago) Intended State: APPLY-ON Apply State: Not Running Pluggable Database Status: DGM-5103: one or more data files were not found ORA-16766: Redo Apply is stopped DGMGRL> show pluggable database pdbdc2 at oradbdc2; Connected to "oradbdc2" Pluggable database 'pdbdc2' at database 'oradbdc2' Data Guard Role: Primary Con_ID: 4 Active Target: con_id 5 at oradbdc1 Pluggable Database Status: SUCCESS DGMGRL>
So, above you can see two commands, the output is different between them because at DC2 it is the primary site for the pdb, and while for DC1 it is the standby site for the pdb. Important to notice that it shows you that the CON_ID from each site is different. In my case at DC2, it is 4, and at DC1 it is 5.
But as you saw above the command reports Warning: ORA-16910: Inconsistency detected for one or more pluggable databases. And this is normal because (apparently, and until now) the “add pluggable database” does not copy the data from one site to another (directly from the documentation: “After you add the source PDB, and before you start recovery on the target PDB, ensure that the database files that correspond to the source PDB are copied to the target database. Use RMAN or operating system copy commands to instantiate source PDB files.”). Adding more this is the output from my alertlog (at the target DC1) shows that files were not copied:
2022-08-03T23:13:02.797847+02:00
create pluggable database PDBDC2 as standby from PDBDC2 at oradbdc2 file_name_convert=('oradbdc2','oradbdc1')
2022-08-03T23:13:10.850260+02:00
****************************************************************
Pluggable Database PDBDC2 with pdb id - 5 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x000000000000011d
****************************************************************
2022-08-03T23:13:10.926824+02:00
PDBDC2(5):ALTER SYSTEM SET _dgpdb_file_name_convert='oradbdc2','oradbdc1' SCOPE=SPFILE PDB='PDBDC2';
*****************************************
WARNING: The converted filename '+DATA/oradbdc1/e55cef3f4eca51d8e0535a0aa00aa405/datafile/system.302.1111789169'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_15.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/oradbdc1/e55cef3f4eca51d8e0535a0aa00aa405/datafile/sysaux.300.1111789169'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_16.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/oradbdc1/e55cef3f4eca51d8e0535a0aa00aa405/datafile/undotbs1.301.1111789169'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_17.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/oradbdc1/e55cef3f4eca51d8e0535a0aa00aa405/datafile/undo_2.305.1111789227'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_18.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
Completed: create pluggable database PDBDC2 as standby from PDBDC2 at oradbdc2 file_name_convert=('oradbdc2','oradbdc1')
2022-08-03T23:13:13.059159+02:00
So, since I am using ASM as a storage system the datafiles can’t even be copied (directly) because (by definition) is a fully qualified name and the direct name is not allowed.
Copying Datafiles
So, there are several options to copy datafiles between sites. Online, and offline modes too. I prefer the online mode using rman because the interference is less over the system (and is easier to parallel things).
I recommend to you do add ISSUE #3 at the end of the post to show some details on why I used the auxiliary at rman.
The first thing that you need to do is disable the configuration to avoid any type/kind of lock over the redos/files/controlfile (is not clear in the documentation if this is needed):
DGMGRL> disable configuration all; Succeeded. DGMGRL>
Before copying the first thing that you need to do is connect to the primary site for this pdb and discover which file numbers it has:
[oracle@oel8sn1-21c ~]$ sqlplus sys/oracle@oradbdc2 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 3 19:46:28 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDBDC2 READ WRITE NO
SQL>
SQL> set linesize 255
SQL> col file_name format a100
SQL> select file_id, file_name from cdb_data_files where con_id = 4;
FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
15 +DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/system.302.1111789169
16 +DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/sysaux.300.1111789169
17 +DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undotbs1.301.1111789169
18 +DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undo_2.305.1111789227
SQL>
So, now I know that I need to copy datafiles 15 to 18 to DC1. To do that I used the command below while connected at the first node of DC1:
[oracle@oel8n1-21c ~]$ rman target sys/oracle@oradbdc2 auxiliary sys/oracle@oradbdc1
Recovery Manager: Release 21.0.0.0.0 - Production on Wed Aug 3 23:20:27 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBDC2 (DBID=3458508890)
connected to auxiliary database: ORADBDC1 (DBID=2977563181)
RMAN>
RMAN> run {
2> allocate channel d1 type disk ;
3> backup as copy reuse datafile 15,16,17,18 auxiliary format '+DATA';
4> }
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=69 instance=oradbdc22 device type=DISK
Starting backup at 03-AUG-22
channel d1: starting datafile copy
input datafile file number=00016 name=+DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/sysaux.300.1111789169
output file name=+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/sysaux.316.1111792861 tag=TAG20220803T232100
channel d1: datafile copy complete, elapsed time: 00:00:25
channel d1: starting datafile copy
input datafile file number=00015 name=+DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/system.302.1111789169
output file name=+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/system.317.1111792887 tag=TAG20220803T232100
channel d1: datafile copy complete, elapsed time: 00:00:15
channel d1: starting datafile copy
input datafile file number=00017 name=+DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undotbs1.301.1111789169
output file name=+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undotbs1.318.1111792901 tag=TAG20220803T232100
channel d1: datafile copy complete, elapsed time: 00:00:03
channel d1: starting datafile copy
input datafile file number=00018 name=+DATA/ORADBDC2/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undo_2.305.1111789227
output file name=+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undo_2.319.1111792905 tag=TAG20220803T232100
channel d1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-22
released channel: d1
RMAN>
Above you can see that i:
- My target is the oradbdc2 at DC2.
- Auxiliary is the oradbdc1 at DC1.
- BACKUP AS COPY, so, the datafiles specified will be copied AS IS.
- AUXILIARY FORMAT defined the destination (so I don’t need to specify newname since is done automatically).
- From the output, you can see the source and target datafilenames (and the conversion as well).
So, at ASM in DC1 now I have this:
ASMCMD> ls -l E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE AUG 03 23:00:00 Y SYSAUX.316.1111792861 DATAFILE UNPROT COARSE AUG 03 23:00:00 Y SYSTEM.317.1111792887 DATAFILE UNPROT COARSE AUG 03 23:00:00 Y UNDOTBS1.318.1111792901 DATAFILE UNPROT COARSE AUG 03 23:00:00 Y UNDO_2.319.1111792905 ASMCMD>
As you saw previously the output from my alertlog the datafiles names was registered with “MUST_RENAME” at the controlfile. This can be checked directly at database oradbdc1 at DC1:
[oracle@oel8n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 3 23:22:23 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL>
SQL> col NAME format a50
SQL> col VALUE$ format a50
SQL> select DB_UNIQ_NAME, NAME, VALUE$, PDB_UID from PDB_SPFILE$ ;
DB_UNIQ_NAME NAME VALUE$ PDB_UID
------------------------------ -------------------------------------------------- -------------------------------------------------- ----------
* _dgpdb_file_name_convert 'oradbdc2','oradbdc1' 565232866
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBDC1 READ WRITE NO
5 PDBDC2 MOUNTED
SQL> alter session set container = PDBDC2;
Session altered.
SQL> set linesize 255
SQL> col name format a70
SQL> select file#, name from v$datafile;
FILE# NAME
---------- ----------------------------------------------------------------------
85 +DATA/MUST_RENAME_THIS_DATAFILE_15.4294967295.4294967295
86 +DATA/MUST_RENAME_THIS_DATAFILE_16.4294967295.4294967295
87 +DATA/MUST_RENAME_THIS_DATAFILE_17.4294967295.4294967295
88 +DATA/MUST_RENAME_THIS_DATAFILE_18.4294967295.4294967295
SQL>
So, to fix that we need to rename the datafiles. This is done using the rename command and specifying the source and target files. Please take attention to rename correctly the datafile names to the correspondent ones:
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_15.4294967295.4294967295' to '+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/system.317.1111792887';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_16.4294967295.4294967295' to '+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/sysaux.316.1111792861';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_17.4294967295.4294967295' to '+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undotbs1.318.1111792901';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_18.4294967295.4294967295' to '+DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undo_2.319.1111792905';
Database altered.
SQL> col name format a100
SQL> select file#, name from v$datafile;
FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
85 +DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/system.317.1111792887
86 +DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/sysaux.316.1111792861
87 +DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undotbs1.318.1111792901
88 +DATA/ORADBDC1/E55CEF3F4ECA51D8E0535A0AA00AA405/DATAFILE/undo_2.319.1111792905
SQL>
Enable Configuration
After copying the datafiles and renaming them, we can come back to dgmgrl and enable the configuration again. Doing that the RFS starts again the redo start to apply correctly:
DGMGRL> enable configuration all; Succeeded. DGMGRL>
And if you look at alertlog from oradbdc1 (that is the standby for the pdb) you can see that the redo is in transit and the “alter pluggable database pdbdc2 recover managed standby database” is in place:
2022-08-03T23:28:01.283876+02:00 ALTER SYSTEM SET fal_server='oradbdc2' SCOPE=BOTH; alter pluggable database PDBDC2 recover managed standby database disconnect alter pluggable database "PDBDC2" close instances=all Completed: alter pluggable database "PDBDC2" close instances=all 2022-08-03T23:28:01.333398+02:00 RSM0 (PID:406236): Requesting Managed Recovery process for PDBID:5 [krsm.c:1352] 2022-08-03T23:28:01.347156+02:00 TT04 (PID:489392): Background Managed Recovery process started [krsm.c:1697] 2022-08-03T23:28:01.355674+02:00 PDBDC2(5):Serial Media Recovery started 2022-08-03T23:28:01.376770+02:00 .... (PID:405802): PDBID:5 Managed Recovery starting Real Time Apply [krsm.c:15931] 2022-08-03T23:28:01.428582+02:00 PDBDC2(5):max_pdb is 5 2022-08-03T23:28:01.568393+02:00 PDBDC2(5):TT04 (PID:489392): Media Recovery Log +RECO/ORADBDC1/ARCHIVELOG/2022_08_03/thread_1_seq_47.489.1111792393 [krd.c:9452] 2022-08-03T23:28:01.874258+02:00 ALTER SYSTEM SET remote_listener=' oel8-21c-scan:1521' SCOPE=MEMORY SID='oradbdc11'; 2022-08-03T23:28:01.879113+02:00 ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='oradbdc11'; 2022-08-03T23:28:02.335897+02:00 Completed: alter pluggable database PDBDC2 recover managed standby database disconnect 2022-08-03T23:28:03.060207+02:00 PDBDC2(5):TT04 (PID:489392): Media Recovery Log +RECO/ORADBDC1/ARCHIVELOG/2022_08_03/thread_2_seq_39.488.1111792393 [krd.c:9452] PDBDC2(5):TT04 (PID:489392): Media Recovery Waiting for T-1.S-48 (in transit) [krsm.c:6205]
And when the archivelolg is generated at the primary site of the pdb, it is applied correctly at the standby:
############################## # # At DC2 # ############################## SQL> alter system archive log current; System altered. SQL> ############################## # # Alertlog at DC2 # ############################## 2022-08-03T23:28:37.724487+02:00 ALTER SYSTEM ARCHIVE LOG 2022-08-03T23:28:38.460517+02:00 NET (PID:512199): Archived Log entry 130 added for B-1111517533.T-2.S-40 ID 0x7f48ce247857 LAD:1 [krse.c:4934] 2022-08-03T23:28:38.628458+02:00 Thread 1 advanced to log sequence 49 (LGWR switch), current SCN: 11795760 Current log# 1 seq# 49 mem# 0: +DATA/ORADBDC2/ONLINELOG/group_1.289.1111517533 Current log# 1 seq# 49 mem# 1: +RECO/ORADBDC2/ONLINELOG/group_1.308.1111517543 2022-08-03T23:28:38.839276+02:00 NET (PID:512199): Archived Log entry 132 added for B-1111517533.T-1.S-48 ID 0x7f48ce247857 LAD:1 [krse.c:4934] ############################## # # Alertlog at DC1 # ############################## 2022-08-03T23:28:38.459226+02:00 rfs (PID:505753): Archived Log entry 50 added for B-1111517533.T-2.S-40 ID 0xce247857 LAD:1 [krsp.c:1256] rfs (PID:505753): No SRLs created [krsk.c:4664] 2022-08-03T23:28:38.576731+02:00 rfs (PID:505753): Opened log for DBID:3458508890 B-1111517533.T-2.S-41 [krsr.c:18247] 2022-08-03T23:28:38.964783+02:00 rfs (PID:505769): Archived Log entry 51 added for B-1111517533.T-1.S-48 ID 0xce247857 LAD:1 [krsp.c:1256] rfs (PID:505769): No SRLs created [krsk.c:4664] 2022-08-03T23:28:39.026570+02:00 rfs (PID:505769): Opened log for DBID:3458508890 B-1111517533.T-1.S-49 [krsr.c:18247]
And at the broker, we can see that everything is fine and the standby site for the pdb is orddbdc1 and the primary is the oradbdc2. And the standby is receiving and applying archivelogs correctly:
DGMGRL> show pluggable database PDBDC2 at oradbdc1; Pluggable database 'pdbdc2' at database 'oradbdc1' Data Guard Role: Physical Standby Con_ID: 5 Source: con_id 4 at oradbdc2 Transport Lag: 0 seconds (computed 43 seconds ago) Intended State: APPLY-ON Apply State: Running Apply Instance: oradbdc11 Average Apply Rate: 1820 KByte/s Real Time Query: OFF Pluggable Database Status: SUCCESS DGMGRL>
Above you can see that everything is OK and the Apply State is in Running mode. If you saw some error like “ORA-16766: Redo Apply is stopped” and the Apply State in “Not Running” mode, please change the state to APPLY-ON:
DGMGRL> edit pluggable database PDBDC2 at oradbdc1 set state ='APPLY-ON'; Succeeded. DGMGRL>
This is even more important because after you bounce the database (planned restart or not), is possible that the apply return in a bad state. So, always remember to check the apply mode at the broker.
Switchover PDB
With everything configured we can play with the switchover command. This is the reason for the DG PDB, which allows switching just one pdb while others remain in the same place. Here is my scenario I will switchover the PDBDC2 from the radbdc2 (from DC2) to oradbdc1 (DC1):
Please check ISSUE #4 at the end of the post to check one detail about the switchover.
The command is quite simple:
DGMGRL> switchover to pluggable database pdbdc2 at oradbdc1; Verifying conditions for Switchover... Connected to "oradbdc2" Source pluggable database is 'PDBDC2' at database 'oradbdc2' Performing switchover NOW, please wait... Closing pluggable database 'PDBDC2'... Switching 'PDBDC2' to standby role... Connected to "oradbdc1" Waiting for 'PDBDC2' to recover all redo data... Stopping recovery at 'PDBDC2'... Converting 'PDBDC2' to primary role... Opening new primary 'PDBDC2'... Connected to "oradbdc2" Waiting for redo data from new primary 'PDBDC2'... Starting recovery at new standby 'PDBDC2'... Switchover succeeded, new primary is "PDBDC2" DGMGRL>
As explained in ISSUE #4, the command can be stuck at “Waiting for ‘PDBDC2’ to recover all redo data…”. If this occurs, you need to connect to the primary database of the pdb and execute the “alter system archive log current” to force the generation of a new archivelog. This is needed because the synchronization mode is in MaxPerformance and for some reason, the process of switchover does not send the command to generate the new archive at all (current primary) instances. So, with the new archive, the standby receives and applies the last redo.
So, after the switchover the current status is that PDBDC2 is running at DC1 as your primary site and DC2 as your standby site:
DGMGRL> show pluggable database PDBDC2 at oradbdc1;
Pluggable database 'pdbdc2' at database 'oradbdc1'
Data Guard Role: Primary
Con_ID: 5
Active Target: con_id 4 at oradbdc2
Pluggable Database Status:
SUCCESS
DGMGRL> show pluggable database PDBDC2 at oradbdc2;
Connected to "oradbdc2"
Pluggable database 'pdbdc2' at database 'oradbdc2'
Data Guard Role: Physical Standby
Con_ID: 4
Source: con_id 5 at oradbdc1
Transport Lag: 0 seconds (computed 23 seconds ago)
Intended State: APPLY-ON
Apply State: Running
Apply Instance: oradbdc22
Average Apply Rate: (unknown)
Real Time Query: OFF
Pluggable Database Status:
SUCCESS
DGMGRL>
DGMGRL> show configuration;
Configuration - dc1
Protection Mode: MaxPerformance
Members:
oradbdc1 - Primary database
oradbdc2 - Primary database in dc2 configuration
Data Guard for PDB: Enabled in SOURCE role
Configuration Status:
SUCCESS (status updated 2 seconds ago)
DGMGRL> show configuration dc1
Configuration - dc1
Protection Mode: MaxPerformance
Members:
oradbdc1 - Primary database
oradbdc2 - Primary database in dc2 configuration
Data Guard for PDB: Enabled in SOURCE role
Configuration Status:
SUCCESS (status updated 8 seconds ago)
DGMGRL> show configuration dc2
Configuration - dc2
Primary Database: oradbdc2
Configuration dc2 Status:
SUCCESS
DGMGRL>
As you can see above (compared with previous outputs) the switchover just changed the status for the PDBDC2. All the rest continue the same.
Always remember to check the Apply State of the pdb after the switchover and even more important if you restarted some of the instances (or the entire site).
DGPDB
The possibility protects individually your PDBS is a nice feature. As explained before it is more linked with OCI Cloud than On-Prem environments. But every client has its own configuration and different setup and this can fit for On-Prem as well.
Just remember that (as an entire 21c) it is an innovative release and features need to be tested before you think to put it in production or not. This is the case for DGPDB, it still has some details that need to be polished (or improved) to help the configuration and also to be fully operational.
I recommend you to read the full documentation of DG for 21.7 and the post from Ludovico (Oracle PM for DG) to understand how the DGPDB works.
ISSUE #1
The first issue that I found was related to the remote connections between databases. If you think of normal DG situations, the dn_name for the databases is the same, so, the entries in your password files are still valid when you copy from one site to another. Here, for DG PDB, they can have completely different names and one password file will not be ready for another. I tried to use the “share” password files but does not help as well.
Below you can see that my remote connection between sites was working properly. The SYS (and even SYSDG) was working and all nodes (for all sites) can connect using the password (with tnsname and ezconnect):
[oracle@oel8n1-21c ~]$ sqlplus sys/oracle@oradbdc2 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 20:12:17 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 [oracle@oel8n1-21c ~]$ [oracle@oel8n1-21c ~]$ sqlplus sys/oracle@oradbdc1 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 20:12:21 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 [oracle@oel8n1-21c ~]$ [oracle@oel8n1-21c ~]$ sqlplus sys/oracle@//oel8s-21c-scan:1521/oradbdc2 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Tue Aug 2 19:52:47 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 ############################## # # At DC2 # ############################## [oracle@oel8sn1-21c ~]$ sqlplus sys/oracle@oradbdc2 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 20:12:33 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 [oracle@oel8sn1-21c ~]$ sqlplus sys/oracle@oradbdc1 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jul 31 20:12:40 2022 Version 21.7.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.7.0.0.0 [oracle@oel8sn1-21c ~]$
And when I tried to add the database at dgmgrl I received the “ORA-1017: invalid username/password; logon denied error”. Look below that I used the trace to support to try to troubleshoot but nothing in special was found:
DGMGRL> add configuration dc2 connect identifier is oradbdc2;
[W000 2022-08-02T19:48:59.302+02:00] Adding a configuration [BEGIN :status := DBMS_DRS.ADD_CONFIGURATION('oradbdc2', 'dc2', 'PDB'); END;].
ORA-1017: invalid username/password; logon denied
DGMGRL>
DGMGRL> add configuration connect identifier is '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8s-21c-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradbdc2)(SERVER=DEDICATED)))';
[W000 2022-08-02T19:44:48.240+02:00] Adding a configuration [BEGIN :status := DBMS_DRS.ADD_CONFIGURATION('(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8s-21c-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradbdc2)(SERVER=DEDICATED)))', '', 'PDB'); END;].
ORA-1017: invalid username/password; logon denied
DGMGRL>
DGMGRL> add configuration dc2 connect identifier is '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8s-21c-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradbdc2)(SERVER=DEDICATED)))';
[W000 2022-08-02T19:49:27.689+02:00] Adding a configuration [BEGIN :status := DBMS_DRS.ADD_CONFIGURATION('(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8s-21c-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradbdc2)(SERVER=DEDICATED)))', 'dc2', 'PDB'); END;].
ORA-1017: invalid username/password; logon denied
DGMGRL>
DGMGRL> add configuration dc2 connect identifier is 'oel8s-21c-scan:1521/oradbdc2';
[W000 2022-08-02T19:54:21.650+02:00] Adding a configuration [BEGIN :status := DBMS_DRS.ADD_CONFIGURATION('oel8s-21c-scan:1521/oradbdc2', 'dc2', 'PDB'); END;].
ORA-1017: invalid username/password; logon denied
DGMGRL>
So, maybe I made something wrong during my configuration, missed some configuration, or not understood some concept here. I tried several times: connected directly at dgmgrl (dgmgrl /), with username password (dgmgrl sys/oracle), with tnsnames (dgmgrl sys/oracle@TNS), or the same with SYSDG user. Nothing worked. The only method that worked was using a wallet because it pick up the username and password from another way.
ISSUE #2
Issue #2 is related to the ADD PLUGGABLE DATABASE that fails with the error “ORA-11402: Oracle Data Guard site entry not found”. Maybe this occurs because at my DC2 site I not add the configuration for DC1 too. Unfortunately, this is not clear in the documentation (if is needed to do or not). Or can be something else.
You can see here in the file Add-Pluggable-Database-Full-Output.txt the output (generated with trace_level set to support) from the moment that I executed the add pluggable database command that the error appears when it connects to the target database and running the “create pluggable database PDBDC2 as standby from PDBDC2 at oradbdc2”.
The simple solution was executing again the same command at dgmgrl and it completes successfully.
ISSUE #3
Issue #3 is related to the way that the pdb is registered at the standby site. As I checked, the files (of the pdb) are not registered at the controlfile. So, the traditional way to restore the datafile (from one site to another) does not work.
Look below that I am connected at the standby site for the pdb (oradbdc1 – DC1) and check that it appears when I execute the “show pdbs”, but does not appears when ai go to the cdb_datafiles or even the v$datafile:
[oracle@oel8n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 3 23:16:21 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.7.0.0.0
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBDC1 READ WRITE NO
5 PDBDC2 MOUNTED
SQL>
SQL> set linesize 255
SQL> col FILE_NAME format a120
SQL> select FILE_NAME from cdb_data_files where con_id = 5;
no rows selected
SQL>
SQL> select file#, NAME from v$datafile where con_id = 5;
no rows selected
SQL>
So, when I try to restore from service using rman, it says that they don’t know the file:
[oracle@oel8n1-21c ~]$ rman target /
Recovery Manager: Release 21.0.0.0.0 - Production on Wed Aug 3 23:17:58 2022
Version 21.7.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBDC1 (DBID=2977563181)
RMAN>
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORADBDC1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1370 SYSTEM YES +DATA/ORADBDC1/DATAFILE/system.287.1111517335
3 840 SYSAUX NO +DATA/ORADBDC1/DATAFILE/sysaux.288.1111517379
4 450 UNDOTBS1 YES +DATA/ORADBDC1/DATAFILE/undotbs1.289.1111517405
5 290 PDB$SEED:SYSTEM NO +DATA/ORADBDC1/C8209F27C6B26005E053362EE80AE60E/DATAFILE/system.295.1111518183
6 360 PDB$SEED:SYSAUX NO +DATA/ORADBDC1/C8209F27C6B26005E053362EE80AE60E/DATAFILE/sysaux.296.1111518183
7 5 USERS NO +DATA/ORADBDC1/DATAFILE/users.290.1111517405
8 110 PDB$SEED:UNDOTBS1 NO +DATA/ORADBDC1/C8209F27C6B26005E053362EE80AE60E/DATAFILE/undotbs1.297.1111518183
9 25 UNDOTBS2 YES +DATA/ORADBDC1/DATAFILE/undotbs2.299.1111518865
10 300 PDBDC1:SYSTEM YES +DATA/ORADBDC1/E51E3000E5CEBB1CE053460AA00A221D/DATAFILE/system.304.1111519609
11 440 PDBDC1:SYSAUX NO +DATA/ORADBDC1/E51E3000E5CEBB1CE053460AA00A221D/DATAFILE/sysaux.305.1111519609
12 110 PDBDC1:UNDOTBS1 YES +DATA/ORADBDC1/E51E3000E5CEBB1CE053460AA00A221D/DATAFILE/undotbs1.303.1111519609
13 110 PDBDC1:UNDO_2 YES +DATA/ORADBDC1/E51E3000E5CEBB1CE053460AA00A221D/DATAFILE/undo_2.307.1111519721
14 5 PDBDC1:USERS NO +DATA/ORADBDC1/E51E3000E5CEBB1CE053460AA00A221D/DATAFILE/users.308.1111519731
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 243 TEMP 32767 +DATA/ORADBDC1/TEMPFILE/temp.294.1111517479
2 35 PDB$SEED:TEMP 32767 +DATA/ORADBDC1/E51DDAE02E247DA5E053460AA00A9806/TEMPFILE/temp.298.1111518203
3 219 PDBDC1:TEMP 32767 +DATA/ORADBDC1/E51E3000E5CEBB1CE053460AA00A221D/TEMPFILE/temp.306.1111519663
RMAN>
RMAN> run{
2> set newname for pluggable database PDBDC2 to new;
3> restore pluggable database PDBDC2 from service oradbdc2;
4> }
executing command: SET NEWNAME
Starting restore at 03-AUG-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=315 instance=oradbdc11 device type=DISK
pluggable database PDBDC2 does not have any data files
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/03/2022 23:18:44
RMAN-06835: specification does not match any data file in the respository
RMAN>
And look that even the “report schema” does not show the datafiles. So, this can be a problem when doing the backup (or restore) of the database, if the files are unknown, they are not backed up.
ISSUE #4
Issue #4 is one that can leave you in a bad situation. As explained during the post the standby stays waiting for the archivelog from the primary and this never arrives (since it was not informed to generate a new one after the switchover command).
Look below that command fails with the error: “Target pluggable database ‘PDBDC2’ at database ‘oradbdc1’ has not applied all redo; cannot complete role change”:
DGMGRL> switchover to pluggable database pdbdc2 at oradbdc1; Verifying conditions for Switchover... Connected to "oradbdc2" Source pluggable database is 'PDBDC2' at database 'oradbdc2' Performing switchover NOW, please wait... Closing pluggable database 'PDBDC2'... Switching 'PDBDC2' to standby role... Connected to "oradbdc1" Waiting for 'PDBDC2' to recover all redo data... Target pluggable database 'PDBDC2' at database 'oradbdc1' has not applied all redo; cannot complete role change Failed. DGMGRL> DGMGRL> switchover to pluggable database pdbdc2 at oradbdc1; Verifying conditions for Switchover... Connected to "oradbdc2" Source pluggable database is 'PDBDC2' at database 'oradbdc2' Source pluggable database 'PDBDC2' is not open in READ WRITE mode. Failed. DGMGRL> DGMGRL> show pluggable database PDBDC2 at oradbdc1; Pluggable database 'pdbdc2' at database 'oradbdc1' Data Guard Role: Physical Standby Con_ID: 5 Source: con_id 3 at oradbdc2 Transport Lag: 0 seconds (computed 58 seconds ago) Intended State: APPLY-ON Apply State: Running Apply Instance: oradbdc11 Average Apply Rate: 96 KByte/s Real Time Query: OFF Pluggable Database Status: SUCCESS DGMGRL> show pluggable database PDBDC2 at oradbdc2; Connected to "oradbdc2" Pluggable database 'pdbdc2' at database 'oradbdc2' Data Guard Role: Physical Standby Con_ID: 3 Source: (unknown) Pluggable Database Status: ORA-16911: Redo data from new source database has not been registered. DGMGRL>
And you can see that (even worst) now both sides are defined as “Physical Standby”. As pointed at my post, if your switchover is stuck at “Waiting for ‘PDBDC2’ to recover all redo data” you need to connect at the current primary for database and execute “alter system archive log current” to generate a new archivelog, and this being transfer and applied.
Look below the alertlog of the current standby primary of the pdb (oradbdc1 – at DC2) and check that between 22:35 and 23:42 it waits doing nothing:
2022-08-03T23:35:14.608810+02:00 PDBDC2(5):TT04 (PID:489392): Media Recovery Log +RECO/ORADBDC1/ARCHIVELOG/2022_08_03/thread_1_seq_50.501.1111793617 [krd.c:9452] PDBDC2(5):TT04 (PID:489392): Media Recovery Waiting for T-2.S-42 (in transit) [krsm.c:6205] 2022-08-03T23:42:24.812496+02:00 PDBDC2(5):TT04 (PID:489392): Media Recovery Log +RECO/ORADBDC1/ARCHIVELOG/2022_08_03/thread_2_seq_42.502.1111793619 [krd.c:9452] PDBDC2(5):End of lifespan for PDB at SCN 0x0000000000b429d7 due to SWITCHOVER 2022-08-03T23:42:25.278394+02:00 PDBDC2(5):Incomplete recovery applied all redo ever generated. PDBDC2(5):Recovery completed through change 11807191 time 08/03/2022 23:35:13 2022-08-03T23:42:25.952898+02:00 ALTER PLUGGABLE DATABASE PDBDC2 RECOVER MANAGED STANDBY DATABASE CANCEL 2022-08-03T23:42:25.962198+02:00 .... (PID:493823): Managed Recovery Canceled [dbsdrv.c:16992] Completed: ALTER PLUGGABLE DATABASE PDBDC2 RECOVER MANAGED STANDBY DATABASE CANCEL ALTER PLUGGABLE DATABASE PDBDC2 COMMIT TO SWITCHOVER PDBDC2(5):Pluggable database PDBDC2 pseudo opening in read only …
And the interesting is that if you connect at the current primary it is in a close state. You can see at alertlog that pdb was closed but no archivelog was generated after that. So, the “close” and an archivelog with the “end of lifespan” for pdb never arrived:
2022-08-03T23:35:04.867884+02:00 PDBDC2(4):Pluggable database PDBDC2 closing PDBDC2(4):Increasing priority of 1 RS PDBDC2(4):JIT: pid 438699 requesting stop PDBDC2(4):Closing sequence subsystem (48992788888). PDBDC2(4):Buffer Cache flush started: 4 PDBDC2(4):Buffer Cache flush finished: 4 PDBDC2(4):queued detach DA request 0x8fd84e00 for pdb 4, ospid 438699 2022-08-03T23:35:05.480217+02:00 Domain Action Reconfiguration started (domid 4, new da inc 5, da windows 1, cluster inc 4) Instance 2 is detaching from domain 4 (lazy abort? 0, recovery member? 0) Global Resource Directory partially frozen for domain action * domain detach - domain 4 valid ? 1 Non-local Process blocks cleaned out Set master node info Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted freeing the fusion rht of pdb 4 freeing the pdb enqueue rht Domain Action Reconfiguration complete (total time 0.1 secs) 2022-08-03T23:35:05.599572+02:00 Pluggable database PDBDC2 closed Decreasing priority of 1 RS 2022-08-03T23:35:06.013872+02:00 freeing rdom 4 2022-08-03T23:35:09.896222+02:00 Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c0] because plan is already set. 2022-08-03T23:41:16.511405+02:00 Control autobackup written to DISK device handle '+RECO/ORADBDC2/AUTOBACKUP/2022_08_03/s_1111794075.438.1111794077' 2022-08-03T23:42:24.538274+02:00 Thread 2 advanced to log sequence 43 (LGWR switch), current SCN: 11814659 Current log# 3 seq# 43 mem# 0: +DATA/ORADBDC2/ONLINELOG/group_3.297.1111518715 Current log# 3 seq# 43 mem# 1: +RECO/ORADBDC2/ONLINELOG/group_3.310.1111518721 2022-08-03T23:42:24.699608+02:00
The full outputs from the alertlogs can be seen here in this file if you want to check the whole process.
Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purposes, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.”
Pingback: 21c, DG PDB, New Steps - Fernando Simon