oracle-redolog日常管理

最新推荐文章于 2024-08-27 19:00:35 发布
最新推荐文章于 2024-08-27 19:00:35 发布
阅读量3.4k 收藏 10
点赞数
分类专栏： oracle 文章标签： database redolog 日志重建 故障 管理
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ora01555/article/details/22667303
版权
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 CLEARING

6 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))) a
GROUP BY thread#, ttime
order 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.log

10 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 immediate
Database 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 2014
ARC3 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 1
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/redolog/redo04b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors 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 header
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/redolog/redo04b.log'
Mon Mar 31 14:02:31 2014
Errors 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 1
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/redolog/redo04b.log'
ORA-27037: unable to obtain file status
Linux-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 INACTIVE

8 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        NO

8 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        NO

8 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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-->删除日志组2全部成员
SQL> ! rm -f /u01/app/oracle/oradata/redolog/redo02*

-->启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             792724664 bytes
Database Buffers          268435456 bytes
Redo Buffers                5541888 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5879
Session ID: 1 Serial number: 5

Mon Mar 31 14:22:00 2014
ARC1 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 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/redolog/redo02a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors 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 2014
ARC2 started with pid=22, OS id=5885 
USER (ospid: 5879): terminating the instance due to error 313
System 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.trc
Dumping 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 mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             792724664 bytes
Database Buffers          268435456 bytes
Redo Buffers                5541888 bytes
Database 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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-->
SQL> ! rm -f /u01/app/oracle/oradata/redolog/redo03*

-->
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             792724664 bytes
Database Buffers          268435456 bytes
Redo Buffers                5541888 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6353
Session ID: 1 Serial number: 5

Mon Mar 31 14:31:17 2014
ARC1 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 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/redolog/redo03a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors 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 2014
ARC2 started with pid=22, OS id=6359 
USER (ospid: 6353): terminating the instance due to error 313
System 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.trc
Dumping diagnostic data in directory=[cdmp_20140331143117], requested by (instance=1, osid=6353), summary=[abnormal instance termination].
Instance terminated by USER, pid = 6353

-->
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             792724664 bytes
Database Buffers          268435456 bytes
Redo Buffers                5541888 bytes
Database 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:26


SQL> 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 archived
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'

-->做不完全恢复
SQL> recover database until cancel;
Media recovery complete.

-->使用resetlogs方式打开数据库
SQL> alter database open resetlogs;

Database altered.

--END--



gltyi99
关注
专栏目录
oracle redo01. log,redo01.log恢复问题
weixin_39725650的博客
04-04 728
背景： 在正常oracle11g正常open状态，我操作系统mv 了redo01.log然后rman不知道该怎么恢复。可是通过下面的脚本居然重建了redo01.log我不知道哪一步能重建的.看哪个日志可以知道呢？ps：操作过程如下：SQL> select member from v$logfile;MEMBER-----------------------------------------...
管理Redo Log
个人公众号：Alen的数据库学习笔记
08-26 636
Redo Log是什么？ 恢复操作最重要的结构就是重做日志，它由两个或更多预分配的文件组成，这些文件存储对数据库所做的所有变更。每个数据库实例都有一个相关的 重做日志用以在实例故障时保护数据库。 重做线程-Redo Threads 当谈到多个数据库实例的上下文，每个数据库实例的重做日志也被称为重做线程。在典型配置中，只有一个数据库实例访问Oracle数据库，因此只有一个线程存
oracle 重做日志（Redo LogBuffer）
最新发布
睡不醒的每天
08-27 1144
重做日志文件用于记录事务操作所引起的数据的变化，包括回滚段事务表、回滚块、数据块上的事务槽、数据行的变化等，当执行DDL、DML操作时，有LGWR进程将日志缓冲区中与事务相关的重做记录写入到重做日志文件中。当丢失或损坏数据库中的数据时，Oracle会根据redo文件中的记录 恢复丢失的数据。
log添加 oracle redo_添加Redo log Member/Group-Oracle
weixin_31232695的博客
12-28 581
添加Redo log Member/Group1.添加redo log groupsa: ALTER DATABASE ADD LOGFILE(‘/oracle/dbs/log1c.rdo’,’/oracle/dbs/log2c.rdo’) SIZE 100M;b:ALTER DATABASE ADD LOGFILE GROUP 10 (‘/oracle/dbs/log1c.rdo’, ‘/or...
centos7 oracle 开启redolog方法
huijieshizhu0的博客
04-10 240
环境准备 centos版本：centos 7 oracle版本：oracle 12c 1.管理员身份连接数据库 sqlplus sys/sys@oratest as sysdba 2.查看当前归档模式，是归档还是非归档 SQL> archive log list; Database log mode No Archive Mode Auto...
oracle redo01. log,ORACLE，REDO01.LOG,REDO02.LOG,REDO03.LOG被删，或ORA-00313
weixin_35107169的博客
04-04 574
某天工作需要连接oracle时突然报错oracle initialization or shutdown in progress，具体是什么操作导致这个问题也不知道，后来网上找到了解决方案，自己试过的，成功的解决了问题，在这记录一下，期望有机会也可以帮到别人。Oracle提示ORA-00313的错误，并且报ORA-00313和ORA-00312错误，原因是日志被误操作删除，解决方法如下：运行cmd...
oracle-DBA日常检查.doc
09-09
Oracle DBA（数据库管理员）的日常检查工作是确保数据库高效、稳定运行的关键环节。下面将详细阐述在Oracle 10g数据库中进行日常检查时涉及的一些核心知识点。 1. **数据库基本状态检查**： - **Oracle实例状态**...
log添加 oracle redo_学习笔记:Oracle在线日志 DBA常用添加、删除、切换redo日志文件命令汇总...
weixin_39716521的博客
12-19 891
天萃荷净汇总运维DBA日常工作对redo在线日志文件的管理命令，创建、更改、删除redo file在线日志文件的案例1、切换redo日志文件alter system switch logfile;2、增加redo联机日志组alter database add logfile group 4 (‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04_1.LOG’,...
Oracle Redo Log解析：数据库恢复的关键
在日常运维中，管理员可以通过ALTER DATABASE命令来管理Redo Log，包括禁用特定线程、删除日志文件组等操作。这些操作需要谨慎执行，以防止对数据库的正常运行产生影响。 Oracle Redo Log是数据库稳定运行的关键，...
oracle-DBA日常检查[参照].pdf
10-11
Oracle DBA（数据库管理员）的日常检查工作是确保数据库高效、稳定运行的关键环节。这份"Oracle 10g 数据库日常维护手册"提供了一系列检查点，涵盖了从基础的实例状态到复杂的性能监控等多个方面。以下是对这些关键...
Oracle-RAC原理浅谈.ppt
09-07
在Oracle RAC的部署过程中，需要注意的关键组件包括密码文件（Passwordfile）、实例（Instance）、全局系统监控器（SGA）、重做日志缓冲区（Redo Log Buffer）、服务器进程（Serverprocess）、pga（Program Global ...
oracle安装 redo log,Oracle Redo Log 及Logmnr配置使用
weixin_30058099的博客
04-07 218
三、日志文件组、日志切换和日志归档-------------Member默认情况下，Oracle数据库实例创建完成以后就已创建了3组日志组。每组有两个日志文件(每个日志文件称为Member)，每个组内所有Member记录同样的信息，每组有两个或两个以上的Member，这样保证此日志文件的高可用性，即当其中一个文件损坏时，其他文件可用。同时为了保证HA(高可用性)建议将Redo Log File 组...
ORACLE，REDO01.LOG,REDO02.LOG,REDO03.LOG被删，或ORA-00313
tongge01uuu的博客
06-13 3045
本文转载自：http://blog.163.com/liuyang_yes/blog/static/128133212200911244347802/
Oracle的redo log（重做日志）
睿思达DBA
03-03 1376
Oracle的redo log（重做日志） Oracle日志文件是Oracle数据库存储信息的重要文件，主要用来存储数据库变化的操作信息。Oracle的重做日志分为在线重做日志和归档重做日志： （1）online Redo log files（在线重做日志）：又称联机重做日志，在线重做日志实时记录数据库的数据更新。 （2）Archive Redo log files（归档重做日志）：简称归档日志，...
oracle处理手记-200907
cuiwadui3512的博客
07-21 339
20090718-01问题：RMAN> recover database;的时候碰到RMAN-03002: recover 命令 (在 07/18/2009 21:36:09 上) 失败ORA-19698: D:ORACL...
开启Oracle的RedoLog配置
sunny05296的博客
07-20 2930
Oracle Redo Log配置： 大致修改步骤： 1.安装logminer 2.开启日志的归档模式 3.开启补充日志(primary key,unique,foreign key,all) 4.日志优化（调整在线日志文件大小和个数） 5.创建redolog同步账号 step1：安装logminer --安装logminer SQL&gt; conn / as sysdba SQL&gt; ...
修改redo logfiles size 大小
代码创造世界
10-13 554
在线修改online redo logfiles size 大小 oracle redolog size 过小有时候会导致性能问题，现在我们在线修改redolog，一般在业务量比较小的时候进行此操作 首先查看当前的日志组大小，路径名 [oracle@tora10 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 23 17:08:33 2013 SQL> select group#, b
oracle redo重做,重做日志文件(redo log)
weixin_34280468的博客
04-05 527
重做日志文件redo log file对于oracle数据库至关重要。它们是数据库的事务日志。通常只用于恢复，也可用于以下工作。1.1系统崩溃后的实例恢复1.2通过备份恢复数据文件之后恢复介质1.3备用数据库处理1.4Input into “Streams,” a redo log mining process for information sharing (a fancyway of sayi...
redolog日志
weixin_45683550的博客
01-25 676
InnoDB的redolog是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这个文件总共就可以记录4GB的操作。checkpoint：检查点，代表的是当前要擦除的位置，擦除之前要把记录更新到数据文件write pos：代表当前记录的位置；write pos和checkpoint之间的是还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值