oracle-11g-redo损坏非归档测试恢复

最新推荐文章于 2023-05-09 10:38:07 发布

1080galf

最新推荐文章于 2023-05-09 10:38:07 发布

阅读量1.1k

点赞数

分类专栏： oracle 文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/qq_38264153/article/details/122544301

版权

oracle 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

数据库版本：11.2.0.4

1、删除oracle所有的redolog日志，强制启动oracle数据库，可能造成数据丢失：
===删除所有redolog
[oracle@yictdbtest1 trade] $r m * . l o g 接着创建表，并插入数据，发现数据库还能正常使用，切换日志也没有问题：然后关闭数据库，并开始强制启动数据库恢复。强制恢复正常后，新建的表数据都还在。 [o r a c l e @ y i c t d b t e s t 1 t r a d e]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 17 15:11:48 2022

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

INSTANCE_NAME STATUS

trade MOUNTED

SQL> alter system set “_allow_resetlogs_corruption” = true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 637535392 bytes
Database Buffers 1795162112 bytes
Redo Buffers 20275200 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered

SQL> alter system set “_allow_resetlogs_corruption” = false scope=spfile;
然后重启数据库即可恢复正常

2、损坏current 当前的redolog

[oracle@yictdbtest1 trade]$ > redo02.log

SQL> create table zxc.t4 (id int);

Table created.

SQL> insert into zxc.t4 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>
select b.group#,a.sequence#,a.status,a.bytes/1024/1024,b.member from v $l o g a, v$ logfile b where a.group#=b.group# order by 1;

GROUP# SEQUENCE# STATUS A.BYTES/1024/1024 MEMBER

 1         10 INACTIVE                  50 /db19c/db11g/data/trade/redo01.log
 2         11 CURRENT                   50 /db19c/db11g/data/trade/redo02.log
 3          0 UNUSED                    50 /db19c/db11g/data/trade/redo03.log

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 16039
Session ID: 191 Serial number: 3

SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE

启动数据：
startup mount；

SQL> recover database until cancel;
ORA-00279: change 1086160 generated at 01/17/2022 15:41:18 needed for thread 1
ORA-00289: suggestion : /db19c/db11g/oracle/product/11.2.0/db/dbs/arch1_11_1094225592.dbf
ORA-00280: change 1086160 for thread 1 is in sequence #11

Specify log: {=suggested | filename | AUTO | CANCEL}
auto

接着open或者open resetlogs 都不行，还是需要修改参数：

SQL> alter system set “_allow_resetlogs_corruption” = true scope=spfile;
shutdown immediate;
startup mount;
–SQL> recover database until cancel;
SQL> alter database open resetlogs;

Database altered.
此时打开数据库，发现zxc.t4 表已经不存在，数据丢失。

SQL> alter system set “_allow_resetlogs_corruption” = false scope=spfile;
然后重启数据库即可恢复正常
此时在current损坏后提交的数据都丢失了。

3、部分处于inictive状态的redo被删除：
rm /db19c/db11g/data/trade/redo03.log
此时数据库使用是正常的，切换也不会报错，数据也不会丢失，只是在下一次重启数据库回启动提示redo问题修复：
修复步骤如下：
启动数据库：
startup mount;

set linesize 300
set pagesize 10000
col member for a60
col status for a10
select a.group#,a.sequence#,a.status,a.bytes/1024/1024/1024 from v $KaTeX parse error: Expected 'EOF', got '#' at position 22: \dots select b.group#̲,a.sequence#,a.\dots$ log a,v$logfile b
where a.group#=b.group# order by 1;

GROUP#  SEQUENCE# STATUS     A.BYTES/1024/1024 MEMBER

     1         31 INACTIVE                  50 /db19c/db11g/data/trade/redo01.log
     2         32 CURRENT                   50 /db19c/db11g/data/trade/redo02.log
     3         30 INACTIVE                  50 /db19c/db11g/data/trade/redo03.log

检查存放oracleredolog的位置，发现/db19c/db11g/data/trade/redo03.log日志丢失了。

此时只需要进行不完全恢复（数据不会丢失），但要resetlogs数据库就可以自动创建一份redolog；
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.