oracle-redolog日常管理

来源：互联网发布：java 音乐播放器 er图编辑：程序博客网时间：2024/05/26 14:10

redolog是oracle中极其重要的组建，它的目的在于保证数据的安全。redolog的丢失可能导致数据库中数据的丢失。涉及redolog的故障恢复，应该是DBA应该掌握的。在生产库上，我们应该尽量将数据库运行在归档模式。

说明：我操作的数据库版本是11.2.0.3

一、online redolog日常管理

1、查询redolog信息

1、查询redolog信息-->日志状态信息SYS@PROD>select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         28        100          2 YES CLEARING_CURRENT       1961831 2014-03-31 03:05:38         3          1         27        100          2 YES CLEARING               1841509 2014-03-27 15:49:34         2          1         28        100          2 YES CLEARING               1961831 2014-03-31 03:05:38-->日志文件信息SYS@PROD>select a.group#,a.thread#,b.member,a.bytes/1024/1024 size_MB,a.members,a.archived,a.status from v$log a,v$logfile b where a.group#=b.group#;    GROUP#    THREAD# MEMBER                                                SIZE_MB    MEMBERS ARC STATUS---------- ---------- -------------------------------------------------- ---------- ---------- --- ----------------         1          1 /u01/app/oracle/oradata/PROD/disk1/redo01.log             100          2 YES CLEARING_CURRENT         1          1 /u01/app/oracle/oradata/PROD/disk1/redo04.log             100          2 YES CLEARING_CURRENT         3          1 /u01/app/oracle/oradata/PROD/disk1/redo06.log             100          2 YES CLEARING         2          1 /u01/app/oracle/oradata/PROD/disk1/redo05.log             100          2 YES CLEARING         3          1 /u01/app/oracle/oradata/PROD/disk1/redo03.log             100          2 YES CLEARING         2          1 /u01/app/oracle/oradata/PROD/disk1/redo02.log             100          2 YES CLEARING6 rows selected.-->列出日志切换的线程号和切换时间col 00 for '999'col 01 for '999'col 02 for '999'col 03 for '999'col 04 for '999'col 05 for '999'col 06 for '999'col 07 for '999'col 08 for '999'col 09 for '999'col 10 for '999'col 11 for '999'col 12 for '999'col 13 for '999'col 14 for '999'col 15 for '999'col 16 for '999'col 17 for '999'col 18 for '999'col 19 for '999'col 20 for '999'col 21 for '999'col 22 for '999'col 23 for '999'SELECT   thread#, a.ttime, SUM (c8) "08", SUM (c9) "09", SUM (c10) "10",         SUM (c11) "11", SUM (c12) "12", SUM (c13) "13", SUM (c14) "14",         SUM (c15) "15", SUM (c16) "16", SUM (c17) "17", SUM (c18) "18",         SUM (c0) "00", SUM (c1) "01", SUM (c2) "02", SUM (c3) "03",         SUM (c4) "04", SUM (c5) "05", SUM (c6) "06", SUM (c7) "07",         SUM (c19) "19", SUM (c20) "20", SUM (c21) "21", SUM (c22) "22",         SUM (c23) "23"    FROM (SELECT thread#, ttime, DECODE (tthour, '00', 1, 0) c0,                 DECODE (tthour, '01', 1, 0) c1,                 DECODE (tthour, '02', 1, 0) c2,                 DECODE (tthour, '03', 1, 0) c3,                 DECODE (tthour, '04', 1, 0) c4,                 DECODE (tthour, '05', 1, 0) c5,                 DECODE (tthour, '06', 1, 0) c6,                 DECODE (tthour, '07', 1, 0) c7,                 DECODE (tthour, '08', 1, 0) c8,                 DECODE (tthour, '09', 1, 0) c9,                 DECODE (tthour, '10', 1, 0) c10,                 DECODE (tthour, '11', 1, 0) c11,                 DECODE (tthour, '12', 1, 0) c12,                 DECODE (tthour, '13', 1, 0) c13,                 DECODE (tthour, '14', 1, 0) c14,                 DECODE (tthour, '15', 1, 0) c15,                 DECODE (tthour, '16', 1, 0) c16,                 DECODE (tthour, '17', 1, 0) c17,                 DECODE (tthour, '18', 1, 0) c18,                 DECODE (tthour, '19', 1, 0) c19,                 DECODE (tthour, '20', 1, 0) c20,                 DECODE (tthour, '21', 1, 0) c21,                 DECODE (tthour, '22', 1, 0) c22,                 DECODE (tthour, '23', 1, 0) c23            FROM (SELECT thread#, TO_CHAR (first_time, 'yyyy-mm-dd') ttime,                         TO_CHAR (first_time, 'hh24') tthour                    FROM v$log_history                   WHERE (SYSDATE - first_time < 30))) aGROUP BY thread#, ttimeorder by ttime;

2、添加日志组

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/redolog/redo04a.log','/u01/app/oracle/oradata/redolog/redo04b.log') size 20M;   Database altered.

3、添加日志成员

SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/redolog/redo05a.log') size 20M;Database altered.-->给group 5添加一个日志成员SQL> alter database add logfile member '/u01/app/oracle/oradata/redolog/redo05b.log' to group 5;    Database altered.SQL> select group#,member from v$logfile;    GROUP# MEMBER---------- --------------------------------------------------         1 /u01/app/oracle/oradata/redolog/redo01a.log         1 /u01/app/oracle/oradata/redolog/redo01b.log         2 /u01/app/oracle/oradata/redolog/redo02a.log         2 /u01/app/oracle/oradata/redolog/redo02b.log         3 /u01/app/oracle/oradata/redolog/redo03a.log         3 /u01/app/oracle/oradata/redolog/redo03b.log         4 /u01/app/oracle/oradata/redolog/redo04a.log         4 /u01/app/oracle/oradata/redolog/redo04b.log         5 /u01/app/oracle/oradata/redolog/redo05a.log         5 /u01/app/oracle/oradata/redolog/redo05b.log10 rows selected.

4、删除日志组

-->检查日志组状态SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         31        100          2 YES INACTIVE            4219305791 2013-12-23 00:12:35         2          1         32        100          2 NO  CURRENT             4219324100 2013-12-25 16:29:10         3          1         30        100          2 YES INACTIVE            4219267204 2013-12-22 03:00:58         4          1          0         20          2 YES UNUSED                       0         5          1          0         20          2 YES UNUSED                       0-->当archvied状态为yes,status状态为inactive or unused状态时，可以删除该redolog group。SQL> alter database drop logfile group 5;Database altered.

二、redolog损坏的恢复
在做下面实验之前，请先对数据库进行一次全库备份。

RMAN> run{2> backup database format '/s01/backup/database_full_%U';3> backup format '/s01/backup/al_%s_%p_%t'  archivelog all delete input;4> backup current controlfile format '/s01/backup/control_%s_%p_%t';5> }

1、日志组一个成员损坏

-->检查redolog日志组状态SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         39        100          2 YES INACTIVE            4219341679 2014-03-31 11:38:20         2          1         40        100          2 NO  CURRENT             4219346542 2014-03-31 13:51:26         3          1         38        100          2 YES INACTIVE            4219340026 2014-03-31 11:38:08         4          1         37         20          2 YES INACTIVE            4219339805 2014-03-31 11:38:02-->正常关闭数据库SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.-->删除日志组4的一个日志组成员SQL> ! rm -f /u01/app/oracle/oradata/redolog/redo04b.log-->启动数据库，数据库可以正常启动但是在alert log 日志中报错误。Mon Mar 31 14:02:30 2014ARC3 started with pid=23, OS id=5593 Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_lgwr_5503.trc:ORA-00313: open failed for members of log group 4 of thread 1ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/redolog/redo04b.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_lgwr_5503.trc:ORA-00321: log 4 of thread 1, cannot update log file headerORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/redolog/redo04b.log'Mon Mar 31 14:02:31 2014Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_m000_5595.trc:ORA-00313: open failed for members of log group 4 of thread 1ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/redolog/redo04b.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directory-->检查发现SQL> select a.group#,a.thread#,b.member,a.bytes/1024/1024 size_MB,a.members,a.archived,a.status from v$log a,v$logfile b where a.group#=b.group#;    GROUP#    THREAD# MEMBER                                                SIZE_MB    MEMBERS ARC STATUS---------- ---------- -------------------------------------------------- ---------- ---------- --- ----------------         1          1 /u01/app/oracle/oradata/redolog/redo01a.log               100          2 YES INACTIVE         1          1 /u01/app/oracle/oradata/redolog/redo01b.log               100          2 YES INACTIVE         2          1 /u01/app/oracle/oradata/redolog/redo02a.log               100          2 NO  CURRENT         2          1 /u01/app/oracle/oradata/redolog/redo02b.log               100          2 NO  CURRENT         3          1 /u01/app/oracle/oradata/redolog/redo03a.log               100          2 YES INACTIVE         3          1 /u01/app/oracle/oradata/redolog/redo03b.log               100          2 YES INACTIVE         4          1 /u01/app/oracle/oradata/redolog/redo04a.log                20          2 YES INACTIVE         4          1 /u01/app/oracle/oradata/redolog/redo04b.log                20          2 YES INACTIVE8 rows selected.-->SQL> ! ls /u01/app/oracle/oradata/redolog/redo01a.log  redo01b.log  redo02a.log  redo02b.log  redo03a.log  redo03b.log  redo04a.log-->SQL> select * from v$logfile;    GROUP# STATUS  TYPE    MEMBER                                             IS_---------- ------- ------- -------------------------------------------------- ---         1         ONLINE  /u01/app/oracle/oradata/redolog/redo01a.log        NO         1         ONLINE  /u01/app/oracle/oradata/redolog/redo01b.log        NO         2         ONLINE  /u01/app/oracle/oradata/redolog/redo02a.log        NO         2         ONLINE  /u01/app/oracle/oradata/redolog/redo02b.log        NO         3         ONLINE  /u01/app/oracle/oradata/redolog/redo03a.log        NO         3         ONLINE  /u01/app/oracle/oradata/redolog/redo03b.log        NO         4         ONLINE  /u01/app/oracle/oradata/redolog/redo04a.log        NO         4 INVALID ONLINE  /u01/app/oracle/oradata/redolog/redo04b.log        NO8 rows selected.-->SQL> alter database add logfile member '/u01/app/oracle/oradata/redolog/redo04b.log' to group 4;alter database add logfile member '/u01/app/oracle/oradata/redolog/redo04b.log' to group 4*ERROR at line 1:ORA-01577: cannot add log file '/u01/app/oracle/oradata/redolog/redo04b.log' - file already part of database-->遇到这种情况使用alter database clear方式重建group.SQL> alter database clear logfile group 4;Database altered.SQL> select * from v$logfile;    GROUP# STATUS  TYPE    MEMBER                                             IS_---------- ------- ------- -------------------------------------------------- ---         1         ONLINE  /u01/app/oracle/oradata/redolog/redo01a.log        NO         1         ONLINE  /u01/app/oracle/oradata/redolog/redo01b.log        NO         2         ONLINE  /u01/app/oracle/oradata/redolog/redo02a.log        NO         2         ONLINE  /u01/app/oracle/oradata/redolog/redo02b.log        NO         3         ONLINE  /u01/app/oracle/oradata/redolog/redo03a.log        NO         3         ONLINE  /u01/app/oracle/oradata/redolog/redo03b.log        NO         4         ONLINE  /u01/app/oracle/oradata/redolog/redo04a.log        NO         4         ONLINE  /u01/app/oracle/oradata/redolog/redo04b.log        NO8 rows selected.SQL> ! ls /u01/app/oracle/oradata/redolog/redo01a.log  redo01b.log  redo02a.log  redo02b.log  redo03a.log  redo03b.log  redo04a.log  redo04b.log

2、非当前日志组损坏
这种情况也是比较好恢复的。与上面的实例相似。

-->检查日志组状态SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         39        100          2 YES INACTIVE            4219341679 2014-03-31 11:38:20         2          1         40        100          2 YES INACTIVE            4219346542 2014-03-31 13:51:26         3          1         42        100          2 NO  CURRENT             4219347530 2014-03-31 14:19:17         4          1         41         20          2 YES INACTIVE            4219347526 2014-03-31 14:19:12-->关闭数据库SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.-->删除日志组2全部成员SQL> ! rm -f /u01/app/oracle/oradata/redolog/redo02*-->启动数据库SQL> startupORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2235208 bytesVariable Size             792724664 bytesDatabase Buffers          268435456 bytesRedo Buffers                5541888 bytesDatabase mounted.ORA-03113: end-of-file on communication channelProcess ID: 5879Session ID: 1 Serial number: 5Mon Mar 31 14:22:00 2014ARC1 started with pid=21, OS id=5883 Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_lgwr_5797.trc:ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02b.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02a.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_lgwr_5797.trc:ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02b.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02a.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_ora_5879.trc:ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02a.log'ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02b.log'Mon Mar 31 14:22:00 2014ARC2 started with pid=22, OS id=5885 USER (ospid: 5879): terminating the instance due to error 313System state dump requested by (instance=1, osid=5879), summary=[abnormal instance termination].System State dumped to trace file /u01/app/oracle/diag/rdbms/db/db/trace/db_diag_5787.trcDumping diagnostic data in directory=[cdmp_20140331142200], requested by (instance=1, osid=5879), summary=[abnormal instance termination].Instance terminated by USER, pid = 5879-->启动数据库到mount状态SQL> startup mountORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2235208 bytesVariable Size             792724664 bytesDatabase Buffers          268435456 bytesRedo Buffers                5541888 bytesDatabase mounted.SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         39        100          2 YES INACTIVE            4219341679 2014-03-31 11:38:20         4          1         41         20          2 YES INACTIVE            4219347526 2014-03-31 14:19:12         3          1         42        100          2 NO  CURRENT             4219347530 2014-03-31 14:19:17         2          1         40        100          2 YES INACTIVE            4219346542 2014-03-31 13:51:26-->处理SQL> alter database clear logfile group 2;Database altered.-->数据库可以正常打开。SQL> alter database open;Database altered.

3、当前日志组损坏
这种状态处理起来是比较麻烦的。需要做不完全恢复或者强制打开，这些都是有可能导致数据丢失的。因此我们要尽量避免这种情况的发生，对每组日志进行镜像就是一个不错的方法。

-->恢复方法:1）如果有归档和备份，用不完全恢复。SQL>startup mount;SQL>recover database until cancel; 先选择auto尽可能多的利用归档日志进行恢复，然后再次执行SQL>recover database until cancel; 这次输入cancel，完成不完全恢复用resetlogs打开数据SQL>alter database open resetlogs; 2）强制恢复， 这种方法可能会导致数据不一致sql>startup mount;sql>alter system set "_allow_resetlogs_corruption"=true scope=spfile;sql>recover database until cancel;sql>alter database open resetlogs;-->使用该隐含参数将库来起来以后，需要将数据导出然后重新建库。

下面演示一下不完全恢复的方法

-->检查日志组状态SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         39        100          2 YES INACTIVE            4219341679 2014-03-31 11:38:20         2          1          0        100          2 YES UNUSED              4219346542 2014-03-31 13:51:26         3          1         42        100          2 NO  CURRENT             4219347530 2014-03-31 14:19:17         4          1         41         20          2 YES INACTIVE            4219347526 2014-03-31 14:19:12-->关闭数据库SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.-->SQL> ! rm -f /u01/app/oracle/oradata/redolog/redo03*-->SQL> startupORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2235208 bytesVariable Size             792724664 bytesDatabase Buffers          268435456 bytesRedo Buffers                5541888 bytesDatabase mounted.ORA-03113: end-of-file on communication channelProcess ID: 6353Session ID: 1 Serial number: 5Mon Mar 31 14:31:17 2014ARC1 started with pid=21, OS id=6357 Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_lgwr_6271.trc:ORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03b.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03a.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_lgwr_6271.trc:ORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03b.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03a.log'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_ora_6353.trc:ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03a.log'ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03b.log'Mon Mar 31 14:31:17 2014ARC2 started with pid=22, OS id=6359 USER (ospid: 6353): terminating the instance due to error 313System state dump requested by (instance=1, osid=6353), summary=[abnormal instance termination].System State dumped to trace file /u01/app/oracle/diag/rdbms/db/db/trace/db_diag_6261.trcDumping diagnostic data in directory=[cdmp_20140331143117], requested by (instance=1, osid=6353), summary=[abnormal instance termination].Instance terminated by USER, pid = 6353-->SQL> startup mountORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2235208 bytesVariable Size             792724664 bytesDatabase Buffers          268435456 bytesRedo Buffers                5541888 bytesDatabase mounted.SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;    GROUP#    THREAD#  SEQUENCE#    SIZE_MB    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         39        100          2 YES INACTIVE            4219341679 2014-03-31 11:38:20         4          1         41         20          2 YES INACTIVE            4219347526 2014-03-31 14:19:12         3          1         42        100          2 NO  CURRENT             4219347530 2014-03-31 14:19:17         2          1          0        100          2 YES UNUSED              4219346542 2014-03-31 13:51:26SQL> alter database clear logfile group 3;alter database clear logfile group 3*ERROR at line 1:ORA-00350: log 3 of instance db (thread 1) needs to be archivedORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03a.log'ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03b.log'-->做不完全恢复SQL> recover database until cancel;Media recovery complete.-->使用resetlogs方式打开数据库SQL> alter database open resetlogs;Database altered.

--END--

0 0