Translate

Search This Blog

create standby database in Oracle Standard edition

Oracle standard edition can not have data guard i.e it can not have the recovery managed mode standby database but it can have manually refreshed standby database.

Steps for creating manually refreshed standby databases is same as that for creating recovery managed standby database

Standby database in Standard edition has two main differences over data guard which are :

1) Primary databases can not perform log shipping i.e it can not copy/send the archive log file from to standby database's archive location.  So you will need to write ftp/sftp or scp shell script or batch script which will copy primary's archive log files to standby node

2) Standby database will be refreshed with below command:

SQL> RECOVER STANDBY DATABASE UNTIL CANCEL;

Optional : You can configure  new parameter introduced from 10g LOG_ARCHIVE_TARGET at the priamry database to the time(in minutes) interval at which you want archive logs to be automatically generated by log switch.

What you do not need:

  • You do not need to set log_archive_dest_2 etc parameter in Primary nor in Standby database
  • You do not need to set DB_UNIQUE_NAME
  • You do not need to set parameter LOG_ARCHIVE_CONFIG

What do you need actually is : How to refresh standby database :

#script to refresh standby database-
sqlplus -s  / as sysdba <
set autorecovery on
alter database recover automatic standby database until cancel;
exit
EOF

You can invoke this script periodically after copying of archive logs from primary databases at time interval set by LOG_ARCHIVE_TARGET or at any interval.
 
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;

Other steps are common:

That is you need to physically copy the database files (except redo logfiles) from primary to standby node and you also need to generate standby control file at primary database and copy to standby database node. You can perform this step via RMAN also.

Alternate way of creating standby database:

is to restore the backup of primary database on standby node and mount it and then refresh using below command. In this case you do not need to create standby control file.

Alter database recover automatic database using backup controlfile until cancel;

Additional Stuff:

If you want to failover to this new standby which was created with alternate way, for disaster recovery)
  ++ Apply maximum archive log from primary to standby. ++ Optionally we can copy the Online Redo Logs (ORL) from primary, provided they are accessible and recover so as to increase the point of recoverability     SQL>recover database using backup controlfile until cancel 
  ++ alter database open resetlogs.
NB:
 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.