Oracle introduced the ADG (Active DataGuard) read-only mode DataGuard in 11g version to replace the DG mode in 10g, which can only be mount ed but cannot be opened. There are also some improvements in the way of building. This article lists two different ways of building and their differences in detail.

General description of two DG construction modes:

1. Use the backup setup method:

This is Oracle 10g's regular DG setup, 11g is still available, its general steps are to restore the backup copy of the primary library to the backup library, turn on redo apply increment in the backup mount state, and then cancel redo apply, open the backup library and reopen redo apply.

2. Use dupilicate to fetch data directly from the master library:

This is Oracle 11g's new stream replication method, which allows you to restore data directly from the current master library in the backup, then open the backup directly and start the MRP process.

The following are the specific steps to build a DG in two ways (Oracle version 11.2.0.4, SID orcl, single machine environment in this paper).

1. Backup method to set up DataGuard

1. The main library opens archiving and sets Force Logging mode.

Don't go into details about opening archives, just list the statements that set Force Logging:

SQL> alter database force logging;
SQL> select force_logging from v$database;

2. standby redo log when the primary library is added as a standby

This also saves the steps for creating standby log s for the standby libraries, as follows:

standby redo is created by the principle that there is one more set of redo log s than the primary library, so there are four sets in this example.

3. Modify the tnsnames.ora file and copy it to the repository to replace the previous tnsnames.ora file.

This step is to allow the primary and standby libraries to connect to each other through tns aliases, which can be configured using the netmgr tool (recommended as errors can be avoided).

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.140.84)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL_ST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.140.208 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

Then copy the tnsnames.ora file as above to the $ORACLE_HOME/network/admin directory of the node where the repository is located to replace the previous tnsnames.ora.(Do not do this in production because previous tnsnames.ora files may contain other alias, and direct replacement may cause unnecessary hassle)

4. The main library generates pfile s and modifies them.

There are two main things to do in this step: 1) Modify the DG parameters related to the primary library 2) Modify the DG parameters related to the standby library by modifying the pfile parameters, in which the primary library can be modified online as follows:

The main library modifies DG parameters online: (the main database directory is the E disk, the standby is the D disk, and db_file_name_convert takes effect when it is used as the standby, so here it is written as D followed by E, and the standby parameter file is the opposite)

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_st)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcl_st reopen=120 lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_st' scope=both;
alter system set fal_server=orcl_st scope=both;
alter system set db_file_name_convert='D:\app\oracle\oradata\','E:\app\oracle\oradata\' scope=spfile; --This parameter does not need to be changed if the primary and backup catalogs are identical
alter system set log_file_name_convert='D:\app\oracle\oradata\','E:\app\oracle\oradata\' scope=spfile; --This parameter does not need to be changed if the primary and backup catalogs are identical
alter system set standby_file_management=AUTO scope=both;

The fal_client parameter was cancelled in 11g, so there is no need to set it. Next, pass the pfile generated by the primary library to the standby library and modify it to the following:

*.audit_file_dest='D:\app\oracle\admin\orcl\adump'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oracle\oradata\orcl\CONTROLFILE\control01.ctl','D:\app\oracle\oradata\orcl\CONTROLFILE\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl_st'
*.db_recovery_file_dest='D:\app\oracle\flash_recovery_area\'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='D:\app\oracle'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=1717567488 --Can be deleted
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300 
*.processes=150
*.remote_login_passwordfile='exclusive'
*.log_archive_config='DG_CONFIG=(orcl,orcl_st)'
*.log_archive_dest_1='location=E:\arch valid_for=(all_logfiles,all_roles)  db_unique_name=orcl_st'
*.log_archive_dest_2='SERVICE=orcl reopen=120 lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.db_file_name_convert='E:\app\oracle\oradata\','D:\app\oracle\oradata\'
*.log_file_name_convert='E:\app\oracle\oradata\','D:\app\oracle\oradata\'
*.standby_file_management=AUTO
*.remote_login_passwordfile='exclusive'
*.fal_server='orcl'
orcl.undo_tablespace='UNDOTBS1'　

5. Back up the primary library and transfer it to the backup folder with the same name.

run
{
     sql "alter system switch logfile";
     backup as compressed backupset database format 'C:\full_orcl_%d_%T_%s.bak';
     backup current controlfile for standby format 'C:\ctl_%d_%T_%s.bak';
     sql "alter system archive log current";
}

Note that the backup files must be transferred to the same folder of the standby node so that the control files can be identified as soon as possible, or catalog is required.

6. Create an instance in the standby and use pfile to launch to the nomount state.

export ORACLE_SID=orcl directly under Linux, or set SID after instance service creation using oradim tools under Windows: oradim-new-sid orcl and set ORACLE_SID=orcl

7.Copy password file and create a backup monitor

1) Copy the password file of the primary library to the password file location of the standby library.

2) Use netmgr to add orcl instances to listening, which is static listening, and lsnrctl reload to restart listening.

8. Restore backups in the backup library and create a DataGuard

rman target sys/oracle@orcl auxiliary /
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK; --Main and standby file directories need to be identical, remove inconsistencies nofilenamecheck parameter,And must be configured db_file_name_convert Parameters.

restore standby controlfile from 'xxx';
This allows restore repositories to be restored in the normal rman way, incremental by archiving backups, and different paths can be specified in the run module.

9.Open the backup library and start the MRP process.

The backup library is automatically mount ed after the restore is complete, at which point the MRP process is opened directly for synchronization because the primary backup database data is inconsistent:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Then use the following commands in the standby to observe the application log of the standby:

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
SELECT thread#,sequence#,applied from v$archived_log;

The main library uses the following commands to check for synchronization errors:

SELECT DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODE from v$archive_dest WHERE TARGET='STANDBY';

After the synchronization is complete, pause the main library and cancel the MRP process in the standby library:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Then open the repository and re-open the MRP process:

ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

10. Validate data synchronization (omitted).

2. Duplicate way to set up DataGuard

The first four steps are identical to the method one, the fifth step is cancelled without doing it, and the sixth and seventh steps are identical. Start with step 8 below:

8. Use duplicate directly from the main restore database in the standby.

rman target sys/oracle@orcl auxiliary sys/oracle@orcl_st
RMAN> duplicate target database for standby from active database dorecover; --If the data directories are identical, add NOFILENAMECHECK parameter

The database will be mount ed when it is finished, and this step is usually an error because the listener is not properly matched or the password file is incorrect.

9. open the standby library directly and start the MRP process.

ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

10. Validate data synchronization (omitted).

Comparison of the two ways:

1. Use backup to stay away from the network, but you may need to pause the main library for the backup redo apply to catch up, then cancel the MRP process, open the database, and start the MRP process again.

2. The Duplicate approach is simpler and more understandable, but it will be slower when there is a lot of data on the network.

The choice between the two methods depends on the specific situation, each has its own advantages and disadvantages. Generally speaking, novices will use the second method faster.