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) and the participants were from all around the world. Then I went to Malta to provide consultancy service. And 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 and 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 and 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 and 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 and 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 command 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
Then 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 and 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;
And 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
