oracle11g两种方法搭建ADG实施手册

1、oracle 11g ADG实施手册此次我是模拟生产环境不停库的情况下搭建adg，一般来说，搭建adg有rman备份恢复和duplicat两种方式，本文也是根据这两种方式搭建并做一个对比，仅供用户借鉴。一：实验环境介绍虚拟机系统：    RHEL Linux 6.5（64位）数据库版本：    Oracle 11gR2 11.2.0.4 （64位）IP地址规划：主数据库：192.168.38.162   备库1：192.168.38.163备库2：192.168.38.166  二：开

2、始搭建Dataguard1：将数据库改为强制日志模式            （此步骤只在主库上做）SQL> select name, log_mode, force_logging from v$database;NAME LOG_MODE FOR- - -ORCL ARCHIVELOG YES 我这里已经是强制日志模式了，如果不是，需要开启SQL> alter database force logging;Database altered.  

3、;2.一般来说生产库都是开启归档的，如果没开启，则需要重启库才能开启归档SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 830930944 bytesFixed Size 2217912 bytesVariable Size 641730632 bytesDatabase Buffers 180355072 bytesRedo

4、Buffers 6627328 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered. 2：配置网络服务名配置文件tnsnames.ora主库： oracleprimary admin$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/oracleprimary admin$ cat tnsnames.oraorcl = (DESCRIP

5、TION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.38.162)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )orcl_pri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.38.162)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (SERVER = DEDICATED) )or

6、cl_sty1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.38.163)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (SERVER = DEDICATED) )备库1(192.168.38.163)：oraclestandby1 admin$ cat tnsnames.ora orcl = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1

7、92.168.38.162)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )orcl_pri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.38.162)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (SERVER = DEDICATED) )orcl_sty1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOC

8、OL = TCP)(HOST = 192.168.38.163)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) (SERVER = DEDICATED) )启动备库1监听lsnrctl start配置完后，确保任意一端都可以tnsping通自己和对方 主库:备库1:3：主库创建standby redolog日志组 -查看现有的备份文件 SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;-查看日志组状态-主库添加 standby redo log

9、：大小和 online redo 相同，比 online redo group 多一组。SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;Database altered.SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;Database altere

10、d.SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;Database altered.SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;Database altered.-再次查看：SQL> select GROUP#,STATUS,TYPE,

11、MEMBER from v$logfile;-查看备机日志生成方式，建议改为自动方式SQL> alter system set standby_file_management=auto scope=both;4：把主库的密码文件拷贝到备库，主备库的密码文件要相同：先把密码文件放在本地，待会一起拷贝过去 ps：正常来说密码文件都是有的，如果没有就生成一个:oracle primary$ cd $ORACLE_HOME/dbs/oracle primary$ orapwd file=orapworcl password=oracle force=y5.主库修改参数：SQL>

12、alter system set fal_client=orcl_pri scope=both; System altered.SQL> alter system set fal_server=orcl_sty1 scope=both;System altered. -配置本地归档和远程归档alter system set log_archive_config=' DG_CONFIG=(orcl_pri,orcl_sty1)'alter system set log_archive_dest_state_1=enable;alter system set log

13、_archive_dest_state_2=enable;alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_pri'alter system set log_archive_dest_2='service=orcl_sty1 LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=orcl_s

14、ty1'ps:在搭建adg的时候还有两个参数要注意主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），如果有多个对映，逐一指明对映关系。一对一映射设定，在主库spfile配置文件中，第一个路径是备库的路径，第二个路径是主库的路径，这点是比较特别的，和常规的情况是相反的；在备库spfile配置文件中，第一个路径是主库的路径，第二个路径是备库的路径，这点是比较特别的，和常规的情况是相反的；db_file_name_convert主数据库和备用数据库的日志文件转换目录对映，主数据库和备用数据库的日志文件转换目录对映（如果两数据库的目录结构不一样），如果有多个对映，逐一

15、指明对映关系。一对一映射设定，在主库spfile配置文件中，第一个路径是备库的路径，第二个路径是主库的路径，这点是比较特别的，和常规的情况是相反的；在备库spfile配置文件中，第一个路径是主库的路径，第二个路径是备库的路径，这点是比较特别的，和常规的情况是相反的；log_file_name_convert我的测试环境是克隆出来的，所以不指定也没关系6主库生成pfile文件SQL> create pfile='/shm/init.ora' from spfile;File created. 创建standby的控制文件SQL> alter database crea

16、te standby controlfile as '/shm/standby.ctl'Database altered.  RMAN 备份数据库，注意磁盘空间大小oracleprimary admin$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 28 13:09:02 2020Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to

17、target database: ORCL (DBID=1556910020)RMAN> run 2> allocate channel c1 type disk;3> allocate channel c2 type disk;4> allocate channel c3 type disk;5> allocate channel c4 type disk;6> backup database format '/shm/FULL_%U.bak'7> backup archivelog all format '/shm/ARC_

18、%U.bak'8> release channel c1;9> release channel c2;10> release channel c3;11> release channel c4;12> using target database control file instead of recovery catalogallocated channel: c1channel c1: SID=41 device type=DISKallocated channel: c2channel c2: SID=42 device type=DISKalloca

19、ted channel: c3channel c3: SID=43 device type=DISKallocated channel: c4channel c4: SID=44 device type=DISKStarting backup at 28-DEC-20channel c1: starting full datafile backup setchannel c1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system

20、01.dbfchannel c1: starting piece 1 at 28-DEC-20channel c2: starting full datafile backup setchannel c2: specifying datafile(s) in backup setinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfcha

21、nnel c2: starting piece 1 at 28-DEC-20channel c3: starting full datafile backup setchannel c3: specifying datafile(s) in backup setinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/test01.dbfchannel c3:

22、starting piece 1 at 28-DEC-20channel c4: starting full datafile backup setchannel c4: specifying datafile(s) in backup setincluding current control file in backup setchannel c4: starting piece 1 at 28-DEC-20channel c3: finished piece 1 at 28-DEC-20piece handle=/shm/FULL_0svj787n_1_1.bak tag=TAG20201

23、228T130911 comment=NONEchannel c3: backup set complete, elapsed time: 00:00:04channel c3: starting full datafile backup setchannel c3: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel c3: starting piece 1 at 28-DEC-20channel c4: finished piece 1 at 28-DEC-20piece han

24、dle=/shm/FULL_0tvj787n_1_1.bak tag=TAG20201228T130911 comment=NONEchannel c4: backup set complete, elapsed time: 00:00:03channel c3: finished piece 1 at 28-DEC-20piece handle=/shm/FULL_0uvj787r_1_1.bak tag=TAG20201228T130911 comment=NONEchannel c3: backup set complete, elapsed time: 00:00:01channel

25、c1: finished piece 1 at 28-DEC-20piece handle=/shm/FULL_0qvj787n_1_1.bak tag=TAG20201228T130911 comment=NONEchannel c1: backup set complete, elapsed time: 00:00:19channel c2: finished piece 1 at 28-DEC-20piece handle=/shm/FULL_0rvj787n_1_1.bak tag=TAG20201228T130911 comment=NONEchannel c2: backup se

26、t complete, elapsed time: 00:00:20Finished backup at 28-DEC-20Starting backup at 28-DEC-20current log archivedchannel c1: starting archived log backup setchannel c1: specifying archived log(s) in backup setinput archived log thread=1 sequence=7 RECID=1 STAMP=1042483030input archived log thread=1 seq

27、uence=8 RECID=2 STAMP=1042483031input archived log thread=1 sequence=9 RECID=3 STAMP=1042483033input archived log thread=1 sequence=10 RECID=4 STAMP=1042483033input archived log thread=1 sequence=11 RECID=5 STAMP=1042483034input archived log thread=1 sequence=12 RECID=6 STAMP=1042562640input archive

28、d log thread=1 sequence=13 RECID=7 STAMP=1042574575input archived log thread=1 sequence=14 RECID=8 STAMP=1042574660input archived log thread=1 sequence=15 RECID=9 STAMP=1042576155input archived log thread=1 sequence=16 RECID=10 STAMP=1042627676input archived log thread=1 sequence=17 RECID=11 STAMP=1

29、042627676input archived log thread=1 sequence=18 RECID=12 STAMP=1042627677input archived log thread=1 sequence=19 RECID=13 STAMP=1042627677input archived log thread=1 sequence=20 RECID=14 STAMP=1042627859input archived log thread=1 sequence=21 RECID=15 STAMP=1042714390input archived log thread=1 seq

30、uence=22 RECID=16 STAMP=1043139743input archived log thread=1 sequence=23 RECID=17 STAMP=1043141542input archived log thread=1 sequence=24 RECID=18 STAMP=1043581782input archived log thread=1 sequence=25 RECID=19 STAMP=1043583547input archived log thread=1 sequence=26 RECID=20 STAMP=1043596759channe

31、l c1: starting piece 1 at 28-DEC-20channel c2: starting archived log backup setchannel c2: specifying archived log(s) in backup setinput archived log thread=1 sequence=27 RECID=21 STAMP=1043661699input archived log thread=1 sequence=28 RECID=22 STAMP=1043661701input archived log thread=1 sequence=29

32、 RECID=23 STAMP=1043697644input archived log thread=1 sequence=30 RECID=24 STAMP=1043877620input archived log thread=1 sequence=31 RECID=25 STAMP=1044184107input archived log thread=1 sequence=32 RECID=26 STAMP=1044197330input archived log thread=1 sequence=33 RECID=27 STAMP=1044304161input archived

33、 log thread=1 sequence=34 RECID=28 STAMP=1046174538input archived log thread=1 sequence=35 RECID=29 STAMP=1046174541channel c2: starting piece 1 at 28-DEC-20channel c3: starting archived log backup setchannel c3: specifying archived log(s) in backup setinput archived log thread=1 sequence=36 RECID=3

34、0 STAMP=1046174545input archived log thread=1 sequence=37 RECID=31 STAMP=1046192427input archived log thread=1 sequence=38 RECID=32 STAMP=1047229608input archived log thread=1 sequence=39 RECID=33 STAMP=1047230082input archived log thread=1 sequence=40 RECID=34 STAMP=1047230396input archived log thr

35、ead=1 sequence=41 RECID=35 STAMP=1047230431input archived log thread=1 sequence=42 RECID=36 STAMP=1047232197input archived log thread=1 sequence=43 RECID=37 STAMP=1047243661input archived log thread=1 sequence=44 RECID=38 STAMP=1048433990input archived log thread=1 sequence=45 RECID=39 STAMP=1056820

36、823input archived log thread=1 sequence=46 RECID=40 STAMP=1056820842input archived log thread=1 sequence=47 RECID=41 STAMP=1056820846input archived log thread=1 sequence=48 RECID=42 STAMP=1056820846input archived log thread=1 sequence=49 RECID=43 STAMP=1056820846input archived log thread=1 sequence=

37、50 RECID=44 STAMP=1056823228input archived log thread=1 sequence=51 RECID=45 STAMP=1056823231input archived log thread=1 sequence=52 RECID=46 STAMP=1056969674input archived log thread=1 sequence=53 RECID=47 STAMP=1056970116input archived log thread=1 sequence=54 RECID=48 STAMP=1056975081input archiv

38、ed log thread=1 sequence=55 RECID=49 STAMP=1056976279input archived log thread=1 sequence=56 RECID=50 STAMP=1056986268channel c3: starting piece 1 at 28-DEC-20channel c4: starting archived log backup setchannel c4: specifying archived log(s) in backup setinput archived log thread=1 sequence=57 RECID

39、=51 STAMP=1056986269input archived log thread=1 sequence=58 RECID=52 STAMP=1056986270input archived log thread=1 sequence=59 RECID=53 STAMP=1056986414input archived log thread=1 sequence=60 RECID=54 STAMP=1056987111input archived log thread=1 sequence=61 RECID=55 STAMP=1056987604input archived log t

40、hread=1 sequence=62 RECID=56 STAMP=1056988437input archived log thread=1 sequence=63 RECID=57 STAMP=1056990471input archived log thread=1 sequence=64 RECID=58 STAMP=1056990473input archived log thread=1 sequence=65 RECID=59 STAMP=1056990474input archived log thread=1 sequence=66 RECID=60 STAMP=10569

41、90480input archived log thread=1 sequence=67 RECID=61 STAMP=1056990677input archived log thread=1 sequence=68 RECID=62 STAMP=1056990680input archived log thread=1 sequence=69 RECID=72 STAMP=1057220251input archived log thread=1 sequence=70 RECID=73 STAMP=1057220254input archived log thread=1 sequenc

42、e=71 RECID=76 STAMP=1058121459input archived log thread=1 sequence=72 RECID=77 STAMP=1058121459input archived log thread=1 sequence=73 RECID=78 STAMP=1058121501input archived log thread=1 sequence=74 RECID=79 STAMP=1058121502input archived log thread=1 sequence=75 RECID=80 STAMP=1058121505input arch

43、ived log thread=1 sequence=76 RECID=87 STAMP=1058127150input archived log thread=1 sequence=77 RECID=88 STAMP=1058127168input archived log thread=1 sequence=78 RECID=90 STAMP=1058127183input archived log thread=1 sequence=79 RECID=95 STAMP=1058127471input archived log thread=1 sequence=80 RECID=92 S

44、TAMP=1058127451input archived log thread=1 sequence=81 RECID=93 STAMP=1058127452input archived log thread=1 sequence=82 RECID=94 STAMP=1058127454input archived log thread=1 sequence=83 RECID=96 STAMP=1058127516input archived log thread=1 sequence=84 RECID=97 STAMP=1058127557input archived log thread

45、=1 sequence=85 RECID=98 STAMP=1058127568input archived log thread=1 sequence=86 RECID=99 STAMP=1058127735channel c4: starting piece 1 at 28-DEC-20channel c4: finished piece 1 at 28-DEC-20piece handle=/shm/ARC_12vj788d_1_1.bak tag=TAG20201228T130932 comment=NONEchannel c4: backup set complete, elapse

46、d time: 00:00:03channel c4: starting archived log backup setchannel c4: specifying archived log(s) in backup setinput archived log thread=1 sequence=87 RECID=100 STAMP=1058127738input archived log thread=1 sequence=88 RECID=101 STAMP=1058127753input archived log thread=1 sequence=89 RECID=102 STAMP=

47、1058127756input archived log thread=1 sequence=90 RECID=107 STAMP=1058128759input archived log thread=1 sequence=91 RECID=108 STAMP=1058128759input archived log thread=1 sequence=92 RECID=109 STAMP=1058129410input archived log thread=1 sequence=93 RECID=110 STAMP=1058129412input archived log thread=

48、1 sequence=94 RECID=111 STAMP=1058129428input archived log thread=1 sequence=95 RECID=112 STAMP=1058129584input archived log thread=1 sequence=96 RECID=113 STAMP=1058130779input archived log thread=1 sequence=97 RECID=114 STAMP=1058130899input archived log thread=1 sequence=98 RECID=115 STAMP=105813

49、0947input archived log thread=1 sequence=99 RECID=116 STAMP=1058130950input archived log thread=1 sequence=100 RECID=118 STAMP=1058131046input archived log thread=1 sequence=101 RECID=123 STAMP=1058131211input archived log thread=1 sequence=102 RECID=126 STAMP=1058131412input archived log thread=1 s

50、equence=103 RECID=127 STAMP=1058131427input archived log thread=1 sequence=104 RECID=129 STAMP=1058131755input archived log thread=1 sequence=105 RECID=131 STAMP=1058131758input archived log thread=1 sequence=106 RECID=133 STAMP=1058131760input archived log thread=1 sequence=107 RECID=135 STAMP=1058

51、132571input archived log thread=1 sequence=108 RECID=136 STAMP=1058132574input archived log thread=1 sequence=109 RECID=137 STAMP=1058132574input archived log thread=1 sequence=110 RECID=138 STAMP=1058132578input archived log thread=1 sequence=111 RECID=139 STAMP=1058132578input archived log thread=

52、1 sequence=112 RECID=167 STAMP=1058605932input archived log thread=1 sequence=113 RECID=168 STAMP=1058605934input archived log thread=1 sequence=114 RECID=169 STAMP=1058605935input archived log thread=1 sequence=115 RECID=170 STAMP=1058605937input archived log thread=1 sequence=116 RECID=181 STAMP=1

53、058616017channel c4: starting piece 1 at 28-DEC-20channel c1: finished piece 1 at 28-DEC-20piece handle=/shm/ARC_0vvj788d_1_1.bak tag=TAG20201228T130932 comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11channel c1: starting archived log backup setchannel c1: specifying archived log(

54、s) in backup setinput archived log thread=1 sequence=117 RECID=182 STAMP=1059935143input archived log thread=1 sequence=118 RECID=183 STAMP=1059935143input archived log thread=1 sequence=119 RECID=187 STAMP=1059935362input archived log thread=1 sequence=120 RECID=190 STAMP=1059935449input archived l

55、og thread=1 sequence=121 RECID=191 STAMP=1059935452input archived log thread=1 sequence=122 RECID=198 STAMP=1060344059input archived log thread=1 sequence=123 RECID=197 STAMP=1060344057input archived log thread=1 sequence=124 RECID=199 STAMP=1060347410input archived log thread=1 sequence=125 RECID=2

56、00 STAMP=1060347413input archived log thread=1 sequence=126 RECID=201 STAMP=1060348172channel c1: starting piece 1 at 28-DEC-20channel c2: finished piece 1 at 28-DEC-20piece handle=/shm/ARC_10vj788d_1_1.bak tag=TAG20201228T130932 comment=NONEchannel c2: backup set complete, elapsed time: 00:00:11cha

57、nnel c3: finished piece 1 at 28-DEC-20piece handle=/shm/ARC_11vj788d_1_1.bak tag=TAG20201228T130932 comment=NONEchannel c3: backup set complete, elapsed time: 00:00:11channel c4: finished piece 1 at 28-DEC-20piece handle=/shm/ARC_13vj788h_1_1.bak tag=TAG20201228T130932 comment=NONEchannel c4: backup set complete, elapsed time: 00:00:07channel c1: finished piece 1 at 28-DEC-20piece han