When the DGPDB was released for 21c (at version 21.7) I wrote a blog post about how to use the feature (you can read it here). This was in August of 2022 and since that time, we got small changes and corrections, but with the update 21.12 (patch 35740258) we got new commands like “EDIT CONFIGURATION PREPARE DGPDB”.
Not just that, but Ludovico Caldara (Data Guard PM) recently wrote one blog post about new commands for Data Guard preparation that can be used with Broker. Is an evolution of the commands I covered in one previous blog post.
So, in this post, I will cover the new commands for DG PDB and the changes/improvements that appeared in the last version. It is a long post, but everything is covered here. No gaps or information are missing, all the steps, logs, and outputs are described and documented.
Previous post
I will try to cover most of the details and steps that you need to do to properly configure the DG PDB, but I recommend that you read my previous post as well. So, you can understand and compare how it was, and what changed with the new update.
Another point is that the steps described here are not documented in the official documentation for 21c. The Broker doc (today at the end of 2023) is dated from April of 2022. But the documentation from 23c is online and updated, so, it can be used as a partial guide for the process.
My environment
The environment that I am using here is:
- Two “sites” (S1 and S2).
- In each site I have one database running in RAC mode, and each RAC has two nodes.
- Storage configuration is the same for each site: DATA and RECO diskgroups.
The databases are:
- O21S1DG1: Database for site 01 (S1), and here I have the pdb S1PDB1 running.
- O21S2DG1: Database for site 02 (S2), and here I have the pdb S2PDB1 running.
You can see this below for S1:
[oracle@o8s1n1-21c ~]$ $ORACLE_HOME/OPatch/opatch lspatches
35638302;JDK BUNDLE PATCH 21.0.0.0.231017
35740265;OCW RELEASE UPDATE 21.12.0.0.0 (35740265)
35740258;Database Release Update : 21.12.0.0.231017 (35740258)
OPatch succeeded.
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ echo $ORACLE_SID
o21s1dg11
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ srvctl status database -d o21s1dg1
Instance o21s1dg11 is running on node o8s1n1-21c
Instance o21s1dg12 is running on node o8s1n2-21c
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:10:42 2023
Version 21.12.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.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S1PDB1 READ WRITE NO
SQL> select cdbdbid, connect_identifier, db_unique_name, dump(db_unique_name) from data_guard_site$;
no rows selected
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string o21s1dg1
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.12.0.0.0
[oracle@o8s1n1-21c ~]$
And for S2:
[oracle@o8s2n1-21c ~]$ $ORACLE_HOME/OPatch/opatch lspatches
35638302;JDK BUNDLE PATCH 21.0.0.0.231017
35740265;OCW RELEASE UPDATE 21.12.0.0.0 (35740265)
35740258;Database Release Update : 21.12.0.0.231017 (35740258)
OPatch succeeded.
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ echo $ORACLE_SID
o21s2dg11
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ srvctl status database -d o21s2dg1
Instance o21s2dg11 is running on node o8s2n1-21c
Instance o21s2dg12 is running on node o8s2n2-21c
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:10:43 2023
Version 21.12.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.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S2PDB1 READ WRITE NO
SQL> select cdbdbid, connect_identifier, db_unique_name, dump(db_unique_name) from data_guard_site$;
no rows selected
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string o21s2dg1
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.12.0.0.0
[oracle@o8s2n1-21c ~]$
TNSNAMES
The first step that I would like to do is adjust the tnsnames.ora file for both nodes to have the entry for each database. Remember that 21c uses the ROOH (Read Only Oracle Home as I described in a previous post), so, I edited the tnsnames from S1 (node1) and added the entry for the database at site 2:
[oracle@o8s1n1-21c ~]$ tnsping o21s1dg1
TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 25-DEC-2023 12:13:00
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 = o8s1-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = o21s1dg1)))
OK (0 msec)
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ tnsping o21s2dg1
TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 25-DEC-2023 12:13:05
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ cat $ORACLE_BASE/homes/OraDB21Home1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
O21S1DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o8s1-21c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = o21s1dg1)
)
)
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ vi $ORACLE_BASE/homes/OraDB21Home1/network/admin/tnsnames.ora
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ cat $ORACLE_BASE/homes/OraDB21Home1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
O21S1DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o8s1-21c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = o21s1dg1)
)
)
O21S2DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o8s2-21c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = o21s2dg1)
)
)
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ tnsping O21S2DG1
TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 25-DEC-2023 12:13:34
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 = o8s2-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = o21s2dg1)))
OK (0 msec)
[oracle@o8s1n1-21c ~]$
You can see above that I edited the file and tested it. So, the next step is to copy the edited file to the second node of S1, and all nodes from S2:
[oracle@o8s1n1-21c ~]$ scp /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora o8s1n2-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora tnsnames.ora 100% 521 148.3KB/s 00:00 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ scp /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora o8s2n1-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora The authenticity of host 'o8s2n1-21c (10.160.21.13)' can't be established. ECDSA key fingerprint is SHA256:vSb+vT+UHebpOlYd9OWzysszhSB//uz9fBoknODFNfA. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added 'o8s2n1-21c,10.160.21.13' (ECDSA) to the list of known hosts. oracle@o8s2n1-21c's password: tnsnames.ora 100% 521 409.4KB/s 00:00 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ scp /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora o8s2n2-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora The authenticity of host 'o8s2n2-21c (10.160.21.15)' can't be established. ECDSA key fingerprint is SHA256:vSb+vT+UHebpOlYd9OWzysszhSB//uz9fBoknODFNfA. Are you sure you want to continue connecting (yes/no/[fingerprint])? yes Warning: Permanently added 'o8s2n2-21c,10.160.21.15' (ECDSA) to the list of known hosts. oracle@o8s2n2-21c's password: tnsnames.ora 100% 521 398.6KB/s 00:00 [oracle@o8s1n1-21c ~]$
And you can even test ant S2:
[oracle@o8s2n1-21c ~]$ tnsping O21S1DG1 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 25-DEC-2023 12:14:15 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 = o8s1-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = o21s1dg1))) OK (10 msec) [oracle@o8s2n1-21c ~]$ [oracle@o8s2n1-21c ~]$ tnsping O21S2DG1 TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 25-DEC-2023 12:14:20 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 = o8s2-21c-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = o21s2dg1))) OK (10 msec) [oracle@o8s2n1-21c ~]$
Basic Data Guard and Broker config
DG PDB is still a Data Guard, so, the basic configs are still needed. So, below you can that I adjusted these parameters and details for all databases:
- dg_broker_start and dg_broker_config_file*.
- standby_file_management defined as AUTO.
- Standby redo log files for CDB level set (check the current RL size and use the same one).
For the database at S1:
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:14:31 2023
Version 21.12.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.12.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/dr1o21s1dg1.dat
dg_broker_config_file2 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr2o21s1dg1.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
SQL> alter system set dg_broker_config_file1='+DATA/O21S1DG1/dr1o21s1dg1.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_config_file2='+RECO/O21S1DG1/dr2o21s1dg1.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_start=TRUE scope = both sid = '*' ;
System altered.
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> 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 the database at S2:
[oracle@o8s2n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:16:25 2023
Version 21.12.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.12.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/dr1o21s2dg1.dat
dg_broker_config_file2 string /u01/app/oracle/homes/OraDB21H
ome1/dbs/dr2o21s2dg1.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
SQL> alter system set dg_broker_config_file1='+DATA/O21S2DG1/dr1o21s2dg1.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_config_file2='+RECO/O21S2DG1/dr2o21s2dg1.dat' scope = both sid = '*' ;
System altered.
SQL> alter system set dg_broker_start=TRUE scope = both sid = '*' ;
System altered.
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> 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>
Wallet and Data Guard connection
The DG PDB uses a special way to connect the broker configurations. It differs from traditional Data Guard because the SYS password from both sites can be different and they do not share the password file. So, the way to do that is using wallets.
The most important point here is that the credential name inside the wallet NEEDS TO BE THE SAME as your connections string (TNSNAMES or EZCONNECT).
So, I started for S1, creating autologin wallet and adding the credentials (compare the names and the TNS entries that I made some steps ago):
[oracle@o8s1n1-21c ~]$ mkdir -p $ORACLE_BASE/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ mkstore -wrl $ORACLE_BASE/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 -createALO Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved. [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ mkstore -wrl $ORACLE_BASE/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 -createCredential o21s1dg1 sys oracle Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved. [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ mkstore -wrl $ORACLE_BASE/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 -createCredential o21s2dg1 sys oracle Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved. [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ mkstore -wrl $ORACLE_BASE/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 -listCredential Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved. List credential (index: connect_string username) 2: o21s2dg1 sys 1: o21s1dg1 sys [oracle@o8s1n1-21c ~]$
To allow this wallet to be used at the connection level I adjusted the sqlnet.ora file at node 1 from site 1:
[oracle@o8s1n1-21c ~]$ ls -l $ORACLE_BASE/homes/OraDB21Home1/network/admin/sqlnet.ora
ls: cannot access '/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora': No such file or directory
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ vi $ORACLE_BASE/homes/OraDB21Home1/network/admin/sqlnet.ora
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$
[oracle@o8s1n1-21c ~]$ cat $ORACLE_BASE/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/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1)
)
)
[oracle@o8s1n1-21c ~]$
After I tested the connections from node1 of site 1 to all databases. You can see that I did not specify the password and I was able to connect to databases:
[oracle@o8s1n1-21c ~]$ sqlplus /@o21s1dg1 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:22:04 2023 Version 21.12.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.12.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.12.0.0.0 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ sqlplus /@o21s2dg1 as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:22:10 2023 Version 21.12.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.12.0.0.0 SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.12.0.0.0 [oracle@o8s1n1-21c ~]$
Everything is working with the wallet, then I copied wallet and sqlnet.ora to the second node of s1, and just the wallet to all nodes of the second site:
[oracle@o8s1n1-21c ~]$ scp -r /u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 o8s1n2-21c:/u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 cwallet.sso.lck 100% 0 0.0KB/s 00:00 cwallet.sso 100% 941 960.9KB/s 00:00 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ scp $ORACLE_BASE/homes/OraDB21Home1/network/admin/sqlnet.ora o8s1n2-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora sqlnet.ora 100% 241 258.4KB/s 00:00 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ scp -r /u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 o8s2n1-21c:/u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s2dg1 oracle@o8s2n1-21c's password: cwallet.sso.lck 100% 0 0.0KB/s 00:00 cwallet.sso 100% 941 785.9KB/s 00:00 [oracle@o8s1n1-21c ~]$ [oracle@o8s1n1-21c ~]$ scp -r /u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s1dg1 o8s2n2-21c:/u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s2dg1 oracle@o8s2n2-21c's password: cwallet.sso.lck 100% 0 0.0KB/s 00:00 cwallet.sso 100% 941 804.0KB/s 00:00 [oracle@o8s1n1-21c ~]$
Since the wallet is there for the second site (S2), I could adjust the sqlnet.ora and test it. You can notice that when I copied from S1 to S2 I adjusted the folder name (at destination), so, this needs to be correctly informed in the sqlnet file. Below you see that I adjusted the sqlnet.ora, tested the connections and after copied the file to the second node of S2:
[oracle@o8s2n1-21c ~]$ ls -l /u01/app/oracle/homes/OraDB21Home1/dbs/dgwallet_o21s2dg1
total 4
-rw------- 1 oracle oinstall 941 Dec 25 12:23 cwallet.sso
-rw------- 1 oracle oinstall 0 Dec 25 12:23 cwallet.sso.lck
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ vi /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-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/homes/OraDB21Home1/dbs/dgwallet_o21s2dg1)
)
)
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ sqlplus /@o21s1dg1 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:25:17 2023
Version 21.12.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.12.0.0.0
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.12.0.0.0
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ sqlplus /@o21s2dg1 as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:25:24 2023
Version 21.12.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.12.0.0.0
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.12.0.0.0
[oracle@o8s2n1-21c ~]$
[oracle@o8s2n1-21c ~]$ scp /u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora o8s2n2-21c:/u01/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
sqlnet.ora 100% 242 211.1KB/s 00:00
[oracle@o8s2n1-21c ~]$
Database adjust
Another point that is recommended to be adjusted is the logging properties for the database. They need to be in archive move, but also is recommended to have the flashback enabled and the force logging too. Below you can see that I adjusted the database running in S1:
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:26:13 2023 Version 21.12.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.12.0.0.0 SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select FORCE_LOGGING from v$database; FORCE_LOGGING --------------------------------------- YES SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.12.0.0.0 [oracle@o8s1n1-21c ~]$
And I made the same for S2:
[oracle@o8s2n1-21c ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:26:56 2023 Version 21.12.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.12.0.0.0 SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> select FORCE_LOGGING from v$database; FORCE_LOGGING --------------------------------------- NO SQL> alter database flashback on; Database altered. SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.12.0.0.0 [oracle@o8s2n1-21c ~]$
Broker, DGMGRL, and CONFIG
The next step is to add the config. Check the details over the steps to add correctly and in the correct place. You will need to run commands in both databases.
So, at node 1 from the database running at site 1, you connect at dgmgrl and create the config:
[oracle@o8s1n1-21c ~]$ dgmgrl sys/oracle@o21s1dg1
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Mon Dec 25 12:27:31 2023
Version 21.12.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "o21s1dg1"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> show all
trace_level USER
echo OFF
time OFF
observerconfigfile = observer.ora
Database version is 21.12.0.0.0.
Oracle SID is o21s1dg12.
Connected as SYSDBA to instance o21s1dg12 of o21s1dg1.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>
DGMGRL> create configuration S1 primary database is o21s1dg1 connect identifier is o21s1dg1;
Connected to "o21s1dg1"
Configuration "s1" created with primary database "o21s1dg1"
DGMGRL>
DGMGRL>
DGMGRL> show configuration verbose;
Configuration - s1
Protection Mode: MaxPerformance
Members:
o21s1dg1 - 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 = 'o21s1dg1_CFG'
ConfigurationSimpleName = 's1'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
And you do the same to the other database. But remember that here you are locally connected at one node running the database. So, doing for the S2:
[oracle@o8s2n1-21c ~]$ dgmgrl sys/oracle@o21s2dg1
DGMGRL for Linux: Release 21.0.0.0.0 - Production on Mon Dec 25 12:27:56 2023
Version 21.12.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "o21s2dg1"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> show all
trace_level USER
echo OFF
time OFF
observerconfigfile = observer.ora
Database version is 21.12.0.0.0.
Oracle SID is o21s2dg11.
Connected as SYSDBA to instance o21s2dg11 of o21s2dg1.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>
DGMGRL>
DGMGRL> create configuration S2 primary database is o21s2dg1 connect identifier is o21s2dg1;
Connected to "o21s2dg1"
Configuration "s2" created with primary database "o21s2dg1"
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - s2
Protection Mode: MaxPerformance
Members:
o21s2dg1 - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> show configuration verbose;
Configuration - s2
Protection Mode: MaxPerformance
Members:
o21s2dg1 - 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 = 'o21s2dg1_CFG'
ConfigurationSimpleName = 's2'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
ADD CONFIGURATION
The next step is executed only when using DG PDB. Basically, you go to the broker that will be the standby (at the first moment) and you will add the configuration from the other site.
In my environment I went to S1 and added S2:
DGMGRL> add configuration s2 connect identifier is o21s2dg1; Configuration s2 added. DGMGRL>
The base command is:
ADD CONFIGURATION [<configuration name>] CONNECT IDENTIFIER IS <connect identifier>;
So, here the broker will use the wallet credential to connect and add the configuration (from a remote site). As you can see above there is no (and not yet) way to pass the password as a parameter, so, some kind of way to collect the password and use it (the wallet is the way) is needed. And it is the official/documented way to do that. If you read my previous blog post, you can see that I tested and got the documented ISSUE #1. As I explained before, the “connect identifier” links TNS entry or EZXCONNECT. So, your credential name inside the wallet needs to be the same as your connect identifier.
After that, you can check the configs (remember that you are at the broker for S1):
DGMGRL> show configuration verbose s1;
Configuration - s1
Protection Mode: MaxPerformance
Members:
o21s1dg1 - Primary database
o21s2dg1 - Primary database in s2 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 = 'o21s1dg1_CFG'
ConfigurationSimpleName = 's1'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> show configuration verbose s2;
Configuration - s2
Primary Database: o21s2dg1
Properties:
DGConnectIdentifier = ''
LogShipping = 'ON'
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
Configuration s2 Status:
SUCCESS
DGMGRL>
The next step is to enable the configuration. We do this in the broker where you added the config because this will force it to connect in both configurations and enable it.
DGMGRL> enable configuration all;
Succeeded.
DGMGRL>
DGMGRL> show configuration verbose;
Configuration - s1
Protection Mode: MaxPerformance
Members:
o21s1dg1 - Primary database
o21s2dg1 - Primary database in s2 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 = 'o21s1dg1_CFG'
ConfigurationSimpleName = 's1'
DrainTimeout = '0'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL>
EDIT CONFIGURATION PREPARE DGPDB
Here we have the new command that will help to prepare and adjust (automatically) the config. In my previous post, you can see that when you add the pluggable database it will ask you for a password (and even the command will fail). But this is not the case now.
The command is exactly this “EDIT CONFIGURATION PREPARE DGPDB;” and the execution is:
DGMGRL> EDIT CONFIGURATION PREPARE DGPDB; Enter password for DGPDB_INT account at o21s1dg1: Enter password for DGPDB_INT account at o21s2dg1: Prepared Data Guard for Pluggable Database at o21s2dg1. Prepared Data Guard for Pluggable Database at o21s1dg1. DGMGRL>
So, what’s happened above? The command will prompt you the password for the new DGPDB_INT (for both sites and he can be even different – I recommend the same). This user (as documented) is used for “The DGPDB_INT user account is used by the database server when making connections to other sites involved in the DG PDB configuration. Typically this occurs during creation and switchover of a standby PDB.” and “Along with the connect identifier for the remote site, the credential and the DGPDB_INT account are used to make connections to the remote site when required.”.
The new command will prepare everything for you before you add the pluggable database. Unlock the user and store properly the password.
ADD PLUGGABLE DATABASE
After adjusting the config, the pluggable database can be added (again, remember that you are connected at the broker from the database running at S1, the target/standby destination from the pluggable database):
DGMGRL> ADD PLUGGABLE DATABASE s2pdb1 AT o21s1dg1 SOURCE IS s2pdb1 AT o21s2dg1 PDBFILENAMECONVERT IS "'o21s2dg1','o21s1dg1'"; Connected to "o21s2dg1" Connected to "o21s1dg1" Pluggable Database "S2PDB1" added DGMGRL>
Details from the command above:
- The pdb name: I added the S2PDB1 with the same name (you can specify any name). The important here is if you change the name of pdb, and do not use OFA (filesystem by example), remember to properly define the pdbfilenameconvert.
- AT: The first “AT” indicates the target (where) the pdb will be added.
- SOURCE IS: This means the source of the PDB, here it is the S2PDB1 coming from the S2.
- PDBFILENAMECONVER: You can use it to specify the name conversation (think in the future when you add one tablespace). It works the same way as db_file_name_convert for traditional Data Guard. In this post, since the storage has the same diskgroup definition, I am just converting the pdb name.
So, not we can see the pdb was added as part of DG PDB:
DGMGRL> SHOW PLUGGABLE DATABASE ALL at o21s1dg1; Pluggable database 'S1PDB1' at database 'o21s1dg1' Data Guard Role: Not Protected Pluggable database 'S2PDB1' at database 'o21s1dg1' Data Guard Role: Physical Standby Con_ID: 4 Source: con_id 3 at o21s2dg1 Transport Lag: 0 seconds (computed 64 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>
Above you can see that I used all, but you can use the pdb name like this:
DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s1dg1; Pluggable database 's2pdb1' at database 'o21s1dg1' Data Guard Role: Physical Standby Con_ID: 4 Source: con_id 3 at o21s2dg1 Transport Lag: 0 seconds (computed 44 seconds ago) Intended State: APPLY-ON Apply State: Not Running Pluggable Database Status: ORA-16766: Redo Apply is stopped DGMGRL>
Import details here. You can see that pdb was added but we have an error, and this is the expected behavior. Is designed to be like that. Because it will give you the possibility to copy the datafiles of pdb from source to target using the method that works better for you, which can be rman (imagine a huge pdb), cp, or reading from tape. The other detail is that the command tells you the con_id for each place (primary/standby).
To fix the error we need to understand what is happening. So, looking at the alertlog of the target database (where we added the pdb) we can see this:
create pluggable database S2PDB1 as standby from S2PDB1 at o21s2dg1 file_name_convert=('o21s2dg1','o21s1dg1')
2023-12-25T12:38:41.305780+01:00
****************************************************************
Pluggable Database S2PDB1 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
local undo-1, localundoscn-0x000000000000010a
****************************************************************
2023-12-25T12:38:41.539003+01:00
S2PDB1(4):ALTER SYSTEM SET _dgpdb_file_name_convert='o21s2dg1','o21s1dg1' SCOPE=SPFILE PDB='S2PDB1';
*****************************************
WARNING: The converted filename '+DATA/o21s1dg1/0d53b48faa587d52e0630d15a00ad47b/datafile/system.264.1156503933'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/o21s1dg1/0d53b48faa587d52e0630d15a00ad47b/datafile/sysaux.282.1156503931'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/o21s1dg1/0d53b48faa587d52e0630d15a00ad47b/datafile/undotbs1.280.1156503931'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_11.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/o21s1dg1/0d53b48faa587d52e0630d15a00ad47b/datafile/undo_2.260.1156503991'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
*****************************************
WARNING: The converted filename '+DATA/o21s1dg1/0d53b48faa587d52e0630d15a00ad47b/datafile/users.278.1156503995'
is an ASM fully qualified filename.
Changing the filename to '+DATA/MUST_RENAME_THIS_DATAFILE_13.4294967295.4294967295'.
Please rename it accordingly.
*****************************************
Completed: create pluggable database S2PDB1 as standby from S2PDB1 at o21s2dg1 file_name_convert=('o21s2dg1','o21s1dg1')
This tells me that was added but datafiles are not there. If I connect at the target CDB I can check all the config and notice that only the pdb itself knows the datafiles:
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:46:24 2023
Version 21.12.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.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S1PDB1 READ WRITE NO
4 S2PDB1 MOUNTED
SQL> set linesize 255
SQL> col FILE_NAME format a120
SQL> select FILE_NAME from cdb_data_files where con_id = 4;
no rows selected
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 'o21s2dg1','o21s1dg1' 2972777329
SQL> set linesize 255
SQL> col name format a100
SQL> select file#, name, con_id from v$datafile order by con_id, file#;
FILE# NAME CON_ID
---------- ---------------------------------------------------------------------------------------------------- ----------
1 +DATA/O21S1DG1/DATAFILE/system.260.1156499949 1
3 +DATA/O21S1DG1/DATAFILE/sysaux.264.1156499967 1
5 +DATA/O21S1DG1/DATAFILE/undotbs1.269.1156499981 1
7 +DATA/O21S1DG1/DATAFILE/undotbs2.284.1156500007 1
8 +DATA/O21S1DG1/DATAFILE/users.257.1156500009 1
2 +DATA/O21S1DG1/0D52C644598FD5D4E0630315A00A0DC5/DATAFILE/system.262.1156499959 2
4 +DATA/O21S1DG1/0D52C644598FD5D4E0630315A00A0DC5/DATAFILE/sysaux.266.1156499973 2
6 +DATA/O21S1DG1/0D52C644598FD5D4E0630315A00A0DC5/DATAFILE/undotbs1.271.1156499983 2
9 +DATA/O21S1DG1/0D53AFBB3DE67E64E0630315A00ADFE0/DATAFILE/system.289.1156503851 3
10 +DATA/O21S1DG1/0D53AFBB3DE67E64E0630315A00ADFE0/DATAFILE/sysaux.280.1156503851 3
11 +DATA/O21S1DG1/0D53AFBB3DE67E64E0630315A00ADFE0/DATAFILE/undotbs1.279.1156503851 3
FILE# NAME CON_ID
---------- ---------------------------------------------------------------------------------------------------- ----------
12 +DATA/O21S1DG1/0D53AFBB3DE67E64E0630315A00ADFE0/DATAFILE/undo_2.270.1156503895 3
13 +DATA/O21S1DG1/0D53AFBB3DE67E64E0630315A00ADFE0/DATAFILE/users.281.1156503897 3
13 rows selected.
SQL>
SQL> alter session set container = S2PDB1;
Session altered.
SQL> select file#, name, con_id from v$datafile order by con_id, file#;
FILE# NAME CON_ID
---------- ---------------------------------------------------------------------------------------------------- ----------
14 +DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295 4
15 +DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295 4
16 +DATA/MUST_RENAME_THIS_DATAFILE_11.4294967295.4294967295 4
17 +DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295 4
18 +DATA/MUST_RENAME_THIS_DATAFILE_13.4294967295.4294967295 4
SQL>
Above you can see that the v$database from cdb doesn’t know the datafiles from the new pdb. It is important to connect at the source and check the files that we need to copy:
[oracle@o8s2n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:48:34 2023
Version 21.12.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.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S2PDB1 READ WRITE NO
SQL> set linesize 255
SQL> col file_name format a100
SQL> select file_id, file_name from cdb_data_files where con_id = 3;
FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
9 +DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/system.264.1156503933
10 +DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/sysaux.282.1156503931
11 +DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undotbs1.280.1156503931
12 +DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undo_2.260.1156503991
13 +DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/users.278.1156503995
SQL>
Here, look at the CON_ID, they are different in each database (and the numbers are the ones shown in the broker too). So, we need to do what documentation tells us to do (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.”
Copying Datafiles
Personally, I prefer to use RMAN because this allows me to use different ways and do some tuning. With rman, I can specify the number of channels, the section size, and parallelization. Things that I can’t do easily copying with cp by example. And to do the copy is not needed (as in the past) disable the config from the broker side.
So, as we saw before I need to copy datafiles from 9 to 13 from the source. To do that with rman I used:
[oracle@o8s1n1-21c ~]$ rman target sys/oracle@O21S2DG1 auxiliary sys/oracle@O21S1DG1
Recovery Manager: Release 21.0.0.0.0 - Production on Mon Dec 25 12:56:31 2023
Version 21.12.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
connected to target database: O21S2DG1 (DBID=1247569180)
connected to auxiliary database: O21S1DG1 (DBID=2394073882)
RMAN> run{
2> allocate channel d1 type disk ;
3> backup as copy reuse datafile 9,10,11,12,13 auxiliary format '+DATA';
4> }
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=316 instance=o21s2dg12 device type=DISK
Starting backup at 25/12/2023 12:56:59
channel d1: starting datafile copy
input datafile file number=00009 name=+DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/system.264.1156503933
output file name=+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/system.276.1156510623 tag=TAG20231225T125700
channel d1: datafile copy complete, elapsed time: 00:00:07
channel d1: starting datafile copy
input datafile file number=00010 name=+DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/sysaux.282.1156503931
output file name=+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/sysaux.265.1156510629 tag=TAG20231225T125700
channel d1: datafile copy complete, elapsed time: 00:00:07
channel d1: starting datafile copy
input datafile file number=00011 name=+DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undotbs1.280.1156503931
output file name=+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undotbs1.263.1156510635 tag=TAG20231225T125700
channel d1: datafile copy complete, elapsed time: 00:00:07
channel d1: starting datafile copy
input datafile file number=00012 name=+DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undo_2.260.1156503991
output file name=+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undo_2.261.1156510643 tag=TAG20231225T125700
channel d1: datafile copy complete, elapsed time: 00:00:07
channel d1: starting datafile copy
input datafile file number=00013 name=+DATA/O21S2DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/users.278.1156503995
output file name=+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/users.267.1156510649 tag=TAG20231225T125700
channel d1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25/12/2023 12:57:30
released channel: d1
RMAN> exit
Recovery Manager complete.
[oracle@o8s1n1-21c ~]$
The details here:
- My target is the O21S2DG1at S2 because is where I need to connect to read the files.
- Auxiliary is the O21S1DG1 at S1 because it is the destination of my backups.
- BACKUP AS COPY, so, the datafiles specified will be copied AS IS.
- AUXILIARY FORMAT defined the destination (so I don’t need to specify newnames since is done automatically).
- From the output, you can see source and target datafilenames (and the conversion as well).
Now to fix the names for the pdb we need to rename and 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:
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 12:58:15 2023
Version 21.12.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.12.0.0.0
SQL> alter session set container = S2PDB1;
Session altered.
SQL> set linesize 255
SQL> col name format a100
SQL> select file#, name, con_id from v$datafile order by con_id, file#;
FILE# NAME CON_ID
---------- ---------------------------------------------------------------------------------------------------- ----------
14 +DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295 4
15 +DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295 4
16 +DATA/MUST_RENAME_THIS_DATAFILE_11.4294967295.4294967295 4
17 +DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295 4
18 +DATA/MUST_RENAME_THIS_DATAFILE_13.4294967295.4294967295 4
SQL>
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_9.4294967295.4294967295' to '+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/system.276.1156510623';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_10.4294967295.4294967295' to '+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/sysaux.265.1156510629';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_11.4294967295.4294967295' to '+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undotbs1.263.1156510635';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_12.4294967295.4294967295' to '+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undo_2.261.1156510643';
Database altered.
SQL> alter database rename file '+DATA/MUST_RENAME_THIS_DATAFILE_13.4294967295.4294967295' to '+DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/users.267.1156510649';
Database altered.
SQL> select file#, name, con_id from v$datafile order by con_id, file#;
FILE# NAME CON_ID
---------- ---------------------------------------------------------------------------------------------------- ----------
14 +DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/system.276.1156510623 4
15 +DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/sysaux.265.1156510629 4
16 +DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undotbs1.263.1156510635 4
17 +DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/undo_2.261.1156510643 4
18 +DATA/O21S1DG1/0D53B48FAA587D52E0630D15A00AD47B/DATAFILE/users.267.1156510649 4
SQL>
The new names you pick up from the rman output and use it. Again, just check and do the correct link between the “file number” from rman and the “file#” that you need to rename. Don’t make mistakes here.
Check and adjust the state
After fixing the datafile we can check the state of the pdb and fix in case:
DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s1dg1; Pluggable database 's2pdb1' at database 'o21s1dg1' Data Guard Role: Physical Standby Con_ID: 4 Source: con_id 3 at o21s2dg1 Transport Lag: 0 seconds (computed 44 seconds ago) Intended State: APPLY-ON Apply State: Not Running Pluggable Database Status: ORA-16766: Redo Apply is stopped DGMGRL>
So, you can see that the error is still there, but you can see the “Apply State” is not running. So, we can enable it:
DGMGRL> EDIT PLUGGABLE DATABASE S2PDB1 at o21s1dg1 SET STATE = 'APPLY-ON'; Succeeded. DGMGRL>
After doing this we can see at alertlog that the recovery process for the pdb started:
2023-12-25T13:02:22.746586+01:00 alter pluggable database S2PDB1 recover managed standby database disconnect alter pluggable database "S2PDB1" close instances=all Completed: alter pluggable database "S2PDB1" close instances=all 2023-12-25T13:02:22.839128+01:00 .... (PID:149783): Requesting Managed Recovery process for PDBID:4 [krsm.c:1352] 2023-12-25T13:02:22.853832+01:00 TT04 (PID:162320): Background Managed Recovery process started [krsm.c:1697] 2023-12-25T13:02:22.866247+01:00 S2PDB1(4):Serial Media Recovery started 2023-12-25T13:02:22.890291+01:00 .... (PID:97424): PDBID:4 Managed Recovery starting Real Time Apply [krsm.c:15931] 2023-12-25T13:02:22.944580+01:00 S2PDB1(4):max_pdb is 4 2023-12-25T13:02:23.079897+01:00 S2PDB1(4):TT04 (PID:162320): Media Recovery Log +RECO/O21S1DG1/ARCHIVELOG/2023_12_25/thread_1_seq_14.270.1156509523 [krd.c:9452] 2023-12-25T13:02:23.392274+01:00 ALTER SYSTEM SET remote_listener=' o8s1-21c-scan:1521' SCOPE=MEMORY SID='o21s1dg11'; 2023-12-25T13:02:23.397050+01:00 ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='o21s1dg11'; 2023-12-25T13:02:23.513048+01:00 S2PDB1(4):TT04 (PID:162320): Media Recovery Log +RECO/O21S1DG1/ARCHIVELOG/2023_12_25/thread_2_seq_3.271.1156509523 [krd.c:9452] S2PDB1(4):TT04 (PID:162320): Media Recovery Waiting for T-2.S-4 (in transit) [krsm.c:6205] 2023-12-25T13:02:23.841935+01:00 Completed: alter pluggable database S2PDB1 recover managed standby database disconnect
And since the DG PDB does not operate in SYNC mode, I recommend that you connect to the source/primary database and archivelog to check the shipping is working and there is no issue.
So, going to the primary database (O21S2DG1 at S2):
[oracle@o8s2n1-21c ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 13:02:51 2023 Version 21.12.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.12.0.0.0 SQL> SQL> alter system archive log current; System altered. SQL>
We can see in the standby alertlog that shipping is working and it is receiving the archivelogs:
2023-12-25T13:02:58.953468+01:00 rfs (PID:149958): Archived Log entry 4 added for B-1156499932.T-2.S-4 ID 0x4a5c671c LAD:1 [krsp.c:1256] rfs (PID:149958): No SRLs created [krsk.c:4671] 2023-12-25T13:02:59.067986+01:00 S2PDB1(4):TT04 (PID:162320): Media Recovery Log +RECO/O21S1DG1/ARCHIVELOG/2023_12_25/thread_2_seq_4.269.1156509525 [krd.c:9452] 2023-12-25T13:02:59.094785+01:00 rfs (PID:149958): Opened log for DBID:1247569180 B-1156499932.T-2.S-5 [krsr.c:18251] 2023-12-25T13:02:59.126353+01:00 S2PDB1(4):TT04 (PID:162320): Media Recovery Log +RECO/O21S1DG1/ARCHIVELOG/2023_12_25/thread_1_seq_15.268.1156509527 [krd.c:9452] S2PDB1(4):TT04 (PID:162320): Media Recovery Waiting for T-1.S-16 (in transit) [krsm.c:6205]
To validate, the show command reports to us no error:
DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s1dg1; Pluggable database 's2pdb1' at database 'o21s1dg1' Data Guard Role: Physical Standby Con_ID: 4 Source: con_id 3 at o21s2dg1 Transport Lag: 0 seconds (computed 51 seconds ago) Intended State: APPLY-ON Apply State: Running Apply Instance: o21s1dg11 Average Apply Rate: 16 KByte/s Real Time Query: OFF Pluggable Database Status: SUCCESS DGMGRL>
Switchover PDB
Now that everything is properly configured, and the apply is running we can play and switchover the pdb between sites. And just to show that it is working I went to the primary and created one table with one record and sent the arhcivelolgs:
SQL> alter session set container = S2PDB1; Session altered. SQL> create table t1(c1 date); Table created. SQL> insert into t1(c1) values(sysdate); 1 row created. SQL> commit; Commit complete. SQL> SQL> alter session set container = cdb$root; Session altered. SQL> alter system archive log current; System altered. SQL>
Calling the switchover connect at the standby (look that my first command was to check the status and verify if the apply was ok):
DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s1dg1; Pluggable database 's2pdb1' at database 'o21s1dg1' Data Guard Role: Physical Standby Con_ID: 4 Source: con_id 3 at o21s2dg1 Transport Lag: 0 seconds (computed 51 seconds ago) Intended State: APPLY-ON Apply State: Running Apply Instance: o21s1dg11 Average Apply Rate: 16 KByte/s Real Time Query: OFF Pluggable Database Status: SUCCESS DGMGRL> DGMGRL> switchover to pluggable database s2pdb1 at o21s1dg1; Verifying conditions for Switchover... Connected to "o21s2dg1" Source pluggable database is 'S2PDB1' at database 'o21s2dg1' Performing switchover NOW, please wait... Closing pluggable database 'S2PDB1'... Switching 'S2PDB1' to standby role... Connected to "o21s1dg1" Waiting for 'S2PDB1' to recover all redo data... Stopping recovery at 'S2PDB1'... Converting 'S2PDB1' to primary role... Opening new primary 'S2PDB1'... Connected to "o21s2dg1" Waiting for redo data from new primary 'S2PDB1'... Starting recovery at new standby 'S2PDB1'... Switchover succeeded, new primary is "S2PDB1" DGMGRL>
Here is one important detail. I passed the same issue that my previous post (ISSUE#4), the target/standby was waiting for the source/primary to send the last archivelog. When you call the command, it will wait at “Waiting for ‘S2PDB1’ to recover all redo data…”.
So, to avoid the known issue I connected at the primary and executed “alter system archive log current”. This will send the required data and the switchover can happen. At the alerlog from the standby we can see this:
2023-12-25T14:32:41.897557+01:00 rfs (PID:149958): Archived Log entry 13 added for B-1156499932.T-2.S-9 ID 0x4a5c671c LAD:1 [krsp.c:1256] rfs (PID:149958): No SRLs created [krsk.c:4671] 2023-12-25T14:32:42.011118+01:00 rfs (PID:149958): Opened log for DBID:1247569180 B-1156499932.T-2.S-10 [krsr.c:18251] 2023-12-25T14:33:01.394899+01:00 S2PDB1(4):TT04 (PID:162320): Media Recovery Log +RECO/O21S1DG1/ARCHIVELOG/2023_12_25/thread_1_seq_20.294.1156516283 [krd.c:9452] 2023-12-25T14:33:01.482329+01:00 S2PDB1(4):TT04 (PID:162320): Media Recovery Log +RECO/O21S1DG1/ARCHIVELOG/2023_12_25/thread_2_seq_9.293.1156516285 [krd.c:9452] S2PDB1(4):End of lifespan for PDB at SCN 0x000000000014cb16 due to SWITCHOVER 2023-12-25T14:33:01.657716+01:00 S2PDB1(4):Incomplete recovery applied all redo ever generated. S2PDB1(4):Recovery completed through change 1362710 time 12/25/2023 14:32:38 2023-12-25T14:33:03.066609+01:00 rfs (PID:149958): Archived Log entry 15 added for B-1156499932.T-2.S-10 ID 0x4a5c671c LAD:1 [krsp.c:1256] rfs (PID:149958): No SRLs created [krsk.c:4671] 2023-12-25T14:33:03.149081+01:00 rfs (PID:149958): Opened log for DBID:1247569180 B-1156499932.T-2.S-11 [krsr.c:18251] 2023-12-25T14:33:04.438359+01:00 freeing rdom 4 2023-12-25T14:33:08.027191+01:00 Thread 1 advanced to log sequence 15 (LGWR switch), current SCN: 1363189 Current log# 1 seq# 15 mem# 0: +DATA/O21S1DG1/ONLINELOG/group_1.286.1156499935 Current log# 1 seq# 15 mem# 1: +RECO/O21S1DG1/ONLINELOG/group_1.282.1156499937 2023-12-25T14:33:09.153056+01:00 ARC1 (PID:97602): Archived Log entry 5 added for B-1156499930.T-1.S-14 ID 0x7f9d8eb2371a LAD:1 [krse.c:4934] 2023-12-25T14:33:22.003784+01:00 Clearing standby activation ID 2394044186 (0x8eb2371a) The primary database controlfile was created using the 'MAXLOGFILES 192' clause. There is space for up to 188 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 209715200; 2023-12-25T14:33:27.451257+01:00 WARNING: Disk space leak. Failed to remove control file copy +DATA/O21S1DG1/CONTROLFILE/backup.291.1156516401 due to the following errors; 2023-12-25T14:33:27.451403+01:00 ORA-15028: ASM file '+DATA/O21S1DG1/CONTROLFILE/backup.291.1156516401' not dropped; currently being accessed
We can see that now the broker recognizes the new primary and the new standby for this pdb:
DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s1dg1; Pluggable database 's2pdb1' at database 'o21s1dg1' Data Guard Role: Primary Con_ID: 4 Active Target: con_id 3 at o21s2dg1 Pluggable Database Status: SUCCESS DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s2dg1; Connected to "o21s2dg1" Pluggable database 's2pdb1' at database 'o21s2dg1' Data Guard Role: Physical Standby Con_ID: 3 Source: con_id 4 at o21s1dg1 Transport Lag: 0 seconds (computed 58 seconds ago) Intended State: APPLY-ON Apply State: Running Apply Instance: o21s2dg12 Average Apply Rate: (unknown) Real Time Query: OFF Pluggable Database Status: SUCCESS DGMGRL>
To check if everything was fine I connected at the primary and checked the T1 table there:
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 14:35:14 2023
Version 21.12.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.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S1PDB1 READ WRITE NO
4 S2PDB1 MOUNTED
SQL> alter pluggable database S2PDB1 open instances = all;
Pluggable database altered.
SQL>
SQL> alter session set container = S2PDB1;
Session altered.
SQL> select * from t1;
C1
---------
25-DEC-23
SQL>
And is even possible to do a direct switchover again. So, I added a new record over the T1 table and made the switchover:
[oracle@o8s1n1-21c ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 14:39:33 2023 Version 21.12.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.12.0.0.0 SQL> alter session set container = S2PDB1; Session altered. SQL> select * from t1; C1 --------- 25-DEC-23 SQL> insert into t1(c1) values(sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from t1; C1 --------- 25-DEC-23 25-DEC-23 SQL> alter session set container = cdb$root; Session altered. SQL> alter system archive log current; System altered. SQL>
At broker (remember to connect at the primary and call the archivelolg to send it to the standby and avoid errors):
DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s1dg1; Pluggable database 's2pdb1' at database 'o21s1dg1' Data Guard Role: Primary Con_ID: 4 Active Target: con_id 3 at o21s2dg1 Pluggable Database Status: SUCCESS DGMGRL> SHOW PLUGGABLE DATABASE S2PDB1 at o21s2dg1; Connected to "o21s2dg1" Pluggable database 's2pdb1' at database 'o21s2dg1' Data Guard Role: Physical Standby Con_ID: 3 Source: con_id 4 at o21s1dg1 Transport Lag: 0 seconds (computed 51 seconds ago) Intended State: APPLY-ON Apply State: Running Apply Instance: o21s2dg12 Average Apply Rate: 111 KByte/s Real Time Query: OFF Pluggable Database Status: SUCCESS DGMGRL> DGMGRL> switchover to pluggable database s2pdb1 at o21s2dg1; Verifying conditions for Switchover... Connected to "o21s2dg1" Connected to "o21s1dg1" Source pluggable database is 'S2PDB1' at database 'o21s1dg1' Performing switchover NOW, please wait... Closing pluggable database 'S2PDB1'... Switching 'S2PDB1' to standby role... Connected to "o21s2dg1" Waiting for 'S2PDB1' to recover all redo data... Stopping recovery at 'S2PDB1'... Converting 'S2PDB1' to primary role... Opening new primary 'S2PDB1'... Connected to "o21s1dg1" Waiting for redo data from new primary 'S2PDB1'... Starting recovery at new standby 'S2PDB1'... Switchover succeeded, new primary is "S2PDB1" DGMGRL>
And checking if the data was there:
[oracle@o8s2n1-21c ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Dec 25 14:49:30 2023
Version 21.12.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.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 S2PDB1 MOUNTED
SQL> alter pluggable database S2PDB1 open instances = all;
Pluggable database altered.
SQL> alter session set container = S2PDB1;
Session altered.
SQL> select * from t1;
C1
---------
25-DEC-23
25-DEC-23
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.12.0.0.0
[oracle@o8s2n1-21c ~]$
If you want to remove you can do this (in the example below remember to go to ASM and delete the datafiles):
DGMGRL> REMOVE PLUGGABLE DATABASE s2pdb1 AT o21s1dg1; Pluggable Database 'S2PDB1' removed. No more standby pluggable databases; stopping redo transport services at source database o21s2dg1... Connected to "o21s2dg1" Succeeded. DGMGRL>
Or include the “[REMOVE DATAFILES]”:
DGMGRL> help remove
Removes a configuration or a member
Syntax:
REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
REMOVE CONFIGURATION <configuration name>;
REMOVE { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }
<object name> [PRESERVE DESTINATIONS];
REMOVE PLUGGABLE DATABASE <pluggable database name>
AT <target CDB> [REMOVE DATAFILES];
REMOVE INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>];
DGMGRL>
DG PDB with 21.12
Resuming after this long post. The process was flawless, was perfect from beginning to end. The new command to prepare the config helped a lot and the errors disappeared. Just compare my previous post with the process that I described here. I got no ORA-XXXX errors, just the simple (and now) documented needed to use the wallets, copy datafiles, and the archivelog (this is still one issue, but it is possible to understand what/why happens).
If you look at the online docs from 23c you can compare both. The commands that were added from 21c come directly from 23c, and this was nice. The process is not the same between 21c and 23c (look at the docs where the standby redo logs are now), but I liked to document the new command for 21c and how it helps the process. Well done Oracle, well done.
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, and 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 - Fernando Simon
Pingback: 2023/2024 - Fernando Simon