redo状态为active丢失的恢复

最新推荐文章于 2021-04-22 09:36:41 发布

cri3224

最新推荐文章于 2021-04-22 09:36:41 发布

阅读量200

点赞数

文章标签：数据库

对于redo为active状态的日志文件丢失，其实是很郁闷的事情，因为此时已经归档了，但oracle还会按照current来报错，所以我们的办法要么恢复，要么将日志从归档中拷贝过去，如果恢复的话，那这里就不用说了，主要是我们该如何将归档的内容重新返回到redo中。（这里主要采用dd命令，但稍微麻烦的是如果归档文件的内容大小比redo（比如redo为50M）小很多，在这种情况下的处理情况）
SQL> create table t(x varchar2(10));

Table created.

SQL> select group#,ARCHIVED,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES ACTIVE
         2 NO CURRENT
         3 YES UNUSED

SQL> insert into t values('1 yes inactive');
insert into t values('1 yes inactive')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."T"."X" (actual: 14, maximum: 10)

SQL> insert into t values('1yinactive');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select group#,ARCHIVED,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 NO CURRENT
         3 YES UNUSED

SQL> insert into t values('2yactive');

1 row created.

SQL> commit;

Commit complete.

SQL> select group#,ARCHIVED,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 NO CURRENT
         3 YES UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#,ARCHIVED,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 YES ACTIVE
         3 NO CURRENT

SQL> insert into t values('3ncurrent');

1 row created.

SQL> commit;

Commit complete.

SQL> select group#,ARCHIVED,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 YES ACTIVE
         3 NO CURRENT
SQL> shutdown abort
ORACLE instance shut down.
[oracle@baobao dong]$ rm -rf redo02.log
[oracle@baobao dong]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 27 15:03:17 2013

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2092498944 bytes
Fixed Size                  1337604 bytes
Variable Size             268437244 bytes
Database Buffers         1811939328 bytes
Redo Buffers               10784768 bytes
Database mounted.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
[oracle@baobao dong]$ cp /u01/app/oracle/diag/rdbms/dong/1_2_832590756.dbf /u01/app/oracle/oradata/dong/redo02.log
SQL>alter database open;--报错如下。所以看来直接将归档cp过去不行。
ORA-00327: log 2 of thread 1, physical size 2332 less than needed 102400
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/dong/redo02.log'
[oracle@baobao dong]$ cd /u01/app/oracle/diag/rdbms/dong
[oracle@baobao dong]$ ll -str
4860 -rw-r-----. 1 oracle oinstall 4974080 11月 27 14:58 1_1_832590756.dbf
1168 -rw-r-----. 1 oracle oinstall 1194496 11月 27 15:01 1_2_832590756.dbf---这个就是redo02的归档
[oracle@baobao dong]$ cp /u01/app/oracle/oradata/dong/redo03.log /u01/app/oracle/oradata/dong/redo02.log
[oracle@baobao dong]$ ll /u01/app/oracle/oradata/dong/redo0*
-rw-r-----. 1 oracle oinstall 52429312 11月 27 14:58 /u01/app/oracle/oradata/dong/redo01.log
-rw-r--r--. 1 oracle oinstall 52429312 11月 27 15:31 /u01/app/oracle/oradata/dong/redo02.log
-rw-r-----. 1 oracle oinstall 52429312 11月 27 15:01 /u01/app/oracle/oradata/dong/redo03.log
[oracle@baobao dong]$ dd if=1_2_832590756.dbf of=/u01/app/oracle/oradata/dong/redo02.log bs=512b count=2332 conv=notrunc
记录了4+1 的读入
记录了4+1 的写出
1194496字节(1.2 MB)已复制，0.0131623 秒，90.8 MB/秒
[oracle@baobao dong]$ ll /u01/app/oracle/oradata/dong/redo0*
-rw-r-----. 1 oracle oinstall 52429312 11月 27 14:58 /u01/app/oracle/oradata/dong/redo01.log
-rw-r--r--. 1 oracle oinstall 52429312 11月 27 15:31 /u01/app/oracle/oradata/dong/redo02.log
-rw-r-----. 1 oracle oinstall 52429312 11月 27 15:01 /u01/app/oracle/oradata/dong/redo03.log
SQL> alter database open;
SQL> select * from t;