Oracle Database Software Downloads

Oracle Database Software Downloads

Shared via AddThis

Creating physical standby for RAC instacne using OCFS with rman

Hi,
Here is a general guideline (with example) for creating a physical standby database on different server/machine for an existing oracle database.
I assume that, you are aware of the general terminologies used for standby databases.
If not then please go through the otn link below
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/concepts.htm

Ok, we move ahead with the physical standby creation with our example now.
This example assumes that you have primary RAC database using OCFS and a similar standby server ready with same OS installed and ready with OCFS drives same as that of primary server. The directory structure on both the environment will be identical.

In our case,
primary database unique name-> master
standby database unique name-> stdby
primary server -> 007/008
standby server -> 009/010

1. You need to create the listeners on standby server with the help of Oracle net configuration assistant. (netca) Select the cluster configuration while creating listener. protocols selected is TCP. standard port is 1521.
2. Create the identical directory structure on 009 or 010 as that of 007. There will be ocfs drives which are ready to use, so you can just create the desired directories manually.
3. You need to edit the primary database configuration file (spfile/init) to make it ready for log transfer on standby server.

From any of the primary node (oo7/oo8) edit the spfile

alter database force logging;

alter system set db_unique_name='MASTER' scope=spfile;

alter system set log_archive_config='dg_config=(MASTER,STDBY)' scope=spfile;

alter system set standby_file_management=AUTO scope=spfile;

alter system set standby_archive_dest=' K:\oracle\stby' scope=spfile;

log_archive_dest_2='SERVICE=STDBY optional lgwr async noaffirm valid_for=(ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name=STDBY net_timeout=15' scope=both;

alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;

alter system set LOG_ARCHIVE_MAX_PROCESSES=20 scope=spfile;

alter system set FAL_SERVER=stdby_dg scope=spfile;

alter system set FAL_CLIENT=master_dg scope=spfile;

Note the following things while setting this up

Primary and standby database name will be same but there should be difference in db_unique_name.

The service name you specified in log_archive_dest_2 should be included in your primary tnsnames.ora to connect to standby database (whicl will be created by rman)

Fal_server will be will be standby dg configue name and fal_client will be primary dg config name

Our primary database's tnsnames.ora will have at least following entries

master_dg, stdby_dg, stdby

Also, you need to add the primary standby log files as

alter database add standby logfile thread 1 group 11 ('H:\Oracle\master\oradata\STBY01_011.LOG’) size 50M;

alter database add standby logfile thread 1 group 11 ('H:\Oracle\master\oradata\STBY01_012.LOG’) size 50M;

etc...etc...

Add the standby logfiles same as that of redo logs on primary. Its not a rule of thumb but I use to follow this.

Note: we need to add standby logs to standby server database as well which will be covered in step 8.

4. Copy the primary configuration file from primary (init file) to standby server and edit it for standby server parameters.

Change the different dump destinations and xxxx_name to reflect standby server directories.

Change the names for local_listener

log_archive_config='dg_config=(OPERA_ONE,OPERA_TWO)'

.log_archive_dest_2='SERVICE=MASTER optional lgwr async noaffirm valid_for (ONLINE_LOGFILES, PRIMARY_ROLE) db_unique_name=STDBY net_timeout=15'

OPERA1.remote_listener='LISTENERS_STDBY'

OPERA2.remote_listener='LISTENERS_STDBY'

standby_archive_dest='K:\oracle\stby'

Now your pfile for standby server is ready.

5. Copy paste the primary database password file to standby server under same location ($ORACLE_HOME/dbs)

If you are creating the standby on UNIX platform then no need to do the following step. Its only for windows machines.

Create the services same as that of primary server with same instance names.

oradim –NEW –SID master1 –STARTMODE manual –SYSPWD xxxxxxx (password same as primary database

oradim –NEW –SID master2 –STARTMODE manual –SYSPWD xxxxxxxx (password same as primary database)

6. Take RMAN backup for primary database as

run

{

crosscheck archivelog all;

backup database tag "stdby_backup" keep until time 'sysdate+5' logs;

backup archivelog all ;

backup current controlfile for standby tag "controlfiles_backup";

sql "alter database backup controlfile to trace";

}

Once the backup is finished then copy the backupsets to standby server under same directory.

7. starting up with standby database. Please note that following operation will be done from only one node of standby environment. In our case 009

connect to standby instance as sysdba
startup nomount pfile='the pfile path created for standby';

create the spfile in shared location so that both the instances will use it later as
create spfile='shared location' from pfile='pfile path';

shutdown immediate;

Edit the initstdby.ora on both nodes to point to shared spfile.

Connect again to instance, and nomount the db

startup nomount;


8. connect to target database as primary database and auxilliary instance as standby instance
from 009.

rman target sys@master1_prod auxiliary /

please note that the master1_prod is an explicit entry made in tnsnames.ora on standby server to connect to primary database.

Once the RMAN is connect to target and auxilliary database as desired, execute

duplicate target database for standby nofilenamecheck;

When the command finishes the standby database is ready to put in recovery managed mode. But, before that we need to add one more standby log file. Its a general process to follow to add one more standby log file compare to production database. So, if primary database is having 20 standby logfiles, then we need to add one more to its standby as

alter database add standby logfile thread 1 group 21 ('H:\Oracle\master\oradat\STBY01_021.LOG') size 50M;

9. Now we can put the standby database in recovery managed mode as

alter database recover managed standby database cancel using current logfile disconnect;

Check from the alert log file of stadby instance that the archives are now shipping to the standby server in stadnby archive destination and getting applied to standby instance.

If you execute 'select * from v$logfile;' at this moment, Oracle 10G will delete all the obsolete logfiles for you and left with only one additional standby logfile that you have added in step 8.

So, we need to add standby logfiles same as that of primary database (step 3)

10. The last step is to add the standby instances in cluster

srvctl add database -d STDBY -o D:\oracle\1020\database

srvctl add instance -d STDBY -i master1 -n 009

srvctl add instance -d STDBY -i master2 -n 010

Please check whether the applications are listing now in crs_stat -t on standby database.

It will show the other instance as down, which is quite obvious as we did not started that instance yet.

So, here we go. Your physical standby database is now created and ready to apply the archive logs.

Hope this example will help you to create your own standby. Enjoy !!

Sarang

Foundation for KBR

Hi,
This blog is created for the purpose of knowledge sharing for an Oracle DBA. Along with my Colleagues, I will try to share as much Oracle database knowledge as possible.
This will include technical process orientation for some of oracle activities, day to day problem faced and their resolution, new features from oracle.

Hope you will benefit from the information from this blog.