Hybrid Dataguard is a relatively new 19c feature that allows to have one side of a Dataguard configuration not encrypted. This is particularly useful when having a primary on premises and standby on a OCI, where tablespaces need to be encrypted. The main point is that it allows to spare a Advanced Security licenses on premises site.
Usages of Hybrid Dataguard can be during migration to the cloud or just as a high availability solution, when we have a small on-premises data center.
In the tutorial below I assume to have a primary database on premises without TDE, a we will create a standby in OCI with encryption enabled. The connection between on-premises and OCI is already configured and not explained.
Pre-requisites
- Check that sqlnet port (1521 or other) is open on both directions
- Make sure you can copy files between the servers (using ssh or other method).
I use the following code to test if ports are open.
IPLIST="10.161.97.224 10.161.97.225" # Destination IPs space separated
for IP in $IPLIST; do
for PORT in 22 1521; do
CHECK_IP=${IP}/${PORT}
timeout 1 bash -c "</dev/tcp/${CHECK_IP} && echo Port ${CHECK_IP} is open || echo Port ${CHECK_IP} is closed" || echo Connection timeout
done
done
Prepare primary database
Configure TDE Wallet
Even if the primary DB will not be encrypted, it is necessary to configure a TDE Wallet (called also Keystore). This requires a DB restart.
First we create the wallet directory and set some static parameters.
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde
sqlplus / as sysdba
SQL> ALTER SYSTEM SET tablespace_encryption=DECRYPT_ONLY SCOPE=spfile;
SQL> ALTER SYSTEM SET wallet_root='/u01/app/oracle/admin/cdb2/wallet' SCOPE=spfile;
Plan and restart database to activate the parameters.
SQL> shutdown immediate
SQL> startup
Then we define the TDE type as a software wallet (file), create the wallet and set a key for all containers (PDBs)
SQL> ALTER SYSTEM SET tde_configuration='keystore_configuration=FILE';
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "<wallet password>";
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<wallet password>" container=all;
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<wallet password>" with backup container=all;
SQL> COL wrl_parameter FOR a40
SQL> SELECT con_id,wrl_parameter, wrl_type, wallet_type, status from v$encryption_wallet;
CON_ID WRL_PARAMETER WRL_TYPE WALLET_TYPE STATUS
---------- ---------------------------------------- -------------------- -------------------- ------------------------------
1 /u01/app/oracle/admin/cdb2/wallet/tde/ FILE PASSWORD OPEN
2 FILE PASSWORD OPEN
3 FILE PASSWORD OPEN
Create an auto-login wallet, so the wallet opens automatically in read-only mode on DB startup. We close the read-write wallet, which is then automatically open in read-only mode (autologin).
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/cdb2/wallet/tde/' IDENTIFIED BY "<wallet password>";
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<wallet password>" container=all;
SQL> select con_id,WRL_PARAMETER, WRL_TYPE,WALLET_TYPE, status from V$ENCRYPTION_WALLET;
CON_ID WRL_PARAMETER WRL_TYPE WALLET_TYPE STATUS
---------- ---------------------------------------- -------------------- -------------------- ------------------------------
1 /u01/app/oracle/admin/cdb2/wallet/tde/ FILE AUTOLOGIN OPEN
2 FILE AUTOLOGIN OPEN
3 FILE AUTOLOGIN OPEN
Pause database backups
Make sure that archivelogs generated during the creation of the standby are available until the end of the standby recovery.
Standby logs and parameter
We set automatic file management and create the standby logs. The loop below works well with up to 9 redologs per thread.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> begin
for log_cur in ( select group#, thread#, bytes from v$log )
loop
execute immediate 'alter database add standby logfile thread ' || log_cur.thread# || ' group ' || log_cur.thread# || '0' || log_cur.group# || ' size ' || log_cur.bytes ;
end loop;
end;
/
Create dummy DB in OCI
We need to create a dummy DB in OCI with the same DB_NAME and different DB_UNIQUE_NAME from our primary DB. This is make so that it appears correctly in the portal. This dummy DB will be replaced by our standby. SYS and TDE passwords given are not relevant, as we will be using the ones from primary.
I do the operation directly on the OCI VM Cluster.
DBNAME=cdb2
STANDBY_UNQNAME=cdb2_oci
STANDBY_OH=/u02/app/oracle/product/19.0.0.0/dbhome_1
PDB_NAME=DUMMY_PDB
dbaascli database create \
--dbName $DBNAME \
--dbUniqueName $STANDBY_UNQNAME \
--oracleHome STANDBY_OH \
--pdbName $PDB_NAME \
--sgaSizeInMB 7600 \
--pgaSizeInMB 3072 \
--dbTerritory SWITZERLAND \
--dbCharset AL32UTF8 \
--dbNCharset AL16UTF16 \
--dbLanguage AMERICAN
Create encrypted standby in OCI
Copy Password file
Copy password file from on-premises to OCI
PRIMARY_HOST=vm1.anjo.ch
DBNAME=cdb2
LEGACY_OH=/u01/app/oracle/product/19.24.0/db_1
scp $PRIMARY_HOST:$LEGACY_OH/dbs/orapw${DBNAME} /tmp/orapw${DBNAME}
Move the password files to ASM
sudo su - grid
[grid@oci-vm01 ~]$
DBNAME=cdb2
STANDBY_UNQNAME=CDB2_OCI
DG_NAME=+DATAC1
asmcmd pwcopy /tmp/orapw${DBNAME}
${DG_NAME}/${STANDBY_UNQNAME}/PASSWORD/orapw${STANDBY_UNQNAME}
sudo su - oracle
[oracle@oci-vm1 ~]$
DBNAME=cdb2
STANDBY_UNQNAME=CDB2_OCI
DG_NAME=+DATAC1
srvctl modify database -db ${STANDBY_UNQNAME} -pwfile ${DG_NAME}/${STANDBY_UNQNAME}/PASSWORD/orapw${STANDBY_UNQNAME}
Copy TDE Wallet
First check where in OCI is the wallet of the dummy configured. We will overwrite this wallet.
sqlplus -S / as sysdba <<EOF
show parameter wallet_root
EOF
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string /var/opt/oracle/dbaas_acfs/cdb2/wallet_root
And we copy the wallet from legacy to this location
DBNAME=cdb2
scp $PRIMARY_HOST:/u01/app/oracle/admin/${DBNAME}/wallet/tde/*wallet* /var/opt/oracle/dbaas_acfs/${DBNAME}/wallet_root/tde/
Restore control file
[oracle@oci-vm1]$ rman target /
RMAN> STARTUP NOMOUNT
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE '//vm1.anjo.ch:1521/cdb2.anjo.ch';
RMAN> ALTER DATABASE MOUNT;
Restore as encrypted
We we use the new feature RESTORE AS ENCRYPTED DATABASE, which encrypts the datafiles while copying them from primary.
[oracle@oci-vm1]$ rman target / | tee /home/oracle/duplicate_$(date "+%Y_%m_%d-%Hh%M").log
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> RESTORE AS ENCRYPTED DATABASE FROM SERVICE '//vm1.anjo.ch:1521/cdb2.anjo.ch';
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE FROM SERVICE '//vm1.anjo.ch:1521/cdb2.anjo.ch';
If DB is very large, one can then use recover to roll forward the standby without using archivelogs.
srvctl stop database -db ${STANDBY_UNQNAME}
rman target /
RMAN> STARTUP NOMOUNT
RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE '//vm1.anjo.ch:1521/cdb2.anjo.ch';
RMAN> ALTER DATABASE MOUNT;
RMAN> CATALOG START WITH '+DATAC1' noprompt; /* ignore errors */
RMAN> CATALOG START WITH '+RECOC1' noprompt; /* ignore errors */
RMAN> SWITCH DATABASE TO COPY;
RMAN> SHUTDOWN IMMEDIATE;
srvctl start database -db ${STANDBY_UNQNAME} -startoption MOUNT
rman target sys/<password> # <-- It is necessary to provide password
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> RECOVER DATABASE FROM SERVICE '//vm1.anjo.ch:1521/cdb2.anjo.ch';
RMAN> SWITCH DATABASE TO COPY;
Set role as standby
The database should be set in the OCI clusterware as physical standby
[oracle@oci-vm1]$ srvctl modify database -db ${STANDBY_UNQNAME} -role physical_standby -startoption mount
Clear redologs
begin
for log_cur in ( select group# group_no from v$log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/
begin
for log_cur in ( select group# group_no from v$standby_log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/
Prepare dataguard broker
First we prepare the broke in standby database
connect / as sysdba
SQL> alter system set dg_broker_config_file1='+DATAC1/<standby unique name>/dr1.dat';
SQL> alter system set dg_broker_config_file2='+RECOC1/<standby unique name>/dr2.dat';
SQL> alter system set dg_broker_start=true;
SQL> alter system register;
Then we prepare on primary, connecting to it from OCI
connect sys/<password>@<primary unique name> as sysdba
SQL> alter system set dg_broker_config_file1='+DATAC1/<standby unique name>/dr1.dat';
SQL> alter system set dg_broker_config_file2='+RECOC1/<standby unique name>/dr2.dat';
SQL> alter system set dg_broker_start=true;
And create the dataguard configuration
dgmgrl sys/<password>@//vm1.anjo.ch:1521/cdb2.anjo.ch
DGMGRL> CREATE CONFIGURATION dgconfig AS PRIMARY DATABASE IS <primary unique name> CONNECT IDENTIFIER IS <primary unique name>;
DGMGRL> ADD DATABASE <standby unique name> AS CONNECT IDENTIFIER IS <standby unique name>;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;
It might take a while until it shows “SUCCESSFUL”, important is to check in alertlog that the recovery is happening.
Post-operations
Check if datafiles are encrypted
We can use DB Verify to check the encryption of datafiles.
SYS@CDB2.CDB$ROOT> select file_name from dba_data_files;
[oracle@oci-vm1]$ dbv FILE=<file_name>
DBVERIFY: Release 19.0.0.0.0 - Production on Sat May 24 16:04:16 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = <file_name>
DBVERIFY - Verification complete
Total Pages Examined : 113920
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17265
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 96654 <-- Here shows that blocks are encrypted
Highest block SCN : 0 (0.0)
Re-enable Primary backups
In case they were paused, to not forget to enable back,