data guard 参数设置详解

案例分析考虑切换,参数的配置是双向对立的Database  DB_UNIQUE_NAME Oracle Net Service Name  service_namesPrimary              test                        test_rac1、test_rac2      testPhysical standby    test_standby          standby_db           test_standby192.168.56.120 node1192.168.56.122 node1vip192.168.110.120 node1priv192.168.56.121 node2192.168.56.123 node2vip192.168.110.121 node2priv192.168.56.124 scanip第一：tnsnames.ora 配置switchover ,主库和备库tnsnames.ora配置相同主库两个主机：TEST =                                   (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = scanip)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test)    )  )test_rac1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.122)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test)       (INSTANCE_NAME = test1)    )  )test_rac2 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test)       (INSTANCE_NAME = test2)    )  )standby_db =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.126)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test_standby)    )  )# Net_service test is for aplication befor switchover备库tnsnames.ora：TEST =                                   (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.126)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test_standby)    )  )test_rac1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.122)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test)       (INSTANCE_NAME = test1)    )  )test_rac2 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test)       (INSTANCE_NAME = test2)    )  )standby_db =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.126)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = test_standby)    )  )# Net_service test is for aplication alter switchoverswitchover之前，rac是主库db_name=test           ------>物理备库需要保持主库和备库db_name相同service_names=test     ------>对外监听服务   默认为db_namedb_unique_name=test    ------>用于datagard主备库区别，会影响service_names,默认为db_namelog_archive_config='DG_CONFIG=(test,test_standby)'     ------>配置主备库的db_unique_namelog_archive_dest_1='LOCATION=+DATADG/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'  ------>LOCATION表示本机归档位置，VALID_FOR用于主备角色切换, ALL_LOGFILES,ALL_ROLES表示不管角色如何互换，都是对所有日志作用log_archive_dest_2='SERVICE=standby_db VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test_standby'------>log_archive_dest_n 如果配置，主库归档会同时归档这两个参数设置的目录中，相当于冗余，SERVICE配置的是net serviceFAL_SERVER='standby_db'    STANDBY_FILE_MANAGEMENT=AUTO DB_FILE_NAME_CONVERT='+DATADG/test/datafile/','/u01/app/oradata/test/data/','+DATADG/test/tempfile/','/u01/app/oradata/test/data/'LOG_FILE_NAME_CONVERT='+DATADG/test/onlinelog/','/u01/app/oradata/test/data/'log_archive_dest_state_1=enablelog_archive_dest_state_2=enableremote_login_passwordfile=EXCLUSIVESQL> alter system set log_archive_config='DG_CONFIG=(test,test_standby)' scope=spfile sid='*';SQL> alter system set log_archive_dest_1='LOCATION=+DATADG/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test' scope=spfile sid='*';SQL> alter system set log_archive_dest_2='SERVICE=standby_db VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test_standby' scope=spfile sid='*';SQL> alter system set fal_server='standby_db' scope=spfile sid='*';SQL> alter system reset fal_client scope=spfile sid='*';SQL> alter system set standby_file_management=AUTO scope=spfile sid='*';SQL> alter system reset standby_archive_dest scope=spfile sid='*';SQL> alter system set db_file_name_convert='+DATADG/test/datafile/','/u01/app/oradata/test/data/','+DATADG/test/tempfile/','/u01/app/oradata/test/data/' scope=spfile sid='*';SQL> alter system set log_file_name_convert='+DATADG/test/onlinelog/','/u01/app/oradata/test/data/' scope=spfile sid='*';SQL> ALTER DATABASE FORCE LOGGING;zhuSQL> alter system set db_file_name_convert='/u01/app/oradata/test/data/','/u01/app/oradata/test/data/TEST_STANDBY/datafile/','+DATADG/test/datafile/','+DATADG/test/tempfile/' scope=spfile sid='*';SQL> alter system set log_file_name_convert='/u01/app/oradata/test/data/TEST_STANDBY/onlinelog/','+DATADG/test/onlinelog/' scope=spfile sid='*';beiSQL> alter system set db_file_name_convert='+DATADG/test/datafile/','+DATADG/test/tempfile/','/u01/app/oradata/test/data/','/u01/app/oradata/test/data/' scope=spfile ;SQL> alter system set log_file_name_convert='+DATADG/test/onlinelog/','/u01/app/oradata/test/data/TEST_STANDBY/onlinelog/' scope=spfile ;switchover 之后，备库是主库备库端：db_name=testservice_names=test_standbydb_unique_name=test_standbylog_archive_config=’DG_CONFIG=(test_standby,test)’  log_archive_dest_1='LOCATION=/u01/app/oradata/test/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test_standby'  log_archive_dest_2='SERVICE=test_rac1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'FAL_SERVER='test_rac1'    STANDBY_FILE_MANAGEMENT=AUTO DB_FILE_NAME_CONVERT='/u01/app/oradata/test/data/','+DATADG/test/datafile/','/u01/app/oradata/test/data/','+DATADG/test/tempfile/'LOG_FILE_NAME_CONVERT='/u01/app/oradata/test/data/','+DATADG/test/onlinelog/'log_archive_dest_state_1=enablelog_archive_dest_state_2=enableremote_login_passwordfile=EXCLUSIVE---------------test_standby.__db_cache_size=230686720test_standby.__java_pool_size=4194304test_standby.__large_pool_size=4194304test_standby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmenttest_standby.__pga_aggregate_target=318767104test_standby.__sga_target=469762048test_standby.__shared_io_pool_size=0test_standby.__shared_pool_size=222298112test_standby.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/test_standby/adump'*.audit_trail='db'*.cluster_database=false*.compatible='11.2.0.4.0'*.control_files='/u01/app/oradata/test/data/control01.dbf'*.db_block_size=8192*.db_create_file_dest='/u01/app/oradata/test/data/'*.db_domain=''*.db_file_name_convert='+DATADG/test/datafile/','+DATADG/test/tempfile/','/u01/app/oradata/test/data/','/u01/app/oradata/test/data/'*.db_name='test'*.db_unique_name='test_standby'*.fal_server='test_rac1','test_rac2'*.log_archive_config='DG_CONFIG=(test_standby,test)'*.log_archive_dest_1='LOCATION=/u01/app/oradata/test/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test_standby'*.log_archive_dest_2='SERVICE=test_rac1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='test_%t_%s_%r.log'*.log_archive_start=TRUE*.log_file_name_convert='+DATADG/test/onlinelog/','/u01/app/oradata/test/data/TEST_STANDBY/onlinelog/'*.memory_target=786432000*.open_cursors=300*.processes=150*.remote_login_passwordfile='exclusive'*.service_names='test_standby'*.standby_file_management='AUTO'*.thread=1*.undo_tablespace='UNDOTBS1'---------------------------