Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, KK .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: January 27, 2002 - 11:52 am UTC

Version:

Viewed 1000+ times

You Asked

I'm now responsible for building a
new backup and recovery procedure for
my company billing database. I would
like to set up a standby database.
The production DB and the standby DB are
both Oracle V7.3.4.

Can you suggest any other alternatives
to me?



and Tom said...


A standby database is a great FAILOVER solution. It is not a backup/recovery solution however. The standby database is used when you want to failover to another machine in seconds/minutes in the event the production machine is blown up or something. Backups/recovery are used when you want to be able to recover all or part of a database and can afford to have the affected data be unavailable during the recovery.

I've attached a note describing how to setup standby databases for 7.3.


Article-ID: <Note:33236.1>
Alias: OLS:9750280.61
Circulation: PUBLISHED (EXTERNAL)
Folder: server.Rdbms.Recovery
Topic: ** Standby Databases
Title: HOT STANDBY DB INFO IN 7.3
Document-Type: FAQ
Impact: MEDIUM
Skill-Level: NOVICE
Server-Version: 07.03 to 07.03
Updated-Date: 11-DEC-1999 07:08:55
References:
Shared-Refs:
Authors: COMET
Attachments: NONE
Content-Type: TEXT/PLAIN
Keywords: RDBMS; RECOVERY; STANDBY;
Products: 5;
Platforms: GENERIC;

Document ID: 9750280.61
Title: Hot Standby DB info in 7.3
Creation Date: 21-DEC-95
Last Revision Date: 21-DEC-95
Revision Number: 0
Product: RDBMS
Product Version: 7.3
Platform: N/A
Information Type: Advisory
Impact: Medium
Abstract: The objective of the Standby Database feature is to support
the capability of maintaining a duplicate,, or standby,
database of a primary,or on-line, database for recovering
from disasters at the production site.
Keywords: RDBMS;STANDBY;RECOVERY

-------------------------------------------------------------------------------


Standby Database Feature of ORACLE7 Version 7.3

Introduction
The objective of the Standby Database feature is to support the capability of
maintaining a duplicate, or standby, database of a primary, or on-line,
database for recovering from disasters at the production site. Standby
Database consists of an external interface and checks within the Oracle7
kernel code to detect and correct data corruption situations. This paper
describes the design concepts and external interfaces of the Standby Database
feature.

The Standby Database feature is intended to address the general requirement
areas of data availability and product usability. Data availability is
increased by providing production application accessibility to the data
through a switch over to the standby database sooner than if the usual
recovery procedures had been applied to the primary database. Usability is
increased in that through this feature Oracle Corporation provides a
methodology to endorse and support disaster recovery.

Design Concepts
The Standby Database feature allows a customer to maintain a duplicate copy of
a database at a remote site by using the archived log information. The
database being copied from is referred to as the primary database, and the
database being copied to, or the duplicate copy, is referred to as the standby
database.

The primary database is any ORACLE7 release 7.3, or higher, database
functioning as accustomed, without restriction. The standby database is
duplicated on physical hardware (disk and cpu) separate from the
primarydatabase and is not used for any application other than as a disaster
recovery system. The standby database is kept constantly in recovery mode.

As the primary database archives its redo logs, these log files can be
transferred to the remote site and applied to the standby database. The
standby database is always behind in time and transactional history from the
primary database.

In the event of a catastrophic failure of the primary database,the standby
database can be taken out of recovery mode and activated for on-line use. From
the point of activating, or opening the standby database, the standby database
is now the primary database from the points of view of the customer's business
and Oracle software.

The standby database is not intended for any other use other than as a
disaster recovery database for the primary database. The standby database can
not be queried or opened for any other purpose other than to activate in the
event of catastrophic error of the primary database. Once the standby database
is activated it can not be returned to standby recovery mode without
recreating it again as a standby database, and the primary database will not
have any transactional history beyond the point in time when the disaster
occurred.

External Interface
The following activities must be done by the database administrator to use
this feature; creation of the standby database, maintenance of the standby
database, and activation of the standby database. The primary goal of these
administrative procedures is to keep the control file, log files and data
files consistent with eachother as well as consistent between the primary and
standby databases.

Creating the Standby Database
To create the standby database,the database administrator must take a back-up
copy of the data files of the primary database, create a back-up control file
for the standby database, and archive the on-line redo logs of the primary
database. The backed-up data files, control file and archived log files can
then be transferred to a remote site where the standby database will be
maintained.

To create a standby database, perform the following procedures:
1. Back-up the data files from your primary database. The back-ups can be done
either on-line or off-line.
2. Create the control file for your standby database by issuing the ALTER
DATABASE CREATE STANDBY CONTROLFILE AS 'filename' command. This command
creates a modified back-up copy of the primary database's control file.
3. Archive the current on-line redo logs of the primary database by issuing
the ALTER SYSTEM ARCHIVE LOG CURRENT command. This command forces a log switch
on all threads and archives all of the redo at an scn after the data files
have been backed-up.
4. Transfer the standby database control file, archived log files and
backed-up data files to the remote (standby) site.

Maintaining the Standby Database
To maintain the standby database, the stand by database mus be mounted and put
in to standby recovery mode. As redo logs are archived at the primary site,
these logs must be transferred to the remote site and applied to the standby
database.

To maintain your standby database in recovery mode, perform the following
procedures:
1. Mount (but do not open) the standby database by issuing the ALTER DATABASE
MOUNT STANDBY DATABASE [EXCLUSIVE/PARALLEL] command.
2. Transfer the archived redo logs from the primary database to the remote
(standby) site.
3. Put the standby database in to recovery mode by issuing the RECOVER [FROM
'location'] STANDBY DATABASE command.

As the archived logs are generated at the primary site, you must continually
transfer and apply them to the standby database. Also, you can only apply logs
to the standby database that have been archived on the primary database.
Refreshing the Standby Database Control File

Certain administrative operations on the primary database make modifications
to the primary database's control file which need to be propagated to the
standby database's control file. In this case it is necessary to refresh the
standby database's control file from the primary database's modified control
file.

To refresh your standby database's control file, perform the following
procedures:
1. Issue the CANCEL command on the standby database to halt the recovery
process.
2. Issue the ALTER DATABASE CREATE STANDBY CONTROL FILE AS 'filename' command
on the primary to database to create a refreshed copy of the control file.
3. Issue the ALTER SYSTEM ARCHIVE LOG CURRENT command on the primary database
to archive the current on-line logs of your primary database.
4. Transfer the standby control file and the archived logfiles to the standby
database.
5. Restart the recovery process on the standby database.

If you use the CREATE CONTROLFILE command on the primary database to change
the maximum number of data files, instances, or redo logfile groups, the
physical size of the control file changes and then you must refresh the
standby database control file.

Activating the Standby Database
In the event of a disaster, you should (if possible) archive the current
on-line logs of the primary database, transfer and apply these logs to the
standby database before activating the standby database. The standby database
will then be current to the same point in time and transactional history as
the primary database before the failure. If you can not archive your current
on-line logs, then you must activate the standby database without recovering
the transactions from the unarchived logs of the primary database.

To activate a standby database, perform the following procedures:
1. Mount the database in EXCLUSIVE mode by issuing the ALTER DATABASE MOUNT
STANDBY DATABASE EXCLUSIVE command.
2. Issue the ALTER DATABASE ACTIVATE STANDBY DATABASE command.
3. Shutdown your standby instance.
4. Back-up the database.
5. Startup the instance.

At the completion of the activate command, the database is no longer
considered a standby database, but is now a production database. For this
reason it is recommended that the database be backed-up immediately.

Activating the standby database resets the on-line log files and modifies the
control file. After activation, the standby database can not be returned to
standby recovery mode.

Resetlogs Operation
Opening the primary database with the RESETLOGS option or using the CREATE
CONTROLFILE command with the RESETLOGS option invalidates the standby
database. If you reset the on-line logs of the primary database you must
re-create the standby database by following the standby database creation
procedures. Activating a standby database resets the on-line logs of the
standby database. You can follow the creation procedures to create a standby
database from the activated database.

Direct Path Operations
Performing direct loads originating from a direct path load, table create via
subquery, or index create on the primary database, the performance gain
applies only to the primary database. There is no corresponding recovery
process performance improvement on the standby database. The standby database
recovery process still sequentially reads and applies the redo information
generated by the unrecoverable direct load. Also, operations using the
UNRECOVERABLE option are not propagated to the standby database since the
operations are not logged.

Data files affected by direct path operations on the primary database can be
altered off-line in the standby database. Data files altered off-line in the
standby database must be altered off-line with the DROP option. Tablespaces
containing the off-lined files must be dropped when the standby database is
activated.





Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

standby database

g.v.n.rao, January 27, 2002 - 11:52 am UTC


More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.