ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 应用开发 > IT综合 > Standby的建立及注意事项

Standby的建立及注意事项

原创 IT综合 作者：playwawa 时间：2004-12-22 10:26:56 0 删除 编辑
前几天很累，就是看厂商做standby，这两天还好所以就把它写下来了，其实也是安装很简单，维护起来就不是很知道了，因为曾经我们的standby在我手上成长了半年就在我没弄懂时夭折了，今天写下安装测试过程以备后用。[@more@]
OS : W2K AD SERVER
oracle版本: oracle 9201
主库服务器ip:192.168.1.6
备库服务器ip:192.168.1.8
oracle_home=D:/tools/oracle/ora92
oracle_sid=test
nls_lang=TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
 
1.设置主备数据库的环境变量
oracle_home=D:/tools/oracle/ora92
oracle_sid=test
nls_lang=TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
等等
 
2.将primary database 设置为archive log模式
 
SQL> shutdown immediate
数据库关闭.
数据库已卸载.
已关闭 ORACLE 执行处理.
SQL> startup mount
已启动 ORACLE 执行处理.
 
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库已挂载.
SQL> archive log list
数据库日志模式              无储存模式
可自动储存             关闭
储存目的地            D:toolsoracleora92RDBMS
最早的在线日志顺序     0
目前日志顺序           1
SQL> alter database archivelog;
 
已更改数据库.
 
SQL> archive log list
数据库日志模式              储存模式
可自动储存             关闭
储存目的地            D:toolsoracleora92RDBMS
最早的在线日志顺序     0
下一个日志顺序以储存   1
 
目前日志顺序           1
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:toolsoracleoradataarchive';
 
已更改系统.
 
SQL> alter system set log_archive_format='%t_%s.dbf' scope=spfile;
 
已更改系统.
 
SQL> alter system set log_archive_start=true scope=spfile;
 
已更改系统.
 
SQL> shutdown immediate
ORA-01109: 尚未开启此一数据库
 
 
数据库已卸载.
已关闭 ORACLE 执行处理.
SQL> startup
已启动 ORACLE 执行处理.
 
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库已挂载.
数据库已开启.
SQL> archive log list
数据库日志模式              储存模式
可自动储存             开启
储存目的地            D:toolsoracleoradataarchive
最早的在线日志顺序     0
下一个日志顺序以储存   1
 
目前日志顺序           1
 
 
3.确定数据文件及redo log
 
 
SQL> select file_name from dba_data_files
 
FILE_NAME
-----------------------------------------
 
D:TOOLSORACLEORADATATESTSYSTEM01.DBF
D:TOOLSORACLEORADATATESTUNDOTBS01.DB
D:TOOLSORACLEORADATATESTCWMLITE01.DB
D:TOOLSORACLEORADATATESTDRSYS01.DBF
D:TOOLSORACLEORADATATESTEXAMPLE01.DBF
D:TOOLSORACLEORADATATESTINDX01.DBF
D:TOOLSORACLEORADATATESTODM01.DBF
D:TOOLSORACLEORADATATESTTOOLS01.DBF
D:TOOLSORACLEORADATATESTUSERS01.DBF
D:TOOLSORACLEORADATATESTXDB01.DBF
 
已选取 10 个数据列.
 
SQL> select member from v$logfile;
 
MEMBER
-----------------------------------------
 
D:TOOLSORACLEORADATATESTREDO03.LOG
D:TOOLSORACLEORADATATESTREDO02.LOG
D:TOOLSORACLEORADATATESTREDO01.LOG
 
SQL> select name from v$tempfile;
 
NAME
-----------------------------------------
 
D:TOOLSORACLEORADATATESTTEMP01.DBF
 
 
4.备份数据文件,redo log到stansby database相应的目录
 
SQL> shutdown immediate
数据库关闭.
数据库已卸载.
已关闭 ORACLE 执行处理.
SQL>
 
 
5.在primary database上制作standby database control file
 
SQL> startup
已启动 ORACLE 执行处理.
 
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库已挂载.
数据库已开启.
SQL> alter database create standby controlfile as 'D:/tools/oracle/oradata/test/stangby.ctl';
已更改数据库.
 
 
6.Copy standby crontrol file 到standby database 相关目录 
 
7.配置主库tnsname.ora和listener.ora文件
 
# TNSNAMES.ORA Network Configuration File: D:toolsoracleora92networkadmintnsnames.ora
# Generated by Oracle configuration tools.
 
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
  
STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
 
# LISTENER.ORA Network Configuration File: D:toolsoracleora92networkadminlistener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
         (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
      )
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = D:toolsoracleora92)
      (SID_NAME = test)
    )
  )
 
 
 
8.配置standby database的tnsname.ora,listener.ora文件
 
# TNSNAMES.ORA Network Configuration File: D:toolsoracleora92networkadmintnsnames.ora
# Generated by Oracle configuration tools.
 
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
  
STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
 
 
# LISTENER.ORA Network Configuration File: D:toolsoracleora92networkadminlistener.ora
# Generated by Oracle configuration tools.
LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
         (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521))
      )
    )
  )
 
SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = D:toolsoracleora92)
      (SID_NAME = test)
    )
  )
 
 
 
9.启动主备数据库的listener
 
primary database上
 
d:>lsnrctl start
 
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 28-8月 -2004 15:04:26
 
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
 
正在激活 tnslsnr: 请稍候...
 
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
系统参数档案是 D:toolsoracleora92networkadminlistener.ora
日志讯息写入至D:toolsoracleora92networkloglistener.log
监听之处: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.6)(PORT=1521)))
 
联机至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.6)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                     LISTENER
版本                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
启动日期                28-8月 -2004 15:04:31
正常运作时间                    0 days 0 hr. 0 min. 3 sec
追踪级次               off
安管                  OFF
SNMP                      OFF
监听器参数档案   D:toolsoracleora92networkadminlistener.ora
监听器日志文件         D:toolsoracleora92networkloglistener.log
监听终止点摘要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.6)(PORT=1521)))
服务摘要...
服务 "test" 有 1 个执行处理.
  执行处理 "test", 状态 UNKNOWN, 有 1 个此服务的处理程序...
命令顺利地完成
 
 
standby database上
 
d:>lsnrctl start
 
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 28-8月 -2004 15:04:26
 
Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.
 
正在激活 tnslsnr: 请稍候...
 
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
系统参数档案是 D:toolsoracleora92networkadminlistener.ora
日志讯息写入至D:toolsoracleora92networkloglistener.log
监听之处: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.8)(PORT=1521)))
 
联机至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.8)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                     LISTENER
版本                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
启动日期                28-8月 -2004 15:04:31
正常运作时间                    0 days 0 hr. 0 min. 3 sec
追踪级次               off
安管                  OFF
SNMP                      OFF
监听器参数档案   D:toolsoracleora92networkadminlistener.ora
监听器日志文件         D:toolsoracleora92networkloglistener.log
监听终止点摘要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.8)(PORT=1521)))
服务摘要...
服务 "test" 有 1 个执行处理.
  执行处理 "test", 状态 UNKNOWN, 有 1 个此服务的处理程序...
命令顺利地完成
 
 
 
10.检查主备库的tnsname.ora的配置
 
主库上：
d:>tnsping standby
 
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 28-8月 -2004 15:11:53
 
Copyright (c) 1997 Oracle Corporation.  All rights reserved.
 
已使用的参数档案:
D:toolsoracleora92networkadminsqlnet.ora
 
 
使用 TNSNAMES 接口程序来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.1.8)(PORT=1521)
ECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (90 msec)
 
备用库上：
d:>tnsping primary
 
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 28-8月 -2004 15:11:53
 
Copyright (c) 1997 Oracle Corporation.  All rights reserved.
 
已使用的参数档案:
D:toolsoracleora92networkadminsqlnet.ora
 
 
使用 TNSNAMES 接口程序来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT=1521)
ECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (100 msec)
 
 
 
11.在主库上创建pfile,因为9i默认使用spfile
d:>sqlplus /nolog
 
SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 8月 28 15:18:02 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
SQL> conn /as sysdba
已联机.
SQL> create pfile='D:toolsoracleadmintestpfileinit.ora' from spfile;
 
已建立档案.
 
 
 
12.将建立的pfile复制到备用库的相关目录
 
 
13.修改PFILE为：
 
*.aq_tm_processes=1
*.background_dump_dest='D:toolsoracleadmintestbdump'
*.compatible='9.2.0.0.0'
*.control_files='D:toolsoracleoradatateststandby.ctl'
*.core_dump_dest='D:toolsoracleadmintestcdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='test'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=D:toolsoracleoradataarchive'
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=33554432
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:toolsoracleadmintestudump'
*.STANDBY_ARCHIVE_DEST='D:toolsoracleoradataarchive'
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.standby_file_management ='AUTO'
主要修改了
crontrol file,standby_archive_dest,fal_server,fal_client,standby_file_management几个参数,其中standby_file_management
不是必须的,设为true可让如果主库以后增加文件,备用库能自己处理,避免麻烦.
 
在备用库os上建立与pfile中对应的备用库归档路径D:toolsoracleoradataarchive
 
14.将主库上的口令文件copy至备用库
 
15.在备用库上通过oradim -new -sid test建立OracleServicetest服务并启动
 
16.启动standby
sqlplus /nolog
SQL> startup nomount pfile='D:toolsoracleadmintestpfileinit.ora';
Oracle instance started.
 
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
 
将standby置于恢复状态下
SQL>alter database recover managed standby database disconnect from session;
 
如果出现如下error:
d:>sqlplus "/as sysdba
 
SQL*Plus: Release 9.2.0.1.0 - Production on 星期日 8月 29 00:54:25 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
ERROR:
ORA-12560: TNS:协议接口程序错误
请check oracle服务有否开启,lsnrctl有否开启,查看环境变量oracle_sid是否有设置
 
17.在主库上设置到从库的归档.
SQL> alter system set log_archive_dest_2='SERVICE=standby MANDATORY REOPEN=60' scopy=spfile;
已更改系统.
其中reopen表示了如果发生归档失败后，第二次重复的时间间隔（秒），这种方式是从8i以
来一直采用的一种方式，具有比较稳定，不影响数据库的性能，如果发生网络失败等原因，
主数据库不会因为远程归档不成功而停下来。
 
 
SQL> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1        113    1048576          1 YES INACTIVE
       505408 28-8月 -04
 
         2          1        114    1048576          1 NO  CURRENT
       509031 28-8月 -04
 
         3          1        110    1048576          1 YES INACTIVE
       5

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/202861/viewspace-785119/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： automatic cold backup script
下一篇： 手工创建oracle数据库的全过程及注意事项
请登录后发表评论 登录
全部评论
playwawa
  • 博文量
    105
  • 访问量
    1196436

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593