Configure an Oracle Physical Standby Database
Introduction
This document will guide you, step by step, through the process of configuring an Oracle Physical
Standby database and ensure log shipping is setup correctly.
A physical standby database is an exact, block-for-block copy of a primary database, that is kept “in-
sync” with primary database by means of either applying redo or archive redo logs. Once the
standby database is configured properly, Oracle automatically ensures that any changes on the
primary database are reflected on the standby.
The main purpose of a standby database is for disaster recovery although the standby database can
be open read only for reporting. It allows for the primary and standby databases to be located in
different server rooms/geographic areas. If a problem happens on the primary site then the standby
database can be opened and no data loss is incurred.
Below are the benefits of an Oracle standby database;
High availability/Highly resilient solution for Disaster prevention – reduces MTTR
Can be open read only for reporting
Off-loading production backups to the standby database
Ability to switch the standby to become the primary.
Data protection in that the standby can intentionally be kept ‘X’ minutes behind the primary
and therefore the standby can be used for recovery.
Fully supported by Oracle
Pre-requisites
This document assumes you have already created an Oracle 10gR1 database or higher on Linux and
that database resides on the local file system. You should also have another server with the same
operating system (OS) and database version installed. This secondary server will be our Physical
Standby.
The operating system (OS) can actually be any version of Linux, UNIX or even Windows, however
both the servers must be the same.
In this example we have chosen Linux as our OS – see table below for further information.
For more information on how to install Oracle and create a database, please see the documentation
in the appendix section below.
Example Setup
Primary Server Standby Server
Machine Name: serverA serverB
Database Name test test
Database Unique test test2
Name:
Available File System /u01/app/oracle/oradata/test /u01/app/oracle/oradata/test2
for DB Files:
Available File System /u01/app/oracle/fast_recovery_area /u01/app/oracle/fast_recovery_area
for Flash Recovery
Area:
Operating System: Oracle Linux 6.3 Oracle Linux 6.3
Oracle Release: Oracle 12c Oracle 12c
1. Configure Primary database
To start with we will make some configuration changes to the primary database so that it can
become aware of the primary-standby setup.
** Perform all the below SQL commands as the SYS Oracle user. Please change filenames to suit
your environment.
On Primary
It is required to place the primary database in FORCE LOGGING mode after database creation. This
forces the Oracle Database to write redo records even when NOLOGGING has been specified in DDL.
SQL> alter database force logging;
Check that the database log mode is Archive Mode – this is essentially for log shipping.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
Check the amount of redo log groups and their members
SQL> select group# from v$logfile;
GROUP#
----------
3
2
1
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/redo03.log
/u01/app/oracle/oradata/test/redo02.log
/u01/app/oracle/oradata/test/redo01.log
Create 1 more standby redo log files than you have groups, so in our case we will create 4.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/test/stby_redo01.log' size
51200k;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/test/stby_redo02.log' size
51200k;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/test/stby_redo03.log' size
51200k;
SQL> alter database add standby logfile '/u01/app/oracle/oradata/test/stby_redo04.log' size
51200k;
Check the standby logfiles have been created
SQL> select group#,type from v$logfile;
GROUP# TYPE
---------- -------
3 ONLINE
2 ONLINE
1 ONLINE
4 STANDBY
5 STANDBY
6 STANDBY
7 STANDBY
Configure the below log_archive parameters for log transportation to the standby.
SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles) db_unique_name=test' scope=spfile
SQL> alter system set log_archive_dest_2='service=test2 async
valid_for=(online_logfiles,primary_role) db_unique_name=test2' scope=spfile;
SQL> alter system set log_archive_dest_state_1=enable scope=spfile;
SQL> alter system set log_archive_dest_state_2=enable scope=spfile;
SQL> alter system set log_archive_format='test_%t_%s_%r.dbf' scope=spfile;
SQL> alter system set fal_server=test scope=spfile;
SQL> alter system set fal_client='test2' scope=spfile;
SQL> alter system set standby_file_management=auto scope=spfile;
Ensure the following parameter is set to EXCLUSIVE
SQL> show parameter remote_login
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
2. Modify the TNSnames and Listener.ora files
Oracle log shipping between a primary and standby database requires SQL*Net connectivity for redo
log transportation. Below we will configure the tnsnames and listener.ora files on BOTH servers
TNSnames.ora
On Each server add the following to the $ORACLE_HOME/network/admin/tnsnames.ora file
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = serverA)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
TEST2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = serverB)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test2)
)
)
Listener.ora
On the primary database server - vi $ORACLE_HOME/network/admin/listener.ora file
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = serverA)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/oracle_base/product/12.1.0)
)
(SID_DESC =
(SID_NAME = test2 )
(ORACLE_HOME = /u01/oracle_base/product/12.1.0)
)
)
On the standby database server - vi $ORACLE_HOME/network/admin/listener.ora file
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = serverB)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/oracle_base/product/12.1.0)
)
(SID_DESC =
(SID_NAME = test2 )
(ORACLE_HOME = /u01/oracle_base/product/12.1.0)
)
)
Stop and Start the listeners on each node
lsnrctl stop
lsnrctl start
Ensure you can tnsping the listener remotely and that you can connect using SQLPLUS to the remote
database
On Primary
Tnsping test2
Sqlplus system/oracle@test2
On Standby
Tnsping test
Sqlplus system/oracle@test
3. Create Standby database
I will show 2 different ways to create a standby database. If you are running Oracle 11gR1 or higher
AND have good network speed between your primary and standby servers then use 3.1 otherwise
use 3.2
3.1 RMAN Duplicate from active database
In Oracle Database 11gR1 and higher the standby database can be created using a new feature
called "active database duplication", that allows us to duplicate a primary database without the
need to take first a backup first. The process reads data directly from primary database.
On Primary
Firstly we need to create a new pfile for our standby database;
SQL> Create pfile=’/tmp/inittest2.ora’ from spfile;
Copy the pfile from the primary server to the standby server using scp
Scp /tmp/inittest2.ora serverB:/u01/app/oracle_base/12.1.0/dbs
On Standby
Edit the newly copied pfile (inittest2.ora) and add the following parameters
fal_server=test
fal_client=test2
db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2/'
log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2/'
background_dump_dest='/u01/app/oracle/admin/test2/bdump'
user_dump_dest='/u01/app/oracle/admin/test2/udump'
control_files='/u01/app/oracle/oradata/test2/control01.ctl',
'/u01/app/oracle/oradata/test2/control02.ctl'
db_unique_name='test2'
LOG_ARCHIVE_DEST_1='location=use_db_recovery_file_dest
valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=test'
LOG_ARCHIVE_DEST_2='service=test2 async
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=test2'
standby_archive_dest =/u01/app/oracle/oradata/standby/archive
** Please make sure all the directories in the pfile are pre-created before running the
duplicate command below
Create an oracle password file (orapwd) as the Oracle OS user
– Make sure the password is the same as the primary database
cd $ORACLE_HOME/dbs
orapwd file=orapwtest2 password=oracle entries=10
Next we need to NOMOUNT the standby database (test2) using our newly edited pfile.
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/inittest2.ora’
On Primary
Using RMAN, connect to the primary and standby databases as per below
Rman target sys/oracle@test auxiliary sys/oracle@test2
We can then run the following duplicate command to create our standby database.
RMAN> duplicate target database for standby from active database dorecover;
Once complete, restart the database
SQL> shutdown immediate;
SQL> startup;
On standby
The standby database will have changed to mount after successful completion of the duplicate
command. Restart the standby database;
SQL> shutdown immediate;
SQL> startup;
Check to see that the standby is now open with a DATABASE_ROLE of PHYSICAL STANDBY
SQL> select name,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST test2 READ ONLY PHYSICAL STANDBY
Place the standby database in recovery managed standby mode to allow it to apply logs from the
primary.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
3.2 RMAN copy backup
Another method for creating a physical standby database is to use RMAN to create an image copy
backup.
On Primary
Firstly we need to create a new pfile for our standby database;
SQL> Create pfile=’/tmp/inittest2.ora’ from spfile;
Copy the pfile from the primary server to the standby server using scp
Scp /tmp/inittest2.ora serverB:/u01/app/oracle_base/12.1.0/dbs
On Standby
Edit the newly copied pfile (inittest2.ora) and add the following parameters
fal_server=test
fal_client=test2
db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2/'
log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test2/'
background_dump_dest='/u01/app/oracle/admin/test2/bdump'
user_dump_dest='/u01/app/oracle/admin/test2/udump'
control_files='/u01/app/oracle/oradata/test2/control01.ctl',
'/u01/app/oracle/oradata/test2/control02.ctl'
db_unique_name='test2'
LOG_ARCHIVE_DEST_1='location=use_db_recovery_file_dest
valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=test'
LOG_ARCHIVE_DEST_2='service=test2 async
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=test2'
standby_archive_dest =/u01/app/oracle/oradata/standby/archive
** Please make sure all the directories in the pfile are pre-created before running the
duplicate command below
Create an oracle password file (orapwd) as the Oracle OS user
– Make sure the password is the same as the primary database
cd $ORACLE_HOME/dbs
orapwd file=orapwtest2 password=oracle entries=10
Next we need to NOMOUNT the standby database (test2) using our newly edited pfile.
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/inittest2.ora’
On Primary
Connect to RMAN and run the below copy command to create an image copy backup of the files.
rman target /
List the datafiles in the database
RMAN> report schema;
Perform a backup copy
RMAN> copy datafile 1 to '/u01/app/oracle/oradata/test3/system01.dbf',
datafile 3 to '/u01/app/oracle/oradata/test3/sysaux01.dbf',
datafile 4 to '/u01/app/oracle/oradata/test3/undotbs01.dbf',
datafile 6 to '/u01/app/oracle/oradata/test3/users01.dbf',
current controlfile for standby to '/u01/app/oracle/oradata/test3/control01.ctl';
Copy all of the backed up files to the same directory structure on the standby database server, with
the same file names – THIS IS VITAL. This can be done using scp, an example of which is below;
Scp /u01/app/oracle/oradata/test3/system01.dbf
serverB:/u01/app/oracle/oradata/test3/
On standby
Copy the control file control01.ctl to the other control file location that you specified in the pfile. In
our case;
cd /u01/app/oracle/oradata/test3
cp control01.ctl control02.ctl
Mount the database in standby mode
SQL> alter database mount standby database;
Place the standby database in recovery managed standby mode to allow it to apply logs from the
primary.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
4.Testing
To ensure that the logs are being transported and applied to the standby database we will run the
below commands.
On standby
The Media Recovery Process (MRP) should be running on the standby database server. This process
is responsible for applying the logs to the standby.
SQL> ! ps -ef | grep mrp
oracle 9125 1 0 15:37 ? 00:00:00 ora_mrp0_test2
The MRP0 process should be in the WAIT_FOR_LOG state until a log switch is performed on the
primary database.
SQL> select process,status,thread# from v$managed_standby;
PROCESS STATUS THREAD#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 1
Below we see that log sequence 17 and 18 have been archived and applied.
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
---------- --- --------- -
17 YES YES
18 YES YES
On Primary
Perform a couple of log switches. This will cause the logs to transported to the standby and for the
MRP process to apply them.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
On standby
Check that the 3 logs have been archived and applied on the standby. Here you can see that log
19,20,21 are now applied.
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
---------- --- --------- -
17 YES YES A
18 YES YES A
20 YES YES A
21 YES YES A
19 YES YES A
The MRP0 process’ status should change to APPLYING_LOG just after the log switches occur on the
primary database.
SQL> select process,status,thread# from v$managed_standby;
PROCESS STATUS THREAD#
--------- ------------ ----------
ARCH CLOSING 1
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 1
MRP0 APPLYING_LOG 1
RFS IDLE 0
RFS IDLE 1
RFS IDLE 0
RFS IDLE 0
Appendix
List of URL’s to help with the Oracle Database Installation, DB creation and Oracle Physical Standby
configuration.
Oracle Database - http://docs.oracle.com/cd/E11882_01/install.112/e47689/toc.htm
Oracle Physical Standby- http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm
