07 oracle 归档模式 inactive/current redo log损坏修复--以及错误ORA-00600: internal error code, arguments: [2663], [0], [9710724], [0], [9711142], [], [], [], [], [], [], []

--环境：

SQL*Plus: Release 11.2.0.4.0 Production

CentOS release 6.5 (Final)

单实例/归档模式

1 Inactive redo log丢失或损坏的恢复

SYS@ orcl >col member forma a50;

SYS@ orcl >select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                          IS_

---------- ------- ------- -------------------------------------------------- ---

     2       ONLINE  /u01/app/oracle/oradata/orcl/redo02.log          NO

     1       ONLINE  /u01/app/oracle/oradata/orcl/redo01.log          NO

     3       ONLINE  /u01/app/oracle/oradata/orcl/redo03.log          NO

     1       ONLINE  /u01/app/oracle/oradata/orcl/redo11.log          NO

SYS@ orcl >select *from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

     1        1         289   52428800       512        2 NO  CURRENT             9672831 04-JUN-19     2.8147E+14

     2        1         287   52428800       512        1 YES INACTIVE             9656990 04-JUN-19        9658951 04-JUN-19

     3        1         288   52428800       512        1 YES INACTIVE             9658951 04-JUN-19        9672831 04-JUN-19

使用dd命令破坏处于inactive状态的 redo log group 3

[oracle@DSI admin]$ dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log bs=512 count=20

0+0 records in

0+0 records out

0 bytes (0 B) copied, 9.6715e-05 s, 0.0 kB/s

关闭重新启动报错

SYS@ orcl >shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ orcl >startup

ORACLE instance started.

Total System Global Area  784998400 bytes

Fixed Size            2257352 bytes

Variable Size          478154296 bytes

Database Buffers      297795584 bytes

Redo Buffers            6791168 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 8282

Session ID: 125 Serial number: 5

退出，启动到mount状态

SYS@ orcl >exit

[oracle@DSI ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 5 10:16:28 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  784998400 bytes

Fixed Size            2257352 bytes

Variable Size          478154296 bytes

Database Buffers      297795584 bytes

Redo Buffers            6791168 bytes

Database mounted.

SQL> select * from v$diag_info;

查看alert log

[oracle@DSI admin]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@DSI trace]$ tail -n 100 alert_orcl.log

Additional information: 1

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8282.trc:

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

查看日志组的状态，确认是处于inactive的

SYS@ orcl >set linesize 1000

SYS@ orcl >select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

     1        1         289   52428800       512        2 NO  CURRENT             9672831 04-JUN-19     2.8147E+14

     3        1         288   52428800       512        1 YES INACTIVE             9658951 04-JUN-19        9672831 04-JUN-19

     2        1         287   52428800       512        1 YES INACTIVE             9656990 04-JUN-19        9658951 04-JUN-19

进行修复

SYS@ orcl >alter database clear logfile group 3;  

Database altered.

SYS@ orcl >alter database drop logfile group 3;

Database altered.

SYS@ orcl >alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03.log') size 50m;

alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03.log') size 50m

*

ERROR at line 1:

ORA-00301: error in adding log file '/u01/app/oracle/oradata/orcl/redo03.log' - file cannot be created

ORA-27038: created file already exists

Additional information: 1

[oracle@DSI trace]$ rm /u01/app/oracle/oradata/orcl/redo03.log

SYS@ orcl >alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03.log') size 50m;

Database altered.

SYS@ orcl >alter database open;

Database altered.

SYS@ orcl >select * from test.t1;

[oracle@DSI trace]$ tail -n 100 alert_orcl.log

2 Current redo log丢失或损坏的恢复

模式数据测试

create table t6 (id int,name varchar2(100));

begin

for i in 1 .. 50000000

loop

insert into t6 values(i,'AAAAAA');

end loop;

commit;

end;

/

select * from v$log;

dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log bs=512 count=20

查看日志

SQL> start mount;

SQL> alter database clear logfile group 3;

alter database clear logfile group 3

*

ERROR at line 1:

ORA-00350: log 3 of instance orcl (thread 1) needs to be archived

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

SQL> recover database until cancel;

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

创建pfile

SQL> create pfile='/tmp/pfile.ora' from spfile;

修改2个隐含参数

[oracle@DSI ~]$ vim /tmp/pfile.ora

*._allow_resetlogs_corruption=true

*._allow_error_simulation=true

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount pfile='/tmp/pfile.ora';

ORACLE instance started.

Total System Global Area  784998400 bytes

Fixed Size            2257352 bytes

Variable Size          478154296 bytes

Database Buffers      297795584 bytes

Redo Buffers            6791168 bytes

Database mounted.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

Process ID: 2108

Session ID: 125 Serial number: 5

ORA-600[2663]与常见的ORA-600[2662]类似,都是由于block的scn大于文件头的scn导致,只不过错误的对象不一样而已.对于该类问题,我们的处理方法一般就是简单的推scn

使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库后,我们说很多时候你会遇到ORA-00600 2662号错误，这个错误的含义是:
A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
stored in a UGA variable.
If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
internal error.

参考：http://www.xifenfei.com/2014/09/ora-600-2663-%E6%95%85%E9%9A%9C%E6%81%A2%E5%A4%8D.html

ORA-600 [2662] [a] [b] [c] [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
算法计算规则如下：Arg [c]*4得出一个数值，假设为V_Wrap,
如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level
增进SCN有两种常用方法
1.通过immediate trace name方式(在数据库Open状态下)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
2.通过10015事件(在数据库无法打开，mount状态下)
alter session set events '10015 trace name adjust_scn level x';
注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整

SQL> exit

[oracle@DSI ~]$ sqlplus / as sysdba

SQL> startup mount pfile='/tmp/pfile.ora';

SQL> show parameter undo;

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

undo_management              string     AUTO

undo_retention                 integer     900

undo_tablespace              string     UNDOTBS1

SQL> oradebug setmypid

Statement processed.

SQL>  oradebug DUMPvar SGA kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000

##使用10015 event手工推进scn

SQL> alter session set events '10015 trace name adjust_scn level 1';

Session altered.

SQL> alter database open;

Database altered.

SQL> set linesize 1000

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

     1        1           7   52428800       512        2 NO  CURRENT             9733801 05-JUN-19     2.8147E+14

     2        1           5   52428800       512        1 YES ACTIVE             9732698 05-JUN-19        9733241 05-JUN-19

     3        1           6   52428800       512        1 YES ACTIVE             9733241 05-JUN-19        9733801 05-JUN-19

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

     1        1          13   52428800       512        2 NO  CURRENT             9735561 05-JUN-19     2.8147E+14

     2        1          11   52428800       512        1 YES ACTIVE             9735350 05-JUN-19        9735452 05-JUN-19

     3        1          12   52428800       512        1 YES ACTIVE             9735452 05-JUN-19        9735561 05-JUN-19

[oracle@DSI ~]$ tail -f -n 100 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

#查看是否有报错信息，一般建议这个时候进行数据库导出，导入，以防止其他问题产生

SQL> select * from test.t5;

    ID NAME

---------- ----------------------------------------------------------------------------------------------------

     1 AAAAA

     2 BBBBB

     3 CCCCC

SQL> select * from t6;

select * from t6

              *

ERROR at line 1:

ORA-00942: table or view does not exist

巴特西

07 oracle 归档模式 inactive/current redo log损坏修复--以及错误ORA-00600: internal error code, arguments: [2663], [0], [9710724], [0], [9711142], [], [], [], [], [], [], []

最新文章

热门文章