oracle 8i下构建standby database

oracle 8i下构建standby database

本方案主要保护数据库系统在发生system failure or other disaster（如：power failures, or from physical disasters such as fire, floods, or earthquakes）时，快速的恢复系统，减少系统的宕机时间，减少对业务的影响，尽量做到数据不丢失。
容灾系统采用oracle 数据库提供的standby database,standby dayabase 工作在managed standby database方式，并且备用数据库放置在与主数据库相隔一定距离的备用机房，这样即使在主机房发生自然灾害等，也能够很容易的快速的实现灾难恢复。下面就我院standby database的实现作具体的说明：

　　　本方案主要保护数据库系统在发生system failure or other disaster（如：power failures, or from physical disasters such as fire, floods, or earthquakes）时，快速的恢复系统，减少系统的宕机时间，减少对业务的影响，尽量做到数据不丢失。
容灾系统采用oracle 数据库提供的standby database,standby dayabase 工作在managed standby database方式，并且备用数据库放置在与主数据库相隔一定距离的备用机房，这样即使在主机房发生自然灾害等，也能够很容易的快速的实现灾难恢复。下面就standby database的实现作具体的说明：
Managed Recovery Mode
　　　You can place the standby database in managed recovery mode, in which case the standby database automatically applies archived redo logs as it receives them from the primary database. To initiate managed recovery, connect to the standby database using SQL*Plus and issue the RECOVER MANAGED STANDBY DATABASE statement.
　　　he principal advantage of running a database in managed recovery mode is that you do not have to transfer or apply archived redo logs manually: Oracle automates the procedure. For example,fig 1 illustrates a case in which a primary database in San Francisco transmits archived redo logs to a standby site in Boston, where the standby database automatically applies them.
Figure 1 Automatic Updating of a Standby Database

oracle数据库运行环境：
主服务器: hp双cpu系统，内存：1g ，raid5
Os: windows server 2000
Oracle version: Oracle8i Enterprise Edition Release 8.1.7.0.0
系统运行在24*7模式下，备份方式采用online hot backup mode,使用自己编制的脚本实现自动备份，备份后自动转储备份好的数据文件和系统的归档日志文件到离线存储设备。
Managed standby database 系统构建方法：
备用database的创建过程包括：
1. Make a backup of the primary datafiles (or access a previous backup) and create the standby control file.
2. Transfer the standby datafiles and control file to the standby site.
3. Configure Net8 so that you can connect to the standby service name.
4. Configure the primary and standby initialization parameter files.
5. Initiate automatic archiving on the primary site.
6. Start the standby instance without mounting it.
其中，比较关键的是主、备数据库的参数文件的设置。
oracle数据库Managed standby database 系统构建过程：
在主数据库执行的操作：
2 创建数据文件的备份。
　　使用现成的联机备份。
4 创建备用控制文件。
svrmgrl
Connect sys/password as sysdba
Alter database create standby controlfile as ‘任意的目录 ’；
在备用系统上执行的操作：
4 首先，在备用主机上安装oracle软件，要求oracle home 与主机系统上完全一样。
5 在windows操作系统下，使用oradim命令创建oracle服务，命名为：stdby； Oradim –new –sid stdby -intpwd oracle -pfile %%oracle_home%%database
6 拷贝主数据库下备份好的数据文件和备用控制文件到备用数据库的%%oracle_home%%oradata下。（主、备数据库的oracle_home相同）。
7 编辑备用数据库参数文件initstdby.ora：
拷贝主数据库的一份参数文件进行编辑，请注意黑体字的参数。
#
# Copyright (c) 1991, 2000 by Oracle Corporation
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################

db_name = "orcl" #The same as PRMYinit.ora
instance_name = stdby #备用sid
service_names = orcl
db_files = 1024
control_files = ("d:oracleoradataorclcontrol01.ctl", "d:oracleoradataorclcontrol02.ctl", "d:oracleoradataorclcontrol03.ctl") #备用控制文件的位置
open_cursors = 300
max_enabled_roles = 120
db_file_multiblock_read_count = 8
db_block_buffers = 73624
shared_pool_size = 100522393
large_pool_size = 614400
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150
parallel_max_servers = 5
log_buffer = 32768
#audit_trail = true # if you want auditing
#timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each
lock_name_space=stdby
##### For archiving if archiving is enabled #####
log_archive_start = false
log_archive_trace=127
log_archive_dest_1 = "location=f:archive"
log_archive_format = ORCLT%TS%S.ARC
standby_archive_dest=f:archive
# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
#rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, RBS7, RBS8, RBS9, RBS10, RBS11 )

# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = false

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true

oracle_trace_collection_name = ""
# define directories to store trace and alert files
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
db_block_size = 8192
remote_login_passwordfile = exclusive
os_authent_prefix = ""
user_dump_dest=f:oracleadminorcludump
background_dump_dest=f:oracleadminorclbdump
core_dump_dest=f:oracleadminorclcdump
distributed_transactions = 500
compatible = 8.1.0.0.0
sort_area_size = 65536
sort_area_retained_size = 65536
关于备用数据库中log_*参数的设置说明如下：
STANDBY_ARCHIVE_DEST?
　　　Used solely by a standby database in managed recovery mode to determine the location for the archived logs received from the primary database. Managed recovery mode uses this value along with LOG_ARCHIVE_FORMAT to generate the fully qualified standby database log filenames and stores the filenames in the standby control file. Managed recovery uses this data to drive recovery.
For managed recovery, set STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value. If manual recovery is required because of a gap sequence, copy the missing log to the same location as the other logs and recover manually. You can then place the standby database back into managed recovery mode.
LOG_ARCHIVE_DEST?
　　　Specifies the location of the archived logs for a standby database in manual recovery mode. When performing manual recovery on the standby database, Oracle relies on either LOG_ARCHIVE_DEST or a user-defined filename to locate the logs.
This parameter is also relevant for managed recovery. If a log is missing at the standby site and managed recovery halts, you must issue RECOVER STANDBY DATABASE to initiate manual recovery, which causes Oracle to look in LOG_ARCHIVE_DEST for the logs by default. Typically, you set STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same value for managed recovery.?
LOCK_NAME_SPACE ?
　　　Always set this value when the standby and primary databases share a host. Specifies the name space that the distributed lock manager uses to create lock names.
此时，可以启动并以只读方式打开备用数据库：
Startup nomount pfile=’’
Alter databse mount standby database;
Recover standby database;
Alter database open read only;
在这里要说明这样做的原因是为了配置net8,只有db打开的情况下其他用户才可以登录数据库。

在主、备数据库上配置net8的文件：listener.ora 、tnsnames.ora
备用数据库：

Listener.ora
# LISTENER.ORA Network Configuration File: F:oracleNETWORKADMINlistener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = orcl))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lcm)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = f:oracle)
(SID_NAME = stdby)
)
)

Tnsnames.ora
# TNSNAMES.ORA Network Configuration File: F:oracleNETWORKADMINtnsnames.ora
# Generated by Oracle configuration tools.

DBSERVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yyds)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = lcm)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(Key = orcl))
)
(CONNECT_DATA =
(SID = stdby)
(SRVR = DEDICATED)
)
)
主数据库：
Listener.ora
# LISTENER.ORA Network Configuration File: d:oracleora81networkadminlistener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yyds)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:oracleora81)
(PROGRAM = extproc)
)
)
Tnsnames.ora
# TNSNAMES.ORA Network Configuration File: d:oracleora81networkadmintnsnames.ora
# Generated by Oracle configuration tools.

STDBY.YY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.98.206.166)(PORT = 1521))
)
(CONNECT_DATA =
(SID = stdby)
)
)

DBSERVER.YY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yyds)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yyds)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
(PRESENTATION = )
)
)

ORCL.YY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yyds)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
测试网络：
Tnsping
Lsnrctl
配置主数据库的参数文件：
主要是本地归档日志和远程归档日志参数的配置
本地归档日志参数：
log_archive_dest_1 = location=e:oraclearchive
log_archive_dest_state_1 = enable
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
log_archive_start = boolean TRUE
远程归档日志参数
log_archive_dest_2 = 'SERVICE=standby1 OPTIONAL REOPEN=180'
log_archive_dest_state_2 = ENABLE

SQL> CONNECT as sysdba
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1 OPTIONAL REOPEN=180';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
Identify the Logs in the Gap Sequence.
Because you have enabled the changes to the primary database parameter file, the primary database is now able to archive to the standby service name. Before you can perform managed recovery, however, you must synchronize the standby database by applying those logs containing changes made after the primary database backup, but before the first log received by the standby database.
Write the following SQL script and run it on the standby database:
SELECT high.thread#, "LowGap#", "HighGap#"
FROM
( SELECT thread#, MIN(sequence#)-1 "HighGap#"
FROM
( SELECT a.thread#, a.sequence#
FROM
( SELECT *
FROM v$archived_log
) a,
( SELECT thread#, MAX(sequence#)gap1
FROM v$log_history
GROUP BY thread#
) b
WHERE a.thread# = b.thread#
AND a.sequence# > gap1
)
GROUP BY thread#
) high,

( SELECT thread#, MIN(sequence#) "LowGap#"
FROM
( SELECT thread#, sequence#
FROM v$log_history, v$datafile
WHERE checkpoint_change# <= next_change#
AND checkpoint_change# >= first_change#
)
GROUP BY thread#
) low
WHERE low.thread# = high.thread#;

The output of the query is as follows:
SQL> @gap
THREAD# LowGap# HighGap#
---------- ---------- ----------
1 250 252

Copy Logs in the Gap Sequence to the Standby File System
The archived log filenames generated by gap sequence queries on the standby database are generated by the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT parameters in the initialization parameter file. Before transmitting the logs from the primary site to the standby site, you must determine the correct filenames.
First, you determine the filenames of the logs in the gap that were archived by the primary database. After connecting to the primary database using SQL*Plus, issue the following SQL query to obtain the names:
SQL> CONNECT
SQL> SELECT name FROM v$archived_log WHERE sequence# IN (250,251,252);

NAME
--------------------------------------------------------------------------------
/fs1/arc_dest/log_1_250.arc
/fs1/arc_dest/log_1_251.arc
/fs1/arc_dest/log_1_252.arc
The gap sequence in this case consists of log_1_250.arc, log_1_251.arc, and log_1_252.arc. The settings for LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT in the standby database parameter file are as follows:
LOG_ARCHIVE_DEST_1 = 'LOCATION=/fs2/arc_dest/'
LOG_ARCHIVE_FORMAT = log_%t_%s.arc
You move the gap sequence logs from the primary file system to the standby file system, renaming them according to values for the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT initialization parameters at the standby site:
% cp /fs1/arc_dest/log_1_250.arc /fs2/arc_dest/log_1_250.arc
% cp /fs1/arc_dest/log_1_251.arc /fs2/arc_dest/log_1_251.arc
% cp /fs1/arc_dest/log_1_252.arc /fs2/arc_dest/log_1_252.arc
Apply the Logs in the Gap Sequence to the Standby Database.
Now you can apply the gap sequence logs using the RECOVER AUTOMATIC STANDBY DATABASE statement. This statement uses the values of the LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT parameters to construct the target filename. Once the gap sequence logs have been applied, the standby database is synchronized with the primary database and can be placed in managed recovery mode.
While connected to the standby database in SQL*Plus, recover the database using the AUTOMATIC option:
SQL> RECOVER AUTOMATIC STANDBY DATABASE;
ORA-00279: change 35083 generated at 08/16/1999 14:08:37 needed for thread 2
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>

After recovering the gap sequence logs, Oracle prompts you for the name of a log that does not exist. The reason is that the recovery process does not know about the logs archived to the STANDBY service by the primary database. Cancel recovery at this point:
SQL> CANCEL
Place the Standby Database in Managed Recovery Mode.
You can now enable managed recovery using the RECOVER MANAGED STANDBY DATABASE statement. You decide to use the TIMEOUT option of the RECOVER statement to specify a time interval of 20 minutes so that Oracle waits the specified number of minutes to write the requested archived log entry to the directory of the standby database control file. If the requested archived log entry is not written to the standby database control file directory within the specified time interval, the recovery operation is canceled.
While connected to the standby database using SQL*Plus, place the standby database in managed recovery mode:
SQL> RECOVER MANAGED STANDBY DATABASE;
Oracle now begins managed recovery. As the primary database archives redo logs to the standby site, the standby database automatically applies them.