Alternative for standby/dataguard - Manual

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2
Applies to any platform.

Goal:
Alternative for standby/dataguard in standard edition
Standby/Dataguard is a high availability feature available only with Enterprise edition.

Here we discuss how to create a duplicate(clone) database of production DB in standard edition
which supports the following features of (physical)standby in Enterprise Edition.

++ Keep the clone database in synchronized state with primary by applying
change records(archivelogs) from primary.
++ Open the database in read only mode for reporting purpose.

Solution:
This alternative standby database doesn’t support advance dataguard features like switch over,automatic log shipping and apply services(Managed recovery) etc.

Note:
1. primary database archivelogs have to be shipped manually to the log_archive_dest of standby instance.
The parameter standby_archive_Dest has no meaning this standby setup.

2. The normal procedure of setting up a clone instance with same DB_NAME as that of production is applicable for this setup also if you are planning to have primary and standby in the same box.
Eg: usage of parameter lock_name_space.

Here are the steps to create the dummy standby database
Take a backup of primary database
++ Any supported method can be used to take this backup.
Eg: RMAN/MANUAL/HOT/COLD..

Take a backup of controlfile from primary.
++ It can be a cold copy of controlfile after a clean shutdown of the DB(Recommended)
++ If you can't shutdown the DB make a binary backup of the controlfile using

SQL>alter database backup controlfile to '';

++ Also you can use a controlfile taken using rman backup keeping following point in mind

Note: The controlfile used for standby should be having a checkpoint_change# higher than that
of restored datafiles.For this you need to restore datafiles from a backup taken prior to
the time stamp of the controlfile which we are planning to use for standby.

Create a new instance for standby and keep in nomount stage
++ The following parameters need to be same in both primary and standby.
DB_NAME,db_block_size,compatible etc.
The standby instance can have a different instance_name but same DB_NAME as the primary instance.
If you want to mount two database with same DB_NAME in the same node you need to use lock_name_space if they are under same ORACLE_HOME(This is applicable even if you use different instance_name)

++ The control_files parameter of standby should point to the backup controlfile that we took in step 2.

SQL>startup nomount(pfile =)

Mount the standby DB using controlfile taken from primary.

++ SQL>alter database mount ( Please keep in mind that we are not creating an actual standby database.
So "STANDBY" keyword is not needed in alter database commands)

Restore the datafiles to the standby location.(Using rman or manually)
++ If you have a different file system structure you can use "set newname" command in rman to restore to a
different location.If you are restoring files manually,restore the files to new location and follow the next step
to rename files in the controlfile.

If the standby site is having a different file system structure rename the datafiles using
SQL>alter database rename file to

Start the recovery.
SQL>recover database using backup controlfile until cancel

Apply all the available archivelogs
(You should apply atleast few archivelogs to make the checkpoint_change# of controlfile and datafiles are
same.This is to make the datafiles and controlfile consistent)

If you want to open the DB for reporting purpose read only mode.

SQL> alter database open read only.

Add tempfiles to temp tablespace.

SQL> alter tablespace temp add tempfile '' size ..
This is because recovery using backup controlfile command automatically removed tempfile records from controlfile.
Now this DB is ready for users to use for reporting.

Now as primary keeps generating archivelogs you need to keep the standby in SYNC with primary

++ Copy the new archivelogs to log_archive_dest of standby
++ Shutdown the standby and startup mount.

++ Apply the available archivelogs using 'recover database using backup controlfile until cancel' command.
++ Once all the archivelogs are applied you can open the standby in read only mode.

If you want to failover to this new standby for disaster recovery)
++ Apply maximum archivelog from primary to standby(Including online redologs if possible)

SQL>recover database ung backup controlfile until cancel
++ alter database open resetlogs.

NOTE:
1) If you create a new tablespace in primary you may get the following errors while applying the corresponding logs in
reporting server.
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 5: '/home/jobin/tes1.dbf'

At this stage you need to run a command like this to create an empty datafile in standby.

a) SQL>select name from v$datafile where name like '%UNNAMED%';
b) SQL> alter database create datafile '/home/oracle/product/ora101/dbs/UNNAMED00005'
as '/oradata/dummy/test01.dbf';
Where /oradata/dummy is location for datafiles in standby.
Now you can restart the recovery process.

2) To find out the last archived log applied on this standby you can use this query on mount stage.
SQL>select max(fhrba_Seq) from x$kcvfh;

mlnote=333749.1 (24-JUN-2008)