DataGuard 日常维护

DataGuard 日常维护数据库采用Oracle 10g版本.Dataguard采用最大性能模式.第一部分日常维护一正确打开主库和备库1 主库:SQL> STARTUP MOUNT;SQL> ALTER DATABASE ARCHIVELOG;SQL> ALTER DATABASE OPEN;2 备库:SQL> STARTUP MOUNT;SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;二正确关闭顺序1 备库:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL>SHUTDOWN IMMEDIATE;2 主库SQL>SHUTDOWN IMMEDIATE;三备库Read-Only模式打开当前主库正常OPEN状态备库处于日志传送状态.1 在备库停止日志传送SQL> recover managed standby database cancel;2 备库Read-only模式打开SQL> alter database open read only;3 备库回到日志传送模式SQL> recover managed standby database disconnect from session; Media recovery complete.SQL> select status from v$instance;STATUS------------MOUNTED四日志传送状态监控1 主库察看当前日志状况SQL> select sequence#,status from v$log;SEQUENCE# STATUS---------- ----------------51 ACTIVE52 CURRENT50 INACTIVE2 备库察看RFS(Remote File Service)接收日志情况和MRP应用日志同步主库情况SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS2 FROM V$MANAGED_STANDBY;PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS--------- ------------ ---------- ---------- ---------- ----------ARCH CONNECTED 0 0 0 0ARCH CONNECTED 0 0 0 0RFS RECEIVING 0 0 0 0MRP0 WAIT_FOR_LOG 1 52 0 0RFS RECEIVING 0 0 0 0可以看到备库MPR0正等待SEQUENCE#为52的redo.3 察看备库是否和主库同步SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#,APPLIED_THREAD#, APPLIED_SEQ#2 FROM V$ARCHIVE_DEST_STATUS;ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD#APPLIED_SEQ#---------------- ------------- --------------- ------------0 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 01 51 1 50可以看到备库已经将SEQUENCE#51的日志归档,已经将SEQUENCE#50的redo应用到备库.由于已经将SEQUENCE#51的日志归档,所以SEQUENCE#51以前的数据不会丢失.4 察看备库已经归档的redoSQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#,FIRST_CHANGE#,2 NEXT_CHANGE# FROM V$ARCHIVED_LOG;REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE#NEXT_CHANGE#------- ------- ---------- ---------- ------------- ------------SRMN SRMN 1 37 572907 573346RFS ARCH 1 38 573346 573538RFS ARCH 1 39 573538 573623RFS ARCH 1 40 573623 573627RFS ARCH 1 41 573627 574326RFS ARCH 1 42 574326 574480RFS ARCH 1 49 601476 601532RFS ARCH 1 50 601532 606932RFS ARCH 1 51 606932 6072565 察看备库已经应用的redoSQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE#2 FROM V$LOG_HISTORY;THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ---------- ------------- ------------1 1 366852 3682221 2 368222 3695901 3 369590 3710711 4 371071 3723881 5 372388 3767811 6 376781 3977441 7 397744 4077381 8 407738 4130351 9 413035 4130371 50 601532 6069321 51 606932 607256可以看到备库已经将SEQUENCE#为51的归档文件应用到备库.6 察看备库接收,应用redo数据过程.SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;MESSAGE-------------------------------------------------------------------------------- ARC0: Archival startedARC0: Becoming the 'no FAL' ARCHARC0: Becoming the 'no SRL' ARCHARC1: Archival startedARC1: Becoming the heartbeat ARCHRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[1]: Assigned to RFS process 19740RFS[1]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE mode Attempt to start background Managed Standby Recovery processMESSAGE-------------------------------------------------------------------------------- MRP0: Background Managed Standby Recovery process started Managed Standby Recovery not using Real Time Apply Clearing online redo logfile 7 /oraguard/redo1/redo_7_1.log Clearing online redo logfile 7 completeMedia Recovery Waiting for thread 1 sequence 47RFS[1]: No standby redo logfiles createdRedo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[2]: Assigned to RFS process 19746RFS[2]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modeMESSAGE-------------------------------------------------------------------------------- Committing creation of archivelog '/arch/1_47_552308270.arc' Media Recovery Log /arch/1_47_552308270.arcMedia Recovery Waiting for thread 1 sequence 48MRP0: Background Media Recovery cancelled with status 16037 MRP0: Background Media Recovery process shutdown Managed Standby Recovery CanceledAttempt to start background Managed Standby Recovery process MRP0: Background Managed Standby Recovery process started Managed Standby Recovery not using Real Time ApplyMedia Recovery Waiting for thread 1 sequence 48RFS[1]: No standby redo logfiles createdMESSAGE-------------------------------------------------------------------------------- Committing creation of archivelog '/arch/1_48_552308270.arc'Media Recovery Log /arch/1_48_552308270.arcMedia Recovery Waiting for thread 1 sequence 49RFS[1]: No standby redo logfiles createdCommitting creation of archivelog '/arch/1_49_552308270.arc'Media Recovery Log /arch/1_49_552308270.arcMedia Recovery Waiting for thread 1 sequence 50RFS[1]: No standby redo logfiles createdCommitting creation of archivelog '/arch/1_50_552308270.arc'Media Recovery Log /arch/1_50_552308270.arcMedia Recovery Waiting for thread 1 sequence 51MESSAGE--------------------------------------------------------------------------------RFS[1]: No standby redo logfiles createdCommitting creation of archivelog '/arch/1_51_552308270.arc'Media Recovery Log /arch/1_51_552308270.arcMedia Recovery Waiting for thread 1 sequence 52可以看到RFS接收到sequence#为51的归档文件并存至备库归档目录/arch/1_51_552308270.arc.Oracle自动应用文件/arch/1_51_552308270.arc进行备库与主库同步Oracle继续等待主库sequence 52的归档文件五备库归档目录维护1 找到备库归档目录SQL> show parameter log_archive_dest_1NAME TYPE------------------------------------ --------------------------------VALUE------------------------------log_archive_dest_1 stringLOCATION=/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=ora2log_archive_dest_10 string2 维护策略每周2,4,7删除已经应用的归档文件具体参见附录二第二部分主库正常切换一人工干预主库正常切换1 在主库端检验数据库可切换状态SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS-----------------TO STANDBY1 row selectedSWITCHOVER_STATUS:TO STANDBY表示可以正常切换.如果SWITCHOVER_STATUS的值为SESSIONS ACTIVE,表示当前有会话处于ACTIVE状态2 开始主库正常切换如果SWITCHOVER_STATUS的值为TO STANDBY 则:SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;如果SWITCHOVER_STATUS的值为SESSIONS ACTIVE 则:SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;成功运行这个命令后，主库被修改为备库3 重启先前的主库SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP MOUNT;4 在备库验证可切换状态SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS-----------------TO_PRIMARY1 row selected5 将目标备库转换为主库如果SWITCHOVER_STATUS的值为TO STANDBY 则:SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;如果SWITCHOVER_STATUS的值为SESSIONS ACTIVE 则:SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;成功运行这个命令后，备库被修改为主库6 重启目标备库SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP;7 先前主库启动日志传送进程SQL> alter database recover managed standby database disconnect;总结: 这样主库的一次正常切换完成.切换后的状态,原先的主库变为备库,原先的备库变为主库.二通过运行脚本实现主库正常切换1 主库切换为备库在主库上运行脚本/admin/dataGuard/switchover/primary_to_standby.sh2 备库切换为主库在备库上运行脚本/admin/dataGuard/switchover/standby_to_primary.sh脚本1成功运行后,再运行脚本2,不能同时运行两个脚本.经过这次切换后原来的主库变为备库,原先的备库变为主数据并且OPEN对应用提供服务.3 复原最初状态在原备库上运行脚本/admin/dataGuard/switchover/primary_to_standby.sh成功完成后在原主库上运行脚本/admin/dataGuard/switchover/standby_to_primary.sh第三部分主库灾难切换一人工干预主库灾难切换二通过运行脚本实现主库灾难切换SQL>alter database recover managed standby database cancel;SQL>shutdown immediateSQL>startup mountSQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;SQL>alter database recover managed standby database finish;-- switchSQL>alter database commit to switchover to primary with session shutdown; -- openSQL>shutdown immediateSQL>startup附:一有选择察看redo传送与应用情况select message from v$dataguard_statuswhere message_num>&message_num;二备库归档目录维护脚本在crontab 中定制每日执行removeCommand.sh即可。