Oracle Standby Databases

Building Standby Databases

  1. Check that there is a working tnsnames (primary machine) to listener (standby machine) connection. The archival process between the primary and standby databases will use the listener on the standby database machine. There is nothing special about tnsnames and listener files between primary and standby databases.

  2. Place the primary database into restricted access mode and either kill off or wait for all connections to be terminated. Any activity will result in standby database inconsistencies between datafiles and control files.

  3. Archive the current log. Since the database is in restricted access mode, and of course you are not executing any DML or DDL statements, no log entries have been made. However, all sorts of things in Oracle produce redo log entries, some things unexpectedly. Therefore since the next step is creation of standby control files we must synchronise the control files and datafiles by forcing the immediate archival of all not as yet archived log files. In fact archiving of the current log is actually incorrect since a redo log is not neccessarily archived whena log switch occurs. Thus archive all not yet archived logs, ie. ALTER SYSTEM ARCHIVE LOG ALL; CHECK THIS. Note that ALTER SYSTEM ARCHIVE LOG CURRENT; will only archive the currently active redo log file and not any log files which are no longer in use but not yet archived.

  4. Thus at this point the database has no currently unarchived log entries. Now create the standby database control files from the primary database.

  5. Now shutdown the primary database.
  6. Copy the production database datafiles, archived logs and standby control files created to the standby database location. Note that the redo logs are not required on the standby but the database will not mount without them, the database must have logs completely cleared on mounting. Note that a standby database control file does not contain SCN reference pointers to log files. It is also best to have the same number of groups and members of log files plus consistent log file sizes between primary and standby databases. The control file stored details and number of log files, keep them the same. Note that even an inconsistent backup of datafiles plus all applicable archive logs can be used to create a standby database.

  7. Copy the init.ora parameter from production to the standby database location.

    1. Change the parameters on the primary database.
      LOG_ARCHIVE_START = true
      LOG_ARCHIVE_DEST_1 = "LOCATION=d:\oracle\oradata\ORCL\archive"
      LOG_ARCHIVE_DEST_2 = "SERVICE=standbydb MANDATORY REOPEN=10"		#standby1 is a tnsnames connection on the primary to the listener on the standby database server

      The option MANDATORY above specifies that archiving to the destination must succeed before the redo log file can be made available for reuse. This is interesting because if the standby database happens to be down then will the production database stall ? This could be a problem. What exactly your standby is required for is in question ? Do you want a standby as an immediately switchable backup or as a read-only database. Note that LOG_ARCHIVE_MIN_SUCCEED_DEST is set to 2. This implies that the LGWR process on the primary cannot overwrite log files until all MANDATORY and non-standby OPTIONAL destinations have succeeded. In the case of a read-only database which is not constantly checked this could definitely cause a primary database stall. As a purely backup database for a 24*7 OLTP system it may be best to leave the LOG_ARCHIVE_MIN_SUCCEED_DEST value at it's default value of 1 such that if the standby MANDATORY connection fails that the primary database does not stall; archives can always be manually copied and recovered on the standby database by the DBA. Weigh up your options between immediate recoverability, access for administration by a DBA and the core function of your standby database. There is no harm in creating a second standby database for the use of switching to read-only and report production. The REOPEN option will force a re-attempt at archiving of a failed archive destination every n seconds specified. Whatever you do never use the OPTIONAL option, if a redo log fails to archive and is overwritten then you lose an archive and have a completely unrecoverable database.

    2. Change the parameters on the standby database.
      LOG_ARCHIVE_START = true
      LOG_ARCHIVE_DEST_1 = "location=d:\Oracle\oradata\ORCL\archive"
      STANDBY_ARCHIVE_DEST = "d:\Oracle\oradata\ORCL\archive"
  8. Start the standby database as follows.
    RECOVER MANAGED STANDBY DATABASE [CANCEL];				#CANCEL will remove managed recovery mode.

    Note that in NT when executing the RECOVER command above that the shell executing from will remain on the screen for a while, simply minimise the shell window and ignore it. This command could also always be executed as a background process.

    It is also important to note that the ALTER DATABASE CLEAR LOGFILE GROUP <All logfile groups>; will clear all redo logs in the standby database. Redo logs will only be required in the standby database if the standby database is activated, in fact standby database activation will clear the redo logs anyway if they are not already cleared.

  9. Now startup the production database. Execute and ALTER SYSTEM SWITCH LOGFILE; command on the primary server and ensure that the next archive is copied on the primary database and also onto the standby database. Making a Standby Database Read-Only Accessible.

Bouncing Primary and Standby Databases

  1. Shutdown the standby database.
  2. Shutdown the primary database.
  3. Restart the primary database.
  4. Restart the standby database.

Making a Standby Database Read-Only Accessible


At this stage normal read-only SQL operations can be performed against the standby database providing a point-in-time snapshot of the database. Note that during the period in which the standby database is read-only that all archives generated in the primary to be applied to the standby are queued in the primary database for later application to the standby database when the standby database once again is placed into standby mode.

Execute the commands shown below to return the standby database to standby mode.


A read-only standby database is useful when you want to decrease the number of queries to the primary database. For example, if specific tablespaces in a primary database change infrequently but are accessed frequently, you can direct those queries to the standby database so the primary database does not become overloaded with read requests.

In some situations, maintaining multiple standby databases can lessen the repercussions of a failover. For example, assume the preceding scenario, with standby databases B and C supporting primary database A. The following events occur.

  1. A's machine suffers a media failure.
  2. You activate standby database B. Users now access B as the primary database.
  3. You quickly fix the media problem on A's machine.
  4. You shut down B, then restart A.
  5. Users now access A as the primary database again. C continues to function as a standby database for A, while B is invalidated. One consequence of this scenario is that any changes made to B while it briefly served as the primary database cannot be applied through archived redo logs. Note that you can generate a report of these changes by using the LogMiner utility and then reenter the changes manually into database A.

Activating the Standby Database

Only do this when the primary database is irretrievably lost. Activation of the standby database is completely irreversible.


Some Interesting Points to Note

  1. On NT, never shutdown the standby database without expecting to shutdown the primary database. The listener connection to the standby database will not reset without bouncing the primary database. Atleast this is the case on 8.1.5 on NT, the same does not seem to occur on Solaris.

  2. Standby databases will work with Parallel Server.

  3. If the primary database is ever recovered the standby database must be recreated.

  4. Any physical changes to the primary database, ie. those types of changes such as adding new datafiles to a tablespace, will not be duplicated to the standby database; these changes must be executed manually on the standby database.

  5. Never use any form of DIRECT path loading or data insertion. Direct path activity does not produce redo entries and thus will not be duplicated to the standby database.

  6. With respect to the parameters MANDATORY, OPTIONAL, REOPEN=s and LOG_ARCHIVE_MIN_SUCCEED_DEST=<default 1>. The order in which the archive destinations may be important in relation to LOG_ARCHIVE_MIN_SUCCEED_DEST. Therefore to avoid stalling the database MANDATORY REOPEN=10 and LOG_ARCHIVE_MIN_SUCCEED_DEST =1 is best option. It is not the best option for recoverability but this can be done manually.

  7. When shutting down the standby database the primary database may lose the Net8 connection. The only solution is to bounce the primary database.

  8. It is possible to create a standby databases from production database, backup mode tablespace datafile copies. This situation will require archive log files.

Creating Tablespaces in Primary and Standby Database

When creating a tablespace and/or new datafile in the primary database the standby database is not updated with the new datafile. New datafile creation is not logged and is thus not created in the archives. Since the archives create all new objects in the standby database, the archives will contain the tablespace creation without the datafile creation. Thus a number of steps have to be performed.

  1. Create the tablespace in the primary database.

  2. Switch the logs and transfer the archives to the standby database.

  3. Recover the standby database. There will be an error.

  4. Create the new datafile in the standby database with the standby database mounted as shown in the command below.

    alter database create datafile
    	as '/<mount-point-1>/oracle/oradata/<SID>/<new-datafile>.dbf';
  5. Recover the standby database again.

  6. At this stage it would be expedient to switch the logs on the production database, transfer the archives to the standby database again and once again, recover the standby database. This step is not absolutely neccessary.

Standby Database Glitches / Features

Locally Managed Temporary Tablespaces

When recovering or creating a standby database or when activating a standby database it is neccessary to recreate locally managed (bitmap managed) temporary tablespaces since the hook between the logical and physical is simply not maintaned between copies. The bitmap is possible stored somewhere inaccessible is somewhere I am not as yet aware of. The impact this could have on the use of locally managed tablespaces for use by non-temporary (sort space) tablespaces could be drastic and something to akin to complete loss. Personally I would not use locally managed tablespaces for anything but temporary sort space or rollback segments. I was not happy with locally managed tablespaces until the final release of Orace8i (8.1.7). Oracle9i may have some improvements in this respect but caution is encouraged.

Multiple Datafiles per Tablespace

As with rollback segments, this probably should not be done. See Managing Rollback Segments.