Prepare Data Guard's primary database
Data Guard requires a primary database and at least one standby database to function. Before setting up Data Guard on Bare Metal Solution, create the primary database.
The following examples are used throughout this guide:
Database unique name | Server hostnames | RAC instance names | Role |
---|---|---|---|
DBDG_SITE1 | site1db1, site1db2 | DBDG_SITE11, DBDG_SITE12 | Primary |
DBDG_SITE2 | site2db1, site2db2 | DBDG_SITE21, DBDG_SITE22 | Standby |
The Bare Metal Solution servers running Oracle in this guide are configured with the following environment variables:
Environment variable name | Value |
---|---|
ORACLE_BASE | /apps/oracle/oracle_base |
ORACLE_HOME | /apps/oracle/19.0.0/db_home1 |
You might need to modify the environment variable paths depending on your setup.
Set up the primary database
Log in to the first Bare Metal Solution server that hosts the primary database.
In the
/etc/oratab
file, add an entry so theoraenv
command can set environment variables for the primary database. Modify the instance name to match the running instance on each node, and specify the full path to the Oracle database home:DBDG_SITE11:ORACLE_HOME:N
Set the
ORACLE_SID
environment variable so you can connect to the primary database:source oraenv <<< "DBDG_SITE11"
You should receive the response
The Oracle base has been set to /apps/oracle/oracle_base
.
Configure database logging and enable flashback database
Start SQL*Plus, then enable
force logging
so thatnologging
tablespaces added to the database are logged:sqlplus / as sysdba
ALTER DATABASE FORCE LOGGING;
Verify that
archivelog mode
andflashback database
are on:ARCHIVE LOG LIST; SELECT LOG_MODE, FLASHBACK_ON FROM V$DATABASE;
If
archivelog mode
andflashback database
are off, turn them on with the following SQL commands:Stop the clustered database so you can make the required changes:
srvctl stop database -d
Run the required SQL commands to turn on
archivelog mode
andflashback database
:STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE FLASHBACK ON; ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SID='*' SCOPE=BOTH; ALTER DATABASE OPEN; SHUTDOWN IMMEDIATE; EXIT;
Start the clustered database again:
srvctl start database -d DBDG_SITE1
Prepare the database for Data Guard
Log in to the first Bare Metal Solution server that hosts the primary database and set the
ORACLE_SID
environment variable so you can connect to the primary database:source oraenv <<< "DBDG_SITE11"
You should receive the response
The Oracle base has been set to /apps/oracle/oracle_base
.Start SQL*Plus, then modify the database initialization parameters required for the Data Guard configuration and services:
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SID='*' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8 SID='*' SCOPE=BOTH; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SID='*' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*' SCOPE=BOTH;
Check for the existence of standby redo logs in the database. Each thread must have one more standby redo log group than the number of online redo log groups.
SELECT 'Logfile groups in thread ' || THREAD# || ': ' || COUNT(*) FROM V$LOG GROUP BY THREAD# UNION SELECT 'Standby logfile groups in thread ' || THREAD# || ': ' || COUNT(*) FROM V$STANDBY_LOG GROUP BY THREAD#;
The following example has four online redo logs per thread, and does not have any standby redo log groups created. Since there are four online redo log groups, five standby redo log groups must be created for each thread:
Logfile groups in thread 1: 4 Logfile groups in thread 2: 4
If enough standby redo log groups exist, skip this step. Standby redo log groups must be the same size as each online redo log group. Create the necessary number of standby redo log groups with the following PL/SQL code:
SET FEEDBACK OFF SET SERVEROUTPUT ON DECLARE CURSOR cur_thread IS SELECT THREAD# THREAD, COUNT(*) COUNT FROM V$LOG GROUP BY THREAD#; standbylog_count NUMBER := 0; standbylog_group NUMBER := 0; standbylog_size NUMBER := 0; last_group_number NUMBER; sqlstmt VARCHAR2(200); BEGIN SELECT MAX(GROUP#), MAX(BYTES) INTO last_group_number, standbylog_size FROM V$LOG; standbylog_group := last_group_number + 1; FOR rec_thread IN cur_thread LOOP standbylog_count := rec_thread.count + 1; FOR i IN 1..standbylog_count LOOP sqlstmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' || rec_thread.thread || ' GROUP ' || TO_CHAR(standbylog_group) || ' SIZE ' || standbylog_size || ';'; DBMS_OUTPUT.PUT_LINE( sqlstmt); standbylog_group := standbylog_group + 1; END LOOP; END LOOP; END; / SET FEEDBACK ON
The following example output instructs you to create five standby redo log groups for each of the two threads:
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 14 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 15 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 16 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 17 SIZE 2147483648; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 18 SIZE 2147483648;
Verify the standby redo logs are set up correctly for your configuration:
SELECT 'Logfile groups in thread ' || THREAD# || ': ' || COUNT(*) FROM V$LOG GROUP BY THREAD# UNION SELECT 'Standby logfile groups in thread ' || THREAD# || ': ' || COUNT(*) FROM V$STANDBY_LOG GROUP BY THREAD#;
The following output has four online redo log groups per thread and five standby redo log groups per thread:
Logfile groups in thread 1: 4 Logfile groups in thread 2: 4 Standby logfile groups in thread 1: 5 Standby logfile groups in thread 2: 5
Exit SQL*Plus:
EXIT;
Copy the database parameter and password files to the standby servers
Create a database parameter file and copy it to the standby server:
Create a folder for the standby database parameter file (
pfile
):mkdir /home/oracle/backup
Start SQL*Plus, then create the standby database
pfile
:sqlplus / as sysdba
CREATE PFILE='/home/oracle/backup/initDBDG_SITE21.ora' FROM SPFILE;
Exit SQL*Plus, then copy the
pfile
to the first Bare Metal Solution server in the standby location:EXIT;
scp /home/oracle/backup/initDBDG_SITE21.ora ORACLE_USERNAME@site2db1:/apps/oracle/19.0.0/db_home1/dbs/initDBDG_SITE21.ora
Make sure the SYS remote login password is saved securely. If you don't know the password, reset it.
Copy the password file to the standby cluster:
Get the location of the password file by using the
srvctl
command:srvctl config database -db DBDG_SITE1 | grep -i password
The following output shows the location of a password file store in ASM:
Password file: +DATA/DBDG_SITE1/PASSWORD/pwdDBDG_SITE1.287.1086459663
Make a temporary copy of the password file on the local filesystem by using the
asmcmd
command:asmcmd cp +DATA/DBDG_SITE1/PASSWORD/pwdDBDG_SITE1.287.1086459663 /tmp/orapwDBDG_SITE21
Copy the password file to the first Bare Metal Solution server in the standby location, and then delete the temporary copy from the Bare Metal Solution server in the primary location:
scp /tmp/orapwDBDG_SITE21 ORACLE_USERNAME@site2db1:/apps/oracle/19.0.0/db_home1/dbs/orapwDBDG_SITE21 rm /tmp/orapwDBDG_SITE21
Configure the database connectivity
Edit the $ORACLE_HOME/network/admin/tsnames.ora
file on each Bare Metal Solution
server in the primary database location to update the net service configuration.
There are three entries in the file; one for each database, and a temporary
entry used during database duplication that you will delete later:
DBDG_SITE1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBDG_SITE1_SCAN_HOSTNAME)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBDG_SITE1) (UR=A)
)
)
DBDG_SITE2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBDG_SITE2_SCAN_HOSTNAME)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBDG_SITE2) (UR=A)
)
)
DBDG_SITE2_FOR_DUPLICATION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = site2db1)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(INSTANCE_NAME = DBDG_SITE21) (UR=A)
)
)
The primary database is now ready to be used in a Data Guard configuration.
Next steps
Next, create Data Guard's standby database.