As Published In
Oracle Magazine
November/December 2008

TECHNOLOGY: High Availability


Keeping Active

By Arup Nanda Oracle ACE Director

Use Oracle Active Data Guard to set up a physical standby database for reporting and recovery.

Physical standby databases provide a rock-solid mechanism for high availability and disaster recovery. In a nutshell, the physical standby database is a physical copy of the source (primary) database, and the redo entries from the primary database are applied to the standby database continuously (or periodically) over the network to keep it in sync. If the primary database fails, the standby database can be quickly activated—a process known as role reversal —enabling applications to connect to the database that was called the standby before and resume processing without a significant delay.

A valuable property of the physical standby database is that it can be opened for read-only access. This makes it highly desirable for reporting and other applications that need not be run on the primary database, reducing the load there considerably.

In the past, when you opened a physical standby database for read-only access, you had to stop the application of redo entries to the standby database. With the redo application stopped, the standby database might not have been ready to immediately take over for the primary database if that database failed. However, a new option of Oracle Database 11g called Oracle Active Data Guard allows you to continue applying redo changes while the physical standby database is open in read-only mode. This makes efficient use of the standby database for reporting applications without compromising the speed of standby database activation in the event of a primary database failure.

Oracle Active Data Guard is even more useful when you enable real time apply, which applies the changes on the primary database in near real time to the standby database. With this combination, the reporting applications accessing the standby database view data almost as soon as it appears in the primary database.

This article shows how to build an Oracle Active Data Guard infrastructure with real time apply.

A Quick Primer

Let’s look at how Oracle Active Data Guard with real time apply works for a physical standby database. As Figure 1 illustrates, the primary database generates redo entries as users modify data, and these entries are written to the online redo log files by a process called log writer (LGWR), an integral part of the Oracle Database instance.

 

>
figure 1
Figure 1: Oracle Active Data Guard physical standby database architecture with real time apply in maximum performance mode


The LGWR process sends redo entries directly to the standby server, using a subprocess called log writer network server (LNS). A process on the standby database called remote file server (RFS) gets the redo stream from the LNS and writes it to the standby redo log files.

A managed recovery process coordinates the reads from the standby redo log files as they are written and, using media recovery, updates the standby database immediately. (This managed recovery process is active only when the standby database is in managed recovery mode.)

Physical standby databases can operate in different modes. The default, described here and shown in Figure 1, is maximum performance mode.

Active Steps

To demonstrate the Oracle Active Data Guard infrastructure with real time apply, I use the following database setup:

 

  Primary Standby
Server prisvr1 sbysvr1
Database Pridb Sbydb




There is a simple way to build a physical standby database by using Oracle Recovery Manager (Oracle RMAN) commands in Oracle Database 11g, and I also make use of that feature. This article also assumes that Oracle Automatic Storage Management is used on both the primary and standby databases. The primary database is on one disk group: DATA1.

Prerequisites. The following prerequisites are required for running through the steps in this article:

1. Both the primary and the standby databases must be running the same release of Oracle Database 11g, Enterprise Edition.
2. The Oracle Active Data Guard option must be installed. (The Oracle Active Data Guard option is licensed separately from Oracle Database.)
3. The primary database must be in archive log mode.

Preparation. Complete the following setup of the primary database, and prepare to create the standby database.

1. Standby databases operate from the entries made in the redo log files. If an operation uses the NOLOGGING option, as in the case of INSERT /*+ APPEND */ statements or SQL*Loader with the DIRECT option, the redo entries will be skipped. To force entries from these operations to go to the redo logs, issue the following SQL statement on the primary database:

 

SQL> alter database force logging


This will force redo logging even when the NOLOGGING option is used.

2. Make sure the primary database is using a server parameter file (spfile):

 

SQL> select value
  2    from v$parameter
  3    where name = 'spfile'
  4    /

VALUE
------------------------------------------------
?/dbs/spfilepridb.ora


The output confirms that an spfile is in use. If the output is null, an spfile is not in use. In that case, create an spfile from the pfile, using the following SQL statement:

 

SQL> create spfile from pfile;


If you create the spfile, you will need to recycle the primary database for the spfile to take effect.

3. Still on the primary database, create standby redo log files, by executing the following commands:

 

SQL> alter database add standby logfile group 4 ('+DATA1/sby_log01.rdo') size 50M;

SQL> alter database add standby logfile group 5 ('+DATA1/sby_log02.rdo') size 50M;


4. Also on the primary server, create a TNS entry for the standby database:

 

sbydb =
  (description =
    (address_list =
      (address = (protocol = tcp)
      (host = sbysvr1)(port = 1521))
    )
    (connect_data =
      (sid = sbydb)
    )
  )


5. Copy the password file on the primary server, usually named orapwpridb, to the standby server under the $ORACLE_HOME/dbs directory. Rename the file orapwsbydb. This will serve as the password file for the standby database (sbydb).
6. On the standby server, edit the listener.ora file under $ORACLE_HOME/network/admin to add the information about the new standby database instance:

 

(sid_desc =
   (global_dbname = sbydb)
   (oracle_home = /opt/oracle/product/11
                              
g
/db1) (sid_name = sbydb) )


Restart the listener, by executing lsnrctl reload, for this to take effect.

7. Still on the standby server under $ORACLE_HOME/dbs, create a file called initsbydb.ora with just one line:

 

db_name=pridb


This file will be used to start the standby database instance, and the rest of the parameters will be created on the fly. Note that the database name of the standby is pridb—the same as the primary, even though the instance name is different.

8. On the standby server, create the directory to hold the audit trace files. In the $ORACLE_BASE/admin directory, create a subdirectory called sbydb (the name of the standby instance). Then under sbydb, create a subdirectory called adump. This will hold the audit trail files for the SYSDBA connections.

Creation. Now that the setup is complete, let’s create the standby database.

1. On the standby server, start the database instance only:

 

sqlplus / as sysdba
SQL> startup nomount


2. On the primary server, execute the Oracle RMAN cr_sbydb.rman script, shown in Listing 1, by issuing the following command:

Code Listing 1: Oracle RMAN script for creating the standby database

 

RMAN> connect target sys/oracle123@pridb
2> connect auxiliary sys/oracle123@sbydb
3> 
4> run {
5>    allocate channel p1 type disk;
6>    allocate auxiliary channel s1 type disk;
7> 
8>    duplicate target database for standby from active database
9>      spfile
10>         parameter_value_convert 'pridb','sbydb'
11>         set db_unique_name='sbydb'
12>         set db_file_name_convert='/pridb/','/sbydb/'
13>         set log_file_name_convert='/pridb/','/sbydb/'
14>         set control_files='+DATA1/sbydb/control01.ctl'
15>         set log_archive_max_processes='5'
16>         set fal_client='sbydb'
17>         set fal_server='pridb'
18>         set standby_file_management='AUTO'
19>         set log_archive_config='dg_config=(pridb,sbydb)'
20>         set log_archive_dest_2='service=pridb 
lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sbydb'
21>         set log_archive_dest_state_2='enable'
22>         set log_archive_format='sbydb_%t_%s_%r.arc'
23>    ;
24>   sql channel p1 "alter system set log_archive_config=''dg_config=(pridb,sbydb)''";
25>   sql channel p1 "alter system set log_archive_dest_2=''service=sbydb 
lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sbydb''"; 
26>   sql channel p1 "alter system set log_archive_max_processes=5";
27>   sql channel p1 "alter system set fal_client=pridb";
28>   sql channel p1 "alter system set fal_server=sbydb"; 
29>   sql channel p1 "alter system set standby_file_management=auto";
30>   sql channel p1 "alter system set log_archive_dest_state_2=enable";
31>   sql channel p1 "alter system archive log current"; 
32>   sql channel s1 "alter database recover managed standby database using current logfile disconnect";
33>  } 
34>
35>


connected to target database: PRIDB (DBID=2903486933)

connected to auxiliary database: PRIDB (not mounted)

using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=153 device type=DISK

allocated channel: s1
channel s1: SID=97 device type=DISK

Starting Duplicate Db at 27-MAY-08

contents of Memory Script:
{
   backup as copy reuse
   file  '/opt/oracle/product/11g/db1/dbs/orapwpridb' auxiliary format 
 '/opt/oracle/product/11g/db1/dbs/orapwsbydb'   file 
 '/opt/oracle/product/11g/db1/dbs/spfilepridb.ora' auxiliary format 
 '/opt/oracle/product/11g/db1/dbs/spfilesbydb.ora'   ;
   sql clone "alter system set spfile= ''/opt/oracle/product/11g/db1/dbs/spfilesbydb.ora''";
}
executing Memory Script

Starting backup at 27-MAY-08
Finished backup at 27-MAY-08

sql statement: alter system set spfile= ''/opt/oracle/product/11g/db1/dbs/spfilesbydb.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest = 
 ''/opt/oracle/product/admin/sbydb/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  diagnostic_dest = 
 ''/opt/oracle/product/admin/sbydb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers = 
 ''(PROTOCOL=TCP) (SERVICE=sbydbXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''sbydb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert = 
 ''/pridb/'', ''/sbydb/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert = 
 ''/pridb/'', ''/sbydb/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''+DATA1/sbydb/control01.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes = 
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client = 
 ''sbydb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server = 
 ''pridb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management = 
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config = 
 ''dg_config=(pridb,sbydb)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_2 = 
 ''service=pridb lgwr async valid_for=(online_logfiles,primary_role) 
db_unique_name=sbydb'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_state_2 = 
 ''enable'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_format = 
 ''sbydb_%t_%s_%r.arc'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount ;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/opt/oracle/product/admin/sbydb/adump'' comment= '''' scope=spfile

sql statement: alter system set  diagnostic_dest =  ''/opt/oracle/product/admin/sbydb'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=sbydbXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''sbydb'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/pridb/'', ''/sbydb/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/pridb/'', ''/sbydb/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA1/sbydb/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  2 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''sbydb'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''pridb'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(pridb,sbydb)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_2 =  ''service=pridb LGWR ASYNC 
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=sbydb'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_state_2 =  ''enable'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_format =  ''sbydb_%t_%s_%r.arc'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1304704 bytes
Variable Size                427820928 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4673536 bytes

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA1/sbydb/control01.ctl';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 27-MAY-08
channel p1: starting datafile copy
copying standby control file
output file name=/opt/oracle/product/11g/db1/dbs/snapcf_pridb.f tag=TAG20080527T114245 RECID=12 STAMP=655818173
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-MAY-08

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+DATA1/sbydb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+DATA1/sbydb/system01.dbf";
   set newname for datafile  2 to 
 "+DATA1/sbydb/sysaux01.dbf";
   set newname for datafile  3 to 
 "+DATA1/sbydb/undotbs01.dbf";
   set newname for datafile  4 to 
 "+DATA1/sbydb/users01.dbf";
   set newname for datafile  5 to 
 "+DATA1/sbydb/flow_1.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "+DATA1/sbydb/system01.dbf"   datafile 
 2 auxiliary format 
 "+DATA1/sbydb/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "+DATA1/sbydb/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "+DATA1/sbydb/users01.dbf"   datafile 
 5 auxiliary format 
 "+DATA1/sbydb/flow_1.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA1/sbydb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 27-MAY-08
channel p1: starting datafile copy
input datafile file number=00002 name=+DATA1/pridb/sysaux01.dbf
output file name=+DATA1/sbydb/sysaux01.dbf tag=TAG20080527T114300 RECID=0 STAMP=0
channel p1: datafile copy complete, elapsed time: 00:01:55
channel p1: starting datafile copy
input datafile file number=00003 name=+DATA1/pridb/undotbs01.dbf
output file name=+DATA1/sbydb/undotbs01.dbf tag=TAG20080527T114300 RECID=0 STAMP=0
channel p1: datafile copy complete, elapsed time: 00:01:15
channel p1: starting datafile copy
input datafile file number=00001 name=+DATA1/pridb/system01.dbf
output file name=+DATA1/sbydb/system01.dbf tag=TAG20080527T114300 RECID=0 STAMP=0
channel p1: datafile copy complete, elapsed time: 00:01:05
channel p1: starting datafile copy
input datafile file number=00005 name=+DATA1/pridb/flow_1.dbf
output file name=+DATA1/sbydb/flow_1.dbf tag=TAG20080527T114300 RECID=0 STAMP=0
channel p1: datafile copy complete, elapsed time: 00:00:07
channel p1: starting datafile copy
input datafile file number=00004 name=+DATA1/pridb/users01.dbf
output file name=+DATA1/sbydb/users01.dbf tag=TAG20080527T114300 RECID=0 STAMP=0
channel p1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 27-MAY-08

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=655818375 file name=+DATA1/sbydb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=655818375 file name=+DATA1/sbydb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=655818375 file name=+DATA1/sbydb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=655818375 file name=+DATA1/sbydb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=655818375 file name=+DATA1/sbydb/flow_1.dbf
Finished Duplicate Db at 27-MAY-08

sql statement: alter system set log_archive_config=''dg_config=(pridb,sbydb)''

sql statement: alter system set log_archive_dest_2=''service=sbydb 
LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=sbydb''

sql statement: alter system set log_archive_max_processes=5

sql statement: alter system set fal_client=pridb

sql statement: alter system set fal_server=sbydb

sql statement: alter system set standby_file_management=AUTO

sql statement: alter system set log_archive_dest_state_2=enable

sql statement: alter system archive log current

released channel: p1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql command at 05/27/2008 11:48:16
RMAN-06033: channel s1 not allocated

Recovery Manager complete.


 

$ rman cmdfile=cr_sbydb.rman 
log=cr_sbydb.log


The process may take a long time, depending on the size of the database. (While the Oracle RMAN process is running, you can tail the cr_sbydb.log log file to check the progress and spot any errors.)

The Oracle RMAN script does the following:

 

  • Shuts down the standby database instance and restarts it with the newly created spfile
  • Creates a standby controlfile on the primary database and copies it over to the standby server
  • Creates a clone database on the standby server using Oracle RMAN cloning
  • Converts the cloned database to a physical standby database


Table 1 shows a line-by-line explanation of the script in Listing 1.

 

Lines Description
1,2 Connects to primary and standby instances as SYSDBA. (This is why you need to copy the password file of the primary server to the standby server.)
5, 6 Allocates two channels—p1 and s1—for primary and standby databases, respectively.
8 Oracle RMAN duplicate command creates a clone of the primary database.
9 An spfile must be created from the initialization parameter file (pfile) and updated with the parameters in lines 10 through 22.
10 If a parameter contains the name of the primary database (pridb), it should be converted to the name of the standby database (sbydb). This line causes the parameter to be changed in the standby database.
11 Specifies the unique name of the standby database.
12, 13 The names of the datafiles are changed from the primary to standby naming conventions. For example, the +DATA1/pridb/system_01.dbf file will be copied and maintained as +DATA1/sbydb/system_01.dbf. This is not required if the standby database is on a different server (as is the case now) but may be a good practice to separate the names.
16, 17 Specifies the fetch archived log (FAL) process parameters: the FAL server is the primary database (pridb), which pushes changes to redo log files to the FAL client—the standby database (sbydb).
19 The Oracle Active Data Guard configuration parameters—the primary and standby databases, respectively—are defined here.
20 This is where the mode of redo shipping is defined. The changes to redo logs are shipped to the standby database's standby redo logs via the log writer (LGWR) process. The process of writing is asynchronous (as specified by the ASYNC parameter).
32 This starts the managed recovery process. The USING CURRENT LOGFILE clause enables real time apply. The DISCONNECT clause returns control to the user and runs the log application process in the background.
32 This starts the managed recovery process. The USING CURRENT LOGFILE clause enables real time apply. The DISCONNECT clause returns control to the user and runs the log application process in the background.




The end of the log file includes the following lines:

 

media recovery complete, 
elapsed time: 00:00:02
Finished recover at 25-MAY-08
Finished Duplicate Db at 25-MAY-08
  sql statement: alter system archive 
  log current


This indicates that the Oracle RMAN job completed successfully and created the physical standby database.

The standby database is now running in managed recovery mode—you don’t have to do anything to keep it in sync. The redo entries will be shipped from the primary to the standby database and applied there automatically.

If you want to stop the managed recovery process (and the redo log application), execute this SQL statement on the standby database:

 

SQL> alter database recover managed standby database cancel;


When you are ready to restart the managed recovery process, issue

 

SQL> alter database 
recover managed standby database 
using current logfile disconnect;


Using the DISCONNECT clause allows control to return to the user, and the log application process goes on in the background.

Active Standby

Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:

1. Stop the managed recovery process:

 

SQL> alter database recover managed standby database cancel;


2. Open the standby database as read-only:

 

SQL> alter database open read only;


3. Restart the managed recovery process:

 

SQL> alter database 
recover managed standby database 
using current logfile disconnect;


Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.

4. To test the “active” part of Oracle Active Data Guard, create a table in the primary database:

 

SQL> create table test (col1 number);


5. After a few seconds, check the existence of the table in the standby database:

 

SQL> select table_name from user_tables where table_name = 'TEST';


The table will be present. The standby database is open in read-only mode, but it is still applying the logs from the primary database. This feature enables you to run reports against it without sacrificing the ability to put the standby database into the primary role quickly.

6. To confirm the application of redo logs on the primary database, first switch the log file:

 

SQL> alter system switch logfile;


7. Now observe the alert log of the standby database. Use the automatic diagnostic repository command interpreter (ADRCI) tool, new in Oracle Database 11g:

 

 
$ adrci 
adrci> show alert -tail -f


The output is shown in Listing 2. The log message confirms that standby log group 5 was opened. The log was switched on the standby database when the log switch occurred on the primary database.

Code Listing 2: Partial output of the standby database’s alert log

 

2008-05-30 17:46:22.593000 -04:00
Media Recovery Waiting for thread 1 sequence 628
2008-05-30 17:46:23.928000 -04:00
Primary database is in MAXIMUM PERFORMANCE mode
kcrrvslf: active RFS archival for log 4 thread 1 sequence 627
RFS[1]: Successfully opened standby log 5: '+DATA1/sby_log02.rdo'
2008-05-30 17:46:28.717000 -04:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 628 Reading mem 0
Mem# 0: +DATA1/sby_log02.rdo


Next Steps



LEARN more about Oracle Active Data Guard
oracle.com/database/active-data-guard.html
oracle.com/technetwork/deploy/availability/htdocs/activedataguard.html
Oracle Data Guard Concepts and Administration
Oracle high-availability solutions

You can take an Oracle RMAN backup from the standby database instead of the primary to reduce the load on the latter. And, better yet, you can take the backup even when the standby database is open in read-only and managed recovery mode.

Conclusion

The physical standby database is a physical replica of the primary database, kept in sync by the application of redo log entries from the primary database. With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary database does not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipment of log information from the primary to the standby database.

Using the Oracle Active Data Guard option, you can open the physical standby database for read-only operations while the managed recovery process is going on. You can offload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on the primary database considerably. And because the standby database is being recovered continuously with real time apply, the standby database can be activated and used immediately in case the primary database fails. This combination of features makes the investment in Oracle Active Data Guard very worthwhile.



Arup Nanda
(arup@proligence.com) has been an Oracle DBA for more than 14 years, handling all aspects of database administration, from performance tuning to security and disaster recovery. He was Oracle Magazine’s DBA of the Year in 2003.

Send us your comments

false ,,,,,,,,,,,,,,,,