【赛迪网－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