Oracle 11g R2 - Configuring Data Guard / Part I

High Availability - Creating Standby Server

After several busy weeks, I didn’t want to miss this chance. It was definetely very overloaded time. I spent one week for a LVC (Live Virtual Class) the participants were from all around the world. , I went to Malta to provide consultancy service. now I’m in Bucharest delivering a Siebel 8.1 Core Consultant Course. At last I start to write a new article. I decided to give some examples from high availability point of view. Actually, I was involved in a project this was the main idea.  So here is the demonstration of Data Guard configuration on 11g R2 database.

In order to demonstrate this infrastructure, I had configured 2 VMware images based on Linux operating systems. I configured all network configurations hosts files on these 2 Linux computers. So I can easily access from both computers to each other. All the prerequisites are done for Oracle 11g R2 database which includes additional rpm packages,  creating OS groups & users, environment variables etc..

In addition to these, I’ve installed Oracle Software on both of the servers create a database on one of the instance which will be PRIMARY server. 

So at this point, there are 2 servers. A database is already created on one of them. There is only Oracle Software installed on the other. Host names of the computers are my favorite cities in Italy. :) Primary server is Rome Standby server will be Venice.

I created a pfile on the standby server.  $ORACLE_HOME/dbs/initSBY1.ora

The contents of the pfile is below, only one parameter:

DB_NAME=SBY1

The next step is configuring the network components of the databases. The content of the listener.ora files are below:

LISTENER.ORA     --STANDBY SERVER so on Venice

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = venice.europe)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

  

  (SID_DESC = 

    (GLOBAL_DBNAME = sby1.europe)

    (SID_NAME = sby1)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1))

  )

-

TNSNAMES.ORA        --PRIMARY & STANDBY Servers  so on Rome & Venice

SBY1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = venice.europe)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = sby1.europe)

    )

  )

PRIMARY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rome.europe)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary.europe)

    )

  )

There is one more step before generating the standby database. This should be done before the DUPLICATE comm from RMAN console. Otherwise you may experience an Oracle Error 00845: Memory_target is not supported on the standby server. The reason of this is the temp device size is less than Memory_target size. So I just increased the device size by executing these commands with Root user account.

root@venice ~]# mount -t tmpfs shmfs -o size=3g /dev/shm

[root@venice ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                       45G   19G   24G  45% /

/dev/sda1              99M   12M   82M  13% /boot

tmpfs                 3.0G     0  3.0G   0% /dev/shm

/dev/hdc              2.8G  2.8G     0 100% /media/Enterprise Linux dvd 20090127

shmfs                 3.0G     0  3.0G   0% /dev/shm

, I started the instance on the standby server (Venice):

EXPORT ORACLE_SID=sby1

sqlplus / as sysdba

startup nomount pfile= /u01/app/oracle/product/11.2.0/db_1/dbs/initsby1.ora

Standby server is ready for the Data Guard configuration. Now there are several steps on the Primary server. The tasks below will be done on the server named “Rome”.

sqlplus / as sysdba

alter system set log_archive_dest_1 = 'SERVICE=sby1 ASYNC VALID_FOR = (ONLINE_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=sby1';

alter system set log_archive_config = 'dg_config=(primary,sby1)';

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl01.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl02.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl03.log' size 50M;

Alter database add standby logfile '/u01/app/oracle/oradata/primary/srl04.log' size 50M;

The recommended practice is the number of standby log files should be greate than the number of redo log files. That’s why I created one extra standby log on the primary. It’s very obvious that Primary database should be running in Archivelog mode. So:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/flash_recovery_area

Oldest online log sequence     38

Next log sequence to archive   40

Current log sequence           40

Before going one step ahead, check the archive log destinations. There should be 2 destinations, one on the localhost one on the remote server which is standby server. If not, take the corrective action.

In order to establish a succesful connection from Primary to Standy server, you should create a password file on the standby server. The steps are:

STANDBY SERVER

[root@venice ~]# rpm -q vsftpd

vsftpd-2.0.5-12.el5

[root@venice ~]# service vsftpd start

Starting vsftpd for vsftpd:                                [  OK  ]

[root@venice ~]#

PRIMARY SERVER

cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapwprimary.ora

ftp venice

user: oracle

pwd: oracle

put orapwprimary.ora

STANDBY SERVER

mv /home/oracle orapwprimary.ora /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsby1.ora

PRIMARY SERVER

rman target sys/xxx@primary auxiliary sys/yyy@sby1

RMAN>@dg.txt

DG.TXT Content is:

run 

{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile parameter_value_convert 'primary','sby1'

set db_unique_name='sby1'

set db_file_name_convert='/primary/','/sby1/'

set log_file_name_convert='/primary/','/sby1/'

set control_files='/u01/app/oracle/oradata/sby1.ctl'

set log_archive_max_processes='5'

set fal_client='sby1'

set fal_server='primary'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(primary,sby1)'

set log_archive_dest_1='service=primary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primary';

}

This will take sometime. After it finishes the process, the next step is:

PRIMARY SERVER

SQLPLUS / AS  SYSDBA

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME

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

        31 27-OCT-10 27-OCT-10

        31 27-OCT-10 27-OCT-10

        32 27-OCT-10 28-OCT-10

        32 27-OCT-10 28-OCT-10

        33 28-OCT-10 28-OCT-10

        33 28-OCT-10 28-OCT-10

        34 28-OCT-10 28-OCT-10

        34 28-OCT-10 28-OCT-10

alter system switch logfile;

the last step is starting the Apply Service on the Standby server:

STANDBY SERVER

sqlplus sys/yyy@sby1 as dba

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME

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

        31 27-OCT-10 27-OCT-10

        32 27-OCT-10 28-OCT-10

        33 28-OCT-10 28-OCT-10

        34 28-OCT-10 28-OCT-10

/%

After several log swithces on the primary server --> Query the archived_log on the standby server

%/

SQL> select sequence#, first_time, next_time, applied from v$archived_log

  2  order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

        31 27-OCT-10 27-OCT-10 NO

        32 27-OCT-10 28-OCT-10 NO

        33 28-OCT-10 28-OCT-10 NO

        34 28-OCT-10 28-OCT-10 NO

        35 28-OCT-10 28-OCT-10 NO

        36 28-OCT-10 28-OCT-10 NO

        37 28-OCT-10 28-OCT-10 NO

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

Database altered.

SQL> select sequence#, first_time, next_time, applied from v$archived_log

  2  order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

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

        31 27-OCT-10 27-OCT-10 YES

        32 27-OCT-10 28-OCT-10 YES

        33 28-OCT-10 28-OCT-10 YES

        34 28-OCT-10 28-OCT-10 YES

        35 28-OCT-10 28-OCT-10 YES

        36 28-OCT-10 28-OCT-10 YES

        37 28-OCT-10 28-OCT-10 YES

        38 28-OCT-10 28-OCT-10 YES

        39 28-OCT-10 28-OCT-10 IN-MEMORY