Creating a Physical Standby Database in Oracle9i
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.
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.
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.
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.
- 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.
- 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 appears to be 2 and not 1 as stated in manuals.
- 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.
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
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)))
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.
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.
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
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.
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.
- Install Oracle on the standby database server with the same options as on the primary database server. Additionally create a dummy database using the Database Configuration Assistant on the standby server. The installation and dummy database will set values in the registry and elsewhere, perhaps awkward to perform manually.
- 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.
- Set the ORACLE_SID variable parameter to the standby database SID name, the same SID name as the primary production database. Use the Windows Registry editor REGEDIT.EXE as shown in Figure 5.
- Create the Windows service on the standby server.
oradim -new -sid PRIMARY -intpwd password –maxusers 2 -startmode auto –pfile c:\oracle\admin\primary\pfile\initPRIMARY.ora