Oracle Cross Region Data Guard in OCI Security Zones
Oracle Security Zones automatically set up and enforce security policies for cloud compartments in Oracle Cloud Infrastructure. A security zone is associated with a compartment of the same name and includes security zone policies or a “recipe” that applies to the compartment and its sub-compartments
Currently, OCI DB Systems also known as DBCS doesn’t allow to enable Data Guard automatically from OCI Console inside of security zones.
This article shows how to configure a Cross Region Data Guard where both databases were created inside of security zones. All network configuration required to connect a security zone is showed in below diagram.
For further information, check documentation links on bottom of this article.
Pre-requisites
2 DB Systems created each one in a different region;
OCI Security Zone created;
Object Storage bucket and OCI Vault and Vault Key;
Archivelog enable and database logging;
Connectivity between Primary Database and standby database;
Initial Set-up
Before configuring Oracle Data Guard, you need to make sure the two databases can communicate. For that, you must configure 2 VCNs per region. One outside of Security Zone and another one inside of security zone. You should configure Local Peering between both. Create all ingress rules on your security lists (open ports, allow CIDRs) on each VCN. Do it that for both regions. The last step is to configure Remote Peering. Test the connectivity from Database Primary to Standby for port 22 or 1521. In the below diagram you have most of the configuration you need.
In this article you will see the DB name, DB Unique name, hosts, Service names, SIDs and database domains that I used during the configuration. Please replace by yours. Don’t do just copy and paste, since the configuration can be referencing objects that don’t exist on your environment.
Before you start the configuration read all article, you will find some errors that can show up during the configuration and the changes you need to perform to solve them (for this use case). The goal is to help people that can find this type of issues.
Primary Database
DB Name: mydb
SID:mydb
DB Unique Name: mydb_dbprim
Host / IP: 10.0.0.88 dbprim.privatesubnetfr.vcnseczone.oraclevcn.com dbprim
Service Name: MYDB_DBPRIMStandby Database
DB Name: mydb
SID:mydb
DB Unique Name: mydb_dgclone
Host / IP: 192.168.0.110 dgclone.privatesubnetlo.vcnseczone.oraclevcn.com dgclone
Service Name: mydb_dgcloneConnectivity between Primary and Standby
Set up the connectivity for Oracle user.
On primary:
[oracle@dbprim ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:LIJMxUrQ2gBmjjO+wqR+gIL2Et1dRpRYeGS/86GbpCk oracle@dbprim
The key's randomart image is:
+---[RSA 2048]----+
|+= .. *=. |
|* o.. o.+. |
|+*.. o . |
|o=o. . o . |
|oo+ o o S o . |
|B+.. o o + . |
|=o+ o . |
|o. o E + o |
| .o .o o |
+----[SHA256]-----+[oracle@dbprim ~]$ cat .ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDpiUEMkOsKo/NT7XIHsL20eX7hc0WAorxgjiWRun2BA4tL0zOLTxFd4iPad0VzNPE/PSpJyS1E2MsWgdGueFSNCMlzmwUx94CorIMCrXpYkc9VjhRtw8PwclXheM1J9wm2AeV7HFtAjaYe+NEJhY2a/980lBHYpS84wihLyL9Iq0oKOcakP8xoJTewezF6c87V3mYqOb54IK+NZCzgDS6lh4gqLK/G/9y9KR+Km4cnQbShyEjK0asjcdDozgkiTqGiwCSYDdU6kk2QfbWrrHuASsJ71h4QCyzguRAAAClPsPLv/KCCDnGC2ELsu9LcdBdvVjl/BN+DpjOPnV1Ax0FD oracle@dbprim
Copy the id_rsa.pub into authorized keys on Standby DB:
vi .ssh/authorized_keysNow, repeat the same on Standby. Generate the ssh key and copy on authorized_keys from Primary DB.
Test connectivity using:
ssh oracle@<ip or host>Configuring Primary database
Log in as grid:
sudo su - gridEdit cd /u01/app/19.0.0.0/grid/network/admin/listener.ora and add the following content to it.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SDU=65535)
(GLOBAL_DBNAME = mydb_dbprim.privatesubnetfr.vcnseczone.oraclevcn.com)
(SID_NAME = mydb)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
)
(SID_DESC=
(SDU=65535)
(GLOBAL_DBNAME = mydb_dbprim_DGMGRL.privatesubnetfr.vcnseczone.oraclevcn.com)
(SID_NAME = mydb)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
)
)Stop and stat the listener:
srvctl stop listener
srvctl start listenerNow, as Oracle user, edit tnsnames.ora:
sudo su - oraclevi $ORACLE_HOME/network/admin/tnsnames.ora#add the standby net service nameMYDB_DGCLONE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgclone)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb_dgclone.privatesubnetlo.vcnseczone.oraclevcn.com)
)
)
Enable automatic standby file management on Primary database:
sqlplus / as sysdbaSQL> alter system set standby_file_management=AUTO;
Set broker configuration files:
alter system set dg_broker_config_file1='+DATA/mydb_dbprim/dr1mydb_dbprim.dat';alter system set dg_broker_config_file2='+DATA/mydb_dbprim/dr2mydb_dbprim.dat';
Enable Broker DMON process:
alter system set dg_broker_start=true;Now, add Standby Redo Logs (SRLs), based on the Online Redo Logs (ORLs):
You can query the ORLs:
SQL> select group#, bytes from v$log;GROUP# BYTES
---------- ----------
1 1073741824
2 1073741824
3 1073741824They all have the same size.Add standby redo logs:alter database add standby logfile thread 1 group 4 ('+RECO/MYDB_DBPRIM/ONLINELOG/standby_redo01.log') size 1073741824;
alter database add standby logfile thread 1 group 5 ('+RECO/MYDB_DBPRIM/ONLINELOG/standby_redo02.log') size 1073741824;
alter database add standby logfile thread 1 group 6 ('+RECO/MYDB_DBPRIM/ONLINELOG/standby_redo03.log') size 1073741824;
alter database add standby logfile thread 1 group 7 ('+RECO/MYDB_DBPRIM/ONLINELOG/standby_redo04.log') size 1073741824;Verify that were created:select group#, bytes from v$standby_log;
Enable flashback mode and perform a single switch redo log:
alter database flashback on;
alter system set db_flashback_retention_target=120;
alter system switch logfile;Configuring Standby Database
Let’s start by configuring the listener:
sudo su - grid
vi /u01/app/19.0.0.0/grid/network/admin/listener.oraSID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SDU=65535)
(GLOBAL_DBNAME = mydb_dgclone.privatesubnetlo.vcnseczone.oraclevcn.com)
(SID_NAME = mydb)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
)
(SID_DESC=
(SDU=65535)
(GLOBAL_DBNAME = mydb_dgclone_DGMGRL.privatesubnetlo.vcnseczone.oraclevcn.com)
(SID_NAME = mydb)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin")
)
)#Start and stop listener:
srvctl stop listener
srvctl start listener#Check the status, the UNKNOWN is normal at this point
lsnrctl status
Now, as Oracle user, edit tnsnames.ora:
sudo su - oracle
cd $ORACLE_HOME/network/admin#add the primary net service name herePRIMARY_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstandby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary_dg.privatesubnetlo.vcnseczone.oraclevcn.com)
)
)MYDB_DBPRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbprimary.privatesubnetfr.vcnseczone.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb_dbprim.privatesubnetfr.vcnseczone.oraclevcn.com)
)
)
Now, we need to copy the TDE wallet from Primary DB to Standby DB.
scp /opt/oracle/dcs/commonstore/wallets/tde/mydb_dbprim/* dgclone:/opt/oracle/dcs/commonstore/wallets/tde/mydb_dgNow, create the audit file destination:
sudo su - oracle
mkdir -p /u01/app/oracle/admin/mydb/adumpCreate temporary password file:
orapwd file=$ORACLE_HOME/dbs/orapwmydb password=<password> entries=5Create the$ORACLE_HOME/dbs/init<standby sid_name>.ora with the following content.
db_name=<Primary db_name>
db_unique_name=<standby db_unique_name>
db_domain=<standby db_domain>In my case:
db_name='mydb'
db_unique_name='mydb_dgclone'
db_domain='privatesubnetlo.vcnseczone.oraclevcn.com'
Set the ORACLE_UNQNAME environment variable to point to your DB_UNIQUE_NAME:
export ORACLE_UNQNAME=mydb_dgcloneNow, let’s put the database in nomount mode:
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;Check if you can connect from Primary DB to Standby DB and vice-versa:
#Primary to Standby
sqlplus sys/<password>@mydb_dgclone as sysdba#Standby to Primary
sqlplus sys/<password>@mydb_dbprim as sysdba
RMAN — Duplicating
Connect to RMAN:
rman target sys/<password>@MYDB_DBPRIM auxiliary sys/<password>@PRIMARY_DG log=rmanlog.outRun this command to duplicate:
run { allocate channel prim1 type disk; allocate auxiliary channel sby type disk; duplicate target database for standby from active database dorecover spfile parameter_value_convert '/mydb_dbprim/','/mydb_dgclone/','/MYDB_DBPRIM/','/MYDB_DGCLONE/'set db_unique_name='mydb_dgclone' set dg_broker_config_file1='+DATA/mydb_dgclone/dr1mydb_dgclone.dat' set dg_broker_config_file2='+DATA/mydb_dgclone/dr2mydb_dgclone.dat' set dispatchers ='(PROTOCOL=TCP) (SERVICE=mydb_dgcloneXDB)' set instance_name='mydb_dgclone';}If it fails with this error:
Starting Duplicate Db at 12-NOV-21
released channel: prim1
released channel: sby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/12/2021 08:41:02
RMAN-05501: aborting duplication of target database
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clauseDo this:
shutdown immediate;STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initmydb.ora';
If it fails with this error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/15/2021 17:08:13
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19849: error while reading backup piece from service mydb_dbprim
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATA/MYDB_DGCLONE/PARAMETERFILE/spfile.269.1088667771
ORA-15046: ASM file name '+DATA/MYDB_DGCLONE/PARAMETERFILE/spfile.269.1088667771' is not in single-file creation form
ORA-19660: some files in the backup set could not be verified
ORA-19685: SPFILE could not be verified
ORA-19849: error while reading backup piece from service mydb_dbprim
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATA/MYDB_DGCLONE/PARAMETERFILE/spfile.269.1088667771
ORA-15046: ASM file name '+DATA/MYDB_DGCLONE/PARAMETERFILE/spfile.269.1088667771' is not in single-file creation formRun this command:
srvctl modify database -d <standby db> -p ' 'If fails with this error:
released channel: prim1
released channel: sby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/15/2021 17:00:12
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19849: error while reading backup piece from service mydb_dbprim
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open
ORA-19660: some files in the backup set could not be verified
ORA-19685: SPFILE could not be verified
ORA-19849: error while reading backup piece from service mydb_dbprim
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not openEdit the listener.ora and add “ORACLE_UNQNAME=<standby db>” on ENVS:
(ENVS=”TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin,ORACLE_UNQNAME=mydb_dgclone”)
After, the duplicate have finished successfully, enable flashback on standby:
alter database flashback on;
alter system set db_flashback_retention_target=120;Now, let’s create a new database service from the standby:
create pfile='/home/oracle/initmydb.ora' from spfile;
create spfile='+DATA' from pfile='/home/oracle/initmydb.ora';stop database -d mydb_dgclone
srvctl remove database -d mydb_dgclone#create database
srvctl add database -d mydb_dgclone -n mydb -o $ORACLE_HOME -c SINGLE -p '+DATA/MYDB_DGCLONE/PARAMETERFILE/spfile.xxx.xxxxxx' -x dgclone -s "READ ONLY" -r PHYSICAL_STANDBY -i mydbsrvctl setenv database -d mydb_dgclone -t "ORACLE_UNQNAME=mydb_dgclone"srvctl config database -d mydb_dgclonesrvctl start database -d mydb_dgclonemv $ORACLE_HOME/dbs/initmydb.ora $ORACLE_HOME/dbs/initmydb_stb.ora_oldmv $ORACLE_HOME/dbs/spfilemydb.ora $ORACLE_HOME/dbs/spfilemydb_stb.ora_old
Go to ASMCMD and check the spfile. After add into init<dbname>.ora:
vi $ORACLE_HOME/dbs/initmydb.ora
#add this. In your case it should a different path and spfile name
SPFILE='+DATA/MYDB_DGCLONE/PARAMETERFILE/spfile.291.1088702449'srvctl stop database -d mydb_dgclonesrvctl start database -d mydb_dgclone
Configuring Data Guard
dgmgrlconnect sys/<password>@mydb_dbprim
create configuration mystby as primary database is mydb_dbprim connect identifier is mydb_dbprim;
add database mydb_dgclone as connect identifier is mydb_dgclone maintained as physical;enable configuration;show database verbose mydb_dgclone
If you find this error:
Database Error(s):
ORA-16766: Redo Apply is stoppedDatabase Warning(s):
ORA-16854: apply lag could not be determined
Please, connect again and just disable and enable the Data Guard and check again.
dgmgrl
connect sys/<password>@mydb_dbprim
disable configuration;
enable configuration;
show database verbose mydb_dgcloneDatabase Status:
SUCCESS
Testing Data Guard
SQL> select distinct name from v$pdbs;
alter session set container=MYDB_PDB1;
CREATE USER TEST IDENTIFIED BY "<password>"
grant connect to TEST;
grant unlimited tablespace to TEST;
create table test.mytab (col1 varchar(5));insert into test.mytab values ('TEST');
commit;
On standby database:
shutdown immediate;
startup mount;
alter database open <read only>;
alter pluggable database open <read only>;alter session set container = <pdbname>;
Select * from TEST.mytab;
#you should see the record inserted. Try to insert one more from primary.
I hope this can help you. If you have any doubt, please reach out to me through LinkedIn or Medium.
References
