Oracle Data Guard ~ Standard Edition 11g

As you know, Oracle Enterprise Edition is very expensive.  With 11g Oracle has introduced the concept of “Active Data Guard”  with the possibility to query our Physical Standby Database while it is constantly actualized to reflect the state of the Primary Database but with an extra charge.

We can have Oracle data guard with Standard Edition, however manual work (archive transfer and archive logs applying process) has to be done.

Lets see how we can achieve this target using Oracle 11g Standard Edition.

Environment

OS: Linux Red Hat 6

Primary DB Server: primhostserv

Primary DB: phostdb

Secondary DB Server: sechostserv

Install Oracle 11g SE on both primhostserv and sechostserv servers. Create a new database named primhostdb. This is our primary database for Oracle data guard.

It will be easier if you could install Oracle in the same paths in both servers.

Step 01

Enable archive in the Primary DB.

You can refer my earlier post Flashback Database ~ Oracle

Step 02

Create similar folder structure in Secondary Server similar to Primary Server.

e.g: /home/app/oracle/admin/PHOSTDB/adump

Step 03

Create password file, pfile from the Primary DB and copy to Secondary DB server.

> create pfile=’/home/oracle/pfile_PHOSTDB.ora’ from spfile;

Step 04

You will not need to create a backup of the Primary DB and apply to Secondary. You can do this using RMAN, however a simple file copy will be easier. Keep your primary DB shutdown during the copy.

Step 05

Create a Standby control file from Primary DB.

>alter database create standby controlfile as ‘/home/app/standby.ctl’;

Please note that, you will need to change the pfile used to create the Standby DB to use the newly created control file.

*.control_files= ‘/home/app/PHOSTDB/standby.ctl’;

Step 06

Now you will have all you files copied to Secondary DB Server. You can edit the created pfile for Secondary DB creation to tell Oracle where you files on Primary is located in Secondary.

DB_FILE_NAME_CONVERT = ‘/home/app/PHOSTDB’,’/home/newapp/PHOSTDB’

Step 07

Now you can start up the Standby database.

> startup nomount pfile=’/home/oracle/pfile_PHOSTDB.ora’

>alter database mount standby database;

Step 08

You can now recover you Standby database. Please note that as we are using Oracle SE here, archive logs will not be copied/moved to Secondary server. You can add a script to copy the newly created archive files every 30-60 min time.  Also, if you need to automate the recover, this too can be done using a script, you can find sample scripts at the end of this post.

> recover standby database

AUTO

Step 09

In order to verify that Secondary DB is in sync, you can open the Secondary in read-only mode.

>alter database open read only;

Please note that, you will need to put the Secondary DB into standby mode to recover.

> shutdown immediate

> startup nomount pfile=’/home/oracle/pfile_PHOSTDB.ora’

>alter database mount standby database;

Please find the scripts used to automatically sync the archivelogs from Primary to Secondary server. The script to apply the archivelogs into the standby DB is also provided.

rsync.sh – Add into Primary server. This script is used to sync the archive logs. I have the linux version here. You can use the link on how to enable secure rsync without entering the remote servers password.

recover.sql – Add into Secondary server. This the sql that will recover/apply the archive logs.

recover.sh – Add into Secondary server. Config the path to recover.sql conrrectly. This script is used to export the environmental variables and invoke recover.sql.

rsync.sh

cd /home/app/oracle/PHOSTDB/archive
rsync * --ignore-existing oracle@sechostserv:/home/app/oracle/phostdb/archive/

 

recover.sql

spool log.recovery replace
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
SELECT MAX(RECID) "Log id now" FROM V$LOG_HISTORY;
recover standby database
auto
spool off
exit;

 

recover.sh

ORACLE_BASE=/home/app/oracle;
export ORACLE_BASE
ORACLE_HOME=/home/app/oracle/product/11.2.0/db_1;
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin;
export PATH
ORACLE_SID=phostdb;
export ORACLE_SID
sqlplus / as sysdba @/home/app/oracle/phostdb/archive/recover.sql

Enjoy!!!

Advertisement

• 11g SE
• archivelog
• archivemode
• data guard
• oracle 11g
• Oracle 11g SE data guard
• standby database