RAC to Single Instance Data Guard Physical Standby

This article shows how to setup an Oracle 11.2.0.2 binary on both a Solaris 10 and on an OEL (Oracle Linux Server release 6.4) system.

This article shows how to setup an Oracle 11.2.0.2 binary on both a Solaris 10 and on an OEL (Oracle Linux Server release 6.4) system.

It’s intended to describe a Solaris-based installation, however some parts include Linux-based procedures.

 

Before beginning the installation process it is highly recommended to read the entire article. The process would be easier if a data guard and RAC has already been installed from one instance to another.

 

Please let me know if there are any mistakes or omissions in the steps shown in this article.

 

DG-RAC_Single_Instance

PRIMARY

STANDBY

Hostname/s

dbk01, dbk02 stddbk

Database Name

mx

mx

Database Unique Name

mx

stdmx

Net Service Name

mx.sysdba.org

stdmx.sysdba.org

Clusterware

11g R2 Grid Infrastructure (11.2.0.2)

11g R2 Grid Infrastructure (11.2.0.2)

Cluster Nodes

node1, node2 (2-node RAC)

Single instance

SCAN

mx-scan

SCAN listener Host/port

SCAN VIPs (port 1521)

DB Instances

mx1, mx2 stdmx

DB STORAGE

ASM ASM

File Management

ASM diskgroup for DB files

DATA DATA

ASM diskgroup for recovery files

FRA FRA

GRID_HOME

/u01/11.2.0/grid /u01/11.2.0/grid

ORACLE_HOME

/u01/app/oracle/product/11.2.0/db_1 /u01/app/oracle/product/11.2.0/db_1

OS

SUN 10u10 SUN 10u10

RDBMS Version

11.2.0.2 11.2.0.2

Grid Version

11.2.0.2 11.2.0.2

 

– Standby Server

  1. Setup both Grid Inf. and Stand Alone Server
  2. Create the DATA and FRA groups using ASMCA (ASM Configuration Asssistant)

– Set up only as a RDBMS only, without a database instance.

– Check to see whether the DATA and FRA groups, in the Primary’s RedoLogs, consist of 2 groups each. If not, then create these groups.

 

You can find the files listed here in this zip file: config_parameter_files.zip

  • .bash_profile/.profile
  • listener.ora
  • tnsnames.ora
  • startup parameters (pfile)

Useful resources:
http://tahiti.oracle.com

http://asanga-pradeep.blogspot.com/2010/11/rac-to-single-instance-physical-standby

http://easyoradba.com/2012/11/20/rac-to-single-instance-dataguard-oracle-11gr2-physical-standby/

http://dpmappsdba.blogspot.com/p/rac-to-single-instance-physical-standby

http://unixoracledba.wordpress.com/2012/03/29/how-to-create-a-single-instance-physical-standby-dataguard-for-a-rac-database/
****************************************************

Step 1. Placing the Primary Database into Archive Mode

Closing all instances using srvctl

Opening the databases

To check

Step 2. Enable Force Logging (if using fast start fail over) & configure the service

Step 3. Creating the Standby RedoLogs

Important notes:
• Don’t create the standby redologs using an alias as shown below.
• Don’t create online redo logs or standby redo logs in areas using automatic storage management (ASM) using this method:
• There are performance benefits to creating an additional user.
There are 2 redo logs in every node, as well as these the DBA should create an additional redo log for each node (as a backup).

Should be created in the following way.

Recommendation: Create the redo logs after setting up the Data Guard, as it is more likely to be tested after the second configuration.(Both in the Primary and in the Standby)

Step 4. Create the password files (orapw$instance_name)
(The password should be the same in all the machines)
In all of the nodes

–ignore case is an important parameter here since from release 11gR2 onwards passwords are case-sensitive
PING[ARC2]: Heartbeat failed to connect to standby ‘mx’. Error is 16191.
Error 1017 received logging on to the standby
These are common errors associated with password files. If you encounter the above errors, recreate the password files.

Step 5. Enter the Session ID (SID) information into listener.ora (in all of the machines including standby ones).
Primary (the same as the RAC-GRID HOME 2 node)

“lsnrctl status” should appear both in standby and primary instances. If it doesn’t, it may be necessary to setup the local_listener parameter (alter local_lister / register).

Step 6. tnsname.ora

In all of the nodes as well as the standby nodes

Step 7. Setting the init.ora parameters for the DataGuard (primary)

First create a backup pfile (send a copy of this file to the standby)

Close, reopen and check the updated values.
In the same directory, in its full state, as primaryinit.ora_for_std.ora

Send this file to standby and make the adjustments

The contents should be as the following

This addresses the spfile’s location. The database can’t be opened if the spfile can’t be opened.

Step 8. Create a temporary stage folders for the RMAN backup
Primary/standby (as an oracle user)

Step 9. Making a backup of rman for the standby, within the primary database.

Note: If the access speed between locations is low and the database is large, sending the RMAN backup using another method may be a better option.

*****************************************************************************************************

Step 10. Creating the directories

Step 11. Adjusting the init.ora parameters for the DataGuard. (making the necessary changes in the primaryinit.ora_for_std.ora file for the standby).

Step 12. Creating the password files (orapw$instance_name)
(There’s no need to do this if the file has already been created as shown in Step 4. of the Steps for the Primary Database)

Step 13. Entering the SID information into listener.ora

Note 1: The tests were carried out successfully before making any changes, however listener entries were made as shown in these articles.

Note 2: Saving to the listener.ora file, without leaving a space character results at the beginning of each line results in a syntax error.

Step 14. Entering the Primary RAC Data into tnsnamas.ora (making it the same as the primary).
All need to be the same. Check using $tnsping

Step 15. Creating the StandBY database

In the StandBY

alternatively

Ceate the standby redologs when opened in mount mode.

alternatively

Primary

Standby

To view the last archived redo logs

Primary

****************************************************************************************************

For the Primary database – on every node.

On Node 1

For the Standby database – on every node.

Due to a bug in version 11.2.0.2.0, NetTimeout has been set to 180.
Both of these should be used carefully as they can close the database depending on the protection mode.

Information

Log

Switchover

Note: After the switchOVER a few errors can be viewed using dgmgrl (Data Guard command-line interface) but after a while the Data Guard Broker fixes these and the errors disappear.

Both sides have thus successfully carried out a switch over.

*****************************************************************************************************
[/tab]
[tab title=”Gap Resolution” ]

Copying the archivelogs to the ASM and saving them:

***********************************************************************

To Switchover
Primary

Standby
If the Managed Recovery Process (MRP) has stopped

Original Primary (Currently Standby)

Error

Action: Query the GV$INSTANCE view as follows to determine which instances are causing the problem:

INSTANCE_NAME————-INST2

HOST_NAME—————–standby2

In the previous example, the identified instance must be manually shut down before the switchover can proceed. You can connect to the identified instance from your instance and issue the SHUTDOWN statement remotely, for example:

**************************************************************************************************************
[/tab]

[tab title=”Protection Modes” ]

Primary

Standby

Maximum Availability

Maximum Performance

Maximum Protection

**********************************************************************************************

Setting log_archive_dest_state to defer mode in the Primary

 

alter system set log_archive_dest_state_2=defer sid = ‘*’;alter system switch logfile ;

shutdown immediate

 

 

Shutdown Managed Recovery Process (MRP) in the Standby

 

alter database recover managed standby database cancel;shutdown immediate;

 

 

 

startup nomount;alter database mount standby database;

alter database recover managed standby database using current logfile disconnect;

alternatively

alter database recover managed standby database disconnect from session;

 

 

Open the primary database, enable log_archive_dest_state and alter the system switch logfile;

 

alter system set log_archive_dest_state_2=enable scope = both sid = ‘*’;

 

 

SHUTDOWN STANDBY DATABASE

Step 1. Disable standby archive writing:

at primary/production database

 

alter system set log_archive_dest_state_2=defer scope = both sid = ‘*’;

 

 

Step 2. Deactivate auto recovery in the Standby Database

 

alter database recover managed standby database cancel;

 

 

PS: When using a Real Application Cluster (RAC) the above command should be run on just ONE of the standby RAC nodes.

 

 

SQL> shutdown immediate

 

This should be applied on all standby nodes in an RAC environment.

 

STARTUP STANDBY DATABASE

If necessary archivelogs should be copied to the standby.

 

$scp /opt/oracle/archive/*.arc oracle@DR-Site-IP:/opt/oracle/archive

 

 

Step 3. Open the standby database and start the automatic recovery process.

 

SQL> startup nomountSQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

 

 

PS: In a Real Appication Cluster (RAC) environment this should be applied on all of the standby nodes.

 

SQL> startup nomount

 

SQL> alter database mount standby database;

 

 

Step 4. Enable Standy Archive writing

On the primary/production database

 

alter system set log_archive_dest_state_2=enable scope = both sid = ‘*’;

 

 

This only needs to be run on a single node in an RAC environment (sid=’*’).

**********************************************************************************************

Taking precautions before encountering any failures. Refer to Doc ID 1302539.1 for further details.

primary

standby

1.
ORA-15025: could not open disk “/dev/oracleasm/disks/DATA001”
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
SUCCESS: diskgroup DATA was mounted
Errors in file /u01/app/oracle/diag/rdbms/stdhira/stdhira/trace/stdhira_ora_26491.trc (incident=7361):
ORA-00600:

If there any errors, run the following command as a root user:

2. Deletion of Applied Archivelogs
If the archivelog files are being managed in the Fast Recovery Area then automatic deletion of backed up and applied archivelog files can be setup by issuing the following RMAN configure command on the primary database:

Creating a physical standby from ASM primary [ID 787793.1]
http://www.datadisk.co.uk/html_docs/oracle_dg/troubleshooting.htm
http://www.datadisk.co.uk/html_docs/oracle_dg/troubleshooting
http://www.oracledba.org

3. Data Guard Broker Status Summary:
Type Name Severity Status
Configuration dg_broker Warning ORA-16608
Primary Database hira Success ORA-00000
Physical Standby Database stdhira Warning ORA-16792

If there are any errors:
Using: show database ‘stdhira’ ‘InconsistentProperties’, find any different values (even if values are occasionally the same) and fix these errors with scope=spfile/both.

4. SQL Execution error=604, sql=[alter system set log_archive_dest_1=”]. See error stack below.
ORA-00604: error occurred at recursive SQL level 1
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires
ORA-00604 ORA-02097 ORA-16028
BUG 12535659 – BROKER OVERRIDES MANDATORY/LOG_ARCHIVE_MIN_SUCCEED_DEST

Solution:
Summary
In that moment’s Primary

RAC

single instance

The following steps have been explained in the metalink:
a. Use DGMGRL to gather information about the Broker configuration.
— From current Primary

# This should be done for the Primary and each Standby
b. Remove the broker configuration.

# This ensures standby operations continue while the Broker
# configuration is being re-created.

c. Change the LOG_ARCHIVE_DEST_n destinations defined with LOCATION to remove DB_UNIQUE_NAME.

# This must be done on all databases in the Data Guard environment
# that Broker will manage.

d. Re-create the Broker configuration.
— From current Primary:

# Use the info from Step #1 to re-create the Broker configuration
# and modify the properties on each database before enabling.

e. ORA-16826: apply service state is inconsistent with the DelayMins property
This can be fixed by disabling and and then enabling the dgmgrl configuration as follows:

5. Places to look if there are any connection problems:

6. Exception in thread “main” java.lang.UnsatisfiedLinkError: /tmp/OraInstall2013-12-30_01-26-43PM/jdk/jre/lib/sparcv9/motif21/libmawt.so: ld.so.1: java: fatal: libXm.so.4: open failed: No such file or directory…..
Solution to this problem