ORACLE DATA GUARD CONFIGURATION

PRIMARY

select force_logging from v$database;

alter database force logging;

• The standby log files are required to store redo received from the primary database. 
• Standby redo logs must be equal or larger to the largest redo log file in the primary database. 
• The standby redo log must have at least one more redo log group than the redo log on the primary database. 
• It is recommended that the standby redo log is created on the primary database and propagated to the standby for any switch over or fail over operations

select * from v$log;

alter database add standby logfile size 150M;

alter database add standby logfile size 150M;

alter database add standby logfile size 150M;

select * from v$logfile;

• The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. 
• The DB_UNIQUE_NAME of the primary database is TESTDB_001 and the DB_UNIQUE_NAME of the standby database is TESTDB_002. 
• The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is TESTDB_002.

• STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.

• Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.]

show parameter db_name
show parameter db_unique_name

alter system set log_archive_config='dg_config=(TESTDB_001,TESTDB_002)';

alter system set log_archive_dest_2='service=TESTDB_002 async valid_for=(online_logfile,primary_role) 

db_unique_name=TESTDB_002';

alter system set standby_file_management=AUTO;

archive log list;

Ensure the Oracle Net Services aliases for both the primary database and standby database are added to the tnsnames.ora file.

cat $ORACLE_HOME/network/admin/tnsnames.ora
TESTDB_001 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB_001.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB_001)
    )
  )
TESTDB_002 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB_002.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB_002)
    )
  )
Note: Copy the updated tnsnames.ora file to the standby site (host).

Standby Server: 

The Oracle database binaries have already been installed at this location ($ORACLE_HOME). The new standby database will have TESTDB_002 as the SID.
The listener on the standby site must be configured with a static service entry for the standby database. Restart the listener after modification.

cat listener.ora 
LISTENER =
  (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=TESTDB_002.localdomain)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))   
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=TESTDB_002)
          (SID_NAME=TESTDB_002)                      
          (ORACLE_HOME=/webdbapp/oracle/product/11.2.0/db_1)
         )
        )
   
lsnrctl start

Create audit directory files under 

$ORACLE_BASE/admin/$ORACLE_SID/adump. In my example, I will also create the Fast recover area and the oradata directories.
mkdir -p $ORACLE_BASE/admin/TESTDB_002/adump
mkdir -p /opt/oradata
mkdir -p /opt/fast_recovery_area
echo DB_NAME=TESTDB_002 > initTESTDB_002.ora
cat initTESTDB_002.ora 
DB_NAME=TESTDB_002
The primary database password file must be copied to the standby system for redo authentication.
scp TESTDB_001:$ORACLE_HOME/dbs/orapwTESTDB_001 orapwTESTDB_002

*****************************************************************

Standby Database Creation
Start the standby database instance in NOMOUNT start up mode:

startup nomount pfile=initTESTDB_002.ora;

Now that the configuration of the standby server is complete, let’s perform the duplication from the primary site.

Primary Server

Use the Recovery Manager (RMAN) to duplicate the primary database to the standby database.
Invoke RMAN; connect to the primary database as the sys user. Make an auxiliary connection to the standby instance:

RMAN> 
connect target sys
target database Password: 
connected to target database: TESTDB_001 (DBID=1644812106)
RMAN> 
connect auxiliary sys@TESTDB_002

auxiliary database Password: 
connected to auxiliary database: TESTDB_002 (not mounted)

RMAN> 
run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'TESTDB_001','TESTDB_002'
set db_unique_name='TESTDB_002'
set db_file_name_convert='/TESTDB_001/','/TESTDB_002/'
set log_file_name_convert='/TESTDB_001/','/TESTDB_002/'
set control_files='/opt/oradata/TESTDB_002/TESTDB_002.ctl'
set log_archive_max_processes='5'
set fal_client='TESTDB_002'
set fal_server='TESTDB_001'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(TESTDB_001,TESTDB_002)'
set log_archive_dest_2='service=TESTDB_001 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TESTDB_001'
;
}
[RMAN duplication process completed, just do log switch for check whether it is applied to the standby]

alter system switch logfile;

Standby Server

• On the standby database, run the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE command to start redo apply.
• The USING CURRENT LOGFILE means the redo is applied as soon as it is received on the standby.
• The DISCONNECT option means the redo apply will run in background session.

alter database recover managed standby database using current logfile disconnect;

select sequence#, first_time, applied from v$archived_log order by sequence#;

Primary Server
Let’s perform 3 additional log switches on the primary database. 
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

Standby Server
we query the standby database. The logs were successfully transported and applied.

select sequence#, first_time, applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM APPLIED
---------- --------- ---------
         15 16-MAR-13 YES
        16 16-MAR-13 YES
        17 16-MAR-13 YES
        18 16-MAR-13 YES
        19 16-MAR-13 IN-MEMORY

-----------------------------------------------------------------