Due to some questions I had while creating a physical Oracle Data Guard in an environment where the primary is a RAC environment and the standby is a single-node environment, both multitenant, I decided to write this article.
Check the environment information:
| HOSTNAME | ORACLE VERSION | OPERACIONAL SYSTEM | ROLE |
|---|---|---|---|
| orarac01 | 19.3.0.0 | Oracle Linux 7.9 | PRIMARY |
| orarac02 | 19.3.0.0 | Oracle Linux 7.9 | PRIMARY |
| oradg | 19.3.0.0 | Oracle Linux 7.9 | DATA GUARD |
We will assume that both environments have their operating systems configured, storage disks created, and the Oracle RDBMS and Grid Infrastructure binaries installed, as well as the database created in the production environment.
Both environments were created using Grid Infrastructure with asmlib.
Notes:
- Before installing Grid in the Data Guard environment, configure the TNS_ADMIN environment variable to the RDBMS TNS path. This will ensure that both the Grid and RDBMS binaries use the same path as the binaries for listener configuration.
- It will be necessary to correctly configure the tnsnames of the RAC and ADG environments so that the duplicate command finds the service name of the Data Guard database when RMAN is performing the reverse connection, from the primary to the standby.
- The init file used to start ADG should contain the pluggable database parameters, indicating that the database will be a container, as well as the cluster database parameter, indicating that the database will not be a cluster.
- Regarding the primary environment, a TNS entry must be created, without using RAC scan, to perform the duplicate.
We will begin by configuring the primary environment, adding the redo logs to the production database.
Check the size of the existing redo logs and create one of the same size.
Create an additional redo log file.
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19C/ONLINELOG/standby_redo101.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19C/ONLINELOG/standby_redo102.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19C/ONLINELOG/standby_redo103.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19C/ONLINELOG/standby_redo104.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19C/ONLINELOG/standby_redo105.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 ('+RECO/CDB19C/ONLINELOG/standby_redo201.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 ('+RECO/CDB19C/ONLINELOG/standby_redo202.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 ('+RECO/CDB19C/ONLINELOG/standby_redo203.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 ('+RECO/CDB19C/ONLINELOG/standby_redo204.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 2 ('+RECO/CDB19C/ONLINELOG/standby_redo205.log') SIZE 512M;
Add the following entries to the listener.ora file of the primary RAC nodes:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=cdb19c1_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1/)
(SID_NAME=cdb19c1)
(ENVS = "TNS_ADMIN=/u01/app/oracle/product/19.3.0/db/network/admin")
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1/)
(SID_NAME = PLSExtProc)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=cdb19c)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1/)
(SID_NAME=cdb19c)
)
)
Add the following entries to the tnsnames.ora file of the primary RAC nodes:
CDB19C =
(DESCRIPTION =
#(ADDRESS = (PROTOCOL = TCP)(HOST = orarac-racscan)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb19c)
)
)
CDB19C1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = cdb19c1)
(UR=A)
)
)
CDB19CDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = cdb19c)
(UR=A)
)
)
Add the following entries to the listener.ora file in Data Guard:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdb19cdg_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
(SID_NAME = cdb19c)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Add the following entries to the tnsnames.ora file in Data Guard:
cdb19c =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orarac01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cdb19c)
(UR=A)
)
)
cdb19cdg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradg)(PORT = 1521))
)
(CONNECT_DATA =
(SID = cdb19c)
(UR=A)
)
)
You will need to create an init file to start the Data Guard database in nomout mode and perform the duplicate. On the oradg server, create the initcdb19c.ora file using your preferred editor and include the following parameters:
*.db_name='cdb19c'
*.db_unique_name='cdb19cdg'
*.enable_pluggable_database=true
*.STANDBY_FILE_MANAGEMENT=AUTO
*.sga_target=1G
*.pga_aggregate_target=500M
*.cluster_database=false
Copy the orapw file from the production environment to the Data Guard environment:
scp $ORACLE_HOME/dbs/orapwcdb19c oradg:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwcdb19c
After this, start the Data Guard database in nomount mode:
sqlplus "/as sysdba"
STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initcdb19c.ora'
Connect to the target database and the auxiliary database using RMAN, then execute the duplicate command. Enforce the init parameters in the duplicate command.
rman target sys/oracle@cdb19c auxiliary sys/oracle@cdb19cdg
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='cdb19cdg'
SET cluster_database=false
SET enable_pluggable_database=true
COMMENT 'Is standby'
NOFILENAMECHECK;
After the duplicate is complete, we will configure the broker. On both databases:
ALTER SYSTEM SET dg_broker_start=true scope=both sid='*';
ALTER SYSTEM set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=both sid='*';
ALTER SYSTEM set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter database flashback on;
Create standby log files on data guard:
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19CDG/ONLINELOG/standby_redo101.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19CDG/ONLINELOG/standby_redo102.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19CDG/ONLINELOG/standby_redo103.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19CDG/ONLINELOG/standby_redo104.log') SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('+RECO/CDB19CDG/ONLINELOG/standby_redo105.log') SIZE 512M;
On primary:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cdb19c,cdb19cdg)' scope=both sid='*';
In case you are using DB_RECOVER_FILE_DEST:
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb19c' scope = both;
If not:
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb19c' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=cdb19cdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdb19cdg' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=enable scope=both sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set FAL_SERVER=CDB19CDG scope=both sid='*';
alter system set FAL_CLIENT=CDB19C scope=both sid='*';
On Data Guard:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cdb19c,cdb19cdg)' scope = both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdb19cdg' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=cdb19c LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdb19c' scope=both;
alter system set FAL_SERVER=cdb19c scope=both;
alter system set FAL_CLIENT=CDB19CDG scope=both;
Configuring broker:
dgmgrl sys/oracle@cdb19c
CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS cdb19c CONNECT IDENTIFIER IS cdb19c;
ADD DATABASE cdb19cdg AS CONNECT IDENTIFIER IS cdb19cdg MAINTAINED AS PHYSICAL;
edit database cdb19c set property StaticConnectIdentifier="orarac-rascan:1521/cdb19c_dgmgrl";
edit database cdb19cdg set property StaticConnectIdentifier="oradg:1521/cdb19cdg_dgmgrl";
EDIT DATABASE cdb19c SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE cdb19cdg SET PROPERTY 'LogXptMode'='SYNC';
ENABLE CONFIGURATION;
Finally, run the switchover tests and verify that the databases have been changed.
My Piece of Data 