Creating Standby Database with RMAN Active Database Duplication in 11gR2

Hello Friends,

In this article, I will write steps required for installation about Standby database creation with RMAN active database duplication. There are several methods for the installation of the Physical Standby database. These are using cold backup (database closed while the manual copying of data files), RMAN with the taking a backup of the primary database (main database) and restoring it in standby database (backup database) . I’ll explain below, the creation of standby database through RMAN active database duplication in 11g R2.

First let’s select our scenario. We are establishing a bank. Our bank  will operate in Turkey and Azerbaijan 🙂  General directorate of the Bank will locate in Istanbul and our disaster location will locate in Baku.  The bussines task is to create a backup of the extraordinary situation in Baku.  Our managers will be doing real-time reporting through Baku Standby database. Our databases are 11g R2 and will be working on Oracle Enterprise Linux 5 as the operating system . For easy understanding of the manifestations I have given server names as Istanbul (primary) and Baku (standby). My primary database name is TALIP which is located in Istanbul. And standby database name is TALIPDR which will be located in Baku. I will  remind you the servername  [server]  before each processing step .I want to make more of a reminder. If we run commands on linux, it will start with “$”,  on sqlplus is “SQL>” and, while on rman “RMAN>” as a start.

In short, our structure is;

Sunucu adı	IP	Veritabanı adı	Rolü
Istanbul	192.168.2.101	TALIP	Primary
Baku	192.168.2.102	TALIPDR	Standby

1- According to article https://taliphakanozturken.wordpress.com/2010/12/12/oracle-enterprise-linux-5-installation/  I have written about installation of Oracle Enterprise Linux. During the installation of the server as manifestations of Istanbul and Baku, I would set my ip addresses according to the above table.

2- According to article https://taliphakanozturken.wordpress.com/2010/12/21/oracle-database-11g-r2-preinstallation-requirements-on-linux/ we must set kernel parameters .

3- According to article https://taliphakanozturken.wordpress.com/2011/01/01/oracle-database-11g-r2-installation/ ,  We are doing the installation of oracle database 11g r2 in Istanbul (primary) server.  On  the  16th Step I give the name as “TALIP” to the “Global Database Name” and “SID” .

4-  We are installing only oracle software in Baku (standby) server . Don’t create a database. We will create a standby database with RMAN later.

5-  Our primary database must be in archive log mode in Istanbul (primary) server. If it is not in arhive log mode then you can do it with following.

First, let’s check whether the primary database is in as follows ;

 select log_mode from v$database;

If it is  not in arhive log mode then we will see log_mode as (NOARCHIVELOG) . Already we are a bank 🙂  and our database met be accurate in archive mode

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

6- [Istanbul]  Copy oracle password file to standby server (Baku) with ftp or scp command on Linux.

$scp orapwTALIP oracle@192.168.2.102:/oracle/ora11g/dbs/orapwTALIPDR

7- [Baku] Create “adump” folder in default location on standby server. Because the database will look for it on startup. If can not find it in this folder then returns an error at startup.

$mkdir $ORACLE_BASE/admin/$ORACLE_SID/adump

8- [Baku] Create pfile parameter file for standby database . ( under $ORACLE_HOME/dbs) And write only db_name in it.

 $vi $ORACLE_HOME/dbs/initTALIPDR.ora

Write db_name parameter in initTALIPDR.ora

db_name='TALIPDR'

9- [Baku ] On standby site open listener.ora file and edit it as below.

 $vi $ORACLE_HOME/network/admin/listener.ora

My ORACLE_HOME directory is /oracle/ora11g .

LISTENER =
   (DESCRIPTION_LIST =
        (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = baku)(PORT = 1521))
        )
 )

SID_LIST_LISTENER =
  (SID_LIST =
        (SID_DESC =
        (GLOBAL_DBNAME = TALIPDR)
        (SID_NAME = TALIPDR)
        (ORACLE_HOME = /oracle/ora11g)
        )
   )

10- [Baku] Stop/Start listener.

$lsnrctl stop
$lsnrctl start

11- [Istanbul ve Baku ikisinde de yapılacak] Edit tnsnames.ora file on both server as belows.

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

Add following lines to tnsnames.ora file

TALIP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = istanbul)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALIP)
    )
  )

TALIPDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = baku)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALIPDR)
    )
  )

12-[Istanbul] Need to create standby redo log file in the primary database as the number of online redo log files . There are 3 redo log files by default. And I create 3 standby redo files. These files will be moved to standby database (Baku)with RMAN.

SQL>alter database add standby logfile '/oracle/ora11g/data_TALIP/srl01.log' size 10M;

SQL>alter database add standby logfile '/oracle/ora11g/data_TALIP/srl02.log' size 10M;

SQL>alter database add standby logfile '/oracle/ora11g/data_TALIP/srl03.log' size 10M;

13-[Baku] Open TALIPDR database in nomount mode.

 SQL> startup nomount;

14-[Istanbul] let’s prepare our RMAN script to create standby database.

 $vi /oracle/ora11g/dataguard

Write following lines to script

 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 nofilenamecheck
         spfile
             parameter_value_convert 'TALIP','TALIPDR'
                  set db_file_name_convert='/TALIP/','/TALIPDR/'
                   set log_file_name_convert='/TALIP/','/TALIPDR/'
                   set db_unique_name='TALIPDR'
                   set control_files='/oracle/ora11g/data_TALIP/TALIPDR.ctl'
                   set log_archive_max_processes='5'
                   set fal_client='TALIPDR'
                   set fal_server='TALIP'
                   set standby_file_management='AUTO'
                   set log_archive_config='dg_config=(TALIP,TALIPDR)'
                   set log_archive_dest_1='service=TALIP LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TALIP ;
         sql channel prmy1 "alter system set log_archive_config=''dg_config=(TALIP,TALIPDR)''";
         sql channel prmy1 "alter system set log_archive_dest_2=''service=TALIPDR LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=TALIPDR''";
         sql channel prmy1 "alter system set log_archive_max_processes=5";
         sql channel prmy1 "alter system set fal_client=TALIP";
         sql channel prmy1 "alter system set fal_server=TALIPDR";
         sql channel prmy1 "alter system set standby_file_management=AUTO";
         sql channel prmy1 "alter system set log_archive_dest_state_1=enable";
         sql channel prmy1 "alter system archive log current";
         sql channel stby "alter database recover managed standby database using current logfile disconnect";
}

15-[Istanbul] And run the script in RMAN

RMAN target sys/oracle@talip auxiliary sys/oracle@talipdr

RMAN>@dataguard

RMAN will do the all:)

16-[Istanbul ve Baku] Finally, with the two following queries can learn the status of our server.

To see instances status;

SQL>select status from v$instance

To see switchover status and our database role;

SQL>select switchover_status,database_role from v$database;

[Baku] To see archive logs whether is applied ;

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

One of the important features of 11g, standby database can be opened in read-only mode when the archive is applying.

1- [Baku] Cancel the recovery process.

SQL>alter database recover managed standby database cancel;

2- [Baku] open the database read-only.

SQL>alter database open read only;

3- [Baku] Check database mode.

SQL>select open_mode from v$database;

4- [Baku] Start apply  again.

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

5- [Baku] Check database mode.

SQL>select open_mode from v$database;

You can see result as “READ ONLY WITH APPLY”

In the following articles I will write what to do in case of switchover and failover.

Talip Hakan Öztürk

Advertisement