oracle没什么没有备份,oracle中无备份丢失当前日志组的恢复

oracle中无备份丢失当前日志组的恢复

1.当前日志组有镜像

SQL> SELECT

l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

2  FROM V$LOG l,V$LOGFILE lf

3  WHERE l.GROUP#=lf.GROUP#

4  ORDER BY l.GROUP#;

GROUP# ARC STATUS           MEMBER

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

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

1 NO  CURRENT          /ORADATA/szscdb/redo01.log

1 NO  CURRENT          /ORADATA/szscdb/redo01a.log

2 YES INACTIVE

/ORADATA/szscdb/redo02a.log

2 YES INACTIVE

/ORADATA/szscdb/redo02.log

3 YES INACTIVE

/ORADATA/szscdb/redo03a.log

3 YES INACTIVE

/ORADATA/szscdb/redo03.log

6 rows selected.

SQL> !mv /ORADATA/szscdb/redo01.log /tmp

Tue Sep 10 15:55:32 2013

Errors in file

/u02/app/oracle/diag/rdbms/szscpdb/szscdb/trace/szscdb_m000_32697.trc:

ORA-00313: open failed for members of log

group 1 of thread 1

ORA-00312: online log 1 thread 1:

'/ORADATA/szscdb/redo01.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file

/u02/app/oracle/diag/rdbms/szscpdb/szscdb/trace/szscdb_m000_32697.trc:

SQL> SELECT

l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

2  FROM V$LOG l,V$LOGFILE lf

3  WHERE l.GROUP#=lf.GROUP#

4  ORDER BY l.GROUP#;

GROUP# ARC STATUS           MEMBER

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

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

1 YES ACTIVE

/ORADATA/szscdb/redo01.log

1 YES ACTIVE           /ORADATA/szscdb/redo01a.log

2 YES ACTIVE

/ORADATA/szscdb/redo02a.log

2 YES ACTIVE

/ORADATA/szscdb/redo02.log

3 NO  CURRENT          /ORADATA/szscdb/redo03a.log

3 NO  CURRENT          /ORADATA/szscdb/redo03.log

6 rows selected.

SQL> alter database drop logfile member

'/ORADATA/szscdb/redo01.log';

Database altered.

SQL> alter database add logfile member

'/ORADATA/szscdb/redo01.log' to group 1;

Database altered.

2.当前日志组的所有成员丢失

SQL> SELECT

l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

2  FROM V$LOG l,V$LOGFILE lf

3  WHERE l.GROUP#=lf.GROUP#

4  ORDER BY l.GROUP#;

GROUP# ARC STATUS           MEMBER

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

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

1 YES ACTIVE

/ORADATA/szscdb/redo01.log

1 YES ACTIVE

/ORADATA/szscdb/redo01a.log

2 YES ACTIVE

/ORADATA/szscdb/redo02a.log

2 YES ACTIVE

/ORADATA/szscdb/redo02.log

3 NO  CURRENT          /ORADATA/szscdb/redo03a.log

3 NO  CURRENT          /ORADATA/szscdb/redo03.log

6 rows selected.

SQL> !mv /ORADATA/szscdb/redo03* /tmp

现在出现switch logfile卡死

SQL> SELECT

l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

2  FROM V$LOG l,V$LOGFILE lf

3  WHERE l.GROUP#=lf.GROUP#

4  ORDER BY l.GROUP#;

GROUP# ARC STATUS           MEMBER

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

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

1 NO  INACTIVE         /ORADATA/szscdb/redo01.log

1 NO  INACTIVE         /ORADATA/szscdb/redo01a.log

2 NO  CURRENT          /ORADATA/szscdb/redo02a.log

2 NO  CURRENT          /ORADATA/szscdb/redo02.log

3 NO  INACTIVE         /ORADATA/szscdb/redo03a.log

3 NO  INACTIVE         /ORADATA/szscdb/redo03.log

6 rows selected.

#确认丢失的是当前redo log file

#尝试清空日志组失败

SQL> alter database clear logfile group

2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery

of instance szscdb (thread 1)

ORA-00312: online log 2 thread 1:

'/ORADATA/szscdb/redo02.log'

ORA-00312: online log 2 thread 1:

'/ORADATA/szscdb/redo02a.log'

###执行检查点，日志组的状态没有改变

SQL> alter system checkpoint;

System altered.

SQL> SELECT

l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER

2  FROM V$LOG l,V$LOGFILE lf

3  WHERE l.GROUP#=lf.GROUP#

4  ORDER BY l.GROUP#;

GROUP# ARC STATUS           MEMBER

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

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

1 NO  INACTIVE         /ORADATA/szscdb/redo01.log

1 NO  INACTIVE         /ORADATA/szscdb/redo01a.log

2 NO  CURRENT          /ORADATA/szscdb/redo02a.log

2 NO  CURRENT          /ORADATA/szscdb/redo02.log

3 NO  INACTIVE         /ORADATA/szscdb/redo03a.log

3 NO  INACTIVE         /ORADATA/szscdb/redo03.log

6 rows selected.

SQL> alter database clear logfile group

2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery

of instance szscdb (thread 1)

ORA-00312: online log 2 thread 1:

'/ORADATA/szscdb/redo02.log'

ORA-00312: online log 2 thread 1:

'/ORADATA/szscdb/redo02a.log'

###启动隐含参数_allow_resetlogs_corruption，该参数指定数据库在启动的时候跳过一致性检查，否则会出现如下错误

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: '/ORADATA/szscdb/system01.dbf

###该参数修改只有在数据库重启时生效

SQL> alter system set

"_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

SQL> show parameter allow

NAME                                 TYPE        VALUE

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

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

_allow_resetlogs_corruption          boolean     TRUE

SQL> recover database until cancel;

##以resetlogs的方式打开，由于这两启动了隐含参数，说没有做一致性检查

SQL> alter database open resetlogs;

##去掉该隐含参数

SQL> alter system reset

"_allow_resetlogs_corruption" scope=spfile;

SQL> shutdown immediate;

SQL> startup

注意：

1.在完成这一系列的操作之后建议对数据库做一个full export，然后重新创建数据库。因为在我的实验环境里面已经开始报各种莫名其妙的错误了。

2.建议给reodo log file组的日志加上member是非常有必要的，而且要房子不同的磁盘上，实现真正的冗余。