Installation Companion Oracle Data Guard on Amazon EC2 Configuration Guide

(1)

Installation Companion

Oracle Data Guard

on Amazon EC2

Configuration Guide

Solutions Network Narender Akula Navin Mudaliar Shakir Aleem Ahmed Sasin Gnanamoorthy September 2010

(2)

About this guide

The purpose of an installation companion is to centralize a variety of product-specific information for technical practitioners so they can quickly become familiar with the installation processes of Oracle Data Guard 11g on AWS infrastructure.

Installation steps have been tested using the specific documentation and software described in this document, which increases the probability of success. Installation companions may also describe lessons learned, and provide links to additional information that will help successful installation.

Installation companions are not intended to replace official Oracle documentation, nor should they be used in this way. Practitioners should familiarize themselves with the official Oracle installation guide(s) and critical OSS notes before starting the installation.

This guide provides instructions for How to configure Data Guard on Amazon EC2 Instance.

Executive summary

Amazon Web Services (AWS) provides on-demand dynamic Information Technology services via the Internet. This type of computing is known as Cloud Computing. The services AWS offers are a subset of Cloud Computing referred to as Platform as a Service (PaaS). With AWS, virtual servers can be quickly deployed with storage and networking that leverages the Elastic Cloud Compute (EC2) service. EC2 offers an alternative to traditional internal managed IT infrastructure. With AWS/EC2 you pay only for what you use and there are no upfront costs or long term commitments.

Amazon Web Service (AWS) – Suite of Cloud Computing services offered by Amazon: http://aws.amazon.com/what-is-aws

Elastic Cloud Computing (EC2) – AWS service that provides on-demand, resizable compute capacity (servers, disk, network, etc.) in the cloud: http://aws.amazon.com/ec2

Elastic Block Store (EBS) – Persistent block level storage volumes for Amazon EC2 instances: http://aws.amazon.com/ebs

Amazon Machine Image (AMI) – Preconfigured server images with various operating systems (Linux) and pre-installed software (databases, Oracle E-Business Suite R12).

Amazon Virtual Private Cloud (Amazon VPC) - Secure and seamless bridge between a company’s existing IT infrastructure and the AWS cloud. It isolates a subset of Amazon EC2 infrastructure and connects AWS servers to the corporate network via a secure, IPSEC VPN tunnel. Using this connection method, Amazon EC2 instances appear on the private network as a subnet of servers that are managed using the same security processes and tools that are used for servers physically deployed within the data center.

Instance – A running server based on AMI image.

Assumptions

1. An Amazon account for AWS, EC2 and S3 service is available. If not new accounts can be established at: http://aws.amazon.com

2. This document assumes the Oracle E-Business Suite or Oracle database is running. This document covers only data guard configuration on AWS.

(4)

Required Software

1. Firefox Browser, available at http://www.mozilla.com

2. S3 Firefox Organizer, available at https://addons.mozilla.org/en-US/firefox/addon/3247 3. Putty and Puttygen, Windows users can download and install PuTTY, a free SSH client. To

download the client an installation instructions, go to the http://www.chiark.greenend.org.uk/~sgtatham/putty/.

To get started, read the “Amazon EC2 Getting Started Guide” for instructions on setting up your “AWS” account and obtain your access key identifiers.

Create your key pair or use existing one if available and modify your security group to include SSH access to port 22 for PuTTY.

Logging into the AWS Management Console

The AWS Management Console provides a graphical user interface for Amazon EC2, Amazon Elastic MapReduce, and Amazon CloudFront.

Access the AWS Management Console via https://console.aws.amazon.com Below is a screenshot of the AWS Management Console

Logging into the Instance using Putty SSH

1. Now run the main PuTTY application. In the main Session screen, add the Public DNS for your new Amazon EC2 Instance into the Host/IP Box. You can also name your profile if you want to reuse.

(5)

2. In the menu tree on the left, expand “SSH” under “Connection” and select “Auth”. Click the “Browse” button in the “Private Key File” field and load the key you just created with PuTTYgen.

3. Click the “Open” button at the bottom (or you can go back to the “Session” screen to save your configuration first). A SSH command line window will open with a prompt saying “Login As:”. Type “root” and hit enter. If you see something like this, you have successfully logged in!

(6)

(7)

Data Guard Configuration

Oracle Data Guard is the key high availability (HA) and disaster recovery (DR) solution for the Oracle Database. With Fast-Start Failover along with enhancements in Oracle Database 11g, Data Guard can failover in a few seconds. The following section covers Oracle Data Guard configuration on AWS Infrastructure using Public and Hybrid Cloud scenarios.

Oracle E-Business Suite R12 – Public Cloud

Figure 1: Deployment with both Primary and Standby in the Cloud - using Availability Zones

Primary Server: EBSR12PRM.COMPUTE-1.AMAZONAWS.COM Standby Server: Ec2r12ebsdbs

Primary Database: VIS Standby Database: VISSTB

Hosting Facility: AWS EC2 Hosting Facility: AWS EC2

(8)

Create a Standby Instance

1. Login to Elasticfox or AWS Management Console select the Database Node AMI (ex: ami-12769e7b) and Launch Standby Instance.

2. The Launch Instance Wizard will be started.

1. Select the number of instances to start – in this case 1 2. Select the instance type (i.e. size) – Large

3. Select Availability Zone: Same zone of Primary Database or different zone 4. Select the key pair created earlier – oracledeloitteaws-east

5. Select the security group created earlier – 6. Click on Launch

After Instance is Launched Login to Instance as described in section “Logging into the Instance using Putty SSH”.

Host file configuration

1. Change the /etc/host file on the primary and standby to reflect both primary server and stand by server hostnames. .

127.0.0.1 localhost.localdomain localhost

10.203.22.180 EBSR12PRM.compute-1.amazonaws.com EBSR12PRM 10.203.22.176 ec2r12ebsdbs.compute-1.amazonaws.com ec2r12ebsdbs

Prepare Primary Server and Standby Server

2. Run preclone on primary database server

cd /data/oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_EBSR12PRM/ ./adpreclone.pl dbTier

3. Copy Oracle_home(/data/oracle/VIS/db/tech_st) from primary server to standby server(/data/oracle/VIS/db/tech_st).

4. Run Autoconfig on Standby Node.

cd /data/oracle/VIS/db/tech_st/11.1.0/appsutil/clone/bin/ ./adcfgclone.pl dbTechStack

[oracle@ec2r12ebsdbs bin]$ ./adcfgclone.pl dbTechStack

5. Listener on Standby Automatically started. Verify the Listener.

[oracle@ec2r12ebsdbs bin]$ lsnrctl status VIS

Connecting to

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ec2r12ebsdbs.compute1.amazonaws.com)(PORT=15 26)))

(9)

--- Alias VIS

Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production Start Date 06-SEP-2010 20:32:50

Uptime 0 days 0 hr. 6 min. 18 sec Trace Level off

Security ON: Local OS Authentication SNMP OFF

Listener Parameter File /data/oracle/VIS/db/tech_st/11.1.0/network/admin/VIS_ec2r12ebsdbs/listener.ora Listener Log File /data/oracle/VIS/db/tech_st/11.1.0/log/diag/tnslsnr/ec2r12ebsdbs/vis/alert/log.xml Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ec2r12ebsdbs.compute1.amazonaws.com)(PORT=152 6)))

Services Summary...

Service "VIS" has 1 instance(s).

Instance "VIS", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Prepare Primary Database

The Following steps needs to be executed on Primary Database. 1. Enable Logging

oracle@EBSR12PRM tech_st]$ sqlplus / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER DATABASE FORCE LOGGING;

2. Add Standby redo log Files (Bug: Log should be same size of redologs)

SQL> alter database add standby logfile '/data/oracle/VIS/db/apps_st/data/stb_redo01.log' SIZE 314572800;

SQL> alter database add standby logfile '/data/oracle/VIS/db/apps_st/data/stb_redo02.log' SIZE 314572800;

SQL> alter database add standby logfile '/data/oracle/VIS/db/apps_st/data/stb_redo03.log' SIZE 314572800;

3. Update Hosts file with Standby Node Information [oracle@EBSR12PRM tech_st]$ cat /etc/hosts

10.203.22.180 EBSR12PRM.compute-1.amazonaws.com EBSR12PRM 10.203.22.176 ec2r12ebsdbs.compute-1.amazonaws.com ec2r12ebsdbs 127.0.0.1 localhost.localdomain localhost

4. Update SID_Hostname_ifile.ora with TNS entries, do not use tnsnames.ora running Autoconfig will over write changes.

(10)

[oracle@EBSR12PRM tech_st]$ cat

/data/oracle/VIS/db/tech_st/11.1.0/network/admin/VIS_EBSR12PRM/VIS_EBSR12PRM_ifile.ora

VISSTB =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526)) )

(CONNECT_DATA = (SERVICE_NAME = VIS) )

)

5. Modify the Init.ora on Primary (Appendix A)

[oracle@EBSR12PRM dbs]$ cat VIS_EBSR12PRM_ifile.ora

LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/VIS/db/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=VIS' LOG_ARCHIVE_CONFIG='DG_CONFIG=(VIS,VISSTB)' DB_FILE_NAME_CONVERT='VIS','VIS' FAL_CLIENT='VIS' FAL_SERVER='VISSTB' log_archive_dest_2='SERVICE=VISSTB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=VISSTB' LOG_ARCHIVE_DEST_STATE_1='ENABLE' LOG_ARCHIVE_DEST_STATE_2='ENABLE' log_archive_format='%t_%s_%r.arc' LOG_FILE_NAME_CONVERT='VIS','VIS' remote_login_passwordfile='EXCLUSIVE' SERVICE_NAMES='VIS' STANDBY_FILE_MANAGEMENT='AUTO' db_unique_name=VIS

(11)

Prepare Standby database

1. Modify the Init.ora on Standby (Appendix A)

oracle@ec2r12ebsdbs bin]$ cat /data/oracle/VIS/db/tech_st/11.1.0/dbs/VIS_ec2r12ebsdbs_ifile.ora LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/VIS/db/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=VISSTB’ LOG_ARCHIVE_CONFIG='DG_CONFIG=(VIS,VISSTB)' DB_FILE_NAME_CONVERT='VIS','VIS' FAL_CLIENT='VISSTB' FAL_SERVER='VIS' log_archive_dest_2='SERVICE=VIS VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=VIS' LOG_ARCHIVE_DEST_STATE_1='ENABLE' log_archive_dest_state_2='DEFER' log_archive_format='%t_%s_%r.arc' LOG_FILE_NAME_CONVERT='VIS','VIS' remote_login_passwordfile='EXCLUSIVE' SERVICE_NAMES='VISSTB' STANDBY_FILE_MANAGEMENT='AUTO' db_unique_name=VISSTB

2. Copy password file from Primary to Standby.

Copy ORACLE_HOME/dbs/orapwVIS from primary to ORACLE_HOME/dbs/orapwVIS on standby 3. Startup standby database in nomount state

Update and Test connectivity between Primary and Standby

1. Update listener.ora as follows: (Appendix B) Primary Node:

VIS =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = EBSR12PRM.compute-1.amazonaws.com)(PORT = 1526)) ) ) SID_LIST_VIS = (SID_LIST = (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VIS_DGMGRL)

(12)

) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VIS_DGB) ) ) Standby Node: VIS = (DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526)) ) ) SID_LIST_VIS = (SID_LIST = (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VISSTB_DGMGRL) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VISSTB_DGB) ) )

2. Update tnsnames.ora on primary and standby with following entries (Appendix C):

VISSTB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526)) ) (CONNECT_DATA = (SID = VIS))) VIS = (DESCRIPTION =

(ADDRESS=(PROTOCOL=tcp)(HOST=EBSR12PRM.compute-1.amazonaws.com)(PORT=1526)) (CONNECT_DATA= (SID=VIS) ) )

• Run tnsping on Primary to verify:

[oracle@EBSR12PRM ~]$ tnsping VIS

(13)

Used parameter files:

/data/oracle/VIS/db/tech_st/11.1.0/network/admin/VIS_EBSR12PRM/sqlnet_ifile.ora Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=EBSR12PRM.compute-1.amazonaws.com)(PORT=1526)) (CONNECT_DATA= (SID=VIS))) OK (10 msec)

[oracle@EBSR12PRM ~]$ tnsping VISSTB

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526))) (CONNECT_DATA = (SID = VIS)))

OK (0 msec)

• Run tnsping on Standby to verify:

[oracle@ec2r12ebsdbs VIS_ec2r12ebsdbs]$ tnsping VISSTB

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526))) (CONNECT_DATA = (SID = VIS)))

OK (0 msec)

[oracle@ec2r12ebsdbs VIS_ec2r12ebsdbs]$ tnsping VIS

Attempting to contact (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=EBSR12PRM.compute-1.amazonaws.com)(PORT=1526)) (CONNECT_DATA= (SID=VIS))) OK (10 msec)

3. Logon to Primary, check the database connection

oracle@EBSR12PRM ~]$ sqlplus sys/syspwd@vis as sysdba

Connected to:

(14)

With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit

[oracle@EBSR12PRM dbs]$ sqlplus sys/syspwd@VISSTB as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>

4. Logon to Secondary, check the database connection

[oracle@ec2r12ebsdbs dbs]$ sqlplus sys/syspwd@vis as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit

[oracle@ec2r12ebsdbs dbs]$ sqlplus sys/syspwd@VISSTB as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit

Create Standby Database using RMAN

1. Create cr8stndbyfrmactv.cmd with RMAN Commands on Primary Database. [oracle@EBSR12PRM ~]$ cat cr8stndbyfrmactv.cmd

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK DORECOVER;

2. Execute RMAN Command.

nohup rman target=sys/syspwd@vis auxiliary=sys/syspwd@VISSTB cmdfile=cr8stndbyfrmactv.cmd log=standby_creation.log &

tail –f standby_creation.log (Content is stripped due to size of log file ) [oracle@EBSR12PRM ~]$ cat standby_creation.log

connected to target database: VIS (DBID=275616243) connected to auxiliary database: VIS (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK DORECOVER;

2>

(15)

using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=382 device type=DISK contents of Memory Script:

{

backup as copy reuse

file '/data/oracle/VIS/db/tech_st/11.1.0/dbs/orapwVIS' auxiliary format '/data/oracle/VIS/db/tech_st/11.1.0/dbs/orapwVIS' ;

}

executing Memory Script Starting backup at 18-AUG-10 allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=289 device type=DISK Finished backup at 18-AUG-10

contents of Memory Script: {

backup as copy current controlfile for standby auxiliary format '/data/oracle/VIS/db/apps_st/data/cntrl01.dbf';

restore clone controlfile to '/data/oracle/VIS/db/apps_st/data/cntrl02.dbf' from '/data/oracle/VIS/db/apps_st/data/cntrl01.dbf';

restore clone controlfile to '/data/oracle/VIS/db/apps_st/data/cntrl03.dbf' from '/data/oracle/VIS/db/apps_st/data/cntrl01.dbf';

sql clone 'alter database mount standby database'; }

executing Memory Script executing Memory Script Starting backup at 18-AUG-10 using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy copying standby control file

output file name=/data/oracle/VIS/db/tech_st/11.1.0/dbs/snapcf_VIS.f tag=TAG20100818T164214 RECID=4 STAMP=727375336

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 18-AUG-10

Starting restore at 18-AUG-10 using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy Finished restore at 18-AUG-10

Starting restore at 18-AUG-10 using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy Finished restore at 18-AUG-10

(16)

contents of Memory Script: {

set newname for tempfile 1 to

"/data/oracle/VIS/db/apps_st/data/temp01.dbf"; set newname for tempfile 2 to

"/data/oracle/VIS/db/apps_st/data/temp02.dbf"; set newname for tempfile 3 to

"/data/oracle/VIS/db/apps_st/data/temp03.dbf"; switch clone tempfile all;

< Content trimmed>

sql 'alter system archive log current'; }

executing Memory Script

executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME

renamed tempfile 1 to /data/oracle/VIS/db/apps_st/data/temp01.dbf in control file renamed tempfile 2 to /data/oracle/VIS/db/apps_st/data/temp02.dbf in control file renamed tempfile 3 to /data/oracle/VIS/db/apps_st/data/temp03.dbf in control file Starting backup at 18-AUG-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00017 name=/data/oracle/VIS/db/apps_st/data/sys6.dbf

output file name=/data/oracle/VIS/db/apps_st/data/sys6.dbf tag=TAG20100818T164233 RECID=0 STAMP=0

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25 channel ORA_DISK_1: starting datafile copy

[oracle@EBSR12PRM ~]$

Check for any error in the RMAN Log.

Configuring Data Guard Broker

1. Setup database parameters on primary and standby database for Data Guard Broker – These parameters include: (Appendix A)

global_names= TRUE

dg_broker_start = TRUE

dg_broker_config_file1 = $ORACLE_HOME/dbs/file1 dg_broker_config_file2 = $ORACLE_HOME/dbs/file2

SQL> alter system set global_names=TRUE scope=both;

(17)

SQL>ALTER SYSTEM SET

DG_BROKER_CONFIG_FILE1='/data/app/oracle/product/11.1.0/db_1/dbs/DGCTPRMDB1.dat' scope=both;

SQL> ALTER SYSTEM SET

DG_BROKER_CONFIG_FILE2='/data/app/oracle/product/11.1.0/db_1/dbs/DGCTPRMDB2.dat' scope=both;

2. Invoke ‘dgmgrl’ utility, connect to either primary using TNS

dgmgrl sys/password@vis

3. Create a configuration, define primary and standby databases

DGMGRL>create configuration DGCONFIG as primary database is 'VIS' connect identifier is 'VIS';

add database 'VISSTB' as connect identifier is 'VISSTB';

4. Update configuration with property StaticConnectIdentifier

DGMGRL>edit database 'VIS' set property StaticConnectIdentifier='VIS';

DGMGRL>edit database 'VISSTB' set property StaticConnectIdentifier='VISSTB';

5. Enable the configuration

DGMGRL>Enable configuration

6. Perform switchover from primary to standby and otherwise

DGMGRL>Switchover to ‘VISSTB’

Note: Before trying Switch over make instances using SPfile.

7. Switch to different protection modes: a) Change to maxavailability:

DGMGRL>edit database 'VIS' set property logxptmode=SYNC; DGMGRL>edit database 'VISSTB' set property logxptmode=SYNC; DGMGRL>edit configuration set protection mode as maxavailability;

b) Change to maxperformance:

DGMGRL>edit configuration set protection mode as maxperformance;

c) Change to maxprotection:

DGMGRL>edit database 'VIS' set property logxptmode=SYNC; DGMGRL> edit database 'VISSTB' set property logxptmode=SYNC; DGMGRL> edit configuration set protection mode as maxavailability; DGMGRL> edit configuration set protection mode as maxprotection;

(18)

Enabling Max Protection mode requires a restart of primary and standby database this is automatically taken care by Data Guard Broker. Standby redo log needs to be available and should be same size as online redologs.

Configuring Fast-Start Failover

1. FastStartFailoverThreshold: This parameter specifies the amount of time (in seconds) the observer attempts to reconnect to the primary database before starting the fast-start failover to the standby database. The default value is set to 30 seconds.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=6;

2. FastStartFailoverLagLimit: This property indicates the maximum amount of data loss that is permissible in order for an automatic failover to occur.

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit=45;

3. FastStartFailoverTarget for primary and standby

DGMGRL> EDIT DATABASE 'VIS' SET PROPERTY FastStartFailoverTarget='VISSTB'; DGMGRL> EDIT DATABASE 'VISSTB' SET PROPERTY FastStartFailoverTarget='VIS';

4. Enable Flashback on the primary and standby databases 5. Once the above settings are done, enable fast start failover

DGMGRL> Enable Fast_start Failover;

However, Fast Start Failover needs an observer to be up and running to perform automatic failover.

The Observer

Since observer needs to be up and running, create a script to run in background. Observer background script:

[oracle@ec2r12ebsdbs scripts]$ cat start_observer.sh

dgmgrl -logfile /home/oracle/scripts/logs/observer.log sys/syspwd@vis <<EOF start observer

EOF

[oracle@ec2r12ebsdbs scripts]$ nohup ./ start_observer.sh &

Verify redo is shipping

Check to see that the database is shipping redo, by connecting to the primary database and causing a log switch:

(19)

On each database server, this query will show which logs have been sent /received and applied:

SQL >select sequence#, applied, to_char(first_time ‘mm/dd/yy hh24:mi:ss’) first from v$archived_log order by first_time;

On the standby database server, monitor the database alert log for recovery progress.

Primary on Premises and Standby on Cloud

(20)

Appendix A – Data Guard init parameters

#Primary init.ora: LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/VIS/db/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=VIS' LOG_ARCHIVE_CONFIG='DG_CONFIG=(VIS,VISSTB)' DB_FILE_NAME_CONVERT='VIS','VIS' FAL_CLIENT='VIS' FAL_SERVER='VISSTB' log_archive_dest_2='SERVICE=VISSTB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=VISSTB' LOG_ARCHIVE_DEST_STATE_1='ENABLE' log_archive_dest_state_2='ENABLE' log_archive_format='%t_%s_%r.arc' LOG_FILE_NAME_CONVERT='VIS','VIS' remote_login_passwordfile='EXCLUSIVE' SERVICE_NAMES='VIS' STANDBY_FILE_MANAGEMENT='AUTO' db_unique_name=VIS global_names=TRUE DG_BROKER_START=TRUE DG_BROKER_CONFIG_FILE1='/data/oracle/VIS/db/tech_st/11.1.0/dbs/DGCVIS1.dat' DG_BROKER_CONFIG_FILE2='/data/oracle/VIS/db/tech_st/11.1.0/dbs/DGCVIS2.dat' #Standby init.ora: LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/VIS/db/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=VISSTB' LOG_ARCHIVE_CONFIG='DG_CONFIG=(VIS,VISSTB)' DB_FILE_NAME_CONVERT='VIS','VIS' FAL_CLIENT='VISSTB' FAL_SERVER='VIS' log_archive_dest_2='SERVICE=VIS VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=VIS' LOG_ARCHIVE_DEST_STATE_1='ENABLE' log_archive_dest_state_2='DEFER' log_archive_format='%t_%s_%r.arc' LOG_FILE_NAME_CONVERT='VIS','VIS' remote_login_passwordfile='EXCLUSIVE' SERVICE_NAMES='VISSTB' STANDBY_FILE_MANAGEMENT='AUTO' db_unique_name=VISSTB global_names=TRUE DG_BROKER_START=TRUE DG_BROKER_CONFIG_FILE1='/data/oracle/VIS/db/tech_st/11.1.0/dbs/DGCVIS1.dat' DG_BROKER_CONFIG_FILE2='/data/oracle/VIS/db/tech_st/11.1.0/dbs/DGCVIS2.dat'

(21)

Appendix B – Listener.ora configuration files

#Primary listener.ora:

VIS =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = EBSR12PRM.compute-1.amazonaws.com)(PORT = 1526)) ) ) (SID_LIST = (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VIS_DGMGRL) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VIS_DGB) ) ) #Standby listener.ora: VIS = (DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs)(PORT = 1526)) ) ) SID_LIST_VIS = (SID_LIST = (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VISSTB_DGMGRL) ) (SID_DESC = (ORACLE_HOME= /data/oracle/VIS/db/tech_st/11.1.0) (SID_NAME = VIS) (GLOBAL_DBNAME=VISSTB_DGB) ) )

(22)

Appendix C – Tnsnames.ora configuration files

#Primary tnsnames.ora:

VISSTB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526)) (CONNECT_DATA = (SID = VIS) ) ) VIS = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = EBSR12PRM.compute-1.amazonaws.com)(PORT = 1526)) (CONNECT_DATA = (SID = VIS) ) ) VIS_LOCAL = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=EBSR12PRM.compute-1.amazonaws.com)(PORT=1526)) ) VIS_REMOTE = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=EBSR12PRM.compute-1.amazonaws.com)(PORT=1526)) ) #Standby tnsnames.ora: VISSTB = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ec2r12ebsdbs.compute-1.amazonaws.com)(PORT = 1526)) ) (CONNECT_DATA = (SID = VIS) ) ) VIS = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=EBSR12PRM.compute-1.amazonaws.com)(PORT=1526)) (CONNECT_DATA=(SID=VIS)) )

(23)

Appendix D – Data Guard status verification

DGMGRL> show configuration

Configuration

Name: dgconfig Enabled: YES

Protection Mode: MaxAvailability Databases:

VIS - Primary database

VISSTB - Physical standby database - Fast-Start Failover target Fast-Start Failover: ENABLED

Current status for "dgconfig": SUCCESS

DGMGRL> SHOW FAST_START FAILOVER; Fast-Start Failover: ENABLED

Threshold: 6 seconds Target: VISSTB Observer: ec2r12ebsdbs

Lag Limit: 45 seconds (not in use) Shutdown Primary: TRUE

Auto-reinstate: TRUE Configurable Failover Conditions Health Conditions:

Corrupted Controlfile YES Corrupted Dictionary YES Inaccessible Logfile NO Stuck Archiver NO Datafile Offline YES Oracle Error Conditions:

(24)

Questions and Feedback

The Deloitte Solutions Network (SNET) strives to make our documents as useful as possible. We welcome your comments and questions regarding this or any other technical documents. Send your feedback or questions in an e-mail to nakula@deloitte.com or nmudaliar@deloitte.com.

(25)

About Deloitte

Deloitte refers to one or more of Deloitte Touche Tohmatsu Limited, a UK private company limited by guarantee, and its network of member firms, each of which is a legally separate and independent entity. Please see www.deloitte.com/about for a detailed description of the legal structure of Deloitte Touche Tohmatsu Limited and its member firms.

Deloitte provides audit, tax, consulting, and financial advisory services to public and private clients spanning multiple industries. With a globally connected network of member firms in more than 140 countries, Deloitte brings world-class capabilities and deep local expertise to help clients succeed wherever they operate. Deloitte's approximately 170,000 professionals are committed to becoming the standard of excellence.

Disclaimer

This publication contains general information only, and none of Deloitte Touche Tohmatsu Limited, Deloitte Global Services Limited, Deloitte Global Services Holdings Limited, the Deloitte Touche

Tohmatsu Verein, any of their member firms, or any of the foregoing’s affiliates (collectively the “Deloitte Network”) are, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or other professional advice or services. This publication is not a substitute for such professional advice or services, nor should it be used as a basis for any decision or action that may affect your finances or your business. Before making any decision or taking any action that may affect your finances or your business, you should consult a qualified professional adviser. No entity in the Deloitte Network shall be responsible for any loss whatsoever sustained by any person who relies on this publication. © 2010 Deloitte Global Services Limited

Installation Companion Oracle Data Guard on Amazon EC2 Configuration Guide