Configuration Oracle RAC to Dataguard RAC

This document explains the step by step process of building the 11g R2 (11.2.0.1) 3-Node RAC primary to 2-Node RAC Physical Standby DataGuard on Oracle Enterprise Linux

Technical Architecture of 11g R2 RAC primary to RAC standby DataGuard Configuration:

  

	PRIMARY	STANDBY
Clusterware	11g R2 Grid Infrastructure (11.2.0.1)	11g R2 Grid Infrastructure (11.2.0.1)
Cluster Nodes	node1, node2, node3 (3-node RAC)	dr-node1, dr-node2 (2-node RAC)
SCAN	lab-scan.hingu.net	dr-lab-scan.hingu.net
SCAN listener Host/port	SCAN VIPs (port 1525)	SCAN VIPs (port 1525)
VIPs	node1-vip, node2-vip, node3-vip	dr-node1-vip, dr-node2-vip
DB_UNIQUE_NAME	USA	INDIA
DB_NAME	BHAVIN	BHAVIN
DB Instances	bhavin1, bhavin2, bhavin3	bhavin1, bhavin2
DB LISTENER	BHAVIN_LISTENER	BHAVIN_LISTENER
DB Listener Host/port	node1-vip, node2-vip, node3-vip (port 1530)	dr-node1-vip, dr-node2-vip (port 1530)
DB STORAGE	ASM	ASM
File Management	OMF	OMF
ASM diskgroup for DB files	DATA	DATA
ASM Diskgroup for Recovery Files	FRA	FRA
ORACLE_HOME	/u01/app/oracle/db11201	/u01/app/oracle/db11201
11g R2 RAC version	11.2.0.1	11.2.0.1
OS	Oracle Enterprise Linux 5.5 (32 bit)	Oracle Enterprise Linux 5.5 (32 bit)

Existing Setup (Before the Dataguard Setup):

It is assumed that preliminary requirement to configure RAC to RAC 11g R2 Dataguard has been established. Here are the screenshots for the existing Setup of Primary as well as Standby site.

PRIMARY Site:

·         3-node 11g R2 Grid Infrastructure (11.2.0.1) has been installed and configured.

·         3-node RAC software (11.2.0.1) has been installed and configured.

·         ASM diskgroup DATA and FRA has been created.

·         Database “BHAVIN” is created on ASM and configured to use OMFs.

·         DB listener BHAVIN_LISTENER has been created and running on port 1530.

·         Database Instances are configured with LOCAL_LISTENER and REMOTE_LISTENER parameters.

DR Site:

·         2-node 11g R2 Grid Infrastructure (11.2.0.1) has been installed and configured.

·         2-node RAC software (11.2.0.1) has been installed and configured.

·         ASM diskgroup DATA and FRA has been created.

·         DB listener BHAVIN_LISTENER has been created and running on port 1530.

Click here for the detail information on installing/configuring 11g R2 Grid Infrastructure, Installing 11g R2 RAC, creating LISTENERs and creating a RAC database on ASM.

RAC to RAC Physical Dataguard:

·         Prepare Primary Site

·         Prepare Standby Site

·         Create RAC Physical Standby Database

·         Configure DataGuard Broker

·         Perform Switchover/Failover using DG Broker

Prepare Primary RAC database for DR configuration:

·         Enable Force Logging.

·         Modify init Parameters.

·         Enable Archivelog Mode.

·         Create the SLRs (Standby Redo Logs).

·         Backup the Database for Standby

·         Create pfile for standby database.

·         Update the tnsnames.ora.

Enable Force Logging:

alter database force logging;

Modify Dataguard related init Parameters:

DB_NAME=BHAVIN

DB_UNIQUE_NAME=USA

LOG_ARCHIVE_CONFIG='DG_CONFIG=(USA,INDIA)'

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=USA'

LOG_ARCHIVE_DEST_2='SERVICE=INDIA ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=8

FAL_SERVER=INDIA

DB_FILE_NAME_CONVERT='INDIA','USA'

LOG_FILE_NAME_CONVERT= 'INDIA','USA'

STANDBY_FILE_MANAGEMENT=AUTO

The db_unique_name parameter has already been set to the appropriate value during the initial creation of the RAC database. The log_archive_dest_state_n and remote_login_passwordfile have default values set to ENABLE and EXCLUSIVE respectively. So, only below mentioned parameter needed to be changed here.

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(USA,INDIA)' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=USA' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=INDIA ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA' scope=both sid='*';

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';

alter system set log_archive_max_processes=8 scope=both sid='*';

alter system set fal_server=INDIA scope=both sid='*';

alter system set db_file_name_convert='INDIA','USA' scope=spfile sid='*';

alter system set log_file_name_convert='INDIA','USA' scope=spfile sid='*';

alter system set standby_file_management=AUTO scope=both sid='*';

Verify that the values are set correctly for these parameters after bouncing the database.

set linesize 500 pages 0

col value for a90

col name for a50

select name, value

from v$parameter

where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',

               'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',

               'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',

                     'log_file_name_convert', 'standby_file_management')

Enable Archivelog Mode:

srvctl stop database –d usa

startup mount (only one instance)

alter database archivelog

alter database open

srvctl start database –d usa ß This will start the remaining Instances on the cluster.

Create the Standby Redo Logs (SRLs) on Primary and Standby:

There should be minimum of (threads)*(groups Per Threads + 1) SLRs created on the standby database. There are 3 threads with 2 groups per thread in this configuration on the primary side so there should be total of 9 SLRs at minimum needs to be created. Let’s get the total Groups and Max size of the logfile from v$log.

SQL> select max (bytes), count (1) from v$log;

MAX(BYTES)   COUNT(1)

---------- ----------

  52428800         6

Here, the total no. of online redo groups are 6 and the maximum size of these groups is 52M. So, 3 Standby Redo Log groups per thread with size of 52M each should be created on Primary as well as standby database.

ASMCMD> mkdir +fra/usa/STANDBYLOG  ß (connected as grid user using asmcmd)

alter system set standby_file_management=manual scope=both sid='*';

alter database add standby logfile thread 1 group 7 '+fra/usa/standbylog/standby_group_07.log' size 52M;

alter database add standby logfile thread 1 group 8 '+fra/usa/standbylog/standby_group_07.log' size 52M;

alter database add standby logfile thread 1 group 9 '+fra/usa/standbylog/standby_group_07.log' size 52M;

alter database add standby logfile thread 2 group 10 '+fra/usa/standbylog/standby_group_10.log' size 52M;

alter database add standby logfile thread 2 group 11 '+fra/usa/standbylog/standby_group_11.log' size 52M;

alter database add standby logfile thread 2 group 12 '+fra/usa/standbylog/standby_group_12.log' size 52M;

alter database add standby logfile thread 3 group 13 '+fra/usa/standbylog/standby_group_13.log' size 52M;

alter database add standby logfile thread 3 group 14 '+fra/usa/standbylog/standby_group_14.log' size 52M;

alter database add standby logfile thread 3 group 15 '+fra/usa/standbylog/standby_group_15.log' size 52M;

alter system set standby_file_management=auto scope=both sid='*';

Backup The Primary Database For Standby.

Take the backup of the primary database and the standby controlfile. Create the staging directory to hold the RMAN backup.

mkdir /home/oracle/backup

rman target / nocatalog

run

{

     sql "alter system switch logfile";

     allocate channel ch1 type disk format '/home/oracle/backup/Primary_bkp_for_stndby_%U';

     backup database;

     backup current controlfile for standby;

     sql "alter system archive log current";

}

Create pfile For Standby.

create pfile='pfile_for_standby.txt' from spfile;

Update TNSNAMES.ora

Add the tns alias for each of the instances of the primary database.

USA1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = usa.hingu.net)

      (SID = bhavin1)

    )

  )

USA2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = usa.hingu.net)

      (SID = bhavin2)

    )

  )

USA3 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = usa.hingu.net)

      (SID = bhavin3)

    )

  )

Copy the tnsnames.ora on all the instances under $ORACLE_HOME/network/admin to keep the same tnsnames.ora on all the instances.

Prepare Standby Site for Dataguard Configuration:

·         Copy the RMAN backup and pfile_for_standby.txt.

·         Copy the password file.

·         Create required directories for Standby Instance.

·         Modify the pfile.

·         Copy the tnsnames.ora from Primary and add the TNS Alias for the standby database instances.

·         Create the ASM directories under the DATA and FRA diskgroup.

Copy the RMAN Backup files and init.ora from Primary to Standby:

Copy the backups from primary node1 to dr-node1 under the same location as primary node1 (/home/oracle/backup). Also copy the pfile that was created for standby on primary node1 to the dr-node1.

scp $ORACLE_HOME/dbs/pfile_for_standby.txt dr-node1:$ORACLE_HOME/dbs/pfile_for_standby.txt

scp –r /home/oracle/backup dr-node1:/home/oracle

  Copy the Password File from Primary to all the nodes in Standby.

Copy the file $ORACLE_HOME/dbs/orapwbhavin1 from Primary node 1 to both the standby nodes under the directory $ORACLE_HOME/dbs with the name of orapwbhavin1 and orapwbhavin2 for dr-node1 and dr-node2 respectively.

scp $ORACLE_HOME/dbs/orapwbhavin1 dr-node1:$ORACLE_HOME/dbs/orapwbhavin1

scp $ORACLE_HOME/dbs/orapwbhavin1 dr-node2:$ORACLE_HOME/dbs/orapwbhavin2

Create required dump file directories for the Standby Instances

On dr-node1:

mkdir –p /u01/app/oracle/admin/india/adump

mkdir –p /u01/app/oracle/diag/rdbms/india/bhavin1

cd /u01/app/oracle/diag/rdbms/india/bhavin1

mkdir trace cdump

On dr-node2:

mkdir –p /u01/app/oracle/admin/india/adump

mkdir –p /u01/app/oracle/diag/rdbms/india/bhavin2

cd /u01/app/oracle/diag/rdbms/india/bhavin2

mkdir trace cdump

Modify pfile_for_standby.txt  file (under $ORACLE_HOME/dbs)

The one’s in RED color are modified for the standby database. The rest of the parameters remain same on both the primary and standby.

*.audit_file_dest='/u01/app/oracle/admin/india/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+DATA/india/controlfile/control01.ctl','+FRA/india/controlfile/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_create_online_log_dest_1='+DATA'

*.db_create_online_log_dest_2='+FRA'

*.db_domain='hingu.net'

*.db_file_name_convert='USA','INDIA'

*.db_name='bhavin'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=4039114752

*.db_unique_name='india'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=bhavinXDB)'

*.fal_server='USA'

bhavin1.instance_number=1

bhavin2.instance_number=2

bhavin1.local_listener='LISTENER_BHAVIN1'

bhavin2.local_listener='LISTENER_BHAVIN2'

*.log_archive_config='DG_CONFIG=(USA,INDIA)'

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=INDIA'

*.log_archive_dest_2='SERVICE=USA ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=USA'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=8

*.log_file_name_convert='USA','INDIA'

*.memory_target=1484783616

*.open_cursors=300

*.processes=1024

*.remote_listener='dr-lab-scan.hingu.net:1525'

*.remote_login_passwordfile='exclusive'

*.service_names='INDIA','india.hingu.net'

*.sessions=1131

*.standby_file_management='AUTO'

bhavin2.thread=2

bhavin1.thread=1

bhavin1.undo_tablespace='UNDOTBS1'

bhavin2.undo_tablespace='UNDOTBS2'

Copy the  tnsnames.ora  file from primary (under $ORACLE_HOME/network/admin)

From primary node1:

scp $ORACLE_HOME/network/admin/tnsnames.ora dr-node1:$ORACLE_HOME/network/admin/tnsnames.ora

scp $ORACLE_HOME/network/admin/tnsnames.ora dr-node2:$ORACLE_HOME/network/admin/tnsnames.ora

Now, Modify the LOCAL_LISTENER parameters in the tnsnames.ora to reflect the dr specific vip host names (On the Standby Nodes only).

LISTENER_BHAVIN1=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dr-node1-vip.hingu.net)(PORT = 1530)))

LISTENER_BHAVIN2=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dr-node2-vip.hingu.net)(PORT = 1530)))

Create the ASM directories

Connect to the asmcmd as a grid Operating System user and create the below directories.

ASMCMD> mkdir data/INDIA

ASMCMD> cd data/india

ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG

ASMCMD> mkdir fra/INDIA

ASMCMD> cd fra/india

ASMCMD> mkdir ARCHIVELOG CONTROLFILE ONLINELOG STANDBYLOG

Create the Physical Standby Database:

·         Start the Instance in NOMOUNT using the pfile_for_standby.txt.

·         Restore the Standby Database using RMAN DUPLICATE command.

·         Create the Online Redo logs and Standby redo logs.

·         Modify the TNSNAMES.ORA of Standby to add the Standby Database’s TNS alias.

·         Modify the TNSNAMES.ORA of Primary to add the Standby Database’s TNS alias.

·         Start the Managed Recovery Process

·         Create the spfile and start the database on both the nodes using spfile.

·         Register the New Standby database to the OCR.

·         Start the Active Dataguard.

Start the Instance in NOMOUNT state:

Connect to the dr-node1 as oracle user and start the Instance bhavin1 using the $ORACLE_HOME/dbs/pfile_for_standby.txt parameter file.

export ORACLE_SID=bhavin1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/db11201

export PATH=$PATH:$ORACLE_HOME/bin:.

sqlplus / as sysdba

startup nomount pfile=$ORACLE_HOME/dbs/pfile_for_standby.txt

Restore the RMAN backup using DUPLICATE DATABASE option:

Connect to the Target database (USA) and auxiliary instance (INDIA) from dr-node1 host to start the DUPLICATE.

rman target sys/oracle@usa1 auxiliary /

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

Modify tnsnames.ora on Standby:

Existing tnsnames.ora does not contain the TNS alias of the standby database. It only contains TNS aliases for the Primary database because this tnsnames.ora was copied over from primary. Add the below TNS alias for standby database/instances into the tnsnames.ora file on both the standby hosts.

INDIA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-lab-scan.hingu.net)(PORT = 1525))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = india.hingu.net)

    )

  )

INDIA1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-node1-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = india.hingu.net)

      (SID = bhavin1)

    )

  )

INDIA2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-node2-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = india.hingu.net)

      (SID = bhavin2)

    )

  )

Modify tnsnames.ora on Primary:

Add the below TNS alias for standby database/instances into the tnsnames.ora file on all the primary hosts.

INDIA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-lab-scan.hingu.net)(PORT = 1525))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = india.hingu.net)

    )

  )

INDIA1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-node1-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = india.hingu.net)

      (SID = bhavin1)

    )

  )

INDIA2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dr-node2-vip.hingu.net)(PORT = 1530))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = india.hingu.net)

      (SID = bhavin2)

    )

  )

Start the Managed Recovery Process:

Start the Managed recovery process on dr-node1 and verify that the log transport and log application is happening. Alert log is a quick and easy way to see if things log transport/Gap resolution and log application is working as expected. Start the tail –f on alert logs on both the standby nodes before starting the MRP.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Perform the log switch on the primary database so initiate the log Transport.

alter system switch logfile

Similar information can be verified by running below SQLs on standby database.

select * from v$archive_gap;

select process, client_process, sequence#, status from v$managed_standby;

select sequence#, first_time, next_time, applied from v$archived_log;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select thread#, max (sequence#) from v$log_history group by thread#;

select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

Create spfile from pfile:

create spfile='+data/india/parameterfile/spfileINDIA.ora' from pfile='/u01/app/oracle/db11201/dbs/pfile_for_standby.txt';

After creating the spfile, create the below init.ora files under $ORACLE_HOME/dbs on both the dr nodes with the spfile entry so that the instance can start with the newly created spfile.

On dr-node1:

[oracle@dr-node1 dbs]$ cat initbhavin1.ora

spfile='+data/india/parameterfile/spfileINDIA.ora'

On dr-node2:

[oracle@dr-node1 dbs]$ cat initbhavin2.ora

spfile='+data/india/parameterfile/spfileINDIA.ora'

Add Standby database and Instances to the OCR:

Add the standby database and its instances to the OCR so that it is managed by CRS.

srvctl add database -d india -n bhavin -o /u01/app/oracle/db11201 -m hingu.net -p +data/india/parameterfile/spfileindia.ora -r physical_standby -a DATA,FRA

srvctl add instance -d india -i bhavin1 -n dr-node1

srvctl add instance -d india -i bhavin2 -n dr-node2

srvctl start database -d india

srvctl modify database -d india -s mount

If the standby database should not start in open mode, then it can be changed by below command. The valid options are open (default), mount and nomount.

srvctl modify database -d india -s mount

Verify the configuration of standby database india.

srvctl config database -d india

Start the Active Dataguard:

In Active Dataguard, the MRP can be running while the Physical Standby database is opened in READ ONLY mode. So, start the MRP after the database is opened in read only mode to activate the Active Dataguard.

srvctl start database –d india –o open

alter database recover managed standby database disconnect from session;

Create the Standby Redo Logs (SRLs) on Standby:

DUPLICATE DATABASE command has replicated the same no. of Online Redo Logs and Standby Redologs from primary database to the Standby database. So, they were not needed to create here.

Change the Protection Mode:

 New Protection Mode: Maximum Availability.

On Primary:

alter system set LOG_ARCHIVE_DEST_2='SERVICE=INDIA SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA' scope=both sid='*';

alter database set standby database to maximize availability;

On Standby:

alter system set LOG_ARCHIVE_DEST_2='SERVICE=USA SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=USA' scope=both sid='*';

alter database set standby database to maximize availability;

Final Configuration Files:

Here is the final look of parameter file, tnsnames.ora and listener.ora files at this stage of successful configuration of 3-node RAC primary to 2-node RAC Physical DataGuard.

Configure Dataguard Broker:

·         Stop the MRP

·         Modify the Listener.ora files

·         Modify the init Parameters

·         Create Configuration

·         Enable Configuration

·         Verify the Configuration

Stop the MRP on standby database:

alter database recover managed standby database cancel;

Modify the listener.ora files:

Update the listener.ora on all the nodes in Dataguard configuration to register a service with the local listener for each instance for the proper functioning of DGMGRL. Add the below lines to the listener.ora file for each of the specified instances.

Primary Node1

SID_LIST_BHAVIN_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=bhavin1)(GLOBAL_DBNAME=usa_DGMGRL.hingu.net)(ORACLE_HOME=/u01/app/oracle/db11201)))

Primary Node2

SID_LIST_BHAVIN_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=bhavin2)(GLOBAL_DBNAME=usa_DGMGRL.hingu.net)(ORACLE_HOME=/u01/app/oracle/db11201)))

Primary Node3

SID_LIST_BHAVIN_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=bhavin3)(GLOBAL_DBNAME=usa_DGMGRL.hingu.net)(ORACLE_HOME=/u01/app/oracle/db11201)))

Standby Node1

SID_LIST_BHAVIN_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=bhavin1)(GLOBAL_DBNAME=india_DGMGRL.hingu.net)(ORACLE_HOME=/u01/app/oracle/db11201)))

Standby Node2

SID_LIST_BHAVIN_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=bhavin2)(GLOBAL_DBNAME=india_DGMGRL.hingu.net)(ORACLE_HOME=/u01/app/oracle/db11201)))

After updating these listener.ora files, restart listeners on each of these nodes.

Modify the init parameters:

Modify these below DataGuard Broker related parameters on both the sides.

Standby:

mkdir +fra/india/DATAGUARDCONFIG

mkdir +data/india/DATAGUARDCONFIG

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+fra/india/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+data/india/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';

alter system set dg_broker_start=true scope=both sid='*';

primary:

mkdir +fra/usa/DATAGUARDCONFIG

mkdir +data/usa/DATAGUARDCONFIG

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+fra/usa/DATAGUARDCONFIG/dgb_config02.ora' SCOPE=BOTH sid='*';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+data/usa/DATAGUARDCONFIG/dgb_config01.ora' SCOPE=BOTH sid='*';

alter system set dg_broker_start=true scope=both sid='*';

Create Configuration:

Connect to the DGMGRL and run the below statements to create and enable the DataGuard configuration. Verify that the DG Broker is configured correctly and it has all the databases and instances registered as expected.

CREATE CONFIGURATION 'DG_Config' AS PRIMARY DATABASE IS 'usa' CONNECT IDENTIFIER IS 'usa';

ADD DATABASE 'india' AS CONNECT IDENTIFIER IS india;

Enable Configuration:

enable configuration;

Verify Configuration:

show configuration;

show database verbose india;

show database verbose usa

show instance verbose bhavin1 on database usa

show instance verbose bhavin2 on database usa

show instance verbose bhavin3 on database usa

show instance verbose bhavin1 on database india

show instance verbose bhavin2 on database india

Performing Switchover/Failover with Dataguard Broker

I received the below error while performing either switchover or failover operation using DataGuard Broker. This is due to the Oracle bug (9645789) occurring when DG Broker trying to stop any custom services in the database. The workaround is to remove all the custom services using srvctl command and add them back in the OCR at the end of the role transition operation.

DGMGRL> switchover to india

Performing switchover NOW, please wait...

Error: ORA-16535: Oracle Restart or Oracle Clusterware prevented completion of broker operation

Failed.

Unable to switchover, primary database is still "usa"

srvctl stop service –d usa –s oltp

srvctl remove service –d usa –s oltp

show parameter service_names (SQLPLUS)

Switchover:

·         Shutdown all the instance on Primary but one.

·         Shutdown all the instance on Standby but one.

·         Connect to dgmgrl on primary.

·         Execute “switchover to india” on dgmgrl.

After the successful Switchover operation , start the remaining instances on both the databases. Modify the database configuration in OCR with their appropriate role and start option. Add any custom services to the new Primary (india) database that were removed earlier.

srvctl modify database – d usa –r physical_standby –s mount

srvctl modify database –d india –r primary –s open

srvctl add service -d india -s oltp -r bhavin1,bhavin2 -r primary -e session -m basic -B SHORT (on the new Primary cluster)

srvctl add service -d usa -s oltp -r bhavin1,bhavin2,bhavin3 -r physical_standby -e session -m basic -B SHORT (on the new standby cluster)

For the Failover, use DGMGRL command “failover to usa”

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

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.

• Assumptions
• Primary Server Setup
• Logging
• Initialization Parameters
• Service Setup
• Backup Primary Database
• Create Standby Controlfile and PFILE

• Standby Server Setup (Manual)
• Copy Files
• Start Listener
• Restore Backup
• Create Redo Logs

• Standby Server Setup (DUPLICATE)
• Copy Files
• Start Listener
• Create Standby Redo Logs on Primary Server
• 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 (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 the SERVICE 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 patch 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

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.

$ export ORACLE_SID=DB11G
$ 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 DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.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 is standby redo logs must be created on both servers.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.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.

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary "init.ora" file.

$ export ORACLE_SID=DB11G
$ 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.

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 CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

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 a newly created standby 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 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

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;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

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;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;

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;
SELECT flashback_on FROM v$database;

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.

For more information see:

• Creating a Physical Standby Database
• Converting a Failed Primary Into a Standby Database Using Flashback Database
• Data Guard (9i)
• Data Guard (11gR2) Setup using Oracle Grid Control