oracledba.help
Legacy

Data Guard (12.1)

<- Legacy

Overview

The following covers the creation of Oracle 12c Data Guard environment.
The examples here use: 

 Hostname  Role     DB_NAME  DB_UNIQUE_NAME  ORACLE_SID (Env Var)
 --------  -------  -------  ---------------  --------------------
 lnx01     Primary  oradb    oradb             oradb    
 lnx02     Standby  oradb    oradb_sb          oradb_sb

 db_create_file_dest        = /u01/oradata
 db_recovery_file_dest      = /u02/oradata
 db_recovery_file_dest_size = 15G

 Primary DB created with 4 Redo Log Groups.

SB = Standby

TOC

Prerequisites

  • You have two systems with the same OS (ex: Oracle RHEL 7.4).
  • The OS's have been prepped for Oracle. ⇒
  • Both systems have Oracle 12c database product installed. ⇒?
  • The primary system has:
    • A fully operational Oracle 12c database. ⇒
    • REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE.
      sho parameter REMOTE_LOGIN_PASSWORDFILE
    • LOG_MODE set to ARCHIVELOG.
      SELECT log_mode FROM v$database;
  • Get control file names and paths on Primary. 
col NAME_COL_PLUS_SHOW_PARAM  format A15
col TYPE                      format A6
col VALUE_COL_PLUS_SHOW_PARAM format A150
show parameter control_files
These values will be used in the RMAN script that creates SB.

Primary Database Changes

Enable Forced Logging

sqlplus / as sysdba
ALTER DATABASE FORCE LOGGING;

Create Standby Redo Log (SRL) Files

  • The SIZE must be the same as your redo log files.
  • The standby-redo-logs must have at least one more than the redo logs on the primary database. So if your Primary has 4 create 5.
OMF Example: 
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 7 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 8 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 9 SIZE 200M;
If using ASM, run this command 5 times for your FRA. 
ALTER DATABASE ADD STANDBY LOGFILE '+FRA' SIZE 200M;
Example added files in +FRA/ORADB/ONLINELOG: 
group_5.263.950869865
group_6.264.950869993
group_7.265.950870001
group_8.266.950870003
group_9.267.950870005
Verify SRL Files Created
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Set Data Guard Parameters

alter system set log_archive_max_processes=10                    scope=both;
alter system set log_archive_config='DG_CONFIG=(oradb,oradb_sb)' scope=both;
alter system set log_archive_dest_1='LOCATION=/u01/oradata/ORADB/onlinelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb' scope=both;
alter system set log_archive_dest_2='SERVICE=oradb_sb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_sb' scope=both;
alter system set log_archive_dest_state_1=ENABLE              scope=both;
alter system set db_file_name_convert='/ORADB_SB/','/ORADB/'  scope=spfile;
alter system set log_file_name_convert='/ORADB_SB/','/ORADB/' scope=spfile;
alter system set fal_server=oradb_sb                          scope=both;
alter system set standby_file_management='AUTO'               scope=both;

Network Changes

tnsnames.ora

Add the same entries on both systems.
gedit $ORACLE_HOME/network/admin/tnsnames.ora 

LISTENER_ORADB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))

ORADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

ORADB_SB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb_sb)
    )
  )

Comments:

  • Change the LISTENER_ entry accordingly on the SB. 
LISTENER_ORADB_SB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
  • SERVER = DEDICATED is required for many DGMGRL communications.

Configure Listener

Insert static entries in the listener.ora ensuring the DGMGRL can start\stop databases.
Examples: Primary | Standby. 

 a. Stop Listener: lsnrctl stop
 b. cd $ORACLE_HOME/network/admin
 c. cp listener.ora listener.ora.orig
 d. Make changes appropriate to system (Primary\Standby).
    gedit $ORACLE_HOME/network/admin/listener.ora
 e. Start Listener: lsnrctl start

The listener on the SB does not exist yet so does not need to be stopped. Just started after configured.

Standby Database Changes

1. Create directories on Standby.

If the Primary uses ASM ensure the Standby has the same groups (ex: +GRID, +FRA, +DATA).

2. Create a password file.

Make the password the same as the SYS password on the Primary.
orapwd file=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapworadb_sb password=go entries=10 force=y
If newly upgraded to 12.2 include: format=12

3. Create a parameter file and start database.

gedit $ORACLE_HOME/dbs/init_tmp.ora 
DB_NAME='oradb'
DB_UNIQUE_NAME='oradb_sb'
DB_BLOCK_SIZE=8192

sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init_tmp.ora';

4. Test Connectivity 

sqlplus sys/go@oradb as sysdba
SQL> SELECT instance_name FROM v$instance;

sqlplus sys/go@oradb_sb as sysdba
SQL> SELECT instance_name FROM v$instance;

5. Perform RMAN Duplicate database operation.

rman target sys/go@oradb auxiliary sys/go@oradb_sb 
connected to target database: ORADB (DBID=2703748640)
connected to auxiliary database: ORADB (not mounted)
RMAN>
Run corresponding RMAN commands: OMF | Legacy Files
  • Once the RMAN restore has completed the primary database and standby database will exist.
  • Successful RMAN operation shows similar message to below: 
     Finished Duplicate Db at 23-AUG-17
 released channel: p1
 released channel: p2
 released channel: a1
  • Ensure standby_file_management = AUTO on Standby.
    sqlplus sys/go@oradb_sb as sysdba
    SQLPlus> show parameter standby_file_management
    SQLPlus> alter system set standby_file_management='AUTO' scope=both;

6. Start Recovery and Ensure Logs Being Transferred 

 -- On SB
 alter system set log_archive_dest_2='' scope=both; # Required for 12.x
 alter database recover managed standby database disconnect from session;

 -- On Both
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

 -- On Primary
 ALTER SYSTEM SWITCH LOGFILE; 
 ALTER SYSTEM SWITCH LOGFILE;

 -- On Both
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

Note: In the next section the DG Broker will automatically set the correct values for LOG_ARCHIVE_DEST_2 .

Enable Broker

 -- On Both set dg_broker_start.
 SQLPlus> ALTER SYSTEM SET dg_broker_start=true sid='*' scope=both;

 -- On Primary CREATE CONFIGURATION and ADD DATABASE
 dgmgrl sys/go@oradb

 DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS oradb CONNECT IDENTIFIER IS oradb;
 Configuration "dg_cfg1" created with primary database "oradb"

 DGMGRL> ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
 Database "oradb_sb" added

 DGMGRL> ENABLE CONFIGURATION;
 Enabled.

 Check Status?

If show configuration; displays any ORA-16nnn errors they should resolve automatically in a few minutes. If not, on the SB do a shutdown immediate then startup mount;

Ensure FAL Entries Still Set

show parameter fal 

 -- On Primary
 alter system set fal_server="oradb_sb" scope=both;
 alter system set fal_client="oradb"    scope=both;

 -- On SB
 alter system set fal_server="oradb"    scope=both;
 alter system set fal_client="oradb_sb" scope=both;

Enable Flashback Database

 -- On Primary 
 sqlplus / as sysdba
 ALTER DATABASE FLASHBACK ON;

 -- On SB
 dgmgrl sys/go@oradb_sb
 DGMGRL> edit database oradb_sb set state=apply-off;
 DGMGRL> sql "alter database flashback on";
 DGMGRL> edit database oradb_sb set state=apply-on;

 -- On Both (QC check)
 SQLPlus> SELECT flashback_on FROM v$database;

The standby redo apply services must be stopped on the Standby before flashback database can be enabled.

If Flashback Database is not enabled, you will have to manually recreate your primary as a standby in a failover event to reinstate it!

APPENDIX

Create Directories

OMF Example

As root:
mkdir -p /u01/app/oracle/admin/oradb/adump
chown -R oracle:oinstall /u01/app/oracle/admin/oradb/adump
chmod -R 775 /u01/app/oracle/admin/oradb/adump

mkdir -p /u01/oradata/ORADB/controlfile
mkdir -p /u01/oradata/ORADB/datafile
mkdir -p /u01/oradata/ORADB/onlinelog

mkdir -p /u01/oradata/ORADB_SB/controlfile
mkdir -p /u01/oradata/ORADB_SB/datafile
mkdir -p /u01/oradata/ORADB_SB/onlinelog

mkdir -p /u02/oradata/ORADB/archivelog
mkdir -p /u02/oradata/ORADB/autobackup
mkdir -p /u02/oradata/ORADB/controlfile
mkdir -p /u02/oradata/ORADB/onlinelog

mkdir -p /u02/oradata/ORADB_SB/archivelog
mkdir -p /u02/oradata/ORADB_SB/autobackup
mkdir -p /u02/oradata/ORADB_SB/controlfile
mkdir -p /u02/oradata/ORADB_SB/onlinelog

chown -R oracle:oinstall /u01/oradata
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u01/oradata
chmod -R 775 /u02/oradata

Standard Datafiles Example

As root:
mkdir -p mkdir -p /u01/app/oracle/admin/oradb/adump

mkdir -p mkdir -p /u01/oradata/ORADB/fbr
mkdir -p mkdir -p /u01/oradata/ORADB/arc
mkdir -p mkdir -p /u01/oradata/ORADB/ctl
mkdir -p mkdir -p /u01/oradata/ORADB/dbf
mkdir -p mkdir -p /u01/oradata/ORADB/rdo

mkdir -p mkdir -p /u02/oradata/ORADB/arc
mkdir -p mkdir -p /u02/oradata/ORADB/ctl
mkdir -p mkdir -p /u02/oradata/ORADB/dbf
mkdir -p mkdir -p /u02/oradata/ORADB/rdo

mkdir -p /u02/oradata/oradb/arc
mkdir -p /u02/oradata/oradb/ctl

chown -R oracle:oinstall /u01/app/oracle/admin/oradb/adump
chown -R oracle:oinstall /u01/oradata
chown -R oracle:oinstall /u02/oradata

chmod -R 775 /u01/app/oracle/admin/oradb/adump
chmod -R 775 /u01/oradata
chmod -R 775 /u02/oradata

ASM Example

mkdir -p mkdir -p /u01/app/oracle/admin/oradb/adump
chown -R oracle:oinstall /u01/app/oracle/admin/oradb/adump
chmod -R 775 /u01/app/oracle/admin/oradb/adump

mkdir +FRA/ORADB
mkdir +FRA/ORADB/ARCHIVELOG/
mkdir +FRA/ORADB/AUTOBACKUP/
mkdir +FRA/ORADB/CONTROLFILE/
mkdir +FRA/ORADB/ONLINELOG/

mkdir +DATA/oradb
mkdir +DATA/oradb/ARCHIVELOG/
mkdir +DATA/oradb/AUTOBACKUP/
mkdir +DATA/oradb/CONTROLFILE/
mkdir +DATA/oradb/ONLINELOG/
mkdir +DATA/oradb/PARAMETERFILE/

RMAN Duplicate

OMF Example

Make sure to enter the control file names from the Primary.
rman target sys/go@oradb auxiliary sys/go@oradb_sb 

run 
{ 
   allocate channel p1 type disk; 
   allocate channel p2 type disk; 
   allocate auxiliary channel a1 type disk; 
   duplicate target database for standby from active database 
   spfile 
      parameter_value_convert 'ORADB','ORADB_SB' 
      set db_unique_name='oradb_sb' 
      set db_file_name_convert='ORADB','ORADB_SB' 
      set control_files='/u01/oradata/ORADB/controlfile/o1_mf_dtx70t9k_.ctl',
                        '/u02/oradata/ORADB/controlfile/o1_mf_dtx70tb3_.ctl' 
      set log_archive_max_processes='10' 
      set fal_server='oradb'
      set fal_client='oradb_sb'
      set db_create_file_dest='/u01/oradata' 
      set db_recovery_file_dest='/u02/oradata'  
      set log_archive_config='dg_config=(oradb,oradb_sb)' 
      set log_archive_dest_1='LOCATION=/u01/oradata/ORADB_SB/onlinelog/ 
          VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb_sb' 
      set standby_file_management='AUTO' 
   nofilenamecheck;
}

Legacy Files Example

run                   
{                     
   allocate channel p1 type disk; 
   allocate channel p2 type disk; 
   allocate auxiliary channel a1 type disk; 
   duplicate target database for standby from active database 
   spfile             
      parameter_value_convert 'ORADB','ORADB_SB' 
      set db_unique_name='ORADB_SB' 
      set control_files='/u01/oradata/oradb/ctl/control01.ctl', 
                        '/u02/oradata/oradb/ctl/control02.ctl' 
      set db_create_file_dest='/u01/oradata/oradb/dbf' 
      set db_create_online_log_dest_1='/u01/oradata/oradb/rdo' 
      set db_recovery_file_dest='/u01/oradata/oradb/fbr' 
      set db_recovery_file_dest_size='25G' 
  nofilenamecheck;    
}

ASM Example

run                   
{                     
   allocate channel p1 type disk; 
   allocate channel p2 type disk; 
   allocate auxiliary channel a1 type disk; 
   duplicate target database for standby from active database 
   spfile             
      parameter_value_convert 'ORADB','ORADB_SB' 
      set db_unique_name='ORADB_SB' 
      set control_files='+DATA/oradb/CONTROLFILE/Current.256.950864971', 
                        '+FRA/ORADB/CONTROLFILE/Current.256.950864971' 
      set db_create_file_dest='+DATA' 
      set db_recovery_file_dest='+FRA' 
      set db_recovery_file_dest_size='15G' 
  nofilenamecheck;    
}

SRL Legacy Files Example

 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo01.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo02.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo03.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo04.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo05.rdo') SIZE 200M;

Listener Examples

Notice both systems have both entries for DGMGRL. This is so the corresponding database can be restarted.

Primary

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC =
	    (SID_NAME = oradb)
	    (GLOBAL_DBNAME = oradb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb_sb)
	    (GLOBAL_DBNAME=oradb_sb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Standby

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC =
	    (SID_NAME = oradb_sb)
	    (GLOBAL_DBNAME = oradb_sb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb_sb)
	    (GLOBAL_DBNAME=oradb_sb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
  )

ADR_BASE_LISTENER = /u01/app/oracle

If needed: alter system set local_listener='' scope=both;

Warning: ORA-16714: the value of property xxx is inconsistent with the member setting

  1. On Primary get the current correct value.
    SQLPlus> show parameter <param_name>
  2. Set the value(s) on the Standby

Common Changes Example 

 alter system set log_archive_max_processes=4    scope=both sid='*';
 alter system set archive_lag_target=0           scope=both sid='*';
 alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
 alter system set data_guard_sync_latency=0      scope=both sid='*'; 

 show parameter log_archive_max_processes;
 show parameter archive_lag_target;
 show parameter log_archive_min_succeed_dest;
 show parameter data_guard_sync_latency;