在单机上创建物理的Oracle9i standby数据库
2024-08-29 13:31:13
供稿：网友
说明；oracle9i数据库的data guard 特性确保对数据进行完整的保护,是oracle 9i的一个关键特性之一．data guard可以创建物理的standby数据库，也可以创建逻辑的standby数据库,还可以混合使用，灵活性比较强．这个文档是fenng练习时候记下来的东西，谈不上是什么心得．*这不过是一份安装过程中的笔记而已* 如果对这个有更高的期望，或者想得到关于oracle数据库的data guard 和standby的更多信息，请参考官方的文档．
系统环境：windows 2000 专业版 sp3 512m 内存
现有的数据库实例（primary）名字：demo
预创建的standby 数据库实例名字：pstandby
数据库版本信息：
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 mode
automatic archival enabled
archive destination d:/oracle/oradata/demo/archive
oldest online log sequence 42
next log sequence to archive 44
current log sequence 44
如果不在归档模式下，调整数据库。
首先提交命令修改spfile：
sql>alter system set log_archive_start=true scope=spfile;
然后关闭数据库实例
sql>shutdown
备份数据库
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/oradata/demo/archive' scope=both;
此操作直接生效
2.创建物理的standby数据库
2.1 标记出primary数据库的数据文件
sql> select name from v$datafile;
name
-----------------------------------------------------
d:/oracle/oradata/demo ystem01.dbf
d:/oracle/oradata/demo/undotbs01.dbf
d:/oracle/oradata/demo/cwmlite01.dbf
d:/oracle/oradata/demo/drsys01.dbf
d:/oracle/oradata/demo/example01.dbf
d:/oracle/oradata/demo/indx01.dbf
d:/oracle/oradata/demo/odm01.dbf
d:/oracle/oradata/demo/tools01.dbf
d:/oracle/oradata/demo/users01.dbf
d:/oracle/oradata/demo/xdb01.dbf
d:/oracle/oradata/demo/oem_repository.dbf
11 rows selected.
sql>
2.2 关闭instance 拷贝数据文件到既定目的地
sql> shutdown immediate;
2.3 为standby 数据库创建控制文件
sql> alter database create standby controlfile
as 'c:/dataguard/pstandby/demo/demostandby.ctl';
要注意这个控制文件的名字不要和primary的控制文件名字重复
2.4 为standby数据库准备初始化参数文件名字：
sql> create pfile='c:/dataguard/pstandby/demo/initpstandby' from spfile;
2.5 设定初始化physical standby database参数
*.aq_tm_processes=1
*.background_dump_dest='d:/oracle/admin/demo/bdump'
*.compatible='9.2.0.0.0'
*.control_files='c:/dataguard/pstandby/demo/demostandby.ctl'
*.core_dump_dest='d:/oracle/admin/demo/cdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='demo'
*.dispatchers='(protocol=tcp) (service=demoxdb)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=true
*.instance_name='pstandby'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:/oracle/oradata/demo/archive'
*.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='d:/oracle/admin/demo/udump'
lock_name_space=pstandby
standby_file_management=auto
remote_archive_enable=true
standby_archive_dest='c:/dataguard/pstandby/demo/archive'
db_file_name_convert=('d:/oracle/oradata/demo', 'c:/dataguard/pstandby/demo/')
log_file_name_convert=('d:/oracle/oradata/demo', 'c:/dataguard/pstandby/demo/')
log_archive_dest_1=('location=c:/dataguard/pstandby/demo/archive')
整个操作的过程中，容易出现错误的地方几乎都集中在此处。必须认真仔细的对待这个文件。标记为黑色的地方是需要进行修改的。
2.6 创建一个windows服务
winnt> oradim -new -sid pstandby -startmode manual
2.7 create a server parameter file for the standby database
可参考执行如下操作：
c:/>set oracle_sid=pstandby
c:/>sqlplus /nolog
sql> connect / as sysdba
sql> create spfile from pfile='c:/dataguard/pstandby/demo/initpstandby.ora';
2.8 启动物理standby数据库
c:/>set oracle_sid=pstandby
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=pstandby' scope=both;
sql> alter system set log_archive_dest_state_2=enable scope=both;
2.11 启动远程归档
sql> alter system archive log current;
3.安装完的的验证：
3.1在database, 查询v$archived_log
(其实也可以直接到相关目录下查看log是否创建):
sql> select sequence#, first_time, next_time
2 from v$archived_log order by sequence#;
sequence# first_time next_time
---------- ---------- ----------
38 23-7? -02 23-7? -02
39 23-7? -02 23-7? -02
40 23-7? -02 23-7? -02
41 23-7? -02 23-7? -02
42 23-7? -02 23-7? -02
3.2 在primary数据库上，归档当前的log
sql> alter system archive log current;
3.3 验证是否收到：
sql> select sequence#, first_time, next_time
2> from v$archived_log order by sequence#;
sequence# first_time next_time
---------- ---------- ----------
38 23-7? -02 23-7? -02
39 23-7? -02 23-7? -02
40 23-7? -02 23-7? -02
41 23-7? -02 23-7? -02
42 23-7? -02 23-7? -02
43 23-7? -02 23-7? -02
3.4 验证是否新的归档redo日志已经被应用：
sql> select sequence#,applied from v$archived_log
2 order by sequence#;
sequence# app
---------- ---
38 yes
39 yes
40 yes
41 yes
42 yes
43 yes
ok.表明我们还是成功的。暂时告一段落。
参考文档
oracle data guard concepts and administration
release 2 (9.2)
part number a96653-02
附加内容：
primary数据库的pfile内容：
*.aq_tm_processes=1
*.background_dump_dest='d:/oracle/admin/demo/bdump'
*.compatible='9.2.0.0.0'
*.control_files='d:/oracle/oradata/demo/control01.ctl',
'd:/oracle/oradata/demo/control02.ctl','d:/oracle/oradata/demo/control03.ctl'
*.core_dump_dest='d:/oracle/admin/demo/cdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='demo'
*.dispatchers='(protocol=tcp) (service=demoxdb)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=true
*.instance_name='demo'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:/oracle/oradata/demo/archive'
*.log_archive_dest_2='service=pstandby'
*.log_archive_dest_state_2='enable'
*.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='d:/oracle/admin/demo/udump'
创建过程中的可能的错误：待续
本文来源于网页设计爱好者web开发社区http://www.html.org.cn收集整理，欢迎访问。
