Oracle_DataGuard的实现和配置.doc

Data Guard的实现从库为physical standby操作系统:RedHat AS 3Oracle: 限于条件,在一台机器上实现DataGuard.Primary SID: reddbStandby SID: reddbstd其中主库的数据文件目录是:/oracle/oradata/reddb设计备库的数据文件目录是:/oracle/oradata/reddb_stdby_一 Primary的准备1 确认主数据库为归档模式 sqlarchive log list; 如果是非归档模式,执行如下:sqlalter system set log_archive_start=true scope=spfile;sqlshutdown immediatesqlstartup mountsqlalter database archivelog;sqlalter database open;2 确认主库处于Force logging模式sqlALTER DATABASE FORCE LOGGING;说明:对于Direct Insert,有可能不会产生redo,这样如果直接写入数据到主库,而由于不产生日志,那么就会导致备库的数据与主库不一致.3 创建主库的本地归档目录在/oracle/oradata/reddb下创建local_arch目录,作为主库的本地归档目录4 设置主库的本地归档目录sqlalter system set log_archive_dest_1=location=/oracle/oradata/reddb/local_arch scope=spfile;sqlalter system set log_archive_dest_state_1=enable scope=spfile;sqlshutdown immediatesqlstartup说明:在设置了数据库的归档模式后,整个数据库就进入了归档的模式;如果尚未设置log_archive_start=true,则数据库处于手工归档状态,如果设置了此参数,则处于自动归档状态;在自动归档状态下,如果没有设置log_archive_dest,则Oracle会选择一个默认目录作为归档目的地;如果设置了log_archive_dest,则不能再增设一个log_archive_dest_n;因此对于DG由于至少需要两个归档目的地(本地和远端),那么就不能设置log_archive_dest.5 测试本地归档sqlalter system switch logfil;通过切换,会发现在 /oracle/oradata/reddb/arch下能生成归档日志.6 建立主库的备用归档路径在/oracle/oradata/reddb下创建stdby_arch目录,作为主库的备用归档目录;当主库切换为备库时,其接收的归档日志存放于此目录下.7为了能是主备进行切换,需要将主库的参数文件进行修改,添加如下:LOG_ARCHIVE_DEST_2=SERVICE=reddbstdLOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_ARCHIVE_ENABLE=trueFAL_SERVER=reddbstdFAL_CLIENT=reddbDB_FILE_NAME_CONVERT=(/oracle/oradata/reddb_stdby/,/oracle/oradata/reddb/)LOG_FILE_NAME_CONVERT=(/oracle/oradata/reddb_stdby/,/oracle/oradata/reddb/)STANDBY_ARCHIVE_DEST/oracle/oradata/reddb/stdby_archSTANDBY_FILE_MANAGEMENT=AUTO二 备库的创建1 创建备库的数据文件所在的目录在/oracle/oradata下执行:mkdir reddb_stdby2 冷备份数据文件sqlshutdown$cp /oracle/oradata/reddb/*.dbf /oracle/oradata/reddb_stdby/$cp /oracle/oradata/reddb/*.log /oracle/oradata/reddb_stdby/sqlstartup3 生成备库的控制文件sql ALTER DATABASE CREATE STANDBY CONTROLFILE AS /oracle/oradata/reddb_stdby/control_std.ctl;此处的控制文件名应该与主库的不一样;另外,生成控制文件的步骤应该在备份数据文件后面.4 生成备库的参数文件SQL CREATE PFILE=/oracle/oradata/reddb_stdby/initreddbstd.ora FROM SPFILE;5 建立备库的本地归档路径在/oracle/oradata/reddb_stdby/下建立arch目录6 建立备库的DUMP目录在/oracle/oradata/reddb_stdby/下建立bdumpcdumpudump等目录7修改从库的参数文件这个参数文件是从主库直接copy过来的,有不合适的地方需要修改.*.background_dump_dest=/oracle/admin/reddb/bdump修改为*.background_dump_dest=/oracle/oradata/reddb_stdby/bdump*.compatible=.0*.control_files=/oracle/oradata/reddb/control01.ctl,/oracle/oradata/reddb/control02.ctl,/oracle/oradata/reddb/control03.ctl修改为:*.control_files=/oracle/oradata/reddb_stdby/control_std.ctl *.core_dump_dest=/oracle/admin/reddb/cdump修改为:*.core_dump_dest=/oracle/oradata/reddb_stdby/cdump*.db_block_size=8192*.db_cache_size=25165824*.db_domain=*.db_file_multiblock_read_count=16*.db_name=reddb*.fast_start_mttr_target=300*.hash_join_enabled=TRUE*.instance_name=reddb修改为:*.db_name=reddbstd(实例名)*.java_pool_size=0*.large_pool_size=8388608*.log_archive_dest_1=location=/oracle/oradata/reddb/local_arch修改为:*.log_archive_dest_1=location=/oracle/oradata/reddb_stdby/local_arch*.log_archive_dest_state_1=ENABLE*.log_archive_start=TRUE*.open_cursors=300*.pga_aggregate_target=25165824*.processes=150*.query_rewrite_enabled=FALSE*.remote_login_passwordfile=EXCLUSIVE*.shared_pool_size=83886080*.sort_area_size=524288*.star_transformation_enabled=FALSE*.timed_statistics=TRUE*.undo_management=AUTO*.undo_retention=10800*.undo_tablespace=UNDOTBS1*.user_dump_dest=/oracle/admin/reddb/udump修改为:*.user_dump_dest=/oracle/oradata/reddb_stdby/udump需要增加的参数:standby_archive_dest= /oracle/oradata/reddb_stdby/stdby_arch/#Specify the location of the archived redo logs that#will be received from the primary database.#这个参数是指从db_file_name_convert=( /oracle/oradata/reddb/, /oracle/oradata/reddb_stdby /)log_file_name_convert=( /oracle/oradata/reddb/, /oracle/oradata/reddb_stdby /)log_archive_format=%t_%s.dbflog_archive_dest_1=(LOCATION=/oracle/oradata/reddb_stdby/local_arch) log_archive_dest_1 - Specify the location where the redo logs are to bearchived on the standby system. (If a switchover occurs and this instancebecomes the primary database, then this parameter will specify the locationwhere the online redo logs will be archived.)standby_file_management=AUTOremote_archive_enable=TRUElock_name_space=reddbstd为了是备库能切换为主库的角色,增加参数如下:FAL_SERVER=reddbFAL_CLIENT=reddbstdLOG_ARCHIVE_DEST_2=SERVICE=reddbLOG_ARCHIVE_DEST_STATE_2=ENABLE三 启动从库生成口令文件:$/oracle/product//bin/orapwd file=/oracle/product//dbs/orapwreddbstd password=sys$export ORACLE_SID=reddbstd$sqlplus “/ as sysdba”sqlstartup nomount pfile=/oracle/oradata/reddb_stdby/initreddbstd.orasqlcreate spfile from pfile=/oracle/oradata/reddb_stdby/initreddbstd.orasqlshutdownsqlexit$sqlplus “/ as sysdba”sqlstartup nomountsqlalter database mount standby database;sqlalter database recover managed standby databasedisconnect from session;四 关闭从库1 先将主库对应的远程归档路径置为无效 主库操作: sqlalter system set log_archive_dest_state_1=defer scope=memory; 2 如果从库正在进行recover,则取消之从库操作,查看是否有MRP0或 MRP进程SQL SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;3 执行SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;4 关闭从库sqlshutdown immediate;五 启动主库修改initreddb.ora文件如上所示.sqlstartup pfile=/oacle/product//dbs/initreddb.orasqlcreate spfile from pfilesqlshutdown sqlstartup六 配置侦听及网络服务名由于主备库是在一台机器上,所以只需各配置一个listener.ora和tnsnames.ora文件即可.Listener.ora文件内容:LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux)(PORT = 1521) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = reddb) (ORACLE_HOME = /oracle/product/) (SID_NAME = reddb) ) )LISTENERSTD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux)(PORT = 1523) )SID_LIST_LISTENERSTD = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = reddbstd) (ORACLE_HOME = /oracle/product/) (SID_NAME = reddbstd) ) )tnsnames.ora文件内容:REDDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = linux)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = reddb) ) )REDDBSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = linux)(PORT = 1523) ) (CONNECT_DATA = (SERVICE_NAME = reddbstd) ) )主从库的启动顺序:从库的listener从库主库的listener主库七 验证备库1 查看从库当前存在的归档日志SQL SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;2 在主库上归档当前日志SQL ALTER SYSTEM ARCHIVE LOG CURRENT;3 查看从库当前归档的日志SQL SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;4 查看从库当前归档日志是否已被applySQL SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG2 ORDER BY SEQUENCE#;5 查看从库上app进程的信息SQL SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS2 FROM V$MANAGED_STANDBY;6 查看从库归档日志被app的情况SQL SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#2 FROM V$ARCHIVE_DEST_STATUS;7 查看从库的已归档日志信息SQL SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,2 NEXT_CHANGE# FROM V$ARCHIVED_LOG;8 在主库上执行sqlcreate table test(b number);sqlinsert into test values(1);sqlcommit;sqlalter system switch logfile;9 在从库上执行sqlalter database recover managed standby database cancel;sqlalter database open read only;sqlselect * from test;应该能检索出有值为1的记录.10 在主库上执行sqlcreate table test(b number);sqlinsert into test values(2);sqlcommit;sqlalter system switch logfile;11 在从库上执行sqlselect * from test;这时应没有值为2的记录,因为从库处于open read only模式,不能进行恢复;也就是说managed recover和read only是从库两个互斥的运行模式.执行Sqlselect sequence#,archived,applied from v$archived_log;通过这个结果就能发现有个已归档的日志没有被applied.八 监测备库的信息1 检查进程在备库上执行:SQL SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;2 查看归档日志在主备库上执行SQL SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#2 FROM V$ARCHIVE_DEST_STATUS;3 查看备库上已归档的日志备库上执行:SQL SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME2 FROM V$ARCHIVED_LOG;4 查看主库的已归档信息SQL SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;5 查看从库的刚被apply的日志SQL SELECT THREAD#, MAX(SEQUENCE#) AS LAST_APPLIED_LOG2 FROM V$LOG_HISTORY3 GROUP BY THREAD#;或者SQL SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;6 查看主库上尚未被传递到从库的日志假设主库上的本地DEST_ID为1;远程DEST_ID为2;这可通过v$archive_dest来确定.SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);九 问题解决1 检查主库上的归档路径是否正确SQL SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;2 如果备库没能收到归档日志1) 先检查主库上的归档路径是否有问题 SELECT DEST_ID ID, STATUS DB_status, DESTINATION Archive_dest, ERROR ErrorFROM V$ARCHIVE_DEST;2) 如果上面正常,则检查:主库上关于备库的service name正确否?归档路径正确否?相应归档路径的state是否为enable备库上的listener已启动否?备库启动否?十 相关知识1 三种保护模式Maximum PerformanceMaximum AvailabilityMaximum Protection2 日志传递进程主库上传递日志的进程可以是LGWR或ARCH.对于Maximum Availability和Maximum Protection两种模式,必须由LGWR进行传递,因为主库要求实时获取从库的事务信息;对于Maximum Performance模式,则可使用LGWR或ARCH.如果是LGWR传递,则从库的接收者是RFS进程,接收的日志信息写入standby redo log;当主库进行日志switch时,会触发从库的arch进程也将standby redo log归档到log_archive_dest_n所指定的本地归档路径.如果时ARCH传递,则从库的接收者是RFS进程,每当主库进行日志switch时,从库会接收到一个完整的redo文件,RFS会将其直接放到standby_archive_dest路径下;此时,主库上的每个arch进程会在从库对应一个RFS进程.十一 保护模式的转换1 在缺省情况下,备库是处于Maximum Performance模式;主库的日志传递进程是Arch在主库上查询:sqlselect protection from v$archive_dest_status;在从库上查询:sqlselect process,client_process from v$managed_standby;2 现在将使用Maximum Availability模式在主库上执行:要想转换为Maximum Availability模式,主库上用来传递log的进程必须是LGWR.改变之:sqlalter system log_archive_dest_2=service=reddbstd lgwr scope=both;sqlshutdown immediatesqlstartup mount;sqlalter database set standby database to maximize availability;sqlalter database open;再执行:sqlselect protection from v$archive_dest_status;在从库上查询:sqlselect process,client_process from v$managed_standby;十二 Switchover测试1 在当前主库上执行 1) 查询是否可进行切换SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;如果结果是:SWITCHOVER_STATUS-TO STANDBY1 row selected表示可进行切换2) 在主库上执行切换操作 SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 如此,主库就成为了从库3) 在这个成为了从库的原主库上执行SQL SHUTDOWN IMMEDIATE;SQL STARTUP NOMOUNT;SQL ALTER DATABASE MOUNT STANDBY DATABASE;SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FROM SESSION;2 在原来的从库上执行1)查询此时从库底状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-SWITCHOVER PENDING1 row selected这表示从库已收到原来主库底通知,可切换为主库2) 切换从库到主库SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;在这个过程中,如果从库需要online redo文件,则会自动创建,但这会很耗时.节约时间起见,可以:从原来的主库拷备在线日志文件过来或者直接使用ALTER DATABASE ADD STANDBY LOGFILE生成在线日志文件.3) 启动新的主库SQL SHUTDOWN;SQL STARTUP;3 测试1) 在切换后的主库上执行sqlselect protection_mode from v$database;可见,此时的模式为Maximum Performance.在从库上执行:SQLselect process,client_process from v$managed_standby;从这里能发现,从库上的RFS进程对应的client_process是ARCH;这也就是说主库上进行日志传递的进程是ARCH,所以包含模式只能是Maximum Performance.在这种模式下,从库接收到的是在主库上进行归档时的完整的一个日志,也就是说,每当主库上进行日志切换时,主库的arch进程向两个方向传递此日志文件:一个是本地归档路径,一个是远程归档路径.可见,在这种模式下,由于在日志切换时才进行从主库到从库的日志切换,所以可能会造成在主库失败时会有数据丢失.2) 检查从库上的日志文件的位置从库的归档位置是:/oracle/oradata/reddb/stdby_arch这是standby_archive_dest所指定的位置;也就是说,从库当接收到主库arch进程发过来的日志文件时,应该是放于此参数所指定的位置.3) 改变主库的运行模式为Maximum Aavailability模式步骤见上面4) 查看此时的归档日志文件的位置,发现仍还在/oracle/oradata/reddb/stdby_arch目录下,不过按照我的理解,应该是在local_archive_dest_1所指定的/oracle/oradata/reddb/local_arch目录下,这不知是什么原因?4 再切换回去按照以上步骤,再将主从库切换回去切换回去,会发现也出现上面这个问题.不过,可证明,Data Guard是可来回Switch Over的.十三 Failover测试1 从库与主库是否有日志缺失.SQL SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;如果有,需要从主库或其它从库copy相应日志文件到将要成为主库的从库.并且对拷贝过来的文件进行注册:SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec1;2 查看是否有归档日志的缺失查找所有主库和从库:SQL SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)2 OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;看是否有比将要成为主库的从库更序列号更高的归档日志,有则拷贝,并进行注册:SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec1;3 在目标从库上执行:如果没有手工copy日志文件过来,则SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;否则:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH2 SKIP STANDBY LOGFILE;4 将从库切换为主库SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;如果出现: ORA-16139 media recovery required则执行:ALTER DATABASE ACTIVATE STANDBY DATABASE SKIP STANDBY LOGFILE;5 启动新主库SQL SHUTDOWN IMMEDIATE;SQL STARTUP;切换完毕,原来的主库不会是DG配置中的一员了,要想重新使用原来的数据库,需要重建之.6 损坏主库的重建按照以上步骤,使用目前主库的数据文件作为损坏主库的数据文件,并使用目前主库生成控制文件作为损坏主库的控制文件.将损坏主库作为从库启动.将损坏主库切换为主库,目前主库切换为从库.十四 DataGuard - ORA-00261错误及解决方法 选择自 Kamus 的 Blog在DataGuard环境中如果我们在做failover的时候，可能会碰到ORA-00261错误，下面是该错误的产生原因和解决方法。如果是由于网络问题而导致需要切换，那么通常standby端的RFS进程并不会意识到primary已经不可访问，所以RFS进程也不会释放当前的standby redo log文件。如果是primary端的数据库实例由于故障中断，那么一般情况下standby端的RFS进程会立刻意识到primary已经不可访问，也就会立刻释放当前的standby redo log文件。只要RFS进程没有释放standby redo log文件，那么执行ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH命令就会在alertlog文件中发现如下的报错信息 Warning: log 4 of thread 1 is being archived or modifiedRecovery interrupted.Media Recovery failed with error 261如果在报上述错误的时候，执行SWITCH，那么将会出现下面的错误：ORA-16139: media recovery required所以必须检查alertlog文件，直到发现如下信息才表示RFS进程已经释放了standby redo log文件，这时候才可以作FINISH：RFS: Possible network disconnect with primary database促使RFS进程释放standby redo log 文件有两种方法：1等待RFS进程的network timeout，通常需要等待8分钟左右2关闭standby数据库，再重新开启，这样会强制RFS进程释放standby redo log我们可以通过v$managed_standby视图来监控RFS进程何时释放实行Failover：ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;alertlog中将显示如下信息，表示finish成功：Terminal Incomplete Recovery: UNTIL CHANGE 3738452Terminal Incomplete Recovery: End-Of-Redo log allocationTerminal Incomplete Recovery: log 4 reserved for thread 1 seq# 8772TERMINAL RECOVERY changing data