Creating a Logical Standby Database in Oracle9i

Creating a Logical Standby Database in Oracle9i

Creating a Logical Standby Database in Oracle9i

mailto:

October 7th, 2003

General Architecture

Oracle9i Standby is an improved version of Oracle8i Standby database technology. Oracle8i Standby provided a time lag switchable backup of a production database. This document will focus on logical standby databases in Oracle9i and somewhat in Oracle10G. Let’s take a quick look at Oracle8i Standby.

Oracle8i Standby

Oracle8i Standby was a very simplistic and primitive manner of maintaining a switchover or failover backup database. Oracle8i Standby simply copied archive logs to a standby database through SQL*Net. The archive logs were then automatically applied on the standby database to its datafiles and control file. The result was a potential single redo log file time lag between production and standby databases. Potential redo log file loss was catered for by maintaining multiple copies of redo log files on the production server.

i  Standby databases created in Standard Edition require scripting to pass archive logs from a production to a standby database. Everything but the most recent archive log can be copied since the current archive could be in the process of being written to.

The Standby database was maintained in permanent (automatically managed) recovery mode, inaccessible for read access unless switched to be so. When a standby database was switched to read access, archive logs were still transferred to the standby database but not applied until the standby database was once again placed into inaccessible (managed) recovery mode. Figure 1 shows the simplicity of Oracle8i Standby database architecture.

Figure 1: Oracle8i Standby Database Architecture

Oracle9i Standby

Oracle9i is improved and more versatile than Oracle8i standby technology but unfortunately much more complex. The beauty of standby databases in the past has been in their inherent simplicity. Alas in Oracle9i that is no longer the case. Standby databases in Oracle9i and beyond are horrendously complex.

i  Oracle8i standby was simple and easy to implement and manage. Oracle9i standby is now excessively complex, especially the logical standby database configuration.

An Oracle9i standby database can be a physical or a logical standby database. A physical standby is maintained in managed recovery mode to remain current and can be set to read only; archive logs are copied and applied. A logical standby database can be maintained in read write mode but objects found on the primary database cannot be tampered with without difficulty. Standby databases can be configured in a number of ways. Figure 2 shows the increased functionality and powerful capabilities of Oracle9i standby.

Figure 2: Basic Oracle9i Standby Database Architecture

Where Oracle8i could only transport archive logs up to the point of the most recently completed and copied archive log file, Oracle9i has the option of maintaining a standby database up to the most recent transaction. The archive transfer functionality is still used. Oracle9i has simply expanded the standby database concept to include up to the most recent transaction update plus automated switchover, switchback and failover. There are a number of aspects to Oracle9i standby architecture.

Basic Aspects of Standby Databases

·  Connectivity. Primary and standby databases use network configuration between the two databases to transfer changes.

·  Log Transport Services. Automated transfer of archive logs from primary to standby database. The LGWR (log writer) or ARCn (archiver) processes can be used to write redo entries to the archive log file on multiple standby databases.

·  Log Apply Services. Application of archive log contents to the standby database, applied on log switch on the primary database. Delays can be implemented but DELAY applies to application of archive entries to the standby not the copy of the archive log to the standby.

·  Role Management Services. Allows switchover, switchback and failover functionality. This will not be covered in this document.

·  Data Guard Broker. Allows standby database creation and monitoring. This will not be covered in this document.

i  This document does not include implementation of Data Guard or switchover.

Synchronization

The crux of standby database architecture is synchronization between primary and standby databases. Synchronization involves keeping the standby databases up to date with the primary database. For a logical standby database set the archive destination service to LGWR SYNC=PARALLEL. The LGWR process writes entries concurrently to primary and standby databases. The ARCn process writes an archive log to the standby when a primary database log switch occurs. The LGWR on the primary writes redo log entries to a pre-created archive log file on the standby. The changes are applied from the new standby archive log when a switch occurs on the primary. Thus the LGWR process provides for no data loss in relation to using the ARCn process. Using ASYNC will be a little faster than SYNC since control is returned to the application immediately. SYNC forces applications to wait until all standby databases have received redo log entries. SYNC is perhaps a little slower but safer. The PARALLEL option simply executes log transfers to all standby databases simultaneously, otherwise changes are applied to multiple standby databases serially, or one after the other. Serial updates imply that control is not returned to the primary database until all standby databases have been updated.

Creating a Logical Standby Database

i  This document is by no means guaranteed to be 100% correct for every possible scenario. I am not responsible for any loss caused by following the steps in this document. If at some stage you need assistance please contact me.

Figure 3 shows a simple architecture for one primary database, one local standby database and one remote standby database.

Figure 3: One Primary and Two Standby Databases

The architecture shown in Figure 3 is two-fold. Both databases can be used for reporting. Logical standby architecture allows creation of new objects on the standby databases, as long as there is no conflict with the primary.

·  Failover. Located at the same site as the primary production database on a separate server. Could be implemented with no data loss using the LGWR process

·  Disaster Recovery. Located remotely with minimal but acceptable potential data loss.

Both standby databases can be used as report generation support databases in order to prevent interference with OLTP activity requirements on the primary production database. Even though the remote standby database is not completely synchronized with the primary production database, and one of its tasks is reporting, it should still be regarded as a potential failover server.

i  One standby database should be reserved as a failover and the other used for reporting. It is inadvisable to use the failover standby as a reporting engine due to potential use causing failure, negating use as an up to date failover in the even of a disaster.

Backup and Recovery

Standby database architecture should never be intended as a replacement for backup and recovery. Backup and recovery should be implemented otherwise. If RMAN is used it is safest to use a repository, installed on a database server different to that of the primary production server. If you use tablespace backup mode datafile copies for backup, these datafiles can be used to begin creation of standby databases.

Starting on the Primary Server

This configuration was implemented entirely on Win2K boxes. Thus GUI tools are used. The Database Configuration Assistant is used in this document to create the initial primary database but Data Guard Manager (Oracle Enterprise Manager connected to a Management Server) is not used due to errors in Oracle9.2. Now let’s go through the steps of both primary and logical standby database implementation and configuration. Execute the Database Configuration Assistant on the primary server. If using scripts to create a database please see the note below first! Your scripts must be changed.

The primary database must have a dictionary managed SYSTEM tablespace. The backup controlfile removes the temporary tablespace but not the temporary datafile. A locally managed SYSTEM tablespace does not allow a database without a temporary tablespace. Therefore in order to remove the temporary tablespace from the backup controlfile the SYSTEM tablespace must be dictionary managed. This problem was found with Oracle 9.2.0.1.0 on Win2K.

i  The standby database will not start if created from a primary database using a locally managed SYSTEM tablespace.

Figure 4 shows database feature requirements for creating a logical standby database. The examples can be removed.

Figure 4: Oracle9i Logical Standby Minimal Feature Requirements

Figure 5 shows minimal memory buffer requirements for using logical standby. Thus values will vary depending on hardware and operating system.

i  The shared pool must be at least 160Mb.

Figure 5: Oracle9i Logical Standby Minimal Buffer Requirements

Note in Figure 6 that the primary database must be archived.

Figure 6: Archive the Primary Database

Figure 7 shows general requirements for successful creation of a logical standby database. Note that the SYSTEM tablespace is created as dictionary and not locally managed. If you are using an existing database with a locally managed SYSTEM tablespace you will have to create a new database and recreate your production database using the EXP (export) and IMP (import) utilities. Additionally there is a single controlfile and five redo logs of 10Mb each in this example. Redo logs are named as REDOnnx.LOG in order to allow for redo log duplexing at a later date. Controlfiles should be duplicated (multiplexing) but on different drives as redo logs should be. Duplexing of redo logs and multiplexing of controlfiles can be left for a later point in time.

i  A locally managed tablespace cannot be converted to a dictionary managed tablespace.

Figure 7: Oracle9i Logical Standby Database Structural Requirements

Run through the Database Configuration Assistant or change your scripts and then execute those scripts to create the primary database, if so required.

Import a Database or Schemas

Databases can be imported as entire databases or separate schemas. I tested with a FULL database import but you could get a clean SYSTEM tablespace and simply export and import the schemas. Schema copies will be much faster but a little riskier if you do not know what is in your SYSTEM tablespace. A FULL import will import the SYSTEM tablespace but could take hours to run.

i  The production database must be unavailable to applications during EXP and IMP processing since you do not want data to change.

Please remember that to execute any type of import (IMP) that tablespaces and datafiles existing in the current production database must exist in the new production database. Create your new production database accordingly.

i  Imports will not be propagated to the logical standby database. At least it did not when I tested it.

When the import is complete you should be able to bring up the new production database. You will have to change any use of SID database names in any listener and TNS Oracle Networking configuration parameter files, both on the server and on client machines, to make it all work.

Primary Database Configuration Parameter File

Here is a simple textual parameter file for the primary database. Changes applicable to managing a single standby are highlighted in red and a second standby database in blue. The first version of the primary parameter file will not have any standby modifications other than standby archive directory settings.

i  Your configuration parameter file may be different. Use the red and blue parts only. This parameter file is simply an example.

#Database and Instance Identification

db_domain=""

db_name=prim

instance_name=prim

remote_login_passwordfile=EXCLUSIVE

#Control files

control_files=("c:\oracle\oradata\prim\CONTROL01.CTL")

#DB Cache and I/O

db_block_size=8192

db_cache_size=64M

db_file_multiblock_read_count=4

db_block_checking=TRUE #I have bad disks and old machines

#Memory - Server

shared_pool_size=160M

shared_pool_reserved_size=4M

large_pool_size=8M

log_buffer=131072

java_pool_size=32M

processes=200

job_queue_processes=10

#Memory - Connection

open_cursors=300

sort_area_size=65536

sort_area_retained_size=6554

hash_area_size=131072

bitmap_merge_area_size=1048576

create_bitmap_area_size=8388608

#Optimizer

hash_join_enabled=TRUE

query_rewrite_enabled=TRUE

query_rewrite_integrity=TRUSTED

star_transformation_enabled=FALSE

parallel_automatic_tuning=TRUE

compatible=9.2.0.1.0

#Logs and recovery

fast_start_mttr_target=300

log_archive_dest_1=

'LOCATION=c:\oracle\oradata\prim\archive MANDATORY REOPEN=30'

log_archive_dest_state_1=ENABLE

log_archive_dest_2='SERVICE=STBY LGWR SYNC=PARALLEL AFFIRM'

log_archive_dest_state_2=ENABLE

#log_archive_dest_3='SERVICE=FAILOVER ARCH AFFIRM'

#log_archive_dest_state_3=ENABLE

#log_archive_min_succeed_dest=1

log_archive_min_succeed_dest=2

#log_archive_min_succeed_dest=3

log_archive_format=%t_%s.dbf

log_archive_start=true

log_archive_max_processes=3

log_archive_trace=1

remote_archive_enable=true

i  Specific settings for LOG_ARCHIVE_DEST_2 and LOG_ARCHIVE_DEST_3 parameters depend on your hardware configuration.

#Undo

undo_management=AUTO

undo_retention=900

undo_tablespace=UNDOTBS1

#SQL*Net

dispatchers="(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521)"

shared_servers=2

circuits=5

shared_server_sessions=5

max_shared_servers=5

max_dispatchers=3

#Diagnostics, Tracing and Statistics

background_dump_dest=c:\oracle\admin\prim\bdump

core_dump_dest=c:\oracle\admin\prim\cdump

user_dump_dest=c:\oracle\admin\prim\udump

trace_enabled=FALSE

timed_statistics=TRUE

sql_trace=FALSE

max_dump_file_size=1M

statistics_level=BASIC

Some points on primary database archive parameters are important to note. These can be found in the Oracle documentation in Chapter 5 of the Data Guard Concepts and Administration Manual.

·  AFFIRM assures that archive logs are written to disk, primary or standby.

·  MANDATORY assures that redo logs are not overwritten until archive logs are successfully created. This should probably only apply to the primary database.

o  REOPEN=30 means that there will be a 30 second delay until ARCn and/or LGWR processes try again on a MANDATORY destination which failed.

·  DELAY is in minutes and does not stop the copy of an archive log file to a standby server but the application of redo on the standby after copying the archive log to the standby. This will not help primary database performance.

·  Using ARCH instead of LGWR for the second standby database may help primary database performance but smaller sized log files would probably be required. SYNC=PARALLEL applies to LGWR only. Using ARCH waits for a switch on the primary, LGWR copies entries to a standby archive log, applied only at switch. ARCH will copy and apply at switch. LGWR is more efficient since it writes redo entries to all standby databases at once but a primary and two standby databases could possibly cause a performance issue for the primary database, possibly but unlikely! Additionally multiple archiver processes can be created on the primary database. Increase the value of the LOG_ARCHIVE_MAX_PROCESSES parameter to start additional archiver processes. The default on my machine appears to be 2 and not 1 as stated in the manuals; probably because I have two standby databases.

·  The ARCHIVE_LAG_TARGET parameter could be used to increase the frequency of log switches, thus sending less data to the standby databases more often.

i  Memory parameter configuration values may be different to that created previously using the Database Configuration Assistant. The shared pool buffer is still set to 160Mb. Set buffer values for your primary database accordingly.