Chinaunix首页 | 论坛 | 博客

永远的ORACLE爱好者

首页　| 　博文目录　| 　关于我

lihui1127

  • 博客访问： 29316
  • 博文数量： 14
  • 博客积分： 1400
  • 博客等级： 上尉
  • 技术积分： 180
  • 用 户 组： 普通用户
  • 注册时间： 2008-11-03 12:58
文章分类

全部博文（14）

文章存档

2011年（1）

2010年（1）

2009年（12）

我的朋友
最近访客
推荐博文
相关博文
RH4下Ora9.2安装及Standby,RMAN实施(2)

分类： Oracle

2009-01-21 11:59:05

StandBy实施
环境
PKIBM6为主库,PKIBM7为从库
客户端通过IP：192.168.123.8访问数据库
配置从库
挂载主库数据文件到从库
# vi /etc/exports
/oradata/SYTSP/data/    192.168.123.7(rw)
主库上
# service portmap restart
# service nfs restart
从库
# service portmap restart
# mount -t nfs 192.168.123.6:/oradata/SYTSP/data /mnt
 
从库可以通过 /mnt 访问主库数据文件夹
建立与主库对应的文件目录
# chown oracle:dba /oradata/
# su – oracle
$ mkdir -p /oradata/SYTSP/bdump
$ mkdir -p /oradata/SYTSP/cdump
$ mkdir -p /oradata/SYTSP/udump
$ mkdir -p /oradata/SYTSP/data
$ mkdir -p /oradata/SYTSP/arch
$ mkdir -p /oradata/SYTSP/bkup
$ mkdir -p /oradata/SYTSP/logmnr
关闭主库，复制数据文件到从库
cp /mnt/* /oradata/SYTSP/data/
把主库置于归档模式下
启动主库到mount状态，检查主库是否在归档模式下
SQL>archive log list
如果不在归档模式下
SQL>alter database archivelog
SQL>alter system set log_archive_dest_1='LOCATION=/oradata/SYTSP/arch';
SQL>alter system set log_archive_format='%t_%s' scope=spfile;
SQL>alter system set log_archive_start=true scope=spfile;
SQL>shutdown immediate;
SQL>startup;
在主库上制作从库的controlfile,cp到从库
SQL>alter database create standby controlfile as '/oradata/SYTSP/data/standby1.ctl';
从库 $ cp /mnt/standby1.ctl /oradata/SYTSP/data/standby1.ctl
     $ cp /oradata/SYTSP/data/standby1.ctl /oradata/SYTSP/data/standby2.ctl
     $ cp /oradata/SYTSP/data/standby1.ctl /oradata/SYTSP/data/standby3.ctl
修改主库listener.ora tnsnames.ora
$ vi /oraapp/oracle/92/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.6)(PORT = 1688))
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = SYTSP.PKIBM.COM)
      (ORACLE_HOME = /oraapp/oracle/92)
      (SID_NAME = SYTSP)
    )
  )
 
$ vi /oraapp/oracle/92/network/admin/tnsnames.ora
PRIMARY =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.6)(PORT = 1688))
  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID = SYTSP)
  )
)
 
STANDBY =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.7)(PORT = 1688))
  )
启动监听
$ lsnrctl start
检查
$ tnsping primary
$ tnsping standby
复制主库的listener.ora tnsnames.ora到从库，并进行修改
[oracle@PKIBM6 ~]$ cp /oraapp/oracle/92/network/admin/tnsnames.ora /oradata/SYTSP/data/
[oracle@PKIBM6 ~]$ cp /oraapp/oracle/92/network/admin/listener.ora /oradata/SYTSP/data/
从库
[oracle@PKIBM7 mnt]$ cp /mnt/listener.ora /oraapp/oracle/92/network/admin/
[oracle@PKIBM7 mnt]$ cp /mnt/tnsnames.ora /oraapp/oracle/92/network/admin/
修改listener.ora中IP为从库IP
启动监听
$ lsnrctl start
检查
$ tnsping primary
$ tnsping standby
建立从库接受归档日志文件夹
$ mkdir /oradata/SYTSP/standbyarchive
准备从库的pfile
在主库上建立pfile
SQL>create pfile='/oradata/SYTSP/data/initSYTSP.ora' from spfile;
拷贝pfile到从库的$ORACLE_HOME/dbs下
[oracle@PKIBM7 ~]$ cp /mnt/initSYTSP.ora /oraapp/oracle/92/dbs
修改pfile,添加
*.CONTROL_FILES='/oradata/SYTSP/data/standby1.ctl','/oradata/SYTSP/data/standby2.ctl','/oradata/SYTSP/data/standby3.ctl'
STANDBY_ARCHIVE_DEST='/oradata/SYTSP/standbyarchive'
fal_server='PRIMARY'
fal_client='STANDBY'
standby_file_management='AUTO'  #为了后面主库添加了文件，从库可以自动处理
准备从库启动所需的密码文件
[oracle@PKIBM6 ~]$ cp /oraapp/oracle/92/dbs/orapwSYTSP /oradata/SYTSP/data/
[oracle@PKIBM7 ~]$ cp /mnt/orapwSYTSP /oraapp/oracle/92/dbs/
以standby模式打开从库 
SQL> startup nomount;
SQL> alter database mount standby database;
设置主库到从库的归档
SQL> alter system set log_archive_dest_2='SERVICE=standby MANDATORY REOPEN=60'
把从库置于恢复模式下
SQL> alter database recover managed standby database disconnect from session;
测试
在主库上插入数据 切换日志  alter system switch logfile
查看从库上 $ tail –f alter_prod.log 查看归档是否应用
如归档
Media Recovery Log /oradata/SYTSP/standbyarchive/1_45.arc
Media Recovery Log /oradata/SYTSP/standbyarchive/1_46.arc
Media Recovery Log /oradata/SYTSP/standbyarchive/1_47.arc
Media Recovery Log /oradata/SYTSP/standbyarchive/1_48.arc
Media Recovery Log /oradata/SYTSP/standbyarchive/1_49.arc
Media Recovery Log /oradata/SYTSP/standbyarchive/1_50.arc
Media Recovery Log /oradata/SYTSP/standbyarchive/1_51.arc
Media Recovery Waiting for thread 1 seq# 52
 
以只读方式打开从库查看是否同步已经生效
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
在次置从库在恢复模式下
SQL> alter database recover managed standby database disconnect from session;
主从库切换
A机 主库   B机 从库
检查保护模式
select dd.protection_mode,dd.protection_level from v$database dd;
 
检查A机是否可以做switch over
 select switchover_status from v$database;
  如果是SESSIONS ACTIVE,说明还有活动的session,需要先kill掉，只留下当前sys进程
 select sid,SERIAL# from v$session where sid>10;
  查询当前连接的session
 select distinct sid from v$mystat;
  杀掉其他session
 alter system kill session '12,14';
在A机上建立需要的归档路径
 $ mkdir -p /oradata/SYTSP/standbyarchive
修改A机的init文件
因为切换后为从库，所以需要加上从库运行方式的一些参数
SQL> create pfile='/oraapp/AInitPrimary.ora' from spfile;
$ cp /oraapp/AInitPrimary.ora /oraapp/AInitStandby.ora
修改AInitStandby
  STANDBY_ARCHIVE_DEST='/oradata/SYTSP/standbyarchive'
  fal_server='standby'
  fal_client='primary'
  standby_file_management='AUTO'
  去掉 log_archive_dest_2
*.DB_CACHE_SIZE=167772160
*.DB_DOMAIN='PKIBM.COM'
*.DB_FILE_MULTIBLOCK_READ_COUNT=16
*.DB_NAME='SYTSP'
*.DML_LOCKS=2048
*.FAST_START_MTTR_TARGET=300
*.GLOBAL_NAMES=FALSE
*.HASH_JOIN_ENABLED=TRUE
*.INSTANCE_NAME='SYTSP'
*.JAVA_POOL_SIZE=0
*.JOB_QUEUE_PROCESSES=2
*.LARGE_POOL_SIZE=25165824
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/SYTSP/arch','MANDATORY','REOPEN'
###*.log_archive_dest_2='SERVICE=standby MANDATORY REOPEN=60'
*.LOG_ARCHIVE_FORMAT='%t_%s.arc'
*.LOG_ARCHIVE_START=TRUE
*.LOG_BUFFER=3145728
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.OPEN_CURSORS=100
*.OPEN_LINKS=2
*.OPTIMIZER_MODE='CHOOSE'
*.PGA_AGGREGATE_TARGET=67108864
*.PROCESSES=200
*.QUERY_REWRITE_ENABLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='TRUSTED'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.REPLICATION_DEPENDENCY_TRACKING=TRUE
*.SGA_MAX_SIZE=268435456
*.SHARED_POOL_SIZE=50331648
*.SORT_AREA_SIZE=524288
*.STAR_TRANSFORMATION_ENABLED='FALSE'
*.TIMED_STATISTICS=TRUE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=7200
*.UNDO_TABLESPACE='UNDOTBS'
*.USER_DUMP_DEST='/oradata/SYTSP/udump'
*.UTL_FILE_DIR='/oradata/SYTSP/logmnr'
*.WORKAREA_SIZE_POLICY='AUTO'
STANDBY_ARCHIVE_DEST='/oradata/SYTSP/standbyarchive'
fal_server='standby'
fal_client='primary'
standby_file_management='AUTO'
修改B机的init文件
SQL> create pfile='/oraapp/BInitStandby.ora' from spfile;
$ cp /oraapp/BInitStandby.ora /oraapp/BInitPrimary.ora
修改BInitPrimary.ora 使其能运行在主库模式下
  log_archive_dest_2='SERVICE=primary MANDATORY REOPEN=10'
  去掉 STANDBY_ARCHIVE_DEST
       fal_server
       fal_client
       standby_file_management
切换主库  A机
  alter database commit to switchover to physical standby with session shutdown;
  A机日志最后
    Completed:ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
  B机日志最后
    MRP0:Background Media Recovery process shutdown
  这时从库已经正确收到END-of-REDO信号，可以切换
关闭A机，以刚才建立的init，以standby方式打开
  shutdown immediate;
  startup nomount pfile='/oraapp/AInitStandby.ora';
  show parameter standby_file_management;
  alter database mount standby database;
切换B机
  alter database commit to switchover to primary with session shutdown;
  检查后台日志
关闭B机，用主库方式运行
  shutdown immediate;
  startup pfile='/oraapp/BInitPrimary.ora'
检查B机日志是否能正常切换
  select sequence#,group#,status from v$log;
  alter system switch logfile;
  select sequence#,group#,status from v$log;
把A机置于恢复模式下
  alter database recover managed standby database disconnect from session;
查看A机的后台警告日志，是否应用了归档
其他
如果在SQL语句中指定了NOLOGGING，则系统不会产生归档，如果在Data Guard投入使用后发生这种情况只能重做Data Guard。在主库mount状态下，alter database FORCE LOGGING可以避免，让系统所有操作都产生日志
 
如果网络太慢或断掉，会出现在所有redo log全部写满而无法归档的情况，导致主库挂起
在这种情况下，可以对主库添加log group，同时禁止对Standby的归档
alter database set log_archive_dest_2_state='defer'  在网络好转的情况下，打开对standby的归档
alter database set log_archive_dest_2_state='enable' 前面没有传过去的日志，会自动从中断处续传
初始化参数脚本
A机作为主库启动
*.AQ_TM_PROCESSES=1
*.BACKGROUND_DUMP_DEST='/oradata/SYTSP/bdump'
*.COMPATIBLE='9.2.0.0.0'
*.CONTROL_FILES='/oradata/SYTSP/data/SYTSP_ctrl01','/oradata/SYTSP/data/SYTSP_ctrl02','/oradata/SYTSP/data/SYTSP_ctrl03'
*.CORE_DUMP_DEST='/oradata/SYTSP/cdump'
*.CURSOR_SHARING='SIMILAR'
*.DB_BLOCK_SIZE=8192
*.DB_CACHE_SIZE=167772160
*.DB_DOMAIN='PKIBM.COM'
*.DB_FILE_MULTIBLOCK_READ_COUNT=16
*.DB_NAME='SYTSP'
*.DML_LOCKS=2048
*.FAST_START_MTTR_TARGET=300
*.GLOBAL_NAMES=FALSE
*.HASH_JOIN_ENABLED=TRUE
*.INSTANCE_NAME='SYTSP'
*.JAVA_POOL_SIZE=0
*.JOB_QUEUE_PROCESSES=2
*.LARGE_POOL_SIZE=25165824
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/SYTSP/arch','MANDATORY','REOPEN'
*.log_archive_dest_2='SERVICE=standby MANDATORY REOPEN=60'
*.LOG_ARCHIVE_FORMAT='%t_%s.arc'
*.LOG_ARCHIVE_START=TRUE
*.LOG_BUFFER=3145728
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.OPEN_CURSORS=100
*.OPEN_LINKS=2
*.OPTIMIZER_MODE='CHOOSE'
*.PGA_AGGREGATE_TARGET=67108864
*.PROCESSES=200
*.QUERY_REWRITE_ENABLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='TRUSTED'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.REPLICATION_DEPENDENCY_TRACKING=TRUE
*.SGA_MAX_SIZE=268435456
*.SHARED_POOL_SIZE=50331648
*.SORT_AREA_SIZE=524288
*.STAR_TRANSFORMATION_ENABLED='FALSE'
*.TIMED_STATISTICS=TRUE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=7200
*.UNDO_TABLESPACE='UNDOTBS'
*.USER_DUMP_DEST='/oradata/SYTSP/udump'
*.UTL_FILE_DIR='/oradata/SYTSP/logmnr'
*.WORKAREA_SIZE_POLICY='AUTO'
A机作为从库启动
*.AQ_TM_PROCESSES=1
*.BACKGROUND_DUMP_DEST='/oradata/SYTSP/bdump'
*.COMPATIBLE='9.2.0.0.0'
*.CONTROL_FILES='/oradata/SYTSP/data/SYTSP_ctrl01','/oradata/SYTSP/data/SYTSP_ctrl02','/oradata/SYTSP/data/SYTSP_ctrl03'
*.CORE_DUMP_DEST='/oradata/SYTSP/cdump'
*.CURSOR_SHARING='SIMILAR'
*.DB_BLOCK_SIZE=8192
*.DB_CACHE_SIZE=167772160
*.DB_DOMAIN='PKIBM.COM'
*.DB_FILE_MULTIBLOCK_READ_COUNT=16
*.DB_NAME='SYTSP'
*.DML_LOCKS=2048
*.FAST_START_MTTR_TARGET=300
*.GLOBAL_NAMES=FALSE
*.HASH_JOIN_ENABLED=TRUE
*.INSTANCE_NAME='SYTSP'
*.JAVA_POOL_SIZE=0
*.JOB_QUEUE_PROCESSES=2
*.LARGE_POOL_SIZE=25165824
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/SYTSP/arch','MANDATORY','REOPEN'
###*.log_archive_dest_2='SERVICE=standby MANDATORY REOPEN=60'
*.LOG_ARCHIVE_FORMAT='%t_%s.arc'
*.LOG_ARCHIVE_START=TRUE
*.LOG_BUFFER=3145728
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.OPEN_CURSORS=100
*.OPEN_LINKS=2
*.OPTIMIZER_MODE='CHOOSE'
*.PGA_AGGREGATE_TARGET=67108864
*.PROCESSES=200
*.QUERY_REWRITE_ENABLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='TRUSTED'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.REPLICATION_DEPENDENCY_TRACKING=TRUE
*.SGA_MAX_SIZE=268435456
*.SHARED_POOL_SIZE=50331648
*.SORT_AREA_SIZE=524288
*.STAR_TRANSFORMATION_ENABLED='FALSE'
*.TIMED_STATISTICS=TRUE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=7200
*.UNDO_TABLESPACE='UNDOTBS'
*.USER_DUMP_DEST='/oradata/SYTSP/udump'
*.UTL_FILE_DIR='/oradata/SYTSP/logmnr'
*.WORKAREA_SIZE_POLICY='AUTO'
STANDBY_ARCHIVE_DEST='/oradata/SYTSP/standbyarchive'
fal_server='standby'
fal_client='primary'
standby_file_management='AUTO'
B机作为从库启动
*.AQ_TM_PROCESSES=1
*.BACKGROUND_DUMP_DEST='/oradata/SYTSP/bdump'
*.COMPATIBLE='9.2.0.0.0'
*.CONTROL_FILES='/oradata/SYTSP/data/standby1.ctl','/oradata/SYTSP/data/standby2.ctl','/oradata/SYTSP/data/standby3.ctl'
*.CORE_DUMP_DEST='/oradata/SYTSP/cdump'
*.CURSOR_SHARING='SIMILAR'
*.DB_BLOCK_SIZE=8192
*.DB_CACHE_SIZE=167772160
*.DB_DOMAIN='PKIBM.COM'
*.DB_FILE_MULTIBLOCK_READ_COUNT=16
*.DB_NAME='SYTSP'
*.DML_LOCKS=2048
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.FAST_START_MTTR_TARGET=300
*.GLOBAL_NAMES=FALSE
*.HASH_JOIN_ENABLED=TRUE
*.INSTANCE_NAME='SYTSP'
*.JAVA_POOL_SIZE=0
*.JOB_QUEUE_PROCESSES=2
*.LARGE_POOL_SIZE=25165824
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/SYTSP/arch','MANDATORY','REOPEN'
*.LOG_ARCHIVE_FORMAT='%t_%s.arc'
*.LOG_ARCHIVE_START=TRUE
*.LOG_BUFFER=3145728
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.OPEN_CURSORS=100
*.OPEN_LINKS=2
*.OPTIMIZER_MODE='CHOOSE'
*.PGA_AGGREGATE_TARGET=67108864
*.PROCESSES=200
*.QUERY_REWRITE_ENABLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='TRUSTED'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.REPLICATION_DEPENDENCY_TRACKING=TRUE
*.SGA_MAX_SIZE=268435456
*.SHARED_POOL_SIZE=50331648
*.SORT_AREA_SIZE=524288
*.STANDBY_ARCHIVE_DEST='/oradata/SYTSP/standbyarchive'
*.standby_file_management='AUTO'
*.STAR_TRANSFORMATION_ENABLED='FALSE'
*.TIMED_STATISTICS=TRUE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=7200
*.UNDO_TABLESPACE='UNDOTBS'
*.USER_DUMP_DEST='/oradata/SYTSP/udump'
*.UTL_FILE_DIR='/oradata/SYTSP/logmnr'
*.WORKAREA_SIZE_POLICY='AUTO'
B机作为主库启动
*.AQ_TM_PROCESSES=1
*.BACKGROUND_DUMP_DEST='/oradata/SYTSP/bdump'
*.COMPATIBLE='9.2.0.0.0'
*.CONTROL_FILES='/oradata/SYTSP/data/standby1.ctl','/oradata/SYTSP/data/standby2.ctl','/oradata/SYTSP/data/standby3.ctl'
*.CORE_DUMP_DEST='/oradata/SYTSP/cdump'
*.CURSOR_SHARING='SIMILAR'
*.DB_BLOCK_SIZE=8192
*.DB_CACHE_SIZE=167772160
*.DB_DOMAIN='PKIBM.COM'
*.DB_FILE_MULTIBLOCK_READ_COUNT=16
*.DB_NAME='SYTSP'
*.DML_LOCKS=2048
##*.fal_client='STANDBY'
##*.fal_server='PRIMARY'
*.FAST_START_MTTR_TARGET=300
*.GLOBAL_NAMES=FALSE
*.HASH_JOIN_ENABLED=TRUE
*.INSTANCE_NAME='SYTSP'
*.JAVA_POOL_SIZE=0
*.JOB_QUEUE_PROCESSES=2
*.LARGE_POOL_SIZE=25165824
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/SYTSP/arch','MANDATORY','REOPEN'
*.LOG_ARCHIVE_FORMAT='%t_%s.arc'
*.LOG_ARCHIVE_START=TRUE
*.LOG_BUFFER=3145728
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.OPEN_CURSORS=100
*.OPEN_LINKS=2
*.OPTIMIZER_MODE='CHOOSE'
*.PGA_AGGREGATE_TARGET=67108864
*.PROCESSES=200
*.QUERY_REWRITE_ENABLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='TRUSTED'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.REPLICATION_DEPENDENCY_TRACKING=TRUE
*.SGA_MAX_SIZE=268435456
*.SHARED_POOL_SIZE=50331648
*.SORT_AREA_SIZE=524288
##*.STANDBY_ARCHIVE_DEST='/oradata/SYTSP/standbyarchive'
##*.standby_file_management='AUTO'
*.STAR_TRANSFORMATION_ENABLED='FALSE'
*.TIMED_STATISTICS=TRUE
*.UNDO_MANAGEMENT='AUTO'
*.UNDO_RETENTION=7200
*.UNDO_TABLESPACE='UNDOTBS'
*.USER_DUMP_DEST='/oradata/SYTSP/udump'
*.UTL_FILE_DIR='/oradata/SYTSP/logmnr'
*.WORKAREA_SIZE_POLICY='AUTO'
log_archive_dest_2='SERVICE=primary MANDATORY REOPEN=10'
阅读(800) | 评论(0) | 转发(0) |
0

上一篇：RH4下Ora9.2安装及Standby,RMAN实施(3)

下一篇：oracle10g 资源管理器的使用

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6