oracle apps dba blog

Dear Readers,
In this post I will be explaining How to enable standby database in Oracle Standard Edition.
Oracle’s Standby Database technology has been renamed as DataGuard in recent versions starting from 9i of Oracle. Oracle has added advanced automation of the standby database technology, making automatic Standby technology easier. Here We will discuss about How We can use the same technology without violating the oracle licenses in Oracle’s Standard Edition.it turns out that it is still possible to create a *manual* standby database on Oracle Standard Edition..

1. First you need to create the initial standby database. Here are the steps to do that:

A. Set the primary database in archivelog mode, if it is not already, and add at least LOG_ARCHIVE_DEST and LOG_ARCHIVE_START to your init.ora.

SQL>SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER SYSTEM ARCHIVE LOG START;
B. Next, create a hotbackup of the primary database. You can do this with RMAN, it is probably easiest to just do it manually so you know what is going on. For each tablespace do:

SQL> alter tablespace USERS begin backup;
SQL> !cp users01.dbf /u01/oracle/backup/
SQL> !cp users02.dbf /u01/oracle/backup/
SQL> !cp users03.dbf /u01/oracle/backup/
SQL> alter tablespace USERS end backup;
.

C. Now, create a standby controlfile from primary database:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/oracle/dbs/standby.ctl’;
D. copy everything over to the standby server including datafiles, standby controlfile & config files:

$ scp /u01/oracle/backup/*.dbf oracle@192.168.1.12:/u02/oracle/
E. In the standby machine, edit the standby init.ora file. Use this parameter to tell Oracle where files on the primary database will be located on the standby. For example if you had files in /ora/oracle on primary, and they are moved to /export/home/oracle on standby, this would work for you:

DB_FILE_NAME_CONVERT=’/u01/oracle’,'/u02/oracle’
Note that you can use MULTIPLE pairs of values here, if you have files in different locations. Alternatively, you can startup and mount the standby database then issue:

SQL> alter database rename file ‘/u01/oracle/users02.dbf’ to /u02/oracle/users02.dbf’ as an example.
You’re also likely to have a new location for your archived redo log files, and there the parameter LOG_FILE_NAME_CONVERT comes into play.

Neither of these parameters work for the ONLINE redolog files. Those you will have to rename yourself. If you do not do so, you will get an error at the time you try to SWITCHOVER your standby database. Such errors are easily remedied by running that command.

F. Now start the standby database and mount it.

SQL> startup nomount pfile=/u01/oracle/dbs/pfile/initorcl.standby
SQL> atler database mount standby database;
G. Finally recover the standby database using the AUTO option. Please note that you should build a simple shell script to startup sqlplus and run these commands. A name like manualstandbydb.sh would work well. You can then run this periodically, say every half hour, from cron to apply any new archived redolog files that have showed up via move_standby.sh below.

SQL> recover standby database;
AUTO
H. Now, , you’ll want to test your standby database. You do this by starting up in read-only mode.

SQL> alter database open read only;
I. Important Don’t forget to put it back in standby mode so that when your manualstandbydb.sh script runs from cron, it won’t return errors.

SQL> shutdown immediate;
SQL> startup nomount pfile=/u01/oracle/dbs/pfile/initorcl.standby
SQL> atler database mount standby database;

a. Enable cron scripts to copy archivelogs from primary and standby machine automatically.

Recall the script called manualstandbydb.sh -this will work well on the standby database too. This script applies new archived redologs that shipped from the production database.Run this scripts every one hour. The database should be mounted in standby mode (not read-only) otherwise this script will fail.

In the production server in the same way create a script called movestandbydb.sh, and run it every one hour . write the script so that it uses rsync to move archive log files from production to standby. A command like this would work:

$ rsync -e ssh -Pazv /u01/oracle/arch/ oracle@remote:/u01/oracle/arch/
Configure your ssh so that auto login works. Because this script (rsync) uses ssh autologin mechanism. Use ssh-keygen command. The .ssh in $HOME directory contains a public key, transfer this to the standby machine, and put in the “authorized_keys” file under .ssh directory in $HOME. . ssh will then login without a password. so this script works without a password.

b.To verify that the standby database is up to date?

Disable the manualstandbydb.sh script running from cron and then login with sqlplus and issue the command:

SQL> alter database open read only;
Verify whether the data is conistent and re-enable manualstandbydb.sh in the cron job.

Alert log errors in Manual Data Guard

You will get lot of messages like the database is trying to find the archive logs to apply and then applies it.(you can ignore them safely)

To Perform a switchover incase of failure , apply the remaining archive logs and start in read write mode.

In conclusion Athough You are having Oracle Standard Edition You can still use Oracle’s Standby log feature by little scripting and thus you can increase Your ROI.

Your Comments are welcome
– Vivek