10gDataGuard单机配置

1、10g Data Guard单机配置1. 强制数据库产生日志。使得主数据库的一切变化都可以写入日志文件。SQLALTER DATABASE FORCE LOGGING;为主库添加联机日志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(也可以不加)为主数据库添加备用联机日志文件，这里要保证备日志文件与主库联机日志文件相同大小。添加备用日志文件是规则：备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。 (每线程日志文件最大数目 + 1 ) * 线程数 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. 修改主库的参数文件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 #必须为每个数据库定义唯一标识*.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 #指定参数值为ENABLE，允许redo传输服务传输redo数据到指定的路径*.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数据库数据文件发生修改（如新建，重命名等）则按照本参数的设置在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; #执行备份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参数文件initorcldg.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/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;修改备份的参数文件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)修改备库处于应用归档状态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;参数解释DB_NAME：注意保持同一个Data Guard中所有数据库DB_NAME相同。DB_UNIQUE_NAME：为每一个数据库指定一个唯一的名称，该参数一经指定不会再发生变化，除非主动修改。LOG_ARCHIVE_CONFIG：该参数通过DG_CONFIG属性罗列同一个Data Guard中所有DB_UNIQUE_NAME，以逗号分隔。CONTROL_FILES：控制文件所在路径。LOG_ARCHIVE_DEST_n：归档文件的生成路径。LOG_ARCHIVE_DEST_STATE_n：指定参数值为ENA

34、BLE，允许redo传输服务传输redo数据到指定的路径。 该参数共拥有4个属性值，功能各不相同。REMOTE_LOGIN_PASSWORDFILE：推荐设置参数值为EXCLUSIVE或者SHARED，注意保证相同Data Guard配置中所有db服务器sys密码相同。LOG_ARCHIVE_FORMAT：指定归档文件格式。LOG_ARCHIVE_MAX_PRODUCESSES：指定归档进程的数量(1-30)，默认值通常是4以下参数是standby角色相关的参数，在Primary数据库的初始化参数中也需要进行设置，这样在主库转备库也能正常运行。FAL_SERVER：指定一个TNSNAMES，通常该tnsnames对应数据库为primary角色。FAL_CLIENT：指定一个TNSNAMES ，通常该tnsnames对应数据库为standby角色。注：FAL是Fetch Archived Log的缩写DB_FILE_N