10gDataGuard单机配置

1、10g Data Guard单机配置1. 强制数据库产生日志。使得主数据库的一切变化都可以写入日志文件。SQL>ALTER 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 standb

2、y logfile '/ oracle /dg/redo03.log' size 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 bytesD

3、atabase Buffers 440401920 bytesRedo Buffers 6365184 bytesDatabase 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_

4、MODE LOG_MODE- -MOUNTED ARCHIVELOG2. 检查主库的密码文件，密码文件存放位置$ORACLE_HOME/dbs/orapw$ORACLE_SID。没有则使用以下语句建立orapwd file='$ORACLE_HOME/dbs/orawporcl' password=king entries=53、添加standby logfile(也可以不加)为主数据库添加"备用联机日志文件"，这里要保证备日志文件与主库联机日志文件相同大小。添加备用日志文件是规则：备用日志最少应该比redo log 多一个。推

5、荐的备重做日志数依赖于主数据库上的线程数。 (每线程日志文件最大数目 + 1 ) * 线程数 alter database add standby logfile group 4 ('/oracle/dg/std_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_r

6、edo06a.log','/oracle/dg/std_redo06b.log') size 50m, group 7 ('/oracle/dg/std_redo07a.log','/oracle/dg/std_redo08b.dbf') size 50m;否则备库在应用时报如下信息：RFS1: No standby redo logfiles createdRFS1: Archived Log: '/oracle2/arch/1_30_633287861.dbf'在主库添加完standby logfile后，当主库切换后

7、备库后会自动使用备库的redo logfile，具体应用信息如下：RFS1: Successfully opened standby log 4: '/oracle/dg/10g/redo04.log'RFS1: Successfully opened standby log 4: '/oracle/dg/10g/redo04.log'3. 修改主库的参数文件SQL> create pfile='/home/oracle/pfile/initcrl.ora' from spfile;File created.oracledg pfile$

8、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/adump'*.background_dump_dest='/home/oracle/oracle/product/10

9、.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'*.cor

10、e_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_

11、recovery_area'*.db_recovery_file_dest_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 VAL

12、ID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' #归档文件的生成路径，必须*.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=E

13、NABLE#同上*.open_cursors=300*.pga_aggregate_target=199229440*.processes=150*.remote_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_c

14、lient='aux'*.fal_server='orcl'*.standby_file_management='AUTO' #如果primary数据库数据文件发生修改（如新建，重命名等）则按照本参数的设置在standby中做相应修改。设为AUTO表示自动管理。设为MANUAL表示需要手工管理。*.standby_archive_dest='/home/oracle/dgarchive'物理备份主库oracledg pfile$ rman target sys/oracle #连接到rmanRecovery Manager: Re

15、lease .0 - Production on Tue Nov 24 05:45:04 2015Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ORCL (DBID=1424343017, not open)RMAN> backup database include current controlfile for standby plus archivelog; #执行备份Starting backup at 24-NOV-15using target

16、 database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=156 devtype=DISKspecification does not match any archive log in the recovery catalogbackup cancelled because all files were skippedFinished backup at 24-NOV-15Starting backup at 24-NOV-15using chan

17、nel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/system01.dbfinput datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/oradata/orcl/sysaux01.db

18、finput 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/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/or

19、acle/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/backupset/2015_11_24/o1_mf_nnndf_TAG20151124T054541_c57285rs_.bkp tag=TAG20151124T054541 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specify

20、ing datafile(s) in backupsetincluding standby control file in backupsetincluding current SPFILE in backupsetchannel 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_1

21、1_24/o1_mf_ncsnf_TAG20151124T054541_c5729x86_.bkp tag=TAG20151124T054541 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 24-NOV-15准备从库orcldg参数文件initorcldg.ora（以主库参数文件为蓝本修改）orcl._db_cache_size=440401920orcl._java_pool_size=4194304orcl._large_pool_size=419

22、4304orcl._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/oracle/oracle/product/10.2.0/db_1/admin/orcl/bdump'*.compatible='.0'*.control_files='/home/oracle

23、/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/oracle/oracle/product/10.2.0/db_1/admin/orcl/cdump'*.db_block_size=819

24、2*.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_file_d

25、est_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 A

26、SYNC VALID_FOR=(ONLINE_LOGFILES,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=59873689

27、6*.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'*.standby_archive_dest='/home/oracle/dgarchive'

28、配置tns信息ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) )

29、 )ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (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;修改备份

30、的参数文件alter system set db_unique_name=orcldg scope=spfile;alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)' scope=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; a

31、lter system set LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both; alter system 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 sco

32、pe=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/oracle/oracle/oradata/orcldg/' scope=spfile; alter system set LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/o

33、rcl','/home/oracle/oracle/oradata/orcldg/' scope=spfile; alter system set STANDBY_FILE_MANAGEMENT=auto scope=both; 对备库进行全库恢复，并启动到mount下，用pfile文件启动。用tnsping测试是否通oracledgz backup$ tnsping orcldgTNS Ping Utility for Linux: Version .0 - Production on 24-NOV-2015 18:14:35Copyright (c)

34、 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 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE

35、_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_archive_dest_state_2='ENABLE' SCOPE=MEMORY;参数解释DB_NAME：注意保持同一个Data Guard中所有数据库DB_NAME相同。D

36、B_UNIQUE_NAME：为每一个数据库指定一个唯一的名称，该参数一经指定不会再发生变化，除非主动修改。LOG_ARCHIVE_CONFIG：该参数通过DG_CONFIG属性罗列同一个Data Guard中所有DB_UNIQUE_NAME，以逗号分隔。CONTROL_FILES：控制文件所在路径。LOG_ARCHIVE_DEST_n：归档文件的生成路径。LOG_ARCHIVE_DEST_STATE_n：指定参数值为ENABLE，允许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对应数据