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. 🙂
Like this:
Like Loading...
