oracle联机重做日志.docx

-=- Oracle联机重做日志文件(ONLINE LOG FILE)-=一、Oracle中的几类日志文件Redologfiles-联机重做日志Archivelogfiles-归档日志Alertlogfiles-告警日志Trace files-跟踪日志user_dump_dest-用户跟踪日志backupground_dump_dest-进程跟踪日志-查看后台进程相关目录SQLshow parameterdumpNAMETYPEVALUE- - -background_core_dumpstringpartialbackground_dump_deststring/u01/app/oracle/admin/orcl/bdumpcore_dump_deststring/u01/app/oracle/admin/orcl/cdumpmax_dump_file_sizestringUNLIMITEDshadow_core_dumpstringpartialuser_dump_deststring/u01/app/oracle/admin/orcl/udump关于Oracle常用目录及路径请参考：Oracle常用目录结构(10g)关于Oracle体系结构请参考：Oracle实例和Oracle数据库(Oracle体系结构)二、联机重做日志的规划管理1.联机重做日志记录了数据的所有变化(DML,DDL或管理员对数据所作的结构性更改等)提供恢复机制(对于意外删除或宕机利用日志文件实现数据恢复)可以被分组管理2.联机重做日志组由一个或多个相同的联机日志文件组成一个联机重做日志组至少两个日志组，每组一个成员(建议每组两个成员，分散放开到不同的磁盘)由LGWR后台进程同时将日志内容写入到一个组的所有成员LGWR的触发条件在事务提交的时候(COMMIT)RedoLogBuffer三分之一满RedoLogBuffer多于一兆的变化记录在DBWn写入数据文件之前3.联机重做日志成员重做日志组内的每一个联机日志文件称为一个成员一个组内的每一个成员具有相同的日志序列号(logsequence number),且成员的大小相同每次日志切换时，Oracle服务器分配一个新的LSN号给即将写入日志的日志文件组LSN号用于唯一区分每一个联机日志组和归档日志处于归档模式的联机日志，LSN号在归档时也被写入到归档日志之中4.日志文件的工作方式日志文件采用按顺序循环写的方式当一组联机日志组写满，LGWR则将日志写入到下一组，当最后一组写满则从第一组开始写入写入下一组的过程称为日志切换切换时发生检查点过程检查点的信息同时写入到控制文件5.联机日志文件的规划总原则分散放开，多路复用日志所在的磁盘应当具有较高的I/O一般日志组大小应满足自动切换间隔至少15-20分钟左右业务需求建议使用rdo结尾的日志文件名，避免误删日志文件。如redo1.rdo,redo2.rdo规划样例RedoLogGroup1RedoLogGroup2RedoLogGroup3Member1Member1Member1-Physical Disk 1Member2Member2Member2-Physical Disk 2Member3Member3Member3-Physical Disk 36.日志切换和检查点切换ALTERSYSTEM SWITCH LOGFILE;-强制手动切换ALTERSYSTEM CHECKPOINT;强制设置检查点间隔ALTERSYSTEMSETFAST_START_MTTR_TARGET=n7.添加日志文件组ALTERDATABASEADDLOGFILE GROUPn($ORACLE_BASE/oradata/u01/logn1.rdo,$ORACLE_BASE/oradata/u01/logn2.rdo)SIZEmM;8.添加日志成员ALTERDATABASEADDLOGFILE MEMBER$ORACLE_BASE/oradata/u01/logn1.rdoTOGROUP1,$ORACLE_BASE/oradata/u01/logn2.rdoTOGROUP2;9.删除日志成员不能删除组内的唯一一个成员不能删除处于active和current状态组内的成员删除处于active和current状态组内的成员，应使用日志切换使其处于INACTIVE状态后再删除对于组内如果一个成员为NULL值，一个为INVALID，且组处入INACTIVE，仅能删除INVALID状态成员删除日志成员，物理文件并没有真正删除，需要手动删除删除日志文件后，控制文件被更新对于处于归档模式下的数据库，删除成员时确保日志已被归档，查看v$log视图获得归档信息ALTERDATABASEDROPLOGFILE MEMBER$ORACLE_BASE/oradata/u01/logn1.rdo10.删除日志组一个实例至少需要两个联机日志文件组活动或当前的日志组不能被删除组内成员状态有NULL值或INVALID状态并存，组不可删除日志组被删除后，物理文件需要手动删除(对于非OMF)ALTERDATABASEDROPLOGFILEGROUPn11.日志的重定位及重命名所需权限ALTERDATABASE系统权限复制文件到目的位置操作系统权限(写权限)CURRENT状态组内的成员不能被重命名建议该行为之前备份数据库重命名或重定位之后建议立即备份控制文件重定位及重命名的两种方法添加一个新成员到日志组，然后删除一个旧的成员使用ALTERDATABASERENAMEFILE命令(不区分归档与非归档模式)复制联机日志文件到新路径:ho cp 执行ALTERDATABASERENAMEFILETO对于处于CURRENT状态的需要改名且不切换的情况下办法是切换到MOUNT状态下再执行上述操作12.清空日志文件组ALTERDATABASECLEAR LOGIFLEGROUPnALTERDATABASECLEAR UNARCHIVED LOGFILEGROUPn-使用unarchived避免归档13.日志周期循环及切换分析Group1Group2Group3CurrentInactiveInactive- Log Switch -ActiveCurrentInactive- Log Switch -ActiveActiveCurrent- Log Switch -CurrentInactiveInactive-Active和Current称之为在一个循环周期之内(按顺序写日志)-Inactive称为一个周期之外(一个新的循环)-新一轮循环开始如在归档状态则先归档再清空，否则直接清空日志-数据库启动时Active和Current状态的日志不能丢失，否则出错14.日志的监视查看日志视图中的物理日志文件是否存在、位置、大小等SELECTho cp |memberFROMv$logfile;查看日志文件所处的磁盘空间是否足够SQLho df-h查看组内是否存在多个成员，如为单一成员应考虑增加日志成员日志切换的间隔时间，应满足15-20分钟业务需求，如果切换间隔很短，应当增加日志文件的大小增加方法，先删除日志组，再重建该组(对于current和active的需要切换再做处理)-查看切换时间间隔(下面的示例中为手工切换的时间，不作考虑)SQLSELECTTO_CHAR(first_time,yyyy-mm-dd hh24:mi:ss),group#FROMv$log;TO_CHAR(FIRST_TIME,GROUP#- -2010-07-20 09:43:1812010-07-19 22:44:3022010-07-19 22:44:32315.日志的异常处理(参照演示中9小节)不一致的情况(启动时)ALTERDATABASECLEAR LOGFILEGROUPnALTERDATABASECLEAR UNARCHIVED LOGFILEGROUPn三、与日志有关的动态性能视图V$LOGV$LOGFILEV$LOG中STATUS的状态值UNUSED:从未对该联机日志写入任何内容，一般为新增加联机日志文件或是使用resetlog后的状态CURRENT：当前重做日志文件，表示该重做日志文件为活动状态，能够被打开和关闭ACTIVE：处于活动状态，不属于当前日志，崩溃恢复需要该状态，可用于块恢复，可能归档，也可能未归档CLEARING：表示在执行alterdatabaseclear logfile命令后正将该日志重建为一个空日志，重建后状态变为unusedCLEARING_CURRENT:当前日志处于关闭线程的清除状态。如日志某些故障或写入新日志标头时发生I/O错误INACTIVE:实例恢复不在需要联机重做文件日志组，可能归档也可能未归档V$LOGFILE中STATUS的状态值INVALID:表明该文件不可访问STALE:表明文件内容不完全DELETED:表明该文件不再使用NULL：表明文件正在使用四、演示-1.查看当前数据库的日志SQLSELECT*FROMv$log;GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -115524288001NOCURRENT2758062 19-JUL-10213524288002 YES INACTIVE2695010 16-JUL-103141048576002 YES INACTIVE2716552 18-JUL-10SQLSELECT*FROMv$logfileORDERBYgroup#;GROUP# STATUSTYPEMEMBERIS_- - - - -1ONLINE/u01/app/oracle/oradata/orcl/redo01.logNO2 STALEONLINE/u01/app/oracle/oradata/orcl/redo02.logNO2 STALEONLINE/u01/app/oracle/oradata/orcl/redo2.logNO3 STALEONLINE/u01/app/oracle/oradata/orcl/redo03.logNO3 STALEONLINE/u01/app/oracle/oradata/orcl/redo3.logNO-2.添加日志组SQLSELECT*FROMv$logfile;GROUP# STATUSTYPEMEMBERIS_- - - - -2 STALEONLINE/u01/app/oracle/oradata/orcl/redo2.logNO2 STALEONLINE/u01/app/oracle/oradata/orcl/redo02.logNO1ONLINE/u01/app/oracle/oradata/orcl/redo01.logNO3 STALEONLINE/u01/app/oracle/oradata/orcl/redo3.logNO3 STALEONLINE/u01/app/oracle/oradata/orcl/redo03.logNO4ONLINE/u01/app/oracle/oradata/orcl/redo4.logNO4ONLINE/u01/app/oracle/oradata/orcl/redo04.logNO-3.添加日志成员SQLALTERDATABASEADDLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo1.logTOGROUP1;Databasealtered.SQLSELECT*FROMv$logfileWHEREgroup#=1;GROUP# STATUSTYPEMEMBERIS_- - - - -1ONLINE/u01/app/oracle/oradata/orcl/redo01.logNO1 INVALID ONLINE/u01/app/oracle/oradata/orcl/redo1.logNO-4.删除日志成员SQLALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo01.log;ALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo01.log*ERROR at line 1:-redo01.log处于NULL状态且该日志组为current状态不能删除ORA-00362:memberisrequiredtoform a valid logfileingroup1ORA-01517:logmember:/u01/app/oracle/oradata/orcl/redo01.logSQLALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo04.log;Databasealtered.SQLALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo4.log;ALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo4.log*ERROR at line 1:-最后一个日志成员不能被删除ORA-00361:cannot remove lastlogmember/u01/app/oracle/oradata/orcl/redo4.logforgroup4-5.日志切换SQLSELECT*FROMv$log;-当前的日志组处于CURRENT状态GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -115524288002NOCURRENT2758062 19-JUL-10213524288002 YES INACTIVE2695010 16-JUL-103141048576002 YES INACTIVE2716552 18-JUL-10410314572801 YES UNUSED0SQLALTERSYSTEM SWITCH LOGFILE;-进行日志切换System altered.SQLSELECT*FROMv$log;-原来的日志组4的unused状态变为current状态GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -115524288002 YES ACTIVE2758062 19-JUL-10213524288002 YES INACTIVE2695010 16-JUL-103141048576002 YES INACTIVE2716552 18-JUL-10416314572801NOCURRENT2759277 19-JUL-10SQLALTERSYSTEM SWITCH LOGFILE;-再次进行日志切换System altered.SQLSELECT*FROMv$log;-日志组1变为current且组4变为active状态GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -115524288002 YES ACTIVE2758062 19-JUL-10217524288002NOCURRENT2759293 19-JUL-103141048576002 YES INACTIVE2716552 18-JUL-10416314572801 YES ACTIVE2759277 19-JUL-10由上可得知，在日志切换时对于unused组将优先作为下一组切换对象-再次删除redo01.log还是收到错误提示SQLALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo01.log;ALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo01.log*ERROR at line 1:ORA-00362:memberisrequiredtoform a valid logfileingroup1ORA-01517:logmember:/u01/app/oracle/oradata/orcl/redo01.logSQLALTERSYSTEM SWITCH LOGFILE;-再次进行日志切换System altered.SQLSELECT*FROMv$log;-group1变为inactiveGROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -115524288002 YES INACTIVE2758062 19-JUL-10217524288002 YES ACTIVE2759293 19-JUL-103181048576002NOCURRENT2759420 19-JUL-10416314572801 YES INACTIVE2759277 19-JUL-10-反复多切几次日志之后redo01.log被成功删除SQLALTERDATABASEDROPLOGFILE MEMBER/u01/app/oracle/oradata/orcl/redo01.log;Databasealtered.-6.删除日志组(CURRENT和ACTIVE状态的不能被删除)SQLSELECT*FROMv$log;GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -119524288001 YES ACTIVE2759487 19-JUL-102111524288002NOCURRENT2759502 19-JUL-103181048576002 YES ACTIVE2759420 19-JUL-104110314572801 YES ACTIVE2759499 19-JUL-10SQLALTERDATABASEDROPLOGFILEGROUP4;ALTERDATABASEDROPLOGFILEGROUP4*ERROR at line 1:-处于活动状态的group4用于灾难恢复，不能被删除ORA-01624:log4 neededforcrash recoveryofinstance orcl(thread 1)ORA-00312:onlinelog4 thread 1:/u01/app/oracle/oradata/orcl/redo4.logSQLALTERSYSTEM SWITCH LOGFILE;-进行日志切换System altered.SQL/System altered.SQLSELECT*FROMv$log;-group 4的状态变为inactvieGROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM- - - - - - - - -1113524288001NOCURRENT2759720 19-JUL-102111524288002 YES ACTIVE2759502 19-JUL-1031121048576002 YES ACTIVE2759718 19-JUL-104110314572801 YES INACTIVE2759499 19-JUL-10SQLALTERDATABASEDROPLOGFILEGROUP4;-成功删除group 4Databasealtered.SQLho ls/u01/app/oracle/oradata/orcl/redo*/u01/app/oracle/oradata/orcl/redo01.log/u01/app/oracle/oradata/orcl/redo1.log/u01/app/oracle/oradata/orcl/redo02.log/u01/app/oracle/oradata/orcl/redo2.log/u01/app/oracle/oradata/orcl/redo03.log/u01/app/oracle/oradata/orcl/redo3.log/u01/app/oracle/oradata/orcl/redo04.log/u01/app/oracle/oradata/orcl/redo4.logSQLho rm/u01/app/oracle/oradata/orcl/redo04.log-删除物理文件SQLho rm/u01/app/oracle/oradata/orcl/redo4.log-删除物理文件-7.日志的重定位及重命名(仅演示ALTER DATABASE RENAME FILE命令)SQLSELECTname,log_modeFROMv$database;NAMELOG_MODE- -ORCLARCHIVELOGSQLSELECT*FROMv$logfileORDERBYgroup#;GROUP# STATUSTYPEMEMBERIS_- - - - -1ONLINE/u01/app/oracle/oradata/orcl/redo01.logNO2ONLINE/u01/app/oracle/oradata/orcl/redo02.logNO2ONLINE/u01/app/oracle/oradata/orcl/redo2.logNO3 STALEONLINE/u01/app/oracle/oradata/orcl/redo03.logNO3 STALEONLINE/u01/app/oracle/oradata/orcl/redo3.logNOSQLho cp/u01/app/oracle/oradata/orcl/redo01.log/u01/app/oracle/oradata/redo01.rdoSQLALTERDATABASERENAMEFILE/u01/app/oracle/oradata/orcl/redo01.log2TO/u01/app/oracle/oradata/redo01.rdo;Databasealtered.SQLSELECT*FROMv$lo