oracle 日志恢复实验

来源：互联网发布：centos 更改时区编辑：程序博客网时间：2024/04/28 09:49

第一种情况：当数据正常关闭时，日志文件被损坏了

SQL> startup
ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size                  1220724 bytes
Variable Size             180359052 bytes
Database Buffers          402653184 bytes
Redo Buffers                7163904 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'/u01/app/oracle/oradata/oracle10g/redo03.log'

通过观察发现：redo01.log不是当前日志。
SQL> select group#,sequence#,archived,status from v$log;

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

通过clear命令清空日志文件，重建日志文件。

SQL> alter database clear unarchived logfile group 3;

Database altered.
打开数据库。

SQL> alter database open ;

Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
BEST OPEN

SQL>

第二种情况：损坏的是当前的日志文件。

SQL> startup
ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size                  1220724 bytes
Variable Size             180359052 bytes
Database Buffers          402653184 bytes
Redo Buffers                7163904 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/oradata/oracle10g/redo01.log'

SQL> desc v$log
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                             NUMBER
THREAD#                                            NUMBER
SEQUENCE#                                          NUMBER
BYTES                                              NUMBER
MEMBERS                                            NUMBER
ARCHIVED                                           VARCHAR2(3)
STATUS                                             VARCHAR2(16)
FIRST_CHANGE#                                      NUMBER
FIRST_TIME                                         DATE

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

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

试图通过clear 命令重建日志文件。

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/u01/app/oracle/oradata/oracle10g/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

发现清除不了。试一试until cancel，基于取消的恢复。

SQL> recover database until cancel;
Media recovery complete.

成功了。打开数据库。没有加resetlogs 参数。

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 instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
BEST OPEN

SQL>

打开了。完毕。