Monday, November 14, 2011

Kill Two Birds with One Stone – Create Physical Standby Database in Oracle11g using Data Guard

Oracle Data Guard, included with Oracle Database Enterprise Edition, is really a cool product. Implement Oracle Data Guard and you will most likely kill two birds with one stone. Oracle Data Guard will help you achieve high availability and disaster recovery. One tool and two benefits. With Oracle Data Guard, you have a standby database that is in synch with the primary database. God forbids and you lose your primary database, you can very quickly switch to the standby database with minimal or no data loss. Your standby database is just a command away from being a primary database.

Most importantly, Oracle manages the Data Guard environment. You don't need to write any custom scripts. You don't need to setup any backup jobs. You don't need to write a job to copy archive logs. You don't need to manage archive logs and RMAN hot backups. Implementation is quite simple, and it works very well.

Based on your Recovery Point Objective(RPO) and Recovery Time Objective(RTO), you can configure Oracle Data Guard in three different modes:

  • Maximum Performance
  • Maximum Availability
  • Maximum Protection


Oracle Data Guard operates on a simple principle - ship redo stream to the standby database and apply it to standby database to keep it in synch with the primary database. A simple but very effective solution. If you have licensed Oracle Enterprise Edition and haven't implemented Data Guard, then you should seriously consider implementing one.

In this blog, I will illustrate the steps that you need to perform to implement physical standby database in maximum performance mode.

Environment

My Data Guard test environment is as follows:
  • Primary Database Server: HRPRDSVR
  • Oracle Version: 11gR2 11.2.0.2
  • Operating System: Windows
  • Standby Type: Physical
  • Data Guard Mode: Maximum Performance
  • Redo Apply: Real-time
  • Archive Log Mode: Enabled on primary
  • Primary DB Unique Name: HRPRD
  • Primary DB TNS Service Name: HRPRD
  • Data and Log Files Location: E:\Oradata\HRPRD
  • Standby Database Server: HRSTBYSVR
  • Standby DB Unique Name: HRPRDS
  • Standby DB TNS Service Name: HRPRDS
  • Data and Log Files Location: E:\Oradata\HRPRDS

Primary Database Configuration

Step 1 - Update init.ora parameters on the primary database

There are only few init.ora parameters that govern the behavior and implementation of Data Guard based physical standby databases. In order to make it easy for you to understand data guard related init.ora parameters, I have created a table that lists data guard related parameters and their corresponding values. This approach would help you understand these parameters and allow you to compare the values between primary and standby databases. I have also added my comments to further explain or clarify these parameters.

Please use this table as a guide while setting up init.ora parameters in your environment. The parameter values listed below are representative values, and should be applicable to most implementations. Update values as appropriate to reflect environment specific characteristics.

Parameter Name
Primary DB Parameters
Physical Standby DB 
Maximum Performance Mode with real-time redo apply
Comments
db_name
HRPRD
HRPRD
Db_name must be  same for the primary and  standby DBs
instance_name
HRPRD
HRPRD
Same Oracle Instance names used in this example, but can be different.
db_unique_name
HRPRD
HRPRDS
Db_unique_name must be different for the primary and physical standby
remote_login_
passwordfile
exclusive
exclusive
  
log_archive_config
'dg_config=
(HRPRD,HRPRDS)'
'dg_config=
(HRPRD,HRPRDS)'
  
log_archive_format
HRPRD%r%s.%t
HRPRD%r%s.%t
  
log_archive_trace
1
1
  
log_archive_dest_1
"location=
f:\orarch\HRPRD "
"location=
f:\orarch\HRPRD"
Replace with your Local Archive Destination
log_archive_dest_2
'service=
HRPRDS
async
lgwr db_unique_name=
HRPRDS
valid_for=
(primary_role,
online_logfile)'
'service=
HRPRD
async
lgwr
db_unique_name=
HRPRD
valid_for=
(primary_role,
online_logfile)'
For maximum performance mode, make sure that you specify async value as part of this parameter.
You also need to use lgwr value to enable real-time redo apply.
log_archive_dest_
state_1
enable
enable
  
log_archive_dest_
state_2
defer
enable
Start with deferred configuration on primary. We will enable it later.
standby_file_
management
auto
auto
Let Oracle add/resize data and log files on standby. Recommended.
fal_server
HRPRDS
HRPRD
In the event primary assumes the role of standby.
fal_client
HRPRD
HRPRDS
In the event primary assumes the role of standby.
log_file_name_convert
('HRPRDS',
'HRPRD')
('HRPRD',
'HRPRDS')
Let Oracle manage logfiles on the standby node through this parameter. Specify on both nodes to manage role reversal.
db_file_name_convert
'HRPRDS',
'HRPRD')
('HRPRD',
'HRPRDS')
Let Oracle manage datafiles on the standby node through this parameter.
Specify on both nodes to manage role reversal.


Step 2 - Create a password file on primary

If you are on Windows platform, then you should have a password file. Otherwise, create the password file on the primary server as follows. In an Oracle Data Guard environment, the primary and standby databases authenticates via password file.

Use the following command to create a password file on the primary database server.

orapwd file=PWDHRPRD.ora password=verysecretpassword (On Windows platform)


orapwd file=orapwHRPRD.ora password=verysecretpassword (On Unix platform)

The default name and location of the password file on Windows platform is %ORACLE_HOME%\DATABASE\PWD<ORACLE_SID>.ora and $ORACLE_HOME\dbs\orapw<ORACLE_SID> on Unix platform. You need to restart the instance after creating the password file.

A sidebar on the password file is in order as many Oracle DBAs are not very clear on the password file concepts. When you want to startup an Oracle database instance, you need an authentication mechanism outside the database. During startup, you can't use database authentication as Oracle database instance is not up at that time.

Oracle operating system group membership or password file provide authentication mechanisms outside the database. If you are a member of ORA_DBA group on Windows or dba group on Unix, then you can login "SYS AS SYSDBA" without any password and startup the instance.

But if you are not a member of these groups, then you need a password file to authenticate your "SYS AS SYSDBA" connections. In addition, if you want to startup and/or shutdown databases from remote servers on different domains, then you also need a password file to authenticate "SYS AS SYSDBA" connections. 


Databases participating in an Oracle Data Guard environment also need password file for authentication.

Step 3 - Enable FORCE LOGGING

Oracle Data Guard operates the standby database with the primary database redo stream. Primary database ships redo stream to the standby database. Redo stream shipment to the standby database can be synchronous or asynchronous depending on the data guard protection mode that you choose. Standby database's managed recovery process applies the redo stream to keep both the databases in synch. Any issues or interruption with the redo stream would put your standby database in jeopardy. For instance, NOLOGGING operations on primary database will definitely jeopardize your standby database. To prevent NOLOGGING operations, you must enable FORCE LOGGING on the primary database.

SQL> SHUTDOWN IMMEDIATE ;

SQL> STARTUP MOUNT ;

SQL> ALTER DATABASE FORCE LOGGING ;

SQL> ALTER DATABSE OPEN;

Run the following query just to make sure:

SELECT FORCE_LOGGING
FROM V$DATABASE ;

You can also query V$DATAFILE to see if there are any datafiles with NOLOGGED operations, as shown below.

SELECT FILE#, FIRST_NONLOGGED_SCN, FIRST_NONLOGGED_TIME
FROM V$DATAFILE ;

Standby Database Configuration

Step 1 - Create init.ora file on the standby server and start the instance

Create init.ora file on the secondary server. Refer to the table above for Oracle Data Guard related parameters that you need to configure on the standby server.

If you are on Windows platform, then you need to create the Windows service as follows:

C:>oradim -new -sid HRPRDS -startmode auto -pfile initHRPRDS.ora -syspwd verysecretpassword

Note that the sys password on standby database must be the same as primary database.

Now start the database instance on standby server and logout from your SQL*Plus session.

Step 2 - Create password file on the standby

If you are on Windos platform, then you don't need to create a password file. Otherwise, create the password file on the secondary server as follows. Note that the password on standby database must be the same as primary database.

orapwd file=PWDHRPRDS.ora password=verysecretpassword (On Windows platform)

Step 3 – Create Folder Structure on the standby server

Create the folders to hold administrative data, Oracle database files and Oracle archive log files. All folder names defined here are as per local organization naming standards. Follow your organizational naming standards.

Step 4 - Configure Listener.ora on the standby server

Update listener.ora as appropriate, and reload or restart listener.

LISTENER_DB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrstbysvr )(PORT = 1531))    
    )
  )

SID_LIST_LISTENER_DB =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = E:\oracle\ora11gR2)
      (SID_NAME = HRPRDS)
     (SDU=8192)
    )
 )

Step 5 – Update TNSNAMES.ORA on standby

Add the primary database's TNSNAMES service entry on the standby server to TNSNAMES.ORA file :

HRPRD =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRPRDSVR)(PORT = 1531))
     )
     (CONNECT_DATA =
        (ORACLE_SID = HRPRD)
     )
  )

Step 6 - Add TNSNAMES.ORA Service on primary server

Add the secondary database's TNSNAMES service entry to the primary server's TNSNAMES.ORA file:

HRPRDS =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRSTBYSVR)(PORT = 1531))
     )
    (CONNECT_DATA =
        (ORACLE_SID = HRPRD)
    )
)

HRPRD =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = HRPRDSVR)(PORT = 1531))
     )
     (CONNECT_DATA =
        (ORACLE_SID = HRPRD)
     )
  )

Step 6 – Test Connectivity on both servers

Use SQL*Plus to test connectivity from the primary database server to the standby server.

C:\> SQLPLUS sys/verysecretpassword@HRPRDS as sysdba

Test connectivity to the primary database server from the standby server as follows:

C:\> SQLPLUS sys/verysecretpassword@HRPRD as sysdba

Don't proceed further if you are having connectivity issues. Verify passwords on both instances and existence of password files on both servers.

Step 7 - Create Standby Database

Start the Oracle instance on the standby server in NOMOUNT mode.


C:\> set ORACLE_SID=HRPRD

C:\>rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 13 17:04:40 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect auxiliary sys@HPPRDS

connected to auxiliary database: HRPRD (not mounted)

RMAN> connect target sys@HRPRD

target database Password:
connected to target database: HRPRD (DBID=358909779)
using target database control file instead of recovery catalog

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover ;

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

media recovery complete, elapsed time: 00:07:50
Finished recover at 13-OCT-10
Finished Duplicate Db at 13-OCT-10

 Common Errors

If you haven't specified db_name = HRPRD and db_unique_name=HRPRDS on the standby, then you will get the following error:

RMAN-03002: failure of Duplicate Db command
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'HRPRD' in control file is not 'HRPRDS'

Note that the standby database must be in NOMOUNT state when you initiate the RMAN duplicate command.

RMAN> duplicate target database for standby dorecover ;
Starting Duplicate Db at 16-DEC-10
using channel ORA_AUX_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/16/2009 11:26:19
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE
command
 
RMAN> quit

Step 6 - Add Standby Redo Log Files on primary and standby databases

You must configure standby redo logs on standby database to activate real-time redo transport. Standby redo log files must be of the same size as primary database redo log files. You need to create at least the same number of standby redo log files as primary database.

ALTER DATABASE ADD STANDBY LOGFILE 
'E:\oradata\HRPRDS\StbyRedo1.log' SIZE 100M;

Also perform the same task on primary database in the event primary assumes the role of standby database.

Step 7 – Open Database and Activate Recovery on standby

SQL> startup ;

SQL> Alter database recover managed standby database using current logfile disconnect ;

Step 8 - Enable Physical Standby on Primary

Enable log_archive_dest_state_2 parameter and then perform a log switch on the primary database server:

SQL> ALTER SYSTEM SET log_archive_dest_state_2 = enable ;

SQL> ALTER SYSTEM SWITCH LOGFILE;
 
SQL> ALTER SYSTEM SWITCH LOGFILE;

And you should see the latest archive log on the standby server! Oracle Data Guard Physical Standby Database is running!!

If you don't see what you were expecting, then review alert logs and trace files. You may also query V$ARCHIVE_LOG table to degug this issue further.

Test It!

You can also run a simple test case to demonstrate that redo is being shipped in real-time and application data is available on standby database.

Run the following on primary database.

SQL> drop table test purge ;

Table dropped.

SQL> create table test
2 ( test date ) ;

 Table created.

SQL> insert into test values(sysdate) ;

1 row created.

SQL> /

1 row created.

SQL> commit ;

Commit complete.

SQL> alter session set nls_date_format='MM/DD/YYYY:HH24:MI:SS' ;

Session altered.

SQL> SELECT * FROM TEST ;

TEST
-------------------
10/17/2010:00:01:57
10/17/2010:00:01:58

SQL> SELECT * FROM TEST ;

TEST
-------------------
10/17/2010:00:01:57
10/17/2010:00:01:58
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)