Enterprise Manager is favored to configure a standby database, but in my condition configuring a standby database with Enterprise Manager is a very hard job for me because I have only one notebook that has 16GB RAM. So I will only use command line interface to configure Data Guard.
In all following scenarios, I will use two cluster nodes of the previous section (srv1, srv2) to create and configure a standby database. First of all, I create a new single instance database (STBDB), with archivelog mode, on the first node (srv1).
1. Enable force logging
ALTER DATABASE FORCE LOGGING;
SELECT force_logging FROM v$database;
2. Change initialization parameters
- LOG_ARCHIVE_DEST_1 specifies archived log destination of the production database and should have VALID_FOR attribute to specify whether redo data will be written to a destination. The default value is VALID_FOR=(ALL_LOGFILES,ALL_ROLES) which means this destination is valid when archiving either online redo log files or standby redo log file and when the database is running in either the primary or the standby role. DB_UNIQUE_NAME attributes must also be defined to specify the unique name of the database at this destination.
- LOG_ARCHIVE_DEST_2 parameter specifies the remote standby database where the archived log files will be shipped. It must contain SERVICE attribute with connection string of the remote database. VALID_FOR attribute of this parameter must be (ONLINE_LOGFILES,PRIMARY_ROLE) to specify that this destination is valid only when archiving online redo log files and when the database is running in the primary role. For DB_UNIQUE_NAME attribute, the unique name of the standby database must be specified. You can also specify redo transport mode as SYNC or ASYNC. When using ASYNC, the redo data will be transmitted asynchronously with respect of transaction commitment.
- State of both destinations must be ENABLED (LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2)
- FAL_SERVER specifies fetch archive log for standby database. Specify TNS entry of the standby database for this parameter.
- DB_FILE_NAME_CONVERT parameter is used to convert a datafile on the primary database to a filename on the standby database.
- LOG_FILE_NAME_CONVERT parameter is used to convert filename of the new logfile on the primary database to the filename of a log file on the standby database.
- STANDBY_FILE_MANAGEMENT parameter is used to enable automatic standby file management. When it is enabled, the automatic file operations are replicated from the primary database to the standby database. It is disabled by default.
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(proddb,stbdb)';
ALTER SYSTEM SET DB_NAME=proddb;
ALTER SYSTEM SET DB_UNIQUE_NAME=proddb;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB';
ALTER SYSTEM SET log_archive_dest_2='service=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBDB';
ALTER SYSTEM SET fal_server=STBDB;
ALTER SYSTEM SET fal_client=PRODDB;
ALTER SYSTEM SET standby_file_management=auto;
ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/STBDB','/u01/app/oracle/oradata/PRODDB' scope=spfile;
ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/STBDB','/u01/app/oracle/oradata/PRODDB' scope=spfile;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
3. modify tnsnames.ora file and add TNS entry of standby database
STBDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.188)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODDB)
)
)
PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.115)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)
4. restart database, take a full backup, then create standby controlfile for standby database, and parameter file, password file from primary database
STARTUP FORCE;
BACKUP DATABASE FORMAT '/home/oracle/backup/full_backup_%U.bkp' PLUS ARCHIVELOG FORMAT '/home/oracle/backup/archive_backup_%U.bkp';
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/backup/control01.ctl';
CREATE PFILE='/home/oracle/backup/pfile.ora' FROM SPFILE;
cp $ORACLE_HOME/network/admin/tnsnames.ora /home/oracle/backup/
cp /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwproddb /home/oracle/backup/
5. move files to the second node
/* on node2 */
mkdir /home/oracle/backup
/* on node1 */
scp /home/oracle/backup/* <node2>:/home/oracle/backup
6. paste files to directory of node 2
/* bash */
cd /home/oracle/backup/
mv control01.ctl /u01/app/oracle/oradata/STBDB/controlfile/
cp /u01/app/oracle/oradata/STBDB/controlfile/control01.ctl /u01/app/oracle/fast_recovery_area/STBDB/controlfile/
mv tnsnames.ora $ORACLE_HOME/network/admin
cp /home/oracle/backup/orapwproddb /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwproddb
lsnrctl start
tnsping STBDB
tnsping PRODDB
vi pfile.ora
audit_file_dest='/u01/app/oracle/admin/stbdb/adump'
control_files='/u01/app/oracle/oradata/STBDB/controlfile/control01.ctl','/u01/app/oracle/fast_recovery_area/STBDB/controlfile/control01.ctl'
db_file_name_convert='/u01/app/oracle/oradata/PRODDB','/u01/app/oracle/oradata/STBDB'
fal_server='PRODDB'
log_archive_dest_1='location=/u01/app/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBDB'
log_archive_dest_2='service=STBDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDB'
log_file_name_convert='/u01/app/oracle/oradata/PRODDB','/u01/app/oracle/oradata/STBDB'
db_unique_name='stbdb'
/* SQL*Plus */
STARTUP NOMOUNT PFILE='/home/oracle/backup/pfile.ora';
CREATE SPFILE FROM PFILE='/home/oracle/backup/pfile.ora';
/* RMAN(1) */
RESTORE DATABASE;
RESTORE ARCHIVELOG ALL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE OPEN READ ONLY;
/* RMAN(2) */
rman target sys/asdf0930k@proddb auxiliary sys/asdf0930k@stbdb
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER;
7. test the log transport and apply process
ALTER SYSTEM SWITCH LOGFILE;
/* node1 */
ALTER SESSION SET nls_date_format='HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
/* node2 */
ALTER SESSION SET nls_date_format='HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
SELECT * FROM v$archive_gap;
Data Guard Protection Modes
There are three protection modes in data guard: 1) Maximum Protection 2) Maximum availability 3) Maximum Performance
SYNC (synchronous) specifies that the redo must have been received before the transaction can commit.
ASYNC (asynchronous) is the default mode and specifies that the redo doesn't need to be received before the transaction can commit.
AFFIRM is the default value when the SYNC attribute is used and specifies that a redo transport destination acknowledges received redo data after writing it to the standby redo log.
NOAFFIRM is the default value when ASYNC attribute is used and specifies that a redo transport destination acknowledges received redo data before writing it to the standby redo log.
Maximum Protection mode ensures that there will be no data loss if the primary database fails. It doesn't allow to commit until redo data is written to online redo log file of primary database and standby log file of at least one of standby databases.
Maximum Availability mode provides the data protection and doesn't commit the transaction until the redo data has been written to both online redo log files of the primary database and standby redo log files of at least one standby database.
Maximum Performance is the default that mode provides the data protection without affecting the performance of the database by writing committed data to the redo log files and asynchronously propagating them to the standby database. If there's a network failure between primary and standby database, the primary database will be up and running. Committed transactions will be propagated to the standby once it will be available.
/* check protection mode of data guard */
SELECT protection_mode FROM v$database;
/* change the protection mode */
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE (AVAILABILIRY | PERFORMACE | PROTECTION);
