二Standby database 的建立
Oracle Standby Database 的建立过程并不复杂但建立过程的相关设置取决于建立standby database 的目的例如如果建立standby database 是为了 disaster protectionstandby database 就不能建立在与 primary database 相同服务器上面如果是为了 protection against data corruption在standby database 接收到 primary database 送来的 archived log files 时apply 需要晚上一段比如三个小时或是六个小时这样当 primary database出现错误的时候standby database 不会与primary database 同步
在这篇文章里面我无法面面俱到的分析各种性能仅做一个具体实例分析
我们承诺客户的条件
x uptime of SIS database
in case of failure on primary:
/ hour to fail over to standby database
no more than mins data loss
hours scheduled downtime to revert back to primary/standby configuration
我们为了完成以上各项必须完成的工作
在remote site 建立 standby database我们有半小时的时间 activing standby database我个人喜欢再做一次 cold backup
以我们的环境组 log groups每组 个membersonline redo log file size 是 M运行高峰期每分钟可以多达 个archived files 产生因此非高峰的时候我们用cron job 做强制 log switch
因为我们的standby database server 不是专用的所以在非高峰期时我们需要重新建立 primary/standby database
在这里我又要说一些多余的话了DBA 在申请down time 的时候应该给自己预留足够的时间到底多少合适自己要掌握好(如果留的时间太少老板和客户可能会认为DBA的工作很容易或不重要如果一旦出了差错自己的压力方面也够大所以一般选择在用户可接受的最多的时间我一般要求需要时间的倍)
根据上面的条件我们做的环境设置
() 首先我们必须确认 primary database 处于archived mode:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradba/sisi/arch
Oldest online log sequence
Next log sequence to archive
Current log sequence
() 我们必须满足的条件是 high availablity所以我们采用的是双机
采用双机形式有很多的好处除了再安装与primary node 相同的OS系统及oracle 系统外其他各种设置都可以与primary node 完全相同省掉很多修改参数的麻烦之处
() 我们的oracle 版本是EEstandby node 通过net 接收 primary node 的 archived log files我们专门在 standby node 开通了 port 做为 standby database 的listener(Oracle 的缺省是 port )
standby database的建立过程
standby database一般是用primary database的cold backup建立的特殊情况下可以用RMAN或export dmp file来做这里我们是讲的正常情况
() 在 standby node上面建立与primary node上面相同的datafile directory我们用的是/oradba/sisi/
() 修改 primary database的 initialize parameter file: (我们的例子请不要问我为什么很多是 application要求的不是我制定的)
primary database:
db_name = sisi
instance_name = sisi
service_names = sisi
control_files = (/oradba/sisi/ctrl/stctlsictl /oradba/sisi/ctrl/stctlsictl)
db_files =
compatible =
rollback_segments = (rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs
rbs rbs)
db_file_multiblock_read_count =
optimizer_mode = rule #application required
db_block_size =
db_block_buffers =
shared_pool_size =
sort_area_size =
sort_area_retained_size =
log_checkpoint_interval =
sessions =
transactions =
transactions_per_rollback_segment =
processes =
open_cursors =
dml_locks =
log_buffer =
log_checkpoint_timeout =
cursor_space_for_time = true
utl_file_dir=/tmp
timed_statistics = false # if you want timed statistics
max_dump_file_size = # limit trace file size to Meg each
core_dump_dest = /oradba/sisi/cdump
background_dump_dest= /oradba/sisi/bdump
user_dump_dest = /oradba/sisi/udump
remote_login_passwordfile = none
parallel_max_servers =
#The following parameters are the HA parameters needed for Standby Database on primary side
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_FORMAT = sisi%Sarc
LOG_ARCHIVE_DEST_=LOCATION=/oradba/sisi/arch MANDATORY REOPEN=
LOG_ARCHIVE_DEST_STATE_=ENABLE
STANDBY_ARCHIVE_DEST=/oradba/sisi/arch
LOG_ARCHIVE_DEST_=SERVICE=standby_sisi MANDATORY REOPEN=
LOG_ARCHIVE_DEST_STATE_=ENABLE
LOG_ARCHIVE_MIN_SUCCEED_DEST=
复制到Standby database side相对的directory下面
db_name = sisi
instance_name = sisi
service_names = sisi
control_files = (/oradba/sisi/ctrl/stctlsictl /oradba/sisi/ctrl/stctlsictl)
db_files =
compatible =
rollback_segments = (rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs rbs
rbs rbs)
db_file_multiblock_read_count =
optimizer_mode = rule
db_block_size =
db_block_buffers =
shared_pool_size =
sort_area_size = #M Change to M after import
sort_area_retained_size =
log_checkpoint_interval =
sessions =
transactions =
transactions_per_rollback_segment =
processes =
open_cursors =
dml_locks =
log_buffer =
log_checkpoint_timeout =
cursor_space_for_time = true
utl_file_dir=/tmp
timed_statistics = false # if you want timed statistics
max_dump_file_size = # limit trace file size to Meg each
core_dump_dest = /oradba/sisi/cdump
background_dump_dest= /oradba/sisi/bdump
user_dump_dest = /oradba/sisi/udump
remote_login_passwordfile = none
parallel_max_servers =
#The following parameter are the HA parameters needed for Standby Database on standby side
LOG_ARCHIVE_START=FALSE
LOG_ARCHIVE_FORMAT = sisi%Sarc
LOG_ARCHIVE_DEST_=LOCATION=/oradba/sisi/arch MANDATORY REOPEN=
LOG_ARCHIVE_DEST_STATE_=ENABLE
STANDBY_ARCHIVE_DEST=/oradba/sisi/arch
LOG_ARCHIVE_DEST_=SERVICE=standby_sisi MANDATORY REOPEN=
LOG_ARCHIVE_DEST_STATE_=ENABLE
LOG_ARCHIVE_MIN_SUCCEED_DEST=
() shutdown primary database normal/immediate做一个冷备份再次 startup primary database时用 pfile标示到上面改过的 parameter file 用ftp或其他OS工具把冷备份的 data
files/online redo log files到在standby node已经建好的对应 directory下面
() 建立 standby database control file
Alter database create standby controlfile as /oradba/sisi/temp/stctlsictl;
用 rcp或 ftp到standby node对应的directory用 cp command复制另一个
() 在primary side编辑 tnsnamesora文件增加一条(可以用netasst做)
STANDBY_SISI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
)
(CONNECT_DATA =
(SID = sisi)
)
)
() 在 standby node编辑 listenerora文件增加一条(可以用netasst做)
ST_LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prtltest)(PORT = ))
)
SID_LIST_ST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sisi)
(ORACLE_HOME = /oracle/)
(SID_NAME = sisi)
)
)
() start standby li
