In this article we will prepare primary server for active data guard. Before moving forward I recommend creating scripts mentioned in the previous article. Let's review the items we need to configure on the primary server.

• Check and configure primary database for archivelog mode
• Create parameter file and append parameters for active data guard configuration
• Create password file for Redo Transport Authentication
• Setup listener
• Setup tnsnames
• Turn Flashback On
• Create standby redo logs
• Take a full backup of the primary database
• Create a standby control file
• Create a standby parameter file

Quite a list, right? Don't worry, we will go through each step in detail.

Configure archivelog mode
Before turning on the archivelog mode we need to check if Fast Recovery Area is configured. What is a Fast Recovery Area ? In very simple terms: it is a special location where the database creates and manages files required for backup and recovery. The Fast Recovery Area can contain control files, online redo logs, flashback logs and RMAN backups.

You use two parameters to configure and set the size of the fast recovery area.

• db_recovery_file_dest=+FRA
• db_recovery_file_dest_size=20G

Go to SQL prompt.Let's use our sqlplus script from the previous article.

[oracle@primary_server ora_scripts] > ./sqlplus.sh

SQL > alter system set db_recovery_dest='+FRA' scope='both';
SQL > alter system set db_recovery_file_dest_size=20G scope='both';

These parameters enable fast recovery area without shutting down the database. We are specifying +FRA as the location for our fast recovery area. +FRA is an ASM directory on one of the ASM disks. We are setting the size of our fast recovery area to 20G. You can set the FRA size according to your needs. Easy, huh?

With fast recovery area configured, next step is to verify and enable the archivelog mode on the primary database.

Go to SQL prompt. Let's use the sqlplus.sh script we created in the previous article.

[oracle@primary_server ora_scripts] > ./sqlplus.sh

Now at the SQL prompt run the SQL below.

SQL > select log_mode from v$database;

If primary database is not in the archivelog mode then shutdown the database, mount it, turn on the archivelog and then open the database. Here are the 4 simple steps.

SQL > shutdown immediate;
SQL > startup mount;
SQL > alter database archivelog;
SQL > alter database open;

//finally turn on force logging
SQL > alter database force logging;

Note: You should see +FRA directory on your ASM disks. Under +FRA you will also notice ARCHIVELOG directory.In order to view and manage ASM files use ASM command line client. Remember the asm script we created in the previous article? Run ./asm.sh to initiate ASM command line client. Then use ASMCMD commands to review the contents on ASM disks

Create & configure the parameter file
Let's create a directory named ora_files. We will store parameter file, password file and few other files in this directory. Consider ora_files directory a temporary staging area. Later on we will copy files from this staging location to standby database server. This is just a personal preference: I like to keep files or assets to be moved to standby server(s) under one location.

/* create directory at your preferred location */
[oracle@primary_server u01] > mkdir ora_files

With ora_files directory in place, we will create the parameter file.

Go to SQL prompt by running the sqlplus.sh script.

[oracle@primary_server ora_scripts] > ./sqlplus.sh

Now run the command below to generate the parameter file.

SQL > create pfile='/ora_files/initprimary.ora' from spfile;

On the primary database log_archive_dest_1 is set to the fast recovery area (+FRA). Oracle docs mention setting the remote_login_passwordfile parameter to exclusive. By default Oracle installation sets it to exclusive, and therefore no need to modify this parameter.

Open the parameter file in a vi editor.

[oracle@primary_server ora_files] vi initprimary.ora

Append these parameters at the end of the file.

/*Unique database names for primary and secondary*/
db_unique_name = cssdev

/*two location for archiving logs. cssdev and cssdg1*/
log_archive_config='DG_CONFIG=(cssdev, cssdg1)'

/*from primary write logs to a second destination cssdg1*/
log_archive_dest_2='SERVICE=cssdg1 LGWR ASYNC VALID FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cssdg1'

/*enable secondary archive destination*/
log_archive_dest_state_2=enable

/*failover server will be cssdg1 from the primary*/
fal_server=cssdg1

/*set file management to auto for the standby database*/
standby_file_management=auto

Note: How to save in a vi editor? Press "Esc" key on the keyboard while in the vi editor and then type in :wq to save the changes.

Now time to apply these parameters to the primary database. Create a new spfile from the modified parameter file and bounce the instance. Oracle will automatically create a name for the spfile. After bouncing the instance verify the changes we made are in effect.

Go to SQL prompt.

SQL > create spfile  from pfile='/ora_files/initprimary.ora';

/* bounce the instance */
SQL > shutdown immediate;
SQL > startup;

/*verify fal_server (a parameter we appended earlier) */
SQL > show parameter fal_server; //output should be 'cssdg1' 

Redo Transport setup
What is redo transport? It is how the redo logs get shipped between primary and standby databases. As per Oracle docs, redo transport must be done via Secure Sockets Layer or the password file. For my project I used password file and therefore I will show you how to configure redo transport with a password file.

Important: Password file must be created on the primary database and then copied over to all standby database(s). Do not create a password file on standby database(s).

It is critical to understand this concept. Two password files created with the same username and password are not identical. Why? Because when you create a password file oracle encrypts these files and encryption results for two files are never the same.

Therefore, a golden rule for Redo Transport: Create a password file on the primary database and copy it over to standby database(s).

On the primary server we will use orapwd utility to generate a password file. Before running the orapwd utility, let's set the ORACLE_HOME. Why? Well, I ran into a weird error "Unable to find error file". Whatever that means, really!

[oracle@primary_server ora_scripts]  export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/

Next, run orapwd at the terminal.

[oracle@primary_server ora_scripts] cd $ORACLE_HOME/bin

[oracle@primary_server bin] ./orapwd file=$ORACLE_HOME/dbs/orapwcssdev password=yourpassword force=y

In the commands above, I am changing the directory to ORACLE_HOME/bin directory. Next, I am calling orapwd to create a password file. Password files must be named as orapw and then append the ORACLE_SID to it. In our case it is cssdev. Therefore a full name for the password file is: orapwcssdev.

Note: By default orapwd generates the password file under $ORACLE_HOME/dbs directory.

Later on we will copy this file on to standby server under $ORACLE_HOME/dbs directory.

Identical password file ensures primary and standby databases can communicate with each other. Refer to Oracle docs for further information on how to manage the password files.

Listener Setup
We need to prepare the listener for incoming requests. Let's open up listener.ora file in the vi editor. Listener file is located under oracle/product/11.2.0/grid/network/admin.

[oracle@primary_server ora_scripts] cd oracle/product/11.2.0/grid
[oracle@primary_server grid] cd network/admin
[oracle@primary_server admin] vi listener.ora  

Copy and paste the text below to the listener.ora file.

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

SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME = cssdev)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = cssdev)
     )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

Remember this: It is always a good practice to test listener and tns changes to make sure the changes are working as expected.

Therefore, let's test our listener.

[oracle@primary_server ora_scripts] export ORACLE_HOME='/u01/app/oracle/product/11.2.0/grid'
[oracle@primary_server ora_scripts] cd $ORACLE_HOME/bin
[oracle@primary_server bin] ./lsnrctl status

In the script above, we set the ORACLE_HOME to grid, then change the local working directory to ORACLE_HOME/bin and execute tnslsnr status. If the listener is configured correctly then you should see an almost similar output.

//running status on tnslsnr should show the output below
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2015 21:38:32

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                31-DEC-2014 17:45:41
Uptime                    1 days 3 hr. 52 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/prmary_server/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "cssdev" has 2 instance(s).
  Instance "cssdev", status UNKNOWN, has 1 handler(s) for this service...
  Instance "cssdev", status READY, has 1 handler(s) for this service...
Service "cssdevXDB" has 1 instance(s).
  Instance "cssdev", status READY, has 1 handler(s) for this service...
The command completed successfully

Configure Service Names
Listener is configured. Next step is to configure service names for the primary server. Network service names are defined in tnsnames.ora file. Locate tnsnames.ora under $ORACLE_HOME/network/admin. Open tnsnames.ora file in the vi editor.

[oracle@primary_server ora_scripts] cd $ORACLE_HOME
[oracle@primary_server dbhome_1] cd network/admin
[oracle@primary_server ora_scripts] vi tnsnames.ora

Now copy and paste the content below in the vi editor.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CSSDEV =
  (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     )
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cssdev)
    )
  )

CSSDG1 =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cssdg1)(UR=A)
  )
 )

Note: How to save while in a vi editor? Press "Esc" and type :wq to save the content of the vi editor.

Just like how we tested listener we will test the tnsnames.

[oracle@primary_server ora_scripts] > export $ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@primary_server ora_scripts] > cd $ORACLE_HOME
[oracle@primary_server dbhome_1] > cd bin

/*test cssdev tns name*/
[oracle@primary_server bin] > ./tnsping cssdev

If all went well then you should expect the output like below.

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2015 21:44:55

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cssdev)))
OK (0 msec)

Now perform similar test for the standby tnsname.

/*test cssdg1 tns name*/
[oracle@primary_server bin] > ./tnsping cssdg1

And this test should show you an output like below.

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2015 21:47:18

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cssdg1)(UR=A)))
OK (10 msec)

Enable Flashback Mode
Oracle recommends to run primary and standby databases in a Flashback Mode. During failover flashback logs will be used to reinstate the primary database. By default flashback retention period is set to 1 day. Read Oracle docs to learn more about flashback mode.

Go to SQL prompt.

[oracle@primary_server ora_scripts] > ./sqlplus.sh
SQL > alter system set db_flashback_retention_target=1440; // 1 day
SQL > alter database flashback on;

Optional Check: Similar to archive logs, flashback database generates flashback logs and stores them on the ASM disk along with other files. Log into ASMCMD to confirm the location of flashback logs.

Create Standby Redo logs
We must create standby redo logs on the primary database. In the event when primary takes a secondary role the redo data gets written to these standby logs. Remember after a switchover / failover primary database becomes a standby database. Plus, it is one of the requirements for running Observer. Later in the series we will learn more about the Observer.

Go to SQL prompt.

[oracle@primary_server ora_scripts] > ./sqlplus.sh
SQL > alter database add standby logfile group 4 '+DATA' size 50M;
SQL > alter database add standby logfile group 5 '+DATA' size 50M;
SQL > alter database add standby logfile group 6 '+DATA' size 50M;

Note: Statements above assumes you have 3 redo log groups configured for your primary database. If so you add 4, 5 and 6 as standby groups accordingly. If you have 5 redo log groups then standby groups will be 6, 7 and 8. You get the gist.

Primary database is now fully prepared for the active data guard. Next step is to take a full backup using RMAN.

Backup Primary Database
We will use primary database backup to create a physical standby database. Therefore, let's take a full backup using RMAN. Database is already running in the archivelog mode and therefore no need to shutdown the database for backups.

Go to RMAN prompt.

[oracle@primary_server ora_scripts] > ./rman.sh
RMAN > connect target;
RMAN > backup database plus archivelog;

These commands will generate a full backup with required archive logs and place it under Fast Recovery Area (+FRA).

Backupsets will be located under: +FRA/cssdev/BACKUPSETS
Autobackups will be located under: +FRA/cssdev/AUTOBACKUP

Create a Standby control file
Preferably, standby control file should be created after performing a full backup of the primary database.

Go to SQL prompt.

[oracle@primary_server ora_scripts] > ./sqlplus.sh
SQL > alter database create standby controlfile as '/ora_files/cssdg1.ctl';

Command above will create a control file for the standby and place it under ora_files directory.

Create a parameter file for the standby database
Finally we need to create a parameter file for the standby database. Easiest way to create this file is by using primary database's parameter file.

We will modify the standby parameter file in the next article when we prepare the standby database server. For now just create the file and place it under ora_files.

Go to SQL prompt.

[oracle@primary_server ora_scripts] > ./sqlplus.sh
SQL > create pfile='/ora_files/initcssdg1.ora' from spfile;

Command above will generate a new parameter file for the standby database.

Phew! That was some intense work. I commend your patience and courage for sticking with me. In the next article we will configure the standby database server.

See you then!