Steps to configure Manual Data Guard – Oracle 11g Manual Data Guard Part-I

Published by Jignesh Jethwa
Oracle 11g Logo

This article is about step by step approach to configure Manual Physical Standby Data Guard in Oracle 11g. In my case, Ingredients to simulate Manual Physical Standby Manual data guard environment are as below:

2 VM’s, PR and DR with enough CPU and RAM in order to run oracle database.

Primary server configuration:

  • CentOS 6.5
  • Server name: PR
  • IP: 192.168.239.130
  • Oracle 11g software plus oracle instance.
  • Oracle SID/Global_name: RTS
  • Oracle db_unique_name: RTS

Standby server configuration:

  • CentOS 6.5
  • Server name: DR
  • IP: 192.168.239.131
  • Oracle 11g software only.
  • Oracle SID/Global name: RTS
  • Oracle db_unique_name: RTSDR

Note:

Oracle version on Primary and Standby should be identical. i.e. In my case it’s 11.2.0.1.0
db_unique_name on standby and primary database should be different.
Primary and Standby server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

PR /etc/hosts file:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.239.130    PR.localdomain    PR
192.168.239.131    DR

DR /etc/hosts file:

[root@dr ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.239.131    DR.localdomain    DR
192.168.239.130    PR

Lets start the configuration:

Step -I

Verify your database is in Archive Log Mode or NO archive log mode, if not than my one of the post will help you to configure your Oracle 11g database is in archive log mode. Primary database need to be in Archive log mode.

Step -II

Enable Force Logging on Primary database(Hostname:PR)

Following query will help you find out your database is in force logging or not:

SQL> column force_logging format a15
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO

Following query will help you to enable force logging:

SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
YES

Step – III

Backup primary database with the help of RMAN:

 [oracle@PR ~]$ rman target /
 connected to target database: RTS (DBID=1470135364)
 RMAN> run {
 allocate channel c1 device type disk format '/home/oracle/prdg/bkup/%U';
 backup as compressed backupset full database plus archivelog;
 release channel c1;
 }

Copy RMAN backup to standby database(Hostname: DR)

 [oracle@PR ~]$ cd /home/oracle/prdg/bkup
 [oracle@PR bkup]$ ll
 total 266332
 -rw-r-----. 1 oracle oinstall 12165120 Apr 18 03:55 09r3ah13_1_1
 -rw-r-----. 1 oracle oinstall 259457024 Apr 18 03:56 0ar3ah17_1_1
 -rw-r-----. 1 oracle oinstall 1097728 Apr 18 03:56 0br3ah3s_1_1
 -rw-r-----. 1 oracle oinstall 4096 Apr 18 03:56 0cr3ah3v_1_1
 [oracle@PR bkup]$ scp * oracle@DR:/home/oracle/drdg/bkup/

Step – IV

Create Standby Controlfile on Primary database(Hostname:PR)

SQL> alter database create standby controlfile as '/home/oracle/prdg/stndby_control.ctl';
Database altered.

Copy Standby Controlfile to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control02.ctl

Step – V

Create parameter file(i.e. initRTS.ora) on Primary database(Hostname:PR). If you are not using spfile than skip this step.

SQL> create pfile='/home/oracle/prdg/initRTS.ora' from spfile;

Copy parameter file(i.e. initRTS.ora) to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/initRTS.ora oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VI

Copy password file to standby database(Hostname: DR). (Note: create password file if not created for your database.)

[oracle@PR ~]$ cd $ORACLE_HOME/dbs
[oracle@PR dbs]$ ll orapwRTS
-rw-r-----. 1 oracle oinstall 2048 Apr 18 03:23 orapwRTS
[oracle@PR dbs]$ scp orapwRTS oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VII

Create Required Directory Structure on standby database server(Hostname: DR). In my case I am going to replicate same directory structure as mentioned in parameter file.

=== initRTS.ora ===
 *.audit_file_dest='/u01/app/oracle/admin/RTS/adump'
 *.control_files='/u01/app/oracle/oradata/RTS/control01.ctl','/u01/app/oracle/oradata/RTS/control02.ctl'
 *.diagnostic_dest='/u01/app/oracle'
 *.log_archive_dest_1='LOCATION=/home/oracle/archdir'
 === End ===

Directory structure on standby database server.(Hostname: DR)

 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/oradata/RTS/
 [oracle@DR ~]$ mkdir -p /u01/app/oracle
 [oracle@DR ~]$ mkdir -p /home/oracle/archdir

Step – VIII

Configure tnsname.ora and listener.ora and Start listener on standby database(Hostname: DR)

 === tnsnames.ora ===
 LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DR.localdomain)(PORT = 1521))
 )
 )
 ADR_BASE_LISTENER = /u01/app/oracle
 === End ===
 ===listener.ora ===
 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 )
 )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DR)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTSDR)
 )
 )
 === End ===

Step – IX

Add following parameters to initRTS.ora file on Standby database. (Hostname: DR)

*.db_unique_name='RTSDR'

In case of your datafile and logfile location on standby is different from primary than add following parameter to initRTS.ora file.

*.db_file_name_convert=('location on primary','location on Stanndby')
*.log_file_name_convert=('location on primary','location on Stanndby')

Step – X

Connect Standby Database

 $ export ORACLE_SID=RTS
 sqlplus / as sysdba
 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 960372736 bytes
 Fixed Size 2219152 bytes
 Variable Size 562037616 bytes
 Database Buffers 390070272 bytes
 Redo Buffers 6045696 bytes
 SQL>

Step – XI

Create spfile from existing parameter file (i.e.initRTS.ora) and bounce the database:

 SQL> create spfile from pfile;
 SQL> Shut immediate;
 SQL> startup mount;

Step – XI

Restore and recover database with the help of RMAN backed up files from primary database. (Step – III)

 $ rman target /

On standby database, Practically there is no backup taken from RMAN, but if you issue following command to list out existing backups, than RMAN will show you full database backup information of primary database.(Step – III) Because RMAN repository is stored in control file and we have copy pasted standby control file from primary to standby database.

 RMAN> list backup of database summary;
 using target database control file instead of recovery catalog
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 10 B F A DISK 18-APR-16 1 1 YES TAG20160418T035519

Just cross check and delete invalid entry from control file with the help of following RMAN commands:

 RMAN> crosscheck backup;
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=25 device type=DISK
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/01r3vadq_1_1 RECID=1 STAMP=910141882
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/02r3vadu_1_1 RECID=2 STAMP=910141886
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/03r3vaga_1_1 RECID=3 STAMP=910141963
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/04r3vagc_1_1 RECID=4 STAMP=910141964
 Crosschecked 4 objects
RMAN> delete noprompt force expired backup;

Step – XII

Catalog all RMAN backup file entry to control file with following:

 RMAN> catalog start with '/home/oracle/drdg/bkup/';
 using target database control file instead of recovery catalog
 searching for all files that match the pattern /home/oracle/drdg/bkup/
 List of Files Unknown to the Database
 =====================================
 File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1
 File Name: /home/oracle/drdg/bkup/09r3ah13_1_1
 File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1
 File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1
 Do you really want to catalog the above files (enter YES or NO)? yes
 cataloging files...
 cataloging done
 List of Cataloged Files
 =======================
 File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1
 File Name: /home/oracle/drdg/bkup/09r3ah13_1_1
 File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1
 File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1

Step – XIII

Restore and recover database with the help of following RMAN commands:

 run
 {
 restore database;
 recover database;
 }

Above recovery session will be end up with RMAN error message like mentioned below. Recovery successful.

 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 04/26/2016 02:18:57
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 954414

Step – XIV

Shutdown database and Startup Standby Database(Mount mode):

 sqlplus / as sysdba
 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 960372736 bytes
 Fixed Size 2219152 bytes
 Variable Size 562037616 bytes
 Database Buffers 390070272 bytes
 Redo Buffers 6045696 bytes
SQL> alter database mount standby database;
 Database altered.

Step – XV

Cheers!! Our Manual Data Guard configuration have configured successfully.

Verify Manual Data Guard configuration on Standby database with the help of following SQL query:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

Issue same above SQL query on primary database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

Step – XVI

Verify your Manual Data Guard configuration is working properly or not with the help of generating and applying archive logs on standby database:

Step – A

Generate multiple archive logs on primary database, issue following SQL query multiple times:

 SQL> alter system switch logfile;
 System altered.
 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 7

Step – B

Transfer above generated archive logs to standby database server @ path: /home/oracle/archdir/ (As per parameter: *.log_archive_dest_1 set for standby database)

scp * oracle@DR:/home/oracle/archdir/

//Above SCP linux command will transfer all the archive logs from mentioned directory. It will overwrite old files. (Note: I used this shortcut because I am demonstrating you on my test environment, In case of production, please copy only newly generated files)

Step – C

Issue following SQL to query for maximum of sequence number of applied archived log on standby database.(Hostname: DR)

 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 3

Apply copy pasted archive logs on standby database as below,

 SQL> recover standby database until cancel;
 ORA-00279: change 1008623 generated at 04/18/2016 03:56:47 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_18_908844551.dbf
 ORA-00280: change 1008623 for thread 1 is in sequence #18
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 AUTO
 ORA-00279: change 957138 generated at 04/26/2016 02:20:59 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_5_910140016.dbf
 ORA-00280: change 957138 for thread 1 is in sequence #5
 ORA-00278: log file '/home/oracle/archdir/1_4_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957141 generated at 04/26/2016 02:21:00 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_6_910140016.dbf
 ORA-00280: change 957141 for thread 1 is in sequence #6
 ORA-00278: log file '/home/oracle/archdir/1_5_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957144 generated at 04/26/2016 02:21:01 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_7_910140016.dbf
 ORA-00280: change 957144 for thread 1 is in sequence #7
 ORA-00278: log file '/home/oracle/archdir/1_6_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957147 generated at 04/26/2016 02:21:01 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_8_910140016.dbf
 ORA-00280: change 957147 for thread 1 is in sequence #8
 ORA-00278: log file '/home/oracle/archdir/1_7_910140016.dbf' no longer needed for this recovery
 ORA-00308: cannot open archived log '/home/oracle/archdir/1_8_910140016.dbf'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

All transfered archive logs have applied on standby database successfully, now check maximum of sequence number of applied archived log.

 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 7

Maximum of sequence number of applied archived log on primary and standby is same, our configuration successful. Cheers!!!

Your comment and suggestion are highly appreciated.

Stay Tuned with my next article on “Switch over and switch back – Oracle 11g Manual Data Guard Part-II“.

Thank you. 🙂

6 thoughts on “Steps to configure Manual Data Guard – Oracle 11g Manual Data Guard Part-I”

Leave a Reply

%d bloggers like this: