Data Guard Physical Standby Setup in Ora

Data Guard Physical Standby Setup in Oracle
Database 11g Release 2

Data Guard is the name for Oracle's standby database solution, used for disaster
recovery and high availability. This article contains an updated version of the 9i physical
standby setup method posted here.
You should probably be using the Data Guard Broker to configure and manage your
standby database, as described here.
 Assumptions
 Primary Server Setup
o Logging
o Initialization Parameters
o Service Setup
o Backup Primary Database
o Create Standby Controlfile and PFILE
 Standby Server Setup (Manual)
o Copy Files
o Start Listener
o Restore Backup
o Create Redo Logs
 Standby Server Setup (DUPLICATE)
o Copy Files
o Start Listener
o Create Standby Redo Logs on Primary Server
o Create Standby using DUPLICATE
 Start Apply Process
 Test Log Transport
 Protection Mode
 Database Switchover
 Failover
 Flashback Database
 Read-Only Standby and Active Data Guard
 Snapshot Standby
Related articles.
 Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle
Database 11g Release 2
 Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle
Database 12c Release 1
 Data Guard (9i)
 Data Guard (11gR2) Setup using Oracle Grid Control
Assumptions
 You have two servers (physical or VMs) with an operating system and Oracle
installed on them. In this case I've used Oracle Linux 5.6 and Oracle Database
11.2.0.2.
 The primary server has a running instance.
 The standby server has a software only installation.
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are
both set to "DB11G" on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE

------------------------------------ ----------- -------------------------
-----
db_name string DB11G
SQL> show parameter db_unique_name
NAME TYPE VALUE

------------------------------------ ----------- -------------------------
-----
db_unique_name string DB11G
SQL>
The DB_NAME of the standby database will be the same as that of the primary, but it must
have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and
standby database should be used in the DG_CONFIG setting of
the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have
the value "DB11G_STBY".
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';
Set suitable remote archive log destinations. In this case I'm using the fast recovery
area for the local location, but you could specify an location explicitly if you prefer.
Notice theSERVICE and the DB_UNIQUE_NAME for the remote location reference the
standby location.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to

appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
In addition to the previous setting, it is recommended to make sure the primary is ready
to switch roles to become a standby. For that to work properly we need to set the
following parameters. Adjust the *_CONVERT parameters to account for your filename and
path differences between the servers.
ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;

--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'
SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Remember, some of the parameters are not modifiable, so the database will need to be
restarted before they take effect.
Service Setup
Entries for the primary and standby databases are needed in the
"$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create
these using the Network Configuration Utility (netca) or manually. The following entries
were used during this setup.
DB11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.WORLD)
)
)
DB11G_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DB11G.WORLD)
)
)
Backup Primary Database

If you are planning to use an active duplicate to create the standby database, then this
step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup
of the primary database.
$ rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the
primary database.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
Create a parameter file for the standby database.
CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
Amend the PFILE making the entries relevant for the standby database. I'm making a
replica of the original server, so in my case I only had to amend the following
parameters.
*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
Standby Server Setup (Manual)
Copy Files
Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.

$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl
/u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl
/u01/app/oracle/fast_recovery_area/DB11G/control02.ctl
$ # Archivelogs and backups

$ scp -r oracle@ol5-112-
dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog
/u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-
dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset
/u01/app/oracle/fast_recovery_area/DB11G
$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora
$ # Remote login password file.

$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs
Notice, the backups were copied across to the standby server as part of the FRA copy.
If your backups are not held within the FRA, you must make sure you copy them to the
standby server and make them available from the same path as used on the primary
server.
Start Listener
Make sure the listener is started on the standby server.
$ lsnrctl start
Restore Backup
Create the SPFILE form the amended PFILE.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba
SQL> CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';
Restore the backup files.
$ rman target=/
RMAN> STARTUP MOUNT;

RMAN> RESTORE DATABASE;
Create Redo Logs

Create online redo logs for the standby. It's a good idea to match the configuration of
the primary server.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD LOGFILE
('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby redo logs on both the
standby and the primary database (in case of switchovers). The standby redo logs
should be at least as big as the largest online redo log and there should be one extra
group per thread compared the online redo logs. In my case, the following standby redo
logs must be created on both servers.
ALTER DATABASE ADD STANDBY LOGFILE

('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
Once this is complete, we can start the apply process.
Standby Server Setup (DUPLICATE)
Copy Files
Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.

$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl
/u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl
/u01/app/oracle/fast_recovery_area/DB11G/control02.ctl
$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora
$ # Remote login password file.

$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs
Start Listener
When using active duplicate, the standby server requires static listener configuration in
a "listener.ora" file. In this case I used the following configuration.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G.WORLD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = DB11G)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT =
1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Make sure the listener is started on the standby server.
$ lsnrctl start
Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby.
To make sure the primary database is configured for switchover, we must create the
standby redo logs on the primary server.

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary
"init.ora" file.
$ sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';
Connect to RMAN, specifying a full connect string for both the TARGET and
AUXILLARY instances. DO not attempt to use OS authentication.
$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY
Now issue the following DUPLICATE command.
DUPLICATE TARGET DATABASE

FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
SET FAL_SERVER='DB11G' COMMENT 'Is primary'
NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
 FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it
will not force a DBID change.
 FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source
datafile, without an additional backup step.
 DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up
to the current point in time.
 SPFILE: Allows us to reset values in the spfile when it is copied from the source
server.
 NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start the apply process.
Start Apply Process

Start the apply process on standby server.
# Foreground redo apply. Session never returns until cancel.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
# Background redo apply. Control is returned to the session once the apply
process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it
being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM
SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM
SESSION;
Provided you have configured standby redo logs, you can start real-time apply using the
following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time

FROM v$archived_log
ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied

FROM v$archived_log
ORDER BY sequence#;
Protection Mode
There are three protection modes for the primary database:
 Maximum Availability: Transactions on the primary do not commit until redo

information has been written to the online redo log and the standby redo logs of
at least one standby location. If no standby location is available, it acts in the
same manner as maximum performance mode until a standby becomes
available again.
 Maximum Performance: Transactions on the primary commit as soon as redo
information has been written to the online redo log. Transfer of redo information
to the standby server is asynchronous, so it does not impact on performance of
the primary.
 Maximum Protection: Transactions on the primary do not commit until redo
information has been written to the online redo log and the standby redo logs of
at least one standby location. If not suitable standby location is available, the
primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum
performance mode.
SELECT protection_mode FROM v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL>
The mode can be switched using the following commands. Note the alterations in the
redo transport attributes.
-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These
roles can be altered at runtime without loss of data or resetting of redo logs. This
process is known as a Switchover and can be performed using the following
statements.
-- Convert primary database to standby

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
-- Shutdown primary database

SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby database

STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
On the original standby database issue the following commands.
-- Convert standby database to primary

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- Shutdown standby database
SHUTDOWN IMMEDIATE;
-- Open old standby database as primary

STARTUP;
Once this is complete, test the log transport as before. If everything is working fine,
switch the primary database back to the original server by doing another switchover.
This is known as a switchback.
Failover
If the primary database is not available the standby database can be activated as a
primary database using the following statements.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up
immediately.
The original primary database can now be configured as a standby. If Flashback
Database was enabled on the primary database, then this can be done relatively easily
(shown here). If not, the whole setup process must be followed, but this time using the
original primary server as the standby.
Flashback Database
It was already mentioned in the previous section, but it is worth drawing your attention
to Flashback Database once more. Although a switchover/switchback is safe for both
the primary and standby database, a failover renders the original primary database
useless for converting to a standby database. If flashback database is not enabled, the
original primary must be scrapped and recreated as a standby database.
An alternative is to enable flashback database on the primary (and the standby if
desired) so in the event of a failover, the primary can be flashed back to the time before
the failover and quickly converted to a standby database. That process is shown here.
Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow
query access. This is often used to offload reporting to the standby server, thereby
freeing up resources on the primary server. When open in read-only mode, archive log
shipping continues, but managed recovery is stopped, so the standby database
becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby
database to be open in read-only mode, but still apply redo information. This means a
standby can be available for querying, yet still be up to date. There are licensing
implications for this feature, but the following commands show how active data guard
can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
Since managed recovery continues with active data guard, there is no need to switch
back to managed recovery from read-only mode in this case.
Snapshot Standby
Introduced in 11g, snapshot standby allows the standby database to be opened in read-
write mode. When switched back into standby mode, all changes made whilst in read-
write mode are lost. This is achieved using flashback database, but the standby
database does not need to have flashback database explicitly enabled to take
advantage of this feature, thought it works just the same if it is.
If you are using RAC, turn off all but one of the RAC instances. Make sure the instance
is in MOUNT mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Make sure managed recovery is disabled.
Convert the standby to a snapshot standby. The following example queries

the V$DATABASE view to show that flashback database is not enabled prior to the
conversion operation.
SELECT flashback_on FROM v$database;
FLASHBACK_ON
------------------
NO
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL>
You can now do treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the
conversion to snapshot standby, issue the following commands.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
FLASHBACK_ON
------------------
NO
SQL>
The standby is once again in managed recovery and archivelog shipping is resumed.
Notice that flashback database is still not enabled.
solutions of problems
FAL_CLIENT will be standby db
FAL_SERVER will be primary db
IF Oracle Data Guard logs shipping

stopped:
- Tnsping
-listener
- DEFER and ENABLE once log shipping from Primary with below commands -
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE = BOTH
SID='EC6';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE = BOTH
SID='EC6';
EC6 -> give your sid and crosscheck LOG_ARCHIVE_DEST_STATE_2 for your scenario before
executing
then check SQL>Archive log list; on both primary and standby
execute SQL> alter system switch logfile; (on primary)
check logs are getting shipped on standby or not
ALSO CHECK LISTENER.ORA file is configured on both side or not:
Example:listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/n
etwork/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB11G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = DB11G)
))
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.169)(PORT = 1521))
ALSO COPY sqlnet.ora file in standby db and configure it
check if both database pinging or not: tnsping <sidname>
Another document:
Here in this article I am going to cover steps by step approach to configure Oracle 11g
Data Guard Physical Standby.
In my case, Ingredients to simulate Physical Standby data guard environment are as

below:
 2 VM’s, Primary and DR with enough CPU and RAM in order to run oracle database.
 Primary server configuration
o CentOS 6.5
o Server name: pr
o IP: 192.168.17.131
o Oracle 11g software plus oracle instance.
o Oracle SID/Global_name: RTS
o Oracle db_unique_name: RTS
 Secondary server configuration
o CentOS 6.5
o Server name: dr
o IP: 192.168.17.132
o Oracle 11g software only.
o Oracle SID/Global name: RTS
o Oracle db_unique_name: RTSDR
Note:
 Oracle version on Primary and secondary should be identical. i.e. In my case it’s
11.2.0.1.0
 db_unique_name on standby and primary database should be different in order to used in
DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.
 Primary and DR server should ping each other by IP as well as its server name. In order
to ping with its server name, edit /etc/hosts file accordingly.
Primary Server Configurations:

<Step – 1 >
Enable Archive log:
Primary database is in No Archive Mode, Enable it:
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1

Current log sequence 3
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Next log sequence to archive 3
<Step – 2 >
Enable force logging with the help of following SQL command:
SQL> ALTER DATABASE FORCE LOGGING;
<Step – 3 >
Verify initialization parameters db_name and db_unique_name on primary, In my case
those are set to: RTS
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
db_name string RTS
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
db_unique_name string RTS
<Step – 4 >
Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard
config.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(RTS,RTSDR)';
Ensure your above changes as below:
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
log_archive_config string DG_CONFIG=(RTS,RTSDR)
<Step – 5 >
Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery
area for remote archive log destination.
Note: Service and DB_UNIQUE_NAME reference the remote standby location.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RTSDR NOAFFIRM ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTSDR';
Ensure your above changes as below:
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
log_archive_dest_2 string SERVICE=rtsdr NOAFFIRM ASYNC V

ALID_FOR=(ONLINE_LOGFILES,PRIM
ARY_ROLE) DB_UNIQUE_NAME=rtsdr
Enable LOG_ARCHIVE_DEST_2:
SQL> alter system set log_archive_dest_state_2=enable;
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
log_archive_dest_state_2 string ENABLE
<Step – 6 >
Set log_archive_format parameter as below:
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
log_archive_format string %t_%s_%r.arc
Set log_archive_max_processes parameter to 30:
SQL> alter system set log_archive_max_processes=30;
SQL> show parameter log_archive_max_processes
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
log_archive_max_processes integer 30
Set remote_login_passwordfile parameter to exclusive:
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
remote_login_passwordfile string EXCLUSIVE
<Step – 7 >
Set fal_server and fal_client parameter for primary database:
SQL> alter system set fal_server=RTSDR;
SQL> show parameter fal_server
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
fal_server string RTSDR
SQL> alter system set fal_client='RTS';
SQL> show parameter fal_client
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
fal_client string RTS
Set standby_file_management parameter to auto:
SQL> alter system set standby_file_management=auto;
SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------

---
standby_file_management string AUTO
<Step – 8 >
Configure listener file and copy it to DR @ location:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Listener file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RTS)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = RTS)
) )
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
<Step – 9 >
Entries for the primary and standby databases are needed in the
“$ORACLE_HOME/network/admin/tnsnames.ora” files on both the servers.
RTS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)
(UR=A)
) )
RTSDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dr)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)
(UR=A)
) )
Ensure your above configuration by TNSPing utility on Primary and standby:
[oracle@pr admin]$ tnsping RTS
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014

06:04:09
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =

TCP)(HOST = pr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = rts)
(GLOBAL_NAME = RTS)))
OK (10 msec)
[oracle@pr admin]$ tnsping RTSDR
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 03-OCT-2014

06:04:15
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =

TCP)(HOST = dr)(PORT = 1521))) (CONNECT_ DATA = (SERVICE_NAME = RTS)
(GLOBAL_NAME = RTS)))
OK (20 msec)
<Step – 10 >
Backup primary database via RMAN backup utility:
[oracle@pr admin]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 4 02:02:11 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RTS (DBID=1421312347)

RMAN> backup database plus archivelog;
Note: Backup location: Flash Recovery Area.

<Step – 11>
Create Standby control file.
SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';
<Step – 12>
Create pfile from spfile:
SQL> create pfile='/u01/bkup/initRTS.ora' from spfile;
<Step – 13>
After creating parameter file as above, edit following changes in newly created pfile:
*.db_unique_name='RTSDR'
*.fal_server='RTS';
*.log_archive_dest_2='SERVICE=RTS ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RTS'
<Step – 14>
Copy parameter file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr bkup]$ scp initRTS.ora
oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Copy standby control file to DR @ location: /u01/app/oracle/oradata/RTS/control01.ctl &

/u01/app/oracle/flash_recovery_area/RTS/control02.ctl
[oracle@pr bkup]$ scp stndbyctrl.ctl
oracle@192.168.17.132:/u01/app/oracle/oradata/RTS/control01.ctl
[oracle@pr bkup]$ scp stndbyctrl.ctl

oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/RTS/control02.ctl
Copy password file to DR @ location: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[oracle@pr ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@pr dbs]$ scp orapwRTS

oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Copy Listener file to DR @ location:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@pr bkup]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@pr admin]$ scp listener.ora

oracle@192.168.17.132:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
Edit DR database listener file with host name. ( i.e. (HOST = dr))
And finally copy RMAN backup to DR @ flash recovery area.
[oracle@pr ~]$ cd /u01/app/oracle/flash_recovery_area/
[oracle@pr flash_recovery_area]$ scp -r RTS

oracle@192.168.17.132:/u01/app/oracle/flash_recovery_area/
Standby/DR Server Configurations:

<Step – 15>
Startup standby database in mount state:
Set following environment variable as oracle user OR edit those in
/home/oracle/.bash_profile in order to set it for every time while oracle user logged in:
export ORACLE_SID=RTS
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
SQL> sqlplus / as sysdba
SQL> startup mount
ORACLE instance started.
Total System Global Area 972898304 bytes
Fixed Size 2219272 bytes
Variable Size 566231800 bytes
Database Buffers 398458880 bytes

Redo Buffers 5988352 bytes
Database mounted.
<Step – 16>
Create spfile from pfile:
SQL> create spfile from

pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initRTS.ora';
<Step – 17>
Start listener on standby:
[oracle@dr ~]$ lsnrctl start
<Step – 18>
Create follwoing directories on DR server, in case those are not available:
/u01/app/oracle/admin/RTS/adump
/u01/app/oracle/flash_recovery_area
<Step – 19>
Restore and recover database @ DR with RMAN backup utility.
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6 B F A DISK 05-OCT-14 1 1 NO TAG20141005T065604
RMAN> restore database;
RMAN> recover database;
Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it
because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10
) who is not also available on Primary database.
Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1
with sequence 10 and starting SCN of 1009554
<Step – 20>
Create standby redolog file to Primary and DR for the user of switch over, It should be
match the configuration of the primary server.
Note: Create one additional standby redolog file on both.
SQL> sqlplus / as sysdba
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;
SQL> ALTER DATABASE ADD STANDBY

LOGFILE('/u01/app/oracle/oradata/RTS/stndby1.log') size 51M;



SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;
<Step – 21>
Start apply process @ DR.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM
SESSION;
In case of you want to cancel apply process, issue following command:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
<Step – 22>
After graceful completion of above apply process, verify archive logs on Primary as well
as DR.
On Primary:

On Standby:
By above result, archive logs on both the databases are in sync now.
To test your data guard configuration, generate archive logs on primary site and verify it
on DR site:
SQL> alter system switch logfile; //Give this command multiple times for
testing.
On Primary:

OR
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
On Standby:
OR
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
Cheers!! Our Data Guard configuration has been configured successfully.

Verify database roles by below mentioned SQL query:
On Primary:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY
On DR:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY
You can also verify total number of log sequence generated and applied on DR site, by
below SQL query:
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#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
3 04-OCT-14 04-OCT-14 YES
4 04-OCT-14 04-OCT-14 YES
5 04-OCT-14 04-OCT-14 YES
6 04-OCT-14 05-OCT-14 YES
7 05-OCT-14 05-OCT-14 YES
8 05-OCT-14 05-OCT-14 YES

9 05-OCT-14 05-OCT-14 YES
10 05-OCT-14 05-OCT-14 YES
11 05-OCT-14 05-OCT-14 YES
12 05-OCT-14 05-OCT-14 YES
13 05-OCT-14 05-OCT-14 YES
14 05-OCT-14 05-OCT-14 YES
15 05-OCT-14 05-OCT-14 YES
16 05-OCT-14 05-OCT-14 YES
17 05-OCT-14 05-OCT-14 YES
18 05-OCT-14 05-OCT-14 YES
19 05-OCT-14 05-OCT-14 YES
20 05-OCT-14 05-OCT-14 YES
In case of you are facing any kind of error than following SQL query will help you to
diagnose it.
SQL> select dest_name,status,error from v$archive_dest where

dest_name='LOG_ARCHIVE_DEST_2';
DEST_NAME
----------------------------------------------------------------------------
----
STATUS ERROR
--------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2
VALID
Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync.
OR
SQL> select message from v$dataguard_status;

Data Guard Physical Standby Setup in Ora

Uploaded by

Copyright:

Available Formats

Data Guard Physical Standby Setup in Ora

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Guard Physical Standby Setup in Ora

Uploaded by

Copyright:

Available Formats

Data Guard Physical Standby Setup in Oracle

Database 11g Release 2

Primary Server Setup

SELECT log_mode FROM v$database;

If it is noarchivelog mode, switch is to archivelog mode.

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

SQL> show parameter db_name

NAME TYPE VALUE

SQL> show parameter db_unique_name

NAME TYPE VALUE

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to

ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;

Backup Primary Database

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';

Create a parameter file for the standby database.

CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;

Standby Server Setup (Manual)

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.

$ # Archivelogs and backups

$ # Remote login password file.

SQL> CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';

Restore the backup files.

RMAN> STARTUP MOUNT;

Create Redo Logs

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD STANDBY LOGFILE

Once this is complete, we can start the apply process.

Standby Server Setup (DUPLICATE)

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.

$ # Remote login password file.

Make sure the listener is started on the standby server.

Create Standby Redo Logs on Primary Server

ALTER DATABASE ADD STANDBY LOGFILE

Create Standby Using DUPLICATE

SQL> STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';

$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE

A brief explanation of the individual clauses is shown below.

Once the command is complete, we can start the apply process.

Start Apply Process

# Foreground redo apply. Session never returns until cancel.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Test Log Transport

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time

ALTER SYSTEM SWITCH LOGFILE;

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied

 Maximum Availability: Transactions on the primary do not commit until redo