10gDataGuard單機配置

1、10g Data Guard單機配置1. 強制數(shù)據(jù)庫產(chǎn)生日志。使得主數(shù)據(jù)庫的一切變化都可以寫入日志文件。SQLALTER DATABASE FORCE LOGGING;為主庫添加聯(lián)機日志SQL alter database add standby logfile /oracle/dg/redo01.log size 50m;SQL alter database add standby logfile / oracle /dg/redo02.log size 50m;SQL alter database add standby logfile / oracle /dg/redo03.log si

2、ze 50m;將主庫改為歸檔模式啟動主庫到mountSQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startup mount;ORACLE instance started.Total System Global Area 599785472 bytesFixed Size 2022600 bytesVariable Size 150995768 bytesDatabase Buffers 440401920 bytesRedo Buffers 6365184 by

3、tesDatabase mounted.查看當前是否是歸檔模式SQL select open_mode,log_mode from v$database;OPEN_MODE LOG_MODE- -MOUNTED NOARCHIVELOG開啟歸檔模式SQL alter database archivelog;Database altered.再次查看，已開啟歸檔模式SQL select open_mode,log_mode from v$database;OPEN_MODE LOG_MODE- -MOUNTED ARCHIVELOG2. 檢查主庫的密碼文件，密碼文件存放位置$ORACLE_HOM

4、E/dbs/orapw$ORACLE_SID。沒有則使用以下語句建立orapwdfile=$ORACLE_HOME/dbs/orawporclpassword=kingentries=53、添加standby logfile(也可以不加)為主數(shù)據(jù)庫添加備用聯(lián)機日志文件，這里要保證備日志文件與主庫聯(lián)機日志文件相同大小。添加備用日志文件是規(guī)則：備用日志最少應該比redo log 多一個。推薦的備重做日志數(shù)依賴于主數(shù)據(jù)庫上的線程數(shù)。 (每線程日志文件最大數(shù)目 + 1 ) * 線程數(shù) alter database add standby logfile group 4 (/oracle/dg/std_

5、redo04a.log,/oracle/dg/std_redo04b.log) size 50m, group 5 (/oracle/dg/std_redo05a.log,/oracle/dg/std_redo05b.log) size 50m, group 6 (/oracle/dg/std_redo06a.log,/oracle/dg/std_redo06b.log) size 50m, group 7 (/oracle/dg/std_redo07a.log,/oracle/dg/std_redo08b.dbf) size 50m;否則備庫在應用時報如下信息：RFS1: No standb

6、y redo logfiles createdRFS1: Archived Log: /oracle2/arch/1_30_633287861.dbf在主庫添加完standby logfile后，當主庫切換后備庫后會自動使用備庫的redo logfile，具體應用信息如下：RFS1: Successfully opened standby log 4: /oracle/dg/10g/redo04.logRFS1: Successfully opened standby log 4: /oracle/dg/10g/redo04.log3. 修改主庫的參數(shù)文件SQL create pfile=/h

7、ome/oracle/pfile/initcrl.ora from spfile;File created.oracledg pfile$ vi initcrl.ora orcl._db_cache_size=440401920orcl._java_pool_size=4194304orcl._large_pool_size=4194304orcl._shared_pool_size=142606336orcl._streams_pool_size=0*.audit_file_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/adu

8、mp*.background_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/bdump*.compatible=.0*.control_files=/home/oracle/oracle/product/10.2.0/oradata/orcl/control01.ctl,/home/oracle/oracle/product/10.2.0/oradata/orcl/control02.ctl,/home/oracle/oracle/product/10.2.0/oradata/orcl/control0

9、3.ctl*.core_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orcl*.db_unique_name=orcl #必須為每個數(shù)據(jù)庫定義唯一標識*.db_recovery_file_dest=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area*.db_recovery_file_des

10、t_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)*.job_queue_processes=10*.log_archive_config=dg_config=(orcl,orcldg) #DG_CONFIG屬性羅列同一個Data Guard中所有DB_UNIQUE_NAME，必須*.log_archive_dest_1=location=/home/oracle/dgarchive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl #歸檔文件的生成路徑，必須

11、*.log_archive_dest_2=service=orcl arch A SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl 遠程服務端的歸檔日志，必須*.log_ARCHIVE_DEST_STATE_1=ENABLE #指定參數(shù)值為ENABLE，允許redo傳輸服務傳輸redo數(shù)據(jù)到指定的路徑*.log_ARCHIVE_DEST_STATE_2=ENABLE#同上*.open_cursors=300*.pga_aggregate_target=199229440*.processes=150*.remot

12、e_login_passwordfile=EXCLUSIVE*.sga_target=598736896*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/udump*.fal_client=aux*.fal_server=orcl*.standby_file_management=AUTO #如果primary數(shù)據(jù)庫數(shù)據(jù)文件發(fā)生修改（如新建，重命名等）則按照本參數(shù)的設置在standby中做相應修改。設為AUTO表示

13、自動管理。設為MANUAL表示需要手工管理。*.standby_archive_dest=/home/oracle/dgarchive物理備份主庫oracledg pfile$ rman target sys/oracle #連接到rmanRecovery Manager: Release .0 - Production on Tue Nov 24 05:45:04 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ORCL (DBID=14243430

14、17, not open)RMAN backup database include current controlfile for standby plus archivelog; #執(zhí)行備份Starting backup at 24-NOV-15using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=156 devtype=DISKspecification does not match any archive log

15、in the recovery catalogbackup cancelled because all files were skippedFinished backup at 24-NOV-15Starting backup at 24-NOV-15using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/oracle/

16、oracle/product/10.2.0/oradata/orcl/system01.dbfinput datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/sysaux01.dbfinput datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/undotbs01.dbfinput datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/oradata

17、/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 24-NOV-15channel ORA_DISK_1: finished piece 1 at 24-NOV-15piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/backupset/2015_11_24/o1_mf_nnndf_TAG20151124T054541_c57285rs_.bkp tag=TAG20151124T054541 comment=NONEchanne

18、l ORA_DISK_1: backup set complete, elapsed time: 00:00:55channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding standby control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 24-NOV-15channel

19、 ORA_DISK_1: finished piece 1 at 24-NOV-15piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/backupset/2015_11_24/o1_mf_ncsnf_TAG20151124T054541_c5729x86_.bkp tag=TAG20151124T054541 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup

20、at 24-NOV-15準備從庫orcldg參數(shù)文件initorcldg.ora（以主庫參數(shù)文件為藍本修改）orcl._db_cache_size=440401920orcl._java_pool_size=4194304orcl._large_pool_size=4194304orcl._shared_pool_size=142606336orcl._streams_pool_size=0*.audit_file_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/adump*.background_dump_dest=/home/

21、oracle/oracle/product/10.2.0/db_1/admin/orcl/bdump*.compatible=.0*.control_files=/home/oracle/oracle/product/10.2.0/oradata/orcl/control01.ctl,/home/oracle/oracle/product/10.2.0/oradata/orcl/control02.ctl,/home/oracle/oracle/product/10.2.0/oradata/orcl/control03.ctl*.core_dump_dest=/home/ora

22、cle/oracle/product/10.2.0/db_1/admin/orcl/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orcl*.db_unique_name=orcldg*.DB_FILE_NAME_CONVERT=(F:oracleoradataorcl,D:Orcldg)*.db_recovery_file_dest=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area*.db_recovery_

23、file_dest_size=2147483648*.dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB)*.job_queue_processes=10*.log_archive_config=dg_config=(orcl,orcldg)*.log_archive_dest_1=location=G:10gArc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl*.log_archive_dest_2=service=AUX LGWR ASYNC VALID_FOR=(ONLINE_LOGFIL

24、ES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg*.log_ARCHIVE_DEST_STATE_1=ENABLE*.log_ARCHIVE_DEST_STATE_2=ENABLE*.log_archive_max_processes=4*.open_cursors=300*.pga_aggregate_target=199229440*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=598736896*.undo_management=AUTO*.undo_tablespace=UND

25、OTBS1*.user_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/orcl/udump*.fal_client=aux*.fal_server=orcl*.standby_file_management=AUTO*.standby_archive_dest=/home/oracle/dgarchive配置tns信息ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED

26、) (SERVICE_NAME = orcl) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (

27、SERVICE_NAME = orcldg) ) )對主庫進行全庫備份run allocate channel t1 type disk;allocate channel t2 type disk;backup database format /tmp/full_%s;release channel t1;release channel t2;修改備份的參數(shù)文件alter system set db_unique_name=orcldg scope=spfile;alter system set LOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orcldg) scope=

28、both; alter system set LOG_ARCHIVE_DEST_1=LOCATION=/home/oracle/oracle/oradata/orcldg/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg scope=both; alter system set LOG_ARCHIVE_DEST_2=SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl scope=both; alter syste

29、m set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; alter system set FAL_SERVER=orcl scope=both; alter system set FAL_CLIENT=orcldg scope=both; alter system set DB_FILE_NAME_CONVERT=/home/oracle/oracle/product/10.2.0/oradata/orcl,/home/oracl

30、e/oracle/oradata/orcldg/ scope=spfile; alter system set LOG_FILE_NAME_CONVERT=/home/oracle/oracle/product/10.2.0/oradata/orcl,/home/oracle/oracle/oradata/orcldg/ scope=spfile; alter system set STANDBY_FILE_MANAGEMENT=auto scope=both; 對備庫進行全庫恢復，并啟動到mount下，用pfile文件啟動。用tnsping測試是否通oracledgz backup$ tns

31、ping orcldgTNS Ping Utility for Linux: Version .0 - Production on 24-NOV-2015 18:14:35Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:/home/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DE

32、SCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg)OK (0 msec)修改備庫處于應用歸檔狀態(tài)alter database recover managed standby database disconnect from session; ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=MEMORY;ALTER SYSTEM SET log_

33、archive_dest_state_2=ENABLE SCOPE=MEMORY;參數(shù)解釋DB_NAME：注意保持同一個Data Guard中所有數(shù)據(jù)庫DB_NAME相同。DB_UNIQUE_NAME：為每一個數(shù)據(jù)庫指定一個唯一的名稱，該參數(shù)一經(jīng)指定不會再發(fā)生變化，除非主動修改。LOG_ARCHIVE_CONFIG：該參數(shù)通過DG_CONFIG屬性羅列同一個Data Guard中所有DB_UNIQUE_NAME，以逗號分隔。CONTROL_FILES：控制文件所在路徑。LOG_ARCHIVE_DEST_n：歸檔文件的生成路徑。LOG_ARCHIVE_DEST_STATE_n：指定參數(shù)值為ENA

34、BLE，允許redo傳輸服務傳輸redo數(shù)據(jù)到指定的路徑。 該參數(shù)共擁有4個屬性值，功能各不相同。REMOTE_LOGIN_PASSWORDFILE：推薦設置參數(shù)值為EXCLUSIVE或者SHARED，注意保證相同Data Guard配置中所有db服務器sys密碼相同。LOG_ARCHIVE_FORMAT：指定歸檔文件格式。LOG_ARCHIVE_MAX_PRODUCESSES：指定歸檔進程的數(shù)量(1-30)，默認值通常是4以下參數(shù)是standby角色相關的參數(shù)，在Primary數(shù)據(jù)庫的初始化參數(shù)中也需要進行設置，這樣在主庫轉(zhuǎn)備庫也能正常運行。FAL_SERVER：指定一個TNSNAMES，通常該tnsnames對應數(shù)據(jù)庫為primary角色。FAL_CLIENT：指定一個TNSNAMES ，通常該tnsnames對應數(shù)據(jù)庫為standby角色。注：FAL是Fetch Archived Log的縮寫DB_FILE_N