In the previous article we configured primary server for the active data guard. Now it's time to prepare the standby server for active data guard. As the title suggests: Yes it is standby database server's turn now.

Let's review the steps required for preparing the standby database server.

• Copy init file from primary server
• Prepare init file and create an spfile for the standby server
• Copy standby control file from primary server
• Configure & test listener
• Configure & test service names
• Copy backups from primary server ASM to standby server ASM
• Catalog backups with RMAN
• Restore Backup
• Start Redo apply
• Test redo log transport
• Turn Flashback on
• Review useful SQL for managing data guard environment

Again an exhaustive list, but not to worry. We will work hand-in-hand to go through each step. I can't let you have all the fun. I want some too :-)

Copy init file from primary server
In previous article we generated a standby init file. Now we need to copy this file onto standby server. Remember our staging location on the primary server? Yes, I am referring to ora_files. Create a similar staging location on the standby server.

[oracle@standby_server u01] mkdir ora_files

Now let's log into the primary server and go to ora_files staging location.

[oracle@primary_server u01] cd ora_files
/* copy init file */
[oracle@primary_server ora_files] scp initcssdg1.ora oracle@standby_server:/u01/ora_files/initcssdg1.ora

Command above will copy standby init file from primary to standby server.

Prepare init file and create an spfile for the standby server
In the previous article we prepared an init file on the primary server and applied it to the primary database. Similarly, we need to prepare init file for the standby server and make standby database aware of the primary server.

Go to ora_files staging location on the standby server and open the initcssdg1.ora file in the vi editor. This is the same file we copied in the previous section.

[oracle@standby_server u01] cd ora_files
[oracle@standby_server ora_files] vi initcssdg1.ora

Now append the parameters below at the end of initcssdg1.ora.

/*location of the standby control file*/
control_files='+DATA/cssdg1/controlfile/cssdg1.ctl'

/*database unique name*/
db_unique_name='cssdg1'

/*fail server (this will be cssdev)*/
fal_server='cssdev'

/*log_archive_dest_2*/
log_archive_dest_2='SERVICE=cssdev async 
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=cssdev'

Now, let's create an SPFILE from this parameter file.

Go to SQL prompt.

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

Command above should generate an SPFILE under dbs directory of your ORACLE_HOME.

Copy standby control file
We created standby control file while preparing the primary server. Let's create a password for the sys user on the ASM instance.

Log into ASM instance on the standby server.

[oracle@standby_server ora_scripts] ./asmsqlplus.sh
SQL> connect /as sysasm
SQL> alter user sys identified by 'password'

Now it's time to copy this control file onto standby server's ASM disk. Take a look at the control_files parameter in the init file. We need to copy this control file to the same location where control_files parameter is pointing.

Here is the control_files parameter.
control_files='+DATA/cssdg1/controlfile/cssdg1.ctl'

Go to primary server and run ./asm.sh. Which should put you on to ASMCMD command line interface.

[oracle@primary_server ora_scripts] ./asm.sh
ASMCMD > cp /u01/ora_files/cssdg1.ctl sys@standby_server.1521.+ASM:+DATA/cssdg1/CONTROLFILE/cssdg1.ctl

We are taking the standby control file from staging directory and copying it to ASM disk on to standby server.

sys@standby_server.1521.+ASM indicates we are using sys account to log into standby_server via port 1521 on ASM instance.

If standby ASM instance is running on a default port then try copying file without specifying the port.

[oracle@primary_server ora_scripts] ./asm.sh
ASMCMD > cp /u01/ora_files/cssdg1.ctl sys@standby_server.+ASM:+DATA/cssdg1/CONTROLFILE/cssdg1.ctl

Configure & Test Listener
Locate the listener file under grid/network/admin on the standby server and open listener.ora file in the vi editor.

[oracle@standby_server admin] vi listener.ora

Copy the text below and replace the contents of listener.ora file.

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

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 = cssdg1)
   (SID_NAME = cssdev)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
  )
 )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

Save the contents of the vi editor. Now let's test these configurations. Go to $ORACLE_HOME/bin directory and run lsnrctl.

[oracle@standby_server bin] ./lsnrctl status

If all is well then you should see the output like below at your command line.

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2015 22:13:51

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                30-DEC-2014 05:26:31
Uptime                    2 days 16 hr. 47 min. 19 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/standby_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 "cssdg1" has 1 instance(s).
  Instance "cssdev", status READY, has 1 handler(s) for this service...
The command completed successfully

Configure & Test Service Names
Similar to primary server we need to configure service names for the secondary server. Open tnsnames.ora under dbhome_1/network/admin in a vi editor.

[oracle@standby_server admin] vi tnsnames.ora

Replace the contents of tnsnames.ora file the text below.

CSSDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cssdev)(UR=A)
    )
  )

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

Now let's test these changes. Yes, I know it's repetitive process, but we must ensure that listener and service name changes are working appropriately.

Go to dbhome_1/bin directory and run tnsping test for each service.

[oracle@standby_server bin] ./tnsping cssdg1

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

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-JAN-2015 11:06:28

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

Used parameter files:


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

Now run the test for primary service name, which is cssdev.

[oracle@standby_server bin] ./tnsping cssdev

And it should show you the output below.

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2015 22:17:20

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

Used parameter files:

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

Copy backups from primary server
Before you proceed: Don't be frustrated if you are not able to get the copy command to work on your very first try. This was one of the pain points when I configured the data guard environment. You will get it in few tries, believe me. I have tried my best to describe how the ASM copy command works. Good luck.

If you remember, in the previous article we used RMAN to generate a full backup of the primary database. RMAN stored these backups on ASM disks of the primary server. Now it's time to copy the backup files from primary server's ASM disks to standby server's ASM disks. In other words: a copy from ASM to ASM.

Copying files between ASM disks is a bit different than copying traditional OS files. We will use ASMCMD command line interface to copy files between ASM disks.

Go to primary server and run asm script. We created these files in article II of this series.

[oracle@primary_server ora_scripts] ./asm.sh

Running this script will put us on the ASMCMD prompt. Now run the copy command as shown in the script below.

ASMCMD > 
ASMCMD > cp +FRA/cssdev/BACKUPSET/date_directory/nnn.xx.xxxx sys@standby_server.1521.+ASM:+FRA/cssdg1/BACKUPSET/nnn

Remember, ASM files are named internally by Oracle. In other words these are Oracle Managed Files and therefore it has weird names and extensions. In the copy command make sure not to use any file extensions for the standby location. Pay attention to how nnn.xx.xxxx is only using nnn at standby location.

Let's look at the "cp" command in detail.

+FRA/cssdev/BACKUPSET/date_directory/nnn.xx.xxxx is the location of backup files on the primary server's ASM disk.

sys@standby_server.1521.+ASM we are logging as a sys user on to standby server via port 1521 to ASM instance.

+FRA/cssdg1/BACKUPSET/nnn is the location to copy the backup files on the standby server.

You must copy all files under the BACKUPSET directory. There should be multiple backup files due to backup copies of the archive logs.

Catalog backups with RMAN
We copied the backup files to the standby server. Now we need to inform RMAN about these backups, else when you try to restore these backups it will complain about backups of various data files not found.

Yet another pain point for me when I tried to restore the backups. Remember, when you copy backups from a different location then RMAN must be informed about these backups. That is if you wish to restore these backups using RMAN.

Go to RMAN prompt.

[oracle@standby_server ora_scripts] ./rman.sh

You should be at the RMAN prompt now.

RMAN > connect target;
RMAN > catalog start with '+FRA/cssdg1/BACKUPSET';

Now RMAN is aware of the newly copied backup files.

Restore Backups
So far, we copied the backups from primary server onto standby server. We cataloged these backups with RMAN. Now it's time to restore these backups.

Restoring a full backup of the primary database will create a standby database and applying backup of the archive logs will sync this new standby database with primary database to the time when we took backups of the primary database. Tongue twister, I know.

From the time we took a full backup on the primary till the time we restored it on the standby server, I guarantee there are additional transactions on the primary database. So to speak: Our standby database is not aware of these additional transactions and is running somewhat behind. We will use "redo apply" process in the next section to bring standby database up-to-date with the primary database. Be patient young lad!

Remember we are restoring backups on the standby server. Therefore go to RMAN prompt on the standby server.

[oracle@standby_server ora_scripts] ./rman.sh

At RMAN prompt run these commands.

/*connect to target standby instance*/
RMAN > connect target;

/*mount the database using standby control file*/
RMAN > startup mount;

/*restore database from the backups*/
RMAN > restore database;

If all went well, then the commands above should restore backups onto standby server and apply all available archive logs from the backup.

Start Redo Apply
As mentioned above: Our restored standby database is still lagging behind from primary database. We need to bring standby database forward and have it synchronized with the primary database in real time.

So how do we accomplish that? Well, we start the redo apply process. Remember we configured Redo Transport? Yeah, if you configured Redo Transport correctly then by now primary server should have started shipping logs onto standby server.

On the standby server go to SQL prompt and start the standby database recovery process.

[oracle@standby_server ora_scripts] ./sqlplus.sh
SQL > alter database recover managed standby database disconnect from session;
SQL >

Command above will start and run the recovery process in the background. By specifying "disconnect from session" ensures that control returns back to the SQL prompt.

All good. We know how to start the redo apply process and we did. But how the heck do you stop it? And why would you want stop it? Let's say you wish to open the standby database in a read only mode. Yeah, we have to cancel the recovery on the standby database before we can open it in a read only mode.

Go to SQL prompt.

[oracle@standby_server ora_scripts] ./sqlplus.sh
SQL > alter database recover managed standby database cancel;
SQL >

Test Redo Log Transport
We enabled "redo apply" process. But how do we know it is working like it should? In other words, we want to see archive logs shipped from primary to standby in real time. Very exciting, right?

Here is what we will do. We will log into primary server. Switch the log file. Then log into standby server and check if the log file has arrived.

Go to SQL prompt on primary server.

[oracle@primary_server ora_scripts] ./sqlplus.sh

/*alter session with readable date format*/
SQL > ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

/*now make a not of the log sequence#*/
SQL > SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;

/*switch logfile at the primary database*/
SQL > alter system switch logfile;

Below is the output after running these commands. Look at the log sequence #. Last # is 589 in the sequence.

  SEQUENCE# FIRST_TIME      NEXT_TIME
---------- -------------------- --------------------
   587 13-JAN-2015 00:44:58 13-JAN-2015 00:45:05
   588 13-JAN-2015 00:45:05 13-JAN-2015 00:45:21
   588 13-JAN-2015 00:45:05 13-JAN-2015 00:45:21
   589 13-JAN-2015 00:45:21 13-JAN-2015 07:40:41
   589 13-JAN-2015 00:45:21 13-JAN-2015 07:40:41

Now go to SQL prompt on to standby server.

[oracle@standby_server ora_scripts] ./sqlplus.sh
/*alter session for readable date format*/
SQL > ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

/*check the log sequence and applied field*/
SQL > SELECT sequence#, first_time, next_time, applied FROM   v$archived_log ORDER BY sequence#;

If redo logs are transporting correctly then you should see the exact same log sequence numbers as the primary database.

 SEQUENCE# FIRST_TIME       NEXT_TIME        APPLIED
---------- -------------------- --------------------
   585 13-JAN-2015 00:44:39 13-JAN-2015 00:44:45 YES
   586 13-JAN-2015 00:44:45 13-JAN-2015 00:44:58 YES
   587 13-JAN-2015 00:44:58 13-JAN-2015 00:45:05 YES
   588 13-JAN-2015 00:45:05 13-JAN-2015 00:45:21 YES
   589 13-JAN-2015 00:45:21 13-JAN-2015 07:40:41 IN-MEMORY

Now notice the last sequence # on the standby database. It is #589. Sure enough, I see my primary and standby databases are in-sync. Log shipping is working like it should and I am a happy camper. Are you?

Note: For log transport issues: Run below mentioned command on primary server .

[oracle@primary_server ora_scripts] ./sqlplus.sh
SQL > select destination, error from v$archive_dest where error is not null;

If you see any log transport errors then fix them.

Turn Flashback On
Similar to primary database we need to turn flashback mode on for the standby database. It is recommended by Oracle, and therefore let's do so.

Go to SQL prompt on the standby server.

[oracle@standby_server ora_scripts] ./sqlplus.sh
/*turn off the recovery process*/
SQL > alter database recover managed standby database cancel;

/*set flashback log retention to 1 day*/
SQL > alter system db_flashback_retention_target=1440;

/*turn flashback on*/
SQL > alter database flashback_on;

/*ensure flashback is on*/
SQL > select flashback_on from v$database;

Simple, right?

Useful SQL statements
We will manage our data guard environment using data guard broker and watch it with the data guard observer, but I still feel that you should know few useful sql statements for managing your data guard environment.

Let's review them.

Open Standby Database As Read Only
Go to SQL prompt on standby server.

[oracle@standby_server ora_scripts] ./sqlplus.sh
SQL > alter database recover managed standby database cancel;
SQL > alter database open read only;

Resume recovery from the read only mode
Go to SQL prompt on standby server.

[oracle@standby_server ora_scripts] ./sqlplus.sh
SQL > shutdown immediate;
SQL > startup mount;
SQL > alter database recover managed standby database disconnect from session;

Check database role
You can check database role on both servers. Run the SQL statement below on both servers. If ran on primary the role should be "PRIMARY" and on a standby it should be "PHYSICAL STANDBY".

SQL > select database_role from v$database;

Check log sequence on the primary database
Go to SQL prompt on the primary server.

[oracle@primary_server ora_scripts] ./sqlplus.sh
SQL > ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SQL > SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;

Check log sequence on the standby server

Go to SQL prompt on the standby server.

[oracle@standby_server ora_scripts] ./sqlplus.sh
SQL > ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SQL > SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Let's play "Clap Along...", because I am happy. I have successfully configured an active data guard. There is still more work to be done but at the least I can see data being replicated from primary to physical standby.

In the next article we will configure "Data Guard Broker".

See you then!