Creating a Physical Standby Database in Oracle9i

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 physical standby databases in Oracle9. Let’s backtrack take a brief 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.

Oracle9i Standby

     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, much like Oracle8i Standby, albeit more sophisticated.

Figure 1: Oracle8i Standby Database Architecture

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.

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

     Oracle8i could only transport archive logs up to the point of the most recently

Figure 2: Basic Oracle9i Standby Database Architecture

completed and copied archive log file. Oracle9i has the option of maintaining a standby database up to the most recent transaction. Transactions are passed as redo log entries from the primary directly to a pre-created archive log file on the standby database server. Additionally Oracle9i can automate 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 archive log files on multiple standby databases.

 

·        Log Apply Services. Application of archive log contents to the standby database, applied at 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.

Synchronization

     The crux of standby database architecture is synchronization between primary and standby databases. Synchronization involves keeping standby databases up to date with the primary 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 zero data loss as opposed 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.

A Dual Standby Database Architecture

     Figure 3 shows a dual standby database architecture.

 

·        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.

 

     When using multiple standby databases one could be reserved as a failover and the other used for periodical snapshot reporting. It is inadvisable to use a failover standby as a reporting engine due to potential use causing failure, negating use as an up to date failover in the event of a disaster.

Figure 3: Required Architecture

     A physical standby must be placed into read only mode to act as a reporting server. Otherwise a physical standby must remain in managed recovery mode to remain up to date with the primary database. Archives are always copied to the physical standby but not applied when in the standby is in read only mode.

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. Tablespace backup mode datafile copies are also effective. Exports are less effective since they can be time consuming to use for database reconstruction.

Preparing for the Physical Standby Database

Database Configuration Parameter Files

     The parameter files listed in this document are examples. These values do not apply to all databases. Required values for physical standby operation are highlighted in red. Some parameters may not be changeable using the ALTER SYSTEM command. It is best to begin with text parameter files and generate an SPFILE binary parameter file when all tasks are completed.

The Primary Database Parameter File

#Database and Instance Identification

db_domain=""

db_name=primary

instance_name=primary

remote_login_passwordfile=EXCLUSIVE

 

#Control files

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

 

#DB Cache and I/O

db_block_size=8192

db_cache_size=32M

db_file_multiblock_read_count=4

db_block_checking=TRUE  #I have bad disks and old machines

 

#Memory - Server

shared_pool_size=32M

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\primary\archive MANDATORY REOPEN=30'

log_archive_dest_state_1=ENABLE

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

log_archive_dest_state_2=ENABLE

log_archive_min_succeed_dest=2

log_archive_format=%t_%s.dbf

log_archive_start=true

log_archive_max_processes=3

remote_archive_enable=true

 

#Undo

undo_management=AUTO

undo_retention=900

undo_tablespace=UNDOTBS1

 

#SQL*Net

dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'

 

#Diagnostics, Tracing and Statistics

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

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

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

trace_enabled=FALSE

timed_statistics=TRUE

sql_trace=FALSE

max_dump_file_size=1M

statistics_level=BASIC

Primary Database Archive Parameters

     Some points on primary database archive parameters are important to remember.

 

 

 

·        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.

 

 

 

i  If your primary database already exists make sure that the LOG_ARCHIVE_FORMAT parameters are set the same for both primary and logical standby databases.

The Standby Database Parameter File

#Database and Instance Identification

db_domain=""

db_name=primary

instance_name=primary

remote_login_passwordfile=EXCLUSIVE

 

#Control files

control_files=("c:\oracle\oradata\primary\CONTROL01.BAK")

 

#DB Cache and I/O

db_block_size=8192

db_cache_size=32M

db_file_multiblock_read_count=4

db_block_checking=TRUE  #I have bad disks and old machines

 

#Memory - Server

shared_pool_size=32M

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

standby_archive_dest='c:\oracle\oradata\primary\archive'

log_archive_dest_1='LOCATION=c:\oracle\oradata\primary\archive'

log_archive_dest_state_1=ENABLE

log_archive_format=%t_%s.dbf

log_archive_start=true

remote_archive_enable=true

 

#Undo

undo_management=AUTO

undo_retention=900

undo_tablespace=UNDOTBS1

 

#SQL*Net

dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'

 

#Diagnostics, Tracing and Statistics

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

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

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

trace_enabled=FALSE

timed_statistics=TRUE

sql_trace=FALSE

max_dump_file_size=1M

statistics_level=BASIC

Updating a Binary SPFILE Parameter File

     Let’s update the binary parameter file. Since the binary parameter file is currently in use by the Oracle Instance the database must be shutdown and restarted into mount mode first using the text parameter file.

 

connect sys/password@primary as sysdba

shutdown immediate;

startup nomount pfile='c:\oracle\admin\PRIMARY\pfile\initPRIMARY.ora';

create spfile='c:\oracle\ora92\database\spfilePRIMARY.ora'

from pfile='c:\oracle\admin\PRIMARY\pfile\initPRIMARY.ora';

shutdown immediate;

startup;

Archiving the Primary Database

     Now make sure that the primary database is archived.

 

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            c:\oracle\oradata\primary\archive

Oldest online log sequence     145

Next log sequence to archive   149

Current log sequence           149

 

     If the primary database is not archived take the following steps to archive it.

 

connect sys/password as sysdba

shutdown immediate;

 

     Set the LOG_ARCHIVE_START parameter value to TRUE in the text configuration parameter file, startup in mounted mode, set the database to archive log mode and recreate the binary parameter file. Make sure the following listed parameters are set.

 

#Logs and recovery

fast_start_mttr_target=300

log_archive_dest_1='LOCATION=c:\oracle\oradata\primary\archive'

log_archive_dest_state_1=ENABLE

log_archive_format=%t_%s.dbf

log_archive_start=true

i  If your primary database already exists make sure that the LOG_ARCHIVE_FORMAT parameters are set the same for both primary and logical standby databases.

connect sys/password@prim as sysdba

startup mount pfile='c:\oracle\admin\PRIMARY\pfile\initPRIM.ora';

ALTER DATABASE ARCHIVELOG;

create spfile='c:\oracle\ora92\database\spfilePRIMARY.ora'

from pfile='c:\oracle\admin\PRIMARY\pfile\initPRIMARY.ora';

 

     Restart the database again using the updated binary parameter configuration file.

 

shutdown immediate;

startup;

Network Services

The Listener

     Network services files are contained in the $ORACLE_HOME/network/admin directory. The SID description in the LISTENER.ORA file on the standby server is something like the following. Restart the listener on the standby using the command LSNRCTL STOP followed by LSNRCTL START.

 

    (SID_DESC =

      (GLOBAL_DBNAME = primary)

      (ORACLE_HOME = c:\oracle\ora92)

      (SID_NAME = prim)

    )

TNS Connection Strings

     Change the TNSNAMES.ORA files on both servers something like that shown.

 

PRIMARY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS=(PROTOCOL=TCP)(HOST=<primary hostname>)(PORT=1521)))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = PRIMARY)))

 

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS=(PROTOCOL=TCP)(HOST=<standby hostname>)(PORT=1521)))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = PRIMARY)))

i  All the connections for communication between primary and standby servers are DEDICATED. I did not test with a Shared Server configuration and I would not recommend it for communication between primary and standby databases. It may not work!

Profiles (SQLNET.ORA)

     The SQLNET.ORA file contains session profiling parameters. Settings in the SQLNET.ORA Oracle Net Services file can possibly cause problems when using a setting in the DB_DOMAIN configuration parameter and thus the GLOBAL_DBNAME setting in the LISTENER.ORA file, for a specific database SID.

 

     The default value for the SQLNET.AUTHENTICATION_SERVICES parameter is NONE. The setting created by the Network Configuration Assistant on database creation is set to (NTS) for Windows NT native authentication. This setting can sometimes cause the aforementioned problems.

 

“If you use MTS, databases can register in listeners which are located in other machines; don't ask me why, it just works that way. Obviously, that creates a huge problem, as the db_name is the same, and the instance name too. I have sorted it out by renaming the instance, and adding the parameter instance_name in tnsnames, so I can connect to the one I want. Basically, what the listener is saying is that you have two instances for the same service; once they have different instance names is easy to choose the one you want. Anyway, now it is up and running, and it seems everything is OK. I have your instructions just in case tomorrow we ran into trouble (I hope we don't need to use them).”

 

     When all changes are made you could use the TNSPING utility on each server to check connections. If TNSPING returns an error check your TNSNAMES.ORA and LISTENER.ORA configuration files. A single bracket in the wrong place will cause the network software to fail. Otherwise test with the operating system PING utility with an IP-Address. If you are using hostnames, the Win2K host file is in the C:\WINDOWS\system32\drivers\etc directory and on Unix probably in /etc. Thus on both the primary and standby database servers:

 

TNSPING primary

TNSPING standby

Creating a Physical Standby Database

Using OFA

     When creating the physical standby database remember to use the OFA (Optimal Flexible Architecture) structure in the operating system. The OFA looks as shown in Figure 4.

i  Tablespace backup mode datafile copies can be used to copy to the standby database. It might be a little more complex.

Figure 4: The OFA (Optimal Flexible Architecture)

     Create the directory structure manually in the $ORACLE_BASE directory for the physical standby something like that following and place the backup files into the appropriate OFA directory structure.

i  It might be advisable to place a backup copy of the production datafiles on the standby server. If something goes wrong you will have to recopy.

mkdir c:\oracle\admin

mkdir c:\oracle\admin\STANDBY

mkdir c:\oracle\admin\STANDBY\bdump

mkdir c:\oracle\admin\STANDBY\cdump

mkdir c:\oracle\admin\STANDBY\create

mkdir c:\oracle\admin\STANDBY\pfile

mkdir c:\oracle\admin\STANDBY\udump

mkdir c:\oracle\oradata

mkdir c:\oracle\oradata\STANDBY

mkdir c:\oracle\oradata\STANDBY\archive

i  It is interesting to note that if the $ORACLE_BASE/admin/STANDBY/bdump directory is not created the physical standby may appear to halt on startup. If there is no directory for the alert log to be placed into the database appears to wait. Interestingly enough creating the bdump alert log directory whilst the database is halted in this situation will instantly allow the database to create the alert log, therebye releasing the halt on the database.

The Steps for Creating a Physical Standby

     The steps to creating a physical standby with the same name as the primary database are as listed following. The standby database must reside on a separate machine to that of the primary database if it is to have the same name.

 

·        Execute a log switch on the primary database.

 

alter system switch logfile;

 

·        Shutdown the primary database followed by stopping the Windows service.

 

·        Copy all datafiles excluding tempfiles (temporary or TEMP tablespace datafiles) to the identical directory structure on the standby server.

i  Do not copy the redo logs, the primary database controlfiles or any tempfiles from the primary to the standby database.

·        Start the Windows service and the primary database again.

 

·        Execute a log switch on the primary database.

 

alter system switch logfile;

 

·        Create the standby controlfile on the primary database.

 

alter database create standby controlfile as

'c:\oracle\oradata\primary\control01.bak' reuse;

 

·        Once again shutdown the primary database followed by stopping the Windows service.

 

 

·        Copy recent archive logs, the standby controlfile (CONTROL01.BAK) and a text version of the parameter file to the identical directory structure on the standby server.

 

 

 

oradim -new -sid PRIMARY -intpwd password –maxusers 2 -startmode auto –pfile c:\oracle\admin\primary\pfile\initPRIMARY.ora

 

 

sqlplus /nolog

 

Figure 5: Setting ORACLE_SID in REGEDIT.EXE

 

connect sys/password as sysdba;

shutdown immediate;

startup nomount

pfile='c:\oracle\oradata\primary\pfile\initPRIMARY.ora';

alter database mount standby database;

recover standby database;

 

 

auto

 

Archive logs will be applied to the standby as required. The final response should be a file error on the next archive log file due from the primary database. Check the primary server and make sure its next archive log will be the same as this missing archive log.

 

 

alter database open read only;

alter tablespace temp

add tempfile 'c:\oracle\oradata\primary\temp01.dbf'

size 100M autoextend on;

 

 

shutdown immediate;

startup nomount

pfile='c:\oracle\oradata\primary\pfile\initPRIMARY.ora';

alter database mount standby database;

 

 

·        If there are any new archive log files on the primary at this stage, and not on the standby, manually copy them across and recover the standby database again.

i  Automatic transfer of archive logs by Log Transport Services require Oracle Enterprise edition on both primary and standby database servers. Standard edition will require manual archive log transfer and is less reliable.

 

alter database recover managed standby database

[ disconnect from session ];

 

To cancel managed recovery mode use the following command:

 

alter database recover managed standby database cancel;

 

i  If the primary parameter file is instantiated at this stage, opening the primary database will execute a log switch and should transfer any missing archives and apply changes to the standby database.

 

alter database recover managed standby database cancel;

alter database open read only;

 

     At this stage the standby database creation process is complete, assuming that testing was verified.

i  Parameters in this document use LGWR to transfer redo log entries to a pre-created archive log file on the standby database. Therefore the latest archive log file should always be one higher on the standby server.

Duplexing Archive Log Destinations

     Extra security can be added by creating duplexed archive directories on both primary and standby databases. However, performance issues could result. Duplexed archive log locations should probably be monitored for excessive disk space usage. If RMAN is used and archive log files are automatically deleted upon backup, verify that duplexed archive destinations are cleared as well.

Change the Primary Database Parameter File

#Logs and recovery

fast_start_mttr_target=300

log_archive_dest_1

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

log_archive_dest_state_1=ENABLE

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

log_archive_dest_state_2=ENABLE

log_archive_dest_3

='LOCATION=c:\oracle\oradata\primary\archivedplx MANDATORY REOPEN=30'

log_archive_dest_state_3=ENABLE

log_archive_min_succeed_dest=3

log_archive_format=%t_%s.dbf

log_archive_start=true

log_archive_max_processes=4

remote_archive_enable=true

i  Ensure that the duplexed archive log directory is created (archivedplx).

Change the Standby Database Parameter File

     This only works if the standby database is activated. Do not enable the second location unless the standby database is activated. Enabling the duplexed location on the standby database whilst in standby mode will disable recovery.

 

#Logs and recovery

fast_start_mttr_target=300

standby_archive_dest='c:\oracle\oradata\primary\archive'

log_archive_dest_1='LOCATION=c:\oracle\oradata\primary\archive'

log_archive_dest_state_1=ENABLE

log_archive_dest_2

='LOCATION=c:\oracle\oradata\primary\archivedplx MANDATORY REOPEN=30'

log_archive_dest_state_2=ENABLE

log_archive_min_succeed_dest=2

log_archive_max_processes=3

log_archive_format=%t_%s.dbf

log_archive_start=true

remote_archive_enable=true

i  Ensure that the duplexed archive log directory is created (archivedplx).

     Do not forget to switch your standby database back into managed recovery mode. Additionally at this stage binary parameter files can be created for both primary and standby databases.

Setting the Level of Protection

     This is achieved by using the following command syntax executed on the primary database.

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE

{ PROTECTION | AVAILABILITY | PERFORMANCE };

 

     The protection mode can be found by executing this query. PERFORMANCE is the default.

 

SELECT name, protection_mode, protection_level FROM v$database;

 

NAME      PROTECTION_MODE      PROTECTION_LEVEL

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

STBY      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

     No Data Loss Mode. The PROTECTION mode applies only to physical standby databases using LGWR SYNC. The primary database will be shutdown if no standby database can be written to.

 

     Minimal Data Loss. The AVAILABILITY mode prevents a transaction committing on the primary until all redo entries are written to at least one standby database. SYNC transport is required and this option is available to both logical and physical standby type databases. Unlike PROTECTION mode, which shuts down the primary database in the event of failure to pass redo entries to the standby, this mode simply lowers the protection mode to PERFORMANCE until the error is corrected.

 

     No Data Divergence. PERFORMANCE mode is the default setting and available for both physical and logical standby type databases. A transaction will commit to the primary before all redo entries are written to any standby database.

 

     To ensure that minimal data loss will be encountered execute this command on the primary database. The database must be in mounted exclusive mode to execute this command.

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

i  Please read about this first in the Oracle documentation before implementing.

Restarting Databases

Shutdown

     Shutdown the primary database first so it cannot send anything to the standby database.

 

shutdown immediate;

 

     Shutdown the standby database after the primary.

 

alter database recover managed standby database cancel;

shutdown immediate;

Startup

     Startup the standby database first in case anything should be sent by the primary database.

 

startup nomount;

alter database mount standby database;

alter database recover managed standby database

disconnect from session;

 

     Startup the primary database after the standby.

 

startup;

Standby In and Out of Read Only Mode

Into Read Only Mode

alter database recover managed standby database cancel;

alter database open read only;

Out of Read Only Mode

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database recover managed standby database

disconnect from session;

 

OR

 

alter database close;

alter database recover managed standby database

disconnect from session;

i  The ALTER DATABASE CLOSE command can be unreliable.

Adding Datafiles to the Primary Database

     Physical file changes on the primary database will not be propagated to the standby database; only new tablespaces will be created (with no datafile). Datafiles must be manually created on the standby database. Any missing archive logs may have to be manually copied and recovered.

 

     A database configuration parameter new to Oracle9i called STANDBY_FILE_MANAGEMENT contradicts the above. The parameter can be set to either MANUAL (the default) or AUTO. Setting the parameter to AUTO will create datafiles on the standby database when created on the primary database. The only danger with using this parameter is accidental overwriting of existing standby datafiles.

Activating a Standby Database

     The command is as shown following, check the manuals for more detail. If a standby database is activated redo log files are generated on the standby database. Existing redo log files should be overwritten (reused).

 

alter database activate standby database;

 

     Once a standby database is activated it cannot become a standby database again. Complete recreation of the standby database from the primary database would be required. Do not test activation unless you are prepared to recreate your standby database from scratch.

Disabling the Standby Database

     If the standby database should need to be temporarily disabled the simplest method would be to disable the standby archive directory service in the primary database parameter file.

 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER';

i  The DEFER option excludes an archive destination from archiving until re-enabled.

     Re-enable the standby database archive destination in the primary database configuration parameter file to reactivate the standby database.

 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE';

 

Disclaimer Notice: This information is available “AS IS”. I am in no way responsible or liable for any mishaps as a result of using this information.