ITPub博客

首页 > 应用开发 > 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博客 ” ,链接:http://blog.itpub.net/202861/viewspace-785119/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    105
  • 访问量
    1173098