ITPub博客

首页 > 数据库 > Oracle > 主库RAC,备库单节点ASM的dataguard搭建

主库RAC,备库单节点ASM的dataguard搭建

Oracle 作者:lpwebnet 时间:2014-02-08 11:21:10 0 删除 编辑
易错点:
1.listener.ora & tnsnames.ora 的配置,注意写对sid_name和service_name。
2.密码文件要重新创建,并传到主备库每个节点上
准备
主库:
SQL> select member from v$logfile;
 
MEMBER 
---------------------------------------------------------------------------------------------------- 
+LOGDG/ppm/redo_a/redo09_a.log 
+LOGDG/ppm/redo_b/redo09_b.log 
+LOGDG/ppm/redo_a/redo10_a.log 
+LOGDG/ppm/redo_b/redo10_b.log 
+LOGDG/ppm/redo_a/redo11_a.log 
+LOGDG/ppm/redo_b/redo11_b.log 
+LOGDG/ppm/redo_a/redo12_a.log 
+LOGDG/ppm/redo_b/redo12_b.log 
+LOGDG/ppm/redo_a/redo13_a.log 
+LOGDG/ppm/redo_b/redo13_b.log 
+LOGDG/ppm/redo_a/redo14_a.log 
+LOGDG/ppm/redo_b/redo14_b.log 
+LOGDG/ppm/redo_a/redo15_a.log 
+LOGDG/ppm/redo_b/redo15_b.log 
+LOGDG/ppm/redo_a/redo16_a.log 
+LOGDG/ppm/redo_b/redo16_b.log 
1.创建Standby Redo Log 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('+LOGDG/ppm/redo_a/standby_redo17.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ('+LOGDG/ppm/redo_a/standby_redo18.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 19 ('+LOGDG/ppm/redo_a/standby_redo19.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ('+LOGDG/ppm/redo_a/standby_redo20.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ('+LOGDG/ppm/redo_a/standby_redo21.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 22 ('+LOGDG/ppm/redo_a/standby_redo22.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 23 ('+LOGDG/ppm/redo_a/standby_redo23.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 24 ('+LOGDG/ppm/redo_a/standby_redo24.log') SIZE 500M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 25 ('+LOGDG/ppm/redo_a/standby_redo25.log') SIZE 500M; 
用以下语句确认创建成功 
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM gv$STANDBY_LOG; 
2.在主库上tnsnames.ora修改备库的连接串,并传到备库上 
PPM = 
  (DESCRIPTION = 
    (LOAD_BALANCE = no) 
    (ADDRESS_LIST = 
        (LOAD_BALANCE = off) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 83.19.14.155)(PORT = 1521)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 83.19.14.157)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = ppm) 
    ) 
  ) 

PPM1 = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 83.19.14.155)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = ppm) 
      (INSTANCE_NAME = ppm1) 
    ) 
  ) 
PPM_standby = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 83.19.14.137)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = ppm) 
    ) 
  ) 
3.grid用户到备库的$ORACLE_HOME/network/admin(/oracle/app/grid/11.2.0/network/admin)下修改listener.ora 
SID_LIST_LISTENER = 
 (SID_LIST = 
  (SID_DESC = 
   (SID_NAME = ppm) 
   (ORACLE_HOME = /oracle/app/oracle/product/11.2.0) 
 ) 
) 
LISTENER = 
(DESCRIPTION_LIST = 
 (DESCRIPTION = 
  (ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 83.19.14.137)(PORT = 1521)(QUEUESIZE=300)) 
  ) 
 ) 
) 
4.修改主库的参数文件 
alter system set standby_file_management=auto; 
alter system set archive_lag_target=1200; 
alter system set fal_client='PPM'; 
alter system set fal_client='PPM_standby'; 
alter system set log_archive_dest_1='location=+LOGDG/ppm/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  db_unique_name=ppm'; 
alter system set log_archive_dest_state_2=defer; 
alter system set log_archive_dest_2='SERVICE=PPM_standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) reopen=60 db_unique_name=ppm_standby'; 
SQL> show parameter spfile; 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
spfile                               string      +DATA_DG/ppm/spfileppm.ora 
SQL> create pfile from spfile; 

5.开始rman备份 
准备好脚本 
$ cat ppm.sh 
rman target / cmdfile=ppm.rcv log=ppm.log 
$ cat ppm.rcv 
run{ 
allocate channel d1 type disk; 
allocate channel d2 type disk; 
setlimit channel d1 kbytes 20480000; 
setlimit channel d2 kbytes 20480000; 
backup incremental level 0 format '/mnt/ppmdg/arch_%t_%s_%U.arc' database include current controlfile for standby; 
sql "alter system archive log current"; 
release channel d1; 
release channel d2; 
} 
在后台运行 
sh ppm.sh & 
耗时7小时。 

准备备库: 

1. hdisk2-6 hdisk12-16 磁盘组名:DATA_DG     hdisk11 磁盘组名:LOGDG 
brw-------    1 root     system       18,  0 Oct 31 09:15 hdisk0 
brw-------    1 root     system       18,  1 Nov 02 01:42 hdisk1 
brw-------    1 root     system       18,  4 Dec 24 04:41 hdisk10 
brw-------    1 root     system       18,  3 Dec 24 04:41 hdisk11 
brw-------    1 root     system       18,  7 Dec 24 04:41 hdisk12 
brw-------    1 root     system       18, 12 Dec 24 04:41 hdisk13 
brw-------    1 root     system       18,  5 Dec 24 04:41 hdisk14 
brw-------    1 root     system       18, 11 Dec 24 04:41 hdisk15 
brw-------    1 root     system       18, 16 Dec 24 04:41 hdisk16 
brw-------    1 root     system       18,  2 Dec 24 04:41 hdisk2 
brw-------    1 root     system       18,  9 Dec 24 04:41 hdisk3 
brw-------    1 root     system       18, 13 Dec 24 04:41 hdisk4 
brw-------    1 root     system       18, 10 Dec 24 04:41 hdisk5 
brw-------    1 root     system       18, 14 Dec 24 04:41 hdisk6 
brw-------    1 root     system       18,  8 Dec 24 05:20 hdisk7 
brw-------    1 root     system       18, 15 Dec 24 04:41 hdisk8 
brw-------    1 root     system       18,  6 Dec 24 04:41 hdisk9 
mknod /dev/orcl/datadisk02 c 18  2; 
mknod /dev/orcl/datadisk03 c 18  9; 
mknod /dev/orcl/datadisk04 c 18 13; 
mknod /dev/orcl/datadisk05 c 18 10; 
mknod /dev/orcl/datadisk06 c 18 14; 
mknod /dev/orcl/datadisk12 c 18  7; 
mknod /dev/orcl/datadisk13 c 18 12; 
mknod /dev/orcl/datadisk14 c 18  5; 
mknod /dev/orcl/datadisk15 c 18 11; 
mknod /dev/orcl/datadisk16 c 18 16; 
mknod /dev/orcl/logdg c 18 3;   
2. asmca有部分盘看不到,dd干净 
dd if=/dev/zero of=/dev/hdisk2 count=100 
dd if=/dev/zero of=/dev/hdisk3 count=100 
dd if=/dev/zero of=/dev/hdisk4 count=100 
dd if=/dev/zero of=/dev/hdisk5 count=100 
dd if=/dev/zero of=/dev/hdisk6 count=100 
dd if=/dev/zero of=/dev/hdisk11 count=100 
dd if=/dev/zero of=/dev/hdisk12 count=100 
再重新asmca建DATA_DG和LOGDG 
3.asmcmd进入后照着主库的目录结构创建目录 

4.修改备库的参数文件initppm.ora: 
*.fal_client='PPM_standby' 
*.fal_server='PPM' 
*.db_unique_name=ppm_standby 
*.log_archive_dest_1='location=+LOGDG/ppm/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  db_unique_name=ppm_standby' 
*.log_archive_dest_2='SERVICE=PPM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) reopen=60 db_unique_name=ppm' 
*.log_archive_dest_state_2='enable' 
*.log_archive_format='%t_%s_%r.dbf' 
log_file_name_convert='+LOGDG/ppm/redo_a','+LOGDG/ppm/redo_a' 
5.生成spfile文件: 
create spfile='+DATA_DG/ppm/spfileppm.ora' from pfile; 
shutdown immediate; 
mv initppm.ora  initppm.ora.bak20131227 
vi initppm.ora 
spfile='+DATA_DG/ppm/spfileppm.ora' 
startup nomount 
6.在主库备控制文件 
backup device type disk format '/mnt/ppmdg/ctl%U' current controlfile for standby;     
7.然后在备库恢复 
restore standby controlfile from '/mnt/dmp/ppmdg/ctl2eot2556_1_1';    
startup mount
 
8.恢复数据文件 
run { 
restore database ; 
switch datafile all; 

9.等了8个小时恢完之后在主备库分别执行 
alter system set log_archive_dest_state_2 = enable; 
10.并开启实时应用,看到备库没有归档文件传过来。日志报错: 
------------------------------------------------------------ 
FAL[client, USER]: Error 16191 connecting to PPM for fetching gap sequence 
Sat Dec 28 07:58:43 2013 
Error 1017 received logging on to the standby 
------------------------------------------------------------ 
Check that the primary and standby are using a password file 
and remote_login_passwordfile is set to SHARED or EXCLUSIVE, 
and that the SYS password is same in the password files. 
      returning error ORA-16191 
------------------------------------------------------------ 
11.密码文件没统一 。在主库一节点重新生成一遍3个密码文件,并传到二节点和备库上。 重启备库。开启实时应用 
alter database recover managed standby database using current logfile disconnect from session; 
12.看到归档传ing ,最终两边查到的最大归档号一致。 切个日志两边同步。
SQL> select max(sequence#) from v$log_history; 
MAX(SEQUENCE#) 
-------------- 
          4787       
done

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

请登录后发表评论 登录
全部评论

注册时间:2012-07-21

  • 博文量
    120
  • 访问量
    846693