【赛迪网-it技术报道】说明:oracle9i 数据库的 data guard 特性确保对数据进行完整的保护,是 oracle 9i 的一个关键特性之一。data guard 可以创建物理的 standby 数据库,也可以创建逻辑的standby数据库,还可以混合使用,灵活性比较强.如果对standby机制的了解有更高的期望,或者想得到关于oracle数据库的data guard 和standby的更多信息,请参考官方的文档。
现有的数据库实例 (primary)名字:orcl1
预创建的standby数据库实例名字:orcl2
数据库版本信息:
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle9i enterprise edition release 9.2.0.1.0 - production
pl/sql release 9.2.0.1.0 - production
core 9.2.0.1.0 production
tns for 32-bit windows: version 9.2.0.1.0 - production
nlsrtl version 9.2.0.1.0 - production
准备工作:
首先确认primary数据库是否在归档模式下
sql> show user
user is "sys"
sql>
sql> archive log list
database log mode archive modeautomatic archival enabledarchive destination d:\oracle\arcoldest online log sequence 28next log sequence to archive 30current log sequence 30如果不在归档模式下,调整数据库。首先提交命令修改spfile:
sql>alter system set log_archive_start=true scope=spfile;
然后关闭数据库实例
sql>shutdown immediate;
备份数据库
sql>startup mount
sql>alter database archivelog;
sql>alter database open;
sql>shutdown immediate
备份
1. primary database 需要做的准备工作
1.1 激活 forced logging
sql> alter database force logging;
1.2 设置本地归档目标
sql> alter system set log_archive_dest_1='location=d:\oracle\arc' scope=both;
此操作直接生效
2.创建物理的standby数据库
2.1 标记出primary数据库的数据文件
sql> select name from v$datafile;
name
-----------------------------------------------------
d:\oracle\oradata\orcl1\system01.dbfd:\oracle\oradata\orcl1\undotbs01.dbfd:\oracle\oradata\orcl1\drsys01.dbfd:\oracle\oradata\orcl1\indx01.dbfd:\oracle\oradata\orcl1\tools01.dbfd:\oracle\oradata\orcl1\users01.dbfd:\oracle\oradata\orcl1\xdb01.dbf
2.2 关闭instance 拷贝数据文件到既定目的地
sql> shutdown immediate;
2.3 为standby 数据库创建控制文件
sql> alter database create standby controlfile
as 'e:\oracle\oradata\orcl2\standby.ctl';
要注意这个控制文件的名字不要和primary的控制文件名字重复
2.4 为standby数据库准备初始化参数文件名字
sql> create pfile='e:\oracle\admin\orcl2\pfile\initorcl2.ora' from spfile;
2.5 设定初始化physical standby database参数
*.aq_tm_processes=1
*.background_dump_dest='e:\oracle\admin\orcl2\bdump'
*.compatible='9.2.0.0.0'
*.control_files='e:\oracle\oradata\orcl2\standby.ctl'
*.core_dump_dest='e:\oracle\admin\orcl2\cdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='orcl1'
*.dispatchers='(protocol=tcp) (service=demoxdb)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=true
*.instance_name='orcl2'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=e:\oracle\arc'
*.log_archive_start=true
*.open_cursors=300
*.optimizer_mode='first_rows'
*.pga_aggregate_target=17825792
*.processes=150
*.query_rewrite_enabled='true'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.star_transformation_enabled='true'
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='undotbs1'
*.user_dump_dest='e:\oracle\admin\orcl2\udump'
lock_name_space=orcl2
standby_file_management=auto
remote_archive_enable=true
standby_archive_dest='e:\oracle\standbyarc'
db_file_name_convert=('d:\oracle\oradata\orcl1', 'e:\oracle\oradata\orcl2')
log_file_name_convert=('d:\oracle\oradata\orcl1', 'e:\oracle\oradata\orcl2')
log_archive_dest_1=('location=e:\oracle\standbyarc')
整个操作的过程中,容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。
2.6 创建一个windows服务
winnt> oradim -new -sid orcl2 -startmode manual
2.7 create a server parameter file for the standby database
可参考执行如下操作:
c:\>set oracle_sid=orcl2
c:\>sqlplus /nolog
sql> connect / as sysdba
sql> create spfile from pfile='e:\oracle\admin\orcl2\pfile\initorcl2.ora';
2.8 启动物理standby数据库
c:\>set oracle_sid=orcl2
c:\>sqlplus /nolog
sql> connect / as sysdba
sql> startup nomount;
sql> alter database mount standby database;
2.9 在standby数据库上,初始化log apply 服务
sql> alter database recover managed standby database disconnect from session;
2.10 激活到物理standby数据库的归档
sql> alter system set log_archive_dest_2='service=orcl2' scope=both;
sql> alter system set log_archive_dest_state_2=enable scope=both;
2.11 启动远程归档
sql> alter system archive log current; -在primary database上执行.
3.安装完的的验证
3.1 在primary database上
alter system set standby_archive_dest='e:\oracle\standbyarc' scope=both;
3.2 在database,查询v$archived_log
(其实也可以直接到相关目录下查看log是否创建):
sql> select sequence#, first_time, next_time
from v$archived_log order by sequence#;
sequence# first_tim next_time---------- --------- --------- 30 09-jan-07 17-jan-07 31 17-jan-07 17-jan-073.3 在primary数据库上,归档当前的log
sql> alter system archive log current;
3.4 验证是否收到
sql> select sequence#, first_time, next_time
from v$archived_log order by sequence#;
sequence# first_tim next_time---------- --------- --------- 30 09-jan-07 17-jan-07 31 17-jan-07 17-jan-07 32 17-jan-07 17-jan-07
3.5 验证是否新的归档redo日志已经被应用
sql> select sequence#, applied from v$archived_log order by sequence#;
sequence# app---------- --- 30 yes 31 yes 32 yesok.表明我们还是成功的.
附加内容:
primary数据库的pfile内容
*.aq_tm_processes=1
*.background_dump_dest='d:\oracle\admin\orcl1\bdump'
*.compatible='9.2.0.0.0'
*.control_files='d:\oracle\oradata\orcl1\control01.ctl','d:\oracle\oradata\orcl1\control02.ctl','d:\oracle\oradata\orcl1\control03.ctl'
*.core_dump_dest='d:\oracle\admin\orcl1\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl1'
*.dispatchers='(protocol=tcp)'
*.fast_start_mttr_target=300
*.hash_join_enabled=true
*.instance_name='orcl1'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='location=d:\oracle\arc'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='false'
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='false'
*.timed_statistics=true
*.undo_management='auto'
*.undo_retention=10800
*.undo_tablespace='undotbs1'
*.user_dump_dest='d:\oracle\admin\orcl1\udump'
*.standby_archive_dest='e:\oracle\standbyarc'
*.remote_archive_enable=true
闽公网安备 35060202000074号