oracle11g兩種方法搭建ADG實(shí)施手冊(cè)

1、oracle 11g ADG實(shí)施手冊(cè)此次我是模擬生產(chǎn)環(huán)境不停庫的情況下搭建adg，一般來說，搭建adg有rman備份恢復(fù)和duplicat兩種方式，本文也是根據(jù)這兩種方式搭建并做一個(gè)對(duì)比，僅供用戶借鑒。一：實(shí)驗(yàn)環(huán)境介紹虛擬機(jī)系統(tǒng)：    RHEL Linux 6.5（64位）數(shù)據(jù)庫版本：    Oracle 11gR2 11.2.0.4 （64位）IP地址規(guī)劃：主數(shù)據(jù)庫：192.168.38.162   備庫1：192.168.38.163備庫2：192.168.38.166  二：開

2、始搭建Dataguard1：將數(shù)據(jù)庫改為強(qiáng)制日志模式            （此步驟只在主庫上做）SQL> select name, log_mode, force_logging from v$database;NAME LOG_MODE FOR- - -ORCL ARCHIVELOG YES 我這里已經(jīng)是強(qiáng)制日志模式了，如果不是，需要開啟SQL> alter database force logging;Database altered.  

3、;2.一般來說生產(chǎn)庫都是開啟歸檔的，如果沒開啟，則需要重啟庫才能開啟歸檔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：配置網(wǎng)絡(luò)服務(wù)名配置文件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) )啟動(dòng)備庫1監(jiān)聽lsnrctl start配置完后，確保任意一端都可以tnsping通自己和對(duì)方 主庫:備庫1:3：主庫創(chuàng)建standby redolog日志組 -查看現(xiàn)有的備份文件 SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;-查看日志組狀態(tài)-主庫添加 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;-查看備機(jī)日志生成方式，建議改為自動(dòng)方式SQL> alter system set standby_file_management=auto scope=both;4：把主庫的密碼文件拷貝到備庫，主備庫的密碼文件要相同：先把密碼文件放在本地，待會(huì)一起拷貝過去 ps：正常來說密碼文件都是有的，如果沒有就生成一個(gè):oracle primary$ cd $ORACLE_HOME/dbs/oracle primary$ orapwd file=orapworcl password=oracle force=y5.主庫修改參數(shù)：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. -配置本地歸檔和遠(yuǎn)程歸檔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的時(shí)候還有兩個(gè)參數(shù)要注意主數(shù)據(jù)庫和備用數(shù)據(jù)庫的數(shù)據(jù)文件轉(zhuǎn)換目錄對(duì)映（如果兩數(shù)據(jù)庫的目錄結(jié)構(gòu)不一樣），如果有多個(gè)對(duì)映，逐一指明對(duì)映關(guān)系。一對(duì)一映射設(shè)定，在主庫spfile配置文件中，第一個(gè)路徑是備庫的路徑，第二個(gè)路徑是主庫的路徑，這點(diǎn)是比較特別的，和常規(guī)的情況是相反的；在備庫spfile配置文件中，第一個(gè)路徑是主庫的路徑，第二個(gè)路徑是備庫的路徑，這點(diǎn)是比較特別的，和常規(guī)的情況是相反的；db_file_name_convert主數(shù)據(jù)庫和備用數(shù)據(jù)庫的日志文件轉(zhuǎn)換目錄對(duì)映，主數(shù)據(jù)庫和備用數(shù)據(jù)庫的日志文件轉(zhuǎn)換目錄對(duì)映（如果兩數(shù)據(jù)庫的目錄結(jié)構(gòu)不一樣），如果有多個(gè)對(duì)映，逐一

15、指明對(duì)映關(guān)系。一對(duì)一映射設(shè)定，在主庫spfile配置文件中，第一個(gè)路徑是備庫的路徑，第二個(gè)路徑是主庫的路徑，這點(diǎn)是比較特別的，和常規(guī)的情況是相反的；在備庫spfile配置文件中，第一個(gè)路徑是主庫的路徑，第二個(gè)路徑是備庫的路徑，這點(diǎn)是比較特別的，和常規(guī)的情況是相反的；log_file_name_convert我的測(cè)試環(huán)境是克隆出來的，所以不指定也沒關(guān)系6主庫生成pfile文件SQL> create pfile='/shm/init.ora' from spfile;File created. 創(chuàng)建standby的控制文件SQL> alter database crea

16、te standby controlfile as '/shm/standby.ctl'Database altered.  RMAN 備份數(shù)據(jù)庫，注意磁盤空間大小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