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

首页 > Linux操作系统 > Linux操作系统 > Configuration DataGuard on Oracle 10gR2

Configuration DataGuard on Oracle 10gR2

原创 Linux操作系统 作者：lily_durham 时间：2010-03-08 00:53:53 0 删除 编辑
Configuration DataGuard on Oracle 10gR2
 
环境:
windows 2003 server
Oracle 10.2.0.3 32bit
 
10gR1的配置方法和10gR2的不太一样,需要注意看清版本...
---------------------------------------------------------------------------

版本 10g R2 !!
1. 修改参数

(1) 将primary database置于force logging
 

alter database force logging;

(2) 将primary database置于归档模式
 

shutdown immediate;
startup mount;
alter database archivelog;

(3) 在primary database创建 standby logfile group,大小必须与redolog file一致.

alter database add standby logfile group 10 ('E:\oracle\product\10.1.0\oradata\ICCPRDQA\STBY_LOG10A.LOG') size 100M;

alter database add standby logfile group 11 ('E:\oracle\product\10.1.0\oradata\ICCPRDQA\STBY_LOG11A.LOG') size 100M;

alter database add standby logfile group 12 ('E:\oracle\product\10.1.0\oradata\ICCPRDQA\STBY_LOG12A.LOG') size 100M;

alter database add standby logfile group 13 ('E:\oracle\product\10.1.0\oradata\ICCPRDQA\STBY_LOG13A.LOG') size 100M;

alter database add standby logfile group 14 ('E:\oracle\product\10.1.0\oradata\ICCPRDQA\STBY_LOG14A.LOG') size 100M;

alter database add standby logfile group 15 ('E:\oracle\product\10.1.0\oradata\ICCPRDQA\STBY_LOG15A.LOG') size 100M;

 为standby database创建密码文件和net service name以listener,这里要注意的是sys的密码要和primary database一样.其实直接将primary的pwdfile拷贝到standby的相应位置就可以了

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = iccprdqa)
      (ORACLE_HOME = E:\oracle\product\10.1.0\Db_1)
      (SID_NAME = iccprdqa)   
)
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.68.195.34)(PORT = 1521))
      )
    )
  )

 

tnsname.ora:

 hkeu_iccuata21 =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.68.195.21)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = iccuata)
     )
   )

 hkeu_iccuata22=
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.68.195.22)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = iccuata)
     )
   )


创建pfile,并拷贝到standby库相应位置

create pfile from spfile;
orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDagil.ORA password=oracle entries=10

## primary:
*.log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\oradata\ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_3='LOCATION=D:\oracle\product\10.2.0\oradata\STBY_ARCH VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES)'
*.log_archive_dest_5='SERVICE=WCISUAT_ICCNUNCLAP02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=60 MAX_FAILURE=3'
*.log_archive_dest_6='SERVICE=WCISAPP_ICCNUATAP01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=60 MAX_FAILURE=3'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_dest_state_5='ENABLE'
*.log_archive_dest_state_6='ENABLE'

## standby:
*.standby_file_management=auto
*.log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\oradata\ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_3='LOCATION=D:\oracle\product\10.2.0\oradata\STBY_ARCH VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES)'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.db_file_name_convert=('D:\oracle\oradata\clone', 'd:\dataguard\pstandby')
*.log_file_name_convert=('D:\oracle\oradata\clone', 'd:\dataguard\pstandby')

alter system set log_archive_dest_1='' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = DEFER;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = enable;

 

将 pwd文件,spfile文件,tnsnames.ora,listener.ora 拷到standby相应位置.
在standby上建立和primary上相同的目录结构:
admin/bdump
admin/cdump
admin/create
admin/pfile
admin/scripts
admin/udump


windows下:在standby 上创建实例
oradim -new -sid iccprdqa
配置listener:
netca


2.
用rman备份primary数据库

rman target /

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
#setlimit channel d1 kbytes 2097150;
#setlimit channel d2 kbytes 2097150;
#backup incremental level 0 format '/beta/home/marrocha/backup/df_%U' database
#backup incremental level 0 format 'd:\share\iccuata.stdby.fullbk_%U' database
backup full format 'e:\share\iccuataqa.stdby.fullbk_%U' database
include current controlfile for standby;
sql "alter system archive log current";
#backup
#archivelog all format '/beta/home/marrocha/backup/al_%U' delete input;
}


3. 恢复rman备份
将备份文件拷贝到standby数据库的相同位置,比如:d:\share\
在standby建立和primary一样的目录结构

rman target sys/password@primary  auxiliary sys/passwod@standby

run {
# set command id to 'standby';
# set until time "to_date('22-JUL-2000 15:34:38,'DD-MON-YYYY HH24:MI:SS')";
allocate auxiliary channel dup1 type disk;
allocate auxiliary channel dup2 type disk;
#set newname for datafile 1 to 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF';
#set newname for datafile 2 to 'D:\ORACLE\ORADATA\TEST\RBS01.DBF';
#set newname for datafile 3 to 'D:\ORACLE\ORADATA\TEST\USERS01.DBF';
#set newname for datafile 4 to 'D:\ORACLE\ORADATA\TEST\TEMP01.DBF';
#set newname for datafile 5 to 'D:\ORACLE\ORADATA\TEST\TOOLS.DBF';
#set newname for datafile 6 to 'D:\ORACLE\ORADATA\TEST\INDX01.DBF';
#set newname for datafile 7 to 'D:\ORACLE\ORADATA\TEST\DATA_01.DBF';
#set newname for datafile 8 to 'D:\ORACLE\ORADATA\TEST\RMAN_TS_01.ORA';
#set newname for datafile 27 to 'D:\ORACLE\ORADATA\TEST\RBS02.DBF';
duplicate target database for standby
dorecover
nofilenamecheck;
 }

shutdown immediate;
startup nomount;
alter database mount standby database;

--
-- standby redolog file 必须和redolog file 一样大 !!!
--
ALTER DATABASE ADD STANDBY LOGFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG10A.LOG'
 SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG10B.LOG'
                                       TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG10A.LOG';
ALTER DATABASE ADD STANDBY LOGFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG11A.LOG'
 SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG11B.LOG'
                                       TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG11A.LOG';
ALTER DATABASE ADD STANDBY LOGFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG12A.LOG'
 SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG12B.LOG'
                                       TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG12A.LOG';
ALTER DATABASE ADD STANDBY LOGFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG13A.LOG'
 SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG13B.LOG'
                                       TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG13A.LOG';
ALTER DATABASE ADD STANDBY LOGFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG14A.LOG'
 SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG14B.LOG'
                                       TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG14A.LOG';
ALTER DATABASE ADD STANDBY LOGFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG15A.LOG'
 SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE MEMBER 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG15B.LOG'
                                       TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ICCUATA\STBY_LOG15A.LOG';

-- 在备库没有online redo logfile
-- 切换的时候会自动在备库建online redo logfile的
-- 即使在建立standby redo logfile，组号也是不能重复的，也就是说，主库有1,2,3三组日志，那么备库的standby redo logfie只能从组号4开始


4. 物理standby
   先切换到readonly模式创建redolog files
（1）启动到管理模式 
 

shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

（2）如果在管理恢复模式下到只读模式 
 

recover managed standby database cancel;
alter database open read only;

 10gr2 此时会自动添加temp数据文件
 这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的） 
 ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\oracle\product\10.2.0\oradata\ICCPRDQA\TEMP01.DBF'
 SIZE 1G  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
 redolog file 
（3）启动到只读方式 
 

shutdown immediate;
startup nomount;
alter database mount standby database; alter database open read only;

 

（4）从只读方式到管理恢复方式

recover managed standby database disconnect from session;

5. 逻辑standby

-- 停止physical standby的恢复进程

alter database recover managed standby database cancel;

-- 在主库redo中生成数据字典信息
-- logical standby使用了logminer技术，需要生成数据字典信息。

exec dbms_logstdby.build

-- 归档主库当前日志，确保包含数据字典信息的归档传递到备库

alter system archive log current;

-- 将physical standby转换成logical standby
-- 此时生成redolog file

alter database recover to logical standby iccprdqa;

-- !!! would change the db_name !!!

-- 重启逻辑备库

shutdown immediate;
startup mount;

-- 打开logical standby

alter database open resetlogs;

 
-- 启动动real time sql apply

alter database stop logical standby apply;
alter database start logical standby apply immediate;

-- 在alert日志中可以发现相关的恢复进程启用成功的信息。

 


6. Switchover

-- 在primary上 
 

select switchover_status from v$database

-- switchover_status的值如果是To standby，可以直接switchover，如果是sessions active，
-- 则需要在switchover的命令后面加上with session shutdown

alter database commit to switchover to physical standby;
shutdowm immediate;
startup mount;

-- 在standby上
 select switchover_status from v$database
-- switchover_status的值如果是To primary，可以直接switchover，如果是sessions active，
-- 则需要在switchover的命令后面加上with session shutdown

alter database commit to switchover to primary;
shutdown immediate;
startup;

-- 要想顺利的实现switchover，最好在每台server上都同时设置好primary和standby的一些初始化参数，虽然其中一些参数只有在primary或者standby其中之一上起作用

7. Failover

-- 在standby上

alter database recover managed standby database finish;
or
alter database recover managed standby database finish skip standby logfile;

 

alter database commit to switchover to primary;
shutdown immediate;
startup;

-- 相关视图

v$archive_dest
v$archive_dest_status
v$log_history
v$archvied_log
v$managed_standby
v$archive_gap
V$dataguard_status

-- 检查状态
primary:
-- Checking
SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST;
standby:
-- 1. Check the status = "APPLYING"
select * FROM V$LOGSTDBY_STATE;

-- 3. Check status of with timestamp

select sequence#, timestamp, applied from dba_logstdby_log;

select sequence#, to_char(timestamp, 'yyyy-MM-dd hh:mm:ss'), applied from dba_logstdby_log;

 

 

不过还是有个问题: rman恢复后没有redolog file,看网上有人说切换的时候会自动重建.不过我好像没试出来,只有open数据库后才会重建.不知道有没有人知道这个问题,redolog究竟是在什么时候重建?能不能在恢复之后马上手工重建?
 
 

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

0
0
上一篇： redo 与undo 的通俗理解
下一篇： linux---ubutun internet connection set
请登录后发表评论 登录
全部评论
lily_durham

注册时间：2009-08-04

  • 博文量
    28
  • 访问量
    45203

最新文章

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

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

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

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