月归档：八月 2011

当前联机日志损坏恢复

发表于 2011 年 8 月 24 日 由 惜分飞

环境模拟
删除状态为active的联机日志，然后强行关闭数据库

处理过程
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Alert.log 日志中错误
Wed Aug 24 00:26:33 2011
Errors in file /u01/admin/xienfei/udump/xff_ora_9186.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

xff_ora_9186.trc文件中错误
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

查询当前日志组状态
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
1 CURRENT /u01/oradata/xienfei/redo01.log
3 INACTIVE /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log

尝试删除redo日志
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance xff (thread 1) – cannot drop
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’

发现是当前日志不能被删除，尝试切换日志
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

在数据库未打开状态，不能切换日志，只能尝试清空日志
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance xff (thread 1)
ORA-00312: online log 1 thread 1: ‘/u01/oradata/xienfei/redo01.log’

因为数据库恢复需要使用，不能被清空，尝试不完成恢复
SQL> recover database until cancel;
ORA-00279: change 1272687 generated at 08/24/2011 00:20:05 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_27_756841839.arc
ORA-00280: change 1272687 for thread 1 is in sequence #27

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1272903 generated at 08/24/2011 00:25:17 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_28_756841839.arc
ORA-00280: change 1272903 for thread 1 is in sequence #28
ORA-00278: log file ‘/u01/archive/1_27_756841839.arc’ no longer needed for this
recovery

ORA-00308: cannot open archived log ‘/u01/archive/1_28_756841839.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oradata/xienfei/system01.dbf’

已经提示数据不一致，尝试着打开数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oradata/xienfei/system01.dbf’

创建pfile文件，添加隐含参数，使之不进行检查点一致性校验
SQL> create pfile=’/tmp/pfile’ from spfile;

File created.

修改pfile ，添加以下参数
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE

SQL> shutdown abort
ORACLE instance shut down.

使用pfile打开数据库
SQL> startup pfile=’/tmp/pfile’
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 150995792 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database

发现flashback导致数据库不能被正常打开，尝试关闭它
SQL> alter database flashback off;

Database altered.

尝试直接open数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

根据提示，使用resetlogs打开数据库
SQL> alter database open resetlogs;

Database altered.

查询日志状态
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 UNUSED /u01/oradata/xienfei/redo02.log
1 CURRENT /u01/oradata/xienfei/redo01.log

因为group1错误，而当前日志组在group 1上，所以切换日志组
SQL> alter system switch logfile;

System altered.

SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 CURRENT /u01/oradata/xienfei/redo02.log
1 ACTIVE /u01/oradata/xienfei/redo01.log

SQL> alter system checkpoint;

System altered.

SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 UNUSED /u01/oradata/xienfei/redo03.log
2 CURRENT /u01/oradata/xienfei/redo02.log
1 INACTIVE /u01/oradata/xienfei/redo01.log
删除有问题的group 1日志组
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

添加日志组并检查是否正确
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 CURRENT /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log

SQL> alter database add logfile group 1 ‘/u01/oradata/xienfei/redo01.log’ size 50m reuse;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

GROUP# STATUS MEMBER
———- —————- ———————————————
3 ACTIVE /u01/oradata/xienfei/redo03.log
2 ACTIVE /u01/oradata/xienfei/redo02.log
1 CURRENT /u01/oradata/xienfei/redo01.log

注意：根据oracle官方建议，使用oracle隐含参数运行数据库可能存在很多不稳定因素，建议立即导出数据库数据，然后新建库，重新导入数据

发表在 Oracle备份恢复 | 一条评论

数据库open状态下日志异常处理

发表于 2011 年 8 月 24 日 由 惜分飞
一、环境模拟
删除INACTIVE状态redo日志的物理文件，然后进行大批量事务操作

二、出现现象
1、alert.log记录
Tue Aug 23 23:32:02 2011
Errors in file /u01/admin/xienfei/bdump/xff_arc1_8773.trc:
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1] [/u01/oradata/xienfei/redo03.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3

2、xff_arc1_8773.trc文件中内容
*** 2011-07-18 18:35:32.071 59526 kcrr.c
kcrrfail: dest:2 err:12541 force:0 blast:1
ORA-00313: Message 313 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1]
ORA-00312: Message 312 not found; No message file for product=RDBMS, facility=ORA; arguments: [3] [1] [/u01/oradata/xienfei/redo03.log]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory

三、处理过程
1、由alert中的redo文件路径，查询group#
SQL> select group#,member from v$logfile;
GROUP# MEMBER
———- ———————————————
3 /u01/oradata/xienfei/redo03.log
2 /u01/oradata/xienfei/redo02.log
1 /u01/oradata/xienfei/redo01.log
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         16 ACTIVE
2         17 CURRENT
3         15 INACTIVE
2、确定是inactive状态的redo日志，直接删除该日志组
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance xff (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’
3、删除失败，提示该日志需要归档，然后直接强行执行清空redo操作
SQL> alter   database   clear   logfile   group   3;
alter   database   clear   logfile   group   3
*
ERROR at line 1:
ORA-00350: log 3 of instance xff (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’
4、还不行，加大力度，直接清空并不归档
SQL> ALTER   DATABASE   CLEAR   UNARCHIVED   LOGFILE   GROUP   3;
Database altered.
5、再删除group 3
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance xff (thread 1) – cannot drop
ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’
6、还是失败，根据提示查看该日志组当前状态
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         16 INACTIVE
2         17 ACTIVE
3         18 CURRENT
7、切换日志组
SQL> alter system switch logfile;
System altered.
8、查看状态，并处理置于INACTIVE状态
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         19 CURRENT
2         17 ACTIVE
3         18 ACTIVE
SQL> alter system checkpoint;
System altered.
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         19 CURRENT
2         17 INACTIVE
3         18 INACTIVE
9、再次删除归档group 3
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance xff (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: ‘/u01/oradata/xienfei/redo03.log’
10、再次清空日志并不归档
SQL> ALTER   DATABASE   CLEAR   UNARCHIVED   LOGFILE   GROUP   3;
Database altered.
11、查询状态
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         19 CURRENT
2         17 INACTIVE
3          0 UNUSED
12、再次删除group 3
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         19 CURRENT
2         17 INACTIVE
13、删除丢失的group 3成功，需要添加一组日志
SQL> alter database add logfile group 3 ‘/u01/oradata/xienfei/redo03.log’ size 50m reuse;
Database altered.
14、核实是否正常
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select group#,sequence#,status from v$log;
GROUP#  SEQUENCE# STATUS
———- ———- —————-
1         22 CURRENT
2         21 ACTIVE
3         20 INACTIVE
发表在 Oracle备份恢复 | 2 条评论

ORA-00205问题处理

发表于 2011 年 8 月 23 日 由 惜分飞

1、启动数据库异常
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
ORA-00205: error in identifying control file, check alert log for more info

alert.log日志中记录
ALTER DATABASE MOUNT
Mon Jul 18 17:32:58 2011
ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/oradata/xienfei/control01.ctl]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory

2、根据提示缺少控制文件，第一步是看看有没有冗余的控制文件，然后修改pfile或者复制控制文件处理，如果没有利用备份控制文件恢复
RMAN> restore controlfile from ‘/tmp/rman_1kmhorc2_1_1′;

Starting restore at 18-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/oradata/xienfei/control01.ctl
Finished restore at 18-JUL-11

3、恢复控制文件后，数据库至于mount状态
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

4、添加归档日志到控制文件
RMAN> catalog start with ‘/u01/archive’;

Starting implicit crosscheck backup at 18-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
Crosschecked 29 objects
Crosschecked 12 objects
Finished implicit crosscheck backup at 18-JUL-11

Starting implicit crosscheck copy at 18-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
Crosschecked 1 objects
Finished implicit crosscheck copy at 18-JUL-11

searching for all files in the recovery area
cataloging files…
no files cataloged

searching for all files that match the pattern /u01/archive

List of Files Unknown to the Database
=====================================
…………
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done

List of Cataloged Files
=======================
…………
RMAN> exit

[oracle@node2 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jul 18 17:43:47 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

5、使用备份控制文件恢复
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008′
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008′

6、发现控制文件后又新增数据文件，需要重命名控制文件中的新增数据文件
SQL> select file#,name from v$datafile where file#=8;

FILE# NAME
———- ———————————————-
8 /u01/oracle/dbs/UNNAMED00008

SQL> alter database rename file ‘/u01/oracle/dbs/UNNAMED00008′ to ‘/u01/oradata/xienfei/cfxff01.dbf’;

Database altered.

7、继续进行不完成恢复（利用归档日志）
SQL> recover database using backup controlfile;
ORA-00279: change 1158476 generated at 07/18/2011 16:43:27 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_1_756837539.arc
ORA-00280: change 1158476 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1158594 generated at 07/18/2011 16:44:01 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_2_756837539.arc
ORA-00280: change 1158594 for thread 1 is in sequence #2
ORA-00278: log file ‘/u01/archive/1_1_756837539.arc’ no longer needed for this
recovery

…………

ORA-00308: cannot open archived log ‘/u01/archive/1_7_756837539.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

8、根据提示，应该是归档日志恢复完成，需要利用联机日志继续恢复
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo03.log
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: ‘/u01/oradata/xienfei/redo03.log’

SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo01.log
Log applied.
Media recovery complete.

9、resetlogs打开数据库
SQL> alter database open resetlogs;

Database altered.

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 | 评论关闭